EXPDP automation in shell scripts

This script is created for SUN Solaris (no change requried) and RHEL (a small change will be required).
it will do
1. create dump file of full db only metadata.
2. create dump of some particular tables.
3. tar and zip that file
4. ftp the files in some other safe places
5. finally delete old dump files from both servers.


ORACLE_BASE=/oracle/orabase; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=SHAONDB; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH

CURR_MONTH=`date +"%b_%Y"|tr "[:lower:]" "[:upper:]"`
CURR_DATE=`date +"%d_%b_%Y"|tr "[:lower:]" "[:upper:]"`
## 7days means 168 hour ###
DAYAGO=`TZ=GMT+168 date +"%d_%b_%Y"|tr "[:lower:]" "[:upper:]"`
## in rhel it will be DAYAGO=`date -d '7 day ago' +"%d_%b_%Y"|tr "[:lower:]" "[:upper:]"` ##
BASE_DIR="/backup/DUMP_ORACLE/DUMP_RAFMDB/DATABASE_DUMP"
DIR_NAME="$BASE_DIR/$CURR_MONTH/$CURR_DATE"
USER_PASS_DB="user/passwd@SHAONDB"

echo "-- ---------------------------- --"
echo "-- CREATING DIRECTORY $DIR_NAME --"
echo "-- ---------------------------- --"

mkdir -p $DIR_NAME
`rm $DIR_NAME/*.dmp`

echo "-- --------------------------------------------------------- --"
echo "-- CREATING PAR FILE FOR FULL SHAONDB DB with metadata ----------"
echo "-- --------------------------------------------------------- --"
SHAONDB_FULL_META_FILE="SHAONDB_FULL_META_"$CURR_DATE


echo "CREATING EXPDP PAR FILE ....."

echo "DIRECTORY=BACKUP_DUMP_DIR" > $DIR_NAME/"EXPDP_"$SHAONDB_FULL_META_FILE".par"
echo "DUMPFILE="$SHAONDB_FULL_META_FILE".dmp" >> $DIR_NAME/"EXPDP_"$SHAONDB_FULL_META_FILE".par"
echo "FULL=y" >> $DIR_NAME/"EXPDP_"$SHAONDB_FULL_META_FILE".par"
echo "CONTENT=METADATA_ONLY" >> $DIR_NAME/"EXPDP_"$SHAONDB_FULL_META_FILE".par"
echo "LOGFILE=EXPDP_"$SHAONDB_FULL_META_FILE".log" >> $DIR_NAME/"EXPDP_"$SHAONDB_FULL_META_FILE".par"

echo "CREATING IMPDP PAR FILE ....."

echo "DIRECTORY=BACKUP_DUMP_DIR" > $DIR_NAME/"IMPDP_"$SHAONDB_FULL_META_FILE".par"
echo "DUMPFILE="$SHAONDB_FULL_META_FILE".dmp" >> $DIR_NAME/"IMPDP_"$SHAONDB_FULL_META_FILE".par"
echo "EXCLUDE=STATISTICS" >> $DIR_NAME/"IMPDP_"$SHAONDB_FULL_META_FILE".par"
echo "LOGFILE=IMPDP_"$SHAONDB_FULL_META_FILE".log" >> $DIR_NAME/"IMPDP_"$SHAONDB_FULL_META_FILE".par"


echo "-- ------------------------------------------------------------------------------------------ --"
echo "-- CREATING PAR FILE FOR PRODUSER TABLES --"
echo "-- ------------------------------------------------------------------------------------------ --"
PRODUSER_TABLES_FILE="PRODUSER_TABLES_"$CURR_DATE

echo "CREATING EXPDP PAR FILE ....."

echo "DIRECTORY=BACKUP_DUMP_DIR" > $DIR_NAME/"EXPDP_"$PRODUSER_TABLES_FILE".par"
echo "DUMPFILE="$PRODUSER_TABLES_FILE".dmp" >> $DIR_NAME/"EXPDP_"$PRODUSER_TABLES_FILE".par"
echo "TABLES=PRODUSER.CELL_BASE,PRODUSER.TAB_1,PRODUSER.TAB_27,PRODUSER.TAB_4,PRODUSER.TAB_ICXIGW,PRODUSER.TAB_ICXIGW_IT,PRODUSER.TAB_INTRCNCT,PRODUSER.TAB_INTRCNCT_IT,PRODUSER.POSTPAID_RATING_SUMMARY" >> $DIR_NAME/"EXPDP_"$PRODUSER_TABLES_FILE".par"
echo "CONTENT=DATA_ONLY" >> $DIR_NAME/"EXPDP_"$PRODUSER_TABLES_FILE".par"
echo "LOGFILE=EXPDP_"$PRODUSER_TABLES_FILE".log" >> $DIR_NAME/"EXPDP_"$PRODUSER_TABLES_FILE".par"

