SQL Query to List All Active RCR jobs in Siebel

As with any Siebel implementation, one of the day-to-day monitoring tasks for an Administrator are the Siebel components. And the RCR jobs running on the servers tend to be an integral part of the Workflow Process Manager and Workflow Process Batch Manager component requests. So, tracking them becomes useful in situations where there are component issues or some critical job failure.

This SQL can be a reference to list all the RCR jobs by their Execution Server name which is very helpful if you are not assigning any Server Name to the job upon submitting.

SELECT
    PAR_REQ_ID "Job Id",
    rpt_interval||' '||rpt_uom "Frequency",
    STATUS,
    ACTL_START_DT,
    EXEC_SRVR_NAME
FROM
    SIEBEL.S_SRM_REQUEST
WHERE
    REQ_TYPE_CD = 'RPT_INSTANCE'
    AND STATUS = 'ACTIVE'
    AND PAR_REQ_ID in
(
select
    par.row_id
from
    siebel.S_SRM_REQUEST par,
    siebel.S_SRM_ACT_PARAM aparam ,
    siebel.S_SRM_REQUEST child ,
    siebel.S_SRM_REQ_PARAM param
where
    par.row_id = child.par_req_id
    and par.req_type_cd = 'RPT_PARENT'
    and par.STATUS = 'ACTIVE'
    and child.status in ('QUEUED','ACTIVE')
    and par.row_id = param.req_id
    and child.req_type_cd = 'RPT_INSTANCE'
    and param.ACTPARAM_ID = aparam.row_id
    and aparam.NAME = 'Workflow Process Name'
group by par.row_id,param.value, par.rpt_interval||' '||par.rpt_uom
)
order by ACTL_START_DT desc;

As usual, you can feel free to play around and come up with complex outputs with this reference SQL.

Cheers,
Shyam

SQL Query to List all users by Responsibility Name in Siebel

I had a random request come in which wanted the list of users by a responsibility name, some quick Google searches later realized that its not available readily on the Internet. So here it is for an easy reference guys.

SELECT U.LOGIN
FROM SIEBEL.S_USER U, SIEBEL.S_PER_RESP PER, SIEBEL.S_RESP R
WHERE U.ROW_ID = PER.PER_ID
AND R.ROW_ID = PER.RESP_ID
AND R.NAME LIKE 'Siebel Administrator';

You can play around and tweak it as you like for more complex results.

How to Increase logging for a particular user in a Large Enterprise?

It is always a challenge when supporting large Siebel Enterprise applications when we have to troubleshoot a particular user issue. The very first question any DEV/Oracle person would ask is to provide them with the detailed logging from the user session for analysis.

I have posted an earlier article on the logging for specific user, but see that it does not work as expected in some scenarios. So listing this method which I follow personally when troubleshooting issues.

In this method, we are first looking for the server in which the user is connected to and increasing the logging on just that one server thereby limiting the overall impact of increasing the logging during business hours.

Steps:
1. Get the user login ID for which we need the logs
2. Login to Siebel server manager using the command:
srvrmgr /g <gatewayname> /e <enterprisename> /u <username> /p <password>
3. Get the active session of the user
srvrmgr> list session for login USER_NAME

SV_NAME  CC_ALIAS  CG_ALIAS  TK_TASKID  TK_PID  TK_DISP_RUNSTATE  TK_IDLE_STATE  TK_PING_TIME  TK_HUNG_STATE  DB_SESSION_ID  OM_LOGIN  OM_BUSSVC  OM_VIEW  OM_APPLET  OM_BUSCOMP
-------  --------  --------  ---------  ------  ----------------  -------------  ------------  -------------  -------------  --------  ---------  -------  ---------  ----------
SERVER1  SCCObjMgr_enu  CallCenter  35653120   18688   Running           FALSE                                       Shared Connection Id:   USER_NAME  Completed : Workflow Process Manager (RunProcess)  Completed: Business Servi

Note the task ID for the particular user session.
4. Increase the component logging by connecting to the server where user session is running
srvrmgr> set server SERVER1
srvrmgr:server1> change evtloglvl %=5 for comp SCCObjMgr_enu
5. Logon to SERVER1 and search by the task ID got from step 3 in the <siebelserver home>/log folder. This will be the user session log
6. Turn off the logging once done
srvrmgr:server1> change evtloglvl %=1 for comp SCCObjMgr_enu

This method will also increase the details in other user sessions that are connected to that Object Manager on the server, but if needed for short term analysis this can be an effective method. Any other alternative and efficient inputs from the community are greatly appreciated.



-Shyam

Siebel Server Manager (srvrmgr) commands

Listing some everyday srvrmgr commands that can be useful for Siebel Admins, will keep updating this list as I come upon different scenarios.

List Component
srvrmgr> list comp sccobjmgr_enu

List Component for specific server
srvrmgr> list comp sccobjmgr_enu for server SERVER1

List Active sessions for a component
srvrmgr> list active sessions for comp sccobjmgr_enu

List Active sessions for a user
srvrmgr> list active sessions for login tom_siebel

List all sessions for a component
srvrmgr> list sessions for comp sccobjmgr_enu

List all sessions for a user
srvrmgr> list sessions for login tom_siebel

To connect to a specific server within the Enterprise
srvrmgr> set server SERVER2

To disconnect from a server session and go back to Enterprise
srvrmgr:SERVER2> unset server

Starting a Siebel component
srvrmgr> start comp wfprocmgr

Stopping a Siebel component
srvrmgr> stop comp wfprocmgr

Starting a component for a specific server
srvrmgr> start comp wfprocmgr for server SERVER1

Stopping a component for a specific server
srvrmgr> stop comp wfprocmgr for server SERVER2

Kill a stuck/non-responsive component
srvrmgr> kill comp wfprocbatchmgr

How to change startup mode for a component
srvrmgr> manual start comp SCCObjMgr_enu
srvrmgr> auto start comp SCCObjMgr_enu
srvrmgr> manual start comp wfprocmgr for server SERVER1
srvrmgr> auto start comp wfprocbatchmgr for server SERVER2

Listing component parameters
srvrmgr> list param for comp wfprocmgr
To list a specific parameter
srvrmgr> list param <parameterAliasName> for comp wfprocmgr
To list a hidden/advanced parameter
srvrmgr> list advanced param ConnForwardAlgorithm for comp SCBroker

Alter sessions commands for Siebel SQL

In order to have the Siebel SQL statements that we obtain from the logs run with the same execution time with SQL clients like SQL Developer/Toad, these alter statements need to be run in the DB client window where you plan to execute the SQL from Siebel logs:

alter session set optimizer_mode = first_rows_10
alter session set “_optimizer_sortmerge_join_enabled” = false
alter session set “_optimizer_join_sel_sanity_check” = true

How to increase logging for a specific user?

In many situations we would need the Siebel log details for troubleshooting issues within the application, in scenarios where we need the logs for one particular user for a component this command might be helpful.

Connect to Server manager command line from any of the Siebel servers.

1. Command to connect to Server Manager
Navigate to siebsrvr\bin directory and use this command:
srvrmgr /g <gateway server name> /e <enterprise name> /s <server name> /u sadmin /p <password>

2. srvrmgr> list param UserList for comp FINSObjMgr_enu show PA_ALIAS, PA_VALUE
 PA_ALIAS   PA_VALUE
--------          --------
UserList
UserList

Now change it by using
 srvrmgr>change param UserList=<UserName> for comp FINSObjMgr_enu

How to Drop and Generate Triggers in Siebel

If there are changes to a Workflow policy, you might be asked to either 'Generate' the triggers or 'Recreate' them for the changes to take effect. This task can be accomplished in two ways, one through the Siebel Application and the other via Server Manager command line.

The main parameter to consider to Generate or Drop the triggers is 'Remove' which can be set to TRUE or FALSE depending upon the purpose.

Server Manager Commands:

1. Drop Triggers
run task for comp GenTrig with EXEC=True,Remove=True,PrivUser=SIEBEL,PrivUserPass=<siebelpassword>

2. Generate Triggers
run task for comp GenTrig with EXEC=True,Remove=False,PrivUser=SIEBEL,PrivUserPass=<siebelpassword>



Siebel Application:

Login to the Siebel application and navigate to the 'Administration - Server Management' and then 'Jobs'.
Create a new job 'Generate Triggers' and add the parameters as shown:

Change the parameter value of 'Remove' to 'True' if you want to drop the triggers. The ideal sequence for re-creating the triggers is to drop them first and then create. So you would have to run the job with the 'Remove' parameter set to 'True' first and then run the job again with the parameter set to 'False'.

You can also include additional logging parameters like 'ErrorFlags', 'SQLFlags','TraceFlags' etc for capturing additional information in the logs.

Oracle bookshelf link related to this topic:

http://docs.oracle.com/cd/E14004_01/books/BPFWorkflow/administering_policies4.html

Thanks,
Shyam