7 Replies Latest reply: Nov 22, 2011 2:25 PM by mseberg RSS

    Correct way to delete applied archived log file on standby database

    390981
      Hi all,

      We've configured Oracle Data Guard with one physical standby.
      So, the archived log files are not deleted after applied on standby,
      and the space on FRA disk group is almost in limit.

      The primary and standby databases were with the following configuration:

      CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

      So, now the configuration is:

      1. On the standby database:

      CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;


      2. On the primary database:

      CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;


      The question is: What is the correct way to delete the archived log files
      already applied of standby database?

      Thank you very much!!!!
        • 1. Re: Correct way to delete applied archived log file on standby database
          mseberg
          Hello;

          Refresh my memory on your Oracle version. Also do you do your RMAN on the PRIMARY or STANDBY database?

          In either case I use RMAN to delete it.

          Best Regards

          mseberg
          • 2. Re: Correct way to delete applied archived log file on standby database
            390981
            Hi,

            Sorry, Oracle version is Oracle 10.2.0.3 for databases and 10.2.0.5 for ASM.

            The RMAN backup is done on primary database.

            thank you.
            • 3. Re: Correct way to delete applied archived log file on standby database
              mseberg
              OK

              I have a few Oracle 10 Data Guards and I have a Linux script I run on the Standby to clean them up. Would sharing that help you?

              Assuming YES - Will proceed ASAP - Working on PDF will post Link ASAP

              Best Regards

              mseberg

              Also worth a look :

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


              Edited by: mseberg on Nov 22, 2011 11:03 AM
              • 4. Re: Correct way to delete applied archived log file on standby database
                390981
                Hi,

                Well, I'm using a command like this:

                delete archivelog <key>;

                Well, if I configure the archive deletion policy on standby database, in the future I don't need script to manually delete the archives on standby. Except any some error.
                Is this right?

                Thank you!
                • 5. Re: Correct way to delete applied archived log file on standby database
                  mseberg
                  No the policy only helps so much.

                  This should explain. ( Quick custom PDF! )

                  www.visi.com/~mseberg/remove_archive_from_oracle10_standby.pdf


                  Let me know if you have questions. Make sure you read the "How it works" section.

                  Best Regards

                  mseberg
                  Well, if I configure the archive deletion policy on standby database, in the future I don't need script to manually delete the archives on standby. Except any some error.
                  Is this right?
                  No you always need a script. You could use RMAN also if you prefer, but on Oracle 10 it barks about the Archive since it thinks it has already removed it for the Primary.

                  Edited by: mseberg on Nov 22, 2011 11:53 AM
                  • 6. Re: Correct way to delete applied archived log file on standby database
                    390981
                    Hello,

                    Sorry, I only want understand this situation.

                    Well, I'm reading the document Oracle Dataguard - B14239-05.

                    On the item 10.3.4 Deletion Policy for Archived Redo Log Files In Flash Recovery Areas, the document says that can be configured automatic deletion:

                    Using the APPLIED ON STANDBY Clause

                    Use the APPLIED ON STANDBY clause so that archived redo log files that have been
                    applied on all mandatory standby destinations will be deleted.

                    And...

                    Using the NONE Clause

                    Use the NONE clause so that archived redo logs in flash recovery area that were backed
                    up or obsolete as per the RMAN retention policy are eligible for deletion. This is the
                    default configuration. Archived redo log files that are backed up or obsolete are
                    deleted to make space if the disk space in the flash recovery area becomes full.

                    Examples of the CONFIGURE ARCHIVELOG DELETION POLICY Command

                    When backups of archived redo log files are taken on the standby database:
                    1. Issue the following command on the primary database:

                    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

                    2. Issue the following command on the standby database:

                    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

                    When backups of archived redo log files are taken on the primary database:

                    1. Issue the following command on the standby database:

                    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

                    2. Issue the following command on the primary database:

                    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;


                    There is a document on metalink about this: ID 728053.1


                    So, Is document wrong? can be my interpretation wrong too?

                    thank you very much!
                    • 7. Re: Correct way to delete applied archived log file on standby database
                      mseberg
                      Hello again;

                      You are reading it correctly. This document has more details :

                      Oracle Metalink Doc ID 464668.1

                      In theory all you should have to do is :
                      1. ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY :
                      
                      RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
                      
                      
                      2.  Enable the flash recovery area on the standby database so that all the archivelogs are shipped in the flash recovery area.
                      
                      3. Make sure these are set on Standby :
                      
                      DB_RECOVERY_FILE_DEST
                      DB_RECOVERY_FILE_DEST_SIZE
                      If this is done whenever there is a space pressure in FRA all the archivelogs that are applied on standby database should be deleted.

                      The problem is if it does not work the FRA fills up and the Primary starts throwing errors because the Standby DEST is not available. If this happens in the middle of the night a few times you might say the heck with this...


                      That said it gave me fits in Oracle 10 so I went to the script. Besides I'm paranoid ( don't tell anyone ) and like to keep a few days of archive around.


                      You have some great habits which make you a better DBA. At the end of the day you have two options.

                      Hey, come back later and post your results if you will.


                      Best Regards

                      mseberg

                      Most if not all of my issues went away when I updated to Oracle 11.

                      Edited by: mseberg on Nov 22, 2011 1:16 PM