Creating the Database
Make sure you have Oracle Database Server installed on your server to create the database.
Creating the Database on Linux/UNIX
Create the database using the Database Configuration Assistant (DBCA).
Make sure to set for database as follows:
• Character set for database as AL32UTF8
• National Character Set (NLS_NCHAR_CHARACTERSET - AL16UTF16)
Refer to Creating the Database for steps to create the database.
Creating the Database on Windows
You should be logged in as a user who is a member of the local ORA_DBA group on that server. The ORA_DBA group should have “administrator” privileges assigned to it.
Make sure to set for database as follows:
• Character set for database as AL32UTF8
• National Character Set (NLS_NCHAR_CHARACTERSET - AL16UTF16)
Refer to Creating the Database for steps to create the database.
Database Globalization Support Consideration
Oracle Utilities Application Framework is a multilingual capable application that supports the storage, processing, and retrieval of data in multiple languages by leveraging the Oracle Database globalization support architecture. Use of the AL32UTF8 Unicode character encoding system allows the database to support multiple languages.
By default, the database is created with BYTE length semantics. To store data using CHARACTER length semantics, set NLS_LENGTH_SEMANTICS to CHAR at session level via a logon trigger during installation.
Example:
CREATE OR REPLACE TRIGGER RCU_INSTALL_TRIGGER after logon on database
declare
user_name varchar2(100);
begin
select user into user_name from dual;
if ( user_name LIKE 'CISADM' or user_name LIKE 'STG%' ) THEN execute immediate 'alter session set nls_length_semantics=CHAR'; END IF;
END;
There are multiple ways to migrate a database from BYTE to CHAR length semantics:
• Alternative procedure: Below is an alternate way to create a schema with character-length semantics, and then importing the date from a byte-based export.
Initial Install
1. Create the database using DBCA.
2. Run the following statement to set nls_length_semantics=CHAR.
Note: For Container DB, it should be done on the container DB.
SQL> ALTER SYSTEM SET nls_length_semantics=CHAR SCOPE=BOTH;
3. Restart the database.
4. Verify that the nls_length_semantics is CHAR using the following command:
SQL> SHOW PARAMETER nls_length_semantics
Note: For pluggable databases, make sure to set nls_length_semantics=CHAR.
Migrating from BYTE Based Storage to CHARACTER Based Storage
1. Create a database using DBCA.
2. Set nls_length_semantics=CHAR
SQL> ALTER SYSTEM SET nls_length_semantics=CHAR SCOPE=BOTH;
3. Restart the database.
4. Make sure nls_length_semantics is CHAR.
SQL> SHOW PARAMETER nls_length_semantics
Note: For pluggable databases make sure to set nls_length_semantics=CHAR.
5. Export schema from database that has nls_semantics_legth=BYTE.
expdp userid=system/<code>@<SID> directory=<DIR_NAME> schemas=<schema_name> dumpfile=<schema_name>.dmp logfile=<schema_name>.log
6. Generate DDL from the dump file using the Oracle impdp utility.
impdp userid=system/<code>@<SID> directory=<DIR_NAME> DUMPFILE=<schema_name>.dmp SCHEMAS=<schema_name> SQLFILE=<schema_name>_DDL.sql
7. Replace “Byte” with “Char” in <schema_name>DDL.sql.
For vi editor (in Linux), use the following command to replace “Byte” with “Char”.
:%s/BYTE/CHAR/g
8. Replace the schema name also if it is required for the environment.
9. Run <schema_name>DDL.sql (generated in step 6) 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;
10. If any object is missing for any reason, create it by fixing DDL manually (DDL for each object is available in the file created in step 6).
Run the DDL for objects that were not created.
11. Generate s DDL to disable triggers.
SQL> SELECT 'ALTER TABLE' || ' ' ||TABLE_NAME || ' ' || 'DISABLE ALL TRIGGERS;' FROM USER_TABLES;
12. Run the script generated from step 11 to disable all triggers.
13. Import 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
14. Enable triggers.
To generate DDL for triggers:
SQL>SELECT 'ALTER TABLE' || ' ' ||TABLE_NAME || ' ' || 'ENABLE ALL TRIGGERS;' FROM USER_TABLES;
15. Run the script generated from step 14 to enable all triggers.
Extended Datatypes
Some of the Oracle Utilities Application Framework application table varchar2 fields require byte size beyond 4000 bytes to store data for new application requirements. To support this requirement the Oracle Utilities Application Framework database 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.0.0 upgrade.