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

oDiWuYuanSu

2016-12-11

关注关注

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;

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;

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.

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;

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.

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;

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'

在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.

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.

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.

数据库处于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.

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恢复相同

log oracle oracle归档模式 oracle数据库

安科网

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

oDiWuYuanSu

oDiWuYuanSu

相关推荐

RabbitMQ如何保证消息的可靠投递？

Linux后台执行命令：&与nohup的用法

Linux 入侵痕迹清理技巧

为什么排序的复杂度为O(N log N)

Filebeat简介

JS中DOM元素的操作

javascript解析json格式的数据方法详解

MySQL是如何保证数据的完整性

mysql解决时区相关问题

如何在JavaScript实现休眠或等待功能，实现sleep函数

debian apach2 wsgi 自定义log logrotate 之后无权限访问

es6 Promise 对象、.then()

filebeat配置文件

mysql的日志模块

Golang保存PostgreSQL数据至结构

封装excel数据层代码，log模块导入

TypeScript（13）：元组

TypeScript（06）：运算符

nginx 日志切割

CentOS 7 如何清空文件内容

oDiWuYuanSu