Oracle copies files from ASM to file system

Work, sometimes you need to copy files from ASM to the file system or, in turn, do some maintenance operations, this article describes 4 ways to copy files:

• CP command in Asmcmd (11g)

• Dbms_file_transfer Bag
  

• Convert or Backup as copy of Rman

• Ftp

These 4 methods are described below

1. CP command in Asmcmd (11g)

The CP command is a 11g new command that makes it easy to copy files from ASM to the file system or vice versa. You can also copy the files in ASM to the file system of other servers on the network.

#从ASM复制到文件系统 [[email protected] ~]$ asmcmd -pasmcmd [+] > cd  dataasmcmd [+data] > cd orclasmcmd [+data/orcl] > cd  datafileasmcmd [+data/orcl/datafile] >  Lssysaux.257.925306091system.256.925306089undotbs1.258.925306091undotbs2.264.925306377users.259.925306091asmcmd  [+data/orcl/datafile] > ls -lType      Redund   striped  time             sys   NameDATAFILE  UNPROT  COARSE   MAY 21 11:00:00   Y    SYSAUX.257.925306091DATAFILE  UNPROT  COARSE    MAY 21 11:00:00  Y    SYSTEM.256.925306089DATAFILE   unprot  coarse   may 21 11:00:00  y     undotbs1.258.925306091datafile  unprot  coarse   may 21  11:00:00  Y    UNDOTBS2.264.925306377DATAFILE  UNPROT   COARSE   MAY 21 11:00:00  Y     users.259.925306091asmcmd [+data/orcl/datafile] > cp users.259.925306091 /home/ Grid/users.dbfcopying +data/orcl/datafile/users.259.925306091 -> /home/grid/users.dbfasmcmd  [+data/orcl/datafile] > #查看复制结果 [[email protected] ~]$ ll /home/grid/users.dbf  -rw-r-----.  1 grid oinstall 5251072 may 21 15:20 /home/grid/ users.dbf# copying from file system to asmasmcmd [+data/orcl/datafile] > cp /home/grid/users.dbf +data/ orclcopying /home/grid/users.dbf -> +data/orcl/users.dbf# Viewing replication results asmcmd [+data/orcl/ Datafile] > cd +data/orclasmcmd [+data/orcl] > ls -ltype            Redund  Striped  Time              Sys  Name                                                    Y    ARCHIVELOG/                                                    Y    CONTROLFILE/                                                   Y     DATAFILE/                                                   y     ONLINELOG/                                                    Y    PARAMETERFILE/                                                   Y     TEMPFILE/                                                   N     spfileorcl.ora => +DATA/ORCL/PARAMETERFILE/spfile.268.925423909                                                    N    users.dbf =>  +data/asm/datafile/users.dbf.271.944580085 

Detailed usage of the CP can be obtained using the help CP command.

If the ASM version is a previous version of 11g, you can copy files using the following three methods.

2. Dbms_file_transfer Bag

This package is not available from Oracle 9 and can be used to transfer files between two locations, either on the same computer or two computers on the network. ORACLE10G extends the functionality of this package to copy from one ASM DiskGroup to another ASM DiskGroup, from ASM DiskGroup to ordinary file systems, from ordinary file systems to ASM DiskGroup, Copy from file system to file system or raw Device. This package provides 3 ways to accomplish different tasks.

Method	Function description
Copy_file	The completion of the file from a local directory copy to another local directory, this method can not complete the remote transfer
Get_file	This method is done to copy the remote files to the local directory, similar to the download
Put_file	This method completes the local file transfer to the remote destination, similar to the upload

How to use:

#创建目录SQL > create directory asm_dir as  ' +data/asm/datafile/';D irectory created . sql> create directory os_dir as  '/home/oracle ';D irectory created. #执行复制SQL >  exec dbms_file_transfer.copy_file (' Asm_dir ', ' user2.dbf.272.944581345 ', ' os_dir ', ' user.dbf ');P l/sql  procedure successfully completed. #验证复制结果 [[email protected] ~]$ ls -l  /home/oracle/user.dbf-rw-r-----.  1 oracle asmadmin 5251072 may 21 15:49  /home/oracle/user.dbf# for reverse replication sql> exec dbms_file_transfer.copy_file (' Os_dir ', ' user.dbf ', ' ASM _dir ', ' user2.dbf ');P l/sql procedure successfully completed. #验证ASMCMD  [+data/asm/datafile]  > ls -ltype      redund  striped  time              Sys  Name                                               n    user2.dbf => +data/orcl/datafile/copy_ file.273.944581883

3. Rman's convert and backup as copy commands

3.1 Convert command

The convert command is used for byte-format conversions of data files. Oracle can do table space Transfer (TTS) between different OS platforms, and if the byte format of the two platforms is inconsistent, the parameter data file is required for format conversion, which can be done either in the source database or in the destination database. If you are executing in the source database, you need to use the Convert tablespace command, which is required if you are executing in the destination database, using the Convert datafile command.

