Friday, May 09, 2014

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>

2 comments:

Anonymous said...

It looks like you are performing cross platform migration. What happened to all the users, roles, etc...?

Andrew Reid said...

Yes, it is going from X86 to Solaris. Both the expdp and impdp were done in FULL mode so Oracle took care of the users and roles for me and they are now in the output database.