expdp full backup thru scheduler

Scheduler and data pump expdb

Oracle Enterprise Edition 11.2.0.2
Linux x86_64

I would like to routinely export some or all of my database as part of my DR strategy. I have always used crontab to call my expdp script on a routine basis. Now I want to change that from using crontab to using Oracle Scheduler.

First I will review the shell script and parfile I use. Then I will use Oracle Scheduler to schedule the execution of this shell script.

This is my full export but with subtle modifications I also export specific schemas on a more frequent basis. I am using csh for this shell script.

This is expdp_full.sh

#!/bin/csh
# set the environment
setenv ORACLE_HOME /app/oracle/product/11.2.0.2
setenv ORACLE_SID grims
setenv ORACLE_BASE /app/oracle
setenv LOGFILE expdp_full.log
setenv DUMPFILE expdp_full.dmp
setenv DMPDIR /scripts/expdp

# expdp does not like files it wants
# to create lying around so I will clean# any up just in case.

# check for existing logfile and
# remove if found.
if ( -e ${DMPDIR}/${LOGFILE} ) then
rm ${DMPDIR}/${LOGFILE}
endif

# check for existing dump file and
# remove if found.if ( -e ${DMPDIR}/${DUMPFILE} ) then
rm ${DMPDIR}/${DUMPFILE}
endif

# change to the working directory
chdir ${DMPDIR}

# call expdp and use the full path to it.
/app/oracle/product/11.2.0.2/bin/expdp parfile=expdp_full.par DUMPFILE=${DUMPFILE} LOGFILE=${LOGFILE} DIRECTORY=DMPDIR

# once the expdp is complete zip it to save space.
# You can use the compress command in UNIX.
zip ${DUMPFILE}.`date +%m%d`.Z ${DUMPFILE}
# Get the log status for email.
set STATUS=`grep Job ${DMPDIR}/${LOGFILE}`
# mail results.
mailx -s "${ORACLE_SID} on `uname -n` FULL export '$STATUS'" email@company.com < ${DMPDIR}/${LOGFILE}

# delete the existing dump file. Zip does not remove it.
if ( -e ${DMPDIR}/${DUMPFILE} ) then
rm ${DMPDIR}/${DUMPFILE}
endif

# rename the log file to preserve it.
if ( -e ${DMPDIR}/${LOGFILE} ) then
mv ${DMPDIR}/${LOGFILE} ${DMPDIR}/${LOGFILE}.`date +%m%d`
endif
exit


The par files. This is both the full expdp and another with specific schemas.
expdp_full.parUSERID="/ as sysdba"
FULL=Y

expdp_users.par
USERID="/ as sysdba"SCHEMAS=SCOTT,HR,APPS

Of course you can stop here and use crontab to execute expdp_full.sh, but I am going to now use the Oracle Scheduler.

First I will create the scheduled job to run every morning at 2:05am. Once it runs successfully for a few days and I like the out put I will update the schedule to run this task once a week.

From sqlplus enter the new scheduled job.

BEGIN
dbms_scheduler.CREATE_JOB (
job_name => 'expdp_full',
job_type => 'EXECUTABLE',
job_action => '/oracle/scripts/expdp/expdp_full.sh',
start_date => to_date('08/14/2013 14:00:00','mm/dd/yyyy hh24:mi:ss'),
repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=5;BYSECOND=0',
end_date => null,
enabled => TRUE,
comments => 'expdp full database');
END;

To update the frequency interval to once a week, Friday morning at 2:05am:

BEGIN
sys.dbms_scheduler.disable( '"SYS"."EXPDP_FULL"' );
sys.dbms_scheduler.set_attribute( name => '"SYS"."EXPDP_FULL"', attribute => 'repeat_interval', value => 'FREQ=WEEKLY;BYDAY=FRI;BYHOUR=2;BYMINUTE=5;BYSECOND=0');
sys.dbms_scheduler.enable( '"SYS"."EXPDP_FULL"' );
END;

Here are some useful queries to examine the scheduler jobs.

SQL> SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS where job_name = 'EXPDP_FULL';
JOB_NAME STATE
------------------------------ ---------------
EXPDP_FULL SCHEDULED

SQL> SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;
no rows selected

The following will show the history of a specific job and whether it succeeded or not. Just because the job succeeded does not mean the script ran without errors. The next query is an example where this first query said SUCCEEDED yet the script failed to run.

SELECT to_char(log_date, 'DD-MON-YY HH24:MM:SS') TIMESTAMP, job_name,
job_class, operation, status FROM USER_SCHEDULER_JOB_LOG
WHERE job_name = 'EXPDP_FULL' ORDER BY log_date;

Show more detail about an job history. This is the output before I gave the full path in my shell script to extdp.

SELECT to_char(log_date, 'DD-MON-YY HH24:MM:SS') TIMESTAMP, job_name, status,
SUBSTR(additional_info, 1, 40) ADDITIONAL_INFO
FROM user_scheduler_job_run_details
WHERE job_name = 'EXPDP_FULL'
ORDER BY log_date;

14-AUG-13 14:02:02 EXPDP_FULL SUCCEEDED STANDARD_ERROR="expdp: Command not found