ORA-600 [2662] Block SCN is ahead of Current SCN 處理方法 說明

一. ORA-600[2662] 說明

關於ORA-600[2662]的的錯誤，之前的blog 有說明：

ORA-600[2662] "Block SCN is ahead of Current SCN" [ID 28929.1]

http://blog.csdn.net/tianlesoftware/article/details/6106130

關於ORA-600 的各個引數說明，參考我的blog：

ORA-600 各個引數含義說明

http://blog.csdn.net/tianlesoftware/article/details/6645809

簡單的說，就是block 裡的SCN 大於current SCN 時，就會報ORA-600[2662]錯誤。

導致這個問題的原因有如下可能：

(1) doing an open resetlogs with_ALLOW_RESETLOGS_CORRUPTION enabled

(2) a hardware problem, like a faultycontroller, resulting in a failed write to the control file or the redo logs

(3) restoring parts of the database frombackup and not doing the appropriate recovery

(4) restoring a control file and not doinga RECOVER DATABASE USING BACKUP CONTROLFILE

(5) having _DISABLE_LOGGING set duringcrash recovery

(6) problems with the DLM in a parallelserver environment

(7) a bug

二. 解決方法

(1)if the SCNs in the error are very close, attempting a startup several timeswill bump up the dscn every time we open the database even if open fails. Thedatabase will open when dscn=scn.

(2)Youcan bump the SCN either on open or while the database is open using (see).

Beaware that you should rebuild the database if you use this option. Once this has occurred you would normally want to rebuild thedatabase via exp/rebuild/imp as there is no guarantee that some other blocksare not ahead of time.

關於這個rebuild 的另一段描述：

Forcing the database to startup using_ALLOW_RESETLOGS_CORRUPTION:

This parameter is undocumented andunsupported. The_allow_resetlogs_corruption should only be done as a lastresort. Usually when a database is opened with either the RESETLOGS or NORESETLOGSoption, the status and checkpoint structures in all the file headers of alldata files are checked to make sure that they are consistent. Once this is checked, the redo logs are zeroedout in case of RESETLOGS.When the_ALLOW_RESETLOGS_CORRUPTION parameter is set, the file header checks arebypassed. This means that we do not make sure that the files are consistent andopen the database. It will potentially cause some lost data and lost dataintegrity.

Thedatabase should be rebuilt since data and the data dictionary could be corruptin ways that are not immediately detectable. This could lead to future outagesor put the database in a state where it cannot be salvaged at all. There is noguarantee that this will work.

當我們設定_ALLOW_RESETLOGS_CORRUPTION引數後，講不在檢查file header，這樣就不能保證files的一致性，就可能造成資料的丟失。 所以這種情況下，就是對資料庫進行rebuild。 即：export DB, 重新建立例項，import 資料。

2.1 方法一

ORA-600的引數格式如下：

Arg[a] Current SCN WRAP

Arg[b] Current SCN BASE

Arg[c] dependent SCNWRAP

Arg[d] dependent SCN BASE

Arg[e] Where present this is the DBA wherethe dependent SCN came from.

當dependent SCN 與 current SCN 差距不大的時候，可以多shutdown 和 startup 資料庫，這樣拉大current SCN，當current SCN 大於dependent SCN, 就可以正常啟動DB了。

2.2 方法二： 使用adjust_scn event

如果dependent SCN 和 current SCN 差距很大的時候，透過多次重啟DB 來拉大SCN 明顯不現實。 這時候，就可以透過adjust_scn event 來拉大current scn。 但是使用這種方法，需要重新rebuild 一下資料庫，即exp 和imp。

關於adjust_scn event 使用說明，參考我的blog。 這篇文件在MOS 上沒有找到，所以轉帖過來了。

EVENT ADJUST_SCN 說明 [30681.1]

http://blog.csdn.net/tianlesoftware/article/details/6645633

2.2.1 資料庫沒有開啟時使用說明

Take a backup.

You can use event 10015 to trigger an ADJUST_SCNon database open:

startup mount;

alter session set events '10015 trace nameadjust_scn level 1';

(NB: You can only use IMMEDIATE here onanOPEN database. If the database is only mounted use the 10015 trigger to adjustSCN,otherwise you get ORA 600 [2251], [65535], [4294967295] )

alter database open;

If you get an ORA 600:2256 shutdown, use ahigher level and reopen.

Do*NOT* set this event in init.ora or the instance will crash as soon as SMON orPMON try to do any clean up.Always use it with the "alter session"command.

