Thanks for the question.

Asked: August 19, 2007 - 11:21 pm UTC

Last updated: September 15, 2007 - 3:06 pm UTC

Version: 10.2.0.1.0

Viewed 100K+ times! This question is

You Asked

Hello,Mr. Tom.

When I issued SQLPLUS / AS SYSDBA ,I login a idle instance,but when I :
conn sys/a@fstest as sysdba .it looks OK.Please help me!

[oracle@localhost dbs]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 20 09:09:07 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn / as sysdba
<i>Connected to an idle instance.</i>

SQL> conn sys/a@fstest as sysdba
<u>Connected.</u>
SQL>

There only one ORACLE installed on the server,
and I check the ORACLE_HOME,ORACLE_SID ,both are ok.

[oracle@localhost dbs]$ echo $ORACLE_SID
fstest

Here are info of lsnrctl status and tnsnames.ora ,listener.ora

[oracle@localhost admin]$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 20-AUG-2007 09:35:10

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 20-AUG-2007 08:56:12
Uptime 0 days 0 hr. 39 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/10.2.0/network/admin/listener.ora
Listener Log File /oracle/product/10.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "fstest" has 1 instance(s).
Instance "fstest", status READY, has 3 handler(s) for this service...
Service "fstest_XPT" has 1 instance(s).
Instance "fstest", status READY, has 3 handler(s) for this service...
The command completed successfully




LSNRCTL> service
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "fstest" has 1 instance(s).
Instance "fstest", status READY, has 3 handler(s) for this service...
Handler(s):
"D001" established:23 refused:0 current:13 max:500 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=32777))
"D000" established:16 refused:0 current:14 max:500 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=32775))
"DEDICATED" established:3 refused:0 state:ready
LOCAL SERVER
Service "fstest_XPT" has 1 instance(s).
Instance "fstest", status READY, has 3 handler(s) for this service...
Handler(s):
"D001" established:23 refused:0 current:13 max:500 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=32777))
"D000" established:16 refused:0 current:14 max:500 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=32775))
"DEDICATED" established:3 refused:0 state:ready
LOCAL SERVER
The command completed successfully


tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle/product/10.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

FSTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = fstest)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)


listener.ora

# listener.ora Network Configuration File: /oracle/product/10.2.0//network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/10.2.0/)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

What elso should I check further?
Best regards!
Alan

and Tom said...

...
When I issued SQLPLUS / AS SYSDBA ,I login a idle instance,but when I :
conn sys/a@fstest as sysdba .it looks OK.Please help me!

....

/ as sysdba - connect to LOCAL instance as specified in the environment variables ORACLE_HOME + ORACLE_SID

sys/a@fstest as sysdba - connect OVER A NETWORK to a remote database, using the credentials sys/a - eg: resolve (using the tnsnames.ora or whatever naming you are using) fstest to a REMOTE MACHINE and connect to it.

these two connect strings are so utterly different - I don't know what you would expect? one connects to a local instance (that apparently doesn't exist OR is not started) and the other to a remote one.

Don't take this wrong, but perhaps you should not have SYSDBA privileges? You are not quite ready for them....

Rating

(14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Strange

A reader, August 21, 2007 - 9:40 pm UTC

"Don't take this wrong, but perhaps you should not have SYSDBA privileges? You are not quite ready for them.... "

This is the first time I have seen Tom reply in "wrong way".

August 22, 2007 - 12:39 pm UTC

I don't know what you mean - you seem to be saying I've said something bad, incorrect or wrong.

In all honesty - someone that doesn't know the difference between:

u/p@db as sysdba

/ as sysdba

is not ready to have sysdba granted to them. They need a little more training first. sysdba is excessively powerful - hardly anyone should have it (sysoper is probably all most people that have sysdba NEED to have and should have).

People with sysdba should pretty much be very experienced.

Do you have root on your production systems? Would you give root to someone that didn't know what dd, cp, ls are?

A reader, August 21, 2007 - 9:42 pm UTC

BTW, the answer to question OP put does deserve five stars.

SQLPLUS / AS SYSDBA

alan tan, August 21, 2007 - 10:00 pm UTC

Thanks,Tom.

 SQL> conn / as sysdba
onnected to an idle instance
SQL> conn sys/a@fstest as sysdba
Connected.
SQL>

It was a same db,the first statment is connect the db directly,the latter is from listner.
I am sure it was the same db.
Regards!
Alan

August 22, 2007 - 1:27 pm UTC

they are not the same db

when you connect / as sysdba - we take the ORACLE_HOME + ORACLE_SID, hash them and attempt to connect (attach) to that SGA identified by that shared memory key.

when you connect u/p@db as sysdba, we resolve db using the tnsnames.ora (or whatever naming service you are using) and ultimately, the listener turns that into an Oracle_HOME+ORACLE_SID and does the same.

The oracle_home+oracle_sid you are using when you "/ as sysdba" is coming from your environment.

The oracle_home+oracle_sid you are using when you "u/p@db as sysdba" is figured out by the listener, set in the environment and then you are connected.

They must be different.

When you started your database, it connected to the listener and registered itself. You started this database with a DIFFERENT oracle home and or oracle sid than you are using when you connect to it with sqlplus.

Your ORACLE_HOME+ORACLE_SIDs cannot be "both the same". That is the problem here.

I don't think so.

Qi, August 22, 2007 - 8:37 am UTC

Please check ORACLE_SID on the machine and compare it with:
select instance_name from v$instance;

They should be different.

August 22, 2007 - 2:02 pm UTC

or the oracle_homes are different

fact is: they are not, they will be different - either the sid or the home is.

Check the ORACLE_HOME

Donat Callens, August 22, 2007 - 9:37 am UTC

Your ORACLE_HOME is most probably set to
/oracle/product/10.2.0
whereas in your listener.ora file, we see that it is
/oracle/product/10.2.0/
This can lead to connection issues as these are not the same values... (see the additional '/' at the end)
Please try

$ ORACLE_HOME=/oracle/product/10.2.0
$ export ORACLE_HOME

before

$ sqlplus / as sysdba

alan, August 22, 2007 - 8:47 pm UTC

Thanks,Tom!
quote
The oracle_home+oracle_sid you are using when you "u/p@db as sysdba" is figured out by the listener, set in the environment and then you are connected.
Where deos listner get the ORACLE_HOME and ORACLE_SID?
the way as the "connect / as sysdba" or from the listener.ora?
Regards!
Alan

August 23, 2007 - 11:07 am UTC

the database registers with the listener and tells it (you appear to be using dynamic registration, I see no static entry in the listener.ora)

log in as the OS account that typed "startup", echo their sid/home

SQLPLUS / AS SYSDBA

alan, August 22, 2007 - 8:48 pm UTC

How can I tell what the DB "thinks" its ORACLE_HOME is?

Paul Moore, August 31, 2007 - 8:40 am UTC

Tom,
In your reply, you explain that when the database auto-registers with the listener, it registers its SID and ORACLE_HOME values. Is there a way of asking a running database what it sees as its ORACLE_HOME setting? (In the same way that ORACLE_SID can be seen from INSTANCE_NAME in V$INSTANCE).

In looking at the original poster's issue, I'd want to see the exact values of the ORACLE_HOME and ORACLE_SID environment variables (for the "/ as sysdba" case) as well as INSTANCE_NAME from the "sys/xx@aa as sysdba" connection, but I don't know how I'd look for a mismatch in ORACLE_HOME...

Thanks,
Paul.

September 05, 2007 - 9:01 am UTC

LSNRCTL> show displaymode
Service display mode is NORMAL
LSNRCTL> set displaymode
set displaymode RAW | COMPAT | NORMAL | VERBOSE: output mode for lsnrctl display
LSNRCTL> set displaymode verbose
Service display mode is VERBOSE
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dellpe)(PORT=1521)))
Services Summary...
Service "ora10gr2" has 1 instance(s).
Instance "ora10gr2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=/home/ora10gr2/bin/oracle)(ARGV0=oracleora10gr2)(ARGS='(LOCAL=NO)')(ENVS='VNCDESKTOP=dellpe:1 (tkyte),HOSTNAME=dellpe,TERM=xterm,SHELL=/bin/bash,HISTSIZE=1000,GTK_RC_FILES=/etc/gtk/gtkrc:/home/tkyte/.gtkrc-1.2-gnome2,WINDOWID=33554507,USER=tkyte,ORACLE_SID=ora10gr2,GNOME_KEYRING_SOCKET=/tmp/keyring-BIZbRx/socket,SESSION_MANAGER=local/dellpe:/tmp/.ICE-unix/3650,PATH=,MAIL=/var/spool/mail/tkyte,_=/home/ora10gr2/bin/sqlplus,PWD=/home/tkyte,INPUTRC=/home/tkyte/.inputrc,LANG=en_US.UTF-8,SQLPATH=/home/tkyte/Desktop/sqlstuff/,HOME=/home/tkyte,SHLVL=6,GNOME_DESKTOP_SESSION_ID=Default,LOGNAME=tkyte,ORACLE_PROC_MAKEFILE=/home/ora10gr2/precomp/demo/proc/demo_proc.mk,CLASSPATH=/home/ora10gr2/jdbc/lib/classes12.zip:.,DISPLAY=:1.0,ORACLE_HOME=/home/ora10gr2,COLORTERM=gnome-terminal,ORA_NET2_DESC=8,11,ORACLE_SPAWNED_PROCESS=1')(ENV_POLICY=NONE))
Service "ora10gr2XDB" has 1 instance(s).
Instance "ora10gr2", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:972 state:ready
DISPATCHER <machine: dellpe, pid: 17270>
(ADDRESS=(PROTOCOL=tcp)(HOST=dellpe)(PORT=32812))
Service "ora10gr2_XPT" has 1 instance(s).
Instance "ora10gr2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=/home/ora10gr2/bin/oracle)(ARGV0=oracleora10gr2)(ARGS='(LOCAL=NO)')(ENVS='VNCDESKTOP=dellpe:1 (tkyte),HOSTNAME=dellpe,TERM=xterm,SHELL=/bin/bash,HISTSIZE=1000,GTK_RC_FILES=/etc/gtk/gtkrc:/home/tkyte/.gtkrc-1.2-gnome2,WINDOWID=33554507,USER=tkyte,ORACLE_SID=ora10gr2,GNOME_KEYRING_SOCKET=/tmp/keyring-BIZbRx/socket,SESSION_MANAGER=local/dellpe:/tmp/.ICE-unix/3650,PATH=,MAIL=/var/spool/mail/tkyte,_=/home/ora10gr2/bin/sqlplus,PWD=/home/tkyte,INPUTRC=/home/tkyte/.inputrc,LANG=en_US.UTF-8,SQLPATH=/home/tkyte/Desktop/sqlstuff/,HOME=/home/tkyte,SHLVL=6,GNOME_DESKTOP_SESSION_ID=Default,LOGNAME=tkyte,ORACLE_PROC_MAKEFILE=/home/ora10gr2/precomp/demo/proc/demo_proc.mk,CLASSPATH=/home/ora10gr2/jdbc/lib/classes12.zip:.,DISPLAY=:1.0,ORACLE_HOME=/home/ora10gr2,COLORTERM=gnome-terminal,ORA_NET2_DESC=8,11,ORACLE_SPAWNED_PROCESS=1')(ENV_POLICY=NONE))
Service "ora9ir2" has 1 instance(s).
Instance "ora9ir2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=/home/ora9ir2/bin/oracle)(ARGV0=oracleora9ir2)(ARGS='(LOCAL=NO)')(ENVS='VNCDESKTOP=dellpe:1 (tkyte),HOSTNAME=dellpe,TERM=xterm,SHELL=/bin/bash,HISTSIZE=1000,GTK_RC_FILES=/etc/gtk/gtkrc:/home/tkyte/.gtkrc-1.2-gnome2,WINDOWID=33554507,USER=tkyte,ORACLE_SID=ora9ir2,GNOME_KEYRING_SOCKET=/tmp/keyring-BIZbRx/socket,SESSION_MANAGER=local/dellpe:/tmp/.ICE-unix/3650,PATH=/home/tkyte/bin:/home/ora9ir2/bin:/home/tkyte/bin:/home/ora10gr2/bin:/home/tkyte/bin:/home/ora10gr2/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/tkyte/bin:.:/home/tkyte/bin:/home/tkyte/bin:.,MAIL=/var/spool/mail/tkyte,_=/home/ora9ir2/bin/sqlplus,PWD=/home/tkyte,INPUTRC=/home/tkyte/.inputrc,LANG=en_US.UTF-8,SQLPATH=/home/tkyte/Desktop/sqlstuff/,HOME=/home/tkyte,SHLVL=6,GNOME_DESKTOP_SESSION_ID=Default,LOGNAME=tkyte,ORACLE_PROC_MAKEFILE=/home/ora9ir2/precomp/demo/proc/demo_proc.mk,CLASSPATH=/home/ora9ir2/jdbc/lib/classes12.zip:.,DISPLAY=:1.0,ORACLE_HOME=/home/ora9ir2,COLORTERM=gnome-terminal,ORA_NET2_DESC=7,10')(ENV_POLICY=NONE))
Service "ora9ir2XDB" has 1 instance(s).
Instance "ora9ir2", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: dellpe, pid: 17436>
(ADDRESS=(PROTOCOL=tcp)(HOST=dellpe)(PORT=32895))
The command completed successfully
LSNRCTL>

You need assistants :)

Anand, September 05, 2007 - 2:40 am UTC

Hi Tom,
Here's another amazing one that would have put your patience to test!!
I guess its about time, asktom started hiring assistants to deal with queries of this level (I'm available just incase your looking <grin> :p )

I completely agree with you when you say "you are not ready for sysdba".

Nice work Tom.
Cheers!
-Anand

Service "fstest_XPT"

A reader, September 05, 2007 - 9:26 am UTC

Thanks a lot! Tom:
I found a service from the result of lsnrctl status like this:
"Service "fstest_XPT"
Could kindly tell me what is this service?
Regards!
Alan

September 05, 2007 - 4:25 pm UTC

from Note 367744.1

...
In version 10.2 of the Oracle RDBMS, when an instance registers with its listeners it will also register by default a service with the name "<SID>_XPT" in addition to the normal service names. This service does not cause a problem and is intended for use within Data Guard environments for redo transport to the standby instance.
....

It was a bit snooty!

Richard, September 05, 2007 - 11:20 am UTC

Tom: Your original reply was a bit snooty.

A bit like when you get on a bus and ask the driver, "Do you go to London?", and getting a slightly snarled "Yeah!" as a reply.

Steve, September 05, 2007 - 5:28 pm UTC

A justified comment when the front of the bus has No. 54 London on it!!

Service "fstest_XPT"

A reader, September 05, 2007 - 8:42 pm UTC

Many thanks! Tom.
Alan

I hadn't seen DISPLAYMODE before!

Paul Moore, September 10, 2007 - 12:27 pm UTC

Thanks for the information about lsnrctl displaymode - I hadn't seen that before. Unfortunately, it doesn't seem to give the information on Windows (where ORACLE_HOME is derived from the registry).

D:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 9.2.0.6.0 - Production on 10-SEP-2007 17:12:22

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set displaymode
set displaymode RAW | COMPAT | NORMAL | VERBOSE: output mode for lsnrctl display
LSNRCTL> set displaymode verbose
Service display mode is VERBOSE
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=UKDCR054)(PORT=1521)))
Services Summary...
Service "OEM.UKDCR054" has 2 instance(s).
  Instance "OEM", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0
         LOCAL SERVER
         (ADDRESS=(PROTOCOL=beq)(PROGRAM=oracle)(ENVS='ORACLE_HOME=,ORACLE_SID=OEM')(ARGV0=oracleOEM)(ARGS='(LOCAL=NO)'))
  Instance "OEM", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3022744 refused:0 state:ready
         LOCAL SERVER
         (ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleoem)(ARGS='(LOCAL=NO)'))
Service "OEMXDB.UKDCR054" has 1 instance(s).
  Instance "OEM", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:51 refused:0 current:51 max:1002 state:ready
         DISPATCHER <machine: UKDCR054, pid: 2436>
         (ADDRESS=(PROTOCOL=tcp)(HOST=UKDCR054.uk.int.atosorigin.com)(PORT=2236))
The command completed successfully
LSNRCTL>

Would I be right in assuming that there is no way of getting the ORACLE_HOME information from within the database (as opposed to the listener)? I have a number of database auditing scripts which report general DB parameters, using the various V$ and DBA views, and it would be useful to be able to include the ORACLE_HOME (currently, I add that in manually). I can use the listener as you explain, but I still need to copy the answer into the report that way...

Paul.

September 15, 2007 - 3:06 pm UTC

well, show parameter spfile would just about do it on most any 10g database using stored parameter files (which should be all of them :) )

you might be able to glean it from a parameter value.

unfortunately, getting an environment variable in java is currently not in the realm of "supported".

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Strange

SQLPLUS / AS SYSDBA

I don't think so.

Check the ORACLE_HOME

SQLPLUS / AS SYSDBA

How can I tell what the DB "thinks" its ORACLE_HOME is?

You need assistants :)

Service "fstest_XPT"

It was a bit snooty!

Service "fstest_XPT"

I hadn't seen DISPLAYMODE before!

More to Explore

Security