Concurrent Manager Issues

Gather Schema Statistics fails with Ora-20001 errors after 11G database upgrade :

Gather Schema Statistics” program reported following errors in request log files :

 +—————————————————————————+

Start of log messages from FND_FILE

+—————————————————————————+

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP

stats on table FND_CP_GSM_IPC_AQTBL is locked

stats on table FND_SOA_JMS_IN is locked

stats on table FND_SOA_JMS_OUT is locked

Error #1: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #2: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #3: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

+—————————————————————————+

End of log messages from FND_FILE

+—————————————————————————+

  Action :

To Check which objects or tables are locked 

 sql > SELECT OWNER,TABLE_NAME,STATTYPE_LOCKED

         FROM DBA_TAB_STATISTICS

         WHERE STATTYPE_LOCKED IS NOT NULL;

TO Unlock all the tables in a schema at once :

sql> exec dbms_stats.unlock_schema_stats(‘schema_owner’);

e.g : sql> exec dbms_stats.unlock_schema_stats(‘apps’);

TO Unlock all Individual  tables in a schema at once 

sql> exec dbms_stats.unlock_schema_stats(‘table_owner’,'table_name’);

e.g : sql > exec dbms_stats.unlock_schema_stats(‘AR’,'AR_REV_REC_QT’);

There are two reasons for that error message:

1 ) There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.

Because of this problem, FND_STATS tries to gather histogram information using wrong command and it fails with ora-20001 errors.

Following SQL should have returned one row, not two.

SQL> select a.column_name, nvl(a.hsize,254) hsize

from FND_HISTOGRAM_COLS a

where table_name = ‘JE_BE_LINE_TYPE_MAP’

order by column_name;

COLUMN_NAME HSIZE

—————————— ———-

SOURCE 254

SOURCE 254

 2) Column does not exist on the table but still listed in FND_HISTOGRAMS_COL table.

Solution:

Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.

Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

– identify duplicate rows

select table_name, column_name, count(*)

from FND_HISTOGRAM_COLS

group by table_name, column_name

having count(*) > 1;

– Use above results on the following SQL to delete duplicates

delete from FND_HISTOGRAM_COLS

where table_name = ‘&TABLE_NAME’

and  column_name = ‘&COLUMN_NAME’

and rownum=1;

– Use following SQL to delete obsoleted rows

delete from FND_HISTOGRAM_COLS

where (table_name, column_name) in

  (

   select hc.table_name, hc.column_name

   from FND_HISTOGRAM_COLS hc , dba_tab_columns tc

   where hc.table_name  =’&TABLE_NAME’

   and hc.table_name= tc.table_name (+)

   and hc.column_name = tc.column_name (+)

   and tc.column_name is null

  );

There are no active responsibilities available for this user – Oracle Apps 11i:

Problem Statement: Recently I encountered a problem in Oracle Apps 11i (11.5.10.2). When a user login into the application he/she gets error “There are no active responsibilities available for this user “and user is not able to see any responsibility in the home page.

The problem is same even for sysadmin user. Now the challenge is that, if the system administrator is also not able to see any responsibility then its very difficult to administer other users facing same problem. So here is the solution that I found out as per metalink note ID 316959.1

Solution :

Part 1: Verification

Execute following queries for verification as per metalink note ID 335487.1

1) Please check if table FND_USER_RESP_GROUPS was backed up to table FND_USER_RESP_GROUPS_OLD in the APPLSYS schema, and verify that there is a SYNONYM  FND_USER_RESP_GROUPS_OLD in the APPS schema.

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE ’FND_USER_RESP_GROUPS_OLD’
AND OBJECT_TYPE IN (‘VIEW’,’TABLE’, ‘SYNONYM’);

OWNER                           OBJECT_NAME                                         OBJECT_TYPE
——————————     ——————————                                       ——————
APPLSYS                        FND_USER_RESP_GROUPS_OLD       TABLE
APPS                                FND_USER_RESP_GROUPS_OLD       SYNONYM

2) Check if object FND_USER_RESP_GROUPS is a view or a table

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE ‘FND_USER_RESP_GROUPS’;  2

OWNER                          OBJECT_NAME                                  OBJECT_TYPE
——————————    ——————————                               ——————
APPS                               FND_USER_RESP_GROUPS           VIEW
APPS_MRC                   FND_USER_RESP_GROUPS           SYNONYM
RTREADONLY            FND_USER_RESP_GROUPS           SYNONYM

