Few Info questions

Question. How verify the sysadmin password from command line?
Answer: This utility can be used to verify the GUEST/ORACLE password
SQL> select fnd_web_sec.validate_login(‘SYSADMIN’,'’)from dual;
If it returns Y then sysadmin password is correct
If it returns N then sysadmin password that we are using
Eg:
SQL> select fnd_web_sec.validate_login(‘SYSADMIN’,’WELCOME123') from dual;
FND_WEB_SEC.VALIDATE_LOGIN(‘SYSADMIN’,’SYSADMIN123')
——————————————————————————–
N
SQL> select fnd_web_sec.validate_login(‘SYSADMIN’,’SYSADMIN’) from dual;
FND_WEB_SEC.VALIDATE_LOGIN(‘SYSADMIN’,’SYSADMIN’)
——————————————————————————–
Y

How you will troubleshoot if concurrent request is taking long time?

First you will try to check how far the query has gone (if in v$session_longops for example, or reading some session statistics) and if there is contention with
another session (such as a lock for example) because it talks about a 'concurrent request'.
Method 1: Trace the session using dbms_system.set_sql_trace_in_session (or) if u r using 10g, using dbms_monitor and
look at the trace file to identify where the issue is. You can get the explain plan and tune the query.
Method 2:
1. Is this a new query (or) an existing query
2. Look for the wait event associated to this query.
3. What is the elapsed time for this query?
4. Are there are any db deadlocks?
5. Are there any long operations?
6. Are there any long transactions?
7. Based on the sql hash value, look at the sql_text from v$sql
8. Run an explain plan (or) use oradebug and trace the query if what bind variables are using and how the data distribution is.
9. Based on the above information, look for when statistics was collected
10. If it is an old query, check if the plan has changed and if so, why?
Once u understand where the problem is, then solutions can be defined ranging from creating sql profiles / stored outlines (or) adding hints to the query and thoroughly test your changes. Always remember to make one change and test for performance change. If you make a series of changes, it will be very difficult to isolate which has caused the performance improvement.

What are the basic steps for Printer Installation in EBS 11i/R12?

For most printing needs, the Pasta Utility offers quick setup and easy maintenance. For additional flexibility, Oracle E-Business Suite allows you to define your own printer drivers and print styles.
1. Setup the printer at the OS level
2. Add a valid entry in the hosts file (Printer Name and the IP Address)
3. Login to System Administrator responsibility
4. Navigate to Install > Printer > Register
5. Define a new printer by entering the Printer Name you have set in the hosts file
6. Save
7. Bounce the Concurrent Manager

What are basic steps for Workflow Mailer configuration?

use Oracle Application Manager (OAM) to configure Workflow Notification Mailer.
For Outbound Notification, CM (Concurrent Manager) node should be able to connect to SMTP (Simple Mail Transfer Protocol) server/relay.
For Inbound Notification (Optional), CM node should be able to connect to IMAP (Internet Message Access Protocol) Server.
Log file for Workflow Mailer Notification are at $APPLCSF/$APPLLOG/FNDC*.txt
Workflow Notification Mailer in background run as Concurrent Manager (Workflow Mailer Service, Workflow Agent Listener Service)
If you wish to configure Inbound Notification as well then ensure IMAP Server should be configured with a valid user (create Inbox, Processed & Discard folder for this User)
Schedule “Workflow Background Process” Concurrent Request:
1. Frequency : Every 5 Minutes – Parameter : Deferred:Yes, Timeout:No, Stuck:No
2. Frequency : Every 60 Minutes – Parameter : Deferred:No, Timeout:Yes, Stuck:No
4. Frequency : Daily – Parameter : Deferred:No, Timeout:No, Stuck:Yes
5. Frequency : Every 10 Minutes – Parameter : Deferred:Yes, Timeout:No, Stuck:
6. Frequency : Every 6 Hours – Parameter : Deferred:Yes, Timeout:Yes, Stuck:Yes
The main component of the Oracle Workflow Notification Mailer is the executable WFMAIL. This is a server side program that queries the database for any pending notifications. It then dispatches these notifications by calling sendmail for UNIX and the MAPI APIs for Microsoft Windows NT. The notification mailer also queries the local inbox for incoming messages. These messages are validated and then passed to the database for response processing. To configure Workflow Notification Mailer we have to do OS level setup (Sendmail) and Application level setup.

In the cloning process, when do you utilize dbTechStack vs just dbTier?
perl adcfgclone.pl dbTechStack vs perl adcfgclone.pl dbtier
There are different components with RapidClone that are used when cloning an Oracle Applications instance. These are:
dbTechStack (RDBMS ORACLE_HOME)
database (database only, including control file creation)
dbconfig (database only, with no control file creation)
dbTier (both dbTechStack and database)
atTechStack (Tools and Web ORACLE_HOMEs)
appltop (APPL_TOP only)
appsTier (both atTechStack and appltop)
perl adcfgclone.pl dbtier
This will configure the ORACLE_HOME on the target database tier node + recreate the control files. This is used for cold backup.
perl adcfgclone.pl dbTechStack
This will configure the ORACLE_HOME on the target database tier node only. When running this command you will have to recreate the control files manually. This is used to clone the Database separately, for example using RMAN hot backup.
When you use “dbTier” option, the perl script will configure both the tech stack and data stack(ORACLE_HOME and Oracle Database) whereas “dbTechStack” option will only configures ORACLE_HOME and it WILL NOT create database and this is generally used while doing hot clone where db creation is a manual step.

User creation in 12C

Common users belongs to CBD’s as well as current and future PDB’s. It means it can performed operation in Container or Pluggable according to Privileges assigned. For more information about common user.

Local users is purely database that belongs to only single PDB. This user may have administrative privileges but this only belongs to that PDB. For more information about local user.

// Consider following example in which i am trying to create common user in container root.
SQL> show con_name
CON_NAME
——————————
CDB$ROOT

SQL> create user scott identified by scott;
create user scott identified by scott
*
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> create user scott identified by scott container=current;
create user scott identified by scott container=current
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT
SQL> create user scott identified by scott container=all;
create user scott identified by scott container=all
*
ERROR at line 1:
ORA-65096: invalid common user or role name

//If you wish to create common user under CDB$ROOT than create user start with C## and c##, as follows:
Note:
Common user will be created under root container only.
Current container must be set to CDB$ROOT.
SQL> create user C##scott identified by scott;
User created.
SQL> create user c##scott identified by scott container=all;
User created.
OR
// Creating local user in PDB:
SQL> alter session set container=sales;
Session altered.
SQL> sho con_name
CON_NAME
——————————
SALES
SQL> create user test identified by test;
User created.
***********************************************************************

Concurrent SQL Query

1.How to Determine Which Manager Ran a Specific Concurrent Request?

col USER_CONCURRENT_QUEUE_NAME for a100 select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a, fnd_concurrent_queues_vl b, fnd_concurrent_requests c where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID and a.CONCURRENT_PROCESS_ID = c.controlling_manager and c.request_id = '&conc_reqid';


2.Concurrent manager status for a given sid?

col MODULE for a20 col OSUSER for a10 col USERNAME for a10 set num 10 col MACHINE for a20 set lines 200 col SCHEMANAME for a10 select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE, s.SCHEMANAME, s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';


3. Find out request id from Oracle_Process Id:

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where ORACLE_PROCESS_ID='&a';


4.To find sid,serial# for a given concurrent request id?

set lines 200 SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, gv$process c, gv$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id AND b.session_id=d.audsid AND a.request_id = &Request_ID AND a.phase_code = 'R';


5.To find concurrent program name,phase code,status code for a given request id?

SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code) phase_code, DECODE(status_code,'D', 'Cancelled' , 'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon- yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'), completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;


6.To find the sql query for a given concurrent request sid?

select sid,sql_text from gv$session ses, gv$sqlarea sql where ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and ses.sid='&oracle_sid'


7. To find child requests

set lines 200 col USER_CONCURRENT_PROGRAM_NAME for a40 col PHASE_CODE for a10 col STATUS_CODE for a10 col COMPLETION_TEXT for a20 SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE (sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D', 'Cancelled' , 'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date, sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum, apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID = '&parent_concurrent_request_id';


8. Cancelling Concurrent request :

update fnd_concurrent_requests set status_code='D', phase_code='C' where request_id=&req_id;


9. Kill sessions program wise

select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where MODULE like '';


10 .Concurrent Request running by SID

SELECT a.request_id, d.sid as Oracle_SID, d.serial#, d.osuser, d.process, c.SPID as OS_Process_ID FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, gv$process c, gv$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id AND b.session_id=d.audsid AND d.sid = &SID;


11. Find out request id from Oracle_Process Id:

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests where ORACLE_PROCESS_ID='&a';


12. Oracle Concurrent Request Error Script (requests which were error ed out)

SELECT a.request_id "Req Id" ,a.phase_code,a.status_code , actual_start_date , actual_completion_date ,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program" FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b ,applsys.fnd_concurrent_queues q ,APPLSYS.fnd_concurrent_programs c ,APPLSYS.fnd_concurrent_programs_tl ctl WHERE a.controlling_manager = b.concurrent_process_id AND a.concurrent_program_id = c.concurrent_program_id AND a.program_application_id = c.application_id AND a.status_code = 'E' AND a.phase_code = 'C' AND actual_start_date > sysdate - 2 AND b.queue_application_id = q.application_id AND b.concurrent_queue_id = q.concurrent_queue_id AND ctl.concurrent_program_id = c.concurrent_program_id AND ctl.LANGUAGE = 'US' ORDER BY 5 DESC;


13. Request submitted by User

SELECT user_concurrent_program_name, request_date, request_id, phase_code, status_code FROM fnd_concurrent_requests fcr, fnd_concurrent_programs_tl fcp, fnd_responsibility_tl fr, fnd_user fu WHERE fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id and fcr.responsibility_id = fr.responsibility_id and fcr.requested_by = fu.user_id and user_name = '&user' AND actual_start_date > sysdate - 1 ORDER BY REQUEST_DATE Asc;


14.Concurrent Program enable with trace

col User_Program_Name for a40 col Last_Updated_By for a30 col DESCRIPTION for a30 SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name", SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name", SUBSTR(B.USER_NAME,1,15) "Last_Updated_By", SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B WHERE A.ENABLE_TRACE='Y' AND A.LAST_UPDATED_BY=B.USER_ID

OPP

Query to get the OPP log file of the request SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp WHERE fcpp.processor_id = fcp.concurrent_process_id AND fcpp.action_type = 6 AND fcpp.concurrent_request_id = &&request_id


Determine the current maximum Java heap size:

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

SERVICE_ID SERVICE_HANDLE DEVELOPER_PARAMETERS
---------- -------------- --------------------------------------------------------
1091 FNDOPP J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx512m
Increase the maximum Java heap size for the OPP to 1024MB (1GB):

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

The OPP queue can be Recreated the using $FND_TOP/patch/115/sql/afopp002.sql file as 'APPLSYS' user. On running the script you will be prompted for username and password.

There are 2 new profiles options that can be used to control the timeouts

Profile Option : Concurrent:OPP Response Timeout
Internal Name : CONC_PP_RESPONSE_TIMEOUT
Description : Specifies the amount of time a manager waits for OPP to respond to its request for post processing.

Profile Option : Concurrent:OPP Process Timeout
Internal Name : CONC_PP_PROCESS_TIMEOUT
Description : Specifies the amount of time the manager waits for the OPP to actually process the request

Query to get Top 10 Big tables

SELECT * FROM ( SELECT OWNER, SEGMENT_NAME, BYTES/1024/1024/1024 SIZE_GB FROM DBA_SEGMENTS WHERE SEGMENT_TYPE = 'TABLE' ORDER BY BYTES/1024/1024 DESC ) WHERE ROWNUM <= 10;

IMP metalinks

Metalink ID
201703.1
SYSADMIN
104282.1
Concurrent Processing Tables and Purge Concurrent Request and/or Manager Data Program (FNDCPPUR)
186361.1
Workflow Background Process Performance Troubleshooting Guide
184375.1
How to Setup The Report Output to Different Viewer Types in Oracle Applications 11i
1012307.6
Moving Tables Between Tablespaces Using EXPORT/IMPORT
358090.1
Workflow Administrator Field Become Uneditable after Selecting System Administrator Responisibility
297522.1
How to investigate printing issues and work towards its resolution
50270.1
Applications Object Library Top 18 Issues
105133.1
Concurrent Manager Questions and Answers Relating to Generic Platform
99687.1
Printing Character Reports with Oracle Applications Release 11
1012181.102
How To Keep Users from Using a Certain Printer
175339.1
Default Printer Defined For A User On Site Level Is Not Used
169935.1
Troubleshooting Oracle Apps Performance Issues
468013.1
Code Snippet for querying responsibilities and users in 11i Applications (user and responsibilites)
156965.1
coe_locks.sql - Session and serial# for locked Rows
1011853.7
Error: FND_POST Insert Trigger Trying to Change Printer Profile
15476.1
FAQ about Detecting and Resolving Locking Conflicts
244274.1
Oracle Application Object Library Generic Service Management (GSM) Setup Test
186893.1
Relinking Executable FNDSM Gives Unsatisified Symbols Error for dlopen, dlerror and dlsym Shared Link Libraries.
181158.1
Cannot Start GSM Service Manager - TNS Errors in Listener Log and CCM Log
74717.1
Troubleshooting Concurrent Manager Startup problems
387077.1
How to find the process identifier (pid, spid) after the corresponding session is killed
1074708.6
How to Select Current Session Information from V$SESSION
557317.1
Apps User is Locked After RDBMS CPU Patches and Utlrp.sql Script Execution
334977.1
REP-1419 Running Accrual Reconciliation Rebuild Process
360628.1
Poor Performance of Accrual Rebuild Reconciliation Report In 11.5.10

A script enabled browser is required for this page to function properly.
391175.1
JInitiator is not Installed When Downloaded to Client PC
130182.1
HOW TO TRACE FROM FORM, REPORT, PROGRAM AND OTHERS IN ORACLE APPLICATIONS
162488.1
Complete Guide to JInitiator 1.1.8 Setup & Troubleshooting for Oracle Applications 11i
1041427.6
KILLING INACTIVE SESSIONS DOES NOT REMOVE SESSION ROW FROM V$SESSION
107686.1
Why several Process remain KILLED in V$SESSION
1020720.102
ALTER SYSTEM KILL Session Marked for Killed Forever
304266.1
Where Should Custom Top Environment Variable Get Added?
216589.1
122008.1
Performance
363048.1
WHY DO DBA_TABLES DBA_OBJECTS AND DBA_SEGMENTS DIFFER WHEN SELECTED FOR TABLES?
1012307.6
Moving Tables Between Tablespaces Using EXPORT/IMPORT
1025288.6
How to Diagnose and Resolve ORA-01650,ORA-01652,ORA-01653,ORA-01654,ORA-01688 : Unable to Extend <> by %S in Tablespace %S
107899.1
Payables Patch Application Troubleshooting Guide
224875.1
Installation, Patching & Upgrade Frequently Asked Questions

ORA-00353: log corruption near block

SQL> startup;

ORACLE instance started.

Total System Global Area 1570009088 bytes

Fixed Size 2221840 bytes

Variable Size 989858032 bytes

Database Buffers 553648128 bytes

Redo Buffers 24281088 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 35389500

Session ID: 331 Serial number: 3

Alert log shows as

Incomplete read from log member '/ora_lcd11/db01/lcd11/db/apps_st/data/log03a.dbf'. Trying next member.
ARC0: Log corruption near block 117330 change 7887289606 time ?
CORRUPTION DETECTED: thread 1 sequence 42 log 3 at block 117330. Arch found corrupt blocks
Errors in file /ora_lcd11/db01/lcd11/db/tech_st/11203/admin/lcd11_ora-lcd11/diag/rdbms/lcd11/lcd11/trace/lcd11_arc0_64028940.trc (incident=90522):
ORA-00353: log corruption near block 117330 change 7887289606 time 07/04/2014 13:06:54
ORA-00312: online log 3 thread 1: '/ora_lcd11/db01/lcd11/db/apps_st/data/log03b.dbf'
ORA-00312: online log 3 thread 1: '/ora_lcd11/db01/lcd11/db/apps_st/data/log03a.dbf'
Incident details in: /ora_lcd11/db01/lcd11/db/tech_st/11203/admin/lcd11_ora-
lcd11/diag/rdbms/lcd11/lcd11/incident/incdir_90522/lcd11_arc0_64028940_i90522.trc
Incomplete read from log member '/ora_lcd11/db01/lcd11/db/apps_st/data/log02a.dbf'. Trying next member.
Incomplete read from log member '/ora_lcd11/db01/lcd11/db/apps_st/data/log02b.dbf'. Trying next member.
ARCH: Log corruption near block 670320 change 7886861019 time ?
CORRUPTION DETECTED: thread 1 sequence 41 log 2 at block 670320. Arch found corrupt blocks
Errors in file /ora_lcd11/db01/lcd11/db/tech_st/11203/admin/lcd11_ora-lcd11/diag/rdbms/lcd11/lcd11/trace/lcd11_ora_60883278.trc (incident=90514):
ORA-00353: log corruption near block 670320 change 7886861019 time 07/03/2014 11:06:05
ORA-00312: online log 2 thread 1: '/ora_lcd11/db01/lcd11/db/apps_st/data/log02b.dbf'
ORA-00312: online log 2 thread 1: '/ora_lcd11/db01/lcd11/db/apps_st/data/log02a.dbf'
Incident details in: /ora_lcd11/db01/lcd11/db/tech_st/11203/admin/lcd11_ora-
lcd11/diag/rdbms/lcd11/lcd11/incident/incdir_90514/lcd11_ora_60883278_i90514.trc
ARC0: All Archive destinations made inactive due to error 354
ARC0: Closing local archive destination LOG_ARCHIVE_DEST_1: '/ora_lcd11/df01/bkp/arch_42_1_851175866.arc' (error 354) (lcd11)
Committing creation of archivelog '/ora_lcd11/df01/bkp/arch_42_1_851175866.arc' (error 354)




SQL> startup nomount;



Fixed Size 2221840 bytes

Variable Size 989858032 bytes

Database Buffers 553648128 bytes

Redo Buffers 24281088 bytes

SQL> alter database mount;

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

--------- --------------------
LCD11 MOUNTED

SQL> alter database clear unarchived logfile group 2;
Database altered.

SQL> alter database clear unarchived logfile group 3;
Database altered.

SQL> alter database open;
SQL> select name,open_mode from v$Database;

NAME OPEN_MODE --------- --------------------
LCD11 READ WRITE

Patch Analysis Report

Patch Impact Analysis (or) Object Impact Analysis:-


set lines 300 column site_version format a30 column patch_version format a30


select f.bug_number "BUG_NBR", a.filename "FILE_NAME", substr(c.version,1,20) "PATCH_VERSION", substr(d.version,1,20) "SITE_VERSION", b.executed_flag, b.creation_date "PATCH_DATE" from ad_files a,ad_patch_run_bug_actions b, ad_file_versions c,ad_file_versions d, ad_patch_run_bugs e,ad_bugs f where a.file_id=b.file_id and c.file_version_id=b.patch_file_version_id and d.file_version_id=b.onsite_file_version_id and b.executed_flag = 'Y' and e.bug_id=f.bug_id and e.patch_run_bug_id=b.patch_run_bug_id and f.bug_number='&Patch_number' order by b.creation_date desc;


Sometimes, the requester may ask us which patch has affected a particular file. we can use the following query to find the answer


set lines 300 column site_version format a30 column patch_version format a30


select f.bug_number "BUG_NBR", a.filename "FILE_NAME", substr(c.version,1,20) "PATCH_VERSION", substr(d.version,1,20) "SITE_VERSION", b.executed_flag, b.creation_date "PATCH_DATE" from ad_files a, ad_patch_run_bug_actions b, ad_file_versions c, ad_file_versions d, ad_patch_run_bugs e, ad_bugs f where a.file_id=b.file_id and a.filename like '&FILE_NAME' -- use % and c.file_version_id=b.patch_file_version_id and d.file_version_id=b.onsite_file_version_id and b.executed_flag = 'Y' and e.bug_id=f.bug_id and e.patch_run_bug_id=b.patch_run_bug_id order by b.creation_date desc;


How To Use Patch Wizard Without Setting Up Internet Connection (Doc ID 741129.1)

How to hold the concurrent programs

1) To hold the specific concurrent program
Hold


update fnd_concurrent_requests set HOLD_FLAG='Y' where PHASE_CODE='P' and CONCURRENT_PROGRAM_ID = (select CONCURRENT_PROGRAM_ID from FND_CONCURRENT_PROGRAMS_VL where CONCURRENT_PROGRAM_NAME like '%FNDSCURS%');


FNDSCURS ----> Short Name of the concurrent Program


Release


update FND_CONCURRENT_REQUESTS set HOLD_FLAG='N' where PHASE_CODE='P' and CONCURRENT_PROGRAM_ID = (select CONCURRENT_PROGRAM_ID from FND_CONCURRENT_PROGRAMS_VL where CONCURRENT_PROGRAM_NAME like '%FNDSCURS%');


2) To hold the program for a user

update APPS.FND_CONCURRENT_REQUESTS set HOLD_FLAG='Y' where PHASE_CODE='P' and REQUEST_ID in (SELECT req.request_id FROM apps.fnd_concurrent_programs_tl prg , apps.fnd_concurrent_requests req , apps.fnd_user fus , apps.fnd_conc_release_classes crc WHERE 1=1 AND prg.application_id = req.program_application_id AND prg.concurrent_program_id = req.concurrent_program_id AND req.requested_by = fus.user_id AND req.phase_code = 'P' AND req.requested_start_date > sysdate AND prg.language = 'US' AND crc.release_class_id(+) = req.release_class_id and CRC.APPLICATION_ID(+) = REQ.RELEASE_CLASS_APP_ID and FUS.USER_NAME in ('USER1','USER1'));


To release the jobs


update APPS.FND_CONCURRENT_REQUESTS set HOLD_FLAG='N' where PHASE_CODE='P' and REQUEST_ID in (SELECT req.request_id FROM apps.fnd_concurrent_programs_tl prg , apps.fnd_concurrent_requests req , apps.fnd_user fus , apps.fnd_conc_release_classes crc WHERE 1=1 AND prg.application_id = req.program_application_id AND prg.concurrent_program_id = req.concurrent_program_id AND req.requested_by = fus.user_id AND req.phase_code = 'P' AND req.requested_start_date > sysdate AND prg.language = 'US' AND crc.release_class_id(+) = req.release_class_id and CRC.APPLICATION_ID(+) = REQ.RELEASE_CLASS_APP_ID and FUS.USER_NAME in ('YYY0M29'));


3) To get the all the child request id from parent request ID -- pass parent request id

