Thinking Out Loud

January 26, 2016

Unsolved Case for Missing archived_log Backup

Filed under: 11g,oracle,RAC,RMAN — mdinh @ 11:00 pm

The project was to migrate database from one DC to another.

The decision we made was to perform RMAN KEEP backup so it does not interfere with existing retention policy.

Backup also resides in its own separate directory for easier checksum and transfer.

This is for 4 nodes RAC environment and backup was taken from node1 at 2016-JAN-21 14:12:10

RMAN backup scripts.

run {
ALLOCATE CHANNEL C1 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 4G MAXOPENFILES 1;
ALLOCATE CHANNEL C2 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 4G MAXOPENFILES 1;
ALLOCATE CHANNEL C3 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 4G MAXOPENFILES 1;
ALLOCATE CHANNEL C4 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 4G MAXOPENFILES 1;
ALLOCATE CHANNEL C5 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 4G MAXOPENFILES 1;
SQL 'ALTER SYSTEM CHECKPOINT';

BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 DATABASE FILESPERSET 1 
KEEP UNTIL TIME 'ADD_MONTHS(SYSDATE,1)' TAG='MIGRATION_KEEP';

BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG from time 'trunc(sysdate)' FILESPERSET 2 
KEEP UNTIL TIME 'ADD_MONTHS(SYSDATE,1)' TAG='MIGRATION_KEEP';
}
run {
ALLOCATE CHANNEL C6 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/CTL_%d_%I_%T_%U_MIGRATION_%s';
BACKUP AS COMPRESSED BACKUPSET CURRENT CONTROLFILE KEEP UNTIL TIME 'ADD_MONTHS(SYSDATE,1)' TAG='MIGRATION_KEEP';
}
LIST BACKUP OF DATABASE SUMMARY TAG='MIGRATION_KEEP';
LIST BACKUP OF ARCHIVELOG ALL SUMMARY TAG='MIGRATION_KEEP';
LIST BACKUP OF CONTROLFILE TAG='MIGRATION_KEEP';
REPORT SCHEMA;

When recovering database, we encountered the error below.

ERROR from database recovery

RMAN-06025: no backup of archived log for thread 1 with sequence 287407 and starting SCN of 198452997924 found to restore

According to gv$archived_log, the sequence has not been deleted.

SQL> select inst_id, thread#, sequence#, completion_time, status, deleted
from gv$archived_log
where thread#=1 and sequence# between 287406 and 287408
order by 1,2,3
;

  2    3    4    5  
   INST_ID    THREAD#  SEQUENCE# COMPLETION_TIME      S DEL
---------- ---------- ---------- -------------------- - ---
	 1	    1	  287406 2016-JAN-21 18:51:29 A NO
	 1	    1	  287407 2016-JAN-21 18:59:45 A NO
	 1	    1	  287408 2016-JAN-21 19:00:08 A NO
	 2	    1	  287406 2016-JAN-21 18:51:29 A NO
	 2	    1	  287407 2016-JAN-21 18:59:45 A NO
	 2	    1	  287408 2016-JAN-21 19:00:08 A NO
	 3	    1	  287406 2016-JAN-21 18:51:29 A NO
	 3	    1	  287407 2016-JAN-21 18:59:45 A NO
	 3	    1	  287408 2016-JAN-21 19:00:08 A NO
	 4	    1	  287406 2016-JAN-21 18:51:29 A NO
	 4	    1	  287407 2016-JAN-21 18:59:45 A NO
	 4	    1	  287408 2016-JAN-21 19:00:08 A NO

12 rows selected.

SQL> SQL> 

Backup was started at 2016-JAN-21 14:12:10.

Noticed sequence 287407 thread 1 was missing from the MIGRATION_KEEP backup.

RMAN> list backup of archivelog sequence 287406 thread 1 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233366  B  A  A DISK        2016-JAN-21 18:59:30 1       1       YES        MIGRATION_KEEP
233374  B  A  A DISK        2016-JAN-21 19:23:41 1       1       YES        ARC021THU1923

RMAN> list backup of archivelog sequence 287407 thread 1 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233375  B  A  A DISK        2016-JAN-21 19:23:46 1       1       YES        ARC021THU1923

RMAN> list backup of archivelog sequence 287408 thread 1 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233372  B  A  A DISK        2016-JAN-21 19:00:16 1       1       YES        MIGRATION_KEEP
233377  B  A  A DISK        2016-JAN-21 19:23:47 1       1       YES        ARC021THU1923


RMAN> list backup summary tag MIGRATION_KEEP;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233092  B  0  A DISK        2016-JAN-21 14:12:10 2       1       YES        MIGRATION_KEEP
233093  B  0  A DISK        2016-JAN-21 14:12:19 2       1       YES        MIGRATION_KEEP

233306  B  0  A DISK        2016-JAN-21 18:48:31 1       1       YES        MIGRATION_KEEP
233307  B  0  A DISK        2016-JAN-21 18:48:32 1       1       YES        MIGRATION_KEEP

233308  B  F  A DISK        2016-JAN-21 18:48:37 1       1       YES        MIGRATION_KEEP
233309  B  A  A DISK        2016-JAN-21 18:50:20 1       1       YES        MIGRATION_KEEP
233310  B  A  A DISK        2016-JAN-21 18:50:47 1       1       YES        MIGRATION_KEEP
233311  B  A  A DISK        2016-JAN-21 18:50:48 1       1       YES        MIGRATION_KEEP
233312  B  A  A DISK        2016-JAN-21 18:50:54 1       1       YES        MIGRATION_KEEP
233313  B  A  A DISK        2016-JAN-21 18:50:58 1       1       YES        MIGRATION_KEEP
233314  B  F  A DISK        2016-JAN-21 18:51:12 1       1       YES        MIGRATION_KEEP
233315  B  A  A DISK        2016-JAN-21 18:52:00 1       1       YES        MIGRATION_KEEP

233366  B  A  A DISK        2016-JAN-21 18:59:30 1       1       YES        MIGRATION_KEEP
233367  B  A  A DISK        2016-JAN-21 18:59:32 1       1       YES        MIGRATION_KEEP
233368  B  A  A DISK        2016-JAN-21 18:59:32 1       1       YES        MIGRATION_KEEP
233369  B  A  A DISK        2016-JAN-21 18:59:35 1       1       YES        MIGRATION_KEEP
233370  B  F  A DISK        2016-JAN-21 18:59:54 1       1       YES        MIGRATION_KEEP
233371  B  F  A DISK        2016-JAN-21 19:00:04 1       1       YES        MIGRATION_KEEP
233372  B  A  A DISK        2016-JAN-21 19:00:16 1       1       YES        MIGRATION_KEEP
233373  B  F  A DISK        2016-JAN-21 19:00:22 1       1       YES        MIGRATION_KEEP

RMAN> list backup of controlfile summary tag MIGRATION_KEEP;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233314  B  F  A DISK        2016-JAN-21 18:51:12 1       1       YES        MIGRATION_KEEP
233370  B  F  A DISK        2016-JAN-21 18:59:54 1       1       YES        MIGRATION_KEEP
233373  B  F  A DISK        2016-JAN-21 19:00:22 1       1       YES        MIGRATION_KEEP --- This CF was restored.

RMAN> 
RMAN> restore controlfile from '/rman_bkp/FRA/migration_backup/CTL_3036635614_20160121_m6qrusa4_1_1_MIGRATION_235206';
RMAN> list backup of archivelog all summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233309  B  A  A DISK        21-JAN-2016 18:50:20 1       1       YES        MIGRATION_KEEP

233365  B  A  A DISK        21-JAN-2016 18:59:29 1       1       YES        MIGRATION_KEEP
233366  B  A  A DISK        21-JAN-2016 18:59:30 1       1       YES        MIGRATION_KEEP
233367  B  A  A DISK        21-JAN-2016 18:59:32 1       1       YES        MIGRATION_KEEP
233368  B  A  A DISK        21-JAN-2016 18:59:32 1       1       YES        MIGRATION_KEEP
233369  B  A  A DISK        21-JAN-2016 18:59:35 1       1       YES        MIGRATION_KEEP
233372  B  A  A DISK        21-JAN-2016 19:00:16 1       1       YES        MIGRATION_KEEP

