Initial Install

This section describes how to install the database components of Oracle Utilities Meter Data Management, including:

• Copying and Decompressing Install Media

• Database Globalization Support Consideration

• Creating the Database

• Installing the CISADM Schema

• Post-installation Tasks

Copying and Decompressing Install Media

To copy and decompress the Oracle Utilities Application Framework and Oracle Utilities Meter Data Management databases:

1. Download the Oracle Utilities Application Framework V4.5.0.1.1 Multiplatform and Oracle Utilities Meter Data Management V2.5.0.1.1 Multiplatform from the Oracle Software Delivery Cloud.

2. Create a temporary directory, such as C:\OUMDM\temp or /OUMDM/temp (referred to below as <TEMPDIR>). This directory must be located outside any current working Oracle Utilities application environment. All the files that are placed in this directory as a part of the installation can be deleted after a successful installation.

3. Copy the FW_V4.5.0.1.1.zip and MDM_V2.5.0.1.1.zip files from the downloaded package to the <TEMPDIR>/DB directory.

4. Unzip the FW_V4.5.0.1.1.zip and MDM_V2.5.0.1.1.zip files to a temporary folder. These files contain the database components required to install the Oracle Utilities Application Framework and Oracle Utilities Meter Data Management databases.

Database Globalization Support Consideration

The Oracle Utilities Application Framework is a multilingual capable application that supports the storage, processing and retrieval of data in multiple languages by leveraging the Oracle Database globalization support architecture. Use of the AL32UTF8 Unicode character encoding system allows the database to support multiple languages.

By default the database is created with BYTE length semantics. If the data is being stored using CHARACTER length semantics, set NLS_LENGTH_SEMANTICS to CHAR at session level via a login trigger during installation.

Example:

CREATE OR REPLACE TRIGGER RCU_INSTALL_TRIGGER after logon on database

declare

user_name varchar2(100);

begin

select user into user_name from dual;

if ( user_name LIKE 'CISADM' or user_name LIKE 'STG%' ) THEN

execute immediate 'alter session set nls_length_semantics=CHAR';

END IF;

END;

There are multiple ways to migrate a database from BYTE to CHAR length sematnics:

• By Script: Refer to Doc ID 313175.1 on My Oracle Support.

• Alternative procedure: The following is an alternate way to create a schema with character-length semantics and to import the date from a byte-based export.

Migrating from BYTE based storage to CHARACTER based storage

1. Create database using DBCA.

2. Run following statement to set nls_length_semantics=CHAR.

SQL> ALTER SYSTEM SET nls_length_semantics=CHAR SCOPE=BOTH;

3. Restart the database.

4. Make sure nls_length_semantics is CHAR.

SQL> SHOW PARAMETER nls_length_semantics

Note: Make sure to set nls_length_semantics=CHAR for pluggable database.

5. Export schema from database which has nls_semantics_legth=BYTE.

expdp userid=system/<code>@<SID> directory=<DIR_NAME> schemas=<schema_name> dumpfile=<schema_name>.dmp logfile=<schema_name>.log

6. Generate DDL from dump file using Oracle impdp utility.

impdp userid=system/<code>@<SID> directory=<DIR_NAME> DUMPFILE=<schema_name>.dmp SCHEMAS=<schema_name> SQLFILE=<schema_name>_DDL.sql

7. Replace the word ‘Byte’ with ‘Char’ in <schema_name>DDL.sql.

For vi editor in Linux environment use the following command to replace Byte with Char.

:%s/BYTE/CHAR/g

8. Replace the schema name also if it is required for the environment.

9. Run <schema_name>DDL.sql (generated in step 6) to create objects in the schema.

Run the following command to make sure the number of objects at source and target are equal.

SQL>select OWNER || ' ' || OBJECT_TYPE || ' ' || COUNT(*) || ' ' || STATUS FROM DBA_OBJECTS WHERE OWNER in ('<SCHEMA_NAMe>') GROUP BY OWNER, OBJECT_TYPE , STATUS ORDER BY OBJECT_TYPE;

10. If any object is missing for any reason, create it, by fixing DDL manually (DDL for each object is available in the file which was created in step 6). Then, execute DDL for the objects which are not created.

11. Generate DDL to disable triggers using following command.

SQL> SELECT 'ALTER TABLE' || ' ' ||TABLE_NAME || ' ' || 'DISABLE ALL TRIGGERS;' FROM USER_TABLES;

12. Run the script generated from step 11 to disable all triggers.

13. Import data only. Use the following command to import data only into the schema created to support CHAR based database storage.

impdp userid=system/<code>@<SID> dumpfile=<schema_name>.dmp CONTENT=DATA_ONLY SCHEMAS=<schema_name> LOGFILE=<schema_name>_import.log

14. Enable triggers.

To generate DDL for triggers:

SQL>SELECT 'ALTER TABLE' || ' ' ||TABLE_NAME || ' ' || 'ENABLE ALL TRIGGERS;' FROM USER_TABLES;

15. Run the script generated in step 14 to enable all triggers.

Extended Datatypes: Some of the Oracle Utilities Application Framework application table varchar2 fields require byte size beyond 4000 bytes to store data for new application requirements. To support this requirement, the Oracle Utilities Application Framework database needs to make use of Extended Data Types - Oracle Database 12c feature (EXTENDED - the 32767 byte limit introduced in Oracle Database 12c applies).

Enable the Extended Data Types by setting DB parameter, max_string_size = EXTENDED

Make sure to follow the instructions provided in Oracle Database documentation for making this change in your database.

Exclude Table/Index

To exclude an index or table during the upgrade process:

1. Edit the file OraSchUpg.inp in the Install-Upgrade directory.

2. Add the tables/indexes in the following format.

INDEX: 'INDEX_NAME','INDEX_NAME'

TABLE: 'TABLE1_NAME','TABLE2_NAME'

Example: To exclude the F1_WEB_SVC table:

-TABLE:'F1_WEB_SVC'

If there are multiple tables, separate them with comas.

-TABLES: 'TABLE-1','TABLE_2','TABLE_3'

For indexes:

-INDEX:' F1C064S1'

If there are multiple indexes, separate them with comas.

-INDEX:'INDEX_1',INDEX_2','INDEX_3

Creating the Database

Note: Make sure to install Oracle Database Server on your machine to create the database. The database can be created using Database Configuration Assistant (DBCA).

Using DBCA

To create an Initial Install or production database it is recommended that you use the Database Configuration Assistant (DBCA). Once the database is created the instance configuration can be done according to the environment needs and based on your production recommendations.

The script for creating the product users is located under the relevant database version subdirectory of the DatabaseCreation directory.

1. Create tablespace CISTS_01 before running the script for creating the product users.

2. Run the …/DB/MDM_V2.5.0.1.1/DatabaseCreation/users.sql after logging into the database as sys user, to create the product users.

3. Grant access on DBMS_CRYPTO to CISADM. For more information, refer to the Enabling the DBMS_CRYPTO Package section.

Grant Privileges to Database Roles

Make sure to provide the CREATE SYNONYM grants to the Database role with read-write privileges and read-only privileges.

GRANT CREATE SYNONYM TO CIS_USER;

GRANT CREATE SYNONYM TO CIS_READ;

Installing the CISADM Schema

You must install the Oracle Utilities Application Framework V4.5.0.1.1 prior to Oracle Utilities Meter Data Management V2.5.0.1.1. The files for Oracle Utilities Application Framework installation are located in the FW_V4.5.0.1.1 folder.

This section includes the following:

• Installing the Oracle Utilities Application Framework Database Component using OraDBI.jar

• Installing the Oracle Utilities Meter Data Management Database Component

• Configuring Security

Installing the Oracle Utilities Application Framework Database Component using OraDBI.jar

Prepare the following parameters before installation:

• The name of the database server in which the database is configured: DB_SERVER

• The listener port number of the database: PORT

• The target database name in which the product is to be installed: SERVICE_NAME

• A database user that will own the application schema (example: CISADM): DBUSER

• Password of the database user that will own the application schema: DBPASS

• A database user that has read-write (select/update/insert/delete) privileges to the objects in the application schema (example: CISUSER). The application will access the database as this user: RWUSER

• A database user with read-only privileges to the objects in the application schema. (example: CISREAD): RUSER

• A database role that has read-write (select/update/insert/delete) privileges to the objects in the application schema. (example: CIS_USER): RW_USER_ROLE

• A database role with read-only privileges to the objects in the application schema. (example: CIS_READ): R_USER_ROLE

• Location for jar files. (The Jar files are bundled with the database package): CLASSPATH

• Java Home (example: <Java Install location>): JAVA_HOME

• Database user password with read-write privileges: RWUSER_PASS

• Database user password with read-only privileges: RUSER_PASS

You can execute OraDBI.jar using either of the following methods:

• Using the Interactive Mode

• Using the Command Line Mode

Using the Interactive Mode

The following procedure lists the steps to install the schema for Oracle Utilities Application Framework V4.5.0.1.1 using OraDBI.

Run the following command with the defined parameters on the command prompt from the ../DB/FW_V4.5.0.1.1/Install-Upgrade directory.

1. Open a command line prompt.

2. Set Java Home.

export JAVA_HOME=<Java Install location>/jdk1.8.0

3. Set the class path.

export CLASSPATH=<TEMPDIR>/DB/FW_V4.5.0.1.1/jarfiles/*

4. Run the following command:

$JAVA_HOME/bin/java -Xmx1500M -cp $CLASSPATH

com.oracle.ouaf.oem.install.OraDBI -p <RWUSER_PASS>,<RUSER_PASS>

The utility prompts you to enter values for the following parameters as per your environment:

• Name of the database server: <DB SERVER>

• Port no: <PORT>

• Name of the target database: <SERVICE_NAME>

• Name of the owner of the database schema: <DBUSER>

• Password of the user name: <DBPASS>

• Location of Java Home: (example: <Java Install location>): <Java Home>

• Oracle user with read-write privileges to the Database Schema: <CISUSER>

• Oracle user with read-only privileges to the Database Schema: <CISREAD>

• Oracle database role with read-write privileges to the Database Schema: <CIS_USER>

• Oracle database role with read-only privileges to the Database Schema: <CIS_READ>

• Enter the name of the target schema where you want to install or upgrade: <CISADM>

• Enter the password for the target schema: <CISADM password>

This process generates log files in the directory Install-Upgrade/logs. Make sure to check the log files for any errors.

Note: For OraDBI jar, you may receive the following message in the display output or logs. These errors can be safely ignored and the process should proceed to completion.

- 2016-05-23 16:31:38,315 [main] ERROR (common.cryptography.KeyStoreWrapperFactory) The keystore file '<filename>' does not exist.…

This file is either provided by the property com.oracle.ouaf.system.keystore.file or expected to exist at the default file location null Attempting to use the legacy cryptography.

- 2016-05-23 16:31:38,566 [main] INFO (oem.install.OraDBI)

Using the Command Line Mode

Run the following command with the defined parameters on the command prompt from ../DB/FW_V4.5.0.1.1/Install-Upgrade directory.

java com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<DB_Server>:1521/

<SERVICE_NAME>,<DBUSER>,<DBPASS>,<RWUSER>,<RUSER>,<RW_USER_ROLE>,< R_USER_ROLE>,<DBUSER> -l 1,2 - q true -p <RWUSER_PASS>,<RUSER_PASS>

This process generates log files in the directory Install-Upgrade/logs. Make sure to check log files for any errors.

Note: For OraDBI jar, you may receive the following message in the display output or logs. These errors can be safely ignored and the process should proceed to completion.

- 2016-05-23 16:31:38,315 [main] ERROR (common.cryptography.KeyStoreWrapperFactory) The keystore file '<filename>' does not exist.…

This file is either provided by the property com.oracle.ouaf.system.keystore.file or expected to exist at the default file location null Attempting to use the legacy cryptography.

- 2016-05-23 16:31:38,566 [main] INFO (oem.install.OraDBI)

Installing the Oracle Utilities Meter Data Management Database Component

To install the Oracle Utilities Meter Data Management Database Component:

Prepare the following parameters before installation:

• The name of the database server in which the database is configured: DB_SERVER

• The listener port number of the database: PORT

• The target database name in which the product is to be installed: SERVICE_NAME

• A database user that will own the application schema (for example: CISADM): DBUSER

• Password of the database user that will own the application schema: DBPASS

• A database user that has read-write (select/update/insert/delete) privileges to the objects in the application schema (for example: CISUSER). The application will access the database as this user: RWUSER

• A database user with read-only privileges to the objects in the application schema. (for example: CISREAD): RUSER

• A database role that has read-write (select/update/insert/delete) privileges to the objects in the application schema. (for example: CIS_USER): RW_USER_ROLE

• A database role with read-only privileges to the objects in the application schema. (for example: CIS_READ): R_USER_ROLE