topic Re: script help in Operating System - HP-UX

script help

file system — Mon, 15 Jan 2007 00:54:10 GMT

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

Re: script help

Sandman! — Mon, 15 Jan 2007 02:30:12 GMT

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

Re: script help

Peter Godron — Mon, 15 Jan 2007 03:12:50 GMT

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

Re: script help

Oviwan — Mon, 15 Jan 2007 03:23:50 GMT

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

Re: script help

file system — Mon, 15 Jan 2007 04:04:26 GMT

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.

Re: script help

Yogeeraj_1 — Mon, 15 Jan 2007 04:17:12 GMT

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

Re: script help

Peter Godron — Mon, 15 Jan 2007 05:11:13 GMT

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.

Re: script help

Frank de Vries — Tue, 16 Jan 2007 02:35:23 GMT

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.