4 Upgrade Tasks

This chapter describes OCDA upgrade tasks that you must complete before you begin to use the OCDA. This chapter includes the following topics:

• Upgrading OCDA 2.0 to OCDA 2.0.0.1

• Upgrading OCDA 2.0.0.1 to OCDA 2.0.0.2

Upgrading OCDA 2.0 to OCDA 2.0.0.1

You must install the OCDA Release 2.0.0.1 as a patch to OCDA Release 2.0.

Installing the 2.0.0.1 Patch

To install the patch:

1. Log in to Oracle LSH as a user who can execute ETL Programs.

   For more information, refer to Oracle Clinical Development Analytics User and Administrator Guide Release 2.0.0.1 (Security).

2. Is there is an existing version of OCDA 2.0 up and running,

   • Yes. Go to step 3.

   • No. Go to step 4.

3. Perform the following steps for OCDA_INFA_Geo_Dim_SIL_PRG:

   1. Navigate OCDA_domain > OCDA_CODE_APP_AREA > OCDA_WORK_AREA.

   2. Click the OCDA_INFA_Geo_Dim_SIL_PRG hyperlink.

   3. Click Submit.

   4. Enter the following information in Submission Details:

      • Submission Type: Backchain

      • Force Execution: Yes

   5. Click Submit.

   6. Navigate to the LSH Home Page.

   7. Click the Job ID of the program you submitted in backchain in step b.

      This opens the Job Execution Details page of the job.

   8. Click Cancel Job.

   Similarly, repeat steps a to h for each of the following programs.

   • OCDA_INFA_LOV_Dim_SIL_PRG

   • OCDA_INFA_Party_Dim_SIL_PRG

   • OCDA_INFA_Party_Per_Dim_SIL_PRG

   • OCDA_INFA_Party_Org_Dim_SIL_PRG

   • OCDA_INFA_User_Dim_SIL_PRG

   • OCDA_INFA_CRF_Book_Dim_SIL_PRG

   • OCDA_INFA_CRF_Dim_SIL_PRG

   • OCDA_INFA_Employee_Dim_SIL_PRG

   • OCDA_INFA_Product_Dim_SIL_PRG

   • OCDA_INFA_Program_Dim_SIL_PRG

   • OCDA_INFA_Site_Dim_SIL_PRG

   • OCDA_INFA_Study_Dim_SIL_PRG

   • OCDA_INFA_Study_Region_Dim_SIL_PRG

   • OCDA_INFA_Study_Site_Dim_SIL_PRG

   • OCDA_INFA_Study_Subject_Dim_SIL_PRG

   • OCDA_INFA_Validation_Procedure_SIL_PRG

4. On the Oracle LSH database server, create a directory and set the permissions on the directory to 777.

   This directory will be used to copy the patch related files and will also contain the patch installation log files.

5. Log in to Oracle LSH database using the apps account and create a database directory on the Oracle LSH database.

   For example,

   create or replace directory CDA_PATCH_IMP as <db directory>;

   where

   <db directory> is the directory created in step 4.

6. Log out of the Oracle LSH database.

7. Copy the extracted OCDA_domain_2.0.0.1.zip from the temporary staging location to the directory created in step 5.

8. Copy the cdrruwainstall.sql and cdrruimport.sql from $CDR_TOP/patch/115/sql/ to the parent directory of the directory created in step 5. For example, if the database directory created in step 5 is /home/cdapatch_2001, copy the files to /home

   Note:

   If Oracle LSH Application server is on a separate physical machine, copy the cdrruwainstall.sql and cdrruimport.sql files from the Oracle LSH Application server to the Oracle LSH Database server.

9. Navigate to the directory where OCDA_domain_2.0.0.1.zip is placed, and run the following command:

   sqlplus apps/<apps_password>@<DB_INSTANCE> @../cdrruimport.sql <LSH_APPL_USER> <DB_DIRECTORY> OCDA_domain_2.0.0.1.zip MANUAL_COMMIT

   where

   <DB_INSTANCE> is the service name for the database where Oracle LSH is installed.

   <LSH_APPL_USER> is the LSH user account as created in the Creating an Oracle Life Sciences Data Hub User Account.

   <DB_DIRECTORY> is the logical DB directory name mapped to the Operating System (OS) directory containing the OCDA_domain_2.0.0.1.zip to be imported.

   Note:

   Use the ocda_domain_import.log file to verify if the script has executed successfully.

