Home » RDBMS Server » Server Administration » how to connect oracle client to a remote oracle server DB (oracle, 11g, windows 64-bit)
how to connect oracle client to a remote oracle server DB [message #588888] Sat, 29 June 2013 08:51 Go to next message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
Hi at all

I'm trying to connect a oracle client application on the client machine to a remote oracle server on the server machine but i get a connection fail.

On the server machine I configured oracle server in the following way:
  1. Installed oracle server.
  2. Created a database "DB_Test" with the database configuration assistant
  3. Created a LISTNER with the Oracle NET Manager with the following parameter:
    • Protocol: TCP/IP
    • HOST: server pc hostname (ENZOVAIO) or server machine address ip (192.168.0.71) in the network lan
    • Port Number: 1521
  4. Created "dbtest" service with the Oracle NET Manager with with the following parameter:
    • Service Name: "dbtest"
    • Protocol: TCP/IP
    • HOST: server pc hostname (ENZOVAIO) or server machine address ip (192.168.0.71) in the network lan
    • Port Number: 1521

All services on the server machine are running and I opened port number (1521) in the router.

On the client machine I installed SQL PLUS and SQL Developer.

With SQL Plus as by the official documentation I have entered the following command:

CONNECT username/password@[//]host[:port][/service_name]. In my case is:

CONNECT SYSTEM/oracledb@//ENZOVAIO:1521/testdb.

With SQL Developer I have entered the same parameter.

But with both SQLPlus and SQL Developer the connection fails.

Can anyone help me?

regards.






[Updated on: Sat, 29 June 2013 08:53]

Report message to a moderator

Re: how to connect client oracle to a remote oracle server DB [message #588889 is a reply to message #588888] Sat, 29 June 2013 08:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
both my cars fail to go.
tell me how to make my cars go.

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

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

>On the client machine I installed SQL PLUS and SQL Developer.
client system must either use EZCONNECT (controlled by sqlnet.ora file) or tnsnames.ora file

post content of the two files in line above from the CLIENT system

use COPY & PASTE so we can see what you do & how Oracle responds.



Re: how to connect oracle client to a remote oracle server DB [message #588890 is a reply to message #588888] Sat, 29 June 2013 08:59 Go to previous messageGo to next message
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
Senior Member
Can you paste the screenshot of the error message ?
Re: how to connect client oracle to a remote oracle server DB [message #588892 is a reply to message #588889] Sat, 29 June 2013 09:13 Go to previous messageGo to next message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
sqlnet.ora has this content
# sqlnet.ora Network Configuration File: C:\app\Fabio\product\11.2.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)



tnsnames.ora has this content
# This file contains the syntax information for 
# the entries to be put in any tnsnames.ora file
# The entries in this file are need based. 
# There are no defaults for entries in this file
# that Sqlnet/Net3 use that need to be overridden 
#
# Typically you could have two tnsnames.ora files
# in the system, one that is set for the entire system
# and is called the system tnsnames.ora file, and a
# second file that is used by each user locally so that
# he can override the definitions dictated by the system
# tnsnames.ora file.

# The entries in tnsnames.ora are an alternative to using
# the names server with the onames adapter.
# They are a collection of aliases for the addresses that 
# the listener(s) is(are) listening for a database or 
# several databases.

# The following is the general syntax for any entry in 
# a tnsnames.ora file. There could be several such entries 
# tailored to the user's needs.

<alias>= [ (DESCRIPTION_LIST =  # Optional depending on whether u have 
				# one or more descriptions
				# If there is just one description, unnecessary ]
	  (DESCRIPTION=
	    [ (SDU=2048) ]	# Optional, defaults to 2048
				# Can take values between 512 and 32K
	    [ (ADDRESS_LIST=    # Optional depending on whether u have
				# one or more addresses
				# If there is just one address, unnecessary ]
	      (ADDRESS=
		[ (COMMUNITY=<community_name>) ] 
		(PROTOCOL=tcp)
		(HOST=<hostname>)
		(PORT=<portnumber (1521 is a standard port used)>)
	      )
	      [ (ADDRESS=
		  (PROTOCOL=ipc)
		  (KEY=<ipckey (PNPKEY is a standard key used)>)	
		)
	      ]
	      [ (ADDRESS=
		  [ (COMMUNITY=<community_name>) ]
		  (PROTOCOL=decnet)
		  (NODE=<nodename>)
		  (OBJECT=<objectname>)
		)
	      ]
              ... # More addresses
	    [ ) ] # Optional depending on whether ADDRESS_LIST is used or not 
	    [ (CONNECT_DATA=
		(SID=<oracle_sid>)
		[ (GLOBAL_NAME=<global_database_name>) ]
	      )
	    ]
	    [ (SOURCE_ROUTE=yes) ]  
	  )
	  (DESCRIPTION=     
	    [ (SDU=2048) ]	# Optional, defaults to 2048
				# Can take values between 512 and 32K
	    [ (ADDRESS_LIST= ]	# Optional depending on whether u have more
				# than one address or not
				# If there is just one address, unnecessary
	      (ADDRESS
		[ (COMMUNITY=<community_name>) ]
		(PROTOCOL=tcp)
		(HOST=<hostname>)
		(PORT=<portnumber (1521 is a standard port used)>)
	      )
	      [ (ADDRESS=
		  (PROTOCOL=ipc)
		  (KEY=<ipckey (PNPKEY is a standard key used)>)
	         )
	      ]
	      ... 		# More addresses
	    [ ) ] 		# Optional depending on whether ADDRESS_LIST  
				# is being used
	    [ (CONNECT_DATA=
		(SID=<oracle_sid>)
		[ (GLOBAL_NAME=<global_database_name>) ]
	      )
	    ]
	    [ (SOURCE_ROUTE=yes) ]
	  )
	  [ (CONNECT_DATA=
	      (SID=<oracle_sid>)
	      [ (GLOBAL_NAME=<global_database_name>) ]
	    )
	  ]
	  ...   # More descriptions 
	[ ) ]	# Optional depending on whether DESCRIPTION_LIST is used or not

Re: how to connect client oracle to a remote oracle server DB [message #588893 is a reply to message #588892] Sat, 29 June 2013 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
below shows proper syntax to use EZCONNECT

sqlplus user1/pass1@'dbserver_hostname:1521/oracle_sid'
Re: how to connect client oracle to a remote oracle server DB [message #588894 is a reply to message #588893] Sat, 29 June 2013 09:22 Go to previous messageGo to next message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
BlackSwan wrote on Sat, 29 June 2013 09:16
below shows proper syntax to use EZCONNECT
sqlplus user1/pass1@'dbserver_hostname:1521/oracle_sid'


Sorry I'm new with oracle databse.

Please, can you say me what's EZCONNET and how to use it?

In oracle net manager profile section of the server machine I have a EZCONNET and TNSNAMES voice in "selected method" part.

[Updated on: Sat, 29 June 2013 09:25]

Report message to a moderator

Re: how to connect client oracle to a remote oracle server DB [message #588895 is a reply to message #588894] Sat, 29 June 2013 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.lmgtfy.com/?q=oracle+ezconnect
Re: how to connect oracle client to a remote oracle server DB [message #588896 is a reply to message #588888] Sat, 29 June 2013 09:29 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You didn't give the error message, but I think it was probably an ORA-12514, the listener does not know of the requested service. If so, that will likely be because your database (and therefore the service) is named db_test but you are requesting a service testdb in your EZ connect string. So try this,

CONNECT SYSTEM/oracledb@//ENZOVAIO:1521/db_test
Re: how to connect client oracle to a remote oracle server DB [message #588898 is a reply to message #588895] Sat, 29 June 2013 09:46 Go to previous messageGo to next message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
Also setting EZCONNECT method i get a connectin fail.

This is a new content of sqlnet.ora file:

# sqlnet.ora Network Configuration File: C:\app\Fabio\product\11.2.0\client_1\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (EZCONNECT, TNSNAMES)

ADR_BASE = C:\app\Fabio\product\11.2.0\client_1\log


this is the error I get:


  • Attachment: Immagine.jpg
    (Size: 82.13KB, Downloaded 1256 times)
Re: how to connect client oracle to a remote oracle server DB [message #588899 is a reply to message #588898] Sat, 29 June 2013 09:54 Go to previous messageGo to next message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
This is the Oracle net manager service configuration

  • Attachment: Listener.jpg
    (Size: 95.73KB, Downloaded 1118 times)
Re: how to connect client oracle to a remote oracle server DB [message #588900 is a reply to message #588899] Sat, 29 June 2013 09:59 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Instead of uploading image files (that many people will not or cannot download) why don't you just try the connect string I gave you?

A why do you not say what the error message is?
Re: how to connect client oracle to a remote oracle server DB [message #588901 is a reply to message #588900] Sat, 29 June 2013 10:05 Go to previous messageGo to next message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
Yes, the error message with SQL Plus is:

ORA-12170: TNS: connection time out has occurred.

Re: how to connect client oracle to a remote oracle server DB [message #588902 is a reply to message #588901] Sat, 29 June 2013 10:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
most common cause for TIMEOUT is a Firewall on or between client & DB Server
Re: how to connect client oracle to a remote oracle server DB [message #588903 is a reply to message #588902] Sat, 29 June 2013 10:22 Go to previous messageGo to next message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
Yeahhhhhhhhhhhh ... I deactivated windows firewall and the connection has succeeded

Now all work fine!!! Very Happy Very Happy Very Happy

thanks for your help. Very Happy Very Happy Very Happy

But, now I can't to have windows firewall deactivated .... How I do?!

[Updated on: Sat, 29 June 2013 10:26]

Report message to a moderator

Re: how to connect client oracle to a remote oracle server DB [message #588919 is a reply to message #588903] Sat, 29 June 2013 14:16 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A firewall can't only be ON or OFF. It has some additional settings. Have a look at Allowing Oracle ports in Windows firewall (and/or research how to yourself).
Previous Topic: Unable to see/insert Chinees characters in Oracle db
Next Topic: ORA-01506
Goto Forum:
  


Current Time: Thu Mar 28 07:43:19 CDT 2024