happydba

故障现象提供：小荷
解决方案提供：老熊

(1)现象：报ora-600,2662错误

Completed: ALTER DATABASE   MOUNT
Thu Jan 22 13:05:08 2009
ALTER DATABASE OPEN
Thu Jan 22 13:05:09 2009
Beginning crash recovery of 1 threads
Thu Jan 22 13:05:09 2009
Started first pass scan
Thu Jan 22 13:05:09 2009
Completed first pass scan
 0 redo blocks read, 0 data blocks need recovery
Thu Jan 22 13:05:09 2009
Started recovery at
 Thread 1: logseq 2, block 3, scn 0.43536037
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
  Mem# 0 errs 0: /oracle/oradata/ora9i/redo01.log
Thu Jan 22 13:05:09 2009
Ended recovery at
 Thread 1: logseq 2, block 3, scn 0.43556038
 0 data blocks read, 0 data blocks written, 0 redo blocks read
Crash recovery completed successfully
Thu Jan 22 13:05:10 2009
Thread 1 advanced to log sequence 3
Thread 1 opened at log sequence 3
  Current log# 2 seq# 3 mem# 0: /oracle/oradata/ora9i/redo02.log
Successful open of redo thread 1.
Thu Jan 22 13:05:10 2009
SMON: enabling cache recovery
Thu Jan 22 13:05:10 2009
Errors in file /oracle/admin/ora9i/udump/ora9i_ora_12968.trc:
ORA-00600: internal error code, arguments: [2662], [0], [43556042], [261], [2396789971], [4194729], [], []

SQL> startup
ORACLE instance started.

Total System Global Area 1076850392 bytes
Fixed Size                   736984 bytes
Variable Size             536870912 bytes
Database Buffers          536870912 bytes
Redo Buffers                2371584 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

 
版本：9206
*._allow_resetlogs_corruption=TRUE

SQL> startup nomount pfile=’?/dbs/initora9i.ora’
ORACLE instance started.

Total System Global Area 1076850392 bytes
Fixed Size                   736984 bytes
Variable Size             536870912 bytes
Database Buffers          536870912 bytes
Redo Buffers                2371584 bytes
SQL>  alter database mount;

Database altered.
(2)采用adjust scn的方法：
SQL> alter session set events ’10015 trace name ADJUST_SCN level 1047′;

Session altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

 
Thu Jan 22 13:27:56 2009
SMON: enabling cache recovery
Thu Jan 22 13:27:56 2009
Errors in file /oracle/admin/ora9i/udump/ora9i_ora_13322.trc:
ORA-00600: internal error code, arguments: [2662], [0], [43576046], [261], [2396789971], [4194729], [], []
  Thu Jan 22 13:28:37 2009
Errors in file /oracle/admin/ora9i/udump/ora9i_ora_13322.trc:
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [0], [43576046], [261], [2396789971], [4194729], [], []
Thu Jan 22 13:28:37 2009
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 13322
ORA-1092 signalled during: alter database open…

 
*._allow_error_simulation=TRUE

 
———————————————————————————————–
(3)老熊的历史案例提供：
SQL> alter system set job_queue_processes=0;
SQL> alter system set “_allow_resetlogs_corruption”=true scope=spfile;
SQL> alter system set “_allow_read_only_corruption”=true scope=spfile;
SQL> alter system set “_allow_terminal_recovery_corruption”=true scope=spfile;

再尝试以resetlogs方式打开数据库：
SQL> alter database open resetlogs;               
                
Database altered.                
                
SQL> select 1 from dual;                
select 1 from dual                
*                
ERROR at line 1:                
ORA-03135: connection lost contact
检查日志发现由于[2662]错误导致数据库实例中止。

 调整SCN：
SQL> alter session set events ’10015 trace name adjust_scn level 7681′;
再次打开数据库：
SQL> alter database open;
————————————————————————————————-

(4)照猫画熊
*._minimum_giga_scn=1047

SQL> startup mount pfile=’?/dbs/initora9i.ora’
ORACLE instance started.

Total System Global Area 1076850392 bytes
Fixed Size                   736984 bytes
Variable Size             536870912 bytes
Database Buffers          536870912 bytes
Redo Buffers                2371584 bytes
Database mounted.
SQL> alter database open;

Database altered.

SQL> 

Successfully onlined Undo Tablespace 15.
Dictionary check beginning
File #20 is offline, but is part of an online tablespace.
data file 20: ‘/opt/oracle/UNDO_001.dbf’
Dictionary check complete
Thu Jan 22 13:55:33 2009
SMON: enabling tx recovery
Thu Jan 22 13:55:33 2009
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Completed: alter database open

–数据库启动成功
看看现在的scn是多少？

SQL> select dbms_flashback.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
              1.1242E+12

(5)老熊讲解

_minimum_giga_scn，就相当于adjust_scn，那个参数意思就是系统的最小scn为多少。g为单位(约为10亿)
SQL> select ksppinm,ksppdesc from x$ksppi where
  2  ksppinm=’_minimum_giga_scn’;

