Thursday, March 12, 2015

Schedule a RMAN script using Oracle Scheduler on Windows Platform

 

Step 1: Create rman script file: (c:\rman\rman_validate.rcv in this example)

backup validate check logical database;

Step2: Create rman batch scripts: (c:\rman\rman_validate.bat in this example)

set ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_1
set ORACLE_SID=ORCL112
set NLS_DATE_FORMAT="YYYY-MON-DD HH24:MI:SS"

%ORACLE_HOME%\bin\rman target / log c:\rman\rman_validate.log cmdfile c:\rman\rman_validate.rcv

exit 0

Step 3: Schedule it  (Both Version 1 and Version 2 are working)
Version 1:

begin
    dbms_scheduler.drop_job (
        job_name    => 'DATABASE_VALIDATION_VIA_RMAN');
end;
/
       

begin
    dbms_scheduler.create_job(
        job_name        => 'DATABASE_VALIDATION_VIA_RMAN',
        job_type        => 'EXECUTABLE',
        job_action        => 'c:\rman\rman_validate.bat',
        start_date        => trunc(systimestamp)+4/24,
        repeat_interval        => 'FREQ=DAILY;BYHOUR=4;BYMINUTE=0',
        enabled            => false,
        comments        => 'Database validation job via RMAN validate command');
end;
/
           

begin
    dbms_scheduler.run_job(job_name=>'DATABASE_VALIDATION_VIA_RMAN',USE_CURRENT_SESSION=>true); -- true is default
end;
/

Version 2:

begin
    dbms_scheduler.drop_job (
        job_name    => 'DATABASE_VALIDATION_VIA_RMAN');
end;
/
       

begin
    dbms_scheduler.create_job(
        job_name        => 'DATABASE_VALIDATION_VIA_RMAN',
        job_type        => 'EXECUTABLE',
        job_action        => 'C:\WINDOWS\SYSTEM32\CMD.EXE',
        number_of_arguments    =>3,
        start_date        => trunc(systimestamp)+4/24,
        repeat_interval        => 'FREQ=DAILY;BYHOUR=4;BYMINUTE=0',
        enabled            => false,
        comments        => 'Database validation job via RMAN validate command');

    dbms_scheduler.set_job_argument_value('DATABASE_VALIDATION_VIA_RMAN',1,'/q');
    dbms_scheduler.set_job_argument_value('DATABASE_VALIDATION_VIA_RMAN',2,'/c');
    dbms_scheduler.set_job_argument_value('DATABASE_VALIDATION_VIA_RMAN',3,'c:\rman\rman_validate.bat');

    dbms_scheduler.enable('DATABASE_VALIDATION_VIA_RMAN');
end;
/
           

begin
    dbms_scheduler.run_job(job_name=>'DATABASE_VALIDATION_VIA_RMAN',USE_CURRENT_SESSION=>true); -- true is default
end;
/

 

Suggestions for Windows Platforms:

 

•The OracleJobScheduler Windows Service must be started before external jobs will run.
  (except for jobs in the SYS schema and jobs with credentials).
•The user that the OracleJobScheduler Windows Service runs as must have the "Log on as batch job" Windows privilege.
•A batch file (ending in .bat) cannot be called directly by the Scheduler. Instead a cmd.exe must be used and the name of the batch file passed in as an argument.