暂无图片
暂无图片
4
暂无图片
暂无图片
暂无图片

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

2212

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 、文章涉及内容,请勿 在 生产环境模拟。 积土成山,风雨兴焉; 积水成渊,蛟龙生焉。 ——荀子《劝学》
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
1人已赞赏
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论