- 恢复重做日志
重做日志用于记录数据库变化。当执行了DML /DDL 操作之后,后台进程LGWR 会将这些操作的信息记载到重做日志中。
- 日志组的某个日志成员损坏
如果某个日志组的一个日志成员出现介质失败,数据库仍然可以正常工作。
- 增加日志MEMBER ,以方便可以模拟 故障
SQL> desc v$logfile
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(257)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
SQL> select group#,member from v$logfile;
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
3
/testdata/oradata/testdb/redo03.log
2
/testdata/oradata/testdb/redo02.log
1
/testdata/oradata/testdb/redo01.log
SQL> alter database add logfile member '/testdata/oradata/testdb/redo01_2.log' ;
Database altered.
SQL> alter database add logfile member '/testdata/oradata/testdb/redo02_2.log' ;
Database altered.
SQL> alter database add logfile member '/testdata/oradata/testdb/redo03_2.log' ;
Database altered.
SQL> select group#,member from v$logfile;
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
3
/testdata/oradata/testdb/redo03.log
2
/testdata/oradata/testdb/redo02.log
1
/testdata/oradata/testdb/redo01.log
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
1
/testdata/oradata/testdb/redo01_2.log
2
/testdata/oradata/testdb/redo02_2.log
3
/testdata/oradata/testdb/redo03_2.log
6 rows selected.
- 模拟 GROUP 1 的MEMBER 2 介质失败,并进行处理
SQL> desc v$logfile
Name Null? Type
----------------------------------------------------- -------- ----------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(257)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
SQL> select status,member from v$logfile;
GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------
3
/testdata/oradata/testdb/redo03.log
2
/testdata/oradata/testdb/redo02.log
1 STALE
/testdata/oradata/testdb/redo01.log
GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------
1
/testdata/oradata/testdb/redo01_2.log
2
/testdata/oradata/testdb/redo02_2.log
3
/testdata/oradata/testdb/redo03_2.log
6 rows selected.
-- 删除redo01_2.log ,模拟介质失败
$ rm redo01_2.log
$ exit
SQL> select member from v$logfile
2 where status ='INVALID';
MEMBER
--------------------------------------------------------------------------------
/testdata/oradata/testdb/redo01_2.log
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile member '/testdata/oradata/testdb/redo01_2.log';
Database altered.
SQL> select member from v$logfile
2 where status ='INVALID';
no rows selected
$ vi alert_testdb.log
db_file_multiblock_read_count= 16
Wed May 23 10:49:34 2007
Errors in file /oradev2/app/oracle/admin/testdb/bdump/testdb_arc1_946200.trc:
ORA-00313: open failed for members of log group 1 of thread 1
Wed May 23 10:49:34 2007
Errors in file /oradev2/app/oracle/admin/testdb/bdump/testdb_arc1_946200.trc:
ORA-00313: open failed for members of log group 1 of thread 1
Wed May 23 10:49:50 2007
alter database drop logfile member '/testdata/oradata/testdb/redo01_2.log'
Wed May 23 10:49:51 2007
Completed: alter database drop logfile member '/testdata/oradata/testdb/redo01_2
.log'
SQL> alter database add logfile member '/testdata/oradata/testdb/redo01_2.log' to group 1;
- 非活动日志组的所有日志成员全部损坏
非活动日志组是指处于INACTIVE 状态的日志组,当非活动日志组的所有日志成员全部出现介质失败时,如果数据库
处于关闭状态,那么在打开数据库时会显示错误信息,如果数据库处于OPEN 状态,那么当切换到该日志组时,数据库将
会处于等待状态。
- 在OPEN 状态下非活动日志组的所有日志成员全部损坏
在数据库处于OPEN 状态时,如果非活动日志组的所有日志成员全部出现介质失败,那么数据库仍然可以正常工作。
当将来切换到该日志时,因为其内容不能被归档,所以后台进行LGWR 会处于等到状态。为了使后台进程LGWR 可以
继续工作,DBA 要清除该日志组
SQL》 alter database clear unarchived logfile group 1;
当执行了以上命令后,ORACDLE 会重新创建日志组1 的所有成员,LGWR 也可以正常工作。但因为日志组内容没有
被归档,所以会导致过去的数据文件备份不能使用。。
[testdb:oratest] /oradev2/home> sqlplus t/test
SQL*Plus: Release 10.2.0.3.0 - Production on Wed May 23 11:15:18 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select count(*) from t;
COUNT(*)
----------
300000
SQL> col member format a40
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ----------------------------------------
3 /testdata/oradata/testdb/redo03.log
2 /testdata/oradata/testdb/redo02.log
1 /testdata/oradata/testdb/redo01.log
1 /testdata/oradata/testdb/redo01_2.log
2 /testdata/oradata/testdb/redo02_2.log
3 /testdata/oradata/testdb/redo03_2.log
6 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 11 52428800 2 NO CURRENT
1271881 23-MAY-07
2 1 9 52428800 2 YES ACTIVE
1270886 23-MAY-07
3 1 10 52428800 2 YES ACTIVE
1271878 23-MAY-07
SQL> !
$ pwd
/oradev2/home
$ cd /testdata/oradata/testdb
$ rm redo03*
$ exit
SQL>
SQL> select count(*) from t.t;
COUNT(*)
----------
300000
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 11 52428800 2 YES ACTIVE
1271881 23-MAY-07
2 1 12 52428800 2 NO CURRENT
1271984 23-MAY-07
3 1 10 52428800 2 YES INACTIVE
1271878 23-MAY-07
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 14 52428800 2 NO CURRENT
1272007 23-MAY-07
2 1 12 52428800 2 YES ACTIVE
1271984 23-MAY-07
3 1 13 52428800 2 NO ACTIVE
1271992 23-MAY-07
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 14 52428800 2 NO ACTIVE
1272007 23-MAY-07
2 1 15 52428800 2 NO CURRENT
1272015 23-MAY-07
3 1 13 52428800 2 NO ACTIVE
1271992 23-MAY-07
-- 此时日志切换HANG 住
SQL> alter system switch logfile;
^C alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> !
$ cd $ORACLE_BASE/admin/testdb/bdump
$ vi alert_testdb.log
Wed May 23 11:22:42 2007
Errors in file /oradev2/app/oracle/admin/testdb/bdump/testdb_arc0_901132.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/testdata/oradata/testdb/redo03_2.log'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/testdata/oradata/testdb/redo03.log'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
~
~
~
~
~
~
~
~
~
~
~
$ exit
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 14 52428800 2 NO ACTIVE
1272007 23-MAY-07
2 1 15 52428800 2 NO CURRENT
1272015 23-MAY-07
3 1 13 52428800 2 NO INACTIVE
1271992 23-MAY-07
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> !
$ cd /testdata/oradata/testdb
- 自动生产了 新的日志成员
$ ls -rtl redo03*
-rw-r----- 1 oratest dba 52429312 May 23 11:24 redo03_2.log
-rw-r----- 1 oratest dba 52429312 May 23 11:24 redo03.log
$ exit
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 14 52428800 2 YES INACTIVE
1272007 23-MAY-07
2 1 15 52428800 2 NO CURRENT
1272015 23-MAY-07
3 1 0 52428800 2 YES UNUSED
1271992 23-MAY-07
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 14 52428800 2 YES INACTIVE
1272007 23-MAY-07
2 1 15 52428800 2 YES ACTIVE
1272015 23-MAY-07
3 1 16 52428800 2 NO CURRENT
1272204 23-MAY-07
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 17 52428800 2 NO CURRENT
1272208 23-MAY-07
2 1 15 52428800 2 YES ACTIVE
1272015 23-MAY-07
3 1 16 52428800 2 NO ACTIVE
1272204 23-MAY-07
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 17 52428800 2 NO ACTIVE
1272208 23-MAY-07
2 1 18 52428800 2 NO CURRENT
1272216 23-MAY-07
3 1 16 52428800 2 NO INACTIVE
1272204 23-MAY-07
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 17 52428800 2 NO ACTIVE
1272208 23-MAY-07
2 1 18 52428800 2 NO CURRENT
1272216 23-MAY-07
3 1 16 52428800 2 NO INACTIVE
1272204 23-MAY-07
SQL> alter system switch logfile;
^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance testdb (thread 1)
ORA-00312: online log 2 thread 1: '/testdata/oradata/testdb/redo02.log'
ORA-00312: online log 2 thread 1: '/testdata/oradata/testdb/redo02_2.log'
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 52428800 2 YES UNUSED
1272208 23-MAY-07
2 1 18 52428800 2 NO CURRENT
1272216 23-MAY-07
3 1 16 52428800 2 NO INACTIVE
1272204 23-MAY-07
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 52428800 2 YES UNUSED
1272208 23-MAY-07
2 1 18 52428800 2 NO CURRENT
1272216 23-MAY-07
3 1 16 52428800 2 NO INACTIVE
1272204 23-MAY-07
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance testdb (thread 1)
ORA-00312: online log 2 thread 1: '/testdata/oradata/testdb/redo02.log'
ORA-00312: online log 2 thread 1: '/testdata/oradata/testdb/redo02_2.log'
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 52428800 2 YES UNUSED
1272208 23-MAY-07
2 1 18 52428800 2 NO CURRENT
1272216 23-MAY-07
3 1 16 52428800 2 NO INACTIVE
1272204 23-MAY-07
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 19 52428800 2 NO CURRENT
1272401 23-MAY-07
2 1 18 52428800 2 NO ACTIVE
1272216 23-MAY-07
3 1 16 52428800 2 NO INACTIVE
1272204 23-MAY-07
SQL> alter system switch logfile;
^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 19 52428800 2 NO ACTIVE
1272401 23-MAY-07
2 1 20 52428800 2 NO CURRENT
1272427 23-MAY-07
3 1 16 52428800 2 NO INACTIVE
1272204 23-MAY-07
SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance testdb (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/testdata/oradata/testdb/redo03.log'
ORA-00312: online log 3 thread 1: '/testdata/oradata/testdb/redo03_2.log'
SQL> alter daabase clear unarchived logfile group 3;
alter daabase clear unarchived logfile group 3
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 19 52428800 2 YES INACTIVE
1272401 23-MAY-07
2 1 20 52428800 2 YES ACTIVE
1272427 23-MAY-07
3 1 21 52428800 2 NO CURRENT
1272499 23-MAY-07
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL>
SQL>
SQL>
SQL>
SQL> shutown immediate
SP2-0734: unknown command beginning "shutown im..." - rest of line ignored.
SQL> shutdown immediate
^CORA-01013: user requested cancel of current operation
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 2072488 bytes
Variable Size 369098840 bytes
Database Buffers 855638016 bytes
Redo Buffers 14704640 bytes
Database mounted.
ORA-00314: log 3 of thread 1, expected sequence# doesn't match
ORA-00312: online log 3 thread 1: '/testdata/oradata/testdb/redo03.log'
ORA-00312: online log 3 thread 1: '/testdata/oradata/testdb/redo03_2.log'
SQL> select count(*) from t.t;
select count(*) from t.t
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> alter database add logfile
2 ('/testdata/oradata/testdb/redo04.log','/testdata/oradata/testdb/redo04_2.l
og') size 3m;
alter database add logfile
*
ERROR at line 1:
ORA-00336: log file size 6144 blocks is less than minimum 8192 blocks
SQL> ed
Wrote file afiedt.buf
117
^C
?
^D
1 alter database add logfile
2* ('/testdata/oradata/testdb/redo04.log','/testdata/oradata/testdb/redo04_2.l
og') size 3m
SQL> alter database add logfile
2 ('/testdata/oradata/testdb/redo04.log','/testdata/oradata/testdb/redo04_2.l
og') size 10m;
Database altered.
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance testdb (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/testdata/oradata/testdb/redo03.log'
ORA-00312: online log 3 thread 1: '/testdata/oradata/testdb/redo03_2.log'
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance testdb (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/testdata/oradata/testdb/redo03.log'
ORA-00312: online log 3 thread 1: '/testdata/oradata/testdb/redo03_2.log'
SQL> select * from v$log
2 /
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 22 52428800 2 NO INACTIVE
1272505 23-MAY-07
4 1 0 10485760 2 YES UNUSED
0
3 1 21 52428800 2 NO INACTIVE
1272499 23-MAY-07
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
2 1 23 52428800 2 NO CURRENT
1272512 23-MAY-07
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database open;
Database altered.
--- 关闭状态非活动日志组的所有日志成员全部损坏
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 2 52428800 2 NO CURRENT
1275358 23-MAY-07
2 1 0 52428800 2 YES UNUSED
0
3 1 0 10485760 2 YES UNUSED
0
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
4 1 1 10485760 2 YES INACTIVE
1275356 23-MAY-07
SQL> startup force
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 2072488 bytes
Variable Size 369098840 bytes
Database Buffers 855638016 bytes
Redo Buffers 14704640 bytes
Database mounted.
Database opened.
SQL> select * from v$log
2 /
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 2 52428800 2 YES INACTIVE
1275358 23-MAY-07
2 1 3 52428800 2 NO CURRENT
1295497 23-MAY-07
3 1 0 10485760 2 YES UNUSED
0
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
4 1 1 10485760 2 YES INACTIVE
1275356 23-MAY-07
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
$ rm redo04*
$ ls -rtl
$ exit
SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 2072488 bytes
Variable Size 369098840 bytes
Database Buffers 855638016 bytes
Redo Buffers 14704640 bytes
Database mounted.
ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1: '/testdata/oradata/testdb/redo04.log'
ORA-00312: online log 4 thread 1: '/testdata/oradata/testdb/redo04_2.log'
SQL> alter database add logfile ('/testdata/oradata/testdb/redo05.log','/testdat
a/oradata/testdb/redo05_2.log') size 10m;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 2 52428800 2 YES INACTIVE
1275358 23-MAY-07
2 1 3 52428800 2 NO CURRENT
1295497 23-MAY-07
5 1 0 10485760 2 YES UNUSED
0
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
4 1 1 10485760 2 YES INACTIVE
1275356 23-MAY-07
3 1 0 10485760 2 YES UNUSED
0
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database open;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 2 52428800 2 YES INACTIVE
1275358 23-MAY-07
2 1 3 52428800 2 YES INACTIVE
1295497 23-MAY-07
3 1 4 10485760 2 NO CURRENT
1295871 23-MAY-07
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
5 1 0 10485760 2 YES UNUSED
0
- 在关闭状态下当前日志组所有日志成员全部损坏
SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 2072488 bytes
Variable Size 369098840 bytes
Database Buffers 855638016 bytes
Redo Buffers 14704640 bytes
Database mounted.
Database opened.
- 查看当前日志组是3 ,一会模拟删除 组3
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 7 52428800 1 YES INACTIVE
1117645 21-MAY-07
2 1 6 52428800 1 YES INACTIVE
1117644 21-MAY-07
3 1 8 52428800 1 NO CURRENT
1175346 21-MAY-07
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
-- 作个备份
[testdb:oratest] /testdata/oradata/testdb> cp * ../back/
[testdb:oratest] /testdata/oradata/testdb> rm redo03.log
[testdb:oratest] /testdata/oradata/testdb> sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Wed May 23 13:17:41 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 2072488 bytes
Variable Size 369098840 bytes
Database Buffers 855638016 bytes
Redo Buffers 14704640 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/testdata/oradata/testdb/redo03.log'
SQL> startup fore mount
SP2-0714: invalid combination of STARTUP options
SQL> startup force mount
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 2072488 bytes
Variable Size 369098840 bytes
Database Buffers 855638016 bytes
Redo Buffers 14704640 bytes
Database mounted.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 7 52428800 1 YES INACTIVE
1117645 21-MAY-07
3 1 8 52428800 1 NO CURRENT
1175346 21-MAY-07
2 1 6 52428800 1 YES INACTIVE
1117644 21-MAY-07
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
- 做了RESETLOGS ,一定要做个备份!!!
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
$ cp * ../back/
- OPEN 状态下当前日志组所有日志成员全部损坏
当处于OPEN 状态时,如果当前日志组的所有日志成员全部损坏,当后台进程LGWR 将事务变化写入日志组时,例程会自动关闭
$ cp *.dbf ../testdb/
$ exit
SQL> startup mount
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 2072488 bytes
Variable Size 369098840 bytes
Database Buffers 855638016 bytes
Redo Buffers 14704640 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 1181366 generated at 05/23/2007 13:19:37 needed for thread 1
ORA-00289: suggestion : /testdata/arch/1_1_623337546.dbf
ORA-00280: change 1181366 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/testdata/arch/1_1_623337546.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
SQL> recover database until cancel;
ORA-00279: change 1181366 generated at 05/23/2007 13:19:37 needed for thread 1
ORA-00289: suggestion : /testdata/arch/1_1_623337546.dbf
ORA-00280: change 1181366 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel;
ORA-00308: cannot open archived log 'cancel;'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7318139/viewspace-915979/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7318139/viewspace-915979/