Guenadi N Jilevski's Oracle BLOG

Transferring files between ASM and OS file system in Oracle 10g – 11gR2 (11.2.0.2)

Transferring files between ASM and OS file system in Oracle 10g – 11gR2 (11.2.0.2)

ASM, introduced in Oracle 10G, is Oracle solution for storing database files. In Oracle 11gR2 ASM was further enhanced with a general purpose ASM Cluster File system (ACFS) and ASM Dynamic Volume Manager (ADVM). Since ASM was introduced RMAN remains the only supported way to migrate databases from file systems to ASM and from ASM to file systems. For information and examples about how to use RMAN for migration to ASM click here.

In this article you will look at ways to transfer files between file system and ASM. Files need to be transferred in cases of transportable tablespaces , transportable database or copying backup files to name a few. You will look at the following features:

• XML DB – enables file transfer since Oracle 10gR1
• ASMCMD cp command since Oracle 11g
• DBMS_FILE_TRANSFER package – allowed transfer between ASM/FS since 10gR2

1. XML DB
   

XML DB creates a virtual folder /sys/asm and makes it accessible via ftp or http. You will see how to use XML DB features for transferring files between file system and ASM using the virtual /sys/asm folder. In order to use this feature for file transfer XML DB needs to be installed and configured. The XML DB configuration steps are listed as follows.

1. Make sure that XML DB is installed. To check if installed issue the desc resource_view as SQL prompts. The output in Oracle 11gR2 is displayed below. If the object does not exist XML DB must be installed. If XML DB is installed continue.

   SQL> desc resource_view

   Name Null? Type

   —————————————– ——– —————————-

   RES XMLTYPE(XMLSchema “http://xm

   lns.oracle.com/xdb/XDBResour

   ce.xsd” Element “Resource”)

   ANY_PATH VARCHAR2(4000)

   RESID RAW(16)

   SQL>

2. Check if http and ftp ports are set

   SQL> SELECT dbms_xdb.gethttpport FROM dual;

   GETHTTPPORT

   ———–

   0

   SQL> SELECT dbms_xdb.getftpport FROM dual;

   GETFTPPORT

   ———-

   0

   SQL>

3. Set the port if not already set.

   SQL> exec dbms_xdb.sethttpport(8080);

   PL/SQL procedure successfully completed.

   SQL>

   SQL> exec dbms_xdb.setftpport(2100);

   PL/SQL procedure successfully completed.

   SQL>

4. Verify the ports.

   SQL> SELECT dbms_xdb.getftpport FROM dual;

   GETFTPPORT

   ———-

   2100

   SQL> SELECT dbms_xdb.gethttpport FROM dual;

   GETHTTPPORT

   ———–

   8080

   SQL>

5. If XML DB is installed an ASM virtual folder is created in /sys and is names /sys/asm. XML DB provides access via ftp or http using the hostname where the database is installed and the respective ftp or http port.
   

   FTP

   Example on Windows (database on windows non ASM storage): As this is non ASM based database /sys/asm folder is empty.

   E:\app\Administrator\product\11.1.0\db_3>ftp -n

   ftp> open userpc 2100

   Connected to userpc.gj.com.

   220- userpc

   Unauthorised use of this FTP server is prohibited and may be subject to civil an

   d criminal prosecution.

   220 userpc FTP Server (Oracle XML DB/Oracle Database) ready.

   ftp> user system

   331 pass required for SYSTEM

   Password:

   230 SYSTEM logged in

   ftp>

   ftp> ls

   200 PORT Command successful

   150 ASCII Data Connection

   OLAP_XDS

   home

   images

   olap_data_security

   public

   sys

   xdbconfig.xml

   226 ASCII Transfer Complete

   ftp: 72 bytes received in 0.10Seconds 0.73Kbytes/sec.

   ftp> cd sys

   250 CWD Command successful

   ftp> ls

   200 PORT Command successful

   150 ASCII Data Connection

   acloids

   acls

   apps

   asm

   databaseSummary.xml

   log

   oid

   principals

   schemas

   version

   workspaces

   xs

   226 ASCII Transfer Complete

   ftp: 103 bytes received in 0.00Seconds 103000.00Kbytes/sec.

   ftp> cd asm

   250 CWD Command successful

   ftp> ls

   200 PORT Command successful

   150 ASCII Data Connection

   226 ASCII Transfer Complete

   ftp>

   Example on Linux (database on Linux ASM storage): As this is an ASM based database /sys/asm folder exists and the ASM content is accessible. A file is transferred to file system from +dgdup/tst.

   [oracle@raclinux1 ~]$ ftp -n

   ftp> open raclinux1 2100

   Connected to raclinux1.gj.com.

   220- raclinux1.gj.com

   Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.

   220 raclinux1.gj.com FTP Server (Oracle XML DB/Oracle Database) ready.

   530 Please login with USER and PASS.

   530 Please login with USER and PASS.

   KERBEROS_V4 rejected as an authentication type

   ftp> user system sys1

   331 pass required for SYSTEM

   230 SYSTEM logged in

   ftp> pwd

   257 “/” is current directory.

   ftp> ls

   227 Entering Passive Mode (192,168,20,21,140,65)

   150 ASCII Data Connection

   drw-r–r– 2 SYS oracle 0 SEP 05 06:07 OLAP_XDS

   drw-r–r– 2 SYS oracle 0 SEP 25 10:57 home

   drw-r–r– 2 SYS oracle 0 SEP 05 06:22 images

   drw-r–r– 2 SYS oracle 0 SEP 05 06:07 olap_data_security

   drw-r–r– 2 SYS oracle 0 SEP 05 06:01 public

   drw-r–r– 2 SYS oracle 0 SEP 25 10:57 sys

   -rw-r–r– 1 SYS oracle 0 FEB 20 20:14 xdbconfig.xml

   drw-r–r– 2 SYS oracle 0 SEP 05 06:07 xds

   226 ASCII Transfer Complete

   ftp> cd sys/asm

   250 CWD Command successful

   ftp> ls

   227 Entering Passive Mode (192,168,20,21,138,105)

   150 ASCII Data Connection

   drw-r–r– 2 SYS oracle 0 FEB 20 20:20 SEC

   drw-r–r– 2 SYS oracle 0 FEB 20 20:20 PRIM

   drw-r–r– 2 SYS oracle 0 FEB 20 20:20 DGDUP

   drw-r–r– 2 SYS oracle 0 FEB 20 20:20 DGDUP2

   drw-r–r– 2 SYS oracle 0 FEB 20 20:20 DGDUP1

   drw-r–r– 2 SYS oracle 0 FEB 20 20:20 DATA

   226 ASCII Transfer Complete

   ftp>

   ftp> cd sys/asm/dgdup/tst

   250 CWD Command successful

   ftp> bin

   200 Type set to I.

   ftp> ls

   227 Entering Passive Mode (192,168,20,21,114,167)

   150 ASCII Data Connection

   drw-r–r– 2 SYS oracle 0 FEB 20 20:24 BACKUPSET

   drw-r–r– 2 SYS oracle 0 FEB 20 20:24 DATAFILE

   drw-r–r– 2 SYS oracle 0 FEB 20 20:24 CONTROLFILE

   drw-r–r– 2 SYS oracle 0 FEB 20 20:24 PARAMETERFILE

   drw-r–r– 2 SYS oracle 0 FEB 20 20:24 ARCHIVELOG

   drw-r–r– 2 SYS oracle 0 FEB 20 20:24 ONLINELOG

   drw-r–r– 2 SYS oracle 0 FEB 20 20:24 TEMPFILE

   -rw-r–r– 1 SYS oracle 20480 FEB 20 20:24 dr1tststby.dat

   -rw-r–r– 1 SYS oracle 20480 FEB 20 20:24 dr2tststby.dat

   drw-r–r– 2 SYS oracle 0 FEB 20 20:24 DATAGUARDCONFIG

   -rw-r–r– 1 SYS oracle 20480 FEB 20 20:24 dr1tst.dat

   -rw-r–r– 1 SYS oracle 20480 FEB 20 20:24 dr2tst.dat

   drw-r–r– 2 SYS oracle 0 FEB 20 20:24 FLASHBACK

   226 ASCII Transfer Complete

   ftp> get dr1tst.dat

   local: dr1tst.dat remote: dr1tst.dat

   227 Entering Passive Mode (192,168,20,21,196,98)

   150 BIN Data Connection

   226 BIN Transfer Complete

   20480 bytes received in 0.87 seconds (23 Kbytes/s)

   ftp> ls

   227 Entering Passive Mode (192,168,20,21,11,14)

   150 ASCII Data Connection

   drw-r–r– 2 SYS oracle 0 FEB 20 20:25 BACKUPSET

   drw-r–r– 2 SYS oracle 0 FEB 20 20:25 DATAFILE

   drw-r–r– 2 SYS oracle 0 FEB 20 20:25 CONTROLFILE

   drw-r–r– 2 SYS oracle 0 FEB 20 20:25 PARAMETERFILE

   drw-r–r– 2 SYS oracle 0 FEB 20 20:25 ARCHIVELOG

   drw-r–r– 2 SYS oracle 0 FEB 20 20:25 ONLINELOG

   drw-r–r– 2 SYS oracle 0 FEB 20 20:25 TEMPFILE

   -rw-r–r– 1 SYS oracle 20480 FEB 20 20:25 dr1tststby.dat

   -rw-r–r– 1 SYS oracle 20480 FEB 20 20:25 dr2tststby.dat

   drw-r–r– 2 SYS oracle 0 FEB 20 20:25 DATAGUARDCONFIG

   -rw-r–r– 1 SYS oracle 20480 FEB 20 20:25 dr1tst.dat

   -rw-r–r– 1 SYS oracle 20480 FEB 20 20:25 dr2tst.dat

   drw-r–r– 2 SYS oracle 0 FEB 20 20:25 FLASHBACK

   226 ASCII Transfer Complete

   ftp> bye

   HTTP

   The URL http://:<XMLDB httpport&gt; will provide an access after logging with DB credentials.

   

   Navigating

   

1. ASMCMD cp command
   

   Starting with 11G asmcmd is further enhanced and one of the benefits is cp. ASMCMD cp command can be used to copy both locally and remotely in the following directions:
   

• ASM->FS
  
• FS->ASM
  
• ASM-ASM
  

Note: If file system is involved while transporting tablespaces the regular conversion must take place as known in Oracle 10g while transporting tablespaces across different Endian f(byte-ordering) format. If a transfer is ASM->ASM there is no need for conversion as ASM format is portable.

ASMCMD> help cp

cp

Enables you to copy files between Oracle ASM disk groups on local

instances to and from remote instances.

cp [-i][-f][connect_str:]src_file [connect_str:]tgt_file

The options for the cp command are described below.

-i – Interactive, prompt before copy file or overwrite

-f – Force, if an existing destination file, remove it and

try again without user interaction

connect_str – The connection string for use with a remote instance

copy.

src_file – Name of the source file to copy.

tgt_file – A user alias for the created target file name or

alias directory name.

cp cannot copy files between two remote instances. The local Oracle

ASM instance must be either the source or the target of the operation.

You can use the cp command to:

Copy files from a disk group to the operating system

Copy files from a disk group to a disk group

Copy files from the an OS File system to a disk group

Some file types cannot be the source or destination of the cp command.

These file types include OCR, OCR backup and SPFILE file types.

To back up, copy, or move an ASM SPFILE, use the spbackup, spcopy,

or spmove commands.

connect_str is not required for a local instance copy, which is the

default case. In the case of a remote instance copy, you must specify

the connect string and Oracle ASM prompts for a password in a

non-echoing prompt. The connect_str is in the form of:

user@host[.port_number].SID

user, host, and SID are required in the connect_str parameter. The

