Re: ORA-1727 - where does the error go?
From: joel garry <joel-garry_at_home.com>
Date: Tue, 10 Feb 2009 10:58:08 -0800 (PST)
Message-ID: <8628e88c-3495-47d7-ade2-f23eb34da334_at_w39g2000prb.googlegroups.com>
On Feb 10, 5:03 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
> On Feb 9, 12:24 pm, joel garry <joel-ga..._at_home.com> wrote:
>
>
>
>
>
> > 10.2.0.4, hp-ux 11.23
>
> > 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.
>
> > _at_> connect / as sysdba
> > Connected.
> > SYS_at_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
> > --
> > _at_home.com is bogus.http://www3.signonsandiego.com/stories/2009/feb/08/lz1e8patridge23282......
>
> 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:
Date: Tue, 10 Feb 2009 10:58:08 -0800 (PST)
Message-ID: <8628e88c-3495-47d7-ade2-f23eb34da334_at_w39g2000prb.googlegroups.com>
On Feb 10, 5:03 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
> On Feb 9, 12:24 pm, joel garry <joel-ga..._at_home.com> wrote:
>
>
>
>
>
> > 10.2.0.4, hp-ux 11.23
>
> > 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.
>
> > _at_> connect / as sysdba
> > Connected.
> > SYS_at_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
> > --
> > _at_home.com is bogus.http://www3.signonsandiego.com/stories/2009/feb/08/lz1e8patridge23282......
>
> 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
-- _at_home.com is bogus. http://icanhascheezburger.com/2009/02/09/funny-pictures-to-see-it-again/Received on Tue Feb 10 2009 - 12:58:08 CST