oracle 联机日志 恢复

 

本文章是关于 联机日志 恢复的实验。

oracle@aoracle ezhou]$ mv redo01.log ./bak
[oracle@aoracle ezhou]$ ls -lrt
total 1516148
drwxr-xr-x 3 oracle oinstall      4096 Sep 28 16:34 patch
-rw-r----- 1 oracle oinstall  52429312 Dec  6 15:40 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Dec  6 15:40 redo01a.rdo
-rw-r----- 1 oracle oinstall  52436992 Dec  6 15:46 temp01.dbf
-rw-r----- 1 oracle oinstall  66854912 Dec  6 15:55 users01.dbf
-rw-r----- 1 oracle oinstall 298852352 Dec  6 15:55 undotbs01.dbf
-rw-r----- 1 oracle oinstall 104865792 Dec  6 15:55 test01.dbf
-rw-r----- 1 oracle oinstall 513810432 Dec  6 15:55 system01.dbf
-rw-r----- 1 oracle oinstall 272637952 Dec  6 15:55 sysaux01.dbf
-rw-r----- 1 oracle oinstall  52429312 Dec  6 15:55 redo03.log
-rw-r----- 1 oracle oinstall  10493952 Dec  6 15:55 example02.dbf
-rw-r----- 1 oracle oinstall 104865792 Dec  6 15:55 example01.dbf
-rw-r----- 1 oracle oinstall   7061504 Dec  6 15:56 control03.ctl
-rw-r----- 1 oracle oinstall   7061504 Dec  6 15:56 control02.ctl
-rw-r----- 1 oracle oinstall   7061504 Dec  6 15:56 control01.ctl
drwxr-xr-x 2 oracle oinstall      4096 Dec  6 16:04 bak

select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- ------------------------------ ---
         3         ONLINE  /u02/ezhou/redo03.log          NO
         2 STALE   ONLINE  /u02/ezhou/redo02.log          NO
         1 INVALID ONLINE  /u02/ezhou/redo01.log          NO
         1 STALE   ONLINE  /u02/ezhou/redo01a.rdo         NO
        

SQL> alter system switch logfile;

System altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- ------------------------------ ---
         3         ONLINE  /u02/ezhou/redo03.log          NO
         2 STALE   ONLINE  /u02/ezhou/redo02.log          NO
         1 INVALID ONLINE  /u02/ezhou/redo01.log          NO
         1         ONLINE  /u02/ezhou/redo01a.rdo         NO

看的出一个redo 损坏，没有影响。

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- -------------------
         1          1         90   52428800          2 NO  CURRENT
      5698589 2011-12-06 16:15:39

         2          1         88   52428800          1 YES INACTIVE
      5675573 2011-12-06 14:37:14

         3          1         89   52428800          1 YES ACTIVE
      5697611 2011-12-06 15:40:24

SQL>
SQL> alter database drop logfile member '/u02/ezhou/redo01.log' ;
alter database drop logfile member '/u02/ezhou/redo01.log'
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
ORA-00312: online log 1 thread 1: '/u02/ezhou/redo01.log'
ORA-00312: online log 1 thread 1: '/u02/ezhou/redo01a.rdo'

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- -------------------
         1          1         90   52428800          2 YES ACTIVE
      5698589 2011-12-06 16:15:39

         2          1         91   52428800          1 NO  CURRENT
      5698804 2011-12-06 16:22:03

         3          1         89   52428800          1 YES INACTIVE
      5697611 2011-12-06 15:40:24
     
看到status current 到其他的group:

SQL> alter database drop logfile member '/u02/ezhou/redo01.log' ;

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- ------------------------------ ---
         3         ONLINE  /u02/ezhou/redo03.log          NO
         2         ONLINE  /u02/ezhou/redo02.log          NO
         1         ONLINE  /u02/ezhou/redo01a.rdo         NO

SQL> alter database add logfile member '/u02/ezhou/redo01.log' to group 1;

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- ------------------------------ ---
         3         ONLINE  /u02/ezhou/redo03.log          NO
         2         ONLINE  /u02/ezhou/redo02.log          NO
         1 INVALID ONLINE  /u02/ezhou/redo01.log          NO
         1         ONLINE  /u02/ezhou/redo01a.rdo         NO
        
这个恢复不用rman.
看到redo01.log 有了：

