We usually schedule expdp jobs in a standard shell script. But the same can be achieved using dbms_scheduler utility also.
Below are steps for scheduling expdp job.
Here we will schedule a full expdp backup of the database, which will run daily at 11:30 HRS.
1. First, prepare the parfile
cat expdp_tab.par userid=system/oracle dumpfile=FULL_DB.dmp logfile=FULL_DB.log directory=T full=y
2. Now create the credential for os user:(oracle)
BEGIN dbms_credential.create_credential ( CREDENTIAL_NAME => 'ORACLEOSUSER', USERNAME => 'oracle', PASSWORD => 'oracle@123', DATABASE_ROLE => NULL, WINDOWS_DOMAIN => NULL, COMMENTS => 'Oracle OS User', ENABLED => true ); END;
If you are using any user other than oracle, then create a credential using that Userid and password.
3. Now create the job:
Begin Dbms_scheduler.create_job ( job_name => 'BACKUP_FULLDB', job_type => 'EXTERNAL_SCRIPT', job_action => '/oracle/app/oracle/product/12.1.0/dbhome_1/bin/expdp parfile=/export/home/oracle/expdp_tab.par', start_date => sysdate, Repeat_interval =>'FREQ=DAILY;BYHOUR=11; BYMINUTE=25', enabled => TRUE, credential_name=>'ORACLEOSUSER'); end; /
Above job is scheduled to run daily at 11:30 hrs
4. Monitor the job status and log:
set pagesize 299 set lines 299 col JOB_NAME for a24 col actual_start_date for a56 col RUN_DURATION for a34 select job_name,status,actual_start_date,run_duration from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='&JOB_NAME'; select * from DBA_SCHEDULER_JOB_LOG where job_name='&JOB_NAME';
SEE ALSO – Scheduler jobs in oracle
SEE ALSO – Useful crsctl commands
SEE ALSO – Useful srvctl commands
3 thoughts on “Schedule expdp job in dbms_scheduler”
not working bro I followed the steps as mentioned.
job running run in dba_datapump_jobs but not export generating and not even log.. what might be wrong
Please check in dba_schedulere_job and alert log
Thanks , it worked for me.
I have one query i want my dumpfile and logfile names to be concatenated with the date and time. If you have any idea how to do that, it will be very helpful.