Logo Background

Data Migration Oracle E-Business Suite And SSO/OID

  • By on June 1, 2009 | No Comments

    You are in the end of setup the integration of Oracle E-Business Suite & SSO/OID via steps written in Oracle E-Business Suite and SSO/OID Integration.

    Assuming you have existing Oracle E-Business Suite where you want to migrate the user from FND_USER table to the new setup of OID, you will need to use some standard script & utilities provided for the Oracle E-Business Suite Release 11i user migration.

    The Oracle E-Business Suite Release 11i user migration utilities include:

    A tool (AppsUserExport) to export existing application accounts from Oracle E-Business Suite Release 11i into an intermediate LDIF file. This tool is invoked from the command line.

    A tool (LDAPUserImport) to read an LDIF file creates new Oracle E-Business Suite application accounts as needed, and import the data. This tool is invoked from the command line. LDAPUserImport is provided for bulk migration of existing Oracle Internet Directory accounts into Oracle E-Business Suite Release 11i.

    For our case on migrate existing Oracle E-Business Suite user to OID, we will just use the AppsUserExport utilities.

    How does the script and utilities works?

    An Oracle E-Business Suite administrator can use AppsUserExport to export a selected set of application accounts from the Oracle E-Business Suite Release 11i native user directory (FND_USER) into an intermediate LDIF file. An Oracle Internet Directory administrator then uses the Oracle Internet Directory ldifmigrator utility to convert this intermediate LDIF file into a final LDIF file, based on Oracle Internet Directory deployment choices. The Oracle Internet Directory administrator then loads the final LDIF file into Oracle Internet Directory.

    Determine which accounts to migrate

    Having determined which accounts to export, the application administrator can then specify whether an account is migrated by utilizing the following profiles:

    Applications SSO Login Types (APPS_SSO_LOCAL_LOGIN) – An account will not be migrated if the user level profile value of the account is ‘LOCAL’, i.e. the account is a local account.
    Applications SSO LDAP Synchronization (APPS_SSO_LDAP_SYNC) — An account will not be migrated if the user level profile value of the account is ‘N’, i.e. the account is marked to not to synchronize with Oracle Internet Directory.

    Note: The site level profile value will be used in the cases where the user level profile value is not set.

    Oracle E-Business Suite ships a number of standard accounts, such as SYSADMIN and GUEST. These accounts should not be migrated.

    To enforce this, the SYSADMIN and GUEST accounts are pre-seeded with Applications SSO Login Types (APPS_SSO_LOCAL_LOGIN) set to ‘LOCAL’ and Applications SSO LDAP Synchronization (APPS_SSO_LDAP_SYNC) set to ‘N’.

    Administrators should check whether there are any additional accounts that should not be migrated, especially accounts with user_id less than 10 (check with: select user_name from FND_USER where user_id<10).

    SELECT p.profile_option_name SHORT_NAME,
    n.user_profile_option_name NAME,
    decode(v.level_id,
    10001, 'Site',
    10002, 'Application',
    10003, 'Responsibility',
    10004, 'User',
    10005, 'Server',
    'UnDef') LEVEL_SET,
    decode(to_char(v.level_id),
    '10001', '',
    '10002', app.application_short_name,
    '10003', rsp.responsibility_key,
    '10005', svr.node_name,
    '10006', org.name,
    '10004', usr.user_name,
    'UnDef') "CONTEXT",
    v.profile_option_value VALUE
    FROM fnd_profile_options p,
    fnd_profile_option_values v,
    fnd_profile_options_tl n,
    fnd_user usr,
    fnd_application app,
    fnd_responsibility rsp,
    fnd_nodes svr,
    hr_operating_units org
    WHERE p.profile_option_id = v.profile_option_id (+)
    AND p.profile_option_name = n.profile_option_name
    AND p.profile_option_name LIKE '%APPS%SSO%'
    AND usr.user_id (+) = v.level_value
    AND rsp.application_id (+) = v.level_value_application_id
    AND rsp.responsibility_id (+) = v.level_value
    AND app.application_id (+) = v.level_value
    AND svr.node_id (+) = v.level_value
    AND org.organization_id (+) = v.level_value
    ORDER BY short_name, level_set

    Step 1 – Exporting Application Accounts into Intermediate LDIF File

    Use AppsUserExport to extract user information

    Use the AppsUserExport tool to extract application user information into an intermediate LDIF file by invoking from command line.

    To invoke the AppsUserExport tool, ensure your environment is set up correctly (check that $APPL_TOP/java is in the CLASSPATH environment variable), and use the following syntax. Note that all parameters will normally be on the same command line; they are shown on different lines (using the ‘\’ continuation character) for clarity.

    $ java oracle.apps.fnd.oid.AppsUserExport \
    [-v] \
    –dbc <dbcfile> \
    -o <outputfile> \
    -pwd <apps schema pwd> \
    -g
    [-l <logfile>]
     
    where:
    [-v] Runs in verbose mode 
    <dbcfile> Full path to the Applications dbcfile
    <outputfile> intermediate LDIF file
    <apps schema pwd> Apps schema password
    -g To create and copy users GUIDs to OID
    <logfile> log file (default is <outputfile>.log)
     
    For example:
    $ java oracle.apps.fnd.oid.AppsUserExport \
    -v \
    -dbc $FND_TOP/secure/$CONTEXT_NAME/oracle.dbc \
    -o users.txt \
    -pwd welcome \
    -g \
    -l users.log
    User Export to users.txt started..
    User Export completed successfully. 
    For further details please refer to log file at: users.log

    The resulting data file and log file may contain confidential information, such as the start and end dates for a user’s account, and should therefore be secured appropriately.

    Sample of the data file:

    # user name = UNCLE_LARRY
    dn:: Y249SElQUE9fTEVVTkcsICVyX1VzZXJDb250YZluZXJETiU=
    sn:: TGV5bmc=
    %s_UserNicknameAttribute%:: SElQUE9fTEVVTkc=
    givenName:: SGlwcG8=
    mail:: aGlwcG8ubGV1bmdAaHAuY50t
    orclActiveStartDate: 20050729000000z
    orclActiveEndDate: 20070515000000z
    orclIsEnabled: DISABLED
    userPassword: {MD5}DR78jJpOgl2cAVRNj3rNsQ==
    orclGuid: 6AE125E8284D6EE0E044001A4BF5D553
    objectClass: inetOrgPerson
    objectClass: orclUserV2

    Step 2 – Converting Intermediate LDIF File to Final LDIF File

    Before performing loading data into Oracle Internet Directory, the Oracle Internet Directory administrator needs to ensure that:

    – The extracted data file is copied from the Oracle E-Business Suite instance to Oracle Internet Directory.

    This command will be useful to specify will get the provisioning profiles on OID which the information will help in the migration. Get the GUID of the orclapplicationcommonname entry associated with the profile by retrieve all the provisioning profiles to a file:

    $ $ORACLE_HOME/bin/ldapsearch -h <oid_hostname> -p <oid_port> \
    -D "cn=orcladmin" -w <replace_with_pwd> -b \
    "cn=Provisioning Profiles, cn=Changelog Subscriber, cn=Oracle Internet Directory" \
    -s sub "objectclass=*" > profile.ldif

    Locate the relevant profile in the profiles.ldif file. Since the profiles do not have names that clearly indicate which application they relate to, the easiest way to do this is to find the profile with the orclodipprofileinterfaceconnectinformation value that matches the interface_connect_info value specified in the oidprovtool command.

    When the correct profile is located, copy the orclodipprovisioningappguid value e.g. for orclodipprovisioningappguid=1CB19CD19922F8ABE040A8C00BF1042B.

    Copy 1CB19CD19922F8ABE040A8C00BF1042B

    Get the orclapplicationcommonname entry DN with the GUID by input the value orclodipprovisioningappguid to orclguid

    $ $ORACLE_HOME/bin/ldapsearch -h <oid_hostname> -p <port> -D "cn=orcladmin" \
    -w <replace_with_pwd> -s sub "orclguid=1CB19CD19922F8ABE040A8C00BF1042B" -b "" dn

    Example output:

    orclApplicationCommonName=PROV_TEST_APP,cn=PROV_TEST,cn=Products,cn=OracleContext

    Now you can run oidprovtool with the correct application_dn to disable or enable the provisioning profile.

    – If the provisioning profile has been set up for the Oracle E-Business Suite instance and the profile mode is either OUTBOUND or BOTH (i.e. you have enabled any provisioning events from Oracle Internet Directory to Oracle E-Business Suite), profile will need to be temporarily disabled during the migration process.

    For example:

    $ oidprovtool operation=disable \
    ldap_host=beta.directory.com \
    ldap_port=3060 \
    ldap_user_dn=cn=orcladmin \
    ldap_user_password=l1ghth0use \
    application_dn="orclApplicationCommonName=beta,cn=EBusiness,
    cn=Products,cn=OracleContext,dc=us,dc=ganseycorp,dc=com" \
    profile_mode=BOTH
     
    Note: For security reasons it is not recommended to provide a password on the 
    command line, unless you're being prompted for it.
     
    This Provisioning Profile is disabled.

    Do not add spaces after any of the commas in the application_dn parameter.

    To convert the intermediate LDIF file to the final LDIF format:
    The intermediate LDIF file created by AppsUserExport has two variables that an Oracle Internet Directory administrator needs to instantiate using the Oracle Internet Directory ldifmigrator utility:
    a. s_UserContainerDN — DN of the entry under which all users are added, for example cn=users,dc=us,dc=oracle,dc=com
    b. s_UserNicknameAttribute – The nickname attribute used for user entries in the subscriber, for example uid

    For example:

    $ ldifmigrator "input_file=users.txt" \
    "output_file=users.ldif" \
    "s_UserContainerDN=cn=Users,dc=us,dc=oracle,dc=com" \
    "s_UserNicknameAttribute=uid"
    Migration of LDIF data completed. All the entries are successfully migrated

    Note that the variable names above are case sensitive.

    Step 3 – Loading Final LDIF file into Oracle Internet Directory

    Once the final LDIF file has been generated, the user data is ready to be loaded into Oracle Internet Directory using the Oracle Internet Directory bulkload tool.

    This section describes the minimum command-line options required to perform this task, but there are additional options available for more advanced requirements.

    Before performing a bulk load:

    1. Use oidprovtool with operation=DISABLE to disable the profile, for each registered E-Business instance, before the migration is started.

    For example:

    $ oidprovtool operation=disable \
    ldap_host=beta.directory.com \
    ldap_port=3060 \
    ldap_user_dn=cn=orcladmin \
    ldap_user_password=l1ghth0use \
    application_dn="orclApplicationCommonName=beta,cn=EBusiness,
    cn=Products,cn=OracleContext,dc=us,dc=ganseycorp,dc=com" \
    profile_mode=BOTH
     
    Note: For security reasons it is not recommended to provide a password on the 
    command line, unless you're being prompted for it.
     
    This Provisioning Profile is disabled.

    Do not add spaces after any of the commas in the application_dn parameter.

    2. Stop all OID processes before using the bulkload utility to load the LDIF file:

    $ $ORACLE_HOME/opmn/bin/opmnctl stopall

    Note the OID password, which should be the same as the instance and orcladmin passwords. You will be prompted for this when running the utility.

    3. In case the oidmon or oidctl commands were used to manually start the oid proceses, you must make sure that they have stopped:

    $ $ORACLE_HOME/ldap/bin/ldapcheck
    Checking Oracle Internet Directory Processes ...ALL
     
    Not Running ---- Process oidmon
    Not Running ---- Process oidldapd
    Not Running ---- Process oidrepld
    Not Running ---- Process odisrv
    No LDAP Processes are Running

    4. If any other OID processes such as odisrv are still running, you must stop them manually using:

    $ oidctl connect=<SID> server=<servername> instance=<#> stop

    You must ensure that no OID processes are running before continuing with the bulkload.sh command.

    The recommended procedures for preventing collisions during the bulkload import are:

    1. Run the bulkload utility with the -check and –generate options to verify that there are no duplicate users.

    For example:

    $ bulkload.sh -connect <connect string> -check \
    -generate <fully qualified path to LDIF file>
     
    Verifying node "ORAOID"
    -----------------------------
    This tool can only be executed if you know database user password
    for OiD on ORAOID
    Enter OiD password ::
     
    ------------------------------------------------------------------
      Checking Internet Directory current schema state
    ------------------------------------------------------------------
     
    -------------------------------------------------------------------
       Checking and Generating Internet Directory data for bulk loading
    -------------------------------------------------------------------
     
    Schema Check Errors are logged in : /oracle/10.1.4/oid/ldap/log/bulkload.log
     
    No Bad Entries found.
     
    Non-Unique Distinguished Names are logged in : /oracle/10.1.4/oid/ldap/log/duplicateDN.log
     
    -------------------------------------------------------------------
       Data Generated for bulk loading
    -------------------------------------------------------------------

    The was the SID of your 10gAS SSO/OID database name.

    2. Check the log file for duplicate users

    3. If the log file indicates duplicate users, manually remove these users from the LDIF file.

    4. Rerun Step 1 to verify all duplicates have been successfully removed.

    $ bulkload.sh -connect <connect string> -check \
    -generate <fully qualified path to LDIF file>
    Verifying node "ORAOID"
    -----------------------------
    This tool can only be executed if you know database user password
    for OiD on ORAOID
    Enter OiD password ::
     
    ------------------------------------------------------------------
      Checking Internet Directory current schema state
    ------------------------------------------------------------------
     
    -------------------------------------------------------------------
       Checking and Generating Internet Directory data for bulk loading
    -------------------------------------------------------------------
     
    No Schema Check Errors.
     
    No Bad Entries found.
     
    No Duplicate DN Entries.
     
    -------------------------------------------------------------------
       Data Generated for bulk loading
    -------------------------------------------------------------------

    5. Once all duplicates are removed, run the bulkload utility with the –load option to load the users.

    For example:

    $ bulkload.sh -connect <connect string> -load <fully qualified path to the LDIF file>

    Just in case the bulkload.sh completed with error, you may use ldapadd command as the workaround by inputting the same final ldif file. Or for small amounts of data, you may also use the ldapadd tool instead of the bulkload tool.

    ldapadd -h <oid_hostname> -p <oid_ldap_port> -D "cn=orcladmin" \
    -w <password> -f users.ldif -v

    Step 4 – Update lastchangenumber and restart OID processes

    1. Start all OID processes

    $ $ORACLE_HOME/opmn/bin/opmnctl startall

    2. Update the lastchangenumber attribute of the profile:
    First, find the current last change number in Oracle Internet Directory with the ldapsearch command:

    $ $ORACLE_HOME/bin/ldapsearch –h <oid_hostname> -p <oid_ldap_port> \
    -D "cn=orcladmin" -w <password> -s base \
    -b "" "objectclass=*" lastchangenumber

    Next, use the following command to update the lastchangenumber attribute to the number n that was discovered in the last step:

    $ oidprovtool operation=MODIFY \
    ldap_host=[ldap_host] \ ldap_port=[ldap_port] \ ldap_user_dn=[user to connect to LDAP] \
    ldap_user_password=[user password] \
    application_dn=[dn of the registered app for which the profile is modified] \
    orclLastAppliedChangeNumber=[n]

    For example:

    $ oidprovtool operation=MODIFY \
    ldap_host=beta.directory.com \ ldap_port=3060 \
    ldap_user_dn=cn=orcladmin \
    ldap_user_password=l1ghth0use \
    application_dn=”orclApplicationCommonName=beta,cn=EBusiness,
    cn=Products,cn=OracleContext,dc=ganseycorp,dc=com” \
    orclLastAppliedChangeNumber=100

    3. Use oidprovtool with operation=ENABLE to enable the profile.

    Step 5 – Create Subscriptions for Bulkloaded Users

    The bulkload tool does not automatically subscribe users to the parent E-Business instance. To create the subscriptions for your bulkloaded users, run the following statement on your E-Business database:

    SELECT user_name FROM FND_USER WHERE FND_profile.
    VALUE_SPECIFIC('APPS_SSO_LOCAL_LOGIN', user_id)<>'LOCAL'
    AND FND_profile.VALUE_SPECIFIC('APPS_SSO_LDAP_SYNC', user_id)='Y'

    Moreover, save results in a txt file using your SQL client capabilities. For example, in SQL Navigator you can save results in a delimited file with .lst extension using as quote character.

    Step 6 – Verify the migrated Oracle E-Business Suite user in OID

    Run the command below to test the authentication of migrated Oracle E-Business Suite user in OID

    $ ldapbind -h <oid_hostname> -p <oid_port> -D "cn=orcladmin" -w password
     
    $ ldapbind -h <oid_hostname> -p <oid_port> -D \
    "cn=UNCLE_LARRY,cn=Users,dc=california,dc=oracle,dc=com" -w password

    1. Use oidprovtool with operation=DISABLE to disable the profile, for each registered E-Business instance, before the migration is started.

    Step 7 – Enable the provisioning profile back in OID to sync with Oracle E-Business Suite

    For example:

    $ oidprovtool operation=enable \
    ldap_host=beta.directory.com \
    ldap_port=3060 \
    ldap_user_dn=cn=orcladmin \
    ldap_user_password=l1ghth0use \
    application_dn="orclApplicationCommonName=beta,cn=EBusiness,
    cn=Products,cn=OracleContext,dc=us,dc=ganseycorp,dc=com" \
    profile_mode=BOTH
     
    Note: For security reasons it is not recommended to provide a password on the 
    command line, unless you're being prompted for it.
     
    This Provisioning Profile is enable.

    Do not add spaces after any of the commas in the application_dn parameter.

    Previous
    Next
    » LDAP Server Is Down
Leave a Comment