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

In this issue, the editor will bring you the reproduction and solutions of ORA-600 2662 errors. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

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.

This article first tries to reproduce the problem.

Existing environment, Oracle10.2.0.3 for windows database. To fully simulate the way the problem works, first create a STANDBY database.

Before creating a STANDBY database, make a backup of the database. In this way, if there is a problem with the test, the damaged database can not be recovered, and the database can also be restored through this backup.

The following is to prepare for the creation of a STANDBY database, which is not described in detail only if the operations are listed here:

SQL > ALTER DATABASE FORCE LOGGING

The database has changed.

SQL > ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG= (ytk,ytks)'

The system has changed.

SQL > ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=E:\ oracle\ oradata\ YTK\ archivelog VALID_FOR= (ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ytk'

The system has changed.

SQL > ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ytks LGWR ASYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ytks'

The system has changed.

SQL > ALTER SYSTEM SET FAL_SERVER=ytks

The system has changed.

SQL > ALTER SYSTEM SET FAL_CLIENT=ytk

The system has changed.

SQL > ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO

The system has changed.

Create a PFILE:

SQL > CREATE PFILE='E:\ ORACLE\ ORADATA\ YTKS\ INITYTKS.ORA' FROM SPFILE

The file has been created.

Add and modify the following initialization parameters:

* .audit_file_dest='E:\ oracle\ admin\ ytks\ adump'

* .background_dump_dest='E:\ oracle\ admin\ ytks\ bdump'

* .control_files='E:\ ORACLE\ ORADATA\ YTK_S\ CONTROLFILE\ CONTROL01.CTL'

* .core_dump_dest='E:\ oracle\ admin\ ytks\ cdump'

* .user_dump_dest='E:\ oracle\ admin\ ytks\ udump'

* .fal_client='ytks'

* .fal_server='ytk'

* .log_archive_dest_1='LOCATION=E:\ oracle\ oradata\ ytks\ archivelog VALID_FOR= (ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ytks'

* .log_archive_dest_2='SERVICE=ytk LGWR ASYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ytk'

Standby_archive_dest='LOCATION=E:\ oracle\ oradata\ ytks\ archivelog'

Db_unique_name='ytks'

Lock_name_space='ytks'

Db_file_name_convert= ('E:\ oracle\ oradata\ ytk','E:\ oracle\ oradata\ ytks')

Log_file_name_convert= ('E:\ oradle\ oradata\ ytk','E:\ oracle\ oradata\ ytks')

It should be noted that the LOG_FILE_NAME_CONVERT parameter is set incorrectly and E:\ oracle is written as E:\ oraDle. This error means that the LOG_FILE_NAME_CONVERT parameter is meaningless. And because the PRIMARY database and the STANDBY database are on the same server, the STANDBY database will clear the LOGFILE of the PRIMARY database after starting RESETLOG.

Close the database and copy the data files to the directory of the STANDBY database by cold backup:

SQL > SHUTDOWN IMMEDIATE

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

E:\ > COPY E:\ ORACLE\ ORADATA\ YTK\ DATAFILE\ * E:\ ORACLE\ ORADATA\ YTKS\ DATAFILE

E:\ ORACLE\ ORADATA\ YTK\ DATAFILE\ O1_MF_SYSAUX_1TDY3TRF_.DBF

E:\ ORACLE\ ORADATA\ YTK\ DATAFILE\ O1_MF_SYSTEM_1TDY2CXS_.DBF

E:\ ORACLE\ ORADATA\ YTK\ DATAFILE\ O1_MF_TEMP_1TDY49L2_.TMP

E:\ ORACLE\ ORADATA\ YTK\ DATAFILE\ O1_MF_UNDOTBS1_1TDY3GNZ_.DBF

E:\ ORACLE\ ORADATA\ YTK\ DATAFILE\ O1_MF_USERS_1TDY4JMK_.DBF

E:\ ORACLE\ ORADATA\ YTK\ DATAFILE\ O1_MF_YANGTK_1THCKSRW_.DBF

E:\ ORACLE\ ORADATA\ YTK\ DATAFILE\ O1_MF_YANGTK_22MWCD5C_.DBF

E:\ ORACLE\ ORADATA\ YTK\ DATAFILE\ TEST01.DBF

Eight files have been copied.

E:\ > COPY E:\ ORACLE\ ORADATA\ YTK\ ONLINELOG\ * E:\ ORACLE\ ORADATA\ YTKS\ ONLINELOG

E:\ ORACLE\ ORADATA\ YTK\ ONLINELOG\ REDO01.LOG

E:\ ORACLE\ ORADATA\ YTK\ ONLINELOG\ REDO02.LOG

E:\ ORACLE\ ORADATA\ YTK\ ONLINELOG\ REDO03.LOG

3 files have been copied.

Start the PRIMARY database to create STANDBY CONTROLFILE:

SQL > STARTUP

The ORACLE routine has been started.

Total System Global Area 251658240 bytes

Fixed Size 1290012 bytes

Variable Size 163578084 bytes

Database Buffers 79691776 bytes

Redo Buffers 7098368 bytes

The database is loaded.

The database is already open.

SQL > ALTER DATABASE CREATE STANDBY CONTROLFILE AS'e:\ ORACLE\ ORADATA\ YTKS\ CONTROLFILE\ CONTROL01.CTL'

The database has changed.

E:\ > COPY E:\ ORACLE\ 10.2\ DATABASE\ PWDYTK.ORA E:\ ORACLE\ 10.2\ DATABASE\ PWDYTKS.ORA

1 file has been copied.

After copying the database password file, the following services for ORACLE are established:

E:\ > ORADIM-NEW-SID YTKS-STARTMODE MANUAL

The instance has been created.

Edit the tnsnames.ora and listener.ora files, add STANDBY database information, and restart listening.

Start the STANDBY database to the MOUNT state below:

SQL > CONN / @ YTKS AS SYSDBA

Connected to an idle routine.

SQL > CREATE SPFILE FROM PFILE='E:\ ORACLE\ ORADATA\ YTKS\ INITYTKS.ORA'

The file has been created.

SQL > STARTUP MOUNT

The ORACLE routine has been started.

Total System Global Area 251658240 bytes

Fixed Size 1290012 bytes

Variable Size 83886308 bytes

Database Buffers 159383552 bytes

Redo Buffers 7098368 bytes

The database is loaded.

Due to the incorrect setting of the parameter LOG_FILE_NAME_CONVERT, when the STANDBY database is placed in the archived state, the logs of the PRIMARY database are overwritten, resulting in an error in the PRIMARY database.

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

The database has changed.

The following error occurs when you close the PRIMARY database:

SQL > CONN / @ YTK AS SYSDBA

Connected.

SQL > SHUTDOWN IMMEDIATE

ORA-03113: end of file for communication channel

SQL > CONN / @ YTK AS SYSDBA

Connected to an idle routine.

SQL > STARTUP

ORA-03113: end of file for communication channel

Check that the alert file has encountered an error:

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

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

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

Tue Dec 11 16:14:05 2007

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

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

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

Tue Dec 11 16:14:05 2007

LGWR: terminating instance due to error 314

Instance terminated by LGWR, pid = 3588

At this point, the database problem has been simulated.

The above is the reproduction of the ORA-600 2662 error and the solution shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.