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) :
Total Pages Processed (Index): 4699
Total Pages Failing (Index):
Total Pages Processed (Other): 3062
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) :
Total Pages Empty : 19202
Total Pages Marked Corrupt :
Total Pages Influx :
Total Pages Encrypted :
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) :
Total Pages Processed (Index): 1445
Total Pages Failing (Index):
Total Pages Processed (Other): 3752
Total Pages Processed (Seg) :
Total Pages Failing (Seg) :
Total Pages Empty : 35017
Total Pages Marked Corrupt :
Total Pages Influx :
Total Pages Encrypted :
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) :
Total Pages Failing (Data) :
Total Pages Processed (Index):
Total Pages Failing (Index):
Total Pages Processed (Other): 23702
Total Pages Processed (Seg) : 10
Total Pages Failing (Seg) :
Total Pages Empty : 1898
Total Pages Marked Corrupt :
Total Pages Influx :
Total Pages Encrypted :
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) :
Total Pages Failing (Data) :
Total Pages Processed (Index):
Total Pages Failing (Index):
Total Pages Processed (Other): 127
Total Pages Processed (Seg) :
Total Pages Failing (Seg) :
Total Pages Empty : 63873
Total Pages Marked Corrupt :
Total Pages Influx :
Total Pages Encrypted :
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) :
Total Pages Processed (Index):
Total Pages Failing (Index):
Total Pages Processed (Other): 143
Total Pages Processed (Seg) :
Total Pages Failing (Seg) :
Total Pages Empty : 12271
Total Pages Marked Corrupt :
Total Pages Influx :
Total Pages Encrypted :
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 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 、文章涉及内容，请勿 在 生产环境模拟。
积土成山，风雨兴焉；积水成渊，蛟龙生焉。——荀子《劝学》
原文链接：https://mp.weixin.qq.com/s/v5h_aFEiV5Pc_qGHZ8qUnA
相关文章