How to Set Up ORMB Batch Scheduling with Oracle DBMS Scheduler

Download and Apply Oracle DBMS Scheduler Patch

$ unzip Oracle_Scheduler_Integration.zip
Archive: Oracle_Scheduler_Integration.zip
 inflating: Getting Started Guide ORMB Batch Scheduling.pdf
 inflating: ORACLE_DBMS_SCHEDULER_SETUP_LINUX.pdf
 inflating: ORMB_Integration_Installer/lastDayPrevMonthJobs.properties
 inflating: ORMB_Integration_Installer/readme.txt
 inflating: ORMB_Integration_Installer/setup.sh
 inflating: ORMB_Integration_Installer/sql/oem_ouaf_package.sql
 inflating: ORMB_Integration_Installer/sql/ouaf_batch_package.sql
 inflating: ORMB_Integration_Installer/sql/setup.sql
 inflating: ORMB_Integration_Installer/sql/user_cisadm_setup.sql
 inflating: ORMB_Integration_Installer/sql/user_schadm_NEW_I.sql
 inflating: ORMB_Integration_Installer/sql/user_schadm_NEW_S.sql
 inflating: ORMB_Integration_Installer/sql/user_schadm_setup_NEW.sql
 inflating: ORMB_Integration_Installer/sql/user_schadm_setup_OEM_N.sql
 inflating: ORMB_Integration_Installer/sql/user_schadm_setup_OEM_Y.sql
 inflating: ORMB_Integration_Installer/sql/user_schadm_setup_UPD.sql
 extracting: ORMB_Integration_Installer/sql/user_schadm_UPD_I.sql
 extracting: ORMB_Integration_Installer/sql/user_schadm_UPD_S.sql

$ cd ORMB_Integration_Installer
oracle@racnode1:/tmp/ORMB_Integration_Installer$ chmod 777 setup.sh
oracle@racnode1:/tmp/ORMB_Integration_Installer$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 16 10:23:42 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> alter session set container=ormbpdb;

Session altered.

SQL> show user
USER is "SYS"
SQL> @?/rdbms/admin/userlock.sql;

Package dropped.
Package created.
Package body created.
Synonym created.
Grant succeeded.
SQL> grant execute,debug on user_lock to cisadm;
Grant succeeded.

oracle@racnode1:/tmp/ORMB_Integration_Installer$ ls -ltr
total 16
-rwxrwxrwx 1 oracle oinstall 417 Mar 14 16:10 setup.sh
-rw-r--r-- 1 oracle oinstall 3485 Mar 14 16:10 readme.txt
-rw-r--r-- 1 oracle oinstall 112 Mar 14 16:10 lastDayPrevMonthJobs.properties
drwxr-xr-x 2 oracle oinstall 4096 Mar 16 10:20 sql

oracle@racnode1:/tmp/ORMB_Integration_Installer$ ./setup.sh

OUAF DBMS Scheduler Setup (4.2.x, 4.3.x)
========================================

NOTES:
1. Package USER_LOCK must be installed before running this script.
That can typically be done by executing @?/rdbms/admin/userlock.sql
2. The schema owner (e.g. CISADM) must have "create user" permission.

Hit enter to start
Enter database service (SID) name: ORMB
Enter schema owner [CISADM]: cisadm
Enter password for user cisadm: xxxxxx
Connecting to cisadm@ORMB ...
Validating user cisadm
Checking USER_LOCK package
Enter scheduler user name [SCHADM]: schadm
User SCHADM does not exist and will be created
Enter password for user SCHADM:
Confirm password for user SCHADM: xxxxxx
Enter default tablespace [CISTS_01]: CISADM
Enter temp tablespace [TEMP]:
Creating user SCHADM
Connecting to SCHADM@ORMB ...

The email server, recipient and sender addresses are required for email
notifications when threads end in error and the thread_notifications
option is in effect. These values can be set here or the defaults
can be accepted and then modified by connecting to user SCHADM and:
a) running statement "DBMS_SCHEDULER.set_scheduler_attribute(...)" to
change the email server and
b) editing scheduler job OUAF_NOTIFY and changing its email notification
settings.

Enter email server name [mailserver.company.com]: smtp.wordpress.com
Enter email recipient address [foo.bar@company.com]: james.huang@wordpress.com
Enter email sender address [do-not-reply@company.com]: schadmWwordpress.com
Use OEM_NOTIFY for thread errors (Y/N)? [N]: Y
Job OEM_NOTIFY will be used for thread error notifications

OUAF DBMS Scheduler setup completed

Schedule Jobs With Scheduler Using Sql Developer

We will follow below steps to achieve this :

  • Create Program ( A program is to be run by schedule which will be ORMB batch job )
  • Create Chain (Steps and Rule, Including BEGIN and END rules)
  • Create Schedule (Schedule when and how many times job is execute)
  • Create Job (A job is collection of metadata which defines program to execute, where to execute as well as its related schedule)
  • Run Job (Run Job to trigger chain which will trigger set of programs<Batch Jobs> with given sequence and rules)
  • Monitoring a job

Create a Program

  1. Right Click on Programs and Select New Program

2) Put all required fields and click on ‘Apply” button to complete the creation.

Or run below sql to create the above program.

BEGIN
 DBMS_SCHEDULER.create_program(
 program_name => 'SCHADM.C1_TXNRP',
 program_action => 'BEGIN OUAF_BATCH.Submit_Job(batch_code => ''C1-TXNRP'', user_id => ''SYSUSER'');END;',
 program_type => 'PLSQL_BLOCK',
 number_of_arguments => 0,
 comments => 'C1-TXNRP',
 enabled => FALSE);

DBMS_SCHEDULER.ENABLE(name=>'SCHADM.C1_TXNRP'); 
END;

Create a Chain

1)Right Click Chain, and select New Chain.

2) Provide the name and description and click on Apply Button to create chain.

or run the below sql to create the chain:

BEGIN
 DBMS_SCHEDULER.create_chain(
 comments => 'SERVICECHGJOBS CHAIN',
 chain_name => 'SCHADM.SERVICECHGJOBS'
 );
 DBMS_SCHEDULER.enable(name=>'SCHADM.SERVICECHGJOBS');
END;

Create a Step

1)Click on created Chain and select Add Step Button

2) Fill all the required fields as shown below (Here select you created program in first step) and click on Apply. This will create the Step.

or run below sqls to create the step the same :

BEGIN
 DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
 CHAIN_NAME => '"SCHADM"."SERVICECHGJOBS"',
 STEP_NAME => '"CMRVRSCH_JOB1"',
 PROGRAM_NAME => '"SCHADM"."CMRVRSCH"' ); 
 DBMS_SCHEDULER.ALTER_CHAIN(
 CHAIN_NAME => '"SCHADM"."SERVICECHGJOBS"',
 STEP_NAME => '"CMRVRSCH_JOB1"',
 ATTRIBUTE => 'PAUSE',
 VALUE => false);
 DBMS_SCHEDULER.ALTER_CHAIN(
 CHAIN_NAME => '"SCHADM"."SERVICECHGJOBS"',
 STEP_NAME => '"CMRVRSCH_JOB1"',
 ATTRIBUTE => 'SKIP',
 VALUE => false);
 DBMS_SCHEDULER.ALTER_CHAIN(
 CHAIN_NAME => '"SCHADM"."SERVICECHGJOBS"',
 STEP_NAME => '"CMRVRSCH_JOB1"',
 ATTRIBUTE => 'RESTART_ON_FAILURE',
 VALUE => false);
 DBMS_SCHEDULER.ALTER_CHAIN(
 CHAIN_NAME => '"SCHADM"."SERVICECHGJOBS"',
 STEP_NAME => '"CMRVRSCH_JOB1"',
 ATTRIBUTE => 'RESTART_ON_RECOVERY',
 VALUE => false);

END;

Create a Rule

1)Switch to Write mode.
2)Right click on any where still in workspace and select Add Rule .

below is the sql which can alternatively create this rule the same.

BEGIN
 DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
 CHAIN_NAME => '"SCHADM"."SERVICECHGJOBS"',
 comments => 'CMRVRSCH_RULE', 
 rule_name => '"CMRVRSCH_RULE"', 
 condition => 'TRUE',
 action => 'START "CMRVRSCH_JOB1"'
 ); 
END;

3) Repeat the same procedure to create all the rest rules:

4) Finally create the END RULE by right clicking the last step -“CM_PSEXT_JOB17” , and then select “Add End Rule”.

5) The final Chain view will be as below:

Create  a  Schedule

1)Right click on Schedule and select New Schedule

2) Choose “REPEATING”, and then click “Repeat Interval” to setup Daily running at 2:00AM.

3)Finally click on Apply will create Schedule.

Create a Job

1)Right click on Job and Select New Job.
2) Fill all required fields as shown below (Select your created chain and schedule respectively).
3) Enable all (job ,schedule, chain, program).

Run a Job

To manually run job execute below command.

BEGIN
DBMS_SCHEDULER.run_job(job_name => 'SERVICECHG_JOB',
use_current_session => false);
END;

Monitor a Job

To monitor a job execute below SQL statement on DB.

 select * from all_scheduler_running_jobs;
 select job_name, chain_name, step_name, state, error_code, completed from all_scheduler_running_chains where job_name = 'SERVICECHG_JOB';

Rerun The Failed Step In a Chain

SQL>exec dbms_scheduler.alter_running_chain('SERVICECHG_JOB', 'C1_TXNRB_JOB2', 'STATE', 'NOT_STARTED');
Advertisements
This entry was posted in dbms_scheduler, ORMB and tagged , , , , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s