RE: connect as sysdba ora-1031

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_at_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_at_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_at_regina.ca

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_at_eds.com> 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_at_dat1

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

> connect sys_at_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_at_regina.ca] Sent: Thursday, November 17, 2005 10:40 AM To: Powell, Mark D
Subject: RE: connect as sysdba ora-1031

The first :

SQL> connect sys_at_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_at_eds.com> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Fuad Arshad Sent: Thursday, November 17, 2005 10:09 AM To: oracle-l_at_freelists.org
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_at_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.

--
http://www.freelists.org/webpage/oracle-l