Saturday, April 15, 2017

Shell script for taking backup of schemas in a oracle database


export ORACLE_HOME=/app/oracle/product/
export PATH=/usr/local/bin:/bin:/usr/bin:$ORACLE_HOME/bin
export BDIR=/app/oracle/admin/ORCL/dump
export START='date '+%b-%d-%y_%H.%M.%S''

SCHEMAS=`sqlplus -s / as sysdba <<EOF
set heading off;
set linesize 2000;
select LISTAGG(username, ',') WITHIN GROUP (ORDER BY username) from dba_users where username in ('SH', 'SCOTT', 'HR') ;

echo "Performing export of SH,SCOTT,HR schemas in ORCL";
expdp "'/ as sysdba'" DIRECTORY=DATA_PUMP_DIR dumpfile=ORCL_SCHEMA_$START_%U.dmp logfile=log_ORCL_SCHEMA_$START.log schemas=$SCHEMAS  compression=all parallel=4;

if grep -q ORA- $BDIR/log_ORCL_$START.log; then mail -s "Backup of $1 FAILED" < $BDIR/log_ORCL_PARTIAL_$START.log; exit 1; else continue; fi;
find $BDIR/*.dmp -type f -mtime +7 -delete;

Note: Copy the above code to a file and save it with .sh extension and run this script when ever we want to take those schemas backup.

No comments:

Post a Comment

Perfect order of ODI export and import

  Smart Export / Import is the recommended way to achieve such a migration. This is the easiest way to make sure all the dependencies are ta...