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

From: joel garry <joel-garry_at_home.com>
Date: Tue, 17 Feb 2009 10:40:27 -0800 (PST)
Message-ID: <94702ec4-7827-49f3-8c47-558061b0f0ef_at_g1g2000pra.googlegroups.com>

On Feb 10, 10:58 am, joel garry <joel-ga..._at_home.com> wrote:
> 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.
>

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_at_YYY /home2/oracle$ ll '/oradata/temp_copy/oraclebackup/ backupcontrolfile.ctl'
/oradata/temp_copy/oraclebackup/backupcontrolfile.ctl not found oracle:XXXX_at_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_at_YYY /home2/oracle$

Isn't anywhere under /oradata or $ORACLE_BASE either.

oracle:XXXX_at_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_at_XXXX> alter database backup controlfile to '/oradata/temp_copy/ oraclebackup/backupcontrolfile.ctl';

Database altered.

SYS_at_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_at_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

--
_at_home.com is bogus.
What if you couldn't pull the plug... http://catless.ncl.ac.uk/Risks/25.55.html#subj14

Received on Tue Feb 17 2009 - 12:40:27 CST