Home » RDBMS Server » Server Administration » Copy many files from ASM to FileSystem, need an issue (Oracle 11gR1, Solaris 10)
Copy many files from ASM to FileSystem, need an issue [message #583575] Wed, 01 May 2013 22:47 Go to next message
trantuananh24hg
Messages: 612
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Hi all!

I have got backup pieces in ASM, I guess about more than 100 files.
Now, I need to copy all of them from ASM to FileSystem, there are 2 methods still now:

1- Copy from ASM to FileSystem using cp command.
2- Copy from ASM to FileSystem using DBMS_FILE_TRANSFER.

But:

In the first method, when I copy one file, I took more than 1 minute, so the following script would take me more than 1 days (I guess so).

#!/bin/ksh
#
# This script copies files from FRA on ASM to local disk
#
ORACLE_SID=+ASM2 
ASMLS=/vasgatedb/app/vsgbkp/asm_ls.txt ##{ASM files list}
BKP=+data/vsgdb/vsgbkp/`date +%Y_%m_%d` ##{source location of files}
LOCALBACKUPDIR=/vasgatedb/app/vsgbkp  ##{destination filesystem}
LOG=/vasgatedb/app/vsgbkp/asm_log.txt ##{log file}
#
# Get the list of files
#
$ORACLE_HOME/bin/asmcmd > $ASMLS <<EOF
ls $BKP
exit
EOF
#
# Clean the list by removing "ASMCMD>"
#
sed -i 's/ASMCMD> //g' $ASMLS
##cat $ASMLS
echo `date` > $LOG
#
# Copy files one by one
#

for FILENAME in `cat $ASMLS`
do
if [[ ! -f $LOCALBACKUPDIR/${FILENAME} ]]
then
$ORACLE_HOME/bin/asmcmd >> $LOG <<EOF
cp $BKP/$FILENAME $LOCALBACKUPDIR
EOF
fi
done
echo `date` >> $LOG


The second method, DBMS_FILE_TRANSFER took me less than 1 second to copy one file completely.

sys@VSGDB> set timing on
sys@VSGDB> exec dbms_file_transfer.COPY_FILE('asm_dir','level_0_vsgdb_9998_813844797.bkp','fs_dir','level_0_vsgdb_9998_813844797.bkp');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.38


Of course, I wish I used the second method as soon as possible, however, said I below, I've got about ~200 files, and I could not copy one by one file.

May you guide me a better way?

Thank you!
Re: Copy many files from ASM to FileSystem, need an issue [message #583576 is a reply to message #583575] Wed, 01 May 2013 23:05 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
1) create a text file which contains all the filenames that need to be transferred.
2) using sqlldr or EXTERNAL TABLE to load list into the DB
3) write PL/SQL to generate lines that look like below using UTL_FILE
exec dbms_file_transfer.COPY_FILE('asm_dir','level_0_vsgdb_9998_813844797.bkp','fs_dir','level_0_vsgdb_9998_813844797.bkp');
4) invoke file from #3 in sqlplus
Re: Copy many files from ASM to FileSystem, need an issue [message #583607 is a reply to message #583576] Thu, 02 May 2013 04:25 Go to previous messageGo to next message
trantuananh24hg
Messages: 612
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
BlackSwan wrote on Thu, 02 May 2013 04:05
1) create a text file which contains all the filenames that need to be transferred.
2) using sqlldr or EXTERNAL TABLE to load list into the DB
3) write PL/SQL to generate lines that look like below using UTL_FILE
exec dbms_file_transfer.COPY_FILE('asm_dir','level_0_vsgdb_9998_813844797.bkp','fs_dir','level_0_vsgdb_9998_813844797.bkp');
4) invoke file from #3 in sqlplus


Thank you for your idea!

I completed those steps, then I re-write as:

1- Collect file from v$asm_alias and spool into a text file named as asm_file.lst
ASM+> select name from v$asm_alias where name like '%bk%';

level_0_vsgdb_998_813701080.bkp
...
10369 rows selected. 


2- Change the text file to Excel, push it into the database
2.1. First, I used sql loader, but I could not because of error SQL-350 combination of letter and number. So, I forgot it.
2.2. Second time, when I tried to use sql loader incompletetly, I use Full Convert tool, push it into table BKP sucessfully.

3- Using Sql command to generate a script file:
select 
'execute dbms_file_transfer.COPY_FILE(''asm_dir'','''||name||''',''fs_dir'','''||name||''');'
from bkp;


4- Execute script file.

Swan, thank you again!

P/S: When I finish move backup files from ASM to FileSystem directory, then I can use them as a correct backup, same to ASM directory, can I?

Re: Copy many files from ASM to FileSystem, need an issue [message #583622 is a reply to message #583575] Thu, 02 May 2013 07:12 Go to previous message
John Watson
Messages: 4693
Registered: January 2010
Location: Global Village
Senior Member
Another technique (I don't know if it would be faster or not) would be to use ftp.
In your database instance, run this

exec dbms_xdb.setftpport(2121)

(use whatever port you want, of course) to create an ftp listening service in your node listener. Then ftp to it, logon on as system/manager (or whomever) and get the files:
[oracle@iron1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-MAY-2013 14:10:58

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                26-APR-2013 11:50:03
Uptime                    6 days 2 hr. 20 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/iron1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.31)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.140)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=iron1.example.com)(PORT=2121))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "bull" has 1 instance(s).
  Instance "bull1", status READY, has 1 handler(s) for this service...
Service "bullXDB" has 1 instance(s).
  Instance "bull1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@iron1 ~]$
[oracle@iron1 ~]$
[oracle@iron1 ~]$ ftp 127.0.0.1 2121
Connected to 127.0.0.1.
220- iron1.example.com
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 iron1.example.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
Name (127.0.0.1:oracle): system
331 pass required for SYSTEM
Password:
230 SYSTEM logged in
Remote system type is Unix.
ftp> ls /sys/asm/oradata/bull
227 Entering Passive Mode (192,168,56,31,52,81)
150 ASCII Data Connection
drw-r--r--   2 SYS      oracle         0 MAY 02 13:10 DATAFILE
drw-r--r--   2 SYS      oracle         0 MAY 02 13:10 CONTROLFILE
drw-r--r--   2 SYS      oracle         0 MAY 02 13:10 ONLINELOG
drw-r--r--   2 SYS      oracle         0 MAY 02 13:10 TEMPFILE
drw-r--r--   2 SYS      oracle         0 MAY 02 13:10 PARAMETERFILE
-rw-r--r--   1 SYS      oracle      5632 MAY 02 13:10 spfilebull.ora
226 ASCII Transfer Complete
ftp>

Previous Topic: ORA-00119: invalid specification for system while dbca deleteDatabase
Next Topic: DBMS_SCHEDULER
Goto Forum:
  


Current Time: Sat Oct 25 22:11:49 CDT 2014

Total time taken to generate the page: 0.08725 seconds