Control File中备份信息被覆盖情况下的数据库恢复案例

来源：互联网发布：淘宝达人申请入口编辑：程序博客网时间：2024/05/17 04:03

Control File中备份信息被覆盖情况下的数据库恢复案例

现场人员报告说误删除了用户（drop user），按照操作文档作RMAN的不完全恢复，但是报错，错误信息显示：

RMAN-03002: failure during compilation of command

RMAN-03013: command type: restore

RMAN-03002: failure during compilation of command

RMAN-03013: command type: IRESTORE

RMAN-06026: some targets not found - aborting restore

RMAN-06023: no backup or copy of datafile 13 found to restore

RMAN-06023: no backup or copy of datafile 12 found to restore

RMAN-06023: no backup or copy of datafile 11 found to restore

RMAN-06023: no backup or copy of datafile 10 found to restore

RMAN-06023: no backup or copy of datafile 9 found to restore

RMAN-06023: no backup or copy of datafile 8 found to restore

RMAN-06023: no backup or copy of datafile 7 found to restore

RMAN-06023: no backup or copy of datafile 6 found to restore

RMAN-06023: no backup or copy of datafile 5 found to restore

RMAN-06023: no backup or copy of datafile 4 found to restore

RMAN-06023: no backup or copy of datafile 3 found to restore

RMAN-06023: no backup or copy of datafile 2 found to restore

在restore database的时候报没有可用的数据文件备份？于是作list backup，检查结果，发现居然没有真的没有显示可用的0级备份。

先说明一下该客户使用的备份策略：

at 5:00 /every:Friday cmd /c E:/oracle/oradata.bak/RMAN/b_db_inc0.bat

at 5:00 /every:Saturday cmd /c E:/oracle/oradata.bak/RMAN/b_db_inc2.bat

at 5:00 /every:Sunday cmd /c E:/oracle/oradata.bak/RMAN/b_db_inc2.bat

at 5:00 /every:Monday cmd /c E:/oracle/oradata.bak/RMAN/b_db_inc2.bat

at 5:00 /every:Tuesday cmd /c E:/oracle/oradata.bak/RMAN/b_db_inc1.bat

at 5:00 /every:Wednesday cmd /c E:/oracle/oradata.bak/RMAN/b_db_inc2.bat

at 5:00 /every:Thursday cmd /c E:/oracle/oradata.bak/RMAN/b_db_inc2.bat

at 6:00 /every:Friday cmd /c E:/oracle/oradata.bak/RMAN/b_del_archive.bat

每周五临晨5点作0级备份，周六至周一作2级备份，周二作1级备份，周三、周四作2级备份。

查看备份路径中生成的文件：

2004-12-31 05:04 2,147,475,968 DB0_QFIIDB_159_1_546325203

2004-12-31 05:08 2,147,475,968 DB0_QFIIDB_159_2_546325203

2004-12-31 05:09 425,230,848 DB0_QFIIDB_159_3_546325203

2005-01-01 05:04 149,479,936 DB2_QFIIDB_160_1_546411603

2005-01-02 05:04 179,380,736 DB2_QFIIDB_161_1_546498003

2005-01-03 05:04 179,945,984 DB2_QFIIDB_162_1_546584403

2005-01-04 05:04 298,500,608 DB1_QFIIDB_163_1_546670803

2005-01-05 05:04 281,788,928 DB2_QFIIDB_164_1_546757203

2005-01-06 05:04 116,072,960 DB2_QFIIDB_165_1_546843603

2005-01-08 05:04 300,474,880 DB2_QFIIDB_167_1_547016403

2005-01-09 05:04 3,596,800 DB2_QFIIDB_168_1_547102803

2005-01-10 05:04 3,662,336 DB2_QFIIDB_169_1_547189205

2005-01-11 05:04 519,537,152 DB1_QFIIDB_170_1_547275603

2005-01-12 05:04 112,280,064 DB2_QFIIDB_171_1_547362003

2005-01-13 05:04 94,863,872 DB2_QFIIDB_172_1_547448404

2005-01-15 05:04 199,836,160 DB2_QFIIDB_174_1_547621203

2005-01-16 05:04 72,442,368 DB2_QFIIDB_175_1_547707603

2005-01-17 05:04 10,600,960 DB2_QFIIDB_176_1_547794003

可以看到2004年12月31日确实作了0级备份，但是2005年1月7日和1月14日的0级备份却都没有生成，查看RMAN备份的log文件发现这两天的0级备份都报告了“磁盘空间不足”的错误，也就是空闲的磁盘空间不足够作一次0级备份，所以这两天的备份全部没有成功。而恰巧的是这个项目中并没有使用catalog，而是只使用了数据库的控制文件来存储备份信息的。而数据库的control_file_record_keep_time是默认的7天，所以很明显控制文件中的备份信息已经超过了记录的保存期限而被后续的备份信息覆盖了。

问题于是就简化为如果控制文件中不包含可用的备份信息（甚或是控制文件完全损坏），那么如何恢复RMAN备份的数据库？

这就需要使用Oracle在线文档中并没有介绍的dbms_backup_restore包。

关于该包的安装和介绍以及一些使用方法可以参看Fenng写的DBA工作备忘录之三:rman备份,未使用catalog,控制文件丢失的解决办法一文。

本文则是一次完整地在真实环境中的恢复实例，并且使用了Fenng那篇文章中没有提到的一些存储过程。

1．关闭数据库

SQL> shutdown immediate;

2．启动数据库到nomount状态

SQL> startup nomount;

3． restore 0级备份文件

DECLARE

devtype varchar2(256);

done boolean;

BEGIN

devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');

sys.dbms_backup_restore.restoreSetDatafile;

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'E:/ORACLE/ORADATA/QFIIDB/SYSTEM01.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'E:/ORACLE/ORADATA/QFIIDB/RBS01.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'E:/ORACLE/ORADATA/QFIIDB/USERS01.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_TS01.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'E:/ORACLE/ORADATA/QFIIDB/TOOLS01.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_INX_TS01.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_HIS_TS01.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_HIS_IND_TS01.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>09,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_TS02.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>10,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_INX_TS02.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>11,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_HIS_TS02.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>12,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_HIS_IND_TS02.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>13,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_TS03.DBF');

sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'E:/ORACLE/ORADATA.BAK/DB0_QFIIDB_159_1_546325203', params=>null);

sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'E:/ORACLE/ORADATA.BAK/DB0_QFIIDB_159_2_546325203', params=>null);

sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'E:/ORACLE/ORADATA.BAK/DB0_QFIIDB_159_3_546325203', params=>null);

sys.dbms_backup_restore.deviceDeallocate;

END;

4． restore 1级备份文件

DECLARE

devtype varchar2(256);

done boolean;

BEGIN

devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');

sys.dbms_backup_restore.applySetDatafile;

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>01,toname=>'E:/ORACLE/ORADATA/QFIIDB/SYSTEM01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>02,toname=>'E:/ORACLE/ORADATA/QFIIDB/RBS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>03,toname=>'E:/ORACLE/ORADATA/QFIIDB/USERS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>04,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_TS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>05,toname=>'E:/ORACLE/ORADATA/QFIIDB/TOOLS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>06,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_INX_TS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>07,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_HIS_TS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>08,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_HIS_IND_TS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>09,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_TS02.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>10,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_INX_TS02.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>11,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_HIS_TS02.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>12,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_HIS_IND_TS02.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>13,toname=>'E:/ORACLE/ORADATA/QFIIDB/STK_TS03.DBF');

sys.dbms_backup_restore.applyBackupPiece(done=>done,handle=>'E:/ORACLE/ORADATA.BAK/DB1_QFIIDB_170_1_547275603', params=>null);

sys.dbms_backup_restore.deviceDeallocate;

END;

5． restore 第一份2级备份文件

DECLARE

devtype varchar2(256);

done boolean;

BEGIN

devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');

sys.dbms_backup_restore.applySetDatafile;

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>01,toname=>'E:/ORACLE/ORADATA/QFIIDB/SYSTEM01.DBF');