| sqlplus /as sysdba, connected to idle instance ... other users work [message #387232] |
Wed, 18 February 2009 06:24  |
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)
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 #387260 is a reply to message #387232] |
Wed, 18 February 2009 10:09  |
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 #387263 is a reply to message #387232] |
Wed, 18 February 2009 10:30 |
 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
Sorry, maybe I am explaining myself incorrectly. My problem is with step 1: