控制檔案全部丟失，無備份，通過異機trace恢復

1.1.  檢視資料庫狀態

 SYS@ANING> select status from v$instance;

STATUS

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

OPEN

1.2.  刪除全部控制檔案

SYS@ANING> !rm -rf /u01/app/oracle/oradata/ANING/disk1/control01.ctl

SYS@ANING> !rm -rf /u01/app/oracle/oradata/ANING/disk2/control02.ctl

SYS@ANING> !rm -rf /u01/app/oracle/oradata/ANING/disk3/control03.ctl   

1.3.  檢查控制檔案是否存在

SYS@ANING> !ls -al /u01/app/oracle/oradata/ANING/disk1

total 1730756

drwxr-xr-x 2 oracle oinstall      4096 Dec 17 13:40 .

drwxr-xr-x 8 oracle oinstall      4096 Dec 17 13:29 ..

-rw-r----- 1 oracle oinstall 419438592 Dec 17 13:39 EXAMPLE01.dbf

-rw-r----- 1 oracle oinstall  41951232 Dec 17 13:39 INDX01.dbf

-rw-r----- 1 oracle oinstall  50339840 Dec 17 13:39 OLTP01.dbf

-rw-r----- 1 oracle oinstall 104858112 Dec 17 13:39 redo01.log

-rw-r----- 1 oracle oinstall 104858112 Dec 17 13:39 redo02.log

-rw-r----- 1 oracle oinstall 104858112 Dec 17 13:39 redo03.log

-rw-r----- 1 oracle oinstall 104858112 Dec 17 13:39 redo04_a.log

-rw-r----- 1 oracle oinstall 104858112 Dec 17 13:39 redo05_a.log

-rw-r----- 1 oracle oinstall  52436992 Dec 17 13:39 sysaux01.dbf

-rw-r----- 1 oracle oinstall 357310464 Dec 17 13:39 system01.dbf

-rw-r----- 1 oracle oinstall  20979712 Dec 14 15:16 temp01.dbf

-rw-r----- 1 oracle oinstall  10493952 Dec 15 21:04 TEMP1_01.dbf

-rw-r----- 1 oracle oinstall  10493952 Dec 14 15:34 TEMP2_01.dbf

-rw-r----- 1 oracle oinstall  50339840 Dec 17 13:39 TOOLS01.dbf

-rw-r----- 1 oracle oinstall 209723392 Dec 17 13:39 undotbs01.dbf

-rw-r----- 1 oracle oinstall  63971328 Dec 17 13:39 USERS01.dbf

SYS@ANING> !ls -al /u01/app/oracle/oradata/ANING/disk2

total 512548

drwxr-xr-x 2 oracle oinstall      4096 Dec 17 13:41 .

drwxr-xr-x 8 oracle oinstall      4096 Dec 17 13:29 ..

-rw-r----- 1 oracle oinstall 104858112 Dec 17 13:39 redo01_b.log

-rw-r----- 1 oracle oinstall 104858112 Dec 17 13:40 redo02_b.log

-rw-r----- 1 oracle oinstall 104858112 Dec 17 13:39 redo03_b.log

-rw-r----- 1 oracle oinstall 104858112 Dec 17 13:39 redo04_b.log

-rw-r----- 1 oracle oinstall 104858112 Dec 17 13:39 redo05_b.log

SYS@ANING> !ls -al /u01/app/oracle/oradata/ANING/disk3

total 12

drwxr-xr-x 2 oracle oinstall 4096 Dec 17 13:41 .

drwxr-xr-x 8 oracle oinstall 4096 Dec 17 13:29 ..

-rw-r--r-- 1 oracle oinstall 1350 Dec 14 15:52 sqlnet.log

1.4停止資料庫

SYS@ANING> shutdown abort

ORACLE instance shut down.

SYS@ANING>

1.5啟動資料庫

SYS@ANING> startup

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              88081808 bytes

Database Buffers          222298112 bytes

Redo Buffers                2973696 bytes

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

1.6分析錯誤資訊

檢視alert日誌

ALTER DATABASE   MOUNT

Mon Dec 17 13:44:21 2012

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

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Mon Dec 17 13:44:21 2012

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

從alert可以看出，很明顯是因為控制檔案丟失導致的資料庫不能mount

那我們應該如何解決呢？

alter database backup controlfile to trace這個相信大家都不陌生，在學習控制檔案結構時經常會用到。

在我們的場景下如果用呢？控制檔案全部丟失肯定是不能使用這個方法啦，但是我們可以在其他執行正常的機器上使用這個方法生成異機的trace檔案，然後根據恢復目標機的資料檔案手動建立控制檔案

1.7 生成異機控制檔案trace

SYS@ANINGDBA> alter database backup controlfile to trace  as '/home/oracle/ctl.ctl';

1.8選擇resetlogs，按照恢復目標機資料檔案編輯生成控制檔案的指令碼

