Showing posts from September, 2022

How to Check AD and TXK code levels in your EBS environment

Below query can be used in finding out current AD and TXK code levels.  [ap******@shsd*** 22989949]$ sqlplus apps SQL*Plus: Release - Production on Fri Sep 30 01:00:25 2022 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release - Production SQL> col ABBREVIATION for a10 set lines 1000 col NAME for a50 col CODELEVEL for a20 SELECT ABBREVIATION,NAME,codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');SQL> SQL> SQL> SQL> ABBREVIATI NAME                                               CODELEVEL ---------- -------------------------------------------------- -------------------- ad         Oracle Applications DBA                            C.13 txk        Oracle Applications Technology Stack               C.13 Check fwk in r12.2 select abbreviation ,codelevel from ad_trackable_entities where abbreviation in( 'fwk') order by abbreviation; SELECT codelevel

session_cached_cursors and open_cursor sizing

Pinning SQL with session_cached_cursors is especially useful for reentrant SQL that contains hoist variables,  and there are many incarnations of the SQL, each with a different host variable value.   The session_cached_cursors parameter is used to reduce the amount of parsing with SQL statements  that use host variables and with PL/SQL cursors.  The session_cached_cursors parameter has a default value of 50, and increasing the value of  session_cached_cursors will requires a larger shared_pool_size to cache the cursors.  ALTER SYSTEM SET OPEN_CURSORS = 500 SCOPE=BOTH; SESSION_CACHED_CURSORS specifies the number of session cursors to cache. ALTER SESSION SET SESSION_CACHED_CURSORS = 50; OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once.  Can  be used this parameter to prevent a session from opening an excessive number of cursors. For example, if OPEN_CURSORS is set to 100, then each session can have up to 100 cursors open

Hold, Un hold and cancel Pending Concurrent Requests

 Here is an easy way to put all the Pending Concurrent requests in hold, if we are going through any situation (Production Cutover/upgrade and go live scenarios). 1. First create a backup table  SQL> CREATE TABLE apps.conc_bkp AS SELECT * FROM fnd_concurrent_requests WHERE phase_code = 'P' AND NVL (hold_flag, 'N') <> ‘Y’; 2. Then put the reqs on hold using the following query, SQL> UPDATE apps.fnd_concurrent_requests SET hold_flag = 'Y' WHERE request_id IN ( SELECT request_id FROM apps.conc_bkp); SQL> Commit; 3. Then to release the hold use the following query, SQL> UPDATE fnd_concurrent_requests SET hold_flag = 'N' WHERE request_id IN ( SELECT request_id FROM apps.conc_bkp); SQL> Commit; NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same Create table apps.conc_req_on_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_

How to troubleshoot Output Post Processor Issues

  The Output Post Processor (OPP) is designed to support XML Publisher as a post-processing action for concurrent requests. If a request is submitted with an XML Publisher template specified as a layout for the concurrent request output, then after the concurrent manager finishes running the concurrent program, it will contact the OPP to apply the XML Publisher template and create the final output. Memory Usage Per Process: The maximum amount of memory or maximum Java heap size a single OPP process can use is by default set to 512MB. This value is needed by the Loader Data File: $FND_TOP/patch/115/import/US/afoppsrv.ldt which specifies that the DEVELOPER_PARAMETERS is “J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx512m”. How to determine the current 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’); Increase the maximum Java heap siz

How to use ADOP Patch Cycle

  A new environment variable, $FILE_EDITION, shows the current designation of a given dual file system member.  Three other new environment variables designate the root directories of the run ($RUN_BASE), patch ($PATCH_BASE), and non-editioned ($NE_BASE) file systems. For example: $FILE_EDITION = patch $RUN_BASE = /u01/R122_EBS/fs1 $PATCH_BASE = /u01/R122_EBS/fs2 $NE_BASE = /u01/R122_EBS/fs_ne When a patch is being applied, the Oracle E-Business Suite system is running in normal production mode  (full functionality, with some documented exceptions) in the run edition of the file system and database.  Full application functionality is retained as patch execution proceeds, until the cutover phase is reached. It is more appropriate to think in terms of a  patching cycle  than a single patching operation. The online patching cycle consists of a number of phases: Prepare Apply Finalize Cutover Cleanup You specify the desired phase or phases as arguments to the adop utility. The actions take