Oracle RAC, DG, EBS, DR and HA DBA BLOG

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 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.

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.

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.

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 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.

About

Guenadi Jilevski is a Senior Oracle Professional (OCP 7, 8, 8i, 9i, 10g, 11g, OCE 10gR2 RAC, OCE Oracle RAC 11gR2 and Grid Infrastructure Administrator, Oracle Exadata 11g Certified Implementation Specialist,Oracle GoldenGate 10 Certified Implementation Specialist and OCM 9i, OCM 11g) with more than 20 years of industrial experience in high end Oracle technology starting with Oracle RDBMS version 6 up to 11g. He has been involved with multi platform High Availability Solutions starting with OPS and Veritas and Sun cluster as well as complex multiple node RAC 9i, 10g and 11g implementations on AIX, Linux, Solaris and HP. He has experience in deploying disaster recovery solutions with standby databases compliant to Oracle MAA and third party solutions such as Shareplex and Goldengate. As a former employee of Oracle Corporation Advanced Technology Solutions Consulting Practice in Americas, Guenadi has been involved in multiple visible high end projects for RAC enabling EBS, Peoplesoft, Siebel ERP applications, end to end performance tuning, architectured High Availability and Disaster Recovery solutions to name a few. Presently he is working as A Senior Oracle Consultant in EMEA.

The views expressed on this blog are my own and do not necessarily reflect the views of Current/Past/Future Employers. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect mine.