OraFAQ Forum: SQL & PL/SQL » Database link creation

Home » SQL & PL/SQL » SQL & PL/SQL » Database link creation (Oracle 11g r2)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Database link creation [message #575867]

Mon, 28 January 2013 11:30

sarma.shp
Messages: 108
Registered: February 2012

Senior Member

Hi all,
I have installed oracle database 11gr2 in laptop1 and installed oracle database 11gr2 in laptop2.
Both the laptop's are connected to same internet connection through wifi. So my question is can i create a database link between these two databases using this wifi? please suggest/correct me and give some steps in creation of a database link.

Thanks in advance!

Report message to a moderator

Re: Database link creation [message #575868 is a reply to message #575867]

Mon, 28 January 2013 11:33

BlackSwan
Messages: 20095
Registered: January 2009

Senior Member

http://docs.oracle.com/cd/E11882_01/network.112/e10836/toc.htm

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Database link creation [message #575869 is a reply to message #575868]

Mon, 28 January 2013 11:45

sarma.shp
Messages: 108
Registered: February 2012

Senior Member

Hi swan, I have tried creating a database link but i got an error: ORA-02010:missing host connect string.Can you help me?

Report message to a moderator

Re: Database link creation [message #575870 is a reply to message #575869]

Mon, 28 January 2013 11:53

Michel Cadot
Messages: 54162
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Maybe you should:
1/ read SQL Reference, CREATE DATABASE LINK page
2/ read the Net Services Reference, tnsnames.ora page
3/ copy and paste what you did and got so that we can see what is wrong; don't you think that saying "I did something and I got error" can lead on another answer that "fix the error and it will be ok"?

Regards
Michel

Report message to a moderator

Re: Database link creation [message #575871 is a reply to message #575870]

Mon, 28 January 2013 12:11

sarma.shp
Messages: 108
Registered: February 2012

Senior Member

Hi Michel,The below is the new entry i have added in tnsnames.ora file of database X in order to connect to database Y.

 
TESTLINK = 
    (DESCRIPTION=
        ADDRESS  = (PROTOCOL = TCP)
                   (HOST= admin)
                   (PORT=1521))
    (CONNECT_DATA = (SERVICE_NAME=XE)
  )
)

and gave the following statements from sqlplus to create a databaselink

CREATE DATABASE LINK REMOTE_LINK
 CONNECT TO "hr"
 IDENTIFIED BY "hr"
 using TESTLINK;
error ORA-02010 :missing host connect string.

Report message to a moderator

Re: Database link creation [message #575872 is a reply to message #575871]

Mon, 28 January 2013 12:26

Michel Cadot
Messages: 54162
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

using 'TESTLINK'

Carefully read the documentation.
In addition, I bet "hr" should be hr without the ".

Regards
Michel

[Updated on: Mon, 28 January 2013 12:26]

Report message to a moderator

Re: Database link creation [message #575874 is a reply to message #575872]

Mon, 28 January 2013 12:40

sarma.shp
Messages: 108
Registered: February 2012

Senior Member

Hi Michel,can you give me the exact document regarding database link creation?

Report message to a moderator

Re: Database link creation [message #575875 is a reply to message #575872]

Mon, 28 January 2013 12:48

joy_division
Messages: 4266
Registered: February 2005
Location: East Coast USA

Senior Member

Michel Cadot wrote on Mon, 28 January 2013 13:26

In addition, I bet "hr" should be hr without the ".

Hmmm, I seem to recall that when creating database links, case sensitivity did not matter even with the double quotes. I could be remembering incorrectly, but there was a case where forcing lower case with double quotes had no effect.

Report message to a moderator

Re: Database link creation [message #575876 is a reply to message #575875]

Mon, 28 January 2013 12:50

sarma.shp
Messages: 108
Registered: February 2012

Senior Member

Hi Michel,I have even tried with hr instead of "hr". but got the same error!

Report message to a moderator

Re: Database link creation [message #575877 is a reply to message #575876]

Mon, 28 January 2013 12:51

joy_division
Messages: 4266
Registered: February 2005
Location: East Coast USA

Senior Member

You haven't learned that SHOWING us what command you used rather than just TELLING us would really help?

Report message to a moderator

Re: Database link creation [message #575881 is a reply to message #575876]

Mon, 28 January 2013 14:34

Michel Cadot
Messages: 54162
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

sarma.shp wrote on Mon, 28 January 2013 19:50

Hi Michel,I have even tried with hr instead of "hr". but got the same error!

As I said:
using 'TESTLINK'

Note the quotes.
Database SQL Reference

Regards
Michel

[Updated on: Mon, 28 January 2013 14:34]

Report message to a moderator

Re: Database link creation [message #575942 is a reply to message #575881]

Tue, 29 January 2013 11:23

sarma.shp
Messages: 108
Registered: February 2012

Senior Member

Hi Michel, I have created the databaselink as following. But on querying ,it is throwing an error:

CREATE DATABASE LINK REMOTE_LINK
 CONNECT TO hr
 IDENTIFIED BY hr
 using 'TESTLINK';
DATABASE LINK CREATED

SELECT * FROM HR@REMOTE_LINK;
ORA-12154: TNS:COULD NOT RESOLVE CONNECT IDENTIFIER SPECIFIED

Report message to a moderator

Re: Database link creation [message #575943 is a reply to message #575942]

Tue, 29 January 2013 11:24

Michel Cadot
Messages: 54162
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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.

Regards
Michel

Report message to a moderator

Re: Database link creation [message #575944 is a reply to message #575943]

Tue, 29 January 2013 11:27

BlackSwan
Messages: 20095
Registered: January 2009

Senior Member

ORA-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
ORA-12154 NEVER involves the listener, the database itself or anything on the DB Server.
ORA-12154 occurs when client requests a connection to some DB server system using some connection string.
TNS-03505 is thrown by tnsping & is same error as ORA-12154 thrown by sqlplus or others.
The lookup operation fails because the name provided can NOT be resolved to any remote DB.
The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
The most frequent cause for the ORA-12154 error is when the connection alias can not be found in tnsnames.ora.
The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.
TROUBLESHOOTING GUIDE: ORA-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]
http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/

Report message to a moderator

Previous Topic:	Help on creating master and detail relationship
Next Topic:	PL/SQl for multiple flat file formats

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 10:27:56 CDT 2013

Total time taken to generate the page: 0.20010 seconds