控制檔案的恢復方法（一）

控制檔案的通常恢復方法有：
1、從控制檔案自動或二進位制備份中恢復

2、從控制檔案快照備份中恢復

3、控制檔案trace檔案備份中恢復

4、Oracle自動生成的控制檔案指令碼恢復

如下對相關的步驟及語法進行介紹：

1、從控制檔案自動備份中恢復

對資料庫進行一些改動：

SQL> create table t as select * from dba_users;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

關閉資料庫後將控制檔案透過OS的mv 或rm命令進行移動或刪除；

$ control01.ctl control02.ctl control03.ctl /opt/oracle/backups/

[@more@]

RMAN> sql 'alter session set nls_language="american"'; --該語句是因為我的cmd介面亂碼才執行的；

RMAN> set DBID=2083742440

executing command: SET DBID

RMAN> restore controlfile from autobackup;

Starting restore at 15-JUN-12

using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20120615

channel ORA_DISK_1: looking for autobackup on day: 20120614

channel ORA_DISK_1: looking for autobackup on day: 20120613

channel ORA_DISK_1: looking for autobackup on day: 20120612

channel ORA_DISK_1: looking for autobackup on day: 20120611

channel ORA_DISK_1: looking for autobackup on day: 20120610

channel ORA_DISK_1: autobackup found: c-2083742440-20120610-00

channel ORA_DISK_1: control file restore from autobackup complete

output filename=/opt/oracle/oradata/test/control01.ctl

output filename=/opt/oracle/oradata/test/control02.ctl

output filename=/opt/oracle/oradata/test/control03.ctl

Finished restore at 15-JUN-12

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> RECOVER DATABASE;

Starting recover at 15-JUN-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=154 devtype=DISK

starting media recovery

archive log thread 1 sequence 24 is already on disk as file /opt/oracle/oradata/test/redo03.log

archive log thread 1 sequence 25 is already on disk as file /opt/oracle/oradata/test/redo01.log

archive log thread 1 sequence 26 is already on disk as file /opt/oracle/oradata/test/redo02.log

unable to find archive log

archive log thread=1 sequence=21

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 06/15/2012 17:56:04

RMAN-06054: media recovery requesting unknown log: thread 1 seq 21 lowscn 232333

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 06/15/2012 17:57:48

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/opt/oracle/oradata/test/system01.dbf'

以上過程還是不成功，是不是沒有restore database的緣故呢？因此執行如下執行令：

RMAN> SQL 'ALTER SESSION SET NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"';

RMAN> run

2> {set until time '2012-06-15 17:30:00';

3> restore database;

4> recover database;

5> }

executing command: SET until clause

省略部分內容...

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 06/15/2012 18:08:53

ORA-01180: can not create datafile 1

ORA-01110: data file 1: '/opt/oracle/oradata/test/system01.dbf'

因為控制檔案完全丟失，再說rman備份時用format命令指定'/opt/backup'目錄，restore後的控制檔案是無法找到備份集的具體位置，因此透過如下命令指定備份集所在目錄：

RMAN> catalog start with '/opt/backup/';

searching for all files that match the pattern /opt/backup/

List of Files Unknown to the Database

=====================================

File Name: /opt/backup/testarch_TEST_20120610_8

File Name: /opt/backup/testarch_TEST_20120610_1

File Name: /opt/backup/testfull_TEST_20120610_2

File Name: /opt/backup/testarch_TEST_20120610_4

File Name: /opt/backup/1_24_785596008.dbf

File Name: /opt/backup/1_23_785596008.dbf

File Name: /opt/backup/testfull_TEST_20120610_6

File Name: /opt/backup/testfull_TEST_20120610_3

File Name: /opt/backup/testarch_TEST_20120610_5

File Name: /opt/backup/full/testfull_TEST_20120610_11

File Name: /opt/backup/full/testfull_TEST_20120610_12

File Name: /opt/backup/full/testarch_TEST_20120610_10

File Name: /opt/backup/full/testarch_TEST_20120610_13

File Name: /opt/backup/1_25_785596008.dbf

File Name: /opt/backup/testfull_TEST_20120610_7

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /opt/backup/testarch_TEST_20120610_8

File Name: /opt/backup/testarch_TEST_20120610_1

File Name: /opt/backup/testfull_TEST_20120610_2

File Name: /opt/backup/testarch_TEST_20120610_4

File Name: /opt/backup/1_24_785596008.dbf

File Name: /opt/backup/1_23_785596008.dbf

File Name: /opt/backup/testfull_TEST_20120610_6

File Name: /opt/backup/testfull_TEST_20120610_3

File Name: /opt/backup/testarch_TEST_20120610_5

File Name: /opt/backup/full/testfull_TEST_20120610_11

File Name: /opt/backup/full/testfull_TEST_20120610_12

File Name: /opt/backup/full/testarch_TEST_20120610_10

File Name: /opt/backup/full/testarch_TEST_20120610_13

File Name: /opt/backup/1_25_785596008.dbf

File Name: /opt/backup/testfull_TEST_20120610_7

RMAN> restore database;

Starting restore at 15-JUN-12

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 00001 to /opt/oracle/oradata/test/system01.dbf

restoring datafile 00002 to /opt/oracle/oradata/test/undotbs01.dbf

restoring datafile 00003 to /opt/oracle/oradata/test/sysaux01.dbf

restoring datafile 00004 to /opt/oracle/oradata/test/users01.dbf

channel ORA_DISK_1: reading from backup piece /opt/backup/full/testfull_TEST_20120610_11

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/backup/full/testfull_TEST_20120610_11 tag=TESTDB

channel ORA_DISK_1: restore complete, elapsed time: 00:00:16

Finished restore at 15-JUN-12

RMAN> recover database;

Starting recover at 15-JUN-12

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 23 is already on disk as file /opt/backup/1_23_785596008.dbf

archive log thread 1 sequence 24 is already on disk as file /opt/oracle/oradata/test/redo03.log

archive log thread 1 sequence 25 is already on disk as file /opt/oracle/oradata/test/redo01.log

archive log thread 1 sequence 26 is already on disk as file /opt/oracle/oradata/test/redo02.log

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=21

channel ORA_DISK_1: reading from backup piece /opt/backup/full/testarch_TEST_20120610_10

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/backup/full/testarch_TEST_20120610_10 tag=TESTDB

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

archive log filename=/opt/backup/1_21_785596008.dbf thread=1 sequence=21

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=22

channel ORA_DISK_1: reading from backup piece /opt/backup/full/testarch_TEST_20120610_13

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/backup/full/testarch_TEST_20120610_13 tag=TESTDB

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

archive log filename=/opt/backup/1_22_785596008.dbf thread=1 sequence=22

archive log filename=/opt/backup/1_23_785596008.dbf thread=1 sequence=23

archive log filename=/opt/oracle/oradata/test/redo03.log thread=1 sequence=24

archive log filename=/opt/oracle/oradata/test/redo01.log thread=1 sequence=25

archive log filename=/opt/oracle/oradata/test/redo02.log thread=1 sequence=26

media recovery complete, elapsed time: 00:00:33

Finished recover at 15-JUN-12

RMAN> alter database open resetlogs;

database opened

RMAN> exit

SQL> select count(*) from t;

COUNT(*)

----------

8