10. Navigate to OCDA_domain > OCDA_SOURCES_APP_AREA > OCDA_OC_DATA_WA and ensure that the following connections are set in Oracle LSH:

    Table 4-1 Connections in Oracle LSH

    Remote Connection	Load Set
    OCDA_OC_OLTP_RL/OPA	OCDA_OC_OPA_LS
    OCDA_OC_OLTP_RL/RXA_DES	OCDA_OC_RXA_DES_LS
    OCDA_OC_OLTP_RL/RXC	OCDA_OC_RXC_LS

    
    

    For more information about setting up remote locations in Oracle LSH, refer to Oracle Clinical Installation Guide Release 2.0.0.1 (Post Installation Tasks)

11. Navigate to OCDA_domain > OCDA_SOURCES_APP_AREA > OCDA_SC_DATA_WA and ensure that the following connections are set in Oracle LSH:

    Table 4-2 Connections in Oracle LSH

    Remote Connection	Load Set
    OCDA_SC_OLTP_RL/SIEBEL	OCDA_SC_LS

    
    

    Note:

    If you have used either the view creation script or the synonym creation script, you must use the same user who is the owner of the schema for setting OCDA_SC_OLTP_RL.

    Note:

    If Siebel clinical is one of your sources, follow the section Handling Deletion in Oracle's Siebel Clinical

12. Navigate to OCDA_domain > OCDA_CODE_APP_AREA >OCDA_SDE_SC_WORK_AREA and ensure that the following connections are set in Oracle LSH:

    Table 4-3 Connections in Oracle LSH

    Remote Connection	Load Set
    OCDA_SC_OLTP_RL/SIEBEL	OCDA_S_PARTY_LS

    
    

    Note:

    If you have used either the view creation script or the synonym creation script, you must use the same user who is the owner of the schema for setting OCDA_SC_OLTP_RL.

    Note:

    If Siebel clinical is one of your sources, follow the section Handling Deletion in Oracle's Siebel Clinical

13. Navigate to OCDA_domain > OCDA_SOURCES_APP_AREA > OCDA_CUSTOM_TABLE_WA and ensure that the OCDA_CUSTOM_OLTP_RL/RXI remote connection is set for OCDA_RXI_LS load set.

    Caution:

    Ensure that the Informatica Distributed Processing (DP) Server is up and running. For more information on setting up DP Server in Oracle LSH, refer to Oracle Life Sciences Data Hub System Administrator's Guide (Setting Up the Distributed Processing Server section in Chapter 1, Setting Up Services.

14. If this is a fresh installation of OCDA,

    • Yes. Go to step 15.

    • No. Go to step 16.

15. Before you run the work area install script, install the following work areas manually in Full mode in the given order:

    In OCDA_domain > OCDA_SOURCES_APP_AREA:

    1. OCDA_CUSTOM_TABLE_WA

    2. OCDA_DELETE_LOG_TABLE_WA

    3. OCDA_DWH_PASS_THROUGH_WA

    4. OCDA_CONTROL_TABLE_WA

    In OCDA_domain > OCDA_CODE_APP_AREA:

    1. If you have used the ocda_config_src.sql script to disable any of the source systems:

       If you have disabled Oracle Clinical, do not install OCDA_SDE_OC_WORK_AREA.

       If you have disabled Siebel Clinical, do not install OCDA_SDE_SC_WORK_AREA.

    2. OCDA_POOL_WORK_AREA

    3. OCDA_WORK_AREA

       Perform the following steps for OCDA_WORK_AREA:

    1. Navigate to OCDA_domain > OCDA_CODE_APP_AREA > OCDA_WORK_AREA > OCDA_ETL_RUN_S_POP_PRG.

    2. Click Check Out. The Check Out screen appears.

    3. Select Check out existing definition and click Apply.

    4. In the Source Code subtab of OCDA_CTRL_TABLE_RET_PKG_SRC.sql, click Remove.

    5. In the Source Code subtab of OCDA_CTRL_TABLE_RET_PKG_SRC.sql, click Add. The system displays the Create Source Code screen.

    6. Select Create an instance of an existing Source Code definition.

    7. Select Yes for Static Reference.

    8. Click the Search icon for the Definition Source field.

    9. Browse and add the definition source for OCDA_CTRL_TABLE_RET_PKG_SRC.sql from OCDA_domain > OCDA_SOURCES_APP_AREA > OCDA_CONTROL_TABLE_WA > OCDA_CONTROL_TABLE_RETRIEVE_PRG.

    10. Install OCDA_WORK_AREA work area in Full mode.

    In OCDA_domain > OCDA_UTIL_APP_AREA:

    1. OCDA_SHARED_PROGRAM_WA

    2. OCDA_ETL_WORKFLOW_WA

