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');
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/DB2_QFIIDB_171_1_547362003', params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
6. 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');
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/DB2_QFIIDB_172_1_547448404', params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
7. mount数据库
SQL> alter database mount;
8. 恢复数据库到drop user前的某一时间点
SQL> >recover database until time '2005-1-14 16:00:00';
9. 启动数据库
SQL> alter database open resetlogs;
10. 立刻作一次full备份