Tuesday, July 14, 2009

How to create Database Link without editing tnsnames.ora file

A month ago one of our developers needed access to some objects from one schema to another using database link. To enable database link he tried to create entry in tnsnames.ora file but had a problem with insufficient permissions. As a developer he has limited privileges on Unix machines so he can't edit and save tnsnames.ora file.

But there is solution for this little problem.
You can create functional database link without editing tnsnames.ora file.



Little demo case:

system@TEST11> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

5 rows selected.


system@TEST11> select * from dba_db_links;

1. no rows selected



Create database link testlink_db2 using full tns entry:
system@TEST11> create database link testlink_db2
2  connect to system identified by oracle
3  using
4  '(DESCRIPTION=
5    (ADDRESS=
6     (PROTOCOL=TCP)
7     (HOST=10.2.10.18)
8     (PORT=1525))
9    (CONNECT_DATA=
10     (SID=test10)))'
11  /

Database link created.


Now little check and cleanout:
system@TEST11> select * from v$version@testlink_db2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

5 rows selected.

-- cleanout
system@TEST11> drop database link testlink_db2;

Database link dropped.


In this test case I've used system user but this also works with any user.



From documentation:
http://download.oracle.com/docs/html/B13951_01/net.htm#i1153728

server_name = (DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(PORT=port_number)
(HOST=host_name)
)
(CONNECT_DATA=(SERVICE_NAME=service_name)
)
)

where:

server_name is the name of an Oracle server that matches an entry in the RDB directory. An entry in the RDB directory can be added using the ADDRDBDIRE command.

TCP is the TCP protocol used for TCP/IP connections.

port_number is the port number of the Oracle Net listener. This is usually port number 1521.

host_name is the name that defines the system where the target Oracle server resides. This name must be in the local host definition on the AS/400 or in a name server on your network. The host name can also be entered as an IP address, for example, 161.14.10.12.

service_name is the service name of the Oracle server.

26 comments:

  1. keep in mind on unix there's an additional directory (i think i remember /etc/tnsnames.ora on tru64, the documentation states /var/opt/oracle for sun) where this file could reside. See the full search order for tnsnames.ora at http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14212/config_concepts.htm#sthref245 .

    ReplyDelete
  2. Marko,

    This is something to remember....

    Never thought on such an issue in this way-cool!

    Cheers!

    Damir Vadas
    http://damir-vadas.blogspot.com

    ReplyDelete
  3. Very useful. Helped me a lot. Thanks.

    ReplyDelete
  4. Thanx.Solved my problem in a second.

    ReplyDelete
  5. I am getting the following error when trying to create databaselink as you mentioned format :-(



    ERROR: ORA-02010: missing host connect string


    Query = Create
    Database link Arjun connect to Scott identified by "tiger"
    using
    using

    '(DESCRIPTION=

    (ADDRESS=

    (PROTOCOL=TCP)


    (HOST=10.60.1.51)

    ReplyDelete
  6. Hi Arjun,

    you made syntax error.
    Just copy text from my example and enter your connection parameters.
    It should work.

    Regards,
    Marko

    ReplyDelete
  7. Thanks for suggestion - solved by problem in an instance i.e. no access to change tnsnames.ora file.

    ReplyDelete
  8. Glad to hear that Peter ;)

    Regards,
    Marko

    ReplyDelete
  9. This really helped me out. I could not create a link using the normal methods. Sometimes for me, IP addresses seem to work better than machine names.

    ReplyDelete
  10. Hallowtheme, this method is very useful when you don't have access to database server to modify tnsnames.ora file.

    It should work with IP names and with machine names. If you have problems with machine names then check your DNS (address resolution mechanism).

    Thanks for comment.

    Regards,
    Marko

    ReplyDelete
  11. Hi Marko,

    I thought I did exactly as you stated but seems I'm still missing out something.

    It creates but select statement hangs when used.

    The two DBs are within different infrastructures that require firewall opening.

    Could this be responsible for the issue?

    Gideon

    ReplyDelete
  12. Hi Gideon,

    confirm with firewall guys that port you are using is open on specified IP.

    Then execute simple statement to check dblink - "select * from dual@REMOTE_DB". If everything is OK you should receive answer immediately.

    Regards,
    Marko

    ReplyDelete
  13. Thank you very much, Marco!
    Your post was extremely helpful for us!

    ReplyDelete
  14. Marko,

    Thanks a lot. It was really helpful for me.

    ReplyDelete
  15. I'm looking to link 'my machine' db server to 'another machine's' db server, to do so how i need to give the "service name"

    ReplyDelete
  16. dear sir,i want to connect to another database which is not on same network.what is the method to connect another database which is on different network.
    R:Burhan Masood

    ReplyDelete
  17. Hello Burhan,

    what seems to be the problem?
    Do you have connection between database servers?
    Have you tried to create database link? What error do you get?

    Regards,
    Marko

    ReplyDelete
  18. Thanks a lot.
    Nice and easy.

    Linked to Oracle9i database from
    Oracle10g in seconds.

    ReplyDelete
  19. hai good xplanation what is the differnce between public dblink and private dblink.

    ReplyDelete
  20. Thanks. This solution work perfect for me.

    ReplyDelete
  21. Sorry, it doesn't work for me. I still get the following error even though I used SID in the CONNECT_DATA part instead of SERVICE_NAME:
    12154. 00000 - "TNS:could not resolve the connect identifier specified"

    Any help would be appreciated!

    ReplyDelete
  22. Thanks a million for sharing. It made me deliver solution to my customer which made them smile.

    ReplyDelete
  23. Hi,
    I am facing below issue using with dblink (oracle11g).Sometimes it is running properly, sometimes its throwing this below error.What is the root cause.
    Any lead can help on this issue.
    Thanks in advance.

    ERROR at line 1:
    ORA-12521: TNS:listener does not currently know of instance requested in
    connect descriptor

    ReplyDelete