Migrating to ALU32UTF8

Updated:

SUU

is now live on

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

Updated Information:

Subject: Data Pump Export Does Not Export Disabled Primary Key ConstraintsDoc 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.

See…

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 10.2.0.4 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.

Finished all C++ and COBOL compiles successfully for all modules in that new code tree.

Finished all Forms/Reports compiles in a 10.1.2.2 or 10.1.2.3 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.

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.

FIMSMGR.FOBTEXT

SATURN.SARQUAN

SATURN.SARRQST

SATURN.SPRCMNT

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).

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 usageEstimated 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.

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.

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.

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

ADVANCEMENT 8.0

POSNCTL 8.0

HUMAN RESOURCES 8.0

ACCOUNTS RECEIVABLE 8.0

FINANCE 8.0

STUDENT 8.0

INTEGRATION COMPONENTS 8.0

FINANCIAL AID 8.0

FINANCIAL AID 8.1

WEBTAILOR 8.0

WEB GENERAL 8.0

WEB ADVANCEMENT 8.0

WEB HUMAN RESOURCES 8.0

WEB FINANCE 8.0

WEB 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

#do_all_forms.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.