Oracle Redo(重做日志) 模拟故障和恢复

最新推荐文章于 2021-04-13 21:57:13 发布
最新推荐文章于 2021-04-13 21:57:13 发布
阅读量181 收藏
点赞数
文章标签： 数据库
..............[@more@]

- 恢复重做日志
重做日志用于记录数据库变化。当执行了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/

clare0807
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
（连载）（完结篇）一个关于ORACLE数据库的虚拟工程（九）--模拟故障
cd4836的博客
08-30 390
模拟rac2故障 把rac2关机，模拟一台主机坏了，然后在rac1上，可以看到rac2没有了，但是rac1还是继续在工作； [root@rac1 ~]# crsctl stat res -t -------------------------------------------------------------------------------- NAME TARGE...
查看oracle redo每天切换次数,Oracle redo 日志切换时间频率
weixin_36366239的博客
04-12 2615
DB: 11.2.0.3.0查看Oracle的redo日志切换频率两条SQL，原理是一样的，第二个用到了统计函数时间单位：分钟方法一、select * from v$log　a　where a.THREAD# = 1 ;set line 200select b.SEQUENCE#, b.FIRST_TIME,a.SEQUENCE#,a.FIRST_TIME,round(((a.FIRST_TIME...
Oracle控制文件的移动、添加、损坏故障模拟修复
weixin_34191734的博客
11-15 168
oracle数据库中的控制文件是二进制文件，其记录了数据库名、数据库标识符、数据库创建时间、表空间信息、重做日志文件历史、归档日志文件位置和状态信息、日志序列号、检查点、备份状态信息和位置。oracle初始安装默认会生成3个控制文件（windows下oracle会生成3个，linux下好像是1个）；查找控制文件物理位置show parameter co...
oracle模拟故障-02 热备及恢复
weixin_34112181的博客
01-12 121
v$recovery_log:在恢复时需要使用的归档日志信息，当一个数据文件需要恢复的时候，这里面才会有信息。 v$archived_log:包括所有的归档日志组信息。 v$recover_file:记录这所有需要被恢复的文件，是从控制文件得来的。要保证控制文件是最新的。 恢复命令： 在mount状态下，可以使用recover database或者recover...
【oracle】模拟故障 - 参数修改导致无法启动oracle
weixin_33884611的博客
05-30 133
问题：把spfile中的processes参数改成10，后出现无法启动oracle数据库。SQL> alter system set processes = 10 scope=spfile;把processes参数改成10后，重启数据库后无法启动：ORA-03113: 通信通道的文件结尾ORA-00020: No more process state objectsav...
模拟“ORA-30012"故障测试
congyuji4066的博客
09-16 98
1、模拟故障： [oracle@dg1 dbs]$ cp -r initdg1.ora /oracle/initdg1.ora[oracle@dg1 dbs]$ mv spfiledg1.ora spfiledg1.oraba...
第七章 重做日志与归档日志 （一） 重做日志
lihan7的博客
09-04 1938
  实验说明Oracle重做日志与归档日志的作用 2012年11月21日 13:41:25 阅读数：11425更多 个人分类： Oracle备份恢复 版权声明：随意复制转载，走捷径懒省事是中国IT的一大悲哀，继承和创新再到原创才是人间正道 https://blog.csdn.net/IndexMan/article/details/8207447 最近在学习Oracle 归档日志模式的管...
