1 2 Previous Next 20 Replies Latest reply: Feb 12, 2014 5:56 AM by CKPT RSS

    How do you purge old archivelogs in Standby DB

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          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

                            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

                              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

                                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

                                  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

                                    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