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 "sys@iddb 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 sys@iddb 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: sys@iddb 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
Advertisements

9 thoughts on “sys as sysdba – insufficient privileges

  1. 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!

  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….)))

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