當前日誌損壞的案例（二）

設定兩個隱含參數：_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: {<RET>=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: {<RET>=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百萬)