200T 数据库非归档无备份恢复---惜分飞

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

通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)脚本检测,发现有10个数据文件异常,无法正常恢复

该库比较大,有近200T,因此恢复需要各位谨慎(无法做现场备份,另外客户要求2天时间必须恢复好)

由于数据库是非归档模式,该库无法通过应用归档日志来实现对这些文件进行恢复,对于这种情况,直接使用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

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

检查数据字典一致性

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