Variable substitution in DDL script

I need to add a redo group member to a mounted database -- and while I'm at it, that ALTER statement should repeat in a DO loop across all instances on the host.  Let's start with:

 
SQL> 
  1  ALTER DATABASE ADD LOGFILE MEMBER
  2   '/<SOMETREE>/redo/${ORACLE_SID}/$i_log_1a.log'
  3*  TO GROUP 1;

Open in new window


I'm trying to recall the syntax to substitute the host's value for ORACLE_HOME ( ? ) and ORACLE_SID -- and failing to find the right search terms.  Someone help the kid, please.
LVL 23
DavidSenior Oracle Database AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
I wouldn't do it inside of Oracle.  Do it with the shell script.  Something like this:

for sid in sid1 sid1
do
#set environtment for sid
export ORACLE_SID=${sid}
#do the alter
sqlplus user/pass  << EOF
ALTER DATABASE ADD LOGFILE MEMBER
'/<SOMETREE>/redo/${ORACLE_SID}/${i}_log_1a.log'
TO GROUP 1;
exit
EOF
done

Open in new window


I am assuming that the shell variable is i and not i_log_1a.  Doing it this way, the shell does the variable substitution before it gets to SQL*Plus.

That isn't meant to be actual code, but to give you the framework of how I would do it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DavidSenior Oracle Database AdministratorAuthor Commented:
Agreed, perhaps a better example of what I'm after:

sqlplus ...  @ ? .../sqlplus/admin/glogin.sql   -- what's the symbol to substitute ORACLE_HOME and/or ORACLE_SID in this context?
johnsoneSenior Oracle DBACommented:
? is the substitution for ORACLE_HOME.

I'll look for ORACLE_SID, but I don't think there is one.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

johnsoneSenior Oracle DBACommented:
Also, I believe that the substitution is not universal, it only works in the @ (or START) command.  I don't think it works within an ALTER statement the way you want it to.
johnsoneSenior Oracle DBACommented:
Well, I sit corrected.  It is not just with an @ command.  I created a file in a tablespace just fine with ?.  After looking in DBA_DATA_FILES the full path for ORACLE_HOME was substituted in, so seems like it would work with just about any command.  I have to admit, it is the first time I ever tried it outside an @ command.

I've been searching documentation and I cannot even find reference to the ? as a shortcut for ORACLE_HOME.  I'll search some more, but I'm not very hopeful.
DavidSenior Oracle Database AdministratorAuthor Commented:
I think we both agree there's no simply way.  I balked at having to bother with the export, in order to substitute the ORACLE_SID in the command path.  In the end, I gave up on trying to pass and parse the FOR array marker (your SID).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.