How to access Oracle database over network?

Question

I am trying to accessing my Oracle database over Network. I have Google a lot and found many solutions, but those doesn't work for me.

The problem is that, I want to access my Oracle Database from One Computer to another Computer over my Local Network.

What I did is....

1. I found a Solution to modify listener.ora file with

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbp.devProject.com)(PORT=1521)))

When I modify this file and tried to run and access the Database nothing will happens, and it throws an Error, the port number you have specified is used by another listener, so i have changed port Number but I am not able to access Database. 2. I have configure a new listener with the help of oracle's tool Net Manager, which made a Listener for me, but it does not make accessible database over Network.

Here Some required results.

1. lsnrctl status : Listener using listener name LISTENER has already been started.
2. Connection String : jdbc:oracle:thin:@dbp.devProject.com:4541:myDB
3. Database Version :
   1. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
   2. PL/SQL Release 11.2.0.1.0 - Production
   3. CORE 11.2.0.1.0 Production
   4. TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
   5. NLSRTL Version 11.2.0.1.0 - Production

status shows me this.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

Alias : LISTENER

Version : TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production

Start Date : 14-MAY-2015 11:58:05

Uptime : 0 days 2 hr. 29 min. 50 sec

Trace Level : off

Security : ON: Local OS Authentication

SNMP : OFF

Listener Parameter File : product\11.2.0\dbhome_1\network\admin\listener.ora

Listener Log File : \listener\alert\log.xml

Listening Endpoints Summary...

1. (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
2. (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Someone please Help me. Thanks for your Help..

Cloth

What IP and port were the existing listener on - what does lsnrctl status show? Which listener (if either) is your database registered against (lsnrctl services for both)? How exactly are you trying to connect - address, port, service name/SID; in fact the whole connection string? – Wicker

@AlexPoole : well i am not using any IP, i am using host name dbp.devProject.com with port number 4541 (this port is Free, not any services or Program is uses.) while lsnrctl shows me LISTENER has been already started. – Cloth

possible duplicate of How to connect Oracle 11G database using SQL developer from another computer in a LAN connection – Abortive

Did you restart the listener after making the changes? As already suggested, did you check the listener status? – Abortive

And most importantly, which listener file are you modifying? Make sue you are checking the listener in the DB server and not in the machine from which you are trying to connect. I mean just in case if you have Oracle software installed on client side too. – Abortive

Yes i have restarted Listener, checked status. All things are going okay, but i just cant access Database in my Project. I cant understand whats going wrong. – Cloth

You need to post the result of lsnrctl status and your connection string. and please mention the database version. – Abortive

i have modified Listener file placed in DBHOME/NETWORK/ADMIN/listener.ora – Cloth

Listener using listener name LISTENER has already been started Here, it is the result of lsnrctl – Cloth

Ok, please edit your question and add the following details: 1> lsnrctl status 2> connection string 3> DB version – Abortive

@LalitKumarB : I have modified Question, now please take a look. – Cloth

It doesn't seem like you really want a second listener. Your existing listener is on 1521, so your connection string should refer to that port too; but it is only listening on localhost, so it isn't visible to anyone else. Possibly related answer. You are also connecting to a SID, myDB. Either the listener needs to have the SID specified in the listener.ora, or you should be using the service name rather than the SID. You don't seem to have any SIDs or service names in the status output though, if that is complete. Is your database up? – Wicker

Listening on 127.0.0.1 - that's the loopback device, it wont work. The listener must bind to the IP address so that the service is then visible to client trying to connect. Find out what the IP address of the PC is (ipconfig), check that you can ping that IP address from the PC you are trying to connect from, then change the listener.ora and tnsnames.ora on the server PC to use the server PC ip address, check using netstat that there is a LISTENING service running on port 1521 after restart, set your client (tnsnames.ora) to point to IP address ofthe server PC then retry. – Carisacarissa

Thanks to You ALL. I have solved this Issue, got the Solution from @Carisacarissa 's Comment. I was used different Host Name and Different Port Number (Which i had provide while creating Listener but using lsnrctl status i have found this Issue) . I have just changed the Port Number following with Host Name and it Works. Thank You so Much Guys... :) @AlexPoole, @LalitKumarB and @Carisacarissa One more Question why and how should this Happens?? Why it takes port number randomly while i'm providing it.? – Cloth

Answer 1

Whenever you encounter this kind of errors while creating remote access for you database, follow these steps beforehand.

1. Create a Listener using Net Manager tool of Oracle or create it Manually.
2. After generating Listener, check out "listener.ora" [Located : ORACLE_INSTALLED_DIR\product\11.2.0\dbhome_1\NETWORK\ADMIN] file, for your entry. (is there any Entry for the Listener which you have created just before. If you found your Entry than and than do next steps otherwise do it from FIRST step.)
3. If you found the Entry for your Listener, Open Command Prompt and fire command lsnrctl status. This will give you all currently running Listeners List, find out the one which you created just before.
4. Check out the Host Name and Port Number for your Listener.
5. Generate the Connection String as per the details you found in lsnrctl status
6. Connect with Database, and You can access the Database from anywhere in your Network.

Cloth

Answer 2

If your host name and port number is listener then You could try this option by enabling port number in firewall settings.

Opening Ports in Windows Firewall

• Open the Control Panel. ...
• Open Windows Firewall. ...
• Click the Advanced Settings link. ...
• Select "Inbound Rules". ...
• Create "New Rule" under "Inbound Rules". ...
• Select "Port" from the first screen of the wizard. ...
• Choose TCP or UDP. ...
• Enter in the port range. ...
• Finish the wizard.

Comprehensible

I was able to connect locally on the same computer but not from the another computer(Mac) , First I tried disabling the firewall , then tried your suggestion. Both worked. – Jung

Answer 3

Create an entry in TNSNAMES.ORA file on the system from which you are trying to connect to the database. (TNSNAMES.ORA is located in ORACLE_INSTALLATION_PATH/Network/Admin/ folder)

Sample entry:

DB_HOST_NAME =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = DB_HOST_NAME)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = XE)

    )

  )

If you have more than one Oracle clients installed, you might need to update this in all those TNSNAMES.ORA

Ouster