Database link

[OraWiz]

I want to create a database link to my production database from my Dev. database, I am using Link name as Prdn.Database name. Link is successfully created but, when I want select data from Production database using link is not working. It is ORA-12154: TNS:could not resolve service name. But my tnsname.ora file is perfectly OK. any clue. I am using oracle8

 

[sem]

Which tnsnames.ora is OK? Oracle database uses one located on server, have you checked it? Can you login from sqlplus located at the same home as your Dev database?

Regards, Dima

http://seminihin.narod.ru

 

[spicysudhi]

may be the problem with the globalnames of the database. check it. globalname might be something like orcl.us.oracle.com and sid could be ORCL !

regards,
Sudhi

 

[OraWiz]

Thanks for all speedy reply
I am creating a link like this

CREATE PUBLIC DATABASE LINK lindel
CONNECT TO lindel IDENTIFIED BY lindel
USING 'LINDEL'

My Production Sid is LINDEL
Database name is LINDEL
GLOBAL_NAMES = TRUE
CONNECT_STRING is LINDEL

I don't see ay problem... and clueless .. Still the connection link is not working
Rgds
Sebastian

 

[sem]

Does LINDEL name exists in TNSNAMES.ORA on your DEV server? Can you post content of sqlnet.ora file along with LINDEL alias description from tnsnames.ora? You haven't answered whether you can connect from sqlplus

Regards, Dima

http://seminihin.narod.ru

 

[carp]

Also, since there may be several tnsnames.ora files on the same server (depending on how your server is set up), you need to make sure you are looking at the right tnsnames.ora file. Hopefully, you have an environmental variable (TNS_ADMIN) that points to the directory that holds the "right" tnsnames.ora file.

 

[OraWiz]

I got it.
My Problem was my GLOBAL_NAMES = TRUE
I changed it to GLOBAL_NAMES = FALSE.
Now I can connect to my production DataBase from my Dev Database and get the rows thru the link.
Thanks for all especially Sudhi for the clue.
Thanks and Rgds
Sebastian

 

[spicysudhi]

welcome,

if ur global name is different from the sid, then use the link name using global name and NOT the sid.

SQL> select name from v$database;
ORCL

SQL> select GLOBAL_NAME from GLOBAL_NAME;

GLOBAL_NAME
-----------
ORCL.US.ORACLE.COM

Connect to other database,

SQL> CREATE DATABASE LINK ORCL.US.ORACLE.COM
CONNECT TO scott IDENTIFIED BY tiger using 'DEV1';

here dev1 is the entry in tnsnames for ORCL


cheerz,
Sudhi

 

[OraWiz]

Hello all
Some more database link related problem.

I want to create links between two databases.
Both DB.’s GLOBAL_NAMES = FALSE
Open Link is 4 on both the DB.
My database names are DEL, BOM respectively

I can create and use links which can access BOM database ( Created at DEL database)
But at the same time I can not use link to access the DEL database ( Created at BOM)
Though, it got created it is not working. It is giving TNS names error.
TNS names at my client machine is perfectly alright and I can connect to this DB using this connect string.

If I change the GLOBAL_NAMES = TRUE at DEL database, then Database link is not working.
(Even if I create a link name same as host database name)

How can I solve this problem. Someone please give me a help.

 

[sem]

TNS names at your client machine doesn't matter: it should be perfectly alright on the server where "source" database (DEL in your case) resides.

Regards, Dima

http://seminihin.narod.ru

 

[OraWiz]

Dear Dima
When I am connecting from my machine (Client) which TNSNames going to matter. The Server TNS or the Client TNS ?
I believe the client TNS. But as a matter of fact my server's (ie DEL ) TNS name also correct. Because I can connect to 'BOM' Sever from this 'DEL' Server from sqlprompt.

Regards
Sebastian

 

[sem]

As I've already mentioned client TNSNAMES.ORA file doesn't matter, because database link is connection between servers regardless on from which pc this command have been issued originally. So your client settings matter when you're connecting to a database, not when you're creating dblink. You've said that you can connect from DEL server to BOM one, haven't you? Are you sure you're connecting from the same ORACLE_HOME, as DEL database is located in?
If that server contains multiple homes, you may set TNS_ADMIN environment variable to maintain only 1 tnsnames.ora/sqlnet.ora pair. Otherwise you should change them in each home. For your specific case you should be able to connect from sql*plus located within the same home as your DEL database is.

Regards, Dima

http://seminihin.narod.ru

 

[OraWiz]

Dear Dima
I am bit confused.
My BOM Server does not contain any TNSfile.
Do I need to create a TNSNames.ora file on this server to connect to my DEL Server (thru link)

But as a matter of fact my DEL Server also does not contain any valid TNSNAMES file, still I can have a working link between DEL to BOM.

Rgds
Sebastian

 

[OraWiz]

I got it thank you DIMA, I have use full address in place of my connection string

Following is the command

CREATE DATABASE LINK PRD2
CONNECT TO MYACCOUNT
IDENTIFIED BY MYpassword
USING
'DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=192.168.1.168)(Port=1521)))
(CONNECT_DATA=(SID=DEL)))'

Now I have created a link between my BOM to DEL

 

[OraWiz]

I got it thank you DIMA, I have use full address in place of my connection string

Following is the command

CREATE DATABASE LINK PRD2
CONNECT TO MYACCOUNT
IDENTIFIED BY MYpassword
USING
'DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=192.168.1.168)(Port=1521)))
(CONNECT_DATA=(SID=DEL)))'

Now I have created a link between my BOM to DEL

Rgds
Sebastian

 

[sbix]

Which was the problem on inserting the correct row describing the "DEL" DB in the tnsnames.ora file into the TNS_ADMIN directory of the "BOM" DB?
Now you could have some problem each time the server hosting "DEL" Db will change it's IP address or TCP-IP port

 

[OraWiz]

As a matter of fact the connction string in Link command,
I have taken from my TNSNAMES.ORA file. But the IF it give the connection string as 'DEL' then the link is not working otherwise it is working.
Rgds
Sebastian

 

[sbix]

Yes but you can put a symbolic name in tnsnames.ora reflecting an entry in /etc/hosts or solved by dns or whatever.
Furthermore it's easier to modify a text file like tnsnames.ora instead of a dblink .... but ... well ... as you like

 

[sem]

I think that the problem may be in names.default_domain parameter within sqlnet.ora file. This suffix is appended to your tns name.

Regards, Dima

http://seminihin.narod.ru