Skip Headers

Oracle® Argus Safety Installation Guide Release 8.0.1 E68657-01 |
| Contents

Previous | Next |

# 3 Installing the Argus Safety Database

This chapter describes the steps in creating, upgrading, and validating the Argus Safety Database schema. The following topics are contained in the chapter:

- Overview

- Schemas Required for Database Instances

- Setting up Oracle Parameters

- Creating the Argus Safety Database Schema

- Loading Factory Data

- Enabling and Disabling Oracle Text

- Working with the MedDRA and MedDRA J Dictionaries

- Loading the WHO-DRUG Dictionary

- Validating the Argus Safety Database

- Enabling and Disabling DLP

- Upgrading the Argus Safety Database

- Merging a Single Enterprise Safety Database into a Multi-tenant Database

- Copy Configuration Tool

## Overview

Argus Safety installation requires a database instance. To set up Axway, a separate database instance is required.

- The Argus Safety database can be set up using the Schema Creation Tool.

- If DLP is to be set up, use the Enable DLP option in the Schema Creation Tool. The DLP Schema is created in the Argus instance only. No separate instance is required for DLP setup

- Axway Synchrony Database Instance (Optional)

  Note: The password can only contain any ASCII Character, 0-9, or any of the following special characters _ # $ when creating new users in Oracle.

  Note: The Create DB User script provided with this release is meant as an alternative to the SYSTEM user. The term SYSTEM mentioned in this chapter can be replaced with the new DBA user. If you use the newly created DBA User to execute the Argus Safety Schema Creation Tool functionalities (such as Schema Creation, Upgrade), then the Validation File might display some extra or missing privileges for the system and/or for the newly created DBA user.

## Schemas Required for Database Instances

The following sections outline the schemas you must create for each database instance.

### Argus Safety Instance Database Schemas

The Argus Safety instance requires you to create database schemas. The Argus schema and the Interchange Service schema are required for all systems. The other schemas you create are MedDRA or WHO.

- Argus Schema:

  Use the Argus Safety Schema Creation Tool to create this database schema. This is a required schema.

- Interchange Service Schema:

  Use the Argus Safety Schema Creation Tool to create this database schema. This is a required schema.

- BIP Schema:

  You must create this schema to hold the BIP Periodic Reporting related objects. This schema must be created even though BIP Periodic Reporting is an optional component.

- DLP Schema:

  This is optional. You can create this schema if DLP is to be enabled.

- MedDRA Schema:

  You must create this schema if MedDRA is to be enabled. This schema is created by the MedDRA Loader Tool when MedDRA is loaded to the new database tables.

- J Drug Schema:

  You must create this schema if J Drug is to be enabled.

- WHO Schema:

  You must create this schema if WHO is to be enabled. This schema is created by the WHO Loader Tool when WHO is loaded to the new database tables.

### Axway Synchrony Database Instance (Optional)

The Axway Synchrony Database Instance is optional and is applicable only if Axway Synchrony is required.

## Setting up Oracle Parameters

This chapter provides the recommended Oracle parameter values for Argus Safety databases.

### Oracle Database Settings

The tables in this section list the suggested parameters, configurations, and/or settings for an Oracle database for various sized companies as follows:

- Small refers to companies with less than 30,000 cases in the database.

- Mid-sized refers to companies with 30,000 to 200,000 cases in the database.

- Large refers to companies with 200,000 to 1 million cases in the database.

- Very Large refers to companies with more than 1 million cases in the database.

#### Argus Safety Database Instance Parameters

Oracle Database parameters are recommendations only, and may differ based on various factors including company's policy, database server needs, configuration and data load. These recommended values should be evaluated for each specific site based on the intended use of the application, business needs, performance testing and adjusted accordingly.

# | Database Parameters | Small | Mid-Sized | Large | Very Large
--- | --- | --- | --- | --- | ---
1 | MEMORY_TARGET | 2 GB | 3 GB | 10 GB | >10 GB
2 | PROCESSES | Expected concurrent users + 100 | Expected concurrent users + 100 | Expected concurrent users + 100 | Expected concurrent users + 100
3 | MEMORY_MAX_TARGET | >= value set for MEMORY_TARGET | >= value set for MEMORY_TARGET | >= value set for MEMORY_TARGET | >= value set for MEMORY_TARGET
4 | OPTIMIZER_SECURE_VIEW_MERGING | FALSE | FALSE | FALSE | FALSE
5 | CURSOR_SHARING | EXACT | EXACT | EXACT | EXACT
6 | WORKAREA_SIZE_POLICY | AUTO | AUTO | AUTO | AUTO
7 | JOB_QUEUE_PROCESSES | 25 | 25 | 25 | 25
8 | SHARED_POOL_SIZE | 500 MB | 500 MB | 1 GB | 2 GB
9 | DB_CACHE_SIZE | 500 MB | 500 MB | 1 GB | 2 GB
10 | DB_BLOCK_SIZE (bytes) | 8192 | 8192 | 8192 | 8192
11 | PGA_AGGREGATE_TARGET | 500 MB | 500 MB | 1 GB | 2 GB

#### Additional Database Setup Information

# | Setting | Small | Mid-Sized | Large | Very Large
--- | --- | --- | --- | --- | ---
1 | Number and Size of Redo Log Files | 5 Groups * 100 MB | 5 Groups * 100 MB | 5 Groups * 100 MB | 5 Groups * 100 MB
2 | TEMP Tablespace Size | 8 GB | 16 GB | 32 GB | 64 GB
3 | Undo Tablespace Size | 8 GB | 16 GB | 32 GB | 64 GB

### GMT Offset Calculation

For column level description, refer to the Oracle Argus DBA Guide. Verify that the value stored in the TABLE is accurate for GMT_DIFF and other columns related to Day Light Saving.

Be aware of the following:

- Argus is using function gss_util.gmt_offset to derive the GMT OFFSET which impacts the calculation of GMT date and time.

- Use the following SQL queries to verify the GMT offset returned by the database function:

  - Query to get the current GMT Time offset

  - Query to get the current Timestamp and GMT Timestamp.

- Daylight Savings Time. Assume that Daylight Savings Time starts on First Sunday of April at 2:00 AM and it ends on Last Sunday of October at 2:00 AM.

  - Query to get GMT Time Difference just before the starting of Day Light Saving.

  - Query to get GMT Time Difference One Second After Day Light Savings started.

  - Query to get GMT Time Difference just before the End of Day Light Saving.

  - Query to get GMT Time Difference just After Day Light Savings ended.

## Prerequisites for New Schema Creation

Before creating a new schema, make sure that you run the "Create DBA User" scripts. This script asks the user to connect as an existing DBA User SYS to create a new DBA User with the proper granted access that will be used while running the Schema Creation Tool.

Note: It is mandatory to run this script when creating a new schema. If you do not wish to create a new DBA user, you can enter SYSTEM when running the script. All the manual grants which used to be assigned to the SYSTEM user (prior to the Argus Safety 8.0 release), are now part of this DBA User script.

If you use the newly created DBA User to execute the Argus Safety Schema Creation Tool functionalities (such as Schema Creation, Upgrade), then the Validation File might display some extra or missing privileges for the system and/or for the newly created DBA user.

To perform the above-mentioned action, go to the Start menu, run the Create DBA User script, and follow the steps given below:

1. Enter a new log file name to store the output of the script execution.

2. Enter the TNSName of the database where the Schema Creation Tool will be run.

3. Enter the Password for SYS account.

4. Enter the name for a new <DBA User account> that will be created.

5. Enter the <password> for the new account.

6. Follow the remaining steps to complete the script.

After the script has successfully run, use the new DBA user account entered in Step 4 when running the Schema Creation Tool to create the Argus Safety Schema.

### Creating the Argus Safety Read Only Database Account (Optional)

If you required a database account that can connect to the Argus Safety Schema with Read Only Privileges, a script has been provided that you can run to create this account.

Note: This is not a requirement to install and run Argus Safety. This is an optional script that can be used to create the read only account for any external interface you may have that needs read only access to the data.

From the Start menu, run the script "Create Read-only Database User" and follow the steps provided in the script.

### Creating the Argus Safety Database Schema

Two (2) required steps and one (1) optional step are involved in creating Argus Safety database schema as follows:

- Installing the Argus Safety schema creation tool

- Creating the Tablespace (optional)

- Creating the Argus schemas using the schema creation tool

  Note: The source Argus Safety Database must have AL32UTF8 character set. When DLP is enabled, DLP Schema will be a part of the Argus Safety database.

  Note: The Argus Safety Database requires the Database semantics to be CHAR and not BYTE. Follow the steps below: Log in to the Database as the SYS user. Execute: ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR SCOPE=BOTH; Shutdown and Startup the database after applying the above statement.

#### XDB Schema Installation Requirement for Interchange

Oracle Schema XDB must be present for Interchange packages to load.

If Schema XDB does not exist, use the following procedure to create it:

1. Click sqlplus.exe

2. Connect to sys as sysdba.

3. Execute the ?/rdbms/admin/catqm.sql script.

4. Provide the following required parameters

- user password

- user default tablespace

- user temporary tablespace

For example: SQL>@?/rdbms/admin/catqm.sql SYSTEM SYSAUX TEMP

#### Installing the Schema Creation Tool

Note: Make sure that you disable the UAC (User Account Control) in order to run the schema creation tool.

Before installing the Schema Creation Tool on a server, verify that an Oracle client with Administrator option is installed on the server.

1. When Argus Safety Setup opens the Argus Safety Solution Components dialog box:

   - Select the Schema Creation Tool.

   - Click Next.

2. The system begins the installation procedure and displays the Setup Status screen.

   - The system displays installation progress.

3. When the system displays the Setup Completed screen:

   - Click Finish.

4. When the system copies the required files to the system and displays the following message:

   - Click OK to reboot the system.

#### Creating the Tablespaces

If you wish to create tablespaces before installing Argus Safety, the following information shows the different tablespaces. However, this step is optional.

# | Tablespace Name
--- | ---
1 | ARGUS_AEXP_DATA_01
2 | ARGUS_AEXP_INDEX_01
3 | ARGUS_AL_DATA_01
4 | ARGUS_AL_INDEX_01
5 | ARGUS_DATA_01
6 | ARGUS_DATA_02
7 | ARGUS_DATA_03
8 | ARGUS_DATA_04
9 | ARGUS_DATA_05
10 | ARGUS_INDEX_01
11 | ARGUS_INDEX_02
12 | ARGUS_INDEX_03
13 | ARGUS_INDEX_04
14 | ARGUS_INDEX_05
15 | ARGUS_INDEX_06
16 | ESM_DATA_01
17 | ESM_INDEX_01

The schema creation tool creates the tablespaces if they do not exist.

#### Creating the Schema

Note: Refer to the chapter Argus Password Management - Cryptography Tool to create the Cryptographic key before creating the new schema.

Before creating the schema, verify that:

- A blank Oracle database instance is available

- A SYSTEM user account is available

- The Oracle database is available from the machine where the schema creation tool is installed

Use the following procedure to create the schema.

1. Open the schema creation tool.

   - Click Create Schema.

2. When the system displays the Oracle Database Connect dialog box, enter the Password associated with the system user and the Database.

   - Enter the password associated with the system user in the Password field and the database name in the Database field.

   - Click OK.

3. When the system displays the Argus Safety Schema Creation Options dialog box:

   - - Enter the user name in the VPD Admin Schema Owner field.

     - Enter the user's password in the VPD Admin Schema Owner Password field.

     - Reenter the user's password in the Reenter Password field.

4. When the system displays the New User dialog box:

   - Enter the user name in the New User Name field.

   - Enter the user's password in the New User Password field.

   - Reenter the user's password in the Reenter Password field.

   - Verify that the Default Tablespace and Temporary Tablespace values are correct.

   - Click OK.

5. When the system displays the Argus Safety Schema Creation Options dialog box, repeat Steps 3 and 4 until you have created all the users.

6. When the system displays the Argus Safety Schema Creation Options dialog box:

   - Click New Role to create the following roles as appropriate:

     - Argus Role

     - Interchange Role

7. When the system displays the New Role dialog box:

   - Type the role name in the New Role field.

   - Click OK.

8. When the system redisplays the Argus Safety Schema Creation Options dialog box:

   - Locate the Argus Safety Schema Owner drop-down list and select the Argus Schema Owner you created.

   - Locate the Schema Options and select the appropriate Database Size and the Time Zone.

   - Select the appropriate Argus Role from the Argus Safety Role drop-down list.

   - Locate Argus Safety Grantees and select the appropriate Argus Login account.

   - Locate the Interchange Support section and do the following:

     - Select the Interchange Schema Owner from the drop-down list.

     - Select the Interchange Role from the drop-down list.

     - Select the Interchange Login User from the drop-down list.

   - Enter password for the ARGUSUSER user.

   - Under BIP, create a new BIP Schema by clicking New User, and select the created schema from the BIP Schema Owner drop-down list.

   - Select an Application Type from the following two radio buttons:

     - Single Tenant - Selecting this option allows the database to only support a single tenant. The options to create multiple tenants in the safety system is diabled.

     - Multi-Tenant - Selecting this option allows the database to support multiple tenants. Users are able to create multiple tenants using the Global Enterprise setup screens.

   - Select the Default Enterprise from the following:

     - Enterprise Name

     - Enterprise Short Name

   - Click Generate.

9. If the Tablespace Creation dialog box displays, you may create new tablespaces or use existing tablespaces as follows:

   - Under Complete Path and Datafile, enter the complete path (including the filename) under which the data file is located on the database server.

   - If the data file does not exist, the system automatically creates it. It will automatically be created.

   - If the data file exists, the system prompts you to use the current data file. Select Yes in the dialog box.

     Note: The Tablespace Creation dialog box appears if the Database Size was selected as Small, Medium, or Large. It will not appear if the database size was selected as Default. When you have existing tablespaces, you may use them; you are not required to create new ones. The system will not regenerate the tablespaces. If a tablespace already exists the Argus Schema Creation tool will warn you to select Yes to use an existing tablespace.

10. When the system opens the Argus Safety Database Installation dialog box:

    - Select Pause on error.

    - Select Continue to start the Schema Creation Process. It may take some time to complete the schema creation process.

      Note: Select Pause on Error to pause the system when an error occurs. This is essential for troubleshooting Schema creation problems. You can also select the Show All box to display the SQL statements the system is executing. However, to create the database schema more quickly, we recommend clearing the Show All check box. The system enters all executed SQL statements in a log file.

11. When the schema creation process is complete:

    - Click Open to open the schema creation log file.

    - Click Finish.

## Creating the Argus Safety Read Only Database Account (Optional)

If you require a database account that can connect to the Argus Safety Schema with Read Only Privileges, a script has been provided that you can run to create this account.

From the Start menu, run the script "Create Read-only Database User" and follow the steps provided in the script.

## Loading Factory Data

Before loading factory data verify that:

- The schema creation tool is installed

- An Oracle database instance is available

- A SYSTEM or DBA user account has been created

To load Factory Data into the Argus Safety database:

1. Open the schema creation tool.

   - Click Factory Data.

2. When the system opens the Connect to Database dialog box, enter the Argus Schema Owner Name, Password, and the Database name in the appropriate fields and click OK.

   - Enter the name of the Argus Schema Owner and the password.

   - Click OK.

3. When the system opens the Connect to Database dialog box a second time:

   - Enter the name of the Interchange Schema Owner and the password.

   - Click OK.

4. Enter the default user passwords for the Admin User and the System User.

   - Verify the passwords for both users in their Password Verify fields.

   - Click OK.

5. The system loads the factory data into the database and displays the following message: Factory Data has been loaded. Please check your factory data folder for "Log" files.

   - Click OK.

6. Check the .LOG files in the \DB Installer\Factory_Data\ folder to verify that the factory data loaded without errors.

7. The system displays the following message: Oracle text is mandatory. Please press the OK button to enable Oracle text.

   - Click OK.

     Note: You can disable the following dashboard triggers, if you are not using the Oracle Argus Safety dashboard feature: TRG_CA_DSHBRD_ROW_AFT_UPD TRG_CMRR_DSHBRD_ROW_AFT_UPD TRG_CMRR_DSHBRD_TBL_AFT_UD TRG_CSRR_DSHBRD_ROW_AFT_UPD TRG_CSRR_DSHBRD_TBL_AFT_IUD TRG_LPF_DSHBRD_ROW_AFT_UPD TRG_LPF_DSHBRD_TBL_AFT_UPD However, if you enable these triggers again, you should populate the data for the existing cases. Since these triggers get enabled after each upgrade, make sure that you disable these triggers. Implement these instructions after completing the database upgrade, listed in the Upgrading the Argus Safety Database section.

## Enabling and Disabling Oracle Text

Oracle Text search is an index-based querying solution that improves Duplicate Case search performance. This section provides information about enabling and disabling Oracle Text.

Note: If you do not use the Schema Creation Tool to install Oracle Text and the Common Profile Switch is enabled, running a search from the Argus Book-in screen can cause the system to display the following error message: Oracle Text is not installed correctly. Please install/verify the Oracle Text installation first.

### Enabling Oracle Text

Once enabled, Oracle Text performs the following functions:

- DB Installer checks whether Oracle Text is installed. If not, it displays an error message that Oracle Text not installed. Please install Oracle Text before adding this feature.

- Estimates the Tablespace Size Requirements and adjusts as required.

- Populates existing cases in the Oracle Text duplicate Search Table for indexing. This process can take a few hours.

- Creates the Oracle Text Index.

- Creates the PDP job for Delta updates.

- Updates the CMN_PROFILE Key, ORA_TXT_SRCH_ENABLE, to a value of 1.

  Before enabling Oracle Text, there must be enough free space available in the tablespace. If there is not enough free space available, the system displays the following dialog box with the amount of space currently available (in megabytes).

  Click OK and provide the required free space before enabling Oracle Text.

Use the following procedure to enable Oracle Text:

1. Open the Schema Creation Tool.

   - Click Oracle Text.

2. When the system displays the Enable/Disable Oracle Text dialog box:

   - Click Yes.

3. When the system displays the Enable Oracle Text dialog box, enter the connection parameter in the Argus Database Name field and click Proceed.

   - Enter the database connection parameter.

   - Enter the Oracle Text Log Directory.

   - Click Proceed to enable Oracle.

   - View Oracle Text Log.

   - Click Close to exit.

4. Oracle Text is enabled. Click Close to exit.

5. Run the schema validation tool to validate the schema.

### Disabling Oracle Text

After Oracle Text is disabled, the system performs the following functions:

- Updates the CMN_PROFILE Key, ORA_TXT_SRCH_ENABLE, to a value of 0

- Deletes the PDP Job

- Drops the Oracle Text Index

- Truncates the Duplicate Case Search Table

Use the following procedure to disable Oracle Text.

1. Open the Schema Creation Tool.

   - Click Oracle Text.

2. When the system displays the Enable/Disable Oracle Text dialog box:

   - Click No.

3. When the system displays the Disable Oracle Text dialog box:

   - Enter the database connection parameter in the Argus Database Name field.

   - Enter the Oracle Text Log Directory.

   - Click Proceed to disable Oracle Text. The system disables Oracle Text.

   - View Oracle Text Log.

   - Click Close to exit.

## Implementing Table Partitioning

Note: Partitioning is an optional module that can be purchased from Oracle database.

Partitioning of CMN_AUDIT_LOG table can significantly improve performance of the system on large Argus Safety databases. Range partitioning can be performed on CMN_AUDIT_LOG table for LOG_DATETIME_STAMP column.

We recommend that you create partitioning on a yearly basis. Partitioning must be performed and maintained by a qualified database administrator.

## Working with the MedDRA and MedDRA J Dictionaries

The minimum space required to install MedDRA and MedDRA J on your system is 50 MB. Verify that you have that amount of space available before loading MedDRA and MedDRA J. You also need to verify that:

- The schema creation tool is installed

- An Oracle database instance is available

- A SYSTEM user account has been created

  Note: If loading MedDRA V8 or V8.1, the smq_list.asc and smq_content.asc files containing SMQ data must be placed in the same folder as the other dictionary files.

### Loading the MedDRA Dictionary

To load the MedDRA dictionary into the database:

1. Open the Schema Creation Tool:

   - Click MedDRA Loader.

2. When the system displays the Oracle Database Connect dialog box, Click OK.

   - Enter the Password associated with the SYSTEM user and the Database name.

   - Click OK.

3. When the system displays the MedDRA Dictionary Loader dialog box, do the following:

   - Select Load to New Tables if a MedDRA dictionary has not been loaded before.

   - Select MedDRA J if you are loading a MedDRA J dictionary.

   - Locate the Tablespace Information section and select the tablespace and index from the drop-down lists. Select the applicable tablespace from the Tables drop-down list.

   - Click Create User to create a new MedDRA user.

4. When the system displays the New MedDRA User dialog box:, enter the appropriate information in the fields and click OK.

   - Enter the name of the user in the New User Name field.

   - Enter the password in the New User Password field.

   - Re-enter the password in the Reenter Password field.

   - Click OK.

5. When the system redisplays the MedDRA Dictionary Loader dialog box again:

   - Click Create Role.

6. When the system displays the New MedDRA Role dialog box:, enter the New Role name and click OK.

   - Enter the new role name in the New Role field.

   - Click OK.

7. When the system redisplays the MedDRA Dictionary Loader dialog box:, locate the Dictionary to Load section an do the following:

   1. Select the MedDRA Version to be loaded from the drop-down list.

   2. Click Browse to go to the directory where the dictionary files reside and select the appropriate dictionary files.

   3. Check the MedDRA Browser check box if this dictionary version is being used in the Argus Safety MedDRA Browser.

   4. Click Load.

   - Select the MedDRA version to be loaded from the MedDRA Version drop-down list.

8. The system loads the dictionary and displays the following message.

   - Click OK.

#### Overwriting an Existing MedDRA Dictionary

If you find it necessary to overwrite an existing MedDRA dictionary, use the following procedure to do so.

1. Open the Schema Creation Tool.

   - Click MedDRA Loader.

2. When the system displays the Oracle Database Connect dialog box:

   - Enter the SYSTEM user password in the Password field and the database name in the Database field.

   - Click OK.

3. When the system displays the MedDRA Dictionary Loader dialog box: locate the Loading Options section and do the following:

   - Select Overwrite.

   - Select MedDRA J if you are loading a MedDRA J dictionary.

   - Select the tablespace and index from the Tablespace and Index drop-down lists.

   - Select the user from the User drop-down list.

   - Enter the user password in the Password field; re-enter it in the Verify Password field.

   - Select the appropriate role from the Role drop-down list.

   - Select the version to overwrite from the Current Version to Overwrite drop-down list.

   - Select the MedDRA version to load from the MedDRA Version drop-down list.

   - Click Browse to go to the directory where the dictionary files reside and select the appropriate dictionary files.

   - Click the MedDRA Browser check box if the dictionary version is being used in the Argus Safety MedDRA Browser.

   - Click Load.

4. When the system displays the Oracle Database Connect dialog box: enter the Password associated with the SYSTEM user and the Database name and click OK.

   - Enter the SYSTEM user password in the Password field and the database name in the Database field.

   - Click OK.

5. When the system finishes overwriting the dictionary, it displays the Dictionary Load dialog box.

   - Click OK.

#### Recoding Events

The following table lists and describes the options in the dialog box.

Event Recoding Dialog Box Options

Option | Point E
--- | ---
Argus MedDRA Version to Re-code | Select the existing MedDRA version to re-code.
Enterprises | Select the enterprises to recode.
Data Update/View Options [Currency determined at LLT Level Only] | Check one or both of the following options: Process Current Terms (Using Primary SOC Path) Process Non-current Terms (Using Primary SOC Path) Select one of the following options: Update Data (Updates will be made to cases and to the audit log.) View Only (Updates will not be made to cases and to the audit log).
Output Log File Options | Select an output log file option and directory path for the log files.
Status | Displays status.

If you find it necessary to recode events, use the following procedure to do so:

1. Open the Schema Creation Tool.

   - Click MedDRA Loader.

2. When the system displays the Oracle Database Connect dialog box, enter the Password associated with the SYSTEM user and the Database name.

   - Enter the password for the SYSTEM user in the Password field and the database name in the Database field.

   - Click OK.

3. When the system displays the MedDRA Dictionary Loader dialog box:

   - Click the Re-Code button.

4. When the system opens the Event Re-Coding dialog box, do the following:

   - Select the Enterprise to recode.

     Note: If Argus is setup in Single Tenant Mode, you will only have one option here. If you are setup as a Multi-Tenant Database, you can choose which Enterprises to recode. Multiple enterprises can be selected.

   - Select the existing version of MedDRA that needs to be re-coded.

     - Select a specific version to only recode data coded with that version.

     - Select All to recode all existing coded data regardless of the version it is coded with.

   - Select either or all of the Process Current Terms, Process Non-Current Terms and/or Update dictionary version check boxes.

   - Select Update Data if events are to be updated or select View Only if you are interested is just seeing what events will be coded without making the changes.

   - Select the Output File format.

     - Delimited Text

     - Excel Sheet output

   - Click on the Execute button to start the recoding process.

   - When the system displays the Connect to Database dialog box, enter the Schema Owner name, Password, and Database. Click OK.

     - Enter the schema owner name in the Argus Schema Owner field.

     - Enter the password in the Password field.

     - Enter the database name in the Database field.

   - The system recodes the following fields from Case Form and Code List.

     Field Location | Name of Recoded Field
     --- | ---
     Case Form | Death Details Lab Data Other Relevant History Product Indications Events Case Diagnosis
     Code List | Product Indication Lab Test Types

### Loading the J Drug Dictionary

To load the J Drug dictionary into the database:

1. Open the Schema Creation Tool:

   - Click J Drug Loader.

2. When the system displays the Oracle Database Connect dialog box, Click OK.

   - Enter the Password associated with the SYSTEM user and the Database name.

   - Click OK.

3. When the system displays the J Drug Dictionary Loader dialog box, do the following:

   - Select Load to New Tables if a J-Drug dictionary is not loaded before.

   - Locate the Tablespace Information section and select the tablespace and index from the drop-down lists.

   - Click Create User to create a new J-Drug user

4. When the system displays the New J-Drug User dialog box:, enter the appropriate information in the fields and click OK.

   - Enter the name of the user in the New User Name field.

   - Enter the password in the New User Password field.

   - Reenter the password in the Reenter Password field.

   - Click OK.

5. When the system redisplays the J-Drug Dictionary Loader dialog box again:

   - Click Create Role.

6. When the system displays the New J-Drug Role dialog box:, enter the New Role name and click OK.

   - Enter the new role name in the New Role field.

   - Click OK.

7. When the system redisplays the J-Drug Dictionary Loader dialog box:, locate the Dictionary to Load section an do the following:

   1. Select the J-Drug Version to be loaded from the drop-down list.

   2. Click Browse to go to the directory where the dictionary files reside and select the appropriate dictionary files.

   3. Check the J-Drug Browser check box if this dictionary version is being used in the Argus Safety MedDRA Browser.

   4. Click Load.

8. The system loads the dictionary and displays the following message.

   - Click OK.

### Overwriting an Existing J Drug Dictionary

This section provides instructions for overwriting an existing J Drug dictionary and for recoding events.

If you find it necessary to overwrite an existing J Drug dictionary, use the following procedure to do so.

1. Open the Schema Creation Tool.

   - Click J Drug Loader.

2. When the system displays the Oracle Database Connect dialog box:

   - Enter the SYSTEM user password in the Password field and the database name in the Database field.

   - Click OK.

3. When the system displays the J Drug Dictionary Loader dialog box: locate the Loading Options section and do the following:

   - Select Overwrite.

   - Select the tablespace and index from the Tablespace and Index drop-down lists.

   - Select the user from the User drop-down list.

   - Enter the user password in the Password field; re-enter it in the Verify Password field.

   - Select the appropriate role from the Role drop-down list.

   - Select the J Drug dictionary version to load from the Dictionary Version drop-down list.

   - Click Browse to go to the directory where the dictionary files reside and select the appropriate dictionary files.

   - Click Load.

4. When the system displays the Oracle Database Connect dialog box: enter the Password associated with the SYSTEM user and the Database name and click OK.

   - Enter the SYSTEM user password in the Password field and the database name in the Database field.

   - Click OK.

5. When the system finishes overwriting the dictionary, it displays the Dictionary Load dialog box.

   - Click OK.

## Loading the WHO-DRUG Dictionary

Before loading the WHO-DRUG dictionary, verify the following:

- Windows workstation PC is available to load the WHO-DRUG data on

- The PC has Oracle client installed, including the following:

  SQLPLUS (Exe=sqlplusw)

  SQL*Loader (Exe=sqlldr)

- There is an updated TNSNAMES file and Oracle client to connect to the Argus Safety database.

- The following WHO-DRUG dictionary data files are available:

  bna.dd | ccode.dd
  dda.dd | ddsource.dd
  ing.dd | man.dd
  dd.dd | ina.dd

- The format of the WHO-DRUG dictionary data files is Text and alternate rows are not blank.

  Note: WHO-DRUG is loaded using sql*load with DIRECT=TRUE option. Because of sql*loader restrictions, no one should have access to the Argus Safety system while WHO-DRUG is being loaded.

You can load WHO-Drug dictionary as follows:

- Use the Load to New Tables option to load the dictionary to new tables

- Use the Overwrite option to overwrite existing and existing dictionary

- Use the Format C option to load the dictionary with a different format

### Loading the WHO-Drug Dictionary to New Tables

Use the following procedure to load WHO-Drug dictionary to new tables:

1. Launch the Schema Creation Tool:

   - Click Who Drug Loader.

2. When the system displays the Oracle Database Connect dialog box:

   - Enter the SYSTEM password in the Password field. Enter the database name in the Database field.

   - Click OK.

3. When the system opens the WHO-Drug Dictionary Loader dialog box do the following:

   - Click Load New Tables to load the dictionary into a separate schema.

   - Click Create User to open the New WHO-Drug User dialog box to open the New WHO-Drug User dialog box.

     Provide the information required to create a new user and click OK.

4. The system reopens the WHO-Drug Dictionary Loader dialog box, click Create Role to open the New WHO-Drug Role dialog box.

   - In the New WHO-Drug Role dialog box, enter the New Role name and click OK.

5. When the system redisplays the WHO-Drug Dictionary Loader dialog box, locate the Dictionary to Load section and do the following:

   - In the New WHO-Drug Role dialog box, enter the New Role name and click OK.

6. When the system displays the WHO-Drug Dictionary Loader dialog box with the appropriate information: click Load.

7. When the system displays the Dictionary Load dialog box to indicate that the dictionary has loaded successfully: click OK.

   - Enter the SYSTEM password in the Password field. Enter the database name in the Database field.

   - Click OK.

### Loading the WHO-Drug Dictionary Using the Overwrite Option

Use the following procedure to when using the overwrite option to load the WHO-Drug Dictionary:

1. Launch the Schema Creation Tool.

2. Click Who Drug Loader.

3. When the system opens the Oracle Database connect dialog box:

   - Enter the SYSTEM password in the Password field. Enter the database name in the Database field.

   - Click OK.

4. When the system opens the WHO-Drug Dictionary Loader dialog box, do the following:

   - Click Overwrite to overwrite existing dictionary files.

   - Select the dictionary version to load.

   - Click Browse to display the Select Folder dialog box and select the appropriate path and click Select.

   - Click Load to load the dictionary.

   - View WHO-Drug dictionary log.

5. When the system opens the Oracle Database Connect dialog box, enter the SYSTEM User Password and click OK.

   - Enter the SYSTEM password in the Password field. Enter the database name in the Database field.

   - Click OK.

6. When the system displays to Dictionary Load dialog box to indicate that the dictionary has loaded successfully:

   - Click OK.

### To Load the WHO-Drug Dictionary using the Format C Option

Format C is a WHO-Drug dictionary format. For information about this format, go to `http://who-umc.org`.

To load the WHO-DRUG dictionary using the Format C option:

1. Launch the Schema Creation Tool.

   - Click Who Drug Loader.

2. When the system displays the Oracle Database Connect dialog box, enter the SYSTEM Password and Database name. Click OK.

   - Enter the SYSTEM password in the Password field. Enter the database name in the Database field.

   - Click OK.

3. When the system opens the WHO-Drug Dictionary Loader dialog box do the following:

   - Click Load New Tables to load the dictionary into a separate schema.

   - Click Create User to open the New WHO-Drug User dialog box When the system opens the New WHO-Drug User dialog box, provide the information required to create a new user and click OK.

   - Select Dictionary Format - Format C

4. When the system reopens the WHO-Drug Dictionary Loader dialog box:

   - Click Create Role to open the New WHO-Drug Role dialog box. Provide the information required to create the new role. Click OK.

5. When the system redisplays the WHO-Drug Dictionary Loader dialog box:

   - Select the Dictionary Version to load from the drop-down list.

   - Click Browse to display the Select Folder dialog box and select the appropriate path.

6. When the system displays the WHO-Drug Dictionary Loader dialog box with the appropriate information:

   - Click Load.

7. When the system opens the Oracle Database Connect dialog box:

   - Enter the SYSTEM password in the Password field. Enter the database name in the Database field.

   - Click OK.

8. When the system displays the Dictionary Load dialog box to indicate that the dictionary has loaded successfully, click OK.

## Validating the Argus Safety Database

A necessary step in installing Argus Safety is to validate the database after installation. Use the following procedure to validate the Argus Safety database.

Note: If you are creating a fresh Argus Safety database, be sure the factory data is loaded before running the Schema Validation tool.

To validate the Argus Safety database:

1. Launch the Schema Creation Tool.

   - Click Schema Validation.

2. When the system opens the Connect to Database dialog box:

   - Enter the user Password.

   - Enter the name of the database to be validated in the Database field.

   - Click OK.

3. When the system displays the Schema Validation dialog box:

   - Validate the values in the fields.

   - Locate the Validation CTL File section and click Browse to open the Selection Path for CTL File dialog box.

4. When the system opens the Selection Path for CTL file dialog box:

   - Click OK.

   - Locate and select the correct folder and CTL file for the database being validated.

5. When the system reopens the Schema Validation dialog box:

   - Locate the Validation Log Files section.

   - Click Browse to open the Selection Path for Creating Log Files dialog box.

6. When the system opens the Selection Path for creating Log files dialog box:

   - Choose the folder where you want the system to create the log files.

   - Click OK.

7. When the system displays the Schema Validation dialog box with the required entries:

   - Click Validate Schema.

8. The system displays the cmd.exe screen to indicate that processing is taking place.

   - Press Enter when the system prompts you to do so.

9. When the system opens the Oracle Sql*Plus window, press Enter.

10. When the system opens another Oracle Sql*Plus:

    - Note the path of the log files created during processing.

11. Exit from the Schema Creation Tool.

12. Check the files for errors.

## Enabling and Disabling DLP

This section provides information about how to enable and disable Data Lock Point (DLP).

### Creating the Tablespaces

If you wish to create tablespaces before enabling DLP, the following information shows the different tablespaces. However, this step is optional.

# | Tablespace Name
--- | ---
1 | DLP_DATA_01
2 | DLP_DATA_02
3 | DLP_DATA_03
4 | DLP_DATA_04
5 | DLP_DATA_05
6 | DLP_INDEX_01
7 | DLP_INDEX_02
8 | DLP_INDEX_03
9 | DLP_INDEX_04
10 | DLP_INDEX_05
11 | DLP_INDEX_06
12 | DLP_LOB_01

The schema creation tool creates the tablespaces if they do not exist.

### Enabling DLP

Before enabling DLP (Data Lock Point), do the following:

- Verify that the Schema Creation Tool is installed.

- Make sure an Oracle Argus database instance is available.

- Verify that either a DBA-privileged or a SYSTEM user account has been created.

- Verify that the database contains extra hard disk space to support DLP. It is advised that you should have a separate disk for DLP.

- Invoke SQL/PLUS and connect to the Argus database as a SYS user.

Use the following procedure to enable DLP:

1. Open the Schema Creation Tool.

   - Click Argus DLP.

2. When the system displays the Enable DLP screen:

   - Click Yes.

3. When the system displays the expanded Enable DLP window:

   Click New User to create the DLP Schema Owner in the New User Information dialog box.

   - Enter the required details for the new DLP Schema Owner and click OK.

   - Enter the required schema information.

   - Enter the user's password in the VPD Admin Schema Password field.

4. The system redisplays the Enable DLP window with the DLP Schema Owner.

5. Enter the required user information.

   Enter the user name in the VPD Admin Schema Owner field and click OK to proceed.

6. In the Enable DLP window:

   - Locate the Local Folder name to create DLP Process Log and DMP files [No Spaces].

   - Click Browse to select a local folder (without spaces) for the temporary path.

7. When the system updates and displays the Enable DLP window:

   - Click OK.

8. When the system opens the Enable DLP: Create Tablespace, the following screen is displayed:

   Note: The Tablespace Creation dialog box appears if the Argus Database Size was created as Small, Medium, or Large. It will not appear if the database size was created as Default.

   - Enter the tablespace information in the Complete Path and Datafile fields.

   - Click Create Tablespace.

     Note: After creating the DLP datafiles in the Argus database, if the AUTOEXTENSIBLE value is set to NO, set the AUTOEXTENSIBLE value to 'YES' on all DLP tablespaces data files.

9. When the system displays the Enable DLP window with a dialog box:

   - Click OK to close the dialog box.

   - Click Proceed to start processing.

     Before clicking Proceed, verify that the DLP tablespsaces Autoextend property is set to YES.

### Disabling DLP

Verify that no one is logged on to the Argus Safety database before beginning the Disable DLP procedure.

1. Open the Schema Creation Tool.

   - Click Argus DLP.

2. When the system displays the Disable DLP dialog box:

   - Click Yes.

3. When the system displays the expanded Disable DLP dialog box:

   - Click OK to close the dialog box.

   - Click Proceed to start processing.

4. When the system displays the following message, click OK and then click Proceed in the Disable DLP expanded dialog box.

   - Click OK to close the dialog box.

   - Click Proceed to start processing.

5. When the system displays the Disable DLP window:

   - Click OK to close the dialog box.

   - The system displays status information regarding the DLP Disable operation in the Disable DLP window.

   - Click Exit.

6. When the Disable DLP operation is complete:

   - Click Exit.

7. DLP has been disabled.

## Enabling DLP on a Specific Enterprise

This section provides the steps to:

a) either enable DLP on a specific enterprise merged from a non-DLP system to a DLP enabled multi-tenant Safety system

OR

b) to enable DLP on delta cases merged into an existing enterprise of a DLP enabled multi-tenant or single-tenant Safety system.

This implementation requires the following pre-requisites:

- This script shall be used after Enabling DLP from Schema Creation Tool using the standard Argus DLP option in Schema Creation Tool which setups the initial DLP infrastructure.

- This script is supported on top of Argus DLP infrastructure which setups the initial DLP on the Argus database for all existing enterprises.

Execute the following step to extract the custom scripts:

1. Extract the custom DLP Enable Enterprise Specific from C:\Program Files\Oracle\Argus\DBInstaller\Utilities\DLP_Enable_Enterprise_Specific into a machine's local folder where Argus Safety 8.0 is installed.

Execute the following steps to set up the base database:

1. Set up an Argus Safety 8.0 multi-tenant or single-tenant database. Enable DLP on the Argus Safety 8.0 database, using standard Argus DLP option available in the Schema Creation Tool.

2. Validate the schema using the Schema Validation in Argus Safety 8.0 Schema Creation Tool by selecting the compatible CTL file for Schema Validation. If any MISSING object exists in schema validation log, it needs to be fixed before proceeding to the next step.

3. Populate new Argus cases into existing enterprise of a DLP enabled multi-tenant or single-tenant Safety system from non-DLP system or create new enterprise in a DLP enabled multi-tenant Safety system using data migration or merge to multitenant utility.

Execute the following steps to enable DLP on a Specific Enterprise or Delta Cases in a Specific Enterprise:

1. Double-click DLP_Enable_Enterprise.bat from C:\Program Files\Oracle\Argus\DBInstaller\Utilities\DLP_Enable_Enterprise_Specific\Argus\DLP\. This batch file execution handles the following scenarios to populate DLP data on newly created Argus cases:

   - Process all cases merged in a Safety system due to creation of new enterprise by merge process

   - Process of delta cases merged in an enterprise due to any migration activity

2. Provide the name and location for the log file.

3. Follow the prompt messages on the screens and proceed by entering the required parameters and continue with the Enable DLP Enterprise Specific process.

4. A message is prompted to display whether the database is single-tenant or multi-tenant.

5. The above screen shows the details entered for the Enable DLP Enterprise Specific process. The process shall only be continued further if the details displayed in this screen are correct.

In case of any error during the Enable DLP process, the execution gets paused.

The process should be continued once the error is corrected and executed from another sql window.

While executing the above, make sure that you use the correct login credentials and set up the appropriate enterprise context.

Once the process is completed the log files shall be verified for any errors.

For any missing cases between Argus and DLP, the log file DLP_ENABLE_Missing_Cases_in_DLP_log.log shall be verified in \DLP_Enable_Enterprise_Specific\Argus\DLP\ folder.

After applying the Enable DLP Enterprise Specific to Argus Safety 8.0, the DLP Enabled system performs the Schema Validation, as listed below:

1. Double-click on ArgusDBInstall.exe file that exists in C:\Program Files\Oracle\Argus\DBInstaller.

2. Click Schema Validation and continue the Schema Validation on Argus Safety 8.0 database.

Extra objects related to table DLP_ENABLE_CASE_HISTORY shall be ignored in schema validation log file.

The following table and related objects shall be ignored in Schema Validation at to Argus Safety 8.0 DLP Enabled system with DLP_Enable_Enterprise_Specific scripts applied on top of it:

Owner | Type | Name | Reason for extra object
--- | --- | --- | ---
DLP | TABLE | DLP_ENABLE_CASE_HISTORY | Objects are part of Enable DLP Enterprise Specific implementation.
DLP | INDEX | PK_DLP_ENABLE_CASE_HISTORY | Objects are part of Enable DLP Enterprise Specific implementation.

## Upgrading the Argus Safety Database

The space requirements for the upgrade are determined by the upgrade script. This requirement is mostly for new objects created during the upgrade. It is a fair estimate of space requirements.

### Prerequisites for Database Upgrade

Before upgrading the schema, connect to ARGUS Safety database as a SYS user.

Note: If another DBA user is used instead of SYSTEM, then change SYSTEM to the name of DBA user. The following grants need not be provided if the DBA user has been created through the Argus 8.0 Create DBA User script: Define user_dba=SYSTEM GRANT EXECUTE on SYS.DBMS_CRYPTO TO &user_dba. WITH GRANT OPTION;

Before starting the upgrade procedure:

- Verify that the Oracle TNSNAMES have been configured.

- To avoid errors during upgrade, do either of the following:

  a) KEEP DATA FILES AUTOEXTEND ON, or

  b) Monitor free space and add more space, if required.

- Ensure you have a sort area of approximately 100 MB to avoid disk sort

- Create one large rollback segment or size 20 GB for LARGE size model.

  Keep all other, except SYSTEM, rollback segments offline.

  Note: If the source Argus Safety Database is not AL32UTF8 character set database, then it must be converted to AL32UTF8 character set before performing the database upgrade to version 8.0.

Note: The Argus Safety Database requires the Database semantics to be CHAR and not BYTE. Execute the following steps: Log in to the Database as the SYSTEM user Execute: ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR SCOPE=BOTH; Shutdown and Startup the database after applying the above statement.

