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

Advertisement