close

  Oracle資料庫是嚴格遵循一致性的資料庫，資料庫在啟動的時候有不一致的情況是無法正常的將Oracle資料庫Open，在一些物理檔損壞導致資料庫不一致的情況下，我們經常不得不選擇強制性打開資料庫，使用exp工具匯出資料，重建資料庫，再使用imp倒入資料，來恢復資料。強制打開Oracle資料庫主要是在參數檔中加入隱藏參數_ALLOW_RESETLOGS_CORRUPTION=TRUE，但是這參數加入之後可能遇到ORA-600 [2662]的錯誤，無法打開，處理方法參考如下兩篇文章。

http://www.eygle.com/archives/2005/12/oracle_diagn...

通過上面的文章可以看出，主要是要推進SCN號，Oracle 10g資料庫還可以參考文章：

http://www.eygle.com/archives/2010/12/fractured_co...

當10g的資料庫強制打開資料庫時遇到ORA-600 [2662]的錯誤，還可以通過加入_minimum_giga_scn隱藏參數來強制推進SCN號。

相關的原理參見文章：

http://www.eygle.com/internal/How.to.Resolve.Ora-6...

        _minimum_giga_scn參數在11.2.0.5版本不可用，詳情參考ITPUB的一篇文章：http://www.itpub.net/thread-1642718-1-1.html

下面是實際環境的操作案例：

# su - oracle

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 24 13:28:50 2013

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

         147119073

SQL> select status from v$Instance;

STATUS

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

MOUNTED

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

         147119073

         147119073

         147119073

         147119073

         147119073

         147119073

         147119073

147119073

         147119073

         147119073

         147119073

CHECKPOINT_CHANGE#

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

         147119073

         147119073

13 rows selected.

SQL> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#

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

         1147119073

         2147119073

         3147119073

         4147119073

         5147119073

         6147119073

         7147119073

         8147119073

9147388587

        10147119073

        11147119073

     FILE# CHECKPOINT_CHANGE#

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

        12147119073

        13147119073

13 rows selected.

SQL> select last_change# from v$datafile;

LAST_CHANGE#

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

LAST_CHANGE#

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

13 rows selected.

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 147119073 generated at 06/18/2013 23:01:53 needed for thread

1

ORA-00289: suggestion :

/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_3041_809469738.dbf

ORA-00280: change 147119073 for thread 1 is in sequence #3041

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

ORA-01112: media recovery not started

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/oradata/d012band/system01.dbf'

SQL> select group#,thread#,sequence#,status,archived,bytes/1024/1024 mb from v$log;

    GROUP#THREAD#SEQUENCE# STATUSARCHIV

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

        MB

----------

         113040 INACTIVEYES

       300

         313039 INACTIVEYES

       300

         213041 CURRENTNO

       300

恢復需要的3041 Redo日誌實際是當前的線上Redo日誌。

SQL> select group#,member from v$logfile;

    GROUP#

----------

MEMBER

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

         3

/oradata/d012band/redo03.log

2

/oradata/d012band/redo02.log

         1

/oradata/d012band/redo01.log

SQL> recover database using backup controlfile ;

ORA-00279: change 147119073 generated at 06/18/2013 23:01:53 needed for thread

1

ORA-00289: suggestion :

/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_3041_809469738.dbf

ORA-00280: change 147119073 for thread 1 is in sequence #3041

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/oradata/d012band/redo02.log

ORA-00279: change 147119073 generated at 06/18/2013 23:01:53 needed for thread

1

ORA-00289: suggestion :

/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_3041_809469738.dbf

ORA-00280: change 147119073 for thread 1 is in sequence #3041

ORA-00278: log file '/oradata/d012band/redo02.log' no longer needed for this

recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

ORA-00600: internal error code, arguments: [3051], [82], [], [], [], [], [],

[], [], [], [], []

ORA-01112: media recovery not started

將3041的線上Redo日誌/oradata/d012band/redo02.log用於恢復收到ORA-600的報錯。

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/oradata/d012band/system01.dbf'

資料庫依然恢復打開。

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

$ cd dbs

$ ls

hc_d012band.datinit.orainitd012band.oralkD012BAND

$ vi initd*

"initd012band.ora" 4 lines, 131 characters

db_name=d012band

_allow_resetlogs_corruption=true

sga_max_size=5g

sga_target=5g

control_files=('/oradata/d012band/control01.ctl','/oradata/d012band/control02.ctl')

在參數文件中加入_allow_resetlogs_corruption=true參數，強制打開資料庫。

$ sql

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 24 14:08:09 2013

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

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 5344731136 bytes

Fixed Size2230912 bytes

Variable Size956302720 bytes

Database Buffers4378853376 bytes

Redo Buffers7344128 bytes

Database mounted.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [2662], [0], [147119094], [0],

[147119742], [12583040], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [2662], [0], [147119093], [0],

[147119742], [12583040], [], [], [], [], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [147119091], [0],

[147119742], [12583040], [], [], [], [], [], []

Process ID: 5308470

Session ID: 373 Serial number: 3

強制打開收到ORA-600 [2662]的錯誤，需要推進SCN號。

下面是METALINK對該錯誤的說明：

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

修改時間:2013-1-24類型:REFERENCE狀態:PUBLISHED優先順序:3

ote: For additional ORA-600 related information please read Note:146580.1 PURPOSE:

  This article discusses the internal error "ORA-600 [2662]", what

  it means and possible actions. The information here is only applicable

  to the versions listed and is provided only for guidance.

ERROR:

  Format: ORA-600 [2662] [a] [b] [c] [d] [e]

VERSIONS:

  versions 6.0 to 10.1

DESCRIPTION:

  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.

ARGUMENTS:

  Arg [a]Current SCN WRAP

  Arg [b]Current SCN BASE

  Arg [c]dependent SCN WRAP

  Arg [d]dependent SCN BASE

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

FUNCTIONALITY:

  File and IO buffer management for redo logs

IMPACT:

  INSTANCE FAILURE

  POSSIBLE PHYSICAL CORRUPTION

SUGGESTIONS:

  There are different situations where ORA-600 [2662] can be raised.

  It can be raised on startup or duing database operation.

  If not using Parallel Server, check that 2 instances have not mounted

  the same database.

  Check for SMON traces and have the alert.log and trace files ready

  to send to support.

  Check the SCN difference [argument d]-[argument b].

  If the SCNs in the error are very close, then try to shutdown and startup

  the instance several times.

  In some situations, the SCN increment during startup may permit the

  database to open. Keep track of the number of times you attempted a

  startup.

  If the Known Issues section below does not help in terms of identifying

  a solution, please submit the trace files and alert.log to Oracle

  Support Services for further analysis.

  Known Issues:

$ cd $ORACLE_HOME/dbs

$ vi initd*.ora

"initd012band.ora" 5 lines, 164 characters

db_name=d012band

_minimum_giga_scn=1047

_allow_resetlogs_corruption=true

sga_max_size=5g

sga_target=5g

control_files=('/oradata/d012band/control01.ctl','/oradata/d012band/control02.ctl')

~

在參數檔中加入_minimum_giga_scn=1047，將SCN推進到1047*1024*1024這個值（1097859072），大於147119742即可。

$ sql

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 24 14:12:44 2013

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 5344731136 bytes

Fixed Size2230912 bytes

Variable Size956302720 bytes

Database Buffers4378853376 bytes

Redo Buffers7344128 bytes

Database mounted.

Database opened.

資料庫被成功打開並不意味著資料庫正常匯出，注意觀察告警日誌中是否有致命錯誤報出。

arrow
arrow
    文章標籤
    Oracle Oracle資料庫 資料庫錯誤 db erro
    全站熱搜
    創作者介紹
    創作者 Johnson峰 的頭像
    Johnson峰

    Johnson峰的部落格

    Johnson峰 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