Saturday, September 20, 2025

ORA-39405 IMPDP Error

Problem

Following Error Occur when try to import oralce export backup using impdp utility

ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 43 into a target database with TSTZ version 32

Cause

Identified that the DST (Daylight Saving Time) versions between the source and target databases are different

Source
SQL> SELECT version FROM v$timezone_file;
Version
----------
43
Target
SQL> SELECT version FROM v$timezone_file;
Version
----------
32

Solution


Method-1

Re-export from source with compatibility version parameter

expdp system/managerd directory=dump full=Y dumpfile=expdp_full_prod.dmp logfile=expdp_full_prod.log version=19.0.0

Method-2

Upgrade target DB to same TSTZ version

Applying the DSTv43 update for the Oracle Database (Doc ID 3002390.1)

Method-3

Workaround Steps to Overcome the Import Error

  1. Prepare impdp command with job_name option

  2. impdp system/manager directory=dump dumpfile=expdp_full_prod.dmp logfile=eimpdp_full_prod.log job_name=impdp_tzchange
    
  3. Create and execute a script in another terminal that runs in an infinite loop as follows

  4. Linux
     
    $ cat tz.sh
    
    export ORACLE_SID=TEST
    while true
    do
      sqlplus / as sysdba <<EOF
        update sys.IMPDP_TZCHANGE set property = 43 where property = 32;
        commit;
    EOF
    done
    $ ./tz.sh 
    Windows
     
    D:\backup> type tz_update.sql
    
    update sys.IMPDP_TZCHANGE
       set property = 43
     where property = 32;
    commit;
    exit;
    
     
    D:\backup> type tz_loop.bat
    
    @echo off
    :loop
    sqlplus / as sysdba @tz_update.sql
    goto loop
    D:\backup> tz_loop.bat
  5. execute impdp command which prepared in step-1

  6. Once the import job has been succesfully completed, Terminate the script which run as infinite to updating the master table

Friday, September 19, 2025

Rename Pluggable Database (PDB)

  1. Connect to the Sqlplus as SYSDBA

  2. SQL> sho pdbs;
    
    CON_ID CON_NAME OPEN MODE RESTRICTED
    ------ -------- --------- ----------
    2 PDB$SEED    READ ONLY   NO
    
    3 PDBMIG   READ WRITE  NO
    
  3. Shutdown PDB which need to rename

  4.   SQL> alter pluggable database PDBMIG close immediate;
    
  5. Open as restricted mode

  6. SQL> alter pluggable database PDBMIG open restricted;
    
  7. Rename PDB

  8.   SQL> alter pluggable database PDBMIG rename global_name to PDBTEST;
    
  9. Restart PDB

  10.   SQL> alter pluggable database PDBTEST close immediate;
      SQL> alter pluggable database PDBTEST open;
    
  11. Connect to PDB and verify

  12.   SQL> show pdbs;
      SQL> select name, open_mode, restricted from v$pdbs;
    SQL> select name, con_id, dbid,con_uid, guid from v$containers;
    SQL> select service_id, name, network_name, creation_date, pdb, con_id from cdb_services;
    
  13. Change datafile path as per new PDB (Optional)

  14.   SQL> alter database move datafile '/u01/oradata/CDB1/PDBMIG/system01.dbf' to '/u01/oradata/CDB1/PDBTEST/system01.dbf';
    
    For Temp File, Drop and re-create
      SQL> alter database tempfile '/u01/oradata/CDB1/PDBMIG/temp01.dbf' drop including datafiles;
    SQL> alter tablespace TEMP add tempfile '/u01/oradata/CDB1/PDBTEST/temp01.dbf' size 1G reuse;