CREATE CONTROLFILE REUSE DATABASE "ANING" RESETLOGS FORCE LOGGING ARCHIVELOG

     MAXLOGFILES 16

     MAXLOGMEMBERS 3

     MAXDATAFILES 100

     MAXINSTANCES 8

     MAXLOGHISTORY 292

 LOGFILE

   GROUP 1 '/u01/app/oracle/oradata/ANING/disk1/redo01.log' SIZE 100M,'/u01/app/oracle/oradata/ANING/disk2/redo01_b.log'  SIZE 100M,

   GROUP 2 '/u01/app/oracle/oradata/ANING/disk1/redo02.log' SIZE 100M,'/u01/app/oracle/oradata/ANING/disk2/redo02_b.log'  SIZE 100M,

   GROUP 3 '/u01/app/oracle/oradata/ANING/disk1/redo03.log' SIZE 100M,'/u01/app/oracle/oradata/ANING/disk2/redo03_b.log'  SIZE 100M,

   GROUP 4 '/u01/app/oracle/oradata/ANING/disk1/redo04_a.log' SIZE 100M,'/u01/app/oracle/oradata/ANING/disk2/redo04_b.log'  SIZE 100M,

   GROUP 5 '/u01/app/oracle/oradata/ANING/disk1/redo05_a.log' SIZE 100M,'/u01/app/oracle/oradata/ANING/disk2/redo05_b.log'  SIZE 100M

 DATAFILE

   '/u01/app/oracle/oradata/ANING/disk1/system01.dbf',

   '/u01/app/oracle/oradata/ANING/disk1/undotbs01.dbf',

   '/u01/app/oracle/oradata/ANING/disk1/sysaux01.dbf',

   '/u01/app/oracle/oradata/ANING/disk1/USERS01.dbf',

   '/u01/app/oracle/oradata/ANING/disk1/EXAMPLE01.dbf',

   '/u01/app/oracle/oradata/ANING/disk1/INDX01.dbf',

   '/u01/app/oracle/oradata/ANING/disk1/OLTP01.dbf',

   '/u01/app/oracle/oradata/ANING/disk1/TOOLS01.dbf'

 CHARACTER SET US7ASCII

 ;

1.9檢視恢復目標機的狀態

SYS@ANING> select status from v$instance;

STATUS

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

STARTED

1.10 執行建立控制檔案指令碼

SYS@ANING> CREATE CONTROLFILE REUSE DATABASE "ANING" RESETLOGS FORCE LOGGING ARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 '/u01/app/oracle/oradata/ANING/disk1/redo01.log' SIZE 100M,'/u01/app/oracle/oradata/ANING/disk2/redo01_b.log'  SIZE 100M,

  9    GROUP 2 '/u01/app/oracle/oradata/ANING/disk1/redo02.log' SIZE 100M,'/u01/app/oracle/oradata/ANING/disk2/redo02_b.log'  SIZE 100M,

 10    GROUP 3 '/u01/app/oracle/oradata/ANING/disk1/redo03.log' SIZE 100M,'/u01/app/oracle/oradata/ANING/disk2/redo03_b.log'  SIZE 100M,

 11    GROUP 4 '/u01/app/oracle/oradata/ANING/disk1/redo04_a.log' SIZE 100M,'/u01/app/oracle/oradata/ANING/disk2/redo04_b.log'  SIZE 100M,

 12    GROUP 5 '/u01/app/oracle/oradata/ANING/disk1/redo05_a.log' SIZE 100M,'/u01/app/oracle/oradata/ANING/disk2/redo05_b.log'  SIZE 100M

 13  DATAFILE

 14    '/u01/app/oracle/oradata/ANING/disk1/system01.dbf',

 15    '/u01/app/oracle/oradata/ANING/disk1/undotbs01.dbf',

 16    '/u01/app/oracle/oradata/ANING/disk1/sysaux01.dbf',

 17    '/u01/app/oracle/oradata/ANING/disk1/USERS01.dbf',

 18    '/u01/app/oracle/oradata/ANING/disk1/EXAMPLE01.dbf',

 19    '/u01/app/oracle/oradata/ANING/disk1/INDX01.dbf',

 20    '/u01/app/oracle/oradata/ANING/disk1/OLTP01.dbf',

 21    '/u01/app/oracle/oradata/ANING/disk1/TOOLS01.dbf'

 22  CHARACTER SET US7ASCII

 23  ;

Control file created.

1.11 使用新建立的控制檔案恢復

SYS@ANING> recover database using backup controlfile until cancel;

ORA-00279: change 219171 generated at 12/17/2012 13:39:12 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/oradata/ANING/arch/1_17_802018928.dbf

ORA-00280: change 219171 for thread 1 is in sequence #17

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

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

ORA-00310: archived log contains sequence 14; sequence 17 required

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

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/ANING/disk1/system01.dbf'

SYS@ANING> recover database using backup controlfile until cancel;

ORA-00279: change 219171 generated at 12/17/2012 13:39:12 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/oradata/ANING/arch/1_17_802018928.dbf

ORA-00280: change 219171 for thread 1 is in sequence #17

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

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

Log applied.

Media recovery complete.

1.12嘗試開啟資料庫

SYS@ANING> alter database open ;

alter database open

*

ERROR at line 1:

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

SYS@ANING> alter database open resetlogs;

Database altered.

1.13檢查資料庫狀態

SYS@ANING> select status from v$instance;

STATUS

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

OPEN

1.14新增臨時檔案

SYS@ANING> ALTER TABLESPACE TEMPTS1 ADD TEMPFILE '/u01/app/oracle/oradata/ANING/disk1/temp01.dbf'

  2       SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Tablespace altered.

到此，我們看到手動建立控制檔案恢復成功，資料庫正常。