控制檔案全部丟失恢復

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.

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