Helped a collegae who was migrating a discoverer configuration to another node with a different version (from 10.1.2 to 10.1.4) with now a 10.2 – infrastructure database in a separate home. The OID-users were already successfully migrated, with a little help of the example-script here, but one of the problems was that discoverer didn’t use the single-sign on. The other was that the private and public connection users had made, had disappeared.

By enabling Single Sign-On for Discoverer (is not default when installed) the first problem had been solved:

Method 1:

Open the mod_osso.conf file in a text-editor and add the following text to the end of the file:

<Location /discoverer/plus>

require valid-user

AuthType Basic

</Location>

<Location /discoverer/viewer>

require valid-user

AuthType Basic

</Location>

<Location /discoverer/app>

require valid-user

AuthType Basic

</Location>

Method 2:

Edit the ORACLE_HOME/opmn/conf/opmn.xml file and change ssl-disabled to ssl-enabled.

In both cases, save the file and perform a restart:

–          Opmnctl stopall

–          Opmnctl startall

The next thing to do was to enable users to create or use private connections to SSO-enabled Oracle Applications databases (and I quote from the manual):

Select the Administration tab to display the Discoverer Administration page.

Select the Private Connections link to display the Connect to SSO-enabled Oracle Applications database area.

Use the Allow authenticated OracleAS Single Sign-On (SSO) users to create and use private connections to SSO-enabled Oracle Applications databases, without entering a password check box to specify whether SSO is enabled for the Discoverer installation, as follows:

  • Select the check box to enable Oracle Applications users (that are associated with an SSO user) to create and use private connections without entering a password (when using an SSO-enabled Oracle Applications database).
  • Clear the check box to disable Oracle Applications users (that are associated with an SSO user) from creating and using private connections without entering a password (when using an SSO-enabled Oracle Applications database).

Click OK to save the details.

Then the nice part: the connections are saved in the infrastructure, schema ORASSO / ORASSO_DS (the latter contains only synonyms) and must be migrated seperataly with a wrap-script called ‘ssomig’. First perform an export with ssomig on the source-database, then an import on the target.  But to use this script, you need the password of the user ORASSO.

To retrieve this password of ORASSO ( and I quote again):

ldapsearch -D cn=orcladmin -w <superuser password> -p <port number> -h <Infrastructure hostname> -b “cn=IAS,cn=Products,cn=OracleContext” -s sub -v OrclresourceName=ORASSO | grep orclpasswordattribute

Where:

  • <superuser password> is the password of the orcladmin user in Oracle Internet Directory (OID). By default, this is the same password as the ias_admin user password after a new Infrastructure installation.
  • <port number> is the OID port number.

For example:

ldapsearch -D cn=orcladmin -w manager1 -p 389 -h hostname1.nl.oracle.com -b “cn=IAS,cn=Products,cn=OracleContext” -s sub -v OrclresourceName=ORASSO | grep orclpasswordattribute

After you retrieved this password, export the connections from the source-infrastructure database by doing this:

  1. On the source Oracle Business Intelligence installation, run the following command at the command prompt:

<ORACLE_HOME>/sso/bin/ssomig -s ORASSO -p <orasso_password> -c <src_tns_alias> -export

Where:

  • <orasso_ password> is the ORASSO login password.
  • <src_tns_alias> is the net service name for the OracleAS Single Sign-On database.

For example:

<ORACLE_HOME>/sso/bin/ssomig -s ORASSO -p Ov0aR19T -c portalsrc -export

If the source installation and target installation are on different machines, then copy the following files from the source machine to the target machine (most likely: $ORACLE_HOME/sso/log or something like that..):

  • The dump (*.dmp) file, the log file.

Importing the connections:

First retrieve the password again, now from the target installation, with the ldapsearch-command used before here.

On the target Oracle Business Intelligence installation, run the following command at the command prompt:

<ORACLE_HOME>/portal/admin/plsql/sso/ssomig -s ORASSO -p <oraaso_password> -c <dst_tns_alias> -import -merge -discoforce

For example:

<ORACLE_HOME>/portal/admin/plsql/sso/ssomig -s ORASSO -p f9qx233h -c portalsrc -import -merge -discoforce

But….. then the process of import hangs,and it must be aborted. What the ….

Note 392005.1 has a cause and a kind of solution:

Using an earlier version of the import utility against a later version of the database
will hang. That is if you are using a 9.2.0.6 for example against a 10.2 database,
the import will hang. The ssomig utility is just a wrapper script for the database import.

The solutions is to use the import utility that is the same version as that of your target database.
Thus if you target database is 10.2.0 then your import utility should also be 10.2.0

Possible ways to achieve this:

You can install the 10.2 utility into a separate oracle home on your middle tier.
Then setup your environment so that the ssomig will pickup the 10.2 import utility.

Transfer the dump file to the Database Server and manually perform the import.

Option 1 would be the recommended solution.

The only problem is that in this case I have to to run the ssomig-command in the ORACLE_HOME from the infrastructure-database, and this is in this case not the infrastructure-home.

Dirty trick which solved this inconvenience:

–          Copy the complete sso AND the perl-directory (save the old-one !!) from the infrastructure-home to the database-home, and run the ssomig-script from there.

Don’t forget to activate the old perl-directory again after you have done this!