SQLPLUS APPS Connection is intermittently failing

SQLPLUS APPS Connection is intermittently failing

Reason for this Issue: Invalid TNS address supplied or destination is not listening. This error can also occur because of underlying network transport problems.

For my case it was wrong gateway configuration.

Successful:

mkkebstestu1@mkktestserver1:/home/mkkebstestu1>sqlplus apps/*************

SQL*Plus: Release 8.0.6.0.0 - Production on Mon Oct 29 15:42:35 2012

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Unsuccessful Attempts:

mkkebstestu1@mkktestserver1:/home/mkkebstestu1>sqlplus apps/************

SQL*Plus: Release 8.0.6.0.0 - Production on Mon Oct 29 15:46:49 2012

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

ERROR:
ORA-12203: TNS:unable to connect to destination


Enter user-name: 

mkkebstestu1@mkktestserver1:/home/mkkebstestu1>netstat -rn
Routing tables
Destination        Gateway           Flags   Refs     Use  If   Exp  Groups

Route Tree for Protocol Family 2 (Internet):
default            172.30.0.1        UG        1     34949 en2      -      -   =>
default            172.30.0.10       UG        7    795361 en2      -      -
60.60.60.1         127.0.0.1         UGHS      0      1733 lo0      -      -   =>
60.60.60.1         60.60.60.1        UHSb      0      1733 en2      -      -   =>
60.60.60.1/24      60.60.60.1        U         0         0 en2      -      -
60.60.60.255       60.60.60.1        UHSb      0         4 en2      -      -
127/8              127.0.0.1         U        14    781913 lo0      -      -
172.30.0.0         172.30.0.92       UHSb      0         0 en2      -      -   =>
172.30/24          172.30.0.92       U         2   6576453 en2      -      -
172.30.0.92        127.0.0.1         UGHS      1       458 lo0      -      -
172.30.0.255       172.30.0.92       UHSb      0         4 en2      -      -

Route Tree for Protocol Family 24 (Internet v6):
::1                ::1               UH        0        92 lo0      -      -
mkkebstestu1@mkktestserver1:/home/mkkebstestu1>

>>>> If you notice the netstat spool there are two gatways are defined. This is not a correct configuration. Highlighted in RED.


Solution: Flush the Gateway from smitty

mkktestserver1[/] # smitty route

Press Enter AFTER making all desired changes.

                                                        [Entry Fields]
  Flush Routing Table in the Current Running System   yes                                                                          +
  Flush IPV4 Routes in the Configuration Data Base    yes                                                                          +
  Flush IPV6 Routes in the Configuration Data Base    no                                                                           +
   (effective in the next system restart)

RMAN Active/Standby Duplicate Hangs

RMAN Active/Standby Duplicate Hangs

Recently, I was doing a DataGuard set-up for 11gR2 database. After doing the preliminary steps and database level preparation, I started the RMAN Standby Duplicate command.
I wait for long, almost 4 hours, but still the datafile copy didn't start from primary to standby(source to target).

Similar issue I have faced earlier too for 11gR1 database while doing implementation of DR Site for eBusiness Suite, OBIEE and some Oracle Retail Applications(RMS, WMS, RIB, SIM etc), but documenting here this time for benefits.

Connect to Primary(Source) database using RMAN:

$ RMAN
RMAN> CONNECT TARGET;
RMAN> SHOW ALL;

Check what is the setting for BACKUP OPTIMIZATION.

Switch backup optimisation off before starting the RMAN Duplicate command, otherwise, RMAN would internally decide the optimization plan for the duplicate process.
This would take lot of time if the DB Size is high to start the datafile copy. Database size in this case was 1.7TB.

In Primary(Source) Database execute the following command:
RMAN> CONFIGURE BACKUP OPTIMIZATION OFF;

---- Revert back the change in Primary when RMAN duplicate finished.

This solve the issue for me, but would like to highlight few more reasons which can cause RMAN Active Duplicate to hang.

1. SGA and Pool Size can Impact your Duplicate Performance

Large pool size plays an important role in RMAN Recovery, SGA decides how many channel you can use your RMAN Duplicate command.

I was having ample sizing for the primary/standby database, so set the following in both:
*.sga_target = 14G
*.shared_pool_size = 3G
*.shared_pool_reserved_size = 300M
*.streams_pool_size = 150M
*.java_pool_size = 1G
*.pga_aggregate_target = 20G
*.result_cache_max_size = 150M
*.db_cache_size = 2G
*.large_pool_size=300M
*.db_keep_cache_size = 500M
*.db_writer_processes = 3
*.processes = 2500
*.sessions = 5000
*.open_cursors = 1500

2. RMAN Duplicate can hang if Compatible setting in both target and auxiliary database is not same. Refer MOS Note 816094.1 for the same. 

Significance of "Sign-On:Audit Level" Profile Option

Significance of "Sign-On:Audit Level" Profile Option

Sign-On: Audit Level can be set at 4 different levels.  The level you specify will determine which Change Tracking information Oracle eBusiness would Capture

The four levels (from lowest to highest level of audit) and the information that they capture are:

1. When Site Level Value is None
Tracks no additional info.

2. When Site Level Value is User
Tracks: who signs on to Oracle Apps, the time users log on and off, the terminals in use

3. When Site Level Value is Responsibility
Tracks: User Info, the responsibilities user choose, how much time users spend using each responsibility

4. When Site Level Value is Form
Tracks: User Info, Responsibility Info, the forms users choose, how long users spend using each form

Based on the level chosen, the information captured gets stored in the following tables:
FND_LOGINS 
FND_LOGIN_RESPONSIBILITIES 
FND_LOGIN_RESP_FORMS 

Truncate Login Related Tables in NONLIVE Instance/Cloned Instance

Truncate Login Related Tables in NONLIVE Instance/Cloned Instance

This I generally do a post clone steps to purge the login related information.

$ADMIN_SCRIPTS_HOME/adopmnctl.sh stopall

truncate table APPLSYS.FND_LOGINS;
truncate table APPLSYS.FND_LOGIN_RESP_FORMS;
truncate table APPLSYS.FND_LOGIN_RESPONSIBILITIES;
truncate table APPLSYS.FND_UNSUCCESSFUL_LOGINS;
truncate table ICX.ICX_SESSIONS;
truncate table ICX.ICX_FAILURES;
truncate table ICX.ICX_SESSION_ATTRIBUTES;
truncate table ICX.ICX_TEXT;
truncate table ICX.ICX_CONTEXT_RESULTS_TEMP;
truncate table ICX.ICX_TRANSACTIONS;
truncate table APPLSYS.FND_SESSION_VALUES;

$ADMIN_SCRIPTS_HOME/adopmnctl.sh startall

Also if you are not able to execute purge signon conc prog, due to huge volume of data in the underlying tables then truncate is an option that you can pursue. OR you have to delete in multiple iterations.

Relevant Concurrent Program to Purge: Purge Signon Audit Data

Purge Inactive Sessions E-Business suite R12: $FND_TOP/sql/FNDDLTMP.sql

Performance Issue on a AP Workflow Approval(Custom Package)

Performance Issue on a AP Workflow Approval(Custom Package)

Few days back I got a chance to work for a performance issue on a AP Workflow Approval Package.
An adhoc request from the team, though there are huge scope of performance improvement on the whole database, I concentrated only on the AP Workflow Approval part.

Firstly I analysed AWRs for some days during business hours and noticed the following:
1. Multiple Procedures of the same package was present in "SQL ordered by Elapsed Time" and in "SQL ordered by CPU Time" with SQL IDs: v9yxuzfup5w3, 05jrjvak9tkry, 5cdb5xwmdggu2
2. Procedure of the same package was present in "SQL ordered by User I/O Wait Time" with SQL IDs: 05jrjvak9tkry
3. Multiple Procedures of the same package was present in "SQL ordered by Gets" with SQL IDs: 05jrjvak9tkry, 2v9yxuzfup5w3
4. Procedure of the same package was present in "SQL ordered by Reads" with SQL IDs: 05jrjvak9tkry

It is always a good practice that DBAs should look into the section of "SQL ordered by Gets" and tune them accordingly.
"SQL ordered by Gets" are always candidate of tuning and that is first thumb rule of SQL performance tuning based on AWR analysis.

In this case following two procedures were bottleneck for the whole package:
1. GET_APPROVER
2. AME_SUPERVISOR_LEVELS

So my initial target was to improve the performance for GET_APPROVER and AME_SUPERVISOR_LEVELS procedures. Needless to say both of these are present in "SQL ordered by Gets".

Now, there is a constraint  you cannot ask the developers to change the logic of the code for this kind of packages, as you never know, while changing what else can be screwed up. Also, Project manager may come to beat you :)
If something goes wrong business can be impacted, and thorough testing on a already live infrastructure is also not possible due to various constraints.

Started analysing the details of the two procedures, using SQLT and Explain Plan.

Solution#1: Reorder WHERE Clause for PER_ALL_ASSIGNMENTS_F/PER_ASSIGNMENTS_X in the Custom Code

First important observation from the analysis: Most of the bottleneck is coming from PER_ALL_ASSIGNMENTS_F.
It was noticed that, from the table PER_ALL_ASSIGNMENTS_F/PER_ASSIGNMENTS_X, the where clause in SELECT statement was very haphazard.
In some cases developers used the columns in where clause in one order and in other case it was in different order.
It is always a best practice, that first condition in the where clause should start with PK. So requested the developer to reorder the where cluase.

<<<<<<<<<<
You have used per_all_assignments_f/ per_assignments_x in various select statement.. Put the where clause in this order:
Select whatever from per_all_assignments_f/ per_assignments_x
Where 
person_id = …….
position_id = …..
assignment_type = …..
primary_flag = …..
effective_start_date = …..
effective_end_date = ……
supervisor_id = ….
>>>>>>>>>>>>>>

As this is a very minor change, and doesn't require any functional testing to be done, it is very much acceptable.
So this is the first change that is done.

Solution#2: CREATE Index on PER_ALL_ASSIGNMENTS_F, PAY_USER_ROWS_F, PAY_USER_COLUMNS, PAY_USER_TABLES

Now, started looking at what is happening inside the code, specially for FULL TABLE SCAN. This means that there is a need of INDEX. 

It is always good for DBAs to keep on checking to understand what are the tables having FULL TABLE SCAN
and if table size is high DBAs should take action to minimise that. Specially look for COST, CPU_COST and IO_COST.

Now a need has come to create an INDEX on PER_ALL_ASSIGNMENTS_F, as this a standard/very important table of core HR, Composite Index is always a safest bet.
As the developer has rearranged the where clause order in the whole package, just created a composite INDEX as follows:
CREATE INDEX HR.PER_ALL_ASSIGNMENTS_F_XXMKK_N1 ON HR.PER_ALL_ASSIGNMENTS_F 
(PERSON_ID, POSITION_ID , ASSIGNMENT_TYPE, PRIMARY_FLAG, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, SUPERVISOR_ID);

In the code, there were 3 more tables where existing Indexes(created as part of eBusiness Installation) were not sufficient:
1. PAY_USER_ROWS_F
2. PAY_USER_COLUMNS
3. PAY_USER_TABLES

Index need could have been eliminated(though not fully), if the where cluase was properly written/ordered, for example PAY_USER_COLUMNS select was done on following order:
USER_TABLE_ID, USER_COLUMN_NAME, USER_COLUMN_ID

Now let me explain, USER_TABLE_ID is FK, USER_COLUMN_ID is PK and USER_COLUMN_NAME is UK.
So in this case order should have been, USER_COLUMN_ID, USER_TABLE_ID, USER_COLUMN_NAME.

Now eBusiness by default has one index for USER_COLUMN_ID(PK), USER_TABLE_ID(FK1) and
PAY_USER_COLUMNS_UK2 UNIQUE (USER_COLUMN_NAME, USER_TABLE_ID, BUSINESS_GROUP_ID, LEGISLATION_CODE).

So it is difficult to utilise the above indexes mentioned.

Created following 3 indexes understanding the need(analysis was almost same with little difference for all three tables)

create index HR.PAY_USER_ROWS_F_XXMKK_N1 on HR.PAY_USER_ROWS_F (USER_TABLE_ID, USER_ROW_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE);
CREATE INDEX HR.PAY_USER_TABLES_XXMKK_N1 ON HR.PAY_USER_TABLES (USER_TABLE_ID, USER_TABLE_NAME);
CREATE INDEX HR.PAY_USER_COLUMNS_XXMKK_N1 ON HR.PAY_USER_COLUMNS (USER_TABLE_ID, USER_COLUMN_NAME, USER_COLUMN_ID);

Now the big question, how to make sure if this indexes are getting used or not. Requested the developer to test the performance of the approval package.

You can set the index usage monitoring in following way:
ALTER INDEX HR.PER_ALL_ASSIGNMENTS_F_XXMKK_N1 MONITORING USAGE;
SELECT * FROM V$OBJECT_USAGE;
ALTER INDEX HR.PER_ALL_ASSIGNMENTS_F_XXMKK_N1 NOMONITORING USAGE;

There is one more way to monitor the usage of INDEX, which I would incorporate in subsequent post.

Solution#3: De-fragment the tables periodically to reclaim unused space / improve performance

Should be done every quarter/half year

WF_LOCAL_ROLES is highly fragmented and causing a lot of performance problem, even I executed the most eligible candidate for de-fragmentation SQL and there is a huge benefit noticed for WF_LOCAL_ROLES.

ALTER TABLE APPLSYS.WF_NOTIFICATIONS MOVE;
ALTER TABLE APPLSYS.WF_LOCAL_ROLES MOVE;
ALTER TABLE APPLSYS.WF_LOCAL_USER_ROLES MOVE;
ALTER TABLE APPLSYS.WF_USER_ROLE_ASSIGNMENTS MOVE;
ALTER TABLE AP.AP_INVOICES_ALL MOVE;

ALTER TABLE <TABLE_NAME> ENABLE ROW MOVEMENT;
ALTER TABLE <TABLE_NAME> SHRINK SPACE CASCADE;
ALTER TABLE <TABLE_NAME> DISABLE ROW MOVEMENT;

I would like to mention one more point on AP Performance where AP.AP_LIABILITY_BALANCE, this table is also highly fragmented, if defrag is done, some other code would be benefited.

ALTER TABLE AP.AP_LIABILITY_BALANCE MOVE;

Solution#4: Rebuild Indexes for Better Performance/After Row Movement

Should be done every quarter/half year

You need to rebuild the indexes associated with these tables, as the status would be unusable.

SELECT TABLE_OWNER, INDEX_NAME FROM dba_ind_columns WHERE INDEX_NAME IN
(SELECT DISTINCT INDEX_NAME FROM dba_ind_columns WHERE TABLE_NAME IN
('WF_NOTIFICATIONS','WF_LOCAL_ROLES','WF_LOCAL_USER_ROLES', 'WF_USER_ROLE_ASSIGNMENTS', 'AP_INVOICES_ALL','AP_LIABILITY_BALANCE'))
ORDER BY INDEX_NAME;

alter index <owner>.<index_name> rebuild online;

Solution#5: Gather Stats On Relevant Tables

In Live, Gathers Stats conc program is scheduled to run every 15 days. There is no need to do this additionally, but every time 3/4 is done, it is recommended to perform.

exec fnd_stats.gather_table_stats('HR','PER_ALL_ASSIGNMENTS_F');
exec fnd_stats.gather_table_stats('HR','PER_ALL_PEOPLE_F');
exec fnd_stats.gather_table_stats('HR','PER_POSITION_EXTRA_INFO');
exec fnd_stats.gather_table_stats('HR','PAY_USER_ROWS_F');
exec fnd_stats.gather_table_stats('HR','PAY_USER_TABLES');
exec fnd_stats.gather_table_stats('AP','AP_INVOICES_ALL');
exec fnd_stats.gather_table_stats('HR','PAY_USER_COLUMNS');
exec fnd_stats.gather_table_stats('HR','PAY_USER_COLUMN_INSTANCES_F');
exec fnd_stats.gather_table_stats('APPLSYS','WF_LOCAL_ROLES');
exec fnd_stats.gather_table_stats('APPLSYS','WF_LOCAL_ROLES_TL');
exec fnd_stats.gather_table_stats('AP','AP_INV_APRVL_HIST_ALL');
exec fnd_stats.gather_table_stats('APPLSYS','WF_NOTIFICATIONS');
exec fnd_stats.gather_table_stats('APPLSYS','WF_LOCAL_USER_ROLES');
exec fnd_stats.gather_table_stats('APPLSYS','WF_USER_ROLE_ASSIGNMENTS');

And finally the result is good, from 50 seconds of approval time we could manage to take it down to 10 seconds.
There is a further scope of improvement and we can work towards to make below 5 seconds...

That would follow very soon, only the analysis part.....

LDAP Maximum Number of Connection Issue

Notice the following error in OIDLDAPD Logs($MW_HOME/asinst_1/diagnostics/logs/OID/oid1)
[2013-07-27T17:13:04+01:00] [OID] [ERROR:8] [23144] [OIDLDAPD] [host: mkktestserver1] [pid: 6070] [tid: 2] ServerListener : Exceeding maximum number of connections allowed. Num Conns = 1024, Max Conns = 1024, Closing the connection.
[2013-07-27T17:13:04+01:00] [OID] [ERROR:8] [23144] [OIDLDAPD] [host: mkktestserver1] [pid: 6070] [tid: 2] ServerListener : Exceeding maximum number of connections allowed. Num Conns = 1024, Max Conns = 1024, Closing the connection.

Even I notices over a period of time connections are not getting closed:
$ netstat -an | grep 3131 | wc -l
590
$ netstat -an | grep 3131 | wc -l
590
$ netstat -an | grep 3131 | wc -l
590

The "Max numbers of Connections reached" / "Exceeding maximum number of connections allowed" error in OID indicates that the oidldapd process has reached the limit of TCP connections that the Operating System (OS) will allow it to open. 

Solution

1. OID /em >>>  Click on oid1 >>> Select Administration >>> Server Properties >>> Performance tab
4. Enter value for LDAP Idle Connection Timeout (minute) field, for this case I entered 10 minutes.
5. Click on Apply button

Enterprise Manager is not showing Proper Status of OID Components

Enterprise Manager is not showing Proper Status of OID Components

In /em console the status of OID components are showing down, specifically DIP and Admin server.

AdminServer-diagnostic.log shows following error:
[2013-07-26T19:18:58.183+01:00] [AdminServer] [ERROR] [J2EE JMX-46335] [] [tid: DmsThread-15] [userId: <anonymous>] [ecid: 0000K06ty6w03zD_R9c9yd1HvKaN000002,0] [APP: NonJ2EEManagement#11.1.1] MBean attribute access denied. [[
MBean: oracle.as.management.mbeans.register:type=opmnInfoCenter
Getter for attribute InstanceNames
Detail: Access denied. Required roles: Admin, Operator, Monitor, executing subject: principals=[] java.lang.SecurityException: Access denied. Required roles: Admin, Operator, Monitor, executing subject: principals=[]
at oracle.as.jmx.framework.wls.spi.security.WLSMBeanSecurityHelper.isInWlsGlobalSecurityRoles(WLSMBeanSecurityHelper.java:245)

Solution

1. Stop OID Admin and Managed Server

2. Edit $DOMAIN_HOME/config/fmwconfig/servers/AdminServer/dms_config.xml using following contents:

Old Value
 <dumpConfiguration>
 <dump intervalSeconds="10800" maxSizeMBytes="75" enabled="true"/>
 </dumpConfiguration>

New Value
 <dumpConfiguration>
 <dump intervalSeconds="10800" maxSizeMBytes="75" enabled="false"/>
 </dumpConfiguration>

3. Stop OID Admin and Managed Server

Also apply the patch mentioned in FM 11g Control Console Shows Webcache or other Opmn-Managed Target As DOWN But It Is UP. (Doc ID 1501246.1 )

Error In Fusion Middleware Control Console After Patchset 11.1.1.6 - JMX-46335 (Doc ID 1477246.1)
OBIEE 11g: Error: "[ERROR] [J2EE JMX-46335]...[APP: NonJ2EEManagement] MBean attribute access denied." after Upgrade from 11.1.1.5 To 11.1.1.6 (Doc ID 1439413.1)
FM 11g Control Console Shows Webcache or other Opmn-Managed Target As DOWN But It Is UP. (Doc ID 1501246.1 )

Remote Diagnostic Agent(RDA) Analysis for WebLogic Admin/Managed Server

Remote Diagnostic Agent(RDA) Analysis for WebLogic Admin/Managed Server

RDA is a powerful tool to gather various important information about the setup in place. In this post I am discussing RDA for WebLogic Server.

1. Set Environmental Variables related to the domain to be profiled, by running: 
cd $DOMAIN_HOME/bin
. ./setDomainEnv.sh

2. Configure RDA to profile WebLogic Server related files:
cd $MW_HOME/oracle_common/rda
./rda.sh -S -p WebLogicServer

This will configure the RDA to include the relevant data for WLS. It will open a text wizard, will ask you some general questions, and set the environment for running a collection with WLS data.

3. Run RDA (with no parameters) to trigger the actual collection
cd $MW_HOME/oracle_common/rda
./rda.sh 

4. You can check then the output, on a browser. In the OUTPUT folder, look for "<YOUR_PREFIX>_start.htm".

This is very useful info for Oracle also to work on Service Request.

JVM Heap Tuning for OID Managed Server/WebLogic Server

JVM Heap Tuning for OID Managed Server/WebLogic Server

This is applicable for any weblogic Admin and Managed Server.
In $DOMAIN_HOME/servers/wls_ods1/logs/wls_ods1.log you would notice an error like this and OID managed server would go to UNKNOW status.

####<Jul 26, 2013 5:57:00 AM BST> <Error> <Kernel> <mkktestserver1.unixdomain.local> <wls_ods1> <[STUCK] ExecuteThread: '5' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <39cb4812cad10e7e:-5dbeb3ba:14006f69c2f:-8000-0000000000005522> <1374814620626> <BEA-000802> <ExecuteRequest failed
java.lang.OutOfMemoryError: Java heap space.
java.lang.OutOfMemoryError: Java heap space
>

Solution

From nodemanager.properties I can observe that we are using start up scripts to bring up OID servers. From start up script I can see we are using a maximum of 1024M of heap which might not be sufficient.

JVM Heap is configured in the following file:
$DOMAIN_HOME/bin/setDomainEnv.sh

Go to the following line:
EXTRA_JAVA_PROPERTIES="${EXTRA_JAVA_PROPERTIES} -Didm.oracle.home=${IDM_ORACLE_HOME} -Xms512m -Xmx1024m -Xss512K -Djava.protocol.handler.pkgs=oracle.mds.net.protocol -Dweblogic.management.discover=false"
export EXTRA_JAVA_PROPERTIES

Since we are using a 64 bit JDK, it is well worth to try update memory settings to:
EXTRA_JAVA_PROPERTIES="${EXTRA_JAVA_PROPERTIES} -Didm.oracle.home=${IDM_ORACLE_HOME} -Xms2048m -Xmx2048m -Xss512K -Djava.protocol.handler.pkgs=oracle.mds.net.protocol -Dweblogic.management.discover=false"
export EXTRA_JAVA_PROPERTIES

Keep monitoring wls_ods1.log for Java Heap Space Issue.

applive IDMLIVE /opt/oracle/IDMLIVE_MW_HOME/WebLogic/user_projects/domains/IDMDomain/bin $ diff setDomainEnv.sh.43 setDomainEnv.sh
372c372
< EXTRA_JAVA_PROPERTIES="${EXTRA_JAVA_PROPERTIES} -Didm.oracle.home=${IDM_ORACLE_HOME} -Xms512m -Xmx1024m -Xss512K -Djava.protocol.handler.pkgs=oracle.mds.net.protocol -Dweblogic.management.discover=false"
---
> EXTRA_JAVA_PROPERTIES="${EXTRA_JAVA_PROPERTIES} -Didm.oracle.home=${IDM_ORACLE_HOME} -Xms2048m -Xmx2048m -Xss512K -Djava.protocol.handler.pkgs=oracle.mds.net.protocol -Dweblogic.management.discover=false"
applive IDMLIVE /opt/oracle/IDMLIVE_MW_HOME/WebLogic/user_projects/domains/IDMDomain/bin $

Upgrade OAM 11.1.2.0 to OAM 11.1.2.1

Upgrade OAM 11.1.2.0 to OAM 11.1.2.1

Recently I have upgraded OAM from 11.1.2.0 to 11.1.2.1.. Sharing the steps.. hope would be helpful.....

1. Stop OAM Admin and Managed Server

2. Download OAM R2PS1 from edelivery: https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=15364663. Just get the files V37472-01.zip (Parts 1 and 2 both)

3. Execute runInstaller >>> specify the existing 11.1.2.0 MW_HOME so it goes into upgrade mode. Follow the instruction in GUI.

4. Upgrade OAM Schema and any other products in the domain using PSA.

. $HOME/oamenv.sh
cd $ORACLE_HOME/bin
./psa
Follow the instruction in the GUI.

5. Start OAM Admin Server Only

. $HOME/oamenv.sh
echo $DOMAIN_HOME
nohup $DOMAIN_HOME/bin/startWebLogic.sh -Dweblogic.management.username=weblogic -Dweblogic.management.password=MalayFalsePass > $HOME/oamweblogic.log 2>&1 &

6. Update System Mbean Configuration

. $HOME/oamenv.sh
cd $ORACLE_HOME/common/bin
./wlst.sh

connect()
Please enter your username [weblogic] : weblogic
Please enter your password [welcome1] : MalayFalsePass
Please enter your server URL [t3://localhost:7001] : t3://mkktestserver1.unixdomain.local:7004

patchUpgrade('/opt/oracle/OAMDEV_MW_HOME/WebLogic/Oracle_IDM1')
copyMbeanXmlFiles('/opt/oracle/OAMDEV_MW_HOME/WebLogic/user_projects/domains/OAMDomain','/opt/oracle/OAMDEV_MW_HOME/WebLogic/Oracle_IDM1')

7. Stop OAM Admin Server using WebLogic Console

8. Apply BP01 on top of OAM 11.1.2.1 or the latest available bundle patch.

OAM Bundle Patch Release History (Doc ID 736372.1)

9. Apply OAM Thread/CPU Utilisation Patch 16971881 and other recommended patches.

10. Start OAM Admin and Managed Server

Start the Oracle WebLogic Administration Server for OAM:
. $HOME/oamenv.sh
echo $DOMAIN_HOME
nohup $DOMAIN_HOME/bin/startWebLogic.sh -Dweblogic.management.username=weblogic -Dweblogic.management.password=MalayFalsePass > $HOME/oamweblogic.log 2>&1 &

Start WebLogic Managed Server for OAM:
. $HOME/oamenv.sh
echo $DOMAIN_HOME
nohup $DOMAIN_HOME/bin/startManagedWebLogic.sh oam_server1 http://mkktestserver1.unixdomain.local:7004 \
-Dweblogic.management.username=weblogic -Dweblogic.management.password=MalayFalsePass \
-Dsun.security.krb5.debug=true -Dsun.security.spnego.debug=true -Dweblogic.system.StoreBootIdentity=true > $HOME/oammanaged.log 2>&1 &

How to Determine the Port used for ODSM

How to Determine the Port used for ODSM

Open $MW_HOME/user_projects/domains/IDMDomain/config/config.xml and look for the following content.

    <name>wls_ods1</name>

    <complete-message-timeout>480</complete-message-timeout>

    <idle-connection-timeout>480</idle-connection-timeout>

    <idle-periods-until-timeout>8</idle-periods-until-timeout>

    <dgc-idle-periods-until-timeout>9</dgc-idle-periods-until-timeout>

    <machine>mkktestserver1.unixdomain.local</machine>

    <listen-port>7006</listen-port>

ODSM URL: http://mkktestserver1.unixdomain.local:7706/odsm

Consolidated Recommendations for eBusiness Suite Maintenance/Performance/Betterment

1. Apply Recommended Performance Patches
Oracle E-Business Suite Recommended Performance Patches [ID 244040.1]

2. Gather Schema Statistics Schedule
Make sure Gather Schema Stats is running on periodical basis, we recommend to scheduled to run every 15 Days.
Identify tables which are excessively updated/deleted, gather stats and do rebuild indexes on those tables on 3/4 days interval.

3. Verify Optimizer Statistics Over a Period of 15 Days
Over a period of 25 Days monitor the Optimizer Statistics using bde_last_analyzed.sql.

This generates the output in HTML format which contains the following files:
>> bde_last_analyzed_ind_partitions.html
>> bde_last_analyzed_indexes.html
>> bde_last_analyzed_summary.html
>> bde_last_analyzed_tab_partitions.html
>> bde_last_analyzed_tables.html

Make sure in the database there is NO Stale Statistics, if so, identify the objects from the reports above and arrange a table/index(object level) statistics for the affected objects.

File bde_last_analyzed.sql can be downloaded from Metalink Note: bde_last_analyzed.sql - Verifies CBO Statistics [ID 163208.1]

4. Database Initialization Parameter as per Oracle Recommendations
Execute bde_chk_cbo.sql as APPS user and analyze the output.
File bde_chk_cbo.sql can be download from Oracle Support Metalink Note: bde_chk_cbo.sql - EBS initialization parameters - Healthcheck [ID 174605.1]
This will confirm that all obsolete parameters are commented out from Database and EBS Database is using the latest parameter set recommended by Oracle.

5. Tuning the Requirement of UNDO Tablespace
Parameters related to system undo, the database releases certified for use with Oracle E-Business Suite Release 12 only support the use of system managed undo (SMU).
SMU is more efficient than traditional rollback segments and reduces the possibility of snapshot too old (ORA-1555) errors.

>> Undo tablespace become crucial for elapse times of the concurrent jobs, and corresponding commit windows.
>> Setting this parameter to a value higher than 900 (the default) is recommended if you experience "ORA-1555: Snapshot too old" errors.
>> Automatic undo is not supported for LOBS

Error Message in Log File:
Adding new assignments ...
SHRD0018: Oracle database error trapped at function: GLBMBO - label: glbmbo4_err
  ORA-01555: snapshot too old: rollback segment number 448 with name "_S

Current value of UNDO_RETENTION was 900, changed to 14400 sec(4 Hour)
SQL> ALTER SYSTEM SET UNDO_RETENTION = 14400 SCOPE=BOTH; [ Make changes in initfile/ifile, so that it preserves ]

As this is changed from default value of 900 to 14400 add some extra space to undo tablespace also:
SQL> ALTER TABLESPACE APPS_UNDOTS1 ADD DATAFILE '+SUP01' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;


5. Setting up TEMP Tablespace

It is recommended that the temporary tablespace for Oracle E-Business Suite users be created using locally managed temp files with uniform extent sizes of 128K. The 128K extent size is recommended because numerous modules, such as Pricing and Planning, make extensive use of global temporary tables which also reside in the temporary tablespace. Since each user instantiates a temporary segment for these tables, large extent sizes may result in space allocation failures.

The following is an example of creating a locally managed temporary tablespace with temp files:

SQL> drop tablespace temp;
SQL> create temporary tablespace temp tempfile '<Temp DBF Name with Location>' size 4096M reuse extent management local uniform size 128K;
Add 3 similar tempfiles.

6. Database Initialization Parameter Sizing

Following is required for 500+ active users in LIVE system. We have done it in many production systems and run very well:
*.sga_target = 14G
*.shared_pool_size = 3G
*.shared_pool_reserved_size = 300M
*.streams_pool_size = 150M
*.java_pool_size = 1G
*.pga_aggregate_target = 20G
*.result_cache_max_size = 150M
*.db_cache_size = 2G
*.large_pool_size=300M
*.db_keep_cache_size = 500M
*.db_writer_processes = 3
*.processes = 2500
*.sessions = 5000
*.open_cursors = 1500

7. Performance Improvement By Disabling "Canceling Long-Running Queries" Features

>> On occasion, a user may start a long-running query, grow tired of waiting, and close the browser. This section considers the implications of such an action, and describes a new feature that can help in this situation.
>> The middle-tier Forms Server process is basically single-threaded, and queries are normally executed in Blocking mode. If the client is killed, the Forms Server process still waits for the query to complete. This means that the Forms Server process will only time out when control has been returned from the database, at which point it will terminate when it can no longer communicate with the PC client. Cleanup will also take place for the database session.
>> The same principle applies in the case of a long-running transaction that calls a stored procedure from client-side PL/SQL. The key point is that in all such cases the query must run to completion before the middle tier process terminates and cleanup takes place.
>> If the query takes longer than about 5 seconds, a dialog box will appear that allows the user to cancel the query by clicking a Cancel button. This Cancel Query feature (sometimes called Non-blocking mode, as it is based on SQL*Net non-blocking functionality) is the only way to terminate a query without killing the Forms session.

>> Set the profile option FND:ENABLE_CANCEL_QUERY to Yes in order to enable this feature.
>> This can be set at site, application, responsibility or user level. Setting this profile to Yes also adds a call to set_form_property in the standard APPCORE when-new-form-instance event, which sets the INTERACTION_MODE property to NON_BLOCKING.
>> Some customers may wish to enable cancel query for power users only due to the additional overheads. In this case the profile should be set at User level for these users only.

8. Faster Processing of Workflow Events

The "Workflow Background Process" program needs to be submitted in various permutation and combination. This would help in minimizing the load by avoiding picking up error or stuck workflow. Also, user wait time would be reduced. This has other many advantages like visibility of roles etc.

Type-1: which Runs Every 3 Mins for Parameters:
Process Deferred: Yes
Process Timeout:  No
Process Stuck:  No

Type-2: which Runs Every 30 Mins for Parameters:
Process Deferred:  No
Process Timeout:  Yes
Process Stuck:  No

Type-3: which Runs Every 1 Hours for Parameters:
Process Deferred:  No
"Process Timeout:  No
Process Stuck:  Yes"

Type-4: which Runs Every 3 Hours for Parameters:
Process Deferred:  Yes
"Process Timeout:  Yes
Process Stuck:  Yes"

9. FNDOPP and FNDSM Tuning

Increase Parallel Threads of Standard Manager and Output Post Processor

Following measures are taken so that business users feels their requests are processing faster:
>> Sleep Seconds for Standard Manager is reduced to 20 Seconds
>> Number of Parallel threads for FNDSM is increased to 20
>> Number of Parallel threads for FNDOPP is increased to 6
>> Sleep Seconds for Output Post Processor is reduced to 20 Seconds

Increase Java Heap Size for OPP:

Determine what the heap size per OPP process is currently:
select DEVELOPER_PARAMETERS from FND_CP_SERVICES where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

The default should be: J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx512m

Increase the Heap Space per Process to 1024:
update FND_CP_SERVICES set DEVELOPER_PARAMETERS = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

Stop the managers, run CMCLEAN, start the managers back to have the changes reflected.

Recommendation: Reports built with large images will require additional space.  So please insure there is adequate temp space and your code is optimized when running large reports/templates.

Implement Greater Heap Size for Large Template Reports:

Navigation: Responsibility: System Administrator >>> Concurrent >>> Program >>> Define >>> Query for the concurrent program

Set the value for the Executable Options : -Xmx2048m

Advantages: After discussing with all the developers, higher heap size is assigned to some large template reports. This would minimize the risk on OPP running with out of memory and better performance.

Use XML Scalable Features for Better performance on .xls template reports:

Navigation: Responsibility: XML Publisher Administrator >>> Administration (Tab) >>> Configuration

Under Temporary Directory pick a temporary file location on your concurrent processing node. This should be at least 5GB or 20x larger than largest XML data file you generate

Under FO Processing, set:
>> Use XML Publisher's XSLT processor set to True 
>> Enable scalable feature of XSLT processor set to False
>> Enable XSLT runtime optimization set to True


9. Schedule Obsolete Session Removal Conc Program

Concurrent Program Name: Remove obsolete sessions from fnd_sessions
Parameters: NONE

It is common fact in Oracle Apps that some sessions eixsts in FND_SESSIONS, but actually from gv$session the process has vanished. Sometime process is defunct in Application level.
By cleaning this we would have better performance.

10. Index on All Custom Tables Created in Custom Schema

During the Implementation cycle, we have followed that all Custom Tables should have proper indexes, so that we have better performance on all custom table fetching application. All the custom columns that are in use should be indexed.

Following query is used to determine the Indexes on the Custom Table:
SQL> Select INDEX_NAME, TABLE_NAME, TABLE_TYPE from DBA_INDEXES where OWNER='XXDOT' and TABLE_NAME like 'XX%';

11. JVM/OC4J - Application Performance Optimization

JVM: Guidelines to setup the Java Virtual Machine in Apps Ebusiness Suite 11i and R12 (Doc ID 362851.1)

Start with 4 JVMS for each, with 1024MB space, for example:
      -Xmx1024M -Xms512M -XX:MaxPermSize=256M
      -XX:NewRatio=2 -XX:+PrintGCTimeStamps

12. Interpret Database AWR/Statspack Report

Tune all the relavant SQLs, specially most buffer gets and most elapsed time ones.
Take DBAs steps like, creating composite indexes, gather stats and similar steps.

DBAs should try to eliminate all the bottlenecks mentioned in AWR/ADDM.

13. Schedule Purge Obsolete Workflow Runtime Data

The program "Purge Obsolete Workflow Runtime Data" calls database package WF_Purge.Total.  This program deletes old Workflow runtime data for completed Workflows. It is recommended that this program or package is run regularly as required.

From Workflow Administrator's Guide PDF Document:
The Oracle Applications Manager console helps you easily maintain the Oracle Workflow and Oracle XML Gateway database tables. Oracle Workflow and Oracle XML Gateway access several tables that can grow quite large with obsolete workflow information that is stored for all completed workflow processes, as well as obsolete information for XML transactions. The size of these tables and indexes can adversely affect performance. These tables should be purged on a regular basis, using the Purge Obsolete Workflow Runtime Data concurrent program.

Parameter Explanation:
Item Type: The item type to purge. Leaving this field blank defaults to purging the runtime data for all item types.
Item Key: The item key to purge. Leaving is field blank defaults to purging the runtime data for all item keys.
Age: Minimum age of data to purge, in days.
Persistence Type: The persistence type to be purged, either for Temporary or for Permanent. The default is temp.
Core Workflow Only: Enter 'Y' to purge only obsolete runtime data associated with work items, or 'N' to purge all obsolete runtime data as well obsolete design data. The default is 'N'.
Commit Frequency: Enter the number of records to purge before the program commits data. To reduce rollback size and improve performance, set this parameter to commit data after a smaller number of records. The default is 500 records.
Signed Notifications: Enter 'N' to preserve signature evidence, including notifications that required electronic signatures and their associated signature information. Enter 'Y' to purge signature-related information. The default is 'N'.

Note: After performing a commit, the program resumes purging work items with the next subsequent begin date. In some cases, if additional items have the same begin date as the last item that was purged before a commit, the program may not purge all eligible items. To purge these remaining work items, simply rerun the program.


14. OS Watcher to Verify Hardware Performance

OSWatcher Black Box Analyzer User Guide (Doc ID 461053.1)

15. Requirement of Flushing Shared Pool

On systems which use a lot of literal SQL the shared pool is likely to fragment over time such that the degree of concurrency which can be achieved diminishes. Flushing the shared pool will often restore performance for a while as it can cause many small chunks of memory to be coalesced. After the flush there is likely to be an interim spike in performance as the act of flushing may remove sharable SQL from the shared pool but does nothing to improve shared pool fragmentation. The command to flush the shared pool is:

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

Contrary to reports elsewhere items kept in the shared pool using DBMS_SHARED_POOL.KEEP will NOT be flushed by this command. Any items (objects or SQL) actually pinned by sessions at the time of the flush will also be left in place.

NB: Flushing the shared pool will flush any cached sequences potentially leaving gaps in the sequence range. DBMS_SHARED_POOL.KEEP('sequence_name','Q') can be used to KEEP sequences preventing such gaps. 

16. Forms Dead Client Detection

>> Enable Forms Dead Client Detection using FORMS_TIMEOUT=10 (Value specified in minutes). Default 30 Minutes is two high and can cause performance issue.
>> Terminates fwebmx processes for dead clients.
>> Enable Forms Abnormal Termination Handle
>> Do not set FORMS_CATCHTERM
>> The above two variables ( FORMS_TIMEOUT and FORMS_CATCHTERM ) can be changed from CONTEXT_FLE XML.
>> In CONTEXT_FLE these parameters can be found as s_f60catchterm and s_f60time parameters for 11i users, s_forms_catchterm and s_forms_time for R12 users.

>> Developers have reviewed the Note Few Basic Techniques to Improve Performance of Forms. [ID 221529.1] for all Custom forms developed to maximize performance

Reference Metalink Notes: Oracle Forms in Applications FAQ [ID 177610.1]

17. Maintenance on Concurrent Request Related Tables/Data

Follow all the recommendations mentioned in following notes:
http://mkkoracleapps.blogspot.co.uk/2013/08/performance-tuning-on-ebs-concurrent.html

Ensure "Purge Concurrent Request and/or Manager Data, FNDCPPUR,"  is run at regular intervals with "Entity" parameter as "ALL".  A high number of records in FND_CONCURRENT tables can degrade the performance.
Functions of FNDCPPUR: It purges data on FND_CONCURRENT tables and log/output files.

Name: Purge Concurrent Request and/or Manager Data
Short Name: FNDCPPUR
Description: Purges Concurrent requests and/or Concurrent Manager data and log/output files

1. Entity: ALL
2. Mode: Age
3. Mode Value : 10
4. Oracle ID: NULL
5. User Name: NULL
6. Responsibility Application: NULL
7. Responsibility: NULL
8. Program Application: NULL
9. Program: NULL
10. Manager Application: NULL
11. Manager: NULL
12. Report: Y

13. Purge Other: Y

Various URLs accessible for eBusiness

Various URLs accessible for eBusiness

Various URLs that you can test for eBusiness to verify that login/forms related components are working fine. This you can use to troubleshoot login/forms related issues with tracing on.

http://<server>.<domain>:<PORT>/OA_HTML/ServletPing 
http://<server>.<domain>:<PORT>/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE 
http://<server>.<domain>:<PORT> 
http://<server>.<domain>:<PORT>/OA_HTML/jsp/fnd/aoljtest.jsp 
http://<server>.<domain>:<PORT>/OA_MEDIA/FNDLOGOL.gif 
http://<server>.<domain>:<PORT>/forms/frmservlet 

How to determine if OACore JVM Sizing is Appropriate or Not

How to determine if OACore JVM Sizing is Appropriate or Not

cd $LOG_HOME/ora/10.1.3/opmn/
ls -ltr default_group~oacore*

grep 'Full GC' default_group~oacore* | wc -l
grep 'Unloading class' default_group~oacore* | grep 'Full GC' | wc -l

If you see there are multiple cases when Full GC is happening then, it is understood that JVM sizing in not proper.

Take following actions for this:
1. Increase the Number of OACore JVMs
2. Increase the JVM Heap Size
3. Make sure you are not using many parallel thread for GC.

Read the following for Sizing recommendations:
http://mkkoracleapps.blogspot.co.uk/2013/07/modoc4j-failed-to-find-failover-oc4j.html

Flush Shared Pool and Buffer Cache

SQL> ALTER PACKAGE FND_SSO_REGISTRATION COMPILE SPECIFICATION;
Package altered.

SQL> ALTER PACKAGE FND_SSO_REGISTRATION COMPILE BODY;
Package body altered.

SQL> ALTER PACKAGE FND_LDAP_WRAPPER COMPILE SPECIFICATION;
Package altered.

SQL> ALTER PACKAGE FND_LDAP_WRAPPER COMPILE BODY;
Package body altered.

SQL> show user;
USER is "SYS"

SQL> alter system flush buffer_cache;
System altered.

SQL> alter system flush shared_pool;
System altered.