3 Replies Latest reply on Apr 30, 2013 2:17 AM by Hemant K Chitale

    LGWR v$archived_logs

    SherrieK
      Oracle 11.1.0.7 RAC
      3 node cluster PRIMARY
      2 node cluster STANDBY

      After a switchover from PRIMARY to STANDBY v$archived_log is used to verify that logs are applying.
      On the standby we periodically run : RMAN delete noprompt archivelog all completed before ‘sysdate’; to reduce the number of rows in v$archived_log.

      Now we’ve switched over and made the STANDBY our PRIMARY. The new STANDBY still shows a bunch of metadata exists for the archive logs that don’t exist physically (we use ASM). We are thinking these are leftover from when this was PRIMARY, but we are confused as to why.

      Thread Seq# FIRST_TIME NEXT_TIME Creator Regr Arch Applied Del Standby Dest
      ------ ---- -------------------- -------------------- ------- ---- ---- ------- --- ------------
      2 3148 18-MAR-2013 09:24:50 18-MAR-2013 09:39:51 LGWR LGWR YES YES NO YES
      1 3167 18-MAR-2013 09:39:01 18-MAR-2013 09:54:01 LGWR LGWR YES YES NO YES
      3 3129 18-MAR-2013 09:39:03 18-MAR-2013 09:54:04 LGWR LGWR YES YES NO YES
      2 3149 18-MAR-2013 09:39:51 18-MAR-2013 09:54:52 LGWR LGWR YES YES NO YES
      1 3168 18-MAR-2013 09:54:01 18-MAR-2013 10:09:02 LGWR LGWR YES YES NO YES

      Does anyone have insight into this, and how to get rid of them?
        • 1. Re: LGWR v$archived_logs
          Hemant K Chitale
          You say that you periodically run RMAN DELETE NOPROMPT ARCHIVELOG ALL on the Standby. What about the Primary ? How frequently did you run DELETEs on the Primary when it was in a Primary role ?

          Since these are dated 18-Mar and they don't exist any more, you can run CROSSCHECK ARCHIVELOG ALL to have RMAN mark them as EXPIRED and then run DELETE EXPIRED ARCHIVELOG ALL to delete t hem.


          Hemant K Chitale
          • 2. Re: LGWR v$archived_logs
            SherrieK
            I misspoke.

            PRIMARY: Every night we run a backup:
            BACKUP INCREMENTAL LEVEL=${lvl} FILESPERSET=1 DATABASE PLUS ARCHIVELOG tag = '${BKPTAG}';
            DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE';
            CROSSCHECK ARCHIVELOG ALL;

            However, we don't do a DELETE EXPIRED ARCHIVELOG ALL;

            A few times a week a cleanup job does:
            delete noprompt obsolete;
            delete noprompt expired backup;
            crosscheck backup;
            resync catalog;

            We've added to delete noprompt expired archivelog all; to the cleanup job and will see what happens.

            I tried this on a staging instance but it didn't make much difference, a few were deleted.

            I notice that the Registrar for everything the old logs are always LGWR. The new stuff is RFS.

            The archivelogs don't really exist in ASM, they were deleted a long time ago, but the metadata in v$archived_log hasn't kept up to date.


            Sherrie
            • 3. Re: LGWR v$archived_logs
              Hemant K Chitale
              So you need to run a CROSSCHECK ARCHIVELOG ALL and DELETE EXPIRED ARCHIVELOG ALL


              Hemant K Chitale