ORA-01031 - insufficient privileges 1

[jmtuser]

Ive seen this problem on several other threads, but none of which match my issue.
When I set TWO_TASK or use sysdba /@train as sysdba I get this error:
======================================================
SQL*Plus: Release 9.2.0.6.0 - Production on Wed Nov 8 10:28:40 2006

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

Enter user-name: /@train as sysdba
ERROR:
ORA-01031: insufficient privileges
========================================================
When I unset TWO_TASK, I can login fine.

Remember the DB is not yet running, I just want to log into sqlplus to start it.
I need TWO_TASK eventually to allow split-mode server operation.

any ideas???

 

[LKBrwnDBA]

Set the ORACLE_SID environment variable and try a direct login:

sqlplus '/as sysdba'

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[jmtuser]

ORACLE_SID was already set , same result.
thanks for the response.
jt

 

[oradba101]

Hi, jmtuser

Try:

sqlplus "/ as sysdba"

Note the double quotes.

Regards,



William Chadbourne
Oracle DBA

 

[karluk]

The documentation on TWO_TASK says that it forces Oracle connections through sqlnet, rather than managing them locally on the server. As a consequence, "connect / as sysdba" type of connections don't work, because Oracle can't check the password file on the server to validate the sys password.

You have at least two work-arounds available. The first you have already discovered - unset the value of TWO_TASK temporarily while bringing the database up or down, and then reset it afterwards. The other is to actually supply the sys password when logging in as sysdba. The syntax "connect sys as sysdba" will cause sqlplus to prompt you for a password. Enter it, and you will be connected as sysdba so that you can start your database.

 

[jmtuser]

Here is what I get when I issue that command:

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Nov 14 13:20:37 2006

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

Enter user-name: "connect sys as sysdba"
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
HP-UX Error: 2: No such file or directory

thanks....

 

[SantaMufasa]

JMTUser,

This error means that the Oracle instance (that your connect string and/or system variables imply) is not running.

Could you please determine, then post here, the current values of your ORACLE_HOME and ORACLE_SID system variable values? Also, please confirm the platform and o/s version upon which your targe Oracle instance resides.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[karluk]

Sorry, I shouldn't have said "connect sys as sysdba". That syntax implies you are already logged into sqlplus and want to log in as a different user. When you are prompted for the username you should have typed "sys as sysdba":


SQL*Plus: Release 9.2.0.6.0 - Production on Tue Nov 14 13:20:37 2006

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

Enter user-name: sys as sysdba
Enter password: {type password here}

 

[jmtuser]

I tried sys as sysdba with and w/o quotes:

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Nov 14 14:30:41 2006

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

Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges



Enter user-name: "sys as sysdba"
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
HP-UX Error: 2: No such file or directory


Enter user-name:

 

[karluk]

Interesting. When I enter "sys as sysdba" without quotes and with TWO_TASK set to my instance name, I either connect sucessfully or get the error "ORA-01017: invalid username/password; logon denied" if I type the wrong password.

Are you certain that you know the correct password for sys? Specifically, if you unset TWO_TASK and connect to sqlplus with "sys as sysdba", do you connect successfully with the password you are using?

You should also check the value of the initialization parameter, "remote_login_passwordfile". It should be set to the value "EXCLUSIVE" for sysdba connections through sqlnet to work. To check this parameter, log in to a running instance with sqlplus and type the command

show parameter remote_login_passworfile

 

[karluk]

Correction:

show parameter remote_login_passwordfile

 

[jmtuser]

Ah yes, good sleuthing Karluk, my problem was actually two fold:
1. passwd was changed in the orapw<inst> file and
2. permissions of the file itself.

Here was the fix for both my prod and train instances:

1. Delete the old orapw<INST> files:
rm /opt/oracle/product/9.2.0.6/dbs/orapwtrain
rm /opt/oracle/product/9.2.0.6/dbs/orapwprod

2. As oracle user re-create the orapw<INST> files:
orapwd file=/opt/oracle/product/9.2.0.6/dbs/orapwprod \ password=<YOUR PASSWD HERE>
orapwd file=/opt/oracle/product/9.2.0.6/dbs/orapwtrain \ password==<YOUR PASSWD HERE>

3. Check the ownership and permissions (MUST BE THIS WAY):
-rwSr----- 1 oracle dba 3072 Nov 15 07:21 orapwprod
-rwSr----- 1 oracle dba 3072 Nov 15 07:21 orapwtrain

4. Double check the oracle_sid and oracle_home / two_task:
TWO_TASK=train
ORACLE_SID=train
ORACLE_HOME=/opt/oracle/product/9.2.0.6


5. Login to sqlplus - sys as sysdba (w/o any quotes)
SQL*Plus: Release 9.2.0.6.0 - Production on Wed Nov 15 07:31:07 2006

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

Enter user-name: sys as sysdba
Enter password: <ENTER THE PASSWD FROM ORAPWD ABOVE)
Connected to an idle instance.

SQL>

DONE

Thanks very much everyone for the help, I hope others find this info useful.

cheers,
jt

 

[SantaMufasa]

So, JT, with such excellent help, would it be a good idea to click the button "Thank karluk for this valuable post"? It seems to me that a Purple Star is in order.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]