This discussion is archived
8 Replies Latest reply: Aug 14, 2013 6:15 AM by JavierFranciscoRuiz RSS

How to automate the purging of archivelogs on primary when backing up from physical standby?

user10657742 Newbie
Currently Being Moderated


Hi,

 

When configuring archivelog backups to run from my Active Standby database (11.2.0.3) I am running into an issue where the archivelogs on the primary database are not getting purged. This is resulting in the archiver hanging on the primary.

 

run {

allocate channel ch1 type 'sbt_tape'

TRACE 0;

setlimit channel ch1 maxopenfiles 8;

backup

filesperset = 32

(archivelog  all   delete input );

backup  current controlfile  spfile  ;

}

 

My archivelog deletion policy is set as follows on both the primary and standby

 

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

 

When I run REPORT OBSOLETE from RMAN however, the primary database seems to have outdated information in it.

 

I have also observed the following in the RMAN backup log:

 

RMAN-08120: WARNING: archived log not deleted, not yet applied by standby.

 

Do I need to perform additional configuration to automate the purging of archivelogs on the primary database or is there an issue with the backup options and / or deletion policy specified?

 

Many thanks,

Gary Allen

DBA

  • 1. Re: How to automate the purging of archivelogs on primary when backing up from physical standby?
    saurabh Pro
    Currently Being Moderated

    can you post the output of following cmd

     

    1. show parameter log_archive_dest

    2. show parameter recovery

  • 2. Re: How to automate the purging of archivelogs on primary when backing up from physical standby?
    JavierFranciscoRuiz Explorer
    Currently Being Moderated

    You need to check on the primary if the archive log shipped to standby are showing applied on standby. On the primary run select on v$archived_log and check if appiled is yes for the standby dest archivelog. If they do not say yes but you know they are applied then this is why rman on the primary is not deleting the archivelogs on the primary.

  • 4. Re: How to automate the purging of archivelogs on primary when backing up from physical standby?
    nagulan.selvakumar Journeyer
    Currently Being Moderated

    Hi,

     

    This is caused due to some lag in apply of archived logs to the standby databases. If a log is still required for apply, then that log will not be deleted and RMAN throws warning message.

    If you don't want this to happen, you can configure your deletion policy to set to delete logs once the logs are shipped and not to worry whether if it is applied or not.

     

    Syntax is as below.

    CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;

     

    Thank you!!

  • 5. Re: How to automate the purging of archivelogs on primary when backing up from physical standby?
    user10657742 Newbie
    Currently Being Moderated

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_1                   string      location=USE_DB_RECOVERY_FILE_
                                                     DEST VALID_FOR=(ALL_LOGFILES,A
                                                     LL_ROLES) db_unique_name=OV_IN
                                                     TRA

    log_archive_dest_2                   string      SERVICE=DG_INTRA LGWR ASYNC VA
                                                     LID_FOR=(ONLINE_LOGFILES,PRIMA
                                                     RY_ROLE) DB_UNIQUE_NAME=DG_INT
                                                     RA

     

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_recovery_file_dest                string      +ORAFRA
    db_recovery_file_dest_size           big integer 3000M
    recovery_parallelism 

     

     

     

  • 6. Re: How to automate the purging of archivelogs on primary when backing up from physical standby?
    user10657742 Newbie
    Currently Being Moderated

    The primary and standby databases are in sync and have been for over a week now.

     

    Primary:

    SQL> select max(sequence#), applied from v$archived_log group by applied;

    MAX(SEQUENCE#) APPLIED
    -------------- ---------
               268 NO
               267 YES

     

    Standby:

    SQL> select max(sequence#) from v$archived_log;

    MAX(SEQUENCE#)
    --------------
               268

     

    For some reason though, the primary controlfile is not being updated properly and when I run the RMAN REPORT OBSOLETE command the data is nearly a week old yet on the standby the same command retrieves up to date information:

     

    Primary:

     

    RMAN> report obsolete

    ...

    ...

    Backup Set           136    06-AUG-13

      Backup Piece       136    06-AUG-13          48ogk9ik_1_1

    Backup Set           138    06-AUG-13

      Backup Piece       138    06-AUG-13          4aogkqbm_1_1

    Backup Set           142    06-AUG-13

      Backup Piece       142    06-AUG-13          4eogl5i6_1_1

    Backup Set           144    07-AUG-13

      Backup Piece       144    07-AUG-13          4gogmc0a_1_1

     

    RMAN> report obsolete

    ...

    ...

     

    Standby:

    Backup Set           156    13-AUG-13

      Backup Piece       156    13-AUG-13          4uoh65o7_1_1

    Backup Set           159    13-AUG-13

      Backup Piece       159    13-AUG-13          51oh6d0r_1_1

    Backup Set           162    13-AUG-13

      Backup Piece       162    13-AUG-13          54oh6n8a_1_1

    Backup Set           165    13-AUG-13

      Backup Piece       165    13-AUG-13          57oh78qr_1_1

  • 7. Re: How to automate the purging of archivelogs on primary when backing up from physical standby?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    The REPORT OBSOLETE relies on the retention period and the Full /  L0 backups to determine obsolescence.

     

    Do a SHOW ALL at the RMAN command prompt to view the retention period.

     

     

    Hemant K Chitale

  • 8. Re: How to automate the purging of archivelogs on primary when backing up from physical standby?
    JavierFranciscoRuiz Explorer
    Currently Being Moderated

    CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;Run the same select but now remove the max function and order by desc on the sequence number and you will find that the archivelogs that are not being deleted on the primary are because the applied still shows no but as you stated and show the archivelov is applied.

     

    You can try two things one as stated in the other reply set the below rman configuration this will delete once the log has been shipped

    CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;

     

    The other thing you can do if this is linux or unix set the arch process parameter parameter to 1 on the primary then go out to the OS and delete the last arc process running for the primary. Do not worry this will not crash the database it will just respawn the process then go to the primary and reset back the arc porcess parameter.

    Below are steps I followed for one of my systems that was rac if your system is not rac you would do the same but only on one server.

     

    -- check if archivelog is currently busy

    select * from gv$archive_processes where state = 'BUSY';

     

    select * from gv$parameter where name like '%log_archive_max_processes%';

     

    -- do an

    alter system set log_archive_max_processes=1;

     

    --then logon to each server and do ps -ef |grep arc

    -- there should be just one arc process running

    -- do a kill -9 on the last arc process

     

    kill -9 ora_arc0_rembtsp

     

    -- now you can delete the archivelog files via rman

     

    -- reset the parameter back to what it was set to

    alter system set log_archive_max_processes=2;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points