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

SQL query to find whether OdiSendMail is configured or not ODI 12c

In the SQL developer connect to the ODI work repository user (Ex: DEV_ODI_REPO) and run the below query. select SCEN_NAME,TASK_NAME3,DE...