oracle正常关闭 下redo丢失恢复。

今天是2013-08-27，收到一个朋友消息，说是数据库在关闭的情况下，删除了redo.log,无法启动，如下是我的笔记。再次记录一下。

[oracle@oracle-one RHYS]$ ls -ltr
total 4898536
-rw-r-----. 1 oracle oinstall   52429312 Aug 27 13:22 redo02.log
-rw-r-----. 1 oracle oinstall   52429312 Aug 27 13:22 redo01.log
-rw-r-----. 1 oracle oinstall    5251072 Aug 27 13:22 users01.dbf
-rw-r-----. 1 oracle oinstall  524296192 Aug 27 13:22 arch_tbs01.dbf
-rw-r-----. 1 oracle oinstall  209723392 Aug 27 13:22 statspack.dbf
-rw-r-----. 1 oracle oinstall  262152192 Aug 27 13:22 undotbs3.dbf
-rw-r-----. 1 oracle oinstall 1688215552 Aug 27 13:30 test.dbf
-rw-r-----. 1 oracle oinstall   67117056 Aug 27 13:33 temp01.dbf
-rw-r-----. 1 oracle oinstall   31465472 Aug 27 13:38 undotbs1.dbf
-rw-r-----. 1 oracle oinstall  629153792 Aug 27 13:40 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 1457528832 Aug 27 13:40 system01.dbf
-rw-r-----. 1 oracle oinstall    9846784 Aug 27 13:40 control01.ctl
-rw-r-----. 1 oracle oinstall   52429312 Aug 27 13:40 redo03.log
[oracle@oracle-one RHYS]$ mv redo01.log redo01.log_bak
[oracle@oracle-one RHYS]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 27 17:15:07 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  405020672 bytes
Fixed Size                  2213816 bytes
Variable Size             260048968 bytes
Database Buffers          138412032 bytes
Redo Buffers                4345856 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 1935
Session ID: 1 Serial number: 5

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle-one RHYS]$
查看alert日志如下：
Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_lgwr_1915.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/opt/app/oracle/RHYS/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_lgwr_1915.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/opt/app/oracle/RHYS/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_1935.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/opt/app/oracle/RHYS/redo01.log'
USER (ospid: 1935): terminating the instance due to error 313
2013-08-27 17:15:22.637000 +08:00
Instance terminated by USER, pid = 1935
adrci>

SQL> startup mount; 
ORACLE instance started.

Total System Global Area  405020672 bytes
Fixed Size                  2213816 bytes
Variable Size             260048968 bytes
Database Buffers          138412032 bytes
Redo Buffers                4345856 bytes
Database mounted.
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1        742   52428800        512          1 YES INACTIVE              11303012 26-AUG-13     11355835 27-AUG-13
         3          1        743   52428800        512          1 NO  CURRENT               11355835 27-AUG-13   2.8147E+14
         2          1        741   52428800        512          1 YES INACTIVE              11265035 26-AUG-13     11303012 26-AUG-13

SQL>

SQL> alter database clear logfile group 1;

Database altered.

SQL> !                        
[oracle@oracle-one RHYS]$ ls -l
total 4949740
-rw-r-----. 1 oracle oinstall  524296192 Aug 27 17:15 arch_tbs01.dbf
-rw-r-----. 1 oracle oinstall    9846784 Aug 27 17:20 control01.ctl
-rw-r-----. 1 oracle oinstall   52429312 Aug 27 17:20 redo01.log
-rw-r-----. 1 oracle oinstall   52429312 Aug 27 13:22 redo01.log_bak
-rw-r-----. 1 oracle oinstall   52429312 Aug 27 13:22 redo02.log
-rw-r-----. 1 oracle oinstall   52429312 Aug 27 17:15 redo03.log
-rw-r-----. 1 oracle oinstall  209723392 Aug 27 17:15 statspack.dbf
-rw-r-----. 1 oracle oinstall  629153792 Aug 27 17:15 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 1457528832 Aug 27 17:15 system01.dbf
-rw-r-----. 1 oracle oinstall   67117056 Aug 27 13:33 temp01.dbf
-rw-r-----. 1 oracle oinstall 1688215552 Aug 27 17:15 test.dbf
-rw-r-----. 1 oracle oinstall   31465472 Aug 27 17:15 undotbs1.dbf
-rw-r-----. 1 oracle oinstall  262152192 Aug 27 17:15 undotbs3.dbf
-rw-r-----. 1 oracle oinstall    5251072 Aug 27 17:15 users01.dbf
[oracle@oracle-one RHYS]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 27 17:20:32 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> alter database open;

Database altered.

SQL>