Query to find All responsibilities of a user

SELECT fu.user_name                "User Name",
       frt.responsibility_name     "Responsibility Name",
       furg.start_date             "Start Date",
       furg.end_date               "End Date",      
       fr.responsibility_key       "Responsibility Key",
       fa.application_short_name   "Application Short Name"
  FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
 WHERE furg.user_id             =  fu.user_id
   AND furg.responsibility_id   =  frt.responsibility_id
   AND fr.responsibility_id     =  frt.responsibility_id
   AND fa.application_id        =  fat.application_id
   AND fr.application_id        =  fat.application_id
   AND frt.language             =  USERENV('LANG')
   AND UPPER(fu.user_name)      =  UPPER('KUMAR')  -- <change it>
   ORDER BY frt.responsibility_name;

ORA-04030: out of process memory while compiling XLA Package

XLA Invalid Object in R12 Application:


SQL>select object_name,object_type from dba_objects where status='INVALID';
OBJECT_NAME OBJECT_TYPE
----------------------------------- -------------------
XLA_00200_AAD_S_000010_PKG PACKAGE BODY
XLA_00200_AAD_S_000011_PKG PACKAGE BODY

SQL> alter package apps.XLA_00200_AAD_S_000010_PKG COMPILE BODY;
alter package apps.XLA_00200_AAD_S_000010_PKG COMPILE BODY
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 4108 bytes (PLS
non-lib hp,pdzgM64_New_Link)


Solution:

SQL> alter system set plsql_optimize_level =0;

System altered.

SQL> alter package apps.XLA_00200_AAD_S_000011_PKG compile body;

Package body altered.

SQL> alter system set plsql_optimize_level =2;


System altered.

R12 Concurrent Manager not came up

To solve this problem, need to perform the below steps

1.  Stop all middle tier services including the concurrent managers.
2.  Stop the database.
3.  Start the database.
4.  Connect SQLPLUS as APPS user and run the following :

EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

5.  Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers and webtiers to repopulate the required system tables.
6.  Connect to SQLPLUS as APPS user and run the following statement :

select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

If the above SQL does not returning any value then please do the following:

Go to $FND_TOP/patch/115/sql

Connect SQLPLUS as APPS user and run the following script :

SQL> @afdcm037.sql;

This script will create libraries for FNDSM and create managers for preexisting nodes.

Check again that FNDSM entries now exist:

select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

The system has encountered an error when servicing the request, Please try again. If the error persists, please contact the system administrator.

While accessing R12.2 EBS url we have faced this below mentioned error

"The system has encountered an error when servicing the request, Please try again. If the error persists, please contact the system administrator."

In Weblogic Console, all the services are up and running successfully.

In Oacore_server.log file:

java.lang.NoClassDefFoundError: Could not initialize class oracle.apps.fnd.functionSecurity.User

In oacore_server.out file:

Exception in static block of jtf.cache.CacheManager. Stack trace is: oracle.apps.jtf.base.resources.FrameworkException: IAS Cache initialization failed. The Distributed Caching System failed to initialize on port: 12345. The list of hosts in the distributed caching system is: 172.16.1.1 . The port 12345 should be free on each host running the JVMs.

Solution:

Shut down the application services and check the java cache port number is still accessing or not.

$netstat -ntpl |grep 12345
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 ::ffff:172.16.1.1:12345 :::*                        LISTEN      6171/java

find the process for this port number and kill the process.

$ps -ef|grep 6171
applmgr   6171     1  0 Jul27 ?        00:51:22 /u01/applmgr/fs1/EBSapps/comn/util/jdk32/bin/java -
applmgr  27304 24214  0 16:19 pts/0    00:00:00 grep 6171

$kill -9 6171

Start the application services and this time ebs url worked fine without any issue.

Find Roles and Privilege

SQL> desc user_sys_privs;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
 USERNAME                                           VARCHAR2(30)
 PRIVILEGE                                 NOT NULL VARCHAR2(40)
 ADMIN_OPTION                                       VARCHAR2(3)

 SQL>desc dba_role_privs;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRANTEE                                            VARCHAR2(30)
 GRANTED_ROLE                              NOT NULL VARCHAR2(30)
 ADMIN_OPTION                                       VARCHAR2(3)

 DEFAULT_ROLE                                       VARCHAR2(3)

SQL>desc role_sys_privs;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROLE                                      NOT NULL VARCHAR2(30)
 PRIVILEGE                                 NOT NULL VARCHAR2(40)
 ADMIN_OPTION                                       VARCHAR2(3)

SQL>desc role_tab_privs;

 Name                                      Null?    Type

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

 ROLE                                      NOT NULL VARCHAR2(30)

 OWNER                                     NOT NULL VARCHAR2(30)

 TABLE_NAME                                NOT NULL VARCHAR2(30)

 COLUMN_NAME                                        VARCHAR2(30)

 PRIVILEGE                                 NOT NULL VARCHAR2(40)

 GRANTABLE                                          VARCHAR2(3)

SQL>desc role_role_privs;

 Name                                      Null?    Type

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

 ROLE                                      NOT NULL VARCHAR2(30)

 GRANTED_ROLE                              NOT NULL VARCHAR2(30)

 ADMIN_OPTION                                       VARCHAR2(3)

Current User Privileges:
-----------------------------
SQL> desc user_sys_privs;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
 USERNAME                                           VARCHAR2(30)
 PRIVILEGE                                 NOT NULL VARCHAR2(40)

 ADMIN_OPTION                                       VARCHAR2(3)

Purge Application Log Files May Be Manually Regularly

you can purge the following Middle Tier files:

1. Shutdown middle (web) tier using the command: $ADMIN_SCRIPTS_HOME/adopmnctl.sh stopall

2. Clean out the Apache/oc4j log files in IAS 10g (10.1.3) ORACLE_HOME:

From an Unix shell where the Apps environment has been sourced,

a-1. $ cd $LOG_HOME/ora/10.1.3/Apache/
a-2. $ pwd (Verify that you are in the "Apache logs directory")
a-3. $ rm -r *

b-1. $ cd $LOG_HOME/ora/10.1.3/opmn/
b-2. $ pwd (Verify that you are in the "opmn logs directory")
b-3. $ rm -r *

c-1. $ rm $APPLRGF/javacache.log

d-1. $ cd $APPLRGF/jvmDump
d-2. $ pwd (Verify that you are in the "jvmDump directory")
d-3. $ rm -r *

For EACH j2ee log group, purge the following files:

You could have multiple JVMs per each type of log groups so "default_group_#" refers to generic #. Whereby the steps below for cleaning should be for each JVM.

e-1. $ cd $LOG_HOME/ora/10.1.3/j2ee/forms/[ forms_default_group_# ]
e-2. $ pwd (Verify that you are in the "forms_default_group_#" directory)
e-3. $ rm -r *

f-1. $ cd $LOG_HOME/ora/10.1.3/j2ee/oacore/[ oacore_default_group_# ]
f-2. $ pwd (Verify that you are in the "oacore_default_group_#" directory)
f-3. $ rm -r *

g-1. $ cd $LOG_HOME/ora/10.1.3/j2ee/oafm/
g-2. $ pwd (Verify that you are in the oafm directory)
g-3. $ rm -r *

h-1. $ cd $LOG_HOME/ora/10.1.3/j2ee/oafm/[ oacore_default_group_# ]
h-2. $ pwd (verify that you are in the "oafm_default_group_#" directory)
h-3. $ rm -r *

Ref : Which Application Log Files May Be Manually Purged Regularly? (Doc ID 2005894.1)

Tuning OPP Heap Memory Issue

SELECT service_id, service_handle, developer_parameters
FROM fnd_cp_services WHERE service_id = (SELECT manager_type
FROM fnd_concurrent_queues
WHERE concurrent_queue_name = 'FNDCPOPP');

use the below command to check whether you will succeed with 2GB of heapsize or not.

$ java -Xmx2048m -version

Error occurred during initialization of VM
Could not reserve enough space for object heap
Could not create the Java virtual machine.

Increase the memory value from 512 to 1024 or higher

UPDATE fnd_cp_services
SET developer_parameters ='J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
WHERE service_id = (SELECT manager_type FROM fnd_concurrent_queues
WHERE concurrent_queue_name = 'FNDCPOPP');

Find Details of Specific OS PID

SELECT p.spid, s.SID, s.serial#, s.action, s.username, s.status, s.program "Session Program",
       p.program "Process Program", s.module, s.lockwait, s.state, s.sql_hash_value,
       s.schemaname, s.osuser, s.machine, s.last_call_et, p.program,
       p.terminal, logon_time, module, s.osuser , f.request_id,  f.request_date,
        f.completion_text, f.outcome_product,
        f.logfile_node_name, f.outfile_name, argument_text,
       f.outfile_node_name, f.oracle_id, f.concurrent_program_id,
       f.responsibility_application_id, f.responsibility_id,
       f.last_update_login, f.nls_language, f.controlling_manager, f.actual_start_date,f.actual_completion_date
  FROM v$process p, v$session s, fnd_concurrent_requests f
 WHERE s.paddr = p.addr /*and s.status = 'ACTIVE'*/
   AND s.username NOT LIKE '%SYS%'
   AND p.spid IN (SELECT oracle_process_id
                    FROM fnd_concurrent_requests
                   WHERE 1 = 1 AND oracle_process_id = &os_pid);

Find Concurrent Request id from SID

SELECT s.inst_id,a.request_id,s.sid,s.serial#,c.spid

  FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s
   WHERE s.sid in ('&SID')
  AND s.paddr = c.addr
  AND a.oracle_process_id = c.spid
  AND a.phase_code = UPPER ('R');

Inactive Sessions

Inactive Session:

select distinct  a.logon_time,a.sql_id,a.username,a.module,a.inst_id,a.osuser,a.program,substr(machine,1,20) "Machine Name",a.status, ' alter system kill session '''||a.sid||','||a.serial#||''';'
from gv$session a, gv$sql b 
where a.status='INACTIVE'
and a.logon_time < sysdate-1 --and a.module like '%JDBC Thin Client%'
and a.machine like ‘%hostname%'  and username='APPS'

and a.sql_id=b.sql_id;

Total Count of sessions

select count(s.status) TOTAL_SESSIONS
from gv$session s;

Total Count of Inactive sessions

select count(s.status) INACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='INACTIVE';

SESSIONS WHICH ARE IN INACTIVE STATUS FROM MORE THAN 1HOUR

select count(s.status) "INACTIVE SESSIONS > 1HOUR "
from gv$session s, v$process p
where
p.addr=s.paddr and
s.last_call_et > 3600 and
s.status='INACTIVE';

How to Kill a INACTIVE session:

select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where status='INACTIVE';


TOTAL FORM SESSIONS

SELECT COUNT(S.SID) INACTIVE_FORM_SESSIONS FROM V$SESSION S
WHERE S.STATUS='INACTIVE' and
s.action like ('%FRM%');

INACTIVE FORM SESSION:

col program for a15
col last_call_et for 999.99
select p.spid, s.sid, s.process,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600
order by last_call_et desc;


TOAD SESSIONS

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program
from gv$session s, gv$process p
where
p.addr=s.paddr
and s.MODULE like ('%TOAD%')
Order by last_call_et;