ORA-00600: internal error code, arguments: [2662], [0](資料SCN不一致)

lovehewenyu發表於2012-08-02
Error

資料檔案SCN不一致恢復（ORA-00600: internal error code, arguments: [2662], [0]）

參考eygle老師的BLOG！在此再次感謝各位大師願意分享的經驗！！

http://www.eygle.com/archives/2005/10/ora_600_2662_error.html

http://www.eygle.com/archives/2005/12/oracle_diagnostics_howto_deal_2662_error.html

 

 

1、恢復，開啟資料庫報錯及alert日誌

SQL> startup

ORACLE instance started.

 

Total System Global Area  457179136 bytes

Fixed Size                  1219976 bytes

Variable Size             146801272 bytes

Database Buffers          306184192 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> startup mount;

ORA-24324: service handle not initialized

ORA-01041: internal error. hostdef extension doesn't exist

 

Alert日誌：

ORA-00600: internal error code, arguments: [2662], [0], [558703], [0], [558891], [4194313], [], []

 

2、嘗試在rman 中startup  竟然可以啟動，心有餘悸，肯定還有問題。

最有可能是的就是SCN不一致，於是看了一下。結果SCN真的不一致！（突然夢醒，我使用了_allow_resetlogs_corruption=true 隱含引數出現的問題）

 

[ora@dg-pp doudou]$ rman target /

 

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 5 23:15:31 2012

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

connected to target database (not started)

 

RMAN> startup

 

Oracle instance started

database mounted

database opened

 

Total System Global Area     457179136 bytes

 

Fixed Size                     1219976 bytes

Variable Size                146801272 bytes

Database Buffers             306184192 bytes

Redo Buffers                   2973696 bytes

 

SQL> conn /as sysdba

Connected.

 

SQL> select file#,CHECKPOINT_CHANGE# from v$datafile;

 

     FILE# CHECKPOINT_CHANGE#

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

         1             578703

         2             578703

         3             578703

         4             578703

         5                  0

         6                  0

         7             578703

 

7 rows selected.

 

3、關閉資料庫，進行強制推進SCN，達到一致！

 

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  457179136 bytes

Fixed Size                  1219976 bytes

Variable Size             146801272 bytes

Database Buffers          306184192 bytes

Redo Buffers                2973696 bytes

Database mounted.

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

 

Session altered.

 

SQL> alter database open;

 

Database altered.

 

4、啟動引數之後竟然沒有被恢復。

想了一下最有可能就是恢復的時候把檔案OFFLINE，結果一引數還真是OFFLINE狀態，這也說明了強制推進SCN只對線上檔案起作用

 

SQL> select file#,CHECKPOINT_CHANGE# from v$datafile;

 

     FILE# CHECKPOINT_CHANGE#

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

         1             598838

         2             598838

         3             598838

         4             598838

         5                  0

         6                  0

         7             598838

 

7 rows selected.

 

SQL> select checkpoint_change#,name,status from v$datafile;

 

CHECKPOINT_CHANGE# NAME                                          STATUS

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

            598838 /opt/oracle/oradata/doudou/system01.dbf       SYSTEM

            598838 /opt/oracle/oradata/doudou/undotbs01.dbf      ONLINE

            598838 /opt/oracle/oradata/doudou/sysaux01.dbf       ONLINE

            598838 /opt/oracle/oradata/doudou/users01.dbf        ONLINE

                 0 /opt/oracle/oradata/doudou/doudou.dbf         OFFLINE

                 0 /opt/oracle/oradata/doudou/test.dbf           OFFLINE

            598838 /opt/oracle/oradata/doudou/doudou_test.dbf    ONLINE

 

5、OFFLINE的檔案修改成ONLINE檔案，然後執行強制推進SCN，達到SCN一致。

OK恢復成功了！

 

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  457179136 bytes

Fixed Size                  1219976 bytes

Variable Size             146801272 bytes

Database Buffers          306184192 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL> alter database datafile 5 online;

 

Database altered.

 

SQL> alter database datafile 6 online;

 

Database altered.

 

SQL> alter session set events '10015 trace name adjust_scn level 10';

 

Session altered.

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01190: control file or data file 5 is from before the last RESETLOGS

