SEO

Platform - Oracle 9.2 EE on Solaris 8

I was putting together a note asking for a second set of eyes, but as
I reviewed it before posting, I found the solution. But I still don't
understand everything about it.

Running a shell script that calls SQL*Plus to execute a sql script.
Ran fine from the command line but failed when run from crontab.
Output showed that after connecting, the first SQL statement returned
a ORA-01034: ORACLE not available.

I finally realized that even though I was setting ORACLE_SID in the
shell script, I wasn't exporting it so that SQLPlus could see it.
After adding 'export ORACLE_SID' to the shell script it ran like a
champ.

But I still don't understand why it got (or seemed to get) a
successful connection in the first place, or once it had it, a DML
statement would return 'ORACLE not available'.

The actual invocation of sqlplus in the shell script looks like this:

sqlplus "/ as sysdba" @mysqlscript


and the output looked like this. Note the successfull 'connected'
just before the DELETE statement.

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Oct 13 14:42:00 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected.
SQL> DELETE FROM mytable
2 WHERE sample_time < sysdate - &1
3 ;
old 2: WHERE sample_time < sysdate - &1
new 2: WHERE sample_time < sysdate - 15
DELETE FROM mytable
*
ERROR at line 1:
ORA-01034: ORACLE not available


I'm sure this is something I should know. Maybe I do know it, but am
just having a DSA (Dumb S*** Attack (tm)) I think my memory is maxed
out, and everytime I have to remember something new, something else
has to be tossed to make room. One of these days it will be my own
name that gets thrown out to make room for the names of seven new
databases to be created . . .

Ed Stevens wrote:
> sqlplus "/ as sysdba" @mysqlscript
>
>
> and the output looked like this. Note the successfull 'connected'
> just before the DELETE statement.
>
> SQL*Plus: Release 9.2.0.1.0 - Production on Wed Oct 13 14:42:00 2004
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> Connected.
> SQL> DELETE FROM mytable
> 2 WHERE sample_time < sysdate - &1
> 3 ;
> old 2: WHERE sample_time < sysdate - &1
> new 2: WHERE sample_time < sysdate - 15
> DELETE FROM mytable
> *
> ERROR at line 1:
> ORA-01034: ORACLE not available
>
>
> I'm sure this is something I should know. Maybe I do know it, but am
> just having a DSA (Dumb S*** Attack (tm)) I think my memory is maxed
> out, and everytime I have to remember something new, something else
> has to be tossed to make room. One of these days it will be my own
> name that gets thrown out to make room for the names of seven new
> databases to be created . . .

Personally I'd expect an 'Connected to an idle instance'. Try what you get
if you do

export ORACLE_SID=foo
sqlplus "/ as sysdba"

since I don't have a Solaris at hand I can't check the outcome. But it should
get you on the right track anyway.

HTH

Holger

Holger Baer wrote:
> Ed Stevens wrote:
>
>> sqlplus "/ as sysdba" @mysqlscript
>>
>>
>> and the output looked like this. Note the successfull 'connected'
>> just before the DELETE statement.
>>
>> SQL*Plus: Release 9.2.0.1.0 - Production on Wed Oct 13 14:42:00 2004
>>
>> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>>
>> Connected.
>> SQL> DELETE FROM mytable
>> 2 WHERE sample_time < sysdate - &1
>> 3 ;
>> old 2: WHERE sample_time < sysdate - &1
>> new 2: WHERE sample_time < sysdate - 15
>> DELETE FROM mytable
>> *
>> ERROR at line 1:
>> ORA-01034: ORACLE not available
>>
>>
>> I'm sure this is something I should know. Maybe I do know it, but am
>> just having a DSA (Dumb S*** Attack (tm)) I think my memory is maxed
>> out, and everytime I have to remember something new, something else
>> has to be tossed to make room. One of these days it will be my own
>> name that gets thrown out to make room for the names of seven new
>> databases to be created . . .
>
>
> Personally I'd expect an 'Connected to an idle instance'. Try what you get
> if you do
>
> export ORACLE_SID=foo
> sqlplus "/ as sysdba"
>
> since I don't have a Solaris at hand I can't check the outcome. But it
> should
> get you on the right track anyway.
>
> HTH
>
> Holger

ORACLE_SID=foo sqlplus "/ as sysdba" @mysqlscript would be better... is
&1 defined?

Steve

Steve wrote:
>>
>> Personally I'd expect an 'Connected to an idle instance'. Try what you
>> get
>> if you do
>>
>> export ORACLE_SID=foo
>> sqlplus "/ as sysdba"
>>
>> since I don't have a Solaris at hand I can't check the outcome. But it
>> should
>> get you on the right track anyway.
>>
>> HTH
>>
>> Holger
>
>
> ORACLE_SID=foo sqlplus "/ as sysdba" @mysqlscript would be better... is
> &1 defined?
>
> Steve

You've lost me there. Why would that be better to demonstrate the fact that
you can get a connected (to an idle instance) when using any arbitrary ORACLE_SID?
The point is that you'll get on any subsequent command (other than startup) the
not connected error.

Cheers
Holger

On Thu, 14 Oct 2004 08:38:43 +0200, Holger Baer
<holger.baer@science-computing.de> wrote:

>Ed Stevens wrote:
>> sqlplus "/ as sysdba" @mysqlscript
>>
>>
>> and the output looked like this. Note the successfull 'connected'
>> just before the DELETE statement.
>>
>> SQL*Plus: Release 9.2.0.1.0 - Production on Wed Oct 13 14:42:00 2004
>>
>> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>>
>> Connected.
>> SQL> DELETE FROM mytable
>> 2 WHERE sample_time < sysdate - &1
>> 3 ;
>> old 2: WHERE sample_time < sysdate - &1
>> new 2: WHERE sample_time < sysdate - 15
>> DELETE FROM mytable
>> *
>> ERROR at line 1:
>> ORA-01034: ORACLE not available
>>
>>
>> I'm sure this is something I should know. Maybe I do know it, but am
>> just having a DSA (Dumb S*** Attack (tm)) I think my memory is maxed
>> out, and everytime I have to remember something new, something else
>> has to be tossed to make room. One of these days it will be my own
>> name that gets thrown out to make room for the names of seven new
>> databases to be created . . .
>
>Personally I'd expect an 'Connected to an idle instance'. Try what you get
>if you do
>
>export ORACLE_SID=foo
>sqlplus "/ as sysdba"
>
>since I don't have a Solaris at hand I can't check the outcome. But it should
>get you on the right track anyway.
>
>HTH
>
>Holger

If it had given 'Connected to an idle instance' it would all make
perfect sense. But as you can see from the original post, it clearly
said 'Connected'. Here's what trying to connect to a null
ORACLE_SID gives at the command line, followed by an attempt with a
bogus value. Both gave expected results. This was just from a
command prompt, not within a script and certainly not in a script
executed by cron.

oracle@ncensn130: echo $ORACLE_SID
VITXD01
oracle@ncensn130: ORACLE_SID=
oracle@ncensn130: echo $ORACLE_SID

oracle@ncensn130: sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 14 06:26:54 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-12162: TNS:service name is incorrectly specified


Enter user-name: ^C
oracle@ncensn130: ORACLE_SID=FOO
oracle@ncensn130: echo $ORACLE_SID
FOO
oracle@ncensn130: sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 14 06:27:31 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> exit
Disconnected

snip

> I finally realized that even though I was setting ORACLE_SID in the
> shell script, I wasn't exporting it so that SQLPlus could see it.
> After adding 'export ORACLE_SID' to the shell script it ran like a
> champ.

Everyone it seems approaches shell scripts (batch jobs one could say)
that use oracle utilities a little differently.

My approach is to always dot in some kind of small file that sets (and
exports) all of the oracle environment that you need to have
established as the first step.

#!/bin/ksh
#
# doc notes as needed
#
.. /u01/app/oracle/local/bin/.9ioraenv

The .9ioraenv file could check what host you are on, what oracle homes
are available, what sids, ... etc ... then set the environment as
needed (whatever you require). This type of approach might be useful
to your environment.

John

On 14 Oct 2004 05:34:26 -0700, johnbhurley@sbcglobal.net (John Hurley)
wrote:

>snip
>
>> I finally realized that even though I was setting ORACLE_SID in the
>> shell script, I wasn't exporting it so that SQLPlus could see it.
>> After adding 'export ORACLE_SID' to the shell script it ran like a
>> champ.
>
>Everyone it seems approaches shell scripts (batch jobs one could say)
>that use oracle utilities a little differently.
>
>My approach is to always dot in some kind of small file that sets (and
>exports) all of the oracle environment that you need to have
>established as the first step.
>
>#!/bin/ksh
>#
># doc notes as needed
>#
>. /u01/app/oracle/local/bin/.9ioraenv
>
>The .9ioraenv file could check what host you are on, what oracle homes
>are available, what sids, ... etc ... then set the environment as
>needed (whatever you require). This type of approach might be useful
>to your environment.
>
>John

I do that as well, and did it in this particular instance. The one
'gotcha' is ORACLE_SID on a box with multiple SIDs. That leaves the
following possibilities:

1) A separate 'environment' file specific to each sid. Calling
scripts have to know which sid-specific environment file to call.

2) (my approach) A single generic 'environment' file that leaves the
setting of ORACLE_SID to the calling script. Or sets it on behalf of
the calling script, but would have to have the calling script supply
the value as a command line parm.

Either way, the main script will have to be the one to know and
specify sid-specific info. It was simply my failure to export it that
caused the problem.

And still leaves me with the fundamental question of how I was able to
get a good connect -- *not* a 'connected to an idle instance' -- then
have the first DML statement result in a ORA-01034: ORACLE not
available.

Ed Stevens <nospam@noway.nohow> wrote in message news:<dr5tm0p1v8gf3nt2iarmfege20dt06iepm@4ax.com>. ..
>
> And still leaves me with the fundamental question of how I was able to
> get a good connect -- *not* a 'connected to an idle instance' -- then
> have the first DML statement result in a ORA-01034: ORACLE not
> available.

I'm frustrated as heck right now because I've done this same thing and
can't remember the answer.

But it might be something like being logged on as root rather than the
oracle user, so you can connect, because that just means running
sqlplus, but when you try to actually do anything you get the 1034
because the shared memory area is owned by oracle:dba (or whatever)
and root is explicitly enjoined from doing certain things in Oracle,
even if it is in the dba group.

I also have a vague memory of something like this when I forgot the -
in the su - oracle, where the oracle .profile had the proper
environment call and the sysadmin had tried to run oracle things as
root, so the environment was partly set up under cron.

jg
--
@home.com is bogus.
http://www.oreillynet.com/pub/a/netw.../phishing.html

Ed Stevens wrote:
[..]
>
>
> If it had given 'Connected to an idle instance' it would all make
> perfect sense. But as you can see from the original post, it clearly
> said 'Connected'. Here's what trying to connect to a null
> ORACLE_SID gives at the command line, followed by an attempt with a
> bogus value. Both gave expected results. This was just from a
> command prompt, not within a script and certainly not in a script
> executed by cron.
>
[..]

Ok, my point was, that if you're really connected to an instance,
sqlplus is in the habit of telling you which instance it's connected
to. And as an absolut shot in the dark I thought maybe that's just
the solaris sqlplus maybe in conjunction with the fact that it was
run from a script.

Sometimes I get those fantasies ;-)

Holger

Holger Baer wrote:
> Ed Stevens wrote:
> [..]
>
>>
>>
>> If it had given 'Connected to an idle instance' it would all make
>> perfect sense. But as you can see from the original post, it clearly
>> said 'Connected'. Here's what trying to connect to a null
>> ORACLE_SID gives at the command line, followed by an attempt with a
>> bogus value. Both gave expected results. This was just from a
>> command prompt, not within a script and certainly not in a script
>> executed by cron.
>>
> [..]
>
> Ok, my point was, that if you're really connected to an instance,
> sqlplus is in the habit of telling you which instance it's connected
> to. And as an absolut shot in the dark I thought maybe that's just
> the solaris sqlplus maybe in conjunction with the fact that it was
> run from a script.
>
> Sometimes I get those fantasies ;-)
>
> Holger

I just shouldn't write before my second cup. ;-)

I intended to say, that if sqlplus does not give an
'connected to: ... ' then it's not connected to a running instance.

Cheers
Holger