Wednesday, January 5, 2011

EXPDP, IMPDP with Network Link

Network link option could be used in export/import pump to get the data from a remote database server. In the case of impdp it could be used to directly import from DB to DB without an intermediary dump file.

1. Create a TNS entry on the local database tnsnames.ora file for the remote database and create a database link using the TNS entry.
REMOTELOC =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = remote-loc)(PORT = 1521))
       (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SID = remote-db)
     )
   )
Create the database link under the user that will run the expdp or impdp. If the user cannot find the database link following error will be shown
ORA-39001: invalid argument value
ORA-39200: Link name "remoteloc" is invalid.
ORA-02019: connection description for remote database not found
SQL> create database link remoteloc connect to scott identified by tiger using 'REMOTELOC'
2. Specify the database link on the impdp or expdp commands.
impdp
The import will be run on the local database server and it will import the remote database's scott schema directly into the local database. There won't be any dumpfile generated in this case.
impdp scott/tiger directory=dumpdir logfile=impi.log network_link=remoteloc schemas=scott

Import: Release 11.2.0.1.0 - Production on Wed Jan 5 15:53:33 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_SCHEMA_01":  scott/******** directory=dumpdir logfile=impi.log network_link=remoteloc schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "SCOTT"."DEPT"                                   4 rows
. . imported "SCOTT"."EMP"                                   14 rows
. . imported "SCOTT"."SALGRADE"                               5 rows
. . imported "SCOTT"."BONUS"                                  0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 15:54:19
In this case the "SCOTT"."SYS_IMPORT_SCHEMA_01" meta table is created in the local DB's importing schema not on the remote schema.




expdp
This will export the remote database's scott schema into the local database server's dump file directory.
expdp scott/tiger directory=dumpdir logfile=impi.log network_link=remoteloc schemas=scott dumpfile=newscot.dmp

Export: Release 11.2.0.1.0 - Production on Wed Jan 5 15:58:30 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=dumpdir logfile=impi.log network_link=remoteloc schemas=scott
dumpfile=newscot.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
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/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/newscot.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:59:08
Posted by at
Labels: , , , , ,