ORA-600 [25016] internal error when ALTER DATABASE OPEN ORA-01092

张维照 2019-05-31
11
0 0
摘要:前几天修改了基表,在重建控制文件时遇到了这个问题,很可能是当前系统的数据字典还存在问题

问题描述

前几天修改了基表,在重建控制文件时遇到了这个问题,记录一下

很可能是当前系统的数据字典还存在问题


专家解答

SYS>select file_id,file_name,tablespace_name from dba_data_files order by 1;                               

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         1 /oracle/oradata/ncme/system01.dbf                  SYSTEM
         2 /oracle/product/10.2.0/dbs/BJHR_INDEX              BJHR_INDEX
         3 /oracle/oradata/ncme/sysaux01.dbf                  SYSAUX
         4 /oracle/oradata/ncme/users01.dbf                   USERS
         5 /oracle/oradata/ncme/BJHR_DEV.dbf                  BJHR_DEV
         6 /oracle/oradata/ncme/ncme01.dbf                    NCME
         7 /oracle/oradata/ncme/undotbs01.dbf                 UNDOTBS1
         9 /oracle/oradata/ncme/keyan01.dbf                   KEYAN
        12 /oracle/oradata/ncme/users02.dbf                   USERS

9 rows selected.

SYS>select file#,name from v$datafile;                                                                     

     FILE# NAME
---------- ------------------------------------------------------------
         1 /oracle/oradata/ncme/system01.dbf
         2 /oracle/product/10.2.0/dbs/BJHR_INDEX
         3 /oracle/oradata/ncme/sysaux01.dbf
         4 /oracle/oradata/ncme/users01.dbf
         5 /oracle/oradata/ncme/BJHR_DEV.dbf
         6 /oracle/oradata/ncme/ncme01.dbf
         7 /oracle/oradata/ncme/undotbs01.dbf
         9 /oracle/oradata/ncme/keyan01.dbf
        12 /oracle/oradata/ncme/users02.dbf
        14 /oracle/oradata/ncme/undotbs02.dbf

10 rows selected.


SYS>select file#,ts# from file$;                                                                           

     FILE#        TS#
---------- ----------
         1          0
         2         30
         3          2
         4          4
         5         25
         6          7
         7          1
         8
         9         18
        10
        11
        12          4
        13

13 rows selected.

With the Partitioning, OLAP and Data Mining options
[oracle@ora10g ~]$ ll -h /oracle/oradata/ncme/
total 45G
-rw-r-----  1 oracle oinstall 960M Mar 22 14:31 BJHR_DEV.dbf
-rw-r-----  1 oracle oinstall 7.3M Mar 22 14:36 control01.ctl
-rw-r-----  1 oracle oinstall 7.3M Mar 22 14:36 control02.ctl
-rw-r-----  1 oracle oinstall 7.3M Mar 22 14:36 control03.ctl
-rw-r-----  1 oracle oinstall  81M Mar 22 14:31 keyan01.dbf
-rw-r-----  1 oracle oinstall 2.1G Mar 22 14:31 ncme01.dbf
-rw-r-----  1 oracle oinstall  51M Mar 22 14:36 redo01.log
-rw-r-----  1 oracle oinstall  51M Mar 22 14:31 redo02.log
-rw-r-----  1 oracle oinstall  51M Mar 22 14:31 redo03.log
-rw-r-----  1 oracle oinstall 1.3G Mar 22 14:36 sysaux01.dbf
-rw-r-----  1 oracle oinstall 1.3G Mar 22 14:36 system01.dbf
-rw-r-----  1 oracle oinstall 2.1G Mar 22 10:20 temp02.dbf
-rw-r-----  1 oracle oinstall  11M Mar 21 17:58 test.dbf
-rw-r-----  1 oracle oinstall 1.1G Mar 22 14:36 undotbs01.dbf
-rw-r-----  1 oracle oinstall  23G Mar 22 14:31 users01.dbf
-rw-r-----  1 oracle oinstall  14G Mar 22 14:31 users02.dbf


SYS>alter database backup controlfile to trace;                                                            

Database altered.

SYS>exit                                                                                                   
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@ora10g ~]$ cd /oracle/admin/ncme/udump/
[oracle@ora10g udump]$ ll -t|head
total 36
-rw-r-----  1 oracle oinstall 6660 Mar 22 14:37 ncme_ora_28507.trc
-rw-r-----  1 oracle oinstall 2229 Mar 22 14:31 ncme_ora_28457.trc
-rw-r-----  1 oracle oinstall  641 Mar 22 14:26 ncme_ora_28454.trc
-rw-r-----  1 oracle oinstall  584 Mar 22 14:26 ncme_ora_28429.trc
-rw-r-----  1 oracle oinstall  769 Mar 22 14:26 ncme_ora_28423.trc
-rw-r-----  1 oracle oinstall  641 Mar 22 14:22 ncme_ora_28383.trc
-rw-r-----  1 oracle oinstall  612 Mar 22 14:22 ncme_ora_28357.trc
-rw-r-----  1 oracle oinstall  806 Mar 22 14:14 ncme_ora_28268.trc

[oracle@ora10g udump]$ vi ncme_ora_28507.trc

/oracle/admin/ncme/udump/ncme_ora_28507.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/product/10.2.0
System name:    Linux
Node name:      ora10g
Release:        2.6.9-55.ELsmp
Version:        #1 SMP Wed May 2 14:28:44 EDT 2007
Machine:        i686
Instance name: ncme
Redo thread mounted by this instance: 1
Oracle process number: 14
Unix process pid: 28507, image: oracle@ora10g (TNS V1-V3)

--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "NCME" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 4672
LOGFILE
  GROUP 1 '/oracle/oradata/ncme/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/ncme/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/ncme/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/ncme/system01.dbf',
  '/oracle/product/10.2.0/dbs/BJHR_INDEX',
  '/oracle/oradata/ncme/sysaux01.dbf',
  '/oracle/oradata/ncme/users01.dbf',
  '/oracle/oradata/ncme/BJHR_DEV.dbf',
  '/oracle/oradata/ncme/ncme01.dbf',
  '/oracle/oradata/ncme/undotbs01.dbf',
  '/oracle/oradata/ncme/keyan01.dbf',
  '/oracle/oradata/ncme/users02.dbf',
  '/oracle/oradata/ncme/undotbs02.dbf'
CHARACTER SET ZHS16GBK
;
-- Take files offline to match current control file.
ALTER DATABASE DATAFILE '/oracle/oradata/ncme/undotbs02.dbf' OFFLINE DROP;
-- 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 '/oracle/flash_recovery_area/NCME/archivelog/2012_03_22/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/oracle/flash_recovery_area/NCME/archivelog/2012_03_22/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;


[oracle@ora10g udump]$ cp ncme_ora_28507.trc /home/oracle/re_createctl.sql
[oracle@ora10g udump]$ cd 
[oracle@ora10g ~]$ vi re_createctl.sql 


--##去掉了,
  '/oracle/oradata/ncme/undotbs02.dbf'和ALTER DATABASE DATAFILE '/oracle/oradata/ncme/undotbs02.dbf' OFFLINE DROP;

sql>startup nomount                                                                                                                                                              
ORACLE instance started.

Total System Global Area  603979776 bytes
Fixed Size                  1220796 bytes
Variable Size             218107716 bytes
Database Buffers          377487360 bytes
Redo Buffers                7163904 bytes
sql>startup nomount                                                                                                                                                              
ORACLE instance started.

Total System Global Area  603979776 bytes
Fixed Size                  1220796 bytes
Variable Size             218107716 bytes
Database Buffers          377487360 bytes
Redo Buffers                7163904 bytes
sql>CREATE CONTROLFILE REUSE DATABASE "NCME" NORESETLOGS  NOARCHIVELOG                                                                                                           
  2      MAXLOGFILES 16                                                                                                                                                          
  3      MAXLOGMEMBERS 3                                                                                                                                                         
  4      MAXDATAFILES 100                                                                                                                                                        
  5      MAXINSTANCES 8                                                                                                                                                          
  6      MAXLOGHISTORY 4672                                                                                                                                                      
  7  LOGFILE                                                                                                                                                                     
  8    GROUP 1 '/oracle/oradata/ncme/redo01.log'  SIZE 50M,                                                                                                                      
  9    GROUP 2 '/oracle/oradata/ncme/redo02.log'  SIZE 50M,                                                                                                                      
 10    GROUP 3 '/oracle/oradata/ncme/redo03.log'  SIZE 50M                                                                                                                       
 11  -- STANDBY LOGFILE                                                                                                                                                          
 12  DATAFILE                                                                                                                                                                    
 13    '/oracle/oradata/ncme/system01.dbf',                                                                                                                                      
 14    '/oracle/product/10.2.0/dbs/BJHR_INDEX',                                                                                                                                  
 15    '/oracle/oradata/ncme/sysaux01.dbf',                                                                                                                                      
 16    '/oracle/oradata/ncme/users01.dbf',                                                                                                                                       
 17    '/oracle/oradata/ncme/BJHR_DEV.dbf',                                                                                                                                      
 18    '/oracle/oradata/ncme/ncme01.dbf',                                                                                                                                        
 19    '/oracle/oradata/ncme/undotbs01.dbf',                                                                                                                                     
 20    '/oracle/oradata/ncme/keyan01.dbf',                                                                                                                                       
 21    '/oracle/oradata/ncme/users02.dbf'                                                                                                                                        
 22  CHARACTER SET ZHS16GBK                                                                                                                                                      
 23  ;                                                                                                                                                                           

