分类目录归档：Oracle备份恢复

redo写丢失导致ORA-600 kcrf_resilver_log_1故障

发表于 2024 年 8 月 22 日 由 惜分飞

有一个客户硬件故障,做完硬件恢复之后,数据库启动报ORA-600 kcrf_resilver_log_1错误.
kcrf_resilver_log_1

Thu Aug 22 13:37:50 2024
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 3 processes
Started redo scan
Errors in file e:\oracle\zy\diag\rdbms\orcl\orcl\trace\orcl_ora_1640.trc  (incident=9767):
ORA-00600: 内部错误代码, 参数: [kcrf_resilver_log_1], [0x7DCEBE020], [2], [], [], [], [], [], [], [], [], []
Incident details in: e:\oracle\zy\diag\rdbms\orcl\orcl\incident\incdir_9767\orcl_ora_1640_i9767.trc
Thu Aug 22 13:37:55 2024
Trace dumping is performing id=[cdmp_20240822133755]
Aborting crash recovery due to error 600
Errors in file e:\oracle\zy\diag\rdbms\orcl\orcl\trace\orcl_ora_1640.trc:
ORA-00600: 内部错误代码, 参数: [kcrf_resilver_log_1], [0x7DCEBE020], [2], [], [], [], [], [], [], [], [], []
Errors in file e:\oracle\zy\diag\rdbms\orcl\orcl\trace\orcl_ora_1640.trc:
ORA-00600: 内部错误代码, 参数: [kcrf_resilver_log_1], [0x7DCEBE020], [2], [], [], [], [], [], [], [], [], []

查询mos出现该问题的原因一般是由于redo log write lost导致
kcrf_resilver_log_1-9056657

这个问题恢复起来不难,一般就是尝试强制打开库,以前有过类似的恢复case:
ORA-600 kcrf_resilver_log_1故障处理
ORA-00600[kcrf_resilver_log_1]异常恢复

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

200T 数据库非归档无备份恢复

发表于 2024 年 8 月 14 日 由 惜分飞

一套近200T的,6个节点的RAC,由于存储管线链路不稳定,导致服务器经常性掉盘,引起asm 磁盘组频繁dismount/mount,数据库集群节点不停的重启,修复好链路问题之后,数据库启动报ORA-01113,ORA-01110
ORA-01113-ORA-01110

通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)脚本检测,发现有10个数据文件异常,无法正常恢复
20240814155122
该库比较大,有近200T,因此恢复需要各位谨慎(无法做现场备份,另外客户要求2天时间必须恢复好)
200t
由于数据库是非归档模式,该库无法通过应用归档日志来实现对这些文件进行恢复,对于这种情况,直接使用dbms_diskgroup把数据文件头拷贝到文件系统中,类似操作

SQL> @dbms_diskgroup_get_block.sql  +DATA/xifenfei.dbf 1 1 /tmp/xff/xifenfei.dbf.header

Parameter 1:
ASM_file_name (required)


Parameter 2:
block_to_extract (required)


Parameter 3
number_of_blocks_to_extract (required)


Parameter 4:
FileSystem_File_Name (required)

old  14:  v_AsmFilename := '&ASM_File_Name';
new  14:  v_AsmFilename := '+DATA/xifenfei.dbf';
old  15:  v_offstart := '&block_to_extract';
new  15:  v_offstart := '1';
old  16:  v_numblks := '&number_of_blocks_to_extract';
new  16:  v_numblks := '1';
old  17:  v_FsFilename := '&FileSystem_File_Name';
new  17:  v_FsFilename := '/tmp/xff/xifenfei.dbf.header';
File: +DATA/xifenfei.dbf
Type: 2 Data File
Size (in logical blocks): 3978880
Logical Block Size: 16384
Physical Block Size: 512

PL/SQL procedure successfully completed.

然后通过bbed修改相关scn

BBED> set filename 'xifenfei.dbf.header'
	FILENAME       	xifenfei.dbf.header

