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

今天药忘吃喽~ 2022-10-14 10:53 108阅读 0赞

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个。

1.  恢复数据文件

[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

1.  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]$

1.  数据库启动

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>

1.  说明  
    1 、以上内容为个人多次测试结果，由于个人原因，如有分析不足之处还请见谅及指正。  
    2 、文章涉及内容，请勿 在 生产环境模拟。
    
    积土成山，风雨兴焉；积水成渊，蛟龙生焉。——荀子《劝学》

完整实例：[http://github.crmeb.net/u/defu][http_github.crmeb.net_u_defu]

来自 “开源世界 ” ，链接：[http://ym.baisou.ltd/post/592.html][http_ym.baisou.ltd_post_592.html]，如需转载，请注明出处，否则将追究法律责任。

[http_github.crmeb.net_u_defu]: http://github.crmeb.net/u/defu
[http_ym.baisou.ltd_post_592.html]: http://ym.baisou.ltd/post/592.html