Tuesday, July 14, 2015

ORA-01031: insufficient privileges on Windows

ORA-01031: insufficient privileges on Windows


sqlplus / as sysdba does not works (ORA-01031: insufficient privileges),
But sqlplus sys/oracle as sysdba works fine.
*********************************************************************************
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 2 17:15:06 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
==================================================================================
Verified remote_login_passwordfile to be EXCLUSIVE
==================================================================================
C:\Windows\system32> sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 2 17:15:06 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> show parameter remote
NAME TYPE VALUE
———————————— ———– ——————————
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
result_cache_remote_expiration integer 0
SQL>
==============================================================================================
Verified sqlnet.ora file is all good, for Windows SQLNET.AUTHENTICATION_SERVICES should be NTS
==============================================================================================
sqlnet.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install “Software Only”, this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
=========================================================================================
Verified user to be part of ORA_DBA group, which is missing below
=========================================================================================
c:\Oracle>echo %username%
malesh
c:\Oracle>NET LOCALGROUP ORA_DBA
Alias name     ORA_DBA
Comment        Oracle DBA Group
Members
——————————————————–
NT AUTHORITY\SYSTEM
The command completed successfully.
=================================================================================================
Using MMMC ( microsoft management console) Add the user “malesh” to be part of ORA_DBA OS group 
=================================================================================================
Start => All Programs => oracle – oraDB11g_home1 => Configuration and Migration Tools => Administration Assistant for Windows
OR
On command Prompt Type below
===========================
“C:\Oracle\product\11.2.0\dbhome_1\MMC Snap-Ins\ORAMMC11.exe” “C:\Oracle\product\11.2.0\dbhome_1\MMC Snap-Ins\Administration Assistant\orammcadm11″
=> expand Oracle Managed Objects => Computers => => Right Click on OS Database Administrators – computer => Add/remove
=> Under Domain Drop down Select your hostname => Select your username => click Add => OK
finally Close your MMMc ( microsoft management console) => save it while closing.
=========================================================================================
Verify again user “malesh” to be part of ORA_DBA group which was missing
=========================================================================================
c:\Oracle>NET LOCALGROUP ORA_DBA
Alias name     ORA_DBA
Comment        Oracle DBA Group
Members
——————————————————–
malesh
NT AUTHORITY\SYSTEM
The command completed successfully.
================================================================================================================
As above we see the user “malesh” is now part of LOCALGROUP ORA_DBA, Let’s check if sqlplus works as expected.
================================================================================================================
Close the command prompt if already open and re-open command prompt as administrator.
c:\Oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 2 19:11:09 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

No comments: