Initial Install
This section describes how to install the database components of Oracle Utilities Market Settlements Management, including:
Copying and Decompressing Install Media
To copy and decompress the Oracle Utilities Application Framework and Oracle Utilities Market Settlements Management databases:
1. Download the Oracle Utilities Application Framework V4.5.0.0.0.Multiplatform and Oracle Utilities Meter Data Management V2.5.0.0.0.Multiplatform from the Oracle Software Delivery Cloud.
2. Create a temporary directory, such as C:\OUMSM\temp or /OUMSM/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.0.0.zip and MDM_V2.5.0.0.0.zip files from the downloaded package to the <TEMPDIR>/DB directory.
4. Unzip the FW_V4.5.0.0.0.zip and MDM_V2.5.0.0.0.zip files to a temporary folder. These files contains the database components required to install the Oracle Utilities Application Framework and Oracle Utilities Market Settlements 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:
• 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 the
Oracle Database documentation for making this change in your database.
Exclude Table/Index
To exclude an index or table during the upgrade process, follow these steps:
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'
Creating the Database
Note: You must have Oracle Database Server installed on your machine in order to create the database. The database can be created using Database Configuration Assistant (DBCA).
Using DBCA
For creating 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. You must create tablespace CISTS_01 before running the script for creating the product users.
2. Run the …/DB/MDM_V2.5.0.0.0/DatabaseCreation/users.sql after logging into the database as sys user, to create the product users.
Granting Privileges to Database Roles
Make sure to provide the CREATE SYNONYM grants to 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.0.0 prior to installing Oracle Utilities Market Settlements Management V2.5.0.0.0. The files for Oracle Utilities Application Framework installation are located in the FW_V4.5.0.0.0 folder.
This section includes the following:
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 (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
• Location for jar files. (The Jar files are bundled with the database package) - CLASS_PATH
• Java Home (for 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
The following procedure lists the steps to install the schema for Oracle Utilities Application Framework V4.5.0.0.0 using OraDBI.
Run the following command with the defined parameters on the command prompt from the ../DB/FW_V4.5.0.0.0/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.0.0/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 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.0.0/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 Market Settlements Management Database Component
Note that the Oracle Utilities Meter Data Management database component is a cumulative package comprising of data used by Oracle Utilities Market Settlements Management.
To install the Oracle Utilities Market Settlements Management database component, prepare the following parameters:
• 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
• Location for jar files. (The Jar files are bundled with the database package) - CLASSPATH
• Java Home (for 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 run OraDBI.jar using either of the following methods:
Using the Interactive Mode
The following procedure lists the steps to install the schema for Oracle Utilities Market Settlements Management using OraDBI.
Run the following command with the defined parameters on the command prompt from the ../DB/MDM_V2.5.0.0.0/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.0.0/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 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/MDM_V2.5.0.0.0/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)
If you chose to continue, OraDBI first checks for the existence of each of the users specified and prompts for their password, default tablespace, and temporary tablespace.
Optional: This optional step should be executed if you have installed Oracle Utilities Meter Data Analytics 2.5.0.0.2 (2.5 Patch Set 2), or if you plan to install it in the future.
Navigate to ../DB/MDM_V2.5.0.0.0/Post-Upgrade/ folder and run Materialized_View_Creation.sql from sql prompt as follows:
a. Connect to Database Owner Schema. (for example: <CISADM>/<CISADM>@<SERVICE_NAME>)
b. Run Materialized_View_Creation.sql as @Materialized_View_Creation.sql from sql prompt.
After the required changes are complete, configure security by following the steps in the
Configuring Security section.
Configuring Security
To configure security:
1. Set PATH.
export JAVA_HOME=<Java Install location>/jdk1.8.0
export PATH=$JAVA_HOME/bin:$PATH
2. Set CLASSPATH.
export CLASSPATH=<TEMPDIR>/DB/FW_V4.5.0.0.0/jarfiles/*
3. Run the following command with the defined parameters on the command prompt.
java com.oracle.ouaf.oem.install.OraGenSec -d <DBUSER>,<DBPASS>,jdbc:oracle:thin:@<DB_Server>:1521/ <SERVICE_NAME> -a A -r <RW_USER_ROLE>,<R_USER_ROLE> -u <RWUSER>,<RUSER> -p <RWUSER_PASS>,<RUSER_PASS>
OraDBI Performs the Following Tasks
• Interacts with the user to collect information about the name of Oracle account that will own the application schema (for example: CISADM), password of this account, and the name of the Oracle account that the application user will use (for example: CISUSER), and the name of the Oracle account that will be assigned read-only privileges to the application schema (for example: CISREAD).
• Connects to the database as CISADM account, checks whether the user already has the application schema installed to verify whether this is an initial installation.
• Verifies whether tablespace names already exist in the Storage.xml file (if not, the process will abort).
• Installs the schema, installs the system data, and configures security.
• Maintains upgrade log tables in the database.
• Updates release ID when the upgrade is completed successfully.
• If an error occurs while executing a SQL script or another utility, it logs and displays the error message and allows you to re-execute the current step. Log files OraDBI###.log are created in the same folder as OraDBI and contains all the SQL commands executed against the database along with the results. The log files are incremental so that the results are never overwritten. If warning messages are generated during the upgrade, OraDBI prompts the user at the end of the process. Users should check the log files to verify the warning messages.
• Warning messages are only alerts and do not necessarily mean a problem exists.
• Stores the Schema owner and password in the feature configuration table. The password is stored in encrypted format.
Post-installation Tasks
The post-installation tasks include:
Populating Language Data
Please note that this database contains data in the ENGLISH language only. If you use any other supported language, you can run the F1-LANG batch program to duplicate the entries for new language records.
For more information on running this batch program, refer to the Background Processes section in the Oracle Utilities Meter Solution Administrative User Guide included in this release.
Generating Database Statistics
During an install process, new database objects may be added to the target database. Before starting to use the database, generate the complete statistics for these new objects using the DBMS_STATS package.
Enabling USER_LOCK Package
For in-bound web services to work the USER_LOCK must be enabled at the database level. This is a one-time step. If this is not already enabled, do so as follows:
1. Login as SYS user.
2. On SQL prompt run:
@?/rdbms/admin/userlock.sql
3. Grant permission by running following SQL:
grant execute on USER_LOCK to public;
Note that grant can also be made to the database user which the Application connects to only instead of to public. For example: cisuser
Creating Activity Statistics Materialized View
To improve the performance of drill down queries, create the materialized view, and then refresh it.
Navigate to ../DB/MDM_V2.5.0.0.0/Post-Upgrade/ and run the scripts below.
1. Login as CISADM user.
2. On SQL prompt run the following:
@Materialized_View_Creation.sql
Configuring Security
To configure security:
1. Set PATH.
export JAVA_HOME=<Java Install location>/jdk1.8.0
export PATH=$JAVA_HOME/bin:$PATH
2. Set CLASSPATH.
export CLASSPATH=<TEMPDIR>/DB/FW_V4.5.0.0.0/jarfiles/*
3. Run the following command with the defined parameters on the command prompt.
java com.oracle.ouaf.oem.install.OraGenSec -d <DBUSER>,<DBPASS>,jdbc:oracle:thin:@<DB_Server>:1521/ <SERVICE_NAME> -a A -r <RW_USER_ROLE>,<R_USER_ROLE> -u <RWUSER>,<RUSER> -p <RWUSER_PASS>,<RUSER_PASS>
Creating Index D1T304S3 for Payload Statistic Functionality (Optional)
For an initial installation, this index does not exist. If you are using the payload statistic functionality, create the index.
Connect to CISADM schema and execute the following:
CREATE UNIQUE INDEX D1T304S3 ON D1_INIT_MSRMT_DATA (IMD_EXT_ID,INIT_MSRMT_DATA_ID);
Populating the CI_INSTALL_PROD Table
After completing the Oracle Utilities Market Settlements Management database installation, navigate to ..\MDM.V2.5.0.0.0\Post-Upgrade, connect to the CISADM schema and run the following script:
@MSM_D1_Update.sql
Applying Patch 29049452
After completing the Oracle Utilities Market Settlements Management database installation, download the Patch 29049452 from
My Oracle Support and apply it to the application. For more information, refer to the ReadMe included the patch installation package.