Impdp over network using Network_link

Applies to:
 
 Oracle Version: 10G and later

Advantages:

This is an alternate and fast approach for importing database from one database to another.
This process can skip following activities:
1.       Creating database directory for backup on source database.
2.       Create dump file on source database.
3.       Copy dump file at destination server.
4.       Creating database directory for backup restoration on Destination database.
5.       Expdp command to take database backup.

Solution:

Suppose we have two databases db1 and db2. We need to restore test_schema1 backup form db1 to db2. 

Prerequisites:

1.       Source and Destination database must be connected through the network.
2.       Both databases must be in open mode.
3.       Source database (db1) tns detail must exist at destination (db2).

Following are the steps:
1.       Crate a public database link at db2 connecting to db1 using system user.

SQL> create public database link db1_link connect to system identified by sys using 'db1';

Database link created.

Crosscheck the db link is working.

SQL> select * from dual@ db1_link;

D
-
X

You may face following error during impdp, if db link is not public.
ORA-39001: invalid argument value
ORA-39200: Link name "fmstest" is invalid.
ORA-02019: connection description for remote database not found
 
2.       Import using Impdp command at db2 (Destination)

[oracle@~]$ export ORACLE_SID=test01
[oracle@~]$ impdp system/sys schemas= test_schema1 network_link= db1_link

Import: Release 11.2.0.2.0 - Production on Fri Jun 17 07:00:57 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** schemas= test_schema1 network_link= db1_link
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
--------------------------------------------------------------------------
-------------------------------------------------------------------------
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed at 07:08:45

Detail of Impdp parameters:

network_link: This parameter will have value of db link created.

Schemas: Name of the schema need to import from source. If user doesn’t exists it will create it, otherwise it import in to existing user with one error.

Here, directory and dumpfile parameters are not required. Other parameters are same as it used to be.

No comments:

Post a Comment