Oracle Database Discussions

Are both OS Services for Oracle & Listener up & running?

Sure I have ideas.
You didn't consult documentation as requested in the Forums Etiquette post, in this case

http://download.oracle.com/docs/cd/B28359_01/network.111/b28316/troublestng.htm#CEGBDCJH

Can you explain why? Laziness? Not getting paid for troubleshooting?

--------
Sybrand Bakker
Senior Oracle DBA

Hi,
in specific to windows environment if you are not using tns string you need to set the SID

try
Post if any errors :)
Thanks

Hi sb92075 ,

Listener and db is up on Database server. I'm connecting from my windows client to the database. As I mentioned, I can connect using SID on connect prompt.
I can echo my SID as well. But it doesn't pickup the SID here in windows.

I can do the same from production UNIX box.

Thanks.

Hi CKPT,

I already set the environment from command prompt. It can echo the SID but can't login. Once I specify the db name, then it works.

_1. I setup the variable and it can echo the SID_

Y:\>set ORACLE_SID=TESTDB

Y:\>echo %ORACLE_SID%
TESTDB

_2. Trying to connect without specifying SID but fails_

Y:\>sqlplus system/******

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 12 11:52:19 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name:

_3. But connects when I specify SID_

Y:\>sqlplus system/******@TESTDB
Connected to:
Oracle Database 11g Release 11.1.0.6.0 - 64bit Production


Thanks.

As you mentioned that you are connecting from client machine, in that case you have to provide tnsname then only it will connect to server.

To connect from an another cliient/server you need to provide the tnsnames.

C:\> sqlplus "sys/password@ORCL"

Edited by: Prathmesh on Nov 12, 2010 10:40 PM

Hi Sybrand,

Thanks for the advice. I was reading docs and forums since yesterday. But not getting any clue, so Posted afterwards as I was unable to troubleshoot.
May be i'm doing silly mistakes, but not getting any clue.

Please advice.

Thanks.

C:\Documents and Settings>sqlplus sys/*****@prod as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Nov 12 09:58:44 2010
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.5.0 - 64bit Production

C:\Documents and Settings>set ORACLE_SID=prod
C:\Documents and Settings>sqlplus sys/****** as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Nov 12 09:59:03 2010
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
SQL>


ORA-12560: TNS:protocol adapter error
Cause: A generic protocol adapter error occurred.

Action: Check addresses used for proper protocol specification. Before reporting this error, look at the error stack and check for lower level transport errors. For further details, turn on tracing and reexecute the operation. Turn off tracing when the operation is complete.

Edited by: CKPT on Nov 12, 2010 10:33 PM

Please realize that we are NOT standing behind you.
Above can only succeed when logged on to DB Server.
was command above issued from DB Server?

I am facing ORA-12560 from windows client PC using sqlplus.

As you mentioned that you are connecting from client machine, in that case you have to provide tnsname then only it will connect to server.

Hi sb92075,

Yes, it works for Database server, ie, the unix box.

I posted this error for, when I try to connect from my client windows PC.

Do you mean, set ORACLE_SID=TESTDB and then

sqlplus system/**** never going to work from client pc? and I have to connect specifying the SID within the syntax as below?
sqlplus system/****@TESTDB

Thanks.

Correct

When "@REMOTE" is specified, then SQL*Net is used to access the database.
When "@REMOTE" is NOT specified, then Oracle tries to connect to a database local to system issuing the command.

Without "@REMOTE" exactly how is software to find correct system & DB to connect to?

Thanks guys....I didn't know that it never works :S
One related question,

I was testing Creating user externally to login using OS authentication. I can do so from Database unix box using below syntax.

export ORACLE_SID=TESTDB
sqlplus /
Connected to:
Oracle Database 11g Release 11.1.0.6.0 - 64bit Production
SQL> show user
USER is "OPS$SCOTT"


If I want to do the same from my client windows pc, what would be exact syntax for this?

Let me know, If I need to open a new thread for this question.

Thanks.

Same answer as before.
It can not be done.
Would it be OK if I created a OS user on my PC & logged into your DB without any password?

Thanks guys for all the guidence....appreciate that :)

Please mark this thread as ANSWER and open in new thread for this question.

Now that you have some idea of what works (and doesn't) maybe this will help you understand WHY

=================================

A couple of important points.

First, the listener is a server side only process. It's entire purpose in life is to receive requests for connections to databases and set up those connections. Once the connection is established, the listener is out of the picture. It creates the connection. It doesn't sustain the connection. One listener, with the default name of LISTENER, running from one oracle home, listening on a single port, will serve multiple database instances of multiple versions running from multiple homes. It is an unnecessary complexity to try to have multiple listeners or to name the listener as if it belongs to a particular database. That would be like the telephone company building a separate switchboard for each customer.

Additional notes on the listener: One listener is capable of listening on multiple ports. But please notice that it is the listener using these ports, not the database instance. You can't bind a specific listener port to a specific db instance. Similarly, one listener is capable of listnening on multiple IP addresses (in the case of a server with multiple NICs) But just like the port, you can't bind a specific ip address to a specific db instance.

Second, the tnsnames.ora file is a client side issue. It's purpose is for address resolution - the tns equivalent of the 'hosts' file further down the network stack. The only reason it exists on a host machine is because that machine can also run client processes.

Assume you have the following in your tnsnames.ora:

larry =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = curley)
    )
  )

Now, when you issue a connect, say like this:

$> sqlplus scott/tiger@larry

tns will look in your tnsnames.ora for an entry called 'larry'. Next, tns sends a request to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = curley).

Where is (HOST = myhost) on the network? When the request gets passed from tns to the next layer in the network stack, the name 'myhost' will get resolved to an IP address, either via a local 'hosts' file, via DNS, or possibly other less used mechanisms. You can also hard-code the ip address (HOST = 123.456.789.101) in the tnsnames.ora.

Next, the request arrives at port 1521 on myhost. Hopefully, there is a listener on myhost configured to listen on port 1521, and that listener knows about SERVICE_NAME = curley. If so, you'll be connected.



What can go wrong?

First, there may not be an entry for 'larry' in your tnsnames. In that case you get "ORA-12154: TNS:could not resolve the connect identifier specified" No need to go looking for a problem on the host, with the listener, etc. If you can't place a telephone call because you don't know the number (can't find your telephone directory (tnsnames.ora) or can't find the party you are looking for listed in it (no entry for larry)) you don't look for problems at the telephone switchboard.

Maybe the entry for larry was found, but myhost couldn't be resolved to an IP address (say there was no entry for myhost in the local hosts file). This will result in "ORA-12545: Connect failed because target host or object does not exist"

Maybe there was an entry for myserver in the local hosts file, but it specified a bad IP address. This will result in "ORA-12545: Connect failed because target host or object does not exist"

Maybe the IP was good, but there is no listener running: "ORA-12541: TNS:no listener"

Maybe the IP was good, there is a listener at myhost, but it is listening on a different port. "ORA-12560: TNS:protocol adapter error"

Maybe the IP was good, there is a listener at myhost, it is listening on the specified port, but doesn't know about SERVICE_NAME = curley. "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor"

Third: If the client is on the same machine as the db instance, it is possible to connect without referencing tnsnames and without going through the listener.

Now, when you issue a connect, say like this:

$> sqlplus scott/tiger

tns will attempt to establish an IPC connection to the db instance. How does it know the name of the instance? It uses the current value of the enviornment variable ORACLE_SID. So...

$> export ORACLE_SID=fred
$> sqlplus scott/tiger

It will attempt to connect to the instance known as "fred". If there is no such instance, it will, of course, fail. Also, if there is no value set for ORACLE_SID, the connect will fail.

check executing instances to get the SID

[oracle@vmlnx01 ~]$ ps -ef|grep pmon|grep -v grep
oracle    4236     1  0 10:30 ?        00:00:00 ora_pmon_vlnxora1

set ORACLE_SID appropriately, and connect

[oracle@vmlnx01 ~]$ export ORACLE_SID='vlnxora1
[oracle@vmlnx01 ~]$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 22 10:42:37 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now set ORACLE_SID to a bogus value, and try to connect

SQL> exit
[oracle@vmlnx01 ~]$ export ORACLE_SID=FUBAR
[oracle@vmlnx01 ~]$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 22 10:42:57 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory


Enter user-name: 

Now set ORACLE_SID to null, and try to connect

[oracle@vmlnx01 ~]$ export ORACLE_SID=
[oracle@vmlnx01 ~]$ sqlplus /scott/tiger

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 22 10:43:24 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-12162: TNS:net service name is incorrectly specified

Ok, that is how we get from the client connection request to the listener. What about the listener's part of all this?

The listener is very simple. It's job is to listen for connection requests and make the connection (server process) between the client and the database instance. Once that connection is made, the listener is out of the picture. If you were to kill the listener, all existing connections would continue. The listener is configured with the listener.ora file, but if that file doesn't exist, the listener is quite capable of starting up with all default values. One common mistake with the listner configuration is to specify "HOST=localhost" or "HOST=127.0.01". This is a NONROUTABLE ip address. LOCALHOST and ip address 127.0.0.1 always mean "this machine on which I am sitting". So, all computers are known as "localhost" or "127.0.0.1". If you specify this address, the listener will only be capable of receiving requests from the machine on which it is running. If you specified that address in your tnsnames file - on a remote client machine - the request would be routed to the machine on which the requesting client resides. Probably not what you want.

=====================================

Mind blowing!!!
Such a wonderful explanasion!!!

Much appreciated.
Thanks!!!