| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to check the status of a sql script
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01BFD6F9.9EEDD250
Content-Type: text/plain;
charset="windows-1252"
A partial Korn shell example to get you going...
#!/bin/ksh
#
# args for DB_TBLS_Status: 1=tablespace_name 2=begin/end
function DB_TBLS_Status {
sqlplus / <<EOF
alter tablespace ${1} ${2} backup;
select 'STATUS=' || B.status
from dba_data_files A, v$backup B
where A.tablespace_name = 'USERS'
and A.file_id = B.file#;
Tel:480.609.4624 Cel:602.617.6075 Fax:480.609.4646 Net:brian.maclean_at_homebid.com
-----Original Message-----
From: David Lee [mailto:dba_lee_at_hotmail.com]
Sent: Thursday, June 15, 2000 11:16 AM
To: Multiple recipients of list ORACLE-L
Subject: How to check the status of a sql script
Hi all,
I am writing a shell script to do hot backups. I have a sql script embedded
in it saying ' alter tablespace ... begin backup' stuff. Now I want to put something in my shell script so that if the sql script fails, the hot backups are cancelled and tablespaces are taken out of hot backup mode. But the thing is, How do I see the exit status of the sql script?
TIA David
--
Author: David Lee
INET: dba_lee_at_hotmail.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
------_=_NextPart_001_01BFD6F9.9EEDD250
Content-Type: text/html;
charset="windows-1252"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2448.0">
<TITLE>RE: How to check the status of a sql script</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=2 FACE="Arial">A partial Korn shell example to get you going...</FONT>
</P>
<P><FONT SIZE=1 FACE="Courier New">#!/bin/ksh</FONT>
<BR><FONT SIZE=1 FACE="Courier New">#</FONT>
<BR><FONT SIZE=1 FACE="Courier New"># args for DB_TBLS_Status: 1=tablespace_name 2=begin/end</FONT>
<BR><FONT SIZE=1 FACE="Courier New">function DB_TBLS_Status {</FONT>
<BR><FONT SIZE=1 FACE="Courier New"> sqlplus / <<EOF</FONT>
<BR><FONT SIZE=1 FACE="Courier New"> alter tablespace ${1} ${2} backup;</FONT>
<BR><FONT SIZE=1 FACE="Courier New"> select 'STATUS=' || B.status </FONT>
<BR><FONT SIZE=1 FACE="Courier New"> from dba_data_files A, v$backup B </FONT>
<BR><FONT SIZE=1 FACE="Courier New"> where A.tablespace_name = 'USERS' </FONT>
<BR><FONT SIZE=1 FACE="Courier New"> and A.file_id = B.file#;</FONT>
<BR><FONT SIZE=1 FACE="Courier New"> exit;</FONT>
<BR><FONT SIZE=1 FACE="Courier New">EOF</FONT>
<BR><FONT SIZE=1 FACE="Courier New">}</FONT>
<BR><FONT SIZE=1 FACE="Courier New">#</FONT>
<BR><FONT SIZE=1 FACE="Courier New">DB_TBLS_Status ${my_tablespace} begin | grep "STATUS=ACTIVE"</FONT>
<BR><FONT SIZE=1 FACE="Courier New">if [ ${?} -eq 0 ]</FONT>
<BR><FONT SIZE=1 FACE="Courier New">then</FONT>
<BR><FONT SIZE=1 FACE="Courier New"> echo "Begin backup successful for tablespace ${my_tablespace}"</FONT>
<BR><FONT SIZE=1 FACE="Courier New">else</FONT>
<BR><FONT SIZE=1 FACE="Courier New"> echo "Begin backup failed for tablespace ${my_tablespace}, exiting..."</FONT>
<BR><FONT SIZE=1 FACE="Courier New"> exit</FONT>
<BR><FONT SIZE=1 FACE="Courier New">fi</FONT>
<BR><FONT SIZE=1 FACE="Courier New">#</FONT>
<BR><FONT SIZE=1 FACE="Courier New"># BACKUP COMMANDS HERE</FONT>
<BR><FONT SIZE=1 FACE="Courier New">#</FONT>
<BR><FONT SIZE=1 FACE="Courier New">DB_TBLS_Status ${my_tablespace} begin | grep "STATUS=NOT ACTIVE"</FONT>
<BR><FONT SIZE=1 FACE="Courier New">if [ ${?} -eq 0 ]</FONT>
<BR><FONT SIZE=1 FACE="Courier New">then</FONT>
<BR><FONT SIZE=1 FACE="Courier New"> echo "End backup successful for tablespace ${my_tablespace}"</FONT>
<BR><FONT SIZE=1 FACE="Courier New">else</FONT>
<BR><FONT SIZE=1 FACE="Courier New"> echo "End backup failed for tablespace ${my_tablespace}, exiting..."</FONT>
<BR><FONT SIZE=1 FACE="Courier New"> exit</FONT>
<BR><FONT SIZE=1 FACE="Courier New">fi</FONT>
<BR><FONT SIZE=1 FACE="Courier New">#END-OF-SCRIPT</FONT>
</P>
<BR>
<BR>
<BR>
<P><B><FONT COLOR="#0000FF" FACE="Comic Sans MS">Brian P. Mac Lean</FONT></B>
<BR><FONT COLOR="#808080" SIZE=1 FACE="Comic Sans MS">Senior Oracle Database Administrator</FONT>
<BR><FONT COLOR="#808080" SIZE=1 FACE="Comic Sans MS">OCPv8/Oracle Master</FONT>
<BR><FONT COLOR="#808080" SIZE=1 FACE="Comic Sans MS">HomeBid.Com</FONT>
<BR><FONT COLOR="#808080" SIZE=1 FACE="Comic Sans MS">8700 N. Gainey Center Drive</FONT>
<BR><FONT COLOR="#808080" SIZE=1 FACE="Comic Sans MS">Scottsdale, AZ 85258</FONT>
<BR><FONT COLOR="#808080" SIZE=1 FACE="Comic Sans MS">Tel:480.609.4624</FONT>
<BR><FONT COLOR="#808080" SIZE=1 FACE="Comic Sans MS">Cel:602.617.6075</FONT>
<BR><FONT COLOR="#808080" SIZE=1 FACE="Comic Sans MS">Fax:480.609.4646</FONT>
<BR><FONT COLOR="#808080" SIZE=1 FACE="Comic Sans MS">Net:brian.maclean_at_homebid.com</FONT>
</P>
<BR>
<BR>
<P><FONT SIZE=2 FACE="Arial">-----Original Message-----</FONT>
<BR><FONT SIZE=2 FACE="Arial">From: David Lee [<A HREF="mailto:dba_lee_at_hotmail.com">mailto:dba_lee_at_hotmail.com</A>]</FONT>
<BR><FONT SIZE=2 FACE="Arial">Sent: Thursday, June 15, 2000 11:16 AM</FONT>
<BR><FONT SIZE=2 FACE="Arial">To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=2 FACE="Arial">Subject: How to check the status of a sql script</FONT>
</P>
<BR>
<P><FONT SIZE=2 FACE="Arial">Hi all,</FONT>
<BR><FONT SIZE=2 FACE="Arial">I am writing a shell script to do hot backups. I have a sql script embedded </FONT>
<BR><FONT SIZE=2 FACE="Arial">in it saying ' alter tablespace ... begin backup' stuff.</FONT>
<BR><FONT SIZE=2 FACE="Arial">Now I want to put something in my shell script so that if the sql script</FONT>
<BR><FONT SIZE=2 FACE="Arial">fails, the hot backups are cancelled and tablespaces are taken out of</FONT>
<BR><FONT SIZE=2 FACE="Arial">hot backup mode. But the thing is, How do I see the exit status of the sql </FONT>
<BR><FONT SIZE=2 FACE="Arial">script?</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">TIA</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">David</FONT>
<BR><FONT SIZE=2 FACE="Arial">________________________________________________________________________</FONT>
<BR><FONT SIZE=2 FACE="Arial">Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">-- </FONT>
<BR><FONT SIZE=2 FACE="Arial">Author: David Lee</FONT>
<BR><FONT SIZE=2 FACE="Arial"> INET: dba_lee_at_hotmail.com</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=2 FACE="Arial">San Diego, California -- Public Internet access / Mailing Lists</FONT>
Received on Thu Jun 15 2000 - 13:43:38 CDT
 |
 |