Note: Customers can use the Argus Safety (AS) 8.0 software to upgrade the Argus Safety database from AS 7.0.2 or AS 7.0.3, using the Database zip file. AS 7.0.2 to AS 7.0.3, using the database zip file in the AS 8.0 software: Extract 'Database_released_code_703.zip' from the 'Previous Database Upgrades' folder of AS 8.0 software into the 'Database_released_code_703_from_702' folder code. This will upgrade the database from AS 7.0.2 to AS 7.0.3. For further details, refer to the Oracle Argus Safety 7.0.3 Installation Guide. AS 7.0.3 to AS 7.0.3.1, using the database zip file in the AS 8.0 software: Extract 'Database_released_code_7031.zip' from the 'Previous Database Upgrades' folder of AS 8.0 software into the 'Database_released_code_7031_from_703' folder code. This will upgrade the database from AS 7.0.3 to AS 7.0.3.1. For further details, refer to the Oracle Argus Safety 7.0.3.1 Release Notes (Database Upgrade section).

### Clean-up Scripts to be Run Before Upgrading the Database

Note: There may be scenarios where DDL/DML scripts can differ due to single-tenancy and multi-tenancy. Execute the single-tenant script in a single-tenant database and the multi-tenant script in a multi-tenant database. Before executing the SQLs given below, make sure that you remove the empty lines within the SQL statements listed throughout this section.

#### Data Clean-up on Duplicate Event Details Information on a Single-tenant Database

Execute the following query to verify duplicate records:

`select case_id, prod_seq_num, event_seq_num, count(*)`

`from case_event_detail`

`group by case_id, prod_seq_num, event_seq_num`

`having count(1) > 1;`

If the above-listed SQLs result in 0 rows (that is, no duplicate data is found), then we do not need to execute the cleanup scripts.

If duplicate records are found, execute sub-section scripts to clean-up duplicate records from Argus Safety and Argus DLP-related tables.

##### Argus Safety Data Clean-up on Duplicate Event Details

Execute the following steps sequentially on the Argus Safety database as an Argus Safety Schema Owner (such as ARGUS_APP), to remove duplicate rows from Event Detail (CASE_EVENT_DETAIL table):

1. Drop the Foreign key constraint between CASE_EVENT_CONSEQUENCE and CASE_EVENT_DETAIL by entering the following SQL:

   `alter table case_event_consequence drop constraint fk_case_event_detail_seq_num;`

2. Create Foreign key constraint between CASE_EVENT_CONSEQUENCE and CASE_EVENT_DETAIL using ON DELETE CASCADE by entering the following SQL:

   `alter table case_event_consequence add (`

   `constraint fk_case_event_detail_seq_num`

   `foreign key (case_id, ed_seq_num)`

   `references case_event_detail (case_id,seq_num) on delete cascade);`

3. Delete event detail rows where it was already marked for logical deletion and is duplicated by entering the following SQL:

   `delete from case_event_detail`

   `where deleted is not null`

   `and exists (select 'x' from case_event_detail a`

   `where a.case_id = case_event_detail.case_id`

   `and a.prod_seq_num = case_event_detail.prod_seq_num`

   `and a.event_seq_num = case_event_detail.event_seq_num`

   `and a.deleted is null);`

   `commit;`

4. Delete event detail rows where they are duplicated, by entering the following SQL:

   `delete from case_event_detail a`

   `where a.seq_num <`

   `any (select b.seq_num`

   `from case_event_detail b`

   `where a.case_id = b.case_id`

   `and a.prod_seq_num = b.prod_seq_num`

   `and a.event_seq_num = b.event_seq_num);`

   `commit;`

5. Verify that no duplicate rows exist in CASE_EVENT_DETAIL, by entering the following SQL:

   `select case_id, prod_seq_num, event_seq_num, count(*)`

   `from case_event_detail`

   `group by case_id, prod_seq_num, event_seq_num`

   `having count(1) > 1;`

6. Drop the Foreign key constraint between CASE_EVENT_CONSEQUENCE and CASE_EVENT_DETAIL by entering the following SQL:

   `alter table case_event_consequence drop constraint fk_case_event_detail_seq_num;`

7. Create a foreign key constraint between CASE_EVENT_CONSEQUENCE and CASE_EVENT_DETAIL without ON DELETE CASCADE, by entering the following SQLs:

   `alter table case_event_consequence add (`

   `constraint fk_case_event_detail_seq_num`

   `foreign key (case_id, ed_seq_num)`

   `references case_event_detail (case_id,seq_num));`

##### DLP Data Clean-up on Duplicate Event Details

If DLP is enabled on the Argus Safety database, you need to execute the following steps (in addition to the steps listed above), sequentially:

Note: Execute DLP Data Clean-up ONLY AFTER executing the Argus Safety clean-up scripts.

1. Log on to DLP database as DLP schema owner (such as DLP_OWNER) to remove duplicate rows from Event Detail (DLP_CASE_EVENT_DETAIL and DLP_CASE_EVENT_CONSEQUENCE tables).

2. Delete invalid records from the DLP_CASE_EVENT_DETAIL table, by entering the following SQL:

   `delete from dlp_case_event_detail a`

   `where (case_id, seq_num) in`

   `(`

   `select case_id, seq_num from dlp_case_event_detail`

   `minus`

   `select case_id, seq_num from &user..case_event_detail`

   `);`

   `Commit;`

   Note: Replace "&user." with the name of the Argus Safety Schema Owner.

3. Delete invalid records from the DLP_CASE_EVENT_CONSEQUENCE table, by entering the following SQL:

   `delete from dlp_case_event_consequence a`

   `where (case_id, seq_num) in`

   `(`

   `select case_id, seq_num from dlp_case_event_consequence`

   `minus`

   `select case_id, seq_num from &user..case_event_consequence`

   `);`

   `Commit;`

   Note: Replace "&user." with the name of the Argus Safety Schema Owner.

#### Data Clean-up on Duplicate Event Details Information on a Multi-tenant Database

This section lists the SQL to veri fy if any duplicate record exists in the Argus Safety database.

Execute the statement given below to set the context:

`BEGIN`

`pkg_rls.set_context ('system', 0, 'ARGUS_SAFETY');`

`END;`

`/`

Execute the following query to verify duplicate records:

`select enterprise_id, case_id, prod_seq_num, event_seq_num, count(*)`

`from case_event_detail`

`group by enterprise_id, case_id, prod_seq_num, event_seq_num`

`having count(1) > 1;`

If the above-listed SQLs result in 0 rows (that is, no duplicate data is found), then we do not need to execute the clean-up scripts.

If duplicate records are found, execute sub-section scripts to clean-up duplicate records from Argus Safety and Argus DLP-related tables.

##### Argus Safety Data Clean-up on Duplicate Event Details

Execute the following steps sequentially on the Argus Safety database as an Argus Safety Schema Owner (such as ARGUS_APP), to remove duplicate rows from Event Detail (CASE_EVENT_DETAIL table):

1. Drop the Foreign key constraint between CASE_EVENT_CONSEQUENCE and CASE_EVENT_DETAIL by entering the following SQL:

   `alter table case_event_consequence drop constraint fk_case_event_detail_seq_num;`

2. Create Foreign key constraint between CASE_EVENT_CONSEQUENCE and CASE_EVENT_DETAIL using ON DELETE CASCADE by entering the following SQL:

   `alter table case_event_consequence add (`

   `constraint fk_case_event_detail_seq_num`

   `foreign key (enterprise_id, case_id, ed_seq_num)`

   `references case_event_detail (enterprise_id, case_id,seq_num) on delete cascade);`

3. Delete event detail rows where it was already marked for logical deletion and is duplicated by entering the following SQL:

   `delete from case_event_detail`

   `where deleted is not null`

   `and exists (select 'x' from case_event_detail a`

   `where a.enterprise_id = case_event_detail.enterprise_id`

   `and a.case_id = case_event_detail.case_id`

   `and a.prod_seq_num = case_event_detail.prod_seq_num`

   `and a.event_seq_num = case_event_detail.event_seq_num`

   `and a.deleted is null);`

   `commit;`

4. Delete event detail rows where they are duplicated, by entering the following SQL:

   `delete from case_event_detail a`

   `where a.seq_num <`

   `any (select b.seq_num`

   `from case_event_detail b`

   `where a.enterprise_id = b. enterprise_id`

   `and a.case_id = b.case_id`

   `and a.prod_seq_num = b.prod_seq_num`

   `and a.event_seq_num = b.event_seq_num);`

   `commit;`

5. Verify that no duplicate rows exist in CASE_EVENT_DETAIL, by entering the following SQL:

   `select enterprise_id, case_id, prod_seq_num, event_seq_num, count(*)`

   `from case_event_detail`

   `group by enterprise_id, case_id, prod_seq_num, event_seq_num`

   `having count(1) > 1;`

6. Drop the Foreign key constraint between CASE_EVENT_CONSEQUENCE and CASE_EVENT_DETAIL by entering the following SQL:

   `alter table case_event_consequence drop constraint fk_case_event_detail_seq_num;`

7. Create a foreign key constraint between CASE_EVENT_CONSEQUENCE and CASE_EVENT_DETAIL without ON DELETE CASCADE, by entering the following SQL:

   `alter table case_event_consequence add (`

   `constraint fk_case_event_detail_seq_num`

   `foreign key (enterprise_id, case_id, ed_seq_num)`

   `references case_event_detail (enterprise_id, case_id,seq_num));`

