Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Database link not active 2

Status
Not open for further replies.

THoey

IS-IT--Management
Jun 21, 2000
2,142
US
I am trying to make a link from one database to another. When I run the following code, I get the standard "Statement Processed." message:
Code:
CREATE DATABASE LINK DB.MyCom.COM
CONNECT TO MyLogin IDENTIFIED BY MyPasswd
USING 'DB.MyCom.COM'
If I go into Oracle DBA Studio and do a test, it returns the message that the link is not active. I have verified that the account I use to create the link has permissions to create links and that the database name that I am trying to link to is correct. I also checked that the login and password that I am trying to connect with are correct and that it has create session permissions. Anyone have any ideas or a way that I can find out why it is not active? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.
 
Try going into SQL*Plus and execute a query using the new DB link. That should fail, but I've found that the error message is helpful in debugging. The DBA studio test doesn't give you a clue as to what's wrong.
 
I can always trust Karluk...

I tried selecting all the records from a small table in that instance using SQL*Plus and SQL*Worksheet, and the following code:
Code:
SQLWKS> select * 
     2> from walkaway_codes@cfdev.sbc.com
     3> 
from walkaway_codes@cfdev.sbc.com
                    *
ORA-12154: TNS:could not resolve service name
I know that this should work. I can start SQL*Plus with the same login/password and connect string that I use in the create database link SQL and I can get in and see that same table.

But you are right, at least it was a better message... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.
 
Such behavior is a bit strange, if your sql*plus and database share the same environment (reside on a single box and use the same TNS_ADMIN variable), but if you run sql*plus from the remote host, try to check, whether your tnsnames.ora file, utilized by database server contains an entry for cfdev.sbc.com. If this is not the case, try to copy the description of this alias from sql*plus's tnsnames.ora (it must be there) to the database's tnsnames.
 
Hi Terry,

When creating a database link from one database to the other, you must specify the link name as the global name of the remote database. Also, in the initSID.ora file of your local database, set the global_names parameter to TRUE.

And the listener must be up before performing any of the operations. To start the listener execute lsnrctl start command.

Also, make sure that you have appropriote entries for cfdev.sbc.com in your TNSNAMES.ORA and LISTENER.ORA.

Nilesh Joshi
 
You only have to use the global name if global_names is set to TRUE. If you set it to FALSE, you can call the link Larry, Moe, Curly, or anything else you want.

I would check to see that there actually is a cfdev.sbc.com entry in your tnsnames.ora file. Also, check your sqlnet.ora file for the value of DEFAULT_DOMAIN. If it is set to something such as "world", then either comment that entry out of your sqlnet.ora file or rename your tnsnames.ora alias to cfdev.sbc.com.world and try again.
 
Nope, that's not it. Would it make a difference if the linked DB is Oracle 7.3.4 and the source DB is 8.1.6?

BTW, thanks for trying... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.
 
No, we have such links: 7.3, 8.0.5, 8.1.6. in all directions. Normally this error means the lack of appropriate entry, namely DB.MyCom.COM
in tnsnames.ora file on that database server. Try to configure your sql*net to add this entry.
 
I thought it was ok to have different versions. Ok, here is the entry in my TNSNAMES.ORA file:
Code:
CFDEV.SBC.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (COMMUNITY = tcp.SBC.COM)(PROTOCOL = TCP)(Host = 999.999.999.999)(Port = 1521))
      (ADDRESS = (COMMUNITY = tcp.SBC.COM)(PROTOCOL = TCP)(Host = 999.999.999.999)(Port = 1526))
    )
    (CONNECT_DATA =
      (SID = TCC01)
    )
  )
(IP changed to protect the innocent.)

Here is the code for the create DB link:
Code:
CREATE DATABASE LINK CFDEV.SBC.COM 
CONNECT TO user IDENTIFIED BY passwd
USING 'CFDEV.SBC.COM'
I had this problem last week connecting to a different DB. I went back and forth with our DBA about it and he finally replied:

"I have completed the configuration.. so please re-try and let me know." After that, it worked.

I asked out of curiosity what he had changed on the DB to be linked, but he never replied. When I started having this problem on this connection (different linked DB), I asked again, so that I could make the changes on this development DB, but he hasn't responded yet.

Are there any DB settings that would or would not allow DB links to be created? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.
 
I suspect your DBA was probably modifying the value of global_names, or something like that, to address the issues brought up by Nilujoshi and Carp. However first we need to resolve the ORA 12154 errors.

I found a suggestion on Metalink that says, if you get an ORA 12154 while trying to use a db link, to try dropping and recreating the link using the entire connect descriptor from the tnsnames.ora file. In your case that would be

CREATE DATABASE LINK CFDEV.SBC.COM
CONNECT TO user IDENTIFIED BY passwd
USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (COMMUNITY = tcp.SBC.COM)(PROTOCOL = TCP)(Host = 999.999.999.999)(Port = 1521))
(ADDRESS = (COMMUNITY = tcp.SBC.COM)(PROTOCOL = TCP)(Host = 999.999.999.999)(Port = 1526))
)
(CONNECT_DATA =
(SID = TCC01)
)
)';
 
I appreciate all of your help, but Karluk's solution did the trick (unless my DBA made a change in the last 2 hours and didn't tell me about it).

Thanks again to all of you... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top