在asm 磁盘组不能mount的情况下,如果是磁盘头的少数部分损坏,或者是asm disk header存在,可以通过kfed修复,或者使用备份的磁盘头信息去恢复从而实现磁盘组mount来恢复数据库.如果没有备份也无法修复可以尝试使用amdu,dul来实现对不能mount的磁盘组进行恢复.
在asm 磁盘组不能mount的情况下,如果是磁盘头的少数部分损坏,或者是asm disk header存在,可以通过kfed修复,或者使用备份的磁盘头信息去恢复从而实现磁盘组mount来恢复数据库.如果没有备份也无法修复可以尝试使用amdu,dul来实现对不能mount的磁盘组进行恢复.在极端情况下(比如磁盘组完全丢失),amdu/dul都无论为力的情况下,可以考虑使用扫描磁盘找出来datafile 的方法求救数据的最后稻草.本实验大概的模拟了asm disk 前10M完全损坏的情况下数据库恢复
测试准备
创建新表空间,创建T_XIFENFEI测试表
SQL> create tablespace xifenfei datafile '+XIFENFEI' SIZE 50m; Tablespace created. SQL> CREATE TABLE T_XIFENFEI TABLESPACE XIFENFEI 2 AS SELECT * FROM DBA_OBJECTS; Table created. SQL> SELECT COUNT(*) FROM T_XIFENFEI; COUNT(*) ---------- 50031 SQL> select ts#,rfile#,bytes/1024/1024,blocks,name from v$datafile; TS# RFILE# BYTES/1024/1024 BLOCKS NAME ---------- ---------- --------------- ---------- -------------------------------------------------- 0 1 480 61440 +XIFENFEI/asm10g/datafile/system.256.845260203 1 2 25 3200 +XIFENFEI/asm10g/datafile/undotbs1.258.845260205 2 3 250 32000 +XIFENFEI/asm10g/datafile/sysaux.257.845260203 4 4 5 640 +XIFENFEI/asm10g/datafile/users.259.845260205 6 5 50 6400 +XIFENFEI/asm10g/datafile/xifenfei.266.845262139 SQL> select GROUP_NUMBER,DISK_NUMBER,STATE,TOTAL_MB,FREE_MB,NAME,path from v$asm_disk; GROUP_NUMBER DISK_NUMBER STATE TOTAL_MB FREE_MB NAME PATH ------------ ----------- -------- ---------- ---------- -------------------- ------------------ 1 0 NORMAL 2048 0 XIFENFEI_0000 /dev/raw/raw1 1 1 NORMAL 784 0 XIFENFEI_0001 /dev/raw/raw2 1 2 NORMAL 7059 0 XIFENFEI_0002 /dev/raw/raw3 --关闭数据库 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. --关闭ASM SQL> shutdown immediate ASM diskgroups dismounted ASM instance shutdown
查看裸设备对应磁盘
[oracle@xifenfei dul]$ more /etc/sysconfig/rawdevices /dev/raw/raw1 /dev/sdc /dev/raw/raw2 /dev/sdd1 /dev/raw/raw3 /dev/sdd2
dd磁盘头
dd asm disk 前面10M,彻底破坏asm disk
[oracle@xifenfei ~]$ dd if=/dev/zero of=/dev/raw/raw1 bs=1M count=10 conv=notrunc 10+0 records in 10+0 records out 10485760 bytes (10 MB) copied, 0.175424 seconds, 59.8 MB/s [oracle@xifenfei ~]$ dd if=/dev/zero of=/dev/raw/raw2 bs=1M count=10 conv=notrunc 10+0 records in 10+0 records out 10485760 bytes (10 MB) copied, 0.11584 seconds, 90.5 MB/s [oracle@xifenfei ~]$ dd if=/dev/zero of=/dev/raw/raw3 bs=1M count=10 conv=notrunc 10+0 records in 10+0 records out 10485760 bytes (10 MB) copied, 0.353435 seconds, 29.7 MB/s
kfed查看磁盘
确定所有asm disk header完全被破坏
[oracle@xifenfei dul]$ kfed read /dev/raw/raw1 kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 0 ; 0x001: 0x00 kfbh.type: 0 ; 0x002: KFBTYP_INVALID kfbh.datfmt: 0 ; 0x003: 0x00 kfbh.block.blk: 0 ; 0x004: T=0 NUMB=0x0 kfbh.block.obj: 0 ; 0x008: TYPE=0x0 NUMB=0x0 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 [oracle@xifenfei dul]$ kfed read /dev/raw/raw2 kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 0 ; 0x001: 0x00 kfbh.type: 0 ; 0x002: KFBTYP_INVALID kfbh.datfmt: 0 ; 0x003: 0x00 kfbh.block.blk: 0 ; 0x004: T=0 NUMB=0x0 kfbh.block.obj: 0 ; 0x008: TYPE=0x0 NUMB=0x0 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 [oracle@xifenfei dul]$ kfed read /dev/raw/raw3 kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 0 ; 0x001: 0x00 kfbh.type: 0 ; 0x002: KFBTYP_INVALID kfbh.datfmt: 0 ; 0x003: 0x00 kfbh.block.blk: 0 ; 0x004: T=0 NUMB=0x0 kfbh.block.obj: 0 ; 0x008: TYPE=0x0 NUMB=0x0 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000
amdu查看asm 磁盘
[oracle@xifenfei ~]$ amdu -diskstring '/dev/raw/raw*' amdu_2014_04_18_23_17_17/ [oracle@xifenfei ~]$ cd amdu_2014_04_18_23_17_17 [oracle@xifenfei amdu_2014_04_18_23_17_17]$ ls report.txt [oracle@xifenfei amdu_2014_04_18_23_17_17]$ more report.txt -*-amdu-*- ………… --------------------------------- Operations --------------------------------- ------------------------------- Disk Selection ------------------------------- -diskstring '/dev/raw/raw*' ------------------------------ Reading Control ------------------------------- ------------------------------- Output Control ------------------------------- ********************************* DISCOVERY ********************************** ----------------------------- DISK REPORT N0001 ------------------------------ Disk Path: /dev/raw/raw1 Unique Disk ID: Disk Label: Physical Sector Size: 512 bytes Disk Size: 65536 megabytes ** NOT A VALID ASM DISK HEADER. BAD VALUE IN FIELD blksize_kfdhdb ** ----------------------------- DISK REPORT N0002 ------------------------------ Disk Path: /dev/raw/raw2 Unique Disk ID: Disk Label: Physical Sector Size: 512 bytes Disk Size: 65536 megabytes ** NOT A VALID ASM DISK HEADER. BAD VALUE IN FIELD blksize_kfdhdb ** ----------------------------- DISK REPORT N0003 ------------------------------ Disk Path: /dev/raw/raw3 Unique Disk ID: Disk Label: Physical Sector Size: 512 bytes Disk Size: 65536 megabytes ** NOT A VALID ASM DISK HEADER. BAD VALUE IN FIELD blksize_kfdhdb ** ******************************* END OF REPORT ********************************
通过这里证明,当asm disk header 损坏严重之时,amdu无法识别,更加无法恢复相关数据库
dul查看完全损坏asm disk header
测试在asm disk header完全损坏情况下,dul是否还能够实现asm磁盘组中抽取数据,同理amdu也无法正常工作.
[oracle@xifenfei dul]$ ./dul Data UnLoader: 10.2.0.5.28 - Internal Only - on Sat Apr 19 04:02:02 2014 with 64-bit io functions Copyright (c) 1994 2014 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only DUL: Warning: block 0 is not a disk header block DUL: Error: Block is not in use DUL: Error: Block type mismatch ( seen 0 expect 1) when parsing block 0 of disk /dev/raw/raw1 DUL: Warning: block 0 is not a disk header block DUL: Error: Block is not in use DUL: Error: Block type mismatch ( seen 0 expect 1) when parsing block 0 of disk /dev/raw/raw2 DUL: Warning: block 0 is not a disk header block DUL: Error: Block is not in use DUL: Error: Block type mismatch ( seen 0 expect 1) when parsing block 0 of disk /dev/raw/raw3
这里可以看出来,当asm disk header完全异常,dul也无法识别出来asm磁盘组(该情况下dul无法正常操作)
通过工具扫描磁盘抽取数据块
CPFL> scan disk /dev/raw/raw1 Scanning disk /dev/raw/raw1, at 2014-04-19 04:05:11 Completed disk /dev/raw/raw1, at 2014-04-19 04:05:56 CPFL> scan disk /dev/raw/raw1 Scanning disk /dev/raw/raw1, at 2014-04-19 04:05:56 Completed disk /dev/raw/raw1, at 2014-04-19 04:06:15 CPFL> scan disk /dev/raw/raw1 Scanning disk /dev/raw/raw1, at 2014-04-19 04:06:15 Completed disk /dev/raw/raw1, at 2014-04-19 04:07:44 CPFL> list datafiles Tablespace: SYSTEM File: 1 Blocks: 61440 Tablespace: UNDOTBS1 File: 2 Blocks: 3200 Tablespace: SYSAUX File: 3 Blocks: 32000 Tablespace: USERS File: 4 Blocks: 640 Tablespace: XIFENFEI File: 5 Blocks: 6400 CPFL> copy datafile 1 to /u01/oracle/oradata/datafile/1.dbf copy datafile start: 2014-04-19 04:10:35 copy datafile 1 have blocks 61440 copy datafile completed: 2014-04-19 04:11:18 CPFL> copy datafile 2 to /u01/oracle/oradata/datafile/2.dbf copy datafile start: 2014-04-19 04:11:52 copy datafile 2 have blocks 3200 copy datafile completed: 2014-04-19 04:11:54 CPFL> copy datafile 3 to /u01/oracle/oradata/datafile/3.dbf copy datafile start: 2014-04-19 04:12:03 copy datafile 3 have blocks 32000 copy datafile completed: 2014-04-19 04:12:27 CPFL> copy datafile 4 to /u01/oracle/oradata/datafile/4.dbf copy datafile start: 2014-04-19 04:13:07 copy datafile 4 have blocks 640 copy datafile completed: 2014-04-19 04:13:08 CPFL> copy datafile 5 to /u01/oracle/oradata/datafile/5.dbf copy datafile start: 2014-04-19 04:13:18 copy datafile 5 have blocks 6400 copy datafile completed: 2014-04-19 04:13:19
查看使用工具抽取数据文件
[oracle@xifenfei datafile]$ ls -l total 830320 -rw-r--r-- 1 oracle oinstall 503324672 Apr 19 04:34 1.dbf -rw-r--r-- 1 oracle oinstall 26222592 Apr 19 04:34 2.dbf -rw-r--r-- 1 oracle oinstall 262152192 Apr 19 04:34 3.dbf -rw-r--r-- 1 oracle oinstall 5251072 Apr 19 04:34 4.dbf -rw-r--r-- 1 oracle oinstall 52436992 Apr 19 04:34 5.dbf
dul验证抽取文件
[oracle@xifenfei dul]$ ./dul Data UnLoader: 10.2.0.5.28 - Internal Only - on Sat Apr 19 06:56:09 2014 with 64-bit io functions Copyright (c) 1994 2014 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only DUL: Warning: Recreating file "dul.log" Found db_id = 181793355 Found db_name = ASM10G DUL> show datafiles; ts# rf# start blocks offs open err file name 0 1 0 61440 0 1 0 /u01/oracle/oradata/datafile/1.dbf 1 2 0 3200 0 1 0 /u01/oracle/oradata/datafile/2.dbf 2 3 0 32000 0 1 0 /u01/oracle/oradata/datafile/3.dbf 4 4 0 640 0 1 0 /u01/oracle/oradata/datafile/4.dbf 6 5 0 6400 0 1 0 /u01/oracle/oradata/datafile/5.dbf DUL> bootstrap; Probing file = 1, block = 377 . unloading table BOOTSTRAP$ DUL: Warning: block number is non zero but marked deferred trying to process it anyhow 57 rows unloaded DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty Reading BOOTSTRAP.dat 57 entries loaded Parsing Bootstrap$ contents DUL: Warning: Recreating file "dict.ddl" Generating dict.ddl for version 10 OBJ$: segobjno 18, file 1 block 121 TAB$: segobjno 2, tabno 1, file 1 block 25 COL$: segobjno 2, tabno 5, file 1 block 25 USER$: segobjno 10, tabno 1, file 1 block 89 Running generated file "@dict.ddl" to unload the dictionary tables . unloading table OBJ$ 51171 rows unloaded . unloading table TAB$ 1576 rows unloaded . unloading table COL$ 55264 rows unloaded . unloading table USER$ 59 rows unloaded Reading USER.dat 59 entries loaded Reading OBJ.dat 51171 entries loaded and sorted 51171 entries Reading TAB.dat 1576 entries loaded Reading COL.dat 55264 entries loaded and sorted 55264 entries Reading BOOTSTRAP.dat 57 entries loaded DUL: Warning: Recreating file "dict.ddl" Generating dict.ddl for version 10 OBJ$: segobjno 18, file 1 block 121 TAB$: segobjno 2, tabno 1, file 1 block 25 COL$: segobjno 2, tabno 5, file 1 block 25 USER$: segobjno 10, tabno 1, file 1 block 89 TABPART$: segobjno 266, file 1 block 2121 INDPART$: segobjno 271, file 1 block 2161 TABCOMPART$: segobjno 288, file 1 block 2297 INDCOMPART$: segobjno 293, file 1 block 2345 TABSUBPART$: segobjno 278, file 1 block 2217 INDSUBPART$: segobjno 283, file 1 block 2257 IND$: segobjno 2, tabno 3, file 1 block 25 ICOL$: segobjno 2, tabno 4, file 1 block 25 LOB$: segobjno 2, tabno 6, file 1 block 25 COLTYPE$: segobjno 2, tabno 7, file 1 block 25 TYPE$: segobjno 181, tabno 1, file 1 block 1297 COLLECTION$: segobjno 181, tabno 2, file 1 block 1297 ATTRIBUTE$: segobjno 181, tabno 3, file 1 block 1297 LOBFRAG$: segobjno 299, file 1 block 2393 LOBCOMPPART$: segobjno 302, file 1 block 2425 UNDO$: segobjno 15, file 1 block 105 TS$: segobjno 6, tabno 2, file 1 block 57 PROPS$: segobjno 96, file 1 block 721 Running generated file "@dict.ddl" to unload the dictionary tables . unloading table OBJ$ DUL: Warning: Recreating file "OBJ.ctl" 51171 rows unloaded . unloading table TAB$ DUL: Warning: Recreating file "TAB.ctl" 1576 rows unloaded . unloading table COL$ DUL: Warning: Recreating file "COL.ctl" 55264 rows unloaded . unloading table USER$ DUL: Warning: Recreating file "USER.ctl" 59 rows unloaded . unloading table TABPART$ 72 rows unloaded . unloading table INDPART$ 80 rows unloaded . unloading table TABCOMPART$ 0 rows unloaded . unloading table INDCOMPART$ 0 rows unloaded . unloading table TABSUBPART$ 0 rows unloaded . unloading table INDSUBPART$ 0 rows unloaded . unloading table IND$ 2231 rows unloaded . unloading table ICOL$ 3650 rows unloaded . unloading table LOB$ 530 rows unloaded . unloading table COLTYPE$ 1701 rows unloaded . unloading table TYPE$ 1945 rows unloaded . unloading table COLLECTION$ 555 rows unloaded . unloading table ATTRIBUTE$ 7275 rows unloaded . unloading table LOBFRAG$ 1 row unloaded . unloading table LOBCOMPPART$ 0 rows unloaded . unloading table UNDO$ 21 rows unloaded . unloading table TS$ 7 rows unloaded . unloading table PROPS$ 28 rows unloaded Reading USER.dat 59 entries loaded Reading OBJ.dat 51171 entries loaded and sorted 51171 entries Reading TAB.dat 1576 entries loaded Reading COL.dat 55264 entries loaded and sorted 55264 entries Reading TABPART.dat 72 entries loaded and sorted 72 entries Reading TABCOMPART.dat 0 entries loaded and sorted 0 entries Reading TABSUBPART.dat 0 entries loaded and sorted 0 entries Reading INDPART.dat 80 entries loaded and sorted 80 entries Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries Reading IND.dat 2231 entries loaded Reading LOB.dat 530 entries loaded Reading ICOL.dat 3650 entries loaded Reading COLTYPE.dat 1701 entries loaded Reading TYPE.dat 1945 entries loaded Reading ATTRIBUTE.dat 7275 entries loaded Reading COLLECTION.dat 555 entries loaded Reading BOOTSTRAP.dat 57 entries loaded Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries Reading UNDO.dat 21 entries loaded Reading TS.dat 7 entries loaded Reading PROPS.dat 28 entries loaded Database character set is ZHS16GBK Database national character set is AL16UTF16 DUL> unload table sys.t_xifenfei; . unloading table T_XIFENFEI 50031 rows unloaded
通过这里可以发现,我们创建测试数据为50031条,dul读取抽取出来数据文件中对应表数据条数也为50031条;证明:在asm disk header完全损坏情况下,amdu,dul无法直接恢复asm里面数据库,但是可以通过工具扫描数据文件,找出来磁盘中的datafile block实现完整恢复数据[只要你的asm中的数据没有覆盖,都可以通过该方法恢复]
如果你在使用这些思路进行恢复遇到突发情况不能自行解决,请联系我们(ORACLE数据库恢复技术支持),将为您提供专业数据库技术支持:
Phone:13429648788 Q Q:107644445 E-Mail:dba@xifenfei.com
- 使用 dul 挖数据文件初试
- dul 10支持oracle 11g r2
- DUL10直接支持ORACLE 8.0
- dul支持ORACLE 12C CDB数据库恢复
- dul恢复truncate表测试
- dul处理分区表
- 使用asm disk header 自动备份信息恢复异常asm disk header
- DUL挖ORACLE 8.0数据库
原文地址:asm disk header 彻底损坏恢复, 感谢原作者分享。

