Hi Tom,

Is it possible to set up log_archive_dest using a variable on unix that will get the value when the database needs to access to the archive location?

What I mean with this is something like the following:

1-On unix define a variable that will have the location for the archives

varloc=/arv/db1; export varloc

2-In the init.ora file, define log_archive_dest in the following way:

log_archive_dest = 'LOCATION=${varloc}'

I tried to do this but I am getting the following errors in the alert log:

LGWR: Error 16032 validating archivelog destination information
Wed Dec 1 16:25:38 2004
ORA-16032: parameter LOG_ARCHIVE_DEST destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.

Is it possible to use a variable when I define log_archive-dest on the init.ora file? or should I explicitly write the directory where the archive destination is?

Thanks for any help on this.

Hi Tom,

I happened to see this thread and thought that I could offer another approach which allows multiple values (retrieved from Oracle) to be assigned to shell variables rather easily.

It uses the "read" command (which by default uses whitespace as a delimiter) to assign the multiple column values to UNIX variable(s). If you only assign to a single UNIX variable - then all fields are assigned (with whitespace included) to that variable.

Here is a simple script which demonstrates this (note that I tested this in HP-UX (11i) - haven't had time to test in other flavors of UNIX (sorry). I also use the sh shell - (sorry - that's the one I know best).

---------------------------------------------------------
#!/bin/sh

PID=$$
VAR_FILE=var_file_${PID}_`date +%y%m%d`.tmp

sqlplus -s / << EOF

SET SHOWMODE OFF
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET RECSEP OFF
SET PAGESIZE 0
SET TRIMSPOOL ON
SET LINESIZE 2000

SPOOL ${VAR_FILE}

SELECT SUBSTR(MAX(CASE name WHEN 'star_transformation_enabled' THEN value END), 1, 10) AS star_trans_val
, SUBSTR(MAX(CASE name WHEN 'query_rewrite_enabled' THEN value END), 1, 10) AS query_rewrite_val
FROM v\$parameter ;

SPOOL OFF ;

EOF

read STAR_TRANS_VAR QUERY_REWRITE_VAR < ${VAR_FILE}
read MULTI_VALUE_VAR < ${VAR_FILE}

RC=$?

echo
echo "STAR_TRANS_VAR = ${STAR_TRANS_VAR}"
echo "QUERY_REWRITE_VAR = ${QUERY_REWRITE_VAR}"

echo
echo "MULTI_VALUE_VAR = ${MULTI_VALUE_VAR}"

# Remove the temp variable file...
rm -f ./${VAR_FILE} 2>/dev/null

exit ${RC}
---------------------------------------------------------


Here is the output of that tiny script:
---------------------------------------------------------
$ ./asktom.sh




false true

STAR_TRANS_VAR = false
QUERY_REWRITE_VAR = true

MULTI_VALUE_VAR = false true
---------------------------------------------------------

Take care as always...

Sincerely,

Philip