
Introduction
Data Pump Import Date Format Issues can bring database migrations to a halt, particularly when dealing with timestamp formats in table creation SQLs like ‘1998-01-01 00:00:00’. Usually this got resolved using setting up NLS_DATE_FORMAT, however, this traditional NLS_DATE_FORMAT solutions might fail, this guide presents a practical RMAN (Recovery Manager) approach to resolve these Data Pump import challenges in Oracle Database 19c
The Challenge: Date Format Incompatibility During Import
Many DBAs encounter date format conflicts when importing data, particularly when dealing with timestamps in the format ‘1998-01-01 00:00:00’. While setting NLS_DATE_FORMAT environment variables seems like a straightforward solution, it often fails to resolve the issue completely.
Common Attempted Solutions
- Setting NLS_DATE_FORMAT:
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
- Configuring multiple NLS parameters:
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS'
export NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS TZR'
- Session-level modifications:
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
The RMAN Solution: A Better Approach
Instead of struggling with date format issues, using RMAN for a full backup and restore provides a more reliable solution. Here’s the step-by-step process:
Step 1: Prepare for Backup
Login to Source database server (or VM). Create a folder on this VM “/u02/backup/YOUR_DB_NAME
“. You may opt to any mount point where you have enough storage to take backup.
- Connect to RMAN:
RMAN TARGET /
- Verify database status:
SQL> SELECT open_mode, database_role FROM v$database;
Step 2: Create Full Backup
No of channels should be adjusted based on resources like CPU available on the target machine.
RMAN> RUN {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT '/u02/backup/YOUR_DB_NAME/full_db_%U.bkp';
ALLOCATE CHANNEL c2 DEVICE TYPE DISK FORMAT '/u02/backup/YOUR_DB_NAME/full_db_%U.bkp';
ALLOCATE CHANNEL c3 DEVICE TYPE DISK FORMAT '/u02/backup/YOUR_DB_NAME/full_db_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG TAG 'BACKUP_FOR_RESTORE';
BACKUP CURRENT CONTROLFILE FORMAT '/u02/backup/YOUR_DB_NAME/controlfile.bkp';
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
}
Step 3: Copy backup to Target machine
You may also backup spfile if needed.
RMAN> BACKUP SPFILE FORMAT '/u02/backup/YOUR_DB_NAME/spfile.bkp'
;
Step 4: Copy backup to Target machine
This backup to be copied to the target machine in a folder “/u02/backup/YOUR_TARGET_PATH
” You may use scp or WinScp or any third party tool to copy this.
Step 5: Prepare Target DB
Target DB to be prepared for this restore. For this document purpose, I am assuming you have a DB with name cdb1 which is already up and running you wanted to refresh this using IMPDP (Data Pump) command which failed because of Date format issue.
Hence, before proceed, you need to shutdown this target database.
Step 6: Shutdown Target DB, if already up
Shut down the database:
RMAN> SHUTDOWN IMMEDIATE;
Step 6: Restore and Recover
- Start instance in nomount state:
RMAN> STARTUP NOMOUNT;
- Restore control file :
RMAN> RESTORE CONTROLFILE FROM '/u02/backup/YOUR_TARGET_PATH/controlfile.bkp';
RMAN> ALTER DATABASE MOUNT;
Step 7: Make RMAN path as per Target DB
With the restoration of controlfile from source DB, it copied every environmental thing which is having set at source DB. For example, the backup path. It is set as “/u02/backup/YOUR_DB_NAME
” on source, so on target also it is set as the same. However, your target DB backup path (where you copied backup file from source) can be different as in our case i.e. “/u02/backup/YOUR_TARGET_PATH
“.
RMAN will not detect automatically this change. You need to bring in this into RMAN. Let’s do this. you need to provide base path of the target DB.
RMAN> CATALOG START WITH '/u02/backup/YOUR_TARGET_PATH'
- Recover until specific time:
Run this query to get time until to recover:
RMAN> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#, 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# asc;
Pick a timeline from this query for which we want to recover. For this blog purpose, I give a timeline.
RMAN> RECOVER DATABASE UNTIL TIME "TO_DATE('2025-02-13 12:10:36','YYYY-MM-DD HH24:MI:SS')";
Step 4: Open Database with Resetlogs
Once recovery is completed, you may try opening database with RESETLOGS.
RMAN> ALTER DATABASE OPEN RESETLOGS;
Best Practices and Considerations
- Backup Verification
- Always verify backup completion
- Check backup pieces are intact
- Validate backup metadata
- Recovery Time Objectives
- Plan maintenance window carefully
- Consider database size for runtime estimation
- Test procedure in development environment first
- Post-Recovery Steps
- Verify database consistency
- Check application connectivity
- Validate data integrity
Troubleshooting Common Issues
- Backup Failures
- Insufficient disk space
- Invalid backup location permissions
- Network connectivity issues
- Recovery Challenges
- Missing archive logs
- Incomplete backup sets
- Incompatible restore points
Conclusion
While date format issues during imports can be frustrating, RMAN provides a robust alternative solution. This approach not only resolves the immediate problem but also ensures data integrity and provides a reliable recovery mechanism.
Additional Resources
- Oracle 19c Documentation
- RMAN Backup and Recovery User’s Guide
- Oracle Support Notes on Database Recovery