Oracle’s Automatic Storage Management (ASM), introduced in Oracle 10g, provides an alternative to raw disk devices for storing Oracle-related files. Like raw disks, ASM volumes (called diskgroups) have no filesystem and cannot be browsed directly at the operating system level. This makes maintenance a challenge because you cannot use the normal commands to copy and delete files (cp and rm in UNIX, copy and del in Windows).You can use RMAN to back up and restore ASM files and, in 10gR2, you can use ASMCMD to view and manipulate the directory structure. The DBMS_FILE_TRANSFER package in Oracle 10g is yet another way to work with ASM.

DBMS_FILE_TRANSFER copies files on the same Oracle server or between two Oracle servers. It uses directory objects to specify the source and destination directories and, because directory objects support ASM pathnames, so does DBMS_FILE_TRANSFER. This makes it an easy way to move files to and from ASM storage from a regular filesystem.

DBMS_FILE_TRANSFER can copy any kind of file to and from regular filesystem storage, but it can only transfer Oracle files to and from ASM diskgroups. Datafiles, logfiles (including archivelogs), and controlfiles can be copied, but you can’t put a copy of your init.ora there (for example).

Suppose you use an ASM diskgroup to store archivelogs, and you need to extract some of the archivelogs to send to a Data Guard standby server. Listing A shows two CREATE DIRECTORY commands that define the archive directory on the ASM diskgroup (+DG1) on my server and a temporary directory (C:\Temp) in a filesystem. Listing B shows the use of DBMS_FILE_TRANSFER’s COPY_FILE procedure to copy the logs.

A cursor loops through the log names, selected from the V$ARCHIVED_LOG dynamic performance view. The filename portion and sequence number are extracted via SUBSTR, and then a new filename is built. Finally, DBMS_FILE_TRANSFER is called to perform the copy.

You can also transfer to and from other servers using the GET_FILE and PUT_FILE procedures in the package.

Bob Watkins (OCP, MCITP, MCDBA, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. He is a Senior Consultant and Managing Partner at B. Watkins, a database consulting and training firm in the Dallas / Fort Worth area. Visit Bob’s site.

——————————————————————————————

Get Oracle tips in your inbox

TechRepublic’s free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!