Process Manager : What diagnostic steps should I follow and what information should I gather when there is an issue with the Process Manager?

Applies To

• ERP System: Oracle, Peoplesoft, SAP
• MarkView Version: All Versions

Overview

The MarkView Process Manager is the workflow processing engine that pushes items through the MarkView workflow. The process manager is integrated into the database. Each instance of the manager runs independently, enabling you to stop and start one manager instance without affecting others.

The sf_event_alert table in the MarkView Schema stores the system events which have been alerted but have not yet been processed by any managers. With successfully running Managers, you would typically see the total number of events continue to decrease as you check the count of events in this table. The Support Diagnostic Tools -> 'Process (SQLFlow) Manager' -> 'Events:' section can be used to determine the number of pending events. Using the refresh button will help you determine if the number of items in this table are growing larger or getting smaller.

Troubleshooting Steps

If you encounter an issue with the Process Manager, please perform the following steps

Note that many of the troubleshooting steps for the Process Manager can be done using the latest version of the Support Diagnostic Tools. If your version of the Support Diagnostic Tools does not contain a reference to an area listed below please install the latest version of the Support Diagnostic Tools available at the following Support Web Answer:

Ensure that the dbms_job has not failed

• As the Process Manager runs as an Oracle dbms_job the first step towards troubleshooting a Process Manager issue is to ensure that the manager is up and running.
• To determine if the dbms_jobs is running from MarkView Home go to the Support menu and choose 'Process (SQLFlow) Manager' -> 'Process Manager DBMS Job Admin:'. This screen will display all of ther user_jobs running in the MarkView schema. By finding the row in the table which corresponds with the manager ( sf_mgr.RunManagerOnce ) you can determine if the job is broken by checking the 'Broken' column as well as the 'Failures' column.

Starting and Stopping the Process Manager

In general if the dbms_job has failed you will need to stop the existing dbms_job for the Manager

• The easiest way to stop and start the Process Manager is to use the Support Diagnostic Tools 'Process (SQLFlow) Manager' -> 'Process Manager DBMS Job Admin:' section.
• If one or more jobs cannot be started via Support Tools, follow the instructions and use the syntax documented in the MarkView System Administrator's Guide for your MarkView version.

Steps to take if the dbms_job is broken

• If the Process Manager dbms_job is broken (the Broken column indicates a 'Y' for "Yes" for that job), you will need to look in the log file to determine if there are any errors. See the 'Logging' section below to determine the location of the log file.
• If the log file shows the same event being processed over and over again the manager is 'spinning' on an event. If we look at the following example we can see that the manager is continuing to process EventOccurrenceID: 3044705 because of an error:

  07/31/2010 14:07:49: EventOccurrenceID: 3044705
  07/31/2010 14:07:49: EventTypeID: 8
  07/31/2010 14:07:49: WorkItemInstanceID:
  07/31/2010 14:07:49: EventName: UserRoute
  07/31/2010 14:07:49: AlertingUserID: SQLFLOW
  07/31/2010 14:07:49: PropertyTypes:0
  07/31/2010 14:07:49: PropertyValues:0
  07/31/2010 14:07:49: Firing Rule Instance: 29
  07/31/2010 14:07:49: Encountered the following error while firing rule (rule instance id = 29):
  07/31/2010 14:07:49: ORA-01403: no data found
  ORA-20802: Property ('DestinationQueueID') not set for event 07/31/2010 14:08:49: Event Info:
  7/11/2018 Portal Knowledge Base · Customer Portal
  07/31/2010 14:08:49: EventOccurrenceID: 3044705
  07/31/2010 14:08:49: EventTypeID: 8
  07/31/2010 14:08:49: WorkItemInstanceID:
  07/31/2010 14:08:49: EventName: UserRoute
  07/31/2010 14:08:49: AlertingUserID: SQLFLOW
  07/31/2010 14:08:49: PropertyTypes:0
  07/31/2010 14:08:49: PropertyValues:0
  07/31/2010 14:08:49: Firing Rule Instance: 29
  07/31/2010 14:08:49: Encountered the following error while firing rule (rule instance id = 29):
  07/31/2010 14:08:49: ORA-01403: no data found    
  ORA-20802: Property ('DestinationQueueID') not set for event  

  In this example we should backup and remove EventOccurrenceID: 3044705 in order to allow other events to process. This is best accomplished through the Support Diagnostic Tools 'Process (SQLFlow) Manager' -> 'Event Alert Admin:'. From this screen you could backup EventOccurrenceID: 3044705 and then restart the manager. (Note that it may be necessary to remove several events before events begin processing again.) After confirming the manager is processing events the Process Manager log file can be reviewed for root cause analysis.
  If the manager stops processing events after the issue is resolved you will want to review the following Support Web Answer to avoid any performance issues resulting from the build-up of events.

The Process Manager log file is not being updated

• The Process Manager uses Oracle's UTL_FILE parameter for writing the log file.
• To get a listing of the directories which UTL_FILE can write to the following command can be run from the MarkView Schema:
  show parameter utl_file
• Running the following command can ensure that UTL_FILE is able to write to a file successfully. To test this run the following from the MarkView Schema entering the 'LogDirectory' and 'LogFilename' that you are using when starting the manager.

  declare
      file_handle utl_file.file_type;
  begin
      file_handle := utl_file.fopen('&LogDirectory', '&LogFilename', 'w');
      utl_file.put_line(file_handle, 'hello, world');
      utl_file.fclose(file_handle);
  end;

  Executing the preceding PL/SQL block should generate the following message:
  PL/SQL procedure successfully completed
  If the PL/SQL block generated the preceding message, then the setup of UTL_FILE is correct, and you should see the "hello, world" message in the file you selected. If you see an error instead, then UTL_FILE is not set up correctly.
  In some cases, one or more jobs can break if file logging is enabled for one or more jobs and either a permissions issue exists for the log(s), or a file or space limit has been reached on the DB host machine. If this happens, the log(s) will not contain any entries for the running job, the job will most likely show that it has failed. Try turning off file logging and starting the job to verify whether it can process events.

Steps to take if the dbms_job is not broken, but the Manager is not processing events

• If the Process Manager dbms_job is not broken but does not appear to be processing events there are a few things that can be checked.
• Each event that is alerted is assigned to a certain manager for processing. If certain events are not processing the Support Diagnostic Tools -> 'Process (SQLFlow) Manager' -> 'Events:' section can determine what Process Managers are created in your environment and what events are assigned to those managers. In order for an event to be processed by a manager an instance of that manager needs to be started. For example if you have most of your events assigned to ManagerID 1 and a few events set to ManagerID 2 you will need to start two instances of the Manager. One of these instances will be for ManagerID 1 while the other will be for ManagerID 2.
• We have seen cases at several customers where Oracle does not process dbms_jobs although querying the user_jobs table indicates that the job should be processed correctly. In these cases a database bounce was required to allow the dbms_jobs to process succesfully.
• In order to determine if this is related to the dbms_job functionality of Oracle the following PL/SQL code can be run from the MarkView Schema:

  begin
  sf_mgr.RunManagerOnce(ManagerID => 1,
                          UsePipeLogging => FALSE,
                          UseFileLogging => TRUE,
                          LogDirectory => '&LogDirectory',
                          LogFilename => '&LogFilename');
  end;
  /

  If the preceding command returns an error, the error message might be helpful in debugging the problem.
  If the above command is successful you can check to see if any events that were pending when the script was executed are now processed. If the events are processed then this indicates that the MarkView code is working as expected and Oracle's dbms_job functionality is not functioning.
  Executing this statement on a periodic basis until the Oracle issue can be resolved will allow events to process in your MarkView environment.
• Ensure that the Oracle job_queue_processes parameter is high enough for your environment. This parameter should be set to the maximum number of concurrent jobs running in your environment. The following query will confirm the current setting of the job_queue_processes parameter :
  select * from v$parameter where name ='job_queue_processes';

Logging

Enable Debug Logging

• There is no debug logging for the Process Manager. In order to make sure that logging is enabled the UseFileLogging parameter should be set to True when the manager is started.

Disable Debug Logging

• There is no debug logging for the Process Manager. In order to disable all logging the UseFileLogging parameter should be set to False when the manager is started.

Log File Location

• MarkView Versions 5.x and higher
• File location: The file location is determined by the 'LogDirectory' parameter when the manager is started.
• Filename(s): The filename is determined by the 'LogFilename' parameter when the manager is started.

Please note that the full log file from the database server is preferred, however, if you do not have access to the database server you can click on the 'LogFileName' link from the Support Diagnostic Tools 'Process (SQLFlow) Manager' -> 'Process Manager DBMS Job Admin:' section to retrieve the last 250 lines of the log file.

Impact of Debug Logging

• There is no specific debug logging for the Process Manager. It is generally recommended that the Manager be configured for logging and that these log files be periodically archived to keep them to a manageable size.