Migrating to AL32UTF8 on 10gR2 for BANNER



is now live on

Banner 8.x….by using the following procedure.

Updated Information:

Subject: Data Pump Export Does Not Export Disabled Primary Key Constraints

Doc ID: Note:455311.1. Enable the PK constraints manually or with a dynamic SQL script before run Data Pump Export. Then disable the PK constraints manually or with a dynamic SQL script in both source and destination database…or use EXP/IMP.  Currently not fixed until 11.2 listed as Bug 5523375.
This didn’t seem to be a problem for my environment… no Primary Key disabled constraints except for system owned LOGMNR objects.


Character Set Consolidation for the Oracle Database Doc ID: Note:306411.1

Comments on some popular database character sets that are not recommended

  • US7ASCII: better to migrate to WE8MSWIN1252, or WE8ISO8859P15 etc.
  • WE8ISO8859P1: WE8MSWIN1252 is a superset
  • UTF8: better to migrate to AL32UTF8
  • ZHS16CGB231280: ZHS16GBK is a superset
  • ZHS32GB18030: better to migrate to AL32UTF8
  • KO16KSC5601: KO16MSWIN949 is a superset
  • ZHT16BIG5: ZHT16MSWIN950 solves various problems of ZHT16BIG5

If you are a SunGardHE Banner institution then the TEST CASE posted here especially applies to your situation since they are requiring AL32UTF8 for Banner 8.   Thanks to S.Harden and C.Aabbott  and many others of the BORACLE list who provided a lot of the advance beta work on this information.  This is not a rehash of their work with 10gR2  but a log of the steps for 10gR2 to work with Banner 8 with complete information from beginning to end for a smaller database.

This test case will move to UTF8 characterset using the expdp/impdp process.  You could do a manual csalter method but the amount of time and effort required would not be worth it.  The SOURCE database is running Oracle Enterprise Edition and approximately ~50GB in size.  Export/Import could also be used but requires several additional steps of importing the structures with no data, converting all char/varchar2 columns from BYTE to CHAR, then importing data. See Metalink Note:313175.1 for the script to convert the columns.

Why two migrations?  In this case there was Lossy Data that couldn’t be converted from (WE8ISO8859P1 to AL32UTF8) but could be eliminated by converting to WE8MSWIN1252 (which makes sense because it is the superset of WE8ISO8859P1) as an intermediate step.  How do I know which characterset is installed?  Note: The actual migration path depends on your starting characterset.

SQL>  select nls_characterset from nls_database_parameters;

See Metalink Note:260192.1

Changing WE8ISO8859P1/ WE8ISO8859P15 or WE8MSWIN1252 to (AL32)UTF8

1.   Install the CSSCAN tool in the SOURCE database.

SQL> connect / as sysdba

@?/rdbms/admin/csminst.sql (change default tablespace by editing script beforehand)

2.  Run CSSCAN on the SOURCE database.


Duration:Time Started : 2008-06-17 14:54:30

Time Completed: 2008-06-17 15:06:51

Application Data Conversion Summary

Datatype      Changeless       Convertible     Truncation     Lossy

VARCHAR2     1,842,002,371             0                     0           438

CHAR                   41,194,938             0                    0               0

LONG                       427,805             0                     0                1

CLOB                     6,584,926             0                     0            133

VARRAY                               0            0                      0               0


To make this entire project feasible in a weekend, the BANNER DBA needs to have done several prepatory steps before starting the final migration in production.

  • Created a new code tree for BANNER 8.
  • Installed the dependent ICU (International Components for Unicode) see SunGardHE FAQ’s
  • Finished all C++ and COBOL compiles successfully for all modules in that new code tree.
  • Finished all Forms/Reports compiles in a or Oracle Application Server.
  • Done this entire process on a clone of production SEVERAL times.
  • Made sure end user testing was adequately done.
  • Decided on which features in 10gR2 will be used now vs migrated to later.
  • TEST by running batch jobs that produce LARGE amount of archive logs to gauge performance.
  • Security changes in BANNER 8 need to be researched and tested.
  • Resolve all TRUNCATED data.

3. Run csscan again on SOURCE with different parameters to prepare to convert to WE8MSWIN1252 character set since there is LOSSY data.

At this point it would be advisable to mount the SOURCE in RESTRICTED MODE to prevent any updates from clients. STOP all batch processing, STOP GURJOBS, PIPES, POSTING.  STOP the SOURCE database LISTENER.


Find the WE8WIN_to_WE8WIN.txt file and if you find the following entries then the lossy data will be taken care of by migrating to an intermediate (actually a super) character set.

All character type data in the data dictionary remain the same in the new character set

All character type application data remain the same in the new character set

The data dictionary can be safely migrated using the CSALTER script

4. Change the SOURCE database characterset to WE8MSWIN1252. Do you have a good backup at this point?

Important: Do not allow any changes to the database between the previous csscan and the characterset change.

Connect as sysdba set PARALLEL_SERVER and CLUSTER_DATABASE parameters to FALSE; job_queue_processes and aq_tm_processes are set to zero.

Connect as sysdba

SQL > shutdown immediate

SQL > startup restrict


SQL > @?/rdbms/admin/csalter.plb (respond with a Y)

SQL > shutdown

SQL > startup

SQL > select * from v$NLS_PARAMETERS;

5. Create the new database (TARGET) with AL32UTF8 characterset

Be sure and apply patchset for BUG 5874989. Set semantics to CHAR.

SQL > alter system set NLS_LENGTH_SEMANTICS=CHAR scope=both;

Change init.ora parameters of the TARGET database to speed up the IMPDP process.



Other init.ora parameters such as PGA_AGGREGATE_TARGET, SORT_AREA_SIZE, SHARED_POOL_SIZE, SGA_MAX_SIZE and PARALLEL_MAX_SERVERS can be adjusted but will vary depending on your environment.

Precreate your BANNER tablespaces if you want to recreate or change the datafile locations.

Autoextend USERS, TEMP, UNDO and any BANNER tablespaces to UNLIMITED.  This will prevent the job from stopping due to lack of storage.  Be sure and reset them back after you are finished.

6.  Run csscan to prepare to converto to AL32UTF8 character set.

You won’t be using the CSALTER method to do the conversion but this step will identify any data that is labeled TRUNCATION.


Changing the NLS_LENGTH_SEMANTICS to CHAR (later in this post) will fix the truncation data.

List of Tables/Columns that contained TRUNCATION data in this case (23 rows in total), yours will be different.


7. Make a full DATAPUMP export of the SOURCE database.

Make sure NLS_LANG is set correctly, doublecheck it.

Unix example: export NLS_LANG=’AMERICAN_AMERICA.WE8MSWIN1252′

expdp userid/password full=y dumpfile=expSOURCE.dmp

Spool any direct grants which won’t be migrated to the TARGET database during the IMPDP process.  Review the script, it may be advisable to add the list of grantees to be excluded such as the default roles (DBA, EXP_FULL_DATABASE, etc).

set head off

set pagesize 0

set feedback off

spool grants_from_sys.sql

select ‘grant ‘||privilege||’ on ‘||owner||’.’||
table_name||’ to ‘||grantee||’ ‘||
decode(grantable,’YES’,’WITH Grant option’)||’;’
from dba_tab_privs
where owner = ‘SYS’
select ‘grant ‘||privilege||’ (‘||column_name||’) ‘||
‘ on ‘||owner||’.’||table_name||’ to ‘||grantee||’ ‘||
decode(grantable,’YES’,’WITH Grant option’)||’;’
from dba_col_privs
where owner = ‘SYS’

8. Use IMPDP on the TARGET database to import everything at once, not a problem for this ~50GB database.

BEFORE you start the IMPDP make sure NLS_LENGTH_SEMANTICS are set correctly on the TARGET database. Do NOT EXECUTE the following test as SYS…NLS_LENGTH_SEMANTICS won’t affect anything owned by SYS.

alter system set nls_length_semantics=’CHAR’ scope=both;
create table test(v1 varchar2(10));
select CHAR_USED from dba_tab_columns where table_name=’TEST’;

you would expect 10 character (C) for CHAR semantics , but if it is 10 BYTE (B) then you need to restart the database and start a new session. See the following Metalink note for other issues when setting NLS_LENGTH_SEMANTICS.
Examples and limits of BYTE and CHAR semantics usage
Estimated Time for ~50GB is 2.5 hours, your mileage may vary depending on hardware and operating system.

This migration was done in RH AS 4 on Dell Commodity Hardware with 64-bit Oracle Enterprise RDBMS.

Again make sure the NLS_LANG is set to your SOURCE characterset, this is what makes the conversion happen during the IMPDP process.

Unix example: export NLS_LANG=’AMERICAN_AMERICA.WE8MSWIN1252′.  export NLS_LANG=’AMERICAN_AMERICA.WE8MSWIN1252’create directory temp_pump as ‘/backup/export/SID/’;

contents of example parfile, impdpfull.par FULL=Y DUMPFILE=expdpSID.dmp DIRECTORY=TEMP_PUMP LOGFILE=impdp_data.log PARALLEL=6

nohup impdp system/password parfile=imdpfull.par &

You could move objects to new tablespaces by splitting up the process into more than one part. At this point the choice was made to precreate the tablespaces in a different location on the TARGET database than the SOURCE database, the default parameter for REUSE_DATAFILES=N for the IMPDP so it won’t overwrite existing datafiles.  See IMPDP Errors later in the post to resolve specific issues related to this step.

9.  Restore TARGET init.ora parameters appropriate for production.

Run the spooled grants_from_sys.sql.  Run Oracle’s utility NID to rename the database to what the SOURCE production had before, if desired.  Turn off autoextend for all the datafiles or at least give them a maxsize.

10.  Change the BANNER home

…..to the one that you have been testing against with everything already precompiled (remember to do all Forms, C and COBOL). This will save LOADS of time.  There is a script provided that is OS specific see $BANNER_HOME/module/misc

Items that need to be edited (they were redelivered and will overwrite your current copies):

  • $BANNER_HOME/admin/banenv
  • $BANNER_HOME/general/c/sctproc.mk (find the templates from SunGard or another school with the same OS)
  • $BANNER_HOME/general/cob/sctprocb.mk
  • $BANNER_HOME/general/misc/gjajobs.shl

SunGardHE suggests putting the new environmental variables for UTF8 in the .profile for the BANNER account that compiles C and COBOL.  Since the variables are database specific, it really should only be executed at certain times and not globally.  A good location is to have it execute when you source the Oracle-supplied /usr/local/bin/oraenv file under custom code for the UTF8 database, see the following example snippet.

if [ “$ORACLE_SID” = “SID” ] ; then
. /u01/app/sct/SID.ini
. /u01/app/sct/banutf8codetree/admin/banenv

Contents of SID.ini file (we run gurjobs on a separate host so we set the TWO_TASK variable)

export TWO_TASK
export TNS_ADMIN

NOTE:  I removed the follwing NLS_LANG settings from our environment on the jobsub box….there was some debate on whether it was needed.  It makes SQLPLUS output larger than in Banner 7.x which would affect reports and other types of spooled output.

export NLS_LANG

#ICU additions

11.  Install BANNER 8 upgrades/patches appropriate for your environment.

To speed things up: use the AUTOMATED installer, skip all of the GURULTRP.sql steps until the end.  The order I used for the install the major releases:

  • GENERAL 8.0
  • POSNCTL 8.0
  • FINANCE 8.0
  • STUDENT 8.0
  • WEB FINAID 8.1
  • And this list repeats in the same order for all of the 8.1  and 8.2 upgrades.
  • plus patches

Hopefully you already finished all of the compiles (C, Cobol, Forms) ahead of time.

Here is a script to do a mass compile of all of the forms at once….on a Unix box.  I only do this when no end users are online as it degrades performance, there is no problem compiling one script at a time like aluform.shl


sh ./aluform.shl >aluform.log 2>&1 &

sh ./fimform.shl >fimform.log 2>&1 &

sh ./genform.shl >genform.log 2>&1 &

sh ./comform.shl >comform.log 2>&1 &

sh ./payform.shl >payform.log 2>&1 &

sh ./posform.shl >posform.log 2>&1 &

sh ./resform.shl >resform.log 2>&1 &

sh ./stuform.shl >stuform.log 2>&1 &

sh ./tasform.shl >tasform.log 2>&1 &

12.  Restart the listener, gurjobs, sleepwake, pipes, workflow etc.

Change the production location of the forms on the Oracle Application Server to the precompiled Banner 8 versions.

Speific issues or Errors Related to IMPDP :

Job “SYSTEM”.”SYS_IMPORT_FULL_01″ completed with 342 error(s) at 14:44:32

1.  ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type TABLE:”BANINST1″.”AQ$_EVENT_BULKSYNC_TABLE_H” creation failed

FAQ #1-321736  – How can I rebuild BANAQ objects (gnewquesub.sql) or fix invalid objects AQ$EVENT_BULKSYNC_TABLE AQ$EVENT_SYNC_TABLE

2.  ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type TABLE:”BANINST1″.”CREATE$JAVA$LOB$TABLE” creation failed


About April C Sims

Oracle DBA for over a decade...enough said.
This entry was posted in characterset, Migration, Uncategorized, UTF8 and tagged . Bookmark the permalink.

4 Responses to Migrating to AL32UTF8 on 10gR2 for BANNER

  1. Pingback: Olds College SIS Upgrade

  2. I am trying to come up with a succinct but potent example of lossy and truncation data, and not finding it easy. Let’s say I want to show how the Microsoft emdash is lossy. In the WE8ISO8859P1 characterset, the emdash has a ascii value of 149. Since WE8MSWIN1252 is a superset, the emdash happens to have the same ascii value in that characterset as well. Once it goes over to AL32UTF8, it becomes a 3-byte number – dumped, it looks like 226,128,147 (e28093 in hex). But how do I *show* that? How do I show that it cannot go directly from WE8ISO8859P1 to AL32UTF8 aside from running csscan? I prefer not to set up an example database just to run csscan. I am hoping to find a way of using “select …. from dual” in different character sets, preferably by setting NLS_LANG or other derivatives. But the tricky part is that these are special Microsoft characters; actually, the lossy data will be something that cannot be normally typed into a standard sqlplus command-line, I believe.

    Any thoughts?

  3. aprilcsims says:

    Taken from Metalink Document
    Subject: The correct NLS_LANG in a Windows Environment
    Doc ID: Note:179133.1

    “if you want to see / insert languages with a 0 or 1 on a windows client then
    you need to use a Unicode client.
    Sqlplusw.exe and sqlplus.exe are NOT unicode clients
    You can use iSqlplus.
    Note 231231.1 Quick setup of iSQL*Plus 9.2 as unicode (UTF8) client on windows.
    Note 281847.1 How do I configure or test iSQL*Plus 10i?”

  4. Pingback: 2010 in review « Oracle High Availability

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s