控制文件全部丢失，无备份，通过异机trace恢复

  • cnaning

    2013-01-22 15:00:01
  • Linux操作系统
  • 原创
 
大家好！今天AningDBA和大家分享的是控制文件全部丢失，无备份，通过其他正常运行的机器生成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.

 

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

 

请使用浏览器的分享功能分享到微信等