Performance tuning on OID - Consolidated Details

Performance tuning on OID - Consolidated Details

OID_Perf_Reco_1: Set DSA config to skip referrals

This is applicable when there are no referrals setup in OID.

By default this capability IS ENABLED and severely impacts performance when large groups (>200K) or large number of nested groups are involved.

Definition: A referral is a special type of entry that when obtained in a search, it contains the location of the actual entry, which could be in another part of the directory tree or even in another ldap server altogether. Unless you have specifically set-up referrals you most likely do not have any.

Confirm, there is no "Referrals" in place in OID:
ldapsearch -h mkkoidserver1 -p XXXX -D "cn=orcladmin" -w "xxxxxxxxxxxxx" -s sub -b "" objectclass=referral

If this doesn't return any rows then, there are no referrals in place.

Set the value of orclskiprefinsql in DSA config, to 1. This would make DSA config to skip referrals.

ldapmodify -h mkkoidserver1 -p XXXX -D cn=orcladmin -w xxxxxxxxxxxxx << eof
dn: cn=dsaconfig,cn=configsets,cn=oracle internet directory
changetype: modify 
replace: orclskiprefinsql 
orclskiprefinsql: 1 
eof



OID_Perf_Reco_2: orclinmemfiltprocess is very expensive on Oracle Database

This attribute can help significantly with the performance of certain types of search operations. It has been identified to be particularly useful with OAM, as some of the searches OAM performs can be especially expensive in the database without the use of "orclinmemfiltprocess".

ldapmodify -h mkkoidserver1 -p XXXX -D "cn=orcladmin" -w "xxxxxxxxxxxxx" -v <EOF 
dn: cn=dsaconfig,cn=configsets,cn=oracle internet directory 
changetype: modify 
replace: orclinmemfiltprocess 
orclinmemfiltprocess:(|(!(obuseraccountcontrol=*))(obuseraccountcontrol=activated)) 
orclinmemfiltprocess:(|(obuseraccountcontrol=activated)(!(obuseraccountcontrol=*))) 
orclinmemfiltprocess:(obapp=groupservcenter)(!(obdynamicparticipantsset=*)) 
orclinmemfiltprocess:(objectclass=oblixworkflowinstance) 
orclinmemfiltprocess:(objectclass=inetorgperson) 
orclinmemfiltprocess:(objectclass=oblixorgperson) 
orclinmemfiltprocess:(objectclass=oblixworkflowstepinstance) 
EOF

For OID 11g it should come as default, cross check and apply, it it applicable for you.

OID_Perf_Reco_3: Run oidstats.sql Regularly as part of daily housekeeping/maintenance

Run oidstats.sql any-time large updates are made to the OID. For large Active Directories, where changes are very frequent, and AD to OID to synchronisation is enabled, this is a very good option to perform on regular basis.

. $HOME/oidenv.sh
cd $MW_HOME/Oracle_IDM1/ldap/admin
sqlplus ods/xxxxxxxxxxxxx@OIDDB
START oidstats.sql;

Remove Dangling DNs: http://mkkoracleapps.blogspot.co.uk/2013/10/remove-dangling-dns-from-oid.html

Removing Configured WebLogic Server Domain

How to remove a domain from a WebLogic Server Installation... As it is just a domain deployment, no tool is required, just remove the appropriate content as given below.....

Make sure you are keeping a proper backup before performing the steps mentioned below:

1. Remove the domain directory $MW_HOME/user_projects/domains/<Domain_Name>
Here in this example I am removing: eag_domain, so issue the following command,

rm -rf $MW_HOME/user_projects/domains/eag_domain

2. Remove the line for eag_domain from domain-registry.xml file

$ cat domain-registry.xml
<?xml version="1.0" encoding="UTF-8"?>
<domain-registry xmlns="http://xmlns.oracle.com/weblogic/domain-registry">
  <domain location="/opt/oracle/OAMLIVE_MW_HOME/WebLogic/user_projects/domains/OAMDomain"/>
  <domain location="/opt/oracle/OAMLIVE_MW_HOME/WebLogic/user_projects/domains/eag_domain"/>


3. Remove the line for eag_domain from nodemanager.domains

cd $MW_HOME/wlserver_10.3/common/nodemanager

$ cat nodemanager.domains
#Domains and directories created by Configuration Wizard
#Fri Jul 12 11:44:27 BST 2013
eag_domain=/opt/oracle/OAMLIVE_MW_HOME/WebLogic/user_projects/domains/eag_domain
OAMDomain=/opt/oracle/OAMLIVE_MW_HOME/WebLogic/user_projects/domains/OAMDomain

TABLESPACE Monitoring

Useful SQLs to monitor tablespace

Find the Tablespace which having less than 5% space left

This query will give the output for APPS_TS_TX_DATA & APPS_TS_TX_IDX tablespaces:
select trunc(sysdate), c.name, b.tablespace_name, tbs_size, tbs_size - a.free_space, a.free_space, round(a.free_space / tbs_size * 100, 0) per_free
from  (select tablespace_name, round(sum(bytes)/1024/1024/1024 ,2) as free_space from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size from dba_data_files group by tablespace_name) b, (select name from v$database) c
where a.TABLESPACE_NAME(+)=B.TABLESPACE_NAME and a.TABLESPACE_NAME like 'APPS_TS_TX%' and round(a.free_space / tbs_size * 100, 0) <= 5 order by 6;

This query will give the output for all tablespaces:
select trunc(sysdate), c.name, b.tablespace_name, tbs_size, tbs_size - a.free_space, a.free_space, round(a.free_space / tbs_size * 100, 0) per_free
from  (select tablespace_name, round(sum(bytes)/1024/1024/1024 ,2) as free_space from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size from dba_data_files group by tablespace_name) b, (select name from v$database) c
where a.TABLESPACE_NAME(+)=B.TABLESPACE_NAME and round(a.free_space / tbs_size * 100, 0) <= 5 order by 6;

Tablespace Percentage(%) Used

It will give How much Percentage(%) is already used for a Particular Tablespace

SET LINESIZE 200

SELECT A.TABLESPACE_NAME, ROUND(A.BYTES/1024/1024) "TOTAL", ROUND(B.BYTES/1024/1024) "USED", ROUND(C.BYTES/1024/1024) "FREE",

ROUND((B.BYTES*100)/A.BYTES) "% USED", ROUND((C.BYTES*100)/A.BYTES) "% FREE"

FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME AND A.TABLESPACE_NAME='&PLEASE_PROVIDE_TABLESPACE_NAME';

Add Datafile to Tablespace

SELECT * FROM dba_tablespace_usage_metrics ORDER BY used_percent;

SELECT max(bytes) FROM dba_free_space WHERE tablespace_name = 'APPS_TS_TX_DATA'; 

SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='<TS_NAME>' ORDER BY FILE_NAME;

ALTER TABLESPACE <TABLE_SPACE_NAME> ADD DATAFILE '<DBF_NAME_WITH_LOCATION>' SIZE 4096M;

ALTER TABLESPACE <TABLE_SPACE_NAME> ADD DATAFILE '<DBF_NAME_WITH_LOCATION>' SIZE 4096M autoextend on;

Alter database datafile '/xxxxx/xxxx/zpbdxx.dbf' autoextend on;

select ts.name||'|'||df.name||'|'||bytes/1024/1024||'|'||CREATE_BYTES/1024/1024 from v$tablespace ts, v$datafile df where ts.ts#=df.ts# and ts.name ='&TABLESPACE_NAME';

ALTER TABLESPACE <TS_NAME> ADD DATAFILE '<DBF_NAME_WITH_PATH>' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;

ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '/u01/UAT4/oracle/db/apps_st/data/APPS_TS_TX_IDX_002.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;

Add Datafile to Tablespace Using Autoextent ON

