script help

[file system]

I 'm going to get the name of dabase oracle
and I'm going to 'sqlplus /nolog' and
query like select name from v$database;

SQL> SQL> SQL> SQL>
NAME
---------
IAEIS

Query like select name from v$controlfile
SQL>
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/oradata/IAEIS/control01.ctl
/oradata/IAEIS/control02.ctl
/oradata/IAEIS/control03.ctl

How can I get the output using awk or other scipt
I just want 'IAEIS' and controlfiles

/oradata/IAEIS/control01.ctl
/oradata/IAEIS/control02.ctl
/oradata/IAEIS/control03.ctl

[Sandman!]

Try the script below:
============================================

#!/usr/bin/sh

sqlplus -s $USERID <<-EOF
set echo off head off feed off
select name from v\$controlfile;
exit
EOF

[Peter Godron]

Hi,
you probably want to add "pages 0" to Sandmans set line, in order to avoid the initial blank line.

Do you want to save the output to b.lis ?
Also you wanted to connect /nolog ?

#!/usr/bin/sh
sqlplus -s /nolog <<-EOF
connect / as sysdba;
set echo off head off feed off pages 0;
spool b.lis
select name from v\$database;
select name from v\$controlfile;
spool off;
exit
EOF

[Oviwan]

Hy

#!/usr/bin/ksh
ctl_files=`sqlplus -s "/ as sysdba" < set echo off head off feed off
select name from v\\$controlfile;
exit
EOF`

for file in ${ctl_files} ; do
#do something awk or whatever
done

Regards

[file system]

I will run the script to backup of oracle hot backup.

but it is not work?

# Get DB info

#get_db_info()
{
#$ORACLE_HOME/bin/sqlplus /nolog << EOF > DB_INFO
sqlplus /nolog << EOF > DB_INFO
connect / as sysdba;

set linesize 200;
select db_unique_name, dbid from v\$database;
select name ||':' || block_size from v\$controlfile;
select file_name ||':'||tablespace_name||':'|| status from dba_data_files
order by tablespace_name;

disconnect
EOF

DB_NAME=`/usr/bin/awk '$2 == "2859985154" { print $1}' DB_INFO`
CONTROLFILE=`/usr/bin/awk -F: '$2 == "16384" { print $1 }' DB_INFO`
CONTROLFILE_BACK=${ORACLE_HOME}/dbs/control.${DATE}
TABLESPACE_NAME=`/usr/bin/awk -F: '$3 == "AVAILABLE" { print $2 }' DB_INFO`
DATAFILE=`/usr/bin/awk -F: '$3 == "AVAILABLE" { print $1 }' DB_INFO`
}
# Programmable Variables

DATE=`/usr/bin/date +%y%m%d`
BACKUP_HOME=/oracle/backuplog
BACKUP_PID=${BACKUP_HOME}/`/usr/bin/basename $0`.pid
BACKUP_LOG=${BACKUP_HOME}/`/usr/bin/basename $0 | /usr/bin/cut -d. -f1`.iaeisdb.${DATE}
INIT_ORA=${ORACLE_HOME}/dbs/initdw.ora

if [ $TABLESPACE_NAME ]
sqlplus /nolog
connect /as sysdba
alter tablespace $TABLESPACE_NAME begin backup;
disconnect
EOF

save -s $NSR_SERVER \
-l full \
-b oracle \
-N $DATAFILE
alter tablespace $TABLESPACE_NAME end backup;

$sh a
$ sh a
a: syntax error at line 44: `end of file' unexpected

How can I run the script to backup perfectly.
any tips are good for me.
thanks
I want the script to run

1. oracle login
2. alter tablespace tablespace_name begin backup
3. run backup command
4. After backup , oracle login
alter tablespace tablespace_name end backup.
SEQUENCIALLY.

[Yogeeraj_1]

hi,

to be able to troubleshoot your script, make a backup of it and remove all the commands that have been commented out using the "#" symbol.

Check if each command runs separately well.

Also, if you are running oracle 8i or later, please consider RMAN for backup of your database. This Oracle tool simplifies the hot backup process.

kind regards
yogeeraj

[Peter Godron · Accepted solution]

Hi,
just a brief scan of your script:
1. You have commeneted out procedure get_db_info
2. Within you main your first sqlplus step does not have a <3. Your save statement is outside the sqlplus script

Other threads with similar issues:
http://orafaq.com/faqdbabr.htm#ONLBRR

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=879590

http://www.samspublishing.com/articles/article.asp?p=30348&seqNum=3&rl=1

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=225926


Please also read:
http://forums1.itrc.hp.com/service/forums/helptips.do?#33 on how to reward any useful answers given to your questions.

[Frank de Vries]

I saw your hot backup solution using script.
Just a comment.
Why not do a an 'alter system switch logfile'
followed by 'select * from v$LOGHIST
where rownum < 2' >> $YOURLOG
Just before your do your 'alter tablespace start backup' command

This gives you increased admin over
logs when your need to restore.