Home » RDBMS Server » Server Utilities » Export data using expdp to remote host (Oracle 11g, windows)
Export data using expdp to remote host [message #556550] Tue, 05 June 2012 04:07 Go to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi,

I have to servers 'A' and 'B', On Server there is a schema with the name "test" having a table "t1". I want to import this t1 table to server B.

Is it possible to export dump using expdp to remote host.

After some googling I found that there is an option for this like "network_link". for testing this, I created a dblink from Server "B" to "A" named "vxmldb".

When I am using the below command on Server B there I am getting the following error.


C:\>expdp directory=data_pump_dir logfile=test.log network_link=vxmldb schemas=test dumpfile=test.dmp

Export: Release 11.1.0.6.0 - 64bit Production on Tuesday, 05 June, 2012 14:22:07

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

Username: system/vxmldb@vxmldb

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39170: Schema expression 'TEST' does not correspond to any schemas.


In above command

directory ---> Server "B" location
network_link ----- > dblink name which is created on Server "B" to access Server "A"
schemas ------ > schema name which is to be exported . Exists on Server "A" DB
username/password ---- >> higher level username/password for Server "A".
@connectString ----- >> connecting to Server "A"



So Please tell me where I am wrong and if it is possible then how.


Thanks in Advance

Pradeep Sharma

Report message to a moderator

Re: Export data using expdp to remote host [message #556553 is a reply to message #556550] Tue, 05 June 2012 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
network_link ----- > dblink name which is created on Server "B" to access Server "A"

Quote:
@connectString ----- >> connecting to Server "A"

If you connect on server A how can you use a db link defined on server B?

Use impdp from server B.

Regards
Michel

Report message to a moderator

Re: Export data using expdp to remote host [message #556555 is a reply to message #556553] Tue, 05 June 2012 04:43 Go to previous messageGo to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi Michal,

Thanks for your reply. According to requirement I want to export a schema and dump should be create on remote server not on the server. So if I will use the impdp command it will read a existing dump file (in my case test.dmp). but I want to create/export the dump file.

C:\>expdp directory=data_pump_dir logfile=test.log network_link=vxmldb schemas=test dumpfile=test.dmp

Export: Release 11.1.0.6.0 - 64bit Production on Tuesday, 05 June, 2012 15:05:48

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

Username: system/************

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39170: Schema expression 'TEST' does not correspond to any schemas.


kindly clear me if I am wrong anywhere.


Thanks Again

Pradeep

Report message to a moderator

Re: Export data using expdp to remote host [message #556559 is a reply to message #556555] Tue, 05 June 2012 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
So if I will use the impdp command it will read a existing dump file


impdp through a db link does not create any dump file, it directly import the remote schema inside the local database.

If you want to create a dump file on server B, you must connect on database on server B and access database on server A using the db link.

Regards
Michel

Report message to a moderator

Re: Export data using expdp to remote host [message #556561 is a reply to message #556559] Tue, 05 June 2012 05:40 Go to previous messageGo to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi Michal,

Quote:
If you want to create a dump file on server B, you must connect on database on server B and access database on server A using the db link.


Quote:
must connect on database on server B and access database on server A


confusing me ..... !!! please clear with example



Thanks

Pradeep

Report message to a moderator

Re: Export data using expdp to remote host [message #556563 is a reply to message #556561] Tue, 05 June 2012 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
On server B with ORACLE_HOME/ORACLE_SID set to database on B and TWO_TASKS not set (check "env"):
expdp x/y directory=data_pump_dir logfile=test.log network_link=vxmldb schemas=test dumpfile=test.dmp

Regards
Michel

Report message to a moderator

Re: Export data using expdp to remote host [message #556571 is a reply to message #556563] Tue, 05 June 2012 06:56 Go to previous messageGo to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
hi Michal,

According to your solution I am doing the following

Quote:


On server B with ORACLE_HOME/ORACLE_SID set to database on B and TWO_TASKS not set (check "env"):
expdp x/y directory=data_pump_dir logfile=test.log network_link=vxmldb schemas=test dumpfile=test.dmp




Server A (DB running - vxmldb)
Server B (DB running - ncpr)
Server B (Db link name - vxmldb ) point to server A

Step -1 ::   On Server B command prompt

SET ORACLE_SID=vxmldb
SET ORACLE_HOME=D:\app\Administrator\product\11.1.0\db_1


C:\>expdp system/**** directory=data_pump_dir logfile=test.log network_link=vxmldb schemas=test dumpfile=test.dmp

Export: Release 11.1.0.6.0 - 64bit Production on Tuesday, 05 June, 2012 17:18:03

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

UDE-12560: operation generated ORACLE error 12560
ORA-12560: TNS:protocol adapter error


And if I am changing ORACLE_SID=ncpr Then it is throwing the following error 
C:\>expdp system/**** directory=data_pump_dir logfile=test.log network_link=vxmldb schemas=test dumpfile=test.dmp

Export: Release 11.1.0.6.0 - 64bit Production on Tuesday, 05 June, 2012 17:18:48

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39170: Schema expression 'TEST' does not correspond to any schemas.



Please tell me where I am wrong .


Thanks

Pradeep Sharma

Report message to a moderator

Re: Export data using expdp to remote host [message #556574 is a reply to message #556571] Tue, 05 June 2012 07:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Step -1 ::   On Server B command prompt

SET ORACLE_SID=vxmldb
SET ORACLE_HOME=D:\app\Administrator\product\11.1.0\db_1

Wrong, ORACLE_SID should be "ncpr".

Quote:
Please tell me where I am wrong .

Quote:
ORA-39170: Schema expression 'TEST' does not correspond to any schemas


Check your db link points to the correct database.
Check the schema exists in this database.

Regards
Michel

Report message to a moderator

Re: Export data using expdp to remote host [message #556713 is a reply to message #556574] Wed, 06 June 2012 04:45 Go to previous messageGo to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi Michal,

Thanks for your important reply. Its works.

One more thing I want to ask in this aspect that Any point of time, there is some network issue then dblink will not work. so at that time what will happen.


1) database export exit at that location with error.
2) database export (expdp) process wait for the dblink to be up.


Thanks Again.

Pradeep Sharma

Report message to a moderator

Re: Export data using expdp to remote host [message #556715 is a reply to message #556713] Wed, 06 June 2012 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Actually it depends on how your network is configured and which kind of error.
Sometimes it will hang, sometimes it will return an error.

Regards
Michel

Report message to a moderator

Re: Export data using expdp to remote host [message #557267 is a reply to message #556715] Mon, 11 June 2012 07:11 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
hey buddy i did one task its also helpfull you to more clear this

Here we have two database
1. Moon
2. Oral

In moon database we have one user Scott

SQL> conn scott/tiger@moon
SQL> show user
USER is "SCOTT"
SQL> select tname from tab;
TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE


Now do this step on Oral database Here we import all scoot table from moon database to new user new_scott user into oral database




E:\oracle\product\10.2.0\db_1\bin>sqlplus
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 11 15:57:09 2012
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter user-name: sys/oracle@oral as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$database
  2 /
NAME
---------
ORAL

SQL> create user new_scott identified by tiger;

User created.

SQL> grant connect,resource to new_scott;

Grant succeeded.

SQL> grant create any directory to new_scott;

Grant succeeded.

SQL> grant create database link to new_scott;

Grant succeeded.

SQL> conn new_scott/tiger@oral
Connected.

SQL> create or replace directory practice as 'E:\practice';

Directory created.



SQL> create database link old_scott connect to scott identified by tiger using '
moon';

Database link created.
SQL> host impdp new_scott/tiger@oral directory=practice network_link=old_scott remap_schema=scott:new_scott

Report message to a moderator

Re: Export data using expdp to remote host [message #557269 is a reply to message #557267] Mon, 11 June 2012 07:19 Go to previous messageGo to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi shaan121,

thanks for your reply. But this process will import data from source to destination schema.

My Requirement was

Quote:

Is it possible to export dump using expdp to remote host.


As per the suggestion provided by Mr Michal, its done.

Thanks for your reply again.

Pradeep

Report message to a moderator

Re: Export data using expdp to remote host [message #557278 is a reply to message #557267] Mon, 11 June 2012 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
@shaan121

Many errors in your post:
- NEVER use SYS to do something that can be done by another account
- NEVER use predefined roles like RESOURCE or CONNECT
- ...

Regards
Michel

[Updated on: Mon, 11 June 2012 08:25]

Report message to a moderator

Re: Export data using expdp to remote host [message #557374 is a reply to message #557278] Tue, 12 June 2012 04:44 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
michel i just did this create user task with this sys user.but i perform this impdp task with this new user new_scott user

Report message to a moderator

Re: Export data using expdp to remote host [message #557375 is a reply to message #557374] Tue, 12 June 2012 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
i just did this create user task with this sys user


You should not.
SYS is only maintenance purpose or some special work that can't be done by other account.

Regards
Michel

Report message to a moderator

Re: Export data using expdp to remote host [message #557384 is a reply to message #557269] Tue, 12 June 2012 06:36 Go to previous message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
yeah you always welcome pradies

and yeah you can export this dmp file using network_link i just did this task i send you this steps how can i did this

bellow its my steps i just test it and its successful it

Here I have two computer

Name

1. Test 1

2. Test 2

On test 1 I have one database name Star

We perform this following steps on Test 1 pc (Star Database)


SQL> Create user roz identified by roz;

SQL> alter user roz default tablespace users;

SQL>   alter user roz temporary tablespace temp;

SQL>    grant dba to roz;


After that connect to this roz user and create directory name practice 


SQL> connect roz/roz@star

SQL> create or replace directory practice as 'E:\practice';


After that create dblink on this star database in roz user

SQL> create database link ts connect to roz identified by roz using 'star';


Now perform this export task on this test2 pc remotely

before using this you have to create service name star on your test 2 pc

to create service goto START - All Programm - oracle-Oradb10g_home1 -
Configuration and Migration Tools - Net Manager - and add this star service on this test 2 computer

after that export this using this syntax on this test 2 pc remotely

E:\oracle\product\10.2.0\db_1\bin> expdp roz/roz@star directory=practice network_link=ts dumpfile=test.dmp


Now goto this test 1 pc and open goto this E drive and open this practice folder you can find this test.dmp file there

Report message to a moderator

Previous Topic: Import schema into a newly created user
Next Topic: Concatenate timestamp to a constant value in a control file
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 30 20:20:26 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.