#!/bin/ksh #******************************************************************************* # KSH PROGRAM #******************************************************************************* # # PROGRAM ID : expdp.ksh # #D DESCRIPTION : Oracle Data Pump Script #D # # INPUT PARAMETERS : none # # OUTPUT PARAMETERS : none # # # INPUT FILES : none # # OUTPUT FILES : $s_dpdir/* # # LOG FILES : $s_dpdir/* # # # SPECIAL LOGIC NOTES : none # #******************************************************************************* # MODIFICATION LOG # # # DATE SE # DESCRIPTION # --------------------------------------------------------------------------- # Fri Jan 30 14:45:58 CET 2015 (HRu) # initial version # --------------------------------------------------------------------------- # #******************************************************************************* export s_debug=0 # initiate debugging toggle (0=nodebug/1=debug) #******************************************************************************* # INITIALIZATION #******************************************************************************* export s_parmcount=$# export s_parmcountExpected=4 # initiate expected number of input parameters #----------------------------------------------------------------------------------------- # check if parameters have been provided #----------------------------------------------------------------------------------------- if [[ $s_parmcount -eq $s_parmcountExpected ]] then export s_instance=$(echo $1 | tr '[:lower:]' '[:upper:]') export s_schema=$(echo $2 | tr '[:lower:]' '[:upper:]') export s_user=$3 export s_pw=$4 else print -u2 "`basename $0` - USAGE: `basename $0` !" exit 1 fi #----------------------------------------------------------------------------------------- # initialization #----------------------------------------------------------------------------------------- export s_myName=`basename $0` # remember my name export s_myNameBase=$(echo ${s_myName} | sed -e's/\.ksh//') export s_sek=`date +'%S'` export s_min=`date +'%M'` export s_std=`date +'%H'` export s_tag=`date +'%d'` export s_mon=`date +'%m'` export s_jhr=`date +'%Y'` export s_now="$s_jhr$s_mon$s_tag$s_std$s_min$s_sek" export s_rc=0 # initiate return code export s_dpdir=/app/orcl/oradata/ORCL/dump/tmp/dmp/${s_now} mkdir -p $s_dpdir 2>/dev/null chmod 777 $s_dpdir export s_namestring=${s_instance}_${s_schema} export s_oratab=/etc/oratab export ORACLE_SID=${s_instance} grep ${ORACLE_SID} ${s_oratab} >/dev/null 2>&1 ;s_rc=$? if [[ $s_rc -ne 0 ]] then print -u2 "ERROR: <${ORACLE_SID}> not found in ORATAB <${s_oratab}>!" exit $s_rc fi #-------------------------------------------------------------------------------- # create parfile #-------------------------------------------------------------------------------- export s_parfile=${s_dpdir}/expdp_${s_namestring}.par echo "CONTENT=ALL" >${s_parfile} echo "DUMPFILE=dpump_dir:dp_${s_namestring}1%U.dmp,dpump_dir:dp_${s_namestring}2%U.dmp,dpump_dir:dp_${s_namestring}3%U.dmp,dpump_dir:dp_${s_namestring}4%U.dmp" >>${s_parfile} echo "FILESIZE=4G" >>${s_parfile} echo "PARALLEL=4" >>${s_parfile} echo "LOGFILE=dpump_log:dp_${s_namestring}.log" >>${s_parfile} echo "JOB_NAME=dp_${s_namestring}" >>${s_parfile} echo "SCHEMAS=${s_schema}" >>${s_parfile} #echo "ESTIMATE_ONLY=Y" >>${s_parfile} #-------------------------------------------------------------------------------- # clear datapump directory #-------------------------------------------------------------------------------- rm -rf $s_dpdir/*.dmp 2>/dev/null #-------------------------------------------------------------------------------- # get current timestamp #-------------------------------------------------------------------------------- fun_TimeStamp () { s_TS_sek=`date +'%S'` s_TS_min=`date +'%M'` s_TS_std=`date +'%H'` s_TS_tag=`date +'%d'` s_TS_mon=`date +'%m'` s_TS_jhr=`date +'%Y'` #s_TS_nowStrg="$s_TS_jhr$s_TS_mon$s_TS_tag$s_TS_std$s_TS_min$s_TS_sek" s_TS_nowDate="$s_TS_jhr-$s_TS_mon-$s_TS_tag $s_TS_std:$s_TS_min:$s_TS_sek" echo $s_TS_nowDate } #******************************************************************************* # here we go... #******************************************************************************* echo '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' echo `fun_TimeStamp` - Start $s_myName $ORACLE_SID #-------------------------------------------------------------------------------- # run datapump export #-------------------------------------------------------------------------------- sqlplus "${s_user}/${s_pw}" <<-EOSQL 1> ${s_dpdir}/expdp_prepare_${s_schema}.log 2>&1 set pages 1000 set lines 150 prompt drop directory dpump_dir; drop directory dpump_dir; prompt drop directory dpump_log; drop directory dpump_log; prompt create or replace directory dpump_dir as '$s_dpdir'; create or replace directory dpump_dir as '$s_dpdir'; prompt create or replace directory dpump_log as '$s_dpdir'; create or replace directory dpump_log as '$s_dpdir'; prompt create tablespace dp_exp create tablespace dp_exp logging datafile '/${s_dpdir}/dp_exp.dbf' size 200m autoextend on next 100m maxsize 2000m extent management local segment space management auto; prompt drop user dpexp; drop user dpexp; prompt create user dpexp identified by dpexp; create user dpexp identified by dpexp; grant create table to dpexp; grant connect to dpexp; grant exp_full_database to dpexp; alter user dpexp default role all; alter user dpexp default tablespace dp_exp temporary tablespace temp account unlock; alter user dpexp quota unlimited on dp_exp; -- grant all on ${s_schema}.${s_schema} to dpexp; grant read, write on directory dpump_dir to dpexp; grant read, write on directory dpump_log to dpexp; prompt commit; commit; quit EOSQL #-------------------------------------------------------------------------------- # PUMP! #-------------------------------------------------------------------------------- expdp dpexp/dpexp parfile=$s_parfile #-------------------------------------------------------------------------------- # cleanup #-------------------------------------------------------------------------------- sqlplus "/ as sysdba" <<-EOSQL 1>> ${s_dpdir}/expdp_prepare_${s_schema}.log 2>&1 set pages 1000 set lines 150 prompt cleanup drop directory dpump_dir; drop directory dpump_dir; prompt cleanup drop directory dpump_log; drop directory dpump_log; prompt cleanup drop tablespace dp_exp drop tablespace dp_exp including contents and datafiles; prompt cleanup drop user dpexp; drop user dpexp cascade; quit EOSQL #******************************************************************************* # FINE #******************************************************************************* echo `fun_TimeStamp` - End ${s_myName} ${ORACLE_SID} echo '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++'