Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

script help

SOLVED
Go to solution
file system
Frequent Advisor

script help

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
7 REPLIES
Sandman!
Honored Contributor

Re: script help

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
Honored Contributor

Re: script help

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
Honored Contributor

Re: script help

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
Frequent Advisor

Re: script help

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
Honored Contributor

Re: script help

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Peter Godron
Honored Contributor
Solution

Re: script help

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
Respected Contributor

Re: script help

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.


Look before you leap