ORA-01110: data file 5: '/opt/oracle/oradata/doudou/doudou.dbf'

 

 

SQL> alter database open resetlogs;

 

Database altered.

 

SQL> select checkpoint_change#,name,status from v$datafile;

 

CHECKPOINT_CHANGE# NAME                                          STATUS

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

            598911 /opt/oracle/oradata/doudou/system01.dbf       SYSTEM

            598911 /opt/oracle/oradata/doudou/undotbs01.dbf      ONLINE

            598911 /opt/oracle/oradata/doudou/sysaux01.dbf       ONLINE

            598911 /opt/oracle/oradata/doudou/users01.dbf        ONLINE

            598911 /opt/oracle/oradata/doudou/doudou.dbf         ONLINE

            598911 /opt/oracle/oradata/doudou/test.dbf           ONLINE

            598911 /opt/oracle/oradata/doudou/doudou_test.dbf    ONLINE

 

7 rows selected.

 

SQL>

 

總結：

一、各位同學，如果我們使用隱含引數恢復成功之後，一定要匯出資料，重新建庫然後匯入。不然會有很多異常的問題出現，不可控的風險是不允許在我們生產庫的！

二、增進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';

 

 

 

 

附表：

蓋老師解決SCN的方法

通過正常方式啟動資料庫時，從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已經足夠。也可以根據實際情況適當調整。

本例由於資料庫無法開啟，只能使用的二種方法。 

[oracle@jumper dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sun Dec 11 18:26:18 2005
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to an idle instance.
SQL> startup mount pfile=initconner.ora

ORACLE instance started.
Total System Global Area   97588504 bytes

Fixed Size                   451864 bytes

Variable Size              33554432 bytes

Database Buffers           62914560 bytes

Redo Buffers                 667648 bytes

Database mounted.
SQL> alter session set events '10015 trace name adjust_scn level 10';
Session altered.
SQL> alter database open;
Database altered.

注意,由於我使用了10015事件，使得SCN增進了10 billion，稍後我們可以驗證。  

[oracle@jumper dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sun Dec 11 18:26:18 2005
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to an idle instance.
SQL> startup mount pfile=initconner.ora

ORACLE instance started.
Total System Global Area   97588504 bytes

Fixed Size                   451864 bytes

Variable Size              33554432 bytes

Database Buffers           62914560 bytes

Redo Buffers                 667648 bytes

Database mounted.
SQL> alter session set events '10015 trace name adjust_scn level 10';
Session altered.
SQL> alter database open;
Database altered.

此時資料庫可以開啟，從alert檔案中我們可以看到如下提示: 

Sun Dec 11 18:27:04 2005

SMON: enabling cache recovery

Sun Dec 11 18:27:05 2005

Debugging event used to advance scn to 10737418240

SCN被增進了10 billion,即 10 * (1024*1024*1024) = 10737418240,正好是日誌裡記錄的數量。

我們從資料庫內部看一下檢查點的增進情況： 

SQL> select open_mode from v$database;
OPEN_MODE

----------

READ WRITE
SQL> select file#,CHECKPOINT_CHANGE# from v$datafile;
     FILE# CHECKPOINT_CHANGE#

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

         1          547783998          2          547783998

         3          547783998
SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.
Total System Global Area   97588504 bytes

Fixed Size                   451864 bytes

Variable Size              33554432 bytes

Database Buffers           62914560 bytes

Redo Buffers                 667648 bytes

Database mounted.

Database opened.

SQL> select file#,CHECKPOINT_CHANGE# from v$datafile;
     FILE# CHECKPOINT_CHANGE#

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

         1         1.0737E+10

         2         1.0737E+10

         3         1.0737E+10
SQL> col CHECKPOINT_CHANGE# for 99999999999999999

SQL>  select file#,CHECKPOINT_CHANGE# from v$datafile;
     FILE# CHECKPOINT_CHANGE#

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

         1        10737418447          2        10737418447

         3        10737418447

我們看到CHECKPOINT_CHANGE# 最終被增進了10 Billion.這要是賬戶上的錢該有多好：）

蓋老師還是一個風趣的人，哇哈！

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-739617/，如需轉載，請註明出處，否則將追究法律責任。

相關文章