IT博客汇 | 重建ctl遭遇ORA-01163 ORA-01110和ORA-600 kccccfl

重建ctl遭遇ORA-01163 ORA-01110和ORA-600 kccccfl_1错误

惜分飞发表于 2023-11-09 13:48:10

标题：重建ctl遭遇ORA-01163 ORA-01110和ORA-600 kccccfl_1错误

有客户联系我,数据库强制关闭之后无法正常启动,通过他们一系列尝试,数据库报open 报ORA-01555错误

通过分析确认是由于客户屏蔽一致性强制拉库失败导致

Wed Nov 01 19:49:03 2023
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 1199697777
Archived Log entry 68106 added for thread 1 sequence 3829 ID 0x761a769a dest 1:
Archived Log entry 68107 added for thread 1 sequence 3830 ID 0x761a769a dest 1:
Archived Log entry 68108 added for thread 1 sequence 3828 ID 0x761a769a dest 1:
Clearing online redo logfile 1 /u01/app/oracle/oradata/ORACLE/redo01.log
Clearing online log 1 of thread 1 sequence number 3829
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/ORACLE/redo02.log
Clearing online log 2 of thread 1 sequence number 3830
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u01/app/oracle/oradata/ORACLE/redo03.log
Clearing online log 3 of thread 1 sequence number 3828
Clearing online redo logfile 3 complete
Resetting resetlogs activation ID 1981445786 (0x761a769a)
Online log /u01/app/oracle/oradata/ORACLE/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/ORACLE/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/ORACLE/redo03.log: Thread 1 Group 3 was previously cleared
Wed Nov 01 19:49:09 2023
Setting recovery target incarnation to 3
Wed Nov 01 19:49:09 2023
Assigning activation ID 2024817472 (0x78b04340)
LGWR: STARTING ARCH PROCESSES
Wed Nov 01 19:49:09 2023
ARC0 started with pid=21, OS id=2078 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Wed Nov 01 19:49:10 2023
ARC1 started with pid=22, OS id=2080 
Thread 1 advanced to log sequence 2 (thread open)
Wed Nov 01 19:49:10 2023
ARC2 started with pid=23, OS id=2082 
Wed Nov 01 19:49:10 2023
ARC3 started with pid=24, OS id=2084 
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/ORACLE/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Nov 01 19:49:10 2023
SMON: enabling cache recovery
ARC2: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
Wed Nov 01 19:49:10 2023
NSA2 started with pid=25, OS id=2086 
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000.4781ef78):
select ctime, mtime, stime from obj$ where obj# = :1
Errors in file /u01/app/oracle/diag/rdbms/oracle_pd/ORACLE/trace/ORACLE_ora_2076.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11_467363169$" too small
Errors in file /u01/app/oracle/diag/rdbms/oracle_pd/ORACLE/trace/ORACLE_ora_2076.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11_467363169$" too small
Error 704 happened during db open, shutting down database
USER (ospid: 2076): terminating the instance due to error 704
Instance terminated by USER, pid = 2076
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (2076) as a result of ORA-1092

这种错误比较常见恢复简单,以前有过类似的文章:
在数据库open过程中常遇到ORA-01555汇总
 数据库open过程遭遇ORA-1555对应sql语句补充
 重建control遗漏数据文件,reseltogs报ORA-1555错误处理
 Oracle Recovery Tools恢复—ORA-00704 ORA-01555故障
这个错误恢复本身不难,只是在数据库的恢复过程中需要重建文件,在这次的重建文件中由于ctl语句错误【语句是通过sql查询拼接而成】,导致重建文件报了两个错误,供参考:
ORA-01163 ORA-01110错误

SQL> CREATE CONTROLFILE REUSE DATABASE "ORACLE" NORESETLOGS  NOARCHIVELOG
        MAXLOGFILES 50
       MAXLOGMEMBERS 5
       MAXDATAFILES 1000
       MAXINSTANCES 8
       MAXLOGHISTORY 2920
LOGFILE
       group 1   '/u01/app/oracle/oradata/ORACLE/redo01.log' size 200M,
  2         group 3   '/u01/app/oracle/oradata/ORACLE/redo03.log' size 200M,
       group 2   '/u01/app/oracle/oradata/ORACLE/redo02.log' size 200M,
DATAFILE
        '/u01/app/oracle/oradata/ORACLE/system01.dbf',
        …………
        '/u01/app/oracle/oradata/ORACLE/xifenfei06.dbf',
CHARACTER SET  ZHS16GBK ;
 23   24   25   26   27  CREATE CONTROLFILE REUSE DATABASE "ORACLE" NORESETLOGS	NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01163: SIZE clause indicates 12800 (blocks), but should match header
1564672
ORA-01110: data file 15: '/u01/app/oracle/oradata/ORACLE/xifenfei06.dbf'

出现该错误是由于我写路径的时候在最后一个数据文件路径后面多了一个【,】导致

ORA-600 kccccfl_1错误

SQL> CREATE CONTROLFILE REUSE DATABASE "ORACLE" NORESETLOGS  NOARCHIVELOG
        MAXLOGFILES 50
       MAXLOGMEMBERS 5
       MAXDATAFILES 1000
       MAXINSTANCES 8
       MAXLOGHISTORY 2920
LOGFILE
       group 1   '/u01/app/oracle/oradata/ORACLE/redo01.log'  size 200M,
       group 3   '/u01/app/oracle/oradata/ORACLE/redo03.log' size 200M,
       group 2   '/u01/app/oracle/oradata/ORACLE/redo02.log' size 200M,
DATAFILE
        '/u01/app/oracle/oradata/ORACLE/system01.dbf',
        ………… 
        '/u01/app/oracle/oradata/ORACLE/xifenfei06.dbf'
CHARACTER SET  ZHS16GBK ;
 23   24   25   26   27  CREATE CONTROLFILE REUSE DATABASE "ORACLE" NORESETLOGS	NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00600: internal error code, arguments: [kccccfl_1], [], [], [], [], [], [],
[], [], [], [], []

出现该错误是由于在最后redo文件路径后面多了【,】导致