[oracle@aoracle ezhou]$ ls -lrt
total 1567408
drwxr-xr-x 3 oracle oinstall      4096 Sep 28 16:34 patch
-rw-r----- 1 oracle oinstall  52436992 Dec  6 15:46 temp01.dbf
-rw-r----- 1 oracle oinstall  52429312 Dec  6 16:15 redo03.log
-rw-r----- 1 oracle oinstall  52429312 Dec  6 16:22 redo01a.rdo
-rw-r----- 1 oracle oinstall  66854912 Dec  6 16:27 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 Dec  6 16:27 test01.dbf
-rw-r----- 1 oracle oinstall  10493952 Dec  6 16:27 example02.dbf
-rw-r----- 1 oracle oinstall 104865792 Dec  6 16:27 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 Dec  6 16:28 redo01.log
-rw-r----- 1 oracle oinstall 298852352 Dec  6 16:34 undotbs01.dbf
-rw-r----- 1 oracle oinstall 513810432 Dec  6 16:34 system01.dbf
-rw-r----- 1 oracle oinstall  52429312 Dec  6 16:35 redo02.log
-rw-r----- 1 oracle oinstall   7061504 Dec  6 16:35 control03.ctl
-rw-r----- 1 oracle oinstall   7061504 Dec  6 16:35 control02.ctl
-rw-r----- 1 oracle oinstall   7061504 Dec  6 16:35 control01.ctl
-rw-r----- 1 oracle oinstall 272637952 Dec  6 16:35 sysaux01.dbf

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- ------------------------------ ---
         3         ONLINE  /u02/ezhou/redo03.log          NO
         2         ONLINE  /u02/ezhou/redo02.log          NO
         1 INVALID ONLINE  /u02/ezhou/redo01.log          NO
         1         ONLINE  /u02/ezhou/redo01a.rdo         NO

SQL> alter system switch logfile;

System altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- ------------------------------ ---
         3         ONLINE  /u02/ezhou/redo03.log          NO
         2         ONLINE  /u02/ezhou/redo02.log          NO
         1         ONLINE  /u02/ezhou/redo01.log          NO
         1         ONLINE  /u02/ezhou/redo01a.rdo         NO

======================

下面模拟损坏非当前联机日志组：

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- -------------------
         1          1         93   52428800          2 YES ACTIVE
      5701219 2011-12-06 17:09:30

         2          1         94   52428800          1 NO  CURRENT
      5701303 2011-12-06 17:12:54

         3          1         92   52428800          1 YES ACTIVE
      5699861 2011-12-06 16:41:03

[oracle@aoracle ezhou]$ ls -l re*
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:12 redo01a.rdo
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:12 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:13 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:09 redo03.log
[oracle@aoracle ezhou]$ date
Tue Dec  6 17:13:48 CST 2011
[oracle@aoracle ezhou]$ mv redo01a.rdo ./bak
[oracle@aoracle ezhou]$ mv redo01.log ./bak
[oracle@aoracle ezhou]$ ls -l re*
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:14 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:09 redo03.log
[oracle@aoracle ezhou]$

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  335544320 bytes
Fixed Size                  1219280 bytes
Variable Size              79693104 bytes
Database Buffers          251658240 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u02/ezhou/redo01a.rdo'
ORA-00312: online log 1 thread 1: '/u02/ezhou/redo01.log'

SQL> alter database clear logfile group 1;

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- ------------------------------ ---
         3         ONLINE  /u02/ezhou/redo03.log          NO
         2         ONLINE  /u02/ezhou/redo02.log          NO
         1         ONLINE  /u02/ezhou/redo01.log          NO
         1         ONLINE  /u02/ezhou/redo01a.rdo         NO

[oracle@aoracle ezhou]$ ls -l re*
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:18 redo01a.rdo
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:18 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:16 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:09 redo03.log

SQL> alter database open;

Database altered.

==============================

损坏全部的联机日志：

[oracle@aoracle ezhou]$ ls -l re*
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:33 redo01a.rdo
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:33 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:25 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:25 redo03.log
[oracle@aoracle ezhou]$ mv re* ./bak
[oracle@aoracle ezhou]$ ls -l re*
ls: re*: No such file or directory

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  335544320 bytes
Fixed Size                  1219280 bytes
Variable Size              79693104 bytes
Database Buffers          251658240 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u02/ezhou/redo01a.rdo'
ORA-00312: online log 1 thread 1: '/u02/ezhou/redo01.log'

-------

恢复步骤：
shutdown immediate.

SQL> create pfile from spfile;

File created.

cd $ORACLE_HOME/dbs
会发现：生成了：
-rw-r--r-- 1 oracle oinstall        951 Dec  6 17:39 initezhou.ora

vi initezhou.ora
加入：：_allow_resetlogs_corruption=true

SQL> create spfile from pfile;

File created.

SQL> startup;
ORACLE instance started.

Total System Global Area  335544320 bytes
Fixed Size                  1219280 bytes
Variable Size              79693104 bytes
Database Buffers          251658240 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u02/ezhou/redo01a.rdo'
ORA-00312: online log 1 thread 1: '/u02/ezhou/redo01.log'

SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

[oracle@aoracle ezhou]$ ls -l re*
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:50 redo01a.rdo
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:50 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:51 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Dec  6 17:50 redo03.log
[oracle@aoracle ezhou]$ date
Tue Dec  6 17:52:01 CST 2011

SQL> select status from v$instance;

STATUS
------------
OPEN

恢复成功。