Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA 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 | oracle

6 Comments »

  1. how can we move the logfile from ASM to FS using FTP in Oracle 10GR2

    Comment by Gopi | November 25, 2011 | Reply

    • Hi,

      The same old procedure to rename log files.

      1. copy as binary
      2. remame to whatever yuu copied.

      Regards,

      Comment by gjilevski | November 25, 2011 | Reply

  2. Hi,

    I’m looking for some kind of assistance or training in installing Oracle
    10g 2 nodes RAC with no ASM on AIX operating system.

    Help would be highly appreciated for my requirement.

    Thanks,
    -Pavan.

    Comment by Pavan | October 19, 2012 | Reply

  3. There is also similar file size limitation with ASMCMD CP. It cannot do file greater than 2TB.

    Another option you have not touched upon is using RMAN. You can use RMAN convert to copy file into ASM.

    Comment by Pawan Shrestha | July 2, 2013 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 778 other followers

%d bloggers like this: