Oracle ASM and Transportable Tablespaces

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

During the transportation of a tablespace from one database to another, it is possible for your source and target tablespaces to be stored either using ASM files or regular file-system files.

In all possible storage combinations, you can perform the transfer by using the DBMS_FILE_TRANSFER package running in one of the database instances. This operation can be performed directly without having to convert the data file.

For information about transportable tablespaces, refer to section "Transporting Tablespaces Across Platforms".

For information about using DBMS_FILE_TRANSFER, refer to section "Copying Files Using the Database Server".

ASM Command-Line Interface

Introduced in Oracle 10g release 2, the ASM Command-Line Interface (ASMCMD) utility provides an easy way to manipulate files within Automatic Storage Management (ASM) diskgroups. Its major functionality is to present an ASM file system in a user-friendly directory-tree structure. ASMCMD provides short commands for accessing the files and directories within ASM diskgroups.

The interface provides both interactive and non-interactive modes. The interactive mode enters a shell-like environment where the user is prompted to issue the commands. The non-interactive mode executes a single command and exits the utility. The latter is made available for scripting and batch-processing purposes.

You can invoke the ASMCMD tool with a -p parameter to always display the present directory inside the prompt itself.

Here is a brief description of ASMCMD commands:

pwd	displays the absolute path of the current directory.
cd	changes the current directory to the specify directory.
find	finds under a specified directory all paths that match a given pattern.
ls	lists aliases or its contents alphabetically by name if the alias is a directory.
mkdir	creates directories.
rm	removes the specified file as well as its system alias. If it is an empty directory, then rm removes it.
mkalias	creates the specified user alias for the specified system alias.
rmalias	deletes the specified user aliases, while preserving the files and their system aliases.
du	displays the total space used for files located recursively under the specified directory.
lsdg	lists all diskgroups and their attributes.
lsct	lists all clients and their attributes.
help	displays list of commands

The browser connects to Oracle XML DB via HTTP. Click on the hyperlink sys and then asm; you will then see all the disk groups from where you can download any datafile.

FTP and HTTP Access

Because ASM is not a regular file system, you can't use the standard FTP and HTTP services to access these files. To access them, you can use the file mapping functionalities provided by the Oracle XML Database (Oracle XML DB) feature.

To set up the FTP access, you must first set up the Oracle XML DB access to the ASM folders. I can do this by executing the catxdbdbca.sql script, found in the $ORACLE_HOME/rdbms/admin directory. The script takes two parameters: the port numbers for the FTP and HTTP services, respectively.

@catxdbdbca 7777 8080

Now you can connect to the created Oracle XML DB FTP service using a database username and password:

ftp myserverhost 7777

ASM disk groups are available outside the database via a virtual file system: /sys/asm. From there, you can navigate ASM storgae. For example:

ftp> cd /sys/asm
ftp> ls
USERDG5
USERDG4
USERDG3
USERDG2
USERDG1
ftp> cd USERDG2
250 CWD Command successful
ftp> ls
emrep
DBA102
ftp> cd DBA102
ftp> ls
DATAFILE
system01.dbf
system01.dbf
sysaux01.dbf
undotbs01.dbf
users01.dbf
CONTROLFILE
control01.ctl
...

You can then switch to binary mode and download any datafile:

ftp> bin
ftp> get users01.db

For HTTP access, open the browser on the following URL:

http://myserverhost:8080

The browser connects to Oracle XML DB via HTTP. Click on the hyperlink sys and then asm; you will then see all the disk groups from where you can download any datafile.