模拟Oracle实例崩溃后的恢复
08-27
将当前的重做日志文件复制到安全位置，如`E:\OracleBackup\MYTEST\RmanBackupFile\REDO`目录。 5. **删除和新建实例**： 使用Database Configuration Assistant (DBCA) 删除现有实例，然后新建与原实例名称和SYS...
Oracle数据库日志文件损坏时修复方法的实验研究.pdf
10-10
Oracle数据库的日志文件主要分为两种类型：重做日志文件（Redo Log Files）和归档日志文件（Archive Log Files）。重做日志文件记录了对数据库的所有更改操作，用于实例恢复，即在数据库意外关闭时，可以通过重做...
oracle11G备份恢复讲义
05-31
要深入学习Oracle 11G的备份恢复技术，除了了解理论知识外，还需要进行大量的实践操作，比如实际配置备份策略、执行备份任务和模拟故障进行恢复操作。这样，当真的遇到需要恢复数据的情况时，可以快速有效地操作，...
实例异常之Oracle数据库无响应故障的处理
03-20
Oracle数据库无响应故障，简单地讲就是数据库实例不能响应客户端发起的请求，客户端提交一个SQL后，就一直处于等待数据库实例返回结果的状态。更严重的现象是客户端根本不能连接到数据库，发起一个连接请求后，一直处于等待状态。
多步Undo_Redo的实现
03-01
实现多步Undo和Redo的方法，对于mfc绘图程序来说，这个很必要，而且很实用！
oracle热备切换问题,oracle模拟故障-02 热备及恢复
weixin_39548490的博客
04-07 106
v$recovery_log:在恢复时需要使用的归档日志信息，当一个数据文件需要恢复的时候，这里面才会有信息。v$archived_log:包括所有的归档日志组信息。v$recover_file:记录这所有需要被恢复的文件，是从控制文件得来的。要保证控制文件是最新的。恢复命令：在mount状态下，可以使用recover database或者recover datafile ‘/u01/oracle...
oracle控制文件易错点,【Control File】Oracle个别控制文件丢失故障场景模拟及处理方法...
weixin_35224206的博客
04-13 140
控制文件是数据库的重要组成部分，我们需要掌握有关控制文件在不同故障场景下的恢复方法。本文讨论当部分控制文件丢失场景下的恢复方法。1.查询系统控制文件信息sys@ora10g> col name for a50sys@ora10g> select name from v$controlfile;NAME-------------------------------------------...
模拟当前redo日志损坏
rgb_rgb的博客
09-25 2707
SQL> create table tb1 as select * from dba_tables; Table created. SQL> insert into tb1 select * from tb1; 27206 rows created. SQL>  select * from v$log;     GROUP#    THREAD#  SEQUENCE#      BYTE
oracle检测block损坏,Oracle中模拟及修复数据块损坏
weixin_39785858的博客
04-06 151
Oracle中模拟及修复数据块损坏在Oracle数据库中，我们可能遭遇很多数据损坏的情况，在面对这些情况时如何进行数据恢复是非常重要的。在很多情况下，恢复是极其复杂的，而且需要谨慎，轻率的操作和尝试可能导致数据库的永久损坏。所以我建议在恢复尝试前应当先进行数据备份。对于重要的数据库，如果不能确定恢复步骤和操作后果，建议最好先向专业人士进行咨询，以避免不必要的数据损失。本文通过试验模拟和解决数据块损...
mysql redo日志恢复源码_【恢复，1】redo日志恢复的各种情况
weixin_33900697的博客
02-07 329
Recovering After the Loss of Online Redo Log Files If a media failure has affected the online redo logs of a database, then the appropriate recovery procedure depends on the following considerations: ...
记一次ORACLE数据库redo日志文件损坏故障恢复
u010476488的博客
08-14 571
记一次redo日志文件损坏故障恢复 环境 ORACLE 11G R2 + RAC+ ASM 归档模式 问题描述 数据库1节点状态变为Stuck Archiver，alert日志报Arch found corrupt blocks导致无法读取redo日志，无法完成归档。 解决方法 1.查看redo日志状态 select group#,thread#,status,archived,to_char(f...
oracle redo2 损坏,redo文件损坏，异常问题处理二则
weixin_27374911的博客
04-07 349
第一种情况：asm存储方式，在数据库open步骤时，提示某个磁盘组无法归档某个在线日志，无法写入文件，此时未归档的redo log刚好是需要启动时归档的日志。1.确定一下 归档空间是否已经满了如果alert日志提示，没有可用的目标空间，首先需要考虑这一点2.如果空间释放后(使用rman进行删除)，仍不能启动需要把对应的在线日志清空alter system clear logfile group 2...
Oracle重做日志深度剖析
"这篇文档是Oracle Forensics系列的一部分，主要关注的是Oracle重做日志（Redo Logs）的深入分析。由David Litchfield撰写，发表于2007年的NGS Software Insight Security Research（NISR）出版物中。作者在文中探讨...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值