How to Export NLS_DATE_FORMAT in Oracle: Data Pump Import Date Format Fix

Table of Contents

The Problem: Data Pump Import Fails with Date Format Error

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