"Translated from MOS article" When backup of control files is lost, how to restore Database

Tag: Oracle control File missing

When the backup of the control file is lost, how does the restore database?

From:
How to restore the database when Controlfile backup missing (document ID 1438776.1)

Suitable for:

Oracle database-enterprise edition-version 9.2.0.1 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.
Checked for relevance on 23-sep-2013***

Goal:
This article is helpful in the following situations: In addition to the control file backup does not exist, other backups are present, in which case the database restore.

Solution:
Give an example.
1. View the current database structure:

Rman> report schema;using target database control file instead of recovery Catalogreport of database schemalist of Perm Anent datafiles===========================file Size (MB) tablespace RB segs datafile Name----------------------------- ----------------------------------1 510 SYSTEM * * * +data/ora102/datafile/system.257.7751266032 595 UNDOTBS1 * * * +data/ ora102/datafile/undotbs1.256.7751265613 Sysaux * * * * +data/ora102/datafile/sysaux.258.7751266374 USERS * * * +DATA/ ora102/datafile/users.259.7751266535 Users * * * +data/ora102/datafile/users.262.776000421list of Temporary Files=== ====================file Size (MB) tablespace Maxsize (MB) tempfile Name--------------------------------------------- ------------------1 TEMP 32767/u01/app/oracle/oradata/ora102/ora102/datafile/o1_mf_temp_7lqq1qko_.tmprman> Exitrecovery Manager Complete

2.Create a dummy instance/can use existing database to extract datafile 1 from backup piece and restore DataFile 1 from B Ackup piece

sql> DECLARE  devtype varchar2 (n);  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. Establishment of control documents in the context of including DataFile 1:

Sql>!cat/u03/1.ctlcreate controlfile Reuse DATABASE "ORA102" Noresetlogs archivelogmaxlogfiles 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 1 '/u01/app/oracle/oradata/ora102/ora102/onlinelog/o1_ Mf_1_7lqq1m62_.log ' size 50m,group 2 '/u01/app/oracle/oradata/ora102/ora102/onlinelog/o1_mf_2_7lqq1myr_.log ' size 50m,group 3 '/u01/app/oracle/oradata/ora102/ora102/onlinelog/o1_mf_3_7lqq1nr0_.log ' SIZE 50MDATAFILE '/u03/ DATAFILE1.DBF ' CHARACTER SET we8iso8859p1; sql> @/u03/1.ctlcontrol file created. Sql> select name from V$datafile; NAME--------------------------------------------------------------------------------/u03/datafile1.dbfsql> Exitdisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-productionwith The partitioning, OLAP, D ATA Mining and Real Application testing options

4.catalog all backuppiece

[[email protected] ~]$ rman target/recovery manager:release 10.2.0.5.0-production on Thu Mar 8 11:55:58 2012Copyr Ight (c) 1982, Oracle. All rights reserved.connected to target database:ora102 (dbid=396070408, no open) rman> catalog start with '/u03/backu p/' noprompt;using target database control file instead of recovery catalogsearching for all files that match the pattern /u03/backup/list of Files Unknown to the Database=====================================file Name:/u03/backup/28n5bki6_ 1_1file Name:/u03/backup/ora1122-backup-080312.logfile Name:/u03/backup/2dn5blsq_1_1file Name:/u03/backup/ 2cn5blrn_1_1file Name:/u03/backup/ora102-080312.logfile name:/u03/backup/27n5bkd0_1_1cataloging files ... Cataloging donelist of cataloged Files=======================file name:/u03/backup/2dn5blsq_1_1file Name:/u03/backup /2cn5blrn_1_1list of Files which Where not cataloged=======================================file Name:/u03/backup/ 28n5bki6_1_1rman-07518:reason:foreignDatabase file dbid:2353175563 database name:ora1122file Name:/u03/backup/ora1122-backup-080312.logrman-07517:reason  : The file header is Corruptedfile Name:/u03/backup/ora102-080312.logrman-07517:reason:the file header is Corruptedfile Name:/u03/backup/27n5bkd0_1_1rman-07518:reason:foreign database file dbid:2353175563 database name:ora1122rman> l  IST backup;using Target Database control file instead of recovery cataloglist of backup Sets===================bs Key Type  LV Size Device Type Elapsed Time completion time-------------------------------------------------------------1 full 1.05M DISK 00:00:00 08-mar-12bp key:1 status:available compressed:yes tag:tag20120308t101415piece Name:/U03/BACKUP/2 Dn5blsq_1_1control File included:ckp scn:1320981 Ckp time:08-mar-12spfile included:modification time:08-mar-12bs Key Type LV Size Device Type Elapsed Time completion time-------------------------------------------------------------2 Full 107.40M DISK 00:00:0008-MAR-12BP key:2 status:available compressed:yes tag:tag20120308t101415piece Name:/u03/backup/2cn5blrn_1_1list of Da Tafiles in Backup set 2File LV Type Ckp SCN Ckp time Name---------------------------------1 full 1320981 08-mar-12 +d ata/ora102/datafile/system.257.7751266032 full 1320981 08-mar-123 full 1320981 08-mar-124 full 1320981 08-MAR-125 full 13 20981 08-mar-12rman>

5. Use the Dbms_backup_restore package to restore datafile. Please note: DataFile's name is not important, as long as the datafile to be restored is the only name.

Sql>  --use Scriptdeclare  devtype varchar2 (n);  Done Boolean; BEGIN  Devtype: = Dbms_backup_restore. Deviceallocate (type=>null, ident=> ' D1 ');  Dbms_backup_restore. Restoresetdatafile;  Dbms_backup_restore. Restoredatafileto (dfnumber = 4,toname = '/u03/datafile4.dbf ');  Dbms_backup_restore. Restoredatafileto (dfnumber = 2,toname = '/u03/datafile2.dbf ');  Dbms_backup_restore. Restoredatafileto (dfnumber = 3,toname = '/u03/datafile3.dbf ');  Dbms_backup_restore. Restoredatafileto (dfnumber = 5,toname = '/u03/datafile5.dbf ');  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;/pl/sql procedure successfully completed.

Metadata shows only datafile 1, I need to rebuild the control file to bring all the datafiles that need to be recovered

Sql> select name from V$datafile; NAME--------------------------------------------------------------------------------+data/ora102/datafile/ system.257.775126603sql> shutdown immediate; <ora-01109:database not OpenDatabase dismounted. ORACLE instance shut down. Sql> Startup Nomount;oracle instance started. Total System Global area 209715200 bytesfixed size 1272864 bytesvariable size 142607328 bytesdatabase buffers 58720256 byt Esredo buffers 7114752 bytessql>!cat/u03/1.ctlcreate controlfile reuse DATABASE "ORA102" noresetlogs Archivelogmaxlogfiles 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 1 '/u01/app/oracle /oradata/ora102/ora102/onlinelog/o1_mf_1_7lqq1m62_.log ' SIZE 50m,group 2 '/u01/app/oracle/oradata/ora102/ora102/ Onlinelog/o1_mf_2_7lqq1myr_.log ' SIZE 50m,group 3 '/u01/app/oracle/oradata/ora102/ora102/onlinelog/o1_mf_3_ 7lqq1nr0_.log ' SIZE 50MDATAFILE '/u03/datafile1.dbf ', '/u03/datafile4.dbf ', '/u03/datafile2.dbf ', '/u03/ Datafile3.dbf ', '/u03/DATAFILE5.DBF ' CHARACTER SET we8iso8859p1; sql> @/u03/1.ctlcontrol file created. Sql> select name from V$datafile; NAME--------------------------------------------------------------------------------/u03/datafile1.dbf/u03/ Datafile2.dbf/u03/datafile3.dbf/u03/datafile4.dbf/u03/datafile5.dbf

6. If the backup is not a cold backup, then we need to recover database, we need the catalog including Archivelog backuppiece, then restore Archivelog, and then in recover

sql> recover database until cancel; Media recovery complete. sql> ALTER DATABASE open Resetlogs;database altered. Sql>

7. Once the database is in a consistent state, we can use the Resetlogs

Sql>alter database open Resetlogs;database altered. Sql>

Attention:
In the case of multisection backup, we need to consider all backuppiece (that is, all sections) and use the INITMSR function to restore datafile

Declaredevtype Varchar2 (;d one boolean; Begindevtype: = Dbms_backup_restore. Deviceallocate (type=>null, ident=> ' D1 ');   

"Translated from MOS article" When backup of control files is lost, how to restore Database