Quick tip: Moving an SSO-integrated E-Business Suite database

Whoops

It seemed simple enough. We were moving our E-Business Suite database to a new server. References to the old database server had to be replaced in a variety of places: Oracle Internet Directory, a handful of tnsnames.ora files, and of course, the context files on the application tier servers. After that, everyone could connect to the database just as they always had. No problems, right?

Well, okay, maybe one small problem: This E-Business Suite system was integrated with Oracle Single Sign-On, and provisioning of users from OID to EBS was not working. Existing users could log in to Oracle Apps, but password changes weren't propagating, and new users in OID were not being created in E-Business Suite. The provisioning trace files on the OID server, located in $ORACLE_HOME/ldap/odi/log, reported the following sort of errors:

Exception Connecting to DB :java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
Error initialzing ProvPLSQLWriter - ODIException: ODI Exception while Initialising PLSQL Writer : DIP_GEN_SPACE_STR
ODIException: Error initialzing ProvPLSQLWriter - ODIException: ODI Exception while Initialising PLSQL Writer : DIP_GEN_SPACE_STR
        at oracle.ldap.odip.prov.ProvPLSQLWriter_2_0.initialise(ProvPLSQLWriter_2_0.java:69)
        at oracle.ldap.odip.prov.ProvOIDToAppSync_2_0.initialise(ProvOIDToAppSync_2_0.java:154)
        at oracle.ldap.odip.engine.ProvThread.runOldVersion(ProvThread.java:534)
        at oracle.ldap.odip.engine.ProvThread.run(ProvThread.java:173)
Error initialzing ProvOIDToAppSync: ODIException: Error initialzing ProvPLSQLWriter - ODIException: ODI Exception while Initialising PLSQL Writer : DIP_GEN_SPACE_STR

Clearly, I had missed a necessary and embarrassingly obvious change in OID. An update to part of the Apps database service identifier (e.g. changing the hostname of the database server) requires an update to the database connect string used by the OID-to-EBS provisioning profile. Otherwise, the provisioning process will be unable to connect to the relocated E-Business Suite database server to update the FND_USER table.

Now what?

I called this an "embarrassingly obvious" miss because the process used to fix the problem is the same one used whenever the Apps password is changed in an SSO-integrated E-Business Suite environment, clearly documented in Section 6.16 of Oracle Application Server 10g with Oracle E-Business Suite Release 11i Troubleshooting. That's a My Oracle Support Note, so I can't reproduce the content here, but in a nutshell, the solution is to use oidprovtool to change the interface_connect_info string for the provisioning profile for the EBS instance in question. After the hostname in the string had been corrected, changes to user records in OID were once again propagating to the EBS database, and new users were able to log in to Oracle Apps.

Two additional notes:

  • Interestingly enough, the R12 version of the My Oracle Support troubleshooting Note, Oracle Application Server 10g with Oracle E-Business Suite Release 12 Troubleshooting, makes no mention of using oidprovtool to change the connection information used by the provisioning profile, but the method described in the 11i note appears to be valid for R12 as well.
  • The "How To" sections of both the 11i and R12 versions of the My Oracle Support notes explain how to dump a list of provisioning profiles to get the application DN required to run oidprovtool.

Hope this helps someone out there. If not, I suspect it'll help me later when I need to look this up again. ;-)

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*