--不要將該引數設定到init.ora 檔案裡，否則當SMON 或者PMON 程式進行cleanup時，DB 就會crash 掉。

2.2.2 資料庫OPEN 狀態使用

You can increase the SCN thus:

alter session set events 'IMMEDIATE tracename ADJUST_SCN level 1';

LEVEL:

Level1 is usually sufficient - it raises the SCN to 1 billion(1024*1024*1024)，Level 2 raises it to 2 billionetc...

--注意level1的值，1代表的是講SCN 增加1億，2代表2億

If you try to raise the SCN to a level LESSTHAN or EQUAL to its current setting you will get <2256> - Seebelow.

Ie: The event steps the SCN to knownlevels. You cannot use the same level twice.

Calculating a Level from600 errors:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

To get a LEVEL for ADJUST_SCN:

a) Determine the TARGET scn:

ora-600 [2662] See <2662> Use TARGET >= blocksSCN

ora-600 [2256] See<2256> Use TARGET >=CurrentSCN

b)Multiply the TARGET wrap number by 4.This will give you the level to use in the adjust_scn to get the correct wrapnumber.

c) Next, add the following value to the levelto get the desired base value as well :

Add to Level Base

~~~~~~~~~~~~ ~~~~~~~~~~~~

0 0

1 1073741824(1024*1024*1024)，

2 2147483648(2*1024*1024*1024)，

3 3221225472(3*1024*1024*1024)，

注意：

在Oracle 9i 下面，直接使用alter session 命令就可以增加SCN。 但是在Oracle 10g下面，還需要修改隱含引數：_allow_error_simulation，將該引數設定為true，才能真正增進scn。

隱含引數的檢視，可以使用如下檢視：

Oracle all_parameters 檢視

http://blog.csdn.net/tianlesoftware/article/details/6641281

三. adjust_scn 示例

db 版本資訊：

SYS@dave2(db2)> select * from v$versionwhere rownum=1;

BANNER

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

Oracle Database 10g Enterprise EditionRelease 10.2.0.1.0 - Prod

3.1 db open 狀態

SYS@dave2(db2)> select current_scn fromv$database;

CURRENT_SCN

-----------

4304475

SYS@dave2(db2)> select open_mode fromv$database;

OPEN_MODE

----------

READ WRITE

SYS@dave2(db2)> alter session set events'immediate trace name adjust_scn level 1';

Session altered.

SYS@dave2(db2)> select current_scn fromv$database;

CURRENT_SCN

-----------

4304523

注意這裡的SCN 並沒有大量的增加，我們設定一下_allow_error_simulation。

SYS@dave2(db2)> alter system set"_allow_error_simulation"=true scope=spfile;

System altered.

SYS@dave2(db2)> startup force

ORACLE instance started.

Total System Global Area 239075328 bytes

Fixed Size 1218724 bytes

Variable Size 79693660 bytes

Database Buffers 155189248 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SYS@dave2(db2)>

再次使用adjust_scn event：

SYS@dave2(db2)> alter session set events'immediate trace name adjust_scn level 1';

Session altered.

SYS@dave2(db2)> select current_scn fromv$database;

CURRENT_SCN

-----------

1073742111

--這次SCN 成功增加了。

3.2 db not open 狀態

這裡_allow_error_simulation 引數已經修改過了，我們就不進行重複修改，直接將將db 啟動到mount，在使用adjust_scn 增加SCN值。

SYS@dave2(db2)> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@dave2(db2)> startup mount

ORACLE instance started.

Total System Global Area 239075328 bytes

Fixed Size 1218724 bytes

Variable Size 79693660 bytes

Database Buffers 155189248 bytes

Redo Buffers 2973696 bytes

Database mounted.

SYS@dave2(db2)> select current_scnfrom v$database;

CURRENT_SCN

-----------

0

--如果DB 沒有open，這個命令是不好使的

SYS@dave2(db2)> altersession set events '10015 trace name adjust_scn level 2';

Session altered.

SYS@dave2(db2)> select current_scn fromv$database;

CURRENT_SCN

-----------

0

SYS@dave2(db2)> alterdatabase open;

Database altered.

SYS@dave2(db2)> select current_scn from v$database;

CURRENT_SCN

-----------

2147483746

--SCN 已經增加

--最後檢視一下_allow_error_simulation引數的值：

SYS@dave2(db2)> select name,value fromall_parameters where name='_allow_error_simulation';

NAME VALUE

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

_allow_error_simulation TRUE

最後不要忘記對db進行rebuild。