Although the convert command is primarily used for cross-platform transfer of table space, it can also complete the copying of files between ASM and local file systems, and is a very simple method.

#使用convert  datafile Copy from ASM to file system rman> convert datafile  ' +data/orcl/datafile/ users.259.925306091 '  format  '/home/oracle/user3.dbf '; Starting conversion at target at 21-may-17using channel ora_disk_1channel  ora_disk_1: starting datafile conversioninput file name=+data/orcl/datafile/ users.259.925306091converted datafile=/home/oracle/user3.dbfchannel ora_disk_1: datafile  Conversion complete, elapsed time: 00:00:01finished conversion at target  at 21-may-17# verification [[email protected] ~]$ ls -l /home/oracle/ User3.dbf-rw-r-----.  1 oracle asmadmin 5251072 may 21 16:00 /home/ oracle/user3.dbf# use Convert tablespace, you need to place the users table space as read-only rman> sql  ' Alter tablespace  users read only '; using target database control file instead of recovery catalogsql statement: alter tablespace users read  onlyrman> convert tablespace users format  '/home/oracle/users%u.dbf '; Starting conversion at source at 21-may-17allocated channel: ora_disk_ 1channel ora_disk_1: sid=81 instance=orcl1 device type=diskchannel ora_disk_1:  starting datafile conversioninput datafile file number=00004 name=+data/ Orcl/datafile/users.259.925306091converted datafile=/home/oracle/usersdata_d-orcl_i-1453086940_ts-users_ Fno-4_0ns4qbtn.dbfchannel ora_disk_1: datafile conversion complete, elapsed time :  00:00:01finished conversion at source at 21-may-17# verification [[email protected]  ~]$ ll /home/oracle/usersdata_d-orcl_i-1453086940_ts-users_fno-4_0ns4qbtn.dbf-rw-r-----.   1 oracle asmadmin 5251072&nbsp may 21 16:03 /home/oracle/usersdata_d-orcl_i-1453086940_ts-users_fno-4_0ns4qbtn.dbf# Copy from file system to asmrman> convert datafile  '/home/oracle/user3.dbf ', '/home/oracle/usersdata_d-orcl_ I-1453086940_ts-users_fno-4_0ns4qbtn.dbf '  format  ' +data '; starting conversion at target at 21-may-17using target database  Control file instead of recovery catalogallocated channel: ora_disk_ 1channel ora_disk_1: sid=64 instance=orcl1 device type=diskchannel ora_disk_1:  starting datafile conversioninput file name=/home/oracle/user3.dbfconverted  datafile=+data/orcl/datafile/users.274.944582713channel ora_disk_1: datafile conversion  complete, elapsed time: 00:00:01channel ora_disk_1: starting datafile  Conversioninput file name=/home/oracle/usersdata_d-orcl_i-1453086940_ts-userS_fno-4_0ns4qbtn.dbfconverted datafile=+data/orcl/datafile/users.275.944582713channel ora_disk_1:  datafile conversion complete, elapsed time: 00:00:01finished conversion  at target at 21-may-17# See the converted file name from the log #converted datafile=+data/orcl/datafile/ users.274.944582713#converted datafile=+data/orcl/datafile/users.274.944582713

3.2 Backup as Copy command

The backup as Copy command copies the files in a mirrored manner, and it is natural to copy the files from ASM to the file system. This is also one of the options you can choose when migrating from a file system to ASM.

#执行复制RMAN > backup as copy datafile  ' +data/orcl/datafile/users.259.925306091 '   format  '/home/oracle/user4.dbf '; Starting backup at 21-may-17using target database control file instead  of recovery catalogallocated channel: ora_disk_1channel ora_disk_1: sid=69  instance=orcl1 device type=DISKchannel ORA_DISK_1: starting datafile  Copyinput datafile file number=00004 name=+data/orcl/datafile/users.259.925306091output  file name=/home/oracle/user4.dbf tag=tag20170521t161225 recid=17 stamp= 944583145channel ora_disk_1: datafile copy complete, elapsed time:  00:00:01finished backup at 21-may-17# verification [[email protected] ~]$ ls -l / Home/oracle/user4.dbf -rw-r-----.  1 oracle asmadmin 5251072 May 21  16:12&Nbsp;/home/oracle/user4.dbf 

4.FTP method

The FTP method can perform operations on ASM files and directories like library replication uses traditional file Transfer Protocol (FTP) to perform regular operations on ordinary files. A typical application for accessing ASM files in this way is to copy ASM files from one database to another. Requires XML DB support, configuration is the most troublesome, interested students can refer to the Oracle Database 11g RAC Manual 2nd version of the original book or the Official document: http://docs.oracle.com/cd/E11882_01/server.112/ E18951/asmfiles.htm#babjccei

Refer to "Oracle RAC"

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1927904

Oracle copies files from ASM to file system