Shulou(Shulou.com)06/02 Report--

Two days ago, when my colleague was testing DATA GUARD, he accidentally overwrote the ONLINE log of the main library, resulting in that the database could not be opened. When opening it with implicit parameters, he encountered an ORA-600 error. Finally, he adopted the method of setting EVENTS to solve the problem.

Because it is operated on someone else's machine, and many of the operations are groped, so the operation is not recorded. Now I'm going to recreate the problem and briefly document the resolution process.

The previous article has described the steps to generate the problem, and the following describes the ways to solve the problem.

Reappearance and solution of ORA-600 (2662) error (1): http://yangtingkun.itpub.net/post/468/464682

The root cause of the problem is that the online LOGFILE of the STANDBY database was overwritten when the PRIMARY database was started.

Try to recover the database:

SQL > CONN / @ YTK AS SYSDBA

Connected to an idle routine.

SQL > STARTUP MOUNT

The ORACLE routine has been started.

Total System Global Area 251658240 bytes

Fixed Size 1290012 bytes

Variable Size 167772388 bytes

Database Buffers 75497472 bytes

Redo Buffers 7098368 bytes

The database is loaded.

SQL > RECOVER DATABASE UNTIL CANCEL

ORA-00279: a change of 5706539056 (generated at 15:49:26 in 12 Thread 11) is required for thread 1

ORA-00289: suggestion: e:\ ORACLE\ ORADATA\ YTK\ ARCHIVELOG\ ARC01333_0577472831.001

ORA-00280: change 5706539056 (for thread 1) in sequence # 1333

Specify log: {= suggested | filename | AUTO | CANCEL}

CANCEL

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS will have the following error

ORA-01194: file 1 needs more recovery to maintain consistency

ORA-01110: data file 1:

'e:\ ORACLE\ ORADATA\ YTK\ DATAFILE\ O1MFsets system 1TDY2CXSIP.DBF'

ORA-01112: media recovery is not started

It seems that the database can no longer be started in a normal way. At this time, the best way is to restore through the backup of the database and restore the database through incomplete recovery.

However, my colleagues did not have a backup of the database at that time, so they had to open it in an unconventional way.

Due to the loss of the online log, you can only add implicit parameters:

SQL > ALTER SYSTEM SET "_ allow_resetlogs_corruption" = TRUE SCOPE = SPFILE

The system has changed.

SQL > SHUTDOWN IMMEDIATE

ORA-01109: the database is not open

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > STARTUP MOUNT

The ORACLE routine has been started.

Total System Global Area 251658240 bytes

Fixed Size 1290012 bytes

Variable Size 171966692 bytes

Database Buffers 71303168 bytes

Redo Buffers 7098368 bytes

The database is loaded.

SQL > RECOVER DATABASE UNTIL CANCEL

ORA-00279: a change of 5706539056 (generated at 15:49:26 in 12 Thread 11) is required for thread 1

ORA-00289: suggestion: e:\ ORACLE\ ORADATA\ YTK\ ARCHIVELOG\ ARC01333_0577472831.001

ORA-00280: change 5706539056 (for thread 1) in sequence # 1333

Specify log: {= suggested | filename | AUTO | CANCEL}

CANCEL

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS will have the following error

ORA-01194: file 1 needs more recovery to maintain consistency

ORA-01110: data file 1:

'e:\ ORACLE\ ORADATA\ YTK\ DATAFILE\ O1MFsets system 1TDY2CXSIP.DBF'

ORA-01112: media recovery is not started

SQL > ALTER DATABASE OPEN RESETLOGS

ALTER DATABASE OPEN RESETLOGS

*

An error occurred on line 1:

ORA-01092: the ORACLE instance is terminated. Force disconnection

Check the alert file:

ALTER DATABASE MOUNT

Tue Dec 11 17:16:19 2007

Setting recovery target incarnation to 1

Tue Dec 11 17:16:19 2007

Successful mount of redo thread 1, with mount id 160883103

Tue Dec 11 17:16:19 2007

Database mounted in Exclusive Mode

Completed: ALTER DATABASE MOUNT

Tue Dec 11 17:16:46 2007

ALTER DATABASE RECOVER DATABASE UNTIL CANCEL

Tue Dec 11 17:16:46 2007

Media Recovery Start

Parallel recovery started with 2 processes

ORA-279 signalled during: ALTER DATABASE RECOVER DATABASE UNTIL CANCEL...

Tue Dec 11 17:16:49 2007

ALTER DATABASE RECOVER CANCEL

ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL...

