How to restore database when controlfile backup missing (Doc ID 1438776.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 16-NOV-2015***
GOAL
This article will help in the case when customer have all backup pieces of database except controlfile backup which is mandatory to mount the database and start restore.
当客户拥有数据库的所有备份集（控制文件备份除外）时，本文将有帮助。控制文件备份是安装数据库和开始还原所必需的。
SOLUTION
Here we are giving one test case where we have current database structure and simulate the customer environment where he/she 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 Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 510 SYSTEM *** DATA/ora102/datafile/system.257.775126603 2 595 UNDOTBS1 *** DATA/ora102/datafile/undotbs1.256.775126561 3 250 SYSAUX *** DATA/ora102/datafile/sysaux.258.775126637 4 28 USERS *** DATA/ora102/datafile/users.259.775126653 5 50 USERS *** DATA/ora102/datafile/users.262.776000421 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 29 TEMP 32767 /u01/app/oracle/oradata/ora102/ORA102/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
创建一个虚拟实例/可以使用现有数据库从备份文件中提取数据文件1并从备份文件中恢复数据文件1
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 使用数据文件1创建控制文件
SQL>!cat /u03/1.ctl CREATE CONTROLFILE REUSE DATABASE "ORA102" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 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 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 Catalog所有备份集
[[email protected] ~]$ 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: ORA102 (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/ora102-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/ora102-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/ora102/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
现在，使用 DBMS_BACKUP_RESTORE 包还原数据文件。请注意，数据文件的名称并不重要，我们可以使用任何唯一的名称来恢复数据文件
SQL> -- use script 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 => 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 will show only datafile 1 . We need to re-create control file with all relevant restored dataifiles
元数据将仅显示数据文件1。我们需要使用所有相关的已还原数据重新创建控制文件
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- DATA/ora102/datafile/system.257.775126603 SQL> shutdown immediate;
ORA-01109: database not open 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 "ORA102" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 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 50M DATAFILE ‘/u03/datafile1.dbf‘, ‘/u03/datafile4.dbf‘, ‘/u03/datafile2.dbf‘, ‘/u03/datafile3.dbf‘, ‘/u03/datafile5.dbf‘ CHARACTER SET WE8ISO8859P1 ; SQL> @/u03/1.ctl Control 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 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
一旦数据库处于一致状态，我们可以使用resetlogs选项打开数据库
SQL>alter database open resetlogs; Database altered. SQL>
Note: In case of Multisection backup we need to consider all the backup pieces (all sections) and use function initmsr as below to restore datafile
注意：如果是多部分备份，我们需要考虑所有备份片（所有部分），并使用如下所示的函数initmsr来还原数据文件
DECLARE devtype varchar2(256); done boolean; BEGIN devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>‘d1‘); dbms_backup_restore.RestoreSetDatafile; dbms_backup_restore.initmsr(1,‘/ud1001/PROD/oradata/system01-test.dbf‘); dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => ‘/ud1001/PROD/oradata/system01-test.dbf‘); dbms_backup_restore.RestoreBackupPiece(done => done,handle =>‘/backup/Oracle-DB-8-7-2014/db_L0_PROD_e6pf7too_1_1.rman‘, params => null); dbms_backup_restore.RestoreBackupPiece(done => done,handle =>‘/backup/Oracle-DB-8-7-2014/db_L0_PROD_e6pf7too_2_1.rman‘, params => null); END; /