when customer have all backup pieces of database except controlfile backup which is mandatory to mount the database and start restore.
Missing controlfile backup
1. See the current database structure
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema
List of Datafiles
==================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 510 SYSTEM *** +DATA/oradb/datafile/system.257.775126603
2 595 UNDOTBS1 *** +DATA/oradb/datafile/undotbs1.256.775126561
3 250 SYSAUX *** +DATA/oradb/datafile/sysaux.258.775126637
4 28 USERS *** +DATA/oradb/datafile/users.259.775126653
5 50 USERS *** +DATA/oradb/datafile/users.262.776000421
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /u01/app/oracle/oradata/oradb/oradb/datafile/o1_mf_temp_7lqq1qko_.tmp
RMAN> exit
Recovery Manager complete.
2. Create a dummy instance/ can use existing database to extract datafile 1 from backup piece and restore datafile 1 from backup piece
SQL> DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
dbms_backup_restore.RestoreSetDatafile;
dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/u03/datafile1.dbf');
dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u03/backup/2cn5blrn_1_1', params => null);
dbms_backup_restore.DeviceDeallocate;
END;
/
3. Create controlfile with datafile 1
SQL>!cat /u03/1.ctl
CREATE CONTROLFILE REUSE DATABASE "oradb" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/d01/app/oracle/oradata/oradb/oradb/onlinelog/o1_mf_1_7lqq1m62_.log' SIZE 50M,
GROUP 2 '/d01/app/oracle/oradata/oradb/oradb/onlinelog/o1_mf_2_7lqq1myr_.log' SIZE 50M,
GROUP 3 '/d01/app/oracle/oradata/oradb/oradb/onlinelog/o1_mf_3_7lqq1nr0_.log' SIZE 50M
DATAFILE '/u03/datafile1.dbf'
CHARACTER SET WE8ISO8859P1;
SQL> @/u03/1.ctl
Control file created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u03/datafile1.dbf
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
4. Catalog all the backup piece
[oracle@linux ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Thu Mar 8 11:55:58 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: oradb (DBID=396070408, not open)
RMAN> catalog start with '/u03/backup/' noprompt;
using target database control file instead of recovery catalog
searching for all files that match the pattern /u03/backup/
List of Files Unknown to the Database
=====================================
File Name: /u03/backup/28n5bki6_1_1
File Name: /u03/backup/ORA1122-backup-080312.log
File Name: /u03/backup/2dn5blsq_1_1
File Name: /u03/backup/2cn5blrn_1_1
File Name: /u03/backup/oradb-080312.log
File Name: /u03/backup/27n5bkd0_1_1
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u03/backup/2dn5blsq_1_1
File Name: /u03/backup/2cn5blrn_1_1
List of Files Which Where Not Cataloged
=======================================
File Name: /u03/backup/28n5bki6_1_1
RMAN-07518: Reason: Foreign database file DBID: 2353175563 Database Name: ORA1122
File Name: /u03/backup/ORA1122-backup-080312.log
RMAN-07517: Reason: The file header is corrupted
File Name: /u03/backup/oradb-080312.log
RMAN-07517: Reason: The file header is corrupted
File Name: /u03/backup/27n5bkd0_1_1
RMAN-07518: Reason: Foreign database file DBID: 2353175563 Database Name: ORA1122
RMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.05M DISK 00:00:00 08-MAR-12
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TAG20120308T101415
Piece Name: /u03/backup/2dn5blsq_1_1
Control File Included: Ckp SCN: 1320981 Ckp time: 08-MAR-12
SPFILE Included: Modification time: 08-MAR-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 107.40M DISK 00:00:00 08-MAR-12
BP Key: 2 Status: AVAILABLE Compressed: YES Tag: TAG20120308T101415
Piece Name: /u03/backup/2cn5blrn_1_1
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1320981 08-MAR-12 +DATA/oradb/datafile/system.257.775126603
2 Full 1320981 08-MAR-12
3 Full 1320981 08-MAR-12
4 Full 1320981 08-MAR-12
5 Full 1320981 08-MAR-12
RMAN>
5. Now restore the datafiles using DBMS_BACKUP_RESTORE package . Please note name of datafiles are not important here we can give any unique name to restore the datafiles
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
5
6 dbms_backup_restore.RestoreSetDatafile;
7 dbms_backup_restore.RestoreDatafileTo(dfnumber => 4,toname => '/u03/datafile4.dbf');
8 dbms_backup_restore.RestoreDatafileTo(dfnumber => 2,toname => '/u03/datafile2.dbf');
9 dbms_backup_restore.RestoreDatafileTo(dfnumber => 3,toname => '/u03/datafile3.dbf');
10 dbms_backup_restore.RestoreDatafileTo(dfnumber => 5,toname => '/u03/datafile5.dbf');
11 dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/u03/datafile1.dbf');
12 dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u03/backup/2cn5blrn_1_1', params => null);
13 dbms_backup_restore.DeviceDeallocate;
14 END;
15 /
PL/SQL procedure successfully completed.
Metadata will show only datafile 1 . We need to re-create control file with all relevant restored dataifiles
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/oradb/datafile/system.257.775126603
SQL> shutdown immediate;
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 1272864 bytes
Variable Size 142607328 bytes
Database Buffers 58720256 bytes
Redo Buffers 7114752 bytes
SQL>!cat /u03/1.ctl
CREATE CONTROLFILE REUSE DATABASE "oradb" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/d01/app/oracle/oradata/oradb/oradb/onlinelog/o1_mf_1_7lqq1m62_.log' SIZE 50M,
GROUP 2 '/d01/app/oracle/oradata/oradb/oradb/onlinelog/o1_mf_2_7lqq1myr_.log' SIZE 50M,
GROUP 3 '/d01/app/oracle/oradata/oradb/oradb/onlinelog/o1_mf_3_7lqq1nr0_.log' SIZE 50M
DATAFILE
'/d03/datafile1.dbf',
'/d03/datafile4.dbf',
'/d03/datafile2.dbf',
'/d03/datafile3.dbf',
'/d03/datafile5.dbf'
CHARACTER SET WE8ISO8859P1
;
SQL> @/d03/1.ctl
Control file created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/d03/datafile1.dbf
/d03/datafile2.dbf
/d03/datafile3.dbf
/d03/datafile4.dbf
/d03/datafile5.dbf
6. If the backup what we restored is not the cold backup then we need to recover the database,hence, we can catalog the backup pieces which contains archives ,restore archives and then do recovery
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL>
7. Once database in consistent status we can open database with resetlogs option
SQL>alter database open resetlogs;
Database altered.
SQL>
Missing controlfile backup
1. See the current database structure
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema
List of Datafiles
==================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 510 SYSTEM *** +DATA/oradb/datafile/system.257.775126603
2 595 UNDOTBS1 *** +DATA/oradb/datafile/undotbs1.256.775126561
3 250 SYSAUX *** +DATA/oradb/datafile/sysaux.258.775126637
4 28 USERS *** +DATA/oradb/datafile/users.259.775126653
5 50 USERS *** +DATA/oradb/datafile/users.262.776000421
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /u01/app/oracle/oradata/oradb/oradb/datafile/o1_mf_temp_7lqq1qko_.tmp
RMAN> exit
Recovery Manager complete.
2. Create a dummy instance/ can use existing database to extract datafile 1 from backup piece and restore datafile 1 from backup piece
SQL> DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
dbms_backup_restore.RestoreSetDatafile;
dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/u03/datafile1.dbf');
dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u03/backup/2cn5blrn_1_1', params => null);
dbms_backup_restore.DeviceDeallocate;
END;
/
3. Create controlfile with datafile 1
SQL>!cat /u03/1.ctl
CREATE CONTROLFILE REUSE DATABASE "oradb" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/d01/app/oracle/oradata/oradb/oradb/onlinelog/o1_mf_1_7lqq1m62_.log' SIZE 50M,
GROUP 2 '/d01/app/oracle/oradata/oradb/oradb/onlinelog/o1_mf_2_7lqq1myr_.log' SIZE 50M,
GROUP 3 '/d01/app/oracle/oradata/oradb/oradb/onlinelog/o1_mf_3_7lqq1nr0_.log' SIZE 50M
DATAFILE '/u03/datafile1.dbf'
CHARACTER SET WE8ISO8859P1;
SQL> @/u03/1.ctl
Control file created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u03/datafile1.dbf
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
4. Catalog all the backup piece
[oracle@linux ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Thu Mar 8 11:55:58 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: oradb (DBID=396070408, not open)
RMAN> catalog start with '/u03/backup/' noprompt;
using target database control file instead of recovery catalog
searching for all files that match the pattern /u03/backup/
List of Files Unknown to the Database
=====================================
File Name: /u03/backup/28n5bki6_1_1
File Name: /u03/backup/ORA1122-backup-080312.log
File Name: /u03/backup/2dn5blsq_1_1
File Name: /u03/backup/2cn5blrn_1_1
File Name: /u03/backup/oradb-080312.log
File Name: /u03/backup/27n5bkd0_1_1
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u03/backup/2dn5blsq_1_1
File Name: /u03/backup/2cn5blrn_1_1
List of Files Which Where Not Cataloged
=======================================
File Name: /u03/backup/28n5bki6_1_1
RMAN-07518: Reason: Foreign database file DBID: 2353175563 Database Name: ORA1122
File Name: /u03/backup/ORA1122-backup-080312.log
RMAN-07517: Reason: The file header is corrupted
File Name: /u03/backup/oradb-080312.log
RMAN-07517: Reason: The file header is corrupted
File Name: /u03/backup/27n5bkd0_1_1
RMAN-07518: Reason: Foreign database file DBID: 2353175563 Database Name: ORA1122
RMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.05M DISK 00:00:00 08-MAR-12
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TAG20120308T101415
Piece Name: /u03/backup/2dn5blsq_1_1
Control File Included: Ckp SCN: 1320981 Ckp time: 08-MAR-12
SPFILE Included: Modification time: 08-MAR-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 107.40M DISK 00:00:00 08-MAR-12
BP Key: 2 Status: AVAILABLE Compressed: YES Tag: TAG20120308T101415
Piece Name: /u03/backup/2cn5blrn_1_1
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1320981 08-MAR-12 +DATA/oradb/datafile/system.257.775126603
2 Full 1320981 08-MAR-12
3 Full 1320981 08-MAR-12
4 Full 1320981 08-MAR-12
5 Full 1320981 08-MAR-12
RMAN>
5. Now restore the datafiles using DBMS_BACKUP_RESTORE package . Please note name of datafiles are not important here we can give any unique name to restore the datafiles
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
5
6 dbms_backup_restore.RestoreSetDatafile;
7 dbms_backup_restore.RestoreDatafileTo(dfnumber => 4,toname => '/u03/datafile4.dbf');
8 dbms_backup_restore.RestoreDatafileTo(dfnumber => 2,toname => '/u03/datafile2.dbf');
9 dbms_backup_restore.RestoreDatafileTo(dfnumber => 3,toname => '/u03/datafile3.dbf');
10 dbms_backup_restore.RestoreDatafileTo(dfnumber => 5,toname => '/u03/datafile5.dbf');
11 dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/u03/datafile1.dbf');
12 dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u03/backup/2cn5blrn_1_1', params => null);
13 dbms_backup_restore.DeviceDeallocate;
14 END;
15 /
PL/SQL procedure successfully completed.
Metadata will show only datafile 1 . We need to re-create control file with all relevant restored dataifiles
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/oradb/datafile/system.257.775126603
SQL> shutdown immediate;
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 1272864 bytes
Variable Size 142607328 bytes
Database Buffers 58720256 bytes
Redo Buffers 7114752 bytes
SQL>!cat /u03/1.ctl
CREATE CONTROLFILE REUSE DATABASE "oradb" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/d01/app/oracle/oradata/oradb/oradb/onlinelog/o1_mf_1_7lqq1m62_.log' SIZE 50M,
GROUP 2 '/d01/app/oracle/oradata/oradb/oradb/onlinelog/o1_mf_2_7lqq1myr_.log' SIZE 50M,
GROUP 3 '/d01/app/oracle/oradata/oradb/oradb/onlinelog/o1_mf_3_7lqq1nr0_.log' SIZE 50M
DATAFILE
'/d03/datafile1.dbf',
'/d03/datafile4.dbf',
'/d03/datafile2.dbf',
'/d03/datafile3.dbf',
'/d03/datafile5.dbf'
CHARACTER SET WE8ISO8859P1
;
SQL> @/d03/1.ctl
Control file created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/d03/datafile1.dbf
/d03/datafile2.dbf
/d03/datafile3.dbf
/d03/datafile4.dbf
/d03/datafile5.dbf
6. If the backup what we restored is not the cold backup then we need to recover the database,hence, we can catalog the backup pieces which contains archives ,restore archives and then do recovery
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL>
7. Once database in consistent status we can open database with resetlogs option
SQL>alter database open resetlogs;
Database altered.
SQL>