Initial Install
This section describes how to install the database components of Oracle Utilities Smart Grid Gateway, including:
Note: The Oracle Utilities Meter Data Management Database Component is a cumulative package comprising of data used by all the Oracle Utilities Smart Grid Gateway and Oracle Utilities Service Order Management adapters.
Copying and Decompressing Install Media
To copy and decompress the Oracle Utilities Application Framework and Oracle Utilities Smart Grid Gateway 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 Oracle Software Delivery Cloud to install any of the following adapters:
• Oracle Utilities Smart Grid Gateway Adapter Development Kit
• Oracle Utilities Smart Grid Gateway Adapter MV-90 Adapter for Itron
• Oracle Utilities Smart Grid Gateway Adapter for Networked Energy Services
• Oracle Utilities Smart Grid Gateway Adapter for Itron OpenWay
• Oracle Utilities Smart Grid Gateway Adapter for Sensus
• Oracle Utilities Smart Grid Gateway Adapter for Landis+Gyr
• Oracle Utilities Smart Grid Gateway Adapter for Silver Spring Networks
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.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 named DB. These files contain the database components required to install Oracle Utilities Application Framework and Oracle Utilities Meter Data Management databases.
Creating the Database
You must have Oracle Database Server installed on your machine to create the database. The database can be created using Database Configuration Assistant.
Using DBCA
To create an Initial Install or production database it is recommended that you use the Database Configuration Assistant. Once the database is created the instance configuration can be done according to the environment requirements and based on the production recommendations.
The script to create product users is located under the relevant database version subdirectory of the DatabaseCreation directory.
1. Create the CISTS_01 tablespace before running the script to create 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.
The UNIX and Windows database creation utilities create an empty database with AL32UTF8 character set and at least one tablespace for storing the application objects before running the installation. The default name of the application tablespace is CISTS_01.
This section includes:
Creating the Database on Unix
Create the database using the Database Configuration Assistant (DBCA). Refer to the
Overview of Database Configuration Assistant (DBCA) (Doc ID 1488770.1) document on
My Oracle Support.
Make sure to set AL32UTF8 as the character set for the database.
Refer to the
Installation Overview section for instructions to create the database.
Creating the Database on Windows
The files for creating the database are located in the …/DB/MDM.V2.5.0.1.1/DatabaseCreation/Windows directory in directory.
You should be logged in as a user who is a member of the local ORA_DBA group on that server. The ORA_DBA group should have “administrator” privileges assigned to it.
Create the database using the Database Configuration Assistant (DBCA). Refer to the
Overview of Database Configuration Assistant (DBCA) (Doc ID 1488770.1) document on
My Oracle Support.
Make sure to set AL32UTF8 as the character set for the database.
Refer to the
Installation Overview section for instructions to create the database.
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:
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: 'TBALE1_NAME','TABLE2_NAME'
Installing the CISADM Schema
Install Oracle Utilities Application Framework V4.5.0.1.1 and then install Oracle Utilities Smart Grid Gateway 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
Before proceeding with the installation make sure to 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
You can execute OraDBI.jar using either of the following methods:
Using Interactive Mode
This procedure lists the steps to install the Oracle Utilities Application Framework V4.5.0.1.1 component 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>
• 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.
You are now ready to install the product using the Java based OraDBI.jar tool.
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 Smart Grid Gateway Database Component
Note: The Oracle Utilities Meter Data Management Database Component is a cumulative package comprising of data used by all the Oracle Utilities Smart Grid Gateway and Oracle Utilities Service Order Management adapters.
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) - CLASSPATH
• Java Home (for example: <Java Install location>) - JAVA_HOME
You can run OraDBI.jar using either of the following methods:
Using the Interactive Mode
This procedure lists the steps to install the Oracle Utilities Smart Grid Gateway component using OraDBI.
Run the following command with the defined parameters on the command prompt from the ../DB/MDM_V2.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>
• 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.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)
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.
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, 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 necessary 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
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 by 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 please do so using the following steps.
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;
Please 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, use the following procedure to create the materialized view and then refresh the materialized view.
From the command prompt, go to ../DB/MDM_V2.5.0.1.1/Post-Upgrade/ and run the scripts below.
1. Login to SQLPlus as CISADM user.
2. On SQL prompt run:
@Materialized_View_Creation.sql
3. Exit SQLPlus.
Configuring Security
To configure the security, follow these instructions:
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.1.1/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 <R_USER_ROLE>,<RW_USER_ROLE> -u <RUSER>,<RWUSER>
Populating the CI_INSTALL_PROD Table
After completing the Oracle Utilities Smart Grid Gateway database installation, run the scripts based on the adapter being upgraded.
Navigate to ../DB/MDM_V2.5.0.1.1/Post-Upgrade/ and connect to the CISADM schema and run the relevant script.
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);