说明
数据库开启归档,有数据库RMAN备份,之后控制文件和数据文件丢失,但是有控制文件备份,这种情况可以先恢复控制文件,再恢复数据文件,在保证归档文件都存在的情况下恢复完成后数据不会丢失。
数据恢复
1 创建新的表空间、数据文件、用户
SQL>create tablespace ttt datafile ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ size 10m; SQL>grant connect,resource to ttt identified by ttt; SQL>alter user ttt default tablespace ttt; SQL>alter user ttt account unlock; |
2 构造数据
SQL>conn ttt/ttt; SQL>create table ttt (id int); SQL> insert into ttt values(1); 1 row created. SQL> insert into ttt values(2); 1 row created. SQL> insert into ttt values(3); 1 row created. SQL> commit; Commit complete. SQL>select * from ttt; ID ---- 1 2 3 |
ttt用户默认表空间为ttt,创建表ttt,并插入3数据。
3 切换日志
SQL>conn / as sysdba; SQL>alter system switch logfile; |
切换日志,保证数据都写到DBF中。
4 备份数据库
RMAN>backup database; |
5 再插入一些数据
SQL>conn ttt/ttt; SQL> insert into ttt values(4); 1 row created. SQL> insert into ttt values(5); 1 row created. SQL> insert into ttt values(6); 1 row created. SQL> commit; Commit complete. SQL>select * from ttt;
|
6 备份控制文件
SQL>conn / as sysdba; SQL>alter database backup controlfile to ‘/home/oracle/control.ctl’; |
说明:这一步也可以省略,在第8步恢复控制文件时需要用之前备份的备份集 来恢复。
执行:
RMAN> restore controlfile to '/oracle/u01/app/oracle/oradata/orcl/control01.ctl' from '/oracle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_07/o1_mf_ncsnf_TAG20130307T145358_8mjgfnbg_.bkp'; |
7 模拟丢失控制文件和数据文件
SQL>conn / as sysdba; SQL>shutdown abort; [oracle@dev206 ~]$ rm -rf /oracle/u01/app/oracle/oradata/orcl/control0*.ctl [oracle@dev206 ~]$ rm -rf /oracle/u01/app/oracle/oradata/orcl/ttt.dbf SQL>startup SQL> startup ORA-32004: obsolete and/or deprecated parameter(s) specified ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started.
Total System Global Area 1224736768 bytes Fixed Size 2020384 bytes Variable Size 352324576 bytes Database Buffers 855638016 bytes Redo Buffers 14753792 bytes ORA-00205: error in identifying control file, check alert log for more info |
此时在启动数据库时报错找不到控制文件而只能启动到nomount状态。
8 恢复控制文件
之前我们trace出了控制文件的备份
利用备份恢复控制文件
RMAN>set dbid=1324770912 RMAN> restore controlfile to '/oracle/u01/app/oracle/oradata/orcl/control01.ctl' from '/home/oracle/control.ctl';
Starting restore at 07-MAR-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=540 devtype=DISK
channel ORA_DISK_1: copied control file copy Finished restore at 07-MAR-13 |
拷贝控制文件
[oracle@dev206 oracle]$ cp /oracle/u01/app/oracle/oradata/orcl/control01.ctl /oracle/u01/app/oracle/oradata/orcl/control02.ctl [oracle@dev206 oracle]$ cp /oracle/u01/app/oracle/oradata/orcl/control01.ctl /oracle/u01/app/oracle/oradata/orcl/control03.ctl |
启动数据库到mount状态
SQL>alter database mount; |
9 利用RMAN恢复数据库
RMAN>recover database;
Starting recover at 07-MAR-13 Starting implicit crosscheck backup at 07-MAR-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=543 devtype=DISK Crosschecked 2 objects Finished implicit crosscheck backup at 07-MAR-13
Starting implicit crosscheck copy at 07-MAR-13 using channel ORA_DISK_1 Finished implicit crosscheck copy at 07-MAR-13
searching for all files in the recovery area cataloging files... no files cataloged
using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 03/07/2013 14:44:05 RMAN-06094: datafile 6 must be restored |
提示需要restore datafile 6;
SQL>restore datafile 6;
Starting restore at 07-MAR-13 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 00006 to /oracle/u01/app/oracle/oradata/orcl/ttt.dbf channel ORA_DISK_1: reading from backup piece /oracle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_07/o1_mf_nnndf_TAG20130307T142051_8mjdg42h_.bkp channel ORA_DISK_1: restored backup piece 1 piece handle=/oracle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_07/o1_mf_nnndf_TAG20130307T142051_8mjdg42h_.bkp tag=TAG20130307T142051 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 Finished restore at 07-MAR-13 |
启动数据库
RMAN> alter database open;
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 03/07/2013 14:49:32 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN>alter database open noresetlogs;
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found "identifier": expecting one of: "resetlogs, ;" RMAN-01008: the bad identifier was: noresetlogs RMAN-01007: at line 1 column 21 file: standard input
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found ";": expecting one of: "allocate, alter, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, host, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, transport, upgrade, unregister, validate" RMAN-01007: at line 1 column 32 file: standard input
RMAN>alter database open resetlogs; |
10 检查数据
SQL> select count(*) from ttt.ttt; ID ---------- 1 2 3 4 5 6 |
数据库恢复完成.
说明:这个恢复过程在一定程度上就是“归档模式有备份丢失数据文件后恢复——重建控制文件”的恢复,只是没有重建控制文件利用了备份来恢复。
详见:http://space.itpub.net/26252014/viewspace-755506
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26252014/viewspace-755511/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26252014/viewspace-755511/