Category Archives: Oracle

Because I work with it. A lot.

Getting Started with OEM 13cR2 ( Enterprise Manager

OEM13c Release 2 Enterprise Manager Summary Page

Relatively easy installation, a new learning curve to master for post-installation maintenance and patching.
The principal changes in Release 2 of 13c are the segregation of the Cloud Management Services plugins (and related functionality) to specific licensable plugin components – namely:

  • Cloud Services Management – used for provisioning, allocation, and administration (think “infrastructure”) of Oracle Public Cloud-based resources (databases, development tools, middleware, etc.)
  • Oracle Cloud Application – a portal punch-out to access Oracle Cloud-based applications such as hosted e-Business Suite, Cloud Content Management, Cloud SOA Suite, Cloud Identity Management, etc. (think “apps”.)
  • Cloud Framework – a repackaging of the EM OMS framework components, and the only portion of the “cloud” components included in the base OEM licensing.
    Installation guide:
Oracle Cloud Framework
Cloud Management related plugins for OEM13cR2

This proof-of-concept testbed system was based upon the latest available installer set – Oracle Enterprise Manager Cloud Control 13c Release 2 Plug-in Update 1 ( – we happen to be testing backwards-compatibility with some 10g databases before committing to the upgrade.  Direct (out-of-place) upgrades are supported for version of EM and newer.

Download page for OEM 13c R2

Fusion Middleware in this release is Weblogic 12.1.3
The host system is built upon Windows Server 2012 R2, single-tier, 16GB RAM, 2 cores, also hosting the OMS repository database in a EE installation.  Since the DB Templates provided seemed to be specific for a 12.1.x database (and errors out in certain steps during the build), I simply created a generic database from the standard template with the pre-requisite parameter settings (e.g. _allow_insert_with_update_check=TRUE; sessions > 300; shared_pool_size (10% of SGA); etc.) , and pre-created the required tablespaces:


  During the installation, the (Oracle Universal Installer) OUI will create the following new users assigned to these tablespaces:


Expired & Locked:

The standard download set includes a single executable file (.exe or .bin) and several additional ZIP archives.  These are to be staged in a single directory, and the ZIP files do NOT need to be pre-extracted.

Overall, the installation was straight-forward and as-documented (despite this being a Windows installation – which usually has its own quirks.) The portion that I feel has the steepest learning curve is post-installation maintenance, and particularly patching. The new tool in 13c, the OMSpatcher (Patchset 19999993 – think of it as a Java wrapper for opatch) was really designed for use specficially in a cloud-based environment and hasn’t been completely polished for use on-premise in smaller installations.

For example, it is designed to rely upon an active Weblogic AdminServer to instantiate it’s requests for inventory versioning information and component availability for patching. But if any of those components are actually in-use by the AdminServer itself, it will tend to fail to apply, and instead provide a lengthy step-by-step instruction set of how to accomplish all of the steps manually. Depending on the complexity of your OEM setup (e.g. the number of registered plugins and target types) this could take over an hour to get the patching utility to fail and then provide the manual steps.

The OEM-specific version of OPatch (Patchset 6880880) is quite different than its predecessors.  While the OMSPatcher uses the prior “unzip into the existing Oracle Home” installation technique, the OPatch utility itself now is a Java installer that relies on the OUI to allow updating of component versioning and inventory adjustments. Most importantly, you will need to “install” the new version of OPatch before being allowed to continue with your planned patching:

$JAVA_PATH\java -jar $PATCH_STAGE\6880880\opatch_generic.jar -silent oracle_home=$ORACLE_HOME (being updated for OPatch)

This OPatch update needs to be applied (before patching) to every Oracle Home using 13c technology (the OMS home, the Agent Home, other FMW homes at version 12.1.3 or newer, etc.)

See MOS EM 13c: How to Apply a Patch to the Enterprise Manager 13c Cloud Control OMS Oracle Home (Doc ID 2091619.1) for details about making an OMSPatcher property file, and why you want to create one.

Final steps executed:
Build general purpose database with new tablespaces.
Install Oracle Enterprise Manager Cloud Control 13c Release 2 Plug-in Update 1 (
Patch 19999993: EM OMSPatcher latest version (unzip to ORACLE_HOME)
Patch 6880880: EM OPatch latest version (both OMS and Agent)

cd $PATCH_STAGE\<patch#>
$FMW_HOME\OMSPatcher\omspatcher apply -analyze -property_file 
$FMW_HOME\OMSPatcher\omspatcher apply -property_file  [OMS_DISABLE_HOST_CHECK=true  -- a useful option added to deal with virtual host names]

Patch 25163555: Tracking bug for Back-porting 24588124 oms side fix
Patch 25604219: MERGE REQUEST ON TOP OF FOR BUGS 25497622 25497731 25506784
Patch 25387277: APR-2017 PSU OMS
Patch 25162444: EM-BEACON Bundle Patch (Agent)
Patch 25580746: EM-AGENT Bundle Patch (Agent)

Other useful references:

Enterprise Manager 13.2 Master Bundle Patch List (Doc ID 2219797.1) Enterprise Manager Base Platform Patch Set Update (PSU) Readme for Oracle Management Server (OMS) (Doc ID 2246778.1)

How To Get Those Customizations to Work Correctly with Oracle EBS R12.2 Editioning (EBR)

Oracle e-Business Suite R12.2 Overview of Edition-Based Redefinition (EBR) in 11gR2 databases.
Oracle e-Business Suite R12.2 Overview of Edition-Based Redefinition (EBR) in 11gR2 databases.

Creating a new view based upon the AP.AP_SUPPLIERS table to reside in a new XXCUSTOMSCHEMA.

If you attempt the simple:

create or replace force view XXCUSTOMSCHEMA.AP_SUPPLIER_NEW_V
FROM ap.ap_suppliers ;

It works, but your data and table definition may change whenever patching editions are in-play.

If you attempt to just reference the editioned object instead:

create or replace force view XXCUSTOMSCHEMA.AP_SUPPLIER_NEW_V
FROM ap.ap_suppliers# /* or the preferred apps.ap_suppliers synonym */

You will receive the ominous ORA-38818: illegal reference to editioned object error message.

What’s missing? The XXCUSTOMSCHEMA isn’t recognized as an edition-friendly schema by Oracle’s e-Business Suite (nor the database), yet.

Create your XXCUSTOMSCHEMA in the usual way:

identified by (password)
default tablespace USERS
temporary tablepace TEMP
profile DEFAULT
account UNLOCK;

/* as APPS user */


/* as SYSTEM user */

alter session set current_schema=APPS;

/* Create your SELECT grants to the editioned objects as APPS user */

grant select on ap.ap_suppliers#  to XXCUSTOMSCHEMA;

/* Create your custom view referencing the APPS synonym – which points to the editioned object */

create or replace force view XXCUSTOMSCHEMA.AP_SUPPLIER_NEW_V
FROM apps.ap_suppliers ;

This is the same process when creating procedures and functions with the usual caveat that unless it’s a 12c database, you cannot create materialized views on the editioned objects.

For the 11gR2 EBS users, you would stick with the existing non-editioned table object reference:

SELECT aps.vendor_id, aps.vendor_name
FROM ap.ap_suppliers aps;

/* The owning schema for the materialized view must have CREATE TABLE, VIEW AND INDEX privileges on the destination tablespace */

Setup and Install the Oracle EBS Integrated SOA Gateway for R12.2.x
1317697.1Functional Diagram of the Integrated SOA Gateway in Oracle e-Business Suite

Registering the Integrated SOA Gateway included in the Oracle E-Business Suite R12.2.x instances is actually reasonably simple, but the documents make it sound much worse than it really is, simply by being several hundred pages of documentation.

The reason to do this is to expose the RESTful services interface for EBS so that other web applications can do useful things, such as check usernames for validity, lookup business process (workflow) statuses, and even query for completion of processes, such as concurrent programs.  The built-in library of accessable Services is quite vast and enables lots of interconnectivity with other applications.

This is also useful if you happen to be extending the application using the Mobile Application Framework, which allows you to build custom applications for mobile devices that access all the various EBS functionalities.

NOTE:1311068.1 – Installing Oracle E-Business Suite Integrated SOA Gateway, Release 12.2
NOTE:1317697.1 – Oracle E-Business Suite Integrated SOA Gateway Troubleshooting Guide, Release 12.2

Before commencing, have the following prepared:
Unlock the ASADMIN (FND_USER) account and set the password so that it can login without a change password challenge (no expiry. required.
APPS password
Weblogic password
IP Address of ApplTier Host
Ports for ApplTier Host (both base port and WLS console – e.g. 8000 7001)
Your base port is the one in your usual EBS URL and the WLS console URL can be determined via Oracle Application Manager in the Site Map link for Weblogic Administration.
Services will be interrupted by bounce twice (2X) during installation.
Installation takes about 45 minutes to complete.

====================================  Start of SOA Integration Steps
#Register new Internal SOA Gateway Configuration

itsrv33m [/export/home/dbausr/oradev] ant -f $JAVA_TOP/oracle/apps/fnd/txk/util/txkSoaConfigUtility.xml
Buildfile: $COMMON_TOP/java/classes/oracle/apps/fnd/txk/util/txkSoaConfigUtility.xml
[input] Enter Oracle WebLogic Server Administration Console URL (host:port) :
[input] Enter Oracle WebLogic Server Admin User Name :  [weblogic]
[input] Enter the password for user weblogic :
[input] Enter the password for user apps :
[input] Enter the ASADMIN user name :  [ASADMIN]
[input] Enter the password for user ASADMIN :
[input] Enter SOA Server  Hostname : (hostname.domain)
[input] Enter External URL of SOA Suite SOA Managed Server (protocol://managed_server_hostname:managed_server_port) :

[input] Enter Internal URL of SOA Suite SOA Managed Server (protocol://managed_server_hostname:managed_server_port) :

[input] Enter External URL of SOA Suite Admin Server (protocol://admin_server_hostname:admin_server_port) :

[echo] ********************************************************************
[echo]                  Adding/Updating ASADMIN user in fnd_vault
[echo] ********************************************************************
[echo] Connecting to the EBS database using below properties
[echo] User: apps
[sql] Executing commands
[sql] 0 rows affected
[sql] 1 of 1 SQL statements executed successfully
[echo] Sucessfully Added/Updated ASADMIN in fnd_vault
[exec] Initializing WebLogic Scripting Tool (WLST) …
[exec] Welcome to WebLogic Server Administration Scripting Shell
[exec] Type help() for help on available commands
[exec] Connecting to server using username:weblogic url:[SOAhost:port]
[exec] Connecting to t3://[SOAhost:port] with userid weblogic …
[exec] Successfully connected to Admin Server ‘AdminServer’ that belongs to domain ‘EBS_domain_[ORACLE_SID]’.
[exec] Warning: An insecure protocol was used to connect to the
[exec] server. To ensure on-the-wire security, the SSL port or
[exec] Admin port should be used instead.
[exec] Creating user ASADMIN
[exec] Sucessfully created user ASADMIN
[delete] Deleting: $INST_TOP/logs/appl/rgf/TXK/
[echo]  Updating Context Variable s_soa_external_url
[echo]  Updating Context Variable s_soa_internal_url
[echo]  Updating Context Variable s_soa_admin_url
[echo]  Running Autoconfig
[exec] Enter the APPS user password:
[exec] The log file for this session is located at: $INST_TOP/admin/log/[session#]/adconfig.log
[exec] AutoConfig is configuring the Applications environment…
[exec] AutoConfig will consider the custom templates if present.
[exec]     Using CONFIG_HOME location     : $INST_TOP
[exec]     Classpath                   :
[exec]     Using Context file          : $CONTEXT_FILE
[exec] Context Value Management will now update the Context file
[exec]     Updating Context file…COMPLETED
[exec]     Attempting upload of Context file and templates to database…COMPLETED
[exec] Configuring templates from all of the product tops…
[exec]     Configuring AD_TOP……..COMPLETED
[exec]     Configuring FND_TOP…….COMPLETED
[exec]     Configuring ICX_TOP…….COMPLETED
[exec]     Configuring MSC_TOP…….COMPLETED
[exec]     Configuring IEO_TOP…….COMPLETED
[exec]     Configuring BIS_TOP…….COMPLETED
[exec]     Configuring CZ_TOP……..COMPLETED
[exec]     Configuring AMS_TOP…….COMPLETED
[exec]     Configuring CCT_TOP…….COMPLETED
[exec]     Configuring WSH_TOP…….COMPLETED
[exec]     Configuring CLN_TOP…….COMPLETED
[exec]     Configuring OKE_TOP…….COMPLETED
[exec]     Configuring OKL_TOP…….COMPLETED
[exec]     Configuring OKS_TOP…….COMPLETED
[exec]     Configuring CSF_TOP…….COMPLETED
[exec]     Configuring IBY_TOP…….COMPLETED
[exec]     Configuring JTF_TOP…….COMPLETED
[exec]     Configuring MWA_TOP…….COMPLETED
[exec]     Configuring CN_TOP……..COMPLETED
[exec]     Configuring CSI_TOP…….COMPLETED
[exec]     Configuring WIP_TOP…….COMPLETED
[exec]     Configuring CSE_TOP…….COMPLETED
[exec]     Configuring EAM_TOP…….COMPLETED
[exec]     Configuring GMF_TOP…….COMPLETED
[exec]     Configuring PON_TOP…….COMPLETED
[exec]     Configuring FTE_TOP…….COMPLETED
[exec]     Configuring ONT_TOP…….COMPLETED
[exec]     Configuring AR_TOP……..COMPLETED
[exec]     Configuring AHL_TOP…….COMPLETED
[exec]     Configuring IES_TOP…….COMPLETED
[exec]     Configuring OZF_TOP…….COMPLETED
[exec]     Configuring CSD_TOP…….COMPLETED
[exec]     Configuring IGC_TOP…….COMPLETED
[exec] AutoConfig completed successfully.
[echo]  Updating Profile  FND_SERVER_DESKTOP_USER
[java] Nov 02, 2016 8:21:24 AM oracle.apps.fnd.txk.util.TXKFMWGenericConfigUtility readPasswordFromStdin
[java] INFO: Enter APPS Password:
[echo]  Updating Profile FND_SERVER_SEC
[java] Nov 02, 2016 8:21:26 AM oracle.apps.fnd.txk.util.TXKFMWGenericConfigUtility readPasswordFromStdin
[java] INFO: Enter APPS Password:
[echo]  Updating Profile FND_SERVER_IP_SEC
[java] Nov 02, 2016 8:21:28 AM oracle.apps.fnd.txk.util.TXKFMWGenericConfigUtility readPasswordFromStdin
[java] INFO: Enter APPS Password:
[echo]  Updating Profile FND_SQLNET_ACCESS
[java] Nov 02, 2016 8:21:29 AM oracle.apps.fnd.txk.util.TXKFMWGenericConfigUtility readPasswordFromStdin
[java] INFO: Enter APPS Password:


Total time: 6 minutes 36 seconds
# Verify new Profile Option Settings
SQL Statement which produced this data:
/* Formatted on 11/17/2016 7:07:54 AM (QP5 v5.300) */
SELECT n.user_profile_option_name              NAME,
TO_CHAR (v.last_update_date, ‘DD-MON-RR’)”Last Updated”,
DECODE (v.level_id,
10001, ‘Site’,
10002, ‘Application’,
10003, ‘Responsibility’,
10004, ‘User’,
10005, ‘Server’,
10007, ‘SERVRESP’,
DECODE (TO_CHAR (v.level_id),
‘10001’, ”,
‘10002’, app.application_short_name,
‘10003’, rsp.responsibility_key,
‘10005’, svr.node_name,
‘10004’, usr.user_name,
‘10007’, v.LEVEL_VALUE2,
v.profile_option_value                  VALUE
FROM fnd_profile_options      p,
fnd_profile_option_values v,
fnd_profile_options_tl   n,
fnd_user                 usr,
fnd_application          app,
fnd_responsibility       rsp,
fnd_nodes                svr,
hr_operating_units       org
WHERE     p.profile_option_id = v.profile_option_id(+)
AND p.profile_option_name = n.profile_option_name
AND UPPER (p.profile_option_name) IN (‘FND_SERVER_DESKTOP_USER’,
AND n.language = ‘US’
/* Use for Friendly PONs e.g. MO: Security Option
AND UPPER (n.user_profile_option_name) =
AND usr.user_id(+) = v.level_value
AND rsp.application_id(+) = v.level_value_application_id
AND rsp.responsibility_id(+) = v.level_value
AND app.application_id(+) = v.level_value
AND svr.node_id(+) = v.level_value
AND org.organization_id(+) = v.level_value
ORDER BY v.last_update_date DESC;

FND: Desktop Nodes allowed|02-NOV-16|User|ASADMIN|[FQhostname]


FND: Validate IP address|02-NOV-16|Site||D

FND: Validate User Type|02-NOV-16|Site||D

# Bounce certain services (minimizes downtime – can also bounce everything)
cd $ADMIN_SCRIPTS_HOME stop oafm_server1 start oafm_server1 stop oacore_server1 start oacore_server1
[etc for any other oacore servers]

#Verify registered payload Class ID (ensures payload can be accessed)
SQL Statement which produced this data:
SELECT class_id
FROM fnd_irep_classes
AND class_type = ‘SERVICEBEAN’;

[a Number]

#Try downloading the payload for a specific function (Class ID discovered above) DEV2 URL:  http://%5ByourEBSURL:port%5D/webservices/isgagent/?generatexsd=%5Bthe above number]

# Configure the ISG BPEL Payload for use by SOA Suite
ant -f $JAVA_TOP/oracle/apps/fnd/bpel/util/txk_integration_packager.xml \ -DIntegration=isg  -DFMWNodeName=itsrv33m.mwd.h2o -DFMWIpAddress=[MT or Virtual host IP]
[echo] Bpel Payload is archived at $INST_TOP/logs/appl/rgf/TXK/

# Verify the “new” SOA Node is registred (MWD is just re-using the built in SOA)
SQL Statement which produced this data:
SELECT node_name, server_address, server_id
FROM fnd_nodes
WHERE UPPER(node_name) in(‘[hostname]’);

[hostname]|[IP address]|3E3B399AEB80184EE0548A507414599D22425228221243006566902023757213

# A Fix for HPUX, if needed, add set +u to the line right after #!/bin/sh
# vi $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/bin/

. $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/bin/

# Warning: the following command will immediately shutdown and restart the WLS Managed Services
ant -f $JAVA_TOP/oracle/apps/fnd/txk/util/txkISGConfigurator.xml ebsSetup

# Change default NAME=APPS connection to NAME=ASADMIN in WLS Admin Console.
# (you want to do this to support APPS password changes in the future – the ASADMIN
# password does not have to be disclosed to Developers once the setup is completed – just
# grant the Integrated SOA Gateway Role and Responsibility to users that need to deploy
# various services.

Data Sources -> OAEADatasource -> Configuration -> Connection Pool
[Lock & Edit]
Modify Properties user=APPS -> user=ASADMIN
Enter ASADMIN password in both password fields
[Save] -> [Activate Changes]

If the ASADMIN password is changed in the target EBS instance, then all of the following steps need to be re-executed in order to register the change with SOA:

# Clean out the prior OAEADatasource connections and definitions
Login to WLS Administration Console.
Services -> Data Sources -> [Lock & Edit] -> Select OAEADatasource -> [Delete]
Security Realms -> myrealm -> Providers -> Select IsgAuthenticator -> [Delete]
[Activate Changes]

$] cd $DOMAIN_HOME/config/jdbc
$] rm OAEADatasource-*-jdbc.xml

Repeat the SOA Integration steps again.

Otherwise, attempting REST deployments will receive an error:
java.lang.SecurityException: User: ASADMIN~~[ORACLE_SID], failed to be authenticated.

New OEM 12c/13c Agent Install Won’t Keep Running – Dies After Awhile

Many agents - Copyright by Warner Bros.,The Wachowski Brothers used without permission as educational content.
Agents are often hard to kill, usually. Images Copyright by Warner Bros., The Wachowski Brothers; used without permission as educational content.

OEM agents tend to occupy memory based upon how many targets they have to keep track of in a particular host.  At another organization, we tended to spin up VM’s for each instance environment, so at maximum, a particular agent might have a few hundred targets (especially on an e-Business Suite Applications Tier.)  In those circumstances, the default Java memory settings are probably fine.

In this environment, we run our hosts to death, and on this particular proof-of-concept host, we have 43 instances running on it, with variants of 10g, 11g, and 12c databases combined.

We are doing a fresh install of OEM for our POC before setting up the 13c production OMS, and after deploying the agent to this particular database host, the agent would startup fine, run for about 20 or so minutes and then abruptly die without warning.

Re-starts fine, passes the usual tests fine (before the 20 minutes or so goes by) and then dies again.


cd $AGENT_INST/bin

./emctl status agent

Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Agent Version          :
OMS Version            :
Protocol Version       :
Agent Home             : /u01/app/oracle/agent12c/agent_inst
Agent Log Directory    : /u01/app/oracle/agent12c/agent_inst/sysman/log
Agent Binaries         : /u01/app/oracle/agent12c/core/
Agent Process ID       : 10598
Parent Process ID      : 10499
Agent URL              : https://itsrv33c.mydomain:3872/emd/main/
Local Agent URL in NAT : https://itsrv33c.mydomain:3872/emd/main/
Repository URL         : https://itsrv35g.mydomain:1159/empbs/upload
Started at             : 2016-11-09 09:57:05
Started by user        : oracle
Operating System       : HP-UX version B.11.31 (IA64W)
Last Reload            : (none)
Last successful upload                       : 2016-11-09 10:19:26
Last attempted upload                        : 2016-11-09 10:19:26
Total Megabytes of XML files uploaded so far : 0.2
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 16.92%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2016-11-09 10:19:53
Last successful heartbeat to OMS             : 2016-11-09 10:19:53
Next scheduled heartbeat to OMS              : 2016-11-09 10:20:53

Agent is Running and Ready

./emctl pingOMS

Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
EMD pingOMS completed successfully

$AGENT_INST/sysman/log/gcagent.log contains

—– Wed Nov  9 09:39:43 2016::26900::Agent Launched with PID 27336 at time Wed
Nov  9 09:39:43 2016 —–
—– Wed Nov  9 09:39:43 2016::27336::Time elapsed between Launch of Watchdog p
rocess and execing EMAgent is 34 secs —–
2016-11-09 09:39:44,287 [1:main] WARN – Missing filename for log handler ‘wsm’
2016-11-09 09:39:44,302 [1:main] WARN – Missing filename for log handler ‘opss’
2016-11-09 09:39:44,305 [1:main] WARN – Missing filename for log handler ‘opsscf
Agent is going down due to an OutOfMemoryError
—– Wed Nov  9 09:40:06 2016::26900::Checking status of EMAgent : 27336 —–
—– Wed Nov  9 09:40:06 2016::26900::EMAgent exited at Wed Nov  9 09:40:06 201
6 with return value 57. —–
—– Wed Nov  9 09:40:06 2016::26900::EMAgent will be restarted because of an O
ut of Memory Exception. —–
—– Wed Nov  9 09:40:06 2016::26900::writeAbnormalExitTimestampToAgntStmp: exi
tCause=OOM : restartRequired=1 —–
—– Wed Nov  9 09:40:06 2016::26900::Restarting EMAgent. —–

That means, the agent is starting, then stopping, then restarting, then stopping (aka “thrashing”)

Take a look for the running agent daemon at the OS level:

ps -ef | grep agent12c
oracle 26900     1  0 09:39:09 pts/0     0:00 /u01/app/oracle/agent12c/core/ /u01/app/oracle/agent12c/core/ agent /u01/app/oracle/…
oracle 27665 26900  0 09:40:12 pts/0     1:01 /u01/app/oracle/agent12c/core/ -Xmx169M -XX:MaxPermSize=96M -server…

Oh – it’s set up for the default of 169MB of RAM.  Check My Oracle Support.

EM 12c: emctl start agent Fails ‘Fatal agent error: State Manager failed at Startup’ ‘restarted because of an Out of Memory Exception’ Reported in emagent.nohup /gcagent.log (Doc ID 1950490.1)

Verify this setting also in the $AGENT_INST/sysman/log/gcagent.log:

—– Wed Nov  9 09:40:06 2016::26900::Auto tuning the agent at time Wed Nov  9
09:40:06 2016 —–
agentJavaDefines=-Xmx169M -XX:MaxPermSize=96M
Auto tuning was successful

Well, it’s trying. Per the above Doc ID 1950490.1

Stop the agent.

$AGENT_INST/bin/emctl stop agent

Edit the $AGENT_INST/sysman/config/   (this contains the runtime parameters for the agent):

old entry:
agentJavaDefines=-Xmx169M -XX:MaxPermSize=96M

new entry:
agentJavaDefines=-Xmx512M -XX:MaxPermSize=96M

(You may tune these values up or down according to your environment requirements)

Restart the agent:

$AGENT_INST/bin/emctl stop agent

Agent runs, and keeps running like the E-Bunny.

R12.2 adop phase fs_clone fails because it can’t copy a file.

Complexity leading to fragility
Complex Systems Integration (Courtesy of:

Oracle…sometimes blinding in complexity, and a foreshadowing of what happens when a system becomes so complex that it becomes fragile.

This is what the screen session looks like: check the logfile /u01/oradev/fs1/inst/apps/DEV2_itsrv33m/logs/appl/admin/log/adnodemgrctl.txt for more information …
[UNEXPECTED]fs_clone has failed.
[STATEMENT] Please run adopscanlog utility, using the command
“adopscanlog -latest=yes”
to get the list of the log files along with snippet of the error message corresponding to each log file.
adop exiting with status = 2 (Fail)

Here is what it looks like in the adop.log

[EVENT]     Performing CLONE steps.
[PROCEDURE] [START 2016/11/08 08:20:19] CLONE Patch File System from Run File System
[PROCEDURE] Calling: /u01/oradev/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/
[EVENT]     Log: /u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m
[UNEXPECTED]Error occurred running “perl /u01/oradev/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/ -contextfile=/u01/oradev/fs2/inst/apps/DEV2_itsrv33m/appl/admin/DEV2_itsrv33m.xml -patchcontextfile=/u01/oradev/fs1/inst/apps/DEV2_itsrv33m/appl/admin/DEV2_itsrv33m.xml -promptmsg=hide -console=off -mode=create -sync_mode=copy -sessionid=25 -timestamp=20161108_081645 -outdir=/u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m”
[UNEXPECTED]occurred during CLONE Patch File System from Run File System, running command: “perl /u01/oradev/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/ -contextfile=/u01/oradev/fs2/inst/apps/DEV2_itsrv33m/appl/admin/DEV2_itsrv33m.xml -patchcontextfile=/u01/oradev/fs1/inst/apps/DEV2_itsrv33m/appl/admin/DEV2_itsrv33m.xml -promptmsg=hide -console=off -mode=create -sync_mode=copy -sessionid=25 -timestamp=20161108_081645 -outdir=/u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m”.
[EVENT]     Releasing managed server ports.
[PROCEDURE] [START 2016/11/08 08:30:55] Releasing ports
[STATEMENT] Calling task: “Releasing ports on itsrv33m”; script: “/u01/oradev/fs2/inst/apps/DEV2_itsrv33m/admin/install/”; args: ” -option=close -server=itsrv33m.mwd.h2o -ports=5570,7015,7615,7415,7215,7216,7217,7815,6114,6214,10013 -log=/u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m/txkCloneAcquirePort.log”
[PROCEDURE] [START 2016/11/08 08:30:56] Releasing ports on itsrv33m
[PROCEDURE] Calling: /u01/oradev/fs2/inst/apps/DEV2_itsrv33m/admin/install/
[EVENT]     Log: /u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m/txkCloneAcquirePort.log
[PROCEDURE] [END   2016/11/08 08:30:57] Releasing ports on itsrv33m
[PROCEDURE] [END   2016/11/08 08:30:57] Releasing ports

How cryptic!  Looks complicated.  This system happens to have AD/TXK.Delta.8, so the logs related to adop are all being centralized under the session # (in this case “25”.)

Diving deep into the log subdirectories:


Inside copyComnUtil()…

Directory /u01/oradev/fs1/EBSapps/comn already exists.
Removing the directory: /u01/oradev/fs1/EBSapps/comn/util

Copying the directory
SOURCE : /u01/oradev/fs2/EBSapps/comn/util
TARGET : /u01/oradev/fs1/EBSapps/comn/util

Execute SYSTEM command : cp -rH  /u01/oradev/fs2/EBSapps/comn/util /u01/oradev/fs1/EBSapps/comn/util
Copied /u01/oradev/fs2/EBSapps/comn/util into /u01/oradev/fs1/EBSapps/comn/utilLOG FILE: /u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m/TXK_SYNC_create/cp_rH.out

Inside copyCloneLogsToFSNE()…

Creating the directory: /u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m/TXK_SYNC_create/fsclone_apply

Copying the directory
SOURCE : /u01/oradev/fs2/inst/apps/DEV2_itsrv33m/admin/log/clone
TARGET : /u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m/TXK_SYNC_create/fsclone_apply

/u01/oradev/fs2/EBSapps/comn/adopclone_itsrv33m/bin/ did not go through successfully.
LOG DIRECTORY: /u01/oradev/fs_ne/EBSapps/log/adop/25/20161108_081645/fs_clone/itsrv33m/TXK_SYNC_create/fsclone_apply.
*******FATAL ERROR*******
PROGRAM : (/u01/oradev/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/
TIME    : Tue Nov  8 08:30:55 2016
FUNCTION: main::runFSCloneApply [ Level 1 ]
ERRORMSG: /u01/oradev/fs2/EBSapps/comn/adopclone_itsrv33m/bin/ did not go through successfully.


(One innocuous, but annoying file, owned by someone other than applmgr, and happened to be 600 (user read/write only):)

Done copying /u01/oradev/fs2/EBSapps/appl/wms to /u01/oradev/fs1/EBSapps/appl/wms/
Copying /u01/oradev/fs2/EBSapps/appl/gmf to /u01/oradev/fs1/EBSapps/appl/gmf/
.end std out.
cp: cannot open /u01/oradev/fs2/EBSapps/appl/mwd/12.0.0/sql/MWDPOLPO.xsl: Permission denied

.end err out.
WARNING: Copying of /u01/oradev/fs2/EBSapps/appl/mwd failed
ERROR: Copying failed, so stopping all the parallel threads
Done copying /u01/oradev/fs2/EBSapps/appl/okc to /u01/oradev/fs1/EBSapps/appl/okc/
WARNING: Copying of /u01/oradev/fs2/EBSapps/appl/okc failed
Done copying /u01/oradev/fs2/EBSapps/comn/java/classes/oracle/apps/pqh to /u01/oradev/fs1/EBSapps/comn/java/classes/oracle/apps/pqh/
WARNING: Copying of /u01/oradev/fs2/EBSapps/comn/java/classes/oracle/apps/pqh failed
Done copying /u01/oradev/fs2/EBSapps/appl/gmf to /u01/oradev/fs1/EBSapps/appl/gmf/
WARNING: Copying of /u01/oradev/fs2/EBSapps/appl/gmf failed
Done copying /u01/oradev/fs2/EBSapps/comn/java/classes/oracle/apps/ies to /u01/oradev/fs1/EBSapps/comn/java/classes/oracle/apps/ies/
WARNING: Copying of /u01/oradev/fs2/EBSapps/comn/java/classes/oracle/apps/ies failed
Done copying /u01/oradev/fs2/EBSapps/appl/msc to /u01/oradev/fs1/EBSapps/appl/msc/
WARNING: Copying of /u01/oradev/fs2/EBSapps/appl/msc failed
Done copying /u01/oradev/fs2/EBSapps/appl/ams to /u01/oradev/fs1/EBSapps/appl/ams/
WARNING: Copying of /u01/oradev/fs2/EBSapps/appl/ams failed
Done copying /u01/oradev/fs2/EBSapps/appl/cs to /u01/oradev/fs1/EBSapps/appl/cs/
WARNING: Copying of /u01/oradev/fs2/EBSapps/appl/cs failed
START: Inside exitClone….
Updating status INCOMPLETE for ApplyApplTop
START: Updating status INCOMPLETE for action ApplyApplTop
END: Updated status INCOMPLETE for action ApplyApplTop

One file.  A single file.  Could have been a hidden file. Could have been anywhere in the entire AppsTier filesystem.  This one file stopped ADOP cold from performing.  This happened to be phase=fs_clone, but could have been a patching session, or a post-clone config ( session.

Remove the file, and:

The fs_clone phase completed successfully.
adop exiting with status = 0 (Success)

Complexity leads to unintentional fragility.

Monitoring Oracle e-Business Suite (R12.2)

People ask what do I look for when monitoring an EBS instance for availability? Oracle Enterprise Manager (OEM) with the Applications Management Pack (AMS) gives a good starting point, but here is a condensed list of my general health check items for my production instance. It’s not everything possible, but it’s what I generally would check during a shake-down of a disaster-recovery exercise.

Warning Critical
Oracle E-Business Suite
Oracle Applications Infrastructure Service
Database Instance select SYSDATE from dual; Up/Down
Tablespace Freespace Available >80% >90%
Temporary Tablespace Used >80% >90%
Undo Tablespace Used >80% >90%
Fast Recovery Area Utilization >80% >90%
Redo Log Switch Rate > 10/hr. >20/hr
Blocking Sessions >1 >5
Database Listener tnsping <ORACLE_SID> Up/Down
TNSPing Response Time > 25ms > 100ms
Applications Listener tnsping <FNDFS_ORACLE_SID> Up/Down
Host Monitoring
CPU Utilization > 80% > 95%
RAM Utilization > 90% > 99%
Mountpoint Free Space Available <10% <5%
Concurrent Processing Services
Internal Manager Up/Down
Standard Manager < 10
Output Post Processor < 3
Receiving Transactions Manager < 1
Document Approval Manager < 1
Forms Applications Service
Forms Listener Service Up/Down
Forms Runtime Processes <1
Forms Response Time >5 secs >30 secs
Active Forms Sessions > 100 > 200
Self-Service Applications Service
HTTP Listener Service Up/Down
OPMN Processor Up/Down
OC4J Service Up/Down
WebLogic Deployed Services Up/Down
WebLogic AdminServer Up/Down
OA Framework Page Response Time > 5 secs > 10 secs
Oracle Applications JVM Usage >80% >90%
Workflow Serivce OAM Dashboard Up/Down
Workflow Agent Listener Up/Down
Workflow Background Engine Up/Down
Workflow Notification Mailer Services Up/Down
Oracle E-Business Suite Custom Objects
Invalid Object Count > 482 > 500
Oracle E-Business Suite Patch Information
Patch Inventory
ADOP Session Status >1 Pending >1 Failed

Oracle OpenWorld 2016 in 5 Pages


All of Oracle Cloud on a Page
Oracle OpenWorld 2016 Cloud Services and Products Summary

4 Days (almost 7 for me) of Product Directors and Managers spinning as many plates in the air as humanly possible, all being tossed into the Cloud with a fervor only matched by the lack of compensation being provided for conventional technology stack sales, left me with some specific value-adds for my time spent (not including the Safe Harbor information, we can’t say anything about….sshhhh.)

Note: (I’m EBS-centric and we have very little budget for hardware, so while I was amazed at the new SPARC S7 processors with on-board encryption and J/SQL optimization, alas they didn’t make it into my summary pages this year.)

AD/TXK Delta.8
Oracle E-Business Suite Applications DBA and Technology Stack Release Notes for R12.AD.C.Delta.8 and R12.TXK.C.Delta.8 (Doc ID 2159750.1)

Configuring Oracle E-Business Suite Release 12.x Using Logical Host Names (Doc ID 1968231.1)

  • e-Mail Notifications at Parent and Child Levels for Request Sets
  • Save and Re-use Concurrent Program Parameters (CP Templates)
  • Regex Support in Profile Option Values and FND_MESSAGES
  • Multiple OA Framework Windows and Tabs in Multiple Browser Windows
  • EBS Healthcheck Diagnostics
  • 64-bit JDK’s on CM Tier
  • adop Improvements
  • Delta Sync Mode (syncmode=patch/delta) – changed rows only
  • Rsync support in phase=prepare
  • Custom module sync auto-supported (no custom drivers req’d)
  • Standardized logging SessionID/ExecutionID/phase/node

EBS 12.2.6.AD Oracle E-Business Suite 12.2.6 Readme (Doc ID 2114016.1)

  • Security Configuration Console
  • JSP Whitelisting
  • Redirection Whitelisting
  • AD/TKX.Delta.8 incorporated
  • More HTML User Interfaces: HTML alternatives to several existing Forms; interfaces offer simpler layouts and more modern user interactions.

–Examples include the Update Assets UI in Enterprise Asset Management, and AOL setup UIs for Users, Responsibilities, and Flexfields.

SecureFiles does not allow shrinking of the tablespace after deletions (AP Imaging)

Available Interconnect Cloud Service adapters:

  • –PL/SQL
  • –Java Beans
  • –Apps Modules

Roadmap ICS adapters:

  • –REST
  • –Concurrent Programs
  • –XML Gateway
  • –BPEL/Business Services

CAAS (Cloud Adaptive Assistant Service) – The widget Larry was text-chatting with.

Mobile Foundation and Mobile Application Framework

Foundation v6 released August 2016

–Incorporates MAF 2.3.2

–Enhancements for Approvals (HR, Projects, Service Centers, Quoting, Custom Approvals)

Foundation v7 scheduled for April 2017

MAF Roadmap items:

–Airwatch and MobileIron support

–Mobile REST API’s

–EBS Integrated Service Gateway support (SOA)

PDF Version for download:       oracle_oow16_summary_jlui

Oracle OpenWorld 2016 Networking Opportunity Events #OOW16

oow16_bannerWhere I can keep track of the special events (note: these are not “parties” as so many people are misled to believe – you should expect to be contacted by sales and account professionals at each event) at OOW.  To attend one of these events:

  1. You’re registered as an OpenWorld Attendee.
  2. You’re either a prospect, customer, or goodwill contact for the host. That means you actually contact the host and ask to attend before OOW.
  3. You visit the host’s booth at OpenWorld in order to pick up whatever is required for entry and get to know what the host actually does.
  4. Do not just show up at the event and attempt to “crash” it – fair warning. You will be sold Oracle products and consulting, and like it, and start telling all your friends about it and be assimilated.

Visit all those vendors and make them feel welcomed this year!


Oracle OpenWorld Fun Community Events
There are 3 community events at OOW this year,

  1. A guided bike tour hosted by Oracle’s PL/SQL and cycling guru, Bryn Llewelyn on Saturday, September 17th at 10 a.m.
  2. A run across the Golden Gate Bridge hosted by the SQL Dev team on Sunday, October 25th at 8:30 a.m.
  3. 5th annual swim in the San Francisco Bay hosted by Oracle ACE Director Chet Justice on Monday, October 26th at 7:30 a.m.

The 2015 List of OOW Events

The 2014 List of OOW Events

The 2013 List of OOW Events

The 2012 List of OOW Events

And OakTable World is back once again, at the Creativity Museum (this year, no pre-registration required).


Agenda for Oaktable World 2016, located at Creativity Museum, is looking like:


What’s New Features in OEM AMS Applications Management Pack for OEM 13c Released July 22

Per Getting Started with Oracle Application Management Pack (AMP) for Oracle E-Business Suite Release (Doc ID 2045552.1)

Key Features

New EBS AMS Plugin Homepage Screenshot 13c
New EBS AMS Plugin Homepage Screenshot 13c

The following are brief descriptions of key features introduced in this release:

Enterprise Manager 13c Technology Uptake
Hybrid Cloud Management
System Management for Oracle E-Business Suite
Change Management for Oracle E-Business Suite

Enterprise Manager 13c Technology Uptake

Oracle Enterprise Manager 13c includes several enhancements including:

  • A new Oracle Fusion Middleware technology stack
  • The “Alta” user interface to enhance user experience

    New OEM Alta Interface 13c
    New OEM Alta Interface 13c
  • Edition Based Redefinition (EBR) support

Hybrid Cloud Managment

Oracle Application Management Suite now delivers several capabilities to monitor and manage Oracle E-Business Suite environments on Oracle Cloud and on-premises. These capabilities are delivered through the Enterprise Manager command line interface (EMCLI) for greater flexibility.

  • Discovering Orchestration Virtual Machine
  • Provisioning a New Oracle E-Business Suite 12.2 Instance
  • Backup and Restore an Oracle E-Business Suite Instance
  • Lift and Shift E-Business Suite instances from On-premise to Oracle Cloud
  • Cloning E-Business Suite instances within Oracle Cloud
  • Ability to run FS_Clone only option
  • Apply E-Business Suite patches
  • Start and Stop E-Business Suite instances on Cloud
  • To create custom packages

System Managment for Oracle E-Business Suite

Discovery and Monitoring
  • Real User Experience Insight (RUEI) regions integrated within the Oracle E-Business Suite Summary page
    • In this release, we have integrated five different RUEI regions within the Oracle E-Business Suite Summary page. Customers can personalize and activate one or all RUEI regions. These RUEI regions are specific to a given Oracle E-Business Suite instance and customers can view the data and drill down for detailed information.
  • Auto Host Aliasing
    • Host aliasing is now automated and will attempt to match the host name in the Oracle E-Business Suite context file and the discovered host name in Oracle Enterprise Manager. If this attempt fails, you will be prompted to perform the mapping manually. Customization Manager and Patch Manager features now support host aliasing.

Change Management for Oracle E-Business Suite

Patch Manager – Oracle E-Business Suite Technology Stack Pack Deployments
  • During the prior release of Application Management Suite, you had the ability to view patch recommendations for E-Business Suite applications and the technology stack. In this release, you now have the ability to deploy Database and WebLogic server patches using Enterprise Manager patch plans.
Customization Manager – Customization Discovery & Reporting
  • Customization Discovery & Reporting process now includes discovery of database objects.
Cloning – Automated Cloning
  • You now have the ability to save the cloning interview process as templates that can be used for future purposes for Smart Clone for R12, 12.2.x procedure.
  • You can add custom parameters to the cloning procedure


Oracle Configuration Manager Quick Tip – EBS R12.2 Disconnected Collections

Collecting Disconnected Mode OCM data for e-Business Suite R12.2
per MOS Document ID 1447334.1 - Oracle E-Business Suite Release 12.2: Release Notes for Oracle Configuration Manager
My Oracle Support - Download Collector page
MOS Oracle Configuration Manager Collector Download screen.
This method is used whenever your EBS instance is behind a firewall and no proxy server is available to reach the OCM collector end-point (

The reason you want to use OCM is not only because it provides a complete catalog of your Oracle software versions, modules and plug-ins, so that they can easily be associated with a Service Request (SR) instead of the analyst asking for every RDA script being run for every component in your application and database. 

It also enables the full functionality of that Systems Tab in My Oracle Support (buried just above your Settings menu) that allows you to do light versions of lifecycle management comparisons (such as what the instance looked like 2 weeks ago versus now) without having to own the OEM Lifecycle Management license. It comes compliments of your support agreement.

This also enables the full functionality of the patch and security recommendations section vital towards identifying high priority one-off patches specific to your particular configuration and platform.

Basically, instead of having a cron job periodically automatically upload the configurations to, you will be creating the same staged configuration file, but uploading it to your Customer Support Identifier (CSI) via a Draft Service Request (SR) or a currently Open SR via the File Attachment... feature of the SRs.

In the background of MOS is a managed file transfer handler that looks for the specifically named "ocmconfig.jar" files and when detected, route them over to the Configuration Manager engine for automatic uploading to your CSI account.

While not as convenient as the automatic cron method, for those of use with no direct internet connectivity to, our OEM and MOS functionalities become quite limited if the configurations aren't present under our CSI's.e.g. ORACLE_BASE=/u01/app/oracle
# Oracle DB Home cd $ORACLE_HOME unset ORACLE_CONFIG_HOME export JAVA_HOME=$ORACLE_HOME/jdk cd $ORACLE_HOME/ccr # Clear prior CCR installation, if required. rm -rf $ORACLE_HOME/ccr/* cd $ORACLE_HOME unzip <patch_stage_dir>/p5567658_<ver>_<platform>.zip cd $ORACLE_HOME/ccr/bin # Run setupCCR in Disconnected mode ./setupCCR -s -d # Create ORACLE_OCM user and related grants . $ORACLE_HOME/ccr/admin/scripts/ collectconfig -s <SID> -r SYS # Additional grants for APPS EBS Collector . $ORACLE_HOME/ccr/admin/scripts/ ebs_collectconfig -u <APPSUSERNAME> # Run the collection and generate the ocmconfig.jar cd $ORACLE_HOME/ccr/bin ./emCCR collect # ocmconfig.jar location $ORACLE_HOME/ccr/hosts/$HOSTNAME/state/upload # Oracle 10.1.2 iAS Home . $ORACLE_BASE/EBSapps.env run export ORACLE_HOME=$ORACLE_HOME export ORACLE_CONFIG_HOME=$INST_TOP/ora/10.1.2 export JAVA_HOME=$ORACLE_HOME/jdk # Remove prior OCM config, if needed rm -rf $ORACLE_HOME/ccr/* rm -rf $ORACLE_CONFIG_HOME/ccr/* cd $ORACLE_HOME unzip <patch_stage_dir>/p5567658_<ver>_<platform>.zip # Bugfix specific to HP-UX Itanium - verify file permissions to execute chmod 754 $ORACLE_HOME/ccr/bin/deployPackages chmod 754 $ORACLE_HOME/ccr/bin/emSnapshotEnv cd $ORACLE_HOME/ccr/bin ./setupCCR -s -d # Disconnected mode # Run the collection and generate the ocmconfig.jar cd $ORACLE_HOME/ccr/bin ./emCCR collect # ocmconfig.jar location $INST_TOP/ora/10.1.2/ccr/state/upload #Repeat for patch filesystem; . $ORACLE_BASE/EBSapps.env patch # Oracle FMW WebLogic Home # source the Domain environment . $ORACLE_BASE/EBSapps.env run . $EBS_DOMAIN_HOME/bin/ export ORACLE_HOME=$FMW_HOME/utils export ORACLE_CONFIG_HOME=$INST_TOP/ora/FMW # Remove prior OCM config, if needed rm -rf $ORACLE_HOME/ccr/* rm -rf $ORACLE_CONFIG_HOME/ccr/* cd $ORACLE_HOME unzip <patch_stage_dir>/p5567658_<ver>_<platform>.zip # Bugfix specific to HP-UX Itanium - verify file permissions to execute chmod 754 $ORACLE_HOME/ccr/bin/deployPackages chmod 754 $ORACLE_HOME/ccr/bin/emSnapshotEnv cd $ORACLE_HOME/ccr/bin ./setupCCR -s -d # Disconnected mode # Run the collection and generate the ocmconfig.jar cd $ORACLE_HOME/ccr/bin ./emCCR collect # ocmconfig.jar location $INST_TOP/FMW/ccr/state/upload #Repeat for patch filesystem; . $ORACLE_BASE/EBSapps.env patch # Upload all of your ocmconfig.jar files (can be named uniquely - e.g. mydbocmconfig.jar) per MOS Document ID 763142.1 - How to upload the collection file ocmconfig.jar to My Oracle Support for Oracle Configuration Manager (OCM) running in Disconnected Mode.
# Also can ZIP up all of them at once and upload them to your Draft SR (or current open SR as long as you're happy with the CSI (Customer Support Identifier) that will receive the new configurations.
# Trick is have the string "ocmconfig" and .jar as the extension of each file
Have fun with your new Systems tab in My Oracle Support!

TRCSESS – Trace Information for Various Oracle Sessions or Modules – Quick & Dirty Tip

Database Sessions view from Oracle SQL Developer
Database Sessions view from Oracle SQL Developer

— Find your session

SQL> /* a Dell TOAD type query */

SELECT ROUND (BITAND (s.ownerid, 65535)) parent_session_sid,
ROUND (BITAND (s.ownerid, 16711680) / 65536) parent_session_instid,
stat.cpu – stat.CPU_this_call_start cpu_this_call,
p.program “OSProgram”,
(  SELECT ss.sid stat_sid,
DECODE (, ‘CPU used when call started’, ss.VALUE, 0))
DECODE (, ‘CPU used by this session’, ss.VALUE, 0))
SUM (DECODE (, ‘session uga memory’, ss.VALUE, 0))
SUM (DECODE (, ‘session pga memory’, ss.VALUE, 0))
SUM (DECODE (, ‘user commits’, ss.VALUE, 0)) commits,
SUM (DECODE (, ‘user rollbacks’, ss.VALUE, 0))
FROM v$sesstat ss, v$statname sn
AND ( = ‘CPU used when call started’
OR = ‘CPU used by this session’
OR = ‘session uga memory’
OR = ‘session pga memory’
OR = ‘user commits’
OR = ‘user rollbacks’)
GROUP BY ss.sid) stat
AND (NVL (s.osuser, ‘x’) <> ‘SYSTEM’)
AND si.sid(+) = s.sid
AND p.addr(+) = s.paddr
AND stat.stat_sid = s.sid

— Setup a Client Trace:

SQL> exec dbms_monitor.client_id_trace_enable (client_id=>'<terminal or client string>’);
PL/SQL procedure successfully completed.

— Setup a trace for all matching session threads for a module:
SQL> exec dbms_monitor.serv_mod_act_trace_enable (service_name=>'<ORACLE_SID or SERVICE_NAME>’, module_name=>'<MODULE from V$SESSION>’);
PL/SQL procedure successfully completed.

Visit your trace directory (in 11gR2 and up, you can consolidate the various dump locations using the init.ora parameter diagnostic_dest='<filepath>’)

# *.trc looks through every trace file in the directory for the matching module string
# Can also use any of the other options to search for specific sessions:
# [action=action_name]
# [clientid=client_id]
# [session=session_id]

oracle > trcsess output=mytrace.trc module=”<MODULE from V$SESSION>” *.trc

# Then just execute your normal tkprof command to format the consolidated output

oracle > tkprof mytrace.trc myformattedtrace.txt sys=no sort=exeela explain=<user/pwd>

# Voila!  All the traces for a specific session, module, or action at a specific time.

— Don’t forget to disable your tracing afterwards!

SQL> exec dbms_monitor.client_id_trace_disable (client_id=>'<terminal or client string>’);
PL/SQL procedure successfully completed.

— Setup a trace for all matching session threads for a module:
SQL> exec dbms_monitor.serv_mod_act_trace_disable (service_name=>'<ORACLE_SID or SERVICE_NAME>’, module_name=>'<MODULE from V$SESSION>’);
PL/SQL procedure successfully completed.

ORA-24247: network access denied by access control list (ACL) Demantra

When attempting to execute a new
ORA-29273: HTTP request failed
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_HTTP”, line 1491
ORA-06512: at line 1
Starting Demantra: Hardware. LOG. JAVA Compatibility ACL / SYS_GRANTS. SYNC WKS. Setup MAIL (Doc ID 1372253.1)
Here is an example of Demantra installed directory where the sql files located:
C:\Program Files (x86)\Oracle Demantra\Demand Planner\Database Objects\Oracle Server\admin

Have you met this error on db_exception_log? ~ This is how you can fix it!:

29273 : ORA-29273: HTTP request failed
ORA-06512: at “”SYS.UTL_HTTP””, line 1817

ORA-24247: network access denied by access control list (ACL)”

SYS_GRANTS.sql performs the following:
– Adds EXECUTE privileges to access DBMS_CRYPTO (UPGRADE_PASSWORDS): Provides the highest level of user password encryption.

– Adds EXECUTE privileges to access DBMS_LOCK: Provides as SLEEP operation for improved concurrency.

– Adds EXECUTE privileges to access V_$PARAMETER so that Oracle Demantra can better adapt to your database configuration.

– (10g only) Adds GRANT privileges to access the package UTL_HTTP, which enables Oracle Demantra to send notification messages to the application server and engine.

– (11g only) Adds an ACL to enable HTTP communications for Oracle Demantra to send notification messages to the application server and engine.

Find it in Demantra_Folder\Demand Planner\Database Objects\Oracle Server\admin
or directly in the root of the Demantra installation package.

Please also verify you have the correct data in

SQL> select * from sys_params where lower(pname) like ‘%url%’;

You need to run this script manually after installing or upgrading Demantra only if you did not specify a database user with full SYSDBA privileges when running the Installer.
1. !! Login AS sys, WITH sysdba privileges:
3. C:\DEMANTRA_INSTALL_DIRECTORY> sqlplus SYS@SERVER as sysdba@sys_grants.sql DM_SCHEMA_USER ACL_for_WebServerURL ACL_for_EngineServerURL
Example execution:
C:\DEMANTRA_INSTALL_DIRECTORY> sqlplus SYS@SERVER as sysdba@sys_grants.sql DM_SCHEMA_USER ‘/sys/acls/demantra.xml’ ‘ /sys/acls/demantra.xml’
OR by Default: if you are not sure, they will be created if they do not exist, or updated in /sys/acls/demantra.xml:
General Checks:
— select * from dba_network_acls;
— select * from dba_network_acl_privileges;
Check also in sys_params or Business Modeler
— AppServerURL and
— EngineServerURL
— select * from sys_params where lower(pname) like ‘%url%’;
— SQL> select * from DBA_tab_privs where grantee = ‘DEMANTRA’ ; — (dem schema)
Be sure from system user run if needed entry for APPS user against demantra.xml in dba_network_acl_privileges table: BEGIN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ( ‘/sys/acls/demantra.xml’, ‘APPS’,TRUE,’connect’); COMMIT; END;
—Also check entry for UTL_HTTP table for the query – select * from DBA_tab_privs where grantee = ‘DEMANTRA’ ;
For failed login problem to Collaborator Workbench, check if sys_grants is needed to be applied again:
1. SQL> select distinct encryption_type from user_id; — This should return a single value like “SHA-1”
2. SQL> select encryption.get_highest_desc() from dual; — This should return the same value returned in step (1).
If the result of (1) is “SHA-1” but the return of (2) is something else, then you need to fix your sys_grants.



Document: 454369.1 Roles and Privileges that are granted to the Demantra database during the install process
Document: 730883.1 Additional Database Privilege needed for Demantra Schema when Running on Oracle 11g Database
Document: 1064995.1 Additional Configurations needed when Running Demantra on Oracle 11g Databases
Oracle Demantra Installation Guide for Release 7.3 Document: 825082.1