The Problem
Remote connections as SYSDBA are failing with ORA-1031 :
$ sqlplus sys/syspassword@sidname as sysdba SQL*Plus: Release 10.2.0.3.0 - Production on Thu Dec 13 19:37:53 2007 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. ERROR: ORA-01031: insufficient privileges
The REMOTE_LOGIN_PASSWORDFILE parameter is set to EXCLUSIVE:
SQL> show parameter remote_login NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile string EXCLUSIVE
The view v$pfile_users shows that the user was not granted the sysdba role:
SQL> select * from v$pwfile_users; USERNAME SYSDB SYSOP -------- ----- ----- SYS FALSE TRUE
The Solution
The issue is caused by the fact that the passwordfile was created with the NOSYSDBA option set to Y. When this is set to Y the remote SYSDBA connections are banned (the v$pwfile_users view shows that the user is not granted the SYSDBA role), irrespective to the fact that Database Vault is enabled or not.
The parameter nosysdba was added to the orapwd utility as part of the Database Vault product, however it was made available with the Standard and Enterprise Edition databases that do not have Database Vault installed too, nosysdba=y can be used to lockout remote sysdba connections, when set it will still allow you to connect as SYSOPER, thus still leaving some purpose to having a remote login passwordfile at all.
1. If the privileges associated with SYSOPER are sufficient, login as SYSOPER, this will allow you to perform some basic task such as to shutdown or startup the database:
$ sqlplus sys/manager@v102 as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 7 11:33:17 2008 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. ERROR: ORA-01031: insufficient privileges Enter user-name: sys/manager@v102 as sysoper Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
2. Grant SYSDBA to the user.
3. Create the passwordfile with the NOSYSDBA option set to N :
$ orapwd file=orapwsidname entries=3 password=syspassword force=y nosysdba=n
$ sqlplus sys/syspassword@sidname as sysdba SQL*Plus: Release 10.2.0.3.0 - Production on Thu Dec 13 19:41:12 2007 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options SQL>