Installing the Oracle Database
This section describes how to install the Oracle Database for Oracle Utilities Digital Asset Management V2.0.0.1.2. It includes the following:
Note that the installation tools outlined in this guide run on Windows and UNIX/Linux only. Refer to
Supported Database Platforms for more information on supported platforms.
Database Scripts and Utilities
Follow these steps before you begin installing the database. Installation scripts can be executed in a Linux or Windows machine with Oracle 19c installed.
Initial Install (Installing V2.0.0.1.2 for the First Time)
This section describes an initial installation of the V2.0.0.1.2 database. It focuses on the following:
Note: You must have a supported version of the Java Development Kit installed on the Windows desktop where you stage and run the database installation package. Refer to the Oracle Utilities Digital Asset Management Installation Guide for more information.
Copying and Decompressing Install Media
To copy and decompress the Oracle Utilities Digital Asset Management database:
1. Download Oracle Utilities Digital Asset Management from Oracle Software Delivery Cloud (eDelivery).
• Oracle Utilities Application Framework V4.5.0.1.2 Oracle Database
• Oracle Utilities Application Framework V4.5.0.1.2 Single Fix Prerequisite Database Rollup for Oracle Utilities Digital Asset Management V2.0.0.1.2 (if there is any)
• Oracle Utilities Customer Care and Billing V2.9.0.1.2 Oracle Database
• Oracle Utilities Customer Care and Billing V2.9.0.1.2 Single Fix Database Rollup MultiPlatform (if there is any)
• Oracle Utilities Meter Data Management V2.5.0.1.2 Oracle Database
• Oracle Utilities Meter Data Management V2.5.0.1.2 Single Fix Database Rollup MultiPlatform (if there is any)
• Oracle Utilities Work and Asset Management V2.4.0.1.2 Oracle Database
• Oracle Utilities Work and Asset Management V2.4.0.1.2 Single Fix Database Rollup MultiPlatform (if there is any)
• Oracle Utilities Digital Asset Management V2.0.0.1.2 Oracle Database
• Oracle Utilities Digital Asset Management V2.0.0.1.2 Single Fix Database Rollup MultiPlatform (if there is any)
2. Copy and extract the downloaded zipped files to your local machine. These files include all the database components required to install the Oracle Utilities Application Framework and Oracle Utilities Digital Asset Management databases.
Database Creation
Note: You must have Oracle Database Server installed on your machine to create the database. This step is not required if you are performing a database upgrade from a previous version of Oracle Utilities Digital Asset Management or Oracle Utilities Customer Care and Billing, Oracle Utilities Meter Data Management, and Oracle Utilities Work and Asset Management to Oracle Utilities Digital Asset Management.
Creating the Database on UNIX
Create the database using the Database Configuration Assistant (DBCA).
Refer to the article Master Note: Overview of Database Configuration Assistant (DBCA) (Doc ID 1488770.1) on My Oracle Support for more information. Make sure to set character set for database as follows:
• Character set for database as AL32UTF8
• National Character Set (NLS_NCHAR_CHARACTERSET - AL16UTF16)
Refer to
Creating the Database for steps to create the database.
Creating the Database on Windows
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.
Refer to the article Master Note: Overview of Database Configuration Assistant (DBCA) (Doc ID 1488770.1) on My Oracle Support for more information. Make sure to set character set for database as follows:
• Character set for database as AL32UTF8
• National Character Set (NLS_NCHAR_CHARACTERSET - AL16UTF16)
Refer to
Creating the Database for steps to create the database.
Database Globalization Support Consideration
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. This setting should be modified to use CHARACTER length semantics by setting NLS_LENGTH_SEMANTICS to CHAR at session level via a logon trigger during installation as shown below.
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 semantics:
• By script: For details, refer to the Doc ID 313175.1on My Oracle Support.
• Alternative procedure: Below is an alternate way to create a schema with character-length semantics, and then importing the date from a byte-based export.
Initial Install
1. Create the database using DBCA.
2. Run the following statement to set nls_length_semantics=CHAR.
SQL> ALTER SYSTEM SET nls_length_semantics=CHAR SCOPE=BOTH;
Note: Ensure to set nls_length_semantics=CHAR on the pluggable DB level only.
3. Restart the database.
4. Verify that the nls_length_semantics is CHAR using the following command:
SQL> SHOW PARAMETER nls_length_semantics
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 OUAF database needs to make use of Extended Data Types - Oracle database 12c feature (EXTENDED means that the 32767 byte limit introduced in Oracle Database 12c applies.).
Enable the Extended Data Types by setting DB parameter, max_string_size = EXTENDED.
Follow the instructions provided in
Oracle database documentation for including this change in your database.
Important! This change in your database environment is mandatory. If not included it will lead to errors during the V4.5.0.1.2 installation.
Enabling DBMS_CRYPTO Package
Before installing Oracle Utilities Application Framework V4.5.0.1.2 make sure to provide execute privilege on dbms_crypto package to CISADM user. Execute the following command as SYS.
grant execute on dbms_crypto to CISADM;
Installing the CISADM Schema
Install Oracle Utilities Digital Asset Management in the order mentioned below:
• Oracle Utilities Application Framework V4.5.0.1.2 Oracle Database
• Oracle Utilities Application Framework V4.5.0.1.2 Single Fix Prerequisite Database Rollup for Oracle Utilities Digital Asset Management V2.0.0.1.2 (if there is any)
• Oracle Utilities Customer Care and Billing V2.9.0.1.2 Oracle Database
• Oracle Utilities Customer Care and Billing V2.9.0.1.2 Single Fix Database Rollup MultiPlatform (if there is any)
• Oracle Utilities Meter Data Management V2.5.0.1.2 Oracle Database
• Oracle Utilities Meter Data Management V2.5.0.1.2 Single Fix Database Rollup MultiPlatform (if there is any)
• Oracle Utilities Work and Asset Management V2.4.0.1.2 Oracle Database
• Oracle Utilities Work and Asset Management V2.4.0.1.2 Single Fix Database Rollup MultiPlatform (if there is any)
• Oracle Utilities Digital Asset Management V2.0.0.1.2 Oracle Database
• Oracle Utilities Digital Asset Management V2.0.0.1.2 Single Fix Database Rollup MultiPlatform (if there is any)
The installation process prompts you for the following information:
• The target database name in which the product is to be installed.
• A database user that will own the application schema (Example: CISADM).
• 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.
• A database user with read-only privileges to the objects in the application schema. (Example: CISREAD).
• A database role that has read-write (select/update/insert/delete) privileges to the objects in the application schema. The application will access the database as this user. (Example: CIS_USER).
• A database role with read-only privileges to the objects in the application schema. (Example: CIS_READ).
• Location for jar files. (The Jar files are bundled with the database package.)
• Java Home (Example: C:\Java\jdk1.8)
This section focuses on the following:
Installing the Oracle Utilities Application Framework Database Component
Note: The Oracle Utilities Application Framework database component can be installed using OraDBI.java. While prior versions of the product have included OraDBI.exe, this is no longer supported going forward as this does not support the latest functionality/features introduced in OraDBI.java.OraDBI.jar is delivered in directory jarfiles.
This section provides the instructions to install the database component.
Installing the Oracle Utilities Application Framework Database Component Using OraDBI.java
OraDBI.java is a tool to install and upgrade database components. It can be run from UNIX or Windows machines that has the following installed:
• Oracle 19c or
• Oracle 19c client
Before installing the database component, make sure the following prerequisites are met.
• JDK 1.8
• Oracle Database
• Schema (such as CISADM) should exist in the database
• max_string_size is set to EXTENDED
To install Oracle Utilities Application Framework V4.5.0.1.2:
1. Unzip the Oracle Utilities Application Framework V4.5.0.1.2 Oracle Database package.
2. Set JAVA_HOME, CLASSPATH, and PATH.
UNIX:
export JAVA_HOME=/scratch/software/jdk1.8.0_102/
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=/../../FW/jarfiles/*
WINDOWS:
SET JAVA_HOME=C:\Program Files\Java\jdk1.8.0_101
SET PATH=%JAVA_HOME%\bin:%PATH%
SET CLASSPATH= C:\..\FW\jarfiles\*
3. Run the following command from the command line or command prompt from ..\FW\Install-Upgrade folder.
There are two options available to execute OraDBI.java:
• Using interactive mode
• Using command on command line
Using Interactive Mode
UNIX:
java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -p <RW_USERPASS>,<R_USERPASS>
WINDOWS:
"C:\Program Files\Java\jdk1.8.0_101"\bin\java -Xmx1500M -cp C:\..\FW\jarfiles\* com.oracle.ouaf.oem.install.OraDBI -p <RW_USERPASS>,<R_USERPASS>
The utility prompts you to enter values for the parameters listed below:
• Enter the database server hostname:<SERVER NAME>
• Enter the database port number:<PORT>
• Enter the database name/SID:<DB NAME>
• Enter your database username:<CISADM>
• Enter your password for username CISADM:
• Enter the location for Java Home: <C:\Program Files\Java\jdk1.8.0_101>
• Enter the Oracle user with read-write privileges to Database Schema:<CISUSER>
• Enter the Oracle user with read-only privileges to Database Schema:<CISREAD>
• Enter the database role with read-write privileges to Database Schema:<CIS_USER>
• Enter the database role with read-only privileges to Database Schema:<CIS_READ>
• Enter the name of the target Schema where you want to install or upgrade:<CISADM>
Using the Command Line:
UNIX:
java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -d
jdbc:oracle:thin:@<DB_SERVER>:<PORT>/
<SID>,<DBUSER>,<DBPASS>,<RW_USER>,<R_USER>,<RW_USER_ROLE>,<R_USER_
ROLE>,<DBUSER> -p <RW_USERPASS>,<R_USERPASS>
WINDOWS:
"C:\Program Files\Java\jdk1.8.0_101"\bin\java -Xmx1500M -cp C:\ ..\FW\jarfiles\* com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<DB_SERVER>:<PORT>/ <SID>,<DBUSER>,<DBPASS>,<RW_USER>,<R_USER>,<RW_USER_ROLE>,<R_USER ROLE>,<DBUSER> -p <RW_USERPASS>,<R_USERPASS>
This process generates log files in the directory ..\FW\Install-Upgrade\logs.
4. Make sure to check the log files for any errors.
Note: For OraDBI java, 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 Oracle Utilities Application Framework Prerequisite Database Single Fixes
Before installing any edge products like Oracle Utilities Customer Care and Billing, Oracle Utilities Meter Data Management, Oracle Utilities Work and Asset Management and Oracle Utilities Digital Asset Management, you must install Oracle Utilities Framework Prerequisite Database Hot Fixes.
Important! Confirm if there are Prerequisite Database Single Fixes. Check if there is a C2M-V2.9.0.1.2-FW-Database-PREREQ-MultiPlatform folder. If none, skip this step and proceed to apply Oracle Utilities Customer Care and Billing.
Applying Hot Fixes
Note: Java 8 JDK should be installed on the machine to use the command. Ensure to install the JDK that is supported for your platform.
1. Extract db_patch_standalone.jar from Oracle Utilities Application Framework V4.5.0.1.2 Single Fix Prerequisite Database Rollup for C2M V2.9.0.1.2 package to any directory on your local machine under dbpatch_tools folder.
UNIX:
cd /../dbpatch_tools
jar xvf db_patch_standalone.jar
WINDOWS:
cd c:\..\dbpatch_tools
jar xvf db_patch_standalone.jar
2. SET TOOLSBIN.
UNIX:
export TOOLSBIN=/../dbpatch_tools/bin
WINDOWS:
SET TOOLSBIN=c:\..\dbpatch_tools\bin
3. Apply prerequisite Framework DB single fixes from FW45012-HFix folder in Oracle Utilities Application Framework V4.5.0.1.2 Single Fix Prerequisite Database Rollup for C2M V2.9.0.1.2 package.
UNIX:
a. Change the permission of ouafDatabasePatch.sh tool.
chmod 755 ouafDatabasePatch.sh
b. Run the ouafDatabasePatch.sh tool.
sh ouafDatabasePatch.sh (or) ./ouafDatabasePatch.sh
WINDOWS:
Run the ouafDatabasePatch.cmd tool.
ouafDatabasePatch.cmd
The utility prompts you to enter values for the parameters listed below:
• Enter the target database type (O/M/D) [O]: <O>
• Enter the username that owns the schema: <CISADM>
• Enter the password for the cisadm user: <CISADM Password>
• Enter the name of the Oracle Database Connection String: <DB_Server:DBPORT/ORACLE_SID>
Installing the Oracle Utilities Customer Care and Billing Database Component
Note: The Oracle Utilities Customer Care and Billing database component can be installed using OraDBI.java. While prior versions of the product have included OraDBI.exe, this is no longer supported going forward as this does not support latest functionality/features introduced in OraDBI.java. OraDBI.jar is delivered in directory jarfiles.
This section provides the instructions to install the database component.
Installing the Oracle Utilities Customer Care and Billing Database Component Using OraDBI.java
OraDBI.java is a new tool to install and upgrade database components. It can be run from UNIX or Windows server that has the following installed:
• Oracle 19c or
• Oracle 19c Client
Before installing the Oracle Utilities Customer Care and Billing V2.9.0.1.2, ensure the following prerequisites are met.
• JDK 1.8
• Oracle Database
• Schema (such as CISADM) should exist in the database
• max_string_size is set to EXTENDED
To install the Oracle Utilities Customer Care and Billing V2.9.01.2 database:
1. Unzip Oracle Utilities Customer Care and Billing V2.9.01.2 Oracle Database package.
2. Set JAVA_HOME, PATH, and CLASSPATH.
UNIX:
export JAVA_HOME=/scratch/software/jdk1.8.0_102/
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=/../../FW/jarfiles/*
WINDOWS:
SET JAVA_HOME=C:\Program Files\Java\jdk1.8.0_101
SET PATH=%JAVA_HOME%\bin:%PATH%
SET CLASSPATH= C:\..\FW\jarfiles\*
3. Run the following command from the command line or command prompt from the ..\CCB\Install-Upgrade folder.
There are two options available to execute OraDBI.java:
• Using interactive mode
• Using command on command line
Using Interactive Mode
UNIX:
java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -p <RW_USERPASS>,<R_USERPASS>
WINDOWS:
"C:\Program Files\Java\jdk1.8.0_101"\bin\java -Xmx1500M -cp C:\..\FW\jarfiles\* com.oracle.ouaf.oem.install.OraDBI -p <RW_USERPASS>,<R_USERPASS>
The utility prompts you to enter values for the parameters listed below:
• Enter the database server hostname:<SERVER NAME>
• Enter the database port number:<PORT>
• Enter the database name/SID:<DB NAME>
• Enter your database username:<CISADM>
• Enter your password for username CISADM:
• Enter the Oracle user with read-write privileges to Database Schema:<CISUSER>
• Enter your password for username CISUSER:
• Enter the Oracle user with read-only privileges to Database Schema:<CISREAD>
• Enter your password for username CISREAD:
• Enter the database role with read-write privileges to Database Schema:<CIS_USER>
• Enter the database role with read-only privileges to Database Schema:<CIS_READ>
• Enter the name of the target Schema where you want to install or upgrade:<CISADM>
Using the Command Line
UNIX:
java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<DB_SERVER>:<PORT>/ <SID>,<DBUSER>,<DBPASS>,<RW_USER>,<R_USER>,<RW_USER_ROLE>,<R_US ER_ROLE>,<DBUSER> -p <RW_USERPASS>,<R_USERPASS>
WINDOWS:
"C:\Program Files\Java\jdk1.8.0_101"\bin\java -Xmx1500M -cp C:\..\FW\jarfiles\* com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<DB_SERVER>:<PORT>/ <SID>,<DBUSER>,<DBPASS>,<RW_USER>,<R_USER>,<RW_USER_ROLE>,<R_US ER ROLE>,<DBUSER> -p <RW_USERPASS>,<R_USERPASS>
This process generates log files in the directory ..\CCB\Upgrade\Install-Upgrade\logs.
4. Make sure to check the log files for any errors.
Note: For OraDBI java, 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 Oracle Utilities Customer Care and Billing Database Rollup
Important! Proceed with the steps in this section only if the installation package contains the CCB-V2.9.0.1.2-Database-Rollup-MultiPlatform folder. If none, skip this step and proceed to apply Oracle Utilities Meter Data Management.
Make sure Java 8 JDK is installed on the machine to use the commands. The JDK version that is supported for your platform should be installed.
To apply Oracle Utilities Customer Care and Billing V29012 Rollup:
2. Apply CCB 29012 Rollup from the ..\ CCB-V2.9.01.2-Database-Rollup-MultiPlatform\CCB29012-HFix folder.
UNIX:
a. Change the permission of ouafDatabasePatch.sh tool.
chmod 755 ouafDatabasePatch.sh
b. Run the ouafDatabasePatch.sh tool.
sh ouafDatabasePatch.sh or ./ouafDatabasePatch.sh
WINDOWS:
a. Run the ouafDatabasePatch.cmd tool.
ouafDatabasePatch.cmd
The utility prompts you to enter values for the parameters listed below:
• Enter the target database type (O/M/D) [O]: <O>
• Enter the username that owns the schema: <CISADM>
• Enter the password for the cisadm user: <CISADM Password>
• Enter the name of the Oracle Database Connection String: <DB_Server:DBPORT/ORACLE_SID>
Installing the Oracle Utilities Meter Data Management Database Component
Note: The Oracle Utilities Meter Data Management database component can be installed using OraDBI.java. While prior versions of the product have included OraDBI.exe, this is no longer supported going forward as this does not support latest functionality/features introduced in OraDBI.java. OraDBI.jar is delivered in directory jarfiles.
This section provides the instructions to install the database component.
Installing the Oracle Utilities Meter Data Management Component Using OraDBI.java
OraDBI.java is a new tool to install and upgrade database components. It can be run from UNIX or Windows server that has the following installed:
• Oracle 19c or
• Oracle 19c Client
Before installing the Oracle Utilities Meter Data Management V2.5.0.1.2, make sure the following prerequisites are met.
• JDK 1.8
• Oracle Database
• Schema (such as CISADM) should exist in the database
• max_string_size is set to EXTENDED
To install the Oracle Utilities Meter Data Management V2.5.0.1.2:
Using UNIX machine
1. Unzip Oracle Utilities Meter Data Management V2.5.0.1.2 Oracle Database package.
2. Set JAVA_HOME, CLASSPATH, and PATH.
UNIX:
export JAVA_HOME=/scratch/software/jdk1.8.0_102/
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=/../../FW/jarfiles/*
WINDOWS:
SET JAVA_HOME=C:\Program Files\Java\jdk1.8.0_101
SET PATH=%JAVA_HOME%\bin:%PATH%
SET CLASSPATH= C:\..\FW\jarfiles\*
3. Run the following command from the command line or command prompt from the ..\MDM\Install-Upgrade folder.
There are two options available to execute OraDBI.java:
• Using interactive mode
• Using command on command line
Using Interactive Mode
UNIX:
java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -p <RW_USERPASS>,<R_USERPASS>
WINDOWS:
"C:\Program Files\Java\jdk1.8.0_101"\bin\java -Xmx1500M -cp C:\..\FW\jarfiles\* com.oracle.ouaf.oem.install.OraDBI -p <RW_USERPASS>,<R_USERPASS>
The utility prompts you to enter values for the parameters listed below:
• Enter the database server hostname:<SERVER NAME>
• Enter the database port number:<PORT>
• Enter the database name/SID:<DB NAME>
• Enter your database username:<CISADM>
• Enter your password for username CISADM:
• Enter the Oracle user with read-write privileges to Database Schema:<CISUSER>
• Enter your password for username CISUSER:
• Enter the Oracle user with read-only privileges to Database Schema:<CISREAD>
• Enter your password for username CISREAD:
• Enter the database role with read-write privileges to Database Schema:<CIS_USER>
• Enter the database role with read-only privileges to Database Schema:<CIS_READ>
• Enter the name of the target Schema where you want to install or upgrade:<CISADM>
Using the Command Line
UNIX:
java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<DB_SERVER>:<PORT>/ <SID>,<DBUSER>,<DBPASS>,<RW_USER>,<R_USER>,<RW_USER_ROLE>,<R_US ER_ROLE>,<DBUSER> -p <RW_USERPASS>,<R_USERPASS>
WINDOWS:
"C:\Program Files\Java\jdk1.8.0_101"\bin\java -Xmx1500M -cp C:\..\FW\jarfiles\* com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<DB_SERVER>:<PORT>/ <SID>,<DBUSER>,<DBPASS>,<RW_USER>,<R_USER>,<RW_USER_ROLE>,<R_US ER ROLE>,<DBUSER> -p <RW_USERPASS>,<R_USERPASS>
This process generates log files in the directory ..\MDM\Upgrade\Install-Upgrade\logs.
4. Make sure to check the log files for any errors.
Note: For OraDBI java, 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)
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 ..\MDM-V2.5.0.1.2-Oracle-Database-Multiplatform\MDM\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.
Installing Oracle Utilities Meter Data Management Database Rollup
Important! Proceed with the steps in this section only if the installation package contains the MDM-V2.5.0.1.2-Database-Rollup-MultiPlatform folder. If none, skip this step and proceed to apply Oracle Utilities Operational Device Management.
Make sure Java 8 JDK is installed on the machine to use the commands. The JDK version that is supported for your platform should be installed.
To apply Oracle Utilities Meter Data Management V25012 Rollup:
2. Apply MDM 25012 Rollup from the ..\ MDM-V2.5.01.2-Database-Rollup-MultiPlatform\MDM25012-HFix folder.
UNIX:
a. Change the permission of ouafDatabasePatch.sh tool.
chmod 755 ouafDatabasePatch.sh
b. Run the ouafDatabasePatch.sh tool.
sh ouafDatabasePatch.sh or ./ouafDatabasePatch.sh
WINDOWS:
a. Run the ouafDatabasePatch.cmd tool.
ouafDatabasePatch.cmd
The utility prompts you to enter values for the parameters listed below:
• Enter the target database type (O/M/D) [O]: <O>
• Enter the username that owns the schema: <CISADM>
• Enter the password for the cisadm user: <CISADM Password>
• Enter the name of the Oracle Database Connection String: <DB_Server:DBPORT/ORACLE_SID>
Installing the Oracle Utilities Operational Device Management Database Component
Note: The Oracle Utilities Operational Device Management database component can be installed using OraDBI.java. While prior versions of the product have included OraDBI.exe, this is no longer supported going forward as this does not support latest functionality/features introduced in OraDBI.java. OraDBI.jar is delivered in directory jarfiles.
This section provides the instructions to install the database component.
Installing the Oracle Utilities Operational Device Management Using OraDBI.java
OraDBI.java is a new tool to install and upgrade database components. It can be run from UNIX or Windows server that has the following installed:
• Oracle 19c or
• Oracle 19c Client
Before installing the Oracle Utilities Operational Device Management V2.4.0.1.2, make sure the following prerequisites are met.
• JDK 1.8
• Oracle Database
• Schema (such as CISADM) should exist in the database
• max_string_size is set to EXTENDED
To install the Oracle Utilities Operational Device Management V2.4.0.1.2:
Using UNIX machine
1. Unzip the Oracle Utilities Work and Asset Management V2.4.0.1.2 Oracle Database package.
2. Unzip the WAM-V2.4.0.1.2-Database.zip package.
3. Set JAVA_HOME, CLASSPATH, and PATH.
UNIX:
export JAVA_HOME=/scratch/software/jdk1.8.0_102/
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=/../../FW/jarfiles/*
WINDOWS:
SET JAVA_HOME=C:\Program Files\Java\jdk1.8.0_101
SET PATH=%JAVA_HOME%\bin:%PATH%
SET CLASSPATH= C:\..\FW\jarfiles\*
4. Run the following command from the command line or command prompt from the ..\WAM\Install-Upgrade folder.
There are two options available to execute OraDBI.java:
• Using interactive mode
• Using command on command line
Using Interactive Mode
UNIX:
java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -p <RW_USERPASS>,<R_USERPASS>
WINDOWS:
"C:\Program Files\Java\jdk1.8.0_101"\bin\java -Xmx1500M -cp C:\..\FW\jarfiles\* com.oracle.ouaf.oem.install.OraDBI -p <RW_USERPASS>,<R_USERPASS>
The utility prompts you to enter values for the parameters listed below:
• Enter the database server hostname:<SERVER NAME>
• Enter the database port number:<PORT>
• Enter the database name/SID:<DB NAME>
• Enter your database username:<CISADM>
• Enter your password for username CISADM:
• Enter the Oracle user with read-write privileges to Database Schema:<CISUSER>
• Enter your password for username CISUSER:
• Enter the Oracle user with read-only privileges to Database Schema:<CISREAD>
• Enter your password for username CISREAD:
• Enter the database role with read-write privileges to Database Schema:<CIS_USER>
• Enter the database role with read-only privileges to Database Schema:<CIS_READ>
• Enter the name of the target Schema where you want to install or upgrade:<CISADM>
Using the Command Line
UNIX:
java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<DB_SERVER>:<PORT>/ <SID>,<DBUSER>,<DBPASS>,<RW_USER>,<R_USER>,<RW_USER_ROLE>,<R_US ER_ROLE>,<DBUSER> -p <RW_USERPASS>,<R_USERPASS>
WINDOWS:
"C:\Program Files\Java\jdk1.8.0_101"\bin\java -Xmx1500M -cp C:\..\FW\jarfiles\* com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<DB_SERVER>:<PORT>/ <SID>,<DBUSER>,<DBPASS>,<RW_USER>,<R_USER>,<RW_USER_ROLE>,<R_US ER ROLE>,<DBUSER> -p <RW_USERPASS>,<R_USERPASS>
This process generates log files in the directory ..\WAM\Upgrade\Install-Upgrade\logs.
5. Make sure to check the log files for any errors.
Note: For OraDBI java, 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 Oracle Utilities Operational Device Management Database Rollup
Important! Proceed with the steps in this section only if the installation package contains the WAM-V2.4.0.1.2-Database-Rollup-MultiPlatform folder. If none, skip this step and proceed to apply Oracle Utilities Digital Asset Management.
Make sure Java 8 JDK is installed on the machine to use the commands. The JDK version that is supported for your platform should be installed.
To apply Oracle Utilities Operational Device Management V24012 Rollup:
2. Apply WAM 24012 Rollup from the ..\ WAM-V2.4.01.2-Database-Rollup- MultiPlatform\WAM24012-HFix folder.
UNIX:
a. Change the permission of ouafDatabasePatch.sh tool.
chmod 755 ouafDatabasePatch.sh
b. Run the ouafDatabasePatch.sh tool.
sh ouafDatabasePatch.sh or ./ouafDatabasePatch.sh
WINDOWS:
a. Run the ouafDatabasePatch.cmd tool.
ouafDatabasePatch.cmd
The utility prompts you to enter values for the parameters listed below:
• Enter the target database type (O/M/D) [O]: <O>
• Enter the username that owns the schema: <CISADM>
• Enter the password for the cisadm user: <CISADM Password>
• Enter the name of the Oracle Database Connection String: <DB_Server:DBPORT/ORACLE_SID>
Installing the Oracle Utilities Digital Asset Management Database Component
Note: The Oracle Utilities Digital Asset Management database component can be installed using OraDBI.java. While prior versions of the product have included OraDBI.exe, this is no longer supported going forward as this does not support latest functionality/features introduced in OraDBI.java. OraDBI.jar is delivered in directory jarfiles.
This section provides the instructions to install the database component.
Installing the Oracle Utilities Digital Asset Management Component Using OraDBI.java
OraDBI.java is a new tool to install and upgrade database components. It can be run from UNIX or Windows server that has the following installed:
• Oracle 19c or
• Oracle 19c Client
Before installing Oracle Utilities Digital Asset Management V2.0.0.1.2, make sure the following prerequisites are met.
• JDK 1.8
• Oracle Database
• Schema (such as CISADM) should exist in the database
• max_string_size is set to EXTENDED
To install Oracle Utilities Digital Asset Management V2.0.0.1.2:
Using UNIX machine
1. Unzip the C2M-V2.9.0.1.2-Oracle-Database-Multiplatform.zip package.
2. Set JAVA_HOME, CLASSPATH and PATH.
UNIX:
export JAVA_HOME=/scratch/software/jdk1.8.0_102/
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=/../../FW/jarfiles/*
WINDOWS:
SET JAVA_HOME=C:\Program Files\Java\jdk1.8.0_101
SET PATH=%JAVA_HOME%\bin:%PATH%
SET CLASSPATH= C:\..\FW\jarfiles\*
3. Execute the following command from the command line or command prompt from the ..\C2M\Install-Upgrade folder.
There are two options available to execute OraDBI.java:
• Using interactive mode
• Using command on command line
Using Interactive Mode
UNIX:
java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -p <RW_USERPASS>,<R_USERPASS>
WINDOWS:
"C:\Program Files\Java\jdk1.8.0_101"\bin\java -Xmx1500M -cp C:\..\FW\jarfiles\* com.oracle.ouaf.oem.install.OraDBI -p <RW_USERPASS>,<R_USERPASS>
The utility prompts you to enter values for the parameters listed below:
• Enter the database server hostname:<SERVER NAME>
• Enter the database port number:<PORT>
• Enter the database name/SID:<DB NAME>
• Enter your database username:<CISADM>
• Enter your password for username CISADM:
• Enter the Oracle user with read-write privileges to Database Schema:<CISUSER>
• Enter your password for username CISUSER:
• Enter the Oracle user with read-only privileges to Database Schema:<CISREAD>
• Enter your password for username CISREAD:
• Enter the database role with read-write privileges to Database Schema:<CIS_USER>
• Enter the database role with read-only privileges to Database Schema:<CIS_READ>
• Enter the name of the target Schema where you want to install or upgrade:<CISADM>
Using the Command Line
UNIX:
java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<DB_SERVER>:<PORT>/ <SID>,<DBUSER>,<DBPASS>,<RW_USER>,<R_USER>,<RW_USER_ROLE>,<R_US ER_ROLE>,<DBUSER> -p <RW_USERPASS>,<R_USERPASS>
WINDOWS:
"C:\Program Files\Java\jdk1.8.0_101"\bin\java -Xmx1500M -cp C:\..\FW\jarfiles\* com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<DB_SERVER>:<PORT>/ <SID>,<DBUSER>,<DBPASS>,<RW_USER>,<R_USER>,<RW_USER_ROLE>,<R_US ER ROLE>,<DBUSER> -p <RW_USERPASS>,<R_USERPASS>
This process generates log files in the directory ..\C2M\Upgrade\Install-Upgrade\logs.
4. Make sure to check the log files for any errors.
Note: For OraDBI java, 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 Oracle Utilities Digital Asset Management Database Rollup
Important! Proceed with the steps in this section only if the installation package contains the C2M-V2.9.0.1.2-Database-Rollup-MultiPlatform folder. If none, skip this step and proceed to the
Post-Installation Tasks.
Make sure Java 8 JDK is installed on the machine to use the commands. The JDK version that is supported for your platform should be installed.
To apply Oracle Utilities Digital Asset Management V20012 Rollup:
2. Apply C2M 29012 Rollup from the ..\ C2M-V2.9.0.1.2-Database-Rollup-MultiPlatform\C2M29012-HFix folder.
UNIX:
a. Change the permission of ouafDatabasePatch.sh tool.
chmod 755 ouafDatabasePatch.sh
b. Run the ouafDatabasePatch.sh tool.
sh ouafDatabasePatch.sh or ./ouafDatabasePatch.sh
WINDOWS:
a. Run the ouafDatabasePatch.cmd tool.
ouafDatabasePatch.cmd
The utility prompts you to enter values for the parameters listed below:
• Enter the target database type (O/M/D) [O]: <O>
• Enter the username that owns the schema: <CISADM>
• Enter the password for the cisadm user: <CISADM Password>
• Enter the name of the Oracle Database Connection String: <DB_Server:DBPORT/ORACLE_SID>
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).
• 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.
• OraDBI can be executed by a non-schema owner.
Post-Installation Tasks
Enable USER_LOCK Package
For inbound web services to work the USER_LOCK must be enabled at the database level. This is a one-time step. If not already enabled, perform these steps to enable it:
1. Login as SYS user.
2. On the SQL prompt, run:
@?/rdbms/admin/userlock.sql
3. Grant permission.
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
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.
Creating Activity Statistics Materialized View (Optional)
To improve the performance of drill down queries, use the following procedure to create the materialized view and then refresh it.
1. Navigate to ..\ MDM\Post-Upgrade folder in Oracle Utilities Meter Data Management V2.5.0.1.2 Oracle Database package.
2. Login as CISADM user.
3. At the SQL prompt, run the following:
@D1_ACTIVITY_STAT_MV.sql
@D1_MV_REFRESH_PROC.sql
Creating Index D1T304S3 for Payload Statistic Functionality (Optional)
This index does not exist for an initial installation. 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);
Setup Oracle Utilities Digital Asset Management Configuration
This section is mandatory for every Oracle Utilities Digital Asset Management installation.
Note: The script can be run from any machine that has Oracle client installed and can connect to the database.
1. Navigate to ..\..\C2M\Post-Install\ folder in Oracle Utilities Digital Asset Management V2.0.0.1.2 Oracle Database.
2. Login as a schema owner.
3. On SQL prompt, run the command below:
@DACS.SQL
Upgrade Install
This section describes how to upgrade the database components for Oracle Utilities Digital Asset Management, including:
Copying and Decompressing Install Media
To copy and decompress the Oracle Utilities Digital Asset Management database:
1. Download Oracle Utilities Digital Asset Management from Oracle Software Delivery Cloud (eDelivery).
• Oracle Utilities Application Framework V4.5.0.1.2 Oracle Database
• Oracle Utilities Application Framework V4.5.0.1.2 Single Fix Prerequisite Database Rollup for Oracle Utilities Digital Asset Management V2.0.0.1.2 (if there is any)
• Oracle Utilities Customer Care and Billing V2.9.0.1.2 Oracle Database
• Oracle Utilities Customer Care and Billing V2.9.0.1.2 Single Fix Database Rollup MultiPlatform (if there is any)
• Oracle Utilities Meter Data Management V2.5.0.1.2 Oracle Database
• Oracle Utilities Meter Data Management V2.5.0.1.2 Single Fix Database Rollup MultiPlatform (if there is any)
• Oracle Utilities Work and Asset Management V2.4.0.1.2 Oracle Database
• Oracle Utilities Work and Asset Management V2.4.0.1.2 Single Fix Database Rollup MultiPlatform (if there is any)
• Oracle Utilities Digital Asset Management V2.0.0.1.2 Oracle Database
• Oracle Utilities Digital Asset Management V2.0.0.1.2 Single Fix Database Rollup MultiPlatform (if there is any)
2. Copy and extract the downloaded zipped files to your local machine. These files include all the database components required to install the Oracle Utilities Application Framework and Oracle Utilities Digital Asset Management databases.
Migrating from BYTE Based Storage to CHARACTER Based Storage
This section assumes that the database already exists for every Oracle Utilities Digital Asset Management installation using BYTE semantic.
1. Set nls_length_semantics=CHAR.
SQL> ALTER SYSTEM SET nls_length_semantics=CHAR SCOPE=BOTH;
2. Restart the database.
3. Make sure nls_length_semantics is CHAR.
SQL> SHOW PARAMETER nls_length_semantics
Note: Ensure to set nls_length_semantics=CHAR on the pluggable DB level only.
4. Export schema from the database that has nls_semantics_legth=BYTE.
expdp userid=system/<code>@<SID> directory=<DIR_NAME> schemas=<schema_name> dumpfile=<schema_name>.dmp logfile=<schema_name>.log
5. 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
6. Replace “Byte” with “Char” in <schema_name>DDL.sql.
For vi editor (in Linux), use the following command to replace Byte to Char.
:%s/BYTE/CHAR/g
7. Replace the schema name also if it is required for environment.
8. Run <schema_name>DDL.sql (generated in step 6) that creates objects in the schema.
To ensure 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;
9. If an 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).
Run DDL for the objects that are not created.
10. Generate DDL to disable triggers using following command:
SQL> SELECT 'ALTER TABLE' || ' ' ||TABLE_NAME || ' ' || 'DISABLE ALL TRIGGERS;' FROM USER_TABLES;
11. Run the script generated from step 11 to disable all triggers.
12. Import the data only.
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
13. Enable the triggers.
To generate DDL for triggers:
SQL>SELECT 'ALTER TABLE' || ' ' ||TABLE_NAME || ' ' || 'ENABLE ALL TRIGGERS;' FROM USER_TABLES;
14. Run the script generated from step 14 to enable all triggers.
Database Globalization Support Consideration
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. This setting should be modified to use CHARACTER length semantics by setting NLS_LENGTH_SEMANTICS to CHAR at session level via a logon trigger during installation as shown below.
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;
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 OUAF database needs to make use of Extended Data Types - Oracle database 12c feature (EXTENDED means that the 32767 byte limit introduced in Oracle Database 12c applies.).
Enable the Extended Data Types by setting DB parameter, max_string_size = EXTENDED.
Follow the instructions provided in
Oracle database documentation for including this change in your database.
Important! This change in your database environment is mandatory. If not included it will lead to errors during the V4.5.0.1.2 upgrade.
Excluding Table/Index
To exclude an index or table during the upgrade process:
1. Edit the OraSchUpg.inp file in the Install-Upgrade directory.
2. Add the tables and indexes in the following format:
-INDEX: 'INDEX_NAME','INDEX_NAME'
-TABLE: 'TBALE1_NAME','TABLE2_NAME'
For example: To exclude the F1_WEB_SVC table:
-TABLE:'F1_WEB_SVC'
If there are multiple tables, include them with separated commas.
-TABLES: 'TABLE-1','TABLE_2','TABLE_3'
Similarly for indexes:
-INDEX:' F1C064S1'
Granting Privileges to Database Roles
Before running the upgrade, make sure to grant the Create Synonym to the database read write (CIS_USER) and read only (CIS_READ) roles.
grant CREATE SYNONYM to CIS_USER;
grant CREATE SYNONYM to CIS_READ;
Enabling DBMS_CRYPTO Package
Before installing Oracle Utilities Application Framework V4.5.0.1.2 make sure to provide execute privilege on dbms_crypto package to CISADM user. Execute the following command as SYS.
grant execute on dbms_crypto to CISADM;
Upgrading the Database Component
A successful database upgrade consists of the following steps:
Upgrading Oracle Utilities Digital Asset Management to Oracle Utilities Digital Asset Management V2.0.0.1.2 from V2.0.0.1.1 or V2.0.0.1.0
This section assumes that only Oracle Utilities Digital Asset Management exists on top of Oracle Utilities Application Framework.
Make sure to install Oracle Utilities Digital Asset Management in the order mentioned below:
• Oracle Utilities Application Framework V4.5.0.1.2 Database
• Oracle Utilities Application Framework V4.5.0.1.2 Single Fix Prerequisite Database Rollup for Oracle Utilities Digital Asset Management V2.0.0.1.2 (if there is any)
• Oracle Utilities Customer Care and Billing V2.9.0.1.2 Oracle Database
• Oracle Utilities Customer Care and Billing V2.9.0.1.2 Single Fix Database Rollup MultiPlatform (if there is any)
• Oracle Utilities Meter Data Management V2.5.0.1.2 Oracle Database
• Oracle Utilities Meter Data Management V2.5.0.1.2 Single Fix Database Rollup MultiPlatform (if there is any)
• Oracle Utilities Work and Asset Management V2.4.0.1.2 Oracle Database
• Oracle Utilities Work and Asset Management V2.4.0.1.2 Single Fix Database Rollup MultiPlatform (if there is any)
• Oracle Utilities Digital Asset Management V2.0.0.1.2 Oracle Database
• Oracle Utilities Digital Asset Management V2.0.0.1.2 Single Fix Database Rollup MultiPlatform (if there is any)
Upgrading the Database as Non-Schema Owner
The product allows non-schema owners to run the database upgrade.
To perform the upgrade, the non-schema owner must have the following database grants:
• grant connect, CREATE SESSION to <Non-Schema owner>;
• grant select on <Schema owner>.CI_WFM to <Non-Schema owner>;
• grant select on <Schema owner>.CI_WFM_OPT to <Non-Schema owner>;
Upgrading the Oracle Utilities Application Framework Database Component
Installing Framework Prerequisite Database Single Fixes
Upgrading the Oracle Utilities Customer Care and Billing Database Component
Installing Oracle Utilities Customer Care and Billing Database Rollup
Upgrading the Oracle Utilities Meter Data Management Database Component
Installing Oracle Utilities Meter Data Management Database Rollup
Upgrading the Oracle Utilities Operational Device Management Database Component
Installing Oracle Utilities Operational Device Management Database Rollup
Upgrading the Oracle Utilities Digital Asset Management Database Component
Installing Oracle Utilities Digital Asset Management Database Rollup
Post-Installation Tasks
The post-installation steps are as follows:
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.
Creating Activity Statistics Materialized View (Optional)
Dropping Index D1T304S3 for Payload Statistic Functionality (Optional)
For an upgrade installation, this index already exists. If you are not using the Payload statistic functionality, or if you have no other SQL scripts referencing these fields, you may drop the index.
Connect to CISADM schema and execute the following:
DROP INDEX D1T304S3;
Setup Oracle Utilities Digital Asset Management Configurations
Demo Install
This section describes how to install the demo database components for Oracle Utilities Digital Asset Management, including:
Copying and Decompressing Install Media
To copy and decompress the Oracle Utilities Digital Asset Management database:
1. Download the Oracle Utilities Digital Asset Management V2.0.0.1.2 Oracle database from the Oracle Software Delivery Cloud.
2. Copy and extract the downloaded zipped file to your local machine.
Creating the Database
Note: You must have Oracle Database Server 19c installed on your machine in order to create the database.
It is strongly recommended to use DBCA to create the database.
Creating the Database on UNIX
Create the database using the Database Configuration Assistant (DBCA).
Refer to the article Master Note: Overview of Database Configuration Assistant (DBCA) (Doc ID 1488770.1) on My Oracle Support for more information. Make sure to set character set for database as follows:
• Character set for database as AL32UTF8
• National Character Set (NLS_NCHAR_CHARACTERSET - AL16UTF16)
Refer to
Creating the Database for steps to create the database.
Creating the Database on Windows
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.
Refer to the article Master Note: Overview of Database Configuration Assistant (DBCA) (Doc ID 1488770.1) on My Oracle Support for more information. Make sure to set character set for database as follows:
• Character set for database as AL32UTF8
• National Character Set (NLS_NCHAR_CHARACTERSET - AL16UTF16)
Refer to
Creating the Database for steps to create the database.
Database Storage BYTES/CHARACTER
Database created by default will store data in BYTES.
To store data in CHARACTER:
Initial Install
1. Set nls_length_semantics=CHAR.
SQL> ALTER SYSTEM SET nls_length_semantics=CHAR SCOPE=BOTH;
Note: Ensure to set nls_length_semantics=CHAR on the pluggable DB level only.
2. Restart the database.
3. Make sure nls_length_semantics is CHAR.
SQL> SHOW PARAMETER nls_length_semantics
Upgrade and Migration from BYTE Based Storage to CHARACTER Based Storage
This section assumes that the database already exists for every Oracle Utilities Digital Asset Management installation using BYTE semantic.
1. Set nls_length_semantics=CHAR.
SQL> ALTER SYSTEM SET nls_length_semantics=CHAR SCOPE=BOTH;
Note: Make sure to set nls_length_semantics=CHAR on the pluggable DB level only.
2. Restart the database.
3. Make sure nls_length_semantics is CHAR.
SQL> SHOW PARAMETER nls_length_semantics
4. Export schema from the database that has nls_semantics_legth=BYTE.
expdp userid=system/<code>@<SID> directory=<DIR_NAME> schemas=<schema_name> dumpfile=<schema_name>.dmp logfile=<schema_name>.log
5. 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
6. Replace “Byte” with “Char” in <schema_name>DDL.sql.
For the vi editor (in Linux), replace Byte to Char.
:%s/BYTE/CHAR/g
7. Replace the schema name also if it is required for environment.
8. Execute <schema_name>DDL.sql (generated in step 6) that creates objects in the schema.
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;
9. If an object is missing for any reason, create it by fixing DDL manually (DDL for each object is available in the file created in step 6).
Execute DDL for the objects that are not created.
10. Generate DDL to disable triggers.
SQL> SELECT 'ALTER TABLE' || ' ' ||TABLE_NAME || ' ' || 'DISABLE ALL TRIGGERS;' FROM USER_TABLES;
11. Run the script generated from step 11 to disable all triggers.
12. Import the 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
13. Enable the triggers. To generate DDL for triggers:
SQL>SELECT 'ALTER TABLE' || ' ' ||TABLE_NAME || ' ' || 'ENABLE ALL TRIGGERS;' FROM USER_TABLES;
15. Run the script generated from step No.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 should use the 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.
Follow the instructions provided in
Oracle database documentation for including this change in your database.
Important! This change in your database environment is mandatory. If not included, it will lead to errors during the V4.5.0.1.2 upgrade.
Importing the Demo Dump File
After a successful database creation, demo data can also be imported by using by following these steps:
1. Set the correct ORACLE_SID and ORACLE_HOME.
2. Make sure max_string_size is set to EXTENDED.
3. Import the demo dump.
Note: Make sure the ..\DAM-V2.0.0.1.2-DemoData \Demo\exp_demo.dmp.gz file is extracted and available in data_pump_dir's location before running the below import command.
impdp directory= data_pump_dir dumpfile= exp_demo.dmp logfile=exp_demo.log schemas=CISADM
4. Once the import is done, enable the DBMS_CRYPTO package. Execute the following command as SYS.
grant execute on dbms_crypto to CISADM;
Configuring Security
The configuration security utility and scripts are already part of the delivered jarfiles in ..\FW-V4.5.0.1.2-Oracle-Database-Multiplatform\FW\jarfiles directory. It can be run from a Linux or a Windows machine.
Note the following:
• Database vault must be disabled before running.
• Interactive mode for this utility is currently not working.
• -f parameter is not working and will be deprecated soon.
The utility configures security for the application owner schema objects.
Oragensec, by default, grantS permissions to CIS_USER and CIS_READ roles. To use site-specific roles, execute Oragensec after providing the command line options and specifying the specific roles.
OraGenSec Java Usage
java OraGenSec [-a <arg>] [-d <arg>] [-f <arg>] [-h] [-l <arg>] [-o <arg>] [-q] [-r <arg>] [-u <arg>]
OraGenSec Help
• -a <arg>: generate security for all objects in the database
• -d <arg>: DB connection string as: $DB_USER,$DB_PWD,$DB_CONNECTION_STRING,$TARGET_SCHEMA
• -f <arg>: generate security for specific objects from an input File
• -h: Print Help
• -l <arg>: Name of log file (optional)
• -o <arg>: Generate security for comma separated list of objects
• -p <arg>: Corresponding passwords of users to create synonyms for
• -q: Quiet mode
• -r <arg>: roles corresponding to the users
• -u <arg>: Read Write user, Read Only user
To run the utility:
1. Set JAVA_HOME, PATH, and CLASSPATH.
UNIX:
export JAVA_HOME=/scratch/software/jdk1.8.0_102/
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=/../FW/jarfiles/*
WINDOWS:
SET JAVA_HOME=C:\Program Files\Java\jdk1.8.0_101
SET PATH=%JAVA_HOME%\bin:%PATH%
SET CLASSPATH= C:\..\FW\jarfiles\*
2. From any directory you can execute Oragensec as long as Step 1 is done. Run the following command:
UNIX:
java -Xmx1500M com.oracle.ouaf.oem.install.OraGenSec-d <DBUSER>,<DBPASS>, jdbc:oracle:thin:@<DB_SERVER>:<PORT>/
<SID> -u <RW_USER>,<R_USER> -r <RW_USER_ROLE>,<R_USER_ROLE> -a A -p <RW_USERPASS>,<R_USERPASS>
WINDOWS:
"%JAVA_HOME%"\bin\java -Xmx1500M com.oracle.ouaf.oem.install.OraGenSec -d <DBUSER>,<DBPASS>, jdbc:oracle:thin:@<DB_SERVER>:<PORT>/
<SID> -u <RW_USER>,<R_USER> -r <RW_USER_ROLE>,<R_USER_ROLE> -a A -p <RW_USERPASS>,<R_USERPASS>