Silly and Interesting – IV

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 - 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 - 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 - Production on Mon Mar 1 11:24:09 2010
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
ORA-01031: insufficient privileges
Enter user-name:

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 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s