1、備份控制檔案；

SQL> alter database backup controlfile to '/u01/backup/ctl_03301534.bk';

Database altered.

2、切換日誌組，並確認控制檔案路徑

SQL> select * from v$logfile;

SQL> select * from v$log;

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select NAME from v$controlfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/control01.ctl

/u01/app/oracle/flash_recovery_area/orcl/control02.ctls

3、模擬刪除控制檔案，並重啟資料庫

SQL> !rm -rf /u01/app/oracle/oradata/orcl/control01.ctl

SQL> !ls -l /u01/app/oracle/oradata/orcl/control01.ctl

ls: /u01/app/oracle/oradata/orcl/control01.ctl: No such file or directory

SQL> startup force

ORACLE instance started.

Total System Global Area 313860096 bytes

Fixed Size 1336232 bytes

Variable Size 234884184 bytes

Database Buffers 71303168 bytes

Redo Buffers 6336512 bytes

ORA-00205: error in identifying control file, check alert log for more info

4、還原檔案，並裝載資料庫

SQL> !cp /u01/backup/ctl_03301534.bk /u01/app/oracle/oradata/orcl/control01.ctl

SQL> !cp /u01/backup/ctl_03301534.bk /u01/app/oracle/flash_recovery_area/orcl/control02.ctl

SQL> alter database mount;

Database altered.

5、使用備份的控制檔案進行恢復

SQL> recover database using backup controlfile;

ORA-00279: change 1005918 generated at 03/30/2013 13:52:50 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/arch/1_23_794102915.dbf

ORA-00280: change 1005918 for thread 1 is in sequence #23

Specify log: {=suggested | filename | AUTO | CANCEL}

SQL> recover database using backup controlfile;

ORA-00279: change 1005918 generated at 03/30/2013 13:52:50 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/arch/1_23_794102915.dbf

ORA-00280: change 1005918 for thread 1 is in sequence #23

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/orcl/redo01.log

ORA-00326: log begins at change 1006363, need earlier change 1005918

ORA-00334: archived log: '/u01/app/oracle/oradata/orcl/redo01.log'

SQL> /u01/app/oracle/oradata/orcl/redo01.log

SP2-0734: unknown command beginning "/u01/app/o..." - rest of line ignored.

SQL> recover database using backup controlfile;

ORA-00279: change 1005918 generated at 03/30/2013 13:52:50 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/arch/1_23_794102915.dbf

ORA-00280: change 1005918 for thread 1 is in sequence #23

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/orcl/redo01.log

ORA-00326: log begins at change 1006363, need earlier change 1005918

ORA-00334: archived log: '/u01/app/oracle/oradata/orcl/redo01.log'

SQL> /u01/app/oracle/oradata/orcl/redo01.log

SP2-0734: unknown command beginning "/u01/app/o..." - rest of line ignored.

SQL> recover database using backup controlfile;

ORA-00279: change 1005918 generated at 03/30/2013 13:52:50 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/arch/1_23_794102915.dbf

ORA-00280: change 1005918 for thread 1 is in sequence #23

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/orcl/redo01.log

ORA-00326: log begins at change 1006363, need earlier change 1005918

ORA-00334: archived log: '/u01/app/oracle/oradata/orcl/redo01.log'

透過歸檔日誌路徑找到歸檔日誌，傳送未歸檔日誌都是不需要的，則需要去歸檔日誌路徑下獲取檔案。

SQL> show parameter archive

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

archive_lag_target integer 0

log_archive_config string

log_archive_dest string

log_archive_dest_1 string location=/u01/app/oracle/arch

log_archive_dest_10 string

log_archive_dest_11 string

log_archive_dest_12 string

log_archive_dest_13 string

log_archive_dest_14 string

log_archive_dest_15 string

log_archive_dest_16 string

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_17 string

log_archive_dest_18 string

log_archive_dest_19 string

log_archive_dest_2 string

log_archive_dest_20 string

log_archive_dest_21 string

log_archive_dest_22 string

log_archive_dest_23 string

log_archive_dest_24 string

log_archive_dest_25 string

log_archive_dest_26 string

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_27 string

log_archive_dest_28 string

log_archive_dest_29 string

log_archive_dest_3 string

log_archive_dest_30 string

log_archive_dest_31 string

log_archive_dest_4 string

log_archive_dest_5 string

log_archive_dest_6 string

log_archive_dest_7 string

log_archive_dest_8 string

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_9 string

