How to export users from OID and import in EBS?

Export Users from OID

You can export users from OID based on time range.

1. Login to OID server
2. Execute given below script to export users from OID which can be imported in EBS.

   SCRIPT

   $ cat expoidusr.sh

   . $ORACLE_HOME/iasenv.sh # OID Oracle Home

   echo "This program will list oid users created during some time period, who can be imported in EBS "

   echo "Enter Connect String name :\c"

   read CONNECT

   echo "Enter time stamp From (YYYYMMDDHHMISS) :\c"

   read TIMEFROM

   echo "Enter time stamp To (YYYYMMDDHHMISS) :\c"

   read TIMETO

   echo "Export OID users in given duration. Output file name is output.ldif"

   $ORACLE_HOME/ldap/bin/ldifwrite connect="$CONNECT" basedn="cn=users,dc=orbit, dc=org" –f output.ldif filter="(&(objectclass=person)(createtimestamp >= "$TIMEFROM")(createtimestamp <= "$TIMETO"))"

    

   EXECUTION

   $expoidusr.sh

   This program will list oid users created during some time period, who can be imported in EBS

   Enter Connect String name : JAISSOD

   Enter time stamp From (YYYYMMDDHHMISS) :20090814051502

   Enter time stamp To (YYYYMMDDHHMISS) :20090824083000

   Export OID users in given duration. Output file name is output.ldif

   This tool can only be executed if you know database user password for OID

   Enter OID Password ::<***********>

   ------------------------------------------------------------

   Reading entries under BaseDN "cn=users,dc=orbit, dc=org"...

   -------------------------------------------------------------

   Noteà You need to enter orcladmin password.

    

Import OID users in FND_USER in EBS 11i

1. Login oracle applications 11i

    

2. Import users by executing given below script

    

   SCRIPT

   $ cat impoidtoesb11i.sh

   if [ -f "$FILE" ]

   then

   echo "File $FILE is available ..."

   else

   echo "Unable to locate $FILE ..."

   exit

   fi

    

   . $APPL_TOP/APPSORA.env

   export CLASSPATH=$COMMON_TOP/java:$CLASSPATH

   JAVA_HOME=`set | grep AF_JRE | awk -F "=" ' { print $2 }'`

   DBCFILE="$FND_TOP"/secure/`grep s_dbc_file_name $CONTEXT_FILE | sed 's/^.*s_dbc_file_name[^>.]*>[ ]*\([^<]*\)<.*/\1/g; s/ *$//g'`.dbc

    

   if [ -f $DBCFILE ]

   then

   echo "DBC $DBCFILE exits..."

   else

   echo "Unable to locate dbc file ..."

   exit

   fi

    

   cd $COMMON_TOP/java

   $JAVA_HOME/jre/bin/java oracle.apps.fnd.oid.LDAPUserImport \

   -v \

   -dbc "$DBCFILE" \

   -f "$FILE" \

   -n uid \

   -l $HOME/usersimp.log

   cd -

   echo "Please check $HOME/usersimp.log file user import detail."

    

   EXECUTION

   $ impoidtoesb11i.sh

   Enter output file location : output.ldif

   File output.ldif is available ...

   DBC /opt01/app/jaiappl/fnd/11.5.0/secure/jai0504e_jaid1.dbc exits...

   LDAP user import from /staging/diagnosis/jsingh/sso/output.ldif started..

   LDAP user import completed sucessfully. For further details refer to log file at /home/orajaid1/usersimp.log

   /home/jaid1

   Please check /home/orajaid1/usersimp.log file user import detail.

    

3. Verify userimp.log generated in step 2 for failed user import.

 

     

Unable to call fnd_ldap_wrapper.create_user due to the following errors

We were not able to create new user or change users password using user define screen in Oracle Apps 12i. Even we were not able to change user id's password using FNDCPASS. All of above were failing with given below error.

ERROR

Unable to call fnd_ldap_wrapper.create_user due to the following errors

ORA-20001: Unable to call fnd_ldap_wrapper.create_user due to the following reason:

Oracle Internet Directory is not registered correctly.

Please contact your System Administrator...

(USER_NAME=TEMP123)

 

From above error it was clear that issue was with OID registration. I check OID registration log and I noticed following error in OID log.

*** Log File = /opt01/app/oracle/ERMR/inst/apps/ERMR_ios0209e/logs/appl/rgf/TXK/txkSetSSOReg_Thu_Oct_15_19_27_42_2009.xml

<record>

<date>Oct 15, 2009 7:27:46 PM</date>

<millis>1255649266373</millis>

<sequence>40</sequence>

<logger>txkSetSSOReg</logger>

<level>SEVERE</level>

<class>oracle.apps.fnd.txk.ias.TXKSTDIO</class>

<method>throwable</method>

<thread>0</thread>

<message>java.lang.UnsatisfiedLinkError: jmisc (A file or directory in the path name does not exist.)

at java.lang.ClassLoader.loadLibraryWithPath(ClassLoader.java:998)

at java.lang.ClassLoader.loadLibraryWithClassLoader(ClassLoader.java:962)

at java.lang.System.loadLibrary(System.java:465)

at oracle.security.misc.C05.c(C05)

at oracle.security.misc.Checksum.SHA(Checksum)

at oracle.ias.repository.IASSchema$Enc.d(IASSchema.java:3076)

at oracle.ias.repository.IASSchema.getDirectoryWriter(IASSchema.java:1263)

at oracle.ias.repository.IASSchema.makeNewOIDEntry(IASSchema.java:1381)

at oracle.ias.repository.SchemaManager.makeNewOIDEntry(SchemaManager.java:820)

at oracle.apps.fnd.txk.ias.oid.Instance.register(Instance.java:916)

at oracle.apps.fnd.txk.ias.oid.Instance.main(Instance.java:1390)

</message>

</record>

</log>

I investigated further and noticed given below in Metalink Note: 376811.1. We missed to apply patch 5855635 on 10.1.3 home.

Note: If you are integrating Oracle 10gAS SSO/OID with AIX based Oracle E-Business Suite Release 12, then OID registration will fail with following error. Apply patch 5855635 to AS 10.1.3 ORACLE_HOME of Release 12. See known issue section and patch readme for more details.

java.lang.UnsatisfiedLinkError: jmisc (A file or directory in the path name does not exist.)

Resolution

1. We applied patch 5855635 on 10.1.3 home
2. We unregistered OID, SSO and instance using given below script.

   $cat unregistration.sh

   . $APPL_TOP/$CONTEXT_NAME.env

   . $INST_TOP/ora/10.1.3/$CONTEXT_NAME.env

    

   echo "Please enter APPS password of database:\c"

   read APPSPWD

    

   echo "Please enter OID/SSO Repository database Hostname:\c"

   read REP_HOSTNAME

    

   echo "Please enter Repository Database Listener Port:\c"

   read REP_PORT

    

   echo "Please enter Repository Database name :\c"

   read REP_DBNAME

    

   echo "Please enter LDAP port (3030):\c"

   read LDAP_PORT

    

   echo "Please enter LDAP SSL Port (default: 3130) :\c"

   read LDAP_SSLPORT

    

   echo "Please enter ORCLADMIN password:\c"

   read ORCL_PWD

    

   echo "PLease enter LDAP Server name(ssod/ssot/ssop.idc.ikon.org):\c"

   read LDAP_HOST

    

   clear

   echo "\n\n\t\t+++++++++++++++++++++++++++++++++++++"

   echo "\t\tREPOSITORY HOSTNAME : $REP_HOSTNAME"

   echo "\t\tREPOSITORY PORT : $REP_PORT"

   echo "\t\tREPOSITORY DBNAME : $REP_DBNAME"

   echo "\t\tAPPS/SYSTEM PWD : $APPSPWD"

   echo "\t\tLDAP PORT : $LDAP_PORT"

   echo "\t\tORCLADMIN PWD : $ORCL_PWD"

   echo "\t\t11i_ENV : $CONTEXT_NAME"

   echo "\t\t++++++++++++++++++++++++++++++++++++++"

    

   echo "Please review and hit enter"

   read

    

   echo "DeRegistering OID"

   echo "*****************"

   $FND_TOP/bin/txkrun.pl \

   -script=SetSSOReg \

   -deregisteroid=yes \

   -appspass=$APPSPWD \

   -ldaphost=$LDAP_HOST \

   -ldapport=$LDAP_PORT \

   -oidadminuserpass=$ORCL_PWD \

   -appname=$CONTEXT_NAME \

   -svcname=$CONTEXT_NAME \

    

   echo "Deregister SSO "

   $FND_TOP/bin/txkrun.pl -script=SetSSOReg \

   -deregistersso=yes \

   -appspass=$APPSPWD

    

   echo "Deregister Instance"

   echo "*******************"

   $FND_TOP/bin/txkrun.pl \

   -script=SetSSOReg \

   -deregisterinstance=yes \

   -oidadminuserpass=$ORCL_PWD \

   -appspass=$APPSPWD

    

   echo "Remove Preference"

   $FND_TOP/bin/txkrun.pl \

   -script=SetSSOReg \

   -removereferences=yes \

   -appspass=$APPSPWD

    

3. I reregistered as given below

   . $APPL_TOP/$CONTEXT_NAME.env

   . $INST_TOP/ora/10.1.3/$CONTEXT_NAME.env

    

   echo "Please enter APPS password of database:\c"

   read APPSPWD

    

   echo "Please enter OID/SSO Repository database Hostname:\c"

   read REP_HOSTNAME

    

   echo "Please enter Repository Database Listener Port:\c"

   read REP_PORT

    

   echo "Please enter Repository Database name (SSOD/SSOT/SSOP):\c"

   read REP_DBNAME

    

   echo "Please enter LDAP port(3030):\c"

   read LDAP_PORT

    

   echo "Please enter LDAP SSL Port (default: 3130) :\c"

   read LDAP_SSLPORT

    

   echo "Please enter ORCLADMIN password:\c"

   read ORCL_PWD

    

   echo "PLease enter LDAP Server name:\c"

   read LDAP_HOST

    

   clear

   echo "\n\n\t\t+++++++++++++++++++++++++++++++++++++"

   echo "\t\tREPOSITORY HOSTNAME : $REP_HOSTNAME"

   echo "\t\tREPOSITORY PORT : $REP_PORT"

   echo "\t\tREPOSITORY DBNAME : $REP_DBNAME"

   echo "\t\tAPPS/SYSTEM PWD : $APPSPWD"

   echo "\t\tLDAP PORT : $LDAP_PORT"

   echo "\t\tORCLADMIN PWD : $ORCL_PWD"

   echo "\t\t11i_ENV : $CONTEXT_NAME"

   echo "\t\t++++++++++++++++++++++++++++++++++++++"

    

   echo "Please review and hit enter"

   read

    

   echo "Registering instance "

   echo "*******************"

    

   $FND_TOP/bin/txkrun.pl -script=SetSSOReg \

   -registerinstance=yes \

   -infradbhost=$REP_HOSTNAME \

   -ldapport=$LDAP_PORT \

   -ldapportssl=$LDAP_SSLPORT \

   -ldaphost=$LDAP_HOST \

   -oidadminuser=cn=orcladmin \

   -oidadminuserpass=$ORCL_PWD \

   -appspass=$APPSPWD

    

   echo "Registering SSO"

   echo "*******************"

   $FND_TOP/bin/txkrun.pl \

   -script=SetSSOReg \

   -registersso=yes \

   -appspass=$APPSPWD

    

   echo "Registering OID"

   echo "*****************"

   $FND_TOP/bin/txkrun.pl \

   -script=SetSSOReg \

   -registeroid=yes \

   -ldaphost=$LDAP_HOST \

   -ldapport=$LDAP_PORT \

   -oidadminuserpass=$ORCL_PWD \

   -appspass=$APPSPWD \

   -instpass=$APPSPWD \

   -appname=$CONTEXT_NAME \

   -svcname=$CONTEXT_NAME \

   -provisiontype=3

