Rooting out the problem: Integration of OID with EBS

This was a tough one. Kudos to my colleague Anil in Bangalore for getting to the bottom of it. Also thanks once again to our friends at Oracle Apps DBA for their willingness to share.

This started out as the typical “ldap wrapper error” type deal. One of my user admins wasn’t able to create a user on EBS, and after checking that password changes were also being rejected (without the usual error message), I dove in and tried the usual list of things. The one thing I noticed right away was that although users were able to SSO in to EBS, and their orclguid was being pushed into the corresponding account on EBS (USER_GUID field), no entry was being created for them under “cn=ACCOUNTS,cn=subscription_data” for the EBS node in question.

After finding nothing relevant on Metalink, Anil did a bit of googling and came up with this article, which turns out to be the best treatment of EBS integration troubleshooting I’ve seen.

The steps were many, but effective in the aggregate:

1. Restart odisrv with trace enabled. Doing this is described in another Apps DBA article linked in the main post. Here’s the command line syntax:

oidctl connect=myinfradb 
server=ODISRV 
instance=1 
flags="port=389 debug=63" 
start

Note: You can get the same effect by going up to the provisioning profile node in question on OID and changing the value for orclodipprofiledebuglevel to 63.

[Look under
“cn=provisioning profiles,cn=changelog subscriber,
cn=oracle internet directory” for the orclODIPProfileName node you want to manipulate.
]

2a. Check to see if the password used by OID to connect to EBS has expired. By default this is APPS. I usually just do a simple sqlplus APPS/xxxxx@ebsdbname for this, which will also reveal any listener or other connectivity issues.

2b. Check to see if the password used by EBS to connect to OID has expired. By default this user will be something like:

“orclapplicationcommonname=[ebs db],cn=ebusiness,
cn=products,cn=oraclecontext,dc=example,dc=com”

From the EBS side you need to connect using sqlplus and run the following:

SELECT fnd_preference.GET('#INTERNAL', 'LDAP_SYNCH','USERNAME') 
Apps_Instance_OID_Account FROM dual;
 
SELECT fnd_preference.eget('#INTERNAL', 'LDAP_SYNCH','EPWD','LDAP_PWD') 
Apps_Password FROM dual;

A blank value for APPS_PASSWORD indicates your integration is borked and you’ll need to re-integrate.

If a value does display you’ll use it to test the validity of the account displayed in the first SQL query against LDAP (usually by using ldapsearch or Oracle’s own ldapbind utility — “ldpabind -h [oidhost] -D [dn displayed for APPS_INSTANCE_OID_ACCOUNT] -w [APPS_PASSWORD]”).

If the password isn’t good you’ll see an ugly error message. You can also verify these passwords by dialing up the OID entry for the EBS node under the realm (“orclapplicationcommonname=[ebs db],…”).

Where the OID side password has expired you can usually fix by just updating it as the root or realm orcladmin (it should be set to the same value as returned from the EBS query).

In our case we got a blank value from the password query on EBS. As a result we recommended that the instance be de-integrated (in this case by manually removing all related OID nodes), having the Apps DBA’s run the “remove references” procedure (using the txkrun.pl script) and then proceeding to re-register both OID and SSO (for EBS R12 the script normally does both in sequence).