select /*+ ORDERED USE_NL(x fcr fcp fcptl)*/ fcr.request_id "Request ID", fcr.requested_by "User", substr(DECODE (FCR.DESCRIPTION, NULL, FCPTL.USER_CONCURRENT_PROGRAM_NAME, FCR.DESCRIPTION||' ('||FCPTL.USER_CONCURRENT_PROGRAM_NAME||')'),1,80)"Program Name", (fcr.actual_completion_date - fcr.actual_start_date)*1440 "Elapsed Time", oracle_process_id "Trace File ID" , fcr.phase_code "Phase", fcr.status_code "Status", to_char(fcr.request_date,'DD-MON-YYYY HH24:MI:SS') "Submitted", (fcr.actual_start_date - fcr.request_date)*1440 "Delay", to_char(fcr.actual_start_date,'DD-MON-YYYY HH24:MI:SS') "Start Time", to_char(fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "End Time", fcr.argument_text "Parameters" from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */ fcr1.request_id from fnd_concurrent_requests fcr1 where 1=1 start with fcr1.request_id = &parent_request_id connect by prior fcr1.request_id = fcr1.parent_request_id) x, fnd_concurrent_requests fcr, fnd_concurrent_programs fcp, fnd_concurrent_programs_tl fcptl where fcr.request_id = x.request_id and fcr.concurrent_program_id = fcp.concurrent_program_id and fcr.program_application_id = fcp.application_id and fcp.application_id = fcptl.application_id and fcp.concurrent_program_id = fcptl.concurrent_program_id and fcptl.language = 'US' order by 1;





