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

2021-03-16 00:00:00 数据 专区 订阅 文件 恢复

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


相关文章