Thursday, July 8, 2021
Installing Oracle E-Business Suite Integrated SOA Gateway, Release 12.2 (Doc ID 1311068.1)
Copyright (c) 2021, Oracle. All rights reserved. Oracle Confidential.
Click to remove from Favorites Installing Oracle E-Business Suite Integrated SOA Gateway, Release 12.2 (Doc ID 1311068.1) To BottomTo Bottom
Oracle E-Business Suite Integrated SOA Gateway (ISG) enables supported interface types published in Oracle Integration Repository. These interfaces can be transformed into SOAP and REST web services as explained below. This document enlists the setup tasks that must be performed to configure ISG on a fresh installation of Oracle E-Business Suite, as well as on an instance upgraded from an earlier release.
Note: To provide high availability of Oracle E-Business Suite services, Oracle recommends configuring your Oracle E-Business Suite environment with multiple nodes. The configuration and setup tasks described in this document are also applicable to an Oracle E-Business Suite environment configured with multiple nodes in the application tier.
Note: The setup tasks described in this document guide you through ISG configuration for Oracle E-Business Suite On-Premises. For information on configuring ISG for Oracle E-Business Suite Release 12.2 on Oracle Cloud, see:
Document 2675815.1, Configuring Oracle E-Business Suite Integrated SOA Gateway for Oracle E-Business Suite Release 12.2 on Oracle Cloud Infrastructure
Document 2386478.1, Configuring Oracle E-Business Suite Integrated SOA Gateway for Oracle E-Business Suite Release 12.2 on Oracle Cloud Infrastructure Classic
This document will be updated with latest information as and when available. You are advised to periodically check this Document 1311068.1 on My Oracle Support for updates. The most current version of this document can be obtained in My Oracle Support Knowledge Document 1311068.1.
There is a change record at the end of this document.
Configuration Tasks In This Document
The following table lists the configuration tasks required for setting up Oracle E-Business Suite web services available through Oracle E-Business Suite Integrated SOA Gateway and the tasks required when your Oracle E-Business Suite instance or database is upgraded from an earlier release or Oracle E-Business Suite is cloned from an existing instance:
Note: If you are using both REST and SOAP services, configure your system by following the instructions described in both Part A and Part B.
Configuration Scenarios Implementation Tasks
Part A: Configuring Oracle E-Business Suite REST Services
Section 1: Configuring Oracle E-Business Suite Integrated SOA Gateway for REST Services
Section 2: Validating Oracle E-Business Suite Integrated SOA Gateway Setup for REST Services
Part B: Configuring Oracle E-Business Suite SOAP Services
Section 3: Performing Prerequisites Tasks for SOAP Services:
Section 3.1: Installing Oracle WebLogic Server and Oracle SOA Suite for SOAP Services
Section 3.2: Enabling TLS on Oracle SOA Suite
Section 4: Configuring Oracle E-Business Suite Integrated SOA Gateway for SOAP Services on a New Installation of Release 12.2.3 or Later
Section 4.1: Setup Tasks on Oracle E-Business Suite Release 12.2.3 or Later
Section 4.2: Configuration Steps on Oracle SOA Suite to Integrate with Oracle E-Business Suite
Section 5: Validating Oracle E-Business Suite Integrated SOA Gateway Setup for SOAP Services
Section 6: Deploying Generic XML Gateway Services for SOAP Services (Optional)
Part C: Configuring Oracle E-Business Suite Integrated SOA Gateway for Upgrading and Cloning Scenarios
Section 7: Configuring Oracle E-Business Suite Integrated SOA Gateway on Release 12.2.x Cloned From an Existing 12.2.x Environment
Section 8: Configuring Oracle E-Business Suite Integrated SOA Gateway for REST Services on Release 12.2.x Upgraded From Oracle E-Business Suite Release 12.1.x
Section 9: Configuring Oracle E-Business Suite Integrated SOA Gateway for SOAP Services on Release 12.2.x Upgraded From Oracle E-Business Suite Release 12.1.x
Section 10: Upgrading Oracle SOA Suite 11g to Oracle SOA Suite 12c in an Existing Configuration
Section 11: Performing Post-Oracle E-Business Suite Database Upgrade Tasks for ISG-Configured Instances
Section 11.1: Setup Tasks on Oracle E-Business Suite
Section 11.2: Setup Tasks on Oracle SOA Suite
Section 11.3: Validating the Setup for Post-Database Upgrade
Note: For more information about Oracle E-Business Suite Integrated SOA Gateway Release 12.2, see the following Knowledge Documents on My Oracle Support:
Document 2693640.1: Oracle E-Business Suite Integrated SOA Gateway Release Notes for Release 12.2.10
Document 2563289.1: Oracle E-Business Suite Integrated SOA Gateway Release Notes for Release 12.2.9
Document 2289192.1: Oracle E-Business Suite Integrated SOA Gateway Release Notes for Release 12.2.7 and 12.2.8
Document 2172887.1: Oracle E-Business Suite Integrated SOA Gateway Release Notes for Release 12.2.6
Document 2059499.1: Oracle E-Business Suite Integrated SOA Gateway Release Notes for Release 12.2.5
Document 1908829.1: Oracle E-Business Suite Integrated SOA Gateway Release Notes for Release 12.2.4
Document 1603897.1: Oracle E-Business Suite Integrated SOA Gateway Release Notes for Release 12.2.3
Document 1317697.1: Oracle E-Business Suite Integrated SOA Gateway Troubleshooting Guide, Release 12.2
Document 1332262.1: Setting Up SAML Token Security for Oracle E-Business Suite Integrated SOA Gateway Release 12.2
For information about Oracle E-Business Suite Integrated SOA Gateway Release 12.1, see My Oracle Support Knowledge Document 556540.1, Installing Oracle E-Business Suite Integrated SOA Gateway, Release 12.
Note: For consistency with the naming convention being adopted by other Oracle products, Oracle E-Business Suite Integrated SOA Gateway consolidated patches are marked with a release version in the following format:
year_quarter_fortnight within a quarter
For example, ISG Consolidated Patch for Release 12.2 (20_3_5) indicates the patch is released in the fifth fortnight of the third quarter in 2020.
Part A: Configuring Oracle E-Business Suite REST Services
REST-based web services are deployed on an Oracle E-Business Suite application server. They do not depend on Oracle SOA Suite and Oracle E-Business Suite Adapter. Hence, the configuration steps for REST services are required to be performed only on Oracle E-Business Suite.
Note: Interface types enabled for REST services are PL/SQL, Java Bean Services, Application Module Services, Concurrent Program, Business Service Object, and Open Interface Tables and Views.
The section includes the following topics:
Section 1: Configuring Oracle E-Business Suite Integrated SOA Gateway for REST Services
Section 2: Validating Oracle E-Business Suite Integrated SOA Gateway Setup for REST Services
Section 1: Configuring Oracle E-Business Suite Integrated SOA Gateway for REST Services
Preparation for Oracle E-Business Suite Integrated SOA Gateway REST Services in a Multi-node Environment
To provide high availability of Oracle E-Business Suite REST services, Oracle E-Business Suite Integrated SOA Gateway recommends multiple nodes of an Oracle E-Business Suite environment.
The following diagram represents the high level architecture for Oracle E-Business Suite REST services in a multi-node environment. This diagram contains a hardware load balancer configuration with a single entry point in Oracle E-Business Suite to load balance the web application running on Oracle E-Business Suite application servers 1 and 2. All users access Oracle E-Business Suite application through a single URL.
High Level Architecture for Oracle E-Business Suite REST Services in a Multi-node Environment
For information on configuring Oracle E-Business Suite Release 12.2 with multiple nodes, see Using Load-Balancers with Oracle E-Business Suite Release 12.2, My Oracle Support Knowledge Document 1375686.1.
Note: Ensure that your Oracle E-Business Suite instance is on the latest AD TXK Delta level and has the recommended technology patches in place. See:
My Oracle Support Knowledge Document 1617461.1, Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2
My Oracle Support Knowledge Document 1594274.1, Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes, for recommended list of technology patches
Apply the consolidated Patch 31764501:R12.OWF.C, ISG Consolidated Patch for Release 12.2 (20_3_5), to your instance if it has not already been applied. This patch is required for your instance regardless of a single node or a multi-node environment. Please note that this patch has two prerequisite patches listed in the patch readme which require adop phase=fs_clone to be run after the patch application, if your Oracle E-Business Suite instance is earlier than Release 12.2.9.
Perform the following tasks to configure Oracle E-Business Suite Integrated SOA Gateway for REST services.
Source the run file system and run the following script:
Note: If your Oracle E-Business Suite instance is configured with multiple nodes, perform this step on the Oracle E-Business Suite primary node.
Source the applications run file system environment file:
source /EBSapps.env
Enter 'R' for the run file system.
Run the following script:
perl $FND_TOP/patch/115/bin/ISGRestSetup.pl
The script prompts you to enter the following information:
Enter the password for APPS user:
'APPS' is the default Oracle E-Business Suite database user name. Provide the associated password for the user.
Enter Oracle E-Business Suite's WebLogic Server Admin User Name: [weblogic]
'weblogic' is the default Oracle WebLogic Server Admin user name.
Enter the password for weblogic user:
Provide the associated password for above Oracle WebLogic Server Admin user 'weblogic'.
When the script starts to configure the environment for REST services, the following message appears:
Configuring Oracle E-Business Suite Integrated SOA Gateway for REST Services. Please wait, as this may take a few minutes.
Log file for each step is located at
Note that the setup may take a few minutes to complete. The completion status would be displayed on terminal. Log files are written in the current directory.
Once the configuration script completes successfully, you can check the log file for more information. If the script ends with errors, then errors and warnings are listed in the log file and other log files in the same directory.
Note: The above script creates and deploys the data source "ISGDatasource" on the Oracle E-Business Suite WebLogic Admin server and "oafm_cluster1" server. You can size up the data source connection pool as per the REST service request volume.
(Conditional) Only if your Oracle E-Business Suite release is earlier than 12.2.9, then run adop phase=fs_clone on Oracle E-Business Suite 12.2 enabled for Online Patching to copy the REST configurations done above to the other file system. Refer to step 3 in Section 4.1: Setup Tasks on Oracle E-Business Suite Release 12.2.3 or Later for details.
This step is not required for Oracle E-Business Suite 12.2.9 and onwards.
Important: If SOAP services are also used, complete the entire setup tasks for both SOAP and REST services before running the adop phase=fs_clone command. For information on configuring Oracle E-Business Suite SOAP services, refer to Part B: Configuring Oracle E-Business Suite SOAP Services.
After configuring Oracle E-Business Suite REST services, validate the setup by following steps described in Section 2: Validating Oracle E-Business Suite Integrated SOA Gateway Setup for REST Services.
Section 2: Validating Oracle E-Business Suite Integrated SOA Gateway Setup for REST Services
After completing the setup tasks for REST services described in Section 1, you can perform the validation through the following ways:
Perform the design-time activities from the Integration Repository user interface
For more information on deploying and undeploying REST services through the Integration Repository user interface, refer to Administering REST Web Services Through Integration Repository, Oracle E-Business Suite Integrated SOA Gateway Implementation Guide.
Perform the design-time activities through the backend script
This section describes how to validate the setup using the script.
Perform the following steps to validate the Oracle E-Business Suite Integrated SOA Gateway setup using an Ant script $JAVA_TOP/oracle/apps/fnd/isg/ant/isgDesigner.xml:
Source the applications run file system environment file:
source /EBSapps.env
Enter 'R' for the run file system.
Run the following command and verify the reports generated at $JAVA_TOP/oracle/apps/fnd/isg/ant, for any errors:
ant -f $JAVA_TOP/oracle/apps/fnd/isg/ant/isgDesigner.xml -Dfile=$JAVA_TOP/oracle/apps/fnd/isg/ant/isg_service.xml -Dverbose=OFF
Copy and paste the following URL in a web browser and you should be able to access Oracle E-Business Suite applications from this URL:
http(s)://:/webservices/rest/provider/isActive/
When prompted, provide Oracle E-Business Suite System Administration user credentials. You should see a response, similar to the following in the browser:
...
For more information on how to use the Ant script, refer to Managing REST Service Lifecycle Activities Using an Ant Script, Oracle E-Business Suite Integrated SOA Gateway Implementation Guide.
Part B: Configuring Oracle E-Business Suite SOAP Services
In Oracle E-Business Suite Integrated SOA Gateway (ISG) Release 12.2, SOAP-based web services are deployed on Oracle SOA Suite running on Oracle WebLogic Server. The steps to configure SOAP services span across Oracle E-Business Suite as well as Oracle SOA Suite.
ISG Release 12.2 Certification Matrix
To enable Oracle E-Business Suite SOAP services, Oracle E-Business Suite Integrated SOA Gateway Release 12.2 has the following product dependencies:
ISG Release 12.2 Product Dependencies and Certification Matrix
Oracle SOA Suite
Oracle E-Business Suite Adapter
(Oracle Applications Adapter)
Oracle WebLogic Server
Release 12c (12.2.1.x.x)
12.2.1.4.0
12.2.1.4.0
12.2.1.4.0
12.2.1.3.0
12.2.1.3.0
12.2.1.3.0
12.2.1.2.0
12.2.1.2.0
12.2.1.2.0
12.2.1.1.0
12.2.1.1.0
12.2.1.1.0
12.2.1.0.0
12.2.1.0.0
12.2.1.0.0
Release 12c (12.1.3)
12.1.3
12.1.3
12.1.3
Release 11g
11.1.1.9.0
11.1.1.9.0
Release 11g (10.3.6)
Note: Interface types enabled for SOAP services are PL/SQL, Concurrent Program, XML Gateway (Inbound), and Business Service Object.
Preparing Oracle E-Business Suite Integrated SOA Gateway in a Multi-node Environment
To provide high availability of Oracle E-Business Suite services, Oracle E-Business Suite Integrated SOA Gateway recommends multiple nodes of an Oracle E-Business Suite environment and each node is configured with multiple nodes of Oracle SOA Suite.
The following diagram represents the high level architecture for Oracle E-Business Suite Integrated SOA Gateway Release 12.2 in a multi-node environment. In this diagram, a hardware load balancer configuration with a single entry point in Oracle E-Business Suite to load balance the web application running on Oracle E-Business Suite application servers 1 and 2. In this configuration, all users access Oracle E-Business Suite application through a single URL. Similarly, a separate load balancer is configured for Oracle SOA Suite to load balance the web application running on Oracle SOA Suite application servers.
High Level Architecture for Oracle E-Business Suite Integrated SOA Gateway Release 12.2 in a Multi-node Environment
Preparing Oracle E-Business Suite in a Multi-node Environment
Oracle E-Business Suite environments can be scaled up to handle large numbers of concurrent users by load-balancing across multiple application middle tier servers. For information on configuring Oracle E-Business Suite Release 12.2 with multiple nodes, see Using Load-Balancers with Oracle E-Business Suite Release 12.2, My Oracle Support Knowledge Document 1375686.1.
This section includes the following topics:
Section 3: Performing Prerequisites Tasks for SOAP Services:
Section 3.1: Installing Oracle WebLogic Server and Oracle SOA Suite for SOAP Services
Section 3.2: Enabling TLS on Oracle SOA Suite
Section 4: Configuring Oracle E-Business Suite Integrated SOA Gateway for SOAP Services on a New Installation of Release 12.2.3 or Later
Section 4.1: Setup Tasks on Oracle E-Business Suite Release 12.2.3 or Later
Section 4.2: Configuration Steps on Oracle SOA Suite to Integrate with Oracle E-Business Suite
Section 5: Validating Oracle E-Business Suite Integrated SOA Gateway Setup for SOAP Services
Section 6: Deploying Generic XML Gateway Services for SOAP Services (Optional)
Section 3: Performing Prerequisites Tasks for SOAP Services
As mentioned earlier SOAP services are deployed on Oracle SOA Suite running on Oracle WebLogic Server, before you begin the configuration for Oracle E-Business Suite SOAP services, you must have the following tasks in place:
Install and configure Oracle SOA Suite 11g or Oracle SOA Suite 12c on a separate Oracle WebLogic Server (Section 3.1)
Enable TLS on Oracle SOA Suite (Section 3.2)
Section 3.1: Installing Oracle SOA Suite and Oracle WebLogic Server for SOAP Services
To configure Oracle E-Business Suite Integrated SOA Gateway for SOAP services, ensure you have Oracle SOA Suite, Oracle WebLogic Server, and Oracle Database in place. For the supported release versions of Oracle SOA Suite and Oracle WebLogic Server, refer to ISG Release 12.2 Certification Matrix described earlier in Part B.
Preparing Oracle SOA Suite in a Multi-node Environment
Oracle E-Business Suite Integrated SOA Gateway follows the reference topology mentioned in Section 2.1.1.1 MySOACompany Topology with Oracle Access Manager, Oracle Fusion Middleware Enterprise Deployment Guide for Oracle SOA Suite. Ensure to configure LDAP as policy store for the ISG-SOA Oracle WebLogic Server domain created while installing Oracle SOA Suite. Refer to Section 2.4 About LDAP as Credential and Policy Store, Oracle Fusion Middleware Enterprise Deployment Guide for Oracle SOA Suite. Refer to the flow chart illustrated in Figure 2-5, Section 2.6.1 Flow Chart of the Oracle SOA Enterprise Deployment Process, Oracle Fusion Middleware Enterprise Deployment Guide for Oracle SOA Suite. However, skip or ignore the steps for Oracle Business Activity Monitoring (BAM), Oracle Business Process Management (BPM), and Oracle Service Bus (OSB).
For more information on configuring Oracle SOA Suite, see the Oracle Fusion Middleware Enterprise Deployment Guide for Oracle SOA Suite.
Note: To upgrade an existing Oracle SOA Suite instance from 11g to Oracle SOA Suite 12c, see Section 10: Upgrading Oracle SOA Suite 11g to Oracle SOA Suite 12c in an Existing Configuration.
For new installation of Oracle SOA Suite:
Install Oracle WebLogic Server and Oracle SOA Suite using either one of the following options from Oracle Software Delivery Cloud (https://edelivery.oracle.com) or Oracle Technology Network (https://download.oracle.com).
For Oracle SOA Suite 12c and Oracle E-Business Suite Adapter 12c on Oracle WebLogic Server 12c
Refer to Roadmap for Installing and Configuring the Standard Installation Topologies, as described in Oracle Fusion Middleware Installing and Configuring Oracle SOA Suite and Business Process Management.
Ensure to install Oracle WebLogic Server as part of the "Install Fusion Middleware Infrastructure" step. For more information, see Oracle Fusion Middleware Installing and Configuring the Oracle Fusion Middleware Infrastructure.
For Oracle WebLogic Server installation, refer to Oracle Fusion Middleware Installing and Configuring Oracle WebLogic Server and Coherence.
Create and configure a new Oracle WebLogic Server domain for ISG-SOA, instead of extending an existing WebLogic domain.
For Oracle SOA Suite 11g and Oracle Applications Adapter 11g on Oracle WebLogic Server 11g
Refer to Oracle Fusion Middleware Installation Guide for Oracle SOA Suite and Oracle Business Process Management Suite. While following steps in Oracle SOA Suite Installation Roadmap, ensure to:
Install Oracle WebLogic Server as part of the "Install an Application Server" step.
For Oracle WebLogic Server installation, refer to the Oracle Fusion Middleware Installation Guide for Oracle WebLogic Server 10.3.6.
Create and configure a new Oracle WebLogic Server domain for ISG-SOA, instead of extending an existing WebLogic domain.
For an existing Oracle SOA Suite installation:
Follow the installation roadmap mentioned above for your Oracle SOA Suite version, but begin with the "Create a WebLogic domain" step for Oracle SOA Suite 12c or the "Create and configure your Oracle WebLogic Server Domain" step for Oracle SOA Suite 11g. Instead of extending an existing WebLogic domain, create and configure a new Oracle WebLogic Server domain for ISG-SOA.
Section 3.2: Enabling TLS on Oracle SOA Suite
To enable TLS on Oracle SOA Suite with a single node configuration, perform the following tasks to import a CA-issued SSL certificate to Oracle SOA Suite administration server and managed servers:
Note: For Oracle SOA Suite with multi-node configuration, follow the steps in Section 19.1.2 Enabling SSL Communication Between the Middle Tier and the Hardware Load Balancer, Oracle Fusion Middleware Enterprise Deployment Guide for Oracle SOA Suite.
Create a keystore for identity and Certificate Signing Request (CSR) through the following steps:
Create a keystore for identity on Oracle SOA Suite, as shown in the example below:
cd /u01/data/domains/SOA_domain/security
keytool -genkey -alias mykey -keyalg RSA -sigalg SHA256withRSA -keysize 2048 -keystore soaidentity.jks -keypass password -storepass password -validity 3650 -dname "CN=,O=Oracle Corporation,L=Redwood City,ST=California,C=US"
Create a CSR for the above keystore. For example, the following command creates a CSR in the file server_oc.csr:
keytool -certreq -sigalg SHA256withRSA -alias myKey -keystore soaidentity.jks -file server_oc.csr
Submit the CSR to CA.
Copy the certificates received on Oracle SOA Suite.
Import the certificates into the keystore through the following sequence:
keytool -import -alias root -trustcacerts -file rootCA.pem -keystore soaidentity.jks -storepass password
keytool -import -alias intermediate -trustcacerts -file interCA.pem -keystore soaidentity.jks -storepass password
keytool -import -alias mykey -trustcacerts -file serverCert.crt -keystore soaidentity.jks -storepass password
Configure custom identity and custom trust using the following steps:
Log in to Oracle WebLogic Server Administration Console.
In the Administration Console, expand Environment and then select Servers from the Domain Structure. On the Servers page, click the name of the server . To configure the Oracle SOA Suite administration server, click the administration server name. To configure an Oracle SOA Suite managed server, click the managed server name.
Select the Configuration tab, then the General subtab and then select the SSL Listen Port Enabled check box.
You may use the default SSL Listen Port mentioned for the server.
Select the Keystores subtab under the Configuration tab.
Click the drop-down menu next to Keystores and select "Custom Identity and Custom Trust".
Enter the following information:
In the Identity section, define attributes for the identity keystore:
Custom Identity Keystore: enter . For example, /u01/data/domains/SOA_domain/security/soaidentity.jks.
By default Oracle WebLogic Server will look for this keystore file in the domain_home location.
Custom Identity Keystore Type : jks
Custom Identity Keystore Passphrase: enter , such as password.
In the Trust section, define attributes for the trust keystore:
Custom Trust Keystore: enter . For example, /u01/data/domains/SOA_domain/security/soaidentity.jks.
By default Oracle WebLogic Server will look for this keystore file in the domain_home location.
Custom Trust Keystore Type : jks
Custom Trust Keystore Passphrase: enter .
Save the changes.
Click the SSL subtab under the Configuration tab.
Enter the following information:
Private Key Alias: Enter , such as mykey.
Private Key Passphrase: Enter , such as password.
Save the changes.
Click the Advanced field, and set the "Hostname Verification:" to 'None' from the drop-down menu.
Select the hostname verification as 'None' if the CN of the certificate is not the same as the hostname of the machine where Oracle WebLogic Server is installed.
Additionally, to enforce TLSv1.2 only on Oracle SOA Suite, add the following JVM option to the JAVA_OPTIONS settings in the setDomainEnv.sh file:
JAVA_OPTIONS="${JAVA_OPTIONS} -Dweblogic.security.SSL.minimumProtocolVersion=TLSv1.2 -Dhttps.protocols=TLSv1.2"
export JAVA_OPTIONS
Restart Oracle SOA Suite servers.
Verify your TLS configuration.
If you are configuring the Oracle SOA Suite administration server, access your Oracle WebLogic Server Administration Console over the following URL and verify the secure icon is present:
https://:/console/
If you are configuring Oracle SOA Suite managed servers, access the following URL in a web browser:
https://:/soa-infra/
Section 4: Configuring Oracle E-Business Suite Integrated SOA Gateway for SOAP Services on a New Installation of Release 12.2.3 or Later
Note: Ensure that your Oracle E-Business Suite instance is on the latest AD TXK Delta level and has the recommended technology patches in place. See:
My Oracle Support Knowledge Document 1617461.1, Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2
My Oracle Support Knowledge Document 1594274.1, Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes, for recommended list of technology patches
Apply the consolidated Patch 31764501:R12.OWF.C, ISG Consolidated Patch for Release 12.2 (20_3_5), to your instance if it has not already been applied. This patch is required for your instance regardless of a single node or a multi-node environment. Please note that this patch has two prerequisite patches listed in the patch readme which require adop phase=fs_clone to be run after the patch application, if your Oracle E-Business Suite instance is earlier than Release 12.2.9.
Perform the setup tasks described in the following sections:
Section 4.1: Setup Tasks on Oracle E-Business Suite Release 12.2.3 or Later
Section 4.2: Configuration Steps on Oracle SOA Suite to Integrate with Oracle E-Business Suite
Section 4.1: Setup Tasks on Oracle E-Business Suite Release 12.2.3 or Later
Perform the following steps to configure Oracle E-Business Suite Integrated SOA Gateway on Oracle E-Business Suite Release 12.2.3 or later:
Source the environment and run the ISGSoapSetupEBS script:
Source the applications run file system environment file:
source /EBSapps.env
Enter 'R' for the run file system.
Run the ISGSoapSetupEBS script using the following command:
Note: If your Oracle E-Business Suite instance is configured with multiple nodes, perform this step on each Oracle E-Business Suite node of the multi-node environment.
perl $FND_TOP/patch/115/bin/ISGSoapSetupEBS.pl
The script prompts you to enter the following information:
Enter the password for APPS user:
'APPS' is the default Oracle E-Business Suite database user name. Provide the associated password for the user.
Enter Oracle E-Business Suite's WebLogic Server Admin User Name [weblogic]:
'weblogic' is the default Oracle WebLogic Server Admin user name.
Enter the password for ${weblogicUser} user:
Provide the associated password for above Oracle WebLogic Server Admin user.
Enter Oracle E-Business Suite WebLogic Admin Server's URL (:) :
Provide the Oracle E-Business Suite WebLogic administration server's :. For example, host01.example.com:7001.
Is Oracle E-Business Suite configured in multiple nodes (Y/ N) :
Enter 'Y' if Oracle E-Business Suite is configured in multiple nodes.
Enter 'N' if Oracle E-Business Suite is configured in a single node.
If your Oracle E-Business Suite is configured in multiple nodes, the following additional prompt appears:
Is this the primary node for the setup? Y/N
Enter 'Y' if this is the primary node.
Enter 'N' if this is your secondary node.
Is Oracle SOA Suite configured in a Cluster (Y/ N) :
If your Oracle SOA Suite is configured in a cluster environment, see: Oracle SOA Suite in Multiple Nodes.
If your Oracle SOA Suite is not configured in a cluster environment, see: Oracle SOA Suite in a Single Node.
Oracle SOA Suite in Multiple Nodes
The following prompts appear if your Oracle SOA Suite is in a cluster environment:
Enter Oracle SOA Suite WebLogic Admin Server's URL (http(s)://:) :
Provide the Oracle SOA Suite WebLogic administration server's URL with non-TLS (HTTP) listen port in the following format: http://:
Note: If the Oracle SOA Suite WebLogic administration server is configured with virtual host, provide the virtual host and port of that administration server.
Enter Oracle SOA Suite's Cluster URL (http(s)://:) :
Provide the Oracle SOA Suite Cluster URL (load balancer URL of the SOA managed server) in the following format: http(s)://:
Enter Oracle SOA Suite Server's Hostnames separated by comma (, ) :
Provide the fully qualified hostnames of the Oracle SOA Suite server separated by comma. For example, soahost1.example.com, soahost2.example.com.
If you have configured the virtual hosts for Oracle SOA Suite servers, provide the physical fully qualified hostname of each of the SOA nodes.
Enter IP addresses of above Oracle SOA Suite Servers separated by comma (, ) :
Provide the IP addresses of the Oracle SOA Suite servers separated by comma. For example, xx.xxx.xx.xx, xx.xxx.xx.xx.
If you have configured the virtual hosts and virtual IPs for Oracle SOA Suite servers, provide the IP address corresponding to the physical hostnames of each of the SOA nodes. Ensure that you provide the IP addresses in the same order as the hostnames provided in the previous prompt.
Oracle SOA Suite in a Single Node
If your Oracle SOA Suite is not configured with multiple nodes, the following prompts appear:
Enter Oracle SOA Suite WebLogic Admin Server's URL (http(s)://:) :
Provide the Oracle SOA Suite WebLogic administration server's URL with non-TLS (HTTP) listen port in the following format: http://:
Enter Oracle SOA Suite's Managed Server URL (http(s)://:) :
Provide the Oracle SOA Suite managed server's URL in the following format: http(s)://:
Enter Oracle SOA Suite Server's Hostname () :
Provide the fully qualified hostname of the Oracle SOA Suite server. For example, soahost.example.com.
Enter IP address of above Oracle SOA Suite Server () :
Provide the IP address of the Oracle SOA Suite server. For example, xx.xxx.xx.xx.
Note: This configuration may take a few minutes to complete. The completion status would be displayed on terminal. Log files are written in the current directory.
As part of the script run, you would see the message like:
Generating DBC file for Host: soahost1.example and IP: xx.xxx.xx.xx ...
DBC file generation completed successfully. Check../logs/appl/rgf/TXK/EBS_BPEL_payload_host01.example.zip
For Oracle SOA Suite with multiple nodes, the EBS_BPEL_payload zip file would be created for each SOA node. Record the zip file generated for each SOA host. The zip file, such as EBS_BPEL_payload_host01.example.zip shown above, would be placed in a respective SOA node as described in Section 4.2.
To restrict access to Oracle E-Business Suite database from external nodes, various tasks should be performed based on your Oracle E-Business Suite database.
For an Oracle Real Application Clusters (RAC) database with grid listener:
Note: This step applies to an Oracle E-Business Suite database 19c, 12c, or an earlier release.
Update sqlnet.ora in the Grid home of each database node, with the following properties:
tcp.validnode_checking = yes
tcp.invited_nodes=()
SQLNET.INBOUND_CONNECT_TIMEOUT = 60
Stop the database listener from the Grid home using the following command:
srvctl stop listener
Stop the scan listener from the Grid home using the following command:
srvctl stop scan_listener
Start the database listener from the Grid home using the following command:
srvctl start listener
Start the scan listener from the Grid home using the following command:
srvctl start scan_listener
For an Oracle RAC database with local listener, perform the following steps on each database node. In an environment that does not use Oracle RAC, follow the steps in a database node:
If your Oracle E-Business Suite database is 19c, perform the following tasks:
Source the PDB database tier environment file.
source /_.env
Run AutoConfig on the database tier.
sh $ORACLE_HOME/appsutil/scripts//adautocfg.sh
Verify that $TNS_ADMIN/sqlnet.ora contains the following properties:
tcp.validnode_checking = yes
tcp.invited_nodes=()
SQLNET.INBOUND_CONNECT_TIMEOUT = 60
$TNS_ADMIN/sqlnet.ora will be updated only if the profile option FND_SQLNET_ACCESS is set to ALLOW_RESTRICTED.
Source the CDB database tier environment file.
source /_.env
Stop the database listener using the following command:
$ORACLE_HOME/appsutil/scripts//adcdblnctl.sh stop $ORACLE_SID
Start the database listener using the following command:
$ORACLE_HOME/appsutil/scripts//adcdblnctl.sh start $ORACLE_SID
If your Oracle E-Business Suite database is 12c or an earlier release, perform the following tasks:
Source the database tier environment file.
source /_.env
Run AutoConfig on the database tier.
sh $ORACLE_HOME/appsutil/scripts//adautocfg.sh
Stop the database listener using the following command:
$ORACLE_HOME/appsutil/scripts//addlnctl.sh stop $ORACLE_SID
Verify that $TNS_ADMIN/sqlnet.ora contains the following properties:
tcp.validnode_checking = yes
tcp.invited_nodes=()
SQLNET.INBOUND_CONNECT_TIMEOUT = 60
$TNS_ADMIN/sqlnet.ora will be updated only if the profile option FND_SQLNET_ACCESS is set to ALLOW_RESTRICTED.
Start the database listener using the following command:
$ORACLE_HOME/appsutil/scripts//addlnctl.sh start $ORACLE_SID
Conditional) Only if your Oracle E-Business Suite release is earlier than 12.2.9, then perform the following step on Oracle E-Business Suite 12.2 enabled for Online Patching to copy the SOAP configurations done above to the other file system. This step is not required for Oracle E-Business Suite 12.2.9 and onwards.
Important: If REST services are also used, complete the entire setup tasks for both SOAP and REST services before running the adop phase=fs_clone command. For information on configuring REST services, refer to Section 1: Configuring Oracle E-Business Suite Integrated SOA Gateway for REST Services.
adop phase=fs_clone
Note: Oracle E-Business Suite Integrated SOA Gateway would be configured on the other file system once the above command is run and the second file system will be active when a patch is applied using the following commands for Online Patching:
adop phase=prepare
adop phase=apply
adop phase=cutover
During the cutover phase of online patching, Oracle E-Business Suite Integrated SOA Gateway services would be unavailable. Service invocation should resume once the cutover phase is complete.
After the cutover, isgagent.properties on Oracle E-Business Suite is not synchronized between both file systems.
Additional Information: Online Patching stores multiple application editions in the database, and has the provision of a dual application tier file system. At any given point in time, one of these file systems is active and designated as the 'run' file system (part of the running system) and the other as the 'patch' file system (either being patched or awaiting the start of the next patching cycle). Whichever is the current run file system appears to the user in exactly the same way as the single application tier file system did in Oracle E-Business Suite releases prior to Release 12.2.
Please note that for a successful setup, either both the systems should be TLS enabled or none of them should be TLS enabled. For more information about Online Patching, see Oracle E-Business Suite Release 12.2: Online Patching FAQ, My Oracle Support Knowledge Document 1583902.1 and the Oracle E-Business Suite Maintenance Guide.
Section 4.2: Configuration Steps on Oracle SOA Suite to Integrate with Oracle E-Business Suite
Perform the following setup steps on Oracle SOA Suite to integrate with Oracle E-Business Suite:
Note: Perform the following steps 1, 2, 3a, and 3b in this section only if Oracle E-Business Suite is TLS (HTTPS) enabled. These steps may be required only if Oracle E-Business Suite is TLS enabled and the root CA certificate for Oracle E-Business Suite is not present in cacerts of Oracle SOA Suite.
Step 3c should be performed, irrespective of whether Oracle E-Business Suite is TLS enabled or not.
Verify if the CA certificate to be imported from issuing authority is valid. Browse the secure EBS Login URL(https://:/OA_HTML/AppsLogin). Click on the Certificate Error icon. Ensure that there is no error message like "Invalid certificate". Invalid certificate might cause TLS handshake errors later in service generation. A valid self-signed certificate can be like:
A valid self-signed certificate
Import the CA certificate of the issuing authority from Oracle E-Business Suite into Oracle SOA Suite.
Copy {s_web_ssl_directory}/Apache/.crt from Oracle E-Business Suite to your Oracle SOA Suite server.
Note: Location of the CERTS directory is controlled using context variable .
Use the following methods to locate which jre is being used by Oracle SOA Suite:
source /bin/setDomainEnv.sh (or \bin\setDomainEnv.cmd on Windows).
echo $JAVA_HOME: This will show which jdk is being used.
environment variable might not be set, but $JAVA_HOME/jre could be used as location.
Note: If your Oracle SOA Suite instance is configured with multiple nodes, should be that of Oracle SOA Suite administration and managed servers.
Note: If $JAVA_HOME is in the shared file system, you may perform this step once from the home directory of your Oracle SOA Suite administration or managed server.
Navigate to the /lib/security directory and import the Oracle E-Business Suite certificate into the default keystore (cacerts) using the following command:
keytool -import -alias ApacheServer -file -trustcacerts -v -keystore cacerts -storepass
For example,
keytool -import -alias ApacheServer -file /tmp/.crt -trustcacerts -v -keystore ./cacerts -storepass password
Note: If the keytool command fails with keytool error, java.io.FileNotFoundException: cacerts (Permission denied), try the import with super user.
In the setDomainEnv script, perform the following tasks:
Note: If your Oracle SOA Suite instance is configured with multiple nodes, perform this step on the domain home directory of both the Oracle SOA Suite administration and managed servers.
Set JAVA_OPTIONS with property for truststore:
Note that mentioned here indicates the same as described earlier in Step 2 of this section.
In setDomainEnv.sh for UNIX/Linux:
JAVA_OPTIONS="${JAVA_OPTIONS} -Djavax.net.ssl.trustStore=/lib/security/cacerts"
export JAVA_OPTIONS
In setDomainEnv.cmd for Windows:
set JAVA_OPTIONS=%JAVA_OPTIONS% -Djavax.net.ssl.trustStore=\lib\security\cacerts
Set JAVA_OPTIONS with property for JSSE (Oracle SOA Suite 11g only):
In setDomainEnv.sh for UNIX/Linux:
JAVA_OPTIONS="${JAVA_OPTIONS} -Dweblogic.ssl.JSSEEnabled=true"
export JAVA_OPTIONS
In setDomainEnv.cmd for Windows:
set JAVA_OPTIONS=%JAVA_OPTIONS% -Dweblogic.ssl.JSSEEnabled=true
Search and set the WLS_JDBC_REMOTE_ENABLED property to enable exception handling (Oracle SOA Suite 11g only):
In setDomainEnv.sh for UNIX/Linux:
WLS_JDBC_REMOTE_ENABLED="-Dweblogic.jdbc.remoteEnabled=true"
In setDomainEnv.cmd for Windows:
set WLS_JDBC_REMOTE_ENABLED=-Dweblogic.jdbc.remoteEnabled=true
Create a temporary folder (designated here as ) on the Oracle SOA Suite server. Please note that this folder can be created in any directory with write permission on the server.
Note: For Oracle SOA Suite configured with multiple nodes, the folder should be created in the shared file system.
Create a folder called on the Oracle SOA Suite server for the DBC file. This folder can be created in any directory with write permission on the server.
Note: For Oracle SOA Suite configured with multiple nodes, the folder should be created in the non-shared file system on each Oracle SOA Suite node. Use the same folder name in the same directory path on all Oracle SOA Suite nodes.
For example, if you create the folder in the /scratch/u01/ directory on the non-shared file system of the Oracle SOA Suite node1, then other Oracle SOA Suite nodes should also have the folder in the /scratch/u01/ directory on their non-shared file system.
Copy the EBS_BPEL_payload.zip file created as part of the step 1 in Section 4.1 from Oracle E-Business Suite (/$INST_TOP/logs/appl/rgf/TXK/EBS_BPEL_payload.zip) to the folder on the Oracle SOA Suite server.
For Oracle SOA Suite configured with multiple nodes, the EBS_BPEL_payload.zip file for each SOA node should be copied to the folder on the Oracle SOA Suite server. For example, zip files, such as EBS_BPEL_payload_host01.example.com.zip and EBS_BPEL_payload_host02.example.com.zip should be copied to the directory.
Download and copy either one of the following application patch into the directory based on your Oracle SOA Suite version:
For Oracle SOA Suite 12.2.1.4.0, apply Patch 31783707.
For Oracle SOA Suite 12.2.1.3.0 or earlier, apply Patch 31764510.
Unzip and extract the contents in the downloaded patch into the directory.
Ensure that the following are in place before proceeding to next step:
Oracle SOA Suite administration and managed servers are running.
Node Manager is also up and running on all nodes.
boot.properties file with user credentials for stopping and starting the servers in background is in place ($DOMAIN_HOME/servers/SERVER_NAME/security directory of all servers).
Listen address is configured for the administration and SOA managed servers.
For a multi-node configuration, load balancer is up and running. It is accessible from the SOA administration and SOA managed servers.
SOA administration and managed servers are able to access Oracle E-Business Suite servers.
Run the setup script:
Note: If your Oracle SOA Suite instance is configured with multiple nodes or in a cluster, you may have a separate domain home directory for Oracle SOA Suite administration and managed servers. This step should be performed for each domain home directory of Oracle SOA Suite administration and managed servers.
While following the instructions in this step, ensure to:
First, run the script on the secondary nodes (SOA node n). Then, run the script on the primary node (SOA node 1).
On the secondary nodes:
Run the script by sourcing the setDomainEnv.sh file in the domain home directory of the SOA managed servers (as mentioned in substep 'a'). In this case, provide the value for soaIsPrimaryNode as 'No' (as mentioned in substep 'c').
On the primary node:
If a separate domain home directory exists for the SOA managed server, run the script by sourcing the setDomainEnv.sh file in the domain home directory of the SOA managed server (as mentioned in substep 'a'). In this case, provide the value for soaIsPrimaryNode as 'No' (as mentioned in substep 'c').
Run the script by sourcing the setDomainEnv.sh file in the domain home directory of the SOA administration server (as mentioned in substep 'a'). In this case, provide the value for soaIsPrimaryNode as 'Yes' (as mentioned in substep 'c').
Source the following files:
source /server/bin/setWLSEnv.sh
source /bin/setDomainEnv.sh
represents the ISG-SOA Domain home.
Change directory to the folder:
cd
Run the setup script using the similar command by changing the information based on your Oracle E-Business Suite and Oracle SOA Suite server configuration and current node information:
java oracle.apps.fnd.isg.setup.SoapSetup ebsUrl= soaAdminUrl= soaManagedUrl= soaWeblogicUser= ebsDbsid= soaOracleHome= appsUser= soaIsCluster= soaClusterName= soaIsPrimaryNode= soaServerName= dbcDirectory=
ebsUrl: Enter your Oracle E-Business Suite URL in the following format: http(s)://:
soaAdminUrl: Enter your Oracle SOA Cloud Service administration sever URL with non-TLS (HTTP) listen port in the following format: http://:
soaManagedUrl: Enter your Oracle SOA Suite managed sever URL in the following format: http(s)://:
For a multi-node configuration, provide the load-balanced host and port in the same format mentioned here.
soaWeblogicUser: Enter your WebLogic server user name.
ebsDbsid: Enter Oracle E-Business Suite database SID information.
soaOracleHome: Enter Oracle SOA Suite Oracle Home.
appsUser: Enter Oracle E-Business Suite application user name.
soaIsCluster: Enter 'Yes' if your Oracle SOA Suite is configured in multiple nodes; and enter 'No' if it is not.
soaClusterName: Enter your Oracle SOA Suite cluster name, such as soa_cluster.
soaIsPrimaryNode: Enter 'Yes' if this is your primary node and if you have sourced the setDomainEnv.sh file of the SOA administration server domain home directory; enter 'No' for other cases.
soaServerName: Enter the Oracle SOA Suite server name, such as soa_server.
For Oracle SOA Suite in a cluster environment with multiple SOA managed servers on the node, enter the Oracle SOA Suite server name of any one SOA managed server on the node.
dbcDirectory: Enter the DBC directory information.
For help on the options passed, run java oracle.apps.fnd.isg.setup.SoapSetup -help.
If Oracle SOA Suite 11g is installed on AIX, Solaris, or HP-UX Itanium platform, modify the setting of the POST_CLASSPATH variable in the /bin/setDomainEnv.sh file as follows:
if ["${POST_CLASSPATH}" != "" ]; then
POST_CLASSPATH="${COMMON_COMPONENTS_HOME}/modules/oracle.jrf_11.1.1/jrf-client.jar${CLASSPATHSEP}${POST_CLASSPATH}"
POST_CLASSPATH="${COMMON_COMPONENTS_HOME}/modules/oracle.jrf_11.1.1/jrf.jar${CLASSPATHSEP}${POST_CLASSPATH}"
export POST_CLASSPATH
else
POST_CLASSPATH="${COMMON_COMPONENTS_HOME}/modules/oracle.jrf_11.1.1/jrf-client.jar"
POST_CLASSPATH="${COMMON_COMPONENTS_HOME}/modules/oracle.jrf_11.1.1/jrf.jar${CLASSPATHSEP}${POST_CLASSPATH}"
export POST_CLASSPATH
fi
Stop and restart all Oracle SOA Suite administration and managed servers from the command prompt. If Oracle SOA Suite is configured for multiple SOA nodes, ensure the SOA load balancer and node managers are up and running.
After configuring Oracle E-Business Suite Integrated SOA Gateway Release 12.2.3 or later, validate the setup by following steps described in Section 5: Validating Oracle E-Business Suite Integrated SOA Gateway Setup for SOAP Services.
Section 5: Validating Oracle E-Business Suite Integrated SOA Gateway Setup for SOAP Services
To ensure the successful installation or upgrade to Release 12.2.x, you can validate the setup tasks for SOAP services through the following ways:
Perform the design-time activities from the Integration Repository user interface
For more information on performing the design-time activities through the Integration Repository user interface, refer to Administering SOAP Web Services Through Integration Repository, Oracle E-Business Suite Integrated SOA Gateway Implementation Guide.
Run the diagnostic tests through backend processing
Oracle E-Business Suite Integrated SOA Gateway uses an Ant script $JAVA_TOP/oracle/apps/fnd/isg/ant/isgDesigner.xml to run the diagnostic tests through backend processing. This section describes how to validate the setup using the script.
Perform the following steps to validate the Oracle E-Business Suite Integrated SOA Gateway setup using the diagnostic script:
Source the applications run file system environment file:
source /EBSapps.env
Enter 'R' for the run file system.
Run the following commands and verify the reports generated at $JAVA_TOP/oracle/apps/fnd/isg/ant, for any errors:
ant -f $JAVA_TOP/oracle/apps/fnd/isg/ant/isgDesigner.xml DiagnoseAPPSetup -Dverbose=ON
This command runs configuration checks on the Oracle SOA Suite side.
ant -f $JAVA_TOP/oracle/apps/fnd/isg/ant/isgDesigner.xml DiagnoseAGENTSetup -Dverbose=ON
This command runs configuration checks on the Oracle E-Business Suite side.
ant -f $JAVA_TOP/oracle/apps/fnd/isg/ant/isgDesigner.xml DiagnoseISGFunctionality -Dverbose=ON
This command runs all design-time operations for all types of interfaces in Oracle E-Business Suite Integrated SOA Gateway.
Note: When running this command, you may find the following errors for Java APIs for Forms. These errors may be shown in the ISGFunctionalDiagnosticsReport.xml report.
oracle.apps.fnd.isg.common.error:ISGException: ISG_SERVICE_GENERATE_ERROR: Error in generating JAVA package
/oracle/apps/fnd/isg/ant/isgDesigner.xml as follows:
ant -f $JAVA_TOP/oracle/apps/fnd/isg/ant/isgDesigner.xml deployGenericXMLG -Dverbose=
Search for an XML Gateway interface in the Integration Repository. Observe that generic XML Gateway service is deployed. Click on the WSDL link to see the deployed generic XML Gateway WSDL description.
Part C: Configuring Oracle E-Business Suite Integrated SOA Gateway for Upgrading and Cloning Scenarios
This section includes the following topics:
Section 7: Configuring Oracle E-Business Suite Integrated SOA Gateway on Release 12.2.x Cloned From an Existing 12.2.x Environment
Section 8: Configuring Oracle E-Business Suite Integrated SOA Gateway for REST Services on Release 12.2.x Upgraded From Oracle E-Business Suite Release 12.1.x
Section 9: Configuring Oracle E-Business Suite Integrated SOA Gateway for SOAP Services on Release 12.2.x Upgraded From Oracle E-Business Suite Release 12.1.x
Section 10: Upgrading Oracle SOA Suite 11g to Oracle SOA Suite 12c in an Existing Configuration
Section 11: Performing Post-Oracle E-Business Suite Database Upgrade Tasks for ISG-Configured Instances
Note: For information on upgrading Oracle SOA Suite 11g to Oracle SOA Suite 12c for Oracle E-Business Suite SOAP services, refer to Section 10: Upgrading Oracle SOA Suite 11g to Oracle SOA Suite 12c in an Existing Configuration.
Section 7: Configuring Oracle E-Business Suite Integrated SOA Gateway on Release 12.2.x Cloned From an Existing 12.2.x Environment
This configuration applies to an Oracle E-Business Suite instance either with or without Oracle E-Business Suite Integrated SOA Gateway configuration being cloned.
Enable your Oracle E-Business Suite Integrated SOA Gateway by following instructions as described in Section 1 for REST services or Section 4 for SOAP services.
Note: Ensure that you have applied the patches mentioned in Section 1 and Section 4.
Log in to Oracle E-Business Suite middle tier and run the following script:
ant -f $JAVA_TOP/oracle/apps/fnd/isg/ant/isgDesigner.xml -Dactions="postclone" -Dverbose=ON
Enter Service Type : (SOAP, [REST], BOTH)
Important: If an API has overloaded methods, the postclone script may not work as expected. Hence, before running the script, make a note of the deployed overloaded methods. Additionally, after running the script, redeploy the overloaded methods from Oracle Integration Repository UI page or using backend script.
Note that the postclone script for cloning REST services is currently certified with Oracle E-Business Suite shared file system.
To clone SOAP services, select "SOAP" as the Service Type value.
A report file PostCloneResults.txt is generated. This file is created on the Oracle SOA Suite instance in .ISG_TEMP_DIRECTORY_LOCATION mentioned in the /appsutil//bpel/isg_contextfile.properties file.
To clone REST services, leave the default selection "REST" as the Service Type value.
The script writes results to the $INST_TOP/soa/RESTPostCloneResults.txt file. It includes postclone status and WADL URL for each deployed interface. If the script fails to redeploy an interface, it is also mentioned in this file.
To clone both SOAP and REST services, select "BOTH" as the Service Type value.
The script writes results to PostCloneResults.txt for SOAP services and RESTPostCloneResults.txt for REST services.
Important: There is no change in the Oracle E-Business Suite Integrated SOA Gateway services when the postclone method is called except the Service End Point (Service Location) URL of these services. The new Service End Point (Service Location) URL is listed in the file /soa/PostCloneResults.txt for SOAP services or the RESTPostCloneResults.txt file for REST services. It is also available in the Interface Detail page of the Integration Repository.
Section 8: Configuring Oracle E-Business Suite Integrated SOA Gateway for REST Services on 12.2.x Upgraded From Oracle E-Business Suite Release 12.1.x
Perform the following steps to configure Oracle E-Business Suite Integrated SOA Gateway Release 12.2.x upgraded from Oracle E-Business Suite Release 12.1.x:
Follow the upgrade information in the Oracle E-Business Suite Upgrade Guide: Release 12.0 and 12.1 to 12.2 to upgrade Oracle E-Business Suite Release 12.2.2 from Release 12.1.x.
Note: Oracle E-Business Suite Upgrade Guide: Release 12.0 and 12.1 to 12.2 is required by Release 12.0 and Release 12.1 customers applying Release 12.2.2.
If you are upgrading from Release 12.1.x to Release 12.2.3 or later, complete the upgrade of Oracle E-Business Suite Release 12.2.0, including the preparing for the upgrade tasks and post-upgrade steps, before you apply Oracle E-Business Suite Release 12.2.3 or later.
Oracle E-Business Suite
Relevant Resources
Oracle E-Business Suite Release 12.2.10
Document 2666934.1, Oracle E-Business Suite Release 12.2.10 Readme
Document 2661861.1, Applying the R12.ATG_PF.C.Delta.9 Release Update Pack
Oracle E-Business Suite Release 12.2.9
Document 2495027.1, Oracle E-Business Suite Release 12.2.9 Readme
Document 2490921.1, Applying the R12.ATG_PF.C.Delta.8 Release Update Pack
Oracle E-Business Suite Release 12.2.8
Document 2393248.1, Oracle E-Business Suite Release 12.2.8 Readme
Document 2230914.1, Applying the R12.ATG_PF.C.Delta.7 Release Update Pack
Oracle E-Business Suite Release 12.2.7
Document 2230783.1, Oracle E-Business Suite Release 12.2.7 Readme
Document 2230914.1, Applying the R12.ATG_PF.C.Delta.7 Release Update Pack
Oracle E-Business Suite Release 12.2.6
Document 2114016.1, Oracle E-Business Suite Release 12.2.6 Readme
Document 2113163.1, Applying the R12.ATG_PF.C.Delta.6 Release Update Pack
Oracle E-Business Suite Release 12.2.5
Document 1983050.1, Oracle E-Business Suite Release 12.2.5 Readme
Document 1983021.1, Applying the R12.ATG_PF.C.Delta.5 Release Update Pack
Oracle E-Business Suite Release 12.2.4
Document 1617458.1, Oracle E-Business Suite Release 12.2.4 Readme
Document 1642465.1, Applying the R12.ATG_PF.C.Delta.4 Release Update Pack
Oracle E-Business Suite Release 12.2.3
Document 1586214.1, Oracle E-Business Suite Release 12.2.3 Readme
Additionally, refer to My Oracle Support Knowledge Document 1581299.1, Oracle E-Business Suite Release 12.2 Information Center, for more information and relevant resources.
To configure REST services, follow all the steps described in Section 1: Configuring Oracle E-Business Suite Integrated SOA Gateway for REST Services.
(Optional) If custom interfaces are used for Oracle E-Business Suite release 12.1.x, Integration Repository Parser (IREP Parser) needs to be configured again for custom interface validation. For information on setting up the IREP Parser and performing required tasks for updated custom interfaces, see Setting Up and Using the Integration Repository Parser and Performing Administrative Tasks for Custom Integration Interfaces and Services, Oracle E-Business Suite Integrated SOA Gateway Implementation Guide.
Section 9: Configuring Oracle E-Business Suite Integrated SOA Gateway for SOAP Services on Release 12.2.x Upgraded From Oracle E-Business Suite Release 12.1.x
Perform the following steps to configure Oracle E-Business Suite Integrated SOA Gateway for SOAP services on Release 12.2.x upgraded from Oracle E-Business Suite Release 12.1.x:
Follow the upgrade information in the Oracle E-Business Suite Upgrade Guide: Release 12.0 and 12.1 to 12.2 to upgrade Oracle E-Business Suite Release 12.2.2 from Release 12.1.x.
Note: Oracle E-Business Suite Upgrade Guide: Release 12.0 and 12.1 to 12.2 is required by Release 12.0 and Release 12.1 customers applying Release 12.2.2.
If you are upgrading from Release 12.1.x to Release 12.2.3 or later, complete the upgrade of Oracle E-Business Suite Release 12.2.0, including the preparing for the upgrade tasks and post-upgrade steps, before you apply Oracle E-Business Suite Release 12.2.3 or later.
Oracle E-Business Suite
Relevant Resources
Oracle E-Business Suite Release 12.2.10
Document 2666934.1, Oracle E-Business Suite Release 12.2.10 Readme
Document 2661861.1, Applying the R12.ATG_PF.C.Delta.9 Release Update Pack
Oracle E-Business Suite Release 12.2.9
Document 2495027.1, Oracle E-Business Suite Release 12.2.9 Readme
Document 2490921.1, Applying the R12.ATG_PF.C.Delta.8 Release Update Pack
Oracle E-Business Suite Release 12.2.8
Document 2393248.1, Oracle E-Business Suite Release 12.2.8 Readme
Document 2230914.1, Applying the R12.ATG_PF.C.Delta.7 Release Update Pack
Oracle E-Business Suite Release 12.2.7
Document 2230783.1, Oracle E-Business Suite Release 12.2.7 Readme
Document 2230914.1, Applying the R12.ATG_PF.C.Delta.7 Release Update Pack
Oracle E-Business Suite Release 12.2.6
Document 2114016.1, Oracle E-Business Suite Release 12.2.6 Readme
Document 2113163.1, Applying the R12.ATG_PF.C.Delta.6 Release Update Pack
Oracle E-Business Suite Release 12.2.5
Document 1983050.1, Oracle E-Business Suite Release 12.2.5 Readme
Document 1983021.1, Applying the R12.ATG_PF.C.Delta.5 Release Update Pack
Oracle E-Business Suite Release 12.2.4
Document 1617458.1, Oracle E-Business Suite Release 12.2.4 Readme
Document 1642465.1, Applying the R12.ATG_PF.C.Delta.4 Release Update Pack
Oracle E-Business Suite Release 12.2.3
Document 1586214.1, Oracle E-Business Suite Release 12.2.3 Readme
Additionally, refer to My Oracle Support Knowledge Document 1581299.1, Oracle E-Business Suite Release 12.2 Information Center, for more information and relevant resources.
Note that if your system is upgraded from Oracle E-Business Suite Release 12.0 or 12.1, and you have leveraged Oracle XML Gateway web services or Oracle E-Business Suite Integrated SOA Gateway feature, install Oracle SOA Suite 11g first, before the upgrade to Release 12.2.
Follow all the steps described in Section 4: Configuring Oracle E-Business Suite Integrated SOA Gateway for SOAP Services on a New Installation of Release 12.2.3 or Later.
(Optional) If custom integration interfaces are used for Oracle E-Business Suite release 12.2.x, Integration Repository Parser (IREP Parser) needs to be configured again for custom interface validation. For information on setting up the IREP Parser and performing required tasks for updated custom interfaces, see Setting Up and Using the Integration Repository Parser and Performing Administrative Tasks for Custom Integration Interfaces and Services, Oracle E-Business Suite Integrated SOA Gateway Implementation Guide.
(Optional) If SAML Token security is used, follow the setup instructions described in My Oracle Support Knowledge Document 1332262.1, Setting Up SAML Token Security for Oracle E-Business Suite Integrated SOA Gateway Release 12.2.
Log in to Oracle E-Business Suite Middle tier and run the following script:
ant -f /oracle/apps/fnd/isg/ant/isgDesigner.xml -Dactions=upgrade -Dverbose=ON
A report file is created in directory /soa/ISGUpgrade.txt which contains the 'Upgrade' Action summary. This summary contains the Service Names and new Service End Point (Service Location) URLs.
Important: There is no change in Oracle E-Business Suite Integrated SOA Gateway services when migrated from 12.1.x to Oracle E-Business Suite Release 12.2.x except Service End Point (Service Location) URL of the deployed services. New Service End Point (Service Location) URL is listed in the file /soa/ISGUpgrade.txt. It is also available in the Interface Detail page of the Integration Repository user interface. To ensure backward compatibility, the previous 12.1.x service endpoint (services deployed in Oracle E-Business Suite) will continue to work at runtime after the upgrade. New service endpoint from Oracle SOA Suite server will be shown in the interface detail page and should be used for client programs.
(Optional) If you have been using generic XML Gateway services in an earlier Oracle E-Business Suite release, perform the steps described in Section 6: Deploying Generic XML Gateway Services to deploy generic XML Gateway services.
Section 10: Upgrading Oracle SOA Suite 11g to Oracle SOA Suite 12c in an Existing Configuration
If your Oracle E-Business Suite Integrated SOA Gateway is already configured with Oracle SOA Suite 11g (11.1.1.9.0) for SOAP-based services and if such Oracle SOA Suite needs to be upgraded to Oracle SOA Suite 12c (12.2.1.2), perform the following steps:
Perform the Oracle Fusion Middleware Pre-Upgrade Tasks, as described in Oracle Fusion Middleware Upgrading SOA Suite and Business Process Management.
Additionally, ensure the following tasks are in place:
Back up the setDomainEnv.sh, setSOADomainEnv.sh, and config.xml files.
If you are using the file-based policy store, create and reassociate OPSS and Audit service to use the database-based or LDAP-based policy store as mentioned in Reassociating the OPSS Security Store, Oracle Fusion Middleware Securing Applications with Oracle Platform Security Services.
Upgrade JDK certified for Oracle SOA Suite 12c. Ensure you uptake the latest policy files for encryption and decryption.
Perform the upgrade by following the instructions described in Upgrading SOA Suite and Business Process Management from 11g, Oracle Fusion Middleware Upgrading SOA Suite and Business Process Management.
Note: Before running reconfig.sh to reconfigure the domain, ensure to remove the following text from /config/config.xml:
ebssid_IsgAuthenticator
SUFFICIENT
jdbc/ebssid
For information about upgrading Oracle SOA Suite in a multi-node environment, see Upgrading a Clustered SOA Environment, Oracle Fusion Middleware Upgrading SOA Suite and Business Process Management.
Additionally, ensure the following tasks are in place:
Install 12c binaries on the SOA secondary nodes where only managed servers are running. Use the same path as used for installing 12c binaries on the primary node for this installation.
Ensure to stop Oracle SOA Suite administration and managed servers as well as associated processes in the sequence, as described in Stopping Servers and Processes, Oracle Fusion Middleware Upgrading SOA Suite and Business Process Management.
Perform a complete upgrade of 11g environment on the primary node by following the tasks listed in the upgrade roadmap table in Upgrading a Clustered Topology.
After a successful upgrade, propagate the domain configuration of the primary node (for example, SOAHOST1) to a secondary node (for example, SOAHOST2).
On SOA Primary Node (SOAHOST1):
Step 1: Run the pack command on the SOA primary node (See Executing the pack command on the server where the Admin Server and one of the Managed Servers is installed)
cd /12c_ORACLE_HOME/oracle_common/common/bin
./pack.sh -domain=/11g_DOMAIN_HOME -template=domainupgradetemplate.jar -template_name=domainupgradetemplate -managed=true
Step 2: Copy the template you just created from the previous step to the secondary node SOAHOST2 (See Copying the template file created in the previous step to SOAHOST2)
scp domainupgradetemplate.jar company@SOAHOST2:12c_ORACLE_HOME/oracle_common/common/bin
On SOA Secondary Node (SOAHOST2):
Step 3: Run the unpack command on the SOA secondary node (See Executing the unpack Command from the 12c Oracle Home on SOAHOST2)
Ensure to empty the contents of 11g_DOMAIN_HOME on the SOA secondary node. Back up your files if this is not already performed.
cd /12c_ORACLE_HOME/oracle_common/common/bin
./unpack.sh -template=domainupgradetemplate.jar - domain=11g_DOMAIN_HOME
Perform validation checks to ensure domain structures are successfully created by following the instructions in Completing the following verification steps after the unpack.
Start the Oracle SOA Suite administration and managed servers by performing the tasks as described in Starting the Admin Server and SOA Managed Servers.
Remove Oracle Web Services Manager (OWSM) targets from SOA and OSB clusters by performing the tasks as described in Removing OWSM Targets from SOA and OSB Clusters.
Rewire the OWSM Policy Manager components by following the instructions as described in Updating OWSM Cross-Component Wiring.
Perform the post-upgrade tasks by following the instructions in Performing Post Upgrade Tasks. Before proceeding, ensure that setDomainEnv.sh and setSOADomainEnv.sh have SOA_ORACLE_HOME set to 12C ORACLE_HOME.
Oracle recommends applying the latest ISG consolidated patch described in Section 4. After applying the latest ISG consolidated patch, you need to follow the steps in Section 4.1 and Section 4.2 to reconfigure Oracle E-Business Suite for ISG SOAP services. If you opt to apply the patch at a later point of time, ensure to follow the steps listed below:
Import Oracle E-Business Suite certificates to a new jdk truststore by following the instructions as described in step 2 of Section 4.2 in this document.
Update isg_contextfile.properties from the /appsutil//bpel directory by adding the following:
#.ISG_FMW_VERSION=12C
.ISG_FMW_VERSION=12C
Provide a new Oracle SOA Suite 12c Oracle Home for ISG_SOA_ORACLE_HOME as:
(For Windows, provide an absolute path with forward slash instead of back slash, for example, C:/fmwhome/oracle_soa.)
#ISG_SOA_ORACLE_HOME=
ISG_SOA_ORACLE_HOME=/u01/Oracle/Middleware12212/Oracle_Home/soa
Perform the following tasks to run the txkISGConfigurator.xml setup script:
Note: If your Oracle SOA Suite instance is configured with multiple nodes or in a cluster, you may have a separate domain home directory for Oracle SOA Suite administration and managed servers. Perform this step for each domain home directory of the Oracle SOA Suite administration and managed servers.
First, run txkISGConfigurator.xml setup on the SOA node SOAHOST2 (secondary nodes). When prompted, provide target type as "cluster", and select "No" when prompted to stop the WebLogic servers.
Then, run txkISGConfigurator.xml setup on the SOA node SOAHOST1 (primary node). When prompted, provide target type as "cluster", and select "Yes" when prompted to stop the WebLogic servers.
Change to Bash shell and source the following files:
source /server/bin/setWLSEnv.sh
source /bin/setDomainEnv.sh
Note that is the new Oracle SOA Suite 12c WebLogic Home; is the existing ISG-SOA domain home.
Start the Oracle SOA Suite administration and managed servers from the command prompt if they are not in running state prior to performing the next step. If Oracle SOA Suite is configured for multiple SOA nodes, ensure the SOA load balancer and node managers are up and running.
Ensure that no changes are active on the Oracle SOA Suite administration and managed servers before running the txkISGConfigurator.xml script using the following command from the /appsutil//bpel folder:
ant -f txkISGConfigurator.xml setup
Respond as shown below to the various prompts that appear:
Enter Oracle WebLogic Server Administration Console URL (host:port) :
Provide the Oracle SOA Suite WebLogic administration server's :. For example, host01.example.com:7001.
Note: For Oracle SOA Suite configured with multiple nodes, if the Oracle SOA Suite WebLogic administration server is configured with virtual host, provide the virtual host and port of that administration server.
Enter Oracle WebLogic Server Admin User Name : [weblogic]
'weblogic' is the default Oracle WebLogic Server Admin user name.
Enter the password for user ${wlsuser} :
Provide the associated password for above Oracle WebLogic Server Admin user name.
Enter the absolute path to the DBC File :
Enter the path to the DBC file: /.dbc
Enter the EBS database SID : []
Accept the default value. If the default value is empty, provide Oracle E-Business Suite database SID as defined in Oracle E-Business Suite applications context file.
Enter the target type : ([server], cluster)
If Oracle SOA Suite is in a single node setup environment, press the [Enter] key for the default selection of 'server'. Otherwise, enter "cluster" for Oracle SOA Suite in multi-node setup.
Enter the SOA Server Name : [soa_server1]
If "server" is selected as the target type, then [soa_server1] is shown. If "cluster" is selected as the target type, then [soa_cluster] is displayed instead.
Provide the Oracle SOA Suite managed server name, such as soa_server1, or soa_cluster.
The script will forcefully stop the Oracle WebLogic Servers now. Do you want to proceed (yes/no)?
If Oracle SOA Suite is in a single node setup environment, enter yes to stop the server. If it is in a multi-node environment,
When the script is run in a secondary node, enter No.
When the script is run in the primary node, enter yes to stop the server.
Note: This ant -f txkISGConfigurator.xml setup command performs the following tasks:
Copy isgmjf.jar to ${WL_HOME}/server/lib/mbeantypes/isgmjf.jar.
Copy fndext.jar to ${DOMAIN_HOME}/lib/fndext.jar.
Create isg.properties and update it with required values.
Update setSOADomainEnv.sh script to set the context file in the JAVA_OPTIONS.
Copy soamon.jar to the $ORACLE_SOA_HOME/soa/modules/oracle.soa.ext_11.1.1 directory. Rebuild oracle.soa.ext.jar by running build.xml.
After running the txkISGConfigurator.xml setup command, isg.properties will have the following default values for optional scheduler properties:
ISG_SCHEDULER_CONFIG_FILE = DEFAULT
ISG_SCHEDULER_LOG_ENABLED = false
To start the scheduler, ISG_SCHEDULER_CONFIG_FILE property needs to be set to DEFAULT. It indicates that the default scheduler configuration file will be used.
The value for property ISG_SCHEDULER_LOG_ENABLED is case sensitive. Value can be either "true" or "false". The property ISG_SCHEDULER_LOG_ENABLED needs to be set to 'true' only when you need scheduler logs to be written. It is written to a file "ISGScheduler.log" in the ISG temporary directory location.
If the following error occurs while running the command, then Oracle SOA Suite servers may not be stopped. Please stop the servers and try it again.
"Unable to rename old file (%FMW_HOME%\Oracle_SOA1\soa\modules\oracle.soa.ext_11.1.1\oracle.soa.ext.jar) to temporary file."
Start the Oracle SOA Suite administration and managed servers from the command prompt if they are not in running state prior to performing the next step.
Ensure that no changes are active on the Oracle SOA Suite administration and managed servers before running txkISGConfigurator.xml updateSecurityRealms using the following commands from the /appsutil//bpel folder:
Note: In a multi-node environment, this script should be run on the SOA node SOAHOST1 (primary node).
source /server/bin/setWLSEnv.sh
source /bin/setDomainEnv.sh
ant -f txkISGConfigurator.xml updateSecurityRealms -DauthenticatorName=isgauth1
Stop and restart all Oracle SOA Suite administration and managed servers from the command prompt. Ensure to source setDomainEnv.sh before starting the servers.
Note: Follow the sequence mentioned in this section to stop the servers (step 2b) and restart the servers (step 2f) for a multi-node environment.
If Oracle SOA Suite is configured with multiple nodes or in a cluster, ensure the load balancer and node manager are up.
After the upgrade, verify the setup for SOAP services as described in Section 5.
Note: For troubleshooting information, refer to Troubleshooting the Upgrade, Oracle Fusion Middleware Upgrading SOA Suite and Business Process Management.
Section 11: Performing Post-Oracle E-Business Suite Database Upgrade Tasks for ISG-Configured Instances
When your Oracle E-Business Suite is configured for Oracle E-Business Suite Integrated SOA Gateway (ISG) and your database is upgraded, you need to perform some setup tasks to ensure Oracle E-Business Suite SOAP services provided through ISG work properly. These post-upgrade tasks are required to be performed on Oracle E-Business Suite and Oracle SOA Suite for SOAP services:
Section 11.1: Setup Tasks on Oracle E-Business Suite
Section 11.2: Setup Tasks on Oracle SOA Suite
Section 11.3: Validating the Setup for Post-Database Upgrade
Note: If your Oracle E-Business Suite is configured for REST services and your database is upgraded, there is no additional setup task specifically for REST services. However, you need to validate the existing REST services setup by following the instructions in Section 11.3 to ensure that REST services work as expected.
Section 11.1: Setup Tasks on Oracle E-Business Suite
Perform the following steps to set up tasks on Oracle E-Business Suite:
Apply Patch 31764501:R12.OWF.C, if not already applied.
Regenerate the DBC file by running the following command from Oracle E-Business Suite to register the external Oracle SOA Suite server with the Oracle E-Business Suite:
ant -f $JAVA_TOP/oracle/apps/fnd/bpel/util/txk_integration_packager.xml createDBC -DFMWNodeName= -DFMWIpAddress=
This script will generate a desktop DBC file, $JAVA_TOP/oracle/apps/fnd/bpel/util/_.dbc.
Tip: To obtain the Oracle SOA Suite IP address for parameter "-DFMWIpAddress", log on to Oracle SOA Suite from backend, and then run the command ping to fetch the information. The txk_integration_packager.xml script updates the fnd_nodes table with the IP address of Oracle SOA Suite specified in the input parameter "-DFMWIpAddress" which can be verified using the following query:
select node_name,server_address,server_id from fnd_nodes where node_name=UPPER('');
Note: If there are two nodes for external Oracle SOA Suite, run the following command for each node. For example,
ant -f $JAVA_TOP/oracle/apps/fnd/bpel/util/txk_integration_packager.xml createDBC -DFMWNodeName= -DFMWIpAddress=
ant -f $JAVA_TOP/oracle/apps/fnd/bpel/util/txk_integration_packager.xml createDBC -DFMWNodeName= -DFMWIpAddress=
Each command will generate a DBC file. The desktop DBC file should be copied to the respective node as mentioned in Step 3 of the Section 11.2: Setup Tasks on Oracle SOA Suite.
To restrict access to Oracle E-Business Suite database from external nodes, various tasks should be performed based on your upgraded Oracle E-Business Suite database.
For an Oracle RAC database with grid listener:
Note: This step applies to an Oracle E-Business Suite database upgraded to 19c, 12c, or an earlier release.
Update sqlnet.ora in the Grid home of each database node, with the following properties:
tcp.validnode_checking = yes
tcp.invited_nodes=()
SQLNET.INBOUND_CONNECT_TIMEOUT = 60
Stop the database listener from the Grid home using the following command:
srvctl stop listener
Stop the scan listener from the Grid home using the following command:
srvctl stop scan_listener
Start the database listener from the Grid home using the following command:
srvctl start listener
Start the scan listener from the Grid home using the following command:
srvctl start scan_listener
For an Oracle RAC database with local listener, perform the following steps on each database node. In an upgraded environment that does not use Oracle RAC, follow the steps in a database node:
If you have upgraded Oracle E-Business Suite database to 19c, perform the following tasks:
Source the PDB database tier environment file.
source /_.env
Run AutoConfig on the database tier.
sh $ORACLE_HOME/appsutil/scripts//adautocfg.sh
Verify that $TNS_ADMIN/sqlnet.ora contains the following properties:
tcp.validnode_checking = yes
tcp.invited_nodes=()
SQLNET.INBOUND_CONNECT_TIMEOUT = 60
$TNS_ADMIN/sqlnet.ora will be updated only if the profile option FND_SQLNET_ACCESS is set to ALLOW_RESTRICTED.
Source the CDB database tier environment file.
source /_.env
Stop the database listener using the following command:
$ORACLE_HOME/appsutil/scripts//adcdblnctl.sh stop $ORACLE_SID
Start the database listener using the following command:
$ORACLE_HOME/appsutil/scripts//adcdblnctl.sh start $ORACLE_SID
If you have upgraded Oracle E-Business Suite database to 12c or an earlier release, perform the following tasks:
Source the database tier environment file.
source /_.env
Run AutoConfig on the database tier.
sh $ORACLE_HOME/appsutil/scripts//adautocfg.sh
Stop the database listener using the following command:
$ORACLE_HOME/appsutil/scripts//addlnctl.sh stop $ORACLE_SID
Verify that $TNS_ADMIN/sqlnet.ora contains the following properties:
tcp.validnode_checking = yes
tcp.invited_nodes=()
SQLNET.INBOUND_CONNECT_TIMEOUT = 60
$TNS_ADMIN/sqlnet.ora will be updated only if the profile option FND_SQLNET_ACCESS is set to ALLOW_RESTRICTED.
Start the database listener using the following command:
$ORACLE_HOME/appsutil/scripts//addlnctl.sh start $ORACLE_SID
Have the following files ready as you will copy them from Oracle E-Business Suite to Oracle SOA Suite:
$JAVA_TOP/oracle/apps/fnd/txk/util/txkISGConfigurator.xml
DBC file, generated in Step 2 above for each SOA node
Section 11.2: Setup Tasks on Oracle SOA Suite
Perform the following steps to set up tasks on Oracle SOA Suite:
Navigate to the folder created on the Oracle SOA Suite server. Replace the existing txkISGConfigurator.xml file located at /appsutil//bpel with the one from Oracle E-Business Suite ($JAVA_TOP/oracle/apps/fnd/txk/util/txkISGConfigurator.xml).
Note: For Oracle SOA Suite configured with multiple nodes, the folder will be created in the shared file system as described earlier in step 4 of Section 4.2.
Navigate to the folder created on the Oracle SOA Suite server for the DBC file.
Note: For Oracle SOA Suite configured with multiple nodes, the folder will be created in the non-shared file system on each Oracle SOA Suite node as described earlier in step 5 of Section 4.2.
Copy the DBC file created as part of the step 2 of Section 11.1: Setup Tasks on Oracle E-Business Suite from Oracle E-Business Suite ($JAVA_TOP/oracle/apps/fnd/bpel/util/_.dbc) to the folder on the Oracle SOA Suite server.
Copy the DBC file _.dbc to the folder.
Remove existing .dbc file.
Rename the DBC file _.dbc to .dbc.
Note: For Oracle SOA Suite configured with multiple nodes, the DBC file is generated for each Oracle SOA Suite node. There is one-to-one mapping of the DBC file to the Oracle SOA Suite node. The DBC file should be copied to the folder of the respective Oracle SOA Suite node. For example, the _.dbc file should be copied to the folder on Oracle SOA Suite node1. It should also be renamed to .dbc. The _.dbc file should be copied and renamed to .dbc on Oracle SOA Suite node2.
Restart the data source using the following steps:
Log in to Oracle WebLogic Server Administration Console of Oracle SOA Suite.
From Domain Structure on the left pane, navigate to ISG-SOA Domain > Services > DataSources. The Summary of JDBC Data Sources page appears.
Click data source.
For the data source, navigate to the Control tab.
Select all deployed instances of the data source and click Shutdown.
When prompted for confirmation, click Yes to continue.
Wait for the state to be "Shutdown" and the status of the last action to be "Success".
Select all deployed instances of the data source and click Start.
When prompted for confirmation, click Yes to continue.
The state should be "Running" and the status of the last action should be "Success" for all servers.
Section 11.3: Validating the Setup for Post-Database Upgrade
Perform the following validation tasks to ensure SOAP and REST services work as expected:
Verify and test the runtime invocation of all SOAP and REST services that were deployed before the database upgrade.
While testing the runtime invocation of the services that were deployed before the database upgrade, if you encounter an exception, for example:
Error while processing the execution of the XXX.XXX.XXX$XXX interaction. An error occurred while processing the interaction for invoking the XXX.XXX.XXX$XXX. Cause:
java.sql.SQLException: invalid name pattern: XXX.XXX
Check to ensure that the XML containing parameter data matches the parameter definitions in the XSD.
Then, perform the following tasks to redeploy your services:
Log in to Oracle E-Business Suite Home Page as a user who has the FND_IREP_ADMIN role.
Select the Integrated SOA Gateway responsibility from the navigation menu and then select the Integration Repository link. Click Search.
Locate a desired API by Name or Internal Name and click Go to run the search. Click the API name link from the search results table. The Interface Detail page for the selected interface appears.
For SOAP services, use the following steps to redeploy your SOAP service:
Click the "SOAP Web Service" tab. Record the deployed method names and authentication method that will be used later.
Click Reset to change the SOAP Service Status to 'Not Generated'.
Once 'Not Generated" is shown as the service status, you need to generate the SOAP service again by selecting the same method names that you recorded earlier and click Generate.
Once 'Generated" is shown as the service status, select the same Authentication Method that you recorded earlier and click Deploy. The SOAP Service Status should be 'Deployed | Active' once the generated SOAP service is successfully deployed.
Test the runtime invocation again.
For REST services, use the following steps to redeploy your REST service:
Click the "REST Web Service" tab. Record the Service Alias name and deployed method names that will be used later.
Click Undeploy to change the REST Service Status to 'Not Deployed'.
Once 'Not Deployed' is shown as the service status, you need to deploy it again by entering the same Service Alias name that you recorded earlier and selecting the same method names before clicking Deploy.
Once the REST service is successfully deployed with 'Deployed' status, you can test the runtime invocation again.
Verify design-time operations for REST and SOAP services.
For REST services validation, see Section 2: Validating Oracle E-Business Suite Integrated SOA Gateway Setup for REST Services.
For SOAP services validation, see Section 5: Validating Oracle E-Business Suite Integrated SOA Gateway Setup for SOAP Services.
Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data (Doc ID 243755.1)
Copyright (c) 2021, Oracle. All rights reserved. Oracle Confidential.
Click to add to Favorites Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data (Doc ID 243755.1) To BottomTo Bottom
In this Document
Purpose
Troubleshooting Steps
References
APPLIES TO:
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
***Checked for Relevance on 10th Sep 2018 ***
PURPOSE
To use the PL/SQL Profiler please refer to DBMS_PROFILER documentation as per Oracle® Database PL/SQL Packages and Types Reference for your specific release and platform.
Once you have executed the PL/SQL Profiler for a piece of your application, you can use script profiler.sql provided in this document. This profiler.sql script produces a nice HTML report with the top time consumers as per your execution of the PL/SQL Profiler.
TROUBLESHOOTING STEPS
Familiarize yourself with the PL/SQL Profiler documented in the "Oracle® Database PL/SQL Packages and Types Reference" under DBMS_PROFILER.
If needed, create the PL/SQL Profiler Tables under your application schema: @?/rdbms/admin/proftab.sql
If needed, install the DBMS_PROFILER API, connected as SYS: @?/rdbms/admin/profload.sql
Start PL/SQL Profiler in your application: EXEC DBMS_PROFILER.START_PROFILER('optional comment');
Execute your transaction to be profiled. Calls to PL/SQL Libraries are expected.
Stop PL/SQL Profiler: EXEC DBMS_PROFILER.STOP_PROFILER;
Connect as your application user, execute script profiler.sql provided in this document: @profiler.sql
Provide to profiler.sql the "runid" out of a displayed list.
Review HTML report generated by profiler.sql.
REFERENCES
NOTE:1460440.1 - Script PXHCDR.SQL: Parallel Execution Health-Checks and Diagnostics Reports
NOTE:215187.1 - All About the SQLT Diagnostic Tool
NOTE:224270.1 - TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces (NO LONGER SUPPORTED - Use SQLTXPLAIN sqltrcanlzr.sql)
NOTE:1366133.1 - SQL Tuning Health-Check Script (SQLHC)
Was this document helpful?
Yes
No
Document Details
Email link to this documentOpen document in new windowPrintable Page
Type:
Status:
Last Major Update:
Last Update:
Language:
TROUBLESHOOTING
PUBLISHED
Sep 10, 2018
Oct 21, 2019
English
Related Products
Oracle Database Cloud Schema Service
Oracle Database Exadata Express Cloud Service
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine)
Oracle Cloud Infrastructure - Database Service
Oracle Database Backup Service
Show More
Information Centers
Oracle Catalog: Service Request Data Collections (SRDCs) for all Products and Services [51.2]
Oracle Catalog: Information Centers for All Products and Services [50.2]
Information Center: OCI Cloud Guard [2765346.2]
Platform as a Service (PaaS) and Oracle Cloud Infrastructure (OCI) Information Center [2048297.2]
Index of Oracle Database Information Centers [1568043.2]
Show More
Document References
Script PXHCDR.SQL: Parallel Execution Health-Checks and Diagnostics Reports [1460440.1]
All About the SQLT Diagnostic Tool [215187.1]
TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces (NO LONGER SUPPORTED - Use SQLTXPLAIN sqltrcanlzr.sql) [224270.1]
SQL Tuning Health-Check Script (SQLHC) [1366133.1]
Recently Viewed
SQL Tuning Health-Check Script (SQLHC) [1366133.1]
How to Determine the SQL_ID for a SQL Statement [1627387.1]
All About the SQLT Diagnostic Tool [215187.1]
SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference [199081.1]
* How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues [376442.1]
Show More
Didn't find what you are looking for?Ask in Community...
Attachments
FILEprofiler.sql script(23.13 KB)
HTMLoutput sample(84.27 KB)
Performance Tuning Basics 15 : AWR Report Analysis
https://expertoracle.com/2018/02/06/performance-tuning-basics-15-awr-report-analysis/
Performance Tuning Basics 15 : AWR Report Analysis
PUBLISHED FEBRUARY 6, 2018 by BRIJESH GOGIA
The Oracle’s Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. The report generated by AWR is a big report and it can take years of experience to actually understand all aspects of this report. In this post we will try to explain some important sections of AWR, significance of those sections and also some important tips. Please note that explaining all sections of AWR will not be possible so we will stick to some of the most frequently used sections.
Note that this is not comprehensive information and goal is to help in giving an overview of few key sections to Junior DBAs as a primer and to encourage them to build further the knowledge in related fields.
To start with let us mention some high level important tips regarding AWR:
1. Collect Multiple AWR Reports: It’s beneficial to have two AWR Reports, one for the good time and other when performance is poor or you can create three reports (Before/Meantime/After reports) during the time frame problem was experienced and compare it with the time frame before and after.
2. Stick to Particular Time: You must have a specific time when Database was slow so that you can choose a shorter timeframe to get a more precise report.
3. Split Large AWR Report into Smaller Reports: Instead of having one report for long time like one report for 3 hrs. it is better to have three reports each for one hour. This will help to isolate the problem
4. FOR RAC, take each instance’s individual report: For RAC environment, you need to do it separately of all the instances in the RAC to see if all the instances are balanced the way they should be.
5. Use ASH also : Use AWR to identify the troublesome areas and then use ASH to confirm those areas.
6. Increase the retention period : Some instances where you get more performance issues you should increase the retention time so that you can have historical data to compare.
TIME UNITS USED IN VARIOUS SECTIONS OF AWR REPORTS
-> s – second
-> cs – centisecond – 100th of a second
-> ms – millisecond – 1000th of a second
-> us – microsecond – 1000000th of a second
Top Header
SIGNIFICANCE OF THIS SECTION:
This contains information about the Database and environment. Along with the snapshot Ids and times. Important thing to notice is that the configuration like CPU and Memory has not changed when the performance is degraded.
PARAMETER DESCRIPTION ANALYSIS
DB TIME Time spent in database during the Elapsed Time
OR
Sum of the time taken by all sessions in the database during the ‘Elapsed’ time.DB Time= CPU Time + Non IDLE wait time.
Note: it does not include background processes DB TIME > Elapsed Time will mean that the sessions were active on database concurrently
You cam find the average active sessions during AWR Time:
DB TIME/ELAPSED => 1964.97/899.99 = 2.18
So database load (average active sessions) = 2.18
(SAME IDEA AS CPU LOAD on UNIX)
It means that
May be ~2 users were active on database for ‘Elapsed’ Time.
or
May be 4 users were active for Elapsed Time/2 each
or
May be 8 users were active for Elapsed Time/4 each
or
May be 64 users were active for Elapsed Time/32 each
If DB Time has higher value means DB Activity/Sessions were High during the AWR Time.
AWR REPORT THAT WE WILL REVIEW BELOW IS BASED ON THIS DB TIME.
This means that for every minute of Elapsed time there is 2.2 minutes of work in done in the database
ELAPSED TIME The time duration in which this AWR report has been generated.
Elapsed time should contain the issue duration.
Take manual snapshots if required
CPUs Thread count per core. It is not “actual” CPU.
STARTUP TIME Database startup time
RAC If you have more than one node then take AWR from all nodes if you don’t know issues are happening in which node.
Load Profile
SIGNIFICANCE OF THIS SECTION:
Here in load profile (average active sessions, DB CPU, logical and physical reads, user calls, executions, parses, hard parses, logons, rollbacks, transactions) —
check if the numbers are consistent with each other and with general database profile (OLTP/DWH/mixed)
Pay most attention to physical reads, physical writes, hard parse to parse ratio and executes to transaction ratio.
The ratio of hard parses to parses tells you how often SQL is being fully parsed. Full parsing of SQL statements has a negative effect on performance.
High hard parse ratios (>2 – 3 percent) indicate probable bind variable issues or maybe versioning problems.
Rows per sort can also be reviewed here to see if large sorts are occurring.
This section can help in the load testing for application releases. You can compare this section for the baseline as well as high load situation.
PARAMETER DESCRIPTION ANALYSIS
Redo Size (Bytes) The main sources of redo are (in roughly descending order): INSERT, UPDATE and DELETE. For INSERTs and UPDATE s Not very scary number in our report
High redo figures mean that either lots of new data is being saved into the database, or existing data is undergoing lots of changes.
What do you do if you find that redo generation is too high (and there is no business reason for that)? Not much really — since there is no “SQL ordered by redo” in the AWR report.
Just keep an eye open for any suspicious DML activity. Any unusual statements? Or usual statements processed more usual than often? Or produce more rows per execution than usual? Also, be sure to take a good look in the segments statistics section (segments by physical writes, segments by DB block changes etc.) to see if there are any clues there.
DB CPU Its the amount of CPU time spent on user calls. Same as DB time it does not include background process. The value is in microseconds We have 8 CORES and so we can potentially use 8 seconds of CPU time per second. In this case DB CPU (s) : 1.9 (per second) is reporting that the system
is using 1.9 seconds of CPU of the potential 8 seconds/second that it can use.We are not CPU Bound
LOGICAL READS Consistent gets + db block gets = Logical Reads
As a process, Oracle will try to see if the data is available in Buffer cache i.e. SGA?
If it does, then logical read increases to 1.
To explain a bit further, if Oracle gets the data in a block which is consistent with a given point in time, then a counter name “Consistent Gets” increases to 1.
But if the data is found in current mode, that is, the most up-to-date copy of the data in that block, as it is right now or currently then it increases a different counter name “db block Gets”.
Therefore, a Logical read is calculated as = Total number of “Consistent Gets” + Total number of “db block gets”.
These two specific values can be observed in ‘Instance Activity Stats’ section.
Logical and physical reads combined shows measure of how many IO’s (Physical and logical) that the database is performing..
If this is high go to section “SQL by logical reads”. That may help in pointing which SQL is having more logical reads.
USER QUERIES Number of user queries generated
PARSES The total of all parses, hard and soft
HARD PARSES The parses requiring a completely new parse of the SQL statement. These consume both latches and shared pool area. How much hard parsing is acceptable?
It depends on too many things, like number of CPUs, number of executions, how sensitive are plans to SQL parameters etc. But as a rule of a thumb, anything below 1 hard parse per second is probably okay, and everything above 100 per second suggests a problem (if the database has a large number of CPUs, say, above 100, those numbers should be scaled up accordingly). It also helps to look at the number of hard parses as % of executions (especially if you’re in the grey zone).
If you suspect that excessive parsing is hurting your database’s performance:
1) check “time model statistics” section (hard parse elapsed time, parse time elapsed etc.)
2) see if there are any signs of library cache contention in the top-5 events
3) see if CPU is an issue.
Soft Parses: Soft parses are not listed but derived by subtracting the hard parses from parses. A soft parse reuses a previous hard parse; hence it consumes far fewer resources.
Physical Reads But if it Oracle does not find the data in buffer cache, then it reads it from physical block and increases then Physical read count to 1. Clearly, buffer get is less expensive than physical read because database has to work harder (and more) to get the data. Basically time it would have taken if available in buffer cache + time actually taken to find out from physical block. If this is high go to section “SQL by Physical reads”. That may help in pointing which SQL is having more Physical reads.
Executes (SQL) If executes per second looks enormous then its a red flag.
Example: This numbers, combined with high CPU usage, are enough to suspect there MAY BE context switching as the primary suspect: a SQL statement containing a PL/SQL function, which executes a SQL statement hundreds of thousands of time per function call.
if it is high then it also suggests that most of the database load falls on SQL statements in PL/SQL routines.
User Calls number of calls from a user process into the database – things like “parse”, “fetch”, “execute”, “close” This is an extremely useful piece of information, because it sets the scale for other statistics (such as commits, hard parses etc.).
In particular, when the database is executing many times per a user call, this could be an indication of excessive context switching (e.g. a PL/SQL function in a SQL statement called too often because of a bad plan). In such cases looking into “SQL ordered by executions” will be the logical next step.
Logons logons – really means what it means. Number of logons Establishing a new database connection is also expensive (and even more expensive in case of audit or triggers). “Logon storms” are known to create very serious performance problems. If you suspect that high number of logons is degrading your performance, check “connection management elapsed time” in “Time model statistics”.
Sorts
Sort operations consume resources. Also, expensive sorts may cause your SQL fail because of running out of TEMP space. So obviously, the less you sort, the better (and when you do, you should sort in memory). However, I personally rarely find sort statistics particularly useful: normally, if expensive sorts are hurting your SQL’s performance, you’ll notice it elsewhere first.
DB Time average number of active sessions is simply DB time per second.
Block Changes Number of blocks modified during the sample interval
Instance Efficiency Percentage
SIGNIFICANCE OF THIS SECTION:
Rule of thumb: Always minimize the number of Hard parses. This reduction yields the benefits of minimizing CPU overhead spent performing costly parse work.
Every ratio here should reach 100%
PARAMETER DESCRIPTION ANALYSIS
In memory sort % Shows %of times Sorting operations happened in memory than in the disk (temporary tablespace). In Memory Sort being low (in the high 90s or lower) indicates PGA_AGGREGATE_TARGET or SORT_AREA_SIZE issues
soft parse % Shows % of times the SQL in shared pool is used. Shows how often sessions issued a SQL statement that is already in the shared pool and how it can use an existing version of that statement. Soft Parsing being low indicates bind variable and versioning issues. With 99.25 % for the soft parse meaning that about 0.75 % (100 – soft parse) is happening for hard parsing. Low hard parse is good for us.
% Non-Parse CPU Oracle utilizes the CPU mostly for statement execution but not for parsing. If this value is near 100% means most of the CPU resources are used into operations other than parsing, which is good for database health.
Most of our statements were already parsed so we weren’t doing a lot of re parsing. Re parsing is high on CPU and should be avoided.
Execute to Parse % Shows how often parsed SQL statements are reused without re-parsing. The way this ratio is computed, it will be a number near 100 percent when the application executes a given SQL statement many times over but has parsed it only once.
If the number of parse calls is near the number of execute calls, this ratio trends towards zero.
If the number of executes increase while parse calls remain the same this ratio trends up.
When this number is low, parsing is consuming CPU and shared pool latching.
Parse CPU to Parse Elapsed % Gives the ratio of CPU time spent to parse SQL statements. If the value are low then it means that there could be a parsing problem. You may need to look at bind variable issues or shared pool sizing issue. If low it also means some bottleneck is there related to parsing. We would start by reviewing library cache contention and contention in shared pool latches. You may need to increase the shared pool.
Buffer Hit % Measures how many times a required block was found in memory rather than having to execute an expensive read operation on disk to get the block.
Buffer Nowait% Indicates % of times data buffers were accessed directly without any wait time. This ratio relates to requests that a server process makes for a specific buffer. This is the percentage of those requests in which the requested buffer is immediately available. All buffer types are included in this statistic. If the ratio is low, check the Buffer Wait Statistics section of the report for more detail on which type of block is being contended. Most likely, additional RAM will be required.
Library Hit% Shows % of times SQL and PL/SQL found in shared pool. Library hit % is great when it is near 100%. If this was under 95% we would investigate the size of the shared pool.
In this ration is low then we may need to:
• Increase the SHARED_POOL_SIZE init parameter.
• CURSOR_SHARING may need to be set to FORCE.
• SHARED_POOL_RESERVED_SIZE may be too small.
• Inefficient sharing of SQL, PLSQL or JAVA code.
• Insufficient use of bind variables
Latch Hit % Shows % of time latches are acquired without having to wait. If Latch Hit % is <99%, you may have a latch problem. Tune latches to reduce cache contention
Redo NOWait% Shows whether the redo log buffer has sufficient size.
Top 10 Foreground Wait Events by Total Wait Time
SIGNIFICANCE OF THIS SECTION:
This section is critical because it shows those database events that might constitute the bottleneck for the system.
Here, first of all check for wait class if wait class is User I/O , System I/O, Others etc this could be fine but if wait class has value “Concurrency” then there could be some serious problem. Next to look at is Total Wait Time (sec) which show how many times DB was waiting in this class and then Wait Avg (ms). If Total Wait Time(sec) are high but Wait Avg(ms) is low then you can ignore this. If both are high or Wait Avg(ms) is high then this has to further investigate.
Note that there could be significant waits that are not listed here, so check the Foreground Wait Events (Wait Event Statistics) section for any other time consuming wait events.
For the largest waits look at the Wait Event Histogram to identify the distribution of waits.
PARAMETER DESCRIPTION ANALYSIS
DB CPU Time running in CPU (waiting in run-queue not included)
Here 84.8% is the %DB Time for this Event which is really HIGH!
DB Time was 1965 minutes and DB CPU is (100000/60= 1667 minutes)
1667/1965 is 84.8% which is shown in above table.
We can find
1) DB CPU LOAD
=Total wait time /AWR TIME
=100,000/(900*60)
=1.85
2) DB CPU UTLIZATION %
=DB CPU LOAD/Number of Cores
=
=(1.85/8) X 100
= 23% of Host cores
IMPORTANT: Your server may have other database instances sharing the CPU resources so take into account those too.
Also this do not mean that server CPU is 84% Utilized!
Sum of %DB Time The sum should be approx 100%. If it is way below 100% then it may mean that wait events were irrelevant OR Server is overloaded.
enq TX – row lock contention waited for locked rows This parameter value currently is only 0.2% of total DB time so we don’t have to much worry about it.
Say that it was higher value, 10% then we will have to look into root cause.
You will have to go to “Segments by Row Lock Waits” and see what tables are getting locked and then you will have to see in which SQL_ID these are used.
DB FILE SEQUENTIAL READ single block i/o
Sequential read is an index read followed by table read because it is doing index lookups which tells exactly which block to go to
Average I/O call is 2ms which is not very high. If you have say very high wait average example 100ms or 200ms, it means that your disks are slow
Are your SQLs returning too many rows, is the I/O response pretty bad on the server, is DB not sized to cache enough result sets
You need to see then the “File IO Stats” section in the AWR report.
The event indicates that index scan is happening while reading data from table. High no. of such event may be a cause of unselective indexes i.e. oracle optimizer is not selecting proper indexes from set of available indexes. This will result in extra IO activity and will contribute to delay in SQL execution. Generally high no. is possible for properly tuned application having high transaction activity.
•If Index Range scans are involved, more blocks than necessary could be being visited if the index is un-selective.By forcing or enabling the use of a more selective index, we can access the same table data by visiting fewer index blocks (and doing fewer physical I/Os).
•If indexes are fragmented, we have to visit more blocks because there is less index data per block. In this case, re-building the index will compact its contents into fewer blocks.
• If the index being used has a large Clustering Factor, then more table data blocks have to be visited in order to get the rows in each index block. By rebuilding the table with its rows sorted by the particular index columns we can reduce the Clustering Factor and hence the number of table data blocks that we have to visit for each index block.
LOG FILE SYNC Here Wait AVG (MS) is 6 which is not a cry number.
Above 20ms we don’t consider good numberAlso go to “Instance Activity Stats” section and see how many commits actually happened and then see here that what % of COMMITS have to wait.Remember that short transactions, frequent commits is property of OLTP Application.
DB FILE SCATTERED READ caused due to full table scans may be because of insufficient indexes or un-avilablity of updated statistics To avoid this event, identify all the tables on which FTS is happening and create proper indexes so that oracle will do Index scans instead of FTS. The index scan will help in reducing no. of IO operations.
To get an idea about tables on which FTS is happening please refer to “Segment Statistics” -> “Segments By Physical Read” section of AWR report. This section lists down both Tables and Indexes on which Physical Reads are happening. Please note that physical reads doesn’t necessarily means FTS but a possibility of FTS.
Concurrency, wait class Concurrency wait class is not good and if high then need to be analyzed.
direct path read temp or direct path write temp this wait event shows Temp file activity (sort,hashes,temp tables, bitmap)
check pga parameter or sort area or hash area parameters. You might want to increase them
Wait Class, column helps in classifying whether the issue is related to application or infrastructure. Wait events are broadly classified in to different WAIT CLASSES:
Administrative
Application
Concurrency
User IO
System IO
Cluster
Commit
Configuration
Idle
Network
Buffer Busy Wait Indicates that particular block is being used by more than one processes at the same. When first process is reading the block the other processes goes in a wait as the block is in unshared more. Typical scenario for this event to occur is, when we have batch process which is continuously polling database by executing particular SQL repeatedly and there are more than one parallel instances running for the process. All the instances of the process will try to access same memory blocks as the SQL they are executing is the same. This is one of the situation in which we experience this event.
enq: TX – row lock contention: Oracle maintenance data consistency with the help of locking mechanism. When a particular row is being modified by the process, either through Update/ Delete or Insert operation, oracle tries to acquire lock on that row. Only when the process has acquired lock the process can modify the row otherwise the process waits for the lock. This wait situation triggers this event. The lock is released whenever a COMMIT is issued by the process which has acquired lock for the row. Once the lock is released, processes waiting on this event can acquire lock on the row and perform DML operation.
enq: UL – contention: This enq wait occurs when application explicitly locks by executing the lock table command.
enq: TM – contention This usually happens due to a missing foreign key constraint on a table that’s part of a DML operation.
Host CPU
SIGNIFICANCE OF THIS SECTION:
A high level of DB CPU usage in the Top N Foreground Events (or Instance CPU: %Busy CPU) does not necessarily mean that CPU is a bottleneck. In this example also we have DB CPU as the highest consuming category in the “Top 10 Foreground Events”
Look at the Host CPU and Instance CPU sections. The key things to look for are the values “%Idle” in the “Host CPU” section and “%Total CPU” in the “Instance CPU” section.
If the “%Idle” is low and “%Total CPU” is high then the instance could have a bottleneck in CPU (be CPU constrained). Otherwise, the high DB CPU usage just means that the database is spending a lot of time in CPU (processing) compared to I/O and other events. In either case (CPU is a bottleneck or not) there could be individual expensive SQLs with high CPU time, which could indicate suboptimal
execution plans, especially if accompanied with high (buffer) gets.
If you see in our case %idle is high 74% AND %Total CPU is just 7.45 so CPU is not a bottle neck in this example.
PARAMETER DESCRIPTION ANALYSIS
CPUs are actually threads.
Here we have 8 Cores and 8 Threads per Core so
CPU = number of core X number of threads per core
= 8 X 8
= 64
Load Average Compare Load average with Cores.
Very Ideal thing is that Load Average should be less than Cores although this may not be happening (any it may not be issue also!)
%Idle Can be misleading as sometimes your %Idle can be 50% but your server is starving for CPU.
50% means that all your cores are BUSY. You may have free threads (CPU) but you can not run two processes CONCURRENTLY on same CORE.
All % in this reports are calculated based on CPU ( which are actually threads)
Cores Here we have 8 core system
So we have 8 cores, meaning in a 60 min hour we have 60 X 8 = 480 CPU minsand total AWR duration is 8 hoursso 480X8 = 3840 CPU minutes in total
Instance CPU
SIGNIFICANCE OF THIS SECTION:
A high level of DB CPU usage in the Top N Foreground Events (or Instance CPU: %Busy CPU) does not necessarily mean that CPU is a bottleneck. In this example also we have DB CPU as the highest consuming category in the “Top 10 Foreground Events”
Look at the Host CPU and Instance CPU sections. The key things to look for are the values “%Idle” in the “Host CPU” section and “%Total CPU” in the “Instance CPU” section.
If the “%Idle” is low and “%Total CPU” is high then the instance could have a bottleneck in CPU (be CPU constrained). Otherwise, the high DB CPU usage just means that the database is spending a lot of time in CPU (processing) compared to I/O and other events. In either case (CPU is a bottleneck or not) there could be individual expensive SQLs with high CPU time, which could indicate suboptimal
execution plans, especially if accompanied with high (buffer) gets.
If you see in our case %idle is high 74% AND %Total CPU is just 7.45 so CPU is not a bottle neck in this example.
Cache Sizes
SIGNIFICANCE OF THIS SECTION:
From Oracle 10g onwards, database server does Automatic Memory Management for PGA and SGA components. Based on load, database server keeps on allocating or deallocating memory assigned to different components of SGA and PGA. Due to this reason, we can observe different sizes for Buffer Cache and Shared Pool, at the beginning or end of AWR snapshot period.
Shared Pool Statistics
SIGNIFICANCE OF THIS SECTION:
PARAMETER DESCRIPTION ANALYSIS
Memory Usage% shared pool usage
If your usage is low (<85 percent) then your shared pool is over sized. if Memory Usage % is too large like 90 % it could mean that your shared pool is tool small
% SQL with executions >1 Shows % of SQLs executed more than 1 time. The % should be very near to value 100. If your reuse is low (<60 – 70 percent) you may have bind variable or versioning issues. Ideally all the percentages in this area of the report should be as high (close to 100) as possible.
memory for SQL w/exec>1 From the memory space allocated to cursors, shows which % has been used by cursors more than 1.
Time Model Statistics
SIGNIFICANCE OF THIS SECTION:
Important statistics here is the DB Time. The statistic represents total time spent in database calls. It is calculated by aggregating the CPU time and wait time of all sessions not waiting on idle event (non-idle user sessions). Since this timing is cumulative time for all non-idle sessions, it is possible that the time will exceed the actual wall clock time.
PARAMETER DESCRIPTION ANALYSIS
SQL EXECUTE ELAPSED TIME Time spent executing the SQL Statement
Out of all the DB Time which is 117,898.47 seconds , 92.32% of time, 108,843.51 seconds, database is executing the SQL query so our attention will be to find out what all SQLs took so much of DB Time
DB CPU DB CPU represents time spent on CPU resource by foreground user processes. This time doesn’t include waiting time for CPU. DB time and DB CPU define two important timescales:
wait times should be measured against the DB TIME,
while CPU consumption during certain activity (e.g. CPU time parsing) should be measured against DB CPU.
Above % showing for DB CPU may not be the only % to focus on. You should find below number and then see what is DB CPU usage.
DB CPU usage (% of CPU power available) =
CPU time / NUM_CPUS / elapsed time
Where NUM_CPUS is found in the Operating System statistics section.
Of course, if there are other major CPU users in the system, the formula must be adjusted accordingly. To check that, look at OS CPU usage statistics either directly in the OS.
Parse Time Elapsed “Parse time elapsed” represents time spent for Syntax and Semantic checks.
Hard parse elapsed time “Hard parse include time” represents time spent for Syntax and Semantic checks PLUS time spent for optimizing the SQL and generating optimizer plan.
% DB Time In the time model statistics hierarchy, a child statistic may be counted under more than one parent and that is why the sum of the percentages equal more than 100
soft parse can be get by subtracting parse time from hard parse
Foreground Wait Class
SIGNIFICANCE OF THIS SECTION:
This is of less use. A wait could have multiple possible causes (in different classes) depending on the context. There are normally only a handful of time consuming waits, which can be analyzed and investigated separately.
There are over 800 distinct wait events. Oracle has grouped these wait events in 12 wait classes. These wait classes are further divided in 2 categories, Administrative Wait Class and Application Wait Class.
These wait classes gives overall information about whether the waits happening for Application or for System events.
PARAMETER DESCRIPTION ANALYSIS
User I/O
High User IO means,
From the pool of available indexes proper indexes are not being used
OR
FTS is happening on big tables with millions of rows
Foreground Wait Events
SIGNIFICANCE OF THIS SECTION:
Mostly The idle events are listed down in the end which should not be focused much.
This is useful because there could be time consuming report wait events that do not appear in the “Top N Timed Foreground Events”.
For the larger waits look at the Wait Event Histogram to identify the distribution of waits. Are they closely clustered around an average value or are there a wide variance of values ? Are there a large number of smaller waits or a few larger waits ?
PARAMETER DESCRIPTION ANALYSIS
SQL*Net Message from client Idle wait event
We can find the number of average inactive sessions by this wait event
Number of inactive sessions
= Total Wait Time/ (AWR Time * 60)
= 12679570/ (900 * 60)
= 235 average inactive sessions
This doesn’t mean user sessions as such but the number of such connections from Application Server connection pool.
Direct path read/write to temp Shows excessive sorting/hashing/global temp table/bitmap activity going to your temporary tablespace. Review PGA_AGGREGATE_TARGET settings. Even if it looks like it is big enough, if you aregetting multiple small sorts to disk it could mean your user load is over-utilizing it.
SQL*Net Message to client SQL*Net message to client waits almost always indicates network contention.
SQL*Net more data from client If it is very low then it indicates that the Oracle Net session data unit size is likely set correctly.
Db file sequential reads Usually indicates memory starvation, look at the db cache analysis and for buffer busy waits along with cache latch issues.
Db file scattered reads Usually indicates excessive full table scans, look at the AWR segment statistics for tables that are fully scanned
Log file Sync Log file related waits: Look at excessive log switches, excessive commits or slow IO subsystems.
Wait Event Histogram
SIGNIFICANCE OF THIS SECTION:
This can be used to determine the distribution of wait times. These days less than 5ms is expected and more than 10ms is considered poor. An analysis of the histogram can indicate if a high average time is due to a few individual long waits.
PARAMETER DESCRIPTION ANALYSIS
DB FILE SEQUENTIAL READ
This parameter will have mostly higher number of wait events in the histogram.
Now if you see approx 50% wait events have less than 1 ms of wait and another 30% has less than 2 ms. It means that our disks are working good. Wait is low for most of the sessions going to database.
We simply don’t want that high% (and high wait events numbers) are above 8ms of wait.
Now if you see that the DB FILE SEQUENTIAL READ is the key wait event then next thing will be to find
a) which segment is the bottleneck (go to “Segments by Physical Reads” section
b) which sql query has that segment used.
SQL ordered by Elapsed Time
SIGNIFICANCE OF THIS SECTION:
This can be used to identify the long running SQLs that could be responsible for a performance issue. It can give useful information about the CPU time, the number of executions
and the (SQL) Module. The Top SQLs can be matched to long running or slow Processes in the application.
In this report, look for query has low executions and high Elapsed time per Exec (s) and this query could be a candidate for troubleshooting or optimizations. In above report, you can see first query has maximum Elapsed time but only 2 execution. So you have to investigate this.
NOTE 1: The Elapsed time can indicate if a SQL is multithreaded (either Parallel DML/SQL or multiple workers). In this case the elapsed time will be multiple times the AWR duration (or the
observed clock time of the process/SQL). The elapsed time for multithreaded SQL will be the total of elapsed time for all workers or parallel slaves.
NOTE 2: The “SQL Ordered” sections can often contain the PL/SQL call that contains SQLs. So in this case the procedure WF_ENGINE (via procedures) ultimately calls the SQL b6mcn03jvfg41. Also if you see the first line here that is also a package BEGIN XXINV7566…. and inside this package it is running the SQL query running in the line 2 which is insert into XXINV7566_IQR…..
PARAMETER DESCRIPTION ANALYSIS
Elapse per Exec (s) Elapse time in seconds for per execution of the SQL.
Captured SQL Account for 79.1% of
total DB Time Shows that how many % of SQL this AWR report was able to capture and show us Remember that AWR reports shows those SQL which were in shared pool at the end of the AWR Time.
This number should be high value which will mean that we were able to capture all those SQLs which consumed the DB Time.
If this is low number than try to generate AWR for lower snap duration so that we are able to capture the required SQLs which are consuming DB Time
Executions Total no. of executions for the SQL during the two snapshot period.
An important point, if executions is 0 also sometimes, it doesn’t means query is not executing, this might be the case when query was still executing and you took AWR report. That’s why query completion was not covered in Report.
SQL Module Provides module detail which is executing the SQL. Process name at the OS level is displayed as SQL Module name.
If the module name starts with any of the names given below, then don’t consider these SQLs for tuning purpose as these SQLs are oracle internal SQLs,
DBMS,
sqlplusw,
TOAD,
rman,
SQL,
Enterprise Manager,
ORACLE,
MMON_SLAVE,
emagent etc…
In the list XXIN1768 has two SQLIDs. The SQL id #1 is PL/SQL code as a wrapper and it took around 53k seconds. The sql #2 took 51k seconds and seems to be called in sql ID# 1, as their module names are same. Since the SQL#2 insert statement took almost all of the time so we sill focus on this query for tuning.
Elasped Time The Elapsed Time is the sum of all individual execution time for the sql_id. So if multiple sessions execute the same SQLs, the elapsed time can be greater than the period of two snap_ids.
SQL ordered by CPU Time
SIGNIFICANCE OF THIS SECTION:
The most useful sections are SQL ordered by Elapsed Time, CPU Time, Gets and Reads. All the sections can be useful in identifying if a particular SQL from a particular module was
running during the AWR report period
However, In most cases this section does not reveal much more information than the “SQL Ordered by Elapsed Time” section. However, it does sort by CPU and can output SQLs that are not
in the previous section.
PARAMETER DESCRIPTION ANALYSIS
The top record in this table
The first and second report are part of same transaction.
Second SQL is the inside part of first PLSQL.It is accounting huge % of the DB CPU and remember that DB CPU was the top event in our AWR.
SQL ordered by Gets
SIGNIFICANCE OF THIS SECTION:
The most useful sections are SQL ordered by Elapsed Time, CPU Time, Gets and Reads. All the sections can be useful in identifying if a particular SQL from a particular module was
running during the AWR report period
This is the logical Reads from Buffer Cache
When “CPU Other” is a significant component of total Response Time, then it is likely that the time is being spent retrieving and manipulating Blocks and Buffers (Block accesses are also known as Buffer Gets and Logical I/Os). Then as a next step we will come to this section to find the SQL statements that access the most blocks because these are likely to be responsible for the majority of this time.
This section will tell you HOW MANY BLOCKS WERE READ and “SQL Ordered by Executions” section will tell you how many rows were fetched. Many a time this and that section will have same SQL_ID.
Now if more BLOCKS are read here and that section tells that number of rows fetched are real low means something is not right with the query. Why it has to read so many blocks to get less rows.May be bad execution plan.
A high number of buffer gets is one of the main indicators of SQLs with suboptimal execution plans Example if you see in this example, insert statement in row number 4 (which is related to PLSQL in row number 3) is doing very high number of buffer gets for single execution..
BUT Bear in mind that the SQL could have a good execution plan and just be doing a lot of work. So we need to bring into account the data volume (and parameters that are getting passed to the query).
You can easily see execution plans by running @awrsqrpt.sql and passing offending SQL_ID as parameter.
You can refer to the “SQL Ordered by Executions” after reading this section.
PARAMETER DESCRIPTION ANALYSIS
Gets per Exec HOW MANY BLOCKS WERE READ insert statement in XXINV1738 module has Gets per Exec that is too high, you need to analyze the SQL with some additional output such as sqlt and sqlhc.
SQL ordered by Reads
SIGNIFICANCE OF THIS SECTION:
This section reports the contents of the SQL area ordered by the number of reads from the data files and can be used to identify SQL causing IO bottlenecks which consume the following resources.
• CPU time needed to fetch unnecessary data.
• File IO resources to fetch unnecessary data.
• Buffer resources to hold unnecessary data.
• Additional CPU time to process the query once the data is retrieved into the buffer.
• % Total can be used to evaluate the impact of each statement.
If we talk about wait time then “Wait Time” is the sum of time waited for non-idle Wait Events. These include I/O waits for reading blocks from disk as measured by the Wait Events ‘db file sequential read’ for single-block reads and ‘db file scattered read’ for multi-block reads.
When such Wait Events are found to be significant components of Response Time, the next step is to find the SQL statements that read the most blocks from disk. We will refer to this section then.
This is the Physical Reads from Disk
If the physical I/O waits (e.g. db file sequential read, db file scattered read, direct path read) are relatively high then this section can indicate which SQLs are responsible.
This section can help identify the SQLs that are responsible for high physical I/O and may indicate suboptimal execution plans, particularly if the execution plan contains full table scans or large index range scans (where more selective index scans are preferable).
PARAMETER DESCRIPTION ANALYSIS
Captured SQL account for 76% of total
Our goal is to have this % as high as possible. Probably breaking down this AWR into smaller interval will increase this %.
The top record in this table We have seen in the “Segments by Physical Reads” section that
MTL_ITEM_CATEGORIES account for 51.32% and looking here the SQL_ID which is a top and having 40.18% TOTAL is using this table.Yousee here that although it has executed 73 times but reads per executions is high making it top query consuming physical i/o.In contrast query at number 4 in this table has been executed around 40k times but since reads per execution is low so number 4th query is not the top query to worry about.
Reads per Exec Possible reasons for high Reads per Exec are use of unselective indexes require large numbers of blocks to be fetched where such blocks are not cached well in the buffer cache, index fragmentation, large Clustering Factor in index etc.
SQL ordered by Physical Reads (UnOptimized)
SIGNIFICANCE OF THIS SECTION:
This section is of concern when you have exadata machine in use
Read requests that are satisfied from the Smart Flash Cache in Exadata are termed ‘optimized’ since they are returned much faster than requests from disk (the implemention uses solid state device (SSD) technology). Additionally, read requests accessing Storage Indexes using smart scans in Oracle Exadata V2 (and significantly reducing I/O operations) also fall under the category ‘optimized read requests’ since they avoid reading blocks that do not contain relevant data.
IMPORTANT: In database systems where ‘Optimized Read Requests’ are not present (which are not using EXA) , UnOptimized Read Reqs will be equal to Physical Read Reqs (I/O requests satisfied from disk). In this case columns ‘UnOptimized Read Reqs’ and ‘Physical Read Reqs’ will display the same values and column ‘%Opt’ will display zero (as seen in extract from AWR report above).
PARAMETER DESCRIPTION ANALYSIS
Physica Read Reqs Note that the ‘Physical Read Reqs’ column in the ‘SQL ordered by Physical Reads (UnOptimized)’ section is the number of I/O requests and not the number of blocks returned. Be careful not to confuse these with the Physical Reads statistics from the AWR section ‘SQL ordered by Reads’, which counts database blocks read from the disk not actual I/Os (a single I/O operation may return many blocks from disk).
SQL ordered by Parse Calls
SIGNIFICANCE OF THIS SECTION:
This section shows the number of times a statement was parsed as compared to the number of times it was executed. One to one parse/executions may indicate that:
• Bind variables are not being used.
• On RDBMS version 8172 and higher the init.ora parameter session_cached_cursors was not set in the init.ora (100 is usually the suggested starting value).
• The shared pool may be too small and the parse is not being retained long enough for multiple executions.
• init.ora cursor_sharing should be set to FORCE
When “CPU Parse” is a significant component of total Response Time the next step is to find the SQL statements that have the most parses.
AWR and Statspack list such SQL statements in sections such as “SQL ordered by Parse Calls”.
Tablespace IO Stats
SIGNIFICANCE OF THIS SECTION:
These are useful to see what your hot tablespaces are. For example, having the SYSTEM tablespace as the number one source of IO could indicate you have improper temporary tablespace assignments as these used to default to SYSTEM. Having the TEMP or UNDO tablespaces in the top position has already been discussed. Usually in an OLTP system one of your index tablespaces should be at the top. In a DWH or OLAP a data tablespace should be at the top. Also look at the latency values. For disk based systems 5.0 ms is considered good performance.
PARAMETER DESCRIPTION ANALYSIS
Av Rd(ms)
Av Rd(ms) on the tablespace IO stats should be controlled under 10, which is ideal. But Avg read (ms) of up to 20 is acceptable for IO performance.
NOTE: When the figure in Reads column is too low, you can ignore the Av Rd(ms).
Av Buf Wt(ms Av Buf Wt(ms) on the tablespace IO stats should be controlled under 10, which is ideal.
Buffer Pool Advisory
SIGNIFICANCE OF THIS SECTION:
The buffer pool advisory report answers the question, how big should you make your database buffer cache. It provides an extrapolation of the benefit or detriment that would result if you added or removed memory from the database buffer cache. These estimates are based on the current size of the buffer cache and the number of logical and physical IO’s encountered during the reporting point. This report can be very helpful in “rightsizing” your buffer cache.
See what the effect of doubling the cache size would be. If it is significant (>20 percent reduction in physical IO) you can suggest increasing the pool.
Example here if you see when size factor goes from 1 to 1.96 , the estimated physical read factor is going down from 1 to 0.26 which is significant drop and is good for database.
The two ‘Size Factor’ and ‘Estimated Phys Read Factor’ columns show how quickly or slowly number of ‘Estimated Physical Reads’ goes down or up if the size of the buffer cache would go up or down. They greatly simplify evaluation of the buffer cache situation, as you do not need to compare big figures in columns ‘Size for Estimate’ and especially ‘Estimated Physical Reads’.
We will also generate the AWR at good time for comparison purpose. Our intention is to not add more buffer pool just because one bad query sucked it up ( in which case we will focus on query and not the buffer pool).
When you see huge Physical I/O happening in other section, you may be tempted to increase the database buffer cache in order to lessen the amount of physical I/O. That however does not take into account the overhead of serving the bigger buffer cache (at Oracle level, longer search for a required buffer in a much bigger space, in particular), which may outweigh benefits of further reduction in the amount of physical reads, especially if such a reduction is small compared with the portion of the increased buffer cache.
PARAMETER DESCRIPTION ANALYSIS
First Parameter “P” Apart from default buffer cache – pool (or subpool) which is always present, buffer cache may have other subpools.
Buffer Cache Advisory section will then have separate subsection for each of those subpools distinguished from others by a letter in the very left column of the section as follows:
‘D’ – Default buffer cache (always present),
‘K’ – Keep buffer cache (if db_keep_cache_size parameter is defined),
‘R’ – Recycle buffer cache (if db_recycle_cache_size parameter is defined),
– Caches for non-default block sizes (if defined with parameters db_k_cache_size)
Size Factor Changing ‘Size Factor’ shows ratio of the proposed size of the buffer cache (increased or decreased) to the approximate actual size currently in use found in the row with ‘Size Factor’ = 1.0.
Estimated Phys Read Factor Changing ‘Estimated Phys Read Factor’ shows ratio of the estimated number of Physical Reads for the proposed (increased or decreased) size of the buffer cache to the number of Physical Reads calculated for the current size of buffer cache found in the row with ‘Estimated Phys Read Factor’ = 1.0. HERE WE CAN SEE THAT IF WE INCREASE OUR BUFFER CACHE FROM 6 GB TO 10 GB IT IS HELPING US SIGNIFICANTLY
THIS FACTOR WILL COME DOWN FROM 1 TO 0.3.
PGA Memory Advisory
SIGNIFICANCE OF THIS SECTION:
Similar to Buffer Pool Advisory, the statistic provides information on how the increase or decrease in PGA memory will cause increase or decrease in Estd PGA Cahce Hit %.
Starting point here is “Size Factor” = 1.0. This gives current memory allocation for PGA. In this example 12 GB is being allocated to PGA. With this allocation the Estd PGA Cahce Hit % is 100, which is good. Hence even if we increase PGA to any value Estd PGA Cahce Hit % won’t change. Hence it won’t be advisable to increase PGA further. However decreasing can save memory without hitting performance
In this section, you first need to find the row with the ‘Size Factr’ column value of 1.0. This column indicates the size factor of the PGA estimates; a value of 1 indicates the current PGA size. The ‘PGA Target Est(MB)’ value of this row will show your current PGA size: 12 GB in this example. Other columns you will be interested in are ‘Estd Extra W/A MB Read/Written to Disk ‘ and ‘Estd PGA Overalloc Count’.
When you go down or up the advisory section from the row with ‘Size Factr’ = 1.0, you get estimates for Disk usage – column ‘Estd Extra W/A MB Read/Written to Disk ‘ – for bigger or smaller settings of PGA_AGGREGATE_TARGET. The less Disk usage figure in this column, usually the better. A lower value means less work areas have to be spilled to disk, enhancing performance of the Oracle instance.
The question on whether to increase or decrease the PGA_AGGREGATE_TARGET from the current value should be always investigated. The answer depends on how much of total memory (SGA+PGA) can be allocated for this database instance on the machine, taking into account memory needs of other database instances on the same machine, non-Oracle software and the OS itself. Too much memory allocated wastes memory, and too less memory allocated causes possible performance issues in the Oracle environment.
PARAMETER DESCRIPTION ANALYSIS
Estd PGA Overalloc Count Shows how many times the database instance processes would need to request more PGA memory at the OS level than the amount shown in the ‘PGA Target Est (MB)’ value of the respective row. Ideally this field should be 0 (indicating that the PGA is correctly sized, and no overallocations should take place), and that is your equally important second goal. In the given example this goal is achieved with PGA_AGGREGATE_TARGET of even 1,536MB.
So our PGA allocation is way too high.
Shared Pool Advisory
SIGNIFICANCE OF THIS SECTION:
SHARED POOL IS QUALITATIVE KIND OF POOL WHILE OTHER POOLS ARE QUANTITATIVE POOL. This means that it greatly depends “what” you are keeping in shared pool and not just “how much” you are keeping.
Similar to Buffer Pool Advisory and PGA, the statistic provides information on how the increase or decrease in Shared pool memory will cause increase or decrease in Estd LC Load Time (s).
Starting point here is “SP Size Factor” = 1.0. This gives current memory allocation for shared pool.
In this example approx 2 GB is being allocated to shared pool. With this allocation the Estd LC Load Time (s) is 58,972. If we increase the shared pool size to 4 GB then Estd LC Load Time (s) will come down to value 39301. Which is not a huge benefit but still the shared pool can be increased if possible.
Also you can analyze by the “Est LC Time Saved Factr” column . if “Est LC Time Saved Factr” increases as the “Size Factor” increases then increasing the shared pool will improve performance.
PARAMETER DESCRIPTION ANALYSIS
EST LC TIME SAVED LC means LIBRARY CACHE
you have to see that if increase your shared pool then what is the amount of this time that you can save
SGA Target Advisory
SIGNIFICANCE OF THIS SECTION:
The SGA target advisory report is somewhat of a summation of all the advisory reports previously presented in the AWR report. It helps you determine the impact of changing the settings of the SGA target size in terms of overall database performance. The report uses a value called DB Time as a measure of the increase or decrease in performance relative to the memory change made. Also the report will summarize an estimate of physical reads associated with the listed setting for the SGA.
Starting at a “Size Factor” of 1 (this indicates the current size of the SGA). If the “Est DB Time (s)” decreases significantly as the “Size Factor” increases then increasing the SGA will significantly reduce the physical reads and improve performance. but here in our example the Est DB Time is not reducing as much with increase in SGA so increasing SGA in our case will not be beneficial.
When the SQL requires a large volume of data access, increasing the SGA_TARGET size can reduce the amount of disk I/O and improve the SQL performance.
Buffer Wait Statistics
SIGNIFICANCE OF THIS SECTION:
The buffer wait statistics report helps you drill down on specific buffer wait events, and where the waits are occurring
We focus on Total wait time(s) and in this example this value is only 702 seconds
Avg time(ms) is also only 1 ms
Enqueue Activities
The Enqueue activity report provides information on enqueues (higher level Oracle locking) that occur. As with other reports, if you see high levels of wait times in these reports, you might dig further into the nature of the enqueue and determine the cause of the delays.
This can give some more information for enqueue waits (e.g. Requests, Successful gets, Failed gets), which can give an indication of the percentage of times that an enqueue has to wait and the number of failed gets.
In our example the top row do have failed gets but the number of waits is only 55 and wait time (s) is also not high number. So Enqueue is not our major issue in this AWR.
Undo Segment Summary
SIGNIFICANCE OF THIS SECTION:
PARAMETER DESCRIPTION ANALYSIS
Min/MAX TR (mins) Represents Minimum and Maximum Tuned Retention Minutes for Undo data. This data will help to set the UNDO_RETENTION database parameter. In this example this parameter can be set to 868.4 min
Max Qry Len(s) Represents Maximum query length in seconds. In this example the max query length is 51,263 seconds.
STO/ OOS Represents count for Sanpshot Too Old and Out Of Space errors, occurred during the snapshot period. In this example, we can see 0 errors occurred during this period.
Latch Activity
SIGNIFICANCE OF THIS SECTION:
The latch activity report provides information on Oracle’s low level locking mechanism called a latch. From this report you can determine if Oracle is suffering from latching problems, and if so, which latches are causing the greatest amount of contention on the system.
There are a plethora of latch statistics
Misses, unless they cause significant amount of sleeps aren’t of concern
Sleeps can be a problem
May need to look at spin count if you have excessive sleeps
Spin count (undocumented (_SPIN_COUNT) was based on CPU speed and 2000 setting was several years ago
If latch waits or other latch related events aren’t showing up, then latches probably aren’t an issue
Usually cache buffer and shared pool related latches are the major latches.
PARAMETER DESCRIPTION ANALYSIS
WAIT TIME (S)
should be 0
(Pct Get Miss should be 0 or near 0
Segments by Logical Reads
SIGNIFICANCE OF THIS SECTION:
The statistic displays segment details based on logical reads happened. Data displayed is sorted on “Logical Reads” column in descending order. It provides information about segments for which more logical reads are happening. Most of these SQLs can be found under section SQL Statistics -> SQL ordered by Gets.
These reports can help you find objects that are “hot” objects in the database. You may want to review the objects and determine why they are hot, and if there are any tuning opportunities available on those objects (e.g. partitioning), or on SQL accessing those objects.
When the segments are suffering from high logical I/O, those segments are listed here. When the table has high logical reads and its index has relatively small logical reads, there is a high possibility some SQL is using the index inefficiently, which is making a throw-away issue in the table. Find out the columns of the condition evaluated in the table side and move them into the index. When the index has high logical reads, the index is used excessively with wide range. You need to reduce the range with an additional filtering condition whose columns are in the same index.
If a SQL is suboptimal then this can indicate the tables and indexes where the workload or throwaway occurs and where the performance issue lies. It can be particularly useful if there are no actual statistics elsewhere (e.g. Row Source Operation Counts (STAT lines) in the SQL Trace or no actuals in the SQLT/Display Cursor report).
Segments by Physical Reads
SIGNIFICANCE OF THIS SECTION:
If there are a high number of physical read waits (db file scattered read, db file sequential read and direct path read) then this section can indicate on which segments (tables or
indexes) the issue occurs.
This can help identify suboptimal execution plan lines. It can also help identify changes to tablespace and storage management that will improve performance.
When the SQLs need excessive physical reads on the particular segments, this section lists them. You need to check if some of SQLs are using unnecessary full scan and wide range scan.
The statistic displays segment details based on physical reads happened. Data displayed is sorted on “Physical Reads” column in descending order. It provides information about segments for which more physical reads are happening.
Queries using these segments should be analysed to check whether any FTS is happening on these segments. In case FTS is happening then proper indexes should be created to eliminate FTS. Most of these SQLs can be found under section SQL Statistics -> SQL ordered by Reads.
These reports can help you find objects that are “hot” objects in the database. You may want to review the objects and determine why they are hot, and if there are any tuning opportunities available on those objects (e.g. partitioning), or on SQL accessing those objects.
For example, if an object is showing up on the physical reads report, it may be that an index is needed on that object.
PARAMETER DESCRIPTION ANALYSIS
Captured Segments account for 90.8%
This % number is important.
It should be high value which shows that we are looking at correct data.
The top segment record MTL_ITEM_CATEGORIES MTL_ITEM_CATEGORIES account for 51.32% of the total physical reads which is a big number. we need to see which SQL statement is using this segment and probably tune that SQL.
You will have to go to “SQL Ordered by Reads” section of AWR to see which SQL Statement is using this segment.
Segments by Row Lock Waits
SIGNIFICANCE OF THIS SECTION:
If there is a high level of “enq: TX row lock contention” waits then this section can identify the segments (tables/indexes) on which they occur.
The statistic displays segment details based on total “Row lock waits” which happened during snapshot period. Data displayed is sorted on “Row Lock Waits” column in descending order. It provides information about segments for which more database locking is happening.
DML statements using these segments should be analysed further to check the possibility of reducing concurrency due to row locking.
When the segments are suffering from Row Lock, those segments are listed in this section. The general solution is to provide more selective condition for the SQL to lock only rows that are restricted. Or, after DML execution, commit or rollback as soon as possible. Or so on.
Segments by ITL Waits
SIGNIFICANCE OF THIS SECTION:
If there is a high level of “enq: TX allocate ITL entry” waits then this section can identify the segments (tables/indexes) on which they occur.
Whenver a transaction modifies segment block, it first add transaction id in the Internal Transaction List table of the block. Size of this table is a block level configurable parameter. Based on the value of this parameter those many ITL slots are created in each block.
ITL wait happens in case total trasactions trying to update same block at the same time are greater than the ITL parameter value.
Total waits happening in the example are very less, 34 is the Max one. Hence it is not recommended to increase the ITL parameter value.
Usually when the segments are suffering from Row Lock, those segments are listed in this section. The general solution is to provide more selective condition for the SQL to lock only rows that are restricted. Or, after DML execution, commit or rollback as soon as possible. Or so on.
Segments by Buffer Busy Waits
SIGNIFICANCE OF THIS SECTION:
If there is a high level of “Buffer Busy Waits” waits then this section can identify the segments (tables/indexes) on which they occur.
The section lists segments that are suffering from buffer busy waits. Based on the reason code or class#, the treatment of each is different. The physical segment’s attributes such as freelist, freelist groups, pctfree, pctused and so on are handled by rebuilding the object. But before this treatment, you need to check if your SQLs can visit different blocks at the same time if possible to avoid the contention.
Buffer busy waits happen when more than one transaction tries to access same block at the same time. In this scenario, the first transaction which acquires lock on the block will able to proceed further whereas other transaction waits for the first transaction to finish.
If there are more than one instances of a process continuously polling database by executing same SQL (to check if there are any records available for processing), same block is read concurrently by all the instances of a process and this result in Buffer Busy wait event.
This is one of the post in Performance Tuning Fundamentals Series. Click on below links to read more posts from the series:
Performance Tuning Basics 1 : Selectivity and Cardinality
Performance Tuning Basics 2 : Parsing
Performance Tuning Basics 3 : Parent and Child Cursors
Performance Tuning Basics 4 : Bind Variables
Performance Tuning Basics 5 : Trace and TKPROF – Part 1: Trace
Performance Tuning Basics 6 : Trace and TKPROF – Part 2: Generating TKPROF
Performance Tuning Basics 7 : Trace and TKPROF – Part 3: Analyzing TKPROF Files
Performance Tuning Basics 8 : Trace File Analyzer (TRCA)
Performance Tuning Basics 9 : Optimizer Mode
Performance Tuning Basics 10 : Histograms
Performance Tuning Basics 11 : Steps to analyze a performance problem
Performance Tuning Basics 12 : Dynamic Performance Views
Performance Tuning Basics 13 : Automatic Workload Repository (AWR) Basics
Performance Tuning Basics 14 : Active Sessions History (ASH) Basics
Performance Tuning Basics 15 : AWR Report Analysis
Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC)
[Post Views: 33809]
Brijesh Gogia
Brijesh Gogia
I’m an experienced Oracle Applications DBA Architect with more than a decade of full-time DBA/Architect experience. I have gained a wide knowledge of the Oracle and Non-Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
You can connect with me on LinkedIn.
Show the Plan Hash Values for a Given SQLID Over a Given Period
--
-- Show the Plan Hash Values for a Given SQLID Over a Given Period
--
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
(
SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
FROM dba_hist_snapshot ss
WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE
) s
WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap
AND q.sql_id IN ( '&SQLID')
/
DBA_HIST_SQL_PLAN
DBA_HIST_SQL_PLAN displays the execution plan information for each child cursor in the workload repository. This view captures information from V$SQL_PLAN and is used with the DBA_HIST_SQLSTAT view.
Column Datatype NULL Description
DBID NUMBER NOT NULL Database ID
SQL_ID VARCHAR2(13) NOT NULL SQL identifier of the parent cursor in the library cache
PLAN_HASH_VALUE NUMBER NOT NULL Numerical representation of the SQL plan for the cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line).
ID NUMBER NOT NULL A number assigned to each step in the execution plan
OPERATION VARCHAR2(30) Name of the internal operation performed in this step (for example, TABLE ACCESS)
OPTIONS VARCHAR2(30) A variation on the operation described in the OPERATION column (for example, FULL)
OBJECT_NODE VARCHAR2(128) Name of the database link used to reference the object (a table name or view name). For local queries that use parallel execution, this column describes the order in which output from operations is consumed.
OBJECT# NUMBER Object number of the table or the index
OBJECT_OWNER VARCHAR2(30) Name of the user who owns the schema containing the table or index
OBJECT_NAME VARCHAR2(31) Name of the table or index
OBJECT_ALIAS VARCHAR2(65) Alias for the object
OBJECT_TYPE VARCHAR2(20) Type of the object
OPTIMIZER VARCHAR2(20) Current mode of the optimizer for the first row in the plan (statement line), for example, CHOOSE. When the operation is a database access (for example, TABLE ACCESS), this column indicates whether or not the object is analyzed.
PARENT_ID NUMBER ID of the next execution step that operates on the output of the current step
DEPTH NUMBER Depth (or level) of the operation in the tree. It is not necessary to issue a CONNECT BY statement to get the level information, which is generally used to indent the rows from the PLAN_TABLE table. The root operation (statement) is level 0.
POSITION NUMBER Order of processing for all operations that have the same PARENT_ID
SEARCH_COLUMNS NUMBER Number of index columns with start and stop keys (that is, the number of columns with matching predicates)
COST NUMBER Cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null.
CARDINALITY NUMBER Estimate, by the cost-based optimizer, of the number of rows produced by the operation
BYTES NUMBER Estimate, by the cost-based optimizer, of the number of bytes produced by the operation
OTHER_TAG VARCHAR2(35) Describes the contents of the OTHER column. See EXPLAIN PLAN for values.
PARTITION_START VARCHAR2(5) Start partition of a range of accessed partitions
PARTITION_STOP VARCHAR2(5) Stop partition of a range of accessed partitions
PARTITION_ID NUMBER Step that computes the pair of values of the PARTITION_START and PARTITION_STOP columns
OTHER VARCHAR2(4000) Other information specific to the execution step that users may find useful. See EXPLAIN PLAN for values.
DISTRIBUTION VARCHAR2(20) Stores the method used to distribute rows from producer query servers to consumer query servers
CPU_COST NUMBER CPU cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null.
IO_COST NUMBER I/O cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null.
TEMP_SPACE NUMBER Temporary space usage of the operation (sort or hash-join) as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null.
ACCESS_PREDICATES VARCHAR2(4000) Predicates used to locate rows in an access structure. For example, start or stop predicates for an index range scan.
FILTER_PREDICATES VARCHAR2(4000) Predicates used to filter rows before producing them
PROJECTION VARCHAR2(4000) Expressions produced by the operation
TIME NUMBER Elapsed time (in seconds) of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null.
QBLOCK_NAME VARCHAR2(31) Name of the query block
REMARKS VARCHAR2(4000) Remarks
TIMESTAMP DATE Timestamp for when the plan was produced
OTHER_XML CLOB Provides extra information specific to an execution step of the execution plan. The content of this column is structured using XML because it allows multiple pieces of information to be stored, including the following:
Name of the schema against which the query was parsed
Release number of the Oracle Database that produced the explain plan
Hash value associated with the execution plan
Name (if any) of the outline or the SQL profile used to build the execution plan
Indication of whether or not dynamic sampling was used to produce the plan
The outline data, a set of optimizer hints that can be used to regenerate the same plan
Subscribe to:
Posts (Atom)
-
The information in this document applies to Oracle E-Business Suite Release 11 i and R12.x . The most current version of this document ...
-
This document also provides links to two presentations on the subject: Oracle OpenWorld presentation "Technical Upgrade Best Practice...
-
The purpose of this document is to present known issues and solutions for the 19c Database Upgrade with Oracle E-Business Suite (EBS). Imp...