Terminate the Request as follows


update fnd_concurrent_requests set status_code='X', phase_code='C' where request_id='request_id';

Status code and Phase code from fnd_concurrent_requests

How To Find the Meanings of Various Status Codes (Doc ID 297909.1)

FND_CONCURRENT_REQUESTS

STATUS_CODE Column:
A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting.

PHASE_CODE column.
C Completed
I Inactive
P Pending
R Running

Responsibility Issue

TROUBLESHOOTING: RESPONSIBILITY NOT VISIBLE BY USER AFTER UNENDATE
≡ Category: Oracle EBS | ≅ Leave a Comment
It can happen that after unendating a responsibility assignment, the user cannot see that responsibility. To solve this problem there are 2 procedures that can help:
1. Run the request ‘Sync responsibility role data into the WF table’.
2. Run the request “Synchronize WF LOCAL tables”.
3. Run the request “Workflow Directory Services User/Role Validation” (with parameters 10000, yes,
yes, yes).
4. Have the user log off and back on and verify that the responsibility appears.
If after that the problem is not solved yet, then it’s possible to use the System Administrator responsibility: Workflow -> Oracle Applications Manager -> Workflow Manager


Selecting ‘Service Components’ and starting the following listeners:
- Workflow Deferred Notification Agent Listener
- Workflow Error Agent Listener
- Workflow Java Deferred Agent Listener
- Workflow Java Error Agent Listener


The user should then be able to use the unendate responsibility.
What Parameters Should Be Passed For "Workflow Directory Services User/Role Validation" Program? [ID 418765.1]

If does not solve the issue
update apps.wf_local_user_roles wur set wur.EFFECTIVE_END_DATE = to_date('01/01/9999','DD/MM/YYYY')
where role_end_date is null and expiration_date is null and USER_NAME = 'USERID'
Query to get the list of user having particular responsibility
select responsibility_key,RESPONSIBILITY_NAME,RESPONSIBILITY_ID from FND_RESPONSIBILITY_VL where responsibility_name = 'Service Contracts Manager';

Daily use scripts for Apps DBA

/* To find the latest application version */

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS "how it is done",BASE_RELEASE_FLAG "Base version" FROM AD_RELEASES where END_DATE_ACTIVE IS NULL

/* Query to find out if any patch except localisation patch is applied or not, if applied, that what all drivers it contain and time of it's application*/

select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.PATCH_DRVIER_ID, B.DRIVER_FILE_NAME, B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATIONG_DATE, B.FILE_SIZE, B.MERGED_DRIVER_FLAG, B.MERGE_DATE from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID and A.PATCH_NAME = ''

/* To know that if the patch is applied successfully, applied on both node or not, start time of patch application and end time of patch application, patch top location , session id ... patch run id */

select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.INSTANCE_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRVIERS C, AD_APPLIED_PATCHES D where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID and A.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '')) ORDER BY 3;

/* to find the base application version */

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES where BASE_RELEASE_FLAG = 'Y'


/* To find all available application version */

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, END_DATE_ACTIVE "when lasted", CASE WHEN BASE_RELEASE_FLAG = 'Y' Then 'BASE VERSION' ELSE 'Upgrade' END "BASE/UPGRADE", ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES


/* To get file version of any application file which is changed through patch application */

select A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME, max(B.VERSION) from AD_FILES A, AD_FILE_VERSIONS B where A.FILE_ID = B.FILE_ID and B.FILE_ID = 86291 group by A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME


/* To get information related to how many time driver file is applied for bugs */

select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS where BUG_NUMBER = ''

/* To find latest patchset level for module installed */

select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME

/* To find what is being done by the patch */

select A.BUG_NUMBER "Patch Number", B. PATCh_RUN_BUG_ID "Run Id",D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F where A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_ID and A.BUG_NUMBER = '' and B.PATCH_RUN_BUG_ID = ' < > ' and C.EXECUTED_FLAG = 'Y' GROUP BY A.BUG_NUMBER, B.PATCH_RUN_BUG_ID, D. APP_SHORT_NAME, D>SUBDIR, D.FILENAME, E.ACTION_CODE


/* To find Merged patch Information from database in Oracle Applications */

select bug_number from ad_bugs where bug_id in ( select bug_id from ad_comprising_patches where patch_driver_id =(select patch_driver_id from ad_patch_drivers where applied_patch_id =&n) );

/* Second Query to know, what all has been done during application of PATCH */

Select J.PATCh_NAME, H.APPLICATIONS_SYSTEM_NAME Instance_Name, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME appl_top,D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B,AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F, AD_PATCH_RUNS G,
AD_APPL_TOPS H, AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J where A.BUG_ID = B.BUG_ID and
B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID
and D.FILE_ID = F.FILE_ID and G.APPL_TOP_ID = H.APPL_TOP_ID and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and
I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and B.PATCH_RUN_ID = G.PATCH_RUN_ID and C.EXECUTED_FLAG = 'Y' and
G.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = 'merged'))
GROUP BY J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME, D.SUBDIR,
D.FILENAME, E.ACTION_CODE


/* Script to find out Patch level of mini Pack */

Select product_version,patch_level from FND_PROUDCT_INSTALLATIONS where patch_level like '%&shortname%';

Replace short name by name of Oracle Apps Minipack for which you want to find out Patch level . ex.
AD - for Applications DBA
GL - for General Ledger
PO - Purchase Order


Where is Concurrent Manager Node ? If you don't know where is your CM here are few ways to find your it .

SQLPLUS> select node_name from apps.fnd_nodes where support_cp='Y';


Here I am listing Unix/Linux commnads which might be useful while troubleshooting Oracle Apps.


Enable Trace on any Executable to find out whats happening at O.S. Level

truss -eafl -o output.trc -o truss.txt
for example for Apache
truss -eafl -o output.trc -o truss.txt apachectl

This command will trace any system calls and will help you to find out errors.



How to find a "word" or pattern in all files in a directory & subdirectories

find . -name "*" -exec grep -l {} \; -print
for example I want to search for word oracle
find . -name "*" -exec grep -l oracle {} \; -print



How to delete files older than N number of days , Useful to delete old log files

find . -name '*.*' -mtime + -exec rm {} \;
for example if I want to delete all files older than 7 days
find . -name '*.*' -mtime +7 -exec rm {} \;
*Check carefully & run it first from logs or dump directory


How to find a class inside a set of Jar files


for i in 'find .-name *.jar'
do
if test 'jar -tvf $i|grep QualityObject.class'
then
ls $i
fi
done


How to find the symbolic links that point to the old path in your oracle_home and appl_top.

This command is useful in cloning after restore from source to target that symbolic link are not pointing to source.

ls -al `find . -type l` | grep $OLD_PATH



To find all the text files that contains the old path at the UNIX level.

find . -type f -print|grep -v ":"|xargs file|grep "text"|awk '{FS=":"}{print $1}'|xargs grep -il $OLD_PATH



How to Sort files based on Size of file in a Directory
Useful in finding out spaces issues

ls -l | sort -nrk 5 | more


How to check if a Port is listening for any Service

netstat -an | grep $PORTNO

How to schedule a Job in Unix

Use cronjob
crontab -l ( list current jobs in cron)
crontab -e ( edit current jobs in cron )
_1_ _2_ _3_ _4_ _5_ $Job_Name
1 - Minutes (0-59)
2 - Hours ( 0-24)
3 - day of month ( 1- 31 )
4 - Month ( 1-12)
5 - A day of week ( 0- 6 ) 0 -> sunday 1-> monday
e.g. 0 0 1 * 5 Means run job at Midnight on 1st of month & every friday



Profile Options Useful for Oracle Apps DBA

Here is the list of few profile options which Apps DBA use frequently. It is not necessary that you as Apps DBA must know all profile options, it depends on your implemnetation. I am going going to update more about Profile Options.

Applications Help Web Agent
Applications Servlet Agent
Applications Web Agent
Concurrent: Active Request Limit
Concurrent: Hold Requests
Concurrent: Multiple Time Zones
Concurrent: Report Access Level
Concurrent: Report Copies
Concurrent: Request priority
Database Instance
Enable Security Group
FND: Debug Log Filename
FND: Debug Log Level
Forms Runtime Parameters
Gateway User ID
ICX: Discoverer Launcher
ICX: Forms Launcher
ICX: Report Launcher
ICX: Limit Connect
ICX: Limit time
ICX: Session Timeout
MO Operating Unit
Node Trust Level
RRA: Delete Temporary Files
RRA: Enabled
RRA: Service Prefix
RRA: Maximum Transfer Size
Self Service Personal Home Page Mode
Sign-On: Audit Level
Signon Password Failure Limit
Signon Password Hard to Guess
Signon Password Length
Signon Password No Reuse
Site Name
Socket Listener Port
TCF: Host
TCF: Port
TWO TASK
Viewer: Text


Below you will find commonly asked questions about various component Version in Oracle Applications & way to find them which might be helpful in Apps DBA Job. Most of these are for Unix / Linux.


Q: How to find Oracle Applications Web Server Version ?
Q: How to find Version of Apache used with oracle apps 11i ?

Log to Application tier as Operating System Usually called as APPLMGR ; go to location $IAS_ORACLE_HOME/Apache/Apache/bin and run command
./httpd -version
You will see output like

Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built: Dec 6 2005 14:59:13 (iAS 1.0.2.2.2 rollup 5)
Which means you are on iAS Version 1.0.2.2.2 with patchset rollup 5 with Apache Version 1.3.19


Server version: Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server

Above is output If you have installed 10g Application Server with 11i


Q: How to find Jinitiator Version ?

Check for file like appsweb_SID_HOSTNAME.cfg under $OA_HTML/bin defined by environment variable FORMS60_WEB_CONFIG_FILE & search for entry like jinit_ver_name , you will see entry like
jinit_ver_name=Version=1,3,1,23
which means Jinitiator version is 1.3.1.23 ; if your version is 1.3.1.18 you will see entry like 1,3,1,18


Q: How to find Forms Version in 11i ?

Login to forms from frontend , on top menu bar of forms click on "Help" & Select "About Oracle Applications" go to "Forms Server " section. You should see entry like below depending on your forms version
Oracle Forms Version : 6.0.8.26.0
Which mean you are on forms version 6.0.8.26 . If you want to know whats your forms patchset level then subtract 9 from fourth digit which means for above case form patchset 17 is applied.


Q: How to find Forms Version in Apps from command Line ?

Enter "f60gen" on Forms Server and check for first line in output like
Forms 6.0 (Form Compiler) Version 6.0.8.26.0 (Production)
This confirms that you are on forms server version 6.0.8.26.0 and patch set 17. ( Patch Set = Fourth Digit - 9)


Q: How to find Version of any file in Oracle Apps 11i ? or
Q: How to find any Reports Version 11i ? or

In Oracle Applications under ad utilities there is utility called as adident Used for Identification purpose or to find out file version use
adident Header
for ex. inorder to to find file version of one AR form i.e. ARXGLCOR.fmx
adident Header ARXGLCOR.fmx
You should see output like
$Header APPSTAND.fmb 115.33 2002/04/04 11:13:40 pkm ship
$ $Header ARXGLCOR.fmb 115.15 2005/01/31 13:48 mraymond ship
Which means above form executable consist of two forms whose version is 115.33 & 115.15 resp. Similarly you can use adident to find version of any report in 11i.


Q: How to find Operation System Version (Unix/Linux) ?

For solaris use command
uname -a
You will see output like
For Solaris SunOS servername 5.8 Generic_117350-23 sun4u sparc SUNW,Sun-Fire-V240
For RedHat Linux use command
cat /etc/*release*
You will see output like
Red Hat Enterprise Linux AS release 3 (Taroon Update 6)

Which means you are on Solaris 5.8 or Linux AS 3 resp.

Q: How to find if your Operating System is 32 bit or 64 Bit ?

For solaris use command
isainfo -v
If you see out put like
32-bit sparc applications
That means your O.S. is only 32 bit but if you see output like


64-bit sparcv9 applications
32-bit sparc applications
above means your o.s. is 64 bit & can support both 32 & 64 bit applications


Q: How to find Operating System version ?

For solaris use command
uname -a
You will see output like
For Solaris SunOS servername 5.8 Generic_117350-23 sun4u sparc SUNW,Sun-Fire-V240
For RedHat Linux use command
cat /etc/*release*
You will see output like
Red Hat Enterprise Linux AS release 3 (Taroon Update 6)

Which means you are on Solaris 5.8 or Linux AS 3 resp.

Q: How to find if your Operating System is 32 bit or 64 Bit ?
For solaris use command
isainfo -v
If you see out put like
32-bit sparc applications
That means your O.S. is only 32 bit but if you see output like


64-bit sparcv9 applications
32-bit sparc applications
above means your o.s. is 64 bit & can support both 32 & 64 bit applications



Q: Can I run 64 bit application on 32 bit Operating system ?

You can run 32 bit application (like oracle application server, web server, all oracle application server are 32 bit ) on both 32 /64 bit operating system but a 64 bit application like 64 bit database can run only on 64 bit operating system.

Q How to find if your database is 32 bit or 64 bit(Useful in applying Patches) ?


execute "file $ORACLE_HOME/bin/oracle" , you should see output like
/u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1
which means you are on 64 bit oracle
If your oracle is 32 bit you should see output like
oracle: ELF 32-bit MSB executable SPARC Version 1


Q. How to find Version of Apps 11i ?

Run following SQL from apps user

SQL> select RELEASE_NAME from fnd_product_groups;
You should see output like
RELEASE_NAME
-----------------------
11.5.10.2
Which means you are on Apps Version 11.5.10.2

Q: Can I run 64 bit application on 32 bit Operating system ?

You can run 32 bit application (like oracle application server, web server, all oracle application server are 32 bit ) on both 32 /64 bit operating system but a 64 bit application like 64 bit database can run only on 64 bit operating system.


Q How to find if your database is 32 bit or 64 bit(Useful in applying Patches) ?

execute "file $ORACLE_HOME/bin/oracle" , you should see output like
/u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1
which means you are on 64 bit oracle
If your oracle is 32 bit you should see output like
oracle: ELF 32-bit MSB executable SPARC Version 1
Now you know what should be bit of patch to download



Q How to Discoverer Version installed with Apps ?

Discoverer with Apps installed in ORACLE_HOME same as 806 is usually 3i or 4i. To find Version login to Application Tier & go to $ORACLE_HOME/discwb4/bin and execute
strings dis4ws | grep -i 'discoverer version'
You should see output like
Discoverer Version:Session 4.1.47.09.00

Q. How to find Workflow Version embedded in Apps 11i ?

Run following SQL from apps user ;
SQL>select TEXT from WF_RESOURCES where NAME='WF_VERSION';

You should see output like
TEXT
-----------------------
2.6.0
Which means you are on Workflow Version 2.6.0


You can also use script wfver.sql in FND_TOP/sql to find version of workflow in Apps.


Q: How to find version of JDK Installed on Apps ?

There might be multiple JDK installed on Operating System . Like JDK 1.3.1, 1.4.2 or 1.5 but in order to find which Version of JDK your Apps is using
Open your Context File $SID_$HOSTNAME.xml under $APPL_TOP/admin and look for variable
JDK_TOP oa_var="s_jdktop" what so ever value assigned against that parameter go to that directory & cd bin & execute command
./java -version so lets assume entry above is /usr/jdk then cd /usr/jdk/bin & ./java -version , you will see output like

java version "1.4.2_10"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_10-b03)
Java HotSpot(TM) Client VM (build 1.4.2_10-b03, mixed mode)

R12 logs--454178.1

OPMN errors at startup

Possible symptoms:

• A failure is noted in "$ADMIN_SCRIPTS_HOME/adstrtal.sh",
• Attempts to start the opmn using "$ADMIN_SCRIPTS_HOME/adopmnctl.sh" fail
• "$ADMIN_SCRIPTS_HOME/adopmnctl.sh status" fails

Useful log files:

Examine opmn.log, opmn.out

HTTP Server fails at startup

Possible symptoms:

• "$ADMIN_SCRIPTS_HOME/adopmnctl.sh status" indicates the HTTP Server is down
• "$ADMIN_SCRIPTS_HOME/adapcctl.sh start" fails

Useful log files:

Examine HTTP_Server~<#>, error_log

OC4J Applications Module (oacore, forms, oafm) fails at startup

Possible symptoms:

• Executing "$ADMIN_SCRIPTS_HOME/adopmnctl.sh status" indicates the module is down

Note:The forms module will show as down, if forms are configured to run in socket mode (this is not an error)

• Script adctl.sh start fails
• Example: "$ADMIN_SCRIPTS_HOME/adoacorectl.sh start"

Useful log files:

Examine: OC4J~~default_group~<#>, std.out, std.err

HTML requests complete, Java requests do not

• "$ADMIN_SCRIPTS_HOME/adopmnctl.sh status" shows the oacore module(s) alive
• http:///OA_HTML/jsp/fnd/aoljtest.jsp returns no page

Useful log files:

Examine: OC4J~~default_group~<#>, std.out, std.err, application.log

Additional debugging steps:

• set s_jsp_dbg_mode in $CONTEXT_FILE to true
• Run Autoconfig $ADMIN_SCRIPTS_HOME/adautocfg.sh
• Stop then Start $ADMIN_SCRIPTS_HOME/adoacorectl.sh service
• Examine application.log for missing jsp files (see Known Issues)

HTML requests complete, Java requests complete, Applications Login page fails

Useful log files:

Examine: application.log , $APPLRGF/javacache.log (see Known Issues)

Additional debugging steps:

If the Applications Login page is returned or an error message such as: "You have encountered an unexpected error. Please contact the System Administrator for assistance" then the problem is with Oracle Applications setup rather than Oracle Application Server.

• Examine the output from aoljtest.jsp
• http:///OA_HTML/jsp/fnd/aoljtest.jsp
• Enable AFLogging
• Open the file $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/config/oc4j.properties in a text editor
• Add:the following values:
• AFLOG_ENABLED=true
• AFLOG_LEVEL=statement
• AFLOG_MODULE=% (% captures all - preferable to use products such as fnd, ies, por)
• AFLOG_FILENAME=/tmp/1.log
• Stop then restart $ADMIN_SCRIPTS_HOME/adoacorectl.sh service, reproduce the issue, and examine the log file denoted by AFLOG_FILENAME

Applications Login Page is displayed, Login Fails

Debugging steps:

• Examine the output from aoljtest.jsp

• http:///OA_HTML/jsp/fnd/aoljtest.jsp
• Enable AFLogging
• Edit $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/config/oc4j.properties
• Add: values
• AFLOG_ENABLED=true
• AFLOG_LEVEL=statement
• AFLOG_MODULE=% (% captures all, better to use products i.e fnd,ies,por,etc..)
• AFLOG_FILENAME=/tmp/1.log
• Stop then Start $ADMIN_SCRIPTS_HOME/adoacorectl.sh service, recreate issue, examine log file denoted in AFLOG_FILENAME..

Return to Top

Oracle Application Server Diagnostic Tools

The tools, techniques, and examples shown below should be used in conjunction with the supporting documentation, which gives more detail of the options available. If in doubt how to proceed, post your questions in an OracleMetalink Forum or open a service request with Oracle Support.

Recommendation: Do not enable debug settings indiscriminately. Standard logging should be used first, to determine which of the tools below is most suited to help uncover the source of a particular problem.

OPMN

The OPMN server provides a means to enhance debug logging during runtime if needed. Higher logging levels may be set for the ons (Oracle Notification Server) or the pm (Process Manager).
Example: Enabling debug values for OPMN in a UNIX environment at runtime. The log values will be disabled upon restart.

prompt> . $INST_TOP/ora/10.1.3/.env
prompt> opmnctl query target=log
prompt> internal;ons;pm -------------(default Applications response)
prompt> opmnctl set target=debug comp="ons[all];pm[all]"

A full list of query options is available in Oracle® Process Manager and Notification Server Administrator's Guide 10g Release 3 (10.1.3), Chapter 6
Log file: opmn.dbg

Recommendation: Enable usage of the opmn.dbg file only after checking with Oracle Support. The opmn.dbg file is used by Support to debug and diagnose OPMN issues. Messages that are contained in the opmn.dbg file are typically not readily comprehensible to the user.

Return to Top

Java Object Cache Monitor

In distributed mode (the Applications Release 12 default mode), the Java Object Cache can share objects and communicate with other caches running either locally on the same system or remotely across the network. Object updates and invalidations are propagated between communicating caches. Distributed mode supports object persistence across system shutdowns and program failures. It may become necessary to review information in regards to the controller and objects cached. Oracle Application Server provides a cache monitoring utility available at the operating system level for Applications Release 12.
Example: Initializing CacheWatchUtil in a UNIX environment

prompt >. ./$APPL_TOP/APPS(sid).env
prompt >java -jar $ORACLE_HOME/../10.1.3/javacache/lib/cache.jar watch \
> java -jar $ORACLE_HOME/../10.1.3/javacache/lib/cache.jar watch -config=$ORA_CONFIG_HOME/10.1.3/javacache/admin/javacache.xml
cache >

An explanation of values and options may be found in Oracle® Containers for J2EE Services Guide 10g Release 3 (10.1.3):Chapter 7 Java Object Cache:Monitoring and Debugging
Return to Top

Applications Server Forms Servlet Debug

Applications Release 12 provide Forms Servlet architecture by default through Rapid Install. This information is specific to the servlet itself: issues with Forms and forms architecture should be addressed using Forms Tracing and JVM Client Tracing

• To test the j2ee Forms Module configuration: http://yourserver/forms
• To debug runtime issues within the Forms Servlet:
• Edit the profile option ICX:Forms Launcher (user level)
• Values available: [reference]
• http://yourserver/forms/frmservlet?serverURL=/forms/lservlet/session --- Log messages are written whenever a Forms session starts or ends. These give the host name and IP address of the client (the computer on which the user's web browser is running), the runtime process id, and a unique internal session id number.
• http://yourserver/forms/frmservlet?serverURL=/forms/lservlet/sessionperf --- Performance summary statistics are included with the session end message.
• http://yourserver/forms/frmservlet?serverURL=/forms/lservlet/perf --- A performance message is written for every request from the client.
• http://yourserver/forms/frmservlet/debug?serverURL=/forms/lservlet/debug --- Full debug messages. Other debug messages are written in addition to the messages mentioned above. This logging level is very verbose and is intended mainly for debugging and support purposes.

Log file: application.log
Forms Servlet Documentation: Oracle® Application Server Forms Services Deployment Guide 10g Release 2 (10.1.2) Chapter 8
Return to Top

Class Loaders Debugging

To provide a generic summary of Class Loading as it applies to Oracle Applications Release 12, the following paragraph has been extracted from Oracle® Containers for J2EE Developer's Guide 10g Release 3, and paraphrased appropriately.
Standard classloaders are linked together in a parent-child hierarchy, with each classloader having an associated parent classloader. This hierarchy represents a tree structure, in which a child classloader imports a set of classloaders from its parent loader. In the OC4J context, all J2EE Applications Modules running within an OC4J instance are children of the system application. These Web Modules use multiple class loaders in place of a single system loader. Each deployed application (OACore, OAFM, Forms) has at least one loader, separate from all other applications; this means that each also has a distinct class-path. For each class lookup, the search begins at a specific loader level, and can normally only visit loaders "above" it. This means that a search starting at the Applications Web Module loader level would have a very different class-path than one starting at the system loader level.
Detailed information on on Class Loading within the J2EE Module may be found in Chapter 3 of Oracle® Containers for J2EE Developer's Guide 10g Release 3 (10.1.3).
Debugging Class Loaders in Applications Release 12 requires manual editing of the relevant files. The following example illustrates how to enable debug values for the Applications Module oacore in opmn.xml:

 ---------- excerpt ----------





data id="java-options" value="-server -verbose:gc -Xmx512M -Xms128M -XX:MaxPermSize=160M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB -XX:+UseParallelGC -XX:ParallelGCThreads=2 -Dcom.sun.management.jmxremote -Djava.security.policy=$ORACLE_HOME/j2ee/oacore/config/java2.policy -Djava.awt.headless=true -Dhttp.webdir.enable=false -Dclass.load.log.level=finest -Dclass.load.trace=loader-create -Dclass.load.log.file=/oracle/test.log -Doracle.security.jazn.config=/oracle/inst/apps/VIS_treebeard/ora/10.1.3/j2ee/oacore/config/jazn.xml"/>
---------- end excerpt ----------

-Dclass.load.log.level=

• For debugging information the level should be set at "all" or below default
• Available values
• all - Output all log messages.
• severe - Output messages at the SEVERE level only.
• warning - Output messages at the WARNING level or above.
• info - Output messages at the INFO level or above. Note that if the log level is set below "info" - for example, to "finer", then trace output will be filtered from the logger output.
• config - Output messages at the CONFIG level or above. This is the default log level.
• fine - Output messages at the FINE level or above.
• finer - Output messages at the FINER level or above.
• finest - Output messages at the FINEST level or above.
• off - Suppress all logging messages.

-Dclass.load.trace=[[:[,]] | [~]]
There are many variations of values that may be used, depending upon the circumstances and issue. Further details on available events, may be found at: in Oracle® Containers for J2EE Developer's Guide 10g Release 3 (10.1.3): Chapter 3 Utilizing the OC4J Class Loading Framework: Table 3-13 class.load.trace System Property Values
Return to Top

Log Files

Log files provide an insight into the configuration and health of the Oracle Application Server. The tables below list the log file locations and provide a short description of the type of information recorded in each file.
Oracle Applications Release 12 allows application tiers to be shared. As part of this strategy, log files are in a central location for the specific Applications instance. Oracle Application Server log files referred to in this article are located on the application tier under $LOG_HOME/ora/10.1.3. The $LOG_HOME environment variable is defined in the Applications context file (which is pointed to by the environment variable $CONTEXT_FILE).

Note: It is not advisable to access files and alter logging levels that are not defined in the $CONTEXT_FILE (and therefore controlled through AutoConfig), unless requested by Oracle Support or Development for debugging purposes. If direct access is required to a file variable that is not editable, an enhancement request should be logged outlining the requirement and business need.

OPMN

Log File	Location	Description	Associated File and values	$CONTEXT_FILE Variable
opmn.log	opmn	Contains messages useful for monitoring the operations of the OPMN server. Output written to the opmn.log file contains the exit status of a child OPMN process. A status code of 4 indicates a normal reload of OPMN.	opmn.xml	none
opmn.dbg	opmn	Capture debug messages for Notification Server and Process Manager.	opmn.xml	none
opmn.out	opmn	Contains the standard output (stdout) and standard error (stderr) logs of OPMN. Also referred to as the OPMN "console log". After a certain point in OPMN initialization, nothing else will be written to this file.	none	none
OC4J~~default_group~<#> example: OC4J~forms~default_group~1	opmn	Process Control log file Standard out/err for j2ee Components (shows GC size and timing)	none	none
HTTP_Server~<#>	opmn	Process Control log file Standard out/err for Component - http	none	none
std.err example: oacorestd.err	opmn/ _default_group_<#> example: opmn/oacore_default_group_1	Records standard out information from the OC4J runtime process designated System.out	opmn.xml	none
std.out example: oacorestd.out	opmn/ _default_group_<#r> example: opmn/oacore_default_group_1	records standard error output from the OC4J runtime process designated System.err	opmn.xml	none

Return to Top

HTTP Server

Log File	Location	Description	Associated File and Values	$CONTEXT_FILE variable
access_log.xxxxx	Apache/logs	Records all requests processed by the server.	No debug levels available	N/A
error_log.xxx		Contains diagnostic information, record any errors encountered when processing requests. It is the first place to look when a problem occurs with starting the Application server or with operation of the Application server, since it will often contain details of what went wrong.	httpd.conf Directive: Variable: LogLevel Values: debug, info, notice, warn, error, crit, alert, emerg.	s_apache_loglevel
mod_rewrite.log		Records server rewrite actions.	httpd.conf Directive: mod_rewrite Variable:RewriteLogLevel Values: 0-9	None: default value 0
sec_audit.log		Used for auditing connections/requests.	security.conf Directive: mod_sec Variable:SecAuditEnginel Values: on, off, relevant	None: default value off
sec_debug.log		Debug security filter issues.	security.conf Directive:mod_sec Variable:SecFilterDebugLevel values: 0-9	None: default value 0
ssl_engine.log		SSL connection requests/negotiation.	ssl.conf Directive: mod_ssl Variable: SSLLogLevel values: none, error, warn, info, trace, debug	s_apache_loglevel

Return to Top

J2EE Applications Modules

Two types of log files can be generated by OC4J:

• Plain text log files are the default log files used for OC4J components, and are ideal for use in a development environment. The messages logged in these text files can be read with any editor, including the Oracle Enterprise Manager 10g Application Server Control Console
• Oracle Diagnostic Logging (ODL) log files use an XML format that is accessible via Oracle Applications Manager (requires OracleAS 10.1.3.1)

Note: ODL logging is not currently enabled or configurable using AutoConfig.

Log File	Location	Description	Associated File and Values	$CONTEXT_FILE variable
application.log	j2ee//_default_group_# example: j2ee/oacore/oacore_default_group_1	Text log file -- records actions specific to the application examples: servlet and jsp initializations.	system-application.xml	none
		When true adds stack trace for certain runtime exceptions, shows full path from missing jsp files.	orion-web.xml	s_jsp_dbg_mode default: false
global-application.log	j2ee//_default_group_# example: j2ee/oacore/oacore_default_group_1	Text Log File - records information related to all deployed J2EE Application Components start/stop	application.xml	none
system-application.log		Text Log File - records internal system J2EE application initialization.	system-application.xml	none
server.log		Text Log File - records Notification server (ons) actions.	server.xml	none
jms.log		Text Log File - records JMS (Java Messaging Service) actions for distributed environments.	jms.xml	none
rmi.log		Text Log File - records RMI (Remote Method Invocation) actions for distributed environments.	rmi.xml	none
log.xml		ODL Logging	j2ee-logging.xml	By default, currently commented out in configuration files.
			system-application.xml application.xml server.xml jms.xml rmi.xml

Return to Top

Known Issues

JOC fails to initialize

Errors:
Browser:
500 Internal ServerError
java.lang.NoClassDefFoundError at oracle.apps.fnd.sso.AppsLoginRedirect.AppsSetting(AppsLoginRedirect.java:120) at oracle.apps.fnd.sso.AppsLoginRedirect.init(AppsLoginRedirect.java:161)
Application.log:
html: Error initializing servlet
java.lang.NoClassDefFoundError
at oracle.apps.fnd.sso.AppsLoginRedirect.AppsSetting(AppsLoginRedirect.java:120)
at oracle.apps.fnd.sso.AppsLoginRedirect.init(AppsLoginRedirect.java:161)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0) ].server.http.HttpApplication.loadServlet(HttpApplication.java:2231)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0) ].server.http.HttpApplication.findServlet(HttpApplication.java:4617)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0) ].server.http.HttpApplication.findServlet(HttpApplication.java:4541)
The issue above has been observed both intermittently and at startup. In both cases the issue is with the port value for the java object cache. Either a port conflict at startup or multiple instances with JOC in distributed mode attempting to use the same port resulting in intermittent errors.
Solution:
Choose another value for $CONTEXT_FILE variable: s_java_object_cache_port

Compiled JSP files are removed or corrupted

In Applications Release 12, class files for .jsp's are not compiled at runtime for performance reasons. If the files are removed, directories may be changed or files may become corrupted
Example Error in Debug Mode:
Missing class: _OA
Missing class: _AppsLocalLogin
Missing class: _OAInfo
Missing class: _jsp._fnd._aoljtest
javax.servlet.ServletException: oracle.classloader.util.AnnotatedClassNotFoundException:
Solution:
Compile associated class files using ojspCompile.pl:
$FND_TOP/patch/115/bin/ojspCompile.pl ojspCompile.conf --compile --flush -p 20
OracleMetaLink Note 215268.1 contains complete syntax and definition

JOC fails in distributed mode when registering group members on AIX

This problem may be noted after a restart of the oacore process.
Error noted in $APPLRGF/javacache.log:
Cache initialization failed: reason
java.lang.ClassCastException: java.lang.Long incompatible with
oracle.ias.cache.group.Address
at oracle.ias.cache.group.Address.equals(Unknown Source)
at java.util.Hashtable.contains(Hashtable.java:347)
Error noted in application.log:
IAS Cache initialization failed. The Distributed Caching System failed to initialize on port: xxxxxx.The list of hosts in the distributed caching system is: Yourmachine.domain.com. The port xxxxx should be free on each host running the JVM's. The default port xxxxx can be overridden using -Doracle.apps.jtf.cache.IASCacheProvidercacheProvider.port=
Error noted in browser:
500 Internal Server Error

java.lang.NoClassDefFoundError: oracle.apps.fnd.profiles.Profiles (initialization failure)
at java.lang.J9VMInternals.initialize(J9VMInternals.java:123)
at oracle.apps.fnd.sso.AppsLoginRedirect.AppsSetting(AppsLoginRedirect.java:120)
at oracle.apps.fnd.sso.AppsLoginRedirect.init(AppsLoginRedirect.java:161)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0) ].server.http.HttpApplication.loadServlet(HttpApplication.java:2231)
Solution:
Apply patches 5261515 and 5946958
Bug 5968938 has been filed to include 5946958 in OracleAS 10.1.3.4

OPMN-controlled service will not start (Apache/OAcore/Any)

Error in std.err file:
Error initializing server: Error initializing ejb-modules: Resource exception(OracleASjms) for MessageDrivenBean event during endpoint activation: failure looking up ConnectionFactoryJndiName:jms/XAQueueConnectionFactory: javax.naming.NameNotFoundException: jms/XAQueueConnectionFactory not found
Solution:
Remove any *.lock files in $ORA_CONFIG_HOME/10.1.3/j2ee//persistence//
This issue is commonly found when the operating system is shut down without shutting down the Oracle Application Server, or where the instance is a clone made from a source where OracleAS was running.