ORA 600 [2252] [3700]

一：ORA-0600 [2252]错误解决实例

昨天由于长时间断电，导致数据库掉电关闭，今天重启数据库时，报600错误，

告警日志文件中错误如下：

Errors in file d:/oracle/admin/tms166/udump/tms166_ora_3092.trc:

ORA-00600: ?????????: [2252], [2481], [3763688818], [], [], [], [], []

 

ORA-600 signalled during: alter database open...

 

(数据库版本：ORACLE RDBMS Version: 10.2.0.3.0.)

解决：

Subject:         ORA-600 [2252] Occurs During Alter Database Open
          Doc ID:         Note:253977.1         Type:         PROBLEM
          Last Revision Date:         19-OCT-2004         Status:         PUBLISHED


Symptom(s)
~~~~~~~~~~

After shutdown of the database you receive the following error during
alter database open:  ora-600 [2252]

You did not restore the database before receiving this error.


Cause
~~~~~~~

The system date is lower than the date of the shutdown.

While opening the database, Oracle compares the given SCN value
with the reasonable upper limit value calculated based on the
system date. If Oracle detects the provided scn is too large,
ORA-600[2252] would be raised.

Fix
~~~~
Set the system date equal or higher the date of the shutdown.

 

根据以上给出的方法，检查操作系统时间，居然是2000年01月01日。

将操作系统时间改为当前时间，成功启动数据库。

数据库解决案例 ORA-600 [3700]

报错信息：

Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default vals:
  processes                = 150
  __shared_pool_size       = 109051904
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 8388608
  spfile                   = D:/ORACLE/PROD T/10.2.0/DB_1/DBS/SPFILEORCL.ORA
  sga_target               = 612368384
  control_files            = D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/CONTROL01.CTL, D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/CONTROL02.CTL, D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/CONTROL03.CTL
  db_block_size            = 8192
  __db_cache_size          = 478150656
  compatible               = 10.2.0.1.0
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = d:/oracle/prod t/10.2.0/flash_recovery_area
  db_recovery_file_dest_size= 2147483648
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  dispatchers              = (PROTOCOL=TCP) (SERVICE=orclXDB)
  job_q_processes      = 10
  a it_file_dest          = D:/ORACLE/PROD T/10.2.0/ADMIN/ORCL/ADUMP
  background_dump_dest     = D:/ORACLE/PROD T/10.2.0/ADMIN/ORCL/BDUMP
  user_dump_dest           = D:/ORACLE/PROD T/10.2.0/ADMIN/ORCL/ UMP
  core_dump_dest           = D:/ORACLE/PROD T/10.2.0/ADMIN/ORCL/CDUMP
  db_name                  = orcl
  open_cursors             = 300
  pga_aggregate_target     = 203423744
PMON started with pid=2, OS id=3068
PSP0 started with pid=3, OS id=3684
MMAN started with pid=4, OS id=4328
DBW0 started with pid=5, OS id=4272
LGWR started with pid=6, OS id=3580
CKPT started with pid=7, OS id=5796
SMON started with pid=8, OS id=4932
RECO started with pid=9, OS id=5940
CJQ0 started with pid=10, OS id=5508
MMON started with pid=11, OS id=4372
MMNL started with pid=12, OS id=4132
Mon Oct 18 18:16:23 2010
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
Mon Oct 18 18:16:24 2010
ALTER DATABASE   MOUNT
Mon Oct 18 18:16:28 2010
Setting recovery target incarnation to 2
Mon Oct 18 18:16:28 2010
S cessful mount of redo thread 1, with mount id 1260511864
Mon Oct 18 18:16:28 2010
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Mon Oct 18 18:16:29 2010
ALTER DATABASE OPEN
Mon Oct 18 18:16:30 2010
Errors in file d:/oracle/prod t/10.2.0/admin/orcl/bdump/orcl_lgwr_3580.trc:
ORA-00600: internal error code, arguments: [3700], [1], [15], [3], [3], [], [], []