ALTER TABLESPACE <TS_NAME> ADD DATAFILE '<DBF_NAME_WITH_PATH>' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;

ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '/u01/UAT4/oracle/db/apps_st/data/APPS_TS_TX_IDX_002.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;

Resize Datafile

SQL> alter database datafile '/prod/oradata/custom01/prod/ts_custom_data13.dbf' resize 4096m;

SQL> alter database datafile '/prod/oradata/custom01/prod/ts_custom_data13.dbf' modify autoextend by 50m;

Using staticports.ini for Oracle HTTP Server during OAM/EBS Integration

Using staticports.ini for Oracle HTTP Server during OAM/EBS Integration

Instead of 7777 of HTTP Server port, we had a requirement for 7778... 

HTTP WebTier Version 11.1.1.6

Create a file staticports.ini before starting the installation, and choose manual port configuration:

[OPMN]
OPMN Local Port = 6706
OPMN Remote Port = 6707

[OHS]
OHS Port = 7778
OHS Proxy Port = 9998
OHS SSL Port = 4444

[WEBCACHE]
Web Cache Listen Port = 7790
Web Cache Admin Port = 7791
Web Cache Statistics Port = 7792
Web Cache Invalidation Port = 7793
Web Cache SSL Port = 7794

appdev WEBTIERDEV /export/home/appdev $ opmnctl status -l

Processes in Instance: instance1
---------------------------------+--------------------+---------+----------+------------+----------+-----------+------
ias-component                    | process-type       |     pid | status   |        uid |  memused |    uptime | ports
---------------------------------+--------------------+---------+----------+------------+----------+-----------+------
webcache1                        | WebCache-admin     |   11620 | Alive    | 1319634190 |    12896 |  90:03:21 | http_admin:7791
webcache1                        | WebCache           |   11619 | Alive    | 1319634189 |    35176 |  90:03:21 | http_stat:7792,http_invalidation:7793,https_listen:7794,http_listen:7790
ohs1                             | OHS                |   11618 | Alive    | 1319634188 |     4680 |  90:03:21 | https:9998,https:4444,http:7778

appdev WEBTIERDEV /export/home/appdev $

Data Source/JDBC Connection Pool tuning for eBusiness AccessGate

Data Source/JDBC Connection Pool tuning for eBusiness AccessGate

I noticed, suddenly, eBusiness Access through Oracle Access Manager stopped working. When I looked into AccessGate Managed server log, I found the following informations.

Jul 30, 2013 10:16:52 AM oracle.apps.fnd.ext.sso.AppsHttpServletRequestWrapper <init>
SEVERE: Cannot retrieve LDAP information for guid='NOT_FOUND'
Jul 30, 2013 10:17:35 AM oracle.apps.fnd.ext.sso.EbsServer$2 load
SEVERE: Failed to get server connection server=113
weblogic.jdbc.extensions.PoolLimitSQLException: weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool EBSLIVE to allocate to applications, please increase the size of the pool and retry..
at weblogic.jdbc.common.internal.JDBCUtil.wrapAndThrowResourceException(JDBCUtil.java:252)
at weblogic.jdbc.common.internal.RmiDataSource.getPoolConnection(RmiDataSource.java:456)

Solution

This is purely data source related issue. When you deploy AccessGate for eBusiness Suite, the ANT deployment create the data source also.

Login to WebLogic Console >>> Services >>> Data Source >>> Click on the Data Source EBSLIVE >>> Connection Pool Tab >>> Advanced

Set Initial Capacity = Max capacity of JDBC connection pool
Set Inactive Connection Timeout to 5 Minutes
Increase Maximum Capacity Value from Default Value of 15 to 30.

Restart AccessGate Managed and Admin Server....

How to Start Complete OAM Fusion Middleware Stack

How to Start Complete OAM Fusion Middleware Stack

Start OID Components:

