Tuesday, January 10, 2012

ORA-1031: Insufficient Privileges


This is one of very common and frequently occuring error . According to the docs note ,the cause of the ORA-01031  is : 

Cause : An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login . 


Action :  Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the appropriate privilege at a higher label, ask the database administrator to re-grant the privilege at the appropriate label .

As we see the above action , nothing is explained in details and only mention about the privileges issues . But ORA-1031 may not only occurs with insufficient privileges but may other reasons too. Here i have try to cover all the possible reason of this error .

There are generally two method through which a user can connect to oracle database AS SYSDBA .
1.) OS authentication : 
2.) Password file authentication : 


1.) OS Authentication : OS authentication allows Oracle to pass control of user authentication to the operating system .The OS authentication is the process of verifying the identity of the user connecting to the database with the information managed by the OS. An OS user is able to use this authentication method if the following conditions are met: 

i.) The  user is a member of a special group : The OS user should belong to the OSDBA group in order to login as sysdba. On Unix/Linux the default name of  these group is "dba" and on Windows the name of the group is "ORA_DBA" .  


ii.) The OS authentication is allowed by the server settings(sqlnet.authentication_services is set correctly) : On Unix Parameter sqlnet.authentication_services must be set to (ALL) or to (BEQ, <other values>) for this to work. On Windows this parameter must be set to (NTS) or (NONE) or ALL. 


If we have configured the Operating System authentication and an OS user is a member of the special groups OSDBA or OSOPER then the OS user does not have to provide any credentials while connecting to the database as SYSDBA or AS SYSOPER from the oracle Server host machine when using the bequeath protocol. As long as the session is not established through the listener the OS Authentication will be used and the credentials provided will be ignored. This means that the users who are able to use the OS authentication can use any username and any password to connect to the database locally on the Oracle Server using the below syntax:


$sqlplus / as sysdba 
or 
SQL> connect any_username/any_password AS SYSDBA


2.) Password File Authentication :  The password file provides a method to authenticate privileged users from a remote (over sqlnet / listener) location . By default the user SYS gets an entry in the passwordfile when we create it so we can connect to a remote database . The credentials provided when connecting remotely as sysdba are compared to the contents of the passwordfile. For example 
$sqlplus sys/xxxx@db_name as sysdba 


Password file authentication is enabled by setting the database parameter remote_login_password file to "shared" or "exclusive". For more about Password file Click Here


If we  grant the SYSDBA or SYSOPER privilege to any additional user then that user will also get an entry in the passwordfile: the hashed password of that user is then copied to the passwordfile , when this user connects,the effective user will be SYS .

Note: When both OS authentication and password file authentication are enabled then the OS Authentication will be used. This means that we can connect with any username/password combination.


Here we will considering all the possible issue related to ORA-1031


Case 1  :  
One of the reason of getting ORA-1031 may be because the osuser is not the member of dba group .In case of  Linux , the osuser user must be the memeber of   "DBA"  group . To check the group the use the below command .
[oracle@Ramtech ~]$ id 
uid=501(oracle) gid=502(oinstall) groups=501(dba),502(oinstall)


In case of Window, OS user must be a member of ora_dba group . Check whether the OS user is a member of ORA_DBA or not by using the below command  : 
C:\>echo %username%                    ( for current osuser )
Neerajs


C:\>NET LOCALGROUP ORA_DBA
Alias name     ORA_DBA
Comment        Oracle DBA Group
Members
------------------------------------
Neerajs
NT AUTHORITY\SYSTEM
The command completed successfully.


If the osuser is not the member of the above group then add the osuser to the DBA and ORA_DBA in case of Linux and Window respectively. 

Case 2 :  
Check the value of  the SQLNET.AUTHENTICATION_SERVICES parameter in file $ORACLE_HOME/network/admin/SQLNET.ORA . 


On Unix/Linux  : This parameter should not be set if no strong authentication method is used. If such a method is being used then set the parameter to one of the following  values: 
SQLNET.AUTHENTICATION_SERVICES = (ALL)
 or 
SQLNET.AUTHENTICATION_SERVICES = (BEQ,<the strong auth method>)
Where  <the strong auth method> can be any combination of the following values: TCPS, KERBEROS5, RADIUS .I will cover more about the sqlnet parameter in my later post .


On Windows : This parameter should be set to NTS or if needed we can add other strong authentication methods besides NTS as such NONE . 
SQLNET.AUTHENTICATION_SERVICES = (NTS)
SQLNET.AUTHENTICATION_SERVICES = (NTS,TCPS)


Note: If the parameter is set to NONE then the OS authentication will be disabled and the user will have to provide a valid username/password combination to be able to connect to the database. On Windows the user who is not able to connect as sysdba using OS authentication might be a domain user. Check the following if we  are in this scenario: 

A.) It is important that this user is a direct member of the local ORA_DBA group .
B.) Oracle Service must be started as a user who is able to check the group membership for any domain user who might be connecting as sysdba locally.
C.) Check whether the clocks of the RDBMS Server and of the Active Directory Server are perfectly synchronized. Even small clock drifts can cause issues to the underlying kerberos authentication mechanism used by default on Windows. In these cases the ORA-1031 would be most of the times intermittent. 
D.) Check whether the Oracle Service is started by an user whose name contains non ASCII characters .


CASE  3 :
Check the value of parameter remote_login_passwordfile. This has to be set to either EXCLUSIVE or SHARED .


SQL> show  parameter  remote_login_passwordfile
NAME                                           TYPE           VALUE
-------------------------------            --------      ---------------
remote_login_passwordfile         string        EXCLUSIVE


If the parameter is not set correctly then modify it and then restart the database:
 SQL> alter system set remote_login_passwordfile=exclusive scope=spfile ;


Check whether the password file with the correct name exists in the right directory/folder and has the right ownership and permissions .
On Unix/Linux  : The password file with the name  orapw<ORACLE_SID> must exist in directory $ORACLE_HOME/dbs. If it does not exist then recreate it using the orapwd command .


[oracle@Ramtech dbs]$orapwd file=$ORACLE_HOME/dbs/orapw<sid> password=<password> force=y ignorecase=n
[oracle@Ramtech dbs]$ ls -l orapw*
-rw-r----- 1 oracle oinstall 1536 Jan 10 14:44 orapwcomcast


On Windows : The default location of the password file on Windows is folder %ORACLE_HOME%/database and the name of the password file must be pwd<%ORACLE_SID%>.ora. When the passwordfile authentication is being used Oracle searches for the password file in the following locations(in this exact order):

The folder pointed to by the registry key  HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HomeXX\ORA_<%ORACLE_SID%>_PWFILE
The folder pointed to by the registry key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HomeXX\ORA_PWFILE
The default location ( %ORACLE_HOME%\database)


If the password file does not exist in the right folder then create it using the orapwd command:


C:\> cd  %ORACLE_HOME/database 
C:\> orapwd file=pwd<sid>.ora password=<password> force=y nosysdba=n


Note: Make sure that the password file exists in the folder specified by the registry keys if these are set.


CASE 4 : 
Check whether the user was granted the SYSDBA privilege. Sometimes , we may get this error when we don't have sysdba privileges and try to connect as sysdba when sqlnet.authentication_services is NONE . For example : 
C:\>sqlplus scott/tiger@noida as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 10 17:56:27 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges


Check the v$pwfile_users  view to find the user which is having sysdba or sysoper privileges .
SQL> select * from v$pwfile_users;
USERNAME         SYSDB   SYSOP     SYSAS
------------          -------     --------     --------
SYS                    TRUE     TRUE       FALSE


If the user is not granted the privilege then log as sys user and run: 
SQL> grant SYSDBA to scott ;
where scott is the user wewant to use to connect as sysdba.


CASE   5 :  If  the problem is not solved after reviewing the above notes open a service request with Oracle Support .


Comments  welcome ....




Enjoy    J J J



Monday, January 9, 2012

How to Reconfigure OEM in Oracle 10g on Linux


We generally find that the OEM is not working due to the various reason . The best approach to solve this issue is to drop the OEM and reconfigure it . OEM may not work for various reason . In my case , I have install the oracle 10g on my system and then changed the computer host name . This leads  the OEM not working . The following are the steps to configure the OEM  : 

Step 1 :  Change IP to Machine name : 

i.)  Change the $ORACLE_HOME/network/admin/listener.ora file from an IP number to machine name.
ii.) Change the $ORACLE_HOME/network/admin/tnsnames.ora file from an IP number to a machine name.