Tue Dec 11 17:16:51 2007

ALTER DATABASE RECOVER CANCEL

ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL...

Tue Dec 11 17:17:00 2007

ALTER DATABASE OPEN RESETLOGS

Tue Dec 11 17:17:01 2007

RESETLOGS is being done without consistancy checks. This may result

In a corrupted database. The database should be recreated.

Tue Dec 11 17:17:01 2007

Errors in file e:\ oracle\ admin\ ytk\ udump\ ytk_ora_2412.trc:

ORA-00265: instance recovery is required. ARCHIVELOG mode cannot be set.

Archive all online redo logfiles failed:265

RESETLOGS after incomplete recovery UNTIL CHANGE 5706539056

Resetting resetlogs activation ID 97264511 (0x5cc237f)

Online log E:\ ORACLE\ ORADATA\ YTK\ ONLINELOG\ REDO01.LOG: Thread 1 Group 1 was previously cleared

Online log E:\ ORACLE\ ORADATA\ YTK\ ONLINELOG\ REDO02.LOG: Thread 1 Group 2 was previously cleared

Online log E:\ ORACLE\ ORADATA\ YTK\ ONLINELOG\ REDO03.LOG: Thread 1 Group 3 was previously cleared

Tue Dec 11 17:17:01 2007

Setting recovery target incarnation to 2

Tue Dec 11 17:17:02 2007

Assigning activation ID 160883103 (0x996e19f)

LGWR: STARTING ARCH PROCESSES

ARC0 started with pid=18, OS id=2904

Tue Dec 11 17:17:02 2007

ARC0: Archival started

ARC1: Archival started

ARC1 started with pid=19, OS id=2584

Tue Dec 11 17:17:03 2007

LGWR: STARTING ARCH PROCESSES COMPLETE

LNS1 started with pid=20, OS id=3328

Tue Dec 11 17:17:06 2007

Thread 1 advanced to log sequence 2

Thread 1 opened at log sequence 2

Current log# 2 seq# 2 mem# 0: e:\ ORACLE\ ORADATA\ YTK\ ONLINELOG\ REDO02.LOG

Successful open of redo thread 1

Tue Dec 11 17:17:06 2007

ARC1: Becoming the'no FAL' ARCH

ARC1: Becoming the'no SRL' ARCH

Tue Dec 11 17:17:06 2007

ARC0: Becoming the heartbeat ARCH

Tue Dec 11 17:17:06 2007

ARC1: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch

ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect

ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned

Tue Dec 11 17:17:07 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_arc1_2584.trc:

ORA-00270: error creating archive log

ARC1: Error 270Closing archive log file 'ytks'

Tue Dec 11 17:17:07 2007

*

LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

*

Tue Dec 11 17:17:07 2007

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Tue Dec 11 17:17:07 2007

SMON: enabling cache recovery

Tue Dec 11 17:17:08 2007

Errors in file e:\ oracle\ admin\ ytk\ udump\ ytk_ora_2412.trc:

ORA-00600: internal error code. Parameters: [2662], [1], [1411571767], [1], [1411576360], [4194309], []

Tue Dec 11 17:17:11 2007

Errors in file e:\ oracle\ admin\ ytk\ udump\ ytk_ora_2412.trc:

ORA-00600: internal error code. Parameters: [2662], [1], [1411571767], [1], [1411576360], [4194309], []

Tue Dec 11 17:17:11 2007

Error 600 happened during db open, shutting down database

USER: terminating instance due to error 600

Tue Dec 11 17:17:11 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_lns1_3328.trc:

ORA-00600: internal error code, parameter: [], []

Tue Dec 11 17:17:11 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_p001_1936.trc:

ORA-00600: internal error code. Parameters: [15784], [], []

ORA-00600: internal error code, parameter: [], []

Tue Dec 11 17:17:11 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_p000_576.trc:

ORA-00600: internal error code. Parameters: [15784], [], []

ORA-00600: internal error code, parameter: [], []

Tue Dec 11 17:17:11 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_pmon_3424.trc:

ORA-00600:?,??: [], []

Tue Dec 11 17:17:12 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_psp0_1720.trc:

ORA-00600:?,??: [], []

Tue Dec 11 17:17:12 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_mman_3256.trc:

ORA-00600:?,??: [], []

Tue Dec 11 17:17:12 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_lgwr_3528.trc:

ORA-00600:?,??: [], []

Tue Dec 11 17:17:12 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_ckpt_2696.trc:

ORA-00600:?,??: [], []