4. I did not noticed error in OID log
5. Restated Oracle Apps 12i services.
6. I was able to login without supplying ID and password. I was also able to reset password and create user.

Oracle Apps 11i Deregistration /Registration with 10giAS RAC enabled Repository

Recently we converted single instance OID repository to RAC (2 instance) node. We performed following activity to register Oracle apps 11i to RAC enabled SSO (OID) repository.

1. Get TNS Entry from from SSO enabled OID repository (RAC) server

   JAISSOD =

   (DESCRIPTION =

   (ADDRESS_LIST =

   (ADDRESS = (PROTOCOL = TCP)(HOST = jai10901vip.orbit.org)(PORT = 1510))

   (ADDRESS = (PROTOCOL = TCP)(HOST = jai12101vip.orbit.org)(PORT = 1510)))

   (CONNECT_DATA =

   (SERVICE_NAME = JAISSOD))

   )

2. Ask Unix Admin to add SSO vip host expression in /etc/hosts file on all EBS server node. e.g. JAID1 (jai0504e).

   10.11.61.12     jai10901vip.orbit.org jai10901vip     # POCR WEB 1 BCD0109; JAISSOD - Single Sign On Dev

   10.11.61.13     jai12101vip.orbit.org jai12101vip     # POCR WEB 2 BCD0207; JAISSOD - Single Sign On Dev

3. Login to Applications and source A (JAID1)
4. Deregister SSO and OID as given below

   Deregister SSO

   $ txkrun.pl -script=SetSSOReg \

   > -registersso=Yes \

   > OTOCOL=TCP)(HOST=jai12101vip.orbit.org)(PORT=1510)))(CONNECT_DATA=(SERVICE_NAME=JAISSOD)))" \ <

   > -appspass=dr1veapp \

   > -orassopass=N16FKR0g \

   > -systempass=dr1veapp \

   > -ssosdkpass=ssosdk

   *** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS

   *** Log File = /opt01/app/oracle/jaid1comn/rgf/JAID1_jai0504e/sso/txkSetSSOReg_Thu_Oct_22_15_42_08_2009.log

   Program : /opt01/app/jaid1appl/fnd/11.5.0/patch/115/bin/txkSetSSOReg.pl started @ Thu Oct 22 15:42:08 2009

    

   *** Log File = /opt01/app/oracle/jaid1comn/rgf/JAID1_jai0504e/sso/txkSetSSOReg_Thu_Oct_22_15_42_08_2009.log

    

   ######################## WARNING ########################################

   This application works with SSOSDK version 9.0.2 or higher. If lower version

   (3.0.9) of SSOSDK was installed in your system and you have a registered

   partner application, this process will remove the 3.0.9 version of the SSOSDK

   schema and install the 9.0.2 version.

   ######################## WARNING ########################################

   Beginning input parameter validation for SSO registration.

   Beginning loading SSO SDK into database if necessary.

   Loading of SSO SDK into database is not required.

   Input parameter validation for SSO registration completed.

    

   BEGIN SSO REGISTRATION:

    

   Beginning to register partner application.

   Partner application has been registered successfully.

   Single Sign-On partner application registered successfully.

    

   End of /opt01/app/jaid1appl/fnd/11.5.0/patch/115/bin/txkSetSSOReg.pl : No Errors encountered

    

   Deregister OID

   $ txkrun.pl -script=SetSSOReg \

   > -deregisteroid=Yes \

   > -appspass=dr1veapp

   Checking preferences in the database.

   Enter the Oracle Internet Directory Administrator (orcladmin) Bind password ? ssotirisqa123

   *** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS

   *** Log File = /opt01/app/oracle/jaid1comn/rgf/JAID1_jai0504e/sso/txkSetSSOReg_Thu_Oct_22_15_12_27_2009.log

   Program : /opt01/app/jaid1appl/fnd/11.5.0/patch/115/bin/txkSetSSOReg.pl started @ Thu Oct 22 15:12:48 2009

    

   *** Log File = /opt01/app/oracle/jaid1comn/rgf/JAID1_jai0504e/sso/txkSetSSOReg_Thu_Oct_22_15_12_27_2009.log

    

   Beginning input parameter validation for OID de-registration.

   Following is the number of user subscriptions found to be deleted : 264

    

   ******************************** WARNING **********************************

    

   For better performance it is advisable to use bulk delete utility as

   described in Oracle Internet Directory Administrator's Guide, before

   continuing with the de-registration.

   This script will delete the subscriptions one at a time. Depending upon number of

   subscriptions, this action might take a long time but you can watch the progress

   in the log file in another terminal window.

    

   ******************************** WARNING **********************************

    

   Do you want to continue <Y/N> ? Y

   Input parameters validation for OID de-registration completed.

    

   BEGIN OID DE-REGISTRATION:

    

   No dangling provisioning profiles found.

   Beginning to delete provisioning profile for this application.

   Provisioning profile for this application has been deleted successfully.

   Following is the number of user subscriptions found to be deleted : 264

    

   USER SUBSCRIPTION DELETE LOG FILE : /opt01/app/oracle/jaid1comn/rgf/JAID1_jai0504e/sso/usrDelSubs_Thu_Oct_22_15_12_27_2009.out

   All user subscriptions have been deleted successfully.

   Beginning to de-register this application from Oracle Internet Directory.

   This application has been de-registered successfully from Oracle Internet Directory.

   End of /opt01/app/jaid1appl/fnd/11.5.0/patch/115/bin/txkSetSSOReg.pl : No Errors encountered

   [orairid1@jai0504e] /staging/patches/apps/SSO

5. Nullify applsys.fnd_user.user_guid column with null.

   create table apps.fnd_user_bkp as select * from apps.fnd_user;

   update apps.fnd_user set user_guid=null where user_name not in

   (

   'APPSMGR',

   'AUTOINSTALL',

   'CR_SCHEDULER',

   'GUEST',

   'IBE_GUEST',

   'IRISLDMON',

   'PORTAL30',

   'PORTAL30_SSO',

   'RAP_ADMINISTRATOR',

   'SYSADMIN',

   'IBE_ADMIN',

   'IEXADMIN',

   'IRC_EMP_GUEST',

   'IRC_EXT_GUEST',

   'OP_CUST_CARE_ADMIN',

   'OP_SYSADMIN',

   'SYSADMIN',

   'WIZARD',

   'ANONYMOUS',

   'AUTOINSTALL',

   'CONCURRENT MANAGER',

   )

   /

   Sql>commit;

    

6. Register SSO, OID. In SSO you need to specify "-infraconnst" as given below

   Register SSO

   txkrun.pl -script=SetSSOReg \

   -registersso=Yes \

   -infraconnstr="(DESCRIPTION=(LOAD_BALANCE=YES)(FAILOVER=YES)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=jai10901vip.orbit.org)(PORT=1510))(ADDRESS=(PROTOCOL=TCP)(HOST=jai12101vip.orbit.org)(PORT=1510)))(CONNECT_DATA=(SERVICE_NAME=JAISSOD)))" \

   -appspass=dr1veapp \

   -orassopass=N16FKR0g \

   -systempass=dr1veapp \

   -ssosdkpass=ssosdk

    

   $ txkrun.pl -script=SetSSOReg \

   > -registersso=Yes \

   > OTOCOL=TCP)(HOST=jai12101vip.orbit.org)(PORT=1510)))(CONNECT_DATA=(SERVICE_NAME=JAISSOD)))" \ <

   > -appspass=dr1veapp \

   > -orassopass=N16FKR0g \

   > -systempass=dr1veapp \

   > -ssosdkpass=ssosdk

   *** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS

   *** Log File = /opt01/app/oracle/jaid1comn/rgf/JAID1_jai0504e/sso/txkSetSSOReg_Thu_Oct_22_15_42_08_2009.log

   Program : /opt01/app/jaid1appl/fnd/11.5.0/patch/115/bin/txkSetSSOReg.pl started @ Thu Oct 22 15:42:08 2009

    

   *** Log File = /opt01/app/oracle/jaid1comn/rgf/JAID1_jai0504e/sso/txkSetSSOReg_Thu_Oct_22_15_42_08_2009.log

    

   ######################## WARNING ########################################

   This application works with SSOSDK version 9.0.2 or higher. If lower version

   (3.0.9) of SSOSDK was installed in your system and you have a registered

   partner application, this process will remove the 3.0.9 version of the SSOSDK

   schema and install the 9.0.2 version.

   ######################## WARNING ########################################

   Beginning input parameter validation for SSO registration.

   Beginning loading SSO SDK into database if necessary.

   Loading of SSO SDK into database is not required.

   Input parameter validation for SSO registration completed.

    

   BEGIN SSO REGISTRATION:

    

   Beginning to register partner application.

   Partner application has been registered successfully.

   Single Sign-On partner application registered successfully.

    

   End of /opt01/app/jaid1appl/fnd/11.5.0/patch/115/bin/txkSetSSOReg.pl : No Errors encountered

    

   Register OID

   txkrun.pl -script=SetSSOReg \

   -provtmp=$FND_TOP/admin/template/ProvOIDToApps.tmp \

   -registeroid=Yes \

   -appspass=dr1veapp \

   -infradbhost=ssod.orbit.org \

   -orcladminpass=ssodpoc123 \

   -instpass=dr1veapp \

   -ldapport=3030 \

   -appname="JAID1_jai0504e" \

   -svcname="JAID1_jai0504e"

    

   $ txkrun.pl -script=SetSSOReg \

   > -provtmp=$FND_TOP/admin/template/ProvOIDToApps.tmp \

   > -registeroid=Yes \

   > -appspass=dr1veapp \

   > -infradbhost=ssod.orbit.org \

   > -orcladminpass=ssodpoc123 \

   > -instpass=dr1veapp \

   > -ldapport=3030 \

   > -appname="JAID1_jai0504e" \

   > -svcname="JAID1_jai0504e"

   *** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS

   *** Log File = /opt01/app/oracle/jaid1comn/rgf/JAID1_jai0504e/sso/txkSetSSOReg_Thu_Oct_22_16_19_22_2009.log

   Program : /opt01/app/jaid1appl/fnd/11.5.0/patch/115/bin/txkSetSSOReg.pl started @ Thu Oct 22 16:19:22 2009

    

   *** Log File = /opt01/app/oracle/jaid1comn/rgf/JAID1_jai0504e/sso/txkSetSSOReg_Thu_Oct_22_16_19_22_2009.log

    

   Beginning input parameter validation for OID registration.

   Input parameters validation for OID registration completed.

    

   BEGIN OID REGISTRATION:

    

   Beginning to register Application and Service containers if necessary.

   Application and Service containers were found and thus not created.

   Beginning to register application in Oracle Internet Directory.

   Registration of application in Oracle Internet Directory completed successfully.

   Beginning to register instance password in Oracle Internet Directory.

   Registration of instance password in Oracle Internet Directory completed successfully.

   Beginning to test application registration in Oracle Internet Directory.

   Testing of application registration in Oracle Internet Directory completed successfully.

   Beginning to register provisioning profile in Oracle Internet Directory.

   Registration of provisioning profile in Oracle Internet Directory completed successfully.

   Application is now registered successfully with provisioning in Oracle Internet Directory.

   End of /opt01/app/jaid1appl/fnd/11.5.0/patch/115/bin/txkSetSSOReg.pl : No Errors encountered

   [orairid1@jai0504e] /home/orairid1

7. Check logs generated using deregistration and registration
8. Start Applications Services
9. Try to login to applications. It should login without asking USERID and password.