The Migration Completed Successfully. Half the Names Are Now Question Marks.

The cutover ran clean. impdp reported completed successfully, the row counts matched to the row, the application came up, and everyone went home. Three weeks later a support ticket lands: a customer named José is in the system as Jose, and a partner in Tokyo shows up as ??. Not a rendering bug — the database really holds Jose and ?? now. The originals are gone, and no backup from after the cutover has them either.

Here’s the uncomfortable part: nothing failed. No error was raised, no row was rejected, no count was off. The migration did exactly what you told it to — you just told it to pour Unicode data into a container too narrow to hold it, and Oracle quietly trimmed everything that didn’t fit. This post is what character-set data loss actually is, why every check you ran said “success,” the one test that catches it, and a lab that reproduces the loss on demand and then shows the migration that keeps every character.

What actually happens

A character set is the map between the characters you see and the bytes stored on disk. Oracle has two that matter here: the database character set (NLS_CHARACTERSET), which governs VARCHAR2/CHAR data, and the client’s declared character set (NLS_LANG), which tells the server what the bytes arriving from a client mean. Modern databases use AL32UTF8 — Unicode — which can represent every character in every language. Many older databases used a narrow set: US7ASCII (7-bit, 128 characters), WE8MSWIN1252 (Western European), WE8ISO8859P1, and so on.

When data moves from a wider character set into a narrower one — an export/import into a legacy target, a CREATE TABLE AS across a database link, a client conversion — Oracle must map each character into the target set. For a character the target can represent, fine. For one it can’t, it does one of two things, and both are data loss:

• Silent transliteration. A character with a rough ASCII equivalent is replaced by that equivalent. José becomes Jose, Müller becomes Muller, François becomes Francois. It looks completely plausible — which is exactly why it sails through QA and lands in production.
• Replacement. A character with no mapping at all becomes a replacement character, almost always ?. A CJK name becomes ??; an emoji or an unusual symbol becomes ?. Obvious once someone looks — but nobody looks at every row.

Crucially, this conversion is a normal, “successful” operation. It does not raise an error. It does not reject the row. The row count is identical before and after. Every signal a migration runbook checks says the load worked.

Why every check said “success”

The standard post-migration checks all operate at the wrong level:

• Row counts compare how many rows moved, not what’s in them. José and Jose are both one row.
• “0 errors” / completed successfully means no row was rejected. Character-set conversion isn’t a rejection — it’s a transformation the database is happy to perform.
• Spot-checking finds the ?? names if you happen to look at one, but misses the transliterated ones entirely, because Jose looks correct.

The loss is in the values, so you have to compare the values — specifically, whether each value would survive a round-trip through the target character set. That’s the check almost nobody runs, and it’s the whole game.

flowchart TD
S["Source: AL32UTF8 (Unicode)<br/>José · Müller · 田中 · Smith"] --> T{"Target character set?"}
T -->|"US7ASCII (7-bit)"| A["José→Jose, Müller→Muller<br/>田中→??<br/>5 of 10 names damaged"]
T -->|"WE8MSWIN1252 (Western Euro)"| B["accents kept<br/>田中→??<br/>CJK name lost"]
T -->|"AL32UTF8 (Unicode superset)"| C["every character preserved<br/>0 loss"]
A --> R["all report: rows match, 0 errors<br/>-> loss is invisible to naive checks"]
B --> R
C --> R

Reproduce it

You don’t need a real migration to see this. Build a 10-row customers table in an AL32UTF8 database — five plain-ASCII names and five with accents or CJK characters — then model loading it into a narrower target with CONVERT(name, target_charset, 'AL32UTF8'), which does exactly what the target database’s conversion does on import. Migrating into US7ASCII:

rows after migration: 10   errors raised: 0
names damaged by a US7ASCII target: 5 of 10   (of those, turned into '?': 1)
names still damaged by a WE8MSWIN1252 (Western European) target: 1
   José         ->  Jose     (US7ASCII target)
   Müller       ->  Muller   (US7ASCII target)
   Renée        ->  Renee    (US7ASCII target)
   François     ->  Francois (US7ASCII target)
   田中          ->  ??       (US7ASCII target)

