• 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. New Connections to the Database lead to ORA-12518 or TNS-12518
  2. Understanding Real-Time SQL Monitoring in Oracle 11g
  3. How To Disable the Oracle WebLogic Server Default Welcome Page
  4. How to Clone a Pluggable Database from an RMAN Container Database Backup
  5. How to Create or Remove Restore Point on Oracle Standby database
  6. Oracle Database 12c2 : CPU_COUNT is Wrong
  7. Oracle RMAN Pluggable Database Point in Time Recovery
  8. Oracle SQL script to Show current Users and SQL being Executed
  9. Beginners Guide to Sequences in Oracle
  10. How To Change SYS user password for oracle database instance

You May Also Like

Primary Sidebar

Recent Posts

  • qm Command Examples in Linux
  • qm wait Command Examples in Linux
  • qm start Command Examples in Linux
  • qm snapshot Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright