ORA-600 kcbzpbuf_1故障恢复

Posted on 2023 年 01 月 11 日 by 惜分飞

数据库启动报错ORA-03113

SQL> startup;
ORACLE instance started.

Total System Global Area 5.1310E+10 bytes
Fixed Size                  2265224 bytes
Variable Size            1.8119E+10 bytes
Database Buffers         3.3152E+10 bytes
Redo Buffers               36069376 bytes
Database mounted.

ORA-03113: end-of-file on communication channel
Process ID: 117892
Session ID: 568 Serial number: 3

分析alert日志发现ORA-600 kcbzpbuf_1报错

Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 4 Seq 4744 Reading mem 0
  Mem# 0: /home/oradata/redo04.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4745 Reading mem 0
  Mem# 0: /home/oradata/redo01.log
Wed Jan 11 14:44:35 2023
Hex dump of (file 87, block 3143379) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_116740.trc
Corrupt block relative dba: 0x15eff6d3 (file 87, block 3143379)
Bad header found during preparing block for write
Data in bad block:
 type: 0 format: 2 rdba: 0x00000000
 last change scn: 0x0b7e.593518d5 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x18d50001
 check value in block header: 0x342b
 computed block checksum: 0x0
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_116740.trc  (incident=553128):
ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_553128/orcl_dbw0_116740_i553128.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_116740.trc:
ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], [], [], [], [], []
DBW0 (ospid: 116740): terminating the instance due to error 471
Wed Jan 11 14:44:36 2023
System state dump requested by (instance=1, osid=116740 (DBW0)), summary=[abnormal instance termination].
Instance terminated by DBW0, pid = 116740

错误比较明显,在应用日志的时候，redo和数据文件的block不匹配，从而出现Corrupt block relative dba: 0x15eff6d3 (file 87, block 3143379)问题,通过bbed对该block进行修复，数据库直接recover成功

RMAN> recover database;

Starting recover at 2023-01-11 14:53:44
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 2023-01-11 14:53:45

数据库open成功

SQL> alter database open;

Database altered.

数据库报ORACLE Instance orcl (pid = 14)类似错误

Thread 1 opened at log sequence 4745
  Current log# 1 seq# 4745 mem# 0: /home/oradata/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Jan 11 14:54:10 2023
