| Multiple errors during impdp [message #577001] |
Mon, 11 February 2013 12:36  |
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 #577010 is a reply to message #577008] |
Mon, 11 February 2013 13:32  |
 |
Michel Cadot
Messages: 68417
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 #577013 is a reply to message #577008] |
Mon, 11 February 2013 15:39 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
lg123 wrote on Mon, 11 February 2013 14:241)
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 |
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 #577135 is a reply to message #577133] |
Tue, 12 February 2013 14:07 |
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 #577137 is a reply to message #577135] |
Tue, 12 February 2013 14:14 |
|
Michel Cadot
Messages: 68417
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 #577142 is a reply to message #577137] |
Tue, 12 February 2013 14:46 |
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 #577154 is a reply to message #577144] |
Tue, 12 February 2013 19:47 |
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 #577156 is a reply to message #577155] |
Tue, 12 February 2013 21:24 |
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 |
|
Michel Cadot
Messages: 68417
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
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
