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

De Notas pessoais

Edição feita às 03h58min de 19 de maio de 2012 por Einstein (disc | contribs)
(dif) ← Versão anterior | ver versão atual (dif) | Versão posterior → (dif)

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/ ; 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
### Function: export isolated schemas
export_owner() {
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
/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
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

contador de visitas