控制文件和REDOLOG同时损坏的ORACLE数据库恢复一例

 控制文件和REDOLOG同时损坏的ORACLE数据库恢复一例  一个客户打电话给我，说他们一个测试数据库起不来了，让我过去看看，
到客户现场发现数据库已经DOWN了，试着启动一下
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Oct 7 10:33:29 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

ORACLE instance started.

Total System Global Area 2365587456 bytes
Fixed Size                  2060096 bytes
Variable Size            1526726848 bytes
Database Buffers          822083584 bytes
Redo Buffers               14716928 bytes
Database mounted.
ORA-16038: log 2 sequence# 454 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: '/home/oradata/test/redo02.log'
这种情况下通常是清除一下归档状态就可以了，本来以为问题很简单，启动数据库到MOUNT状态
ORACLE instance shut down.
SQL> SQL>
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 2365587456 bytes
Fixed Size                  2060096 bytes
Variable Size            1526726848 bytes
Database Buffers          822083584 bytes
Redo Buffers               14716928 bytes
Database mounted.
SQL> select * from v$log;
 

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ------------
         1          1        456   52428800          1 NO  CURRENT
     24058954 18-SEP-08

         3          1        455   52428800          1 NO  INACTIVE
     23980807 18-SEP-08

         2          1        454   52428800          1 NO  INACTIVE
     23912312 18-SEP-08
看到状态都是非归档的，清理一下吧SQL> alter database clear unarchived logfile group 1;
SP2-0734: unknown command beginning "alte..." - rest of line ignored.
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-00262: current log 1 of closed thread 1 cannot switch
ORA-00312: online log 1 thread 1: '/home/oradata/test/redo01.log'
ORA-00350: log 2 of instance test (thread 1) needs to be archived
ORA-00312: online log 2 thread 1: '/home/oradata/test/redo02.log'


SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;
alter database clear unarchived logfile group 3
*
ERROR at line 1:
ORA-00221: error on write to control file
ORA-00206: error in writing (block 439, # blocks 1) of control file
ORA-00202: control file: '/home/oradata/test/control02.ctl'
ORA-27072: File I/O error
HP-UX Error: 5: I/O error
Additional information: 4
Additional information: 439
Additional information: -1

清理第一个和第三个归档都出错了，而且说控制文件2I/O有问题，控制文件一共有3份，不可能都坏吧，RECOVER一下试一下，
SQL> recover database until cancel;
Media recovery complete.
SQL> recover database until cancel
Media recovery complete.
recover没有出错，看来是个好兆头，打开数据库看看
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
要我用 RESETLOGS打开数据库，那就打开吧

 
SQL> alter database open resetlogs;

Database altered.
哈哈，没有出错，关闭数据库
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2365587456 bytes
Fixed Size                  2060096 bytes
Variable Size            1526726848 bytes
Database Buffers          822083584 bytes
Redo Buffers               14716928 bytes
Database mounted.
Database opened.
SQL>    
看看归档情况，果然归档号被重设了
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1
SQL> exit

准备用RMAN做个备份，结果又报控制文件错误
ORA-00221: error on write to control file
ORA-00206: error in writing (block 439, # blocks 1) of control file
ORA-00202: control file: '/home/oradata/test/control02.ctl'
ORA-27072: File I/O error
HP-UX Error: 5: I/O error
看来这第二个控制文件还有问题，没关系，将一个好的控制文件覆盖它就是了
先做个备份
cp /home/oradata/test/control02.ctl /oracle/control02.ctl
这么小的文件应该是一闪而过的，可是光标居然就一直停在那里，按CTEL-C也不能中断，只好开了个新的终端KILL了这个CP进程
 ls －l /oracle/control02.ctl，居然才400多个字节
看来这个文件果然有问题，删掉旧的control02.ctl，关闭数据库将当前的control01.ctl拷贝成control02.ctl,再做RMAN，一切正常，在此做个记号，免得以后忘