Oracle:HEALTH CHECK REPORT(Capturing Archive information).
In this article we will implement to capture the Archive information using Shell script.
Lets create a table which can store Archive information.
Connect to SYS user and execute below statement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
CREATE TABLE DBMON.ARCHIVE_USAGE_HISTORY ( GENERATION_DATE DATE NOT NULL, INST_ID NUMBER NOT NULL, TOTAL_DAY_GB NUMBER ) TABLESPACE DATA01 RESULT_CACHE (MODE DEFAULT) PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; |
Create unique index and primary key on ARCHIVE_USAGE_HISTORY to maintain unique records.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
CREATE UNIQUE INDEX DBMON.ARCHIVE_USAGE_HISTORY_PK ON DBMON.ARCHIVE_USAGE_HISTORY (GENERATION_DATE, INST_ID) LOGGING TABLESPACE DATA01 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) NOPARALLEL; ALTER TABLE DBMON.ARCHIVE_USAGE_HISTORY ADD ( CONSTRAINT ARCHIVE_USAGE_HISTORY_PK PRIMARY KEY (GENERATION_DATE, INST_ID) USING INDEX DBMON.ARCHIVE_USAGE_HISTORY_PK ENABLE VALIDATE); |
Table name is ARCHIVE_USAGE_HISTORY
Now write a script to capture database size into above table.
|
1 |
cat /home/oracle/DBA/bin/archive_usage_history.sh |
|
1 2 3 4 5 6 7 8 9 10 |
#!/bin/sh set +x export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 export ORACLE_SID=orcl export PATH=$ORACLE_HOME/bin:$PATH:. sqlplus -s / as sysdba <<EOF INSERT INTO DBMON.ARCHIVE_USAGE_HISTORY SELECT TRUNC(completion_time) Generation_Date,INST_ID,round(SUM(blocks*block_size)/1073741824,0) Total_DAY_GB FROM gv\$archived_log WHERE TRUNC(COMPLETION_TIME)=TRUNC(SYSDATE-1) GROUP BY TRUNC(completion_time),INST_ID ORDER BY TRUNC(completion_time),INST_ID; COMMIT; exit EOF |
Open Crontab
|
1 |
$ crontab -e |
|
1 |
00 01 * * * /home/oracle/DBA/bin/archive_usage_history.sh>/dev/null 2>&1 |
Daily once the above script will execute.
Thank you ….
Note: Please test scripts in Non Prod before trying in Production.
Loading...
