RE: connect as sysdba ora-1031

  • From: "Shannon St. Dennis" <SStDennis@xxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 17 Nov 2005 12:01:51 -0600

Sorry for the short post earlier. I had to get a meeting.

We use password files and we do not usually get those issues.

my point I was trying to make is:

sqlplus /nolog
connect / as sysdba

and
sqlplus '/ as sysdba'

will connect to the database indicated by the environment variables ORACLE_SID 
and/or TWO_TASK

whereas
sqlplus /nolog
connect sys@dbname as sysdba

will connect you to the database called dbname (which is not necessarily the 
database indicated by the ORACLE_SID variable).


I get this issue if (for example):

ORACLE_SID=oradb1

sqlplus /nolog
connect sys@oradb2 as sysdba
Enter password:
Connected.
this connected me as sysdba to oradb2 (which has a password file)
SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges

this errored during the connection as sysdba to oradb1 (which does not have a 
password file)

Fred's note does not indicate whether his ORACLE_SID is set to dbname or 
something else.  If it is set to dbname, then the two statements are equal, and 
there is a problem.
If oracle_sid is not dbname, then the issue becomes whether the database that 
oracle_sid is set to has a password file.


Thanks
Shannon

Shannon St. Dennis
Database Administrator
City of Regina
(306) 777-7415 (phone)
(306) 777-6804 (fax)
sstdennis@xxxxxxxxx

Always remember some people are like slinkies not really good for anything but 
they still bring a smile to your face when you push them down a flight of stairs

>>> "Powell, Mark D" <mark.powell@xxxxxxx> 17/11/2005 10:36:37 am >>>

Shannon, your reply just refers to obvious details that have nothing to do with 
my question.  I wanted to know exactly how Fred got into sqlplus so I could try 
to see if it makes any difference.  Unfortunately I was too terse in my post.
 
Fred has since replied to me that he gets the same error either way be gets 
into sqlplus and as a  developer is going to use the database I was going to 
setup a password file to use with I will not be able to pursue this.  It is too 
bad work gets in the way of fun.

From Fred >>
sqlplus /nolog
connect /  as sysdba

and sqlplus '/ as sysdba'
<< both result in same error


Using 9.2.0.6 with no password file I get the following, (expected results):

$ sqlplus /nolog

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Nov 17 10:54:15 2005

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

 > connect sys@dat1
Enter password:
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper

 > connect sys@ut1 as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges

 > connect / as sysdba
Connected.

$ sqlplus "/ as sysdba" works fine for me.

I will keep an eye on the thread to see if this is expected behavior of some 
kind or a problem.  Shannon, do you use a password file?  If so you can try to 
duplicate Fred's connections and see if you get the same behavior.  You could 
then post your results and Oracle version so the board can determine if this is 
expected or bug.

-- Mark D Powell --


From: Shannon St. Dennis [mailto:SStDennis@xxxxxxxxx]
Sent: Thursday, November 17, 2005 10:40 AM
To: Powell, Mark D
Subject: RE: connect as sysdba ora-1031



The first :

SQL> connect sys@dbname  as sysdba
connects you to dbname as sysdba

the second:
SQL> connect / as sysdba
connects you to the local database (whatever ORACLE_SID is set to)




>>> "Powell, Mark D" <mark.powell@xxxxxxx> 17/11/2005 9:30:03 am >>>

Fred, you did not list the sqlplus command option which you used.
sqlplus /nolog
then at the prompt
> connect / as sysdba

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Fuad Arshad
Sent: Thursday, November 17, 2005 10:09 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: connect as sysdba ora-1031



We're having this issue  which started today and we cant get ot the bottom  of 
it


looged in as the oracle owner TWO_TASK is unset
 SQL> connect sys@dbname  as sysdba
Enter password:
Connected.
SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

now the user i'm using  is the oracle software owner
 password file is not corrupted  remote_login_password is also set to exclusive.


what steps can i take to further debug this. i"m thinking about  oradebug but 
then since i'm disconnected how would oradebug capture anything .
need some advice before i  brave the tar  and all the same questions  with 
oracle.








DISCLAIMER: The information transmitted is intended only
for the addressee and may contain confidential,
proprietary and/or privileged material. Any
unauthorized review, distribution or other use
of or the taking of any action in reliance upon
this information is prohibited. If you received
this in error, please contact the sender and
delete or destroy this message and any copies.





DISCLAIMER: The information transmitted is intended only
for the addressee and may contain confidential,
proprietary and/or privileged material. Any
unauthorized review, distribution or other use
of or the taking of any action in reliance upon
this information is prohibited. If you received
this in error, please contact the sender and
delete or destroy this message and any copies. 

Other related posts: