oracle数据库联机日志丢失处理方式详解

oracle联机日志文件丢失处理方法[@more@]

sqlplus /nolog

connect sys/sys@denglh as sysdba

alter system set "_allow_resetlogs_corruption"=true scope=spfile ;

alter system set "_allow_terminal_recovery_corruption"=true scope=spfile ;

alter system set "_corrupted_rollback_segments"=true scope=spfile ;

shutdown abort ;

recover database until cancel;

alter database open resetlogs;

alter system set "_allow_resetlogs_corruption"=false scope=spfile ;

alter system set "_allow_terminal_recovery_corruption"=false scope=spfile ;

recover database using backup controlfile until cancel;

create pfile='c:oraclepfile.tmp' from spfile;

当前日志损坏的案例(二)

设置两个隐含参数：_allow_resetlogs_corruption = true 和 _corrupted_rollback_segments ，因为redo损坏的时候，undo数据也大都不一致了。

使用隐含参数启动数据库：

sql> create pfile='/home/oracle/pfile.tmp' from spfile;

file created.

sql>

sql> shutdown immediate;

ora-01109: database not open

database dismounted.

oracle instance shut down.

sql>

[oracle@ts01 oracle]$ tail pfile.tmp

*.sort_area_size=524288

*.star_transformation_enabled='false'

*.timed_statistics=true

*.undo_management='auto'

*.undo_retention=10800

*.undo_tablespace='undotbs2'

*.user_dump_dest='/oracle/admin/tsmisc02/udump'

_allow_resetlogs_corruption = true

[oracle@ts01 oracle]$

这个参数的解释：

sql> select ksppdesc from x$ksppi where ksppinm='_allow_resetlogs_corruption';

ksppdesc

----------------------------------------------------------------

allow resetlogs even if it will cause corruption

sql>

[oracle@ts01 oracle]$ sqlplus '/ as sysdba'

sql*plus: release 9.2.0.4.0 - production on mon nov 21 15:57:21 2005

copyright (c) 1982, 2002, oracle corporation. all rights reserved.

connected to an idle instance.

sql> startup mount pfile=pfile.tmp

oracle instance started.

total system global area 236000356 bytes

fixed size 451684 bytes

variable size 201326592 bytes

database buffers 33554432 bytes

redo buffers 667648 bytes

database mounted.

sql>

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> select status from v$instance;

status

------------

mounted

sql> alter database open;

alter database open

*

error at line 1:

ora-00316: log 1 of thread 1, type 0 in header is not log file

ora-00312: online log 1 thread 1: '/oracle/oradata/tsmisc02/redo01.log'

sql>

sql> recover database using backup controlfile until cancel;

ora-00279: change 658443 generated at 11/15/2005 08:52:17 needed for thread 1

ora-00289: suggestion : /oracle/oradata/tsmisc02/archive/1_62.dbf

ora-00280: change 658443 for thread 1 is in sequence #62

specify log: {=suggested | filename | auto | cancel}

cancel

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: '/oracle/oradata/tsmisc02/system01.dbf'

ora-01112: media recovery not started

sql> alter database open resetlogs;

。。。

很长时间，就好像hang住了

。。。

有过了一会：

sql> alter database open resetlogs;

alter database open resetlogs

*

error at line 1:

ora-03113: end-of-file on communication channel

sql>

sql> select status from v$instance;

select status from v$instance

*

error at line 1:

ora-03114: not connected to oracle

sql> conn / as sysdba

connected to an idle instance.

sql>

检查日志，发现：

[oracle@ts01 bdump]$ tail alert_tsmisc02.log

errors in file /oracle/admin/tsmisc02/udump/tsmisc02_ora_10762.trc:

ora-00600: internal error code, arguments: [2662], [0], [658448], [0], [664313], [12582921], [], []

mon nov 21 16:11:44 2005

errors in file /oracle/admin/tsmisc02/udump/tsmisc02_ora_10762.trc:

ora-00600: internal error code, arguments: [2662], [0], [658448], [0], [664313], [12582921], [], []

mon nov 21 16:11:44 2005

error 600 happened during db open, shutting down database

user: terminating instance due to error 600

instance terminated by user, pid = 10762

ora-1092 signalled during: alter database open resetlogs...

[oracle@ts01 bdump]$

设置event adjust_scn：

alter session set events '10015 trace name adjust_scn level 1';

sql> conn / as sysdba

connected to an idle instance.

sql> startup mount pfile=pfile.tmp

oracle instance started.

total system global area 236000356 bytes

fixed size 451684 bytes

variable size 201326592 bytes

database buffers 33554432 bytes

redo buffers 667648 bytes

database mounted.

sql>

sql> alter session set events '10015 trace name adjust_scn level 1';

session altered.

sql>

sql> recover database using backup controlfile until cancel;

ora-00279: change 658445 generated at 11/21/2005 16:11:43 needed for thread 1

ora-00289: suggestion : /oracle/oradata/tsmisc02/archive/1_1.dbf

ora-00280: change 658445 for thread 1 is in sequence #1

specify log: {=suggested | filename | auto | cancel}

cancel

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: '/oracle/oradata/tsmisc02/system01.dbf'

ora-01112: media recovery not started

sql> alter database open resetlogs;

alter database open resetlogs

*

error at line 1:

ora-01092: oracle instance terminated. disconnection forced

sql>

检查日志：

[oracle@ts01 bdump]$ tail alert_tsmisc02.log

errors in file /oracle/admin/tsmisc02/udump/tsmisc02_ora_10762.trc:

ora-00600: internal error code, arguments: [2662], [0], [658448], [0], [664313], [12582921], [], []

mon nov 21 16:11:44 2005

errors in file /oracle/admin/tsmisc02/udump/tsmisc02_ora_10762.trc:

ora-00600: internal error code, arguments: [2662], [0], [658448], [0], [664313], [12582921], [], []

mon nov 21 16:11:44 2005

error 600 happened during db open, shutting down database

user: terminating instance due to error 600

instance terminated by user, pid = 10762

ora-1092 signalled during: alter database open resetlogs...

[oracle@ts01 bdump]$ tail alert_tsmisc02.log

recovery of online redo log: thread 1 group 1 seq 1 reading mem 0

mem# 0 errs 0: /oracle/oradata/tsmisc02/redo01.log

mon nov 21 16:21:41 2005

errors in file /oracle/admin/tsmisc02/udump/tsmisc02_ora_10795.trc:

ora-00607: internal error occurred while making a change to a data block

ora-00600: internal error code, arguments: [4194], [91], [69], [], [], [], [], []

error 607 happened during db open, shutting down database

user: terminating instance due to error 607

instance terminated by user, pid = 10795

ora-1092 signalled during: alter database open resetlogs...

[oracle@ts01 bdump]$ tail -f alert_tsmisc02.log

recovery of online redo log: thread 1 group 1 seq 1 reading mem 0

mem# 0 errs 0: /oracle/oradata/tsmisc02/redo01.log

mon nov 21 16:21:41 2005

errors in file /oracle/admin/tsmisc02/udump/tsmisc02_ora_10795.trc:

ora-00607: internal error occurred while making a change to a data block

ora-00600: internal error code, arguments: [4194], [91], [69], [], [], [], [], []

error 607 happened during db open, shutting down database

user: terminating instance due to error 607

instance terminated by user, pid = 10795

ora-1092 signalled during: alter database open resetlogs...

[oracle@ts01 bdump]$

设置隐含参数：

将undo改变成手工管理的，然后重启数据库，

sql> show parameter undo

name type value

------------------------------------ ----------- ------------------------------

undo_management string manuel

undo_retention integer 10800

undo_suppress_errors boolean false

undo_tablespace string undotbs2

sql>

[oracle@ts01 oracle]$ tail pfile.tmp

*.undo_management='auto'

*.undo_retention=10800

*.undo_tablespace='undotbs2'

*.user_dump_dest='/oracle/admin/tsmisc02/udump'

*._allow_resetlogs_corruption = true

*.undo_management='manual'

[oracle@ts01 oracle]$

[oracle@ts01 oracle]$ exit

exit

sql> startup pfile=pfile.tmp

oracle instance started.

total system global area 236000356 bytes

fixed size 451684 bytes

variable size 201326592 bytes

database buffers 33554432 bytes

redo buffers 667648 bytes

database mounted.

database opened.

sql>

sql> conn lunar/lunar

connected.

sql> select * from tab;

tname tabtype clusterid

------------------------------ ------- ----------

lunartest table

sql>

总结：

将undo改变成手工管理的，

然后设置隐含参数 _allow_resetlogs_corruption = true 和 _corrupted_rollback_segments ,因为redo损坏的时候，undo数据也大都不一致

了。

2，open resetlogs之前，先使用recover database using backup controlfile until cancel;

如果此时又遇到600错误，就使用adjust_scn事件来调整当前的scn，如果scn相差不多，可以通过多次重起数据库解决。如果scn相差比较多，

可以使用10015 event：

alter session set events '10015 trace name adjust_scn level 1';

如果scn相差比较多，可以设置level 2，。。。level 10等 (level 1是每次打开时将将scn推进1百万)