Monday, June 27, 2011

Schema migratation to remote host using DATAPUMP without creating the DUMP file


1. Create 3 tablespaces on the target database e.g. target_data, target_index, target_logger (or as per the requirment)

2. Create user
CREATE USER target  PROFILE DEFAULT
    IDENTIFIED BY target_pass DEFAULT TABLESPACE "target_DATA"
    TEMPORARY TABLESPACE "TEMP"
    QUOTA UNLIMITED
    ON "target_DATA"
    QUOTA UNLIMITED
    ON "target_INDEX"
    QUOTA UNLIMITED
    ON "target_LOGGER"
    ACCOUNT UNLOCK;
GRANT CONNECT,RESOURCE TO target;

3. create the database directory as sys user. This directory must point to a valid directory on the same server as database

CREATE DIRECTORY dump AS '/u06/oradata/target_db/';

4. GRANT READ,WRITE ON DIRECTORY dump TO target;

5. GRANT CREATE DATABASE LINK TO target;

6. login to the target user and create the database link pointing to the source user.

CREATE DATABASE LINK source_link connect to source identified by source_pass using 'source_db_sid'

7. get to the host and make sure you are connected to right SID (echo $ORACLE_SID) and set the sid accordingly (export ORACLE_SID = target_sid

8. transfer the data from source to target using impdp and link

impdp target/target_pass REMAP_SCHEMA=source:target REMAP_TABLESPACE=source_data:target_data
REMAP_TABLESPACE=source_index:target_index REMAP_TABLESPACE=source_logger:target_LOGGER 
DIRECTORY=dump
NETWORK_LINK=source_link

No comments:

Post a Comment