9 Replies Latest reply: Dec 13, 2011 9:04 AM by CKPT RSS

    standby, RMAN-8137 and Oracle 10.2.0.4

    Laurent Schneider
      Last week I experienced a serious network issue, my standby databases experienced lags.

      Now as everything should work fine, I am still enable to use DELETE INPUT to delete archivelogs
      RMAN> backup as compressed backupset archivelog all delete input;
      RMAN-08137: WARNING: archive log not deleted as it is still needed
      archive log filename=/u01/app/oracle/admin/DB01/arch/DB01_30288_1_663410211.arc
        thread=1 sequence=30288
      I have no lag on standby.
      SQL> select process, thread#, sequence#, status from v$managed_standby ;
      
      PROCESS      THREAD#  SEQUENCE# STATUS
      --------- ---------- ---------- ------------
      ARCH               1      30322 CLOSING
      ARCH               1      30323 CLOSING
      RFS                1      30324 IDLE
      MRP0               1      30324 APPLYING_LOG
      RFS                0          0 IDLE
      It seem out of my dozens of databases, only 2 are affected, both 10.2.0.4

      I will migrate to 11g in a few months and I could well try to reinstatiate the standby. But I am interested in another solution, if possible.

      As a current workaround I do use
      RMAN> DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DISK ;
      Thanks
      Laurent
        • 1. Re: standby, RMAN-8137 and Oracle 10.2.0.4
          CKPT
          RMAN> backup as compressed backupset archivelog all delete input;
          RMAN-08137: WARNING: archive log not deleted as it is still needed
          archive log filename=/u01/app/oracle/admin/DB01/arch/DB01_30288_1_663410211.arc
          thread=1 sequence=30288
          What about RMAN configuration of archive backups?

          RMAN> show all;
          • 2. Re: standby, RMAN-8137 and Oracle 10.2.0.4
            Laurent Schneider
            only default values
            RMAN> show all;
            
            using target database control file instead of recovery catalog
            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 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
            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 NONE; # default
            CONFIGURE SNAPSHOT CONTROLFILE NAME TO 
              '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_DB01.f'; # default
            The DELETE INPUT did work for years and nothing changed on the database, apart that I had to recover the standby after the network exception
            • 3. Re: standby, RMAN-8137 and Oracle 10.2.0.4
              CKPT
              I saw one metalink note,
              With similar errors in dataguard configuration. It says to crosscheck and delete expired archives. refer

              *Rman-08137: Warning: Archive Log Not Deleted As It Is Still Needed Rman-08137 [ID 374421.1]*
              • 4. Re: standby, RMAN-8137 and Oracle 10.2.0.4
                Laurent Schneider
                I have searched on metalink too, but did not find anything applicable

                If you query v$archived_log on the primary , you will find at least one archivelog with APPLIED=NO:
                does not apply :
                SQL> select dest_id,standby_dest, applied, count(*) 
                  from v$archived_log 
                  group by dest_id,standby_dest, applied 
                  order by DEST_ID;
                
                   DEST_ID STA APP   COUNT(*)
                ---------- --- --- ----------
                         1 NO  NO        9669
                         2 YES YES       9627
                all are applied
                solution delete expired archivelog all does not help
                RMAN> crosscheck archivelog all;
                
                using target database control file instead of recovery catalog
                allocated channel: ORA_DISK_1
                channel ORA_DISK_1: sid=110 devtype=DISK
                allocated channel: ORA_DISK_2
                channel ORA_DISK_2: sid=114 devtype=DISK
                allocated channel: ORA_DISK_3
                channel ORA_DISK_3: sid=130 devtype=DISK
                allocated channel: ORA_DISK_4
                channel ORA_DISK_4: sid=120 devtype=DISK
                validation succeeded for archived log
                archive log filename=/u01/app/oracle/admin/DB01/arch/DB01_30324_1_663410211.arc 
                  recid=60690 stamp=769778362
                validation succeeded for archived log
                archive log filename=/u01/app/oracle/admin/DB01/arch/DB01_30325_1_663410211.arc 
                  recid=60692 stamp=769780164
                validation succeeded for archived log
                archive log filename=/u01/app/oracle/admin/DB01/arch/DB01_30326_1_663410211.arc 
                  recid=60694 stamp=769781963
                Crosschecked 3 objects
                
                
                RMAN> delete expired archivelog all;
                
                released channel: ORA_DISK_1
                released channel: ORA_DISK_2
                released channel: ORA_DISK_3
                released channel: ORA_DISK_4
                allocated channel: ORA_DISK_1
                channel ORA_DISK_1: sid=110 devtype=DISK
                allocated channel: ORA_DISK_2
                channel ORA_DISK_2: sid=114 devtype=DISK
                allocated channel: ORA_DISK_3
                channel ORA_DISK_3: sid=130 devtype=DISK
                allocated channel: ORA_DISK_4
                channel ORA_DISK_4: sid=120 devtype=DISK
                specification does not match any archive log in the recovery catalog
                
                RMAN> backup as compressed backupset archivelog all delete input 
                  format '/u99/backup/DB01/arch/201112131236/arc_%d_s%s_p%p';
                
                Starting backup at 2011-12-13_12:37:04
                current log archived
                using channel ORA_DISK_1
                using channel ORA_DISK_2
                using channel ORA_DISK_3
                using channel ORA_DISK_4
                channel ORA_DISK_1: starting compressed archive log backupset
                channel ORA_DISK_1: specifying archive log(s) in backup set
                input archive log thread=1 sequence=30324 recid=60690 stamp=769778362
                channel ORA_DISK_1: starting piece 1 at 2011-12-13_12:37:07
                channel ORA_DISK_2: starting compressed archive log backupset
                channel ORA_DISK_2: specifying archive log(s) in backup set
                input archive log thread=1 sequence=30326 recid=60694 stamp=769781963
                channel ORA_DISK_2: starting piece 1 at 2011-12-13_12:37:07
                channel ORA_DISK_3: starting compressed archive log backupset
                channel ORA_DISK_3: specifying archive log(s) in backup set
                input archive log thread=1 sequence=30325 recid=60692 stamp=769780164
                channel ORA_DISK_3: starting piece 1 at 2011-12-13_12:37:08
                channel ORA_DISK_4: starting compressed archive log backupset
                channel ORA_DISK_4: specifying archive log(s) in backup set
                input archive log thread=1 sequence=30327 recid=60696 stamp=769783025
                channel ORA_DISK_4: starting piece 1 at 2011-12-13_12:37:08
                channel ORA_DISK_1: finished piece 1 at 2011-12-13_12:37:09
                piece handle=/u99/backup/DB01/arch/201112131236/arc_DB01_s5145_p1 
                  tag=TAG20111213T123705 comment=NONE
                channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
                channel ORA_DISK_1: deleting archive log(s)
                archive log filename=/u01/app/oracle/admin/DB01/arch/DB01_30324_1_663410211.arc 
                  recid=60690 stamp=769778362
                channel ORA_DISK_2: finished piece 1 at 2011-12-13_12:37:09
                piece handle=/u99/backup/DB01/arch/201112131236/arc_DB01_s5146_p1 
                  tag=TAG20111213T123705 comment=NONE
                channel ORA_DISK_2: backup set complete, elapsed time: 00:00:03
                channel ORA_DISK_2: deleting archive log(s)
                archive log filename=/u01/app/oracle/admin/DB01/arch/DB01_30326_1_663410211.arc 
                  recid=60694 stamp=769781963
                channel ORA_DISK_3: finished piece 1 at 2011-12-13_12:37:09
                piece handle=/u99/backup/DB01/arch/201112131236/arc_DB01_s5147_p1 
                  tag=TAG20111213T123705 comment=NONE
                channel ORA_DISK_3: backup set complete, elapsed time: 00:00:03
                channel ORA_DISK_3: deleting archive log(s)
                archive log filename=/u01/app/oracle/admin/DB01/arch/DB01_30325_1_663410211.arc 
                  recid=60692 stamp=769780164
                channel ORA_DISK_4: finished piece 1 at 2011-12-13_12:37:09
                piece handle=/u99/backup/DB01/arch/201112131236/arc_DB01_s5148_p1 
                  tag=TAG20111213T123705 comment=NONE
                channel ORA_DISK_4: backup set complete, elapsed time: 00:00:03
                RMAN-08137: WARNING: archive log not deleted as it is still needed
                archive log filename=/u01/app/oracle/admin/DB01/arch/DB01_30327_1_663410211.arc 
                  thread=1 sequence=30327
                Finished backup at 2011-12-13_12:37:09
                
                RMAN> crosscheck archivelog all;
                
                released channel: ORA_DISK_1
                released channel: ORA_DISK_2
                released channel: ORA_DISK_3
                released channel: ORA_DISK_4
                allocated channel: ORA_DISK_1
                channel ORA_DISK_1: sid=110 devtype=DISK
                allocated channel: ORA_DISK_2
                channel ORA_DISK_2: sid=114 devtype=DISK
                allocated channel: ORA_DISK_3
                channel ORA_DISK_3: sid=130 devtype=DISK
                allocated channel: ORA_DISK_4
                channel ORA_DISK_4: sid=120 devtype=DISK
                validation succeeded for archived log
                archive log filename=/u01/app/oracle/admin/DB01/arch/DB01_30327_1_663410211.arc 
                  recid=60696 stamp=769783025
                Crosschecked 1 objects
                
                
                RMAN> delete expired archivelog all;
                
                released channel: ORA_DISK_1
                released channel: ORA_DISK_2
                released channel: ORA_DISK_3
                released channel: ORA_DISK_4
                allocated channel: ORA_DISK_1
                channel ORA_DISK_1: sid=110 devtype=DISK
                allocated channel: ORA_DISK_2
                channel ORA_DISK_2: sid=114 devtype=DISK
                allocated channel: ORA_DISK_3
                channel ORA_DISK_3: sid=130 devtype=DISK
                allocated channel: ORA_DISK_4
                channel ORA_DISK_4: sid=120 devtype=DISK
                specification does not match any archive log in the recovery catalog
                • 5. Re: standby, RMAN-8137 and Oracle 10.2.0.4
                  CKPT
                  Can you please post one more query output from primary?
                  SQL> select to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') from v$archived_log where thread#=1 and sequence#=30325;
                  • 6. Re: standby, RMAN-8137 and Oracle 10.2.0.4
                    Laurent Schneider
                    Primary :
                    SQL> select dest_id,standby_dest,applied,to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') 
                      from v$archived_log 
                      where thread#=1 and sequence#=30325;
                    
                       DEST_ID STA APP TO_CHAR(COMPLETION_T
                    ---------- --- --- --------------------
                             1 NO  NO  13-DEC-2011 11:49:24
                             2 YES YES 13-DEC-2011 11:49:24
                    Standby :
                    SQL> select dest_id,standby_dest,applied,to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') 
                      from v$archived_log 
                      where thread#=1 and sequence#=30325;
                    
                       DEST_ID STA APP TO_CHAR(COMPLETION_T
                    ---------- --- --- --------------------
                             1 NO  YES 13-DEC-2011 11:49:24
                    • 7. Re: standby, RMAN-8137 and Oracle 10.2.0.4
                      Laurent Schneider
                      wait, I feel deeply idiot now, does it work now?

                      it seems only the latest one did not get deleted... which is fine

                      I need to figure out what I did !!!
                      • 8. Re: standby, RMAN-8137 and Oracle 10.2.0.4
                        Laurent Schneider
                        Ok, I tried on my second database and this is what I did

                        on both standby and primary rman
                        crosscheck archivelog all;
                        delete expired archivelog all;
                        in dataguard
                        disable database standby02;
                        enable database standby02;
                        on primary sqlplus
                        alter system set log_archive_dest_state_2=defer scope=memory;
                        alter system set log_archive_dest_state_2=enable scope=memory;
                        on standby sqlplus
                        shutdown immediate
                        startup mount
                        on primary db server
                        ps -ef | arc.*DB02
                        kill -9 <pid of ora_arc0_DBO2>  <pid of ora_arc1_DBO2>
                        the problem vanished by itself. Now the delete input works.

                        On the first db, I did try those things before posting, but probably not in the right order, or I was unlucky.

                        Thanks CKPT for assisting me so far, it proofed very helpful :-)
                        • 9. Re: standby, RMAN-8137 and Oracle 10.2.0.4
                          CKPT
                          On the first db, I did try those things before posting, but probably not in the right order, or I was unlucky.

                          Thanks CKPT for assisting me so far, it proofed very helpful :-)
                          Glad, if my posts are really helpful :)