要优化MySQL慢查询,需使用slowquerylog和performance_schema:1.启用slowquerylog并设置阈值,记录慢查询;2.利用performance_schema分析查询执行细节,找出性能瓶颈并优化。

MySQL和SQL是开发者必备技能。1.MySQL是开源的关系型数据库管理系统,SQL是用于管理和操作数据库的标准语言。2.MySQL通过高效的数据存储和检索功能支持多种存储引擎,SQL通过简单语句完成复杂数据操作。3.使用示例包括基本查询和高级查询,如按条件过滤和排序。4.常见错误包括语法错误和性能问题,可通过检查SQL语句和使用EXPLAIN命令优化。5.性能优化技巧包括使用索引、避免全表扫描、优化JOIN操作和提升代码可读性。

MySQL异步主从复制通过binlog实现数据同步,提升读性能和高可用性。1)主服务器记录变更到binlog;2)从服务器通过I/O线程读取binlog;3)从服务器的SQL线程应用binlog同步数据。

MySQL是一个开源的关系型数据库管理系统。1)创建数据库和表:使用CREATEDATABASE和CREATETABLE命令。2)基本操作:INSERT、UPDATE、DELETE和SELECT。3)高级操作:JOIN、子查询和事务处理。4)调试技巧:检查语法、数据类型和权限。5)优化建议:使用索引、避免SELECT*和使用事务。