16. Navigate to the work area specified and select all the objects that are checked out and click Install:

    Tip:

    You can identify the checked out object in the Work Area screen. Their latest version will be greater than the installed version.

    Table 4-4 List of Objects

    Name	Object Type	Path
    OCDA_UTIL_APP_AREA	Application Area	OCDA_domain
    OCDA_WORK_AREA	Work Area	OCDA_domain > OCDA_CODE_APP_AREA
    OCDA_SDE_SC_WORK_AREA	Work Area	OCDA_domain > OCDA_CODE_APP_AREA
    OCDA_SDE_OC_WORK_AREA	Work Area	OCDA_domain > OCDA_CODE_APP_AREA
    OCDA_CONTROL_TABLE_WA	Work Area	OCDA_domain > OCDA_SOURCES_APP_AREA
    OCDA_OBIEE_WA	Work Area	OCDA_domain > OCDA_OBIEE_CODE_APP_AREA
    OCDA_POOL_WORK_AREA	Work Area	OCDA_domain > OCDA_CODE_APP_AREA

    
    

17. On the Oracle LSH database server, navigate to the directory where OCDA_domain_2.0.0.1.zip is placed (directory created in step 3), and run the following work area (WA) install script:

    sqlplus apps/<apps_password>@<DB_INSTANCE> @../cdrruwainstall.sql <LSH_APPL_USER> <DB_DIRECTORY> OCDA_domain_2.0.0.1.zip

    where:

    <DB_INSTANCE> is the service name for the database where Oracle LSH is installed.

    <LSH_APPL_USER> is the LSH user account as created in the Creating an Oracle Life Sciences Data Hub User Account.

    <DB_DIRECTORY> is the logical DB directory name mapped to the Operating System (OS) directory containing the OCDA_domain_2.0.0.1.zip to be imported.

    Note:

    Use the ocda_domain_import.log file to verify if the script has executed successfully.

    Gathering Schema Statistics

    After you have successfully run the WA install script, run the Gather Schema Statistics concurrent program to gather table statistics. For more information about gathering table statistics, refer to Gathering Table Statistics, Gathering Table Statistics.

18. In Oracle LSH, execute the ETL Programs. Follow steps listed in Oracle Clinical Development Analytics User and Administrator Guide Release 2.0.0.1 (Executing the ETL Programs).

19. If you use OC4J as your application server, perform the following steps:

    1. Copy the help.zip from the temporary staging location to the following locations:

       <OBIEE_directory>/oc4j_bi/j2ee/home/applications/analytics/analytics/res/s_oracle10/help

       <OBIEE_directory>/web/app/res/s_oracle10/help

       where:

       <OBIEE_directory> is the location where OBIEE is installed.

    Deploy OCDA's help and images files on Oracle WebLogic Managed Server. Perform post installation steps listed in Deploying Oracle Clinical Development Analytics on Oracle WebLogic Managed Server, Deploying Oracle Clinical Development Analytics on Oracle WebLogic Managed Server (Chapter 3, Post Installation Tasks.

You have now installed the OCDA patch. To confirm that the patch has been successfully applied, start Dashboards and confirm that the data is displayed.

See Also:

• Oracle Life Sciences Data Hub System Administrator's Guide

• Oracle Life Sciences Data Hub Installation Guide

Upgrading OCDA 2.0.0.1 to OCDA 2.0.0.2

You must install the OCDA Release 2.0.0.2 as a patch to OCDA Release 2.0.0.1

Installing the 2.0.0.2 Patch

To install the patch:

1. Log in to Oracle LSH as a user who can execute ETL Programs.

   For more information, refer to Oracle Clinical Development Analytics User and Administrator Guide Release 2.0.0.2 (Security).

2. On the Oracle LSH database server, create a directory and set the permissions on the directory to 777.

   This directory will be used to copy the patch related files and will also contain the patch installation log files.

3. Log in to Oracle LSH database using the apps account and create a database directory on the Oracle LSH database.

   For example,

   create or replace directory CDA_PATCH_IMP as <db directory>;

   where

   <db directory> is the directory created in step 3.

4. Log out of the Oracle LSH database.

5. Copy the extracted OCDA_domain_2.0.0.2.zip from the temporary staging location to the directory created in step 5.

6. Copy the cdrruwainstall.sql and cdrruimport.sql from $CDR_TOP/patch/115/sql/ to the parent directory of the directory created in step 5. For example, if the database directory created in step 5 is /home/cdapatch_2001, copy the files to /home

   Note:

   If Oracle LSH Application server is on a separate physical machine, copy the cdrruwainstall.sql and cdrruimport.sql files from the Oracle LSH Application server to the Oracle LSH Database server.

