oracle asm 数据块重构恢复

1. 内容概述

   oracle asm dd格式化50M以内，oracle metadata信息会被格式化，oracle磁盘组无法mount，
存储在asm中的数据面临丢失的风险，针对该场景本文使用数据块重构方法进行恢复。

2. 环境准备

创建asm磁盘组

SQL> create diskgroup test external redundancy disk '/dev/oracleasm/disks/arch1';

Diskgroup created.

SQL>

创建数据文件

SQL> create tablespace tt666 datafile '+test' size 100M;

Tablespace created.

SQL>

创建测试表

SQL> create table t_666 tablespace tt666 as select * from obj$;
select count(1) from t_666;
Table created.

SQL> 

  COUNT(1)
----------
     86420

SQL>

3. 破坏asm磁盘

dd if=/dev/zero of=/dev/oracleasm/disks/arch1 bs=1M count=50 conv=notrunc

4. 检查asm磁盘组

[grid@snyxdb1 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      3072     2977                0            2977              0             N  ARCH/
MOUNTED  EXTERN  N         512   4096  1048576     30720    16942                0           16942              0             Y  DATA/
[grid@snyxdb1 ~]$

检查asm日志

NOTE: cache deleting context for group TEST 3/0x41a9310d
GMON dismounting group 3 at 33 for pid 33, osid 79556
NOTE: Disk TEST_0000 in mode 0x7f marked for de-assignment
SUCCESS: diskgroup TEST was dismounted
Fri Jan 22 10:08:43 2021
NOTE: diskgroup resource ora.TEST.dg is offline
SUCCESS: alter diskgroup TEST dismount force /* ASM SERVER:1101607181 */
SUCCESS: ASM-initiated MANDATORY DISMOUNT of group TEST

检查数据库状态

[oracle@snyxdb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 22 10:13:03 2021

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1185853440 bytes
Fixed Size		    2252664 bytes
Variable Size		  436207752 bytes
Database Buffers	  721420288 bytes
Redo Buffers		   25972736 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 18 - see DBWR trace file
ORA-01110: data file 18: '+TEST/epmsn/datafile/tt666.256.1062497259'


SQL>

5. 重新mount asm磁盘组

SQL> alter diskgroup test mount;
alter diskgroup test mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "TEST" cannot be mounted
ORA-15040: diskgroup is incomplete


SQL>

6. scan asmdisk恢复数据文件

[root@snyxdb1 xdul]# ./xdul

xdul: Data Unload for Oracle version 1.1.1

Copyright(c) 2020 orastar.All rights reserved.

Wechat: xidoublestr
Email: 634025070@qq.com
loading default config.......

load config file 'config.txt' successful
loading default control file ......


ts#	fn	rfn	bsize	blocks	filename                  	kscnbas_484	kcvcptim_496	kcvfhrlc_112	kscnbas_116
---- ---- ---- ----- -------- -------------------------------------------- -------- -------- -------- --------
16	14	14	8192	1280	/home/grid/star2.dbf      	766cb2      	3f5418ae      	3c54493e      	e2006
load control file 'control.txt' successful
XDUL>scan asmdisk
XDUL>extract datafile
input extract file_no:18

[root@snyxdb1 xdul]# ls -ltr data/
total 102408
-rw-r--r-- 1 root root 104865792 Jan 22 10:28 18.dbf  <-- extract生成数据文件
[root@snyxdb1 xdul]#

7. dbv检查数据文件

[oracle@snyxdb1 data]$ dbv file=18.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Fri Jan 22 10:30:12 2021

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

DBVERIFY - Verification starting : FILE = /home/oracle/xdul/data/18.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 1068
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 127
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 11605
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 7764758 (0.7764758)
[oracle@snyxdb1 data]$

7. 重启数据库

SQL> select name from v$datafile where file#=18;

NAME
--------------------------------------------------------------------------------
+TEST/epmsn/datafile/tt666.256.1062497259
SQL> alter database rename file '+TEST/epmsn/datafile/tt666.256.1062497259' to '/home/oracle/xdul/data/18.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL>

8. 验证测试数据

SQL> select count(1) from t_666;

  COUNT(1)
----------
     86420

SQL>