> -----Original Message-----
> From: Leslie Lu [mailto:leslie_y_lu_at_yahoo.com]
>
> I got a compressed file Amin.dmp.Z of 1.28G. After
> uncompress, the Amin.dmp file is 9.7G. Can someone
> show me how to import using pipe? The pipe works on Z
> file or dmp file? (Oracle815 for both export, import
> db, Sun 5.6).
Haven't done it on Sun Solaris, but here's what I did on HP-UX several years ago, copying the idea from an article in the LAOUG magazine if I remember correctly (LAOUG = Los Angeles Oracle Users Group)
I thought that the exp/imp file size limit of 2GB would be gone in Oracle 8.1.5 on Sun Solaris 2.6. In solaris 2.6 (= SunOS 5.6), I think that the "-largefiles" mount option is the default. But I'm no Sun Solaris expert.
- ksh script to export file, compress, split into 2GB "chunks"
#!/usr/bin/ksh
#
# This shell script uses the "split" command to create an export
# file divided into several smaller size files (useful to override
# the 2 GB limit on export file sizes.)
#
# The -b option on the split command does the following:
# -b n The input file is split into pieces n bytes in
# size.
# -b nk The input file is split into pieces n x 1024 bytes
# in size. No space separates the n from the k.
# -b nm The input file is split into pieces n x 1048576
# bytes in size. No space separates the n from the
# m.
# e.g. -b 2000m indicates to split command to split the input file into every
# 2000 MB size.
# In this script, the "split size" is determined by the split_size
# environment variable.
#
# As it splits, the split command will suffix aa, ab, ac ... up to zz to the
# file name specified (a limit of 626 files). That limit can be increased by
# the split command's "-a suffix_length" parameter. The default filename
# is x, but will be superseded in this script by dump_file environment
# variable.
#
# Make sure the directory chosen is large enough to hold the files.
#
# chmod a+x split_export.ksh
# and run with the following command
# nohup split_export.ksh > split_export.log 2>&1 &
# -----------------------------------------------------------------
# Start time
#
date +"%A, %Y/%m/%d %H:%M:%S %Z"
# -----------------------------------------------------------------
# Configuration Section 1
# set up work directories and other parameters
#
pipe_dir="/home/my_dir"
tmp_dir="/tmp"
pipe_for_split="split_pipe"
pipe_for_compress="compress_pipe"
split_size="2000m"
tmp_ctl="exp_`date +\"%Y%m%d%H%M%S\"`.ctl"
tmp_ctl_with_pwd="exp2_`date +\"%Y%m%d%H%M%S\"`.ctl"
# -----------------------------------------------------------------
# Configuration Section 2
# set up Oracle export parameters
# note: export log file will be created in current directory.
#
export ORACLE_SID="my_db"
db_user="my_userid"
db_pwd="my_password"
dump_dir="/home/my_dir"
dump_file="my_tables.dmp"
cat > ${tmp_dir}/${tmp_ctl} << LA_FIN
# enter Oracle export commands here
tables = (owner.table1, owner.table2)
indexes = y
rows = y
constraints = n
log = testexp.log
# end of Oracle export commands
LA_FIN
# -----------------------------------------------------------------
# a) create named pipes
#
rm -f ${pipe_dir}/${pipe_for_split}
rm -f ${pipe_dir}/${pipe_for_compress}
/sbin/mknod ${pipe_dir}/${pipe_for_split} p
/sbin/mknod ${pipe_dir}/${pipe_for_compress} p
# -----------------------------------------------------------------
# b) start background processes that use the named pipes
# i) compress will take export file and compress it
# ii) split will take compressed file and split it
#
nohup compress < ${pipe_dir}/${pipe_for_compress} > ${pipe_dir}/${pipe_for_split} &
nohup split -b ${split_size} < ${pipe_dir}/${pipe_for_split} - ${dump_dir}/${dump_file} &
# -----------------------------------------------------------------
# c) create parameter file for export
# (with permissions only for user since file has Oracle pwd)
#
cat > ${tmp_dir}/${tmp_ctl_with_pwd} << LA_FIN
LA_FIN
chmod a-rwx,u+rw ${tmp_dir}/${tmp_ctl_with_pwd}
echo "userid = $db_user/$db_pwd" >> ${tmp_dir}/${tmp_ctl_with_pwd}
cat < ${tmp_dir}/${tmp_ctl} >> ${tmp_dir}/${tmp_ctl_with_pwd}
echo "file = ${pipe_dir}/${pipe_for_compress}" >> ${tmp_dir}/${tmp_ctl_with_pwd}
# -----------------------------------------------------------------
# d) perform export
#
exp parfile=${tmp_dir}/${tmp_ctl_with_pwd}
echo "Export complete!"
# -----------------------------------------------------------------
# e) cleanup of temporary files/pipes
#
rm -f ${tmp_dir}/${tmp_ctl_with_pwd}
rm -f ${tmp_dir}/${tmp_ctl}
rm -f ${pipe_dir}/${pipe_for_split}
rm -f ${pipe_dir}/${pipe_for_compress}
# -----------------------------------------------------------------
# End time
#
date +"%A, %Y/%m/%d %H:%M:%S %Z"
- ksh script to "concatenate", uncompress, and import the file
- that was compressed and split into 2GB chunks
#!/usr/bin/ksh
#
# This shell script does an import using an export file that was
# compressed and then "split" into smaller files using the split
# command (useful for circumventing export's 2 GB limit on
# export dump files.)
#
# The split command has appended the suffixes aa, ab, ac ... up to zz to the
# dump file name specified (a limit of 626 files). That limit can be increased by
# the split command's "-a suffix_length" parameter.
#
# chmod a+x split_import.ksh
# and run with the following command
# nohup split_import.ksh > split_import.log 2>&1 &
# -----------------------------------------------------------------
# Start time
#
date +"%A, %Y/%m/%d %H:%M:%S %Z"
# -----------------------------------------------------------------
# Configuration Section 1
# set up work directories and other parameters
#
pipe_dir="/home/my_dir"
tmp_dir="/tmp"
pipe_for_cat="cat_pipe"
pipe_for_uncompress="uncompress_pipe"
tmp_ctl="imp_`date +\"%Y%m%d%H%M%S\"`.ctl"
tmp_ctl_with_pwd="imp2_`date +\"%Y%m%d%H%M%S\"`.ctl"
# -----------------------------------------------------------------
# Configuration Section 2
# set up Oracle import parameters
# note: import log file will be created in current directory.
#
export ORACLE_SID="my_db"
db_user="my_userid"
db_pwd="my_password"
dump_dir="/home/my_dir"
dump_file="my_tables.dmp"
cat > ${tmp_dir}/${tmp_ctl} << LA_FIN
# enter Oracle import commands here
fromuser = owner
touser = new_owner
indexes = n
commit = y
analyze = n
feedback = 100000
log = testimp.log
# end of Oracle import commands
LA_FIN
# -----------------------------------------------------------------
# a) create named pipes
#
rm -f ${pipe_dir}/${pipe_for_cat}
rm -f ${pipe_dir}/${pipe_for_uncompress}
/sbin/mknod ${pipe_dir}/${pipe_for_cat} p
/sbin/mknod ${pipe_dir}/${pipe_for_uncompress} p
# -----------------------------------------------------------------
# b) start background processes that use the named pipes
# i) cat will concatenate "split" files
# ii) uncompress will decompress the file
# "sleep" commands allow data to start "feeding" to the pipe
# before another process starts reading from the pipe.
# IMPORTANT! This step assumes that in the dump directory,
# all files starting with $dump_file result from the "split".
#
nohup cat `ls ${dump_dir}/${dump_file}*` > ${pipe_dir}/${pipe_for_cat} &
sleep 30
nohup uncompress < ${pipe_dir}/${pipe_for_cat} > ${pipe_dir}/${pipe_for_uncompress} &
sleep 120
# -----------------------------------------------------------------
# c) create parameter file for import
# (with permissions only for user since file has Oracle pwd)
#
cat > ${tmp_dir}/${imp_par_file} << LA_FIN
LA_FIN
chmod a-rwx,u+rw ${tmp_dir}/${tmp_ctl_with_pwd}
echo "userid = $db_user/$db_pwd" >> ${tmp_dir}/${tmp_ctl_with_pwd}
cat < ${tmp_dir}/${tmp_ctl} >> ${tmp_dir}/${tmp_ctl_with_pwd}
echo "file = ${pipe_dir}/${pipe_for_uncompress}" >> ${tmp_dir}/${tmp_ctl_with_pwd}
# -----------------------------------------------------------------
# d) perform import
#
imp parfile=${tmp_dir}/${tmp_ctl_with_pwd}
echo "Import complete!"
# -----------------------------------------------------------------
# e) cleanup of temporary files/pipes
#
rm -f ${tmp_dir}/${tmp_ctl_with_pwd}
rm -f ${tmp_dir}/${tmp_ctl}
rm -f ${pipe_dir}/${pipe_for_cat}
rm -f ${pipe_dir}/${pipe_for_uncompress}
# -----------------------------------------------------------------
# End time
#
date +"%A, %Y/%m/%d %H:%M:%S %Z"
Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com
Received on Wed Aug 01 2001 - 15:56:11 CDT
