Silly and Interesting V – “/ as sysdba” insufficient privileges

During a server migration activity(higher version of Sun Solaris Box), Oracle 9i was installed on new server,patched to 9.2.0.8 and the cold backup of database was restored.Oracle 9i installation was done with user “oracle” and group “dba”.For Oracle 10g installation user “ora10g” and group “dba10” was created.During the installation “dba10” was provided as the OS group name and rest of the installation was carried out successfully.

After, the successful installation, it was time to restore the database, but the PFILE was missing (luckily, only the pfile was missing 🙂 ).One of the team member made a pfile and tried to nomount the instance using the pfile.

bash-3.00$ echo $ORACLE_HOME
/oracle10g/u01/app/oracle/product/10.2.0
bash-3.00$ echo $ORACLE_SID
LIV
bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 13 09:46:49 2010

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

ERROR:
ORA-01031: insufficient privileges

Ooppssss, error in logging in 😦 . No OS level authentication??Why??

Checked the user :-

bash-3.00$ id -a
uid=501(ora10g) gid=101(dba10)

Checked if “sys as sysdba” works??

bash-3.00$ sqlplus "sys as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 13 10:15:35 2010

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

Enter password:
ERROR:
ORA-01031: insufficient privileges

Now what!!!!!! Where is the problem???

Created the password file and tried logging in as “sys as sysdba”

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

OK!!!! so password file is being used, that definitely makes sure its not OS authentication.What when we try to start database

SQL> startup nomount pfile='/oracle10g/u01/app/oracle/product/10.2.0/dbs/initLIV.ora';
ORA-01031: insufficient privileges

Again insufficient privileges!!!!To find the reason for insufficient privilege checked the PFILE and interestingly found remote_login_passwordfile parameter set to ‘NONE’.Changed it to ‘EXCLUSIVE’ and tried starting the database

********************************************************************************
With password file and remote_login_passwordfile='EXCLUSIVE'
********************************************************************************

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup nomount pfile='/oracle10g/u01/app/oracle/product/10.2.0/dbs/initLIV.ora';
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2126464 bytes
Variable Size             396201344 bytes
Database Buffers         1207959552 bytes
Redo Buffers                4325376 bytes
SQL>

Coming back to why “/ as sysdba” is showing insufficient privilege when the software was installed with “ora10g” user??

***********************************
CHECKING THE /etc/group file 
***********************************
bash-3.00$ cat /etc/group | grep dba*
dba::100:oracle
dba10::101:ora10g

************************************
CHECKING THE /etc/passwd/ file
************************************
oracle:x:500:100:Oracle DB user:/export/home/oracle:/bin/sh
ora10g:x:501:101:Oracle 10G DB user:/export/home/ora10g:/bin/sh

While checking the /etc/groups file, didn’t see “ora10g” user entry in “dba10” group and thought this could be the reason for why “/ as sysdba” is showing insufficient privilege and hence added it, but no luck.Still “/ as sysdba” showed insufficient privilege and couldn’t understand why so?

When added the user “ora10g” to “dba” group, i was able to login as “/ as sysdba”.This behavior is still mysterious to me.

Is multiple users with single group only allowed?Why was i not able to login when “ora10g” was in “dba10” group?Few questions still unanswered to me.

Hoping to get suggestions for you all !!!!!

Advertisements

One thought on “Silly and Interesting V – “/ as sysdba” insufficient privileges

  1. I too got same issue.. has been trying for 3days.. but no luck.. can anyone help me out from this…

    Thanks in advance..

    >>>>When added the user “ora10g” to “dba” group, i was able to login as “/ as sysdba”.

    How could you do this? I mean, Can you please elobrate the above?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s