##### DLP Data Clean-up on Duplicate Event Details

If DLP is enabled on the Argus Safety database, you need to execute the following steps (in addition to the steps listed above) sequentially:

Note: Execute DLP Data Clean-up ONLY AFTER executing the Argus Safety clean-up scripts.

1. Log on to DLP database as DLP schema owner (such as DLP_OWNER) to remove duplicate rows from Event Detail (DLP_CASE_EVENT_DETAIL and DLP_CASE_EVENT_CONSEQUENCE tables)

   Execute the statement given below to set the context:

   `BEGIN`

   `pkg_rls.set_context ('system', 0, 'ARGUS_SAFETY');`

   `END;`

   `/`

2. Delete invalid records from the DLP_CASE_EVENT_DETAIL table, by entering the following SQL:

   `delete from dlp_case_event_detail a`

   `where (enterprise_id, case_id, seq_num) in`

   `(`

   `select enterprise_id, case_id, seq_num from dlp_case_event_detail`

   `minus`

   `select enterprise_id, case_id, seq_num from &user..case_event_detail`

   `);`

   `Commit;`

   Note: Replace "&user." with the name of the Argus Safety Schema Owner.

3. Delete invalid records from the DLP_CASE_EVENT_CONSEQUENCE table, by entering the following SQL:

   `delete from dlp_case_event_consequence a`

   `where (enterprise_id, case_id, seq_num) in`

   `(`

   `select enterprise_id, case_id, seq_num from dlp_case_event_consequence`

   `minus`

   `select enterprise_id, case_id, seq_num from &user..case_event_consequence`

   `);`

   `Commit;`

   Note: Replace "&user." with the name of the Argus Safety Schema Owner.

### Database Upgrade Procedure (with or without DLP) from AS 7.0.3.1 to AS 8.0

Note: The Oracle Database Server version should be upgraded to 11.2.0.4 or 12c (12.1.0.1.0, 12.1.0.2.0) prior to upgrading the database from AS 7.0.3.1 to AS 8.0.

Note: During an upgrade, a key will need to be generated prior to upgrading or an existing key from the existing setup can be used to perform the database upgrade. You must also make sure that the password information specified in the database is consistent with the information provided in the ArgusSecureKey.ini file.

Use the following procedure to upgrade the database.

- You may be prompted to press Enter at screens that are not included in the procedure. This does not hinder the upgrade procedure. Where applicable, press Enter to continue with the upgrade process.

1. Select Start > Programs > Oracle > Schema Creation Tool.

2. When the system opens the Schema Creation Tool:

   - Click DB Upgrade.

3. When the system opens the Connect to Database dialog box:

   - Enter the DBA username.

   - Enter the password.

   - Enter the Database name.

   - Click OK.

4. Select the version-specific upgrade folder and click OK.

   Note: During the upgrade this validation may appear. This is due to a few tables where the column size has been adjusted and the table currently has more data than the new column size.

5. When the system opens the Upgrade Parameters screen loaded with default values:

   In the Upgrade Parameters screen, enter information in the following fields:

   - Credentials for VPD Admin User - This includes the VPD Admin Schema Owner, Password, and Verify Password.

   - Application Type - This includes the following two options:

     - Single Tenant – Select this option if you are upgrading this database and leaving it as a single tenant model.

     - Multi Tenant – Select this option if you are upgrading this database and changing to a multi-tenant model.

   - Default Enterprise Details - This includes the Enterprise Name and Enterprise Short Name.

   Note: The three optional functions SF_CASE_SERIOUSNESS, SF_CASE_LISTEDNESS, and SF_CASE_CAUSALITY have had their signatures changed. The original functions will be spooled to the log file at .\DBInstaller\Upgrades\UPGRADE_TO_80\UPG_SF_FUNCTIONS_SOURCE.LOG. If custom functions are in use, it will be necessary to re-apply them after the upgrade is complete to conform to the new signatures.

   - Enter the Argus Safety Schema Owner password.

   - Enter the ESM Schema Owner password.

   - Click New User to create a new BIP Schema and select it from the BIP Schema Owner drop-down list.

   - Enter the BIP Schema Owner password.

   - Click Next.

     Note: If DLP is already enabled, the check box will be checked; otherwise it will be unchecked.

6. When the system loads the Tablespace Management window for the Argus database:

   - Select the tablespace name from the drop-down list corresponding to the description.

   - Click Recalculate Free Space. Verify that the available free space is greater than the amount of required space. If you have increased the freespace, click this button to recalculate the amount of available free space.

   - Click Next.

7. If DLP is already enabled on the selected Argus DLP database, the system displays the Tablespace Management DLP window. If Argus does not have DLP, this system does not display this screen.

   - Enter the appropriate path of the tablespace and click Next.

8. When the system prompts for confirmation:

   - Click OK and then click Proceed on the main screen.

9. When the upgrade is complete:

   - Click OK.

10. When the system opens the Database Upgrade Execution window:

    - Click the log icon to verify any upgrade errors.

    - Click Exit.

11. Upgrade is finished.

12. Run the Schema Validation tool to validate the schema.

    Note: Make sure that you disable the dashboard triggers after completing the database upgrade, as listed in the Note at the end of the Loading Factory Data section.

## Post-Upgrade Steps

This section provides the following post-upgrade scripts to be executed on the Argus Safety database:

<C>:\Program Files\Oracle\Argus\DBInstaller\utilities\Post_Upgrade_Scripts (Optional)

Run the Post_Upgrade_Scripts.bat batch file present in the folder mentioned above and follow the ReadMe.txt for details to initiate the Post-Upgrade and execute the following steps:

1. Double-click the post_upgrade_script.bat file.

2. Enter the log file name. This creates a log file in the current working directory.

3. Enter the database TNS details, and log in with the Argus user credentials.

4. Press Enter to Continue, if the provided details are correct.

5. Press Enter again, if the user could connect successfully to the database.

6. Press Enter to initiate the migration script.

7. Review the log file for any errors.

## Enabling Local Locking in Argus Safety

Pre-requisite:

Before enabling Local Locking in Argus Safety, you must make sure that you have upgraded your database to Argus Safety 8.0 successfully.

Execute the following steps to enable local locking:

1. Execute the batch file Enable_local_lock.bat under the <C>:\Program Files\Oracle\Argus\DBInstaller\utilities\Enable_local_lock directory.

2. Enter the response for Do you wish to turn on the Local Locking feature for one or more enterprises (Yes/No)?, enter Yes to continue.

3. Enter the log file name to record the results. This is the execution log that is created on the client workstation under the Enable_local_lock directory mentioned above.

4. Enter TNSNAMES Entry to Connect to the source SAFETY Database.

5. Enter SAFETY schema owner name in source Database.

6. Enter the password for safety schema name in source Database.

7. Enter comma separated list of enterprise where local locking feature is to be enabled or enter ALL for all enterprises in Source safety Database. If no value is entered script will run for enterprise 1 by default.

8. Enter the Agency name for PMDA reporting destination as configured in Reporting Destination codelist.

9. Enter Yes or No in case you wish to enable the local locking privileges for Argus J users. Follow the prompts for confirmation.

   Note: If the agency entered is invalid for any of the enterprises, the utility will abort and no changes will be committed. In case of a nulti-tenant environment, if this utility is re-run for any of the enterprises, it will display a list of the enterprises for which it has already executed and will continue to process rest of the enterprises.

Making cases appear in PSUR regardless of past submission:

Pre-requisite:

Before making cases appear in PSUR regardless of past submission, you must make sure that you have upgraded your database to Argus Safety 8.0 successfully.

Execute the following process to make cases appear in PSUR:

1. Delete the data from the cmn_per_sub_child table.

2. Execute the following query to restore the data to factory settings as per upgrade:

   INSERT INTO CMN_PER_SUB_CHILD (id,reg_report_id,rec_type,field,enterprise_id)

   SELECT S_CMN_PER_SUB_CHILD.NEXTVAL,reg_report_id,rec_type,field,enterprise_id FROM (

   WITH report_ids AS (

   SELECT crr.reg_report_id, crr.report_form_id FROM

   v$cmn_reg_reports crr,

   v$lm_report_forms lrf

   WHERE crr.report_form_id=lrf.report_form_id and crr.enterprise_id=lrf.enterprise_id

   AND crr.state_id=6 AND crr.report_form_id>100

   AND lrf.rpt_type in (2,12)

   )

   , dataview as (

   select distinct ri.reg_report_id,cprc.report_form_id,cprc.rec_type,cprc.field, max(cprc.rec_type) over (partition by cprc.report_form_id) max_rec_type ,cprc.enterprise_id

   from v$cfg_per_rpt_child cprc, report_ids ri

   where ri.report_form_id=cprc.report_form_id

   and cprc.rec_type in (1,8)

   )

   select reg_report_id,rec_type,field,enterprise_id

   from dataview

   where rec_type=max_rec_type

   );

