通過Snapshot Control File 恢復控制檔案

大家好！

今天AningDBA和大家分享的是通過rman 快照控制檔案恢復控制檔案的方法：

我今天測試使用的oracle版本是10.2.01 由於版本問題，實驗結果可能會存在差異。

 

1.1.  檢視Snapshot Control File位置

RMAN> SHOW SNAPSHOT CONTROLFILE NAME;

 

using target database control file instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_PROD.f'; # default

 

RMAN>

1.2.  檢查Snapshot Control File是否存在

[oracle@secdb1 dbs]$

[oracle@secdb1 dbs]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs

[oracle@secdb1 dbs]$ ll -h snapcf_PROD.f

-rw-r----- 1 oracle oinstall 6.6M Jan  8 23:21 snapcf_PROD.f

[oracle@secdb1 dbs]$

1.3.  Snapshot Control File的官方解釋 

When RMAN needs to resynchronize from a read-consistent version of the control file, it creates a temporary snapshot control file. RMAN needs a snapshot control file only when resynchronizing with the recovery catalog or when making a backup of the current control file.

 

當RMAN需要重新同步，版本控制檔案的讀一致性，它會建立一個臨時快照控制檔案。只有在重新同步恢復目錄或備份當前控制檔案的時候，RMAN需要一個快照控制檔案。

1.4.  模擬控制檔案丟失

1.4.1檢視控制檔案位置

SQL> show parameter control_files;

 

NAME                                 TYPE        VALUE

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

control_files                        string      /u01/app/oracle/oradata/PROD/d

                                                 isk1/control01.ctl, /u01/app/o

                                                 racle/oradata/PROD/disk2/contr

                                                 ol02.ctl, /u01/app/oracle/orad

                                                 ata/PROD/disk3/control03.ctl

SQL>

1.4.2 刪除當前控制檔案

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

SQL>!rm -rf /u01/app/oracle/oradata/PROD/disk2/control02.ctl

SQL>!rm -rf /u01/app/oracle/oradata/PROD/disk3/control03.ctl

 

這裡我們分兩種情況分別處理，第一種情況是在控制檔案丟失資料庫重啟後恢復，第二種是控制檔案丟失資料庫沒有重啟恢復

 

1.5控制檔案丟失資料庫重啟後恢復

1.5.1關閉資料庫

我們發現不能正常關閉，使用abort關閉

SQL> shutdown immediate;

ORA-00210: cannot open the specified control file

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

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SQL> shutdown abort

ORACLE instance shut down.

SQL>

1.5.2啟動資料庫

SQL> startup

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              75498896 bytes

Database Buffers          234881024 bytes

Redo Buffers                2973696 bytes

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

SQL>

1.5.3檢查alert日誌檔案

這裡報找不到控制檔案

Wed Jan 23 17:16:22 2013

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

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Wed Jan 23 17:16:22 2013

ORA-205 signalled during: ALTER DATABASE   MOUNT...

1.5.4把Snapshot Control File 複製到控制檔案目錄

