Unix shell script to export data from oracle schemas in slices using exp, gzip and split

De Notas pessoais





#!/usr/bin/ksh
 
EXP_PATH="./"; export EXP_PATH;                                         # output directory
DATA="$(date +%Y-%m-%d_%H%M%S)"; export DATA;                           # date component for the file names
 
ORACLE_SID=ORAPRD                               ; export ORACLE_SID     # oracle instance name
ORACLE_HOME=/u01/app/oracle/product/11.1.0.7/db ; export ORACLE_HOME    # oracle home
LIBPATH=$ORACLE_HOME/lib                        ; export LIBPATH        #
INFRA=$ORACLE_HOME/infra                        ; export INFRA          #
NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252          ; export NLS_LANG       # oracle national language to be used by the exporter
PATH=$PATH:$ORACLE_HOME/bin:                    ; export PATH           # add oracle bin to path
LIBPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32     ; export LIBPATH
 
###------------------------------------------------------------------------------
### Function: export isolated schemas
###------------------------------------------------------------------------------
export_owner() {
BKORIG=${1}
BKOWNR=$(echo "$BKORIG" | tr '\$' 'S')
 
EXPFILE=$(echo "$EXP_PATH/exp_${DATA}_${ORACLE_SID}_${BKOWNR}.dmp." | tr "\$" "S"); export EXPFILE
EXPLOGF=$(echo "$EXP_PATH/exp_${DATA}_${ORACLE_SID}_${BKOWNR}.log"  | tr "\$" "S"); export EXPLOGF
 
PIPE_CMPRSS=${EXP_PATH}/${BKOWNR}_PIPE_TO_GZIP; rm -f $PIPE_CMPRSS; /usr/sbin/mknod $PIPE_CMPRSS p
PIPE_SPLITS=${EXP_PATH}/${BKOWNR}_PIPE_TO_SPLT; rm -f $PIPE_SPLITS; /usr/sbin/mknod $PIPE_SPLITS p
 
/usr/bin/split -b 2000m -a 3 $PIPE_SPLITS $EXPFILE &
/usr/bin/gzip  < $PIPE_CMPRSS > $PIPE_SPLITS &
 
exp / owner="$BKORIG" file=$PIPE_CMPRSS log=$EXPLOGF buffer=100000 statistics=none consistent=y
 
rm -f $PIPE_CMPRSS
rm -f $PIPE_SPLITS
 
}
 
export_owner  "SCHEMA_NAME"


This script will create files with the following name layout:

-rw-r--r--    1 oracle   dba           43432 Sep 23 22:07 exp_2011-09-23_220622_ORAPRD_SCHEMA01.log
-rw-r--r--    1 oracle   dba        59482813 Sep 23 22:07 exp_2011-09-23_220622_ORAPRD_SCHEMA01.dmp.aaa
-rw-r--r--    1 oracle   dba           10756 Sep 23 22:17 exp_2011-09-23_221051_ORAPRD_SCHEMA02.log
-rw-r--r--    1 oracle   dba      2147483648 Sep 23 22:17 exp_2011-09-23_221051_ORAPRD_SCHEMA02.dmp.aaa
-rw-r--r--    1 oracle   dba      2147483648 Sep 23 22:23 exp_2011-09-23_221051_ORAPRD_SCHEMA02.dmp.aab
-rw-r--r--    1 oracle   dba      2147483648 Sep 23 22:28 exp_2011-09-23_221051_ORAPRD_SCHEMA02.dmp.aac
-rw-r--r--    1 oracle   dba         6837658 Sep 23 22:33 exp_2011-09-23_221051_ORAPRD_SCHEMA02.dmp.aad

Note that the maximum size of each slice will be 2000m (split parameter -b 2000m) and will be idenfitied as aaa, aab, aac, etc (split parameter -a 3).

To import those files, you will need to group slices in only one file because the old oracle "exp" does not support multiple files as the oracle "expdp" does. To achieve this, use the command below:

cat exp_2011-09-23_221051_ORAPRD_SCHEMA02.dmp.aaa \
    exp_2011-09-23_221051_ORAPRD_SCHEMA02.dmp.aab \
    exp_2011-09-23_221051_ORAPRD_SCHEMA02.dmp.aac \
    exp_2011-09-23_221051_ORAPRD_SCHEMA02.dmp.aad   > exp_2011-09-23_221051_ORAPRD_SCHEMA02.dmp.gz

And use the output file "exp_2011-09-23_221051_ORAPRD_SCHEMA02.dmp.gz" as input file for yout import.

For the export files with only one slice, you only need to rename the file as follows:

mv exp_2011-09-23_220622_ORAPRD_SCHEMA01.dmp.aaa exp_2011-09-23_220622_ORAPRD_SCHEMA01.dmp.gz

Hope this helps...

Roberto Baronas


Ferramentas pessoais
Imprimir/exportar

contador de visitas