cannot connect oracle database from other computer

Question

I have two computers, Computer A has IP (10.11.11.2) which contain oracle database 11g R2 and Computer B has IP (10.11.11.18). I searched alot and make some changes in Tnsnames.ora and listener.ora files but cannot connect. But when I tnsping 10.11.11.2 from client it gives me OK output. That's mean database is accessible from client but I am unable to connect database using Toad or Sqldeveloper.

Error is given : tns does not currently know of service requested

My Computer A (10.11.11.2) tnsname.ora and listener.ora files paste below:

Tnsnames.ora file

# tnsnames.ora Network Configuration File:     D:\app\Mobilityusr\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

listener.ora file

# listener.ora Network Configuration File: D:\app\Mobilityusr\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\Mobilityusr\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Mobilityusr\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.11.2)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = D:\app\Mobilityusr

My Computer B (10.11.11.18) tnsname.ora and listener.ora files paste below:

tnsnames.ora file

# tnsnames.ora Network Configuration File: D:\app\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

listener.ora

# listener.ora Network Configuration File: D:\app\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS =     "EXTPROC_DLLS=ONLY:D:\app\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = D:\app

Image of tnsping from computer B to oracle database (installed on Comupter A) is also attached:

Error image also attached

Answer 1

After a lot of research I solved the problem.And I must share it with you too so that any one who have this problem can solve it easily.

Listener.ora file is used to listen requests. We must add two entries(one for SID and other for machine IP address) in listener.ora if we want to access database from another computer.

Computer A files(listener.ora and tnsnames.ora) setting are given below:

Listener.ora

# listener.ora Network Configuration File: D:\app\Mobilityusr\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\Mobilityusr\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS =     "EXTPROC_DLLS=ONLY:D:\app\Mobilityusr\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )  
    (SID_DESC =
     (GLOBAL_DBNAME = orcl)
     (ORACLE_HOME = D:\app\Mobilityusr\product\11.2.0\dbhome_1)
     (SID_NAME = orcl)
    )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.11.2)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = D:\app\Mobilityusr

SID_LIST_LISTENER contains two entries first SID_DESC is by default entry and we must add 2nd SID_DESC in order to identify orcl SID otherwise following error will be given by Sqldeveloper or toad or sqlplus when connecting database from other machine: error: sid does not currently know

Now Listener contain two entries one by default for localhost and we must add 2nd entry which contain server machine IP. By this entry database on server machine can listen coming connections which contain this IP address will be connected to server database.Otherwise database will not be connected.

Note: You can use these configurations but don't forget to change IPs and oracle_home path

and tnsnames.ora file is used to define alias of any database parameters(SID,PORT,HOST). But it is not compulsory to change tnsnames.ora to connect server and client so I am not modifying tnsnames.ora located at server machine.

Computer B tnsnames.ora is same as above mentioned in question. Computer B listener.ora is same as above mentioned in question.

Summary: You only need to change listener.ora file located at server machine (Oracle_home/network/admin/).and add two entries one in SID_LIST_LISTENER to add SID detail and 2nd in LISTENER to add machine IP description.

Also check environment variables (ORACLE_UNQNAME,ORACLE_SID,ORACLE_HOME,TNS_ADMIN etc)

One more important thing You must check that Listener service is up and running use these commands(Open CMD and type) :

**lsnrctl status **

**lsnrctl start **

**lsnrctl stop **

and when you change listener.ora file you must stop and then start lsnrctl service in order to changes effect.

Answer 2

In SQLDeveloper, instead of specifying SID, choose Service Name.

Answer 3

I had lots of trouble with same problem but with Oracle 12C. Finally got it working with almost similar solution.

My database server is on 192.168.1.165 with port 1526. Also had to open port 1526 in Windows server firewall.

# listener.ora Network Configuration File: G:\app\db12\product\12.1.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = G:\app\db12\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:G:\app\db12\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
     (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1526))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.165)(PORT = 1526))
    )
  )