如果丢失或损坏所有的控制文件就需要从备份中还原控制文件。restore controlfile命令用来还原控制文件。在还原控制文件后需要对数据亩执行完全介质恢复并以resetlog选项来打开数据库。RMAN可以将控制文件还原到它的默认存储位置,也可以使用restore controlfile … to destination来指定位置。
从已经知的控制文件备份中还原控制文件
SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /u01/app/oracle/oradata/test/c ontrol01.ctl, /u01/app/oracle/ oradata/test/control02.ctl, /u 01/app/oracle/oradata/test/con trol03.ctl
显示当前可用的备份
RMAN> list backup; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 77 693.50K DISK 00:00:02 28-JAN-15 BP Key: 75 Status: AVAILABLE Compressed: YES Tag: TAG20150128T131713 Piece Name: /u02/test_df870182233_s95_s1 List of Archived Logs in backup set 77 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 13 2928236 28-JAN-15 2928830 28-JAN-15 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 78 Full 166.91M DISK 00:01:19 28-JAN-15 BP Key: 76 Status: AVAILABLE Compressed: YES Tag: TAG20150128T131716 Piece Name: /u02/test_df870182236_s96_s1 List of Datafiles in backup set 78 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/system01.dbf 2 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/undotbs01.dbf 3 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/sysaux01.dbf 4 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/users01.dbf 5 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/example01.dbf 6 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/test01.dbf BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 79 7.50K DISK 00:00:01 28-JAN-15 BP Key: 77 Status: AVAILABLE Compressed: YES Tag: TAG20150128T131841 Piece Name: /u02/test_df870182321_s97_s1 List of Archived Logs in backup set 79 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 14 2928830 28-JAN-15 2928868 28-JAN-15 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 80 Full 9.42M DISK 00:00:02 28-JAN-15 BP Key: 78 Status: AVAILABLE Compressed: NO Tag: TAG20150128T131843 Piece Name: /u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150128-0d Control File Included: Ckp SCN: 2928874 Ckp time: 28-JAN-15 SPFILE Included: Modification time: 28-JAN-15
从上面的信息可以看到备份集80是控制文件与spfile文件的备份
下面来删除当前数据库的所有控制文件:
[root@oracle11g ~]# cd /u01/app/oracle/oradata/test/ [root@oracle11g test]# ls -lrt total 2213868 -rw-r----- 1 oracle oinstall 11804672 Feb 1 11:36 users01.dbf -rw-r----- 1 oracle oinstall 52436992 Feb 1 11:36 test01.dbf -rw-r----- 1 oracle oinstall 52429312 Feb 1 11:36 redo02.log -rw-r----- 1 oracle oinstall 52429312 Feb 1 11:36 redo01.log -rw-r----- 1 oracle oinstall 104865792 Feb 1 11:36 example01.dbf -rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf -rw-r----- 1 oracle oinstall 838868992 Feb 1 19:05 system01.dbf -rw-r----- 1 oracle oinstall 492838912 Feb 1 19:05 undotbs01.dbf -rw-r----- 1 oracle oinstall 576724992 Feb 1 19:05 sysaux01.dbf -rw-r----- 1 oracle oinstall 52429312 Feb 1 19:10 redo03.log -rw-r----- 1 oracle oinstall 9814016 Feb 1 19:11 control03.ctl -rw-r----- 1 oracle oinstall 9814016 Feb 1 19:11 control02.ctl -rw-r----- 1 oracle oinstall 9814016 Feb 1 19:11 control01.ctl [root@oracle11g test]# rm -rf control*.ctl [root@oracle11g test]# ls -lrt total 2185068 -rw-r----- 1 oracle oinstall 52429312 Feb 1 11:36 redo02.log -rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf -rw-r----- 1 oracle oinstall 52429312 Feb 1 19:13 redo03.log -rw-r----- 1 oracle oinstall 11804672 Feb 1 19:14 users01.dbf -rw-r----- 1 oracle oinstall 492838912 Feb 1 19:14 undotbs01.dbf -rw-r----- 1 oracle oinstall 52436992 Feb 1 19:14 test01.dbf -rw-r----- 1 oracle oinstall 838868992 Feb 1 19:14 system01.dbf -rw-r----- 1 oracle oinstall 576724992 Feb 1 19:14 sysaux01.dbf -rw-r----- 1 oracle oinstall 52429312 Feb 1 19:14 redo01.log -rw-r----- 1 oracle oinstall 104865792 Feb 1 19:14 example01.dbf
向测试表t2中插入一些数据库
SQL> insert into t2 select * from dba_objects; 51319 rows created. SQL> select count(*) from t2; COUNT(*) ---------- 102560 SQL> commit; Commit complete.
这里因为是从linux操作系统层面删除了所有控制文件,因为在数据库没有关闭的情况下文件的句柄没有释放所以数据库还能运行。
人为将数据库异常终止
[root@oracle11g test]# ps -ef | grep smon oracle 3463 1 0 22:30 ? 00:00:00 ora_smon_test root 3179 3123 0 22:45 pts/3 00:00:00 grep smon [root@oracle11g test]# kill -9 3463
启动数据库:
SQL> startup ORACLE instance started. Total System Global Area 327155712 bytes Fixed Size 1273516 bytes Variable Size 138412372 bytes Database Buffers 184549376 bytes Redo Buffers 2920448 bytes ORA-00205: error in identifying control file, check alert log for more info
alert日志的内容如下:
ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/test/control01.ctl' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Sun Feb 01 19:18:18 CST 2015 ORA-205 signalled during: ALTER DATABASE MOUNT...
找不到控制文件不能将数据库置于mount状态.现在通过备份来还原控制文件执行完全数据库恢复:
RMAN> restore controlfile from '/u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150128-0d'; Starting restore at 01-FEB-15 using channel ORA_DISK_1 channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:05 output filename=/u01/app/oracle/oradata/test/control01.ctl output filename=/u01/app/oracle/oradata/test/control02.ctl output filename=/u01/app/oracle/oradata/test/control03.ctl Finished restore at 01-FEB-15 RMAN> sql 'alter database mount'; sql statement: alter database mount released channel: ORA_DISK_1 SQL> select status from v$instance; STATUS ------------------------ MOUNTED
执行完全恢复
RMAN> recover database; Starting recover at 01-FEB-15 Starting implicit crosscheck backup at 01-FEB-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=155 devtype=DISK Crosschecked 3 objects Finished implicit crosscheck backup at 01-FEB-15 Starting implicit crosscheck copy at 01-FEB-15 using channel ORA_DISK_1 Crosschecked 6 objects Finished implicit crosscheck copy at 01-FEB-15 searching for all files in the recovery area cataloging files... no files cataloged using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 17 is already on disk as file /u01/app/oracle/oradata/test/redo02.log archive log thread 1 sequence 18 is already on disk as file /u01/app/oracle/oradata/test/redo03.log archive log thread 1 sequence 19 is already on disk as file /u01/app/oracle/oradata/test/redo01.log archive log filename=/u02/1_15_870133266.dbf thread=1 sequence=15 archive log filename=/u02/1_16_870133266.dbf thread=1 sequence=16 archive log filename=/u01/app/oracle/oradata/test/redo02.log thread=1 sequence=17 archive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=18 archive log filename=/u01/app/oracle/oradata/test/redo01.log thread=1 sequence=19 media recovery complete, elapsed time: 00:00:06 Finished recover at 01-FEB-15 RMAN> sql 'alter database open resetlogs'; sql statement: alter database open resetlogs SQL> select status from v$instance; STATUS ------------ OPEN SQL> select count(*) from t2; COUNT(*) ---------- 102560
表t2中的记录与恢复之前相同,说明恢复成功。
当还原控制文件时,控制文件的默认位置是由参数control_files控制的。如果没有设置control_files参数,那么数据库判断还原控制文件存储位置的规则将会与没有设置control_files参数时创建控制文件时使用的规则一样。