Control file created.

sql>RECOVER DATABASE;                                                                                                                                                            
Media recovery complete.
sql>ALTER DATABASE OPEN;                                                                                                                                                         
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced


alert 日志
Thu Mar 22 14:44:49 2012
CREATE CONTROLFILE REUSE DATABASE "NCME" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 4672
LOGFILE
  GROUP 1 '/oracle/oradata/ncme/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/ncme/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/ncme/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/ncme/system01.dbf',
  '/oracle/product/10.2.0/dbs/BJHR_INDEX',
  '/oracle/oradata/ncme/sysaux01.dbf',
  '/oracle/oradata/ncme/users01.dbf',
  '/oracle/oradata/ncme/BJHR_DEV.dbf',
  '/oracle/oradata/ncme/ncme01.dbf',
  '/oracle/oradata/ncme/undotbs01.dbf',
  '/oracle/oradata/ncme/keyan01.dbf',
  '/oracle/oradata/ncme/users02.dbf'
CHARACTER SET ZHS16GBK
Thu Mar 22 14:44:49 2012
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Setting recovery target incarnation to 1
Thu Mar 22 14:44:50 2012
Successful mount of redo thread 1, with mount id 1208628513
Thu Mar 22 14:44:50 2012
Completed: CREATE CONTROLFILE REUSE DATABASE "NCME" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 4672
LOGFILE
  GROUP 1 '/oracle/oradata/ncme/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/ncme/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/ncme/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/ncme/system01.dbf',
  '/oracle/product/10.2.0/dbs/BJHR_INDEX',
  '/oracle/oradata/ncme/sysaux01.dbf',
  '/oracle/oradata/ncme/users01.dbf',
  '/oracle/oradata/ncme/BJHR_DEV.dbf',
  '/oracle/oradata/ncme/ncme01.dbf',
  '/oracle/oradata/ncme/undotbs01.dbf',
  '/oracle/oradata/ncme/keyan01.dbf',
  '/oracle/oradata/ncme/users02.dbf'
CHARACTER SET ZHS16GBK
Thu Mar 22 14:45:03 2012
ALTER DATABASE RECOVER  DATABASE  
Thu Mar 22 14:45:03 2012
Media Recovery Start
 parallel recovery started with 2 processes
Thu Mar 22 14:45:03 2012
Recovery of Online Redo Log: Thread 1 Group 2 Seq 40722 Reading mem 0
  Mem# 0 errs 0: /oracle/oradata/ncme/redo02.log
Thu Mar 22 14:45:03 2012
Media Recovery Complete (ncme)
Completed: ALTER DATABASE RECOVER  DATABASE  
Thu Mar 22 14:45:29 2012
ALTER DATABASE OPEN
Thu Mar 22 14:45:29 2012
Beginning crash recovery of 1 threads
 parallel recovery started with 2 processes
Thu Mar 22 14:45:29 2012
Started redo scan
Thu Mar 22 14:45:29 2012
Completed redo scan
 45 redo blocks read, 0 data blocks need recovery
Thu Mar 22 14:45:29 2012
Started redo application at
 Thread 1: logseq 40722, block 2, scn 12965692819
Thu Mar 22 14:45:29 2012
Recovery of Online Redo Log: Thread 1 Group 2 Seq 40722 Reading mem 0
  Mem# 0 errs 0: /oracle/oradata/ncme/redo02.log
Thu Mar 22 14:45:29 2012
Completed redo application
Thu Mar 22 14:45:29 2012
Completed crash recovery at
 Thread 1: logseq 40722, block 47, scn 12965712876
 0 data blocks read, 0 data blocks written, 45 redo blocks read
Thu Mar 22 14:45:29 2012
Thread 1 advanced to log sequence 40723
Thread 1 opened at log sequence 40723
  Current log# 3 seq# 40723 mem# 0: /oracle/oradata/ncme/redo03.log