Tue Dec 11 17:17:12 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_dbw0_2148.trc:

ORA-00600:?,??: [], []

Tue Dec 11 17:17:14 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_reco_1928.trc:

ORA-00600:?,??: [], []

Tue Dec 11 17:17:14 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_smon_1940.trc:

ORA-00600:?,??: [], []

Instance terminated by USER, pid = 2412

ORA-1092 signalled during: ALTER DATABASE OPEN RESETLOGS...

The ORA-600 [2662] error mentioned earlier has now occurred.

Query metalink,Oracle gives two solutions. If there is little difference between the third parameter and the fifth parameter in the 2662 error, you can increase the value of SCN by opening and closing the database instance multiple times.

However, in this way, only SCN is increased by 1 per boot, and the system is restarted three times:

ORA-00600: internal error code. Parameters: [2662], [1], [1411571770], [1], [1411576360], [4194309], []

However, there is still a difference of more than 4000 between the current SCN and the expected SCN, so it is obviously not realistic to restart.

The second method given by Oracle is to improve the CURRENT SCN of Oracle by setting EVENTS. The premise of this method is to set the implicit parameter _ allow_resetlogs_corruption to true:

SQL > CONN / @ YTK AS SYSDBA

Connected to an idle routine.

SQL > STARTUP MOUNT

The ORACLE routine has been started.

Total System Global Area 251658240 bytes

Fixed Size 1290012 bytes

Variable Size 209715428 bytes

Database Buffers 33554432 bytes

Redo Buffers 7098368 bytes

The database is loaded.

SQL > ALTER SESSION SET EVENTS '10015 TRACE NAME ADJUST_SCN LEVEL 1'

The session has changed.

SQL > ALTER DATABASE OPEN

ALTER DATABASE OPEN

*

An error occurred on line 1:

ORA-00314: the sequence number 2 required by log 2 (for thread 1) does not match 0

ORA-00312: online log 2 thread 1: e:\ ORACLE\ ORADATA\ YTK\ ONLINELOG\ REDO02.LOG'

The database is not open, but an error ORA-314 is reported. This error is easy to understand because the current log is overwritten by the reset archive of the STANDBY database, resulting in the expected log and the current dissatisfaction.

The following renames the three connection logs so that the PRIMARY database cannot find the online logs:

E:\ > CD E:\ ORACLE\ ORADATA\ YTK\ ONLINELOG

E:\ oracle\ oradata\ YTK\ ONLINELOG > MOVE REDO01.LOG REDO01.LOG.BAK

E:\ oracle\ oradata\ YTK\ ONLINELOG > MOVE REDO02.LOG REDO02.LOG.BAK

E:\ oracle\ oradata\ YTK\ ONLINELOG > MOVE REDO03.LOG REDO03.LOG.BAK

Try to open it again:

SQL > ALTER DATABASE OPEN

ALTER DATABASE OPEN

*

An error occurred on line 1:

ORA-00313: unable to open a member of log group 2 (for thread 1)

ORA-00312: online log 2 thread 1: e:\ ORACLE\ ORADATA\ YTK\ ONLINELOG\ REDO02.LOG'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

If an error was reported, the file could not be found. Create a new log by RESETLOGS:

SQL > RECOVER DATABASE UNTIL CANCEL

ORA-00279: a change of 5706539077 (generated at 14:17:14 in 12 shock 2007) is required for thread 1

ORA-00289: suggestion: e:\ ORACLE\ ORADATA\ YTK\ ARCHIVELOG\ ARC00002_0641139429.001

ORA-00280: change 5706539077 (for thread 1) in sequence # 2

Specify log: {= suggested | filename | AUTO | CANCEL}

CANCEL

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS will have the following error

ORA-01194: file 1 needs more recovery to maintain consistency

ORA-01110: data file 1:

'e:\ ORACLE\ ORADATA\ YTK\ DATAFILE\ O1MFsets system 1TDY2CXSIP.DBF'

ORA-01112: media recovery is not started

SQL > ALTER DATABASE OPEN RESETLOGS

ALTER DATABASE OPEN RESETLOGS

*

An error occurred on line 1:

ORA-01092: the ORACLE instance is terminated. Force disconnection

Try setting up EVENTS again:

SQL > CONN / @ YTK AS SYSDBA

Connected to an idle routine.

SQL > STARTUP MOUNT

The ORACLE routine has been started.

Total System Global Area 251658240 bytes

Fixed Size 1290012 bytes

Variable Size 218104036 bytes

Database Buffers 25165824 bytes

