Cash Positioning Issue in 11i Instance

Hi All.

When attempting to Cash Positioning: Existing worksheet go button
the following error occurs an error:
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122


Step to reproduce:
Go to CM responsibility --> Cash positioning

Press "Go" button

Got the following error: You have encountered an unexpected error. Please contact the System Administrator for assistance

After changing FND: Diagnostics profile to YES, I can see the full error:

 oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.  Statement: SELECT * FROM (SELECT WorksheetHeadersEO.WORKSHEET_HEADER_ID, 

WorksheetHeadersEO.WORKSHEET_NAME,

WorksheetHeadersEO.COLUMNS_DISPLAYED,

WorksheetHeadersEO.CREATED_BY,

decode(WorksheetHeadersEO.CREATED_BY,

1, 'DeleteDisabled',

'DeleteEnabled') DELETE_SWITCHER,

decode(WorksheetHeadersEO.CREATED_BY,

1, 'UpdateDisabled',

'UpdateEnabled') UPDATE_SWITCHER

FROM CE_CP_WORKSHEET_HEADERS WorksheetHeadersEO) QRSLT  WHERE (EXISTS (SELECT 1 FROM ((SELECT 1 FROM CE_CP_WS_BA_V WBA WHERE WBA.worksheet_header_id = QRSLT.worksheet_header_id AND WBA.xtr_user_flag = 'N')UNION ALL (SELECT 1 FROM CE_CP_WORKSHEET_LINES WL WHERE WL.CASHPOOL_ID IS NOT NULL)))) ORDER BY WORKSHEET_NAME

at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:891)

at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:865)

at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:988)

at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:211)

at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:153)

at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:750)

at oracle.apps.ce.cashpositions.webui.WorksheetSearchCO.processFormRequest(WorksheetSearchCO.java:130)

at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:810)

at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)

at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(OAPageLayoutHelper.java:1159)

at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processFormRequest(OAPageLayoutBean.java:1579)

at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1022)

at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:988)

at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:843)

at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)

at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processFormRequest(OAFormBean.java:395)

at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1022)

at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:988)

at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:843)

at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)

at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363)

at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2700)

at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1707)

at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:511)

at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:432)

at _oa__html._OA._jspService(_OA.java:84)

at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)

at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)

at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)

at oracle.jsp.JspServlet.internalService(JspServlet.java:186)

at oracle.jsp.JspServlet.service(JspServlet.java:156)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)

at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)

at org.apache.jserv.JServConnection.run(JServConnection.java:294)

at java.lang.Thread.run(Thread.java:662)

## Detail 0 ##

java.sql.SQLException: ORA-00904: "QRSLT"."WORKSHEET_HEADER_ID": invalid identifier

Action plan:

1.  Please download and review the readme for Patch 4715697 .

2. Please apply Patch 4715697 in a TEST environment.
3. Please confirm the following file versions:
ce java/cashpositions/server WorksheetSummaryVOImpl.java 115.7
4. Restart apache server and clear the apache cash

5. Please retest the issue.

Good luck ...

How to Enable Automatic Compilation of JSP pages in R12 Environment

How to Enable Automatic Compilation of JSP pages in R12 Environment [ID 458338.1]
		Modified 10-NOV-2011     Type HOWTO     Status PUBLISHED

In this Document
  Goal
  Solution
  References

---

Applies to:

Oracle Applications Technology Stack - Version: 12.0.6 to 12.1.3 - Release: 12.0 to 12.1
Oracle Applications Technology Stack - Version: 12.0.6 to 12.1.3   [Release: 12.0 to 12.1]
Information in this document applies to any platform.

Goal

***Checked for relevance on 12-MAY-2010***

In Release 11i, the deployment of a (custom) JSP was done as follows:

1. Copy the file (for example: custom.jsp) to the web tier in the $OA_HTML directory. 
2. Request it in the browser like URL> http://:/OA_HTML/custom.jsp.

The result on the web tier is that:

• The custom.jsp was compiled 
• Output files of the compilation _custom.class (and _custom.java) are saved under the _pages directory 
• The custom.jsp page is shown in the browser 

Performing the same steps in Release 12 environment just shows a blank screen and no compilation of the custom.jsp is done. The steps in this Note explain how the same behavior as in Release 11i can be achieved in Release 12.

Solution

In Release 12 the (automatic) compilation of a JSP is disabled and, at runtime, only the pre-compiled JSP's are picked up. Since this requires fewer checks to be done, the performance is improved and therefore this is the recommended and default setting for a Production environment where JSP's will only be replaced occasionally.

In cases where development activities are done and JSP pages are changing often this default setting makes things more complicated. Each time a JSP is deployed a manual compilation using the ojspCompile.pl is needed and the OC4J running the oacore needs a restart to pick up the changes. This will also affect other people working on the same environment. 

Follow the steps below to:

• Automatically have the JSP recompiled when the JSP is changed (at least the time stamp)
• See the new JSP without the need to restart the OC4J running oacore

The objective is to change the setting for main_mode from justrun to recompile in 

$INST_TOP /ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml 

This can be achieved by performing the following:

• Login into E-Business suite and select System Administrator responsibility
• Select function AutoConfig (under Oracle Applications Manager) (*)
• For each web tier server perform the following:
  • Click on pencil icon under Edit Parameters
  • Select tab System
  • Expand section jtff_server
  • Change value for the entry s_jsp_main_mode from justrun to recompile
  • Confirm the change by clicking Save button
• Run AutoConfig to propagate the changes to the configuration files
• Verify that the $INST_TOP/ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml  has the following:

      
         main_mode
         recompile
      

• Restart the web tier services
• Request a JSP in the browser which is compiled. See that a new _.class is created in _pages
• Make a change in the JSP file
• Request it again in the browser. See that _.class is 'refreshed' in _pages and the change is seen in the browser

---

(*) If the Autoconfig function is not available it can also be accessed by selecting other function for Oracle Application Manager and then select Site Map > System Confiration - Autoconfig. Also the Autoconfig function can be added to the menu as follows:

• Log in as System Administrator and select System Administrator
• Application>Menu
• Put the system into query\
• In Menu enter OAM_ADMIN_MENU
• Run the query: This should return Oracle Applications Manager Administrator menu
• Add new line
• Enter Prompt = Autoconfig + Function select OAM_AD_CONFIG_FILES_TABLE and ensure Grant is ticked
• Save
• After recompilation (and possibly bounce) the new function will be available to be used in the future

References

Oracle E-Business Suite Support on x86-64

Oracle E-Business Suite Support on x86-64

Questions addressed in this document

What is x86-64?
What operating systems are supported on x86-64?
Is Oracle E-Business Suite supported on x86-64?
What are the benefits of running Oracle E-Business Suite on x86-64?
How do I install Oracle E-Business Suite database on x86-64?
Why are there two installation packages for E-Business Suite Release 12 for Linux?
How do I migrate an existing Oracle E-Business Suite database to x86-64?
Is RAC supported with the database tier on x86-64?
Is cloning supported on an E-Business Suite system with an x86-64 database?
How do I clone the database on platforms that do not support Rapid Clone?
Are there separate patches for Oracle E-Business Suite on x86-64?
Questions and Answers

What is x86-64?
Answer:
x86-64 (also called x64) refers to both AMD Opteron (AMD64) and Intel Extended Memory (EM64T) processors. AMD64 and EM64T are x86-compatible chip sets with 64-bit extensions. x86-64 based servers can run either a 32-bit or a 64-bit operating system.

Refer to the Intel web site for more information on EM64T and to to the AMD web site for more information on AMD64.

[top]



What operating systems are supported on x86-64?
Answer:
Apple Macintosh, Linux, Microsoft Windows, Solaris and other operating systems are supported on x86-64. Each combination of an operating system and chip architecture is refered to as a platform. Not all these platforms are certified with Oracle E-Business Suite.

[top]



Is Oracle E-Business Suite supported on x86-64?
Answer:
The answer has three parts:

1. 32-bit Operating Systems for Oracle E-Business Suite Release 12 and 11i:
When Linux or Windows 32-bit operating systems are installed, x86-64 servers can be used for Oracle E-Business Suite application and database tiers; "Linux x86" and "Windows 32-bit" platform certification criteria apply. The following 32-bit operating systems are supported:

Microsoft Windows Server
Oracle Linux
Red Hat Enterprise Linux
SUSE Linux Enterprise Server
2. Some 64-bit platforms for Oracle E-Business Suite Release 12: the following 64-bit operating systems are supported for both application and database tiers on x86-64 servers:

Oracle Linux
Red Hat Enterprise Linux
SUSE Linux Enterprise Server
Microsoft Windows Server x64 (2008 R2 only)
3. Remaining 64-bit platforms for Release 12 and all 64-bit platforms for Release 11i : when Oracle E-Business Suite Release 11i or 12 application tier is installed on a certified platform, additional platforms may be used for a 64-bit database tier on x86-64 servers. This is an example of a mixed platform architecture. In Release 11i this was known as Split Configuration.

The following 64-bit operating systems are supported on x86-64 servers for E-Business Suite database tier only:

Microsoft Windows Server x64
Oracle Linux
Red Hat Enterprise Linux
SUSE Linux Enterprise Server
Oracle Solaris on x86-64
For up-to-date information on mixed platform architecture, refer to "Database Tier Only " in Certify -> "View Certifications by Platform", choose "Database Tier Only " in the platform list.


See Certifications on My Oracle Support for more information on certified platforms and specific software versions.

[top]



What are the benefits of running Oracle E-Business Suite on x86-64?
Answer:
When a 32-bit OS is installed on x86-64, the maximum 64GB of main memory (using Physical Address Extension or PAE) provides scalability and stability advantages over x86 (32-bit) processor systems, which are limited to 4GB. The use of OS features such as PAE or the hugemem kernel to increase memory available to a 32-bit operating system should have no effect on the Oracle E-Business Suite and is supported.

Each process is still limited to a 32-bit memory space (4GB for Linux, and up to 3GB for Windows) when running a 32-bit OS on x86-64 processor systems.

When a 64-bit OS is installed on x86-64, the limits on physical and process memory significantly exceed today's manufacturing capabilities.

When larger amounts (higher than 16GB) of memory are installed, running a 64-bit Linux OS is more stable than running a 32-bit Linux OS.

[top]



How do I install Oracle E-Business Suite database on x86-64?
Answer:
For new installations of Oracle E-Business Suite Release 12 on supported 64-bit Linux operating systems, you can use Rapid Install as directed in "Oracle Applications Installation Guide: Using Rapid Install" to install the database and application tiers.

For Database Tier Only platforms in a mixed platform architecture, you can use Rapid Install as directed in "Oracle Applications Installation Guide: Using Rapid Install" to install the database and application tiers on supported platforms, and then migrate the database tier to a certified 64-bit operating system (see references).

[top]



Why are there two installation packages for E-Business Suite Release 12 for Linux?
Answer:
The Oracle E-Business Suite Applications Release 12.0 Media Pack for Linux x86 is used to install on x86 or x86-64 servers with a supported 32-bit Linux operating system. The Oracle E-Business Suite Applications Release 12.0 Media Pack for Linux x86-64-bit is used to install on x86-64 servers with a supported 64-bit operating system. For the Installation and Upgrade Notes specific to each installation package, refer to the documentation in the references

[top]



How do I migrate an existing Oracle E-Business Suite database to x86-64?
Answer:
To migrate the database to a certified operating system on x86-64, refer to the documentation in the references.

[top]



Is RAC supported with the database tier on x86-64?
Answer:

Yes, it is. RAC certifications are generic and apply to all platforms unless otherwise specified.

Using Oracle 10g Release 2 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12 (My Oracle Support Note 388577.1) contains Oracle E-Business Suite Release 12 with RAC implementation details.

A list of the supported Release 11i versions with RAC is available in Oracle E-Business Suite 11i and Database FAQ (My Oracle Support Note 285267.1). In addition, the following documents contain Oracle E-Business Suite 11i with RAC implementation details:

Oracle E-Business Suite Release 11i with 9i RAC; Installation & Configuration Using AutoConfig (My Oracle Support Note 279956.1)
Configuring Oracle Applications Release 11i with 10g Real Application Clusters and Automatic Storage Management (My Oracle Support Note 312731.1)
If you use parallel concurrent processing (PCP) with Oracle E-Business Suite 11i, we recommend applying ATG Family Pack 11i.ATG_PF.H RUP3 or higher (patch 4334965).

The use of RAC on Solaris 10 Containers (a feature that allows partitioning of an OS into separate virtual hosts) is restricted to global (non-local) containers - please see My Oracle Support Note 317257.1 for the best practices document for deploying Oracle databases in Solaris 10 Containers.

[top]



Is cloning supported on an E-Business Suite system with an x86-64 database?
Answer:

Yes, it is. For Oracle E-Business Suite systems with database tiers on 64-bit operating systems, use Rapid Clone.

More information about Rapid Clone is available in the references.



How do I clone the database on platforms that do not support Rapid Clone?
Answer:

Cloning the E-Business Suite database tier using the Oracle database utilities is a process of cloning the database Oracle home and creating a duplicate database.

To clone the database Oracle home, use the Oracle Universal Installer (OUI). This process allows you to configure inventory information for the new target Oracle home. See the Oracle Universal Installer and OPatch User's Guide, 10g Release 2 for additional information.

To create a duplicate database, use Recovery Manager (RMAN). See the Oracle Database Backup and Recovery Advanced User's Guide, 10g Release 2 for additional information.



Are there separate patches for Oracle E-Business Suite on x86-64 with a 64-bit OS?
Answer:

There are really two answers to this quesiton - for the Database Tier, the answer is yes. Since we bundle the 64-bit Database for use on 64-bit OS'es, the database tier's patches may indeed differ for the 64-bit distribution vs the 32-bit.

On the application tier, the answer is no. Users should apply 32-bit patches for components on the application tier (E-Business Suite applications, Fusion Middleware components, 3rd party patches) since there is not a separate 64-bit distribution generally for E-Business Suite.

Cloning Oracle Application 11i /R12 with Rapid Clone - Database (9i/10g/11g) Using Hot Backup on Open Database

You need to follow the "pre-requisite" and "preparation" steps from section 1 and 2 of the cloning note applicable to your EBS Version:

Release 11i  : Note 230672.1 - 'Cloning Oracle Applications Release 11i with Rapid Clone'
Release 12  : Note 406982.1 - 'Cloning Oracle Applications Release 12 with Rapid Clone'


This Note helps you to implement the steps mentioned in "Appendix B: Recreating database control files manually in Rapid Clone" as per above Clone Note.

Step 1: Ensure adpreclone.pl has been run

Step 2: Obtain a trace file script to recreate the controlfile. On the source database issue the following
            command:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;


The trace file script will be put into the user_dump_dest directory. The name of the trace file script will be something like PROD_ora_12345.trc.
Compare the date and time of the new trace file script to the time in which you entered the ALTER DATABASE BACKUP CONTROLFILE TO TRACE command.
This will ensure you will be using the most recent trace file script, the one you created in this step


Step 3: In the create controlfile script just created in step 2 change:
CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS

to:

CREATE CONTROLFILE DATABASE "PROD" RESETLOGS ARCHIVELOG
If you want to change the Database Name , You need to use the clause SET DATABASE in the create control file script as follows:
CREATE CONTROLFILE SET DATABASE "newdbname" RESETLOGS NOARCHIVELOG
You must specify RESETLOGS.

The ARCHIVELOG mode may be changed to NOARCHIVELOG if you wish to run the copied database in noarchive log mode. Change all directories in the create controlfile clause to point to the correct directories for the new target database, if necessary.

Leave "only" the CREATE CONTROLFILE clause. The other statements, like the recover command, will be done manually. Be sure you also remove the STARTUP NOMOUNT command.
Note:  Please ensure that there is no new datafile/tablespace added to Database after you generate controlfile script as above

Step 4: On the source database make an online copy of all datafiles using:
SQL> ALTER TABLESPACE BEGIN BACKUP;

Copy all datafiles within tablespace to the new directory. 
On Unix systems, this can be done with the cp command.

Then do:

SQL> ALTER TABLESPACE END BACKUP;

Do NOT copy the controlfiles and redo log files as they will be recreated. You must copy the datafiles only after the ALTER .. BEGIN BACKUP command has been executed, otherwise the datafiles may be corrupted.

The names of the datafiles and tablespaces to which the datafiles belong can be obtained using the following command:

SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES;

Note: Instead of performing step 4 and step 5 to create a new online backup, you may instead use a previously taken online backup of your database.If you choose to use a previous online backup be sure to copy the required archived redo logs taken with the the previous online backup.

Step 5: After all datafiles have been copied and the tablespaces taken out of backup mode issue the following command:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

You will need all of the archivelog files from the start of datafile copy commands including the one just created with the command ALTER SYSTEM ARCHIVE LOG CURRENT.

Step 6: Copy the database (DBF) files,controlfile script and archive log files from the source to the target
           system

Step 7: So, As mentioned in 230672.1 (Appendix B), Replace section 2.2a (Configure the target system database server) with the following steps:

Execute the following commands to configure the target system. You will be prompted for the target system specific values (SID, Paths, Ports, etc)

· Log on to the target system as the ORACLE user
· Configure the
cd /appsutil/clone/bin
perl adcfgclone.pl dbTechStack

Step 7: On the target system issue STARTUP NOMOUNT command. For example:
     
SQL> startup nomount pfile=initTEST.ora


Step 8: Run the prepared script created in step 3 to create the new controlfile. For example:

SQL> @PROD_ora_12345.trc

Step 9: Issue the command:
         
SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE


You will be prompted to apply all of the archived redo logs from the source database including
the last archive redo log file created with the ALTER DATABASE ARCHIVE LOG CURRENT
command from step 5.

