还原oracle控制文件位置,rman还原控制文件（一）

如果丢失或损坏所有的控制文件就需要从备份中还原控制文件。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参数时创建控制文件时使用的规则一样。