控制文件全部丢失恢复

控制文件全部丢失， 无备份，通过其他正常运行的机器生成 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.

到此，我们看到手动创建控制文件恢复成功，数据库正常。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24558279/viewspace-753838/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24558279/viewspace-753838/