Ten rows in, ten rows out, zero errors — and half the names quietly damaged. Four were transliterated into plausible-but-wrong ASCII; one became ??. Note the middle row of the story: even a Western European target (WE8MSWIN1252), which happily keeps José and Müller, still turns the CJK name into ??. “Wide enough for our data” is a judgment that ages badly the moment you onboard a customer from a new region.

Detect it — before cutover

The test that catches what row counts miss is a round-trip: convert each value to the target character set and back, and compare it to the original. Anything that comes back changed would be damaged by the migration:

-- pre-migration scan: which rows would a US7ASCII target silently damage?
select id, name
from   customers
where  convert(convert(name,'US7ASCII','AL32UTF8'),'AL32UTF8','US7ASCII') <> name;

In the lab this flags exactly the five non-ASCII rows, in advance. This is precisely what Oracle’s Database Migration Assistant for Unicode (DMU) — and the legacy csscan utility — do for real: scan every column of every table and report the data that is lossy (can’t be represented in the target) or would need byte expansion. Run it against the source before you migrate, not against the wreckage afterward.

The fix

• Migrate into a Unicode target (AL32UTF8). Unicode is a superset of every legacy Oracle character set, so every source character has a representation and nothing is lost. In the lab, the same data migrated into AL32UTF8 has a round-trip loss of zero. If you’re modernizing anyway, this is the answer: go to AL32UTF8 and stop having this problem forever.
• If you must land in a narrower set, scan first and remediate. Use DMU/csscan to find lossy data, then fix it at the source (correct the values, or widen the target) before cutover — never discover it after.
• Set the client NLS_LANG correctly on every tool in the pipeline. More on this trap below; a wrong NLS_LANG corrupts data even when both databases are Unicode.
• Watch column length semantics when migrating up to Unicode. Going from a single-byte set to AL32UTF8, characters that were one byte can become up to four. A VARCHAR2(10 BYTE) column that held a 10-character name may no longer fit — the import fails with ORA-12899, or worse, silently truncates. Define columns with CHAR semantics (VARCHAR2(10 CHAR)) or size for the byte expansion.

What teams get wrong

• Trusting the row count and the “completed successfully”. Both are true and both are irrelevant to character-set loss. The count tells you rows moved; it tells you nothing about whether the values survived.
• Thinking ? is the only symptom. The ?-marks are the lucky case — they’re visible. The silent transliteration (José→Jose) is the dangerous one: it looks right, passes review, and you find out when a customer complains that you’ve been spelling their name wrong for a month.
• The NLS_LANG pass-through trap. The other classic corruption isn’t a narrow database — it’s a wrong client. If a tool inserts UTF-8 bytes while its NLS_LANG claims US7ASCII, Oracle believes the lie, does no conversion (or the wrong one), and stores garbage — even when the database is AL32UTF8. “Both sides are Unicode” does not save you if a client in the middle misdeclares its character set. Set NLS_LANG on every client, exporter, and loader.
• Assuming “Western European is wide enough”. It holds accented Latin, so it survives a demo full of French and German names — and destroys the first Japanese, Greek, Arabic, or emoji-bearing value it meets.
• Migrating first, scanning never. DMU/csscan exist to be run before the migration. Skipping the scan is choosing to find your data loss in production.
• No backup of the pre-migration source. Character-set loss is irreversible: once José is stored as Jose, the accent is gone. If you didn’t scan and you didn’t keep the source, there is nothing to recover from. Keep the source readable until you’ve verified the target at the value level.

Character-set loss belongs to the same family of migrations that “succeed” without being correct: it is the values-level cousin of counting the rows after a Data Pump job, where a load reports success but the data isn’t whole — here the rows are all present and it’s the characters inside them that are missing. It’s the detail that turns a clean-looking cloud migration into a month-late incident. Treat “completed successfully” as the start of verification, not the end of it: scan the source for lossy data with DMU or a round-trip query, migrate into a Unicode (AL32UTF8) target so there is nothing to lose, set NLS_LANG correctly on every client, and prove it the way that ends the argument — with the character-set migration lab, where a US7ASCII target silently damages half the names and a Unicode target keeps every one.