Upgrading the Schema
The following section assumes that you have Oracle Utilities Work and Asset Management V2.3.0.0.0 or V2.4.0.0.0 installed.
Note: You must have Oracle Database Server 19c installed on your server to continue with the procedure.
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 online 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.1 upgrade.
Upgrading the Oracle Utilities Application Framework Database Component using OraDBI.jar
Prepare the following parameters before installation:
• The name of the database server in which the database is configured - DB_SERVER
• The listener port number of the database - PORT
• The target database name in which the product is to be installed - SID
• A database user that will own the application schema (for example: CISADM) - DBUSER
• Password of the database user that will own the application schema - DBPASS
• A database user that has read-write (select/update/insert/delete) privileges to the objects in the application schema (for example: CISUSER). The application will access the database as this user - RWUSER
• A database user with read-only privileges to the objects in the application schema. (for example: CISREAD) - RUSER
• A database role that has read-write (select/update/insert/delete) privileges to the objects in the application schema. (for example: CIS_USER) - RW_USER_ROLE
• A database role with read-only privileges to the objects in the application schema (for example: CIS_READ) - R_USER_ROLE
• Location for jar files (the Jar files are bundled with the database package) - CLASSPATH
• The database user password with read-write privileges - RWUSER_PASS
• The database user password with read-only privileges - RUSER_PASS
Granting Privileges to the Database Roles
Make sure to provide the CREATE SYNONYM grants to the database role with read-write and read-only privileges.
GRANT CREATE SYNONYM TO CIS_USER;
GRANT CREATE SYNONYM TO CIS_READ;
Enabling the DBMS_CRYPTO Package
Before installing Oracle Utilities Application Framework V4.5.0.1.1 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;
You can execute OraDBI.jar using either of the following methods:
Using Interactive Mode to Install the Oracle Utilities Application Framework Database Component using OraDBI.jar
The following procedure lists the steps to install the schema for Oracle Utilities Application Framework V4.5.0.1.1 using OraDBI.
Run the following commands with the defined parameters on the command prompt from the Install-Upgrade folder where the Oracle Utilities Application Framework 4.5.0.1.1 - Database Installation Media was unzipped.
1. Open a command line prompt in the Windows environment.
2. Set Java Home.
In the following example, JDK 1.8 is installed in C:\Program Files\Java\jdk1.8.0_131 directory.
SET JAVA_HOME=C:\Program Files\Java\jdk1.8.0_131
3. Set the class path.
In the following example, the required jarfiles, including OraDBI.jar, are available in the C:\InstallUpgrade\Jarfiles\* directory.
SET CLASSPATH=C:\InstallUpgrade\Jarfiles\*
4. Run the following command.
"%JAVA_HOME%"\bin\java -Xmx1500M -cp %CLASSPATH% com.oracle.ouaf.oem.install.OraDBI
-p <RWUSER_PASS>,<RUSER_PASS>
(or)
"C:\Program Files\Java\jdk1.8.0_101"\bin\java -Xmx1500M -cp C:\InstallUpgrade\Jarfiles\* com.oracle.ouaf.oem.install.OraDBI -p <RWUSER_PASS>,<RUSER_PASS>
The utility prompts you to enter values for the following parameters as per your environment:
• Name of the database server: <DB SERVER>
• Name of the target database: <SID>
• Name of the owner of the database schema: <DBUSER>
• Password of the user name: <DBPASS>
• Oracle user with read-write privileges to the Database Schema: <CISUSER>
• Password of the user with read-write privileges: <DBPASS>
• Oracle user with read-only privileges to the Database Schema: <CISREAD>
• Password of the user with read-only privileges: <DBPASS>
• Oracle database role with read-write privileges to the Database Schema: <CIS_USER>
• Oracle database role with read-only privileges to the Database Schema: <CIS_READ>
• Enter the name of the target schema where you want to install or upgrade: <CISADM>
This process generates log files in the directory Install-Upgrade\logs. Check the log files for any errors. Refer to Installation Notes for more information.
Note: You can also run the OUAF install process on a Linux/UNIX server which has Java installed by exporting the JAVA HOME and CLASSPATH.
Example:
export JAVA_HOME=/scratch/spl/jdk1.8.0_92
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=/scratch/Candidate/FW-V4.5.0.1.1-Database/jarfiles/*
In interactive mode, you can run the installer using the command:
java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -p <RWUSER_PASS>,<RUSER_PASS>
Using the Command Line to Install the Oracle Utilities Application Framework Database Component using OraDBI.jar
Run the following commands with the defined parameters on the command prompt from the Install-Upgrade folder where the Oracle Utilities Application Framework V4.5.0.1.1 - Database Installation Media was unzipped.
"C:\Program Files\Java\jdk1.8.0_65\bin\java" -Xmx1500M -cp /
scratch/Candidate/FW-V4.5.0.1.1-Database/jarfiles/spl/*
com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<DatabaseServer_Name>:1521/<DatabaseName>,
%DBUSER%,%DBPASS%, CISUSER, CISREAD, CIS_USER, CIS_READ, %DBUSER% - l 1,2 -p
<RWUSER_PASS>,<RUSER_PASS>
OR
"%JAVA_HOME%"\bin\java -Xmx1500M -cp %CLASSPATH% com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<Database ServerName>:1521/<Database_Name>,%DBUSER%,%DBPASS%,CISUSER,CISREAD,CIS_USER,CIS_READ,%DBUSER% -l 1,2 -p <RWUSER_PASS>,<RUSER_PASS>
This process generates log files in the directory Install-Upgrade\logs. Check the log files for any errors. Refer to Installation Notes for more information.
You can also run the process on a Linux/UNIX server that has Java installed by exporting the JAVA HOME and CLASSPATH.
Note: The following error messages can be ignored during process:
- 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 Oracle Utilities Work and Asset Management, you must install Oracle Utilities Application Framework V4.5.0.1.1 Single Fix Prerequisite Database Rollup for Oracle Utilities Work and Asset Management V2.4.0.1.1.
Important! Confirm if there are Prerequisite Database Single Fixes. Check if there is a WAM-V2.4.0.1.1-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 8 JDK should be installed on the server to use the command. Make sure to install the JDK that is supported for your platform.
1. Copy db_patch_standalone.jar from ..\WAM-V24011-FW-PREREQ-DB\ FW45011-HFix folder to any directory on your local server 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 the prerequisite Oracle Utilities Application Framework database single fixes from ..\ WAM-V24011-FW-PREREQ-DB \FW45011-HFix folder.
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: <DB_Server:DBPORT:ORACLE_SID>
Upgrading Oracle Utilities Work and Asset Management Database Component using OraDBI.jar
Prepare the following parameters before installation:
• The name of the database server in which the database is configured - DB_SERVER
• The listener port number of the database - PORT
• The target database name in which the product is to be installed - SID
• A database user that will own the application schema (for example: CISADM) - DBUSER
• Password of the database user that will own the application schema - DBPASS
• A database user that has read-write (select/update/insert/delete) privileges to the objects in the application schema (for example: CISUSER). The application will access the database as this user - RWUSER
• A database user with read-only privileges to the objects in the application schema. (for example: CISREAD) - RUSER
• A database role that has read-write (select/update/insert/delete) privileges to the objects in the application schema. (for example: CIS_USER) - RW_USER_ROLE
• A database role with read-only privileges to the objects in the application schema. (for example: CIS_READ) - R_USER_ROLE
• Location for jar files. (The Jar files are bundled with the database package) - CLASSPATH
• Java Home (for example: C:\Java\jdk1.8.0) - JAVA_HOME
• The database user password with read-write privileges - RWUSER_PASS
• The database user password with read-only privileges - RUSER_PASS
You can execute OraDBI.jar using either of the following methods:
Using Interactive Mode to Install Using ORADBI.jar
The following procedure lists the steps to install the schema for Oracle Utilities Work and Asset Management V2.4.0.1.1 using OraDBI.
Run the following commands with the defined parameters on the command prompt from ..\WAM\Install-Upgrade directory:
1. Open a command line prompt in the Windows environment.
2. Set Java Home.
In the following example, JDK 1.8 is installed in C:\Program Files\Java\jdk1.8.0_101 directory.
SET JAVA_HOME=C:\Program Files\Java\jdk1.8.0_101
3. Set the class path.
In the following example, the required jarfiles, including OraDBI.jar, are available in the C:\InstallUpgrade\Jarfiles\* directory.
SET CLASSPATH=C:\InstallUpgrade\Jarfiles\*
4. Run the following command.
"%JAVA_HOME%"\bin\java -Xmx1500M -cp %CLASSPATH% com.oracle.ouaf.oem.install.OraDBI -p <RWUSER_PASS>,<RUSER_PASS>
(or)
"C:\Program Files\Java\jdk1.8.0_101"\bin\java -Xmx1500M -cp C:\InstallUpgrade\Jarfiles\* com.oracle.ouaf.oem.install.OraDBI -p <RWUSER_PASS>,<RUSER_PASS>
The utility prompts you to enter values for the following parameters as per your environment:
• Name of the database server: <DB SERVER>
• Name of the target database: <SID>
• Name of the owner of the database schema: <DBUSER>
• Password of the user name: <DBPASS>
• Oracle user with read-write privileges to the Database Schema: <CISUSER>
• Password of the user with read-write privileges: <DBPASS>
• Oracle user with read-only privileges to the Database Schema: <CISREAD>
• Password of the user with read-only privileges: <DBPASS>
• Oracle database role with read-write privileges to the Database Schema: <CIS_USER>
• Oracle database role with read-only privileges to the Database Schema: <CIS_READ>
• Enter the name of the target schema where you want to install or upgrade: <CISADM>
This process generates log files in the directory Install-Upgrade\logs. Check the log files for any errors. Refer to Installation Notes for more information.
You can also run the process on a Linux/UNIX server that has Java installed by exporting the JAVA HOME and CLASSPATH.
In interactive mode, run the installer.
java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -p <RWUSER_PASS>,<RUSER_PASS>
Using the Command Line to Install Using ORADBI.jar
Run the following commands with the defined parameters at the command prompt from the ..\WAM\Install-Upgrade directory.
"C:\Program Files\Java\jdk1.8.0_65\bin\java" -Xmx1500M -cp %CLASSPATH% com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<DatabaseServer_Name>:1521/<DatabaseName>, %DBUSER%,%DBPASS%, CISUSER, CISREAD, CIS_USER, CIS_READ, %DBUSER% -l 1,2 -j “C:\Program Files\Java\jdk1.8.0_65” -p <RWUSER_PASS>,<RUSER_PASS>
OR
"%JAVA_HOME%"\bin\java -Xmx1500M -cp %CLASSPATH% com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<Database ServerName>:1521/<Database_Name>,%DBUSER%,%DBPASS%,CISUSER,CISREAD,CIS_USER,CIS_READ,%DBUSER% -l 1,2 -j "%JAVA_HOME%" -p <RWUSER_PASS>,<RUSER_PASS>
This process generates log files in the directory Install-Upgrade\logs. Check the log files for any errors. Refer to Installation Notes for more information.
This process can also be run on a Linux/UNIX server that has Java installed by exporting the JAVA HOME and CLASSPATH.
Tasks Performed by ORADBI
The following tasks are performed by ORADBI:
• 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, password of the SYSTEM account in the database, 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.
• OraDBI automatically runs the Security Oragensec utility.