Mon Oct 18 18:16:31 2010
Errors in file d:/oracle/prod t/10.2.0/admin/orcl/bdump/orcl_lgwr_3580.trc:
ORA-00600: internal error code, arguments: [3700], [1], [15], [3], [3], [], [], []

出现问题的描述：

       根据他所说的是，他在启动数据库时，报有控制文件损坏，他就使用了1个控制文件复制出来了3个，数据库可以启动到mount了。后报数据文件错误，recover ……，在open数据库时出现以上保存信息，而且每次重新连接数据库都是连接到空实例。

      但是600错误具体是不是断电或复制出来的控制文件造成的真说不清楚。

      meatlink上边居然没有关于3700的说法，但是有人说可能是controlfile的问题，因本库是测试机，所以冷备份数据后进行操作。

      因本库为非归档模式且无rman备份，只能选择重建控制文件的方案！

具体方案
   1. backup controlfile to trace
   2. modified trace script of controlfile 
   3. startup nomount 
   4. run the scripts for create control file. 
   5. recover database

   6. alter database open

   7.add tempfile

以下重建控制文件详细信息：

C:/Documents and Settings/kimchoonho>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Prod tion on 星期二 10月 19 09:06:51 2010

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

已连接到空闲例程。

SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             125832068 bytes
Database B?rs          478150656 bytes
Redo B?rs                7135232 bytes
数据库装载完毕。
SQL> alter database backup controlfile to trace;

数据库已更改。

 

查看trace文件：

Dump file d:/oracle/prod t/10.2.0/admin/orcl/ ump/orcl_ora_2428.trc
T Oct 19 09:07:12 2010
ORACLE V10.2.0.1.0 - Prod tion vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod tion
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 3
CPU                 : 4 - type 586, 4 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:2224M/3327M, Ph+PgF:3396M/5210M, VA:1317M/2047M
Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 15

Windows thread id: 2428, image: ORACLE.EXE (SHAD)

*** SERVICE NAME:() 2010-10-19 09:07:12.812
*** SESSION ID:(159.3) 2010-10-19 09:07:12.812
*** 2010-10-19 09:07:12.812
-- The following are current System-scope REDO Log Archival related
-- parameters and can be incl ed in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=ARC%S_%R.%T
--
-- DB_UNIQ_NAME="orcl"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MAN L
-- STANDBY_ARCHIVE_DEST=%ORACLE_HOME%/RDBMS
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQ_NAME'
-- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_10=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/REDO01.LOG'  SIZE 50M,
  GROUP 2 'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/REDO02.LOG'  SIZE 50M,
  GROUP 3 'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/REDO03.LOG'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/SYSTEM01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/UNDOTBS01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/SYSAUX01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/USERS01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/EXAMPLE01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/SMS.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/TSSMS001.DBF',
  'E:/ORACLE/MAPUSE_DATA01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/H NGJIANAN01.DBF',
  'E:/ORACLE/W O_DATA01.DBF'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE 'D:/ORACLE/PROD T/10.2.0/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2010_10_19/O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'D:/ORACLE/PROD T/10.2.0/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2010_10_19/O1_MF_1_1_%U_.ARC';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/TEMP01.DBF' REUSE;
ALTER TABLESPACE MAPUSER_TEMP ADD TEMPFILE 'E:/ORACLE/MAPUSER_TEMP.DBF' REUSE;
ALTER TABLESPACE TEST_TEMP ADD TEMPFILE 'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/H NGJIANAN.DBF' REUSE;
ALTER TABLESPACE H NGJIANAN_TEMP ADD TEMPFILE 'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/H NGJIANAND.DBF' REUSE;
ALTER TABLESPACE W O_TEMP ADD TEMPFILE 'E:/ORACLE/W O_TEMP01.DBF' REUSE;
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/REDO01.LOG'  SIZE 50M,
  GROUP 2 'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/REDO02.LOG'  SIZE 50M,
  GROUP 3 'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/REDO03.LOG'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/SYSTEM01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/UNDOTBS01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/SYSAUX01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/USERS01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/EXAMPLE01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/SMS.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/TSSMS001.DBF',
  'E:/ORACLE/MAPUSE_DATA01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/H NGJIANAN01.DBF',
  'E:/ORACLE/W O_DATA01.DBF'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE 'D:/ORACLE/PROD T/10.2.0/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2010_10_19/O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'D:/ORACLE/PROD T/10.2.0/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2010_10_19/O1_MF_1_1_%U_.ARC';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/TEMP01.DBF' REUSE;
