Oracle的redo log在各场景下的恢复

Oracle的redo log非常重要,redo log损坏将导致数据库开法开启或数据丢失,针对redo log在各种场景下如何打开或恢复数据库,特别模拟测试说明:

各场景包括如下(共6个场景):

 场景一.非归档下inactive状态的redo 恢复

 场景二.非归档下active状态的redo 恢复

 场景三.非归档下current状态的redo恢复

 场景四.归档模式下inactive状态的redo 恢复

 场景五.归档模式下的active状态的redo 恢复

 场景六.归档模式下的current状态的redo恢复

oracle版本为oracle 11.1.0.7

各场景恢复操作如下:

一.非归档下inactive状态的redo 恢复

session 1:
查看归档模式:---非归档
SQL> archive log list
 Database log mode No Archive Mode
 Automatic archival Disabled
 Archive destination /kttest1-1_data1/emsdev/arch
 Oldest online log sequence 3
 Current log sequence 8

查看log情况:

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
 ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
 1 1 10 104857600 1 NO INACTIVE 1.2790E+13 30-MAY-14
 2 1 13 104857600 1 NO CURRENT 1.2790E+13 30-MAY-14
 3 1 12 104857600 1 NO ACTIVE 1.2790E+13 30-MAY-14

SQL> col member for a70
 SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
 ---------- ------- ------- ----------------------------------------------------------------------
 IS_
 ---
 3 ONLINE /kttest1-1_data1/emsdev/redo03.dbf
 NO

1 ONLINE /kttest1-1_data1/emsdev/redo01.dbf
 NO

2 ONLINE /kttest1-1_data1/emsdev/redo02.dbf
 NO

SQL> alter system checkpoint;

System altered.

SQL> alter system checkpoint;

System altered.


 session 2:
 root@kttest1-1 # dd if=/dev/null of=/kttest1-1_data1/emsdev/redo02.dbf bs=512 count=10
 0+0 records in
 0+0 records out


 session 1:
 SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
 ---------- ---------- ---------- ---------- ---------- --- ----------------
 FIRST_CHANGE# FIRST_TIM
 ------------- ---------
 1 1 23 104857600 1 NO INACTIVE
 1.2792E+13 03-JUN-14

2 1 22 104857600 1 NO INACTIVE
 1.2792E+13 02-JUN-14

3 1 24 104857600 1 NO CURRENT
 1.2792E+13 03-JUN-14


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

Total System Global Area 3207790592 bytes
 Fixed Size 2119072 bytes
 Variable Size 381586016 bytes
 Database Buffers 2818572288 bytes
 Redo Buffers 5513216 bytes
 Database mounted.
 ORA-00320: cannot read file header from log 2 of thread 1
 ORA-00312: online log 2 thread 1: '/kttest1-1_data1/emsdev/redo02.dbf'

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database open;
 alter database open
 *
 ERROR at line 1:
 ORA-00320: cannot read file header from log 2 of thread 1
 ORA-00312: online log 2 thread 1: '/kttest1-1_data1/emsdev/redo02.dbf'


 SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database add logfile group 2 ('/kttest1-1_data1/emsdev/redo02.dbf') size 100m;
 alter database add logfile group 2 ('/kttest1-1_data1/emsdev/redo02.dbf') size 300m
 *
 ERROR at line 1:
 ORA-00301: error in adding log file '/kttest1-1_data1/emsdev/redo02.dbf' - file
 cannot be created
 ORA-27038: created file already exists
 Additional information: 1


 SQL> alter database add logfile group 2 ('/kttest1-1_data1/emsdev/redo02.dbf') size 100m reuse;

Database altered.

 


二.非归档下active状态的redo 恢复

如果非current redo 损坏,但是包含active事务,那么情况完全不同。

----Session 1


 SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
 ---------- ---------- ---------- ---------- ---------- --- ----------------
 FIRST_CHANGE# FIRST_TIM
 ------------- ---------
 1 1 26 104857600 1 NO CURRENT
 1.2792E+13 04-JUN-14

2 1 25 314572800 1 NO ACTIVE
 1.2792E+13 04-JUN-14

