FMW 11g Upgrade Changing the Characterset to AL32UTF8 - Part 1

FMW 11g Upgrade Master Index

One of the pre-requisite task in moving to fusion middleware 11g is converting the existing 10g Infrastructure Database to AL32UTF8 character set.

This document has all the information to do it.

Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) [ID 260192.1]

I had to follow the instructions as per the above doc.
There were two issues for me

1. Package PORTAL.WWCTX_API_PROXY and ORASSO.WWCTX_API_PROXY has to be dropped and recreated after the upgrade.
Solution to solve this is drop the packages and convert the database to AL32UTF8 character set. Then recreate it using note 751442.1

2. Some Portal Tables and ODS Tables were marked as CONVERTABLE by the CSSCAN utility.
Proposed solution as per the document is to export the tables , truncate the rows , convert the database and then import the tables back.

Looks simple but I was worried since some tables where from PORTAL and ODS

ODS.CT_CN(ATTRVALUE) - VARCHAR2 - CONVERTIBLE
ODS.CT_DN(RDN) - VARCHAR2 - CONVERTIBLE
ODS.CT_MIDDLENAME(ATTRVALUE) - VARCHAR2 - CONVERTIBLE
ODS.CT_ORCLNORMDN(ATTRVALUE) - VARCHAR2 - CONVERTIBLE
ODS.CT_SN(ATTRVALUE) - VARCHAR2 - CONVERTIBLE
ODS.CT_UID(ATTRVALUE) - VARCHAR2 - CONVERTIBLE
ODS.DS_ATTRSTORE(ATTRVAL) - VARCHAR2 - CONVERTIBLE
ORASSO.WWSSO_PSEX_USER_INFO$(SSO_USERNAME) - VARCHAR2 - CONVERTIBLE
PORTAL.WWA_FORM$(JS_CODE) - CLOB - CONVERTIBLE
PORTAL.WWA_FORM$(JS_LIB) - CLOB - CONVERTIBLE
PORTAL.WWA_FORM$(LAYOUT_BODY) - CLOB - CONVERTIBLE
PORTAL.WWA_FORM$(LAYOUT_FOOTER) - CLOB - CONVERTIBLE
PORTAL.WWA_FORM$(LAYOUT_HEADER) - CLOB - CONVERTIBLE
PORTAL.WWV_TEXT(TEXT) - CLOB - CONVERTIBLE
PORTAL.WWV_THINGS(NAME) - VARCHAR2 - CONVERTIBLE
PORTAL.WWV_THINGS(TITLE) - VARCHAR2 - CONVERTIBLE


Here is what I did
- exported the tables using expdp
- portal tables were giving zero rows in the export
- security policy was stopping from expdp to export the rows
- ran "grant exempt access policy to system;"
- exported the portal tables as system user
- tried to truncate the tables but constraints were stopping me
- so disabled all the constraints for the relevant tables
- truncated the rows
- converted the database to AL32UTF8 characterset
- imported the tables using impdp with exist action as truncate
- all tables went fine except WWV_THINGS
- there were some triggers on that table so disabled them
- re-imported wwv_things
- verified the exported and imported tables row count
- enabled the triggers
- enabled all the constraints that were disabled
- started the middletire
- validated portal and oid


Infra DB Looks good now for the FMW Upgrade 11g.