DBMS SCHEDULER for Data Pump

How to take logical database backup automatically using Data pump .

We can use many method to achieve this one. i choosed to play with DBMS SCHEDULER.

Step 1:

Connect  sys user.

sql> grant execute on dbms_lock to system;

Connect System user .

Step 2 : Create Directory

SQL> CREATE DIRECTORY NEW_DIR AS ‘D:\ORACLE\DUMP’;

Directory created.

SQL> GRANT READ,WRITE  ON DIRECTORY  NEW_DIR TO SYSTEM;

Grant succeeded.

SQL> COMMIT;

Commit complete.

Step 3:  Create Package

SQL> CREATE OR REPLACE PACKAGE fullexport
  2  IS
  3  PROCEDURE exp;
  4  END fullexport;
  5  /

Package created.

Step 4 : Create  Package Body

SQL> CREATE OR REPLACE PACKAGE BODY fullexport
  2  IS
  3  h1 NUMBER := NULL; PROCEDURE exp
  4  IS
  5  BEGIN
  6  BEGIN
  7  DBMS_LOCK.sleep(30);
  8  END;
  9  –DO THE EXPORT
 10  BEGIN
 11  h1 := DBMS_DATAPUMP.open(
 12  operation => ‘EXPORT’,
 13  job_mode => ‘FULL’,
 14  job_name => ‘MYTEST’,
 15  version => ‘COMPATIBLE’);
 16  DBMS_DATAPUMP.add_file(
 17  handle => h1,
 18  filename => ‘FULLEXP.LOG’,
 19  directory => ‘NEW_DIR’,
 20  filetype => 3);
 21  DBMS_DATAPUMP.add_file(
 22  handle => h1,
 23  filename => ‘MYTEST.DMP’,
 24  directory => ‘NEW_DIR’,
 25  filetype => 1);
 26  DBMS_DATAPUMP.START_JOB(h1);
 27  END;
 28  END;
 29  END fullexport;
 30  /

Package body created.

Step 5: Run procedure

SQL> exec fullexport.exp;

PL/SQL procedure successfully completed.

Step 6: Create DBMS_SCHEDULER

SQL> BEGIN
  2  DBMS_SCHEDULER.CREATE_JOB_CLASS(
  3  job_class_name => ‘BACKUP’,
  4  resource_consumer_group => ‘DEFAULT_CONSUMER_GROUP’,
  5  comments => ‘BACKUP, EXPORT ETC’);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2  DBMS_SCHEDULER.CREATE_JOB(
  3  job_name => ‘TEST’,
  4  job_type => ‘STORED_PROCEDURE’,
  5  job_action => ‘FULLEXPORT.EXP’,
  6  start_date => ’16-FEB-10 01:00:00 PM’,
  7  repeat_interval => ‘FREQ=DAILY;BYHOUR=13’,
  8  job_class => ‘BACKUP’,
  9  comments => ‘FULL EXPORT’);
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2  DBMS_SCHEDULER.ENABLE(‘TEST’);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>

VIEW :

DBA_DATAPUMP_JOBS

USER_TAB_PRIVS