BBED> set blocksize 16384
	BLOCKSIZE      	16384

BBED> map
 File: xifenfei.dbf.header (0)
 Block: 1                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header

 struct kcvfh, 860 bytes                    @0       

 ub4 tailchk                                @16380   


BBED> p kcvfh.kcvfhckp.kcvcpscn
struct kcvcpscn, 8 bytes                    @484     
   ub4 kscnbas                              @484      0xa8061324
   ub2 kscnwrp                              @488      0x0081

BBED> assign file 295 block 1 kcvfh.kcvfhckp.kcvcpscn = file 1 block 1 kcvfh.kcvfhckp.kcvcpscn;
struct kcvcpscn, 8 bytes                    @484     
   ub4 kscnbas                              @484      0xa8133e2b
   ub2 kscnwrp                              @488      0x0081

然后把修改的数据文件头写回到asm中

SQL> @dbms_diskgroup_cp_block_to_asm.sql  /tmp/xff/xifenfei.dbf.header  +DATA/xifenfei.dbf 1 1 

Parameter 1:
v_FsFileName (required)


Parameter 2:
v_AsmFileName (required)


Parameter 3
v_offstart (required)


Parameter 4
v_numblks (required)

old  16: v_FsFileName := '&v_FsFileName';
new  16: v_FsFileName := '/tmp/xff/xifenfei.dbf.header';
old  17: v_AsmFileName := '&v_AsmFileName';
new  17: v_AsmFileName := '+DATA/xifenfei.dbf';
old  18: v_offstart := '&v_offstart';
new  18: v_offstart := '1';
old  19:  v_numblks := '&v_numblks';
new  19:  v_numblks := '1';
File: +DATA/xifenfei.dbf
Type: 2 Data File
Size (in logical blocks): 3978880
Logical Block Size: 16384

PL/SQL procedure successfully completed.

查询文件头是否修改成功

[oracle@xff1 xff]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 10 16:45:02 2024

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> set numw 16
SQL> select CHECKPOINT_CHANGE# from v$datafile_header where file# in (1,295);

CHECKPOINT_CHANGE#
------------------
      556870614571
      556870614571

SQL> recover datafile 295;
Media recovery complete.

通过上述操作,确认bbed修改文件头成功,后续类似方法对其他9个文件进行修改,并打开数据库

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

Database altered.

alert日志提示

Sat Aug 10 16:46:11 2024
ALTER DATABASE RECOVER  datafile 295  
Media Recovery Start
Serial Media Recovery started
WARNING! Recovering data file 295 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
Media Recovery Complete (xff1)
Completed: ALTER DATABASE RECOVER  datafile 295  
Sat Aug 10 16:46:39 2024
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Sat Aug 10 16:46:51 2024
WARNING! Recovering data file 1139 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 1140 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 1601 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 1803 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 1827 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 1931 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 2185 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 2473 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 2616 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
Sat Aug 10 16:46:54 2024
Parallel Media Recovery started with 64 slaves
Media Recovery Complete (xff1)
Completed: ALTER DATABASE RECOVER  database  
Sat Aug 10 17:19:58 2024
alter database open
This instance was first to open
Sat Aug 10 17:19:58 2024
SUCCESS: diskgroup DATA was mounted
Sat Aug 10 17:19:58 2024
NOTE: dependency between database xff and diskgroup resource ora.DATA.dg is established
Sat Aug 10 17:20:10 2024
Picked broadcast on commit scheme to generate SCNs
Sat Aug 10 17:20:10 2024
SUCCESS: diskgroup REDO was mounted
Sat Aug 10 17:20:10 2024
NOTE: dependency between database xff and diskgroup resource ora.REDO.dg is established
Thread 1 opened at log sequence 124958
  Current log# 14 seq# 124958 mem# 0: +REDO/xff/log2.ora
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Aug 10 17:20:14 2024
SMON: enabling cache recovery
Instance recovery: looking for dead threads
Instance recovery: lock domain invalid but no dead threads
[33770] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:261099864 end:261100854 diff:990 (9 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
Sat Aug 10 17:20:16 2024
minact-scn: Inst 1 is now the master inc#:2 mmon proc-id:33650 status:0x7
minact-scn status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000
Starting background process GTX0
Sat Aug 10 17:20:16 2024
GTX0 started with pid=45, OS id=34119 
Starting background process RCBG
Sat Aug 10 17:20:16 2024
RCBG started with pid=46, OS id=34121 
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sat Aug 10 17:20:16 2024
QMNC started with pid=47, OS id=34134 
Starting background process SMCO
Completed: alter database open

其他集群其他节点数据库,一切正常
20240814162201

检查数据字典一致性

SQL> @hcheck.sql
HCheck Version 07MAY18 on 10-AUG-2024 18:24:49
----------------------------------------------
Catalog Version 11.2.0.3.0 (1102000300)
db_name: XFF

				   Catalog	 Fixed
Procedure Name			   Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj		       ... 1102000300 <=  *All Rel* 08/10 18:24:49 PASS
.- MissingOIDOnObjCol	       ... 1102000300 <=  *All Rel* 08/10 18:24:49 PASS
.- SourceNotInObj	       ... 1102000300 <=  *All Rel* 08/10 18:24:49 PASS
.- OversizedFiles	       ... 1102000300 <=  *All Rel* 08/10 18:24:50 PASS
.- PoorDefaultStorage	       ... 1102000300 <=  *All Rel* 08/10 18:24:50 PASS
.- PoorStorage		       ... 1102000300 <=  *All Rel* 08/10 18:24:50 PASS
.- TabPartCountMismatch        ... 1102000300 <=  *All Rel* 08/10 18:24:50 PASS
.- OrphanedTabComPart	       ... 1102000300 <=  *All Rel* 08/10 18:24:50 PASS
.- MissingSum$		       ... 1102000300 <=  *All Rel* 08/10 18:24:50 PASS
.- MissingDir$		       ... 1102000300 <=  *All Rel* 08/10 18:24:50 PASS
.- DuplicateDataobj	       ... 1102000300 <=  *All Rel* 08/10 18:24:50 PASS
.- ObjSynMissing	       ... 1102000300 <=  *All Rel* 08/10 18:24:51 PASS
.- ObjSeqMissing	       ... 1102000300 <=  *All Rel* 08/10 18:24:51 PASS
.- OrphanedUndo 	       ... 1102000300 <=  *All Rel* 08/10 18:24:51 PASS
.- OrphanedIndex	       ... 1102000300 <=  *All Rel* 08/10 18:24:51 PASS
.- OrphanedIndexPartition      ... 1102000300 <=  *All Rel* 08/10 18:24:51 PASS
.- OrphanedIndexSubPartition   ... 1102000300 <=  *All Rel* 08/10 18:24:52 PASS
.- OrphanedTable	       ... 1102000300 <=  *All Rel* 08/10 18:24:52 PASS
.- OrphanedTablePartition      ... 1102000300 <=  *All Rel* 08/10 18:24:52 PASS
.- OrphanedTableSubPartition   ... 1102000300 <=  *All Rel* 08/10 18:24:52 PASS
.- MissingPartCol	       ... 1102000300 <=  *All Rel* 08/10 18:24:52 PASS
.- OrphanedSeg$ 	       ... 1102000300 <=  *All Rel* 08/10 18:24:52 PASS
.- OrphanedIndPartObj#	       ... 1102000300 <=  *All Rel* 08/10 18:24:52 PASS
.- DuplicateBlockUse	       ... 1102000300 <=  *All Rel* 08/10 18:24:52 PASS
.- FetUet		       ... 1102000300 <=  *All Rel* 08/10 18:24:52 PASS
.- Uet0Check		       ... 1102000300 <=  *All Rel* 08/10 18:24:52 PASS
.- SeglessUET		       ... 1102000300 <=  *All Rel* 08/10 18:24:52 PASS
.- BadInd$		       ... 1102000300 <=  *All Rel* 08/10 18:24:52 PASS
.- BadTab$		       ... 1102000300 <=  *All Rel* 08/10 18:24:53 PASS
.- BadIcolDepCnt	       ... 1102000300 <=  *All Rel* 08/10 18:24:53 PASS
.- ObjIndDobj		       ... 1102000300 <=  *All Rel* 08/10 18:24:53 PASS
.- TrgAfterUpgrade	       ... 1102000300 <=  *All Rel* 08/10 18:24:53 PASS
.- ObjType0		       ... 1102000300 <=  *All Rel* 08/10 18:24:53 PASS
.- BadOwner		       ... 1102000300 <=  *All Rel* 08/10 18:24:53 PASS
.- StmtAuditOnCommit	       ... 1102000300 <=  *All Rel* 08/10 18:24:53 PASS
.- BadPublicObjects	       ... 1102000300 <=  *All Rel* 08/10 18:24:53 PASS
.- BadSegFreelist	       ... 1102000300 <=  *All Rel* 08/10 18:24:53 PASS
.- BadDepends		       ... 1102000300 <=  *All Rel* 08/10 18:24:53 PASS
.- CheckDual		       ... 1102000300 <=  *All Rel* 08/10 18:24:53 PASS
.- ObjectNames		       ... 1102000300 <=  *All Rel* 08/10 18:24:53 PASS
.- BadCboHiLo		       ... 1102000300 <=  *All Rel* 08/10 18:24:54 PASS
.- ChkIotTs		       ... 1102000300 <=  *All Rel* 08/10 18:24:54 PASS
.- NoSegmentIndex	       ... 1102000300 <=  *All Rel* 08/10 18:24:54 PASS
.- BadNextObject	       ... 1102000300 <=  *All Rel* 08/10 18:24:54 PASS
.- DroppedROTS		       ... 1102000300 <=  *All Rel* 08/10 18:24:54 PASS
.- FilBlkZero		       ... 1102000300 <=  *All Rel* 08/10 18:24:54 PASS
.- DbmsSchemaCopy	       ... 1102000300 <=  *All Rel* 08/10 18:24:54 PASS
.- OrphanedObjError	       ... 1102000300 >  1102000000 08/10 18:24:54 PASS
.- ObjNotLob		       ... 1102000300 <=  *All Rel* 08/10 18:24:54 PASS
.- MaxControlfSeq	       ... 1102000300 <=  *All Rel* 08/10 18:24:55 PASS
.- SegNotInDeferredStg	       ... 1102000300 >  1102000000 08/10 18:25:18 PASS
.- SystemNotRfile1	       ... 1102000300 >   902000000 08/10 18:25:18 PASS
.- DictOwnNonDefaultSYSTEM     ... 1102000300 <=  *All Rel* 08/10 18:25:18 PASS
.- OrphanTrigger	       ... 1102000300 <=  *All Rel* 08/10 18:25:18 PASS
.- ObjNotTrigger	       ... 1102000300 <=  *All Rel* 08/10 18:25:18 PASS
---------------------------------------
10-AUG-2024 18:25:18  Elapsed: 29 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)

PL/SQL procedure successfully completed.

Statement processed.

Complete output is in trace file:
/u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_71148_HCHECK.trc

运气不错,数据字典本身没有损坏,业务直接运行,一切正常(主要原因是在光纤链路不稳定的情况下,客户已经没有往库中写入数据)

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

断电引起redo和数据文件不一致故障恢复

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

有些时候故障总是来的让人非常意外,这个在准备停机迁移数据库之前的几分钟由于某种原因直接导致主机掉电,再次开机数据库无法启动

Sat Aug 03 23:10:37 2024
Successful mount of redo thread 1, with mount id 3696805928
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount
Sat Aug 03 23:10:43 2024
alter database open
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_6808.trc:
ORA-01113: 文件 21 需要介质恢复
ORA-01110: 数据文件 21: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\XIFENFEI.DBF'
ORA-1113 signalled during: alter database open...

尝试数据库恢复各种报错ORA-600 kdourp_inorder2,ORA-600 3020,ORA-7445 kdxlin等

ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XFF\ARCHIVELOG\2024_08_03\O1_MF_1_1159998_MBW605HP_.ARC
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XFF\ARCHIVELOG\2024_08_03\O1_MF_1_1159999_MBW63QBY_.ARC
Sat Aug 03 23:22:10 2024
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xC] [PC:0x14306B54A, kdxlin()+4432]
Sat Aug 03 23:22:10 2024
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xC] [PC:0x14306B54A, kdxlin()+4432]
Sat Aug 03 23:22:10 2024
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_pr25_7740.trc  (incident=132557):
ORA-00600: internal error code, arguments: [kdourp_inorder2], [4], [22], [44], [44], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\incident\incdir_132557\xff_pr25_7740_i132557.trc
ERROR: Unable to normalize symbol name for the following short stack (at offset 213):
dbgexProcessError()+200<-dbgeExecuteForError()+65<-dbgePostErrorKGE()+2269<-dbkePostKGE_kgsf()+77<-kgeade()+562
<-kgerelv()+151<-kgerev()+45<-kgerec5()+60<-sss_xcpt_EvalFilterEx()+1862<-sss_xcpt_EvalFilter()+174
<-.1.4_5+59<-00007FFCB5E2C92F<-00007FFCB5E3D82D<-00007FFCB5DE916B<-00007FFCB5E3C9EE<-kdxlin()+4432
<-kco_issue_callback()+196<-kcoapl()+746<-kcbr_apply_change()+6156<-kcbr_mapply_change()+1162
<-kcbrapply()+2297<-kcbr_apply_pending()+2931<-krp_slave_apply()+1155<-krp_slave_main()+4010<-ksvrdp()+2580
<-opirip()+904<-opidrv()+906<-sou2o()+98<-opimai_real()+280<-opimai()+191<-BackgroundThreadStart()+646
<-00007FFCB562168D<-00007FFCB5E14629
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_pr1w_6472.trc  (incident=132485):
ORA-07445: exception encountered: core dump [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A]
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\incident\incdir_132485\xff_pr1w_6472_i132485.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 D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_pr2o_7472.trc  (incident=132709):
ORA-07445: exception encountered: core dump [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] 
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\incident\incdir_132709\xff_pr2o_7472_i132709.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sat Aug 03 23:22:11 2024
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_pr18_7812.trc  (incident=132301):
ORA-00600: internal error code, arguments: [3020], [62], [517633], [260564481], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 62, block# 517633, file offset is 4240449536 bytes)
ORA-10564: tablespace HSEMR_TAB
ORA-01110: data file 62: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\EMR006.DBF'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Sat Aug 03 23:22:56 2024
Slave exiting with ORA-10562 exception
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_pr25_7740.trc:
ORA-10562: Error occurred while applying redo to data block (file# 64, block# 508263)
ORA-10564: tablespace HSEMR_TAB
ORA-01110: data file 64: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\HSEMR_TAB008.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 467202
ORA-00600: internal error code, arguments: [kdourp_inorder2], [4], [22], [44], [44], [], [], []
Sat Aug 03 23:22:56 2024
Slave exiting with ORA-10562 exception
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_pr1w_6472.trc:
ORA-10562: Error occurred while applying redo to data block (file# 65, block# 498512)
ORA-10564: tablespace HSEMR_TAB
ORA-01110: data file 65: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\EMR009.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 467200
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] 
Sat Aug 03 23:22:57 2024
Media Recovery failed with error 448
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_pr00_6732.trc:
ORA-00283: recovery session canceled due to errors
ORA-00448: normal completion of background process
Sat Aug 03 23:22:57 2024
ORA-600 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER CANCEL 
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...

通过分析确认有部分数据文件和redo信息不匹配,导致无法正常recover成功

SQL> recover datafile 77;
完成介质恢复。
SQL> recover datafile 78;
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [78], [473221], [327628933], [], [], [],
[], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 78, block# 473221, file
offset is 3876626432 bytes)
ORA-10564: tablespace HSEMR_TAB
ORA-01110: 数据文件 78: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\HIS23.DBF'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'

SQL> recover datafile 66;
ORA-00279: 更改 6029114092 (在 08/03/2024 19:44:05 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XFF\ARCHIVELOG\2024_08_03\O1_MF_1_115999

9_MBW63QBY_.ARC
ORA-00280: 更改 6029114092 (用于线程 1) 在序列 #1159999 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
已应用的日志。
完成介质恢复。
SQL> recover datafile 65;
ORA-00279: 更改 6029114092 (在 08/03/2024 19:44:05 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XFF\ARCHIVELOG\2024_08_03\O1_MF_1_115999

9_MBW63QBY_.ARC
ORA-00280: 更改 6029114092 (用于线程 1) 在序列 #1159999 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: 恢复会话因错误而取消
ORA-10562: Error occurred while applying redo to data block (file# 65, block#
498544)
ORA-10564: tablespace HSEMR_TAB
ORA-01110: 数据文件 65: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\EMR009.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 467200
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC]
[PC:0x14306B54A] [UNABLE_TO_READ] []


ORA-01112: 未启动介质恢复

对于最终无法正常recover成功数据文件,使用Oracle数据库恢复利器：Oracle Recovery Tools工具快速调整scn
oracle-recovery-tools

然后重建ctl，recover 数据库并open成功

Sun Aug 04 01:01:51 2024
Successful mount of redo thread 1, with mount id 3696824638
Completed: CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 200
    MAXINSTANCES 8
    MAXLOGXFFTORY 23360
LOGFILE
  GROUP 1 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE
  'D:\APP\ADMINISTRATOR\ORADATA\XFF\SYSTEM01.DBF',
  'D:\APP\ADMINISTRATOR\ORADATA\XFF\SYSAUX01.DBF',
……
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Only allocated 127 recovery slaves (requested 128)
Parallel Media Recovery started with 127 slaves
Sun Aug 04 01:01:56 2024
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1160002 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO02.LOG
Completed: ALTER DATABASE RECOVER  database  
Sun Aug 04 01:02:20 2024
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 32 processes
Started redo scan
Completed redo scan
 read 1946 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 1160002, block 2, scn 6029119350
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1160002 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO02.LOG
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 1160002, block 3895, scn 6029139793
 0 data blocks read, 0 data blocks written, 1946 redo k-bytes read
Initializing SCN for created control file
Database SCN compatibility initialized to 3
Sun Aug 04 01:02:21 2024
LGWR: STARTING ARCH PROCESSES
Sun Aug 04 01:02:21 2024
ARC0 started with pid=71, OS id=2772 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Sun Aug 04 01:02:22 2024
ARC1 started with pid=72, OS id=7996 
Sun Aug 04 01:02:22 2024
ARC2 started with pid=73, OS id=2900 
Sun Aug 04 01:02:22 2024
ARC3 started with pid=74, OS id=6856 
Archived Log entry 1 added for thread 1 sequence 1160000 ID 0xc4814d77 dest 1:
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Thread 1 advanced to log sequence 1160003 (thread open)
Thread 1 opened at log sequence 1160003
  Current log# 1 seq# 1160003 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Aug 04 01:02:23 2024
SMON: enabling cache recovery
Archived Log entry 2 added for thread 1 sequence 1160002 ID 0xc4814d77 dest 1:
Archived Log entry 3 added for thread 1 sequence 1160001 ID 0xc4814d77 dest 1:
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
[7808] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:7657234 end:7657703 diff:469 (4 seconds)
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
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         Txff 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
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused txff:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
   then disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sun Aug 04 01:02:27 2024
QMNC started with pid=75, OS id=7884 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open

后续处理异常表,lob,index等数据,客户业务测试都ok,完成本次恢复工作

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