DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01188 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01188: Character Set Mismatch in Block Header and File Header

ORA-01188 occurs when Oracle detects a discrepancy between the character set information stored in a datafile's block header and its file header. This typically surfaces during database startup, file recovery, or cross-database migration operations. If left unresolved, it can lead to data corruption and database unavailability.

Top 3 Causes

1. Moving Datafiles Between Databases with Different Character Sets

Copying a datafile directly from a database using AL32UTF8 into an environment using KO16MSWIN949 (or any other charset mismatch) will cause the headers to conflict.

-- Check current database character set
SELECT PARAMETER, VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

-- Check datafile header status
SELECT FILE#, STATUS, CHECKPOINT_CHANGE#
FROM V$DATAFILE_HEADER
ORDER BY FILE#;
Enter fullscreen mode Exit fullscreen mode

2. Incomplete or Forced Character Set Conversion

Running ALTER DATABASE CHARACTER SET without using Oracle's official DMU tool, or having the conversion interrupted midway, can leave some datafile headers updated while others remain on the old charset.

-- Check NLS metadata in PROPS$ (requires DBA privilege)
SELECT NAME, VALUE$
FROM SYS.PROPS$
WHERE NAME IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

-- If a forced internal reset is required (only after consulting Oracle Support)
-- STARTUP RESTRICT;
-- ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
-- SHUTDOWN IMMEDIATE;
-- STARTUP;
Enter fullscreen mode Exit fullscreen mode

3. Improper RMAN Restore from Mixed-Environment Backups

Restoring backups created from a different character set environment, or mixing backup sets from multiple database versions without proper validation, can produce this error.

-- Validate all datafiles using RMAN
-- RMAN> VALIDATE DATABASE;

-- List backup details to identify character set origin
-- RMAN> LIST BACKUP OF DATABASE;

-- Recover a specific problematic datafile
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/problem_file.dbf' OFFLINE;

-- Then in RMAN:
-- RESTORE DATAFILE '/u01/oradata/ORCL/problem_file.dbf';
-- RECOVER DATAFILE '/u01/oradata/ORCL/problem_file.dbf';

-- Bring it back online
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/problem_file.dbf' ONLINE;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 — Identify the problem file from the alert log. ORA-01188 is almost always accompanied by ORA-01110, which names the exact datafile.

Step 2 — Check file header details:

-- Dump file headers for analysis
ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level 10';

-- Review datafile and tablespace mapping
SELECT DF.FILE#, DF.NAME, DF.STATUS, TS.NAME AS TABLESPACE_NAME
FROM V$DATAFILE DF
JOIN V$TABLESPACE TS ON DF.TS# = TS.TS#
ORDER BY DF.FILE#;
Enter fullscreen mode Exit fullscreen mode

Step 3 — Restore from a clean backup taken before the charset mismatch occurred:

-- Point-in-time recovery in RMAN (example)
-- RMAN> RUN {
--   SET UNTIL TIME "TO_DATE('2024-06-01 08:00:00','YYYY-MM-DD HH24:MI:SS')";
--   RESTORE DATABASE;
--   RECOVER DATABASE;
-- }

-- After recovery, open with RESETLOGS
ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always validate character sets before any cross-database file operation.
Use Data Pump (expdp/impdp) or Transportable Tablespaces instead of raw file copies. Before any migration, run a pre-check script:

-- Pre-migration charset validation
SELECT 'NLS_CHARACTERSET' AS PARAM,
       VALUE AS DB_VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';
Enter fullscreen mode Exit fullscreen mode

2. Use Oracle DMU and take a full backup before any charset conversion.
Never use ALTER DATABASE CHARACTER SET without Oracle's Database Migration Assistant for Unicode (DMU). Always perform a validated full RMAN backup immediately before and after any character set change, and review Oracle MOS Note 1297961.1 for the official conversion procedure.

Related Errors

  • ORA-01122 — Datafile header verification failed; commonly appears alongside ORA-01188.
  • ORA-01110 — Signals the specific datafile involved; always check this first.
  • ORA-01190 — Datafile is from before the last RESETLOGS; often co-occurs during incomplete recovery.
  • ORA-12714 — Invalid national character set; relevant in NCHAR/NVARCHAR2 environments.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)