Pages

Wednesday 21 March 2012

ORA-01031: insufficient privileges while connecting to database as sysdba

While I was trying to connect to the database, which was created recently, I was getting error as


#> sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 21 02:52:18 2012

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

ERROR:
ORA-01031: insufficient privileges


Enter user-name:
oracle@test.sangam /orasw/app/oracle/product/10.2.0/bin

Solution:


1. Check the  uid and gid of an oracle user. 

#> id oracle
uid=1014(oracle) gid=3005(oinstall) groups=100(users),3004(dba),3005(oinstall),8500(oemdba)

But the problem was db was created giving "users" as dbagroup.

You can check this in $ORACLE_HOME/rdbms/lib/config.c or $ORACLE_HOME/rdbms/lib/config.s file (depends on the OS type)

Eg:

#> cat config.c

/*  SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.  */
/*  Refer to the Installation and User's Guide for further information.  */

/* IMPORTANT: this file needs to be in sync with
              rdbms/src/server/osds/config.c, specifically regarding the
              number of elements in the ss_dba_grp array.
 */

#define SS_DBA_GRP "users"
#define SS_OPER_GRP "users"
#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

Once file is updated, relink the oracle binaries as below
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH
 $ORACLE_HOME/bin/relink all
writing relink log to: /oracle/product/11.2.0/install/relink.log


2.check the same thing exist in /etc/passwd file.

#> cat /etc/passwd |grep oracle
oraem:x:24872:8500::/opt/oracle/oraem:/bin/csh
oracle:x:1014:3005::/orasw:/usr/bin/ksh
root@test.sangam /orasw/app/oracle/product/10.2.0

3.Check the permissions of all oracle binaries.

4.Assign the "users" group gid to "oracle" by editing /etc/passwd file.

 #> cat /etc/passwd |grep oracle
oraem:x:24872:8500::/opt/oracle/oraem:/bin/csh
#oracle:x:1014:3005::/orasw:/usr/bin/ksh
oracle:x:1014:100::/orasw:/usr/bin/ksh
root@test.sangam /orasw/app/oracle/product/10.2.0

5. Now try to connect to the database. You would be able to connect to the database.


#> . oraenv
ORACLE_SID = [TESTDB] ?
oracle@test.sangam /orasw/app/oracle/product/10.2.0/bin
#> sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 21 02:59:12 2012

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

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2026224 bytes
Variable Size             272631056 bytes
Database Buffers          792723456 bytes
Redo Buffers                6361088 bytes
Database mounted.
Database opened.
SQL>


2 comments:

  1. how to all those with dos commands?

    ReplyDelete
    Replies
    1. You have to check in User account and get the modification done from Windows Administrators.

      Thank you!

      Regards,
      Sangamesh

      Delete