April 05, 2012

Transportable Tablespace with remap option

There is a lot of way to upgrade the database, in case of upgrading database with huge size i usually use TTS as we know Transportable Tablespace, the method is export the metadata tablespace then copying the read only datafile and finally import the metadata to the destination database
one of my client want to rename the schemas that migrated, and there is an option in datapump to rename the schema to the new one, in case of that we have to create the remap schema
here is the way

--CREATE USER
CREATE USER TTSREMAP
IDENTIFIED BY TTSREMAP
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 2 Roles for TTSREMAP
GRANT EXP_FULL_DATABASE TO TTSREMAP;
GRANT IMP_FULL_DATABASE TO TTSREMAP;
ALTER USER TTSREMAP DEFAULT ROLE ALL;
-- 1 System Privileges for TTSUSER;
GRANT UNLIMITED TABLESPACE TO TTSREMAP;



and here is the import command to remap to the new schema

impdp DUMPFILE=TTS.dmp LOGFILE=TTS.log DIRECTORY=DATAPUMP REMAP_TABLESPACE=TTS:TTSREMAP \
REMAP_SCHEMA=TTSUSER:TTSREMAP TRANSPORT_DATAFILES='/DATA/ASM/DATAFILE/TTS01.dbf'


No comments:

Post a Comment