Step 2 : Change the computer name : 
Open the network file and replace with new HOSTNAME . (in my case, I have changed from localhost to Ramtech.com) . 

[root@Ramtech ~]# vi /etc/sysconfig/network
NETWORKING=yes
NETWORKING_IPV6=no
HOSTNAME=Ramtech.com


save the changes and exit vi. Now edit the /etc/hosts file and set new host name . In hosts file edit the line and replace it with new one as
[root@Ramtech ~]# vi /etc/hosts
127.0.0.1          localhost
192.168.80.131   Ramtech.com

save and exit vi. The changes to /etc/hosts and /etc/sysconfig/network are necessary to make our changes persistent . Finally, we will restart the network to apply the changes we made to above file .

[root@Ramtech ~]# service network restart
Shutting down interface eth0:                              [  OK  ]
Shutting down loopback interface:                       [  OK  ]
Bringing up loopback interface:                            [  OK  ]
Bringing up interface eth0:                                   [  OK  ]

Now we use the hostname command to check the changes :
[root@Ramtech ~]# hostname
Ramtech.com

Step 3 : Drop the em configuration : 
First , we will drop the existing console and repository .

[oracle@Ramtech ~]$ export ORACLE_SID=noida
[oracle@Ramtech ~]$ emca  -deconfig  dbcontrol  db  -repos  drop

STARTED EMCA at Jan 8, 2012
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: noida
Listener port number: 1521
Password for SYS user:
Password for SYS user:

Do you wish to continue? [yes(Y)/no(N)] :  y 
 oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /home/oracle/product/10.2.0/db_1/cfgtoollogs/emca/noida/emca_2012-01-08_05-52-43-PM.log.
Jan 8, 2012 5:55:32 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration

WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Jan 8, 2012 5:55:33 PM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Jan 8, 2012 5:56:52 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jan 8, 2012 5:56:59 PM

Step 4 :  Drop the user and dependent :
If any of the below objects exists ,then we will face error , so before creating the OEM drop the following :

SQL> drop  user  SYSMAN   cascade; 
SQL> drop  user  MGMT_VIEW ; 
SQL> drop  role MGMT_USER ; 


and finally drop all the dependent synonyms of  "sysman" schema .

SQL> spool drop_synonym.sql 
SQL> SELECT  'drop public synonym '  || synonym_name || ';'   FROM dba_synonyms  WHERE    table_owner = 'SYSMAN';
SQL> @drop_synonym.sql 

Step 5 : Re-create the OEM : 

[oracle@Ramtech ~]$ export ORACLE_SID=noida
[oracle@Ramtech ~]$ emca -config dbcontrol db -repos create

STARTED EMCA at Jan 8, 2012 5:58:50 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: noida
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /home/oracle/product/10.2.0/db_1
Database hostname ................ Ramtech.com
Listener port number ................ 1521
Database SID ................ noida
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]:
Jan 8, 2012 5:59:31 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /home/oracle/product/10.2.0/db_1/cfgtoollogs/emca/noida/emca_2012-01-08_05-58-49-PM.log.
Jan 08, 2012 06:01:30 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Jan 08, 2012 06:17:38 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Jan 08, 2012 06:18:50 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jan 08, 2012 06:23:01 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jan 08, 2012 06:23:02 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://Ramtech.com:5500/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jan 08, 2012 06:23:02 PM

Step 6 :  Start and stop the dbconsole : 

$ emctl start dbconsole
$ emctl stop dbconsole
$ emctl status dbconsole

Note :  The same steps are used in window also. Sometimes we cannot able to recreate the oem(in case of window) in such case delete the  instance(using oradim) and again create the instance (using oradim) and then try to configure the OEM .


Enjoy     J J J


Saturday, December 31, 2011

A Very Very Happy New Years To U All



Receive my simple gift of LOVE
Wrapped with SINCERITY
Tied with CARE &
Sealed with BLESSINGS
2 Keep u HAPPY & SAFE all the life long.




Have a lucky and wonderful 2012
A Happy New Year! Grant that I
May bring no tear to any eye
When this New Year in time shall end
Let it be said I’ve played the friend
Have lived and loved and labored here
And made of it a happy year.






















My wishes for you, great start for Jan,
Love for Feb, peace for March,
No worries for April, fun for May,
Joy for June to Nov,happiness for Dec.



Have Fun and Enjoy  
J J J