Description:-
In this article we are going to see the Oracle 18c Datapump- REMAP_DATAFILE
- This is a very useful feature when you move databases between platforms that have different file naming conventions.
- This parameter changes the source datafile name to the target datafile name in all SQL statements where the source datafile is referenced. Because the REMAP_DATAFILE value uses quotation marks, it’s best to specify the parameter within a parameter file.
- If you move the data between two databases which have different directory structures, Data Pump throws an error while creating the tablespace of the datafile with a different directory. For this, you can use the remap_datafile parameter which helps you to change the datafile name during import.Let see demo,
Export the FULL database
expdp hari/oracle FULL=YES directory=TEST_DIR DUMPFILE=hari.dmp logfile=hari.log
Copy the dumpfile to the target database,
[oracle@18c empdata]$ scp hari_data.dmp oracle@192.168.1.105:/u01/empdata/
Normally in impdp, sqlfile has DDL statements from the export dumpfile.Here we have used REMAP_DATAFILE parameter to change the datafile path and naming conventions in sqlfile.
[oracle@test18c empdata]$ impdp system/oracle DIRECTORY=TESTDIR DUMPFILE=hari.dmp LOGFILE=dbfull.log sqlfile=expdp_dbfull.sql FULL=Y remap_datafile='/u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf':'/u01/app/oracle/oradata/TEST18C/datafile/dbwrtbs02.dbf' Import: Release 18.0.0.0.0 - Production on Fri Aug 24 14:44:37 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_SQL_FILE_FULL_01": system/******** DIRECTORY=TESTDIR DUMPFILE=hari.dmp LOGFILE=dbfull.log sqlfile=expdp_dbfull.sql FULL=Y remap_datafile=/u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf:/u01/app/oracle/oradata/TEST18C/datafile/dbwrtbs02.dbf Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/TABLESPACE Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SCHEMA/USER Processing object type DATABASE_EXPORT/RADM_FPTM Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA Processing object type DATABASE_EXPORT/RESOURCE_COST Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS Processing object type DATABASE_EXPORT/STATISTICS/MARKER Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at Fri Aug 24 14:44:47 2018 elapsed 0 00:00:08
After impdp,open the sqlfile and check the DDL statement for the given datafile.
Data Pump impdp operation automatically changed the datafile name for the dbwrtbs02.dbf file.
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
