備份與恢復--利用備份的控制檔案恢復

如果全部控制檔案丟失，但是包含以前控制檔案的備份，這時可以利用備份的控制檔案進行恢復，不過在

恢復後需要以RESETLOGS方式開啟資料庫。

如果重做日誌檔案可用則可以完全恢復，如缺少重做日誌檔案，則只能進行不完全恢復。

[@more@]

下面模擬一下完全恢復的情況：

SQL> alter database backup controlfile to '/u01/backup/control01.bak';

Database altered.

SQL> select * from jglu.a;

ID
----------
1
2

SQL> insert into jglu.a values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into jglu.a values(4);

1 row created.

SQL> alter system switch logfile;

System altered.

SQL> insert into jglu.a values(5);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> host rm /u01/app/oracle/oradata/orcl/control*.*

SQL> host cp /u01/backup/control01.bak /u01/app/oracle/oradata/orcl/control01.ctl

SQL> host cp /u01/backup/control01.bak /u01/app/oracle/oradata/orcl/control02.ctl

SQL> host cp /u01/backup/control01.bak /u01/app/oracle/oradata/orcl/control03.ctl

SQL> startup mount
ORACLE instance started.

Total System Global Area 335544320 bytes
Fixed Size 1219280 bytes
Variable Size 117441840 bytes
Database Buffers 213909504 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 687057 generated at 01/13/2009 10:03:29 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_29_675592897.dbf
ORA-00280: change 687057 for thread 1 is in sequence #29

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 690351 generated at 01/14/2009 02:27:49 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_30_675592897.dbf
ORA-00280: change 690351 for thread 1 is in sequence #30
ORA-00278: log file '/u01/app/oracle/admin/orcl/archive/1_29_675592897.dbf' no
longer needed for this recovery

ORA-00279: change 690354 generated at 01/14/2009 02:27:55 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_31_675592897.dbf
ORA-00280: change 690354 for thread 1 is in sequence #31
ORA-00278: log file '/u01/app/oracle/admin/orcl/archive/1_30_675592897.dbf' no
longer needed for this recovery

ORA-00279: change 690367 generated at 01/14/2009 02:28:14 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_32_675592897.dbf
ORA-00280: change 690367 for thread 1 is in sequence #32
ORA-00278: log file '/u01/app/oracle/admin/orcl/archive/1_31_675592897.dbf' no
longer needed for this recovery

ORA-00308: cannot open archived log
'/u01/app/oracle/admin/orcl/archive/1_32_675592897.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

最後出現的錯誤是由於SHUTDOWN ABORT後，使用備份的控制檔案造成的。Oracle需要應用當前的重做日誌

檔案來進行恢復，但是由於使用了備份的控制檔案，備份之後的歸檔日誌資訊丟失，因此，這時需要手工

輸入需要恢復的聯機重做日誌檔案

其實這個時候是可以透過REDO的迴圈使用推斷出最後需要使用的聯機日誌的。

SQL> SELECT GROUP#, SEQUENCE#, STATUS FROM V$LOG;

GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 29 CURRENT
3 28 INACTIVE
2 27 INACTIVE

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 690367 generated at 01/14/2009 02:28:14 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_32_675592897.dbf
ORA-00280: change 690367 for thread 1 is in sequence #32

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl/redo01.log
Log applied.
Media recovery complete.

提示需要32的歸檔日誌，根據V$LOG可以推算出30為redo02.log,31為redo03.log,32為redo01.log.

SQL> ALTER DATABASE OPEN RESETLOGS;

資料庫已更改。

恢復到這裡已經完成，現在需要做的是馬上進行資料庫的備份。

更多詳細類似此種恢復的辦法如下：

恢復之利用備份在所有控制檔案丟失情況下恢復（一）：控制檔案丟失單聯機日誌可用的恢復。

恢復之利用備份在所有控制檔案丟失情況下恢復（二）：控制檔案丟失，聯機日誌不可用，但資料檔案是最新的情況。

恢復之利用備份在所有控制檔案丟失情況下恢復（三）：控制檔案丟失，聯機日誌可用，但資料檔案來自備份。

恢復之利用備份在所有控制檔案丟失情況下恢復（四）：控制檔案丟失，聯機日誌丟失，利用備份資料檔案恢復資料庫。