Oracle 从ASM复制文件到文件系统

使用方法：

#创建目录
SQL>createdirectoryasm_diras'+data/ASM/DATAFILE/';

Directorycreated.

SQL>createdirectoryos_diras'/home/oracle';

Directorycreated.
#执行复制
SQL>execdbms_file_transfer.copy_file('asm_dir','user2.dbf.272.944581345','os_dir','user.dbf');

PL/SQLproceduresuccessfullycompleted.

#验证复制结果
[oracle@rac1~]$ls-l/home/oracle/user.dbf
-rw-r-----.1oracleasmadmin5251072May2115:49/home/oracle/user.dbf

#进行反向复制
SQL>execdbms_file_transfer.copy_file('os_dir','user.dbf','asm_dir','user2.dbf');

PL/SQLproceduresuccessfullycompleted.

#验证

ASMCMD[+data/ASM/DATAFILE]>ls-l
TypeRedundStripedTimeSysName
Nuser2.dbf=>+DATA/ORCL/DATAFILE/COPY_FILE.273.944581883

3、rman的convert和backup as copy命令

3.1 convert命令

convert命令是用于对数据文件进行字节格式转换的。Oracle可以在不同OS平台间进行表空间传(TTS)，如果两个平台的字节格式不一致，则需要参数据文件进行格式转换，这个转换可以 在源数据库进行，也可以在目的数据库中执行。如果在源数据库中执行，则需要使用convert tablespace命令，如果在目的数据库中执行，则需要使用convert datafile命令。

虽然convert这个命令主要用于跨平台传输表空间，它也可以完成ASM和本地文件系统间拷贝文件，并且也是很简单的一种方法。

#使用convertdatafile从ASM复制到文件系统
RMAN>convertdatafile'+data/orcl/datafile/USERS.259.925306091'format'/home/oracle/user3.dbf';

Startingconversionattargetat21-MAY-17
usingchannelORA_DISK_1
channelORA_DISK_1:startingdatafileconversion
inputfilename=+DATA/orcl/datafile/users.259.925306091
converteddatafile=/home/oracle/user3.dbf
channelORA_DISK_1:datafileconversioncomplete,elapsedtime:00:00:01
Finishedconversionattargetat21-MAY-17

#验证
[oracle@rac1~]$ls-l/home/oracle/user3.dbf
-rw-r-----.1oracleasmadmin5251072May2116:00/home/oracle/user3.dbf

#使用converttablespace，使用时需要把users表空间置为只读
RMAN>sql'altertablespaceusersreadonly';

usingtargetdatabasecontrolfileinsteadofrecoverycatalog
sqlstatement:altertablespaceusersreadonly

RMAN>converttablespaceusersformat'/home/oracle/users%U.dbf';

Startingconversionatsourceat21-MAY-17
allocatedchannel:ORA_DISK_1
channelORA_DISK_1:SID=81instance=orcl1devicetype=DISK
channelORA_DISK_1:startingdatafileconversion
inputdatafilefilenumber=00004name=+DATA/orcl/datafile/users.259.925306091
converteddatafile=/home/oracle/usersdata_D-ORCL_I-1453086940_TS-USERS_FNO-4_0ns4qbtn.dbf
channelORA_DISK_1:datafileconversioncomplete,elapsedtime:00:00:01
Finishedconversionatsourceat21-MAY-17

#验证
[oracle@rac1~]$ll/home/oracle/usersdata_D-ORCL_I-1453086940_TS-USERS_FNO-4_0ns4qbtn.dbf
-rw-r-----.1oracleasmadmin5251072May2116:03/home/oracle/usersdata_D-ORCL_I-1453086940_TS-USERS_FNO-4_0ns4qbtn.dbf

#从文件系统拷贝到ASM
RMAN>convertdatafile'/home/oracle/user3.dbf','/home/oracle/usersdata_D-ORCL_I-1453086940_TS-USERS_FNO-4_0ns4qbtn.dbf'format'+data';

