Windows bat file from oracle scheduler

DB - Oracle 11.2.0.3
OS - Windows server 2008

In the process of migrating a database from 10g to 11g on windows, it was required for us to create rman backup schedule  using DBMS_SCHEDULER.
In 10g also, we were using DBMS_SCHEDULER so we were hoping to use same job definition in 11g, but with 11g there has been changes in the dbms_scheduler.
In 10g we have used a .bat script which will run a rman script inside it to do a full backup of database.
So in 10g job defined with job_type “EXCUTABLE” and job_action pointing to the .bat file did the trick.
In 11g also we needed to do the same. But mainly two differences were there.

1 – We can not directly provide .bat file for the job_action.
2    If OracleJobScheduler<DBID>  windows service is not started, Then “Credentials” must be used

So the solution was the below job definition with use of credentials and also using arguments for providing the location of bat file.

 begin  
  dbms_scheduler.create_schedule(  
     schedule_name  => 'sch_back',  
     repeat_interval => 'FREQ=WEEKLY;BYDAY=THU;BYHOUR=09;BYMINUTE=00; BYSECOND=00',  
     comments    => 'Repeats weekly');  
  dbms_scheduler.create_program  
   ( program_name  => 'pro_back',  
     program_type  => 'EXECUTABLE',  
     number_of_arguments => 3,  
     program_action => 'C:\windows\system32\cmd.exe',  
     enabled    => FALSE,  
     comments    => 'Backup database.'  
   );  
  dbms_scheduler.create_job (  
   job_name=>'job_back',  
   program_name =>'pro_back',  
     schedule_name=> 'sch_back',  
   enabled   => FALSE,  
     comments   => 'weekly_back'  
  );  
 dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(  
 program_name=>'pro_back',  
 argument_position=>1,  
 argument_type=>'VARCHAR2',  
 DEFAULT_VALUE=>'/q');  
 dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(  
 program_name=>'pro_back',  
 argument_position=>2,  
 argument_type=>'VARCHAR2',  
 DEFAULT_VALUE=>'/c');  
 dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(  
 program_name=>'pro_back',  
 argument_position=>3,  
 argument_type=>'VARCHAR2',  
 DEFAULT_VALUE=>'D:\backup\RMAN_BACKUP\scripts\ctxds_back.bat');  
 DBMS_SCHEDULER.set_attribute('job_back', 'credential_name', 'BACK_CREDENTIALA');  
 dbms_scheduler.enable('pro_back');  
 dbms_scheduler.enable('job_back');  
 end;  
 /       
Although we have used different program, and separate schedule to create the job, same can be done using single job which include the job action and scheduler inside it without using programs.

Comments

Popular posts from this blog

ORA-16433: The database or pluggable database must be opened in read/write

Wait for unread message on broadcast channel - Blocking Sessions

ORA-14126 - While splitting max value partition