13 Replies Latest reply: Apr 24, 2013 3:18 AM by Mahir M. Quluzade RSS

    Deleting Archive Logs in Standby

    Thunder2777
      Hi All

      What Steps should we take to Delete Archive Logs in Standby DB.

      Can we do it Automatically by setting specific Changes in DB e.g. Alter Database ...& Time e.g. 1 month

      or we have to do process Manually.

      Regards
      Thunder2777
        • 1. Re: Deleting Archive Logs in Standby
          FreddieEssex
          Are you using RMAN?? If so then:
          configure archivelog deletion policy to applied on standby;
          If not then it will be manual using a shell script or some other method....not ideal.

          RMAN would be the preferred solution.
          • 2. Re: Deleting Archive Logs in Standby
            Thunder2777
            Thanks Freddie for prompt response

            Now I should

            1. connect to standby DB e.g. c:/> rman target /

            2. rman> configure archivelog deletion policy to applied on standby;

            That's all or something else?

            Regards
            Thunder2777
            • 3. Re: Deleting Archive Logs in Standby
              FreddieEssex
              Check http://docs.oracle.com/cd/E11882_01/server.112/e25608/rman.htm#BAJBGEIF.

              I'm using RMAN to backup my primary and I have it set the parameter on my primary as well as the standby.

              Configure your retention policy and you also have the option to delete shipped archive logs on your primary in 11g.
              • 4. Re: Deleting Archive Logs in Standby
                Thunder2777
                Hi Freddie

                I am using Oracle 10g (10.2.0.4) 64 bits on windows 2007 Server 64 bits.
                I have applied deletion policy on Both Primary DB and Physical Standby DB. Results are

                -----------------------------------Standby DB-------------------------------------------------------------------

                RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

                old RMAN configuration parameters:
                CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
                new RMAN configuration parameters:
                CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
                new RMAN configuration parameters are successfully stored

                RMAN> show all;

                RMAN configuration parameters are:
                CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
                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 ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
                CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\S
                NCFSTLDB2.ORA'; # default

                -----------------------------------Primary DB-------------------------------------------------------------------

                RMAN> SHOW ARCHIVELOG DELETION POLICY;

                RMAN configuration parameters are:
                CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

                RMAN> SHOW ALL;

                RMAN configuration parameters are:
                CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
                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 ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
                CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\S
                NCFPRMDB2.ORA'; # default

                After applying on Both Primary DB and Standby DB. When I query the Both DB

                -----------------------------------Standby DB-------------------------------------------------------------------

                SQL> select * from v$flash_recovery_area_usage;

                FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
                ------------ ------------------ ------------------------- ----------------------------------------------------------------
                CONTROLFILE 0 0 0
                ONLINELOG 0 0 0
                ARCHIVELOG 0 0 0
                BACKUPPIECE 0 0 0
                IMAGECOPY 0 0 0
                FLASHBACKLOG 0 0 0

                6 rows selected.

                -----------------------------------Primary DB-------------------------------------------------------------------

                SQL> select * from v$flash_recovery_area_usage;

                FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
                ------------ ------------------ ------------------------- --------------------------------------------------------------------
                CONTROLFILE 0 0 0
                ONLINELOG 0 0 0
                ARCHIVELOG .47 0 7
                BACKUPPIECE 0 0 0
                IMAGECOPY 0 0 0
                FLASHBACKLOG 0 0 0

                6 rows selected.

                Stnadby DB is showing Zero Percent. Is It Normal or Not?

                Regards
                Thunder2777
                • 5. Re: Deleting Archive Logs in Standby
                  Mahir M. Quluzade
                  Hi,

                  What is location of, archived logs on standby database?

                  I think you are store archived log different location (not default) on standby database.
                   SQL>show parameter log_archive_dest_1
                  Regards
                  Mahir M. Qulzuade
                  • 6. Re: Deleting Archive Logs in Standby
                    Thunder2777
                    Salam Mahir

                    As requested, Location of Log Archive Destination


                    SQL> show parameter log_archive_dest_1

                    NAME TYPE VALUE
                    ------------------------------------ ----------- ------------------------------
                    log_archive_dest_1 string location="E:\ORACLE\FRA\STLDB2
                    \ARCHIVELOG\", valid_for=(ALL_
                    LOGFILES,ALL_ROLES)
                    log_archive_dest_10 string

                    Regsrds
                    Thunder2777
                    • 7. Re: Deleting Archive Logs in Standby
                      Mahir M. Quluzade
                      Thunder2777 wrote:
                      Salam Mahir

                      As requested, Location of Log Archive Destination


                      SQL> show parameter log_archive_dest_1

                      NAME TYPE VALUE
                      ------------------------------------ ----------- ------------------------------
                      log_archive_dest_1 string location="E:\ORACLE\FRA\STLDB2
                      \ARCHIVELOG\", valid_for=(ALL_
                      LOGFILES,ALL_ROLES)
                      log_archive_dest_10 string

                      Regsrds
                      Thunder2777
                      Aleykum Salam Shahzad Iqbal,

                      AS you know, default value of log_archive_dest_1 is USE_DB_RECOVERY_FILE_DEST.
                      It means archived logs destination is DB_RECOVERY_FILE_DEST, so flash recovery area.

                      Yes, you are store ARCHIVED log in other "E:\ORACLE\FRA\STLDB2\ARCHIVELOG\ - location.
                      You are not using FLASH RECOVERY AREA.

                      It means, in v$flash_recovery_area_usage for archivelog is 0 and it is normally.


                      Thanks
                      Mahir M. Quluzade
                      • 8. Re: Deleting Archive Logs in Standby
                        Thunder2777
                        Salam Again Mahir M. Quluzade

                        1. For Standby DB, we install Oracle SW only on the 2nd Server and So No Flash Recovery Area
                        is there on Standby Server by default. We create Directory Structure as follows

                        E:\Oracle\FRA\STLDB2\ARCHIVELOG
                        E:\Oracle\FRA\STLDB2\ONLINELOG

                        E:\Oracle\ORADATA\STLDB2\*.DBF
                        E:\Oracle\ORADATA\STLDB2\controlfile\control01.ctl

                        2. We Change Parameters of pfile.ora to this New Location. Then we Create SPFILE.ora

                        So Oracle should Know the Location of New Flash Recovery Area as we have mentioned it
                        in PFILE.ORA and then in SPFILE.ORA. (STLDB2 is Stnadby DB Name)

                        *.db_recovery_file_dest='E:\oracle\fra\'
                        *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRMDB2,STLDB2)'
                        *.LOG_ARCHIVE_DEST_1='LOCATION=E:\ORACLE\FRA\STLDB2\ARCHIVELOG\
                        VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STLDB2'

                        Kind Regards
                        Thunder2777
                        • 9. Re: Deleting Archive Logs in Standby
                          Mahir M. Quluzade
                          1. For Standby DB, we install Oracle SW only on the 2nd Server and So No Flash Recovery Area
                          is there on Standby Server by default. We create Directory Structure as follows

                          E:\Oracle\FRA\STLDB2\ARCHIVELOG
                          E:\Oracle\FRA\STLDB2\ONLINELOG

                          E:\Oracle\ORADATA\STLDB2\*.DBF
                          E:\Oracle\ORADATA\STLDB2\controlfile\control01.ctl

                          2. We Change Parameters of pfile.ora to this New Location. Then we Create SPFILE.ora

                          So Oracle should Know the Location of New Flash Recovery Area as we have mentioned it
                          in PFILE.ORA and then in SPFILE.ORA. (STLDB2 is Stnadby DB Name)

                          *.db_recovery_file_dest='E:\oracle\fra\'
                          *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRMDB2,STLDB2)'
                          *.LOG_ARCHIVE_DEST_1='LOCATION=E:\ORACLE\FRA\STLDB2\ARCHIVELOG\
                          VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STLDB2'
                          As you know, LOCATION=E:\Oracle\FRA\STLDB2\ARCHIVELOG this is different from value USE_DB_RECOVERY_FILE_DEST.

                          Because it is not same, when you change DB_RECOVERY_FILE_DEST parameter then log archive destination change if you using USE_DB_RECOVERY_FILE_DEST value for log archive destination.
                          But if we are using LOCATION then not change

                          Thanks
                          Mahir M. Quluzade
                          • 10. Re: Deleting Archive Logs in Standby
                            Thunder2777
                            Salam Mahir

                            It means Should I change it to like

                            SQL> Alter database set db_recovery_file_dest='E:\Oracle\FRA\STLDB2\ARCHIVELOG\';

                            Then it will br Fine or Not?

                            Regards
                            Thunder2777
                            • 11. Re: Deleting Archive Logs in Standby
                              Mahir M. Quluzade
                              Thunder2777 wrote:
                              Salam Mahir

                              It means Should I change it to like

                              SQL> Alter database set db_recovery_file_dest='E:\Oracle\FRA\STLDB2\ARCHIVELOG\';
                              It is changing flash recovery area destination, after this script all recovery files, RMAN backups, Archivelogs etc, will store there E:\Oracle\FRA\STLDB2\ARCHIVELOG\.
                              Db recovery file destination using for all of recovery files and v$flash_recovery_are_usages view shows witch file type using Flash recovery Area.

                              I think, you are understand now.

                              Regards
                              Mahir M. Quluzade
                              • 12. Re: Deleting Archive Logs in Standby
                                Thunder2777
                                Thanks (Murhaba) Mahir M. Quluzade
                                • 13. Re: Deleting Archive Logs in Standby
                                  Mahir M. Quluzade
                                  You are welcome!

                                  p.s. If you questions is answered, please set your thread answered for clearly forum!