Data Pump impdp expdp NETWORK_LINK option : Transfer schema across database using db links without dump file!

Using the NETWORK_LINK option you can import the schema from source database to target database. One advantage of this option you don’t need export and import as it does the export and import in single shot from the source to destination. Also, the file system space is not needed to accommodate the huge dump files as we can directly import to target using network_link.
It is very amazing option with data pump. You can take the backup of source database schema from another database and you can store in dump files in target location as well.

See the examples below. Here we have two databases – prod8 (source) and prod9(target)


SQL>  select name from v$database;

NAME
---------
PROD8

SQL> show user
USER is "SCOTT"

SQL> select * from tab;

no rows selected

SQL> create table example_tab1 as select * from all_objects;

Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EXAMPLE_TAB1                   TABLE

I have added a TNS entry (File location: $ORACLE_HOME/network/admin/tnsnames.ora) for prod8 in my prod9 database box. Entry as below:

prod8 =
   (description =
      (address =
         (protocol = tcp)
         (host = devdata.abc.diamond.net)
         (port = 1522)
      )
      (connect_data =
         (server = dedicated)
         (sid = prod8)
      )
   )
Test the connectivity using the tnsping utility

$ tnsping prod8

TNS Ping Utility for Solaris: Version 11.1.0.7.0 - Production on 05-JUL-2011 22:26:12

Copyright (c) 1997, 2008, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (description = (address = (protocol = tcp) (host = devdata.abc.diamond.net) (port = 1522)) (connect_data = (server = dedicated) (sid = prod8)))
OK (20 msec)

Connect to prod9 using sqlplus and create a database link to prod8 with scott user

$ sqlplus

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Jul 5 22:26:20 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Enter user-name: scott/tiger

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create database link prod8 connect to scott identified by scott using 'prod8';

Database link created.

SQL> select * from tab@prod8
  2  ;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EXAMPLE_TAB1                   TABLE

Database link is working and ready from the database prod9 to prod8


Now I am going to import the scott schema of prod8 database to prod9 database without dumpfile. See below

$ impdp scott/tiger directory=exp_dir logfile=impnetworkscott.log network_link=prod8

Import: Release 11.1.0.7.0 - 64bit Production on Tuesday, 05 July, 2011 23:55:37

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_SCHEMA_01":  scott/******** directory=exp_dir logfile=impnetworkscott.log network_link=prod8
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 12 MB
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"."EXAMPLE_TAB1"                      95307 rows
Job "SCOTT"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 23:58:04

Verify whether it is imported or not. Please ignore the error because the schema already exist in the target.

SQL> select name from v$database;

NAME
---------
PROD9

SQL> show user
USER is "SCOTT"

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP1                           TABLE
EMP2                           TABLE
EXAMPLE                        TABLE
EXAMPLE_PARTITION              TABLE
EXAMPLE_TAB1                   TABLE
GT_EMP                         TABLE
TEST                           TABLE

8 rows selected.
Yes. table EXAMPLE_TAB1 has been imported without dumpfile to prod9 database!!!!

Next example is taking the schema export from source database from target machine. You can store the dump in files.

$ expdp scott/tiger directory=exp_dir dumpfile=networkscott.dmp logfile=networkscott.log network_link=prod8

Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 05 July, 2011 23:29:50

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=exp_dir dumpfile=networkscott.dmp logfile=networkscott.log network_link=prod8
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 12 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/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "SCOTT"."EXAMPLE_TAB1"                     9.496 MB   95307 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/scott/networkscott.dmp
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google