Data Pump Date Format Error: NLS Fix Failed, Used RMAN

data pump import date format error ORA-01843 RMAN fix Oracle 19c

Table of Contents

The Problem: Data Pump Import Fails with Date Format Error

Setting NLS_DATE_FORMAT before impdp is the standard fix for data pump import date format error i.e. ORA-01843 and ORA-01861 during Data Pump imports — but it doesn’t always work. Data Pump spawns its own worker processes and doesn’t always inherit the OS-level environment. When the NLS fix failed in our environment, we moved to RMAN backup and restore to bypass the date format issue entirely. This post documents both the NLS fix attempt and the RMAN path that resolved it, on Oracle 19c.

You’re running a Data Pump import and it crashes with date format errors like '1998-01-01 00:00:00'. You try setting export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' in your shell, but the import still fails.

Error you’re seeing:

ORA-01843: not a valid month
ORA-01861: literal does not match format string

This happens because Data Pump doesn’t always respect environment-level NLS settings, especially when table creation DDLs contain hardcoded timestamp formats.

Solution 1: Export NLS_DATE_FORMAT Correctly

Step 1: Set NLS Parameters at OS Level

Before starting Data Pump import, export these variables:

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS'
export NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS TZR'
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Step 2: Verify Export Settings

echo $NLS_DATE_FORMAT
echo $NLS_LANG

Expected Output:

YYYY-MM-DD HH24:MI:SS
AMERICAN_AMERICA.AL32UTF8

Step 3: Run Data Pump Import

impdp username/password@database \
directory=dpump_dir \
dumpfile=export.dmp \
logfile=import.log \
full=y

Solution 2: Session-Level NLS_DATE_FORMAT (SQL*Plus)

If you’re using SQL*Plus or running PL/SQL scripts:

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS';

-- Verify settings
SELECT VALUE FROM NLS_SESSION_PARAMETERS 
WHERE PARAMETER='NLS_DATE_FORMAT';

Output:

VALUE
--------------------------------
YYYY-MM-DD HH24:MI:SS

Why NLS_DATE_FORMAT Export Sometimes Fails

Data Pump import processes run in their own session context and may not inherit environment variables correctly, especially when:

DDL contains hardcoded formats in table creation statements
Import runs as a different OS user (oracle vs. root)
SSH/remote connections don’t propagate environment variables
Database NLS parameters override session settings

Solution 3: RMAN Backup/Restore Approach (When NLS Export Fails)

When export NLS_DATE_FORMAT doesn’t resolve the issue, use RMAN for a complete database copy. This bypasses date format issues entirely.

Step 1: Prepare Source Database Backup

Login to source database server:

mkdir -p /u02/backup/SOURCEDB

Connect to RMAN:

rman target /

Verify database status:

SQL> SELECT open_mode, database_role FROM v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

Step 2: Create Full RMAN Backup

Adjust channels based on available CPU/disk:

RUN {
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK 
    FORMAT '/u02/backup/SOURCEDB/full_db_%U.bkp';
  ALLOCATE CHANNEL c2 DEVICE TYPE DISK 
    FORMAT '/u02/backup/SOURCEDB/full_db_%U.bkp';
  ALLOCATE CHANNEL c3 DEVICE TYPE DISK 
    FORMAT '/u02/backup/SOURCEDB/full_db_%U.bkp';
  
  BACKUP AS COMPRESSED BACKUPSET 
    DATABASE PLUS ARCHIVELOG 
    TAG 'MIGRATION_BACKUP';
  
  BACKUP CURRENT CONTROLFILE 
    FORMAT '/u02/backup/SOURCEDB/controlfile.bkp';
  
  BACKUP SPFILE 
    FORMAT '/u02/backup/SOURCEDB/spfile.bkp';
  
  RELEASE CHANNEL c1;
  RELEASE CHANNEL c2;
  RELEASE CHANNEL c3;
}

Expected Output:

channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oradata/SOURCEDB/system01.dbf
input datafile file number=00003 name=/u01/oradata/SOURCEDB/sysaux01.dbf
channel c1: finished piece 1 at 14-FEB-25
piece handle=/u02/backup/SOURCEDB/full_db_0a1b2c3d_1_1.bkp

Step 3: Copy Backup to Target Server

Using scp:

scp -r /u02/backup/SOURCEDB/* oracle@target-server:/u02/restore/TARGETDB/

Or using rsync (faster for large backups):

rsync -avzP /u02/backup/SOURCEDB/ oracle@target-server:/u02/restore/TARGETDB/

Step 4: Prepare Target Database

Shutdown existing database (if running):

RMAN> SHUTDOWN IMMEDIATE;

database closed
database dismounted
Oracle instance shut down

Start in NOMOUNT mode:

RMAN> STARTUP NOMOUNT;

connected to target database (not started)
Oracle instance started
Total System Global Area    1073741824 bytes

Step 5: Restore Control File

RMAN> RESTORE CONTROLFILE FROM '/u02/restore/TARGETDB/controlfile.bkp';

Starting restore at 14-FEB-25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: restored control file from backup piece
Finished restore at 14-FEB-25

Mount the database:

RMAN> ALTER DATABASE MOUNT;

database mounted

Step 6: Catalog Backup Files

This is critical – Tell RMAN where backup files are located on target server:

RMAN> CATALOG START WITH '/u02/restore/TARGETDB/';

searching for all files that match the pattern /u02/restore/TARGETDB/
List of Files Unknown to the Database
=====================================
File Name: /u02/restore/TARGETDB/full_db_0a1b2c3d_1_1.bkp
File Name: /u02/restore/TARGETDB/controlfile.bkp

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files...
cataloging done

Step 7: Restore and Recover Database

Check available archive logs:

RMAN> SELECT THREAD#, SEQUENCE#, 
       TO_CHAR(FIRST_TIME,'YYYY-MM-DD HH24:MI:SS') FIRST_TIME,
       TO_CHAR(NEXT_TIME,'YYYY-MM-DD HH24:MI:SS') NEXT_TIME
FROM V$ARCHIVED_LOG 
ORDER BY SEQUENCE# DESC 
FETCH FIRST 10 ROWS ONLY;

THREAD#  SEQUENCE# FIRST_TIME          NEXT_TIME
-------- --------- ------------------- -------------------
       1       125 2025-02-14 11:45:30 2025-02-14 11:50:15
       1       124 2025-02-14 11:40:20 2025-02-14 11:45:30

Restore database:

RMAN> RESTORE DATABASE;

Starting restore at 14-FEB-25
channel ORA_DISK_1: restoring datafile 00001
channel ORA_DISK_1: restore complete, elapsed time: 00:15:30
Finished restore at 14-FEB-25

Recover to specific point (optional):

RMAN> RECOVER DATABASE UNTIL TIME 
  "TO_DATE('2025-02-14 12:00:00','YYYY-MM-DD HH24:MI:SS')";

Starting recover at 14-FEB-25
applying archived logs
archived log file name=/u02/restore/TARGETDB/arch_125.log
recovery complete
Finished recover at 14-FEB-25

Or recover to latest available:

RMAN> RECOVER DATABASE;

Step 8: Open Database with RESETLOGS

RMAN> ALTER DATABASE OPEN RESETLOGS;

database opened

Verify database status:

SQL> SELECT name, open_mode, log_mode FROM v$database;

NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
TARGETDB  READ WRITE           ARCHIVELOG

Best Practices for NLS_DATE_FORMAT Export

1. Make NLS Settings Permanent

Add to .bash_profile:

vi ~/.bash_profile

# Add these lines:
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

# Save and source:
source ~/.bash_profile

2. Verify Before Every Import

# Quick verification script
echo "NLS_DATE_FORMAT: $NLS_DATE_FORMAT"
echo "NLS_LANG: $NLS_LANG"
sqlplus -s / as sysdba <<EOF
SELECT parameter, value FROM nls_session_parameters 
WHERE parameter IN ('NLS_DATE_FORMAT','NLS_LANGUAGE','NLS_TERRITORY');
EXIT
EOF

3. Use Parameter Files for Data Pump

Create import.par:

username/password@database
directory=dpump_dir
dumpfile=export.dmp
logfile=import.log
full=y

Run import:

impdp parfile=import.par

Troubleshooting NLS_DATE_FORMAT Export Issues

Issue 1: Export Command Not Working

Symptom: export NLS_DATE_FORMAT doesn’t change date format

Solution:

# Check if variable is set
env | grep NLS

# If empty, ensure no typos:
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'  # Correct
export NLS_DATE_FROMAT='YYYY-MM-DD HH24:MI:SS'  # Wrong (typo)

Issue 2: Import Still Fails After Export

Symptom: Data Pump import fails even after correct export

Causes:

Running impdp as different user (root instead of oracle)
SSH connection doesn’t propagate environment
Database init parameter overrides session settings

Solution:

# Run as correct user:
su - oracle
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
impdp ...

# OR use RMAN method (Solution 3 above)

Issue 3: Date Format Works in SQL*Plus But Not Data Pump

Symptom: ALTER SESSION SET NLS_DATE_FORMAT works in SQL*Plus but not in import

Solution: Data Pump spawns separate worker processes. Use RMAN restore method to avoid the issue entirely.

When to Use Each Solution

Scenario	Best Solution
Simple date format mismatch	Export NLS_DATE_FORMAT
Session-level quick fix	ALTER SESSION SET
Persistent export failures	RMAN backup/restore
Large database migration	RMAN backup/restore
DDL has hardcoded formats	RMAN backup/restore
Testing in dev environment	Export NLS_DATE_FORMAT

Post-Migration Validation

1. Verify Date Formats

-- Check date columns
SELECT table_name, column_name, data_type 
FROM user_tab_columns 
WHERE data_type IN ('DATE','TIMESTAMP');

-- Test date display
SELECT SYSDATE, 
       TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') formatted_date
FROM dual;

2. Validate Data Integrity

-- Compare row counts
SELECT 'SOURCE' db, COUNT(*) FROM source_table@dblink
UNION ALL
SELECT 'TARGET' db, COUNT(*) FROM target_table;

-- Check sample data
SELECT * FROM target_table WHERE ROWNUM <= 10;

3. Test Application Connectivity

# Test TNS connection
tnsping TARGETDB

# Test SQL*Plus connection
sqlplus user/pass@TARGETDB

Common Errors and Quick Fixes

ORA-01843: not a valid month

Cause: Date format mismatch between export and import

Fix:

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

ORA-01861: literal does not match format string

Cause: Hardcoded date literals in DDL don’t match NLS settings

Fix: Use RMAN restore method (bypasses DDL parsing)

ORA-12154: TNS:could not resolve the connect identifier

Cause: Target database TNS entry missing or incorrect