The month starts with silly and interesting event 🙂 . How Interesting !!!!
This event is on “UNIX is CASE SENSITIVE”.
Today morning a friend on mine came to me asking can we login with sys using the tns_alias(@tns_alias), by which he actually meant is – “Can sys user login remotely to the database”?????
I said YES and showed him an example on one of my test server
-bash-3.00$sqlplus "sys/password@TEST as sysdba" SQL*Plus: Release 10.2.0.4.0 - Production on Mon Mar 1 11:22:01 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 11:22:03 SYS @ TEST >
He said he is trying the same and getting “insufficient privileges”.Something like this
-bash-3.00$sqlplus "sys/password@TEST as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Mar 1 11:24:09 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
ERROR: ORA-01031: insufficient privileges
As soon as someone says something like this, the first thing that comes to mind is the “PASSWORD FILE” and the second is “REMOTE_LOGIN_PASSWORDFILE”. So i immediately said him, the Password file wouldn’t be there and hence you are getting the error.
Now, is the interesting part!!!!
He says , he does have a “Password file”.
Hmmmmm…then why is he getting “insufficient privilege”
Please note that the database was created manually using the create database command and later password file was created using the orapwd utility.
On investigation we found that the password file name was orapwtest.ora,which seems to be ok, then why error.Interestingly, if i do “ps -ef | grep pmon” the output was :-
-bash-3.00$ ps -ef | grep pmon oracle 9823 9816 0 22:56:37 pts/2 0:00 grep pmon oracle 3750 1 0 07:03:35 ? 0:10 ora_pmon_TEST
Look here , “TEST” is in CAPITAL.And this is the reason for the password file not being used.The password file has the name “test”, and as UNIX is Case Sensitive it is not able to recognize this as the password file for the instance and hence prompting “insufficient privilege”.
Renaming the file as “orapwTEST.ora” enabled the sys user to login remotely (or using the @tns_alias and logging, even on the same server).
The sid TEST was in Capital because while creating the database, my friend has given the db_name=’TEST’ which created the instance name TEST in CAPS.It was a nice experience for me,so thought to just write it 🙂
UPDATE :- Another reason for “TEST” being in CAPS , could be cause of export ORACLE_SID=TEST and then would have created the database .
Comments are for free, you can comment 🙂