Oracle Character Set Conversion / Oracle Character Set Migration
1- Reference Notes and Documents
Following are the useful notes and documents for character set conversion:
To convert from US7ASCII to either AL32UTF8 or UTF8 please follow the steps outlined in:
Note 1272374.1 - The Database Migration Assistant for Unicode (DMU) Tool
The following note provides more details of a similar character set conversion to AL32UTF8 or UTF8:
Note 1546507.1 - How to Migrate a WE8ISO8859P1 DB to AL32UTF8 using DMU 1.2 - an example
Some additional references to read through and consider:
Note 124721.1 - Migrating an E-Business Suite Installation to a New Character Set
Note 788156.1 - AL32UTF8 / UTF8 (Unicode) Database Character Set Implications
2- Overview
1- Compile invalid objects before starting the activity.
2- Start DMU 2.1 and connect database (all application services should be down).
3- There should be sufficient space in the following table-spaces IDX, TEMP, DATA and UNDO.
4- Scan database for UTF8 character set and also provide assumed character set.
5- Check and resolve all the issues.
6- Re-scan the database. Repeat step 3 and 4 till the resolution of all issues.
7- Convert database.
8- Compile invalid objects.
9- Validate the database after uninstalling the repository and installing the repository in validation mode. Re-scan database to check there should not exists any issues. If you found any of the data relevant issues then you can fix them at this stage as well.
3- How to Start DMU 2.1 Utility?
a- Following table-spaces should have minimum amount of free space before running the DMU:
Table Spaces FREE ( GBs )
TEMP1 15
TEMP2 15
DATA 8
IDX 20
UNDO 4
b- Source DB environment.
c- export JAVA_HOME=$ORACLE_HOME/jdk
d- Create Password File ( if not already created ) :
cd $ORACLE_HOME/dbs
orapwd file=orapwPRODCL entries=10 password=oracle ignorecase=true
(Syntax: orapwd file=orapwSID entries=10 password=[syspassword] ignorecase=true )
e- Add entry in sqlnet.ora file
cd $ORACLE_HOME/network/admin/SID_hostname
SQLNET.AUTHENTICATION_SERVICES = all
f- Add entry in init.ora
cd $ORACLE_HOME/dbs
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
g- Unzip and start DMU utility:
Remove or backup the already existing DMU folder on $ORACLE_HOME
Unzip dmu folder under $ORACLE_HOME.
Start DMU:
cd $ORACLE_HOME/dmu
sh dmu.sh
h- Make connection using sys user and provide the login details as shown below.
Note: DMU default log location is /home/oracle/.dmu/log
4- Character Set Conversion
Create connection by right clicking on Databases and selecting New Database Connection.
Fill the connection details.
Test connection and press SAVE button.
Right click on Connection Name and select Connect.
Now right click on Connection Name and select Configure DMU Repository.
Select UTF8 and press NEXT button.
Press table-space and press NEXT button if asks for table-space, for repository installation.
Repository installed successfully. Press OK button.
Following status window will appear after installing repository.
Right click on Connection Name and Select Properties.
Select assumed character set to WE8MSWIN1252 and press APPLY button.
Right click on Connection Name and select Scan Database.
DMU will refresh the repository.
Press NEXT button.
Enter number of scanning processes and press NEXT button.
Press NEXT button.
DMU will calculate the table split threshold.
Press FINISH button.
DMU will show the following Scan Progress screen. You have to wait to finish the scan.
Scan Database completed.
Right click on Connection Name and select Database Scan Report.
DMU will show Database Scan Report.
Select “With Some Issues” as shown below.
Expand the tree and check errors.
In our case we have to deal with 2 types of errors:
Invalid Representation and Over Column Limit.
Dealing with Over Column Limits:
Select table which shows more than 0 in Over Column Limit column.
Click on blue colored highlighted number (in my case 1).
DMU will show the erroneous data.
Double click on yellow text. DMU will display the field data.
It shows current column size 240 byte and data is of 241 byte. So we need to remove 1 character. Press OK button.
Press SAVE button.
Now close MTL_CATALOG_SEARCH_ITEMS window correct all over column limit data as shown above. OR you can increase the column size if you want to keep data without removing any character. Click on blue colored number.
Press blue colored right () arrow.
Pressing blue colored right () arrow will move you to the next erroneous column.
Now reduce text from these yellow colored cells and press SAVE button.
Dealing with Invalid Representation:
Click on blue colored number from Invalid Representation column.
DMU will show the column’s data which have invalid data.
Now select the errornous column and select anohter character set which suites to this column and press SAVE button.
Repeat the above process to remove all Invalid Representations.
After resolving all errors the Scan Database again and find data “With Some Issues”. Following screen shot shows that there exists no errors and warnings.
Now we can convert database to UTF8 character set. Right click on Connection Name and select Convert Database.
DMU will refresh the repository.
Now press CONVERT button.
DMU is now staring the conversion process.
Press OK button to start conversion process.
Press NEXT button to scan the database before starting the conversion.
Select number of scanning processes and press NEXT button.
DMU will show scanning details.
Press FINISH button.
DMU will show the scanning progress.
DMU will start the pre-conversion tasks.
During the conversion process if error occurs, DMU will show error screen as shown below.
(Note: You can find log files on the location: /home/oracle/.dmu/log)
You should press CONTINUE button after resoling the error.
Conversion completed successfully. Press OK button.
Select Exit from File menu.
Press YES button to close the DMU. (Bounce the database)
Note: Do not forget to bounce the database.
5- Un-Install the Repository
Re-open DMU after bouncing the database and Un-Install the repository.
Right click on Connection Name and select Configure DMU Repository
Select Uninstall and Press Next.
Press FINISH button:
Repository is Un-Installed successfully. Press OK button.
Compile objects using adadmin, or $ORACLE_HOME/rdbms/admin/utlrp.sql (connect using sys user) or using below query if you want to compile manually:
SELECT
OWNER,
OBJECT_NAME ,
OBJECT_TYPE,
'ALTER ' || DECODE(OBJECT_TYPE,'PACKAGE BODY','PACKAGE','SYNONYM','PUBLIC SYNONYM',OBJECT_TYPE) || ' ' ||
DECODE(OBJECT_TYPE,'SYNONYM',NULL,OWNER ||'.') || OBJECT_NAME || ' COMPILE ' || DECODE(OBJECT_TYPE,'PACKAGE BODY', ' BODY ',NULL) || ' ; ' ALTER_STATEMENT
FROM
DBA_OBJECTS
WHERE
STATUS = 'INVALID'
AND OBJECT_TYPE IN ('PACKAGE','FUNCTION','PROCEDURE','VIEW', 'PACKAGE BODY','SYNONYM')
ORDER
BY
OBJECT_NAME,
OBJECT_TYPE;
6- Validate the Migration
Right click on Connection Name and select Configure DMU Repository.
Select Install Repository in Validation Mode and press NEXT button.
Select anther table-space if you want to install repository on any other table-space. Then press FINISH button.
Right click on Connection Name and select Scan Database again to validate the data.
DMU will refresh the repository.
Press NEXT button.
Select number of scanning process and press NEXT button.
DMU will start scanning.
Scan completed. Press OK button.
Right click on Connection Name and select Database Scan Report and validate there should not exist any issue like Invalid Data Representation, Over Column Limits, etc. If there exits any issues then you should resolve them at this stage.