MySQL的安装和基本操作包括:1.下载并安装MySQL,设置根用户密码;2.使用SQL命令创建数据库和表,如CREATEDATABASE和CREATETABLE;3.执行CRUD操作,使用INSERT,SELECT,UPDATE,DELETE命令;4.创建索引和存储过程以优化性能和实现复杂逻辑。通过这些步骤,你可以从零开始构建和管理MySQL数据库。

InnoDBBufferPool通过将数据和索引页加载到内存中来提升MySQL数据库的性能。1)数据页加载到BufferPool中,减少磁盘I/O。2)脏页被标记并定期刷新到磁盘。3)LRU算法管理数据页淘汰。4)预读机制提前加载可能需要的数据页。

MySQL适合初学者使用,因为它安装简单、功能强大且易于管理数据。1.安装和配置简单,适用于多种操作系统。2.支持基本操作如创建数据库和表、插入、查询、更新和删除数据。3.提供高级功能如JOIN操作和子查询。4.可以通过索引、查询优化和分表分区来提升性能。5.支持备份、恢复和安全措施,确保数据的安全和一致性。

全表扫描在MySQL中可能比使用索引更快,具体情况包括:1)数据量较小时;2)查询返回大量数据时;3)索引列不具备高选择性时;4)复杂查询时。通过分析查询计划、优化索引、避免过度索引和定期维护表,可以在实际应用中做出最优选择。


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

AI Hentai Generator
免费生成ai无尽的。

热门文章

热工具

Atom编辑器mac版下载
最流行的的开源编辑器

适用于 Eclipse 的 SAP NetWeaver 服务器适配器
将Eclipse与SAP NetWeaver应用服务器集成。

禅工作室 13.0.1
功能强大的PHP集成开发环境

SecLists
SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合,这些列表在安全评估过程中经常使用,都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表,帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上,他就可以访问到所需的每种类型的列表。

SublimeText3汉化版
中文版,非常好用