Re: different DB state via same SQL cmd

Frank van Bortel <fvanbortel_at_netscape.net> wrote in message news:<cuf8pp$i3o$1_at_news5.zwoll1.ov.home.nl>...
> 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,
> >
> > Kris
> 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 Received on Fri Feb 11 2005 - 03:23:35 CST