Installing the Oracle Database
This section describes how to install the Oracle database for Oracle Utilities Customer Care and Billing 2.9.0.1.1, including:
Database Scripts and Utilities
Follow these steps before you begin installing the database:
Starting V2.6.0.0.0, installation scripts can be run in a Linux or Windows machine. Upgrades from version beyond 2.6.0.0.0 need to set up a Microsoft Windows desktop with the Oracle Client 19c 32-bit installed.
Initial Install (Installing V2.9.0.1.1 for the First Time)
This section describes an initial installation of the V2.9.0.1.1 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 Customer Care and Billing Installation Guide for more information.
Copying and Decompressing Install Media
To copy and decompress the Oracle Utilities Customer Care and Billing database:
1. Download Oracle Utilities Application Framework V4.5.0.1.1 Oracle Database, Oracle Utilities Application Framework V4.5.0.1.1 Single Fix Prerequisite Database Rollup for CCB V2.9.0.1.1 (if there is any), Oracle Utilities Customer Care and Billing V2.9.0.1.1 Oracle Database and Oracle Utilities Customer Care and Billing V2.9.0.1.1 Single Fix Database Rollup MultiPlatform (if there is any) from the Oracle Software Delivery Cloud.
2. Copy FW-V4.5.0.1.1-Oracle-Database-Multiplatform, CCB-V2.9.0.1.1-FW-Database-PREREQ-MultiPlatform (if there is any), CCB-V2.9.0.1.1-Oracle-Database-Multiplatform and CCB-V2.9.0.1.1-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 Customer Care and Billing 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 Customer Care and Billing.
Creating the Database on 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. To store data using CHARACTER length semantics, set NLS_LENGTH_SEMANTICS to CHAR at session level via a logon trigger during installs.
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: 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. 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 4.5.0.1.1 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 V4.5.0.1.1 and Oracle Utilities Application Framework V4.5.0.1.1 Single Fix Prerequisite Database Rollup for CCB V2.9.0.1.1 (if there is any) prior to installing Oracle Utilities Customer Care and Billing V2.9.0.1.1.
The files for Oracle Utilities Application Framework installers are located in the ..\FW-V4.5.0.1.1-Oracle-Database-Multiplatform\FW\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\jdk1.8)
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-4.5.0.1.1.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 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
• 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 V4.5.0.1.1:
1. Unzip FW-V4.5.0.1.1-Oracle-Database-Multiplatform.zip package.
2. Set JAVA_HOME, PATH, and CLASSPATH.
Note: For ADB installation, export/SET TNS_ADMIN=<wallet location>.
UNIX:
export JAVA_HOME=/scratch/software/jdk1.8.0_102/
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=/../FW-V4.5.0.1.1-Oracle- Database-Multiplatform/FW/jarfiles/*
WINDOWS:
SET JAVA_HOME=C:\Program Files\Java\jdk1.8.0_101
SET PATH=%JAVA_HOME%\bin:%PATH%
SET CLASSPATH= C:\ FW-V4.5.0.1.1-Oracle-Database-Multiplatform\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-V4.5.0.1.1-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:
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\jdk1.8.0_101”\bin\java -Xmx1500M -cp C:\ FW-V4.5.0.1.1-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\jdk1.8.0_101”\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 UNIX or Windows machines.
Important: Confirm if there are Prerequisite Database Single Fixes. Check if there is a CCB-V2.9.0.1.1-FW-Database-PREREQ-MultiPlatform folder. If none, skip this step and proceed to apply Oracle Utilities Customer Care and Billing, else continue with the steps below.
Applying Hot Fixes
Note: Java 8 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 ..\CCB-V2.9.0.1.1-FW-Database-PREREQ-MultiPlatform\FW45011-HFix\db_patch_standalone.jar 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 Oracle Utilities Application Framework database single fixes from ..\CCB-V2.9.0.1.1-FW-Database-PREREQ-MultiPlatform\FW45011-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>.
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 Customer Care and Billing Database Component
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.
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 machines that has the following installed:
• Oracle 19c or
• Oracle 19c Client
Before installing the database component, make sure the below prerequisites are met.
• JDK 1.8
• 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 step 1.b.a.c in the
Creating the Database section.
To install the Oracle Utilities Customer Care and Billing V2.9.0.1.1:
1. Unzip CCB-V2.9.0.1.1-Oracle-Database-Multiplatform.zip package.
2. Set JAVA_HOME, PATH, and CLASSPATH.
Note: For ADB installation, export/SET TNS_ADMIN=<wallet location>.
UNIX:
export JAVA_HOME=/scratch/software/jdk1.8.0_102/
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=/../FW-V4.5.0.1.1-Oracle- Database-Multiplatform/FW/jarfiles/*
WINDOWS:
SET JAVA_HOME=C:\Program Files\Java\jdk1.8.0_101
SET PATH=%JAVA_HOME%\bin:%PATH%
SET CLASSPATH= C:\ FW-V4.5.0.1.1-Oracle-Database-Multiplatform\FW\jarfiles\*
3. Run the following command from the command line or command prompt from ..\CCB\Upgrade\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-V4.5.0.1.1-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:
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\jdk1.8.0_101”\bin\java -Xmx1500M -cp C:\ FW-V4.5.0.1.1-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\jdk1.8.0_101”\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 ..\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
The previous versions of Oracle Utilities Customer Care and Billing included cdxpatch.exe programs for applying the database hot fixes. This is not supported from the current version. Starting Oracle Utilities Customer Care and Billing V2.9.0.1.1, use the ouafDatabasePatch.cmd or ouafDatabasePatch.sh. The new tool can be run from UNIX or Windows machines.
Important! Proceed with the steps in this section only if the installation package contains the CCB-V2.9.0.1.1-Database-Rollup-MultiPlatform folder.
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 V29011 Rollup:
2. Apply CCB 29011 Rollup from the ..\ CCB-V2.9.0.1.1-Database-Rollup-MultiPlatform\CCB29011-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>.
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 Customer Care and Billing, including:
Copying and Decompressing Install Media
To copy and decompress the Oracle Utilities Customer Care and Billing database:
1. Download Oracle Utilities Application Framework V4.5.0.1.1 Oracle Database, Oracle Utilities Application Framework V4.5.0.1.1 Single Fix Prerequisite Database Rollup for CCB V2.9.0.1.1 (if there is any), Oracle Utilities Customer Care and Billing V2.9.0.1.1 Oracle Database and Oracle Utilities Customer Care and Billing V2.9.0.1.1 Single Fix Database Rollup MultiPlatform (if there is any) from the Oracle Software Delivery Cloud.
2. Copy FW-V4.5.0.1.1-Oracle-Database-Multiplatform, CCB-V2.9.0.1.1-FW-Database-PREREQ-MultiPlatform (if there is any), CCB-V2.9.0.1.1-Oracle-Database-Multiplatform and CCB-V2.9.0.1.1-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 Customer Care and Billing database.
Migrating from BYTE Based Storage to CHARACTER Based Storage
This section is mandatory for every Oracle Utilities Customer Care and Billing 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.
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 CHARACTER 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 V4.5.0.1.1 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 V4.5.0.1.1 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;
Upgrading the CISADM Schema to V2.9.0.1.1
To upgrade to V2.9.0.1.1 from Oracle Utilities Customer Care and Billing 2.5.0.2 or below, run the following steps before running the blueprint. This is intended for huge table (s) due to changes in column(s) structure from CHAR to VARCHAR2 as part of Oracle Utilities Customer To Meter enhancement.
Below is the list of the affected columns.
Below is a list of some tables that might have a high number of records.
To upgrade the CISADM schema to V2.9.0.1.1:
1. Rename the table(s) and corresponding indexes before running the upgrade.
a. There will be view/s that will become invalid due to change in table name. Reference those view/s to the renamed table. This DML can assist in finding those affected views:
Select owner as schema_name,
name as view_name,
referenced_owner as referenced_schema_name,
referenced_name,
referenced_type
From sys.all_dependencies
Where type= 'VIEW' and owner ='<change to schema owner>' and REFERENCED_NAME = <'table'>;
Note: Change the values assigned to owner and referenced_name variables.
2. Run the blueprint upgrade by referring to the steps provided per version. This creates the replacement for the tables that were renamed which contains the new column structure that is in VARCHAR2(30) except for EXT_TRANSMIT_ID which should be VARCHAR(50).
3. Once upgrade is done for both Oracle Utilities Application Framework and Oracle Utilities Customer Care and Billing, modify the view/s that were updated as part of step 1.a to point to the original table.
4. Copy data from the old table to the new table using INSERT /*+APPEND*/. Trim the data to remove extra spaces and assign a space to those that are empty.
Sample DML to trim data that is not null:
update table <table_name> set UOM_CD =trim(<column_name>) where trim(column_name) is not null;
Sample DML to trim data that is null:
update table <table_name> set UOM_CD =' ' where trim(column_name) is null;
Note: Execute the CCB_TRIM_DATA.sql script from ..\CCB\Upgrade\Install-Upgrade to trim spaces in all affected table.
5. Generate database statistics.
This section assumes an existing Oracle Utilities Customer Care and Billing on top of Oracle Utilities Application Framework. The following upgrade paths are described:
Upgrading from V2.9.0.0.0 to V2.9.0.1.1
You must install the Oracle Utilities Application Framework V4.5.0.1.1 and Oracle Utilities Application Framework V4.5.0.1.1 Database Single Fix Prerequisite Rollup for Oracle Utilities Customer Care and Billing V2.9.0.1.1 (if there's any) prior to Oracle Utilities Customer Care and Billing V2.9.0.1.1. The files for Oracle Utilities Application Framework installer is located in ..\FW-V4.5.0.1.1-Oracle-Database-Multiplatform\FW\Install-Upgrade folder.
Upgrading the Database as Non-Schema Owner
The product allows Non-Schema owners to run the database upgrade.
To perform 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>;
Installing the Oracle Utilities Application Framework Database Component Using OraDBI.java
Installing Prerequisite Database Single Fixes
Installing the Oracle Utilities Customer Care and Billing Database Component
Installing the Oracle Utilities Customer Care and Billing 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.
Upgrading from V2.8.0.0.0 to V2.9.0.1.1
You must install the Oracle Utilities Application Framework V4.5.0.1.1 and Oracle Utilities Application Framework V4.5.0.1.1 Database Single Fix Prerequisite Rollup for Oracle Utilities Customer Care and Billing V2.9.0.1.1 (if there's any) prior to Oracle Utilities Customer Care and Billing V2.9.0.1.1. The files for Oracle Utilities Application Framework installer is located in ..\FW-V4.5.0.1.1-Oracle-Database-Multiplatform\FW\Install-Upgrade folder.
Upgrading the Database as Non-Schema Owner
The product allows Non-Schema owners to run the database upgrade.
To perform 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>;
Installing the Oracle Utilities Application Framework Database Component Using OraDBI.java
Installing Prerequisite Database Single Fixes
Installing the Oracle Utilities Customer Care and Billing Database Component
Installing the Oracle Utilities Customer Care and Billing 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.
Upgrading from V2.7.0.0.0, V2.7.0.1.0 or V2.7.0.3.0 to V2.9.0.1.1
You must install the Oracle Utilities Application Framework V4.5.0.1.1 and Oracle Utilities Application Framework V4.5.0.1.1 Database Single Fix Prerequisite Rollup for Oracle Utilities Customer Care and Billing V2.9.0.1.1 (if there's any) prior to Oracle Utilities Customer Care and Billing V2.9.0.1.1. The files for Oracle Utilities Application Framework installer is located in ..\FW-V4.5.0.1.1-Oracle-Database-Multiplatform\FW\Install-Upgrade folder.
Upgrading the Database as Non-Schema Owner
The product allows Non-Schema owners to run the database upgrade.
To perform 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>;
Installing the Oracle Utilities Application Framework Database Component Using OraDBI.java
Installing Prerequisite Database Single Fixes
Installing the Oracle Utilities Customer Care and Billing Database Component
Installing the Oracle Utilities Customer Care and Billing 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.
Upgrading from V2.6.0.0.0 or V2.6.0.1.0 to V2.9.0.1.1
You must install the Oracle Utilities Application Framework V4.5.0.1.1 and Oracle Utilities Application Framework V4.5.0.1.1 Single Fix Prerequisite Database Rollup for CCB V2.9.0.1.1 (if there’s any) prior to Oracle Utilities Customer Care and Billing V2.9.0.1.1. The files for Oracle Utilities Application Framework installer is located in ..\FW-V4.5.0.1.1-Oracle-Database-Multiplatform\FW\Install-Upgrade folder.
Upgrading the Database as Non-Schema Owner
The product allows Non-Schema owners to run the database upgrade.
To perform 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>;
Installing the Oracle Utilities Application Framework Database Component Using OraDBI.java
Installing Prerequisite Database Single Fixes
Installing the Oracle Utilities Customer Care and Billing Database Component
Installing the Oracle Utilities Customer Care and Billing 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.
Upgrading from V2.5.0, V2.5.0.1, or V2.5.0.2 to V2.9.0.1.1
You must install the Oracle Utilities Application Framework V4.5.0.1.1 and Oracle Utilities Application Framework V4.5.0.1.1 Single Fix Prerequisite Database Rollup for CCB V2.9.0.1.1 (if there's any) prior to Oracle Utilities Customer Care and Billing 2.9.0.1.1. The files for Oracle Utilities Application Framework installer is located in ..\FW-V4.5.0.1.1-Oracle-Database-Multiplatform\FW\Install-Upgrade folder.
Upgrading the Database as Non-Schema Owner
The product allows non-schema owners to run the database upgrade.
To perform 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>;
Installing the Oracle Utilities Application Framework Database Component Using OraDBI.java
Installing Prerequisite Database Single Fixes
Installing the Oracle Utilities Customer Care and Billing Database Component
Installing the Oracle Utilities Customer Care and Billing 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.
ORADBI Performs the Following Tasks
For more information, refer to
Tasks Performed by ORADBI.
Upgrading from V2.4.0.2 or V2.4.0.3 to V2.9.0.1.1
You must install the Oracle Utilities Application Framework V4.5.0.1.1 and Oracle Utilities Application Framework V4.5.0.1.1 Single Fix Prerequisite Database Rollup for CCB V2.9.0.1.1 (if there's any) prior to Oracle Utilities Customer Care and Billing 2.9.0.1.1. The files for Oracle Utilities Application Framework installer is located in ..\FW-V4.5.0.1.1-Oracle-Database-Multiplatform\FW\Install-Upgrade folder.
Upgrading the Database as Non-Schema Owner
The product allows Non-Schema owners to run the database upgrade.
To perform 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>;
Installing the Oracle Utilities Application Framework Database Component Using OraDBI.java
Installing Prerequisite Database Single Fixes
Installing the Oracle Utilities Customer Care and Billing Database Component
Installing the Oracle Utilities Customer Care and Billing 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.
ORADBI Performs the Following Tasks
For more information, refer to
Tasks Performed by ORADBI.
Upgrading from V2.4.0.0 or V2.4.0.1 to V2.9.0.1.1
This section describes the steps for upgrading Oracle Utilities Customer Care and Billing V2.4.0.0 to V2.9.0.1.1 or from V2.4.0.1 to V2.9.0.1.1.
The files for this upgrade are located in the following directory: ..\CCB-V2.9.0.1.1-Oracle-Database-Multiplatform\CCB\Upgrade\Upgrade-From-v2400-v2401\.
1. Apply Framework version 4.2.0.2 and Oracle Utilities Customer Care and Billing 2.4.0.2 from Step_1_Upgrade_to_v2402 folder:
Note: Make sure to run OraDBI.exe from a Window 32-bit or 64-bit desktop that has the Oracle 19c 32-bit client. The database should already be listed in the local file tnsnames.ora.
a. Apply Framework V4.2.0.2.0 by running ORADBI.exe from the \01_FW420_SP2 folder.
b. Apply Framework V4.2.0.2.0 Rollup by running CDXPATCH.exe from the \02_FW420_SP2_Rollup folder.
c. Execute the CCB2402_Trim_SRCH_CHAR_VAL.sql script from \03_CCB_TRIM_SRCH_VAL folder.
a. Login as CISADM user.
b. On SQL prompt, run CCB2402_Trim_SRCH_CHAR_VAL.sql.
@CCB2402_Trim_SRCH_CHAR_VAL.sql
This generates a file called CCB_TRIM_SRCH_CHAR_VAL.sql.
c. Run the generated CCB_TRIM_SRCH_CHAR_VAL.sql script.
@CCB_TRIM_SRCH_CHAR_VAL.sql
d. Apply Customer Care and Billing 2.4.0 Service Pack 2 by running the ORADBI.exe from the \04_CCB240_SP2 folder.
e. Run the FW4202_Trim_SRCH_CHAR_VAL.sql script from \05_FW_TRIM_SRCH_VAL folder.
a. Login as CISADM user.
b. On SQL prompt, run FW4202_Trim_SRCH_CHAR_VAL.sql.
@FW4202_Trim_SRCH_CHAR_VAL.sql
This will generate a file called TRIM_SRCH_CHAR_VAL.sql.
c. Run the generated TRIM_SRCH_CHAR_VAL.sql script.
@TRIM_SRCH_CHAR_VAL.sql
f. Enable USER_LOCK Package.
For in-bound Web services to work the USER_LOCK must be enabled at the database level. This is a one-time step. If this is not already enabled, please do so using the following steps:
a. Login as SYS user. On SQL prompt run:
@?/rdbms/admin/userlock.sql
b. Grant permission by running the following SQL:
grant execute on USER_LOCK to public;
Note that grant can also be made to the database user which the application connects to only instead of to public. For example: cisuser.
Upgrading from V2.3.1.10 to V2.9.0.1.1
You must install the Oracle Utilities Application Framework V4.5.0.1.1 and Oracle Utilities Application Framework V4.5.0.1.1 Single Fix Prerequisite Database Rollup for CCB V2.9.0.1.1 (if there's any) prior to Oracle Utilities Customer Care and Billing 2.9.0.1.1. The files for Oracle Utilities Application Framework installer is located in ..\FW-V4.5.0.1.1-Oracle-Database-Multiplatform\FW\Install-Upgrade folder.
Note: There is a known issue with CI_BILL and CI_PAY_EVENT tables with high volume of data; hence please follow steps documented in Doc ID 2153482.1 (My Oracle Support), prior to running the upgrade.
Upgrading the Database as Non-Schema Owner
The product allows Non-Schema owners to run the database upgrade.
To perform 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>;
Installing the Oracle Utilities Application Framework Database Component Using OraDBI.java
Optional: Execute CCB2401_BpSchema2.SQL
This step is recommended to improve the performance of the upgrade process.
Before executing this script, verify the script and note that these operations are long-running and the script specifies a default level of parallelism that can be tailored to the implementation's hardware. Also, note that CCB2401_BpSchema2.SQL can be executed well in advance of the upgrade to CCB 2.4.0.2 as these changes are compatible with Oracle Utilities Customer Care and Billing 2.2.0 and 2.3.1:
1. Open a command prompt.
2. Change directory to ..\CCB-V2.9.0.1.1-Oracle-Database-Multiplatform\CCB\Upgrade\Install-Upgrade.
3. Connect to SQLPLUS.
4. Execute the file.
@CCB2401_BpSchema2.SQL
Optional: Execute CCB2401_BpSchema3.SQL
This step is recommended to improve the performance of the upgrade process.
Before executing this script, please verify the script and make a note that these operations are long-running and the script specifies a default level of parallelism that can be tailored to the implementation's hardware.
1. Open a command prompt.
2. Change directory to ..\CCB-V2.9.0.1.1-Oracle-Database-Multiplatform\CCB\Upgrade\Install-Upgrade.
3. Connect to SQLPLUS.
4. Execute the file.
@CCB2401_BpSchema3.SQL
Installing Prerequisite Database Single Fixes
Installing the Oracle Utilities Customer Care and Billing Database Component
Installing the Oracle Utilities Customer Care and Billing Database Rollup
ORADBI Performs the Following Tasks
For more information, refer to
Tasks Performed by ORADBI.
Execute CCB2401_APDATA1.sql
Before executing this script, please verify the script and make a note that these SQLs can be run in chunks across multiple sqlplus sessions in parallel. The execution process below explains how to run the script at once.
1. Open a command prompt.
2. Change directory to ..\CCB-V2.9.0.1.1-Oracle-Database-Multiplatform\CCB\Upgrade\Install-Upgrade.
3. Connect to SQLPLUS.
4. Execute the file.
@CCB2401_APDATA1.sql
Installing the upgrade script to trim the SRCH_CHAR_VAL column on the char tables
1. Login as CISADM user.
2. On SQL prompt, run FW4202_Trim_SRCH_CHAR_VAL.sql from the ..\FW-V4.5.0.1.1-Oracle-Database-Multiplatform\FW\Install-Upgrade directory.
@FW4202_Trim_SRCH_CHAR_VAL.sql
This generates a file called TRIM_SRCH_CHAR_VAL.sql.
3. Run the generated TRIM_SRCH_CHAR_VAL.sql script.
@TRIM_SRCH_CHAR_VAL.sql
Enabling USER_LOCK Package
For In-bound web services to work the USER_LOCK must be enabled at the database level. This is a one-time step. If this is not already enabled please do so using the following steps.
1. Login as SYS user.
2. At the SQL prompt, run the following command:
@?/rdbms/admin/userlock.sql
3. Grant permission by running the below SQL:
grant execute on USER_LOCK to public;
Please note that grant can also be made to the database user which the application connects to only instead of to public. For example: cisuser
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.
Upgrading from V2.2.0.10 to V2.9.0.1.1
This section describes the steps for upgrading Oracle Utilities Customer Care and Billing V2.2.0.10 to V2.9.0.1.1. The files for this upgrade are located in the following directory:
..\CCB-V2.9.0.1.1-Oracle-Database-Multiplatform\CCB\Upgrade\Upgrade-From-v210-v220\From-v220-Upgrade-to-v29011.
Note: Make sure to run OraDBI.exe from a Window 32-bit or 64-bit desktop that has the Oracle 19c 32-bit client. The database should already be listed in the local file tnsnames.ora.
1. Apply Oracle Utilities Application Framework V4.2.0.2 from the \Step_2_Upgrade_to_v2402 folder:
a. Apply Framework version 4.2.0 Service Pack 2 by running OraDBI.exe from the \01_FW420_SP2 folder.
b. Apply Framework version 4.2.0 Service Pack 2 rollup by running CDXPATCH.exe from the \02_FW420_SP2_Rollup folder.
2. Optional: Execute CCB2401_BpSchema2.SQL
3. Optional: Execute CCB2401_BpSchema3.SQL
4. Installing the upgrade script to trim the SRCH_CHAR_VAL column on the char tables. Execute the CCB2402_Trim_SRCH_CHAR_VAL.sql
5. Apply Customer Care and Billing 2.4.0 Service Pack 2 by running the OraDBI.exe from the \04_CCB240_SP2 folder.
6. Run CCB2401_APDATA1.sql.
7. Installing the upgrade script to trim the SRCH_CHAR_VAL column on the char tables. Execute the FW4202_Trim_SRCH_CHAR_VAL.sql.
8. Enable USER_LOCK Package.
Upgrading from V2.2.0.0.0 to V2.9.0.1.1
This section describes the steps for upgrading Oracle Utilities Customer Care and Billing V2.2.0.0.0 to V2.9.0.1.1. The files for this upgrade are located in the following directory:
..\CCB-V2.9.0.1.1-Oracle-Database-Multiplatform\CCB\Upgrade\ Upgrade-From-v210-v220\ From-v220-Upgrade-to-v29011.
Note: For steps 1 and 2, be sure to run OraDBI.exe from a Window 32-bit or 64-bit desktop that has the Oracle 19c 32-bit client. The database should already be listed in the local file tnsnames.ora.
1. Apply Oracle Utilities Application Framework V2.2.0 and Oracle Utilities Customer Care and Billing 2.2.0 Service Packs from the \Step_1_Apply_v220_SP10 folder:
a. Apply Oracle Utilities Application Framework V2.2.0 Service Pack 1 by running CDXDBI.exe from the \01_FW22_SP1 folder.
b. Apply Oracle Utilities Application Framework V2.2.0 Service Pack 18 by running CDXPATCH.exe from the \02_FW_220_SP18 folder.
Note: Use the -q parameter to prevent CDXPATCH tool from prompting to apply the patch for every patch included in the service pack.
c. Apply Oracle Utilities Application Framework V220 Service Pack 18 Rollup by running the CDXPATCH.exe from the \03_FW_220_SP18_Rollup folder.
Note: Use the -q parameter to prevent CDXPATCH tool from prompting to apply the patch for every patch included in the service pack.
d. Apply Oracle Utilities Customer Care and Billing 2.2.0 Service Pack 10 by running the CDXPATCH.exe from the \04_CCB_220_SP10 folder.
2. Apply Oracle Utilities Application Framework V4.2.0 Service Pack 2 and Oracle Utilities Customer Care and Billing 2.4.0 Service Packs 2 from the \Step_2_Upgrade_to_v2402 folder:
a. Apply Oracle Utilities Application Framework V4.2.0 Service Pack 2 by running OraDBI.exe from the \01_FW420_SP2 folder.
b. Apply Framework version 4.2.0 Service Pack 2 rollup by running CDXPATCH.exe from the \02_FW420_SP2_Rollup folder.
c. Optional: Execute CCB2401_BpSchema2.SQL
d. Optional: Run CCB2401_BpSchema3.SQL
e. Installing the upgrade script to trim the SRCH_CHAR_VAL column on the char tables.
f. Run CCB2402_Trim_SRCH_CHAR_VAL.sql.
g. Apply Oracle Utilities Customer Care and Billing 2.4.0 Service Pack 2 by running the OraDBI.exe from the \04_CCB240_SP2 folder.
h. Run CCB2401_APDATA1.sql.
i. Installing the upgrade script to trim the SRCH_CHAR_VAL column on the char tables.
Run the FW4202_Trim_SRCH_CHAR_VAL.sql.
j. Enable USER_LOCK Package.
Upgrading from V2.1.0.0.0 to 2.9.0.1.1
This section describes the steps for upgrading Oracle Utilities Customer Care and Billing V2.1.0.0.0 to V2.9.0.1.1. The files for this upgrade are located in the following directory: ..\CCB-V2.9.0.1.1-Oracle-Database-Multiplatform\CCB\Upgrade\Upgrade-From-v210-v220\From-v210-Upgrade-to-v29011.
Note: For steps 1-4, be sure to run OraDBI.exe from a Window 32-bit or 64-bit desktop that has the Oracle 19c 32-bit client. The database should already be listed in the local file tnsnames.ora
1. Apply the Oracle Utilities Application Framework 2.1.0 and Oracle Utilities Customer Care and Billing 2.1.0 current rollups from the \ Step_1_Apply_210_Current_Rollup folder:
a. Apply the Oracle Utilities Application Framework V2.1.0 current rollup by running CDXPATCH.exe from the \01_FW210SP7_plus_Rollup folder.
b. Apply the Oracle Utilities Customer Care and Billing V2.1.0 current rollup by running CDXPATCH.exe from the \02_CCB210SP7_plus_Rollup folder.
2. Upgrade to Oracle Utilities Application Framework V2.2.0 and Oracle Utilities Customer Care and Billing V2.2.0 by running CDXDBI.exe from the \Step_2_Upgrade_to_v220\Upgrade-Install folder.
3. Apply Oracle Utilities Application Framework V2.2.0 and Oracle Utilities Customer Care and Billing 2.2.0 Service Packs from the \Step_3_Apply_v220_SP10 folder.
a. Apply Oracle Utilities Application Framework version 2.2.0 Service Pack 1 by running CDXDBI.exe from the \01_FW22_SP1 folder.
b. Apply Framework version 2.2.0 Service Pack 18 by running CDXPATCH.exe from the \02_FW_220_SP18 folder.
Note: Use the -q parameter to prevent CDXPATCH tool from prompting to apply the patch for every patch included in the service pack.
c. Apply Framework version 220 Service Pack 18 Rollup by running the CDXPATCH.exe from the \03_FW_220_SP18_Rollup folder.
Note: Use the -q parameter to prevent CDXPATCH tool from prompting to apply the patch for every patch included in the service pack.
d. Apply Customer Care and Billing 2.2.0 Service Pack 10 by running the CDXPATCH.exe from the \04_CCB_220_SP10 folder.
4. Apply Framework version 4.2.0 Service Pack 2 and Customer Care and Billing 2.4.0 Service Packs 2 from the \Step_4_Upgrade_to_v2402 folder:
a. Apply Framework version 4.2.0 Service Pack 2 by running OraDBI.exe from the \01_FW420_SP2 folder.
b. Apply Framework version 4.2.0 Service Pack 2 rollup by running CDXPATCH.exe from the \02_FW420_SP2_Rollup folder.
c. Optional: Execute CCB2401_BpSchema2.SQL.
d. Optional: Execute CCB2401_BpSchema3.SQL.
e. Installing the upgrade script to trim the SRCH_CHAR_VAL column on the char tables.
Execute CCB2402_Trim_SRCH_CHAR_VAL.sql.
f. Apply Customer Care and Billing 2.4.0 Service Pack 2 by running the OraDBI.exe from the \04_CCB240_SP2 folder.
g. Execute CCB2401_APDATA1.sql.
h. Installing the upgrade script to trim the SRCH_CHAR_VAL column on the char tables.
Execute the FW4202_Trim_SRCH_CHAR_VAL.sql.
i. Enable USER_LOCK Package
Upgrading from V2.0.5.0.0 to 2.9.0.1.1
This section describes the steps for upgrading Oracle Utilities Customer Care and Billing V2.0.5.0.0 to V2.9.0.1.1. The files for this upgrade are located in the following directory:
..\CCB-V2.9.0.1.1-Oracle-Database-Multiplatform\CCB\Upgrade\Upgrade-From-v205\
Note: For steps 1-5, be sure to run OraDBI.exe from a Window 32-bit or 64-bit desktop that has the Oracle 19c 32-bit client. The database should already be listed in the local file tnsnames.ora.
1. Upgrade to Customer Care and Billing 2.1.0 by running CDXDBI.exe from \Step_1_Upgrade_to_v210\Upgrade-Install folder.
2. Apply the Framework 2.1.0 and Customer Care and Billing 2.1.0 current rollups from the \ Step_2_Apply_210_Current_Rollup folder:
a. Apply the Framework V2.1.0 current rollup by running CDXPATCH.exe from the \01_FW210SP7_plus_Rollup folder.
b. Apply the Customer Care and Billing V2.1.0 current rollup by running CDXPATCH.exe from the \02_CCB210SP7_plus_Rollup folder.
3. Upgrade to Framework V2.2.0 and Customer Care and Billing V2.2.0 by running CDXDBI.exe from the \Step_3_Upgrade_to_v220\Upgrade-Install folder.
4. Apply Framework V2.2.0 and Customer Care and Billing 2.2.0 Service Packs from the \Step_4_Apply_v220_SP10 folder:
a. Apply Framework V2.2.0 Service Pack 1 by running CDXDBI.exe from the \01_FW22_SP1 folder.
b. Apply Framework V2.2.0 Service Pack 18 by running CDXPATCH.exe from the \02_FW_220_SP18 folder.
Note: Use the -q parameter to prevent CDXPATCH tool from prompting to apply the patch for every patch included in the service pack.
c. Apply Framework version 220 Service Pack 18 Rollup by running the CDXPATCH.exe from the \03_FW_220_SP18_Rollup folder.
Note: Use the -q parameter to prevent CDXPATCH tool from prompting if you would like to apply the patch for every patch included in the service pack.
d. Apply Customer Care and Billing 2.2.0 Service Pack 10 by running the CDXPATCH.exe from the \04_CCB_220_SP10 folder.
5. Apply Framework version 4.2.0 Service Pack 2 and Customer Care and Billing 2.4.0 Service Pack 2 from the \Step_5_Upgrade_to_v2402 folder:
a. Apply Framework version 4.2.0 Service Pack 2 by running OraDBI.exe from the \01_FW420_SP2 folder.
b. Apply Framework version 4.2.0 Service Pack 2 rollup by running CDXPATCH.exe from the \02_FW420_SP2_Rollup folder.
c. Optional: Execute CCB2401_BpSchema2.SQL
d. Optional: Execute CCB2401_BpSchema3.SQL
e. Installing the upgrade script to trim the SRCH_CHAR_VAL column on the char tables.
Execute the CCB2402_Trim_SRCH_CHAR_VAL.sql
f. Apply Customer Care and Billing 2.4.0 Service Pack 2 by running the OraDBI.exe from the \04_CCB240_SP2 folder.
g. Execute CCB2401_APDATA1.sql
h. Installing the upgrade script to trim the SRCH_CHAR_VAL column on the char tables.
Execute the FW4202_Trim_SRCH_CHAR_VAL.sql
i. Enable USER_LOCK Package
Upgrading from V1.5.20 to V2.9.0.1.1
This section describes the steps for upgrading Oracle Utilities Customer Care and Billing V1.5.20 to V2.9.0.1.1. The files for this upgrade are located in the following directory:
..\CCB-V2.9.0.1.1-Oracle-Database-Multiplatform\CCB\Upgrade\Upgrade-From-v1.5.10-v1.5.15\
Note: For steps 1-7, be sure to run OraDBI.exe from a Window 32-bit or 64-bit desktop that has the Oracle 19c 32-bit client. The database should already be listed in the local file tnsnames.ora.
1. Apply Customer Care and Billing 1.5.20 Service Pack 1 by running CDXPATCH.exe from \Step_2_Apply_ServicePack_v15201 folder.
2. Upgrade to Customer Care and Billing 2.0.5 by executing the following steps from \Step_3_Upgrade_to_v205\Upgrade-Install folder.
a. Pre-Install: Run CDXDBI.exe from \01_Pre-Install Folder. During this process, Owner Flag information will be upgraded from CI to C1 or F1 on system data.
b. Install: Run CDXDBI.exe from \02_Install Folder. This process will complete upgrade of rest of the system data to CCB V2.0.5.
3. Upgrade to Customer Care and Billing 2.1.0 by running CDXDBI.exe from \Step_4_Upgrade_to_v210\Upgrade-Install folder.
4. Apply the Framework 2.1.0 and Customer Care and Billing 2.1.0 current rollups from the \ Step_5_Apply_210_Current_Rollup folder:
a. Apply the Framework version 2.1.0 current rollup by running CDXPATCH.exe from the \01_FW210SP7_plus_Rollup folder.
b. Apply the Customer Care and Billing version 2.1.0 current rollup by running CDXPATCH.exe from the \02_CCB210SP7_plus_Rollup folder.
5. Upgrade to Framework version 2.2.0 and Customer Care and Billing version 2.2.0 by running CDXDBI.exe from the \Step_6_Upgrade_to_v220\Upgrade-Install folder.
6. Apply Framework version 2.2.0 and Customer Care and Billing 2.2.0 Service Packs from the \Step_7_Apply_v220_SP10 folder:
a. Apply Framework version 2.2.0 Service Pack 1 by running CDXDBI.exe from the \01_FW22_SP1 folder.
b. Apply Framework version 2.2.0 Service Pack 18 by running CDXPATCH.exe from the \02_FW_220_SP18 folder.
Note: Use the -q parameter to prevent CDXPATCH tool from prompting to apply the patch for every patch included in the service pack.
c. Apply Framework version 220 Service Pack 18 Rollup by running the CDXPATCH.exe from the \03_FW_220_SP18_Rollup folder.
Note: Use the -q parameter to prevent CDXPATCH tool from prompting to apply the patch for every patch included in the service pack.
d. Apply Customer Care and Billing 2.2.0 Service Pack 10 by running the CDXPATCH.exe from the \04_CCB_220_SP10 folder.
7. Apply Framework version 4.2.0 Service Pack 2 and Customer Care and Billing 2.4.0 Service Packs 2 from the \Step_8_Upgrade_to_v2402 folder:
a. Apply Framework version 4.2.0 Service Pack 2 by running OraDBI.exe from the \01_FW420_SP2 folder.
b. Apply Framework version 4.2.0 Service Pack 2 rollup by running CDXPATCH.exe from the \02_FW420_SP2_Rollup folder.
c. Optional: Execute CCB2401_BpSchema2.SQL
d. Optional: Execute CCB2401_BpSchema3.SQL
e. Installing the upgrade script to trim the SRCH_CHAR_VAL column on the char tables.
Execute the CCB2402_Trim_SRCH_CHAR_VAL.sql
f. Apply Customer Care and Billing 2.4.0 Service Pack 2 by running the OraDBI.exe from the \04_CCB240_SP2 folder.
g. Execute CCB2401_APDATA1.sql
h. Installing the upgrade script to trim the SRCH_CHAR_VAL column on the char tables.
Execute the FW4202_Trim_SRCH_CHAR_VAL.sql
i. Enable USER_LOCK Package
Upgrading from V1.5.10 or V1.5.15 to V2.9.0.1.1
This section describes the steps for upgrading Oracle Utilities Customer Care and Billing V1.5.10 or V1.5.15 to V2.9.0.1.1. The files for this upgrade are located in the following directory:
..\CCB-V2.9.0.1.1-Oracle-Database-Multiplatform\CCB\Upgrade\Upgrade-From-v1.5.10-v1.5.15\
Note: For steps 1-8, be sure to run OraDBI.exe from a Window 32-bit or 64-bit desktop that has the Oracle 19c 32-bit client. The database should already be listed in the local file tnsnames.ora.
1. Upgrade to Customer Care and Billing 1.5.20 by executing the steps below from \Step_1_Upgrade_to_v1520\Upgrade-Install folder.
a. Pre-Install Steps
The clean-up scripts for each task consist of a “select” SQL and a “delete” SQL script. The “select” SQL script when executed will display the data that will be deleted by the “delete” SQL script. All the clean-up scripts spool their results in output files that have same names as the scripts but with an “.out” extension.
To execute these scripts, users must log in as a database user with delete privileges on the CC&B schema using SQLPLUS.
1. Open a command prompt.
2. Change directory to ..\01_Pre-Install Folder.
3. Connect to SQLPLUS.
4. Execute the file as follows:
@ delete_mdfl.sql.sql
@ delete_tddrl.sql
@ delete_tdsrt.sql
@ delete_tde.sql
Users must “commit” the data cleanup transaction explicitly and roll it back if the script fails for some reason.
The following scripts are included in the \01_Pre-Install folder:
• select_mdfl.sql and delete_mdfl.sql
• select_tddrl.sql and delete_tddrl.sql
• select_tdsrt.sql and delete_tdsrt.sql
• select_tde.sql and delete_tde.sql
After completion of this follow the Install steps below.
b. Install Steps
Upgrade to Customer Care and Billing version 1.5.20 by running CDXDBI.exe from \02_Install Folder.
c. Post-Install Steps
The following steps are included in the post-install process:
Sequence synchronization
In release 1.4.5, two new sequences CI_MRSTGUPID_SEQ, CI_NTUPID_SEQ were added to ID the primary key columns of CI_MR_STAGE_UP and CI_NT_UP tables. Because these tables existed in previous versions of CC&B, we must adjust the "last number" value of the new sequences to either an existing sequence that you have already been using for the same purpose or the maximum value of the primary key column of the table(s). If you have already set these sequences in Release 1.4.5 or later, skip this step and continue from the next step (if any).
Following are the steps involved in the adjust sequences process:
1. Execute the AdjustSequences.bat utility file under \03_Post-Install folder, by double-clicking it or running it from command line. The utility prompts you to enter values for following parameters:
Enter the username that owns the CC&B schema (e.g. CISADM):
Enter the password for the CC&B schema owner:
Enter the name of the Oracle Database:
2. The utility connects to the database and prompts you to continue the processing.
3. The utility checks for the two new sequences in the database and for each sequence, prompts you to enter the name of an existing sequence that you have already been using to ID the primary key column of its corresponding table. You can press Enter if you are not using any existing sequence (the utility sets its value to the maximum value of the primary key in that case).
If you choose to adjust a new sequence to an existing sequence, the utility sets the new sequence to the current “last number” value of the existing sequence, drops the existing sequence, and creates a synonym for the dropped sequence. This way the existing sequence is replaced with a CCB sequence without breaking any existing code that may be referring to the existing sequence.
4. After sequences are adjusted, the utility reconfigures the security in the database and prompts you to enter the values for following parameters:
Enter the Oracle user that owns the schema (e.g. CISADM):
Enter the password for the CC&B schema:
Enter a comma-separated list of Oracle users in which synonyms need to be created (e.g. cisuser,cisread):
Enter the name of the Oracle Database:
This completes the adjust sequences process. The process generates a log file (AdjustSequences.log). You can review the log for the actions performed and any errors that may have occurred during the process.
2. Apply Customer Care and Billing 1.5.20 Service Pack 1 by running CDXPATCH.exe from \Step_2_Apply_ServicePack_v15201 folder.
3. Upgrade to Customer Care and Billing 2.0.5 by executing the following steps from \Step_3_Upgrade_to_v205\Upgrade-Install folder.
a. Pre-Install: Run CDXDBI.exe from \01_Pre-Install Folder. During this process, Owner Flag information will be upgraded from CI to C1 or F1 on system data.
b. Install: Run CDXDBI.exe from \02_Install Folder. This process will complete upgrade of rest of the system data to CCB V2.0.5.
4. Upgrade to Customer Care and Billing 2.1.0 by running CDXDBI.exe from \Step_4_Upgrade_to_v210\Upgrade-Install folder.
5. Apply the Framework 2.1.0 and Customer Care and Billing 2.1.0 current rollups from the \ Step_5_Apply_210_Current_Rollup folder:
a. Apply the Framework version 2.1.0 current rollup by running CDXPATCH.exe from the \01_FW210SP7_plus_Rollup folder.
b. Apply the Customer Care and Billing version 2.1.0 current rollup by running CDXPATCH.exe from the \02_CCB210SP7_plus_Rollup folder.
6. Upgrade to Framework version 2.2.0 and Customer Care and Billing version 2.2.0 by running CDXDBI.exe from the \Step_6_Upgrade_to_v220\Upgrade-Install folder.
7. Apply Framework version 2.2.0 and Customer Care and Billing 2.2.0 Service Packs from the \Step_7_Apply_v220_SP10 folder:
a. Apply Framework version 2.2.0 Service Pack 1 by running CDXDBI.exe from the \01_FW22_SP1 folder.
b. Apply Framework version 2.2.0 Service Pack 18 by running CDXPATCH.exe from the \02_FW_220_SP18 folder.
Note: Use the -q parameter to prevent CDXPATCH tool from prompting to apply the patch for every patch included in the service pack.
c. Apply Framework version 220 Service Pack 18 Rollup by running the CDXPATCH.exe from the \03_FW_220_SP18_Rollup folder.
Note: Use the -q parameter to prevent CDXPATCH tool from prompting to apply the patch for every patch included in the service pack.
d. Apply Customer Care and Billing 2.2.0 Service Pack 10 by running the CDXPATCH.exe from the \04_CCB_220_SP10 folder.
8. Apply Framework version 4.2.0 Service Pack 2 and Customer Care and Billing 2.4.0 Service Packs 2 from the \Step_8_Upgrade_to_v2402 folder:
a. Apply Framework version 4.2.0 Service Pack 2 by running OraDBI.exe from the \01_FW420_SP2 folder.
b. Apply Framework version 4.2.0 Service Pack 2 rollup by running CDXPATCH.exe from the \02_FW420_SP2_Rollup folder.
c. Optional: Execute CCB2401_BpSchema2.SQL
d. Optional: Execute CCB2401_BpSchema3.SQL
e. Installing the upgrade script to trim the SRCH_CHAR_VAL column on the char tables.
Run the CCB2402_Trim_SRCH_CHAR_VAL.sql.
f. Apply Customer Care and Billing 2.4.0 Service Pack 2 by running the OraDBI.exe from the \04_CCB240_SP2 folder.
g. Run CCB2401_APDATA1.sql.
h. Installing the upgrade script to trim the SRCH_CHAR_VAL column on the char tables.
Execute the FW4202_Trim_SRCH_CHAR_VAL.sql.
i. Enable USER_LOCK Package.
Demo Install
This section describes how to install the demo database components for Oracle Utilities Customer Care and Billing, including:
Copying and Decompressing Install Media
To copy and decompress the Oracle Utilities Customer Care and Billing database:
1. Download Oracle Utilities Application Framework V4.5.0.1.1 Oracle Database, Oracle Utilities Application Framework V4.5.0.1.1 Single Fix Prerequisite Database Rollup for CCB V2.9.0.1.1 (if there's any), Oracle Utilities Customer Care and Billing V2.9.0.1.1 Oracle Database and Oracle Utilities Customer Care and Billing V2.9.0.1.1 Single Fix Database Rollup MultiPlatform (if there is any) from the Oracle Software Delivery Cloud.
2. Copy FW-V4.5.0.1.1-Oracle-Database-Multiplatform, CCB-V2.9.0.1.1-FW-Database-PREREQ-MultiPlatform (if there's any), CCB-V2.9.0.1.1-Oracle-Database-Multiplatform and CCB-V2.9.0.1.1-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 Customer Care and Billing 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 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 BYTES/CHARACTER
Database created by default will store data in BYTES. To store data in CHARACTER follow the procedure below:
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: 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 CHARACTER 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.
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 V4.5.0.1.1 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.
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. Make sure max_string_size is set to EXTENDED.
Note: This step is applicable for on-premises databases only.
4. Import the demo dump.
Note: Make sure the ..\DAM-V2.0.0.1.3-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
5. 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 ..\FW-V4.5.0.1.1-Oracle-Database-Multiplatform\FW\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.
UNIX:
export JAVA_HOME=/scratch/software/jdk1.8.0_102/
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=/../FW-V4.5.0.1.1-Oracle-
Database-Multiplatform/FW/jarfiles/*
WINDOWS:
SET JAVA_HOME=C:\Program Files\Java\jdk1.8.0_101
SET PATH=%JAVA_HOME%\bin:%PATH%
SET CLASSPATH= C:\ FW-V4.5.0.1.1-Oracle-Database-Multiplatform\FW\jarfiles\*
2. From any directory, run Oragensec. Make sure that Step 1 is complete. Run the following command:
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>