• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

ORA-1031 When Connecting Remotely AS SYSDBA

by admin

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> 

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to Convert STANDARD ASM to FLEX ASM in 12C
  2. Difference between using srvctl vs using sqlplus for start/stop one or more Oracle Database Instances
  3. Oracle ASM : Shell script to map physical disk devices to ASMLIB disks
  4. Oracle SQL Script: To Obtain Session Information
  5. How to Offline a PDB Datafile in NOARCHIVELOG mode CDB which is not Open in Read Write
  6. How an SQL query is executed in Oracle Database
  7. What Is Oracle Key Vault
  8. How to shrink a Temporary Tablespace datafile in Oracle
  9. Table Vs. Materialized View
  10. How To Disable the Oracle WebLogic Server Default Welcome Page

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright