Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: different DB state via same SQL cmd

Re: different DB state via same SQL cmd

From: Frank van Bortel <>
Date: Fri, 11 Feb 2005 10:44:27 +0100
Message-ID: <cuhumd$6rv$>

dompie wrote:

> Frank van Bortel <> wrote in message news:<cuf8pp$i3o$>...

>>dompie wrote:
>>>Helo ,
>>>I noticed something very strange with the result of an SQL query.
>>>I have a script for a third party apps the back up the apps and the
>>>Oracle db that uses it.
>>>The script didn't work. After investigation, I noticed that within the
>>>script there is a function that checks if the database is up and
>>>running. The check is done via an sql statement. Basically, a
>>>connection via sqlplus is made using sysdba and the following
>>>statement is issued:
>>>"select state from v$instance;"
>>>If the output from this sql cmd returns "OPEN" then the database is
>>>considered up and running and the script continues. In my case, the
>>>script stop here.
>>>I did the following test:
>>>An 'su' from root to Oracle using 'su - oracle' and then the SQL
>>>returns the 'OPEN' state.
>>>An 'su' from root to Oracle using 'su oracle' and then the SQL returns
>>>an 'STARTED' state.
>>>So, the same sql cmd, executed by Oracle, returns a different output
>>>depending on the 'su'
>>>(note that the script does a 'su oracle' and not an 'su - oracle')
>>>The only reason I see is a difference in env settings.
>>>I still need to investigate why the two SQL results are different but
>>>I understand why it doesn't work.
>>>There is only on DB on the system using Oracle v9.
>>>Can this be explained? I cannot alter the db script since then I have
>>>no more vendor support but in any case, Oracle should return the same
>>>result (not?)
>>>Any help much appreciated.
>>>With kind regards,
>>Because a 'sqlplus system/manager' needs the environment variable
>>oracle_sid to know where to connect to.
>>Just do a su - oracle next time...
> Thanks for the feedback,
> but all ORACLE env params are already set before I execute the script.
> I cannot do an 'su - oracle' because this means that I would need to
> change the script which I don't want to do I will loose vendor
> support.
> I checked before sqlplus that oracle_sid was correct and it was.
> Also doing 'select instance_name' from v$instance;' then correct
> instance name is returned.
> It's is a very strange.
> Kd

Eh? Your Oracle parameters are set upon login - and that's what the '-' is about: su oracle: change to oracle, keep the current settings; su - oracle: change to oracle, use oracle's settings (from .cshrs, .bash_profile, .profile, or whatever).

If your vendor's script *overrides* these; fine - they should, probably. If the script does not work when you su - oracle, it's a sloppy script: some variables, set during the login of oracle are used, but never initialed from *within* the script.

Make your vendor change that!

Frank van Bortel
Received on Fri Feb 11 2005 - 03:44:27 CST

Original text of this message