• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Connecting to Oracle database installed on one pc to the java application from another pc

 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've installed Oracle 11g (desktop class) on my machine, and I can use it via sql developer on the same machine. Now how can I use the same database from another machine (for java application) in the LAN?
I tried using the jdbc thin driver as follows: jdbc:oracle:thin:@localhost:1521:skytech where Oracle is installed.
Do i need to install any server where oracle is installed to connect to my db from another machine?
My listener.ora description is as follows:

 
Campbell Ritchie
Sheriff
Posts: 51346
86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moving to our databases forum.
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for sharing forum to Database section.
Now can any one please help me with this problem ...
Thank you in advance.
 
Abhishk Singh
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


localhost points to the servers configured on the local machine. To hit server on machine B from machine A, you should be using that machine's IP instead of localhost. The URL would be like . To configure alias name for IP, you can modify hosts file in your system to point to IP eg. : <IP> dbServerURL on Windows and then use this string:
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Abhishk for replying. I want to know to which IP address should I provide public or private IP address ??
 
Abhishk Singh
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not sure about public or private IP.
When the machines are connected over the LAN, you can configure the the ip in LAN properties. The IP configured there should work.
IP configured in properties of machine B, should be used by the program running on machine A.
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So if my database is installed on machine A and I want to access database form machine B. Then I have to provide machine A's IP address right?? And also one thing do I need to install Oracle client on machine B.
 
Abhishk Singh
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So if my database is installed on machine A and I want to access database form machine B. Then I have to provide machine A's IP address right??

Correct.

do I need to install Oracle client on machine B

No. You need it only in case you want to run queries using the oracle client from machine B. But you will need database driver classes/jar file containing driver classes on machine B in your classpath.
 
Tim Holloway
Saloon Keeper
Posts: 18359
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You also need to ensure that the firewall (if any) of Machine A (the Oracle DB Server) allows access from Machine B (the client) via TCP port 1521 (the designated oracle communications channel). That includes any other firewalls that might exist between machine B and Machine A as well.
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

You need it only in case you want to run queries using the oracle client from machine B. But you will need database driver classes/jar file containing driver classes on machine B in your classpath.

From machine B I am running my java application which handled all database operation like DML so does that mean I have to install Oracle Client. and I change the host from localhost to IP address of machine A but then its giving me following error when I am trying to access the database from sql developer


Listener refused the connection with following error : ORA-12505
 
Abhishk Singh
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sanurag Dharme wrote:
but then its giving me following error when I am trying to access the database from sql developer


Are you getting this error when running the query on SQL developer installed on machine B trying to access database server installed on machine A?
This link describes probable causes and potential solutions: http://docs.oracle.com/cd/B19306_01/server.102/b14219/net12500.htm
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sanurag Dharme wrote:
From machine B I am running my java application which handled all database operation like DML so does that mean I have to install Oracle Client


No, just the relevant oracle driver jar file (which it appears you have).

Sanurag Dharme wrote:
. and I change the host from localhost to IP address of machine A but then its giving me following error when I am trying to access the database from sql developer


Listener refused the connection with following error : ORA-12505


"listener does not currently know of SID given in connect descriptor"

What does your connector string look like?
You seem to be reaching the server, but the server does not know of the SID you are supplying.
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
if you are talking about connection string then


that is the static IP address I assigned to machine A on which oracle is installed.
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sanurag Dharme wrote:if you are talking about connection string then


that is the static IP address I assigned to machine A on which oracle is installed.
Are you able to do a telnet on Machine B like the following?
What is the result of telnet?
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sanurag Dharme wrote:if you are talking about connection string then


that is the static IP address I assigned to machine A on which oracle is installed.


This is your SID:

Does the value held in that variable exist as a SID on the server at address 192.168.0.1?
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes Dave
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The ORA-12505 error suggests otherwise.
What does:
lsnrctl status
on your Oracle server give you?

It should show the SIDs (IIRC).
 
Abhishk Singh
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Check if the machine A is reachable from machine B. As Tapas has suggested, run the telnet command on machine B command prompt.
If it is connected properly, then may be database is not registered with the listener. Next time you get error, try restarting the db server on machine A and test your code again.
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried to connect from machine b using telnet like this

o 192.168.0.1 1521


but it gave me following error

Could not open connection to the host, on port 1521: connect failed


And also my listener is running but DBConsole is not running.
I also want you guys to check whether the tnsnames and listener file is correct or not

1. tnsnames


# tnsnames.ora Network Configuration File: D:\OracleHome\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = skytech)
)
)

SKYTECH =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = skytech)
)
)


2. listener


# listener.ora Network Configuration File: D:\OracleHome\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:\OracleHome\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:\OracleHome\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
)
)

ADR_BASE_LISTENER = D:\OracleHome


and the status of lsnrctl is


LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 06-NOV-2015 18:38:11
Uptime 0 days 0 hr. 19 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\OracleHome\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oraclehome\diag\tnslsnr\SANURAG-VAIO\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