RMAN> list backupset 233372;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time     
------- ---------- ----------- ------------ --------------------
233372  35.84M     DISK        00:00:04     21-JAN-2016 19:00:16
        BP Key: 359665   Status: AVAILABLE  Compressed: YES  Tag: MIGRATION_KEEP
        Piece Name: /rman_bkp/FRA/migration_backup/CTL_3036635614_20160121_m5qrus9s_1_1_MIGRATION_235205
        Keep: BACKUP_LOGS        Until: 21-FEB-2016 19:00:12

  List of Archived Logs in backup set 233372
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    287408  198453187859 21-JAN-2016 18:59:44 198453194240 21-JAN-2016 19:00:08
  2    207046  198452998035 21-JAN-2016 18:51:29 198453187879 21-JAN-2016 18:59:44
  2    207047  198453187879 21-JAN-2016 18:59:44 198453193569 21-JAN-2016 19:00:05
  3    182524  198452999167 21-JAN-2016 18:51:31 198453188295 21-JAN-2016 18:59:47
  3    182525  198453188295 21-JAN-2016 18:59:47 198453194175 21-JAN-2016 19:00:08
  4    75721   198452999243 21-JAN-2016 18:51:32 198453188286 21-JAN-2016 18:59:47
  4    75722   198453188286 21-JAN-2016 18:59:47 198453194112 21-JAN-2016 19:00:08

RMAN> 

Even from the log file sequence 287407 is missing.

channel C4: backup set complete, elapsed time: 00:00:30
channel C4: starting compressed archived log backup set
channel C4: specifying archived log(s) in backup set
input archived log thread=4 sequence=75720 RECID=709008 STAMP=901738292
input archived log thread=1 sequence=287406 RECID=709005 STAMP=901738289
channel C4: starting piece 1 at 2016-JAN-21 18:59:28
channel C5: finished piece 1 at 2016-JAN-21 18:59:28
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_lvqrus7u_1_1_MIGRATION_235199 tag=MIGRATION_KEEP comment=NONE
channel C5: backup set complete, elapsed time: 00:00:13
channel C5: starting compressed archived log backup set
channel C5: specifying archived log(s) in backup set
input archived log thread=2 sequence=207045 RECID=709006 STAMP=901738289
channel C5: starting piece 1 at 2016-JAN-21 18:59:29
channel C3: finished piece 1 at 2016-JAN-21 18:59:30
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_luqrus7p_1_1_MIGRATION_235198 tag=MIGRATION_KEEP comment=NONE
channel C3: backup set complete, elapsed time: 00:00:20
channel C4: finished piece 1 at 2016-JAN-21 18:59:32
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_m1qrus8g_1_1_MIGRATION_235201 tag=MIGRATION_KEEP comment=NONE
channel C4: backup set complete, elapsed time: 00:00:04
channel C5: finished piece 1 at 2016-JAN-21 18:59:32
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_m2qrus8g_1_1_MIGRATION_235202 tag=MIGRATION_KEEP comment=NONE
channel C5: backup set complete, elapsed time: 00:00:03
channel C1: finished piece 1 at 2016-JAN-21 18:59:36
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_ltqrus7p_1_1_MIGRATION_235197 tag=MIGRATION_KEEP comment=NONE
channel C1: backup set complete, elapsed time: 00:00:31
channel C2: finished piece 1 at 2016-JAN-21 18:59:36
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_m0qrus83_1_1_MIGRATION_235200 tag=MIGRATION_KEEP comment=NONE
channel C2: backup set complete, elapsed time: 00:00:15
Finished backup at 2016-JAN-21 18:59:36
released channel: C1
released channel: C2
released channel: C3
released channel: C4
released channel: C5
                               