log_archive_dest_state_1 string enable

log_archive_dest_state_10 string enable

log_archive_dest_state_11 string enable

log_archive_dest_state_12 string enable

log_archive_dest_state_13 string enable

log_archive_dest_state_14 string enable

log_archive_dest_state_15 string enable

log_archive_dest_state_16 string enable

log_archive_dest_state_17 string enable

log_archive_dest_state_18 string enable

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_state_19 string enable

log_archive_dest_state_2 string enable

log_archive_dest_state_20 string enable

log_archive_dest_state_21 string enable

log_archive_dest_state_22 string enable

log_archive_dest_state_23 string enable

log_archive_dest_state_24 string enable

log_archive_dest_state_25 string enable

log_archive_dest_state_26 string enable

log_archive_dest_state_27 string enable

log_archive_dest_state_28 string enable

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_state_29 string enable

log_archive_dest_state_3 string enable

log_archive_dest_state_30 string enable

log_archive_dest_state_31 string enable

log_archive_dest_state_4 string enable

log_archive_dest_state_5 string enable

log_archive_dest_state_6 string enable

log_archive_dest_state_7 string enable

log_archive_dest_state_8 string enable

log_archive_dest_state_9 string enable

log_archive_duplex_dest string

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_format string %t_%s_%r.dbf

log_archive_local_first boolean TRUE

log_archive_max_processes integer 4

log_archive_min_succeed_dest integer 1

log_archive_start boolean FALSE

log_archive_trace integer 0

standby_archive_dest string ?/dbs/arch

SQL> !ls -lrt /u01/app/oracle/arch

total 22124

-rw-r----- 1 oracle oinstall 14071808 Oct 14 11:58 1_20_794102915.dbf

-rw-r----- 1 oracle oinstall 391168 Oct 14 12:33 1_21_794102915.dbf

-rw-r----- 1 oracle oinstall 733184 Mar 30 13:52 1_22_794102915.dbf

-rw-r----- 1 oracle oinstall 7396352 Mar 30 15:56 1_23_794102915.dbf

-rw-r----- 1 oracle oinstall 1024 Mar 30 15:56 1_24_794102915.dbf

-rw-r----- 1 oracle oinstall 8704 Mar 30 15:57 1_25_794102915.dbf

SQL> recover database using backup controlfile;

ORA-00279: change 1005918 generated at 03/30/2013 13:52:50 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/arch/1_23_794102915.dbf

ORA-00280: change 1005918 for thread 1 is in sequence #23

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/arch/1_23_794102915.dbf

ORA-00279: change 1006360 generated at 03/30/2013 15:56:21 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/arch/1_24_794102915.dbf

ORA-00280: change 1006360 for thread 1 is in sequence #24

ORA-00278: log file '/u01/app/oracle/arch/1_23_794102915.dbf' no longer needed

for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/arch/1_24_794102915.dbf

ORA-00279: change 1006363 generated at 03/30/2013 15:56:23 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/arch/1_25_794102915.dbf

ORA-00280: change 1006363 for thread 1 is in sequence #25

ORA-00278: log file '/u01/app/oracle/arch/1_24_794102915.dbf' no longer needed

for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/arch/1_25_794102915.dbf

ORA-00279: change 1006379 generated at 03/30/2013 15:57:04 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/arch/1_26_794102915.dbf

ORA-00280: change 1006379 for thread 1 is in sequence #26

ORA-00278: log file '/u01/app/oracle/arch/1_25_794102915.dbf' no longer needed

for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/orcl/redo01.log

ORA-00310: archived log contains sequence 25; sequence 26 required

ORA-00334: archived log: '/u01/app/oracle/oradata/orcl/redo01.log'

SQL> recover database using backup controlfile;

ORA-00279: change 1006379 generated at 03/30/2013 15:57:04 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/arch/1_26_794102915.dbf

ORA-00280: change 1006379 for thread 1 is in sequence #26

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/orcl/redo03.log

ORA-00310: archived log contains sequence 24; sequence 26 required

ORA-00334: archived log: '/u01/app/oracle/oradata/orcl/redo03.log'

SQL> recover database using backup controlfile;

ORA-00279: change 1006379 generated at 03/30/2013 15:57:04 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/arch/1_26_794102915.dbf

ORA-00280: change 1006379 for thread 1 is in sequence #26

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/orcl/redo02.log

Log applied.

Media recovery complete.

SQL>

手工恢復控制檔案

相關文章