Startingconversionattargetat21-MAY-17
usingtargetdatabasecontrolfileinsteadofrecoverycatalog
allocatedchannel:ORA_DISK_1
channelORA_DISK_1:SID=64instance=orcl1devicetype=DISK
channelORA_DISK_1:startingdatafileconversion
inputfilename=/home/oracle/user3.dbf
converteddatafile=+DATA/orcl/datafile/users.274.944582713
channelORA_DISK_1:datafileconversioncomplete,elapsedtime:00:00:01
channelORA_DISK_1:startingdatafileconversion
inputfilename=/home/oracle/usersdata_D-ORCL_I-1453086940_TS-USERS_FNO-4_0ns4qbtn.dbf
converteddatafile=+DATA/orcl/datafile/users.275.944582713
channelORA_DISK_1:datafileconversioncomplete,elapsedtime:00:00:01
Finishedconversionattargetat21-MAY-17
#从日志中看出转换的文件名
#converteddatafile=+DATA/orcl/datafile/users.274.944582713
#converteddatafile=+DATA/orcl/datafile/users.274.944582713

3.2 backup as copy命令

backup as copy命令是以镜像的方式对文件进行备份，自然可以把文件从ASM复制到文件系统中。这也是从文件系统迁移到ASM时可以选择的方法之一。

#执行复制
RMAN>backupascopydatafile'+DATA/orcl/datafile/users.259.925306091'format'/home/oracle/user4.dbf';

Startingbackupat21-MAY-17
usingtargetdatabasecontrolfileinsteadofrecoverycatalog
allocatedchannel:ORA_DISK_1
channelORA_DISK_1:SID=69instance=orcl1devicetype=DISK
channelORA_DISK_1:startingdatafilecopy
inputdatafilefilenumber=00004name=+DATA/orcl/datafile/users.259.925306091
outputfilename=/home/oracle/user4.dbftag=TAG20170521T161225RECID=17STAMP=944583145
channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:00:01
Finishedbackupat21-MAY-17

#验证
[oracle@rac1~]$ls-l/home/oracle/user4.dbf
-rw-r-----.1oracleasmadmin5251072May2116:12/home/oracle/user4.dbf

4.FTP方法

FTP方法可以像库中复制使用传统文件传输协议(FTP)对普通文件执行常规操作那样，对ASM文件和目录执行操作。通过这种方式访问ASM文件的典型应用是从一个数据库向另一个数据库中复制ASM文件。需要XML DB支持，配置起来最麻烦，有兴趣的同学可以参考《Oracle Database 11g RAC手册原书第2版》或官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e18951/asmfiles.htm#BABJCCEI

参考《大话Oracle RAC》

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

#从ASM复制到文件系统
[grid@rac1~]$asmcmd-p
ASMCMD[+]>cddata
ASMCMD[+data]>cdorcl
ASMCMD[+data/orcl]>cddatafile
ASMCMD[+data/orcl/datafile]>ls
SYSAUX.257.925306091
SYSTEM.256.925306089
UNDOTBS1.258.925306091
UNDOTBS2.264.925306377
USERS.259.925306091
ASMCMD[+data/orcl/datafile]>ls-l
TypeRedundStripedTimeSysName
DATAFILEUNPROTCOARSEMAY2111:00:00YSYSAUX.257.925306091
DATAFILEUNPROTCOARSEMAY2111:00:00YSYSTEM.256.925306089
DATAFILEUNPROTCOARSEMAY2111:00:00YUNDOTBS1.258.925306091
DATAFILEUNPROTCOARSEMAY2111:00:00YUNDOTBS2.264.925306377
DATAFILEUNPROTCOARSEMAY2111:00:00YUSERS.259.925306091
ASMCMD[+data/orcl/datafile]>cpUSERS.259.925306091/home/grid/users.dbf
copying+data/orcl/datafile/USERS.259.925306091->/home/grid/users.dbf
ASMCMD[+data/orcl/datafile]>
#查看复制结果
[grid@rac1~]$ll/home/grid/users.dbf
-rw-r-----.1gridoinstall5251072May2115:20/home/grid/users.dbf

#从文件系统复制到ASM
ASMCMD[+data/orcl/datafile]>cp/home/grid/users.dbf+data/orcl
copying/home/grid/users.dbf->+data/orcl/users.dbf
#查看复制结果
ASMCMD[+data/orcl/datafile]>cd+data/orcl
ASMCMD[+data/orcl]>ls-l
TypeRedundStripedTimeSysName
YARCHIVELOG/
YCONTROLFILE/
YDATAFILE/
YONLINELOG/
YPARAMETERFILE/
YTEMPFILE/
Nspfileorcl.ora=>+DATA/ORCL/PARAMETERFILE/spfile.268.925423909
Nusers.dbf=>+DATA/ASM/DATAFILE/users.dbf.271.944580085