Error RMAN-06023 but there are backups available

Error RMAN-06023 but there are backups available


Demo case:
Environment : Oracle 10.2.0.1 on Linux 64bit


I have backup files of the database:

$ ls -l /oracle/backup/*
ARCH_OTP_20101117_0tlt6lum_s8221_p1
controlf_OTP_0ult6mop_8222_20101117
FULL_OTP_0rlt6ip4
spfile_OTP_0vlt6mos_8223_20101117

Restore controlfile from backup and mount database:

$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Nov 18 08:32:09 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database (not started)

RMAN> startup nomount;
Oracle instance started
Total System Global Area     838860800 bytes
Fixed Size                     2087672 bytes
Variable Size                750781704 bytes
Database Buffers              67108864 bytes
Redo Buffers                  18882560 bytes

RMAN> restore controlfile from '/back/rmaback/o1_mf_s_826770604_93vwxw3j_.bkp';

Starting restore at 18.11.2010 08:40:51

channel c1: restoring control file
channel c1: restore complete, elapsed time: 00:00:01
output filename=/oracledb/oradata/stlbas/control01.ctl
output filename=/oracledb/oradata/stlbas/control02.ctl
Finished restore at 18.11.2010 08:40:52
released channel: c1

RMAN> alter database mount;
database mounted

I will delete all EXPIRED backups from RMAN repository:


RMAN> crosscheck backup;
RMAN> delete noprompt expired backup;

RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired archivelog all;

For this case it is very important to mention that I’ve had enabled controlfile autobackup (CONFIGURE CONTROLFILE AUTOBACKUP ON) in older incarnations. With this parameter enabled RMAN automatically takes backup of controlfile and server parameter file whenever the database structure metadata in the control file changes or whenever a backup or copy operation is performed using RMAN.

List backup command shows that there are two old autobackup files in flash recovery area.


RMAN> list backup;


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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8199    Full    7.89M      DISK        00:00:00     16.06.2010 14:50:28
        BP Key: 8199   Status: AVAILABLE  Compressed: NO  Tag: TAG20100616T145028
        Piece Name: /oracle/oradata/archive/flash_recovery_area/OTP/autobackup/2010_06_16/o1_mf_s_721839028_61kl4o2y_.bkp
  Control File Included: Ckp SCN: 43471045788   Ckp time: 16.06.2010 14:50:28
  SPFILE Included: Modification time: 16.06.2010 13:15:03

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8200    Full    7.89M      DISK        00:00:00     16.06.2010 14:49:49
        BP Key: 8200   Status: AVAILABLE  Compressed: NO  Tag: TAG20100616T144949
        Piece Name: /oracle/oradata/archive/flash_recovery_area/OTP/autobackup/2010_06_16/o1_mf_s_721838989_61kl3hhq_.bkp
  Control File Included: Ckp SCN: 43471045571   Ckp time: 16.06.2010 14:49:49
  SPFILE Included: Modification time: 16.06.2010 13:15:03


To continue with my restore process I will catalog my backup files noted before.

RMAN> catalog start with '/oracle/backup/';

searching for all files that match the pattern /oracle/backup/

List of Files Unknown to the Database
=====================================
File Name: /oracle/backup/FULL_OTP_0rlt6ip4
File Name: /oracle/backup/ARCH_OTP_20101117_0tlt6lum_s8221_p1
File Name: /oracle/backup/controlf_OTP_0ult6mop_8222_20101117

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oracle/backup/FULL_OTP_0rlt6ip4
File Name: /oracle/backup/ARCH_OTP_20101117_0tlt6lum_s8221_p1
File Name: /oracle/backup/controlf_OTP_0ult6mop_8222_20101117

Check backup of database.

RMAN> list backup of database;


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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8196    Incr 0  11.18G     DISK        00:53:38     17.11.2010 01:53:58
        BP Key: 8201   Status: AVAILABLE  Compressed: YES  Tag: WEEKLY_FULL
        Piece Name: /oracle/backup/FULL_OTP_0rlt6ip4
  List of Datafiles in backup set 8196
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1    0  Incr 48852276752 17.11.2010 01:00:20 /oracledb/oradata/stlbas/system01.dbf
  2    0  Incr 48852276752 17.11.2010 01:00:20 /oracledb/oradata/stlbas/users02.dbf
  3    0  Incr 48852276752 17.11.2010 01:00:20 /oracledb/oradata/stlbas/sysaux01.dbf
  4    0  Incr 48852276752 17.11.2010 01:00:20 /oracledb/oradata/stlbas/users01.dbf
  5    0  Incr 48852276752 17.11.2010 01:00:20 /oracledb/oradata/stlbas/tools01.dbf
  6    0  Incr 48852276752 17.11.2010 01:00:20 /oracledb/oradata/stlbas/users03.dbf
  7    0  Incr 48852276752 17.11.2010 01:00:20 /oracledb/oradata/stlbas/undotbs01.dbf
  8    0  Incr 48852276752 17.11.2010 01:00:20 /oracledb/oradata/stlbas/cluser_tbs01.dbf

Restore database files from backup to another location.

RMAN> run
2> {
3> allocate channel c1 device type disk;
4> SET NEWNAME FOR DATAFILE 1 TO '/oracledb/oradata/stlbas/system01.dbf';
5> SET NEWNAME FOR DATAFILE 2 TO '/oracledb/oradata/stlbas/users02.dbf';
6> SET NEWNAME FOR DATAFILE 3 TO '/oracledb/oradata/stlbas/sysaux01.dbf';
7> SET NEWNAME FOR DATAFILE 4 TO '/oracledb/oradata/stlbas/users01.dbf';
8> SET NEWNAME FOR DATAFILE 5 TO '/oracledb/oradata/stlbas/tools01.dbf';
9> SET NEWNAME FOR DATAFILE 6 TO '/oracledb/oradata/stlbas/users03.dbf';
10> SET NEWNAME FOR DATAFILE 7 TO '/oracledb/oradata/stlbas/undotbs01.dbf';
11> SET NEWNAME FOR DATAFILE 8 TO '/oracledb/oradata/stlbas/cluser_tbs01.dbf';
12>
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=321 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 18.11.2010 08:46:21

released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/18/2010 08:46:22
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

Hm...
For me this was unexpected error because I had database backup available.


RMAN> list backup of datafile 4;


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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8196    Incr 0  11.18G     DISK        00:53:38     17.11.2010 01:53:58
        BP Key: 8201   Status: AVAILABLE  Compressed: YES  Tag: WEEKLY_FULL
        Piece Name: /oracle/backup/FULL_OTP_0rlt6ip4
  List of Datafiles in backup set 8196
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  4    0  Incr 48852276752 17.11.2010 01:00:20 /oracledb/oradata/stlbas/users01.dbf

RMAN> list backup of datafile 3;


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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8196    Incr 0  11.18G     DISK        00:53:38     17.11.2010 01:53:58
        BP Key: 8201   Status: AVAILABLE  Compressed: YES  Tag: WEEKLY_FULL
        Piece Name: /oracle/backup/FULL_OTP_0rlt6ip4
  List of Datafiles in backup set 8196
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  3    0  Incr 48852276752 17.11.2010 01:00:20 /oracledb/oradata/stlbas/sysaux01.dbf

…
…
…

After two hours of searching for answers and performing more tests I ran to the Metalink document 965122.1.

The problem were those autobackups in FRA that I showed before. That files belonged to different incarnation than the available backups current incarnation.

[From Metalink document]
If we start a RESTORE database with a BACKUP controlfile and Flash Recovery Area is defined, RMAN execute and implicit crosscheck and catalog of all the objects in the Flash Recovery Area.

RMAN will catalog any objects in the Flash Recovery Area that will not be registered in the controlfile and if any of this files belongs to an incarnation different from CURRENT incarnation in the controlfile then changes controlfile CURRENT incarnation to the one found in the file that is being cataloged.

This prevents database from restoring backups that belong to old CURRENT incarnation.
RMAN considers backup availble for being restored if the backup incarnation and CURRENT incarnation in controlfile are the same.



So I’ve decided to temporary disable FRA during recovery process commenting db_recovery_file_dest and db_recovery_file_dest_size parameters in parameter file.


RMAN> shutdown abort;

Oracle instance shut down

$ vi $ORACLE_HOME/dbs/initotp.ora
...
#*.db_recovery_file_dest='/oracle/oradata/archive/flash_recovery_area'
#*.db_recovery_file_dest_size=2147483648
…
...

$ sqlplus "/as sysdba"

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initotp.ora';
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2087672 bytes
Variable Size             750781704 bytes
Database Buffers           67108864 bytes
Redo Buffers               18882560 bytes

Restore and recover database.

RMAN> run
2> {
3> allocate channel c1 device type disk;
4> restore controlfile from '/oracle/backup/controlf_OTP_0ult6mop_8222_20101117';
5> }
…
…
…
RMAN> alter database mount;
database mounted

RMAN> catalog start with '/oracle/backup';
…
…

RMAN> run
2> {
3> allocate channel c1 device type disk;
4> SET NEWNAME FOR DATAFILE 1 TO '/oracledb/oradata/stlbas/system01.dbf';
5> SET NEWNAME FOR DATAFILE 2 TO '/oracledb/oradata/stlbas/users02.dbf';
6> SET NEWNAME FOR DATAFILE 3 TO '/oracledb/oradata/stlbas/sysaux01.dbf';
7> SET NEWNAME FOR DATAFILE 4 TO '/oracledb/oradata/stlbas/users01.dbf';
8> SET NEWNAME FOR DATAFILE 5 TO '/oracledb/oradata/stlbas/tools01.dbf';
9> SET NEWNAME FOR DATAFILE 6 TO '/oracledb/oradata/stlbas/users03.dbf';
10> SET NEWNAME FOR DATAFILE 7 TO '/oracledb/oradata/stlbas/undotbs01.dbf';
11> SET NEWNAME FOR DATAFILE 8 TO '/oracledb/oradata/stlbas/cluser_tbs01.dbf';
12>
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=321 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 18.11.2010 09:01:48

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracledb/oradata/stlbas/system01.dbf
restoring datafile 00002 to /oracledb/oradata/stlbas/users02.dbf
restoring datafile 00003 to /oracledb/oradata/stlbas/sysaux01.dbf
restoring datafile 00004 to /oracledb/oradata/stlbas/users01.dbf
restoring datafile 00005 to /oracledb/oradata/stlbas/tools01.dbf
restoring datafile 00006 to /oracledb/oradata/stlbas/users03.dbf
restoring datafile 00007 to /oracledb/oradata/stlbas/undotbs01.dbf
restoring datafile 00008 to /oracledb/oradata/stlbas/cluser_tbs01.dbf
channel c1: reading from backup piece /var/umoracle/otp/fullbkp_dir/FULL_OTP_0rlt6ip4
channel c1: restored backup piece 1
failover to piece handle=/mnt/l01dbdev-s01storage1/oracle/backup/FULL_OTP_0rlt6ip4 tag=WEEKLY_FULL
channel c1: restore complete, elapsed time: 01:28:51
Finished restore at 18.11.2010 10:30:40

datafile 1 switched to datafile copy
input datafile copy recid=9 stamp=735388241 filename=/oracledb/oradata/stlbas/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=10 stamp=735388241 filename=/oracledb/oradata/stlbas/users02.dbf
datafile 3 switched to datafile copy
input datafile copy recid=11 stamp=735388241 filename=/oracledb/oradata/stlbas/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=12 stamp=735388241 filename=/oracledb/oradata/stlbas/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=13 stamp=735388241 filename=/oracledb/oradata/stlbas/tools01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=14 stamp=735388242 filename=/oracledb/oradata/stlbas/users03.dbf
datafile 7 switched to datafile copy
input datafile copy recid=15 stamp=735388242 filename=/oracledb/oradata/stlbas/undotbs01.dbf
datafile 8 switched to datafile copy
input datafile copy recid=16 stamp=735388242 filename=/oracledb/oradata/stlbas/cluser_tbs01.dbf

Starting recover at 18.11.2010 10:30:43

starting media recovery

channel c1: starting archive log restore to default destination
channel c1: restoring archive log
archive log thread=1 sequence=33610
channel c1: reading from backup piece /var/otp/fullbkp_dir/ARCH_OTP_20101117_0tlt6lum_s8221_p1
channel c1: restored backup piece 1
failover to piece handle=/oracle/backup/ARCH_OTP_20101117_0tlt6lum_s8221_p1 tag=ARCHIVELOGS
channel c1: restore complete, elapsed time: 00:11:44
archive log filename=/oracle/oradata/archive/arch_1_33610_586538926.arc thread=1 sequence=33610
unable to find archive log
archive log thread=1 sequence=33611
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/18/2010 10:50:16
RMAN-06054: media recovery requesting unknown log: thread 1 seq 33611 lowscn 48852472202
RMAN> exit


$ sqlplus "/as sysdba"

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 48852472202 generated at 11/17/2010 01:54:16 needed for
thread 1
ORA-00289: suggestion :
/oracle/oradata/archive/arch_1_33611_586538926.arc
ORA-00280: change 48852472202 for thread 1 is in sequence #33611

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

SQL> alter database open resetlogs;
Database altered.

I'm glad that this problem occurred now in test environment because those two hours spent on searching for answers could be critical in recovery of a production database.

You never know :)


REFERENCES
Metalink - [ID 965122.1]