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所有备份集

[oracle@oel57 ~]$ 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;
/