介绍
本文档主要演示使用集群amdu工具抽取ASM整个数据库文件，还原至单机的过程。
Oracle 集群下提供了三个命令kfed、kfod、amdu，这些本身属于独立的工具，独立于ASM实例，无论ASM实例是否启动，ASM磁盘组是否MOUNT，它都可以正常使用。
- kfed - Kernel Files metadata EDitor
功能：可以用来修复ASM损坏的元数据 - kfod - Kernel Files OSM Disk
功能：可以查询ASM磁盘信息 - amdu - ASM Metadata Dump Utility
功能：可以直接从磁盘抽取数据库文件
各个命令位置如下
注：在11G之前的版本，kfed工具默认没有编译，需要手动编译使用
[grid@19db2:/home/grid/amdu_2021_11_09_11_16_45]$ cd
[grid@19db2:/home/grid]$ which kfed
/u01/app/19.0.0/grid/bin/kfed
[grid@19db2:/home/grid]$ which kfod
/u01/app/19.0.0/grid/bin/kfod
[grid@19db2:/home/grid]$ which amdu
/u01/app/19.0.0/grid/bin/amdu
实践过程
生成抽取语句
在ASM实例中，查询RACDB实例的所有数据文件，拼接为抽取命令。
注1：如果ASM实例已经打不开，需要使用kfed分析磁盘元数据找到对应数据文件别名，难度上升N个等级，下面先使用偷懒的方式验证amdu的使用。
注2：RACDB是在19C的集群里面安装一套11G的软件搭建的11G的库，下面恢复使用11G软件恢复。
[grid@19db2:/home/grid/racdb]$ sqlplus / as sysasm
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 30 13:25:00 2024
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
SQL> set linesize 200
SQL> set pagesize 999
SQL> select 'amdu -dis ''/dev/sd*'' -extract '||gname||'.'||FILE_NUMBER||' -output /home/grid/RACDB/'||aname "amdu extract"
2 from (select concat('+' || gname, sys_connect_by_path(aname, '/')) full_path,
3 dir,
4 sys,
5 aname,
6 FILE_NUMBER,
7 gname
8 from (select g.name gname,
9 a.PARENT_INDEX pindex,
10 a.NAME aname,
11 a.REFERENCE_INDEX rindex,
12 a.ALIAS_DIRECTORY dir,
13 a.SYSTEM_CREATED sys,
14 a.FILE_NUMBER FILE_NUMBER
15 from v$asm_alias a, v$asm_diskgroup g
16 where a.GROUP_NUMBER = g.GROUP_NUMBER)
17 start with (mod(pindex, power(2, 24))) = 0
18 connect by prior rindex = pindex
19 order by dir desc, full_path asc)
20 where full_path like upper('%RACDB%') and dir = 'N';
amdu extract
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
amdu -dis '/dev/sd*' -extract DATA.406 -output /home/grid/RACDB/Current.406.1108894561
amdu -dis '/dev/sd*' -extract DATA.438 -output /home/grid/RACDB/Current.438.1108894563
amdu -dis '/dev/sd*' -extract DATA.440 -output /home/grid/RACDB/SYSAUX.440.1108894519
amdu -dis '/dev/sd*' -extract DATA.401 -output /home/grid/RACDB/SYSTEM.401.1108894519
amdu -dis '/dev/sd*' -extract DATA.409 -output /home/grid/RACDB/UNDOTBS1.409.1108894519
amdu -dis '/dev/sd*' -extract DATA.371 -output /home/grid/RACDB/UNDOTBS2.371.1108894657
amdu -dis '/dev/sd*' -extract DATA.347 -output /home/grid/RACDB/USERS.347.1108894519
amdu -dis '/dev/sd*' -extract DATA.408 -output /home/grid/RACDB/V7PRODATA.408.1183728179
.......
amdu -dis '/dev/sd*' -extract DATA.387 -output /home/grid/RACDB/spfileracdb.ora
amdu -dis '/dev/sd*' -extract DATA.440 -output /home/grid/RACDB/sysaux01.dbf
amdu -dis '/dev/sd*' -extract DATA.401 -output /home/grid/RACDB/system01.dbf
amdu -dis '/dev/sd*' -extract DATA.370 -output /home/grid/RACDB/temp01.dbf
amdu -dis '/dev/sd*' -extract DATA.409 -output /home/grid/RACDB/undotbs01.dbf
amdu -dis '/dev/sd*' -extract DATA.371 -output /home/grid/RACDB/undotbs02.dbf
amdu -dis '/dev/sd*' -extract DATA.347 -output /home/grid/RACDB/users01.dbf
27 rows selected.
抽取文件
使用 amdu 命令可以快速将ASM磁盘数据文件直接导出至本地。
注：此处的数据库抽取为双份
[grid@19db2:/home/grid/RACDB]$ amdu -dis '/dev/sd*' -extract DATA.406 -output /home/grid/RACDB/Current.406.1108894561
[grid@19db2:/home/grid/RACDB]$ amdu -dis '/dev/sd*' -extract DATA.438 -output /home/grid/RACDB/Current.438.1108894563
[grid@19db2:/home/grid/RACDB]$ amdu -dis '/dev/sd*' -extract DATA.440 -output /home/grid/RACDB/SYSAUX.440.1108894519
.....
[grid@19db2:/home/grid/RACDB]$ amdu -dis '/dev/sd*' -extract DATA.440 -output /home/grid/RACDB/sysaux01.dbf
[grid@19db2:/home/grid/RACDB]$ amdu -dis '/dev/sd*' -extract DATA.401 -output /home/grid/RACDB/system01.dbf
[grid@19db2:/home/grid/RACDB]$ amdu -dis '/dev/sd*' -extract DATA.370 -output /home/grid/RACDB/temp01.dbf
[grid@19db2:/home/grid/RACDB]$ amdu -dis '/dev/sd*' -extract DATA.409 -output /home/grid/RACDB/undotbs01.dbf
[grid@19db2:/home/grid/RACDB]$ amdu -dis '/dev/sd*' -extract DATA.371 -output /home/grid/RACDB/undotbs02.dbf
[grid@19db2:/home/grid/RACDB]$ amdu -dis '/dev/sd*' -extract DATA.347 -output /home/grid/RACDB/users01.dbf
导出后文件
[grid@19db2:/home/grid/RACDB]$ ll
total 13790120
-rw-r--r-- 1 grid oinstall 18497536 Oct 30 13:34 control01.ctl
-rw-r--r-- 1 grid oinstall 18497536 Oct 30 13:34 control02.ctl
-rw-r--r-- 1 grid oinstall 18497536 Oct 30 13:32 Current.406.1108894561
-rw-r--r-- 1 grid oinstall 18497536 Oct 30 13:32 Current.438.1108894563
-rw-r--r-- 1 grid oinstall 52429312 Oct 30 13:33 group_1.413.1108894585
-rw-r--r-- 1 grid oinstall 52429312 Oct 30 13:33 group_2.404.1108894587
-rw-r--r-- 1 grid oinstall 52429312 Oct 30 13:33 group_3.433.1108894699
-rw-r--r-- 1 grid oinstall 52429312 Oct 30 13:33 group_4.390.1108894699
......
-rw-r--r-- 1 grid oinstall 413212672 Oct 30 13:35 undotbs02.dbf
-rw-r--r-- 1 grid oinstall 125837312 Oct 30 13:32 UNDOTBS1.409.1108894519
-rw-r--r-- 1 grid oinstall 413212672 Oct 30 13:33 UNDOTBS2.371.1108894657
-rw-r--r-- 1 grid oinstall 5251072 Oct 30 13:35 users01.dbf
-rw-r--r-- 1 grid oinstall 5251072 Oct 30 13:33 USERS.347.1108894519
-rw-r--r-- 1 grid oinstall 104865792 Oct 30 13:33 V7PRODATA.408.1183728179
传输文件之备机
将数据文件传输至备机。
[grid@19db2:/home/grid/RACDB]$ scp * root@192.168.20.43:/racdb/datafile
## 切换至备机
[root@zsdb racdb]# cd /racdb/datafile/
[oracle@zsdb datafile]$ ll
总用量 13791116
-rw-r--r-- 1 oracle oinstall 18497536 10月 30 14:12 control01.ctl
-rw-r--r-- 1 oracle oinstall 18497536 10月 30 14:12 control02.ctl
-rw-r--r-- 1 oracle oinstall 18497536 10月 30 13:43 Current.406.1108894561
-rw-r--r-- 1 oracle oinstall 18497536 10月 30 13:43 Current.438.1108894563
-rw-r--r-- 1 oracle oinstall 52429312 10月 30 13:43 group_1.413.1108894585
-rw-r--r-- 1 oracle oinstall 52429312 10月 30 13:43 group_2.404.1108894587
......
-rw-r--r-- 1 oracle oinstall 460333056 10月 30 13:45 TEMP.370.1108894591
-rw-r--r-- 1 oracle oinstall 125837312 10月 30 14:07 undotbs01.dbf
-rw-r--r-- 1 oracle oinstall 413212672 10月 30 14:07 undotbs02.dbf
-rw-r--r-- 1 oracle oinstall 125837312 10月 30 13:45 UNDOTBS1.409.1108894519
-rw-r--r-- 1 oracle oinstall 413212672 10月 30 13:45 UNDOTBS2.371.1108894657
-rw-r--r-- 1 oracle oinstall 5251072 10月 30 14:07 users01.dbf
-rw-r--r-- 1 oracle oinstall 5251072 10月 30 13:45 USERS.347.1108894519
-rw-r--r-- 1 oracle oinstall 104865792 10月 30 14:07 V7PRODATA.408.1183728179
.....
[root@zsdb datafile]# cd /
[root@zsdb /]# chown -R oracle.oinstall racdb
备机调整spfile
根据集群的spfile，调整部分参数，去掉集群相关参数。
[oracle@zsdb datafile]$ export ORACLE_SID=racdb
[oracle@zsdb datafile]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 30 13:55:59 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create pfile='/racdb/datafile/pracdb.ora' from spfile='/racdb/datafile/spfileracdb.ora';
File created.
[oracle@zsdb datafile]$ vi pracdb.ora
*.audit_file_dest='/racdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/racdb/datafile/control01.ctl','/racdb/datafile/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='racdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
*.open_cursors=300
*.pga_aggregate_target=1362100224
*.processes=800
*.remote_login_passwordfile='exclusive'
*.sessions=885
*.sga_target=4087349248
*.undo_tablespace='UNDOTBS1'
启动数据库到mount
利用修改的pfile启动数据库至mount状态，后续调整文件路径。
[oracle@zsdb datafile]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 30 14:00:14 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount pfile='/racdb/datafile/pracdb.ora'
ORACLE instance started.
Total System Global Area 4075356160 bytes
Fixed Size 2259600 bytes
Variable Size 1056966000 bytes
Database Buffers 3003121664 bytes
Redo Buffers 13008896 bytes
Database mounted.
调整相关文件路径
此处的路径拼接脚本在RAC恢复至单机时，经常使用。
## 获取调整文件路径命令
SQL> select 'alter database rename file ''' || name ||
2 ''' to ''/racdb/datafile/' ||
3 substr(name, instr(name, '/', -1) + 1) || ''';' datafilepath
4 from v$datafile
5 union
6 select 'alter database rename file '''||name||''' to ''/racdb/datafile/'||
7 substr(name, instr(name, '/', -1) + 1) || ''';' tempfilepath from v$tempfile
8 union
9 select 'alter database rename file '''||member||''' to ''/racdb/datafile/'||
10 substr(MEMBER, instr(MEMBER, '/', -1) + 1) || ''';' redopath from v$logfile;
DATAFILEPATH
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter database rename file '+DATA/racdb/datafile/v7prodata.408.1183728179' to '/racdb/datafile/V7PRODATA.408.1183728179';
alter database rename file '+DATA/racdb/sysaux01.dbf' to '/racdb/datafile/sysaux01.dbf';
alter database rename file '+DATA/racdb/system01.dbf' to '/racdb/datafile/system01.dbf';
alter database rename file '+DATA/racdb/undotbs01.dbf' to '/racdb/datafile/undotbs01.dbf';
alter database rename file '+DATA/racdb/undotbs02.dbf' to '/racdb/datafile/undotbs02.dbf';
alter database rename file '+DATA/racdb/users01.dbf' to '/racdb/datafile/users01.dbf';
alter database rename file '+DATA/racdb/redo01.log' to '/racdb/datafile/redo01.log';
alter database rename file '+DATA/racdb/redo02.log' to '/racdb/datafile/redo02.log';
alter database rename file '+DATA/racdb/redo03.log' to '/racdb/datafile/redo03.log';
alter database rename file '+DATA/racdb/redo04.log' to '/racdb/datafile/redo04.log';
alter database rename file '+DATA/racdb/temp01.dbf' to '/racdb/datafile/TEMP01.DBF';
11 rows selected.
## 执行调整文件路径操作
SQL> alter database rename file '+DATA/racdb/sysaux01.dbf' to '/racdb/datafile/sysaux01.dbf';
Database altered.
SQL> alter database rename file '+DATA/racdb/system01.dbf' to '/racdb/datafile/system01.dbf';
Database altered.
.......
SQL> alter database rename file '+DATA/racdb/undotbs02.dbf' to '/racdb/datafile/undotbs02.dbf';
Database altered.
SQL> alter database rename file '+DATA/racdb/users01.dbf' to '/racdb/datafile/users01.dbf';
Database altered.
启动数据库
[oracle@zsdb datafile]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 30 14:07:04 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database open;
Database altered.
SQL> col TABLESPACE_NAME for a20
SQL> col FILE_NAME for a60
SQL> set linesize 200
SQL> select TABLESPACE_NAME,FILE_ID,STATUS,FILE_NAME,BYTES/1024/1024 from dba_data_files;
TABLESPACE_NAME FILE_ID STATUS FILE_NAME BYTES/1024/1024
-------------------- ---------- --------- ------------------------------------------------------------ ---------------
USERS 4 AVAILABLE /racdb/datafile/users01.dbf 5
UNDOTBS1 3 AVAILABLE /racdb/datafile/undotbs01.dbf 120
SYSAUX 2 AVAILABLE /racdb/datafile/sysaux01.dbf 4710
SYSTEM 1 AVAILABLE /racdb/datafile/system01.dbf 780
UNDOTBS2 5 AVAILABLE /racdb/datafile/undotbs02.dbf 394.0625
V7PRODATA 6 AVAILABLE /racdb/datafile/V7PRODATA.408.1183728179 100
6 rows selected.
总结
通过工具amdu实现从ASM磁盘直接抽取数据文件进行恢复，整体操作步骤不算复杂，关键是需要提前得到相关的抽取信息，上述操作基于ASM实例正常的情况下进行，相对简单，可以作为恢复的一种手段。当然，数据库服务器故障时，最好是利用可靠备份进行恢复，愿广大运维人员永远不会遇到使用kfed、kfod、amod进行恢复的时候。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
