Integrate Oracle LSH with the Oracle Warehouse Builder

After you have installed both the Oracle Warehouse Builder (OWB) and the Oracle Life Sciences Data Hub (Oracle LSH), do the following:

• Set Up Your Environment
  
• Create Directories and Copy Files
  
• Disable Application Server Authentication
  
• Edit run_service.sh
  
• Edit runtime.properties
  
• Edit owb.classpath
  
• Edit Shell Scripts to Match Directory Structure
  
• Run OWB Shell Scripts
  
• Restart the OWB Service
  
• Edit listener_ifile.ora
  

Set Up Your Environment

You must set up your environment as follows.

• Ensure that Oracle SID and Short Global Names Match
  
• Edit init.ora
  
• Restart the Database and Listener
  
• Test Database Connectivity
  

Ensure that Oracle SID and Short Global Names Match

The database short global name must be the same as the Oracle SID. If they are not the same, you must change the short global name to match the Oracle SID.

For example, if the Oracle SID is LSHDB55, the short global name must also be set to LSHDB55.

Edit init.ora

Check the init.ora file and add the following parameters and values if they do not already exist.

Usually located in <ORACLE_HOME>/dbs on the database tier, the file is named initOracle SID.ora; for example, initLSHDB55.ora where the database Oracle SID is LSHDB55.

The required parameter values are:

Compatible: This value must match the database release number; for example, in Oracle LSH 2.5:

compatible=11.2.0

Global Names: If you plan to set up a database link to one or more databases with a name different from the current database, set the Global Names parameter to FALSE both in the current Oracle LSH database and in each database to which you create a link from Oracle LSH.

global_names=FALSE

Job Queue Processes: This parameter value determines the number of job queue processes that are started. The default value is 2. Oracle recommends changing this to a minimum value of 10. If you do not have enough job queues started, OWB processes may not be able to start. However, too many job queues use resources unnecessarily.

job_queue_processes=10

Local Listener: Set as follows:

local_listener="(ADDRESS= (PROTOCOL=TCP)HOST=hostname.domain)(PORT=db_port))"

For example:

local_listener="(ADDRESS=(PROTOCOL=TCP)(HOST=lshdb.your_company.com)(PORT=4321))"

where the hostname is lshdb, the domain is your_company.com, and the port is 4321.

Service Names: Set as follows:

service_names=Oracle_SID, Oracle_SID.domain

For example:

service_names=LSHDB, LSHDB.your_company.com

where the Oracle SID is LSHDB and the domain is your_company.com

Time Manager: The Time Manager process is required to move deferred messages from WAIT state to READY state when the message has passed its delay time. Set the aq_tm_processes parameter to 1 to enable the Time Manager process.

aq_tm_processes=1

Utility File Directory: The first value of the parameter utl_file_dir must match the value of the Oracle Applications variable $APPLPTMP. Otherwise the post-installation program will fail. For instructions on reading and modifying Oracle Applications variables, see Increase JVM Memory.

oa_var=s_applptmp

Open Links per Session: (Required only for Oracle DMW): Oracle suggests setting a value of 5.

open_links_per_session=5

Open Links per Instance: (Required only for Oracle DMW) Oracle suggests setting a value of 200 to support many parallel adapter executions (concurrent loads).

open_links_per_instance=200

Restart the Database and Listener

If you changed the value for the any parameter in the init.ora file, you must stop and start both the database and the listener for the changes to take effect.

Test Database Connectivity

Set up the environment so that you can connect to the database using SQL*Plus for the Oracle SID. This requires having values set for the following two variables in the environment:

• ORACLE_SID

• ORACLE_HOME

Create Directories and Copy Files

Create directories for Oracle LSH in the OWB home directory and copy files into them.

1. Go to the <OWB_HOME> directory.
2. Create a directory under <OWB_HOME> called cdr and run chmod 755 to grant access permissions.
3. Copy files into the <OWB_HOME>/cdr directory as follows:
   • From $FND_SECURE/secure copy the .dbc Oracle Applications database connection file.
   • From $CDR_TOP/jar copy cdr_owb_jars.zip. This zip file contains the Oracle LSH/OWB integration jar files.
   • From $CDR_TOP/admin/template copy installOwbOperator.sh and installOwbAdapter.sh and installOwbBIPAdapter.sh These shell scripts integrate Oracle LSH adapters and operators with OWB.
4. Using a zip utility, unzip cdr_owb_jars.zip to extract the following files:
   • cdr_owb_operators.jar
   • cdr_owb_adapters.jar
   • cdr_owb_bip_adapters.jar
5. Create a directory under <OWB_HOME>/cdr called appslibs.
6. Copy the following files into <OWB_HOME>/cdr/appslibs from

   $JAVA_TOP/oracle/apps/fnd/jar:

   • fndsec.jar
   • fndaolj.jar
   • fndcct.jar

Disable Application Server Authentication

By default when Oracle Applications is installed, the application security authentication is set to ON. To enable OWB to find the jdbc connection from the dbc file during Business Area installation, set this to OFF by doing the following:

1. Open your Oracle LSH URL and log in as sysadmin.
2. Click System Administration in the left-hand column under Navigator. The system refreshes the page and adds a column of links on the right.
3. Under Oracle Applications Manager in the Navigation pane click Dashboard. The Applications Dashboard screen opens.
4. Click Sitemap to go to the Sitemap tab.
5. Under System Configuration, click AutoConfig. The AutoConfig screen opens.
6. Under Context Files, click the Edit Parameters icon for the Application Tier. The Context File Parameters screen opens.
7. From the Search drop-down list, select OA_VAR.
8. In the field next to the Search drop-down list, enter the parameter name s_appserverid_authentication and click Go.
9. In the Value field for s_appserverid_authentication, change the value to OFF.
10. Run AutoConfig. See My Oracle Support article 387859.1, Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12.
11. Stop and start the Apache server for the new value to take effect.

Edit run_service.sh

Edit the run_service.sh file as follows:

1. Go to the directory <OWB_HOME>/owb/bin/unix
2. Back up the run_service.sh as run_service.sh.orig
3. Open run_service.sh.
4. Find the command starting with: $JAVAPATH/bin/java
5. Modify this command by adding the DBC_LOCATION environment variable using the -D command line option. Enter the following string before the first existing -D parameter in the command:

   -DDBC_LOCATION="${OWB_HOME}/cdr/DBC_file_name.dbc"
   

   For example:

   $JAVAPATH/bin/java -Xmx768M -DDBC_LOCATION="${OWB_HOME}/cdr/XYZ.dbc" -D...
   

   where XYZ.dbc is the DBC file name.

Edit runtime.properties

This step enables OWB to read the setting of the profile LSH: Use Character Semantics for Workarea Installation. You can then set that profile to select whether to use byte or character semantics during Work Area installation. The default setting is byte. If your data includes special characters, Oracle recommends using character semantics to help ensure that Oracle LSH stores and displays the characters correctly. See the Oracle Life Sciences Data Hub System Administrator's Guide for more information and instructions for setting the profile.

Note:

This step is required for all Oracle Health Sciences Data Management Workbench (Oracle DMW) installations.

Edit the Runtime.properties file as follows:

1. Go to the directory OWB_HOME/owb/bin/admin.
2. Edit Runtime.properties by adding the following lines at the end:

   connection.init_session=apps.cdr_owb_session_setup

Edit owb.classpath

Edit the owb.classpath file as follows:

1. Go to the directory <OWB_HOME>/owb/bin/admin.
2. Back up the owb.classpath as owb.classpath.orig
3. Edit owb.classpath. After the section on "OWB external jars" add the following lines:

   $OWB_HOME/cdr/cdr_owb_operators.jar

   $OWB_HOME/cdr/cdr_owb_adapters.jar

   $OWB_HOME/cdr/appslibs/fndsec.jar

   $OWB_HOME/cdr/appslibs/fndaolj.jar

   $OWB_HOME/cdr/appslibs/fndcct.jar

   If you are using or plan to use Oracle Business Intelligence Publisher, add the following lines as well:

   $OWB_HOME/cdr/cdr_owb_bip_adapters.jar

   $OWB_HOME/owb/lib/int/rtpcommon.jar

   $OWB_HOME/owb/lib/int/rtpplatform.jar

   $OWB_HOME/lib/xmlparserv2.jar

   $OWB_HOME/lib/activation.jar

   $OWB_HOME/lib/mail.jar

   $OWB_HOME/oc4j/j2ee/home/lib/http_client.jar

   $OWB_HOME/jdev/lib/jdev-rt.jar

   $IAS_ORACLE_HOME/BC4J/lib/bc4jdomorcl.jar

   Note:

   If your database and application servers are installed on different machines, you must copy this file from the application server to the $OWB_HOME/cdr/appslibs directory on the database server and include the new path instead of the line above:

   $OWB_HOME/cdr/appslibs/bc4jdomorcl.jar

   In addition, for use with Oracle Business Intelligence Publisher, add the physical path for the Oracle Home:

   ORACLE_HOME_VALUE/oc4j/webservices/lib/soap.jar

Edit Shell Scripts to Match Directory Structure

Before you run the shell scripts, edit the following values in each script to reflect your environment values:

• DB_HOST

• DB_PORT

• DB_SERVICE_NAME

• DB_SID

• OWB_HOME

Make sure that the <OWB_HOME> directory is set up the same way as in the file pathnames in the files installOwbOperator.sh and installOwbAdapter.sh; see Run OWB Shell Scripts.

You must edit the value of DB_USER_PASS if the OWBSYS account password has been reset:

DB_USER_NAME=owbsys

DB_USER_PASS=<password> (the password created for the OWBSYS account)

Run OWB Shell Scripts

To run the scripts:

Note:

These scripts require using a bash shell. Other shells do not work.

1. Change directory to <OWB_HOME>/cdr.
2. Install the adapters by running the following scripts. The third script is required only if you are using the Oracle Business Intelligence Publisher (BIP) adapter (required for creating BIP Programs).

   ./installOwbOperator.sh

   ./installOwbAdapter.sh

   ./installOwbBIPAdapter.sh

No Java exceptions should occur.

Note:

If you run installOwbOperator.sh more than once, you receive a unique constraint violation error. You can safely ignore this message.

The system creates a log file for each script in <OWB_HOME>/cdr called installOwbOperator.log and installOwbAdapter.log. The log files spool out the actual Java statement that the shell script sets up.

Restart the OWB Service

You must restart the OWB service. You will need the password you created for the OWBSYS account.

• Stop the OWB Service
  
• Starting the OWB Service
  

Stop the OWB Service

To stop the OWB service do the following:

1. Change directory to $ORACLE_HOME/owb/rtp/sql
2. Log into SQL*Plus as OWBSYS.
3. At the SQL prompt, enter:

   @stop_service.sql

When the system displays the words "Not Available" the service is stopped.

Starting the OWB Service

To start the OWB service do the following:

1. Change directory to $ORACLE_HOME/owb/rtp/sql
2. Log into SQL*Plus as OWBSYS.
3. At the SQL prompt, enter:

   @start_service.sql

When the system displays the word "Available" the service has been restarted.

Edit listener_ifile.ora

If you have not already done so, configure the database listener to allow OWB to communicate with the database by editing your listener_ifile.ora file; see Edit listener_ifile.ora.