FND_USER_RESP_GROUPS should be a view in APPS schema.

If FND_USER_RESP_GROUPS is of type TABLE in the APPLSYS schema  please apply solution from step 3 from metalink note ID 335487.1

Else execute following steps from metalink note ID 316959.1

Part 2: Fix

Run following SQLs

1)

SELECT *
FROM Fnd_Responsibility_vl
WHERE Application_Id = 1
AND Responsibility_Name = ‘System Administrator’
AND Start_Date <= Sysdate
AND ( End_Date is NULL OR End_Date > Sysdate );

2)

SELECT count(*)
FROM Fnd_User_Resp_Groups;

3)

Back up the wf_role_hierarchies table into a new table you create.

CREATE TABLE wf_role_hierarchies_copy AS
SELECT * FROM wf_role_hierarchies;

Delete the entries in that table (2 rows exist)

TRUNCATE TABLE applsys.wf_role_hierarchies;

Run affurgol.sql FORCE

SQL> @$FND_TOP/patch/115/sql/affurgol.sql FORCE

Then replace those entries back into wf_role_hierarchies table

SQL> INSERT INTO wf_role_hierarchies
SELECT * FROM wf_role_hierarchies_copy;

Bounce Apache and try to login as sysadmin. You should be able to see the correct responsibilities.

CM not starting after Cloning ?

Once after doing Rapid cloning some of concurrent managers were not starting. The issue was that the node was wrongly defined as source node in Administer Manager screen. I then checked the FND_NODES table which wrongly had the source nodes as well apart from the target nodes.

I performed the following action plan which resolved the issue->

•          Shutdown the apps services

•          EXEC FND_CONC_CLONE.SETUP_CLEAN;

COMMIT;

EXIT;

•          Ran AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers.

•          Start the apps services

Actually FND_CONC_CLONE.SETUP_CLEAN clears up the tables with nodes information and when we run autoconfig, it repopulates these tables with correct node information.

Concurrent Manager Trouble shooting

Concurrent manager trouble shooting :

1. First check the CM is up or not by using below any one procedure.
      i. ps –ef|grep FNDLIBR
     ii. adcmctl.sh status apps/appsPWD
    iii. Login as system administrator responsibilities go to the below navigation Concurrent -> manager -> Administer -> see the  Actual and target for all the managers

2. If CM in down check the internal manager Logfile in $APPLCSF/APPLLOG location for errors.
3. If any errors related to FNDFS then check the Application listener STATUS , if it is not running start it.
4. If CM is up/running then log file and output file are not able see the user from the front end then check the Application listener is status if it is not running then start it.

5. If any user is complained some request is running long time then

   i. First check the CM status and ICM status.
  ii. Check any Locks on DB particularly related to that program.
 iii. Check any INVALID objects related to that program.
 iv. Check any incompatible requests/programs are running for that program.
Login as system administrator responsibility and query the long running request /program. Navigate to concurrent program define query that long running request in the same form select incompatible requests .
If any incompatible request is running then find the user name who is running that program inform them regarding the incompatibilities.

How to Purge Concurrent Request and/or Manager Data?

Loigin to frontend with sysadmin responsibility:

Navigate :

System Administrator– > Concurrent –> Requests

Click on Submit a New Request — >Select Single Request click ok

Enter Name as Purge Concurrent Request and/or Manager Data

 Entity – ALL / Request / Manager

 ALL - Purges records from database tables that record history information for concurrent requests,concurrent managers, and purges request log files, manager log files, and report output files from the operating system.

Request - Purges records from database tables that record history information for concurrent requests, and purges request log files and report output files from the operating system.

Manager – Purges records from database tables that record history information for concurrent managers, and purges manager log files from the operating system. 

 Mode - Age / Count

Age – Enter the number of days for which you want to save concurrent request history, log files, and report output files. The purge program deletes all records older (in days) than the number you enter. For example, if you enter “5″, then all concurrent request history, log files, and report output files older than five days is purged.

Count – Enter the number of (most recent) records for which you want to save concurrent request history, log file, and report output files. The purge program starts from the most recent records, retains the number you enter, and purges all remaining records. For example, if you enter “5″, then the five most recent concurrent request history records, request log files, manager log files, report output files are saved, and all remaining records are purged.

 Mode Value – Value

Mode Value – Enter a value to define the number of days for Mode=Age or the number of records for Mode=Count. The valid values are 1 – 9999999

 Then Submit.

 The database tables that are affected by running the Purge Concurrent Request and/or Manager Data program are:

 FND_CONCURRENT_REQUESTS : This table contains a complete history  of all concurrent requests.

FND_RUN_REQUESTS :  When a user submits a report set, this table stores information about the reports in the report set and the parameter values for each report.

FND_CONC_REQUEST_ARGUMENTS : This table records arguments passed by the concurrent manager to each program it starts running.

FND_DUAL : This table records when requests do not update database tables.

FND_CONCURRENT_PROCESSES: This table records information about Oracle Applications and operating system processes.

FND_CONC_STAT_LIST : This table collects runtime performance statistics for concurrent requests.

FND_CONC_STAT_SUMMARY: This table contains the concurrent program performance statistics generated by the Purge Concurrent Request and/or Manager Data program. The Purge Concurrent Request and/or Manager Data program uses the data in FND_CONC_STAT_LIST to compute these statistics.

Purge Concurrent Request and/or Manager Data?

One of the important area of Concurrent Manager tuning is monitoring the space usage for the subsets within each concurrent manager. When the space inFND_CONCURRENT_PROCESSES and FND_CONCURRENT_REQUESTS exceed 50K, you can start to experience serious performance problems within your Oracle Applications. When you experience these space problems, a specific request called “Purge Concurrent Requests And/Or Manager Data” should be scheduled to run on a regular basis. This request to purge can be configured  the request data from the FND tables as well as the log files and output files on accumulate on disk.

When the tables FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROCESSES reaches above 3000-4000 rows, the performance diminishes.You have to run Purge Concurrent Request and/or Manager Data program on a regular basis depending on the amount of request being run.

 Find Size in KB for FND_CONCURRENT_REQUESTS :

 sql> SELECT (BYTES/1024)/1024 “Size in KB” from dba_segments where SEGMENT_NAME=’FND_CONCURRENT_REQUESTS’;

O/P  : Size in KB
           ———-
           3.65

 Sql> SELECT (BYTES/1024)/1024 “Size in KB” from dba_segments WHERE     SEGMENT_NAME=’FND_CONCURRENT_PROCESSES’;

O/P :    Size in KB
              ———-
              10.75

 The Purge Concurrent Request and/or Manager Data program job can be used to purge Requests,Mgr Logs

and all requests depending on what is choosen.

 Use the following options: Enter=ALL, Mode =Age, Mode Value=15

What happens if you don’t give cache size while defining Concurrent Manager ?

Lets first understand what is cache size in Concurrent Manager. When Manager picks request from FND CONCURRENT REQUESTS Queues, it will pick up number of requests defined by cache size in one shot & will work on them before going to sleep. So in my views if you don’t define cache size while defining CM then it will take default value 1, i.e. picking up one request per cycle.

Why I need two Concurrent Processing Nodes or in what scenarios PCP is Used ?

Well If you are running GL Month end reports or taxation reports annually these reposrts might take couple of days. Some of these requests are very resource intensive so you can have one node running long running , resource intensive requests while other processing your day to day short running requets.

another scenario is when your requests are very critical and you want high resilience for your Concurrent Processing Node , you can configure PCP. So if node1 goes down you still have CM node available processing your requests.

What is PCP is Oracle Applications 11i ?

PCP is acronym for Parallel Concurrurent processing. Usually you have one Concurrent Manager executing your requests but if you can configure Concurrent Manager running on two machines (Yes you need to do some additional steps in order to configure Parallel Concurrent Processing) . So for some of your requests primary CM Node is on machine1 and secondary CM node on machine2 and for some requests primary CM is on machine2 & secondary CM on machine1.

What is Sleep Time?

The sleep time parameter indicates the seconds that the ICM should wait between checking for requests that are waiting to run. The default sleep time is 60, but you can lower this number if you see you have a lot of request waiting (Pending/Normal) . However, reducing this number to a very low value many cause excessive cpu utilization.

What is Queue Size?

 The queue size is the number of PMON cycles that the ICM waits between checking for disabled or new concurrent managers. The default for queue size of one PMON cycle should be used.

What is PMON cycle?

 This is the number of sleep cycles that the ICM waits between the time it checks for concurrent managers failures, which defaults to 20. You should change the PMON cycle to a number lower than 20 if your concurrent managers are having problems with abnormal terminations.

What happens if user kills ICM?

Applications will be down

If ICM is down, what happens?

All the other managers will keep working. ICM takes care of the queue control requests, which means starting up and shutting down other concurrent managers.