oracle apps dba blog

Hi All,
In this post I would like to take the oppurtunity about one of the possible issues everyone will face when supporting Single Sign On (with OID) and Oracle E-business R12/R11i.
For Some time After the Implementation the synchronization will work fine , i.e If you create a User in Oracle Apps from Sysadmin->Security –> Define –> User then it will automatically propogate in SSO(OID) if you have enabled bidirectional profiling. But After some time you will face issues and your user created in apps will not be propogated to single sign on and there may be many reasons behind this.
1. like the related procedures becoming invalid or something like that.
2.The odisrv sync process may not be running
3.You can query FND_LOG_MESSAGES after enabling Debug for FND..
but the thing which you will not come to know is about the Password Expiry date of AppsDN User…
AppsDN is the user which internally links OID to Oracle Apps R12/11i.
by default OID sets the Password Expiry to 60 days so after that the synchronization will not happen automatically.
How to check that this the original cause?

AppsDN password is kept in two places, one in OID Repository and one in Oracle Apps Instance

If you do a ldapcompare of this then you will be able to find whether the password is locked or you can troubleshoot further…

This is stored in Entry Management realm DN and in Oracle Context ->Products->Ebusiness->DN..

steps to verify this are as follows..

1.execute the following in the apps instance to get the actual password

SQL>select fnd_preference.eget(‘#INTERNAL’, ‘LDAP_SYNCH’,'EPWD’,'LDAP_PWD’) Password from dual;
PASSWORD
———————————–
vivek123

in my case the password is vivek123

and orcladmin password is sunmicro1

AppsDN from OID Manager i found is VIS (since this one is vision)

Now login to shell prompt in Infra Instance where OID is installed and fire the following command

a) [oracle@ssoserver bin]$ $ORACLE_HOME/bin/ldapcompare -h sso.vectorconsuting.co.uk -p 389 -D cn=orcladmin -w sunmicro1 -b “orclApplicationCommonName=VIS, cn=EBusiness,cn=Products,cn=OracleContext,dc=vectorconsulting,dc=co,dc=uk” -a userpassword -v vivek123
The value oracleadmin1 is contained in the attribute userpassword in DN orclApplicationCommonName=VIS, cn=EBusiness,cn=Products,cn=OracleContext,dc=vectorconsulting,dc=co,dc=uk.

This means the password is working properly and synchronization is happening properly without any issues…

b) [oracle@ssoserver bin]$ $ORACLE_HOME/bin/ldapcompare -h sso.vectorconsuting.co.uk -p 389 -D cn=orcladmin -w sunmicro1 -b “orclApplicationCommonName=VIS, cn=EBusiness,cn=Products,cn=OracleContext,dc=vectorconsulting,dc=co,dc=uk” -a userpassword -v vivek123

The value oracleadmin1 is not contained in the attribute userpassword in DN orclApplicationCommonName=VIS, cn=EBusiness,cn=Products,cn=OracleContext,dc=vectorconsulting,dc=co,dc=uk.

for this output the password is wrong(not matching)

c) [oracle@ssoserver bin]$ $ORACLE_HOME/bin/ldapcompare -h sso.vectorconsuting.co.uk -p 389 -D cn=orcladmin -w sunmicro1 -b “orclApplicationCommonName=VIS, cn=EBusiness,cn=Products,cn=OracleContext,dc=vectorconsulting,dc=co,dc=uk” -a userpassword -v vivek123

ldap_compare_s: Invalid credentials
ldap_compare_s: additional info: Password Policy Error :9000: GSL_PWDEXPIRED_EXCP :Your Password has expired. Please contact the Administrator to change your password.

This means the password is expired..

So If you can find that AppsDN password is expired then either you can increase the expiry days to a big value by creating a New Password Policy with option “Not to Lock Account” in OID Manager and assigning it to the AppsDN

or reset the password to a new password but take care to remove the option “password reset when login next time”

I hope this Post Sounds Interesting for troubleshooting..

More to come..
–Vivek R