If I have done something wrong please let me know. Thank you in advance.
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And while installation in system class it shows two class, desktop class and server. What is the difference between them and when to used ? Any help guys ??
 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your listener.ora doesn't contain an entry for SKYTECH. Add an entry for SKYTECH in your listener.ora and restart the listener.
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I add the entry but still its not working..
 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sanurag Dharme wrote:I add the entry but still its not working..


How did you add the entry? Directly into the file? Or using Net Manager or EM Console? Please show what your listener.ora looks like now and please also show the output of lsnrctl status.

Thanks.
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply..I used Net Manager to add entry.

1. Listener


# listener.ora Network Configuration File: D:\OracleHome\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:\OracleHome\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:\OracleHome\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)

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

ADR_BASE_LISTENER = D:\OracleHome


2. Output

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date 11-NOV-2015 10:37:26
Uptime 0 days 0 hr. 35 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\OracleHome\product\11.2.0\dbhome_1\network\admin\li
stener.ora
Listener Log File d:\oraclehome\diag\tnslsnr\SANURAG-PC\listener\alert\l
og.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SANURAG-PC)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "skytech" has 1 instance(s).
Instance "skytech", status READY, has 1 handler(s) for this service...
Service "skytechXDB" has 1 instance(s).
Instance "skytech", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>


If I didn't provide localhost it doesn't show the ouput...
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Its working now guys. After adding the entry I tried to connect two machine it worked.
Thank you guys for your helps...
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey guys again...I tried to insert some values in server side from client side but gave me following error


the network adapter could not establish the connection


to check if server side is ok or not i also tried to insert from server then following error occurs


ORA-12541 TNS no listener error


i tried to start listener but gave me above error.
I did not change anything in listener and tnsnames file. Please help me guys.
 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First of all, it would be helpful if when you say "I tried to insert", you make it clear where you tried to insert from e.g. your Java application, sqlplus, sql developer etc ...

Secondly, in your listener.ora file add the below:



And then try and restart the listener and show us what is contained in:

 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply. I tried that but didn't worked. That file does not get created and output is same as previous one.
 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sanurag Dharme wrote:Thanks for the reply. I tried that but didn't worked. That file does not get created and output is same as previous one.


What exactly was the output of the "previous one"? Why does the log file not get created? It is quite hard to see what the problem is without seeing any output or log files.

Maybe we should start from the beginning - I see earlier on you had specified the IP address of your database server as 192.168.0.1 in the thsnames file on your client - are you sure this is actually correct? Thatis usually the IP address of the wireless router and not the actual machine. I mean, if I run ipconfig on my machine, I get:



I also see that in one of the listener output the log is on SANURAG-PC, and on the other on SANURAG-VAIO. Why is there this difference?

It would help if you could follow the following steps:

1) Go to the machine where your database is running
2) Open up the command prompt, run ipconfig, and provide the information like I have done above
3) Provide listener.ora and sqlnet.ora from this machine
4) Provide output of lsnrctl status from this machine
5) Go to the machine from which you want to run your Java application
6) Open up the command prompt, run ipconfig, and provide the information like I have done above
7) Provide tnsnames.ora from this machine

Without having the above information it is quite difficult trying to identify where you are going wrong.
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1. ipconfig of Server/parent


C:\Users\SANURAG>ipconfig

Windows IP Configuration


Ethernet adapter Local Area Connection:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::d103:170f:7fdb:dc65%11
IPv4 Address. . . . . . . . . . . : 192.168.1.9
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.1.1

Tunnel adapter isatap.{E2C3369E-F9C6-4BA3-A92C-2CD598C80E37}:

Media State . . . . . . . . . . . : Media disconnected
Connection-specific DNS Suffix . :

C:\Users\SANURAG>


2. listener.ora


# listener.ora Network Configuration File: D:\OracleHome\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:\OracleHome\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:\OracleHome\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)

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

ADR_BASE_LISTENER = D:\OracleHome
TRACE_LEVEL_LISTENER=admin
diag_adr_enabled_listener=off
log_file_listener = listener_tmp.log



3. sqlnet.ora


# sqlnet.ora Network Configuration File: D:\OracleHome\product\11.2.0\dbhome_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)



4. tnsnames.ora


# tnsnames.ora Network Configuration File: D:\OracleHome\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

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

SKYTECH =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.9)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = skytech)
(SERVER = DEDICATED)
)
)

LISTENER_SKYTECH =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.9)(PORT = 1521))


5. lsnrtctl status


C:\Users\SANURAG>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 12-NOV-2015 09:52
:37

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 61: Unknown error
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.9)(PORT=1521))
)
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 61: Unknown error

C:\Users\SANURAG>



6. ipconfig of client/child


C:\Users\SANURAG>ipconfig

Windows IP Configuration

Ethernet adapter Local Area Connection:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::d8f5:e5e6:8b65:8a9d%11
IPv4 Address. . . . . . . . . . . : 192.168.1.8
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.1.1

Ethernet adapter Bluetooth Network Connection 2:

