使用rman catalog命令恢复数据库

有这样的一种需要恢复数据库场景：
当前数据库有一份全备，归档，联机重做日志都没有损坏或丢失，但是控制文件意外的损坏了，而且更糟糕的是，居然没有备份的控制文件（当然， 这种情形很少见），这种情形下，只有重建控制文件，但是重建控制文件也是一件很棘手的事情，如果数据库有上百个数据文件，要写在控制文件里，不是件容易的是，这就要求数据库管理员对数据库结构相当熟悉。这也是说明了控制文件的重要性了。我这里的场景是使用alter database backup controlfile to trace得到重建控制文件的脚本，纯粹是为了今天要说的主题来的。在生产环境，可能比这个要复杂的多。好了，控制文件重建成功，数据库也打开了。但不幸的是，数据库管理员并没有及时进行一次全备份，这个时候，一个数据文件损坏或丢失了。使用原来的备份集无法restore数据文件，因为重建的控制文件并没有记录原来备份集的备份元数据。我们怎么办？

[@more@]

好，下面进入今天的主题：

使用catalog注册备份集的备份信息到控制文件中，之后，就可以restore数据库了。

1.生产库上原有的备份集存在，而且是可用的。
total 793052
-rw-r----- 1 oracle oinstall 802693120 Dec 5 22:11 01ns4nbj_1_1.bak

2.重建控制文件
[oracle@primary dbwdn]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Dec 8 11:17:02 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DBWDN" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/dbwdn/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/dbwdn/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/dbwdn/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/dbwdn/system01.dbf',
'/u01/app/oracle/oradata/dbwdn/undotbs01.dbf',
'/u01/app/oracle/oradata/dbwdn/sysaux01.dbf',
'/u01/app/oracle/oradata/dbwdn/users01.dbf',
'/u01/app/oracle/oradata/dbwdn/test01.dbf',
'/u01/app/oracle/oradata/dbwdn/ggs01.dbf',
'/u01/app/oracle/oradata/dbwdn/perfstat.dbf',
'/u01/app/oracle/oradata/dbwdn/shkz_data_01.dbf'
CHARACTER SET WE8ISO8859P1
;ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 130024004 bytes
Database Buffers 150994944 bytes
Redo Buffers 2920448 bytes
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

21 22

Control file created.

SQL>

3.数据库并没有重新进行rman全备份，丢失数据文件

rm test01.dbf

4.使用原来的备份集无法restore数据文件

RMAN> restore datafile 5;

Starting restore at 08-DEC-2012 11:20:15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/08/2012 11:20:16
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore

5.尝试进行恢复
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery if it was restored from backup, or END
BACKUP if it was not
ORA-01110: data file 1: '/u01/app/oracle/oradata/dbwdn/system01.dbf'

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: '/u01/app/oracle/oradata/dbwdn/test01.dbf'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/dbwdn/test01.dbf'

SQL> recover datafile 5;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: '/u01/app/oracle/oradata/dbwdn/test01.dbf'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/dbwdn/test01.dbf'

SQL> alter database create datafile '/u01/app/oracle/oradata/dbwdn/test01.dbf';
alter database create datafile '/u01/app/oracle/oradata/dbwdn/test01.dbf'
*
ERROR at line 1:
ORA-01178: file 5 created before last CREATE CONTROLFILE, cannot recreate ##这条信息说明当前控制文件并没有数据文件的备份信息存在

ORA-01110: data file 5: '/u01/app/oracle/oradata/dbwdn/test01.dbf'

6.使用catalog命令注册原来的备份集信息到控制文件中.
RMAN> catalog backuppiece '/u01/bak/01ns4nbj_1_1.bak';

cataloged backuppiece
backup piece handle=/u01/bak/01ns4nbj_1_1.bak recid=1 stamp=801487520

RMAN>

当然如果有过多备份片，可以使用catalog start with '/u01/bak/';

现在进行restore 备份集看看

RMAN> restore datafile 5;

Starting restore at 08-DEC-2012 11:26:25
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /u01/app/oracle/oradata/dbwdn/test01.dbf
channel ORA_DISK_1: reading from backup piece /u01/bak/01ns4nbj_1_1.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/bak/01ns4nbj_1_1.bak tag=TAG20121205T221058
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 08-DEC-2012 11:26:28

RMAN>

可以了。

7.恢复数据库
SQL> recover database;
ORA-00279: change 5085599 generated at 12/05/2012 22:10:59 needed for thread 1
ORA-00289: suggestion : /archivelog/1_38_800989177.dbf
ORA-00280: change 5085599 for thread 1 is in sequence #38

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 5085652 generated at 12/05/2012 22:12:33 needed for thread 1
ORA-00289: suggestion : /archivelog/1_39_800989177.dbf
ORA-00280: change 5085652 for thread 1 is in sequence #39
ORA-00278: log file '/archivelog/1_38_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5085696 generated at 12/05/2012 22:14:01 needed for thread 1
ORA-00289: suggestion : /archivelog/1_40_800989177.dbf
ORA-00280: change 5085696 for thread 1 is in sequence #40
ORA-00278: log file '/archivelog/1_39_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5086088 generated at 12/05/2012 22:23:02 needed for thread 1
ORA-00289: suggestion : /archivelog/1_41_800989177.dbf
ORA-00280: change 5086088 for thread 1 is in sequence #41
ORA-00278: log file '/archivelog/1_40_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5106328 generated at 12/05/2012 22:32:39 needed for thread 1
ORA-00289: suggestion : /archivelog/1_42_800989177.dbf
ORA-00280: change 5106328 for thread 1 is in sequence #42
ORA-00278: log file '/archivelog/1_41_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5127758 generated at 12/05/2012 23:04:10 needed for thread 1
ORA-00289: suggestion : /archivelog/1_43_800989177.dbf
ORA-00280: change 5127758 for thread 1 is in sequence #43
ORA-00278: log file '/archivelog/1_42_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5149034 generated at 12/06/2012 20:41:25 needed for thread 1
ORA-00289: suggestion : /archivelog/1_44_800989177.dbf
ORA-00280: change 5149034 for thread 1 is in sequence #44
ORA-00278: log file '/archivelog/1_43_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5149687 generated at 12/06/2012 20:53:15 needed for thread 1
ORA-00289: suggestion : /archivelog/1_45_800989177.dbf
ORA-00280: change 5149687 for thread 1 is in sequence #45
ORA-00278: log file '/archivelog/1_44_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5149698 generated at 12/06/2012 20:53:33 needed for thread 1
ORA-00289: suggestion : /archivelog/1_46_800989177.dbf
ORA-00280: change 5149698 for thread 1 is in sequence #46
ORA-00278: log file '/archivelog/1_45_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5149718 generated at 12/06/2012 20:54:05 needed for thread 1
ORA-00289: suggestion : /archivelog/1_47_800989177.dbf
ORA-00280: change 5149718 for thread 1 is in sequence #47
ORA-00278: log file '/archivelog/1_46_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5149764 generated at 12/06/2012 20:55:28 needed for thread 1
ORA-00289: suggestion : /archivelog/1_48_800989177.dbf
ORA-00280: change 5149764 for thread 1 is in sequence #48
ORA-00278: log file '/archivelog/1_47_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5149772 generated at 12/06/2012 20:55:50 needed for thread 1
ORA-00289: suggestion : /archivelog/1_49_800989177.dbf
ORA-00280: change 5149772 for thread 1 is in sequence #49
ORA-00278: log file '/archivelog/1_48_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5150485 generated at 12/06/2012 20:59:13 needed for thread 1
ORA-00289: suggestion : /archivelog/1_50_800989177.dbf
ORA-00280: change 5150485 for thread 1 is in sequence #50
ORA-00278: log file '/archivelog/1_49_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5150487 generated at 12/06/2012 20:59:13 needed for thread 1
ORA-00289: suggestion : /archivelog/1_51_800989177.dbf
ORA-00280: change 5150487 for thread 1 is in sequence #51
ORA-00278: log file '/archivelog/1_50_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5171207 generated at 12/06/2012 21:39:16 needed for thread 1
ORA-00289: suggestion : /archivelog/1_52_800989177.dbf
ORA-00280: change 5171207 for thread 1 is in sequence #52
ORA-00278: log file '/archivelog/1_51_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5194247 generated at 12/06/2012 22:20:29 needed for thread 1
ORA-00289: suggestion : /archivelog/1_53_800989177.dbf
ORA-00280: change 5194247 for thread 1 is in sequence #53
ORA-00278: log file '/archivelog/1_52_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5194743 generated at 12/06/2012 22:25:30 needed for thread 1
ORA-00289: suggestion : /archivelog/1_54_800989177.dbf
ORA-00280: change 5194743 for thread 1 is in sequence #54
ORA-00278: log file '/archivelog/1_53_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5214846 generated at 12/06/2012 22:26:25 needed for thread 1
ORA-00289: suggestion : /archivelog/1_55_800989177.dbf
ORA-00280: change 5214846 for thread 1 is in sequence #55
ORA-00278: log file '/archivelog/1_54_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5235761 generated at 12/07/2012 22:10:38 needed for thread 1
ORA-00289: suggestion : /archivelog/1_56_800989177.dbf
ORA-00280: change 5235761 for thread 1 is in sequence #56
ORA-00278: log file '/archivelog/1_55_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5237163 generated at 12/07/2012 22:47:49 needed for thread 1
ORA-00289: suggestion : /archivelog/1_57_800989177.dbf
ORA-00280: change 5237163 for thread 1 is in sequence #57
ORA-00278: log file '/archivelog/1_56_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5237324 generated at 12/07/2012 22:49:01 needed for thread 1
ORA-00289: suggestion : /archivelog/1_58_800989177.dbf
ORA-00280: change 5237324 for thread 1 is in sequence #58
ORA-00278: log file '/archivelog/1_57_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5237326 generated at 12/07/2012 22:49:02 needed for thread 1
ORA-00289: suggestion : /archivelog/1_59_800989177.dbf
ORA-00280: change 5237326 for thread 1 is in sequence #59
ORA-00278: log file '/archivelog/1_58_800989177.dbf' no longer needed for this
recovery

ORA-00279: change 5237329 generated at 12/07/2012 22:49:06 needed for thread 1
ORA-00289: suggestion : /archivelog/1_60_800989177.dbf
ORA-00280: change 5237329 for thread 1 is in sequence #60
ORA-00278: log file '/archivelog/1_59_800989177.dbf' no longer needed for this
recovery

Log applied.
Media recovery complete.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>

附：catalog命令的一些用法：

1.oracle 9i catalog命令

catalog controlfilecopy
注册用户管理备份的控制文件的备份元素到控制文件或recovery catalog

catalog datafilecopy
注册用户管理备份的数据文件的备份元素到控制文件或recovery catalog

catalog archivelog
注册用户管理备份的归档日志文件的备份元素到控制文件或recovery catalog

2.oracle 10g以上 catalog命令

除了上面三个之外，还增加了下面的用途，极大的丰富了catalog的作用

catalog backuppiece
注册用备份片的备份信息到控制文件或recovery catalog

注：这个catalog的用法是革命性的，它可以改变备份片的位置，比如：备份片的位置是/u01/bak1/xxxx.bak,
当修改备份片的位置为/u01/bak2/xxxx.bak，就可以使用该命令.
这个对于异机restore备份集很有用。当异机还原的位置和目标库不一致的时候，可以使用catalog命令修改它。
oracle 9i catalog命令没有这项功能。

catalog start with
注册一个备份目录下的所有的备份文件到控制文件或recovery catalog

当然还有catalog 闪回区

3.使用catalog的限制

很遗憾，对于备份集在磁带库，oracle catalog命令不予支持，仅限于磁盘。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28227905/viewspace-1059876/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28227905/viewspace-1059876/