After applying all of these archive log files issue the CANCEL command.

Step 10: Open the database with resetlogs:
         
SQL>ALTER DATABASE OPEN RESETLOGS

 At this point the target database will have been successfully cloned and opened.

Step 11:  Create Temporary Tablespace if not created in Source, else you shall add Temporary tablespace

You can check if tablespace TEMP has tempfiles or datafiles using the following SQL:

SQL> SELECT FILE_NAME,TABLESPACE_NAME,STATUS,AUTOEXTENSIBLE from DBA_TEMP_FILES where TABLESPACE_NAME like 'TEMP';

SQL> SELECT FILE_NAME,TABLESPACE_NAME, STATUS,AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE 'TEMP';

If Temporary Tablespace is not created from above query,
SQL> create temporary tablespace temp add tempfile 'xxxx.dbf' size xx

Or

SQL> alter tablespace TEMP add tempfile  'xxxx.dbf' size xx


Step 12:  Run the library update script against the database

· cd /appsutil/install/
· sqlplus "/ as sysdba" @adupdlib.sql
      where is "sl" for HP-UX, "so" for any other UNIX platform and 
      not required for Windows.

Step 13: Configure the target database (the database must be open)

· cd /appsutil/clone/bin
· perl adcfgclone.pl dbconfig
     where target context file is:
     /appsutil/.xml 


Finally, refer back to cloning notes and the following sections:

· Copy the Application Tier File System
· Configure the Target System Application Tier Server Nodes
· Finishing Tasks
Release 11i : Note 230672.1 - 'Cloning Oracle Applications Release 11i with Rapid Clone'
Release 12 : Note 406982.1 - 'Cloning Oracle Applications Release 12 with Rapid Clone'

Interoperability Notes Oracle EBS 12 with Oracle Database 10gR2 (10.2.0) 812362.1

This note is the master document describing the use of Oracle E-Business Suite Release 12 (12.0 and 12.1) with Oracle Database 10g Release 2 (10.2.0.x),
where x denotes the patch set level (currently 5). It may refer to related interoperability notes, which you should review if they apply to your specific release of the Oracle Database.

The most current version of this document is document 812362.1 on OracleMetaLink. There is a change log at the end of this document.

Attention: Interoperability notes of previous patch sets are available through the following links:
10.2.0.3 Interoperability Notes
10.2.0.4 Interoperability Notes

Attention: Check My Oracle Support to make sure that your platform and environment configuration are certified to work with Oracle Database 10g before performing any instruction in this document.

Attention: If you are going to use 10g Real Application Clusters (RAC) or Automatic Storage Management (ASM), see Using Oracle 10g Release 2 Real Application Clusters and Automatic Storage
Management with Oracle E-Business Suite Release 12 on My Oracle Support.

Attention: These notes apply to UNIX, Linux, and Windows platforms. However, you may need to modify some instructions slightly depending on your platform. For example, these notes typically
use UNIX/Linux syntax when specifying a directory, so Windows users will need to substitute the appropriate syntax when accessing that directory.

Attention: You can obtain the 10.2.0.5 patch set from patch 8202632 on My Oracle Support.

Upgrading an Existing Oracle Database 10g Release 2 (10.2.0) patch set

Follow the instructions in this section if you have an Oracle E-Business Suite Release 12.x system using Oracle 10g Release 2 (10.2.0) and you wish to apply the latest certified Oracle 10g Release 2 (10.2.0) patch set to it.

    Apply 10.2.0.5 interoperability patch 13725068 (conditional)

    If you are on E-Business Suite Release 12.0.4 or a later version, apply 10.2.0.5 interoperability patch for Release 12.0 (13725068)

    Perform 10.2.0.5 Patch Set preinstallation tasks (conditional)

    On the database server node, as the owner of the Oracle 10g file system and database instance, unzip and extract the patch set file appropriate for your platform.

Read the patch set notes (usually README.html) and make sure that you thoroughly understand the patch set installation process before you begin.

Check My Oracle Support or contact Oracle Support Services to determine any known issues with the patch set and its interoperability with Oracle Applications.

    Perform the tasks in the "Preinstallation Tasks" section of the patch set notes (if they apply to your system).

    Shut down Applications server processes

    Shut down all Applications and database server processes. The Applications will be unavailable to users until all tasks in this section are completed.

    Perform 10.2.0.5 Patch Set installation tasks

    On the database server node, as the owner of the Oracle RDBMS file system and database instance, perform the tasks in the "Installing the Oracle Database 10g Patch Set Interactively"

 section of the patch set notes. Make sure that you use the runInstaller (UNIX/Linux) or the setup.exe executable (Windows) provided in the patch set to run OUI.

    Do not perform the "Postinstallation Tasks" section yet.

    Perform 10.2.0.5 Patch Set postinstallation tasks

    Review the "Postinstallation Tasks" section of the patch set notes. Perform the steps that are relevant for your database.

    Note: Skip the sections "Upgrading Earlier Oracle Database to Oracle Database 10g Release 2 (10.2.0.5)" and "Enabling Oracle Database Vault" of the patch set notes.

    Apply additional 10.2.0.5 RDBMS patches

    Apply the following patches:

    For all UNIX/Linux platforms, apply RDBMS patches:
        4247037
        9726739
        9870614

    For all Windows platforms:
        Apply patch 1 or the latest Windows patch: 10058290 (32-bit), 10099855 (64-bit)
        4247037

    Attention: While not mandatory for the interoperability of Oracle E-Business Suite with the Oracle Database, customers may choose to apply Database Patch Set Updates (PSU) on their
Oracle E-Business Suite Database. If so, please see Document 1147107.1 ('Database Patch Set Update Overlay Patches Required for Use with PSUs and Oracle E-Business Suite') on
My Oracle Support for more information.

    Gather statistics for SYS schema

    Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node. Note that adstats.sql has to be run in restricted mode.
 Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode, run adstats.sql, and restart the database in normal mode:

        $ sqlplus "/ as sysdba"
        SQL> shutdown normal;
        SQL> startup restrict;
        SQL> @adstats.sql
        SQL> shutdown normal;
        SQL> startup;
        SQL> exit;

    Note: Make sure that you have at least 1.5 GB of free default temporary tablespace.

    Restart Applications server processes

    Restart all the Applications server processes that you shut down before installing the patch set. Users may return to the system.

APPS DBA Scripts

Oracle Applications System Administration Scripts [ID 214088.1]
		Use These First! If the associated script does not allow you to solve your issue, then please upload the output file from the script when you first log a Service Request (SR)

Applications Performance

Locks for given Session ID

• Note: 200590.1 bde_session_locks.sql(<-- Click here for details & download)
  This script creates a report with locks for one session that is not responding (hanging), because of a Lock.




---

Validates and Rebuilds Fragmentated Indexes

• Note: 182699.1 bde_rebuild.sql(<-- Click here for details & download)
  This script validates and proceeds to generate a dynamic script to rebuild them. 




---

Analyze Indexes to determine Fragmentation

• Note: 182049.1 bde_analyze_indexes.sql(<-- Click here for details & download)
  This script will analyze indexes to determine fragmentation




---

Current, Required and Recommended Apps 11i init.ora params

• Note: 174605.1 bde_chk_cbo.sql(<-- Click here for details & download)
  This script displays the current value, the required value (if any), the recommended value (if any), and the default value (if any). 




---

Simple Explain Plan for given SQL Statement

• Note: 174603.1  bde_x.sql(<-- Click here for details & download)
  Script generates a Simple Explain Plan for one SQL statement, required to diagnose apps performance issues (transaction tuning). 




---

Expensive SQL and Resources Utilization for a Given Session ID

• Note: 169630.1 bde_session.sql(<-- Click here for details & download)
  This script creates a report with relevant information for one session that is either performing poorly or not responding (hanging). 




---

Verifies Statistics for all Installed Apps Modules 11.5

• Note: 163208.1 bde_last_analyzed.sql(<-- Click here for details & download)
  Script verifies statistics for all installed Apps modules 11.5




---

Changes CBO Stats Selectivity for a Given Index and Column

• Note: 157276.1 bde_chg_stats.sql(<-- Click here for details & download)
  Script that changes CBO stats selectivity for a given index and column 




---

Clone Views Across Instances for SQL Tuning Exercises

• Note: 156972.1 coe_view.sql(<-- Click here for details & download)
  This script facilitates cloning views across instances for SQL tuning exercises




---

Trace Apps Online Transactions with Event 10046

• Note: 156969.1 coe_trace.sql(<-- Click here for details & download)
  This script will Generates SQL Trace with bind variables and waits information for an Oracle Applications Form (equivalent of Event 10046 level 12) 




---

Automate CBO Stats Gathering

• Note: 156968.1 coe_stats.sql(<-- Click here for details & download)
  This script automates CBO stats gathering using FND_STATS and table sizes




---

Top 10 Expensive SQL from SQL Area

• Note: 156967.1  coe_sqlarea.sql(<-- Click here for details & download)
  This script scans sql area and sql text v$ dynamic performance views and displays Top n SQL Statements in terms of resources utilization. 




---

SQL Tracing online transactions using Event 10046

• Note: 156966.1 coe_event_10046.sql(<-- Click here for details & download)
  Process and Session info for one Concurrent Request




---

Session and Serial# for Locked Rows

• Note: 156965.1 coe_locks.sql(<-- Click here for details & download)
  This script displays all sessions holding a lock on a table or row. 




---

Import CBO Stats from COE_STATTAB_XYZ

• Note: 156964.1 coe_import_stattab.sql(<-- Click here for details & download)
  This script uploads data dictionary statistics from table COE_STATTAB_XYZ...




---

Enhanced Explain Plan for given SQL Statement

• Note: 215187.1 SQLTXPLAIN.sql(<-- Click here for details & download)
  Given one SQL Statement as input, generate a comprehensive report.. (sql tuning).




---

Audit Trail

Active Users executing FORMs or Conc Programs

• Note: 233871.1 FNDFindActiveUsers115.sql(<-- Click here for details & download)
  This script generates a list of active apps users with at least one open form, or executing at least one concurrent program. 




---

Trace

Enhanced Explain Plan for given SQL Statement

• Note: 179848.1 bde_system_event_10046.sql(<-- Click here for details & download)
  This script is used to turn SQL trace ON with LEVEL 12 for any concurrent program that starts its execution AFTER the EVENT 10046 is turned ON at the SYSTEM level.
  
• Note: 224270.1 TRCANLZR.sql(<-- Click here for details & download)
  Trace Analyzer - Interpreting Raw SQL Traces generated by EVENT 10046

FAQ (Concurrent Manager Unix specific) [ID 105101.1]

FAQ (Concurrent Manager Unix specific) [ID 105101.1]
  Modified 17-AUG-2011     Type FAQ     Status PUBLISHED
Checked for relevance 12-May-2010

Application Install 11.0.0 to 11.5.10

Concurrent Manager FAQs

Q: Which Concurrent Managers (CCMs) are supported by the Sysadmin team?
Q: Where do the concurrent request output and log files reside?
Q: Where are temporary files located?
Q: How to change PMON  method to LOCK.
Q: How to start/shutdown the CCM on Unix as APPS
Q: How to terminate a concurrent request that cannot be cancelled.
Q: A Manager is not activating.
Q: Internal Concurrent Manager functionality.
Q: How to purge requests that are in Pending status.
1.  Q: Which Concurrent Managers (CCMs) are supported by the Sysadmin team?

Internal Concurrent Manager (ICM)

Standard Manager

Conflict Resolution Manager  (CRM)



The setup of product-specific managers related issues should

be directed to the associated product group, i.e. the Inventory manager

[top]


  Q: Where do the concurrent request output and log files reside?

 A:  $APPLCSF/out or $Product_Top/out for output files

    $APPLCSF/log or $Product_Top/log for log files

See Where do the concurrent request output and log files reside? Note:105133.1



 [top]

Q: Where are temporary files located?

 A:  Temporary files reside in:

$APPLPTMP

$APPLTMP

$REPORTS25_TMP (Release 11.0)

$REPORTS60_TMP (Release 11.5)

[top]
  Q: How to change PMON  method to LOCK.



To check the process monitor (PMON) method status:

          Connect as database user "APPS".

            SELECT profile_option_value

            FROM fnd_profile_option_values

            WHERE level_id = 10001

            AND level_value = 0

            AND application_id = 0

            AND profile_option_id =

                    (SELECT profile_option_id

                    FROM fnd_profile_options

                    WHERE profile_option_name = 'CONC_PMON_METHOD');



           This should return one row with a value of 'LOCK'



If the value is 'RDBMS' or 'OS' run the following script:



$FND_TOP/sql/AFIMPMON.SQL - this will set the PMON method to LOCK instead of RDBMS.



Bounce the database.  If running on NT, restart the NT Server completely.



Within 11.5 Applications, the only PMON method is LOCK.

[top]
   Q: How to start and shutdown the CCM on Unix as APPS

A:  To Start:

     

         STARTMGR sysmgr=APPS/passwd



      To Shutdown:



         concsub apps/ sysadmin 'System Administrator' SYSADMIN CONCURRENT FND ABORT

11.5 => 11.5.4

adcmctl.sh apps/passwd start/stop prod

11.5.5 =>

adcmctl.sh start/stop apps/passwd prod

[top]


 Q: How to terminate a concurrent request that cannot be cancelled.

A:    Identify the request number to terminate.

       Shut down the concurrent managers and issue the following sql command as applsys:

     

            update fnd_concurrent_requests

            set status_code = 'E', phase_code = 'C'

            where Request_id = ; (reqnum = request number)

[top]
  Q: A Manager is not activating.        

 A:    Check the count in sys.dual, system.dual, & apps.fnd_dual. There should only be one (1) row for each.

        If the count is greater, change to one only. This can be done from sqlplus as  follows:



    As user apps:

                   select *

                   from SYS.DUAL;

         Any extra rows should be deleted.

                   delete rownum

                   from SYS.DUAL;

               (rownum = the row number to delete)



        Any extra rows for apps.fnd_dual must be removed by performing the following SQL command:



                 delete from fnd_dual

                 where rownum < (select max(rownum) from fnd_dual);



Bounce the Concurrent Managers.


[top]

 .  Q: Internal Concurrent Manager.        

A:   If the ICM should go down, requests will continue to run normally, except for 'queue control' requests.



      If the ICM should go down, you can restart it with 'startmgr'. There is no need to shut down the other managers first.



[top]

 Q: How to purge requests that are in Pending status.        

A:  The Purge Concurrent Requests program will only purge requests that are in Completed status. Set the status of the Pending requests  to Completed

     before purging them. The Internal Manager Must be DOWN!



     Using sqlplus as APPLSYS perform the following:



              UPDATE fnd_concurrent_requests

              SET phase_code = 'C'

              WHERE phase_code = 'P'

cmdailylong.sql see the top jobs based on total runtime > xx minutes for today first parameter is the nbr of minutes runtime to use

/* cmdailylong.sql
see the top jobs based on total runtime > xx minutes for today
first parameter is the nbr of minutes runtime to use
SDR-Oracle 12/5/02 Created from various other scripts
*/
set pages 66
set lines 132
set verify off
col program form A35 trunc head "Program Full Name"
col intprog format a35 trunc head "Internal Name"
col RunTime form 99999
col actstart format a6 head Started
col actcompl format a6 head Finishd
col qname head "Queue" format a15 trunc

select q.concurrent_queue_name qname,
       c.concurrent_program_name  ||
' (' || to_char(c.concurrent_program_id) || ')' intprog,
       ctl.user_concurrent_program_name "program",
request_id,
phase_code,
status_code,
to_char(actual_start_date,'hh24:mi') actstart,
to_char(actual_completion_date,'hh24:mi') actcompl,
      ((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) "RunTime"
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 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'
  and trunc(actual_completion_date) = trunc(sysdate)
  and actual_start_date is not null
  and actual_completion_date is not null
  and ((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) > &1
order by 9 desc;

find the number of jobs requested for a queue by each hour in one day useful to find out if the nbr of workers is sufficient for the requests

/* cmqhourly.sql
        find the number of jobs requested for a queue by each hour in one day
useful to find out if the nbr of workers is sufficient for the requests
SDR-OCS 10/16/02 Creation based on archlogs.sql and cmqjobs.sql
*/
set verify off
set lines 160
col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999
col ccpid head ProgID-Name format a15 trunc
break on report
compute sum of 11PM on report
compute sum of 10PM on report
compute sum of 9PM on report
compute sum of 8PM on report
compute sum of 7PM on report
compute sum of 6PM on report
compute sum of 5PM on report
compute sum of 4PM on report
compute sum of 3PM on report
compute sum of 2PM on report
compute sum of 1PM on report
compute sum of Noon on report
compute sum of 11AM on report
compute sum of 10AM on report
compute sum of 9AM on report
compute sum of 8AM on report
compute sum of 7AM on report
compute sum of 6AM on report
compute sum of 5AM on report
compute sum of 4AM on report
compute sum of 3AM on report
compute sum of 2AM on report
compute sum of 1AM on report
compute sum of MidN on report
accept trgtqname char default NONE prompt 'Limit to which ccmgr queue name : '
accept trgtprog char default ALL prompt 'Limit to which ccmgr job name : '
accept trgtdte char default TODAY prompt 'Limit to what date (dd-mon-yyyy) : '
accept trgtproc char default ALL prompt 'Limit to which ccmgr os pid : '
select g.concurrent_program_id || '-' || g.concurrent_program_name ccpid,
sum(decode(to_char(requested_start_date,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(requested_start_date,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(requested_start_date,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(requested_start_date,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(requested_start_date,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(requested_start_date,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(requested_start_date,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(requested_start_date,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(requested_start_date,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(requested_start_date,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(requested_start_date,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(requested_start_date,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(requested_start_date,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(requested_start_date,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(requested_start_date,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(requested_start_date,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(requested_start_date,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(requested_start_date,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(requested_start_date,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(requested_start_date,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(requested_start_date,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(requested_start_date,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(requested_start_date,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(requested_start_date,'hh24'),'23',1,0)) "11PM"
  from applsys.fnd_concurrent_requests r,
       applsys.fnd_concurrent_processes p,
       applsys.fnd_concurrent_programs g,
       applsys.fnd_concurrent_queues q
 where r.controlling_manager (+) = p.concurrent_process_id
   and p.queue_application_id = q.application_id
   and p.concurrent_queue_id = q.concurrent_queue_id
   and (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   and (upper(g.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
   and trunc(r.requested_start_date) = decode(upper('&trgtdte'),'TODAY',trunc(sysdate),'&trgtdte')
   and r.concurrent_program_id = g.concurrent_program_id
   and r.program_application_id = g.application_id
   and (p.os_process_id = '&trgtproc' or upper('&trgtproc') = 'ALL')
group by g.concurrent_program_id || '-' || g.concurrent_program_name
order by 1
/          

prompt Getting queue statistics.....
set verify off
set lines 132
prompt Note - times are expressed in decimal minutes
col qname head "Activated Concurrent Queue" format a26
col actual head "Actual" format 999999
col target head "Target" format 999999
col minr head MinRTime format 9999.99
col maxr head MaxRTime format 9999.99
col avgr head AvgRun format 9999.99
col avgq head AvgQTime format 9999.99
col totreq head "# Jobs" format 99999
col qname head "Queue Name" format a25 trunc
clear breaks
clear computes
select  q.concurrent_queue_name || '(' || q.target_node || ')' qname,
count(r.request_id) totreq,
       q.running_processes actual,
       q.max_processes target,
       avg((r.actual_start_date - r.requested_start_date)*1440)   avgq,
       min((r.actual_completion_date - r.actual_start_date)*1440) minr,
       max((r.actual_completion_date - r.actual_start_date)*1440) maxr,
       avg((r.actual_completion_date - r.actual_start_date)*1440) avgr
  from applsys.fnd_concurrent_requests r,
       applsys.fnd_concurrent_processes p,
       applsys.fnd_concurrent_programs g,
       applsys.fnd_concurrent_queues q
 where r.controlling_manager = p.concurrent_process_id
   and p.queue_application_id = q.application_id
   and p.concurrent_queue_id = q.concurrent_queue_id
   and trunc(r.requested_start_date) = decode(upper('&trgtdte'),'TODAY',trunc(sysdate),'&trgtdte')
   and (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   and (upper(g.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
   and r.concurrent_program_id = g.concurrent_program_id
   and r.program_application_id = g.application_id
group by q.concurrent_queue_name || '(' || q.target_node || ')',
       q.running_processes, q.max_processes;

Certified Oracle Solaris and SPARC Virtualization and Partitioning Technologies for Oracle E-Business Suite

Certified Oracle Solaris and SPARC Virtualization and Partitioning Technologies for Oracle E-Business Suite

Oracle E-Business Suite has been supported on hardware vendor virtualization technologies (running on non x86/x86-64 architectures) following the 'supported but not explicitly certified' principle as outlined in the My Oracle Support Document 794016.1.

Oracle E-Business Suite is comprised of both an application tier (applications code and Fusion Middleware components) as well as a database tier (Oracle Database) - any limitations of the Oracle Database or Fusion Middleware with respect to the use of virtualization technologies necessarily also apply to Oracle E-Business Suite's tiers (see References section below for links).

All requirements listed in the Oracle E-Business Suite Installation and Upgrade Notes (see References section below) must be met in order to use E-Business Suite with these virtualization technologies.

Certifications for E-Business Suite Release 12

The following are the specific technologies certified explicitly for Oracle E-Business Suite Release 12:

Dynamic System Domains

Dynamic System Domains is a hardware partitioning technology (with electrical and physical isolation) built in to the Sun SPARC Enterprise M-Series server family. Dynamic Reconfiguration technology is used with Dynamic System Domains in order to reallocate resources between different domains (such as removing and adding CPUs, memory and I/O subsystems). See this white paper for information on the use of these technologies.

Oracle E-Business Suite Release 12 (12.0.4 or higher, 12.1.1 or higher) is certified with this hardware partitioning technology for Sun SPARC Enterprise M-Series servers running Oracle Solaris on SPARC versions 9 or 10.

Oracle VM Server for SPARC

Oracle VM Server for SPARC (previously known as Logical Domains or LDoms) is a hypervisor-based virtual partition technology available on Sun SPARC T-Series servers. It allows for a single physical server to be partitioned into domains each with its own access to resources while running a separate instance of the operating system. See this data sheet for more information.

Oracle VM Server for SPARC version 1.3 or higher is certified with Oracle E-Business Release 12 (12.0.4 or higher, 12.1.1 or higher) running Solaris 10 10/09 (Update 8) or higher. The Fusion Middleware versions bundled with EBS must be upgraded to 10gR2 (10.1.2.3 or higher) and 10gR3 (10.1.3.4 or higher).

Oracle Solaris Containers

Oracle Solaris Containers is an OS partitioning technology specific to the Solaris operating system - it allows for multiple instances of 'local' hosts running within a single global instance of an OS.

Solaris Containers version Solaris 10 10/09 (Update 8) or higher is certified with Oracle E-Business Suite Release 12 (12.0.4 or higher, 12.1.1 or higher). The Fusion Middleware versions bundled with EBS must be upgraded to 10gR2 (10.1.2.3 or higher) and 10gR3 (10.1.3.4 or higher).

On the application tier, both local and global Containers are certified with the E-Business Suite.

On the database tier, please refer to the specific requirements and limitations listed in the 'Supported Virtualization and Partitioning Technologies for Oracle Database and RAC Product Releases' document.

Solaris Branded Zones

The use of Branded Zones (specifically Solaris 10 Zones) for use with the SPARC-based Oracle Exalogic Elastic Cloud T3-1B system is certified for the E-Business Suite R12 (12.1.1 or higher) application tier. A Solaris 10 zone is an instance of Solaris 10 running as a container within a native Solaris 11 Express operating system (see the System Administration Guide in the References section below) which is the Solaris version installed on the Oracle Exalogic Elastic Cloud T3-1B.

Note that Oracle Solaris 11 Express (SPARC) is not a fully certified Oracle E-Business Suite platform - there are plans to certify E-Business Suite Release 12 (both the application and database tiers) on Solaris 11 when it becomes generally available in the future.

All requirements pertaining to the use of E-Business Release 12 on Solaris 10 applies to Solaris 10 Zones with the addition of the following requirements as outlined in the current E-Business Suite Installation and Upgrade Notes for Oracle Solaris on SPARC (64-bit):

Patch 127111-11 which supersedes 127111-02
Patch 137111-09 which supersedes 137111-04
The Oracle Database is not supported or certified to run on a Solaris 10 branded zone within Solaris 11 Express - the use of an Oracle E-Business Suite R12 application tier in a Solaris 10 zone on Oracle Exalogic T3-1B must be in conjunction with running the database tier on a native Solaris 10 or other E-Business Suite certified database tier platform.



Support for E-Business Suite Release 11i

Oracle E-Business Suite 11i's terminal release (11.5.10.2) has been in Extended Support since December 2010 (see the Oracle Lifetime Support web page for details).

For Dynamic Systems Domains, Oracle VM Server for SPARC and Solaris Containers, E-Business Suite 11i continues to be supported under the previous 'supported but not explicitly certified' philosophy as outlined in the My Oracle Support Document 794016.1.

References

Hardware Vendor Virtualization Technologies on non x86/x86-64 Architectures and Oracle E-Business Suite (My Oracle Support Note 794016.1)
Supported Virtualization and Partitioning Technologies for Oracle Fusion Middleware
Supported Virtualization and Partitioning Technologies for Oracle Database and RAC Product Releases
Oracle's Optimized Solution for Mission Critical Databases - A Technical White Paper
Oracle VM Server for SPARC
Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.1.1) for Oracle Solaris on SPARC (64-bit) (My Oracle Support Note 761568.1)
Oracle Applications Installation and Upgrade Notes Release 12 (12.0.4) for Solaris Operating System (SPARC) (My Oracle Support Note 402312.1)
System Administration Guide: Oracle Solaris Zones, Oracle Solaris 10 Containers, and Resource Management
Oracle Announces SPARC Solaris-Based Exalogic Elastic Cloud System
Change Record

Troubleshoot long running Concurrent Request in Apps 11i/R12

This post covers overview of How to troubleshoot long running concurrent request in  Oracle Apps 11i/R12

Step 1 : Check Concurrent Request ID of long running concurrent request from front end
Step 2 : Find SID, SERIAL# and SPID by running SQL (given below)
Step 3 : Enable event 10046 trace with level 12 using oradebug ( for 15-20 minute)
Step 4 : Disable trace (once you are happy with trace size)
Step 5 : Convert raw trace to TKPROF using various sort options like fchela, prsela, execpu
Step 6 : Check TKPROF out file to find root cause of slow concurrent request

.

Step 1 : Check Request ID from Find Concurrent request screen (In my case Request ID is 2355) 

Step 2 : Run below command to find SPID, provide concurrent request ID (2355 in my case) when prompted

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$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’;

REQUEST_ID        SID    SERIAL# OSUSER           PROCESS    SPID
—————-
   2355        514         28 applmgr                        17794   1633.
. 

Step 3.1 : Check and confirm SPID on Database Node
oraclevis11i@onlineappsdba>ps-ef | grep 1633
 ovis11i  1633     1  0 13:30:43 ?        0:03 oraclevis11i (LOCAL=NO)

Step 3.2 : Set OSPID (1633 in my case) for ORADEBUG
SQL> oradebug setospid 1633
—
Oracle pid: 68, Unix process pid: 1633, image: oraclevis11i@onlineappsdba
—

Step 3.3 : Enable trace for 10046 event with level 12
SQL> oradebug event 10046 trace name context forever, level 12

Step 3.4 : Locate Trace file as
SQL>oradebug tracefile_name

/oracle/apps/vis11idb/10.2.0/admin/vis11i_onlineappsdba/udump/vis11i_ora_1633.trc

Wait for 15-20 minutes

Step 4 : Disable trace
SQL> oradebug event 10046 trace name context off

Step 5: Create tkprof file like
tkprof  ‘/oracle/ apps/ vis11idb/ 10.2.0/ admin/ vis11i_onlineappsdba/ udump/ vis11i_ora_1633.trc’   ’/oracle/ apps/ vis11idb/ 10.2.0/ admin/ vis11i_onlineappsdba/ udump/ tkprof_1633.txt’ explain=apps/[apps_passwd]  fchela …

Step 6 : Check TKPROF file to find root cause of slow concurrent requet

Long running concurrent requests

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

WARNING: inbound connection timed out (ORA-3136)

WARNING: inbound connection timed out (ORA-3136)

Many times I faced this issue “WARNING: inbound connection timed out (ORA-3136)” in alert log and if you are using data guard, then you can see that dgmgrl status will show failed because standby is not able to sync up with primary.

The “WARNING: inbound connection timed out (ORA-3136)” in the alert log indicates that the client was not able to complete it’s authentication within the period of time specified by parameter SQLNET.INBOUND_CONNECT_TIMEOUT.

From 10.2 onwards the default value of this parameter is 60 seconds, hence if the client is not able authenticate within 60 secs , the warning would appear in the alert log and the client connection will be terminated.

Root cause of this issue could be

1. malicious client is flooding database servers with connect requests that consumes resources

2. The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.

3. The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified

As a workaround to avoid only this warning messages, you can set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT=0 in $ORACLE_HOME/network/admin/sqlnet.ora file

Once you set this parameter the warnings in alert log will go away. If you are using data guard, may be you want to enable the databases on which you were getting warnings.

Hope this helps !!