Home » RDBMS Server » Server Utilities » how to import data over network (windows server 2008, oracle 10g)
how to import data over network [message #624952] Mon, 29 September 2014 06:39 Go to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
i have two database on two different different system

one database is live db

another its test db on another machine.

I Have .dmp file on my live db using expdp.now i want to use this live db .dmp file on my test db for importing some table from this live db .dmp file.. i have create one directory on my live db server i want to use this directory created on live db server on my test db database.i donot want to copy this my live db dmp file on my test db .but i directly waant to use this my live db .dmp file on my test db can you help me in this.
...

[Updated on: Mon, 29 September 2014 06:50]

Report message to a moderator

Re: how to import data over network [message #624953 is a reply to message #624952] Mon, 29 September 2014 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can't unless you give Oracle the access rights on network drive something that should not be given.

Re: how to import data over network [message #624991 is a reply to message #624953] Tue, 30 September 2014 01:56 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
thanks for your reply michel..michel can you please help me in this with detail with some detail.or suggest me soome website.i create one map drive on my ive db and map this drive on my test db i already give full rights on this map dirve.if i am wrong can you guide me how can i use this live db directory on my test db on windows server 2008 or windows 7.
Re: how to import data over network [message #624995 is a reply to message #624991] Tue, 30 September 2014 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said give access to network drive to Oracle is a bad idea and a security hole, and even worse to give access to a production server from a test server.
Better go to file transfer.

Re: how to import data over network [message #624999 is a reply to message #624995] Tue, 30 September 2014 03:06 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
its ok michel but here i want to do this.can you please guide me .i give full access to this map drive..i am right on this first i have create map drive on my live db on my this expdp directory.then create a network link on this live db after that when i import on this test db its give error like this..here this orcl its my test db and lvorcl its my live db.


C:\Users\shoaib>impdp system/oracle@orcl directory=prac network_link=sts remap_s
chema=roz:scott  dumpfile=SH.DMP

Import: Release 10.2.0.3.0 - Production on Tuesday, 30 September, 2014 12:53:08

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name PRAC is invalid


i create this my lvorcl service on my test db

SQL> create public database link sts connect to roz identified by roz using 'lvorcl';

Database link created.


Re: how to import data over network [message #625002 is a reply to message #624999] Tue, 30 September 2014 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-39087: directory name %s is invalid
 *Cause:  A corresponding directory object does not exist.
 *Action: Correct the directory object parameter, or create a corresponding
          directory object with the CREATE DIRECTORY command.


Quote:
i create this my lvorcl service on my test db


So you can directly import the object without creating the export dump file (and so transfer) using the NETWORK parameter of impdp.

Re: how to import data over network [message #625011 is a reply to message #625002] Tue, 30 September 2014 05:23 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
direct import i can successfuly done using network like but i want to import from dmp file from my live db.how can i use it this dmp file from my live db directory.can you please suggest me please
Re: how to import data over network [message #625012 is a reply to message #625011] Tue, 30 September 2014 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Transfer the file.

Re: how to import data over network [message #625014 is a reply to message #625012] Tue, 30 September 2014 05:43 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
this i know already.but how can i import directly from this live db dmp file on my test db server.because so many time we have to import so many tables from our live db dmp file to our test db server.this dmp file size are so big so.can you please help me in this
Re: how to import data over network [message #625015 is a reply to message #625014] Tue, 30 September 2014 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Either transfer the file, either do it using NETWORK option.
If you don't want to do it in these ways then don't do it.

Re: how to import data over network [message #625019 is a reply to message #625015] Tue, 30 September 2014 06:21 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
michel suggest me how can i do this using NETWORK option.suggest me any blogs they discribe me how can i use dmp file for import using NETWORK option.
Re: how to import data over network [message #625020 is a reply to message #625019] Tue, 30 September 2014 06:43 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Maybe you should simply forget it and find someone who is capable of searching the Internet (for already known keywords), reading documentation & examples, understanding what's been read and - finally - implementing newly acquired knowledge.
Re: how to import data over network [message #645232 is a reply to message #625020] Tue, 01 December 2015 02:42 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
ok i did this
here i have two server

1. database name livedb -- hostname livedb

2. database name testdb -- hostname testdb

please do this steps on your test db server

host username on both server must be same means on livedb must be its administrator user and on testdb you also login as administrator user

do this steps on your testdb where you want to import

1.Listener service must start with administrator user
2.And instance service also must start with administrator user

To change the default logon for the Oracle services, go to:
-> Control Panel
-> Services
-> OracleServiceXXXX (where XXXX is the instance name)
-> Startup
-> Log On As


Repeat this procedure for the TNS Listener:
-> Control Panel
-> Services
-> OracleXXXXTNSListener (where XXXX is the Oracle Home Name)
-> Startup
-> Log On As



i regularly export on my live db using simple expdp on this exp folder in tso folder so i simple share this tso folder on my livedb

on testdb access this shared folder and right click on this shared folder name tso and click on this map netwrok drive and follow this map network drive steps. after that you can find this shared drive on your testdb my computer

do this steps now on your testdb


C:\Users\Administrator>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 20 15:48:13 2015

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

Enter user-name: test/test@testdb

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

SQL> create or replace directory NET_SHARE as '\\livedb\tso\exp';

Directory created.

-- here livedb its my live database hostname and tso its shared directory and exp its directory where i export my live database using expdp

[b]A directory object has to be created using UNC format to point to the shared directory:

create or replace directory NET_SHARE as '\\<machine name>\<share name>\<path>';

SQL> GRANT READ, WRITE ON DIRECTORY NET_SHARE to public;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options





just for testing purpose i export on my livedb using this

C:\Users\Administrator>expdp system/password@livedb schemas=scott directory=exp dumpfile=scott.dmp logfile=expdpscott.log


and import this scott user on this testdb without copy this dumpfile on this testdb server

C:\Users\Administrator>impdp test/test@testdb schemas=scott directory=net_shar
e dumpfile=scott.dmp logfile=impscott.log
Re: how to import data over network [message #645233 is a reply to message #645232] Tue, 01 December 2015 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
instance service also must start with administrator user


NO, this is a huge security hole.
Instance service should start with local system user.

Re: how to import data over network [message #645235 is a reply to message #645233] Tue, 01 December 2015 03:10 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Great feedback after 15 months though Laughing
Re: how to import data over network [message #645236 is a reply to message #645235] Tue, 01 December 2015 03:41 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
thanks


yeah but so many time we have to import table from our live environment to test environment for testing purpose.that time we copy and past this whole dumpfile from live database to test database its take to much time.so this is a best way.i do so many steps but this oracle document id UTL_FILE on Windows NT (Doc ID 45172.1) help me and must import its os username must be same on both database.so here we use administrator user on livedb and testdb so its succeed. another thing its password of this both username must be same on both server.

michel you told me

Quote:
Either transfer the file, either do it using NETWORK option.
If you don't want to do it in these ways then don't do it.


but its possible i follow this link also

http://dba.stackexchange.com/questions/59781/accessing-shared-folder-using-oracle-database

and oracle document id (Doc ID 45172.1

[Updated on: Tue, 01 December 2015 03:49]

Report message to a moderator

Re: how to import data over network [message #645237 is a reply to message #645236] Tue, 01 December 2015 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I didn't say it is not possible, I said you must NOT do it.
Now realize that your test environment has (full) access to your live environment.
Really great for a hacker or anyone who wants to steal your data and/or crash your enterprise business.

Re: how to import data over network [message #645238 is a reply to message #645237] Tue, 01 December 2015 03:51 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
hmmm its depand on user.

here this test database used by our dba team only.only we dba use this test database so.not big deal
Re: how to import data over network [message #645239 is a reply to message #645238] Tue, 01 December 2015 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you say so.
Did you never hear that database or system can be hacked?

Re: how to import data over network [message #645240 is a reply to message #645238] Tue, 01 December 2015 04:10 Go to previous message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
There is another important point regarding computer security that often gets overlooked: not only is it provided to prevent malicious damage, it is also to safeguard against accidental damage.
Previous Topic: Multiple Export Dump File generation using %u.
Next Topic: SQL loader error while using parallel in direct path
Goto Forum:
  


Current Time: Thu Mar 28 05:40:26 CDT 2024