1.PURPOSE AND SCOPE
Solving the Oracle 11.2.0.3 version auto archivelog deletion problem
2.PRACTICE
2.1. At Oracle 11.2.0.3 version there is a bug related with deleting archivelogs. Normally when FRA is need a space ,it need to check archivelogs which can be deleted but because of the bug it doesn’t control which archivelogs can it be deleted ,you need to execute a dbms package to refresh the view.
PUBLIC@CROORAFLP01:APMTMART_SBY: >SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE 0 0 0 REDO LOG 27 0 9 ARCHIVED LOG 68.89 0 35 BACKUP PIECE .09 0 1 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 7 rows selected.
2.2. This query result taken by standby database, while this query executed standby was waiting new log archivelog so we can say all these archivelogs can be deleted, if database worked properly on PERCENT_SPACE_RECLAIMABLE column we were going 68.89 at ARCHIVED LOG row. We need to execute a package which called dbms_backup_restore.refreshagedfiles with help of this package view will be refreshed.
PUBLIC@CROORAFLP01:APMTMART_SBY: >exec dbms_backup_restore.refreshagedfiles ; PL/SQL procedure successfully completed.
2.3. Now lets execute query again.
PUBLIC@CROORAFLP01:APMTMART_SBY: >SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE 0 0 0 REDO LOG 27 0 9 ARCHIVED LOG 68.92 68.92 40 BACKUP PIECE .09 0 1 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 7 rows selected.
2.4. Before refreshing view.I had got space problem on FRA , database start to delete unneeded archivelogs.
Deleted Oracle managed file /oradata/APMTMART/fast_recovery_area/APMTMART_SBY/archivelog/2013_04_23/o1_mf_1_744_8qft2zkl_.arc RFS[2]: Selected log 11 for thread 1 sequence 785 dbid -1961087772 branch 799952871 Archived Log entry 761 added for thread 1 sequence 784 ID 0x8b1d6fb5 dest 1: Deleted Oracle managed file /oradata/APMTMART/fast_recovery_area/APMTMART_SBY/archivelog/2013_04_24/o1_mf_1_745_8qgffjbw_.arc
2.5. For solving this problem we need to schedule a database job or crontab job which execute dbms_backup_restore.refreshagedfiles
package.
Leave a Reply