Re: ORA-1727 - where does the error go?

From: joel garry <joel-garry_at_home.com>
Date: Wed, 11 Feb 2009 10:33:18 -0800 (PST)
Message-ID: <2f0ca452-d052-4793-a787-0ab96555d9b4_at_z2g2000prg.googlegroups.com>



On Feb 10, 3:09 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Feb 10, 1:58 pm, joel garry <joel-ga..._at_home.com> wrote:
>
> snip
>
> > Thanks, I was wondering if it was platform specific.  I thought of the
> > root dir issue (oracle can't write there), but still, no errors
> > anywhere.  The cron is root running su - oracle, with the redirected
> > output outside of the command quotes, like:
>
> > 00 3 * * 6 su - oracle -c "/home2/oracle/scripts/backupxyz.ksh" >> /
> > home2/oracle/backupxyz.out 2>&1
>
> > So I would expect to see a permission denied error, even if the output
> > and error output is mixed up.   I guess under these circumstances
> > sqlplus -s doesn't pass it back?
>
> > Of course, now that it is fixed for me, why will be left as a mystery
> > of the universe.  And I hope I remember next time I make the same
> > mistake.
>
> Another possible approach is to work the process and submit it into
> oracle support as a "reported bug".  If they do legwork and reject it
> ultimately then you could re-work it as a request for improvement.
>
> Not that any of that will happen quickly of course.

Yeah, I put it out here because I'm not going to do it, but maybe some 11g person may want to look into it (or maybe four eyes isn't enough to see if I've done something silly).

>
> I tend to not use many oracle invocations of sqlplus with environment
> variables inside the code (in fact I can't recall the last time I did
> that ) ... if necessary I tend to like to create a .sql file first
> with everything "completed" in it with maybe some unique part of the
> sql file name and run stuff that way ... everyone has their own
> preferred approaches.
>
> Often I may have a canned whatever.sql file that gets run by different
> shell scripts and bear the burden of having multiple "similar" shell
> scripts.  Must be rambling it's been a long week already ...

Yeah, I do it both ways, but the burden of multiple scripts inevitably leads to too many and a maintenance problem. I've seen enough backup devices/locations get changed in a hurry I got tired long ago. I'm a fan of the optional parameter kind of syntax, like (from an imp script, obviously)

EXP_LOC=$1
FROM_USER=$2
: ${EXP_LOC:="/oradata/exports/prod.exp"} : ${FROM_USER:="produser"}
echo "Importing from $EXP_LOC"
echo "User is $FROM_USER"

though of course it gets messy with more than a few. Just having few places at the beginning to edit defaults (as opposed to global edits) helps, especially with the spookily similar scripts involved with primary/standby/dev environs.

Yeah, everyone has their preferred approaches. Every approach has some way to screw up production. The trick is to know your flaws, protect against them, and know how to recover quickly when it happens. What's more difficult is to figure out how to prevent others from screwing up with your stuff.

jg

--
_at_home.com is bogus.
http://www3.signonsandiego.com/stories/2009/feb/11/bn11sexting0984/?zIndex=51444
Received on Wed Feb 11 2009 - 12:33:18 CST

Original text of this message