METHOD 1st
Impdp over network using Network_link
(Without expdp)
Applies to:
Oracle Version: 10G and later
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:
Broker and ACE
Suppose we have two databases (db1 is Broker) and (db2
is ACE). We need to restore MYNET 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).
4. Database (db1) (db2) tnsping check.
Go to BROKER database
ro =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ACE)(PORT
= 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sarathi)
)
)
SQL> create user nettest identified by
nettest;
User created.
SQL> grant connect,resource,dba to nettest;
Grant succeeded.
SQL> conn nettest/nettest;
Connected.
SQL> create table mynet(empno number,empname varchar2(20));
Connected.
SQL> create table mynet(empno number,empname varchar2(20));
Table created.
SQL> insert into mynet values(001,'Mohan');
1 row created.
SQL> insert into mynet values(002,'Sohan');
1 row created.
SQL> commit;
Commit complete.
SQL> create table ABC as select * from mynet;
Table created.
SQL> create table CDE select * from ABC;
Table created.
Following are the steps:
1. Crate a public
database link at db2 connecting to db1 using system user.
Go
to ACE database
----- TNS
------
JP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = BROKER)(PORT =
1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = ORCL)
)
)
SQL> conn / as sysdba
Connected.
SQL> create user mynet identified by mynet;
User created.
SQL> grant connect,resource,dba to mynet;
Grant succeeded.
SQL> grant read,write on directory data_pump_dir
to mynet;
Grant succeeded.
SQL> commit;
Commit complete.
SQL> conn mynet/mynet;
Connected.
SQL> select * from tab;
no rows selected
SQL>
create database link useme connect to nettest identified by nettest using ‘JP’;
Database link created.
SQL> commit;
Commit complete.
SQL>
select * from dba_directories where directory_name like ‘DATA_PUMP_DIR%’;
OWNER
DIRECTORY_NAME DIRECTORY_PATH
——————
————————————–----
--------------------------------------------
SYS
DATA_PUMP_DIR
/u01/oracle/product/10.2.0/admin/ace/dpdump/
SQL> select * from tab;
no rows selected
SQL>
SQL>
select * from dba_db_links where owner=’MYNET’;
OWNER DB_LINK USERNAME HOST CREATED
———--- ----------------------------------------------------------------------------- ----------------------- ----------- -------------------
MYNET USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM NETTEST JP 26-AUG-15
[oracle@~]$ impdp
mynet/mynet directory=data_pump_dir
network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=mynet.log
remap_schema=nettest:mynet exclude=TABLE:\"IN \(\'ABC\', \'CDE\'\)\"
Import: Release 10.2.0.1.0 - Production on
Wednesday, 26 August, 2015 12:59:17
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release
10.2.0.1.0 - Production
Starting
"MYNET"."SYS_IMPORT_SCHEMA_01": mynet/******** directory=data_pump_dir
network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=mynet.log
remap_schema=nettest:mynet exclude=TABLE:"IN ('ABC', 'CDE')"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"MYNET"
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
"MYNET"."MYNET" 2 rows
Job
"MYNET"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at
12:59:43
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.
METHOD 2nd
Data pump network_link export and import
10G and >
This post show you, just a easy understanding about Datapump network_link
parameter.How the expdp work and dump
file create on target machine
I have used two site database
Broker and ACE
Step 1:
Go to Broker database
SQL> create user nettest
identified by nettest;
User created.
SQL> grant
connect,resource,dba to nettest;
Grant succeeded.
SQL> conn nettest/nettest;
Connected.
SQL> create table mynet(empno number,empname varchar2(20));
Connected.
SQL> create table mynet(empno number,empname varchar2(20));
Table created.
SQL> insert into mynet
values(001,'Mohan');
1 row created.
SQL> insert into mynet
values(002,'Sohan');
1 row created.
SQL> commit;
Commit complete.
Step 2:Go to ACE database
SQL> conn / as sysdba
Connected.
SQL> create user mynet identified by mynet;
Connected.
SQL> create user mynet identified by mynet;
User created.
SQL> grant
connect,resource,dba to mynet;
Grant succeeded.
SQL> grant read,write on
directory data_pump_dir to mynet;
Grant succeeded.
SQL> commit;
Commit complete.
SQL> conn mynet/mynet;
Connected.
Connected.
SQL> select * from tab;
no rows selected
SQL> create database link
useme connect to nettest identified by nettest using ‘JP’;
Database link created.
SQL> commit;
Commit complete.
SQL> select * from dba_directories where
directory_name like ‘DATA_PUMP_DIR%’;
OWNER
DIRECTORY_NAME DIRECTORY_PATH
——————
————————————–----
--------------------------------------------
SYS
DATA_PUMP_DIR /u01/oracle/product/10.2.0/admin/ace/dpdump/
SQL> select * from tab;
no rows selected
SQL>
SQL> select * from dba_db_links where owner=’MYNET’;
OWNER
DB_LINK
USERNAME HOST
CREATED
———---
----------------------------------------------------------------------------- ----------------------- ----------- -------------------
MYNET
USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM NETTEST JP 26-AUG-15
Step 3:Go to broker db server and execute your export command here, you can
see your broker DB schema (nettest) dump file stored in ACE db server
schema directory (mynet).
$ expdp mynet/mynet@ro directory=data_pump_dir
network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=testnet.log dumpfile=testnet.dmp tables=mynet
OR
expdp mynet/mynet@ro directory=data_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=mynet.log dumpfile=mynet.dmp exclude=TABLE:\"IN \(\'ABC\', \'CDE\'\)\"
Export: Release 10.2.0.1.0 – 64bit
Production on Wednesday, 27 October, 2010 16:
53:52
53:52
Copyright (c) 2003, 2005,
Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release
10.2.0.1.0 – Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting “MYNET”.”SYS_EXPORT_TABLE_01″: mynet/********@ro directory=data
_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=testnet.log
dumpfile=testnet.dmp tables=mynet
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “NETTEST”.”MYNET” 5.25 KB 2 rows
Master table “MYNET”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for MYNET.SYS_EXPORT_TABLE_01 is:
/u01/ORACLE/PRODUCT/10.2.0/ADMIN/ACE/DPDUMP/TESTNET.DMP
Job “MYNET”.”SYS_EXPORT_TABLE_01″ successfully completed at 16:55:02
tion
With the Partitioning, OLAP and Data Mining options
Starting “MYNET”.”SYS_EXPORT_TABLE_01″: mynet/********@ro directory=data
_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=testnet.log
dumpfile=testnet.dmp tables=mynet
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “NETTEST”.”MYNET” 5.25 KB 2 rows
Master table “MYNET”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for MYNET.SYS_EXPORT_TABLE_01 is:
/u01/ORACLE/PRODUCT/10.2.0/ADMIN/ACE/DPDUMP/TESTNET.DMP
Job “MYNET”.”SYS_EXPORT_TABLE_01″ successfully completed at 16:55:02
Export Finished and then If you
need , you can import the dump file from broker db server to ace db
server.
For
import, dumpfile parameter not need.
$ impdp mynet/mynet directory=data_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=testnetimp.log remap_schema=nettest:mynet tables=mynet
$ impdp mynet/mynet directory=data_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=testnetimp.log remap_schema=nettest:mynet tables=mynet
OR
impdp mynet/mynet@ro directory=data_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM dumpfile=mynet.dmp logfile=impnetimp.log remap_schema=nettest:mynet
Import: Release 10.2.0.1.0 - Production on Wednesday, 26 August,
2015 12:59:17
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 -
Production
Starting "MYNET"."SYS_IMPORT_SCHEMA_01": mynet/******** directory=data_pump_dir
network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=mynet.log
remap_schema=nettest:mynet exclude=TABLE:"IN ('ABC', 'CDE')"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"MYNET" 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 "MYNET"."MYNET" 2 rows
Job "MYNET"."SYS_IMPORT_SCHEMA_01" completed
with 1 error(s) at 12:59:43
[Oracle]$
Step 4:
SQL>
conn mynet/mynet
Connected.
SQL> select * from tab;
Connected.
SQL> select * from tab;
TNAME
TABTYPE CLUSTERID
—————————— ——- ———-
MYNET TABLE
—————————— ——- ———-
MYNET TABLE
SQL> select * from mynet;
EMPNO EMPNAME
-------———- --——————–
1 Mohan
2 Sohan
-------———- --——————–
1 Mohan
2 Sohan
SQL>
If
you need to refer about network_link parameter, you can from here
EXPDP/ IMPDP with Network_Link in Version
10G and >, Localy Backup for the remote Database
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'
expdp
This will export the remote database's scott schema into the local database server's dump file directory.
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.2.0 - Production on Wed Jan 5 15:58:30 2015
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, 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
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
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.2.0 - Production on Wed Jan 5 15:53:33 2015
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, 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.
No comments:
Post a Comment