Home » SQL & PL/SQL » SQL & PL/SQL » Database link creation (Oracle 11g r2)
Database link creation [message #575867] Mon, 28 January 2013 11:30 Go to next message
sarma.shp
Messages: 111
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!
Re: Database link creation [message #575868 is a reply to message #575867] Mon, 28 January 2013 11:33 Go to previous messageGo to next message
BlackSwan
Messages: 21967
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/
Re: Database link creation [message #575869 is a reply to message #575868] Mon, 28 January 2013 11:45 Go to previous messageGo to next message
sarma.shp
Messages: 111
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?
Re: Database link creation [message #575870 is a reply to message #575869] Mon, 28 January 2013 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 57644
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
Re: Database link creation [message #575871 is a reply to message #575870] Mon, 28 January 2013 12:11 Go to previous messageGo to next message
sarma.shp
Messages: 111
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.
Re: Database link creation [message #575872 is a reply to message #575871] Mon, 28 January 2013 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 57644
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 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
Hi Michel,can you give me the exact document regarding database link creation?
Re: Database link creation [message #575875 is a reply to message #575872] Mon, 28 January 2013 12:48 Go to previous messageGo to next message
joy_division
Messages: 4454
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.
Re: Database link creation [message #575876 is a reply to message #575875] Mon, 28 January 2013 12:50 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
Hi Michel,I have even tried with hr instead of "hr". but got the same error!
Re: Database link creation [message #575877 is a reply to message #575876] Mon, 28 January 2013 12:51 Go to previous messageGo to next message
joy_division
Messages: 4454
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?
Re: Database link creation [message #575881 is a reply to message #575876] Mon, 28 January 2013 14:34 Go to previous messageGo to next message
Michel Cadot
Messages: 57644
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 Go to previous messageGo to next message
sarma.shp
Messages: 111
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
Re: Database link creation [message #575943 is a reply to message #575942] Tue, 29 January 2013 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 57644
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
Re: Database link creation [message #575944 is a reply to message #575943] Tue, 29 January 2013 11:27 Go to previous message
BlackSwan
Messages: 21967
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/
Previous Topic: Help on creating master and detail relationship
Next Topic: PL/SQl for multiple flat file formats
Goto Forum:
  


Current Time: Thu Apr 24 04:35:30 CDT 2014

Total time taken to generate the page: 0.06670 seconds