EXPDP and IMPDP through
network link:
For EXPDP
1) Create database link on
target database.
myserver01:ABC11TST\sys>
create public database link eqedge_to_gem connect to scott identified by amaresh123 using 'EQEGDEV';
Database
link created.
myserver01:ABC11TST\sys>
select * from dual@eqedge_to_gem;
D
-
X
1) grant EXP_FULL_DATABASE
privs to the source user or db link created user (source database)
[oracle@myserver01 ~]$ export ORACLE_SID=ABCDEV
[oracle@myserver01 ~]$ sysdba
SQL*Plus:
Release 10.2.0.4.0 - Production on Mon Jul 7 22:18:47 2014
Copyright
(c) 1982, 2007, Oracle. All Rights
Reserved.
Connected
to:
Oracle
Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the
Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant
EXP_FULL_DATABASE to scott;
Grant
succeeded.
If we will not give this privs below error will come.
ORA-31631:
privileges are required
ORA-39149:
cannot link privileged user to non-privileged user
Now create a parfile on target
backup location
myserver01:ABC11TST\sys>
create or replace directory DATA_PUMP_DIR as '/orabkup_nonprod/ABC11TST';
Directory
created.
[oracle@myserver01 ABC11TST]$ pwd
/orabkup_nonprod/
[oracle@myserver01 ABC11TST]$
cat par_test_network.par
directory=DATA_PUMP_DIR
dumpfile=IQSONAR_testing.dmp
logfile=IQSONAR_testing.log
JOB_NAME=test123
schemas=IQSONAR
network_link=eqedge_to_gem
now use the expdp to take export
backup.
[oracle@myserver01 ABC11TST]$ expdp parfile=par_test_network.par
Export:
Release 11.1.0.7.0 - 64bit Production on Monday, 07 July, 2014 22:42:10
Copyright
(c) 2003, 2007, Oracle. All rights
reserved.
Username:
aniraj/amaresh23
Connected
to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit
ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing
options
Starting
"ANIRAJ"."TEST123":
aniraj/******** parfile=par_test_network.par
Estimate
in progress using BLOCKS method...
Processing
object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total
estimation using BLOCKS method: 0 KB
Processing
object type SCHEMA_EXPORT/USER
Processing
object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing
object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing
object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Master
table "ANIRAJ"."TEST123" successfully loaded/unloaded
******************************************************************************
Dump
file set for ANIRAJ.TEST123 is:
/orabkup_nonprod/ABC11TST/IQSONAR_testing.dmp
Job
"ANIRAJ"."TEST123" successfully completed at 22:42:24
Check the dump file at target
backup directory location:
[oracle@myserver01 ABC11TST]$ pwd
/orabkup_nonprod/
[oracle@fcdbfrm14 ABC11TST]$ ls -ltr
IQSONAR_testing.dmp IQSONAR_testing.log
-rw-r----- 1 oracle oradba 147456 Jul 7 22:41 IQSONAR_testing.dmp
-rw-r--r-- 1 oracle oradba 1021 Jul
7 22:41 IQSONAR_testing.log
[oracle@fcdbfrm14 ABC11TST]$
For IMPDP
Grant below privs to schema through which you are going to take
expdp:
NOTE: on source database user which is being user
in dblink must have EXP_FULL_DATABASE privs as well if we are going to expdp
for full db or any other schemas of source db.
myserver01:ABC11TST\sys> grant IMP_FULL_DATABASE to aniraj;
Grant
succeeded.
Create parfile for impdp
No dumpfile parameter needed
here to import through network link
[oracle@myserver01 ABC11TST]$ cat impdp_test_network.par
directory=DATA_PUMP_DIR
logfile=impdp_scott_eqd.log
JOB_NAME=test123
schemas=SCOTT
remap_schema=SCOTT:AMARESH
network_link=eqedge_to_gem
[oracle@myserver01 ABC11TST]$ impdp parfile=impdp_test_network.par
Import:
Release 11.1.0.7.0 - 64bit Production on Monday, 07 July, 2014 22:31:04
Copyright
(c) 2003, 2007, Oracle. All rights
reserved.
Username:
aniraj/amaresh123
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
"ANIRAJ"."TEST123":
aniraj/******** parfile=impdp_test_network.par
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
. .
imported "AMARESH"."DEPT" 4 rows
. .
imported "AMARESH"."EMP" 14 rows
. .
imported "AMARESH"."SALGRADE" 5 rows
. .
imported "AMARESH"."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
"ANIRAJ"."TEST123" successfully completed at 22:31:15
select
name from v$database;
NAME
---------
ABC11TST
select
count(*) from dba_tables where owner='AMARESH';
COUNT(*)
--------------------
4