7. Navigate to the directory where OCDA_domain_2.0.0.2.zip is placed, and run the following command:

   sqlplus apps/<apps_password>@<DB_INSTANCE> @../cdrruimport.sql <LSH_APPL_USER> <DB_DIRECTORY> OCDA_domain_2.0.0.2.zip MANUAL_COMMIT

   where

   <DB_INSTANCE> is the service name for the database where Oracle LSH is installed.

   <LSH_APPL_USER> is the LSH user account as created in the Creating an Oracle Life Sciences Data Hub User Account section.

   <DB_DIRECTORY> is the logical DB directory name mapped to the Operating System (OS) directory containing the OCDA_domain_2.0.0.2.zip to be imported.

   Note:

   Use the OCDA_domain_2.0.0.2_import.log file to verify if the script has executed successfully.

8. Perform the following steps for OCDA_SDE_SC_WORK_AREA:

   1. Navigate OCDA_domain > OCDA_CODE_APP_AREA > OCDA_SDE_SC_WORK_AREA.

   2. Select OCDA_S_PARTY_LS and S_PARTY objects and remove them.

   3. Click the OCDA_PLS_SC_PARTY_HIERARCHY_PRG hyperlink.

   4. Click Submit.

   5. Enter the following information in Submission Details:

      • Submission Type: Backchain

      • Force Execution: Yes

   6. Click Submit.

   7. Navigate to the LSH Home Page.

   8. Click the Job ID of the program you submitted in backchain in step c.

      This opens the Job Execution Details page of the job.

   9. Click Cancel Job.

9. Navigate to OCDA_domain > OCDA_SOURCES_APP_AREA > OCDA_OC_DATA_WA and ensure that the following connections are set in Oracle LSH:

   Table 4-5 Connections in Oracle LSH

   Remote Connection	Load Set
   OCDA_OC_OLTP_RL/OPA	OCDA_OC_OPA_LS
   OCDA_OC_OLTP_RL/RXA_DES	OCDA_OC_RXA_DES_LS
   OCDA_OC_OLTP_RL/RXC	OCDA_OC_RXC_LS

   
   

   For more information about setting up remote locations in Oracle LSH, refer to Oracle Clinical Installation Guide Release 2.0.0.2 (Post Installation Tasks)

10. Navigate to OCDA_domain > OCDA_SOURCES_APP_AREA > OCDA_SC_DATA_WA and ensure that the following connections are set in Oracle LSH:

    Table 4-6 Connections in Oracle LSH

    Remote Connection	Load Set
    OCDA_SC_OLTP_RL/SIEBEL	OCDA_SC_LS

    
    

    Note:

    If you have used either the view creation script or the synonym creation script, you must use the same user who is the owner of the schema for setting OCDA_SC_OLTP_RL.

    Note:

    If Siebel clinical is one of your sources, follow the section Handling Deletion in Oracle's Siebel Clinical

11. Navigate to OCDA_domain > OCDA_SOURCES_APP_AREA > OCDA_CUSTOM_TABLE_WA and ensure that the OCDA_CUSTOM_OLTP_RL/RXI remote connection is set for OCDA_RXI_LS load set.

12. Navigate to the work area specified and select all the objects that are checked out and click Install:

    Tip:

    You can identify the checked out object in the Work Area screen. Their latest version will be greater than the installed version.

    Table 4-7 List of Objects

    Name	Object Type	Path
    OCDA_UTIL_APP_AREA	Application Area	OCDA_domain
    OCDA_INFA_Party_Parent_SDE_SC_PRG	Work Area	OCDA_domain > OCDA_CODE_APP_AREA > OCDA_SDE_SC_WORK_AREA
    OCDA_INFA_Subject_Prtcptn_Fact_SDE_SC_PRG	Work Area	OCDA_domain > OCDA_CODE_APP_AREA > OCDA_SDE_SC_WORK_AREA
    OCDA_PLS_SC_PARTY_HIERARCHY_PRG	Work Area	OCDA_domain > OCDA_CODE_APP_AREA > OCDA_SDE_SC_WORK_AREA
    OCDA_INFA_Subject_Status_Fact_SDE_SC_PRG	Work Area	OCDA_domain > OCDA_CODE_APP_AREA > OCDA_SDE_SC_WORK_AREA
    OCDA_INFA_CRF_Dim_SDE_Pool_PRG	Work Area	OCDA_domain > OCDA_CODE_APP_AREA > OCDA_POOL_WORK_AREA
    OCDA_INFA_Application_User_D_SIL_PRG	Work Area	OCDA_domain > OCDA_CODE_APP_AREA > OCDA_WORK_AREA
    OCDA_INFA_Study_Site_Access_Sec_SIL_PRG	Work Area	OCDA_domain > OCDA_CODE_APP_AREA > OCDA_WORK_AREA
    OCDA_INFA_Discrepancy_Agg_Fact_SIL_PRG	Work Area	OCDA_domain > OCDA_CODE_APP_AREA > OCDA_WORK_AREA
    OCDA_INFA_Discrepancy_Status_Agg_Fact_SIL_PRG	Work Area	OCDA_domain > OCDA_CODE_APP_AREA > OCDA_WORK_AREA
    OCDA_INFA_Subject_Prtcptn_Aggr_Fact_SIL_PRG	Work Area	OCDA_domain > OCDA_CODE_APP_AREA > OCDA_WORK_AREA
    OCDA_INFA_Study_Access_Sec_SIL_PRG	Work Area	OCDA_domain > OCDA_CODE_APP_AREA > OCDA_WORK_AREA

    
    

13. Create an execution setup for OCDA_INFA_Party_Parent_SDE_SC_PRG by doing the following:

    1. Navigate OCDA_domain > OCDA_CODE_APP_AREA > OCDA_SDE_SC_WORK_AREA.

    2. Click the OCDA_INFA_Party_Parent_SDE_SC_PRG hyperlink.

    3. From the Actions list, select Execution Setups and click Go.

    4. Click Create Execution Setup.

    5. In the Name field, enter a meaningful name for the execution setup and click Apply.

    6. Select the newly created execution setup in Execution Setup listing screen and click Set as Default.

    7. Click the Execution Setup's name in the Execution Setup column.

    8. Click the System Parameters subtab in the Execution Setup screen.

    9. Click the Update button in the System Parameters subtab.

    10. In the Submission Type section, select Backchain and Triggered.

    11. Click Apply.

14. Perform the following steps for OCDA_INFA_Party_Parent_SDE_SC_PRG:

    1. Navigate OCDA_domain > OCDA_CODE_APP_AREA > OCDA_SDE_SC_WORK_AREA.

    2. Click the OCDA_INFA_Party_Parent_SDE_SC_PRG hyperlink.

    3. Click Submit.

    4. Enter the following information in Submission Details:

       • Submission Type: Backchain

       • Force Execution: Yes

    5. Click Submit.

    6. Similarly, repeat steps d and e for each of the following programs:

       OCDA_domain > OCDA_CODE_APP_AREA > OCDA_POOL_WORK_AREA > OCDA_INFA_Subject_Prtcptn_Fact_SDE_SC_PRG

       OCDA_domain > OCDA_CODE_APP_AREA > OCDA_POOL_WORK_AREA > OCDA_INFA_CRF_Dim_SDE_Pool_PRG

15. Verify if all Source Independent Load (SIL) programs in OCDA_domain > OCDA_CODE_APP_AREA > OCDA_WORK_AREA have a default Execution Setup with runtime parameters.

16. In Oracle LSH, execute the ETL Programs. Follow steps listed in Oracle Clinical Development Analytics User and Administrator Guide Release 2.0.0.2 (Executing the ETL Programs).

17. Navigate to OCDA_domain > OCDA_OBIEE_CODE_APP_AREA, and install OCDA_OBIEE_WA.

18. Install the OCDA_OBIEE_WA work area in Full mode.

19. If you use OC4J as your application server, perform the following steps:

    1. Copy the help.zip from the temporary staging location to the following locations:

       <OBIEE_directory>/oc4j_bi/j2ee/home/applications/analytics/analytics/res/s_oracle10/help

       <OBIEE_directory>/web/app/res/s_oracle10/help

       where:

       <OBIEE_directory> is the location where OBIEE is installed.

    Manually deploy OCDA's help and images files on Oracle WebLogic Managed Server. Perform post installation steps listed in Deploying Oracle Clinical Development Analytics on Oracle WebLogic Managed Server, Deploying Oracle Clinical Development Analytics on Oracle WebLogic Managed Server (Chapter 3, Post Installation Tasks).

You have now installed the OCDA patch. To confirm that the patch has been successfully applied, start Dashboards and confirm that the data is displayed.

See Also:

• Oracle Life Sciences Data Hub System Administrator's Guide

• Oracle Life Sciences Data Hub Installation Guide