本文章是关于 联机日志 恢复的实验。
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
恢复成功。