Successful open of redo thread 1
Thu Mar 22 14:45:30 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Mar 22 14:45:30 2012
SMON: enabling cache recovery
Thu Mar 22 14:45:30 2012
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
File #2 in the controlfile not found in data dictionary.
Removing file from controlfile.
data file 2: '/oracle/product/10.2.0/dbs/BJHR_INDEX'
Tablespace 'BJHR_INDEX' #30 found in controlfile,
but not in the data dictionary. Deleting from controlfile.
File #5 in the controlfile not found in data dictionary.
Removing file from controlfile.
data file 5: '/oracle/oradata/ncme/BJHR_DEV.dbf'
Tablespace 'BJHR_DEV' #25 found in controlfile,
but not in the data dictionary. Deleting from controlfile.
File #6 in the controlfile not found in data dictionary.
Removing file from controlfile.
data file 6: '/oracle/oradata/ncme/ncme01.dbf'
Tablespace 'NCME' #7 found in controlfile,
but not in the data dictionary. Deleting from controlfile.
File #9 in the controlfile not found in data dictionary.
Removing file from controlfile.
data file 9: '/oracle/oradata/ncme/keyan01.dbf'
Tablespace 'KEYAN' #18 found in controlfile,
but not in the data dictionary. Deleting from controlfile.
Thu Mar 22 14:45:30 2012
Errors in file /oracle/admin/ncme/udump/ncme_ora_28605.trc:
ORA-00600: internal error code, arguments: [25016], [2], [30], [], [], [], [], []
Thu Mar 22 14:45:30 2012
Errors in file /oracle/admin/ncme/udump/ncme_ora_28605.trc:
ORA-00600: internal error code, arguments: [25016], [2], [30], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 28605
ORA-1092 signalled during: ALTER DATABASE OPEN...
Thu Mar 22 14:52:54 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =220
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.

trace文件中
*** 2012-03-22 14:54:22.753
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [25016], [2], [30], [], [], [], [], []
Current SQL statement for this session:
alter database open
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+27          call     ksedst1()            0 ? 1 ?
ksedmp()+557         call     ksedst()             0 ? 322E3031 ? 642F302E ?
                                                   B5D2B008 ? BFFF6438 ?
                                                   51333110 ?
ksfdmp()+19          call     ksedmp()             3 ? BFFF629C ? AC05B10 ?
                                                   CBC2A40 ? 3 ? CB740C0 ?
kgeriv()+188         call     00000000             CBC2A40 ? 3 ?
kgesiv()+118         call     kgeriv()             CBC2A40 ? B7110020 ? 61B8 ?
                                                   2 ? BFFF6308 ?
ksesic2()+44         call     kgesiv()             CBC2A40 ? B7110020 ? 61B8 ?
                                                   2 ? BFFF6308 ? 61B8 ? 2 ?
                                                   BFFF6308 ?
krtadf()+74          call     ksesic2()            61B8 ? 0 ? 2 ? 0 ? 0 ? 1E ?
                                                   0 ?
kcfckdf()+1264       call     krtadf()             BFFF6C48 ? 1E ? 2 ?
                                                   BFFF6994 ?
dbsckd()+1038        call     kcfckdf()            2 ? 2 ? 1E ? 1 ? BFFF6E00 ?
                                                   BFFF6DAC ? 0 ? 0 ? 2000 ?
adbdrv()+5727        call     dbsckd()             0 ? 0 ? 0 ? 0 ? 0 ? 0 ?
opiexe()+18301       call     adbdrv()             101C5 ? 0 ? 8478EF72 ?
                                                   49F68 ? 43B2EAD8 ? 0 ?
opiosq0()+3918       call     opiexe()             4 ? 0 ? BFFFC888 ?



--#######################MOS##################---
ORA-600[25016] At Startup [ID 1017018.102]
  	修改时间 31-AUG-2010     类型 PROBLEM     状态 PUBLISHED 	 


Problem Description
-------------------

After a restore of the database, you may see the following error:

    svrmgrl> startup
    svrmgrl> ORA-00600: internal error code, arguments: [25016]


Solution Description
--------------------

You need to create a new control file.

    svrmgrl> alter database backup controlfile to trace;

1.  This will be dumped in the user dump directory.

2.  Modify it by removing the unnecessary details above the
    "alter database no mount".

3.  Run this script to create a new control file.

    Example: 

    svrmgrl>@cntrl.sql

4.  Make sure that this controlfile has the correct datafiles before
    proceeding.
   
Either add the next steps to the control file or issue manually:

    svrmgrl> recover database using backup controlfile;
    svrmgrl> alter database open resetlogs;
 

Explanation
-----------

This type of ORA-600 indicates that it is possible that the control file
created is referencing different datafiles than that which is in the
database.  A new control file is needed in order to identify the
datafiles necessary to open the database.


「喜欢文章,快来给作者赞赏墨值吧」

评论

0
0
Oracle
订阅
欢迎订阅Oracle频道,订阅之后可以获取最新资讯和更新通知。
墨值排行
今日本周综合
近期活动
全部
相关课程
全部