当控制文件的备份丢失时,怎么restore database?
来源于:
How to restore database when controlfile backup missing (文档 ID 1438776.1)
适用于:
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***
目标:
本文对如下的情况有帮助:除了控制文件备份不存在,其他的备份都存在,该情况下的database restore.
解决方案:
给出一个例子。
1. 查看当前数据库结构:
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
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.在包括datafile 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 所有的backuppiece
[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. 使用dbms_backup_restore package 来restore datafile。请注意:datafile的名字不重要,只要对于要恢复的datafile,是唯一的名字即可。
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.
元数据显示只有datafile 1,我么需要重建控制文件以便带上所有需要恢复的datafiles
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.若是该备份不是冷备份,那么我们需要recover database,我们需要catalog 包括archivelog的backuppiece,然后restore archivelog,然后在recover
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL>
7. 一旦数据库处于一致性状态,我们可以用resetlogs的方式 open database
SQL>alter database open resetlogs;
Database altered.
SQL>
注意:
在multisection backup 的情况下,我们需要考虑所有的backuppiece(也就是所有的section),使用initmsr函数来restore datafile
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;
/