Start the Oracle WebLogic Administration Server for OID:
. $HOME/oidenv.sh
echo $DOMAIN_HOME
nohup $DOMAIN_HOME/bin/startWebLogic.sh -Dweblogic.management.username=weblogic -Dweblogic.management.password={{{WebLogicPass}}} > $HOME/oidweblogic.log 2>&1 &

Start OID OPMN Component:
. $HOME/oidenv.sh
opmnctl startall
opmnctl status
opmnctl status -l

Start WebLogic Managed Server for OID:
. $HOME/oidenv.sh
nohup $DOMAIN_HOME/bin/startManagedWebLogic.sh wls_ods1 http://mkkoamoidserver1:7001 -Dweblogic.management.username=weblogic -Dweblogic.management.password={{{WebLogicPass}}} -Dweblogic.system.StoreBootIdentity=true > $HOME/oidmanaged.log 2>&1 &

**************************

Start OAM Components:

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={{{WebLogicPass}}} > $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://mkkoamoidserver1:7002 \
-Dweblogic.management.username=weblogic -Dweblogic.management.password={{{WebLogicPass}}} \
-Dsun.security.krb5.debug=true -Dsun.security.spnego.debug=true -Dweblogic.system.StoreBootIdentity=true > $HOME/oammanaged.log 2>&1 &

****************************


Start HTTP WebTier:

. $HOME/webtierenv.sh
opmnctl startall
opmnctl status
opmnctl status -l

***************************


Start EBS Access Gate:

Start WebLogic AdminServer:
. $HOME/eagenv.sh
echo $DOMAIN_HOME

nohup $DOMAIN_HOME/bin/startWebLogic.sh -Dweblogic.http.isWLProxyHeadersAccessible=true \
-Dweblogic.management.username=weblogic -Dweblogic.management.password={{{WebLogicPass}}} > $HOME/eagweblogic.log 2>&1 &

Start WebLogic Managed Server:
. $HOME/eagenv.sh
echo $DOMAIN_HOME

nohup $DOMAIN_HOME/bin/startManagedWebLogic.sh eag_server1 http://mkkoamoidserver1:7041 \
-Dweblogic.http.isWLProxyHeadersAccessible=true \
-Dweblogic.management.username=weblogic -Dweblogic.management.password={{{WebLogicPass}}} \
-Dweblogic.system.StoreBootIdentity=true > $HOME/eagmanaged.log 2>&1 &

******************************

You have encountered an unexpected PLSQL Error, Please contact System Administrator

You have encountered an unexpected PLSQL Error, Please contact System Administrator

Justification: Cloning has de-registered the EBS instance from OID, and that is the reason, Autolink of user from EBStoOID is not happening.

If you manually update the user with the GUID information, single sign-on is working fine.

When enabled FND Debug, in fnd_log_messages I noticed the following:

106500486|fnd.plsql.oid.fnd_ldap_util.get_oid_session: |ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials|

106500487|fnd.plsql.oid.fnd_ldap_util.c_get_oid_session: |ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials|

106500488|fnd.plsql.oid.fnd_oid_plug.get_realm_dn: |END ->ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials|

106500489|fnd.plsql.oid.fnd_ldap_wrapper.create_user: |ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials|

**** This happened during one of my GOLIVE and during the cutover we window we had the cloning of production is also involved...

**** Solution is straight forward... OID component has been deregistered during the cloning process...

Register using following command:

$FND_TOP/bin/txkrun.pl -script=SetSSOReg -registeroid=yes -provisiontype=4

How to Turn Archiving OFF/ON

How to Turn Archiving OFF/ON

1. Shut down the database instance
SQL> shutdown immediate

2. Start up a new instance and mount, but do not open the database
SQL> startup mount

3. Put the database into archivelog mode/noarchivelog mode
SQL> alter database noarchivelog;
SQL> alter database archivelog;                                  >>>>>>>>>>> IF YOU NEED TO TURN ON ARCHIVING

4. Open the database
SQL> alter database open; 

5. Verify your database is now in noarchivelog/archivelog mode.
SQL> ARCHIVE LOG LIST
Database log mode                   Archive Mode
Automatic archival                    Disabled
Archive destination                   USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence         22
Next log sequence to archive      24
Current log sequence                 24

SQL> alter system switch logfile;

RMAN Archive Log Delete - Ready Reference

RMAN Archive Log Delete - Ready Reference

Archive Log Location: 


select name from SYS.V_$ARCHIVED_LOG;

I use the following archive log delete command, and that solve my purpose:

RMAN> delete noprompt archivelog all completed before 'SYSDATE-3' backed up 01 times to device type disk;
RMAN> DELETE FORCE NOPROMPT OBSOLETE RECOVERY WINDOW OF 2 DAYS DEVICE TYPE DISK;
RMAN> DELETE FORCE NOPROMPT OBSOLETE REDUNDANCY = 2 DEVICE TYPE DISK;
RMAN> DELETE NOPROMPT FORCE ARCHIVELOG UNTIL TIME 'SYSDATE-1' DEVICE TYPE DISK;
RMAN> DELETE NOPROMPT FORCE ARCHIVELOG UNTIL TIME 'SYSDATE-1/2' DEVICE TYPE DISK;
RMAN> DELETE NOPROMPT FORCE ARCHIVELOG UNTIL SEQUENCE 260 DEVICE TYPE DISK;


Archive Log List:

RMAN> list archivelog all;
RMAN> list copy of archivelog until time ‘SYSDATE-10′;
RMAN> list copy of archivelog from time ‘SYSDATE-10′
RMAN> list copy of archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN> list copy of archivelog from sequence 1000;
RMAN> list copy of archivelog until sequence 1500;
RMAN> list copy of archivelog from sequence 1000 until sequence 1500;

Archive Log Delete:

RMAN> delete archivelog all;
RMAN> delete archivelog until time ‘SYSDATE-10′;
RMAN> delete archivelog from time ‘SYSDATE-10′
RMAN> delete archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN> delete archivelog from sequence 1000;
RMAN> delete archivelog until sequence 1500;
RMAN> delete archivelog from sequence 1000 until sequence 1500;

Oracle Access Manager 11gR2 for eBusiness R12.1.3

Oracle Access Manager 11gR2 for eBusiness R12 12.1.3

Two weeks back, I completed another Access Manager Implementation to provide zero sign to eBusiness Suite 12.1.3.


I have used following versions for this implementation:

Oracle Access Manager: 11.1.2.0
Oracle Identity Management: 11.1.1.6
Oracle Access Manager WebGate: 11.1.2.0
Oracle E-Business Suite AccessGate: 1.2
Oracle Fusion Middleware WebTier 11.1.1.7
Oracle Database for RCU: 11.2.0.3
Oracle E-Business Suite Release 12: 12.1.3

• For Windows Native Authentication we had Windows Server 2003 AD.
• Most of the client Machines are Windows 7, also tested from Mac OS.

Another level of complexity was to integrate Oracle Access Manager with Microsoft UAG. For external users UAG is delegating the Kerberos Authentication with WNA CredCollector on behalf of user.

• AD to OID Synchronisation with, of more than 70K records.
• External Authentication Plugin Set-up AD ldapbind and AD ldapcompare) in case, WNA is not operational.

• In front of OAM, F5 Load balancer and similarly for access gate another F5 Load balancer  SSL termination is happening at F5 Level. As UAG is in picture, Proxy Header SSL is also implemented.

Also, in one of the Development Server 2 Instances of Test OAM Installation is really a good experience. Still I am struggling to find the reason, let me call it as Installation#1(first installation) and Installation#2.

If I start Installation#2 after server reboot, then I am unable to start Installation#1. Reverse is not true.

Another good experience is Playing with KVNO. If you set password for a user, used for keytab file, multiple time, KVNO goes into three digit and kerberos would not work, until and unless you mention -kvno in the command line argument.

Though in terms of Identity and Access Management it was 5th Implementation Project, but for various complexities it was pleasant experience to explore.