August 03, 2013

Network Level Import of Schema

How to take Schema Import at Network Level

The NETWORK_LINK parameter initiates a network import. This means that the impdp client initiates the import request, typically to the local database. That server contacts the remote source database referenced by the database link in the NETWORK_LINK parameter, retrieves the data, and writes it directly back to the target database. There are no dump files involved.

1. Create a TNS entry on the local database tnsnames.ora file for the remote database
Create a TNS entry on the local database tnsnames.ora file for the remote database and create a database link using the TNS entry. The user used in the database link must have read/write permission on the remote servers directory.

SOURCE_DATABASE_LINK =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = remote-loc)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SID = remote-db)
   )
 )

2. Create the Database Link on the Destination database:

create database link SOURCE_DATABASE_LINK connect to SYSTEM identified by password using 'SOURCE_DATABASE_LINK';

3. Script to get database link information:

$ sqlplus "/as sysdba"

DBLINKS DETAILS
---------------
set pages 50000 lines 32767
col owner for a15
col username for a10
col host for a20
col created for a20

COL DB_LINK FORMAT A30

select owner, db_link, username, host, to_char(created,'dd-mon-yyyy hh24:mi:ss') CREATED from DBA_DB_LINKS order by owner, db_link;

OWNER      DB_LINK                        USERNAME HOST    CREATED
---------- ------------------------------ -------- ------- -------

4. PAR file.
$vi schema_network_level_import.par

user_id="/ as sysdba"
directory=DATA_PUMP_DIR
logfile=schema_network_level_import.log
schemas=schema1,schema2
NETWORK_LINK=source_database_link
FLASHBACK_TIME=SYSTIMESTAMP
exclude=statistics
parallel=6
cluster=n

:wq

$nohup impdp parfile=schema_network_level_import.par &

$tail -f nohup.out

$tail -f schema_network_level_import.log

Optional Parametrs:
------------------

ESTIMATE={BLOCKS | STATISTICS}
FLASHBACK_SCN=scn_number
FLASHBACK_TIME="TO_TIMESTAMP()"

The Import ESTIMATE parameter is valid only if the NETWORK_LINK parameter is also specified.

The FLASHBACK_SCN parameter is valid only when the NETWORK_LINK parameter is also specified. This is because the value is passed to the source system to provide an SCN-consistent data extraction.

This parameter is valid only when the NETWORK_LINK parameter is also specified. This is because the value is passed to the source system to provide a time-consistent import.

FLASHBACK_SCN and FLASHBACK_TIME are mutually exclusive.

No comments:

Post a Comment