Экспорт - импорт БД oracle большого размера.

ORACLE_HOME=/u01/app/oracle/products/8.1.7.4; export ORACLE_HOME
ORACLE_SID=ORCL; export ORACLE_SID
EXP=$ORACLE_HOME/bin/exp
IMP=$ORACLE_HOME/bin/imp
NLS_LANG=AMERICAN_AMERICA.CL8MSWIN1251; export NLS_LANG
DUMP_DIR=/mnt/drive/exp-imp
PIPE=$DUMP_DIR/pipe/exp_full_pipe
ELOG=$DUMP_DIR/elog/$ORACLE_SID-full-`date +%Y%m%d`.elog
ILOG=$DUMP_DIR/ilog/$ORACLE_SID-full-`date +%Y%m%d`.ilog
rm -f $PIPE
mknod $PIPE p # Make the pipe
date >> $ELOG
date >> $ILOG
$EXP system/pass@source FULL=Y ROWS=N CONSTRAINTS=N INDEXES=Y TRIGGERS=N log=$ELOG file=$PIPE& #2>/dev/null 1>/dev/null & # Export to the pipe
$IMP system/pass@dest FULL=Y IGNORE=Y log=$ILOG file=$PIPE #Import from the pipe 
date >> $ELOG
date >> $ILOG
rm -f $PIPE
^D
По аналогии создаем скрипт, с помощью которого можно перенести отдельный объект.
cd ..
cat > table_export.sh
Код:
ORACLE_HOME=/u01/app/oracle/products/8.1.7.4; export ORACLE_HOME
ORACLE_SID=ORCL; export ORACLE_SID
EXP=$ORACLE_HOME/bin/exp
IMP=$ORACLE_HOME/bin/imp
NLS_LANG=AMERICAN_AMERICA.CL8MSWIN1251; export NLS_LANG
DUMP_DIR=/mnt/drive/exp-imp
PIPE=$DUMP_DIR/pipe/$1.$2
ELOG=$DUMP_DIR/elog/$ORACLE_SID-$1.$2-`date +%Y%m%d`.elog
ILOG=$DUMP_DIR/ilog/$ORACLE_SID-$1.$2-`date +%Y%m%d`.ilog
rm -f $PIPE
mknod $PIPE p # Make the pipe
date >> $ELOG
date >> $ILOG
$EXP $1/temp_pass@source TABLES=$2 COMPRESS=Y FEEDBACK=1000 log=$ELOG file=$PIPE 2>/dev/null& # Export to the pipe
$IMP $1/temp_pass@dest TABLES=$2 COMMIT=Y IGNORE=Y log=$ILOG file=$PIPE 2>/dev/null # Import from the pipe
date >> $ELOG
date >> $ILOG
rm -f $PIPE
^DДля полного счастья строим отсортированный по размеру список таблиц, данные которых необходимо перенести с помощью следующего SQL:
cat >list_tbls.sql
Код:
whenever sqlerror exit sql.sqlcode;
set pause off;
set pages 0;
set linesize 150;
set feedback off;
set termout off;
column cmd_line format a80
column tick format a3
column sum_bytes format 999,999,999,999
spool master.list;
-select '/mnt/drive/exp-imp/full_export_no_rows.sh' from dual;
select '/mnt/drive/exp-imp/table_export.sh '||owner||' '||segment_name cmd_line,' # ' tick,
sum(bytes) sum_bytes
from dba_segments
where segment_type = 'TABLE'
and owner <> 'SYS'
and owner not like 'AURORA%'
group by owner, segment_name
order by sum(bytes) desc;
spool off;
exit;
^D
Далее создем скрипт который позволит пускать необходимые процессы в параллели:
cat >paresh
Код:
#!/bin/bash
#Исправте количество запускаемых процессов
#А также путь к командному файлу
message()
{ timestamp=`date +%Y.%m.%d:%H:%M` echo "$timestamp $*" | tee -a $logfile return
}
get_shell()
{ echo "`date` $1 Shell Request $$" >> $lklogfile while : do next_shell="" if [ ! -s ${workfile} ] then break fi if [ ! -f $lockfile ] then. echo $$ > $lockfile echo "`date` $1 Lock Obtained $$" >> $lklogfile if [ "$$" = "`cat $lockfile`" ] then next_shell=`sed -e q $workfile` sed -e 1d $workfile > ${workfile}.tmp mv ${workfile}.tmp $workfile rm -f $lockfile echo "`date` $1 Shell Issued " >> $lklogfile return else echo "`date` $1 Lock FAULTED $$" >> $lklogfile fi fi sleep 1 done return
}
paresh_slave()
{ shell_count=0 get_shell $1 while [ "$next_shell" != "" ] do shell_count=`expr $shell_count + 1` message "Slave $1: Running Shell $next_shell" $next_shell shell_status=$? if [ "$shell_status" -gt 0 ] then message "Slave $1: ERROR IN Shell $next_shell 2status=$shell_status" echo "Slave $1: ERROR IN Shell $next_shell status=$shell_status" >> $errfile fi get_shell $1 done message "Slave $1: Done (Executed $shell_count Shells)" return
}

paresh_driver()
{ rm -f $lklogfile if [ "$1" = "" ] then master_file="/mnt/drive/exp-imp/master.list" echo $master_file else if [ ! -f "$1" ] then echo "$0: Unable to find File $1" exit 1 else master_file="$1" fi fi if [ "$2" = "" ] then parallel_count=24 #(CPU*2) else if [ "$2" -lt 1 ] then echo "$0: Parallel Process Count Must be > 0" exit 1 else parallel_count=$2 fi fi message "-" message "Master Process ID: $PARESH" message "Processing File: $master_file" message "Parallel Count: $parallel_count" message "Log File: $logfile" message "-" cp $master_file $workfile while test $parallel_count -gt 0 do if [ ! -s $workfile ] then message "All Work Completed - Stopped Spawning at $parallel_count" break fi $0 $parallel_count & message "Spawned Slave $parallel_count [pid $!]" parallel_count=`expr $parallel_count - 1` done wait message "All Done" return 
}

# main
if [ "$PARESH" != "" ]; then 
workfile=/tmp/paresh.work.$PARESH 
lockfile=/tmp/paresh.lock.$PARESH 
lklogfile=/tmp/paresh.lklog.$PARESH
ogfile=/tmp/paresh.log.$PARESH 
errfile=/tmp/paresh.err.$PARESH 
paresh_slave $* 
else 
PARESH="$$"; export PARESH 
workfile=/tmp/paresh.work.$PARESH 
lockfile=/tmp/paresh.lock.$PARESH 
lklogfile=/tmp/paresh.lklog.$PARESH
logfile=/tmp/paresh.log.$PARESH 
errfile=/tmp/paresh.err.$PARESH 
rm -f $errfile 
paresh_driver $* 
rm -f $workfile 
rm -f $lklogfile 
if [ -f $errfile ] 
then
message "*************************************************"
message "FINAL ERROR SUMMARY. Errors logged in $errfile"
cat $errfile | tee -a $logfile
message "*************************************************"
exit 1
fi
fi
exit

^D