[oracle@secdb1 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs

[oracle@secdb1 dbs]$ cp snapcf_PROD.f /u01/app/oracle/oradata/PROD/disk1/control01.ctl

[oracle@secdb1 dbs]$ cp snapcf_PROD.f /u01/app/oracle/oradata/PROD/disk2/control02.ctl

[oracle@secdb1 dbs]$ cp snapcf_PROD.f /u01/app/oracle/oradata/PROD/disk3/control03.ctl

[oracle@secdb1 dbs]$

1.5.5 mount資料庫

SQL> alter database mount;

 

Database altered.

 

SQL>

1.5.6 open資料庫

open資料庫報錯，需要恢復資料庫

SQL> alter database open;

alter database open

*

ERROR at line 1:

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

 

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

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

SQL>

1.5.7恢復資料庫

SQL> recover database using backup controlfile;

ORA-00279: change 185863 generated at 01/08/2013 23:21:15 needed for thread 1

ORA-00289: suggestion :

/home/oracle/flash/PROD/archivelog/2013_01_23/o1_mf_1_11_%u_.arc

ORA-00280: change 185863 for thread 1 is in sequence #11

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

/u01/app/oracle/oradata/PROD/disk1/redo04_a.log

ORA-00328: archived log ends at change 185845, need later change 185863

ORA-00334: archived log: '/u01/app/oracle/oradata/PROD/disk1/redo04_a.log'

 

SQL> recover database using backup controlfile;

ORA-00279: change 185863 generated at 01/08/2013 23:21:15 needed for thread 1

ORA-00289: suggestion :

/home/oracle/flash/PROD/archivelog/2013_01_23/o1_mf_1_11_%u_.arc

ORA-00280: change 185863 for thread 1 is in sequence #11

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

/u01/app/oracle/oradata/PROD/disk1/redo05_a.log

Log applied.

Media recovery complete.

SQL>

1.5.8 重新open資料庫

SQL> alter database open resetlogs;

 

Database altered.

 

SQL> SQL> SQL>

1.6 控制檔案丟失資料庫沒有重啟恢復

1.6.1執行1.4模擬控制檔案丟失

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

 

SQL> !rm -rf /u01/app/oracle/oradata/PROD/disk2/control02.ctl

 

SQL> !rm -rf /u01/app/oracle/oradata/PROD/disk3/control03.ctl

 

SQL>

1.6.2檢查控制檔案是否存在

[oracle@secdb1 dbs]$ ll /u01/app/oracle/oradata/PROD/disk1/control01.ctl

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

[oracle@secdb1 dbs]$ ll /u01/app/oracle/oradata/PROD/disk2/control02.ctl

ls: /u01/app/oracle/oradata/PROD/disk2/control02.ctl: No such file or directory

[oracle@secdb1 dbs]$ ll /u01/app/oracle/oradata/PROD/disk3/control03.ctl

ls: /u01/app/oracle/oradata/PROD/disk3/control03.ctl: No such file or directory

[oracle@secdb1 dbs]$

1.6.3插入測試資料

控制檔案丟失還可以插入資料

SQL> select * from test;

no rows selected

 

SQL> insert into test values(1);

1 row created.

 

SQL> select * from test;

        ID

----------

         1

 

SQL> commit;

Commit complete.

1.6.4把Snapshot Control File 複製到控制檔案目錄

[oracle@secdb1 dbs]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs

[oracle@secdb1 dbs]$ cp snapcf_PROD.f /u01/app/oracle/oradata/PROD/disk1/control01.ctl

[oracle@secdb1 dbs]$ cp snapcf_PROD.f /u01/app/oracle/oradata/PROD/disk2/control02.ctl

[oracle@secdb1 dbs]$ cp snapcf_PROD.f /u01/app/oracle/oradata/PROD/disk3/control03.ctl

1.6.5檢查控制檔案恢復狀態

[oracle@secdb1 dbs]$ ll /u01/app/oracle/oradata/PROD/disk1/control01.ctl

-rw-r----- 1 oracle oinstall 6832128 Jan 23 17:42 /u01/app/oracle/oradata/PROD/disk1/control01.ctl

[oracle@secdb1 dbs]$ ll /u01/app/oracle/oradata/PROD/disk2/control02.ctl

-rw-r----- 1 oracle oinstall 6832128 Jan 23 17:42 /u01/app/oracle/oradata/PROD/disk2/control02.ctl

[oracle@secdb1 dbs]$ ll /u01/app/oracle/oradata/PROD/disk3/control03.ctl

-rw-r----- 1 oracle oinstall 6832128 Jan 23 17:42 /u01/app/oracle/oradata/PROD/disk3/control03.ctl

[oracle@secdb1 dbs]$

1.6.6測試當前控制檔案是否可用

查詢檢視控制檔案可用，但是alert報錯

SQL> select status from v$instance;

STATUS

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

OPEN

 

SQL> select dbid from v$database;

      DBID

----------

 215959243

1.6.7停止資料庫

SQL> shutdown immediate;

ORA-00600: internal error code, arguments: [2141], [217309148], [0], [], [], [], [], []

SQL>

檢視alert資訊：

Wed Jan 23 21:22:11 2013

Error: Controlfile was changed externally while mounted

       Please check if another Oracle database is running

       and accessing the same controlfile

Wed Jan 23 21:22:11 2013

Errors in file /u01/app/oracle/admin/PROD/udump/prod_ora_5636.trc:

ORA-00600: internal error code, arguments: [2141], [217309148], [0], [], [], [], [], []

Wed Jan 23 21:22:12 2013

Errors in file /u01/app/oracle/admin/PROD/udump/prod_ora_5636.trc:

ORA-00600: internal error code, arguments: [2141], [217309148], [0], [], [], [], [], []

Wed Jan 23 21:22:12 2013

Errors in file /u01/app/oracle/admin/PROD/udump/prod_ora_5636.trc:

ORA-00600: internal error code, arguments: [2141], [217309148], [0], [], [], [], [], []

Wed Jan 23 21:22:12 2013

Errors in file /u01/app/oracle/admin/PROD/udump/prod_ora_5636.trc:

ORA-00600: internal error code, arguments: [2141], [217309148], [0], [], [], [], [], []

 

1.6.8 強制停止資料庫

SQL> shutdown abort

ORACLE instance shut down.

SQL>

 

1.6.9 啟動資料庫

SQL> startup

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              79693200 bytes

Database Buffers          230686720 bytes

Redo Buffers                2973696 bytes

Database mounted.

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

SQL>

1.6.10使用resetlogs啟動資料庫

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

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

1.6.11 recovery資料庫

SQL>  recover database using backup controlfile;

ORA-00279: change 185863 generated at 01/08/2013 23:21:15 needed for thread 1

ORA-00289: suggestion : /home/oracle/flash/PROD/archivelog/2013_01_23/o1_mf_1_11_%u_.arc

ORA-00280: change 185863 for thread 1 is in sequence #11

 

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

/u01/app/oracle/oradata/PROD/disk1/redo05_a.log

ORA-00279: change 207533 generated at 01/08/2013 23:42:43 needed for thread 1

ORA-00289: suggestion : /home/oracle/flash/PROD/archivelog/2013_01_23/o1_mf_1_12_%u_.arc

ORA-00280: change 207533 for thread 1 is in sequence #12

ORA-00278: log file '/u01/app/oracle/oradata/PROD/disk1/redo05_a.log' no longer needed for this recovery

 

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

/u01/app/oracle/oradata/PROD/disk1/redo01.log

ORA-00310: archived log contains sequence 9; sequence 12 required

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

 

SQL> recover database using backup controlfile;

ORA-00279: change 207533 generated at 01/08/2013 23:42:43 needed for thread 1

ORA-00289: suggestion : /home/oracle/flash/PROD/archivelog/2013_01_23/o1_mf_1_12_%u_.arc

ORA-00280: change 207533 for thread 1 is in sequence #12

 

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

/u01/app/oracle/oradata/PROD/disk1/redo02.log

Log applied.

Media recovery complete.

SQL>

1.6.12 重新open資料庫

SQL>  alter database open resetlogs;

Database altered.

SQL>