1 2 Previous Next 20 Replies Latest reply: Feb 12, 2014 4:02 AM by petra-K RSS

How do you purge old archivelogs in Standby DB

petra-K Expert
Currently Being Moderated

Hi all,

 

11.2.0.1

aix6

 

I have a standby db whose archivelogs keep on growing. I do not know which command will auto delete it after applying to the standby.

For the primary after an schedule backup is done, I just keep 1 backup set then purge the old ones immediately using :

run

{

crosscheck archivelog all;

crosscheck backup;

delete noprompt expired backup;

delete noprompt expired archivelog all;

delete noprompt obsolete;

 

But this does not work on standby db right? Do I need to invoke rman backup at standby so that I can auto delete expired backups?

 

Thanks,

  • 1. Re: How do you purge old archivelogs in Standby DB
    Fran Guru
    Currently Being Moderated

    you must create a script to delete old archivelog on standby. For example:

     

    run{

    allocate channel for maintenance device type disk;

    delete archivelog until time 'sysdate - 7' ;

    }

  • 2. Re: How do you purge old archivelogs in Standby DB
    Rehab-Oracle Pro
    Currently Being Moderated

    echo "Starting RMAN..."

    $ORACLE_HOME/bin/rman target / catalog user/password@rcatalog << EOF

    delete noprompt ARCHIVELOG UNTIL TIME 'SYSDATE-8';

    exit

    EOF

     

    For more information, refer to the below MOS documents:

    How to Ensure that RMAN Does NOT Delete Archived Logs That Have Not Yet Shipped to Standby [ID 394261.1]

    Rman Deletes Archive Log On Primary Database Not Applied On Physical Standby [ID 1324759.1]

    Configure RMAN to purge archivelogs after applied on standby [ID 728053.1]

     

    Hope this helps.

    Rehab

  • 3. Re: How do you purge old archivelogs in Standby DB
    petra-K Expert
    Currently Being Moderated

    Thanks all,

     

    I have tried that but it does not delete any? How do I check if it can see the list of archivelogs?

  • 4. Re: How do you purge old archivelogs in Standby DB
    CKPT Guru
    Currently Being Moderated

    Old archivelogs do you mean, They are not applied (or) they are not belongs to current resetlogs_change#?

  • 5. Re: How do you purge old archivelogs in Standby DB
    petra-K Expert
    Currently Being Moderated

    Thanks Ck1,

     

    ***Old archivelogs do you mean, They are not applied (or) they are not belongs to current resetlogs_change#?


    They are old archivelogs and has been applied to the standby db and they belong to it because they keep on growing is new date folder.


    Is there a command in RMAN for standby db  which list the old archivelogs that has been applied and can be deleted? Or is there a retention period that hold these archivelogs from being deleted?



  • 6. Re: How do you purge old archivelogs in Standby DB
    Shivananda Rao Guru
    Currently Being Moderated

    Hello,

     

    You can schedule a script that would delete the archives on the standby.

     

    cat delete.sh

     

    [oracle@ora1-2 u01]$ cat delete.sh

    ORACLE_HOME=<ORACLE_HOME path>

    export ORACLE_HOME

    PATH=$PATH:$ORACLE_HOME/bin

    export PATH

    rman target sys/<pwd>@<standbydb> <<EOF

    spool log to "/u01/delete.log";

    run

    {

    delete force noprompt archivelog all completed before 'SYSDATE-4';

    }

    EXIT;

    EOF

     

     

    This would delete all the archives before SYSDATE-4. If this does not work, then you can try deleting them by checking the sequence that was last applied and delete until that sequence using RMAN.

     

    SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

     

     

    RMAN>delete archivelog until sequence <above value>;

     

     

    Regards,

    Shivananda

  • 7. Re: How do you purge old archivelogs in Standby DB
    petra-K Expert
    Currently Being Moderated

    Thanks Shiv,

     

    Do I need to delete the old db-backupset created by standby db?

  • 8. Re: How do you purge old archivelogs in Standby DB
    Shivananda Rao Guru
    Currently Being Moderated

    f55237a7-2c38-4db3-a7a3-1d77256f0730 wrote:

     

    Thanks Shiv,

     

    Do I need to delete the old db-backupset created by standby db?

     

    I do not understand your question. Are the backups configured on standby DB or on primary DB ?

    As per your question, I feel that the backups are being taken from the standby DB and you need to remove them. If so, then check the retention policy or the redundancy window set as per your environment, and you can remove the obsolete backups.

     

     

    Regards,

    Shivananda

  • 9. Re: How do you purge old archivelogs in Standby DB
    Anar Godjaev Expert
    Currently Being Moderated

    HI,

     

    Can you please see Other OTN discussion:

     

    https://community.oracle.com/thread/2388130

     

    Thank you 

  • 10. Re: How do you purge old archivelogs in Standby DB
    petra-K Expert
    Currently Being Moderated

    Thank you all,

     

    I am new to this company, and I inherit this task to manage this standby without documentation from the previous dba. I do not know what type of dataguard they have setup.

    Are there different kinds of dataguard setup?

     

    I am just confused with my standby db. It is also creating a db backupset. Is this normal for a standby db to generate its own backupset?  I checked the crontab but there is no scheduled rman backup to run.

    I do not know also if this is the rman backup from primary thet is being file transferred to the dr server. In this case I need to remove this by my own script?

     

    At our DR server is am seeing this backupset:

    =================================

    PROD-DR:oracle[/backup/flash_recovery_area/PROD/backupset] du -s *

    17548592        2013_12_29

    20576144        2014_01_05

    15186872        2014_01_19

    14709528        2014_01_26

    15963984        2014_02_02

    14560880        2014_02_09

     

    And also this Archivelog set:

    ====================

    PROD-DR:oracle[/backup/flash_recovery_area/PRODDR/archivelog] du -s *

    2127992 2014_02_04

    3535424 2014_02_05

    2595680 2014_02_06

    2573560 2014_02_07

    2286280 2014_02_08

    2212128 2014_02_09

    3654720 2014_02_10

    3303456 2014_02_11

    1390904 2014_02_12

     

    My question is are the 2 folders generated by the local standby db? or they are being file transferred from the PRIMARY database server?

  • 11. Re: How do you purge old archivelogs in Standby DB
    CKPT Guru
    Currently Being Moderated

    PROD-DR:oracle[/backup/flash_recovery_area/PROD/backupset] du -s *

    17548592        2013_12_29

    20576144        2014_01_05

    15186872        2014_01_19

    14709528        2014_01_26

    15963984        2014_02_02

    14560880        2014_02_09

    It looks me that, weekly once backup is configured as per the backupsets created in you FRA, either you can check crontab to check jobs (or) run RMAN query to find out jobs.  Below is query.

     

    set pages 2000 lines 200

    COL STATUS FORMAT a9

    COL hrs FORMAT 999.99

    select INPUT_TYPE,

    STATUS,

    TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,

    TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,

    ELAPSED_SECONDS/3600 hrs,

    INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,

    OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,

    OUTPUT_DEVICE_TYPE

    FROM V$RMAN_BACKUP_JOB_DETAILS

    order by SESSION_KEY;

    And then the next, you have archive logs created daily basis.. That is normal. Also you need to check what is the RMAN configuration from

    RMAN> show all;

  • 12. Re: How do you purge old archivelogs in Standby DB
    petra-K Expert
    Currently Being Moderated

    Thanks Ck1,

     

    Where do I run the above scripts you mentioned? at primary or at standby

  • 13. Re: How do you purge old archivelogs in Standby DB
    CKPT Guru
    Currently Being Moderated

    I believe you have shared the directory structure information from standby database, so it should be from standby and the view is accessible from the standby database.

  • 14. Re: How do you purge old archivelogs in Standby DB
    petra-K Expert
    Currently Being Moderated

    Thanks dear

     

    SQL> set pages 2000 lines 200

    SQL> COL STATUS FORMAT a9

    SQL> COL hrs FORMAT 999.99

    select INPUT_TYPE,

    SQL>   2  STATUS,

      3  TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,

      4  TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,

      5  ELAPSED_SECONDS/3600 hrs,

      6  INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,

    OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,

    OUTPUT_DEVICE_TYPE

      7    8    9  FROM V$RMAN_BACKUP_JOB_DETAILS

    order by SESSION_KEY; 10

     

     

    no rows selected

     

     

    ==================================================

     

    localhost:oracle[/home/oracle] rman target /


    Recovery Manager: Release 11.2.0.1.0 - Production on Wed Feb 12 16:49:15 2014

    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

    connected to target database: PROD (DBID=501039509, not open)

     

    RMAN> show all;

     

    using target database control file instead of recovery catalog

    RMAN configuration parameters for database with db_unique_name PRODDR are:

    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS;

    CONFIGURE BACKUP OPTIMIZATION OFF; # default

    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

    CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

    CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

    CONFIGURE MAXSETSIZE TO UNLIMITED; # default

    CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

    CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

    CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

    CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/11.2.0/dbhome_1/dbs/snapcf_PRODDR.f'; # default

1 2 Previous Next

Legend

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