Shell Programming and Scripting

The following script is designed to backup the oracle control file to the trace directory and then copy (the trace file that was created by the backup command) and rename that file(to a .sql) to a backup disk. When I run the script from sqlplus as sysdba everything works but when I execute from command line the host cp command does not work.

What gives?


bk_control.ksh" 35 lines, 999 characters
#!/bin/ksh
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_2
ORACLE_SID=FFITEST
PATH="$PATH:$ORACLE_HOME/bin"
export PATH ORACLE_HOME ORACLE_SID

sqlplus -S / as sysdba <<EOF >> /dev/null
set echo off feedback off verify off pages 0
column TRCLOC new_value TRCFILE
column TRCDATE new_value FILEDATE
column INSTNAME new_value INSTANCE
select to_char(sysdate, 'YYYYMMDD_HH24MISS') TRCDATE
from dual;
select instance INSTNAME
from v$thread;
alter database backup controlfile to trace;
select
c.value || '/' ||
lower(instance) || '_ora_' ||
ltrim(to_char(a.spid,'fm99999999')) ||
'.trc' TRCLOC
from
v$process a, v$session b,
v$parameter c, v$thread c
where
a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
/
host cp &TRCFILE /svm/oracle/work/daily_export/control_&instance._&filedate..sql
exit
EOF

move the copy command outside of sqlplus where most coders would expect to find it.
then you can use environment variables.

I also do not see how &TRCFILE &instance &filedate are passed to the script.
sqlplus requests the substitution when run from the command line like this:

Code:

SQL> select '&TRCFILE' from dual;
Enter value for trcfile: plpl
old   1: select '&TRCFILE' from dual
new   1: select 'plpl' from dual

'PLP
----
plpl

SQL> host cp &TRCFILE ~/t.lis
Enter value for trcfile: plpl
cp: cannot access plpl: No such file or directory