ALTER TABLESPACE MAPUSER_TEMP ADD TEMPFILE 'E:/ORACLE/MAPUSER_TEMP.DBF' REUSE;
ALTER TABLESPACE TEST_TEMP ADD TEMPFILE 'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/H NGJIANAN.DBF' REUSE;
ALTER TABLESPACE H NGJIANAN_TEMP ADD TEMPFILE 'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/H NGJIANAND.DBF' REUSE;
ALTER TABLESPACE W O_TEMP ADD TEMPFILE 'E:/ORACLE/W O_TEMP01.DBF' REUSE;
-- End of tempfile additions.

编辑这个trace文件,我们就可以获得创建控制文件的脚本（cctl.sql）.

根据数据库不同状况，你可以选择是使用RESETLOGS/NORESETLOGS来重建控制文件.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/REDO01.LOG'  SIZE 50M,
  GROUP 2 'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/REDO02.LOG'  SIZE 50M,
  GROUP 3 'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/REDO03.LOG'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/SYSTEM01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/UNDOTBS01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/SYSAUX01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/USERS01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/EXAMPLE01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/SMS.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/TSSMS001.DBF',
  'E:/ORACLE/MAPUSE_DATA01.DBF',
  'D:/ORACLE/PROD T/10.2.0/ORADATA/ORCL/H NGJIANAN01.DBF',
  'E:/ORACLE/W O_DATA01.DBF'
CHARACTER SET ZHS16GBK
;

SQL*Plus: Release 10.2.0.1.0 - Prod tion on 星期二 10月 19 09:12:24 2010

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

已连接到空闲例程。

SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             125832068 bytes
Database B?rs          478150656 bytes
Redo B?rs                7135232 bytes
SQL> @cctl.sql

控制文件已创建。

SQL> RECOVER DATABASE
完成介质恢复。

如是归档模式使用：ALTER SYSTEM ARCHIVE LOG ALL;

此时可以打开数据库了。

SQL> ALTER DATABASE OPEN;

数据库已更改。

新重建控制文件是不建立临时表空间的，数据库备份也不会备份临时表空间，所以alert文件会报警告：

*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
           Empty temporary tablespace: MAPUSER_TEMP
           Empty temporary tablespace: TEST_TEMP
           Empty temporary tablespace: H NGJIANAN_TEMP
           Empty temporary tablespace: W O_TEMP
*********************************************************************

添加临时表空间文件：

 ALTER TABLESPACE TEMP ADD TEMPFILE 'D:/oracle/prod t/10.2.0/oradata/orcl/temp01.dbf' SIZE 524288000 REUSE AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;

 ALTER TABLESPACE MAPUSER_TEMP ADD TEMPFILE 'D:/oracle/prod t/10.2.0/oradata/orcl/MAPUSER_TEMP.dbf' SIZE 524288000 REUSE AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;

 ALTER TABLESPACE TEST_TEMP ADD TEMPFILE 'D:/oracle/prod t/10.2.0/oradata/orcl/TEST_TEMP.dbf' SIZE 524288000 REUSE AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;

 ALTER TABLESPACE H NGJIANAN_TEMP ADD TEMPFILE 'D:/oracle/prod t/10.2.0/oradata/orcl/H NGJIANAN_TEMP.dbf' SIZE 524288000 REUSE AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;

 ALTER TABLESPACE W O_TEMP ADD TEMPFILE 'D:/oracle/prod t/10.2.0/oradata/orcl/W O_TEMP.dbf' SIZE 524288000 REUSE AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;