allocated channel: C6
channel C6: SID=373 instance=1 device type=DISK

Starting backup at 2016-JAN-21 18:59:44
current log archived

backup will be obsolete on date 2016-FEB-21 18:59:52
archived logs required to recover from this backup will be backed up
channel C6: starting compressed full datafile backup set
channel C6: specifying datafile(s) in backup set
including current control file in backup set
channel C6: starting piece 1 at 2016-JAN-21 18:59:53
channel C6: finished piece 1 at 2016-JAN-21 19:00:04
piece handle=/oracle/FRA/migration_backup/CTL_3036635614_20160121_m3qrus98_1_1_MIGRATION_235203 tag=MIGRATION_KEEP comment=NONE
channel C6: backup set complete, elapsed time: 00:00:11

backup will be obsolete on date 2016-FEB-21 19:00:04
archived logs required to recover from this backup will be backed up
channel C6: starting compressed full datafile backup set
channel C6: specifying datafile(s) in backup set
including current SPFILE in backup set
channel C6: starting piece 1 at 2016-JAN-21 19:00:04
channel C6: finished piece 1 at 2016-JAN-21 19:00:05
piece handle=/oracle/FRA/migration_backup/CTL_3036635614_20160121_m4qrus9k_1_1_MIGRATION_235204 tag=MIGRATION_KEEP comment=NONE
channel C6: backup set complete, elapsed time: 00:00:01

backup will be obsolete on date 2016-FEB-21 19:00:04
archived logs required to recover from this backup will be backed up
channel C6: starting compressed full datafile backup set
channel C6: specifying datafile(s) in backup set
including current SPFILE in backup set
channel C6: starting piece 1 at 2016-JAN-21 19:00:04
channel C6: finished piece 1 at 2016-JAN-21 19:00:05
piece handle=/oracle/FRA/migration_backup/CTL_3036635614_20160121_m4qrus9k_1_1_MIGRATION_235204 tag=MIGRATION_KEEP comment=NONE
channel C6: backup set complete, elapsed time: 00:00:01

current log archived
backup will be obsolete on date 2016-FEB-21 19:00:12
archived logs required to recover from this backup will be backed up
channel C6: starting compressed archived log backup set
channel C6: specifying archived log(s) in backup set
input archived log thread=2 sequence=207046 RECID=709010 STAMP=901738785
input archived log thread=3 sequence=182524 RECID=709011 STAMP=901738788
input archived log thread=4 sequence=75721 RECID=709012 STAMP=901738788
input archived log thread=1 sequence=287408 RECID=709016 STAMP=901738808
input archived log thread=2 sequence=207047 RECID=709013 STAMP=901738806
input archived log thread=4 sequence=75722 RECID=709014 STAMP=901738808
input archived log thread=3 sequence=182525 RECID=709015 STAMP=901738808
channel C6: starting piece 1 at 2016-JAN-21 19:00:13
channel C6: finished piece 1 at 2016-JAN-21 19:00:20
piece handle=/oracle/FRA/migration_backup/CTL_3036635614_20160121_m5qrus9s_1_1_MIGRATION_235205 tag=MIGRATION_KEEP comment=NONE
channel C6: backup set complete, elapsed time: 00:00:07

backup will be obsolete on date 2016-FEB-21 19:00:20
archived logs required to recover from this backup will be backed up
channel C6: starting compressed full datafile backup set
channel C6: specifying datafile(s) in backup set
including current control file in backup set
channel C6: starting piece 1 at 2016-JAN-21 19:00:21
channel C6: finished piece 1 at 2016-JAN-21 19:00:31
piece handle=/oracle/FRA/migration_backup/CTL_3036635614_20160121_m6qrusa4_1_1_MIGRATION_235206 tag=MIGRATION_KEEP comment=NONE
channel C6: backup set complete, elapsed time: 00:00:10
Finished backup at 2016-JAN-21 19:00:31
released channel: C6

Any ideas as to why the archived log was missing from backup?

BTW, I have already deleted the backups to save space.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: