Oracle Apps Log files locations

Database Tier Logs are

Alert Log File location:

$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log


Trace file location:

$ORACLE_HOME/admin/SID_Hostname/udump

Application Tier Logs


Start/Stop script log files location:

$COMMON_TOP/admin/log/CONTEXT_NAME/ 


OPMN log file location

$ORACLE_HOME/opmn/logs/ipm.log


Apache, Jserv, JVM log files locations:

$IAS_ORACLE_HOME/Apache/Apache/logs/ssl_engine_log

$IAS_ORACLE_HOME/Apache/Apache/logs/ssl_request_log

$IAS_ORACLE_HOME/Apache/Apache/logs/access_log

$IAS_ORACLE_HOME/Apache/Apache/logs/error_log

$IAS_ORACLE_HOME/Apache/JServ/logs


Concurrent log file location:

$APPL_TOP/admin/PROD/log or $APPLLOG/$APPLCSF

$APPLCSF/$APPLOG and $APPLCSF/$APPLOUT

Patch log file location:

$APPL_TOP/admin/PROD/log


Worker Log file location:

$APPL_TOP/admin/PROD/log


AutoConfig log files location:

Application Tier:

$APPL_TOP/admin/SID_Hostname/log//DDMMTime/adconfig.log


Database Tier:

$ORACLE_HOME/appsutil/log/SID_Hostname/DDMMTime/adconfig.log


Error log file location:

Application Tier:

$APPL_TOP/admin/PROD/log


Database Tier :

$ORACLE_HOME/appsutil/log/SID_Hostname


In Oracle Applications R12, the log files are located in $LOG_HOME (which translates to $INST_TOP/logs)

Below list of log file locations could be helpful for you:


Concurrent Request related logs

$LOG_HOME/appl/conc - > location for concurrent requests log and out files

$LOG_HOME/appl/admin - > location for mid tier startup scripts log files


Apache Logs (10.1.3 Oracle Home which is equivalent to iAS Oracle Home - Apache, OC4J and OPMN)

$LOG_HOME/ora/10.1.3/Apache - > Location for Apache Error and Access log files

$LOG_HOME/ora/10.1.3/j2ee - > location for j2ee related log files

$LOG_HOME/ora/10.1.3/opmn - > location for opmn related log files


Forms & Reports related logs (10.1.2 Oracle home which is equivalent to 806 Oracle Home)

$LOG_HOME/ora/10.1.2/forms

$LOG_HOME/ora/10.1.2/reports


Startup/Shutdown Log files location:

$INST_TOP/apps/$CONTEXT_NAME/logs/appl/admin/log


Patch log files location:

$APPL_TOP/admin/$SID/log/ 


Clone and AutoConfig log files location in Oracle E-Business Suite Release 12 

Logs for the adpreclone.pl are located: 

On the database tier: 

RDBMS $ORACLE_HOME/appsutil/log/< context >/StageDBTier_< timestamp >.log 


On the application tier: 

$INST_TOP/admin/log/StageAppsTier_< timestamp >.log 


Where the logs for the admkappsutil.pl are located? 

On the application tier: 

$INST_TOP/admin/log/MakeAppsUtil_< timestamp >.log

How to Trace a Concurrent Request And Generate TKPROF File

1. Enable Tracing For The Concurrent Manager  Program 
Responsibility: System Administrator
Navigate: Concurrent > Program > Define
Query Concurrent Program
Select the Enable Trace Checkbox 
Turn On Tracing
Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option Concurrent: Allow Debugging
Set profile to Yes
 Run Concurrent Program With Tracing Turned On
Logon to the Responsibility that runs the Concurrent Program 
 In the Submit Request Screen click on Debug Options (B)
Select the Checkbox for SQL Trace


 2. Find Trace File Name
  Run the following SQL to find out the Raw trace name and location for the concurrent program.  The SQL prompts the user for the request id

SELECT ‘Request id: ‘||request_id ,  ‘Trace id: ‘||oracle_Process_id,  ‘Trace Flag: ‘||req.enable_trace,  ‘Trace Name:  ‘||dest.value||’/’||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’,  ‘Prog. Name: ‘||prog.user_concurrent_program_name,  ‘File Name: ‘||execname.execution_file_name|| execname.subroutine_name ,  ‘Status : ‘||decode(phase_code,’R’,’Running’)  ||’-‘||decode(status_code,’R’,’Normal’),  ‘SID Serial: ‘||ses.sid||’,’|| ses.serial#,  ‘Module : ‘||ses.module  from fnd_concurrent_requests req, v$session ses, v$process proc,  v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,  fnd_executables execname  where req.request_id = &request  and req.oracle_process_id=proc.spid(+)  and proc.addr = ses.paddr(+)  and dest.name=’user_dump_dest’  and dbnm.name=’db_name’  and req.concurrent_program_id = prog.concurrent_program_id  and req.program_application_id = prog.application_id  and prog.application_id = execname.application_id  and prog.executable_id=execname.executable_id; 


To know the Trace file location

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

3. TKPROF Trace File from concurrent request

Once you have obtained the Raw trace file you need to format the file using TKPROF.

$tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no

Where: raw_trace_file.trc: Name of trace file

output_file: tkprof out file

explain: This option provides the explain plan for the sql statements

sort: his provides the sort criteria in which all sql statements will be sorted.  This will bring the bad sql at the top of the outputfile.

sys=no:Disables sql statements issued by user SYS 

Another example: To get (TKPROF) sorted by longest running queries first and limits the results to the “Top 10” long running queries



$ tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)’ print=10 

example: tkprof  CR30326048.trc CR30326048.tkp sort='(fchela,exeela,prsela)' explain=apps/pass






tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
  print=integer    List only the first 'integer' SQL statements.
  aggregate=yes|no
  insert=filename  List SQL statements and data inside INSERT statements.
  sys=no           TKPROF does not list SQL statements run as user SYS.
  record=filename  Record non-recursive statements found in the trace file.
  waits=yes|no     Record summary for any wait events found in the trace file.
  sort=option      Set of zero or more of the following sort options:
    prscnt  number of times parse was called
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of disk reads during parse
    prsqry  number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis  number of misses in library cache during parse
    execnt  number of execute was called
    execpu  cpu time spent executing
    exeela  elapsed time executing
    exedsk  number of disk reads during execute
    exeqry  number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    exerow  number of rows processed during execute
    exemis  number of library cache misses during execute
    fchcnt  number of times fetch was called
    fchcpu  cpu time spent fetching
    fchela  elapsed time fetching
    fchdsk  number of disk reads during fetch
    fchqry  number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow  number of rows fetched
    userid  userid of user that parsed the cursor

Panaya ETL Extraction - How to extract & upload your custom code

Download the Oracle Extraction Tool

Select the Setup tab and then Code Box.

Select the relevant System from the drop down list

Download the Oracle extraction tool (jar file)
Always download the newest extraction code from Panaya's Code Box. It is not recommended to use older versions of the extraction.

Extracting the Custom Code and Usage Statistics

1. Upload panaya-extractor.jar to your Oracle Application Server.
2. Change your working directory to the location of the panaya-extractor.jar
3. Using your Oracle Apps OS user, execute the following command:
   

For Oracle EBS target version* up to 12.1.3 (including):

java -jar panaya-extractor.jar <apps password> [<ETL type>] [-customJavaTop=<Custom Java Top>]
 

For Oracle EBS target version* above 12.2 (including):

java -jar panaya-extractor.jar <apps password> <SYSTEM password> [<ETL type>] [-customJavaTop=<Custom Java Top>] 

* The target version is the version you wish to upgrade to 

Example

java -jar panaya-extractor.jar <appspass> <systempass> F

Note
If you wish to expedite the extraction process when upgrading to Oracle EBS version 12.2 and above, click here

   

Parameters explanation
<apps password> - The password of your apps schema in the DB
<SYSTEM password> - The password of your SYSTEM schema in the DB
[<ETL type>] - What data to extract during the ETL 

F - Full ETL (Customizations & Usage data) Please use this for the first extraction
C - Customizations only
U - Usage only

If no ETL type is specified, a Full ETL will be executed

[-customJavaTop=<Custom Java Top>] - Path(s) to your framework projects other than $JAVA_TOP.
If more than one path is defined in the classpath, use ':' as a delimiter - If -customJavaTop is not specified, will use only $JAVA_TOP.



During the extraction process, an output directory will automatically will be created for you.
The name of the directory will be output_<date>_<time>.
 

At the end of the process, one ZIP File will be created in your output directory, which you will need to upload to Panaya Code Box.

If you do not have access from your Application server to the Panaya web site, please copy the file from there.

If you run the Concurrent Manager on a different server than the Oracle Application server, a separate extraction must be done from each server.

Click here for more information

If you are having two node setup with concurrent managers on different server, then you need to copy the panaya extraction tool on both the servers and execute the command same as above. After we get the output file we need to merge it using below command.

A Merge tool is available to combine the two extracts into a single file which can be uploaded to Panaya’s servers.

• Run the Panaya extractor both on the Oracle Applications server and on one of the Concurrent Manager servers, as described in the section: EXTRACTING THE CUSTOM CODE AND USAGE STATISTICS.

Using your Oracle Apps OS user, execute the following command in order to merge the two files into one:
java -cp panaya-extractor.jar MergeFiles appsETL=<oa_etl> concETL=<cm_etl> [<new_etl>]
Parameters explanation

• <oa_etl> – is the full path of the extracted filename from the Oracle Application server
• <cm_etl> – is the full path of the extracted filename from the Concurrent Manager server
• <new_etl> – is the full path of the merged extracted filename. This is an optional parameter; the default value if not specified is "ETL_MERGE_ + timestamp.zip"

The merged file will be built as follows:

• reports.zip/bin.zip/sql.zip – will be copied from the cm_etl file
• framework.zip – will be copied from the cm_etl file, unless it is found in the oa_etl.zip file
• Appl_tops.properties, etl.log – will be copied from the cm_etl and renamed to conc_appl_tops.properties, conc_etl.log
• All other files will be copied from the oa_etl file

Example

java -cp panaya-extractor.jar MergeFiles appsETL=/b01/panaya_23feb/ETL_FULL_20160223_1633.zip concETL=/b01/knapps/panaya_23feb/output_20160223_0820/ETL_FULL_20160223_0820.zip /b01/knapps/panaya_23feb/merge/ETL_24feb.zip

Upload the Extract File

1. Upload the ZIP file to Panaya Code Box
   
2. Once uploaded, you should now receive a confirmation email regarding your custom code upload.
   Other users with the Upload Code and/or Manage Users permission will also receive a confirmation email. 

 To expedite the extraction process when upgrading to Oracle EBS target version 12.2 and above?

Oracle E-Business Suite Release 12.2 introduces Online Patching (OLP), a new feature that significantly reduces the patch downtime that was needed in previous releases. Panaya can greatly assist you in identifying the numerous changes required when implementing OLP.

The Panaya extract (ETL) includes 4 Oracle supplied SQL scripts (Readiness Reports) that will gather the necessary information for you about your instance and the OLP. These scripts can be very long running.

To improve the performance of these scripts, we highly recommend you refresh the SYS and data dictionary statistics to ensure that the database will use the most efficient execution plan.

Suggested commands (to be run at the DBA’s discretion) before the ETL:

1. SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS (‘SYS’);
2. SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);
3. SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
4. SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Concurrent Managers Questions

Concurrent Managers

A concurrent manager is itself a concurrent program that starts other concurrent programs running. When an application user submits a request to run a program, the request is entered into a database table that lists all of the requests. Concurrent managers read requests from the table and start programs running. 

The Internal Concurrent Manager, which functions as the “boss" of all the other managers. The Internal Concurrent Manager starts up, verifies the status of, resets, and shuts down the individual managers.

The Standard manager accepts any and all requests; it has no specialization. The Standard manager is active all the time; it works 365 days a year, 24 hours a day.

Transaction Managers

While conventional concurrent managers let you execute long-running, data-intensive application programs asynchronously, transaction managers support synchronous processing of the particular requests from client machines. A request from a client program to run a server-side program synchronously causes a transaction manager to run it immediately, and then to return a status to the client program.

Where do concurrent request or manager log files and output files go?

The concurrent manager first looks for the environment variable $APPLCSF. If this is set, it creates a path using two other environment variables: $APPLLOG and $APPLOUT It places the log files in $APPLCSF/$APPLLOG, output files go in $APPLCSF/$APPLOUT

So for example, if you have this environment set:
$APPLCSF = /u01/appl/common
$APPLLOG = log
$APPLOUT = out

Can I delete a concurrent manager?
You can disable the manager by checking the 'Enabled' checkbox, or you can simply Terminate the manager and it will not run again unless you reactivate it. If it is really necessary, you can query the manager in the
'Define Manager' form, and delete the row. (It is recommended that you DO NOT do this)

What is the function of the 'Conflict Resolution Manager'?
Concurrent managers read requests to start concurrent programs running. The Conflict Resolution Manager checks concurrent program definitions for incompatibility rules.

If a program is identified as Run Alone, then the Conflict Resolution Manager prevents the concurrent managers from starting other programs in the same conflict domain.

When a program lists other programs as being incompatible with it, the Conflict Resolution Manager prevents the program from starting until any incompatible programs in the same domain have completed running.

How do I clean out the Concurrent Manager tables?
Cleaning out the tables is a useful method of making sure that there are no invalid statuses that can prevent the managers from starting. Previously, this has been done by truncating fnd_concurrent_processes and/or fnd_concurrent_requests. Truncation of the tables is a little drastic, and can cause problems later when trying to purge requests, not to mention losing all of the request information.

Run the script, cmclean.sql, article Note 134007.1 CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables It will make sure the relevant status codes are valid without deleting any information.

I hit the Restart button to start the Standard manager, but it still did not start?
Telling a manager to restart just sets the status to Restart. The ICM will start it the next process monitor session or the next time the ICM starts. Use Activate to start a manager immediately. When a manager is deactivated manually, the ICM will not restart it, you will need to set it to Restart, or activate it manually.

How many rows are in FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROCESSES tables?
When tables reach above 3000-4000 rows, the performance begins to diminish. You may want to run Purge Concurrent Request on a regular basis, dependant on the amount of requests being run.

The Purge Concurrent Requests job can be used to purge: Requests, Mgr logs, and All requests depending on what is chosen.

Use the following options: Enter = All, Mode = AGE, Mode Value = 15

The std.mgr log continuously grows where it may good to archive it regularly.

Create new User with same responsibilities as an existing user

Find responsibilities attached to a user

SELECT frt.RESPONSIBILITY_NAME, furg.end_date
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,fnd_responsibility_tl frt,fnd_user fu
WHERE fu.user_name = '&&username'
AND   fu.user_id = furg.user_id
AND   furg.responsibility_id = fr.RESPONSIBILITY_ID
AND   frt.responsibility_id = fr.RESPONSIBILITY_ID
ORDER BY 1;

SIMPLEST WAY TO Create application users with same responsibilties  across instances

Downloading Application User Responsibilities

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_FND_USER_PASSI.ldt FND_USER USER_NAME='DC128877'

Uploading Application user Responsibilites in another instance

FNDLOAD apps/torpedor12 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_FND_USER_PASSI.ldt

Create another Application user with same Responsibilities as the existing application user.

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_FND_USER_PASSI.ldt FND_USER USER_NAME='DC128877'

Uploading Application user Responsibilites to another Applicatioon user in same instance

do vi *.ldt file
Change username to new username
vi XX_FND_USER_PASSI.ldt

In this ldt file change USER_NAME to new user - then upload the ldt file

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_FND_USER_PASSI.ldt

Note : If Application user does not exists it will create it and if it exists it will add the responsibilities its missing.

Displays a list of tablespaces and their used/full status.

SET PAGESIZE 140

COLUMN used_pct FORMAT A11
SELECT tablespace_name,
       size_mb,
       free_mb,
       max_size_mb,
       max_free_mb,
       TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct,
       RPAD(' '|| RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), 'X'),11,'-') AS used_pct
FROM   (
        SELECT a.tablespace_name,
               b.size_mb,
               a.free_mb,
               b.max_size_mb,
               a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
        FROM   (SELECT tablespace_name,
                       TRUNC(SUM(bytes)/1024/1024) AS free_mb
                FROM   dba_free_space
                GROUP BY tablespace_name) a,
               (SELECT tablespace_name,
                       TRUNC(SUM(bytes)/1024/1024) AS size_mb,
                       TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
                FROM   dba_data_files
                GROUP BY tablespace_name) b
        WHERE  a.tablespace_name = b.tablespace_name
       )
ORDER BY tablespace_name;

SET PAGESIZE 14

find all the users with Sysadmin Responsibility

SELECT fu.*
FROM fnd_user_resp_groups_direct furgd, fnd_responsibility_vl frvl, fnd_user fu
WHERE furgd.responsibility_id = frvl.responsibility_id
AND fu.user_id = furgd.user_id
AND(to_char(furgd.end_date) is null
OR furgd.end_date > sysdate)
AND frvl.end_date is null
AND frvl.responsibility_name = 'System Administrator'; 

Oracle apps R12 Resposibilities Undefined error

This short post is about how to fix the undefined issues in Oracle Application . Many times when we install the Java for Oracle EBS , name of all of the responsibilities change to undefined.

As shown below to fix this issue just click on Compatibility View . System will prompt you to login again and once you login  you can see correct responsibility Name.

Can not login to Applications : oracle.apps.fnd.framework.OAException: Could not load application module 'oracle.apps.fnd.sso.login.server.MainLoginPageAM'.

No user able to connect: 

oracle.apps.fnd.framework.OAException: Could not load application module

'oracle.apps.fnd.sso.login.server.MainLoginPageAM'.

## Detail 0 ##

oracle.apps.fnd.framework.OAException: Application: FND, Message Name:

FND_NO_TRANSACTION_ID.

===========CAUSE =============

One of the Tablespace is full 

=======SOLUTION =================

Increase the tablespace and retry ....

Ref: 737960.1
        

View the Text of the triggers

SQL> set long 100000

SQL> select DESCRIPTION, TRIGGER_BODY from user_triggers where trigger_name = '<YOUR TRIGGER>';

<YOUR TRIGGER> must be uppercase.

Oracle Database User Management

CREATING THE USER:

create user <USERNAME> identified by <PASSWORD> 

default tablespace <TABLESPACE NAME> 

temporary tablespace <TABLESPAC NAME> 

quota 30m on <TABLESPACE NAME>; 

Eg:

create user <John> identified by <John> 

default tablespace <users> 

temporary tablespace <temp> 

quota 30m on <users>; 

Above command creates a user chandra with password chandra. Consider the tablespace you have in which chandra will store his data is "USERS". The tablespace used for storing temporary segments will be "TEMP" and the amount of space which the user chandra can use on "USERS" tablespace is 30M.

DROPING THE USER:

            SQL> DROP USER John CASCADE;
 

ALTERING/UNLOCKING ACCOUNT: 

SQL> alter user john identified by john account unlock;

The above command alters or unlocks the "john" user with password "john".

SQL> select username, account_status, default_tablespace, 

temporary_tablespace, profile from dba_users 

where username = 'john';

SQL> select * from dba_ts_quotas where username = 'john'; 

GRANTING AND REVOKING PRIVILEGES:

Syntax:

SQL>GRANT <PRIVILAGE> TO <USERNAME>

SQL>REVOKE <PRIVILAGE> FROM <USERNAME>

Eg:
SQL> GRANT create table to john;‎ 
SQL> GRANT create session to john;
SQL> GRANT create any table, create tablespace to john;
SQL> REVOKE create any table from john;
SQL> REVOKE create tablespace from john;
SQL> GRANT select, insert, update, delete on <TABLENAME> to john;
SQL> REVOKE update,delete on USERS.PAY_PAYMENT_MASTER from john;
 

ROLES:

Creating Roles
Create Role	CREATE ROLE <role_name>;
	CREATE ROLE read_only;
Create Password Protected Role	CREATE ROLE <role_name> IDENTIFIED BY <password>;
	CREATE ROLE dba IDENTIFIED BY "johnny";
Assigning Privileges And Roles To Roles
Assign Privilege To A Role	GRANT <privilege_name> TO <role_name>;
	GRANT create session TO read_only
Create A Role Heirarchy	GRANT <role_name> TO <role_name>;
	CREATE ROLE ap_clerk; GRANT read_only TO ap_clerk; GRANT select ON general_ledger TO ap_clerk; GRANT insert ON ap_master TO ap_clerk; GRANT update ON ap_master TO ap_clerk; GRANT insert ON ap_detail TO ap_clerk; GRANT update ON ap_detail TO ap_clerk;
Add Another Layer To The Heirarchy	GRANT <roles and privileges> TO <role_name>;
	CREATE ROLE ap_manager IDENTIFIED BY appwd; GRANT ap_clerk TO ap_manager; GRANT delete ON ap_master TO ap_manager; GRANT delete ON ap_detail TO ap_manager; GRANT select any table TO ap_manager;
Assigning Roles
Assigning Roles To Users	GRANT <roles_name> TO <user_name>;
	GRANT read_only TO jbiden; GRANT ap_clerk TO jstough; GRANT ap_clerk TO ckeizer; GRANT ap_clerk TO rallen; GRANT ap_manager TO escott;
Revoking Privileges From Roles
Revoke Privilege	REVOKE <privilege_name> FROM <role_name>;
	REVOKE select any table FROM ap_manager;
Revoking Roles
Revoke a role from a user	REVOKE <role_name> FROM <user_name>;
	REVOKE ap_manager FROM escott;
Revoke A Role And Drop Any Invalidated Constraints	REVOKE ALL ON <table_name> FROM <schema_name>  CASCADE CONSTRAINTS;
	REVOKE ALL ON invoices FROM abc  CASCADE CONSTRAINTS;
Activating & Decactivating Roles
Activating A Role	SET ROLE <role_name>;
	SET ROLE ap_clerk;
Activating A Password Protected Role	SET ROLE <role_name> IDENTIFIED BY <role_password>;
	SET ROLE ap_manager IDENTIFIED BY appwd;
Activating All Roles	SET ROLE all;
Activating All Roles Except One	SET ROLE all EXCEPT <role_name>;
	SET ROLE all EXCEPT ap_manager;
Deactivating A Role	Can not be done on an indiviDUAL basis
Deactivating All Roles	SET ROLE none;
Drop Role
Dropping A Role	DROP ROLE <role_name>;
	DROP ROLE manager_role;