Archivelog Deletion Policy on Oracle 12 Standby

Version 1

    Background


     

    This is not an example or a how to document. It is something I ran into in the Spring of 2016 and didn't see anybody mention it until an OTN post in the Data Guard Forum "12.0.1.2 + PSU Apr 2016 / Archivelogs on Standby not deleted". I would to thank "SPA09" for posting the question and reporting back his test results. There are unanswered questions. Please feel free to post a comment or any addition information you have.


     

    Information on the system where I first noticed the issue:


     

    OS Oracle Linux 6 - Linux x86-64

    Oracle Version 12.1.0.2 Enterprise Edition

    Database Type Single Instance


     


     

    Reclaimable space issue on a Standby database


     

    In May of 2016 I noticed the Archive was not being deleted from one of the Standby databases. This was Oracle 12.1.0.2 with the April 2016 patches 22899531 and 22291127 recently applied. Since the database had been upgraded to 12 not long ago I decided double check all the settings in case I had missed something. The archive deletion policy was set to " APPLIED ON ALL STANDBY" on both the Primary and Standby sides. To confirm this was still valid in Oracle 12 I checked "Database Backup and Recovery User's Guide 12c Release 1 (12.1) E50658-07" which referred me to "Data Guard Concepts and Administration 12c Release 1 (12.1) E48552-06" In sections 12.3.2 and 12.3.4 the setting " CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY" is confirmed since backup is occurring on the Primary side only.


     

    So the setup appears correct so I decided to check v$flash_recovery_area_usage to see if it offered a clue.


     

    SQL> Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable, number_of_files as "number" from v$flash_recovery_area_usage; 


     

    FILE_TYPE USED RECLAIMABLE number

    ----------------------- ---------- ----------- ----------

    CONTROL FILE 0 0 0

    REDO LOG .14 0 3

    ARCHIVED LOG 13.08 0 369

    BACKUP PIECE 0 0 0

    IMAGE COPY 0 0 0

    FLASHBACK LOG 2.15 .23 48

    FOREIGN ARCHIVED LOG 0 0 0

    AUXILIARY DATAFILE COPY 0 0 0


     

    8 rows selected.


     


     

    Notice the reclaimable for archive is 0 so unless I do something the Standby will continue to grow the percent Archive is using until it reaches100 percent, and then I have a bigger issue. At this point I figured I found a bug so I searched Oracle support for any information. It worth noting that this search was repeated on October 26th, 2016 but in both cases I was unable to find anything. At this point I decided to change my Standby setting and run the query again.


     

    RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;


     

    old RMAN configuration parameters:

    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

    new RMAN configuration parameters:

    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

    new RMAN configuration parameters are successfully stored


     

    SQL> Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable, number_of_files as "number" from v$flash_recovery_area_usage; 


     

    FILE_TYPE USED RECLAIMABLE number

    ----------------------- ---------- ----------- ----------

    CONTROL FILE 0 0 0

    REDO LOG .14 0 3

    ARCHIVED LOG 13.08 13.04 369

    BACKUP PIECE 0 0 0

    IMAGE COPY 0 0 0

    FLASHBACK LOG 2.15 .23 48

    FOREIGN ARCHIVED LOG 0 0 0

    AUXILIARY DATAFILE COPY 0 0 0


     

    8 rows selected.


     

    Notice now the reclaimable for Archive is no longer 0. But at 13 percent I don't think Oracle feels any pressure so I checked db_recovery_file_dest_size and then reduced it.


     


     

    SQL> show parameter db_rec


     

    NAME TYPE VALUE

    ------------------------------------ ----------- ------------------------------

    db_recovery_file_dest string /u01/app/oracle/flash_recovery

    _area

    db_recovery_file_dest_size big integer 211G

    db_recycle_cache_size big integer 0


     


     

    SQL>alter system set db_recovery_file_dest_size = 15G scope=both;


     

    System altered.

     


     

    SQL>  Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,number_of_files as "number" from v$flash_recovery_area_usage; 


     

    FILE_TYPE USED RECLAIMABLE number

    ----------------------- ---------- ----------- ----------

    CONTROL FILE 0 0 0

    REDO LOG 1.95 0 3

    ARCHIVED LOG 70.83 70.33 144

    BACKUP PIECE 0 0 0

    IMAGE COPY 0 0 0

    FLASHBACK LOG 27.02 0 43

    FOREIGN ARCHIVED LOG 0 0 0

    AUXILIARY DATAFILE COPY 0 0 0


     

    8 rows selected.


     

    The Archive count was dropping so the size was reduced again.


     

    SQL>  alter system set db_recovery_file_dest_size = 10G scope=both;


     

    System altered.


     

    SQL> Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable, number_of_files as "number" from v$flash_recovery_area_usage;  


     

    FILE_TYPE USED RECLAIMABLE number

    ----------------------- ---------- ----------- ----------

    CONTROL FILE 0 0 0

    REDO LOG 2.93 0 3

    ARCHIVED LOG 56.51 55.77 78

    BACKUP PIECE 0 0 0

    IMAGE COPY 0 0 0

    FLASHBACK LOG 40.53 0 43

    FOREIGN ARCHIVED LOG 0 0 0

    AUXILIARY DATAFILE COPY 0 0 0


     

    8 rows selected.


     

    At this point I decided to check the alert log:


     


     

    ALTER SYSTEM SET db_recovery_file_dest_size=15G SCOPE=BOTH;

    Mon May 02 14:00:21 2016

    Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/SBAPP/archivelog/2016_04_19/o1_mf_1_45716_ckcgv5tz_.arc

    Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/SBAPP/archivelog/2016_04_19/o1_mf_1_45717_ckcmbc05_.arc

    Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/SBAPP/archivelog/2016_04_19/o1_mf_1_45718_ckd0jp4s_.arc

    Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/SBAPP/archivelog/2016_04_19/o1_mf_1_45719_ckd7f368_.arc

    Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/SBAPP/archivelog/2016_04_19/o1_mf_1_45720_ckdq83nf_.arc

    Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/SBAPP/archivelog/2016_04_19/o1_mf_1_45721_ckdskppk_.arc

    Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/SBAPP/archivelog/2016_04_19/o1_mf_1_45722_ckdxrzp7_.arc

    Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/SBAPP/archivelog/2016_04_19/o1_mf_1_45723_ckf6ofqy_.arc

    Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/SBAPP/archivelog/2016_04_19/o1_mf_1_45724_ckfm420g_.arc

    Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/SBAPP/archivelog/2016_04_19/o1_mf_1_45725_ckfo559c_.arc

    Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/SBAPP/archivelog/2016_04_19/o1_mf_1_45726_ckfogsj8_.arc

    Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/SBAPP/archivelog/2016_04_19/o1_mf_1_45727_ckfokslm_.arc


     


     

    It confirms what I already know, Oracle is deleting archive from the FRA again.


     


     

    Newly Upgraded Standby


     

    On October 27th, 2016 I upgraded an Oracle 11.2.0.4 database to Oracle 12.1.0.2. When I check the standby RMAN is set like this:


     

    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;


     

    So what does v$flash_recovery_area_usage say now?


     


     

    SQL> Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable, number_of_files as "number" from v$flash_recovery_area_usage;

     

    FILE_TYPE USED RECLAIMABLE number

    ----------------------- ---------- ----------- ----------

    CONTROL FILE 0 0 0

    REDO LOG 1.13 0 6

    ARCHIVED LOG 62.69 45.64 556

    BACKUP PIECE .2 0 3

    IMAGE COPY 0 0 0

    FLASHBACK LOG 15.78 0 84

    FOREIGN ARCHIVED LOG 0 0 0

    AUXILIARY DATAFILE COPY 0 0 0


     

    8 rows selected.


     

    Not exactly what I was expecting. I figured "Reclaimable" to be all in or all out. There are still unanswered questions in my mind so I going to reduce the FRA again.


     

    SQL> show parameter db_rec


     

    NAME TYPE VALUE

    ------------------------------------ ----------- ------------------------------

    db_recovery_file_dest string /u01/app/oracle/flash_recovery

    _area

    db_recovery_file_dest_size big integer 26G

    db_recycle_cache_size big integer 0


     

    SQL> alter system set db_recovery_file_dest_size = 15G scope=both;


     

    System altered.


     

    SQL> Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable, number_of_files as "number" from v$flash_recovery_area_usage;

     

    FILE_TYPE USED RECLAIMABLE number

    ----------------------- ---------- ----------- ----------

    CONTROL FILE 0 0 0

    REDO LOG 1.95 0 6

    ARCHIVED LOG 70.32 40.76 339

    BACKUP PIECE .34 0 3

    IMAGE COPY 0 0 0

    FLASHBACK LOG 27.34 0 84

    FOREIGN ARCHIVED LOG 0 0 0

    AUXILIARY DATAFILE COPY 0 0 0


     

    8 rows selected.


     


     

    It appears to be working. I still don't know why this happens or does not happen. Another reduction.


     

    SQL> alter system set db_recovery_file_dest_size = 8G scope=both;


     

    System altered.


     

    SQL> Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable, number_of_files as "number" from v$flash_recovery_area_usage; 


     

    FILE_TYPE USED RECLAIMABLE number

    ----------------------- ---------- ----------- ----------

    CONTROL FILE 0 0 0

    REDO LOG 3.66 0 6

    ARCHIVED LOG 55.43 0 109

    BACKUP PIECE .64 0 3

    IMAGE COPY 0 0 0

    FLASHBACK LOG 39.67 0 65

    FOREIGN ARCHIVED LOG 0 0 0

    AUXILIARY DATAFILE COPY 0 0 0


     

    8 rows selected.


     


     

    OK reclaimable is back to 0. So if I switch the RMAN setting does reclaimable change again?


     


     

    RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;


     

    old RMAN configuration parameters:

    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

    new RMAN configuration parameters:

    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

    new RMAN configuration parameters are successfully stored


     

    SQL> Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable, number_of_files as "number" from v$flash_recovery_area_usage; 


     

    FILE_TYPE USED RECLAIMABLE number

    ----------------------- ---------- ----------- ----------

    CONTROL FILE 0 0 0

    REDO LOG 3.66 0 6

    ARCHIVED LOG 55.43 55.03 109

    BACKUP PIECE .64 .21 3

    IMAGE COPY 0 0 0

    FLASHBACK LOG 39.67 0 65

    FOREIGN ARCHIVED LOG 0 0 0

    AUXILIARY DATAFILE COPY 0 0 0


     

    8 rows selected.


     

    The short answer is yes. I hate to guess but did Oracle just remove Oracle 11 archive and when it got to Oracle 12 stop?


     

    It seems like a bug has been found. I don’t think the Primary and Standby sides should have to have different settings for archivelog deletion policy. But several other people have reported the same results on OTN and I’ve tested it on 3 different systems.