ORA-1727 - where does the error go?
joel garry
I have a slightly odd script that does an alter database backup
controlfile to '$OBACKUP/backupcontrolfile.ctl'
Of course, I had forgotten to export the OBACKUP, since, well, there
is an alter to trace which happens to work ok, and everything else
works ok since all the data copying is local to the ksh script.
Now I understand that sqlplus must be creating a subprocess to do
this, as when I do it manually in sqlplus I see the error, as well as
in the log: ORA-7217 signalled during: alter database backup
controlfile to '$OBACKUP/backupcontrolfile.ctl'...
$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Feb 9 09:14:52 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
@> connect / as sysdba
Connected.
SYS@XXXX> alter database backup controlfile to '$OBACKUP/
backupcontrolfile.ctl';
alter database backup controlfile to '$OBACKUP/backupcontrolfile.ctl'
*
ERROR at line 1:
ORA-07217: sltln: environment variable cannot be evaluated.
It works without error echoing the command into sqlplus -s manually:
unset OBACKUP
OBACKUP=/oradata/temp_copy/oraclebackup
echo "
connect sys as sysdba
alter database backup controlfile to '$OBACKUP/backupcontrolfile.ctl';
"|sqlplus -s /nolog
And I understand since I'm echoing all these sqlplus commands into
sqlplus -s /nolog that there shouldn't be any normal output. Where
there should be error output is debateable, I suppose, but I can
understand the viewpoint that there shouldn't be.
I just don't quite understand why such an error should be suppressed
from the alert log or anywhere in a cron script with a redirected
standard and error output. Curly braces ${OBACKUP} seem to make no
difference.
Bug or misfeature? I'm thinking at least sqlplus -s working different
in a script must be a bug. Silent fail of backup controlfile can't be
minor... can it? Or have I just missed something obvious?
jg
--
@home.com is bogus.
http://www3.signonsandiego.com/stories/2009/feb/08/lz1e8patridge232829-medicinal-or-illegal/?uniontrib
http://www3.signonsandiego.com/stories/2009/feb/08/lz1e8kreit232828-no-headline/?uniontrib
Steve Howard
I get a different error. Since OBACKUP is not set, when I do it
through a pipe it tries to write it into the root directory. I did
take some liberties with your command line, but I got the same error
regardless. This is on RHAT 4 AS...
tdb01txdu:oracle:orcl:/home/oracle>echo "alter database backup
controlfile to '$OBACKUP/backupcontrolfile.ctl';"|sqlplus -s / as
sysdba
alter database backup controlfile to '/backupcontrolfile.ctl'
*
ERROR at line 1:
ORA-01580: error creating control backup file /backupcontrolfile.ctl
ORA-27040: file create error, unable to create file
Linux Error: 13: Permission denied
When I do it interactively, I get the ORA-07217 error you noted...
SQL> alter database backup controlfile to '$FOO/
backupcontrolfile.ctl';
alter database backup controlfile to '$FOO/backupcontrolfile.ctl'
*
ERROR at line 1:
ORA-07217: sltln: environment variable cannot be evaluated.
SQL>
joel garry
>
> I get a different error. Since OBACKUP is not set, when I do it
> through a pipe it tries to write it into the root directory. I did
> take some liberties with your command line, but I got the same error
> regardless. This is on RHAT 4 AS...
>
> tdb01txdu:oracle:orcl:/home/oracle>echo "alter database backup
> controlfile to '$OBACKUP/backupcontrolfile.ctl';"|sqlplus -s / as
> sysdba
> alter database backup controlfile to '/backupcontrolfile.ctl'
> *
> ERROR at line 1:
> ORA-01580: error creating control backup file /backupcontrolfile.ctl
> ORA-27040: file create error, unable to create file
> Linux Error: 13: Permission denied
>
> When I do it interactively, I get the ORA-07217 error you noted...
>
> SQL> alter database backup controlfile to '$FOO/
> backupcontrolfile.ctl';
> alter database backup controlfile to '$FOO/backupcontrolfile.ctl'
> *
> ERROR at line 1:
> ORA-07217: sltln: environment variable cannot be evaluated.
>
> SQL>
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.
jg
--
@home.com is bogus.
http://icanhascheezburger.com/2009/02/09/funny-pictures-to-see-it-again/
hpuxrac
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.
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 ...
joel garry
> On Feb 10, 1:58 pm, joel garry <joel-ga...@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
--
@home.com is bogus.
http://www3.signonsandiego.com/stories/2009/feb/11/bn11sexting0984/?zIndex=51444
joel garry
And yet, it is not fixed for me.
This has really turned into a WTF. Exporting in the script now.
From alert log:
...
Sat Feb 14 03:54:27 2009
alter database backup controlfile to '/oradata/temp_copy/oraclebackup/
backupcontrolfile.ctl'
Completed: alter database backup controlfile to '/oradata/temp_copy/
oraclebackup/backupcontrolfile.ctl'
Sat Feb 14 03:54:28 2009
alter database backup controlfile to trace as '/oradata/temp_copy/
oraclebackup/controlfile.txt'
Completed: alter database backup controlfile to trace as '/oradata/
temp_copy/oraclebackup/controlfile.txt'
...
But (cut and pasting the filenames from the alert log)...
oracle:XXXX@YYY /home2/oracle$ ll '/oradata/temp_copy/oraclebackup/
backupcontrolfile.ctl'
/oradata/temp_copy/oraclebackup/backupcontrolfile.ctl not found
oracle:XXXX@YYY /home2/oracle$ ll '/oradata/temp_copy/oraclebackup/
controlfile.txt'
-rw-r--r-- 1 oracle oinstall 9886 Feb 14 03:54 /oradata/
temp_copy/oraclebackup/controlfile.txt
oracle:XXXX@YYY /home2/oracle$
Isn't anywhere under /oradata or $ORACLE_BASE either.
oracle:XXXX@YYY /home2/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Feb 17 10:12:36 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
SYS@XXXX> alter database backup controlfile to '/oradata/temp_copy/
oraclebackup/backupcontrolfile.ctl';
Database altered.
SYS@XXXX> exit
Disconnected from Oracle Database 10g Enterprise Edition Release
10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
oracle:XXXX@YYY /home2/oracle$ ll '/oradata/temp_copy/oraclebackup/
backupcontrolfile.ctl'
-rw-r----- 1 oracle oinstall 20103168 Feb 17 10:12 /oradata/
temp_copy/oraclebackup/backupcontrolfile.ctl
jg
--
@home.com is bogus.
What if you couldn't pull the plug... http://catless.ncl.ac.uk/Risks/25.55.html#subj14