Redo Buffers 7098368 bytes

The database is loaded.

SQL > ALTER SESSION SET EVENTS '10015 TRACE NAME ADJUST_SCN LEVEL 1'

The session has changed.

SQL > ALTER DATABASE OPEN

ERROR:

ORA-03113: end of file for communication channel

There is a difference between the error and just now. There seems to be a hope of opening it. Check the alert file and find out:

ORA-00316: 2 (1) 0?

ORA-00312:? 2? 1:'e:\ ORACLE\ ORADATA\ YTK\ ONLINELOG\ REDO02.LOG'

Wed Dec 12 14:45:55 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_lgwr_3712.trc:

ORA-00316: 2 (1) 0?

ORA-00312:? 2? 1:'e:\ ORACLE\ ORADATA\ YTK\ ONLINELOG\ REDO02.LOG'

Wed Dec 12 14:45:55 2007

LGWR: terminating instance due to error 316

Wed Dec 12 14:45:55 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_lns1_3508.trc:

ORA-00316: the type in the log (for threads) header is not a log file

Wed Dec 12 14:45:55 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_arc1_536.trc:

ORA-00316: the type in the log (for threads) header is not a log file

Wed Dec 12 14:45:55 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_arc1_536.trc:

ORA-00316: the type in the log (for threads) header is not a log file

Wed Dec 12 14:45:55 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_arc1_536.trc:

ORA-00316: the type in the log (for threads) header is not a log file

Wed Dec 12 14:45:55 2007

Master background archival failure: 316

Wed Dec 12 14:45:56 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_arc0_3656.trc:

ORA-00316: the type in the log (for threads) header is not a log file

Wed Dec 12 14:45:56 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_arc0_3656.trc:

ORA-00316: the type in the log (for threads) header is not a log file

Wed Dec 12 14:45:56 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_arc0_3656.trc:

ORA-00316: the type in the log (for threads) header is not a log file

Wed Dec 12 14:45:56 2007

Master background archival failure: 316

Wed Dec 12 14:45:56 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_dbw0_3716.trc:

ORA-00316: (?)

Wed Dec 12 14:45:58 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_pmon_3608.trc:

ORA-00316: (?)

Wed Dec 12 14:45:58 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_psp0_1816.trc:

ORA-00316: (?)

Wed Dec 12 14:45:58 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_mman_2304.trc:

ORA-00316: (?)

Wed Dec 12 14:45:58 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_reco_3408.trc:

ORA-00316: (?)

Wed Dec 12 14:45:58 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_ckpt_2296.trc:

ORA-00316: (?)

Wed Dec 12 14:45:58 2007

Errors in file e:\ oracle\ admin\ ytk\ bdump\ ytk_smon_3416.trc:

ORA-00316: (?)

Wed Dec 12 14:45:59 2007

Instance terminated by LGWR, pid = 3712

Reported a bunch of ORA-316 and ORA-312 errors. To avoid interference with the STANDBY database that is still started, close the STANDBY database and try to open the database in RESETLOGS again:

SQL > CONN / @ YTKS AS SYSDBA

Connected.

SQL > SHUTDOWN IMMEDIATE

ORA-01109: the database is not open

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > CONN / @ YTK AS SYSDBA

Connected to an idle routine.

SQL > STARTUP MOUNT

The ORACLE routine has been started.

Total System Global Area 251658240 bytes

Fixed Size 1290012 bytes

Variable Size 222298340 bytes

Database Buffers 20971520 bytes

Redo Buffers 7098368 bytes

The database is loaded.

SQL > RECOVER DATABASE UNTIL CANCEL

Complete the media recovery.

SQL > ALTER DATABASE OPEN RESETLOGS

The database has changed.

The database is finally open. The next thing to do is to remove the implicit parameters, restart again, and verify that it can be started.

SQL > ALTER SYSTEM RESET "_ allow_resetlogs_corruption" SCOPE = SPFILE SID ='*'

The system has changed.

SQL > SHUTDOWN IMMEDIATE

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > STARTUP

The ORACLE routine has been started.

Total System Global Area 251658240 bytes

Fixed Size 1290012 bytes

Variable Size 218104036 bytes

Database Buffers 25165824 bytes

Redo Buffers 7098368 bytes

The database is loaded.

The database is already open.

The database has been opened normally, but due to the use of _ allow_resetlogs_corruption mode, the data will be lost, and the state of the database is inconsistent. Therefore, in this case, Oracle recommends exporting the database through EXP. After rebuilding the new database, import it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.