SMON: enabling cache recovery
[108954] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:2313624 end:2313634 diff:10 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_110633.trc  (incident=577160):
ORA-01578: ORACLE data block corrupted (file # 87, block # 3143379)
ORA-01110: data file 87: '/home/oradata/xifenfei04.dbf'
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Jan 11 14:54:10 2023
QMNC started with pid=80, OS id=114315
Completed: alter database open
Wed Jan 11 14:54:10 2023
db_recovery_file_dest_size of 4182 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
ORACLE Instance orcl (pid = 14) - Error 1578 encountered while recovering transaction (10, 0) on object 156475.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_110633.trc:
ORA-01578: ORACLE data block corrupted (file # 87, block # 3143379)
ORA-01110: data file 87: '/home/oradata/xifenfei04.dbf'

对其异常对象进行分析,确认是回收站对象,清理回收站

数据库后续运行正常【alert日志没有其他报错】，该恢复完成，业务数据可以直接使用，数据0丢失

Oracle 19c 断电异常恢复

Posted on 2023 年 01 月 05 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 19c 断电异常恢复

19.3数据库由于异常断电,导致数据库无法启动，报ORA-600 ktbair2: illegal inheritance,ORA-600 6101等错误

2023-01-02T22:01:37.310225+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_pr0l_3788.trc:
ORA-10562: Error occurred while applying redo to data block (file# 10, block# 399386)
ORA-10564: tablespace DATA
ORA-01110: data file 10: 'D:\ORADATA\DATA04.ORA'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 81882
ORA-00600: internal error code, arguments: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
2023-01-02T22:01:37.544630+08:00
Slave exiting with ORA-10562 exception
2023-01-02T22:01:37.560258+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_pr03_4364.trc:
ORA-10562: Error occurred while applying redo to data block (file# 2, block# 716430)
ORA-10564: tablespace DATA
ORA-01110: data file 2: 'D:\ORADATA\DATA01.ORA'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 81743
ORA-00600: internal error code, arguments: [6101], [0], [16], [0], [0], [0], [], [], [], [], [], []
2023-01-02T22:01:38.294726+08:00
Slave exiting with ORA-10562 exception
2023-01-02T22:01:38.310354+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_pr0e_5980.trc:
ORA-10562: Error occurred while applying redo to data block (file# 2, block# 714911)
ORA-10564: tablespace DATA
ORA-01110: data file 2: 'D:\ORADATA\DATA01.ORA'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 74014
ORA-00600: internal error code, arguments: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
2023-01-02T22:01:48.921092+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_pr00_4760.trc:
ORA-00283: recovery session canceled due to errors
ORA-00448: normal completion of background process
2023-01-02T22:01:49.171125+08:00
ORA-756 signalled during: ALTER DATABASE RECOVER  database  ...

报错比较明显由于redo和datafile不匹配导致recover 不成功,尝试强制拉库

SQL> alter database open resetlogs ;
alter database open resetlogs 
*
第 1 行出现错误:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [],
[], [], [], [], [], [], []
进程 ID: 6068
会话 ID: 3631 序列号: 54960

Undo initialization recovery: err:600 start: 20760593 end: 20762484 diff: 1891 ms (1.9 seconds)
2023-01-02T22:09:05.539709+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_6068.trc:
ORA-00600: 内部错误代码, 参数: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
2023-01-02T22:09:05.555336+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_6068.trc:
ORA-00600: 内部错误代码, 参数: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_6068.trc  (incident=293955):
ORA-00603: ORACLE 服务器会话因致命错误而终止
ORA-01092: ORACLE 实例终止。强制断开连接
ORA-00600: 内部错误代码, 参数: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_293955\orcl_ora_6068_i293955.trc
2023-01-02T22:09:06.805497+08:00
opiodr aborting process unknown ospid (6068) as a result of ORA-603
2023-01-02T22:09:06.961768+08:00
ORA-603 : opitsk aborting process

参考类似处理open数据库成功:
ORA-600 kcbzib_kcrsds_1报错
 12C数据库报ORA-600 kcbzib_kcrsds_1故障处理
 redo异常强制拉库报ORA-600 kcbzib_kcrsds_1修复
 ORA-00603 ORA-01092 ORA-600 kcbzib_kcrsds_1

Oracle Recovery Tools快速恢复ORA-19909

Posted on 2023 年 01 月 02 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle Recovery Tools快速恢复ORA-19909

数据库服务器异常断电，数据库启动报ORA-01113 ORA-01110错误,无法正常open

Sun Jan 01 17:02:55 2023
alter database mount exclusive
Successful mount of redo thread 1, with mount id 1652739647
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_4396.trc:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORACLE11G\ORADATA\ORCL\SYSTEM01.DBF'
ORA-1113 signalled during: alter database open...

offline datafile 4，并open数据库

Sun Jan 01 20:36:22 2023
alter database datafile 4 offline drop
Completed: alter database datafile 4 offline drop

Sun Jan 01 20:37:40 2023
ALTER DATABASE OPEN
Thread 1 opened at log sequence 13068
  Current log# 3 seq# 13068 mem# 0: E:\ORACLE11G\ORADATA\ORCL\REDO03.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
WARNING: AQ_TM_PROCESSES is set to 0. System operation                     might be adversely affected.
Completed: ALTER DATABASE OPEN

尝试recover datafile 4和online datafile 4失败

Sun Jan 01 22:33:19 2023
ALTER DATABASE RECOVER datafile 4
Media Recovery Start
Serial Media Recovery started
WARNING! Recovering data file 4 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
ORA-279 signalled during: ALTER DATABASE RECOVER datafile 4 ...
Sun Jan 01 22:34:02 2023
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
Errors with log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CANCEL
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
ALTER DATABASE RECOVER datafile 4
Media Recovery Start
Serial Media Recovery started
WARNING! Recovering data file 4 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
ORA-279 signalled during: ALTER DATABASE RECOVER datafile 4 ...
Sun Jan 01 22:34:15 2023
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
Errors with log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
Errors with log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CANCEL
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
Sun Jan 01 22:36:34 2023
alter database datafile 4 online
ORA-1113 signalled during: alter database datafile 4 online

在datafile 4 offline的情况下，resetlogs库

Sun Jan 01 23:50:01 2023
ALTER DATABASE RECOVER  database until cancel  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 56 slaves
Sun Jan 01 23:50:02 2023
Warning: Datafile 4 (E:\ORACLE11G\ORADATA\ORCL\USERS01.DBF) 
    is offline during full database recovery and will not be recovered
Media Recovery Not Required
Completed: ALTER DATABASE RECOVER  database until cancel  
Sun Jan 01 23:50:15 2023
alter database open
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_2480.trc:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORA-1589 signalled during: alter database open...
Sun Jan 01 23:50:34 2023
alter database open RESETLOGS
RESETLOGS after complete recovery through change 158902238
Resetting resetlogs activation ID 1504008459 (0x59a5590b)
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_2480.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO01.LOG'
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_2480.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO02.LOG'
Sun Jan 01 23:50:36 2023
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_m000_8340.trc:
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO01.LOG'
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_2480.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO03.LOG'
Sun Jan 01 23:50:38 2023
Setting recovery target incarnation to 3
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_m000_8340.trc:
ORA-00314: log 2 of thread 1, expected sequence# 13070 doesn't match 0
ORA-00312: online log 2 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO02.LOG'
Sun Jan 01 23:50:39 2023
Assigning activation ID 1652808490 (0x6283db2a)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: E:\ORACLE11G\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Jan 01 23:50:39 2023
SMON: enabling cache recovery
Checker run found 5 new persistent data failures
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
File #4 is offline, but is part of an online tablespace.
data file 4: 'E:\ORACLE11G\ORADATA\ORCL\USERS01.DBF'
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
WARNING: AQ_TM_PROCESSES is set to 0. System operation                     might be adversely affected.
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Sun Jan 01 23:50:47 2023
Completed: alter database open RESETLOGS

后续尝试恢复datafile 4报ORA-19909

Mon Jan 02 00:02:10 2023
alter database datafile 4 online
Completed: alter database datafile 4 online
Mon Jan 02 00:03:31 2023
ALTER DATABASE RECOVER  database using backup controlfile  
Media Recovery Start
 started logmerger process
Mon Jan 02 00:03:31 2023
Datafile 4 is on orphaned branch
          File status = 4
        Abs fuzzy SCN = 0
 Hot backup fuzzy SCN = 0
Media Recovery failed with error 19909
Slave exiting with ORA-283 exception
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_pr00_8868.trc:
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 4 belongs to an orphan incarnation
ORA-01110: data file 4: 'E:\ORACLE11G\ORADATA\ORCL\USERS01.DBF'
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database using backup controlfile  ...

通过Oracle Database Recovery Check检查发现,确实datafile 4的状态为:WRONG RESETLOGS

对于此类情况,参考：Oracle Recovery Tools 解决ORA-01190 ORA-01248等故障快速解决

ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Mon Jan 02 16:14:15 2023
Media Recovery failed with error 264
Slave exiting with ORA-283 exception
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_pr00_10712.trc:
ORA-00283: 恢复会话因错误而取消
ORA-00264: 不要求恢复
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...
Mon Jan 02 16:14:29 2023
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Mon Jan 02 16:14:29 2023
Media Recovery failed with error 264
Slave exiting with ORA-283 exception
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_pr00_20032.trc:
ORA-00283: 恢复会话因错误而取消
ORA-00264: 不要求恢复
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...
alter database open
Mon Jan 02 16:14:37 2023
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: H:\BAIDUNETDISK\ORCL\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Jan 02 16:14:37 2023
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
*********************************************************************
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
*********************************************************************
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Jan 02 16:14:37 2023
QMNC started with pid=22, OS id=14152 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open

后续增加tempfile,导出数据完成本次恢复

IMP-00009: abnormal end of export file

Posted on 2023 年 01 月 01 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：IMP-00009: abnormal end of export file

exp导出数据正常,没有任何报错

imp导入报IMP-00009和IMP-00020,而且报错表之后数据均未导入,imp程序结束

IMP-00009: abnormal end of export file
IMP-00020: long column too large for column buffer size (2)
Import terminated successfully with warnings.

使用show=y进行dmp文件验证,也报IMP-00009错误,证明是dmp本身异常

通过dul对dmp文件分析

找出来损坏的位置,对其进行人工修复,然后imp顺利导入

故障原因是由于direct=true和分区表（该表132列,而且是空表）一起触发的某个bug

truncate sys用户表导致数据库异常恢复

Posted on 2023 年 01 月 01 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：truncate sys用户表导致数据库异常恢复

有客户本想truncate业务用下面所有的表,结果连接成SYS用户,并且拼接truncate 批量语句,导致sys用户下面大量表被truncate

sqlplus无法登录数据库

通过分析obj$发现truncate成功了大量sys用户下面表

基于这种情况,只能把业务数据恢复到一个新库中,然后应用厂商重新配置调试应用.提醒各位:truncate/drop等风险较高操作,一定要核实用户,避免误操作,如果真的遇到此类误操作,第一时间保护现场,原则上只要truncate表之后以前的block没有被覆盖均可恢复

13429648788

qq咨询

专业Oracle数据库恢复技术支持

专业Oracle数据库恢复，或许是您恢复数据的最后机会@phone:17813235971

Monthly Archives: 一月 2023

ORA-600 kcbzpbuf_1故障恢复

Oracle 19c 断电异常恢复

Oracle Recovery Tools快速恢复ORA-19909

IMP-00009: abnormal end of export file

truncate sys用户表导致数据库异常恢复

S	M	T	W	T	F	S
« Dec				Feb »
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31