Friday, December 13, 2019

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

 The warning is clear that archive log not deleted and the below are the possible reasons. This post will help you how to manage the archive logs on standby.

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

1. The archive log destination on Standby is full
2. Standby destination not accepting any logs due to issues
3. RMAN archive log configuration is set to none/default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

First run below command to identify the archive log gap

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 185685 185669 16

If there is an archive gap then the issue is either standby archive destination is full or standby destination has some issues and not accepting any more logs, based up on the error take appropriate action. As long as you archive logs backed upon on Primary you can proceed deleting the archive logs on standby

In order to manage the archive logs you have 2 options

1. Create a shell script to check applied logs on standby and delete rest on periodic basis
2. Configure FRA for archive logs and set the RMAN archive log deletion policy as per below

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=800GBG;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/oracle/FRA';

In case if you are using ASM specify disk group
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FRA_DISK';
You need to set LOG_ARCHIVE_DEST_1 parameter DB_RECOVERY_FILE_DEST so that archived logfiles will be created at Flash recovery area.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

Set the RMAN archived log deletion policy as follows.
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

With above setting the applied archived logs will be deleted automatically when there is a space constraint in flash recovery area.

It’s different for the database where you do the backup, because you want to be sure that the backup is done before an archive log is deleted:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

Regards
Satishababu Gunukula, Oracle ACE

11 comments:

  1. This comment has been removed by the author.

    ReplyDelete

  2. Thank you very much for writing such an interesting article on this topic. This has really made me think and I hope to read more. @ Packers and Movers Ahmedabad

    ReplyDelete
  3. Welcome to Elitemodelsbangalore.com, home of the premium High Class Escorts in Bangalore area. Our Bangalore escorts are the supreme Bangalore has to offer and will provide an unforgettable Bangalore Escort experience!

    Models Escorts in Bangalore | Top Bangalore Escorts | VIP Escorts in Bangalore | Pune Escorts Models | Escorts in Pune | Elite Escort Service in Bangalore | VVIP Pune escorts | High Profile Bangalore Escorts | Bangalore High Profile Escorts | Elite Escorts in Pune | Bangalore High Class Escorts

    ReplyDelete
  4. This is the best article I have seen. This is awesome content. Thanks for providing value to your web.
    Top 10 silk saree with designer blouse

    ReplyDelete
  5. This is the best article I have seen. This is awesome content. Thanks for providing value to your web.
    Hyundai Aura car Price in India and Specifications

    ReplyDelete
  6. Nice blog has been shared by you. before i read this blog i didn't have any knowledge about this but now i got some knowledge.
    so keep on sharing such kind of an interesting blogs. Oracle Institute in Delhi

    ReplyDelete
  7. Thank you so much for sharing this awesome info! Keep posting, eCheck

    ReplyDelete