标签归档：kcbzib_kcrsds_1

ORA-00756 ORA-10567故障处理

发表于 2024 年 7 月 16 日 由 惜分飞

数据库异常断电之后，recover 报ORA-00756 ORA-10567等错

SQL> recover database;
ORA-00756: 恢复操作检测到数据块写入丢失
ORA-10567: Redo is inconsistent with data block (file# 1,block# 113855,file offset is 932700160 bytes)
ORA-10564: tablespace SYSTEM
ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 67

alert日志报大量block逻辑错误

2024-07-16T13:16:31.050599+08:00
Slave exiting with ORA-10562 exception
2024-07-16T13:16:31.050599+08:00
Errors in file C:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr01_43460.trc:
ORA-10562: Error occurred while applying redo to data block (file# 3, block# 107952)
ORA-10564: tablespace SYSAUX
ORA-01110: 数据文件 3: 'H:\BAIDUNETDISK\XIFENFEI\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 8689
ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], , [], [], [], []
2024-07-16T13:16:31.088497+08:00
Slave exiting with ORA-10562 exception
2024-07-16T13:16:31.088497+08:00
Errors in file C:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr0e_10596.trc:
ORA-10562: Error occurred while applying redo to data block (file# 1, block# 755)
ORA-10564: tablespace SYSTEM
ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 64
ORA-00600: 内部错误代码, 参数: [kdolkr-2], [2], [155], [26], , []
2024-07-16T13:16:31.106449+08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2024-07-16T13:16:31.130385+08:00
Slave exiting with ORA-10562 exception
2024-07-16T13:16:31.130385+08:00
Errors in file C:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr0i_40632.trc:
ORA-10562: Error occurred while applying redo to data block (file# 1, block# 110095)
ORA-10564: tablespace SYSTEM
ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 40
ORA-00600: 内部错误代码, 参数: [kdxdBlkCheckError], [1], [4304399], [6401], , []
2024-07-16T13:16:31.157313+08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2024-07-16T13:16:31.181249+08:00
Slave exiting with ORA-10562 exception
2024-07-16T13:16:31.182247+08:00
Errors in file C:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr09_15592.trc:
ORA-10562: Error occurred while applying redo to data block (file# 1, block# 5490)
ORA-10564: tablespace SYSTEM
ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 822
ORA-00600: 内部错误代码, 参数: [kdxdBlkCheckError], [1], [4199794], [6401], , []
2024-07-16T13:16:31.242087+08:00
Slave exiting with ORA-10562 exception
2024-07-16T13:16:31.242087+08:00
Errors in file C:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr05_28908.trc:
ORA-10562: Error occurred while applying redo to data block (file# 3, block# 3935)
ORA-10564: tablespace SYSAUX
ORA-01110: 数据文件 3: 'H:\BAIDUNETDISK\XIFENFEI\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 8694
ORA-00600: 内部错误代码, 参数: [6102], [27], [2], , [], []
2024-07-16T13:16:31.265025+08:00
Slave exiting with ORA-10562 exception
2024-07-16T13:16:31.266023+08:00
Errors in file C:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr0d_24400.trc:
ORA-10562: Error occurred while applying redo to data block (file# 1, block# 51243)
ORA-10564: tablespace SYSTEM
ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 8
ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], , [], [], [], []
2024-07-16T13:16:31.272007+08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2024-07-16T13:16:31.293948+08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2024-07-16T13:16:31.294946+08:00
Slave exiting with ORA-10562 exception
2024-07-16T13:16:31.294946+08:00
Errors in file C:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr02_24168.trc:
ORA-10562: Error occurred while applying redo to data block (file# 1, block# 114402)
ORA-10564: tablespace SYSTEM
ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 64
ORA-00600: 内部错误代码, 参数: [kdbBlkCheckError], [1], [4308706], [6124], , []
2024-07-16T13:16:31.307911+08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2024-07-16T13:16:31.315890+08:00
Slave exiting with ORA-10562 exception
2024-07-16T13:16:31.316916+08:00
Errors in file C:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr0h_37312.trc:
ORA-10562: Error occurred while applying redo to data block (file# 1, block# 116359)
ORA-10564: tablespace SYSTEM
ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 64
ORA-00600: 内部错误代码, 参数: [kdbBlkCheckError], [1], [4310663], [6124], , []
2024-07-16T13:16:31.329881+08:00
Slave exiting with ORA-10562 exception
2024-07-16T13:16:31.329881+08:00
Errors in file C:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr0g_38356.trc:
ORA-10562: Error occurred while applying redo to data block (file# 1, block# 115210)
ORA-10564: tablespace SYSTEM
ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 64
ORA-00600: 内部错误代码, 参数: [kdbBlkCheckError], [1], [4309514], [6124], , []
2024-07-16T13:16:49.657116+08:00

Corrupt block relative dba: 0x01000c1d (file 4, block 3101)
Fractured block found during in-flux buffer recovery
Data in bad block:
 type: 2 format: 2 rdba: 0x01000c1d
 last change scn: 0x0000.0000.00ddfe50 seq: 0x1 flg: 0x04
 spare3: 0x0
 consistency value in tail: 0xcaae0205
 check value in block header: 0x2ebc
 computed block checksum: 0xee94

Reread (file 4, block 3101) found same corrupt data (no logical check)
2024-07-16T13:16:49.893484+08:00
Errors in file C:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr00_23116.trc:
ORA-00283: 恢复会话因错误而取消
ORA-00448: 后台进程正常结束

dbv检查system文件报有坏块

C:\Users\XFF>dbv file=H:\BaiduNetdisk\XIFENFEI\system01.dbf

DBVERIFY: Release 19.0.0.0.0 - Production on 星期二 7月 16 13:17:32 2024

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF
页 11290 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x00402c1a (file 1, block 11290)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x00402c1a
 last change scn: 0x0000.0000.00dec9ca seq: 0x1 flg: 0x06
 spare3: 0x0
 consistency value in tail: 0x56e00601
 check value in block header: 0xaf3c
 computed block checksum: 0xdc2d

页 50842 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x0040c69a (file 1, block 50842)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0040c69a
 last change scn: 0x0000.0000.00de200e seq: 0x1 flg: 0x06
 spare3: 0x0
 consistency value in tail: 0x799a0601
 check value in block header: 0x68ef
 computed block checksum: 0x5994

页 113852 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x0041bcbc (file 1, block 113852)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0041bcbc
 last change scn: 0x0000.0000.00df78b9 seq: 0x1 flg: 0x06
 spare3: 0x0
 consistency value in tail: 0x1f1c0601
 check value in block header: 0xf5fc
 computed block checksum: 0x46af



DBVERIFY - 验证完成

检查的页总数: 119040
处理的页总数 (数据): 82822
失败的页总数 (数据): 0
处理的页总数 (索引): 14268
失败的页总数 (索引): 0
处理的页总数 (其他): 4570
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 17377
标记为损坏的总页数: 3
流入的页总数: 3
加密的总页数        : 0
最高块 SCN            : 14645988 (0.14645988)

由于无法直接应用日志打开库,尝试屏蔽一致性,强制打开库,报ORA-600 kcbzib_kcrsds_1错误

SQL> startup mount pfile='d:/pfile.txt';
ORACLE 例程已经启动。

Total System Global Area 5167381760 bytes
Fixed Size                  9039104 bytes
Variable Size             989855744 bytes
Database Buffers         4160749568 bytes
Redo Buffers                7737344 bytes
数据库装载完毕。
SQL>
SQL>
SQL>
SQL> recover database until cancel;
ORA-00279: 更改 14599839 (在  生成) 对于线程 1 是必需的


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'


ORA-01112: 未启动介质恢复


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-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [],

进程 ID: 23392
会话 ID: 618 序列号: 30029

使用Patch_SCN工具修改scn(修改oracle scn小工具(patch scn)),然后打开库,报ORA-600 6711
patch_scn-kcbzib_kcrsds_1

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [6711], [4310861], [1], [4309052],
[0], 
进程 ID: 40100
会话 ID: 618 序列号: 10845

这个故障最近刚刚处理过一次,见:数据库启动报ORA-600 6711故障分析处理,open数据库之后，尝试导出数据,报各种错误
ORA-600 6711报错

C:\Users\XFF>exp "'/ as sysdba'" owner=XIFENFEI file=e:/XIFENFEI.dmp log=e:/XIFENFEI.log  

Export: Release 19.0.0.0.0 - Production on 星期二 7月 16 13:33:11 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


连接到: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的用户...
. 正在导出 pre-schema 过程对象和操作
EXP-00008: 遇到 ORACLE 错误 600
ORA-00600: 内部错误代码, 参数: [6711], [4310861], [1], [4309052], [0], 
EXP-00083: 调用 SYS.DBMS_AW_EXP.schema_info_exp 时出现前一问题
. 正在导出用户 XIFENFEI 的外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
EXP-00008: 遇到 ORACLE 错误 1578
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 11290)
ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'
EXP-00000: 导出终止失败

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on 星期二 7月 16 13:33:23 2024
Version 19.3.0.0.0

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


连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select object_name,object_type from dba_objects where object_id=64;

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
C_OBJ#_INTCOL#
CLUSTER

ORA-01578报错

C:\Users\XFF>exp "'/ as sysdba'" owner=XIFENFEI file=e:/XIFENFEI.dmp log=e:/XIFENFEI.log

Export: Release 19.0.0.0.0 - Production on 星期二 7月 16 13:34:07 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


连接到: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的用户...
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 XIFENFEI 的外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
EXP-00008: 遇到 ORACLE 错误 1578
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 11290)
ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'
EXP-00000: 导出终止失败

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on 星期二 7月 16 13:34:21 2024
Version 19.3.0.0.0

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


连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
输入 file_id 的值:  1
原值    3:  WHERE FILE_ID = &FILE_ID
新值    3:  WHERE FILE_ID = 1
输入 block_id 的值:  11290
原值    4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
新值    4:    AND 11290 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

OWNER
--------------------------------------------------------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE       TABLESPACE_NAME
------------------ ------------------------------
PARTITION_NAME
--------------------------------------------------------------------------------
SYS
I_OBJ2
INDEX              SYSTEM

SQL> create table t1 as select * from dba_objects;
create table t1 as select * from dba_objects
                                 *
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 50842)
ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'


SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
输入 file_id 的值:  1
原值    3:  WHERE FILE_ID = &FILE_ID
新值    3:  WHERE FILE_ID = 1
输入 block_id 的值:  50842
原值    4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
新值    4:    AND 50842 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

OWNER
--------------------------------------------------------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE       TABLESPACE_NAME
------------------ ------------------------------
PARTITION_NAME
--------------------------------------------------------------------------------
SYS
I_COL3
INDEX              SYSTEM

通过上述分析,确认还有I_OBJ2和I_COL3这两个核心index异常,参考:bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)异常恢复—ORA-00701错误解决 进行处理,数据库可以正常导出


连接到: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的用户...
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 XIFENFEI 的外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
. 正在导出用户 XIFENFEI 的对象类型定义
即将导出 XIFENFEI 的对象...
. 正在导出数据库链接
. 正在导出序号
. 正在导出簇定义
. 即将导出 XIFENFEI 的表通过常规路径...
. . 正在导出表                    标准诊断明细
.....
导出了                                                         50213 行
…………
. . 正在导出表                    诊断旁支分类
导出了                                                             1 行
. 正在导出同义词
. 正在导出视图
. 正在导出存储过程
. 正在导出运算符
. 正在导出引用完整性约束条件
. 正在导出触发器
. 正在导出索引类型
. 正在导出位图, 功能性索引和可扩展索引
. 正在导出后期表活动
. 正在导出实体化视图
. 正在导出快照日志
. 正在导出作业队列
. 正在导出刷新组和子组
. 正在导出维
. 正在导出 post-schema 过程对象和操作
. 正在导出统计信息
成功终止导出, 没有出现警告。
发表在 Oracle备份恢复 | 标签为 , , , , , , | 留下评论

Oracle 23ai rm redo*.log恢复

发表于 2024 年 5 月 4 日 由 惜分飞

在oracle 23ai的pdb中创建用户和表,并且插入数据(不提交),在另外一个会话中abort库,并从os层面rm删除掉redo文件,模拟数据库当前redo丢失,数据库恢复
创建用户和表并插入数据

[oracle@xifenfei ~]$ sqlplus sys/oracle@free as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 3 15:40:55 2024
Version 23.4.0.24.05

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


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> 
SQL> 
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ WRITE NO

SQL> alter session set container=FREEPDB1;

Session altered.

SQL> create user xff identified by oracle;

User created.

SQL> grant dba to xff ;

Grant succeeded.

SQL> conn xff/oracle@FREEPDB1
Connected.
SQL> create table t1 as select * from dba_objects;

Table created.


SQL> insert into t1 select *from t1;

75877 rows created.

SQL> /

151754 rows created.

另外一个会话中abort库

[oracle@xifenfei ~]$ sqlplus sys/oracle@free as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 3 15:43:30 2024
Version 23.4.0.24.05

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


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> shutdown abort;
ORACLE instance shut down.
SQL> 
SQL> 
SQL> 
SQL> exit
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

操作系统层面rm -rf 删除redo

[oracle@xifenfei ~]$ cd $ORACLE_BASE/oradata
[oracle@xifenfei oradata]$ ls
FREE
[oracle@xifenfei oradata]$ cd FREE/
[oracle@192 FREE]$ ls
control01.ctl  FREEPDB1  redo01.log  redo03.log    system01.dbf  undotbs2.dbf
control02.ctl  pdbseed   redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
[oracle@xifenfei FREE]$ ls -ltr
total 2441036
drwxr-x---. 2 oracle     1000         85 May  1 16:49 pdbseed
-rw-r-----. 1 oracle oinstall   20979712 May  1 16:51 temp01.dbf
drwxr-x---. 2 oracle     1000        104 May  1 16:55 FREEPDB1
-rw-r-----. 1 oracle oinstall  209715712 May  3 15:23 redo01.log
-rw-r-----. 1 oracle oinstall  209715712 May  3 15:23 redo02.log
-rw-r-----. 1 oracle oinstall    7348224 May  3 15:23 users01.dbf
-rw-r-----. 1 oracle oinstall 1080041472 May  3 15:43 system01.dbf
-rw-r-----. 1 oracle oinstall  692068352 May  3 15:43 sysaux01.dbf
-rw-rw----. 1 oracle oinstall   52436992 May  3 15:43 undotbs2.dbf
-rw-r-----. 1 oracle oinstall  209715712 May  3 15:43 redo03.log
-rw-r-----. 1 oracle oinstall   18759680 May  3 15:43 control01.ctl
-rw-r-----. 1 oracle oinstall   18759680 May  3 15:43 control02.ctl
[oracle@xifenfei FREE]$ rm -rf redo0*
[oracle@192 FREE]$ ls -l redo*
ls: cannot access 'redo*': No such file or directory
[oracle@xifenfei FREE]$

尝试启动数据库，报ora-00313，ora-00312，ora-27037等错误

[oracle@xifenfei ~]$ sqlplus sys/oracle@free as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 3 15:44:17 2024
Version 23.4.0.24.05

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603726344 bytes
Fixed Size                  5360648 bytes
Variable Size             671088640 bytes
Database Buffers          922746880 bytes
Redo Buffers                4530176 bytes
Database mounted.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/FREE/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

由于所有redo均被删除(包含当前redo),因此只能强制resetlogs方式打开库,尝试打开数据库

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 1603726344 bytes
Fixed Size                  5360648 bytes
Variable Size             671088640 bytes
Database Buffers          922746880 bytes
Redo Buffers                4530176 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 4244588 generated at 05/03/2024 15:23:22 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/product/23ai/dbhomeFree/dbs/arch1_6_1167842962.dbf
ORA-00280: change 4244588 for thread 1 is in sequence #6


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/FREE/system01.dbf'


ORA-01112: media recovery not started

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by irrecoverable error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [],
[], [], [], [], [], [], []
Process ID: 5596
Session ID: 29 Serial number: 63204

数据库遇到常见的ORA-600 kcbzib_kcrsds_1错误,这类错误类似oracle在12c之前版本中的ORA-600 2662错误
ORA-600 kcbzib_kcrsds_1报错
存储故障,强制拉库报ORA-600 kcbzib_kcrsds_1处理
redo异常强制拉库报ORA-600 kcbzib_kcrsds_1修复
ORA-00603 ORA-01092 ORA-600 kcbzib_kcrsds_1
这种一般就是scn问题,通过修改数据库scn,数据库启动报ORA-16433错误

[oracle@xifenfei ~]$ sqlplus sys/oracle@free as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 3 15:49:00 2024
Version 23.4.0.24.05

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

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/pfile';
ORACLE instance started.

Total System Global Area 1603726344 bytes
Fixed Size                  5360648 bytes
Variable Size             671088640 bytes
Database Buffers          922746880 bytes
Redo Buffers                4530176 bytes
SQL> alter database mount;

Database altered.

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database or pluggable database must be opened in read/write mode.


SQL> shutdown abort;
ORACLE instance shut down.

处理ctl,open数据库成功

SQL> startup nomount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 1603726344 bytes
Fixed Size                  5360648 bytes
Variable Size             671088640 bytes
Database Buffers          922746880 bytes
Redo Buffers                4530176 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "FREE" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 1024
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/opt/oracle/oradata/FREE/redo01.log'  SIZE 200M BLOCKSIZE 512,
  9    GROUP 2 '/opt/oracle/oradata/FREE/redo02.log'  SIZE 200M BLOCKSIZE 512,
 10    GROUP 3 '/opt/oracle/oradata/FREE/redo03.log'  SIZE 200M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/opt/oracle/oradata/FREE/system01.dbf',
 14    '/opt/oracle/oradata/FREE/pdbseed/system01.dbf',
 15    '/opt/oracle/oradata/FREE/sysaux01.dbf',
 16    '/opt/oracle/oradata/FREE/pdbseed/sysaux01.dbf',
 17    '/opt/oracle/oradata/FREE/users01.dbf',
 18    '/opt/oracle/oradata/FREE/pdbseed/undotbs01.dbf',
 19    '/opt/oracle/oradata/FREE/FREEPDB1/system01.dbf',
 20    '/opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf',
 21    '/opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf',
 22    '/opt/oracle/oradata/FREE/FREEPDB1/users01.dbf',
 23    '/opt/oracle/oradata/FREE/undotbs2.dbf'
 24  CHARACTER SET AL32UTF8
 25  ;

Control file created.

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> show pdbs;

          CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------------- ------------------------------ ---------- ----------
               2 PDB$SEED                       READ ONLY  NO
               3 FREEPDB1                       READ WRITE NO

至此当前数据库redo故障模拟和恢复基本完成

发表在 Oracle 23ai, Oracle备份恢复 | 标签为 , , , | 评论关闭

存储故障,强制拉库报ORA-600 kcbzib_kcrsds_1处理

发表于 2024 年 1 月 19 日 由 惜分飞

硬件故障,客户自行强制resetlogs库,报ORA-600 kcbzib_kcrsds_1错误

2024-01-17T17:30:33.094367+08:00
alter database open resetlogs
2024-01-17T17:30:33.105461+08:00
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 16456823130015 time 
.... (PID:1584): Clearing online redo logfile 1 /u01/app/oracle/oradata/XFF/redo01.log
.... (PID:1584): Clearing online redo logfile 2 /u01/app/oracle/oradata/XFF/redo02.log
.... (PID:1584): Clearing online redo logfile 3 /u01/app/oracle/oradata/XFF/redo03.log
Clearing online log 1 of thread 1 sequence number 1345
Clearing online log 2 of thread 1 sequence number 1346
Clearing online log 3 of thread 1 sequence number 1347
2024-01-17T17:30:43.054150+08:00
.... (PID:1584): Clearing online redo logfile 1 complete
.... (PID:1584): Clearing online redo logfile 2 complete
.... (PID:1584): Clearing online redo logfile 3 complete
Resetting resetlogs activation ID 259120030 (0xf71db9e)
Online log /u01/app/oracle/oradata/XFF/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/XFF/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/XFF/redo03.log: Thread 1 Group 3 was previously cleared
2024-01-17T17:30:43.201042+08:00
Setting recovery target incarnation to 3
2024-01-17T17:30:43.267669+08:00
Smart fusion block transfer is disabled:
  instance mounted in exclusive mode.
2024-01-17T17:30:43.282033+08:00
Crash Recovery excluding pdb 2 which was cleanly closed.
Endian type of dictionary set to little
2024-01-17T17:30:43.396061+08:00
Assigning activation ID 280673168 (0x10babb90)
Redo log for group 1, sequence 1 is not located on DAX storage
2024-01-17T17:30:43.433441+08:00
TT00 (PID:1652): Gap Manager starting
2024-01-17T17:30:43.526972+08:00
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/XFF/redo01.log
Successful open of redo thread 1
2024-01-17T17:30:43.528058+08:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Stopping change tracking
2024-01-17T17:30:44.097557+08:00
Errors in file /u01/app/oracle/diag/rdbms/XFF/XFF/trace/XFF_ora_1584.trc(incident=263984)(PDBNAME=CDB$ROOT):
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/XFF/XFF/incident/incdir_263984/XFF_ora_1584_i263984.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2024-01-17T17:30:47.913013+08:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
2024-01-17T17:30:48.228934+08:00
Errors in file /u01/app/oracle/diag/rdbms/XFF/XFF/trace/XFF_ora_1584.trc:
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
2024-01-17T17:30:48.229250+08:00
Errors in file /u01/app/oracle/diag/rdbms/XFF/XFF/trace/XFF_ora_1584.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
2024-01-17T17:30:48.229572+08:00
Errors in file /u01/app/oracle/diag/rdbms/XFF/XFF/trace/XFF_ora_1584.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
Errors in file /u01/app/oracle/diag/rdbms/XFF/XFF/trace/XFF_ora_1584.trc  (incident=263985) (PDBNAME=CDB$ROOT):
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/XFF/XFF/incident/incdir_263985/XFF_ora_1584_i263985.trc
2024-01-17T17:30:50.982998+08:00
opiodr aborting process unknown ospid (1584) as a result of ORA-603
2024-01-17T17:30:50.989089+08:00
ORA-603 : opitsk aborting process

这个错误处理过多次一般是由于scn异常导致,以前部分类似文章
ORA-600 kcbzib_kcrsds_1报错
12C数据库报ORA-600 kcbzib_kcrsds_1故障处理
redo异常强制拉库报ORA-600 kcbzib_kcrsds_1修复
Patch SCN工具一键恢复ORA-600 kcbzib_kcrsds_1
处理好该错误之后,数据库在open pdb的时候报ORA-600 4193错误

2024-01-18T09:59:18.211131+08:00
alter pluggable database all open
2024-01-18T09:59:18.213569+08:00
XFFPDB(4):Pluggable database XFFPDB opening in read write
2024-01-18T09:59:18.590332+08:00
QPI: opatch file present, opatch
QPI: qopiprep.bat file present
2024-01-18T09:59:18.701197+08:00
XFFPDB(4):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
XFFPDB(4):Autotune of undo retention is turned on. 
XFFPDB(4):Endian type of dictionary set to little
2024-01-18T09:59:18.842283+08:00
Buffer Cache Full DB Caching mode changing from FULL CACHING ENABLED to FULL CACHING DISABLED 
Full DB Caching disabled: DEFAULT_CACHE_SIZE should be at least 153531 MBs bigger than current size. 
2024-01-18T09:59:19.017859+08:00
XFFPDB(4):Undo initialization recovery: Parallel FPTR failed: start:233023254 end:233023260 diff:6 ms(0.0 seconds)
XFFPDB(4):Undo initialization recovery: err:0 start: 233023254 end: 233023286 diff: 32 ms (0.0 seconds)
2024-01-18T09:59:19.256942+08:00
XFFPDB(4):[2475] Successfully onlined Undo Tablespace 40.
XFFPDB(4):Undo initialization online undo segments: err:0 start: 233023286 end: 233023500 diff:214 ms(0.2 seconds)
XFFPDB(4):Undo initialization finished serial:0 start:233023228 end:233023508 diff:280 ms (0.3 seconds)
XFFPDB(4):Database Characterset for XFFPDB is ZHS16GBK
XFFPDB(4):*********************************************************************
XFFPDB(4):WARNING: The following temporary tablespaces in container(XFFPDB)
XFFPDB(4):         contain no files.
XFFPDB(4):         This condition can occur when a backup controlfile has
XFFPDB(4):         been restored.  It may be necessary to add files to these
XFFPDB(4):         tablespaces.  That can be done using the SQL statement:
XFFPDB(4): 
XFFPDB(4):         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
XFFPDB(4): 
XFFPDB(4):         Alternatively, if these temporary tablespaces are no longer
XFFPDB(4):         needed, then they can be dropped.
XFFPDB(4):           Empty temporary tablespace: TEMP
XFFPDB(4):*********************************************************************
Errors in file /u01/app/oracle/diag/rdbms/XFF/XFF/trace/XFF_ora_2475.trc  (incident=392032) (PDBNAME=XFFPDB):
ORA-00600: internal error code, arguments: [4193], [14648], [14652], [], [], [], [], [], [], [], [], []
XFFPDB(4):Incident details in:/u01/app/oracle/diag/rdbms/XFF/XFF/incident/incdir_392032/XFF_ora_2475_i392032.trc
XFFPDB(4):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2024-01-18T09:59:20.079597+08:00
XFFPDB(4):*****************************************************************
XFFPDB(4):An internal routine has requested a dump of selected redo.
XFFPDB(4):This usually happens following a specific internal error, when
XFFPDB(4):analysis of the redo logs will help Oracle Support with the
XFFPDB(4):diagnosis.
XFFPDB(4):It is recommended that you retain all the redo logs generated (by
XFFPDB(4):all the instances) during the past 12 hours, in case additional
XFFPDB(4):redo dumps are required to help with the diagnosis.
XFFPDB(4):*****************************************************************
===========================================================
Dumping current patch information
===========================================================
Unable to obtain current patch information due to error: 20001
===========================================================
2024-01-18T09:59:31.793666+08:00
XFFPDB(4):Flush retried for xcb 0x179400620, pmd 0x174a2f6c0
XFFPDB(4):Doing block recovery for file 31 block 1239
2024-01-18T09:59:31.805351+08:00
Errors in file /u01/app/oracle/diag/rdbms/XFF/XFF/trace/XFF_ora_2475.trc  (incident=392034)(PDBNAME=XFFPDB):
ORA-00600: internal error code, arguments: [4193], [14648], [14652], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4193], [14648], [14652], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4193], [14648], [14652], [], [], [], [], [], [], [], [], []
XFFPDB(4):Incident details in:/u01/app/oracle/diag/rdbms/XFF/XFF/incident/incdir_392034/XFF_ora_2475_i392034.trc
XFFPDB(4):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2024-01-18T09:59:32.009996+08:00
XFFPDB(4):*****************************************************************
XFFPDB(4):An internal routine has requested a dump of selected redo.
XFFPDB(4):This usually happens following a specific internal error, when
XFFPDB(4):analysis of the redo logs will help Oracle Support with the
XFFPDB(4):diagnosis.
XFFPDB(4):It is recommended that you retain all the redo logs generated (by
XFFPDB(4):all the instances) during the past 12 hours, in case additional
XFFPDB(4):redo dumps are required to help with the diagnosis.
XFFPDB(4):*****************************************************************
Incident details in: /u01/app/oracle/diag/rdbms/XFF/XFF/incident/incdir_392384/XFF_mz00_2879_i392384.trc
2024-01-18T09:59:36.918296+08:00
Reread of blocknum=142165, file=/u01/app/oracle/oradata/XFF/system01.dbf. found same corrupt data
Reread of blocknum=142165, file=/u01/app/oracle/oradata/XFF/system01.dbf. found same corrupt data
Reread of blocknum=142165, file=/u01/app/oracle/oradata/XFF/system01.dbf. found same corrupt data
Reread of blocknum=142165, file=/u01/app/oracle/oradata/XFF/system01.dbf. found same corrupt data
Reread of blocknum=142165, file=/u01/app/oracle/oradata/XFF/system01.dbf. found same corrupt data
2024-01-18T09:59:41.146850+08:00
XFFPDB(4):Errors in file /u01/app/oracle/diag/rdbms/XFF/XFF/trace/XFF_ora_2475.trc:
ORA-00600: internal error code, arguments: [4193], [14648], [14652], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4193], [14648], [14652], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4193], [14648], [14652], [], [], [], [], [], [], [], [], []
2024-01-18T09:59:45.434605+08:00
Errors in file /u01/app/oracle/diag/rdbms/XFF/XFF/trace/XFF_ora_2475.trc  (incident=407714):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4193], [14648], [14652], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4193], [14648], [14652], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4193], [14648], [14652], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/XFF/XFF/incident/incdir_407714/XFF_ora_2475_i407714.trc
2024-01-18T09:59:45.439083+08:00
Errors in file /u01/app/oracle/diag/rdbms/XFF/XFF/incident/incdir_407714/XFF_ora_2475_i407714.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4193], [14648], [14652], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4193], [14648], [14652], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4193], [14648], [14652], [], [], [], [], [], [], [], [], []
2024-01-18T09:59:48.575971+08:00
XFFPDB(4):pdb open recovery: pdbid=4 log=255 acquired_ip=1

这个相对比较简单,对异常undo进行处理,cdb和pdb都open成功,使用数据泵把数据迁移到新库,完成本次恢复(比较幸运,硬件故障的库直接迁移成功,没有报其他错误)

发表在 Oracle备份恢复 | 标签为 , | 评论关闭