Sunday, July 13, 2014

Oracle 11g:Daily export metadata of databases.

Oracle 11g:Daily export metadata of databases.





Sometime we almost forgot about to take backup of sources like PL/SQL.table DDL before updating.

We already have fully sync DR Database and we lost when we had done mistake on our work.Fully sync will also will make big trouble.

So its better to keep backup of metadata of whole database atleast twice a day.

some prerequisite to do setup automate it.

a) Create mount point with sufficient space for one month of db backup.its almost one gb require for one day.SO Mount point should have sufficient space like 30Gb.

b)DB Directory created in db.Full access of SYSTEM User to access directory in read, write mode.

1.Use below script.FULL_METADATA_EXPORT.sh

#!/usr/bin/ksh
######## For Daily Database FULL  metadata export####
set -a

export ORACLE_SID=DBEXPERT
export ORACLE_HOME=/oracle11g/11.2.0.4/products/apps/11.2.0.4
export PATH=$ORACLE_HOME/bin:$PATH

ddy=`date +%a`
dt=`date +%Y%m%d`
echo $dt
cd /metadata/
expdp system/**** CONTENT=METADATA_ONLY COMPRESSION=METADATA_ONLY directory=DATA_PUMP_DIR dumpfile=metadata_$dt.dmp logfile=metadata_$dt.log full=y





status=`tail -10 /metadata/FULL_METADATA_EXPORT.log|grep "successfully"`
echo $status
len=${#status}
echo $len

if [[ $len -eq 0  ]];
then
uuencode /metadata/FULL_METADATA_EXPORT.log DBEXPERT_METADATA.log | mailx -s "DBEXPERT:Metadata export failed" dba@de.com
else
uuencode /metadata/FULL_METADATA_EXPORT.log DBEXPERT_METADATA.log | mailx -s "DBEXPERT:Metadata export successfully completed" dba@de.com
fi;  


No comments:

Post a Comment

Dear User,

Thank you very much for your kind response