Media State . . . . . . . . . . . : Media disconnected
Connection-specific DNS Suffix . :

C:\Users\SANURAG>


Please tell me whats wrong here...
 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Great - now things are much clearer.

In an earlier post, you wrote:


Now this is clearly wrong, the IP address should be 192.168.1.9, and not 192.168.0.1. So first of all, make sure you correct this.

Ok, so your listener seems down. Without having access to the log, I do not know why it is down. So the next step is to try and bring it up, but first let's run the stop command - sometimes the listener did not shut down cleanly, and although the status shows as it being down, it is actually still running. Therefore, run the following:




Then after a few minutes:



If you still have problems, then attach the following two files (the second one should be created now, and that's the one I am ionterested in, but if it hasn't been created, then attach log.xml)
d:\oraclehome\diag\tnslsnr\SANURAG-PC\listener\alert\log.xml
d:\oraclehome\diag\tnslsnr\SANURAG-PC\listener\alert\listener_tmp.log
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I did what you told me to do. At first I did stop then start and finally status it was showing detailed though skytech was not running but status was showing the details.



C:\Users\SANURAG>lsnrctl status skytech

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 12-NOV-2015 23:05
:30

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.9)(PORT=1521))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=skytech)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date 12-NOV-2015 22:03:06
Uptime 0 days 1 hr. 2 min. 23 sec
Trace Level admin
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\OracleHome\product\11.2.0\dbhome_1\network\admin\li
stener.ora
Listener Log File D:\OracleHome\product\11.2.0\dbhome_1\network\log\list
ener_tmp.log
Listener Trace File D:\OracleHome\product\11.2.0\dbhome_1\network\trace\li
stener.trc
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.9)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

C:\Users\SANURAG>


After some time I start my computer again to check status that time it was showing nothing except previous error like no listener.
I am trying to attached the files but its giving me error. So how can I upload here..

 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, well the maximum total size is 1MB, so maybe you could just copy and paste the latest entries from
Listener Log File D:\OracleHome\product\11.2.0\dbhome_1\network\log\listener_tmp.log

But before you do that, follow these steps.

1) Reboot your database server.
2) Run ipconfig once it restarts - make sure the IP address hasn't changed.
3) Connect to SKYPATH from your database server - make sure you can log in
4) Do lsnrctl status skypath and look for the following lines:
Service "skytech" has 1 instance(s).
Instance "skytech", status READY, has 1 handler(s) for this service...

If all this is good, then you should be able to connect from your Java program - remember, if the IP address of your database server has changed, then update the IP address in the JDBC connection string.

5) If step 4 fails because the listener isn't started, start it and look for the lines:

Service "skytech" has 1 instance(s).
Instance "skytech", status READY, has 1 handler(s) for this service...

6) If you still do not see these lines, then first update tnsnames.ora if the IP address has changed, and then connect to your database as sys or using OS authentication, and run:
alter system set local_listener='LISTENER_SKYTECH' scope=both;
alter system register;

7) Wait 5 minutes, and then do lsnrctl status to see if you now have the lines:
Service "skytech" has 1 instance(s).
Instance "skytech", status READY, has 1 handler(s) for this service...
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi sorry for the late reply.
I tried by doing your way but didn't work. When I check the output of lsnrctl status it show only one service running and that wasn't skytech.
Can you show me, if I you were trying to do this what was your listener ans tnsnames files look like. May be by this I will be able to know where I am going wrong.
Thank you.
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do i need to add entry of my ip address in host file ? If yes how to do it
 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don'tthink there is a problem with your listener configuration file, it did come up before.

If you want to update the hosts file on Windows, find it here and edit it with a text editor.

C:\Windows\System32\drivers\etc

I would suggest you change

# 127.0.0.1 localhost
to
127.0.0.1 localhost

i.e. get rid of the # - this shouldn't be the problem, but it's just better to do this.

Next, in your tnsnames.ora and listener.ora, replace all occurrences of 192.168.#.# with 127.0.0.1

Next, try and restart the listener, and if there are issues, then provide your listener.ora, tnsnames.ora and attach d:\oraclehome\diag\tnslsnr\SANURAG-PC\listener\alert\listener_tmp.log. If this file is too large, then open it, and copy and paste the last 500 entries here.
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for replying. I add the ip address and host name of server into client host file and after that i did the process from the beginning like i add the ip address into listener and tnsnames file with localhost already present.
Now its working but every time starting my server it shows "no listener" error i have to start the listener then it works. Can you tell me why this error is coming and how to stop it. Thank you...
 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry, I don't quite get what you are saying. Are you saying that each time you reboot your PC your listener isn't running and you have to manually start it?
 
Sanurag Dharme
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Everytime I reboot my PC won't listener running and I have to manually start it. but after started everything works properly.
 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Go to: Control Panel -> Administrative Tools -> Services

Find the Oracle listener service (it will be called something like OracleOraDb11g_home1TNSListener), right click on it, go to Properties, and change Startup Type to Automatic.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic