Oracle Recovery Tools快速恢复ORA-19909

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

标题：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没有被覆盖均可恢复

ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况

Posted on 2022 年 11 月 26 日 by 惜分飞

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

标题：ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况

由于主机异常断电，导致oracle数据库无法正常启动,数据库启动报错ORA-07445 kdxlin,ORA-01172,ORA-00312,ORA-00742等错误

Fri Nov 25 11:24:53 2022
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 900 KB redo, 386 data blocks need recovery
Started redo application at
 Thread 1: logseq 93214, block 60163
Recovery of Online Redo Log: Thread 1 Group 1 Seq 93214 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
Completed redo application of 0.46MB
Fri Nov 25 11:25:02 2022
Hex dump of (file 3, block 208) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c000d0 (file 3, block 208)
Reread (file 3, block 208) found valid data
Hex dump of (file 3, block 208) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc
Repaired corruption at (file 3, block 208)
Hex dump of (file 3, block 152) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c00098 (file 3, block 152)
Reread (file 3, block 152) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 152 OF FILE 3
Fri Nov 25 11:25:02 2022
Hex dump of (file 3, block 6859) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p001_19268.trc
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c01acb (file 3, block 6859)
Reread (file 3, block 6859) found same corrupt data (logically corrupt)
Fri Nov 25 11:25:13 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc  (incident=224379):
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224379\XFF_p010_7024_i224379.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Nov 25 11:25:13 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p005_12036.trc  (incident=224343):
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224343\XFF_p005_12036_i224343.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Nov 25 11:25:18 2022
Sweep [inc][224379]: completed
Sweep [inc][224343]: completed
Sweep [inc2][224379]: completed
Sweep [inc2][224343]: completed
RECOVERY OF THREAD 1 STUCK AT BLOCK 6859 OF FILE 3
Fri Nov 25 11:25:33 2022
Slave exiting with ORA-1172 exception
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc:
ORA-01172: 线程 1 的恢复停止在块 152 (在文件 3 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
Fri Nov 25 11:25:34 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p001_19268.trc:
ORA-10388: parallel query server interrupt (failure)
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p001_19268.trc:
ORA-10388: parallel query server interrupt (failure)
Fri Nov 25 11:25:38 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc:
ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况
ORA-00312: 联机日志 1 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG'
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Fri Nov 25 11:25:41 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p005_12036.trc  (incident=224344):
ORA-01578: ORACLE 数据块损坏 (文件号 27, 块号 520567)
ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF'
ORA-10564: tablespace POWERMPP
ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 89776
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224344\XFF_p005_12036_i224344.trc
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p005_12036.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 27, 块号 520567)
ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF'
ORA-10564: tablespace POWERMPP
ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 89776
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc  (incident=224380):
ORA-01578: ORACLE 数据块损坏 (文件号 26, 块号 227101)
ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF'
ORA-10564: tablespace POWERMSP
ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 99375
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Fri Nov 25 11:25:51 2022
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224380\XFF_p010_7024_i224380.trc
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 26, 块号 227101)
ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF'
ORA-10564: tablespace POWERMSP
ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 99375
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Fri Nov 25 11:25:54 2022
Aborting crash recovery due to slave death, attempting serial crash recovery
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 900 KB redo, 386 data blocks need recovery
Started redo application at
 Thread 1: logseq 93214, block 60163
Recovery of Online Redo Log: Thread 1 Group 1 Seq 93214 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
Hex dump of (file 3, block 6743) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_4172.trc
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c01a57 (file 3, block 6743)
Reread (file 3, block 6743) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 6743 OF FILE 3
Fri Nov 25 11:26:09 2022
Aborting crash recovery due to error 1172
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_4172.trc:
ORA-01172: 线程 1 的恢复停止在块 6743 (在文件 3 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_4172.trc:
ORA-01172: 线程 1 的恢复停止在块 6743 (在文件 3 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
ORA-1172 signalled during: alter database open...

尝试人工recover恢复,报ORA-00283 ORA-00742 ORA-00312错误

SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况
ORA-00312: 联机日志 1 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG'

通过特殊这里之后recover库成功

SQL> recover database until cancel;
ORA-00279: 更改 47073228694 (在 11/25/2022 08:11:15 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XFF\ARCHIVELOG\2022_11_25\O1_MF_1_932
14_%U_.ARC
ORA-00280: 更改 47073228694 (用于线程 1) 在序列 #93214 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
已应用的日志。
完成介质恢复。

打开数据库报ORA-600 2662错误

使用oracle patch scn工具快速修改 open库成功

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

Total System Global Area       1603411968 bytes
Fixed Size                        2281656 bytes
Variable Size                  1191186248 bytes
Database Buffers                402653184 bytes
Redo Buffers                      7290880 bytes
数据库装载完毕。
SQL> ALTER DATABASE OPEN;

数据库已更改。

然后逻辑导出数据,导入新库,完成数据迁移工作

ora-600 kccpb_sanity_check_2故障处理

Posted on 2022 年 11 月 17 日 by 惜分飞

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

标题：ora-600 kccpb_sanity_check_2故障处理

数据库启动报ORA-600 kccpb_sanity_check_2

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

Total System Global Area 1258291200 bytes
Fixed Size                  1250548 bytes
Variable Size             243272460 bytes
Database Buffers         1006632960 bytes
Redo Buffers                7135232 bytes
ORA-00600: ??????, ??: [kccpb_sanity_check_2], [66014], [66011], [0x0], [], [],[], []

重建控制文件报错ora-600 kccsga_update_amx_1

SQL> CREATE CONTROLFILE REUSE DATABASE "zs" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 50
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 226
  7  LOGFILE
  8    GROUP 1 'd:\zs\redo01.log'  SIZE 50M,
  9    GROUP 2 'd:\zs\redo02.log'  SIZE 50M,
 10    GROUP 3 'd:\zs\redo03.log'  SIZE 50M
 11  DATAFILE
 12  'd:\zs\SYSAUX01.DBF',
………………
 22  'd:\zs\SYSTEM01.DBF',
 23  'd:\zs\UNDOTBS01.DBF',
 24  'd:\zs\USERS01.DBF'
 25  CHARACTER SET zhs16gbk
 26  ;
CREATE CONTROLFILE REUSE DATABASE "zs" NORESETLOGS  NOARCHIVELOG
*
第 1 行出现错误:
ORA-01503: CREATE CONTROLFILE ??
ORA-00600: ??????, ??: [kccsga_update_amx_1], [9], [2920], [292], [], [], [],[]

重启实例，重建ctl成功.尝试恢复库提示需要很久之前的日志，因为有两个数据文件scn异常