Oct 1, 2013

Export or Import - Remap / overwrite schema using impdp

Export or Import - Remap / overwrite schema using impdp
~ Taking full backup using expdp ( RAC / Non-RAC) in any platform
~ Import to: existing environment of version above 10.2.x.x in any platform

About Document:
Some times management ask to import into a dumy schema ( may be frequently) in  pre-prod/ UAT / DEV database environment to test some scenarios/ to train users / patch movement.

you can follow the below method:
--Remap schema
===========
Requirement: To import specific schema from Full dump to single dummy schema:
HR: scource schema
HR_DUMMY: Target schema
-- To take backup
expdp directory=data_pump dumpfile=oraprod_full.dmp logfile=oraprodfull_exp.log full=y exclude=statistics

-- To import with remap schema
impdp DIRECTORY=data_pump DUMPFILE=oraprod_full.dmp LOGFILE=oraprod_hr_imp.log schemas=HR  REMAP_SCHEMA=HR:HR_DUMMY

Note:
1) 'REMAP_SCHEMA' attribute can be used to overwrite the schema in exting database while import.

Import a single table data with remap option from full backup dump using "impdp":

See sample example:

impdp
full=N
directory=data_pump
dumpfile=PROD_full_%U.dmp
logfile=PROD_EMP_imp.log
TABLE_EXISTS_ACTION=TRUNCATE
schemas=HR
TABLES=HR.EMP
REMAP_TABLE=HR.EMP:EMP_NEW

Note: Assume full backup table using parallelism. If you want to truncate the existing data of your EMP_NEW table then use "TABLE_EXISTS_ACTION=TRUNCATE" only.

-- Find currently export executing jobs
sql> select * from dba_datapump_jobs where state='EXECUTING';

Note: Before any import activity with remap schema, exting schema backup should be taken to avoid if any object is required for reference.

Hope this can help you.
Thanks

No comments:

Post a Comment

Translate >>