Oracle

sys as sysdba – insufficient privileges

Working on the Solaris server i always use ” / as sysdba” to login to the database.While creating a backup scripts, i used

sqlplus -s "[email protected] as sysdba" 

Before testing the script, did a tnsping

bash-3.00$ tnsping iddb
TNS Ping Utility for Solaris: Version 10.2.0.3.0 - Production on 28-MAY-2010 17:00:44
Copyright (c) 1997, 2006, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST= (ADDRESS =(PROTOCOL=TCP)(HOST=XXX.XXX.XXX.XX)(PORT=XXXX))) (CONNECT_DATA= (SERVICE_NAME=iddb)))
OK (10 msec)

A log file is generated whenever the backup script is run.While checking the log file i saw the below error

ERROR:
ORA-01031: insufficient privileges

Hmmm…time for check –

bash-3.00$ sqlplus sys as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Fri May 28 17:29:51 2010
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
bash-3.00$
bash-3.00$ sqlplus [email protected] as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Fri May 28 17:30:28 2010
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Enter password:
ERROR:
ORA-01031: insufficient privileges

Check whether passwordfile exists or not??

bash-3.00$ cd $ORACLE_HOME/dbs
bash-3.00$ ls -lrt
total 21198
-rw-r-----   1 oracle1  dba         8385 Sep 11  1998 init.ora
-rwxr-xr-x   1 oracle1  dba         3854 Jul 24  2008 initiddb.ora
-rw-r-----   1 oracle1  other       2048 Sep 10  2009 orapwiddb.ora
-rw-r-----   1 oracle1  dba         7680 May 17 14:11 spfileiddb.ora
bash-3.00$

“orapwiddb.ora” file is present in ORACLE_HOME/dbs.Next was to check v$pwfile_users view

SQL> select * from v$pwfile_users;
no rows selected
SQL> show parameter remote_login
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL>

Opppssss, “no rows selected”.Created a new password file

bash-3.00$ orapwd file=$ORACLE_HOME/dbs/orapwiddb.ora password=xyzyzx#123 entries=2
bash-3.00$ ls -lrt
total 24202
-rw-r-----   1 oracle1  dba         8385 Sep 11  1998 init.ora
-rwxr-xr-x   1 oracle1  dba         3854 Jul 24  2008 initiddb.ora
-rw-r-----   1 oracle1  other       2048 Sep 10  2009 orapwiddb.bkp
-rw-r-----   1 oracle1  dba         7680 May 17 14:11 spfileiddb.ora
-rw-r-----   1 oracle1  dba         1536 May 28 17:52 orapwiddb.ora
bash-3.00$
bash-3.00$

Granting sysdba privilege to SYS

SQL>  grant sysdba to sys;
 grant sysdba to sys
*
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled

Something wrong!!!!! It say “password file missing or disabled”, even after creating the password file.Seems like, oracle is not able to read “orapwiddb.ora” file.Created a new password file with name “orapwiddb”

bash-3.00$ orapwd file=$ORACLE_HOME/dbs/orapwiddb password=xyzyzx#123 entries=2
bash-3.00$ ls -lrt
total 24202
-rw-r-----   1 oracle1  dba         8385 Sep 11  1998 init.ora
-rwxr-xr-x   1 oracle1  dba         3854 Jul 24  2008 initiddb.ora
-rw-r-----   1 oracle1  other       2048 Sep 10  2009 orapwiddb.bkp
-rw-r-----   1 oracle1  dba         7680 May 17 14:11 spfileiddb.ora
-rw-r-----   1 oracle1  dba         1536 May 28 17:58 orapwiddb

Lets try grant sysdba privilege to SYS now,

SQL> grant sysdba to sys;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
bash-3.00$ sqlplus
SQL*Plus: Release 10.2.0.3.0 - Production on Fri May 28 18:05:01 2010
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Enter user-name: [email protected] as sysdba
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
SQL> exit

12 thoughts on “sys as sysdba – insufficient privileges

  1. Anand,
    Thanks a lot!
    I had a similar but slightly different problem – I named the pw file with the wrong case (oraSID instead of orasid). Was driving me crazy!
    Your blog post has pushed me in the correct direction. I love it when things finally make sense!
    Serge

  2. sir i connect sys .cant connect becoze tnsname file is not here,somebody delete that,,……how it solved…..((((.the error msg is insufficient privilages….)))

  3. Hi anand!
    No one can explain as good as this one!
    Thanks for great explanation, also thanks for the user639256
    for whom you helped in OTN discussion from where i got this solution!
    Thanks a bundle!

Leave a Reply