The Self Exporting Database, how to export the database using DBMS_SCHEDULER
May 21st, 2006 By Karl Reitschuster
I always dreamed of a self exporting oracle database. Now with the new Oracle scheduler it is possible. Due to the possibility to start external programs like shell scripts or any other OS executables you can start an export job exporting your database.
Oracle Scheduler allows definition of Program objects. A program can be a PL/SQL Unit or an external operating system executable. Also the parameter of a scheduler program can be defined. You should use VARCHAR2 or a NUMBER type as parameter. Other types for example BOOLEAN are not supported with DB Console yet but bypassing the GUI on PL/SQL level they are. Any operation of the Oracle scheduler is manged with the DBMS_SCHEDULER package.
You need
- one external program definition
- one job definition per database
Creating the Scheduler Program
You define on the Scheduler Programs/Create Program Page :
- the name of tee program unit
- if it should be enabled
- the programs description
- the program type
- EXECUTABLE
- PL_SQL_BLOCK
- STORED_PROCEDURE
- the executable name – better the command line with optional parameter switches
- always put the output to NULL devices under UNIX to /dev/null and under Windows to nul
- some additional arguments
The DDL for the external program calling a command script starting the Oracle export utility
BEGIN
Dbms_Scheduler.Create_Program(
Program_Name => ‘PROG_EXP_FULL_EXT’,
Program_Type => ‘EXECUTABLE’,
Program_Action => ‘C:\WINDOWS\SYSTEM32\CMD.EXE /C C:\oracle\Scripts\Cmd\exp_full.cmd > nul’,
Number_Of_Arguments => 1,
Enabled => FALSE,
Comments => ‘Full export of the ISISM database’
);Dbms_Scheduler.Define_Program_Argument(
Program_Name => ‘PROG_EXP_FULL_EXT’,
Argument_Name => ‘SID’,
Argument_Position => 1,
Argument_Type => ‘CHAR’,
Default_Value => ”
);
Dbms_Scheduler.Enable(NAME => ‘PROG_EXP_FULL_EXT’);
END;
/
Creating the Job/General Page
On the Scheduler Jobs/Create Job/General Page you start to define a job. Jobs are identified by a job name. A job can execute some PL/SQL directly or in our case the defined program PROG_EXP_FULL_EXT with the value for the Instance for the SID parameter.
Enter following Properties :
- Job name : JOB_EXP_FULL_ISISM
For all job operations the job name will be used - Job owner : SYSTEM
Other user the system could need some additional grants for creating jobs - ENABLED : Yes
Enabled is to be ready to start - Description : The description of the job
- LOGGING LEVEL : FULL
Would do this if your create a new job - Job class
is needed for collecting performance metrics dependent of the job class - AutoDrop : FALSE
I you have a job with run once frequency you should set this property to TRUE - Restartable : FALSE
Creating the Job/Schedule
On the Scheduler Jobs/Create Job/Schedule Page you can set scheduling parameters for your job. Either you specify all Parameters like
- Time Zone
- Repeating : the interval of you job is running
- Available to start : first starting time of the job
- Not Available After : the job is scheduled until the specified time
or you could assign a predefined schedule (a scheduling template) which stores all the parameter mentioned above.
Creating the Job/Options
On the Scheduler Jobs/Create Job/Options Page following job options can be specified
- Priority : now it’s possible to give jobs priorities
- Schedule Limit (minutes) : Time after which a job that has not been run on the scheduled time will be rescheduled. Only valid for repeating jobs
- Maximum Runs : Maximum number of consecutive times this job is allowed to run after which its state will be changed to ‘COMPLETED’
- Maximum Failures : Number of times a job can fail on consecutive scheduled runs before it is automatically disabled
- Job Weight : Job which include parallel queries should set this to the number of parallel slaves they expect to spawn
- Instance Stickiness : A switch which enables load balancing on RAC
The DDL for the job definition. The database is specified by the Parameters SID
BEGIN
Dbms_Scheduler.Create_Job(
Job_Name => ‘"SYSTEM"."JOB_EXP_FULL_ISISM"’,
Program_Name => ‘SYSTEM.PROG_EXP_FULL_EXT’,
Repeat_Interval => ‘FREQ=DAILY;BYHOUR=2;BYMINUTE=10;BYSECOND=0′,
Start_Date => To_Timestamp_Tz(’2006-04-07 Europe/Berlin’,
‘YYYY-MM-DD TZR’),
Job_Class => ‘DEFAULT_JOB_CLASS’,
Comments => ‘Full Export of ISISM database’,
Auto_Drop => FALSE,
Enabled => FALSE
);Dbms_Scheduler.Set_Attribute(
NAME => ‘"SYSTEM"."JOB_EXP_FULL_ISISM"’,
Attribute => ‘logging_level’,
VALUE => Dbms_Scheduler.Logging_Full
);Dbms_Scheduler.Set_Attribute(
NAME => ‘"SYSTEM"."JOB_EXP_FULL_ISISM"’,
Attribute => ‘job_weight’,
VALUE => 1
);Dbms_Scheduler.Set_Job_Argument_Value(
Job_Name => ‘"SYSTEM"."JOB_EXP_FULL_ISISM"’,
Argument_Name => ‘SID’,
Argument_Value => ‘ISISM’
);Dbms_Scheduler.Enable(’"SYSTEM"."JOB_EXP_FULL_ISISM"’);
END;
/
Creating the Job/Scheduler Jobs
After the job is created it appears on the Page Scheduler Jobs. From this point you can monitor the current job status and the history of runs a jobs has done.
Finally the Windows Command script exp_full.sh which is called as external program
SET INSTANCE=%1
SET MODE=FULL
echo %1
pause
SET ORACLE_SID=%INSTANCE%
exp system/***** FULL=Y GRANTS=Y FILE=C:\TEMP\Exp\Exp.Stage\%MODE%_%INSTANCE%.dmp log=C:\TEMP\Exp\Exp.Stage\%MODE%_%INSTANCE%.log COMPRESS=N BUFFER=4000000
Currently i export all local databases driven by dbms_scheduler scheduled on one instance. Even non database tasks could be scheduled with the oracle scheduler.
HTH Karl
Further Documentation