default port number is 1521.

src_file must be either the fully qualified file name, the

system-generated name, or the Oracle ASM alias.

The cp command performs a binary copy without any data transformation.

The following are examples of the cp command. The

first example shows a copy of a file in the data disk group to a file

on the operating system. The second example shows a copy of a file on

the operating system to the DATA disk group.

The third and fourth example shows how to copy files from ASM to

remote server (OS file).

sys is the user name on the remote server

server is the remote server name

asminstance ASM Instance name

/scratch/file OS file.

If password is not provided in the command line, it will be asked.

ASMCMD [+] > cp +data/orcl/datafile/EXAMPLE.265.691577295 /mybackups/example.bak

copying +data/orcl/datafile/EXAMPLE.265.691577295 -> /mybackups/example.bak

ASMCMD [+] > cp /mybackups/examples.bak +data/orcl/datafile/myexamples.bak

copying /mybackups/examples.bak -> +data/orcl/datafile/myexamples.bak

ASMCMD [+] > cp +data/orcl/datafile/EXAMPLE.265.691577295 sys@server.asminstance:/scratch/file

ASMCMD [+] > cp +data/orcl/datafile/EXAMPLE.265.691577295 sys/passwd@server.asminstance:/scratch/file

ASMCMD [+] > cp sys@server.asminstance:/scratch/file +data/orcl/datafile/file

ASMCMD>

1. DBMS_FILE_TRANSFER PL/SQL package.
   

   DBMS_FILE_TRANSFER package is available since Oracle 10gR1 and since 10gR2 supports transferring files as follows:
   

• ASM->ASM
  
• FS->ASM
  
• FS->FS
  
• FS->ASM
  

Structure of the package is

PROCEDURE COPY_FILE

Argument Name Type In/Out Default?

—————————— ———————– —— ——–

SOURCE_DIRECTORY_OBJECT VARCHAR2 IN

SOURCE_FILE_NAME VARCHAR2 IN

DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN

DESTINATION_FILE_NAME VARCHAR2 IN

PROCEDURE GET_FILE

Argument Name Type In/Out Default?

—————————— ———————– —— ——–

SOURCE_DIRECTORY_OBJECT VARCHAR2 IN

SOURCE_FILE_NAME VARCHAR2 IN

SOURCE_DATABASE VARCHAR2 IN

DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN

DESTINATION_FILE_NAME VARCHAR2 IN

PROCEDURE PUT_FILE

Argument Name Type In/Out Default?

—————————— ———————– —— ——–

SOURCE_DIRECTORY_OBJECT VARCHAR2 IN

SOURCE_FILE_NAME VARCHAR2 IN

DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN

DESTINATION_FILE_NAME VARCHAR2 IN

DESTINATION_DATABASE VARCHAR2 IN

Example:

Let’s create a directory pointing to a file system

CREATE OR REPLACE DIRECTORY fs_dir AS ‘/u01/stage_file’;

Let’s create a directory pointing to ASM

CREATE OR REPLACE DIRECTORY ASM_dir AS ‘+DGDUP/tst’;

Execute dbms_file_transfer.COPY_FILE(‘asm_dir’,’dr1tst.dat’,’fs_dir’,’dr1tst.dat’) to copy dr1tst.dat file from ASM to a /u01/tsage_file directory.

SQL> exec dbms_file_transfer.COPY_FILE(‘asm_dir’,’dr1tst.dat’,’fs_dir’,’dr1tst.dat’);

PL/SQL procedure successfully completed.

SQL>

The remaining procedures (GET_FILE and PUT_FILE) are similar in the way source and destination are specified but enable further transfer to a remote DB specified by dblink. Both source and target databases should have directories defined. Restrictions related to dbms_file_transfer package are:

• Files are supposed to be multiple of 512 bytes
• Files should be less than 2TB
• There is no character set conversion
• Oracle should have permissions to the file and after copy file are owned by oracle user.
• File transfer operation can be monitored from v$session_longops

May 12, 2011 - Posted by gjilevski | oracle