echo "CREATING IMPDP PAR FILE ....."

echo "DIRECTORY=BACKUP_DIR" > $DIR_NAME/"IMPDP_"$PRODUSER_TABLES_FILE".par"
echo "DUMPFILE="$PRODUSER_TABLES_FILE".dmp" >> $DIR_NAME/"IMPDP_"$PRODUSER_TABLES_FILE".par"
echo "TABLES==PRODUSER.CELL_BASE,PRODUSER.TAB_1,PRODUSER.TAB_27,PRODUSER.TAB_4,PRODUSER.TAB_ICXIGW,PRODUSER.TAB_ICXIGW_IT,PRODUSER.TAB_INTRCNCT,PRODUSER.TAB_INTRCNCT_IT,PRODUSER.POSTPAID_RATING_SUMMARY" >> $DIR_NAME/"IMPDP_"$PRODUSER_TABLES_FILE".par"
echo "REMAP_SCHEMA=PRODUSER:DEVUSER" >> $DIR_NAME/"IMPDP_"$PRODUSER_TABLES_FILE".par"
echo "CONTENT=DATA_ONLY" >> $DIR_NAME/"IMPDP_"$PRODUSER_TABLES_FILE".par"
echo "LOGFILE=IMPDP_"$PRODUSER_TABLES_FILE".log" >> $DIR_NAME/"IMPDP_"$PRODUSER_TABLES_FILE".par"


echo "-- ------------------------------------------ --"
echo "-- RECREATE DIRECTORY OBJECT BACKUP_DUM_DIR ----- --"
echo "-- ------------------------------------------ --"

echo "RECREATING DIRECTORY OBJECT BACKUP_DUMP_DIR to $DIR_NAME"
SQL_COMMAND="CREATE OR REPLACE DIRECTORY BACKUP_DUMP_DIR AS '$DIR_NAME';"
OUTPUT=`sqlplus $USER_PASS_DB << EOF
$SQL_COMMAND
disc
exit
<< EOF`

echo "$OUTPUT"

echo "-- ------------------ --"
echo "-- START EXPDP ----- --"
echo "-- ------------------ --"

echo "EXPDP: START:"`date`" EXPORTING SHAONDB FULL DB Metadata only ...."
expdp $USER_PASS_DB parfile=$DIR_NAME/"EXPDP_"$SHAONDB_FULL_META_FILE".par"
echo "EXPDP: END:"`date`

echo "EXPDP: START:"`date`" EXPORTING PRODUSER TAB_ TABLES ...."
expdp $USER_PASS_DB parfile=$DIR_NAME/"EXPDP_"$PRODUSER_TABLES_FILE".par"
echo "EXPDP: END:"`date`

echo "---------------------"
echo "--tar the directory--"
echo "---------------------"

cd "$BASE_DIR/$CURR_MONTH"
`tar -cvf "$CURR_DATE".tar "$CURR_DATE"`
`gzip "$CURR_DATE".tar`

echo "----------------------------"
echo "--- FTP to 11.11 (OthersafeServer)---"
echo "----------------------------"

cd "$BASE_DIR/$CURR_MONTH"

TUSER="ftpuser"
TPASSWD="ftppasswd"
ftp -n 192.168.11.11<quote USER $TUSER
quote PASS $TPASSWD
bin
prompt
cd /data2/oracle/BACKUP/BKP_SHAONDB
mput "$CURR_DATE".tar.gz
delete "$DAYAGO".tar.gz
bye
EOF

echo " ------ deleting the tar file "$CURR_DATE".tar ---------"
`rm "$CURR_DATE".tar.gz`
echo " ----- deleting 7 day before data "$DAYAGO" ------"
`rm -rf "$DAYAGO"`

echo "-- -------------------- --"
echo "-- END OF SCRIPT ----- --"
echo "-- -------------------- --"

Comments

Popular posts from this blog

Restoring Master, Model and MSDB from Netbackup

Oracle Job Operations