NyQuest Consulting, LLC

Converting the Database to UTF8

Starting with Banner® 8, the entire database character set must be converted to UTF8. While the procedure is not difficult, it is somewhat of a pain. This document goes over the steps...

1. Before starting, you will need to create two empty databases in UTF8 format. You can do so using the Java-based "Database Creation Assistant" (dbca) or do so by manually. BE SURE to create ALL the tablespaces which you have in your Banner Database, otherwise you will have problems in your import.

In this document, the Banner Database will be named PROD. The Two empty databases will be named UTF8a and UTF8b, respectively.

2. Cleanly shutdown, and then take a full export of your Banner PROD database. This database will most likely be in US7ASCII or WE8ISO8859P1 character set. You can do so using the export command as shown below:

ORACLE_SID=PROD ; export ORACLE_SID
exp 'system/' full=y buffer=100000 file=ban8.dmp log=prod_export.log

3. If the UTF8a or UTF8b databases are on separate servers, move the export file to that server using ftp or sftp. Perform the following grant on the SYSTEM account in the UTF8a database. At this point you are done with the PROD database.

grant EXEMPT ACCESS POLICY to system;

4. Import the PROD export file into the UTF8a database with the following command:

ORACLE_SID=UTF8a ; export ORACLE_SID
imp 'system/manager' full=y buffer=100000 file=ban8.dmp log=ban8_import.log

In general, an import process will take 5-6 times as long as the export, so please be patient. Inspect the log file for any errors:

5. Export the database from the UTF8a database using the Oracle Datapump utility:

ORACLE_SID=UTF8a ; export ORACLE_SID
expdp 'system/manager' full=y dumpfile=utf8a_dp.dmp logfile=ban8_dp_export.log

Usually, the export file will be in the $ORACLE_HOME/admin//dp directory. At this point you are done with the UTF8a database.

6. Before importing into the UTF8b database, but sure the following parameters are first set:

NLS_LENGTH_SEMANTICS=CHAR

You will need to restart the UTF8b database after setting this parameter.

7. Finally import the second export file into the UTF8b database using the Oracle Datapump utility:

ORACLE_SID=UTF8b ; export ORACLE_SID
impdp 'system/manager' full=y dumpfile=utf8a_dp.dmp logfile=ban8_dp_import.log

8. At this point, you are done. The new database will be called UTF8b.

9. You can check the character set by doing a DESC on any table with a VARCHAR2 type. If it says VARCHAR2(20) or something like that, you are ok. if it says VARCHAR2(20 BYTE) something is wrong.