Changing the NLS_CHARACTERSET to AL32UTF8 – This article help you to convert character set of database form WE8ISO8859P1 to AL32UTF8 in 12c or 19c database.
What is the DMU (Database Migration Assistant for Unicode) tool?
DMU is a GUI based tool that is more intuitive than csscan/csalter and automate a lot of the conversion process when changing the NLS_CHARACTERSET to UTF8 or AL32UTF8 of an Oracle RDBMS database.
DMU is the Database Migration Assistant for Unicode . It converts the NLS_CHARACTERSET of an existing database to AL32UTF8 or UTF8. It is NOT a tool to do an Oracle RDBMSupgrade.
Applies to:
- oracle cloud infrastructure – database service – version n/a and later
- oracle database cloud exadata service – version n/a and later
- oracle database cloud schema service – version n/a and later
- oracle database exadata express cloud service – version n/a and later
- oracle database cloud service – version n/a and later
- information in this document applies to any platform.
Purpose
The purpose of this note is to provide the exact steps to change the current nls_characterset to AL32UTF8 / UTF8 or an other multibyte characterset.
In 10g and 11g you need to use csalter (or the dmu tool).
In 12c you need to use the DMU tool.
Do not use “alter database character set” in 10g, 11g or 12c to go to AL32UTF8 or UTF8.
Using “alter database character set” to go to UTF8 or AL32UTF8 is not supported in 10g, 11g or 12c and will corrupt at least (!) data dictionary objects and most likely also user data.
If “alter database character set” is used in 10g , 11g or 12c to go to AL32UTF8 or UTF8 the only action possible is back to backup.
From oracle 12c onwards the DMU will be the only tool available to migrate to unicode see note 1272374.1 the database migration assistant for unicode (dmu) tool.
Scope
Changing the current nls_characterset to AL32UTF8 / UTF8 or an other multibyte characterset.
In this note al32utf8 will be used, but it’s applicable to utf8 (like to be used in 8I instead of al32utf8) or other multibyte charactersets also.
The current nls_characterset is seen in nls_database_parameters.
sqlplus / as sysdba
select value from nls_database_parameters where parameter='nls_characterset';
The nls_characterset is defining the characterset of the char, varchar2, long and clob datatypes.
Oracle RDBMS version requirements (this is server / database side) for the DMU tool:
Install the required PL/SQL package in the database. Install the SYS.DBMS_DUMA_INTERNAL package.
SQL> show user
USER is "SYS"
SQL> @?/rdbms/admin/prvtdumi.plb
Library created.
Package created.
No errors.
Package body created.
No errors.
SQL>
We need the java install on server before going to install the DMU tool on server. We have downloaded the jdk 1.8 on server and we will use it during the installation.
Oracle Database Migration Assistant for Unicode: Getting Started
[testx@testserver01.com ~]$ sudo -iu otestdb
Password:
testserver01.com:/testdb/db/otestdb> cd ..
testserver01.com:/testdb/db> ls
19.0.0 admin cfgtoollogs checkpoints diag gpnmu jdk1.8.0_241 otestdb tmp
Go to directory location where you have downloaded the DMU tool and run the dmu.sh script.
DMU tool
Oracle Database Migration Assistant for Unicode: Downloads
testserver01.com:/testdb/db> cd /backup/dump/dmu/
testserver01.com:/backup/dump/dmu> ls -ltr
total 160
drwxrwxrwx 3 otestdb dba 4096 Jul 7 2016 sleepycat
drwxrwxrwx 2 otestdb dba 4096 Jul 7 2016 equinox
drwxrwxrwx 8 otestdb dba 4096 Jul 7 2016 ide
drwxrwxrwx 5 otestdb dba 4096 Jul 7 2016 netbeans
-rwxrwxrwx 1 otestdb dba 238 Jul 13 2017 dmucls.sh
-rwxrwxrwx 1 otestdb dba 53 Nov 27 2018 dmu.sh
-rwxrwxrwx 1 otestdb dba 8983 Mar 12 2019 dmucls.bat
drwxrwxrwx 9 otestdb dba 4096 Jul 23 2019 dmu
drwxrwxrwx 6 otestdb dba 4096 Jul 23 2019 jdev
drwxrwxrwx 2 otestdb dba 4096 Jul 23 2019 dropins
drwxrwxrwx 2 otestdb dba 4096 Jul 23 2019 external
drwxrwxrwx 10 otestdb dba 4096 Jul 23 2019 modules
drwxrwxrwx 2 otestdb dba 4096 Jul 23 2019 jlib
drwxrwxrwx 2 otestdb dba 4096 Jul 23 2019 configuration
-rwxrwxrwx 1 otestdb dba 24576 Jul 23 2019 dmuW64.exe
-rwxrwxrwx 1 otestdb dba 24064 Jul 23 2019 dmuW32.exe
-rwxrwxrwx 1 otestdb dba 24576 Jul 23 2019 dmu64.exe
-rwxrwxrwx 1 otestdb dba 24064 Jul 23 2019 dmu32.exe
testserver01.com:/backup/dump/dmu> ./dmu.sh
Right-click on the “Databases” node in the Navigator pane and select the “New Database Connection” option from the popup menu.
Enter the connection details and click the “Test Connection” button. Once you get a successful test, click the “Save” button to return to the previous screen.
Click Next
Click Next
Select the Repository tablespace as per your environments.
Click Finish.
Repository installed.
Scan database.
Click next
Choose collect rowid option and click next.
Select the Scan objects
Click next
Select option All to convert.
Monitor until it get completed.
Check validate status.
Choose Bulk Cleansing
Click next
Choose Migrate to character length option.
Choose immediate cleansing
Click next
Click next
Progress.
Now go to database section and click on convert database.
Click on convert button and wait until process is completed.
Click on YES button.
Check the migration status.
Now you can choose reconfigure option to uninstall the DMU repository from database.
Click next
Repository has been uninstalled successfully.
AL32UTF8 / UTF8 (Unicode) Database Character Set Implications (Doc ID 788156.1)
How to Migrate a WE8ISO8859P1 DB to AL32UTF8 using DMU 1.2 – an example (Doc ID 1546507.1)
Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g (Doc ID 260192.1)
Please share this Blog with your colleagues or friends. Your suggestions and feedback are very helpful for everyone who come to this site and learn it from oracleocpworld.com.
Please comment here for your any query related to above post. You can email me on : oracleocpworld@gmail.com.