3. The above query can be used as a base for any custom changes that may be required.

   Note: The above steps have to be executed after setting the enterprise context using PKG_RLS.SET_CONTEXT procedure.

## Merging a Single Enterprise Safety Database into a Multi-tenant Database

### Prerequisites to Running the Merge Export Step

- The end user should not use the Source database during export process.

- Install Argus Safety 8.0 on a computer where Oracle 11.2.0.4 or 12c (12.1.0.1.0, 12.1.0.2.0) is installed.

- The source databases should be schema validated at Argus Safety 8.0.

- The source database should only be a single-tenant database.

- The source database data must contain only one ENTERPRISE.

### Prerequisites to Running the Merge Import Step

- Create a cold backup of the target database before starting the MERGE IMPORT step.

- The end user should not use the target database during the import process

- There is only one at the time MERGE Import process allowed to run on the Target database.

- Auto extend should be set on for all Database files in the target database

- Sufficient space should be available on the target database server to import the new Enterprise Data. The amount of space depends on the number of cases in source Safety database.

- Install the Argus 8.0 application. Make sure that Oracle Client version is 11.2.0.4 or 12c (12.1.0.1.0, 12.1.0.2.0).

- The Target databases should be Schema Validated at Argus 8.0.

- The target database must be a Multi-tenant database

- All source database dictionaries should be available in target Database. If the dictionary doesn't exist then install missing dictionaries on Target database.

- All existing AG service users on the Source Database must exist on the target Database

- All source database LDAP configured Server name should be available in target database.

### Completing the Merge Process

Use the following sections to complete the merge process.

#### Merge Export

1. Navigate to the following Path from Start Menu:

   All Programs > Oracle > Merge to Multi-tenant

2. Click on Export and follow the instructions on the sqlplus screen.

   1. Enter Log File Name to record results.

      This is the execution log that is created on the client workstation:

      Log file path: <C>:\Program Files\Oracle\Argus\DBInstaller\Merge_to_Multitenant

   2. Enter TNSNAMES Entry to Connect to the Source SAFETY Database.

   3. Enter SYSTEM or DBA user name in source Database.

   4. Enter password for DBA user in source Database.

   5. Enter SAFETY schema owner name in source Database.

   6. Enter password for Safety schema owner in source Database

   7. Enter Interchange schema owner name in Safety Database

   8. Enter password for Interchange schema owner in source Database.

   9. Enter the full directory Path to create the Source Safety database export dump file: This is the Path on the Source Database Server where the Argus Safety Database resides. The Batch file will create an export dump file (SAFETY.DMP) and an export log file (SAFETY_EXPORT.LOG) in the Directory. Make sure that SAFETY.DMP file does not exist prior to the export.

3. Check the database export process log and export step log file for any errors. This is critical step to make sure no errors during export step. Check following log files:

   - Log file name entered as parameter 1 during export step execution.

   - Following Oracle Import log files are created on database server. The path is the value entered on ”Enter Directory including full Path to create Source safety database export dump file” during export step:

     SAFETY_EXPORT.log

#### Exporting the dmp File Copy to the Target Database Server

Move the export Dmp file created in Merge Export from the source database server to the target database server.

#### Merge Import

1. Navigate to the following path from Start Menu:

   All Programs > Oracle > Merge to Multi-tenant

2. Click on Import and follow the instructions on the sqlplus screen.

   1. Enter Log File Name to record results. This is the execution log will be created on the client workstation.

      Log file path: <C>:\Program Files\Oracle\Argus\DBInstaller\Merge_to_Multitenant

   2. Enter TNSNAMES Entry to Connect to the Target SAFETY Database.

   3. Enter SYSTEM or DBA user name in target Database.

   4. Enter password for DBA user in target Database.

   5. Enter VPD schema owner name in target Database.

   6. Enter VPD schema owner password in target Database.

   7. Enter SAFETY schema owner name in target Database.

   8. Enter password for Safety schema owner in target Database

   9. Enter Interchange schema owner name in target Database

   10. Enter password for Interchange schema owner in target Database.

   11. Enter Directory including full Path on target database server where export dmp file copied for import process. This is the Path on the "Target Database Server" where the Argus Safety Database resides. The Batch file creates an import log files file in the directory mentioned.

   12. Enter the name of new ENTERPRISE.

   13. Enter the abbreviation of new ENTERPRISE.

   14. Enter SAFETY schema owner name in source Database.

   15. Enter Interchange schema owner name in source Database.

3. This Batch files imports the data from the dump file into the target database.

4. Check the database import process log and import step log file for any errors. This is critical step to make sure no errors during import step. Check following log files:

   - Log file name entered as parameter 1 during Import step execution.

   - The following Oracle Import log files are created on database server. The path is the value entered on ”Enter Directory including full Path on target database server where export dmp file copied for import process” during import step.

     - SAFETY_IMPORT_safety.log

     - SAFETY_IMPORT_interchange.log

     - SAFETY_IMPORT_SAFETY_DUP_SEARCH_DATA.log

     - SAFETY_IMPORT_SAFETY_DUP_LAM_SEARCH_DATA.log

5. Validate the Schema of the Ttget database using Safety Schema Validation tool.

#### Manual Dictionary Synchronization

The MERGE process synchronizes the dictionary information based on the dictionary name in the source and target database. If the source Dictionary name is not available in Target Database then manual synchronization is required.

Use the following steps to synchronize the dictionary data manually on the target database:

1. Log in as Safety schema owner using sqlplus on Target Safety Database.

2. Locate the new ENTERPRISE_ID value created from import process using the following sql:

   `SELECT VALUE`

   `FROM cmn_profile_global`

   `WHERE section = 'DATABASE' AND KEY = 'MERGING_TO_MULTITENANT';`

3. Set the context value to new Enterprise_id

   Exec pkg_rls.set_context('admin',< Value of New Enterprise ID>,'ARGUS_SAFETY');

4. Locate the list of Dictionaries ID's where Dictionary synchronization pending due to missing Dictionaries on Target database. If the following sql results in NO ROWS, then no further action required.

   `Select dict_id`

   `From cfg_dictionaries_enterprise`

   `Where enterprise_id = <Value of New Enterprise ID>`

   `And global_dict_id = -1;`

5. Log in as the Safety schema owner using sqlplus on the source safety database.

6. Locate the dictionary name of each Dictionary ID where the Dictionary does not exist on the target database using the following sql:

   `Select name from cfg_dictionaries_global`

   `where dict_id in (<List of Dict ID values (comma separated) from Step 4);`

7. Load the missing dictionaries on the target database.

8. Set the context to new enterprise_id using following sql on target database.

   `Exec pkg_rls.set_context('admin',<Value of new ENTERPRISE_ID> ,'ARGUS_SAFETY');`

9. Update GLOBAL_DICT_ID data in the target database using the following SQL:

   `UPDATE CFG_DICTIONARIES_ENTERPRISE`

   `SET GLOBAL_DICT_ID = <Dictionary Global Dict ID value from target database>`

   `WHERE ENTERPRISE_ID = <New ENTERPRISE_ID created in Target Database>`

   `AND DICT_ID = <Value of Dict ID in New ENTERPRISE with Dictionary name>`

   `AND GLOBAL_DICT_ID =-1;`

## Copy Configuration Tool

This tool is intended to provide functionality for copying configuration data from one Argus Safety database to another.

Note: If no dictionary credential is provided while exporting the source database, you must make sure that before you import, you create a dummy DICTIONARY.DMP file. To create the dummy dump file, right-click export dump files directory and click on Create a Text Document and rename it from *.txt to DICTIONARY.DMP.

The following steps are required to run the tool:

1. Validate Schema on the source database using Schema Validation Tool.

   Make sure that there are no extra or missing objects exist in Schema Validation log file. Messages for extra custom objects created should be ignored.

2. Copy the Copy Configuration Tool utility files recursively from C:\Program Files\Oracle\Argus\DBInstaller\Copy_Config to the C:\CONFIG_EXP_IMP_70 folder.

3. Export the Source database by running C:\CONFIG_EXP_IMP_70\Data_ExportConfigOnly_11g.bat and follow the prompts.

4. Copy ArgusSecureKey.ini (working with Source database) from the .\Windows folder and save it with generated source database file.

5. Create a new database using Argus Safety 8.0 Schema Creation tool.

6. Import into Target database by running C:\CONFIG_EXP_IMP_70\ Data_ImportConfigOnly_11g.bat and follow the prompts. Ignore any "ORA-28101: policy already exists" error.

7. Validate Schema on the target database using Schema Validation Tool.

8. Copy ArgusSecureKey.ini from the source database folder and paste it in the .\Windows folder of application server(s) which are intended to be used with the target database.

9. In case you do not have ArgusSecureKey.ini, follow the steps listed in the Resetting the Environment if ArgusSecureKey.ini is Lost section.