Migrating to a new Oracle Database with UTF-8 Character Set

[Fred638]

🏷️ Discussion Type

Question

Body

Hi,
I am not sure if this topic belongs in this section, but I will post it here for now.

We are in the process of migrating from Oracle 21c without UTF-8 to Oracle 21c with UTF-8 Character Set. I heard that Oracle recommends certain checks before going to production. I did not find a thorough guide on this topic. AI also did not show a more detailed step-by-step guide.

Any links, documentation or other resources would be greatly appreciated.

Guidelines

• I have read and understood this category's guidelines before making this post.

[azunox]

Hi @Fred638 ,

Migrating to a UTF-8 character set (specifically AL32UTF8 in Oracle) is a critical task.
The most significant challenge you will face is data expansion, as characters that previously took 1 byte might now take up to 4 bytes, potentially exceeding your current column limits.

Here is a breakdown of the key checks and the standard process Oracle recommends before going to production:

1. Run the Database Migration Assistant for Unicode (DMU)

This is Oracle's official and most essential tool for this process (it replaces the legacy csscan utility).

What it does: DMU scans your source database and provides a comprehensive readiness report.

Crucial Pre-checks: It identifies data that will be truncated due to expansion, flags data with invalid binary representations, and finds "lossy" data (data that cannot be properly converted).

Action: Do not proceed to production without running a full DMU scan and cleansing any reported exceptions.

2. Review Column Length Semantics (BYTE vs. CHAR)

If your current database uses BYTE semantics (e.g., VARCHAR2(50) means exactly 50 bytes), moving to UTF-8 might cause future INSERT or UPDATE statements to fail if a user enters 50 characters that now require significantly more bytes.

Action: Consider migrating your schema to use CHAR semantics (e.g., VARCHAR2(50 CHAR)). This allows the column to store 50 characters regardless of the underlying byte size.

3. Verify Client and Application Configurations (NLS_LANG)

Migrating the database is only half the battle. Your client applications (JDBC, OCI, middleware) must be configured correctly to communicate without corrupting data.

Action: Audit and update the NLS_LANG environment variables and connection string settings across all application servers and client machines to ensure they are aware of the new character set, preventing double-conversion issues.

4. Storage and Performance Testing

UTF-8 databases generally require more storage space for indexes and table data, as well as more memory (SGA/PGA).

Action: Monitor your tablespace growth in a staging environment and conduct performance load testing to ensure the slightly increased overhead of multibyte character processing does not impact your SLAs.

Recommended Official Resources
For detailed step-by-step instructions, I highly recommend looking up these specific Oracle 21c documents:

Oracle Database Globalization Support Guide (21c) - Focus specifically on the "Character Set Migration" chapter.

Oracle Database Migration Assistant for Unicode Guide - This will be your primary technical manual for executing the migration safely.

Hope this helps give you a solid roadmap for your migration!

[Fred638]

Hi @azunox ,

Thanks a lot for the detailed explanation! This is exactly what I was looking for. I have a follow-up question: What if the Data Pump Utility will be used for the migration? Does this Utility take care of the conversion or does the DMU have to be run in any case?

Additional information:
Source: Oracle 21c SE2, Character Set: WE8ISO8859P15, Size: ~1,2 TB, Target: Oracle 21c SE2, Character Set: AL32UTF8

We ran a test migration and noticed that some columns in views have UNDEFINED() data types.

[azunox]

@Fred638

Here is a helpful response you can reply with:

Data Pump automatically handles character set conversion during import, but DMU is still strongly recommended before doing a 1.2 TB migration.

While Data Pump converts data from WE8ISO8859P15 to AL32UTF8 on the fly, it will not prevent data truncation or invalid character mapping. If a string expands beyond its target column length during conversion, Data Pump will fail or truncate data without prior warning. Running a DMU scan beforehand ensures you identify and fix these potential truncation risks before running impdp.

Regarding the UNDEFINED() data types in views: this usually happens when character set conversion changes the byte evaluation of expression columns inside the view definition. Try recompiling the views in the target database using ALTER VIEW view_name COMPILE. If that does not resolve it, inspect the view definitions for implicit character type conversions or expression calls that may need explicit CASTing.

[Fred638]

Thanks again for pointing out. We will proceed accordingly.

[azunox]

Your welcome! @Fred638 ,

If this resolved your issue, could you please click "Mark as answer" on the comment? It helps others in the community find the solution more easily. Thanks!

[Fred638]

Yes, I will definitely mark the answers.

[Fred638]

What about the semantics. Currently nls_length_semantics is set to byte in the UTF-8 database. What if we change this to char will it affect objects that have, i.e. VARCHAR2(50 byte) columns?

[EsraaRMashaal]

Hi,

We went through a similar migration before. From what I remember, Oracle's Database Migration Assistant for Unicode (DMU) is a good place to start.

It can scan the database before the migration and help identify data that might cause problems during the character set conversion.

I'd recommend doing the scan in a test environment first and checking especially for columns containing multilingual data, as well as VARCHAR2, CHAR, and CLOB data.

After fixing any issues reported by the scan, I'd do a full migration test and verify the application, imports/exports, and database connections before moving to production.

Oracle has a pretty good guide for DMU here:
https://docs.oracle.com/en/database/oracle/dmu/

Hope this helps

[Fred638]

Hi @EsraaRMashaal ,
Thanks to you as well for your feedback! I'll have a look at the guide.

[Saharier36]

Hi @Fred638,

To directly answer your question: No, changing NLS_LENGTH_SEMANTICS will NOT automatically change or affect existing columns.

Here is how Oracle handles this behavior:

• Existing Columns are Explicit: Any column created as VARCHAR2(50 BYTE) retains its explicit BYTE definition. Changing the parameter at the system or session level (ALTER SYSTEM/SESSION SET NLS_LENGTH_SEMANTICS=CHAR;) only acts as a default for new tables or new columns created without an explicit specifier (e.g., plain VARCHAR2(50)).

• Modifying Existing Columns: If you want existing columns to use character semantics, you must explicitly alter them:

  ALTER TABLE your_table MODIFY (column_name VARCHAR2(50 CHAR));

[Fred638]

Hi @Saharier36 ,
Thank you!