Installing the Oracle Database
This section describes how to install the Oracle database for Oracle Utilities Work and Asset Management 25.4, including:
Note: The installation tools outlined in this guide run on Linux, Unix, or Windows only. See the Supported Database Platforms section in Database Overview for more information on supported database platforms.
Database Scripts and Utilities
Note that installation scripts can be run in a Linux or Windows server.
Initial Install (Installing V25.4 for the First Time)
This section describes an initial installation of the V25.4 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. See the Oracle Utilities Work and Asset Management Installation Guide for more information.
Copying and Decompressing Install Media
To copy and decompress the database:
1. Download Oracle Utilities Application Framework V25.4-Database Installation Media, Oracle Utilities Application Framework V25.4 Single Fix Prerequisite Database Rollup for WAM V25.4 (if there is any), Oracle Utilities Work and Asset Management V25.4 Oracle Database Multiplatform, and Oracle Utilities Work and Asset Management V25.4 Single Fix Database Rollup MultiPlatform (if there is any) from the Oracle Software Delivery Cloud.
2. Copy Oracle Utilities Application Framework V25.4 Database Installation Media, WAM-V25.4-FW-Database- PREREQ-MultiPlatform (if there is any), Oracle Utilities Work and Asset Management V25.4 Oracle Database Multiplatform, and WAM-V25.4-Database-Rollup-MultiPlatform (if there is any) directories to your local machine.
These files contain all the database components required to install the Oracle Utilities Application Framework and Oracle Utilities Work and Asset Management database.
Database Creation
Note: You must have Oracle Database Server installed on your machine in order to create the database. This step is not required if you are performing a database upgrade from a previous version of Oracle Utilities Work and Asset Management.
Creating the Database on Linux/UNIX
Create the database using the Database Configuration Assistant (DBCA).
Refer to the Creating the Database section 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 Creating the Database section 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 CHAR 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.1 on My Oracle Support.
• Alternative procedure: Refer to the Initial Install section for steps to create a schema with char length semantics.
Initial Install
1. Create the database using DBCA. Refer to the Creating the Database section for steps to create the database.
2. Run the following statement to 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.
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
Note: This step is applicable only for the on-premises databases. By default, ADB is set to EXTENDED.
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 upgrade.
Enabling DBMS_CRYPTO package
Before installing Oracle Utilities Application Framework 25.4 make sure to provide execute privilege on dbms_crypto package to CISADM user. Execute the following command as SYS for on-premises and ADMIN for ADB.
grant execute on dbms_crypto to CISADM;
Installing the CISADM Schema
You should install Oracle Utilities Application Framework V25.4 and Oracle Utilities Application Framework V25.4 Single Fix Prerequisite Database Rollup for WAM V25.4 (if there is any) prior to installing Oracle Utilities Work and Asset Management V25.4.
The files for Oracle Utilities Application Framework installers are located in the Oracle Utilities Application Framework V25.4 Database Installation Media Install-Upgrade folder.
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\jdk)
This section focuses on the following:
Installing the Oracle Utilities Application Framework Database Component
Note: 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 latest functionality/features introduced in spl-oradbi-25.4.jar is delivered in directory jarfiles.
This section includes the instructions to install the database component.
Installing the Oracle Utilities Application Framework Database Component Using OraDBI.java
OraDBI.java is a new tool to install and upgrade database components. It can be run from Linux, UNIX, or Windows machines that has the following installed:
• Oracle 19c
• Oracle 19c Client
Before installing the database component, make sure the following prerequisites are met:
• JDK 17
• Oracle Database
• Schema (such as CISADM) should exist in the database
• max_string_size is set to EXTENDED
• For ADB, the database connection should be working. For more information, seee step 1.b.a.c in the Creating the Database section.
To install the Oracle Utilities Application Framework V25.4:
1. Unzip the Oracle Utilities Application Framework V25.4 Database Installation Media package.
2. Set JAVA_HOME, PATH, and CLASSPATH.
Note: For ADB installation, export/SET TNS_ADMIN=<wallet location>.
Linux/UNIX:
export JAVA_HOME=/scratch/software/jdk
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=/../FW-V25.4-Oracle- Database-Multiplatform/FW/jarfiles/*
 
WINDOWS:
SET JAVA_HOME=C:\Program Files\Java\jdk
SET PATH=%JAVA_HOME%\bin:%PATH%
SET CLASSPATH= C:\FW-V25.4-Oracle-Database-Multiplatform\FW\jarfiles\*
 
3. Run the following command from the command line or command prompt from the Oracle Utilities Application Framework V25.4 Database Installation Media Install-Upgrade folder.
There are two options available to execute OraDBI.java:
• Using interactive mode
• Using command on command line
Using Interactive Mode:
Linux/UNIX:
java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -p <RW_USERPASS>,<R_USERPASS>
 
WINDOWS
“C:\Program Files\Java\jdk”\bin\java -Xmx1500M -cp C:\FW-V25.4-Oracle-Database-Multiplatform\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:
Linux/UNIX:
• On-premises:
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>
 
• ADB:
java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<tnsname>?TNS_ADMIN=<wallet location>,<DBUSER>,<DBPASS>,<RW_USER>,<R_USER>,<RW_USER_ROLE>,<R_US
ER_
ROLE>,<DBUSER> -p <RW_USERPASS>,<R_USERPASS>
 
Windows:
• On-premises:
“C:\Program Files\Java\jdk”\bin\java -Xmx1500M -cp C:\FW-V25.4-Oracle-Database-Multiplatform\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>
 
• ADB:
“C:\Program Files\Java\jdk”\bin\java -Xmx1500M -cp C:\..\FW\jarfiles\* com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<tnsname>?TNS_ADMIN=<wallet location>,<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 ..\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 Framework Prerequisite Database Single Fixes
While prior versions of the product have included the cdxpatch.exe programs for applying DB Hot Fixes, this is no longer supported going forward; the ouafDatabasePatch.cmd or ouafDatabasePatch.sh should be used instead. The new tool can be run from Linux, UNIX, or Windows machines.
Important: Confirm if there are Prerequisite Database Single Fixes. Check if there is a WAM-V25.4-FW-Database-PREREQ-MultiPlatform folder. If none, skip this step and proceed to apply Oracle Utilities Work and Asset Management, else continue with the steps below.
Applying Hot Fixes
Note: Java 17 JDK should be installed on the machine to use the command. Make sure to install the JDK that is supported for your platform.
1. Extract ..\WAM-V25.4-FW-Database-PREREQ-MultiPlatform\FW254-HFix\db_patch_standalone.jar to any directory on your local machine under dbpatch_tools folder.
Linux/UNIX:
cd /../dbpatch_tools
jar xvf db_patch_standalone.jar
 
WINDOWS:
cd c:\..\dbpatch_tools
jar xvf db_patch_standalone.jar
 
2. SET TOOLSBIN.
Linux/UNIX:
export TOOLSBIN=/../dbpatch_tools/bin
WINDOWS:
SET TOOLSBIN=c:\..\dbpatch_tools\bin
 
3. Apply prerequisite Oracle Utilities Application Framework database single fixes from ..\WAM-V25.4-FW-Database-PREREQ-MultiPlatform\FW254-HFix folder.
Note: For ADB, the database connection should be working. For more information, see step 1.b.a.c in the Creating the Database section and export/SET TNS_ADMIN=<wallet location>.
Linux/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:
• On-premises: <DB_Server:DBPORT/ORACLE_SID>
• ADB: @<tnsname>?TNS_ADMIN=<wallet location>
Installing Oracle Utilities Work and Asset Management Database Component
Oracle Utilities Work and 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.
Installing the Oracle Utilities Work and Asset Management 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 machines that has the following installed:
• Oracle 19c
• Oracle 19c Client
Before installing the database component, make sure the below prerequisites are met.
• JDK 17
• Oracle Database
• Schema (such as CISADM) should exist in the database
• max_string_size is set to EXTENDED
• For ADB, the database connection should be working. For more information, see in the Creating the Database section.
To install the Oracle Utilities Work and Asset Management V25.4:
1. Unzip WAM-V25.4-Oracle-Database-Multiplatform.zip package.
2. Set JAVA_HOME, PATH, and CLASSPATH.
Note: For ADB installation, export/SET TNS_ADMIN=<wallet location>.
Linux/UNIX:
export JAVA_HOME=/scratch/software/jdk
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=/../FW-V25.4-Oracle- Database-Multiplatform/FW/jarfiles/*
 
WINDOWS:
SET JAVA_HOME=C:\Program Files\Java\jdkSET PATH=%JAVA_HOME%\bin:%PATH%
SET CLASSPATH= C:\ FW-V25.4-Oracle-Database-Multiplatform\FW\jarfiles\*
 
3. Run the following command from the command line or command prompt from ..\WAM\Upgrade\Install-Upgrade folder.
There are two options available to execute OraDBI.java
• Using interactive mode
• Using command on command line
Using Interactive Mode:
Linux/UNIX:
java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -p <RW_USERPASS>,<R_USERPASS>
 
WINDOWS:
“C:\Program Files\Java\jdk”\bin\java -Xmx1500M -cp C:\ FW-V25.4-Oracle-Database-Multiplatform\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:
Linux/UNIX:
• On-premises:
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>
 
• ADB:
java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<tnsname>?TNS_ADMIN=<wallet location>,<DBUSER>,<DBPASS>,<RW_USER>,<R_USER>,<RW_USER_ROLE>,<R_USER_ROLE>,<DBUSER> -p <RW_USERPASS>,<R_USERPASS>
 
WINDOWS:
• On-premises:
“C:\Program Files\Java\jdk”\bin\java -Xmx1500M -cp C:\FW-V25.4-Oracle-Database-Multiplatform\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>
 
• ADB:
“C:\Program Files\Java\jdk”\bin\java -Xmx1500M -cp C:\..\FW\jarfiles\* com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<tnsname>?TNS_ADMIN=<wallet location>,<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 ..\WAM\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 Work and Asset Management Database Rollup
The previous versions of Oracle Utilities Work and Asset Management included cdxpatch.exe programs for applying the database hot fixes. This is not supported from the current version. Starting Oracle Utilities Work and Asset Management V25.4, use the ouafDatabasePatch.cmd or ouafDatabasePatch.sh. The new tool can be run from Linux, UNIX, or Windows machines.
Important! Proceed with the steps in this section only if the installation package contains the WAM-V25.4-Database-Rollup-MultiPlatform folder.
Make sure Java 17 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 Work and Asset Management V25.4 Rollup:
1. Perform the steps 1 and 2 listed in the Installing Framework Prerequisite Database Single Fixes section.
2. Apply WAM 254 Rollup from the ..\ WAM-V25.4-Database-Rollup-MultiPlatform\WAM254-HFix folder.
Note: For ADB, the database connection should be working. For more information, see step 1.b.a.c in the Creating the Database section. export/SET TNS_ADMIN=<wallet location>.
Linux/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:
• On-premises: <DB_Server:DBPORT/ORACLE_SID>
• ADB: @<tnsname>?TNS_ADMIN=<wallet location>
Tasks Performed by ORADBI
The tasks performed by ORADBI are as follows:
• Interacts with the user to collect information about the name of Oracle account that will own the application schema (example: CISADM), password of this account, and the name of the Oracle account that the application user will use (example: CISUSER), and the name of the Oracle account that will be assigned read-only privileges to the application schema (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
Enabling USER_LOCK Package
For the inbound 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 as follows:
1. Login as SYS user for on-premises and as ADMIN for ADB.
Note: For ADB, the database connection should be working. For more information, see the step 1.b.a.c in the Creating the Database.
2. At the SQL prompt, run the following:
@?/rdbms/admin/userlock.sql
 
3. Grant permission.
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 public. 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.
Upgrade Install
This section describes how to upgrade the database components for Oracle Utilities Work and Asset Management, including:
Copying and Decompressing Install Media
To copy and decompress the Oracle Utilities Work and Asset Management database:
1. Download Oracle Utilities Application Framework V25.4 Database Installation Media, Oracle Utilities Application Framework V25.4 Single Fix Prerequisite Database Rollup for WAM V25.4 (if there is any), Oracle Utilities Work and Asset Management V25.4 Oracle Database Multiplatform, and Oracle Utilities Work and Asset Management V25.4 Single Fix Database Rollup MultiPlatform (if there is any) from the Oracle Software Delivery Cloud.
2. Copy Oracle Utilities Application Framework V25.4 Database Installation Media, WAM-V25.4-FW-Database- PREREQ-MultiPlatform (if there is any), Oracle Utilities Work and Asset Management V25.4 Oracle Database Multiplatform, and WAM-V25.4-Database-Rollup-MultiPlatform (if there is any) directories to your local machine. These files contain all the database components required to install the Oracle Utilities Application Framework and Oracle Utilities Work and Asset Management database.
Migrating from BYTE Based Storage to CHAR Based Storage
This section is mandatory for every Oracle Utilities Work and Asset Management 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_length_semantics=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 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 which was created in step 6).
Run 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.
Note: For ADB, follow the instructions in the Importing the Demo Dump File section.
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 No.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 CHAR length semantics by setting NLS_LENGTH_SEMANTICS to CHAR at session level via a logon trigger during installation as follows:
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
Note: This step is applicable only for the on-premises databases. By default, ADB is set to EXTENDED.
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 upgrade.
Exclude 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: ‘TABLE1_NAME’,’TABLE2_NAME’
 
Example: To exclude the F1_WEB_SVC table, use the following:
-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'
Grant Privilege 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 as SYS for on-premises and as ADMIN for ADB.
grant CREATE SYNONYM to CIS_USER;
grant CREATE SYNONYM to CIS_READ;
Enabling the DBMS_CRYPTO Package
Before installing Oracle Utilities Application Framework V25.4 make sure to provide execute privilege on dbms_crypto package to CISADM user.
Execute the following command as SYS for on-premises and as ADMIN for ADB:
grant execute on dbms_crypto to CISADM;
Enabling DBMS_RLS Package
Before installing Oracle Utilities Application Framework V25.4 make sure to provide execute privilege on dbms_rls package to CISADM user. Execute the following command as SYS for on-premises and as ADMIN for ADB.
grant execute on dbms_rls to CISADM;
 
This upgrade section assumes an existing Oracle Utilities Work and Asset Management on top of Oracle Utilities Application Framework. The following upgrade paths are described:
•  
•  
•  
•  
Upgrading from V2.4.0.1.1 to V25.4
You must install the Oracle Utilities Application Framework V25.4 and Oracle Utilities Application Framework V25.4 Database Single Fix Prerequisite Rollup for Oracle Utilities Work and Asset Management V25.4 (if there is any) prior to Oracle Utilities Work and Asset Management V25.4. The files for the Oracle Utilities Application Framework installer are located in the Oracle Utilities Application Framework V25.4 Database Installation Media Install-Upgrade folder.
Installing the Oracle Utilities Application Framework Database Component Using OraDBI.java
For instructions, refer to Installing the Oracle Utilities Application Framework Database Component Using OraDBI.java.
Installing Prerequisite Database Single Fixes
For instructions, refer to Installing Framework Prerequisite Database Single Fixes.
Installing the Oracle Utilities Work and Asset Database Component
For instructions, refer to Installing Oracle Utilities Work and Asset Management Database Component.
Installing the Oracle Utilities Work and Asset Database Rollup
For instructions, refer to Installing Oracle Utilities Work and Asset Management Database Rollup.
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.
Tasks Performed by ORADBI
For more information, refer to Tasks Performed by ORADBI.
Demo Install
This section describes how to install the demo database components for Oracle Utilities Work and Asset Management, including:
Copying and Decompressing Install Media
To copy and decompress the database:
1. Download Oracle Utilities Application Framework V25.4 Database Installation Media, Oracle Utilities Application Framework V25.4 Single Fix Prerequisite Database Rollup for WAM V25.4 (if there is any), Oracle Utilities Work and Asset Management V25.4 Oracle Database Multiplatform, and Oracle Utilities Work and Asset Management V25.4 Single Fix Database Rollup MultiPlatform (if there is any) from the Oracle Software Delivery Cloud.
2. Copy Oracle Utilities Application Framework V25.4 Database Installation Media, WAM-V25.4-FW-Database-PREREQ-MultiPlatform (if there is any), Oracle Utilities Work and Asset Management V25.4 Oracle Database Multiplatform and WAM-V25.4-Database-Rollup-MultiPlatform (if there is any) directories to your local machine.
These files contain all the database components required to install the Oracle Utilities Application Framework and Work and Asset Management database.
Creating the Database
Note: You must have Oracle Database Server 19c installed on your machine to create the database.
It is strongly recommended to use DBCA to create the database.
Creating the Database on Linux/UNIX
Create the database using the Database Configuration Assistant (DBCA). Refer to the Creating the Database section 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 Creating the Database section for steps to create the database.
Database Storage BYTE/CHAR
Database created by default will store data in BYTE. This setting should be modified to use CHAR length semantics. To store data in CHAR, refer to the instructions below:
Initial Install
1. Create the database using DBCA.
2. 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.
3. Restart the database.
4. Verify that the nls_length_semantics is CHAR using the following command:
SQL> SHOW PARAMETER nls_length_semantics
Upgrade and Migration from BYTE Based Storage to CHAR Based Storage
1. Run the following statement to 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_length_semantics=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.
Run the following command 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.
Note: For ADB, follow the instructions in the Importing the Demo Dump File section.
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
 
13. Enable the triggers.
Use the following command to generate DDL for triggers.
SQL>SELECT ‘ALTER TABLE’ || ‘ ‘ ||TABLE_NAME || ‘ ‘ || ‘ENABLE ALL TRIGGERS;’ FROM USER_TABLES;
 
14. Run the script generated from step No.13 to enable all triggers.
Extended Datatypes
Note: This step is applicable for on-premises databases only. By default, ADB is set to EXTENDED.
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 V25.4 upgrade.
Importing the Demo Dump File
After a successful database creation, import the demo data.
1. For on-premises database:
a. Set the correct ORACLE_SID and ORACLE_HOME.
b. Make sure max_string_size is set to EXTENDED.
Note: This step is applicable for on-premises databases only.
c. Proceed to step 3.
2. For autonomous database (ADB):
a. Make sure the database connection is working. For more information, see step 1.b.a.c in the Creating the Database section.
b. Upload the dumpfile in OCI Storage bucket and obtain the dumpfile URL.
c. Create the cloud credentials in the database.
Note: Execute the script as a ADMIN user. The authentication token can be generated in User Settings in the OCI profile.
SET DEFINE OFF
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => '<credential name>',
username => '<OCI Profile>',
password => '<authentication token>'
);
END;
/
d. Proceed to step 4.
3. Import the demo dump.
Note: Make sure the ..\WAM-V25.4-DemoData\Demo\exp_demo.dmp.gz file is extracted and available in data_pump_dir’s location before running the following import command.
On-premises:
impdp directory= data_pump_dir dumpfile= exp_demo.dmp logfile=exp_demo.log schemas=CISADM
 
ADB:
impdp USERNAME/password@<tns_name> directory=DATA_PUMP_DIR credential=<credential name> dumpfile=<dumpfile url in OCI> logfile=import_demo.log SCHEMAS=CISADM
 
4. After the import is complete, enable the DBMS_CRYPTO and and DBMS_RLS packages. Execute the following command as SYS for on-premises and as ADMIN for ADB.
grant execute on dbms_crypto to CISADM;
grant execute on dbms_rls to CISADM;
Configuring Security
The configuration security utility and scripts are already part of the delivered jarfiles in the Oracle Utilities Application Framework V25.4 Database Installation Media jarfiles directory. It can be executed from a Linux or a Windows machine.
Please 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
Note: Make sure the syntax for $DB_CONNECTION is as below:
• On-premises: jdbc:oracle:thin: :@<DB_SERVER>:<PORT>/<SID>
• ADB: jdbc:oracle:thin:@<tnsname>?TNS_ADMIN=<wallet location>
• -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:
Note: For ADB, export/SET TNS_ADMIN=<wallet location>
1. Set JAVA_HOME, PATH, and CLASSPATH.
Linux/UNIX:
export JAVA_HOME=/scratch/software/jdk
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=/../FW-V25.4-Oracle-
Database-Multiplatform/FW/jarfiles/*
 
WINDOWS:
SET JAVA_HOME=C:\Program Files\Java\jdk
SET PATH=%JAVA_HOME%\bin:%PATH%
SET CLASSPATH= C:\ FW-V25.4-Oracle-Database-Multiplatform\FW\jarfiles\*
 
2. From any directory, run Oragensec. Make sure that Step 1 is complete. Run the following command:
Linux/UNIX
• On-premises:
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>
 
• ADB:
java -Xmx1500M com.oracle.ouaf.oem.install.OraGenSec-d <DBUSER>,<DBPASS>, jdbc:oracle:thin:@<tnsname>?TNS_ADMIN=<wallet location> -u <RW_USER>,<R_USER> -r <RW_USER_ROLE>,<R_USER_ROLE> -a A -p
<RW_USERPASS>,<R_USERPASS>
 
WINDOWS:
• On-premises:
“%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>
 
• ADB:
“%JAVA_HOME%”\bin\java -Xmx1500M com.oracle.ouaf.oem.install.OraGenSec -d <DBUSER>,<DBPASS>, jdbc:oracle:thin:@<tnsname>?TNS_ADMIN=<wallet location> -u <RW_USER>,<R_USER> -r <RW_USER_ROLE>,<R_USER_ROLE> -a A -p
<RW_USERPASS>,<R_USERPASS>