Home » RDBMS Server » Server Utilities » Multiple errors during impdp (Oracle 11.2.0.2, windows 7)
Multiple errors during impdp [message #577001] Mon, 11 February 2013 12:36 Go to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
Hi,

I am trying to use NETWORK_LINK option in datapump and import a table from one server to another. I gave the below command :

C:>impdp example/example@db DIRECTORY=DATA_PUMP_DIR

NETWORK_LINK=db.legal.regn.net remap_schema=BI:example

tables=BI.BI_DIRECT dumpfile=BI.dmp logfile=BI.log

Got the following errors :

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

Is this error related to the permission in the OS level (windows 7 in my case)? I manually created the folder 'DATA_PUMP_DIR' in the specified directory path. Though the directory I created (DATA_PUMP_DIR) shows read-only in the general tab of the property, I am able to create files under the folder 'DATA_PUMP_DIR'. Please let me know how to resolve this issue?

Re: Multiple errors during impdp [message #577003 is a reply to message #577001] Mon, 11 February 2013 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Does DATA_PUMP_DIR exists in @db?
2/ Does the associated OS directory exists?
3/ Does the user that started the listener for @db has the read and write accesses to this later directory?

Do not just tell yes, show us: copy and paste what you do and get to verify these points.

Regards
Michel
Re: Multiple errors during impdp [message #577008 is a reply to message #577003] Mon, 11 February 2013 13:24 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
1)

SQL> select directory_path from dba_directories where directory_name='DATA_PUMP_
DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------

C:\users\lg123\desktop

SQL> grant read, write on directory DATA_PUMP_DIR to example;

Grant succeeded.

SQL> select name from v$database;

NAME
---------
db

This shows the directory DATA_PUMP_DIR exists in the db database.

2)

C:\Users\lg123\Desktop\DATA_PUMP_DIR>

This shows the OS directory "DATA_PUMP_DIR" exists.

3)

I am the owner of the machine. I have full permission in the OS level. I have attached the image which shows I have full permission on the OS level.
  • Attachment: Untitled.png
    (Size: 27.10KB, Downloaded 141 times)
Re: Multiple errors during impdp [message #577010 is a reply to message #577008] Mon, 11 February 2013 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note that the directory must be on the database server not on your PC, is this the case?

Quote:
I have attached the image which shows I have full permission on the OS level.


This is not you that must have the privilege but the account that starts the impdp Oracle process (not the impdp process, the one that is started by Oracle when it creates the session). I doubt this account has any privilege on your private desktop.

Regards
Michel

[Updated on: Mon, 11 February 2013 13:33]

Report message to a moderator

Re: Multiple errors during impdp [message #577011 is a reply to message #577010] Mon, 11 February 2013 14:05 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
Here the oracle user "example" is trying to perform the import operation. Should the "example" user be added in the OS level and provided the full control/access? I tried adding the user "example" in the OS level, but it is giving error. I have attached the snapshot.
  • Attachment: Untitled.png
    (Size: 47.70KB, Downloaded 142 times)
Re: Multiple errors during impdp [message #577012 is a reply to message #577011] Mon, 11 February 2013 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
3/ Does the user that started the listener for @db has the read and write accesses to this later directory?


Regards
Michel
Re: Multiple errors during impdp [message #577013 is a reply to message #577008] Mon, 11 February 2013 15:39 Go to previous messageGo to next message
joy_division
Messages: 4532
Registered: February 2005
Location: East Coast USA
Senior Member
lg123 wrote on Mon, 11 February 2013 14:24
1)

SQL> select directory_path from dba_directories where directory_name='DATA_PUMP_
DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------

C:\users\lg123\desktop

The above says the datapump directory is C:\users\lg123\desktop, but
Quote:

C:\Users\lg123\Desktop\DATA_PUMP_DIR>

This shows the OS directory "DATA_PUMP_DIR" exists.

shows that the case does not match. I do not know how Windows handles case, but in unix, they are different.

Additionally, there is no point in having a directory called DATA_PUMP_DIR, because that is NOT where the output will go. I think you misunderstand what an Oracle directory is. DIRECTORY_PATH is the actual directory, not a path in which you create an OS directory under.
Re: Multiple errors during impdp [message #577017 is a reply to message #577013] Mon, 11 February 2013 19:36 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
In windows, the location is not case sensitive. So both the locations that I had pasted are same. The default directory is the DATA_PUMP_DIR and the data gets stored here.
Re: Multiple errors during impdp [message #577018 is a reply to message #577017] Mon, 11 February 2013 19:39 Go to previous messageGo to next message
BlackSwan
Messages: 22926
Registered: January 2009
Senior Member
when all else fails Read The Fine Manual; which contains examples

http://docs.oracle.com/cd/E11882_01/server.112/e22490/toc.htm
Re: Multiple errors during impdp [message #577027 is a reply to message #577017] Tue, 12 February 2013 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
lg123 wrote on Tue, 12 February 2013 02:36
In windows, the location is not case sensitive. So both the locations that I had pasted are same. The default directory is the DATA_PUMP_DIR and the data gets stored here.


Answer my point 3.
Anyway, you already have the answer:
Quote:
I doubt this account has any privilege on your private desktop.


Regards
Michel

Re: Multiple errors during impdp [message #577131 is a reply to message #577027] Tue, 12 February 2013 12:55 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I have attached a screenshot. Please see it and let me know whether i should change the read-only permission to read-write?
  • Attachment: Untitled.png
    (Size: 22.45KB, Downloaded 139 times)
Re: Multiple errors during impdp [message #577132 is a reply to message #577131] Tue, 12 February 2013 13:04 Go to previous messageGo to next message
BlackSwan
Messages: 22926
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Multiple errors during impdp [message #577133 is a reply to message #577131] Tue, 12 February 2013 13:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
lg123 wrote on Tue, 12 February 2013 19:55
I have attached a screenshot. Please see it and let me know whether i should change the read-only permission to read-write?


Read my posts, answer my questions.

Regards
Michel

Re: Multiple errors during impdp [message #577135 is a reply to message #577133] Tue, 12 February 2013 14:07 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
To answer your question, I do not have a listener file installed on my machine as I am using Oracle client to access the database. Someone in this forum told me that I wouldn't need a listener file if i am connecting to the database with oracle client.
Re: Multiple errors during impdp [message #577136 is a reply to message #577135] Tue, 12 February 2013 14:09 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
In order to ping a TCP connected server, we use tnsping command. If we use LDAP server, how do we ping the server. Do we have any command?
Re: Multiple errors during impdp [message #577137 is a reply to message #577135] Tue, 12 February 2013 14:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
To answer your question, I do not have a listener file installed on my machine as I am using Oracle client to access the database.


And so you didn't carefully read and badly answer to my post:

Quote:
1/ Does DATA_PUMP_DIR exists in @db?
2/ Does the associated OS directory exists?


It appears that the directory you're trying to reach is on your PC and not on the server.

Regards
Michel

[Updated on: Tue, 12 February 2013 14:15]

Report message to a moderator

Re: Multiple errors during impdp [message #577140 is a reply to message #577136] Tue, 12 February 2013 14:35 Go to previous messageGo to next message
BlackSwan
Messages: 22926
Registered: January 2009
Senior Member
lg123 wrote on Tue, 12 February 2013 12:09
In order to ping a TCP connected server, we use tnsping command. If we use LDAP server, how do we ping the server. Do we have any command?



telnet DBServer 1521
Re: Multiple errors during impdp [message #577142 is a reply to message #577137] Tue, 12 February 2013 14:46 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
Yes, you are right. The database is on some other machine, not in my machine. I am trying to use my machine to access the database which sits in some other machine. For this purpose, I use oracle client. My aim is to load the tables from source database to the destination database. I need to prepare a script which can load the tables every night at 12. That is why I want to use datapump tool. Since I have only read access to the source database, I want to use NETWORK_LINK option and try to load the tables without doing anything in the source database(like creating dump files, etc.).

Should the directory be only on the server?

Now the major issue is that I am not able to connect to the source database because I do not have the tnsentry to keep it in my tnsnames.ora file local to my machine. I have been provided only LDAP details. If I could connect through SQL Plus to the source database, I should be able to do the import using network_link parameter.
Re: Multiple errors during impdp [message #577143 is a reply to message #577140] Tue, 12 February 2013 14:53 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
Thanks
Re: Multiple errors during impdp [message #577144 is a reply to message #577143] Tue, 12 February 2013 14:57 Go to previous messageGo to next message
BlackSwan
Messages: 22926
Registered: January 2009
Senior Member
>My aim is to load the tables from source database to the destination database
from Source DB do as below
INSERT into table1@DEST_DB select * from table2.
from Destination DB do as below
INSERT into table1 select * from table2@SOURCE_DB.
Re: Multiple errors during impdp [message #577145 is a reply to message #577144] Tue, 12 February 2013 15:05 Go to previous messageGo to next message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or use impdp with NETWORK_LINK parameter, you then need no directory for the dump file (there is no dump file).

Regards
Michel
Re: Multiple errors during impdp [message #577152 is a reply to message #577145] Tue, 12 February 2013 17:46 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
Thanks
Re: Multiple errors during impdp [message #577154 is a reply to message #577144] Tue, 12 February 2013 19:47 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I do not have the write access to the source database from where I need to pull the tables. So, I cannot issue insert statement in the source database. For the source database, I have the LDAP connection information. Please find the LDAP information in the attachment. Using LDAP connection information, I was able to connect to the source database using SQL Developer tool. I do not have the accurate tnsentry for the source database.

create public database link dblink
connect to user identified by password
using

I do not know what I need to write after the keyword 'using'. I have the user and password information of the source database with me.

I realize that in order to use network_link in the datapump, we need to have/know the tnsentry of the source database which i do not have. So, I guess I wouldn't be able to use datapump (network_link parameter).
Re: Multiple errors during impdp [message #577155 is a reply to message #577154] Tue, 12 February 2013 20:43 Go to previous messageGo to next message
BlackSwan
Messages: 22926
Registered: January 2009
Senior Member
why do you consistently & repeatedly ignore & not follow Posting Guidelines?

http://www.orafaq.com/forum/t/88153/0/

[Updated on: Tue, 12 February 2013 20:44]

Report message to a moderator

Re: Multiple errors during impdp [message #577156 is a reply to message #577155] Tue, 12 February 2013 21:24 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I could create the database link successfully,

SQL> CREATE PUBLIC DATABASE LINK DATALINK CONNECT TO user IDENTIFIED BY password using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server_name)(port=1521)))(connect_data = (service_name = AVYOADB)))'
/

Database Link created.

I tried to connect to the remote database, but failed

C:\Users\lg123>sqlplus user/password@AVYOADB

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 12 22:18:15 2013

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:

I tried to select a table from the remote database, but failed

SQL> SELECT * FROM BIREPORTING.TEST@DATALINK;
SELECT * FROM BIREPORTING.TEST@DATALINK
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4632
Session ID: 156 Serial number: 36181
Re: Multiple errors during impdp [message #577167 is a reply to message #577156] Wed, 13 February 2013 00:57 Go to previous message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
ORA-12154: TNS:could not resolve the connect identifier specified
 *Cause:  A connection to a database or other service was requested using
 a connect identifier, and the connect identifier specified could not
 be resolved into a connect descriptor using one of the naming methods
 configured. For example, if the type of connect identifier used was a
 net service name then the net service name could not be found in a
 naming method repository, or the repository could not be
 located or reached.
 *Action:
   - If you are using local naming (TNSNAMES.ORA file):
      - Make sure that "TNSNAMES" is listed as one of the values of the
        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA)
      - Verify that a TNSNAMES.ORA file exists and is in the proper
        directory and is accessible.
      - Check that the net service name used as the connect identifier
        exists in the TNSNAMES.ORA file.
      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
        file.  Look for unmatched parentheses or stray characters. Errors
        in a TNSNAMES.ORA file may make it unusable.
   - If you are using directory naming:
      - Verify that "LDAP" is listed as one of the values of the
        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA).
      - Verify that the LDAP directory server is up and that it is
        accessible.
      - Verify that the net service name or database name used as the
        connect identifier is configured in the directory.
      - Verify that the default context being used is correct by
        specifying a fully qualified net service name or a full LDAP DN
        as the connect identifier
   - If you are using easy connect naming:
      - Verify that "EZCONNECT" is listed as one of the values of the
        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA).
      - Make sure the host, port and service name specified
        are correct.
      - Try enclosing the connect identifier in quote marks.

   See the Oracle Net Services Administrators Guide or the Oracle
   operating system specific guide for more information on naming.


2/
ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink/MOS and/or call Oracle support
Have a look at alert.log and trace files.
You can also read this article: Troubleshooting Internal Errors.

Regards
Michel
Previous Topic: ORA-00904: "POLTYP": invalid identifier
Next Topic: Reg DAtabase directory
Goto Forum:
  


Current Time: Fri Oct 31 06:38:45 CDT 2014

Total time taken to generate the page: 0.11289 seconds