ORMB Batch Scheduling Job Hangs At F1-FLUSH with “PL/SQL Lock Timer” Wait Event

Overnight ORMB Batch Scheduling Job has been running for more than twenty hours, user wants to know why ?

1) Check which job at what step is still running and hanging.

SQL> select OWNER,JOB_NAME,JOB_SUBNAME,SESSION_ID,ELAPSED_TIME,CPU_USED 
     from dba_scheduler_running_jobs;

OWNER   JOB_NAME   JOB_SUBNAME  SESSION_ID ELAPSED_TIME  CPU_USED
------- ---------- ------------ ---------- ----------- ---------------
SCHADM  DAILY_JOBS F1_FLUSH_JOB 1006       +000 21:47:00.08 +00000:00:22.15

2) Check session status.

SQL> select USERNAME,STATUS,EVENT,sql_id,LAST_CALL_ET 
      from gv$session where sid=1006;

USERNAME  STATUS EVENT               SQL_ID           LAST_CALL_ET
--------- ------ ------------------  ---------------- -----------
SCHADM    ACTIVE PL/SQL lock timer   fnfa105xssacx    78949

3) Check which sql is the running with ‘PL/SQL lock timer’.

SQL> set long 20000
SQL> select SQL_FULLTEXT from v$sql where sql_id='fnfa105xssacx';

SQL_FULLTEXT
---------------------------------------------------------------------
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname
VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start
TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME
 ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window
_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(
14) := :chainid; credential_owner varchar2(30) := :credown; credential_name
varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destinat
ion_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_i
d number := :log_id; BEGIN DECLARE
 BATCH_CODE CHAR(8);
 USER_ID CHAR(8);
 THREAD_COUNT NUMBER;
 BUSINESS_DATE DATE;
 MAX_TIMEOUT_MINS NUMBER;
 RERUN_NUMBER NUMBER;
 THREAD_POOL VARCHAR2(254);
 MAIL_ID VARCHAR2(254);
 MIN_RUN_STATUS CHAR(2);
 RAISE_ERROR BOOLEAN;
 THREAD_NOTIFICATIONS BOOLEAN;
 SOFT_PARM_MAP CISADM.OUAF_BATCH.PARM_MAP_T;
 SOFT_PARM_LIST CISADM.OUAF_BATCH.PARM_LIST_T;
BEGIN
 BATCH_CODE := 'F1-FLUSH';
 USER_ID := 'JAMES';
 --THREAD_COUNT := 0;
 --BUSINESS_DATE := to_date('2016-01-31','yyyy-MM-dd');
 MAX_TIMEOUT_MINS := 0;
 RERUN_NUMBER := 0;
 THREAD_POOL := 'ORMB_BATCH';
 --MIN_RUN_STATUS := NULL;
 --RAISE_ERROR := NULL;
 -- THREAD_NOTIFICATIONS := true;
 --SOFT_PARM_MAP := NULL;
 --SOFT_PARM_LIST := CISADM.OUAF_BATCH.PARM_LIST_T('DIST-THD-POOL','');
 OUAF_BATCH.SUBMIT_JOB(
 BATCH_CODE => BATCH_CODE,
 USER_ID => USER_ID,
 THREAD_COUNT => THREAD_COUNT,
 BUSINESS_DATE => BUSINESS_DATE,
 MAX_TIMEOUT_MINS => MAX_TIMEOUT_MINS,
 RERUN_NUMBER => RERUN_NUMBER,
 THREAD_POOL => THREAD_POOL,
 MIN_RUN_STATUS => MIN_RUN_STATUS,
 RAISE_ERROR => RAISE_ERROR,
 THREAD_NOTIFICATIONS => THREAD_NOTIFICATIONS,
 SOFT_PARM_MAP => SOFT_PARM_MAP,
 SOFT_PARM_LIST => SOFT_PARM_LIST
 );
END; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

4) ‘PL/SQL Lock Timer’ event ( Doc ID 1476088.1 )

"PL/SQL Lock Timer"

This event is called through the DBMSLOCK.SLEEP procedure or USERLOCK.SLEEP procedure. This event is most likely to come from user written stored procedures. The ‘idle’ event ‘PL/SQL lock timer’ is worth watching because it points us in the direction of application response, throughput, and possible coding issues. The PL/SQL lock timer event is, as the command that issues it states, a sleep mandated by the application code. The application is idle and doing nothing. This means that if the application sleeps for a combined interval of 5 minutes, the response to the user or complete run of the application will take at least 5 minutes longer.
While this event does not require resources and thus is not an Oracle resource contention issue, it is our job to provide the quickest response to users and push applications through our system to increase the user’s perceived performance. Most often, sleeps are put into an application for serialization of transactional events or spinning on queues until something happens. When the event occurs or a queue is populated, then the application continues its work. We should ask ourselves why an application is sleeping for any amount of time and provide alternatives if necessary to reduce this idle event. You will gain quicker responses if you trigger an action instead of the action waiting for an event.

This event is called through the DBMSLOCK.SLEEP procedure or 
USERLOCK.SLEEP procedure. This event is most likely to come from 
user written stored procedures.

5) Sent the details to vendor why F1-Flush has “PL/SQL lock timer’  wait event forever.

Unable to load Sfix.ini file: java.io.FileNotFoundException: 22644310\sfix.ini (The system cannot find the path specified)

Configuration file CDXPatch.ini should only include patch IDs to be applied.

SITUATION

Trying to apply the following two patches onto ORMB database, got the error.

08/08/2017 11:52 AM <DIR> 20986229
02/04/2016 04:11 AM <DIR> 22329775
Directory of D:\PATCHES\V4.3.0.1.0-25963443_MultiPlatform\FW.V4.3.0.1.0-25963443\prereqpatches\RollupPack\FW\Database

08/08/2017 11:52 AM <DIR> .
08/08/2017 11:52 AM <DIR> ..
08/08/2017 11:52 AM <DIR> 20986229
02/04/2016 04:11 AM <DIR> 22329775
08/08/2017 11:52 AM 63 CDXPatch.ini
17/05/2016 08:33 PM 1,117 IgDbErr
17/05/2016 08:33 PM 5,115 ouafDatabasePatch.cmd
17/05/2016 08:33 PM 3,846 ouafDatabasePatch.sh
 4 File(s) 10,141 bytes
 4 Dir(s) 26,984,898,560 bytes free

D:\PATCHES\V4.3.0.1.0-25963443_MultiPlatform\FW.V4.3.0.1.0-25963443\prereqpatches\RollupPack\FW\Database>ouafDatabasePatch.cmd
"CMDLINE::: d:\java\bin\java.exe -cp D:\dbpatch_tools\lib\*;D:\dbpatch_tools\config com.oracle.ouaf.database.patch.OUAFPatch "

Folder for 22644310 is missing
File 22644310\sfix.ini is missing
File 22644310\CDXPatch.lang is missing
Folder for 21133947 is missing
File 21133947\sfix.ini is missing
File 21133947\CDXPatch.lang is missing
Folder for 22244788 is missing
File 22244788\sfix.ini is missing
File 22244788\CDXPatch.lang is missing
Folder for 23201172 is missing
File 23201172\sfix.ini is missing
File 23201172\CDXPatch.lang is missing
Folder for 23194604 is missing
File 23194604\sfix.ini is missing
File 23194604\CDXPatch.lang is missing
Unable to load Sfix.ini file: java.io.FileNotFoundException: 22644310\sfix.ini (The system cannot find the path specified)

INVESTIGATION

Configuration file CDXPatch.ini includes other patch ids:

20986229
22329775
21133947
22244788
22644310
23194604
23201172

SOLUTION

Edit configuration file CDXPatch.ini to include the right patch ids which will be applied onto ORMB database.

20986229
22329775

...
..
.

Applying 22329775 ...

Writing to log file: ORMB_DEV001\log22329775.log

-----------------------------------------------------------

--Applying patch 22329775 at 08-09-2017 14:29:10using $LastChangedRevision: 42479 $

---------------------------------------------------------------

--Copying language information

-----------------------------------------------------------

--Patch 22329775 applied successfully at 08-09-2017 14:29:11

Patch applied successfully..

Change the ORMB DB User Password

After the DB user password is changed by DBA, or after the ORMB schema is refreshed from other environment and need keep the password of old one. The DB user password needs to be changed as per Oracle Doc.

For example, CISADM_TEST was refreshed from CISADM_PROD. In ORMB database, the password is still the one of CISADM_PROD.

Subscribe to get access

Read more of this content when you subscribe today.

How to Set Up ORMB Batch Scheduling with Oracle DBMS Scheduler

Oracle DBMS Scheduler is the easiest and cost saving way for ORMB Batch Scheduling.

Download and Apply Oracle DBMS Scheduler Patch

The Patch id is 23639775, which can be downloaded from Oracle support.
This patch is supposed to be installed on to ORMB application server. But we zip it and ship onto a Windows server for installation.

$ 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]: infor@dbalifeeasy.com
Enter email sender address [do-not-reply@company.com]: schadm@dbalifeeasy.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 the following steps to achieve this :

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');

How to Apply Database Component Of a Patch Onto ORMB Database

Always make a backup of CISADM schema or whole database before applying database component of a patch onto ORMB database.

Here is an example of applying an ORMB patch onto ORMB Oracle database.  There working environment is as below ;

Oracle Database 12c
Oracle Client 32bit 12c
ORMB 2.5.0.3
JDK 8

Before applying patching, make a backup of database in any way you prefer:

Set the environment variables:

C:\>set TNS_ADMIN=d:\oracle
C:\>set TOOLSBIN=d:\dbpatch_tools\bin
C:\>set JAVA_HOME=d:\java

Ensure to be able to connect to the database from the application server workstation:

C:\>tnsping ormb
TNS Ping Utility for 32-bit Windows: Version 12.1.0.2.0 - Production on 13-JAN-2017 10:19:11
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
d:\oracle\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ractest-scan.ractest)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVIC
E_NAME = ORMB)))
OK (10 msec)

Unzip the patch files, change to the CDXPatch directory and execute the ouafDatabasePatch.cmd utility. The utility will prompt you for the value of the following parameters:

if it prompts you for the target database type, enter O for Oracle.
Enter the username that owns the schema (e.g., CISADM)
The password for the user (in silent mode)(e.g., XXXXXXX)
Enter the name of the Oracle Database Connection String : //ractest-scan.ractest:1521/ORMB

Below is the output from applying patch 25054594 ( with three child patches -24586912, 24604908, 24963398  ).

D:\PATCHES\p25054594_25030\MultiPlatform\V2.5.0.3.0-25054594_MultiPlatform\database\ORACLE\CDXPatch>ouafDatabasePatch.cmd
"CMDLINE::: d:\java\bin\java.exe -cp d:\dbpatch_tools\lib\*;d:\dbpatch_tools\config com.oracle.ouaf.database.patch.OUAFPatch "
Enter the target database type (O/M/D) [O]: O
Enter the username that owns the schema: CISADM
Enter the password for the CISADM user: XXXXXXX
Enter the name of the Oracle Database Connection String: //ractest-scan.ractest:1521/ORMB
Target Schema is a Production Schema
Ready to process patches, Do you want to continue? (Y/N): Y
Working Directory: ORMB001
***********************************
Setting up language file: ORMB001\CDXPatch.lang
exit value: 0

Applying 24586912 ...
Writing to log file: ORMB001\log24586912.log
-----------------------------------------------------------
--Applying patch 24586912 at 01-13-2017 14:20:49using $LastChangedRevision: 42479 $
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 24586912 applied successfully at 01-13-2017 14:21:20
---------------------------------------------------------------

Applying 24604908 ...
Writing to log file: ORMB001\log24604908.log
-----------------------------------------------------------
--Applying patch 24604908 at 01-13-2017 14:21:20using $LastChangedRevision: 42479 $
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 24604908 applied successfully at 01-13-2017 14:21:31
---------------------------------------------------------------

Applying 24963398 ...
Writing to log file: ORMB001\log24963398.log
-----------------------------------------------------------
--Applying patch 24963398 at 01-13-2017 14:21:31using $LastChangedRevision: 42479 $
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 24963398 applied successfully at 01-13-2017 14:21:31
---------------------------------------------------------------
Patch applied successfully...

Check the successful installations of the patches as per How to Check the Successful Installation of a Database Patch Onto ORMB Database

We can see only the child patches ( 24586912, 24604908, 24963398 ) are stored in repository. The parent patch ID is NOT installed into database patch repository )

SQL>  select sr_no,run_status_flg,END_DTTM 
        from cisadm.ci_ut_instl 
       where sr_no in ( '25054594','24586912','24604908','24963398') 
         and run_status_flg = '02' 
         and END_DTTM IS Not NULL;

SR_NO                          RU END_DTTM
----------------               -- ---------
24586912                       02 13-JAN-17
24604908                       02 13-JAN-17
24963398                       02 13-JAN-17

Finally run Security utility as per How to Run Security Utility in ORMB Multiple Instances Environment