ORA-600 [kccpb_sanity_check_2],重建控制文件，重建undo

ORA-00600: internal error code, arguments: [2131], [9], [8], [], [], [], [],

Error: kccpb_sanity_check_2
Control file sequence number mismatch!

重建控制文件，trace生成，手工修改。
重建undo

报错描述

有台12c单机数据库启动不了，未开归档，未做备份。
启动报错如下

SQL> startup
ORACLE instance started.

Total System Global Area 9965666304 bytes
Fixed Size		    2934600 bytes
Variable Size		 1677723832 bytes
Database Buffers	 8254390272 bytes
Redo Buffers		   30617600 bytes
ORA-00600: internal error code, arguments: [2131], [9], [8], [], [], [], [],
[], [], [], [], []



Errors in file /u01/app/oracle/diag/rdbms/orcl/sjzk/trace/sjzk_ora_19735.trc  (incident=37514) (PDBNAME=CDB$ROOT):
ORA-00600: internal error code, arguments: [2131], [9], [8], [], [], [], [], [], [], [], [], []

[oracle@XJPT-DBSW pdborcl]$ more /u01/app/oracle/diag/rdbms/orcl/sjzk/trace/sjzk_ora_19735.trc

*** CONTAINER ID:(1) 2019-09-26 17:24:26.784
 
Error: kccpb_sanity_check_2
Control file sequence number mismatch!

ORA-00600: internal error code, arguments: [2131], [9], [8], [], [], [], [],
Error: kccpb_sanity_check_2
Control file sequence number mismatch!

控制文件的序列号匹配不了，搜索了下上面的报错，解决办法：

Cause
ORA-600 [kccpb_sanity_check_2] indicates that the seq# of the last read block is higher than the seq# of the control file header block.
 
This is indication of the lost write of the header block during commit of the previous cf transaction.
Solution
1) restore a backup of a controlfile and recover
OR
2) recreate the controlfile
OR
3) restore the database from last good backup and recover
NOTE: If you do not have any special backup of control file to 
restore and you are using Multiple Control File copies in your pfile/init.ora/spfile you can attempt to mount the database using each control file one by one. 
If you are able to mount the database with any of these control file copies you can then issue ‘alter database backup controlfile to trace’ to recreate controlfile.

ORA-600 [kccpb_sanity_check_2] 意思就是控制文件记录的seq#和数据文件的不一致了
解决办法是：
1，恢复控制文件。
2，控制文件有多路径的话，可以挨个试试，不一定所有的控制文件都错了。
3，最后，没办法，就只有重建了。

开始解决

1,恢复控制文件

由于未做备份，恢复也就无从谈起了

2,试试其他控制文件

SQL> show parameter control_file

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time	     integer	 7
control_files			     string					 /u01/app/oracle/oradata/sjzk/c
															 ontrol01.ctl, /u01/app/oracle/
															 fast_recovery_area/sjzk/contro
															 l02.ctl

控制文件有两份，用pfile启动挨个试试：

create pfile='/home/oracle/pfile.ora' from spfile;
vi /home/oracle/pfile.ora
...
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
....

挨个试了2个控制文件，都还是报错。开始重建

3,重建控制文件

由于数据库只能到nomount状态，不能用trace重建。
从其他地方用trace获取脚本后改一改来重建。

trace重建控制文件

alter database backup controlfile to trace;

查看alter.log，寻找trace文件路径。

Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_3790.trc
Completed: alter database backup controlfile to trace

ORA11GR2_ora_3790.trc就是我们需要的重建脚本。

获取脚本所需的信息

脚本基本是通用的，完全可以在网上找个相应版本的控制脚本，再根据自己的数据情况修改修改参数。

数据库名

SQL> show parameter db_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_name 			     string	 orcl

LOGFILE & DATAFILE

LOGFILE & DATAFILE的路径名，可以通过很多方式获取到。
这里用strings从损坏的控制文件中获取

strings /u01/app/oracle/oradata/orcl/control01.ctl |grep dbf

最后脚本

最后再修改一下字符集、归档模式、是否RESETLOGS开启，等等参数。

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 8
    MAXLOGHISTORY 4672
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/orcl/system01.dbf',
  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
  '/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf',
  '/u01/app/oracle/oradata/orcl/users01.dbf',
  '/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf',
  '/u01/app/oracle/oradata/orcl/pdborcl/system01.dbf',
  '/u01/app/oracle/oradata/orcl/pdborcl/sysaux01.dbf',
  '/u01/app/oracle/oradata/orcl/pdborcl/pdborcl_users01.dbf'
CHARACTER SET ZHS16GBK
;

注意，这里不包含临时数据文件。在脚本的最后，还有一段是重建临时表空间的：

RECOVER DATABASE
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Open all the PDBs.
ALTER PLUGGABLE DATABASE ALL OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
     SIZE 206569472  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = PDB$SEED;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/pdbseed/pdbseed_temp012017-03-23_01-57-51-PM.dbf'
     SIZE 104857600  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = PDBORCL;
ALTER TABLESPACE TEMP ADD TEMPFILE '/storage/ora_data/temp01.DBF'
     SIZE 2048M REUSE AUTOEXTEND ON NEXT 2048M MAXSIZE 32767M;

运行脚本创建控制文件。

后续的报错

重建完控制文件后，需要做一次recover database。
这里开始报错：

SQL> RECOVER DATABASE
ORA-00756: recovery detected a lost write of a data block
ORA-10567: Redo is inconsistent with data block (file# 4, block# 102912, file
offset is 843055104 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/undotbs1.dbf'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'

UNDO数据文件损坏，先将其offline掉。

alter database datafile 
 '/u01/app/oracle/oradata/orcl/undotbs1.dbf' offline for drop;

接着又报错

SQL> RECOVER DATABASE
ORA-00756: recovery detected a lost write of a data block
ORA-10567: Redo is inconsistent with data block (file# 3, block# 102912, file
offset is 843055104 bytes)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'

其他数据文件也有损坏，不管三七二十一，先把数据库启起来再说。

recover database allow 100 corruption;
alter dabase open;

然后开始重建undo表空间

重建undo

create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/orcl/UNDOTBS02.DBF' size 1g reuse autoextend on next 100m maxsize unlimited;
alter system set undo_tablespace='UNDOTBS2' scope=spfile;
alter system set undo_management=manul scope=spfile; 

drop tablespace UNDOTBS1;
--报错，提示在使用。
--查看回滚段的内如
select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;

 --修改pfile参数
*._allow_resetlogs_corruption=true  
*._corrupted_rollback_segments=
(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)  

shutdown immediate;
startup pfile=xxxxxxx;

drop tablespace UNDOTBS1;

至此，数据库能够启动。