1_oracle asm磁盘组异常_全库重构恢复

1. 内容介绍

     由于服务器掉电、人为误操作等原因造成asm磁盘组无法挂载，数据库无法启动，业务系统面试数据丢失的风险，本文主要测试以下问题，
1、asm磁盘metadata损坏，全库datafile重构恢复。
2、优化多个tempfile与datafile重突
3、优化重构数据块日志显示
复制

2. 环境检查

SQL> set linesize 200 pagesize 200
col name for a60
select name from v$datafile
union
select name from v$tempfile
union
select member from v$logfile;SQL> SQL>   2    3    4    5  

NAME
------------------------------------------------------------
+DATA01/enmo/datafile/hsql.264.1063746219
+DATA01/enmo/datafile/sysaux.260.1063610629
+DATA01/enmo/datafile/system.259.1063610625
+DATA01/enmo/datafile/undotbs1.261.1063610629
+DATA01/enmo/datafile/users.263.1063610631
+DATA01/enmo/onlinelog/group_1.256.1063610625
+DATA01/enmo/onlinelog/group_2.257.1063610625
+DATA01/enmo/onlinelog/group_3.258.1063610625
+DATA01/enmo/tempfile/tempts1.262.1063610631
+DATA01/enmo/tempfile/tempts1.265.1063746423
+DATA01/enmo/tempfile/tempts1.266.1063746423
+DATA01/enmo/tempfile/tempts1.267.1063746423
+DATA01/enmo/tempfile/tempts1.268.1063746423

13 rows selected.

SQL> select count(1) from hsql.drop_1;

  COUNT(1)
----------
    100000

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

环境共包含数据文件5个，在线日志文件3个，临时数据文件5个。
复制

3. 恢复数据文件

[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
---- ---- ---- ----- -------- -------------------------------------------- -------- -------- -------- --------
load control file 'control.txt' successful
loading default asm disk file ......
disk_no: 1
group_no: 1
disk_path: /dev/oracleasm/disks/arch5
disk_size_bytes: 5368709120
load control file 'asmdisk.txt' successful
XDUL>scan asmdisk
XDUL>extract datafile 1
XDUL>extract datafile 2
XDUL>extract datafile 3
XDUL>extract datafile 4
XDUL>extract datafile 5
复制

4. dbv工具检查恢复数据文件

[oracle@snyxdb1 data]$ ls
1.dbf  2.dbf  3.dbf  4.dbf  5.dbf
[oracle@snyxdb1 data]$ ls -ltr
total 1484852
-rw-r--r-- 1 root root 340795392 Feb  6 01:43 1.dbf
-rw-r--r-- 1 root root 340795392 Feb  6 01:43 2.dbf
-rw-r--r-- 1 root root 209723392 Feb  6 01:43 3.dbf
-rw-r--r-- 1 root root 524296192 Feb  6 01:43 4.dbf
-rw-r--r-- 1 root root 104865792 Feb  6 01:44 5.dbf
[oracle@snyxdb1 data]$ dbv file=1.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Sat Feb 6 01:44:41 2021

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

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


DBVERIFY - Verification complete

Total Pages Examined         : 41600
Total Pages Processed (Data) : 14637
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 4699
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 3062
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 19202
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 218127 (0.218127)
[oracle@snyxdb1 data]$ dbv file=2.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Sat Feb 6 01:44:44 2021

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

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


DBVERIFY - Verification complete

Total Pages Examined         : 41600
Total Pages Processed (Data) : 1386
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1445
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 3752
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 35017
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 217895 (0.217895)
[oracle@snyxdb1 data]$ dbv file=3.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Sat Feb 6 01:44:47 2021

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

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


DBVERIFY - Verification complete

Total Pages Examined         : 25600
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 23702
Total Pages Processed (Seg)  : 10
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1898
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 218125 (0.218125)
[oracle@snyxdb1 data]$ dbv file=4.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Sat Feb 6 01:44:50 2021

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

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


DBVERIFY - Verification complete

Total Pages Examined         : 64000
Total Pages Processed (Data) : 0
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            : 63873
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3292 (0.3292)
[oracle@snyxdb1 data]$ dbv file=5.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Sat Feb 6 01:44:53 2021

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

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


DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 386
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 143
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 12271
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 214473 (0.214473)
[oracle@snyxdb1 data]$
复制

5. 数据库启动

SQL> startup mount pfile='/home/oracle/bak/28.ora';
ORACLE instance started.

Total System Global Area 1185853440 bytes
Fixed Size		    2252664 bytes
Variable Size		  385876104 bytes
Database Buffers	  771751936 bytes
Redo Buffers		   25972736 bytes
Database mounted.
SQL>
SQL> alter database rename file '+DATA01/enmo/datafile/system.259.1063610625' to '/home/oracle/bak/1.dbf';

Database altered.

SQL> alter database rename file '+DATA01/enmo/datafile/sysaux.260.1063610629' to '/home/oracle/bak/2.dbf';

Database altered.

SQL> alter database rename file '+DATA01/enmo/datafile/undotbs1.261.1063610629' to '/home/oracle/bak/3.dbf';

Database altered.

SQL> alter database rename file '+DATA01/enmo/datafile/users.263.1063610631' to '/home/oracle/bak/4.dbf';
alter database rename file '+DATA01/enmo/datafile/hsql.264.1063746219' to '/home/oracle/bak/5.dbf';
alter database rename file '+DATA01/enmo/onlinelog/group_1.256.1063610625' to '/home/oracle/bak/group_1.256.1063610625';
alter database rename file '+DATA01/enmo/onlinelog/group_2.257.1063610625' to '/home/oracle/bak/group_2.257.1063610625';
alter database rename file '+DATA01/enmo/onlinelog/group_3.258.1063610625' to '/home/oracle/bak/group_3.258.1063610625';
alter database rename file '+DATA01/enmo/tempfile/tempts1.262.1063610631' to '/home/oracle/bak/tempts1.262.1063610631';
alter database rename file '+DATA01/enmo/tempfile/tempts1.265.1063746423' to '/home/oracle/bak/tempts1.265.1063746423';
alter database rename file '+DATA01/enmo/tempfile/tempts1.266.1063746423' to '/home/oracle/bak/tempts1.266.1063746423';
alter database rename file '+DATA01/enmo/tempfile/tempts1.267.1063746423' to '/home/oracle/bak/tempts1.267.1063746423';
alter database rename file '+DATA01/enmo/tempfile/tempts1.268.1063746423' to '/home/oracle/bak/tempts1.268.1063746423';
Database altered.

SQL>
Database altered.

SQL> 
Database altered.

SQL> set linesize 300 pagesize 200
col ERROR for a10
col NAME for a40
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col con_id for 999
col STATUS for a10
select FILE#,STATUS,TS#,RFILE#,RESETLOGS_CHANGE#,RESETLOGS_TIME,
CHECKPOINT_CHANGE#,CHECKPOINTSQL> SQL> SQL> 
Session altered.

SQL> SQL> SQL>   2  _TIME,CHECKPOINT_COUNT,status,ERROR,RECOVER,FUZZY from v$datafile_header order by file#;

     FILE# STATUS	     TS#     RFILE# RESETLOGS_CHANGE# RESETLOGS_TIME	  CHECKPOINT_CHANGE# CHECKPOINT_TIME	 CHECKPOINT_COUNT STATUS     ERROR	REC FUZ
---------- ---------- ---------- ---------- ----------------- ------------------- ------------------ ------------------- ---------------- ---------- ---------- --- ---
	 1 ONLINE	       0	  1		    1 2021-02-04 07:23:40	      218131 2021-02-06 01:34:33	       17 ONLINE		NO  NO
	 2 ONLINE	       1	  2		    1 2021-02-04 07:23:40	      218131 2021-02-06 01:34:33	       17 ONLINE		NO  NO
	 3 ONLINE	       2	  3		    1 2021-02-04 07:23:40	      218131 2021-02-06 01:34:33	       17 ONLINE		NO  NO
	 4 ONLINE	       4	  4		    1 2021-02-04 07:23:40	      218131 2021-02-06 01:34:33	       17 ONLINE		NO  NO
	 5 ONLINE	       5	  5		    1 2021-02-04 07:23:40	      218131 2021-02-06 01:34:33	        5 ONLINE		NO  NO

SQL>
SQL> alter database open resetlogs;

Database altered.

SQL>
复制

6.业务数据测试验证

SQL> select count(1) from hsql.drop_1;

  COUNT(1)
----------
    100000

SQL> select count(1) from obj$;

  COUNT(1)
----------
     13520

SQL> select count(1) from tab$;

  COUNT(1)
----------
      1174

SQL> select count(1) from seg$;

  COUNT(1)
----------
      2727

SQL> select count(1) from col$;

  COUNT(1)
----------
     60916

SQL>
复制

7. 说明

	1 、以上内容为个人多次测试结果，由于个人原因，如有分析不足之处还请见谅及指正。
	2 、文章涉及内容，请勿 在 生产环境模拟。

    积土成山，风雨兴焉； 积水成渊，蛟龙生焉。 ——荀子《劝学》
复制