Home » RDBMS Server » Networking and Gateways » sqlplus /as sysdba, connected to idle instance ... other users work (10g, sles 9)
sqlplus /as sysdba, connected to idle instance ... other users work [message #387232] Wed, 18 February 2009 06:24 Go to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
I have a problem with one of my listeners after rebooting a server. I can connect with my own user, but when I try as sysdba, it get the following errors:

1)
sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 18 13:08:10 2009

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

Connected to an idle instance.


2)
sqlplus /@unich as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 18 13:44:31 2009

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

ERROR:
ORA-01031: insufficient privileges


4)
sqlplus dirkm@unich

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 18 13:07:31 2009

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

Enter password:

Connected to:
Oracle Database 10g Release 10.2.0.2.0 - Production

SQL> show user
USER is "DIRKM"


5)
lsnrctl status

LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 18-FEB-2009 13:05:52

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=unic-db-test.unc.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.2.0 - Production
Start Date 18-FEB-2009 12:51:11
Uptime 0 days 0 hr. 14 min. 40 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/10GR2//network/admin/listener.ora
Listener Log File /opt/oracle/product/10GR2/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=unic-db-test.unc.com)(PORT=1521)))
Services Summary...
Service "unich" has 1 instance(s).
Instance "unich", status READY, has 1 handler(s) for this service...
Service "unichXDB" has 1 instance(s).
Instance "unich", status READY, has 1 handler(s) for this service...
Service "unich_XPT" has 1 instance(s).
Instance "unich", status READY, has 1 handler(s) for this service...
The command completed successfully


6)
echo $ORACLE_SID
unich


7)
tnsping unich

TNS Ping Utility for Linux: Version 10.2.0.2.0 - Production on 18-FEB-2009 13:06:34

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = unic-db-test)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = unich)))
OK (0 msec)


Cool
ping unic-db-test
PING unic-db-test.unc.com (6.11.2.208) 56(84) bytes of data.
64 bytes from unic-db-test.unc.com (6.11.2.208): icmp_seq=1 ttl=64 time=0.042 ms
64 bytes from unic-db-test.unc.com (6.11.2.208): icmp_seq=2 ttl=64 time=0.019 ms
64 bytes from unic-db-test.unc.com (6.11.2.208): icmp_seq=3 ttl=64 time=0.021 ms
64 bytes from unic-db-test.unc.com (6.11.2.208): icmp_seq=4 ttl=64 time=0.029 ms

--- unic-db-test.unc.com ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 2999ms
rtt min/avg/max/mdev = 0.019/0.027/0.042/0.011 ms
Re: sqlplus /as sysdba, connected to idle instance ... other users work [message #387252 is a reply to message #387232] Wed, 18 February 2009 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no point 3) maybe this is the root of the error. Grin

The reason is that you are not allowed to remotely connect / as sysdba. Check remote_os_authent parameter and password file.

Regards
Michel


Re: sqlplus /as sysdba, connected to idle instance ... other users work [message #387254 is a reply to message #387232] Wed, 18 February 2009 09:07 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
Smile Sorry, maybe I am explaining myself incorrectly. My problem is with step 1:

1)
sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 18 13:08:10 2009

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

Connected to an idle instance.



I used to be able to do this before the system reboot. I run this command directly on the database server (linux), logged in as user Oracle.
The database is up, and I can log onto the DB with my own login (dirkm), but not as sysdba, then I get "Connected to an idle instance".

ORACLE_SID exists for user Oracle (point 6 of my original post) ..... point 3 not being there - a typo on my part (cut & paste error) Surprised
Re: sqlplus /as sysdba, connected to idle instance ... other users work [message #387255 is a reply to message #387232] Wed, 18 February 2009 09:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you
Re: sqlplus /as sysdba, connected to idle instance ... other users work [message #387257 is a reply to message #387254] Wed, 18 February 2009 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The reason is that you are not allowed to remotely connect / as sysdba. Check remote_os_authent parameter and password file.

/ as sysdba
/@unich as sysdba
are NOT the same thing.

I don't think you reach the same instance with "/ as sysdba" and "dirkm@unich".


Regards
Michel

[Updated on: Wed, 18 February 2009 09:19]

Report message to a moderator

Re: sqlplus /as sysdba, connected to idle instance ... other users work [message #387258 is a reply to message #387232] Wed, 18 February 2009 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
UNIX: Checklist for Resolving Connect AS SYSDBA Issues
Doc ID: 69642.1

above from Metalink
Re: sqlplus /as sysdba, connected to idle instance ... other users work [message #387260 is a reply to message #387232] Wed, 18 February 2009 10:09 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
Thank you BlackSwan - my original post was too long ? (not sure if I understood the guidelines correctly)


Thank you Michel. I have made some progress, but I still do not have a fix for my original problem. I was not able to log in as sysdba, so I killed the pmon process. I then went into sql*plus and started the database, and now it works fine. I can now use:

sqlplus / as sysdba

I am guessing right now that there is something wrong with the automatic startup of oracle when the server reboots - could this be ?

Dirk
Re: sqlplus /as sysdba, connected to idle instance ... other users work [message #387261 is a reply to message #387232] Wed, 18 February 2009 10:15 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
PS. unfortunately I do not have access to Metalink, so I cannot view Doc ID: 69642.1


Dirk
Re: sqlplus /as sysdba, connected to idle instance ... other users work [message #387263 is a reply to message #387232] Wed, 18 February 2009 10:30 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Simply stated when you use "@alias", then SQL*Net is used
& can be used either on the DB server itself or remote client.
Conversely without "@alias" you MUST be logged onto the DB server itself.
When logged on to DB server, either OS authentication (no password) or a password can/must be provided.


I contend it is bad practice to try to log into DB "as sysdba" across SQL*Net, because it increases security risk to the DB.
From my perspective, it is more secure to only allow "as sysdba" access by the OS owner of the Oracle s/w when only logged onto DB server.

Your Milage May Vary (YMMV).

[Updated on: Wed, 18 February 2009 11:21]

Report message to a moderator

Previous Topic: Putty to establish a tunnel for Oracle
Next Topic: Connecting ORacle application (forms 6i on Oracle 9i) thro internet.
Goto Forum:
  


Current Time: Thu Mar 28 06:41:35 CDT 2024