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
- 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.
-
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>
-
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>
-
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>
-
Verify the ports.
SQL> SELECT dbms_xdb.getftpport FROM dual;
GETFTPPORT
———-
2100
SQL> SELECT dbms_xdb.gethttpport FROM dual;
GETHTTPPORT
———–
8080
SQL>
-
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> will provide an access after logging with DB credentials.
Navigating
-
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>
-
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
6 Comments »
Leave a Reply
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
how can we move the logfile from ASM to FS using FTP in Oracle 10GR2
Hi,
The same old procedure to rename log files.
1. copy as binary
2. remame to whatever yuu copied.
Regards,
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.
Hi,
I would suggest to look at Oracle 10gR2 documentation here http://www.oracle.com/pls/db102/portal.portal_db?selected=3
Pay attention to
Oracle® Database Oracle Clusterware and Oracle Real Application Clusters Installation Guide
10g Release 2 (10.2) for AIX Part Number B14201-04
here http://docs.oracle.com/cd/B19306_01/install.102/b14201/toc.htm
Regards,
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.
Hi,
RMAN is here in the link and is only for database files.
https://gjilevski.com/2010/12/21/moving-a-database-from-file-system-to-asm-and-vice-versa-in-oracle-11gr2/
Best Regards,