KSPPINM
———————————————————
KSPPDESC
———————————————————
_minimum_giga_scn
Minimum SCN to start with in 2^30 units （g=2^30=1,073,741,824=10亿）
SCN构成：
ORA-00600: internal error code, arguments: [2662], [0], [43616053], [261], [2396789971], [4194729], [], []
warp=261
base=2396789971

261–2396789971
高2字节就是261
低4字节就是2396789971
scn= wrap….base
就是
0xffff.ffffffff
（base最大是ffffffff，那么加1就进位上去到wrap了。
其实就是说base的1就相当于base中的ffffffff+1了。 ）
wrap中的1其实就是2的32次方 （2^32=4294967296=2^30 *4）

 
_minimum_giga_scn，是我们要调整的，是以G为单位，就是2的30次方啊
就是说261那个1就相当于4个2的30次方啊

261*4+3=1047，我们去1057，比1047大一些，也就是比target大一些

(6)取消相关参数，重启数据库：

*.user_dump_dest=’/oracle/admin/ora9i/udump’
#*._allow_resetlogs_corruption=TRUE
#*._allow_error_simulation=TRUE
#*._minimum_giga_scn=1047
“initora9i.ora” 47 lines, 1467 characters
$
$
$ sqlplus “/ as sysdba”

SQL*Plus: Release 9.2.0.1.0 – Production on Thu Jan 22 14:32:13 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1076850392 bytes
Fixed Size                   736984 bytes
Variable Size             536870912 bytes
Database Buffers          536870912 bytes
Redo Buffers                2371584 bytes
Database mounted.
Database opened.
SQL>   
当然如果是生产系统，最好是exp再重建库，imp进去

注：对于10个g，缺省情况_ALLOW_ERROR_SIMULATION是FALSE，这会阻止ADJUST_SCN，所以要做ADJUST_SCN必须设置这个隐含参数为TRUE

HTML clipboard使用隐含参数_ALLOW_RESETLOGS_CORRUPTION后resetlogs打开数据库后,我们说很多时候你会遇到ORA-00600 2662号错误，这个错误的含义是:

A data block SCN is ahead of the current SCN.
The ORA-600 [2662] occurs when an SCN is compared to the dependent SCN
stored in a UGA variable.
If the SCN is less than the dependent SCN then we signal the ORA-600 [2662]
internal error.

在测试中,很容易模拟这个错误:

Thu Oct 20 10:38:27 2005 SMON: enabling cache recovery Thu Oct 20 10:38:27 2005 Errors in file /opt/oracle/admin/conner/udump/conner_ora_31607.trc: ORA-00600: internal error code, arguments: [2662], [0], [897694446], [0], [897695488], [8388697], [], [] Thu Oct 20 10:38:28 2005 Errors in file /opt/oracle/admin/conner/udump/conner_ora_31607.trc: ORA-00600: internal error code, arguments: [2662], [0], [897694446], [0], [897695488], [8388697], [], [] 

如果SCN相差不多，可以通过多次重起数据库解决。

也可以通过内部事件:
alter session set events ‘IMMEDIATE trace name ADJUST_SCN level 1′;
来解决。

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

通过正常方式启动数据库时，从alert文件中，我们可以看到ora-00600 2662号错误。

Sun Dec 11 18:02:25 2005
Errors in file /opt/oracle/admin/conner/udump/conner_ora_13349.trc:
ORA-00600: internal error code, arguments: [2662], [0], [547743994], [0], [898092653],
[8388617], [], []
Sun Dec 11 18:02:27 2005
Errors in file /opt/oracle/admin/conner/udump/conner_ora_13349.trc:
ORA-00600: internal error code, arguments: [2662], [0], [547743994], [0], [898092653],
[8388617], [], []
Sun Dec 11 18:02:27 2005
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600

此时我们可以通过Oracle的内部事件来调整SCN:

增进SCN有两种常用方法:

1.通过immediate trace name方式(在数据库Open状态下)

alter session set events ‘IMMEDIATE trace name ADJUST_SCN level x’;

2.通过10015事件(在数据库无法打开，mount状态下)

alter session set events ’10015 trace name adjust_scn level x’;

注:level 1为增进SCN 10亿 (1 billion) (1024*1024*1024),通常Level 1已经足够。也可以根据实际情况适当调整。

本例由于数据库无法打开，只能使用的二种方法。

注意,由于我使用了10015事件，使得SCN增进了10 billion，稍后我们可以验证。

此时数据库可以打开，从alert文件中我们可以看到如下提示:

SCN被增进了10 billion,即 10 * (1024*1024*1024) = 10737418240,正好是日志里记录的数量。

我们从数据库内部看一下检查点的增进情况：

我们看到CHECKPOINT_CHANGE# 最终被增进了10 Billion.这要是账户上的钱该有多好：）

ref:http://www.eygle.com/archives/2005/12/20/