impdp REMAP_DATAFILE Parameter

I was asked to copy one database into another, which I usually do by copying the datafiles then recreating the control file. However, on this occasion, although both databases were on Oracle 11.2.0.4, the machine hosting the input database was X86 whereas the machine hosting the output database was Sparc. Data Pump seemed to be a better option.

 

I created the output database using dbca, which set up the SYSTEM, SYSAUX, TEMP, UNDOTBS1 and USERS tablespaces.

 

The datafiles on the input database had names beginning with /database/PQEPERF but the output database needed to have filenames starting with /cogdbase/pqecog2. When I do a full expdp / impdp, I precreate the tablespaces in the output database if the file systems have different names. In the past, I have extracted the tablespace creation DDL from the input database. Then I have done a global edit on the resultant SQL to change the file names to match the file system name(s) used by the output database. I could not find my SQL to do this so I decided to try something else.

 

I ran the following SQL on the input database to extract the datafile names. I'm sorry the printout is so small and has a mixture of fonts but if you copy and paste it into a text file, it should look better:

SQL> @remap_datafile

SQL> set echo on

SQL> spool remap_datafile

SQL> set feedback off

SQL> set lines 200

SQL> set pages 0

SQL> set trimspool on

SQL> select 'REMAP_DATAFILE="'''||file_name||''':''+'||file_name||'''"'

  2  from dba_data_files

  3  where tablespace_name not in

  4  ('SYSAUX','SYSTEM','UNDOTBS1','USERS')

  5  /

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/costing_data1.dbf':'+/database/PQEPERF/pqe_data/costing_data1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/costing_idx1.dbf':'+/database/PQEPERF/pqe_data/costing_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/costing_idx2.dbf':'+/database/PQEPERF/pqe_data/costing_idx2.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/datemove.dbf':'+/database/PQEPERF/pqe_data/datemove.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/event_monitor_data.dbf':'+/database/PQEPERF/pqe_data/event_monitor_data.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/event_monitor_index.dbf':'+/database/PQEPERF/pqe_data/event_monitor_index.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/mislog_data.dbf':'+/database/PQEPERF/pqe_data/mislog_data.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_a.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_b.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_c.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_d.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_e.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_f.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_f.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_g.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_g.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_h.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_h.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_i.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_i.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_idx1_a.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_idx1_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/slow_growth.dbf':'+/database/PQEPERF/pqe_data/slow_growth.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/slow_growth_idx1.dbf':'+/database/PQEPERF/pqe_data/slow_growth_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/stand.dbf':'+/database/PQEPERF/pqe_data/stand.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/stand_idx1.dbf':'+/database/PQEPERF/pqe_data/stand_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/static.dbf':'+/database/PQEPERF/pqe_data/static.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/static_idx1.dbf':'+/database/PQEPERF/pqe_data/static_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/tfe_indexes.dbf':'+/database/PQEPERF/pqe_data/tfe_indexes.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/transient.dbf':'+/database/PQEPERF/pqe_data/transient.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cai_data.dbf':'+/database/PQEPERF/pqe_data/cai_data.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_idx1_b.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_idx1_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/transient_idx1.dbf':'+/database/PQEPERF/pqe_data/transient_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/mtdconsumption_a.dbf':'+/database/PQEPERF/pqe_data/mtdconsumption_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_j.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_j.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_a.dbf':'+/database/PQEPERF/pqe_data/cons_part_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_b.dbf':'+/database/PQEPERF/pqe_data/cons_part_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_c.dbf':'+/database/PQEPERF/pqe_data/cons_part_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_a.dbf':'+/database/PQEPERF/pqe_data/cons_part_idx_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_a.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_b.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_c.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_d.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_a.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_idx_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_d.dbf':'+/database/PQEPERF/pqe_data/cons_part_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_e.dbf':'+/database/PQEPERF/pqe_data/cons_part_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_e.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_b.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_idx_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_c.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_idx_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_d.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_idx_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_e.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_idx_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_b.dbf':'+/database/PQEPERF/pqe_data/cons_part_idx_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_c.dbf':'+/database/PQEPERF/pqe_data/cons_part_idx_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_d.dbf':'+/database/PQEPERF/pqe_data/cons_part_idx_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_e.dbf':'+/database/PQEPERF/pqe_data/cons_part_idx_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/mislog_data_1.dbf':'+/database/PQEPERF/pqe_data/mislog_data_1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/mislog_data_2.dbf':'+/database/PQEPERF/pqe_data/mislog_data_2.dbf'"

SQL> spool off

SQL>

Then I opened the SPOOL file with vi and ran the following global edit:

:%s?+/database/PQEPERF?/cogdbase/pqecog2?g

I then appended the SPOOL file onto the parfile I was going to use for the impdp so it looked like this:

CONTENT=ALL

DIRECTORY=ANDREWS_DATAPUMP

DUMPFILE=pqeperf.dmp

FULL=Y

LOGFILE=pqeperf.imp.log

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/costing_data1.dbf':'/cogdbase/pqecog2/pqe_data/costing_data1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/costing_idx1.dbf':'/cogdbase/pqecog2/pqe_data/costing_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/costing_idx2.dbf':'/cogdbase/pqecog2/pqe_data/costing_idx2.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/datemove.dbf':'/cogdbase/pqecog2/pqe_data/datemove.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/event_monitor_data.dbf':'/cogdbase/pqecog2/pqe_data/event_monitor_data.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/event_monitor_index.dbf':'/cogdbase/pqecog2/pqe_data/event_monitor_index.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/mislog_data.dbf':'/cogdbase/pqecog2/pqe_data/mislog_data.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_a.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_b.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_c.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_d.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_e.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_f.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_f.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_g.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_g.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_h.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_h.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_i.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_i.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_idx1_a.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_idx1_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/slow_growth.dbf':'/cogdbase/pqecog2/pqe_data/slow_growth.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/slow_growth_idx1.dbf':'/cogdbase/pqecog2/pqe_data/slow_growth_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/stand.dbf':'/cogdbase/pqecog2/pqe_data/stand.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/stand_idx1.dbf':'/cogdbase/pqecog2/pqe_data/stand_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/static.dbf':'/cogdbase/pqecog2/pqe_data/static.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/static_idx1.dbf':'/cogdbase/pqecog2/pqe_data/static_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/tfe_indexes.dbf':'/cogdbase/pqecog2/pqe_data/tfe_indexes.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/transient.dbf':'/cogdbase/pqecog2/pqe_data/transient.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cai_data.dbf':'/cogdbase/pqecog2/pqe_data/cai_data.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_idx1_b.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_idx1_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/transient_idx1.dbf':'/cogdbase/pqecog2/pqe_data/transient_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/mtdconsumption_a.dbf':'/cogdbase/pqecog2/pqe_data/mtdconsumption_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_j.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_j.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_a.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_b.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_c.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_a.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_idx_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_a.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_b.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_c.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_d.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_a.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_idx_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_d.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_e.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_e.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_b.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_idx_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_c.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_idx_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_d.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_idx_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_e.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_idx_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_b.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_idx_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_c.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_idx_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_d.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_idx_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_e.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_idx_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/mislog_data_1.dbf':'/cogdbase/pqecog2/pqe_data/mislog_data_1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/mislog_data_2.dbf':'/cogdbase/pqecog2/pqe_data/mislog_data_2.dbf'"

I kicked off the impdp. It has been running for an hour or so but will not finish for a long time. However, looking at the output database, several tablespaces have already been created and the REMAP_DATAFILE parameters seem to be working as expected: 

SQL> l

  1  select tablespace_name, file_name

  2  from dba_data_files

  3* order by 1,2

SQL> /

 

TABLESPACE_NAME      FILE_NAME

-------------------- --------------------------------------------------

COSTING_DATA         /cogdbase/pqecog2/pqe_data/costing_data1.dbf

COSTING_IDX1         /cogdbase/pqecog2/pqe_data/costing_idx1.dbf

COSTING_IDX1         /cogdbase/pqecog2/pqe_data/costing_idx2.dbf

DATEMOVE             /cogdbase/pqecog2/pqe_data/datemove.dbf

EVENT_MONITOR_DATA   /cogdbase/pqecog2/pqe_data/event_monitor_data.dbf

EVENT_MONITOR_INDEX  /cogdbase/pqecog2/pqe_data/event_monitor_index.dbf

MISLOG_DATA          /cogdbase/pqecog2/pqe_data/mislog_data.dbf

MISLOG_DATA          /cogdbase/pqecog2/pqe_data/mislog_data_1.dbf

MISLOG_DATA          /cogdbase/pqecog2/pqe_data/mislog_data_2.dbf

SYSAUX               /cogdbase/pqecog2/pqe_system/sysaux01.dbf

SYSTEM               /cogdbase/pqecog2/pqe_system/system01.dbf

UNDOTBS1             /cogdbase/pqecog2/pqe_system/undotbs01.dbf

USERS                /cogdbase/pqecog2/pqe_system/users01.dbf

 

13 rows selected.

 

SQL>

6th December 2016:
Checked for relevance.
Shared on LinkedIn.