3 1 24 104857600 1 NO INACTIVE
 1.2792E+13 03-JUN-14

SQL> col member for a70
 SQL> select * from v$logfile;

GROUP# STATUS TYPE
 ---------- ------- -------
 MEMBER IS_
 ---------------------------------------------------------------------- ---
 3 ONLINE
 /kttest1-1_data1/emsdev/redo03.dbf NO

1 ONLINE
 /kttest1-1_data1/emsdev/redo01.dbf NO

2 ONLINE
 /kttest1-1_data1/emsdev/redo02.dbf NO

session 2:
 root@kttest1-1 # dd if=/dev/null of=/kttest1-1_data1/emsdev/redo02.dbf bs=512 count=10
 0+0 records in
 0+0 records out

---Session 1
 SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
 ---------- ---------- ---------- ---------- ---------- --- ----------------
 FIRST_CHANGE# FIRST_TIM
 ------------- ---------
 1 1 26 104857600 1 NO CURRENT
 1.2792E+13 04-JUN-14

2 1 25 314572800 1 NO ACTIVE
 1.2792E+13 04-JUN-14

3 1 24 104857600 1 NO INACTIVE
 1.2792E+13 03-JUN-14

 

SQL>SQL> shutdown abort
 ORACLE instance shut down.
 SQL> select * from v$log;
 select * from v$log
 *
 ERROR at line 1:
 ORA-01034: ORACLE not available
 Process ID: 3544
 Session ID: 83 Serial number: 3


 SQL> conn /as sysdba
 Connected to an idle instance.
 SQL> startup
 ORACLE instance started.

Total System Global Area 3207790592 bytes
 Fixed Size 2119072 bytes
 Variable Size 381586016 bytes
 Database Buffers 2818572288 bytes
 Redo Buffers 5513216 bytes
 Database mounted.
 ORA-00320: cannot read file header from log 2 of thread 1
 ORA-00312: online log 2 thread 1: '/kttest1-1_data1/emsdev/redo02.dbf'
 ORA-27069: attempt to do I/O beyond the range of the file
 Additional information: 1
 Additional information: 1


 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 emsdev1 (thread 1)
 ORA-00312: online log 2 thread 1: '/kttest1-1_data1/emsdev/redo02.dbf'


 SQL> alter database drop logfile group 2;
 alter database drop logfile group 2
 *
 ERROR at line 1:
 ORA-01624: log 2 needed for crash recovery of instance emsdev1 (thread 1)
 ORA-00312: online log 2 thread 1: '/kttest1-1_data1/emsdev/redo02.dbf'

SQL> recover database until cancel;
 ORA-00279: change 12792226234738 generated at 06/04/2014 09:35:35 needed for
 thread 1
 ORA-00289: suggestion : /kttest1-1_data1/emsdev/arch/1_32_848646308.arch
 ORA-00280: change 12792226234738 for thread 1 is in sequence #32


 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 auto
 ORA-00308: cannot open archived log
 '/kttest1-1_data1/emsdev/arch/1_32_848646308.arch'
 ORA-27037: unable to obtain file status
 SVR4 Error: 2: No such file or directory
 Additional information: 3


 ORA-00308: cannot open archived log
 '/kttest1-1_data1/emsdev/arch/1_32_848646308.arch'
 ORA-27037: unable to obtain file status
 SVR4 Error: 2: No such file or directory
 Additional information: 3


 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
 ORA-01194: file 1 needs more recovery to be consistent
 ORA-01110: data file 1: '/kttest1-1_data1/emsdev/system01.dbf'

32没有归档,不能这样恢复

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
 ---------- ---------- ---------- ---------- ---------- --- ----------------
 FIRST_CHANGE# FIRST_TIM
 ------------- ---------
 1 1 32 104857600 1 NO ACTIVE
 1.2792E+13 04-JUN-14

3 1 34 104857600 1 NO CURRENT
 1.2792E+13 04-JUN-14

2 1 33 104857600 1 NO ACTIVE
 1.2792E+13 04-JUN-14


 SQL> alter database open resetlogs;
 alter database open resetlogs
 *
 ERROR at line 1:
 ORA-01194: file 1 needs more recovery to be consistent
 ORA-01110: data file 1: '/kttest1-1_data1/emsdev/system01.dbf'

SQL> show parameter pfile

NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 spfile string
 SQL> shutdown immediate
 ORA-01109: database not open


 Database dismounted.
 ORACLE instance shut down.
 SQL>

在pfile文件中加入两个参数:
*._allow_resetlogs_corruption=true
 *._allow_error_simulation=true ----从oracle 10g开始,引入的另外一个参数。

SQL> conn /as sysdba
 Connected to an idle instance.
 SQL> startup mount
 ORACLE instance started.

Total System Global Area 3207790592 bytes
 Fixed Size 2119072 bytes
 Variable Size 381586016 bytes
 Database Buffers 2818572288 bytes
 Redo Buffers 5513216 bytes
 Database mounted.
 SQL> alter database open resetlogs;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

 

三.非归档下current状态的redo恢复


session 1:
 SQL> create table test as select * from dba_extents;

Table created.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
 ---------- ---------- ---------- ---------- ---------- --- ----------------
 FIRST_CHANGE# FIRST_TIM
 ------------- ---------
 1 1 1 104857600 1 NO CURRENT
 1.2792E+13 04-JUN-14

2 1 0 104857600 1 YES UNUSED
 0

3 1 0 104857600 1 YES UNUSED

 

session 2 :
 root@kttest1-1 # dd if=/dev/null of=/kttest1-1_data1/emsdev/redo01.dbf bs=512 count=10
 0+0 records in
 0+0 records out

session 1:

SQL> delete from test where rownum <100000;

45074 rows deleted.
 SQL> commit;

Commit complete.

SQL> shutdown immediate
 ORA-03113: end-of-file on communication channel
 Process ID: 14382
 Session ID: 83 Serial number: 3

SQL> startup
 ORA-24324: service handle not initialized
 ORA-01041: internal error. hostdef extension doesn't exist
 SQL> conn /as sysdba
 Connected to an idle instance.
 SQL> startup
 ORACLE instance started.

Total System Global Area 3207790592 bytes
 Fixed Size 2119072 bytes
 Variable Size 381586016 bytes
 Database Buffers 2818572288 bytes
 Redo Buffers 5513216 bytes
 Database mounted.
 ORA-00316: log 1 of thread 1, type 0 in header is not log file
 ORA-00312: online log 1 thread 1: '/kttest1-1_data1/emsdev/redo01.dbf'

SQL> recover database until cancel;
 ORA-00279: change 12792226263763 generated at 06/04/2014 10:45:34 needed for
 thread 1
 ORA-00289: suggestion : /kttest1-1_data1/emsdev/arch/1_1_849350732.arch
 ORA-00280: change 12792226263763 for thread 1 is in sequence #1


 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 auto
 ORA-00308: cannot open archived log
 '/kttest1-1_data1/emsdev/arch/1_1_849350732.arch'
 ORA-27037: unable to obtain file status
 SVR4 Error: 2: No such file or directory
 Additional information: 3


 ORA-00308: cannot open archived log
 '/kttest1-1_data1/emsdev/arch/1_1_849350732.arch'
 ORA-27037: unable to obtain file status
 SVR4 Error: 2: No such file or directory
 Additional information: 3


 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
 ORA-01194: file 1 needs more recovery to be consistent
 ORA-01110: data file 1: '/kttest1-1_data1/emsdev/system01.dbf'


 SQL> alter database open resetlogs;
 alter database open resetlogs
 *
 ERROR at line 1:
 ORA-01194: file 1 needs more recovery to be consistent
 ORA-01110: data file 1: '/kttest1-1_data1/emsdev/system01.dbf'


在pfile文件中加入两个参数:
*._allow_resetlogs_corruption=true
 *._allow_error_simulation=true


 SQL> alter database open;
 alter database open
 *
 ERROR at line 1:
 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


 SQL> alter database open resetlogs;

Database altered.


如果无法直接open resetlogs打开的话,要检查一下alert.log报错:

Thu Jul 18 16:38:42 2013
 SMON: enabling cache recovery
 Thu Jul 18 16:38:43 2013
 Errors in file /oracle/admin/orcl/udump/orcl_ora_11149.trc:
 ORA-00600: internal error code, arguments: [2662], [0], [4099916], [0], [4100136], [4194313], [], []
 Thu Jul 18 16:38:43 2013
 Error 600 happened during db open, shutting down database
 USER: terminating instance due to error 600
 Instance terminated by USER, pid = 11149
 ORA-1092 signalled during: alter database open resetlogs...

如出现ora-00600 [2662]报错,需要推进scn。推进方法如下:

ORA-600 [2662] [a] [b] [c] [d] [e]

Arg [a] Current SCN WRAP
 Arg [b] Current SCN BASE
 Arg [c] dependent SCN WRAP
 Arg [d] dependent SCN BASE
 Arg [e] Where present this is the DBA where the dependent SCN came from.

算法计算规则如下:Arg [c]*4得出一个数值,假设为V_Wrap,
如果Arg [d]=0,则V_Wrap值为需要的level
 Arg [d] < 1073741824,V_Wrap+1为需要的level
 Arg [d] < 2147483648,V_Wrap+2为需要的level
 Arg [d] < 3221225472,V_Wrap+3为需要的level

数据库处于mount状态下,执行:alter session set events '10015 trace name adjust_scn level 1';

再alter database open;

可以检查v$datafile与v$datafile_header的substr(checkpoint_change#,1,14)大小相同


四.归档模式下的red log恢复
1)inactive logfile损坏的情况
session 1:
 SQL> select * from v$log;
 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
 ---------- ---------- ---------- ---------- ---------- --- ----------------
 FIRST_CHANGE# FIRST_TIM
 ------------- ---------
 1 1 4 104857600 1 NO CURRENT
 1.2792E+13 04-JUN-14

2 1 2 104857600 1 YES INACTIVE
 1.2792E+13 04-JUN-14

3 1 3 104857600 1 YES INACTIVE
 1.2792E+13 04-JUN-14


 SQL> col member for a70
 SQL> select * from v$logfile;

GROUP# STATUS TYPE
 ---------- ------- -------
 MEMBER IS_
 ---------------------------------------------------------------------- ---
 3 ONLINE
 /kttest1-1_data1/emsdev/redo03.dbf NO

1 ONLINE
 /kttest1-1_data1/emsdev/redo01.dbf NO

2 ONLINE
 /kttest1-1_data1/emsdev/redo02.dbf NO


 session 2:
 root@kttest1-1 # dd if=/dev/null of=/kttest1-1_data1/emsdev/redo03.dbf bs=512 count=10
 0+0 records in
 0+0 records out

在这一步,如果不将数据库进行shutdown,继续进行logfile的切换,那么redo03仍然是可以写的,然而存在潜在风险。

session 1:
 SQL> shutdown abort
 ORACLE instance shut down.
 SQL> startup
 ORACLE instance started.

Total System Global Area 3207790592 bytes
 Fixed Size 2119072 bytes
 Variable Size 381586016 bytes
 Database Buffers 2818572288 bytes
 Redo Buffers 5513216 bytes
 Database mounted.
 ORA-00320: cannot read file header from log 3 of thread 1
 ORA-00312: online log 3 thread 1: '/kttest1-1_data1/emsdev/redo03.dbf'

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 ('/kttest1-1_data1/emsdev/redo03.dbf') size 100m reuse;

Database altered.

SQL> alter database open; ++++ 这里地方由于原来旧有的redo03被进程持有,可能句柄未释放,所以仍然报错,重启实例即可.

alter database open
 *
 ERROR at line 1:
 ORA-00320: cannot read file header from log 3 of thread 1
 ORA-00312: online log 3 thread 1: '/kttest1-1_data1/emsdev/redo03.dbf'

SQL> startup
 ORACLE instance started.

Total System Global Area 3207790592 bytes
 Fixed Size 2119072 bytes
 Variable Size 381586016 bytes
 Database Buffers 2818572288 bytes
 Redo Buffers 5513216 bytes
 Database mounted.
 Database opened.
 SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
 ---------- ---------- ---------- ---------- ---------- --- ----------------
 FIRST_CHANGE# FIRST_TIM
 ------------- ---------
 1 1 4 104857600 1 YES INACTIVE
 1.2792E+13 04-JUN-14

2 1 2 104857600 1 YES INACTIVE
 1.2792E+13 04-JUN-14

3 1 5 104857600 1 NO CURRENT
 1.2792E+13 04-JUN-14


 SQL> alter system switch logfile;

System altered.


2. 归档模式下current logfile损坏

场景一:
session 1:
 SQL> set line 200
 SQL> col member for a30
 SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
 ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
 1 1 4 104857600 1 YES INACTIVE 1.2792E+13 04-JUN-14
 2 1 6 104857600 1 NO CURRENT 1.2792E+13 04-JUN-14
 3 1 5 104857600 1 YES INACTIVE 1.2792E+13 04-JUN-14

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
 ---------- ------- ------- ------------------------------ ---
 3 ONLINE /kttest1-1_data1/emsdev/redo03 NO
 .dbf

1 ONLINE /kttest1-1_data1/emsdev/redo01 NO
 .dbf

2 ONLINE /kttest1-1_data1/emsdev/redo02 NO
 .dbf
 session 2:
 root@kttest1-1 # dd if=/dev/null of=/kttest1-1_data1/emsdev/redo02.dbf bs=512 count=10
 0+0 records in
 0+0 records out

session 1:

SQL> shutdown immediate
 ORA-03113: end-of-file on communication channel
 Process ID: 15430
 Session ID: 74 Serial number: 1

SQL> conn /as sysdba
 Connected to an idle instance.
 SQL> startup
 ORACLE instance started.

Total System Global Area 3207790592 bytes
 Fixed Size 2119072 bytes
 Variable Size 381586016 bytes
 Database Buffers 2818572288 bytes
 Redo Buffers 5513216 bytes
 Database mounted.
 ORA-00316: log 2 of thread 1, type 0 in header is not log file
 ORA-00312: online log 2 thread 1: '/kttest1-1_data1/emsdev/redo02.dbf'


 SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
 ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
 1 1 4 104857600 1 YES INACTIVE 1.2792E+13 04-JUN-14
 3 1 5 104857600 1 YES INACTIVE 1.2792E+13 04-JUN-14
 2 1 6 104857600 1 NO CURRENT 1.2792E+13 04-JUN-14


 SQL> conn /as sysdba
 Connected.
 SQL> shutdown immediate
 ORA-01109: database not open


 Database dismounted.
 ORACLE instance shut down.

在pfile文件中加入两个参数:
*._allow_resetlogs_corruption=true
 *._allow_error_simulation=true

SQL> startup
 ORACLE instance started.

Total System Global Area 3207790592 bytes
 Fixed Size 2119072 bytes
 Variable Size 381586016 bytes
 Database Buffers 2818572288 bytes
 Redo Buffers 5513216 bytes
 Database mounted.
 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


 SQL> recover database until cancel;
 ORA-00279: change 12792226298118 generated at 06/04/2014 14:21:06 needed for
 thread 1
 ORA-00289: suggestion : /kttest1-1_data1/emsdev/arch/1_6_849351843.arch
 ORA-00280: change 12792226298118 for thread 1 is in sequence #6


 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 auto
 ORA-00308: cannot open archived log
 '/kttest1-1_data1/emsdev/arch/1_6_849351843.arch'
 ORA-27037: unable to obtain file status
 SVR4 Error: 2: No such file or directory
 Additional information: 3


 ORA-00308: cannot open archived log
 '/kttest1-1_data1/emsdev/arch/1_6_849351843.arch'
 ORA-27037: unable to obtain file status
 SVR4 Error: 2: No such file or directory
 Additional information: 3


 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
 ORA-01194: file 1 needs more recovery to be consistent
 ORA-01110: data file 1: '/kttest1-1_data1/emsdev/system01.dbf'


 SQL> alter database open resetlogs;
 alter database open resetlogs
 *
 ERROR at line 1:
 ORA-01092: ORACLE instance terminated. Disconnection forced
 Process ID: 16850
 Session ID: 166 Serial number: 3


检查alert.log发现如下报错:
Errors in file /oracle/emsdev/diag/rdbms/emsdev1/emsdev1/trace/emsdev1_ora_16850.trc (incident=107308):
 ORA-00600: internal error code, arguments: [2662], [2978], [1813690638], [2978], [1813691502], [4194432], [], [], [], [], [], []
 Incident details in: /oracle/emsdev/diag/rdbms/emsdev1/emsdev1/incident/incdir_107308/emsdev1_ora_16850_i107308.trc
 Wed Jun 04 14:38:11 2014
 Errors in file /oracle/emsdev/diag/rdbms/emsdev1/emsdev1/trace/emsdev1_ora_16850.trc:
 ORA-00600: internal error code, arguments: [2662], [2978], [1813690638], [2978], [1813691502], [4194432], [], [], [], [], [], []
 Error 600 happened during db open, shutting down database
 USER (ospid: 16850): terminating the instance due to error 600
 Instance terminated by USER, pid = 16850
 ORA-1092 signalled during: alter database open resetlogs...
 ORA-1092 : opiodr aborting process unknown ospid (16850_1)
 Wed Jun 04 14:38:15 2014
 ORA-1092 : opitsk aborting process


 /* 如出现ora-00600 [2662]报错,需要推进scn。推进方法如下:

ORA-600 [2662] [a] [b] [c] [d] [e]

Arg [a] Current SCN WRAP
 Arg [b] Current SCN BASE
 Arg [c] dependent SCN WRAP
 Arg [d] dependent SCN BASE
 Arg [e] Where present this is the DBA where the dependent SCN came from.

算法计算规则如下:Arg [c]*4得出一个数值,假设为V_Wrap,
如果Arg [d]=0,则V_Wrap值为需要的level
 Arg [d] < 1073741824,V_Wrap+1为需要的level
 Arg [d] < 2147483648,V_Wrap+2为需要的level
 Arg [d] < 3221225472,V_Wrap+3为需要的level

数据库处于mount状态下,执行:alter session set events '10015 trace name adjust_scn level 1';

再alter database open;

可以检查v$datafile与v$datafile_header的substr(checkpoint_change#,1,14)大小相同 */


按以上方法计算出需要推进的scn大小为11914


 session 1:
 SQL> conn /as sysdba
 Connected to an idle instance.
 SQL> startup mount
 ORACLE instance started.

Total System Global Area 3207790592 bytes
 Fixed Size 2119072 bytes
 Variable Size 381586016 bytes
 Database Buffers 2818572288 bytes
 Redo Buffers 5513216 bytes
 Database mounted.


 SQL> alter session set events '10015 trace name adjust_scn level 11914';

Session altered.

SQL> select substr(checkpoint_change#,1,14) as df_scn from v$datafile;

DF_SCN
 --------------------------------------------------------
 12792226298122
 12792226298122
 12792226298122
 12792226298122
 12792226298122
 12792226298122
 12792226298122
 12792226298122
 12792226298122
 12792226298122
 12792226298122

DF_SCN
 --------------------------------------------------------
 12792226298122
 12792226298122

13 rows selected.

SQL> select substr(checkpoint_change#,1,14) as dfh_scn from v$datafile;

DFH_SCN
 --------------------------------------------------------
 12792226298122
 12792226298122
 12792226298122
 12792226298122
 12792226298122
 12792226298122
 12792226298122
 12792226298122
 12792226298122
 12792226298122
 12792226298122

DFH_SCN
 --------------------------------------------------------
 12792226298122
 12792226298122

13 rows selected.

SQL> alter database open;

Database altered.

场景二:

+++++Session 1
 SQL> conn /as sysdba
 Connected.
 SQL> set lines 200
 SQL> col member for a60
 SQL> select * from v$Log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
 ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
 1 1 7 10485760 1 NO CURRENT 1073767933 22-JUL-13
 2 1 5 10485760 1 YES INACTIVE 1073742213 21-JUL-13
 3 1 6 52428800 1 YES INACTIVE 1073742215 21-JUL-13
 SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
 ---------- ------- ------- ------------------------------------------------------------ ---
 2 ONLINE /home/ora10g/oradata/roger/redo02.log NO
 3 ONLINE /home/ora10g/oradata/roger/redo03.log NO
 1 ONLINE /home/ora10g/oradata/roger/redo01.log NO

SQL>
 SQL> alter system switch logfile;

System altered.

++++Session 2

[ora10g@killdb ~]$ dd if=/dev/null of=/home/ora10g/oradata/roger/redo02.log bs=512 count=10
 0+0 records in
 0+0 records out

++++Session 1

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

Total System Global Area 167772160 bytes
 Fixed Size 1272600 bytes
 Variable Size 96470248 bytes
 Database Buffers 67108864 bytes
 Redo Buffers 2920448 bytes
 Database mounted.
 ORA-00327: log 2 of thread 1, physical size less than needed
 ORA-00312: online log 2 thread 1: '/home/ora10g/oradata/roger/redo02.log'


 SQL> select * from v$Log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
 ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
 1 1 7 10485760 1 YES INACTIVE 1073767933 22-JUL-13
 3 1 6 52428800 1 YES INACTIVE 1073742215 21-JUL-13
 2 1 8 10485760 1 NO CURRENT 1073770766 22-JUL-13

 

SQL> recover database until cancel;
 Media recovery complete.


 SQL> alter database open resetlogs;
 alter database open resetlogs
 *
 ERROR at line 1:
 ORA-00327: log 2 of thread 1, physical size less than needed
 ORA-00312: online log 2 thread 1: '/home/ora10g/oradata/roger/redo02.log'

在pfile文件中加入两个参数:
*._allow_resetlogs_corruption=true
 *._allow_error_simulation=true

SQL> startup mount pfile='/tmp/pfile.ora';
 ORACLE instance started.

Total System Global Area 167772160 bytes
 Fixed Size 1272600 bytes
 Variable Size 96470248 bytes
 Database Buffers 67108864 bytes
 Redo Buffers 2920448 bytes
 Database mounted.

SQL> alter database open resetlogs;
 alter database open resetlogs
 *
 ERROR at line 1:
 ORA-01139: RESETLOGS option only valid after an incomplete database recovery


 SQL> recover database until cancel;
 ORA-00283: recovery session canceled due to errors
 ORA-00600: internal error code, arguments: [2130], [0], [8], [2], [], [], [], []


 SQL> show parameter resetlog

NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 _allow_resetlogs_corruption boolean TRUE
 SQL> show parameter allow

NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 _allow_error_simulation boolean TRUE
 _allow_resetlogs_corruption boolean TRUE
 SQL> alter database open;

Database altered.

SQL>

SQL> alter system switch logfile;

System altered.

 

3. 归档模式下active logfile损坏

场景一:

session 1:

SQL> set line 300
 SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
 ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
 1 1 1 104857600 1 YES INACTIVE 1.2793E+13 04-JUN-14
 2 1 2 104857600 1 NO CURRENT 1.2793E+13 04-JUN-14
 3 1 0 104857600 1 YES UNUSED 0

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 1 104857600 1 YES INACTIVE 1.2793E+13 04-JUN-14
 2 1 2 104857600 1 YES ACTIVE 1.2793E+13 04-JUN-14
 3 1 3 104857600 1 NO CURRENT 1.2794E+13 04-JUN-14

session 2:
 root@kttest1-1 # dd if=/dev/null of=/kttest1-1_data1/emsdev/redo02.dbf bs=512 count=10
 0+0 records in
 0+0 records out

session 1:

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

Total System Global Area 3207790592 bytes
 Fixed Size 2119072 bytes
 Variable Size 381586016 bytes
 Database Buffers 2818572288 bytes
 Redo Buffers 5513216 bytes
 Database mounted.
 ORA-00320: cannot read file header from log 2 of thread 1
 ORA-00312: online log 2 thread 1: '/kttest1-1_data1/emsdev/redo02.dbf'


 SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database add logfile group 2 ('/kttest1-1_data1/emsdev/redo02.dbf') size 100m reuse;

Database altered.

场景二:
 恢复方法与非归档模式下的active redo log恢复相同

相关推荐