| Difficulties creating database link and synonyms [message #2585] |
Mon, 29 July 2002 10:45  |
Eve
Messages: 3
Registered: July 2002
|
Junior Member |
|
|
I run (for training purposes) Oracle9i Personal Edition Release 9.0.1.1.1 on a computer which is not normally connected to the internet, and when so by modem through a telephone company which is offering this service to private customers.
When I try to create a database link to a third database (namely the database of the DBA training course provider) I receive two errors (depending on what I type, I've tried many combinations) ORA-02019 & ORA-12154
I've checked the tnsnames.ora which seems to be in order with respect to the other database's protocol, host, port, service_name. I've checked the listener and it's up and running. I've checked the other schema's user-id and password. I can connect to the other database through e.g. SQL> connect scott@dbkurs/tiger in which case I get connected and may select any file in the said schema. I'm able to dump the entire table to my pc through SPOOL, but that is not what I want.
Do you think my problems with ORA-02019 and ORA-12154 (the latter is: TNS could not resolve service name) has anything to do with my connecting through the internet provider which - I think - means that I don't have a proper database domain name for my database?
This is the only plausible explanation I've been able to come up with so far, i.e. that these problems in some ways are related to my INIT.ORA file looking like this:
...
##########################################
# Distributed, Replication and Snapshot
###########################################
db_domain=""
...
Suggestions for a workaround, anybody? What I need to do, really, is to perform a SELECT on one of the tables in the remote user schema (here: scott), ie something similar to select * from scott.emp@scottlink;
Eve
|
|
|
|
| Re: Difficulties creating database link and synonyms [message #2586 is a reply to message #2585] |
Mon, 29 July 2002 11:56  |
Silpa
Messages: 23
Registered: July 2002
|
Junior Member |
|
|
Do you have an entry in TNSnames.ora refering to the Database2 in the Database1 Server.
Are you connecting to Database2 from some other client machine or directly from Database1 Server.
If you are able to connect from Database1 Server to Database2 Server I think you should be able to create the link.
|
|
|
|
| Re: Difficulties creating database link and synonyms [message #2589 is a reply to message #2585] |
Mon, 29 July 2002 22:43 |
Eve
Messages: 3
Registered: July 2002
|
Junior Member |
|
|
Thanks Silpa for a prompt reply!
I do have an entry in the tnsnames.ora referring to database 2 (the remote database) and was able to create the link properly but not to perform any SELECT statement using the said link, neither directly nor with the aid of a synonym. To connect to database 2 using the link works fine, ie connect SCOTT@DBLINK/tiger but I can't use the link in any SELECT clause entered on database 1 (my database on the PC).
I connect to the other database in the following way:
1. PC with database 1 on it connects from time to time through a non-permanent telephone line to the internet provider's server. The PC is a stand-alone pc, not a server, with oracle's personal edition installed on it.
2. The Internet access is provided by way of the internet provider's servers, which makes it possible for me to connect to database 2 (on the remote third-party server).
I suspect that database 1 doesn't have a functioning service_name (?) nor db_domain (which is blank) and that this may be the reason why I'm experiencing the problem?
Eve
|
|
|
|
| Re: Difficulties creating database link and synonyms [message #2598 is a reply to message #2589] |
Tue, 30 July 2002 07:56 |
Silpa
Messages: 23
Registered: July 2002
|
Junior Member |
|
|
The Entry in the TNSNAMES.ora is the Service Name. This one is used to login into the remote database .
To run queries on remote database you have to create a Database Link on Database1 referring to remote database Database2.
If DBlINK is the entry in TNSNAMES.ORA which is Service Name.
create database link DBLINK_REMOTEDBASE
connect to SCOTT identified by TIGER
using 'DBLINK';
Now use the this link in your queries.
select * from emp@DBLINK_REMOTEDBASE;
DB_DOMAIN can be blank. This shouldn't matter.
For example, this parameter allows one department to create a database without worrying that it might have the same name as a database created by another department. If one sales department's DB_DOMAIN = "JAPAN.ACME.COM", then their "SALES" database (SALES.JAPAN.ACME.COM) is uniquely distinguished from another database with DB_NAME = "SALES" but with DB_DOMAIN = "US.ACME.COM", (SALES,US.ACME.COM) .
If you are able to login into the remote database then you should be able to create the link.
|
|
|
|
| Re: Difficulties creating database link and synonyms [message #2605 is a reply to message #2589] |
Tue, 30 July 2002 11:29 |
Eve
Messages: 3
Registered: July 2002
|
Junior Member |
|
|
Hello again, and thank's for your prompt reply. I tried it, but unfortunately it didn't work out all that well. This is what transpired:
(the comments from the Oracle database have been translated into English, the SQL*Plus doesn't use English on my installation)
*********************
SQL*Plus: Release 9.0.1.0.1 - Production on Tis Jul 30 20:40:56 2002
...
SQL> create database link dblink_remotedb
2 connect to remote_user identified by password
3 using 'DBKURS'
4 /
Database link has been created.
SQL> select count(*) from books@dblink_remotedb;
select count(*) from books@dblink_remotedb
Error on line 1:
ORA-12545: The connection was not made due to missing target computer or object.
SQL> connect remote_user/password@dbkurs;
Connected.
SQL> select count(*) from remote_user.books;
COUNT(*)
----------
54
SQL> ...
**************
I can thus connect to the user remote_user, but it seems I am unable to use it in a database link...
Following a lookup on this forum of all entries with ORA-12545 in them, I've also looked into the tnsnames.ora and they both look fine to me:
MY_DB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 012345678901)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = MY_DB)))
DBKURS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = remote.db.web.address)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = dbkurs)))
|
|
|
|
| Re: Difficulties creating database link and synonyms [message #2622 is a reply to message #2605] |
Wed, 31 July 2002 10:33 |
Eva
Messages: 16
Registered: July 2001
|
Junior Member |
|
|
Did check status of "global_names" in the remote database, by logging onto the remote system and then type SHOW PARAMETERS. The boolean parameter "global_names" is "TRUE". The string parameter "service_names" is "dbkurs". Did also check the status of "global_names" and "service_names" in my database. Global_names is FALSE. Entered SID name instead of service_names on the connect_data line for dbkurs.
Attempt 1 to connect resulted in ORA-02082, a circular reference: "create database link {my db's sid} connect to remote_user identified by password using 'dbkurs'"
Attempt 2 resulted in the creation of a link but when trying SELECT on a table at the remot database, this resulted in ORA-12545, "connection not made since there is no target computer": "create database link dbkurs connect to remote_user identified by password using 'dbkurs'"
Attempt 3 also resulted in the creation of a link, but when trying SELECT on the said table this resulted in ORA-12154, cannot interpret service name: "create database link dbkurs connect to remote_user identified by password using 'remote.db.web.address'"
Changed to the service_name, but am still unable to use the link in a select clause.
Changed back to the sid, stopped and restarted the listener from the msdos prompt, this also resulted in an ORA-12545. Settings:
DBKURS = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = remote.db.web.address)(PORT = 1521))) (CONNECT_DATA = (SID = DBKURS)))
Turned to http://technet.oracle.com/doc/oracle8i_816/network.816/a76933/troubles.htm#440455
1. I verified that a tnsnames.ora file exists.
2. I verified that there were no multiple copies of the tnsnames.ora file.
3. I verified in the tnsnames.ora file, that the net service name specified in your connect string is mapped to a connect descriptor.
4. I verified that there are no duplicate copies of the sqlnet.ora file.
5. I tried to verify that my sqlnet.ora file contains a NAMES.DEFAULT_DOMAIN parameter, which it did not. If this parameter does not exist, you must specify the domain name in your connect string. I checked the parameters through show parameters and verified that domain_name but I am not using domain names and therefore followed the other recommendation "If you are not using domain names, and this parameter exists, delete it or disable it by commenting it out."
6. I am not using a login dialog box and have not accidentally used an "@" symbol.
I noticed that there were quite a few people asking questions about the ORA-12545 but few answers... I looked up another source: "ORA-12545: Connect failed because target host or object does not exist Cause: The address specified is not valid, or the program being connected to does not exist. ... not much help there! However, they suggested to "...if the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the host name to a numeric IP address and try again." I looked up the ip address at http://www.kloth.net/services/nslookup.php and then I tried to use the ip-address but the use of the ip address resulted in ORA-12560: TNS: protocol adapter error
I give up! I'll send an e-mail to the tutors at the course, and wait... meanwhile I'll dump the table to my pc by way of SPOOL.
|
|
|
|
| Re: Difficulties creating database link and synonyms [message #2667 is a reply to message #2605] |
Mon, 05 August 2002 13:32 |
Silpa
Messages: 23
Registered: July 2002
|
Junior Member |
|
|
If global_names=true in your remote database. You have to put the remote database SID name as database link name.
For example if Remote database SID is dbkurs
create database link dbkurs connect to remote_user identified by password using 'dbkurs'"
Also in tnsnames.ora try giving the same name
dbkurs= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = remote.db.web.address)(PORT = 1521))) (CONNECT_DATA = (SID = dbkurs)))
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
