Oracle Redo(重做日誌) 模擬故障和恢復
- 恢復重做日誌
重做日誌用於記錄資料庫變化。當執行了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: {
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: {
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: {
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7318139/viewspace-915979/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 恢復重做日誌Oracle
- 恢復重做日誌
- 模擬線上重做日誌被刪除的情況恢復
- 重做日誌的恢復
- redo重做日誌管理
- oracle redo 日誌刪除後的恢復Oracle Redo
- MySQL重做日誌（redo log）MySql
- 【備份恢復】 恢復重做日誌組成員
- rman恢復--丟失聯機重做日誌的恢復
- Oracle11g redo log 建立、新增、刪除（重做日誌組，重做日誌檔案）Oracle
- 【恢復】Redo日誌檔案丟失的恢復
- MySQL重做日誌恢復資料的流程MySql
- 聯機重做日誌丟失的恢復
- 聯機重做日誌檔案的恢復
- Oracle資料庫Redo故障的恢復Oracle資料庫
- 丟失所有重做日誌檔案的恢復例子丟失所有重做日誌檔案的恢復例子如下:
- Oracle recover current redo ORA-00600:[4193] (oracle 故障恢復current redo日誌ORA-00600:[4193]報錯）Oracle
- Oracle重做日誌檔案損壞或丟失後的恢復Oracle
- 重做日誌（redo log）相關總結
- 【備份恢復】恢復 丟失已歸檔重做日誌檔案
- 丟失聯機重做日誌檔案的恢復
- Oracle的重做日誌Oracle
- 利用MySQL日誌模擬恢復資料變化軌跡IIMySql
- 為oracle新增重做日誌組及重做日誌成員Oracle
- 【備份與恢復】恢復受損的複用聯機重做日誌檔案
- 【REDO】重做日誌檔案（redo log files）管理（增，刪，改，查，切）
- 【REDO】刪除REDO LOG重做日誌組後需要手工刪除對應的日誌檔案
- oracle日誌錯誤恢復(轉)Oracle
- oracle線上調整重做日誌Oracle
- Oracle重做日誌調整技巧Oracle
- 重做日誌
- 【故障恢復】【驚魂】ORACLE聯機日誌檔案無故全部消失Oracle
- 聯機重做日誌、歸檔日誌、備用重做日誌
- 備份恢復之redo日誌組member成員丟失
- 【EM】資料表誤刪除故障模擬及恢復
- 【聽海日誌】之ORACLE恢復案例Oracle
- Oracle重做日誌檔案基礎Oracle
- oracle 聯機重做日誌檔案Oracle