【備份恢復】利用 備份控制檔案到指定目錄下的控制檔案 恢復控制檔案

 備份控制檔案到指定目錄下的控制檔案恢復

1.備份當前控制檔案到指定目錄

SYS@ORA11GR2>alter database backup controlfile to '/home/oracle/control_bak.ctl';

 

Database altered.

——驗證：

SYS@ORA11GR2>!ls /home/oracle/

control_bak.ctl  oradata

 

2.刪除所有控制檔案

SYS@ORA11GR2>show parameter control_files

 

NAME                                 TYPE        VALUE

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

control_files                        string      /u01/app/oracle/oradata/ORA11G

                                                 R2/control01.ctl, /u01/app/ora

                                                 cle/oradata/ORA11GR2/control02

                                                 .ctl

SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/*.ctl

/u01/app/oracle/oradata/ORA11GR2/control01.ctl  /u01/app/oracle/oradata/ORA11GR2/control02.ctl

 

SYS@ORA11GR2>!rm /u01/app/oracle/oradata/ORA11GR2/*.ctl

 

SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/*.ctl

ls: /u01/app/oracle/oradata/ORA11GR2/*.ctl: No such file or directory

 

3.此時控制檔案已丟失，採用abort方式關閉資料庫

SYS@ORA11GR2>shutdown immediate;

Database closed.

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/ORA11GR2/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

SYS@ORA11GR2>shutdown abort;

ORACLE instance shut down.

4.將備份的控制檔案複製到控制檔案原目錄

SYS@ORA11GR2>!cp /home/oracle/control_bak.ctl /u01/app/oracle/oradata/ORA11GR2/control02.ctl

 

SYS@ORA11GR2>!cp /home/oracle/control_bak.ctl /u01/app/oracle/oradata/ORA11GR2/control01.ctl

 

——驗證：

SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/*.ctl

/u01/app/oracle/oradata/ORA11GR2/control01.ctl  /u01/app/oracle/oradata/ORA11GR2/control02.ctl

 

5.資料庫啟動到mount模式

SYS@ORA11GR2>startup mount;

ORACLE instance started.

 

Total System Global Area  830930944 bytes

Fixed Size                  2257800 bytes

Variable Size             503319672 bytes

Database Buffers          322961408 bytes

Redo Buffers                2392064 bytes

Database mounted.

6.嘗試open資料庫，提示使用RESETLOGS or NORESETLOGS選項

SYS@ORA11GR2>alter database open;  

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

7.使用resetlogs選項再次嘗試開啟資料庫，提示需要恢復1號資料檔案

SYS@ORA11GR2>alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

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

ORA-01110: data file 1: '/u01/app/oracle/oradata/ORA11GR2/system01.dbf'

 

8.我們直接recover database，提示使用BACKUP CONTROLFILE選項

SYS@ORA11GR2>recover database;（利用舊版控制檔案資訊去找備份時資料檔案，將其重置）

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

 

9.再次恢復資料庫，如果採用預設方式（直接“回車”）那麼提示無法獲取檔案狀態，恢復失敗

SYS@ORA11GR2>recover database using backup controlfile until cancel;  （利用舊版控制檔案資訊去找備份時資料檔案，將其重置）

ORA-00279: change 1233421 generated at 10/02/2016 15:57:40 needed for thread 1

ORA-00289: suggestion :

/u01/app/FRA/ORA11GR2/archivelog/2016_10_02/o1_mf_1_2_%u_.arc

ORA-00280: change 1233421 for thread 1 is in sequence #2

 

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

 

ORA-00308: cannot open archived log

'/u01/app/FRA/ORA11GR2/archivelog/2016_10_02/o1_mf_1_2_%u_.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

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

ORA-01110: data file 1: '/u01/app/oracle/oradata/ORA11GR2/system01.dbf'

——解決： 

10.指定當前redo路徑進行恢復，成功

SYS@ORA11GR2>select group#,status from v$log where status='CURRENT';

 

    GROUP# STATUS

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

         2 CURRENT

 

SYS@ORA11GR2>select group#,member from v$logfile where group#=2;

 

    GROUP#                        MEMBER

 

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

         2                 /u01/app/oracle/oradata/ORA11GR2/redo02.log

 

SYS@ORA11GR2>recover database using backup controlfile until cancel;
   應用當前正在用的日誌去恢復資料庫                 

ORA-00279: change 1233421 generated at 10/02/2016 15:57:40 needed for thread 1

ORA-00289: suggestion :

/u01/app/FRA/ORA11GR2/archivelog/2016_10_02/o1_mf_1_2_%u_.arc

ORA-00280: change 1233421 for thread 1 is in sequence #2

 

 

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

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

Log applied.

Media recovery complete.

——再次以resetlogs方式啟動資料庫

SYS@ORA11GR2>alter database open resetlogs;

 

Database altered.

SYS@ORA11GR2>select status from v$instance;

 

STATUS

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

OPEN

完成！！！！！！！！！！！！！！！！