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_01BFD7A2.B0B8813E
Content-Type: text/plain;
charset="windows-1252"
Yup! (#@!$% copy paste)
-----Original Message-----
From: David Lee [mailto:dba_lee_at_hotmail.com]
Sent: Thursday, June 15, 2000 12:28 PM
To: brian.maclean_at_homebid.com
Subject: RE: How to check the status of a sql script
Hi Brian,
This is nice. I am gonna use it.
I am wondering if there is a typo in your script:
The line after the "Backup commands here".
Shouldn't the argument to the function be 'end' instead of 'begin'?
I just want to make sure.
Thanks
David
>From: Brian MacLean <bmaclean_at_homebid.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: How to check the status of a sql script
>Date: Thu, 15 Jun 2000 11:40:53 -0800
>
>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#;
> exit;
>EOF
>}
>#
>DB_TBLS_Status ${my_tablespace} begin | grep "STATUS=ACTIVE"
>if [ ${?} -eq 0 ]
>then
> echo "Begin backup successful for tablespace ${my_tablespace}"
>else
> echo "Begin backup failed for tablespace ${my_tablespace}, exiting..."
> exit
>fi
>#
># BACKUP COMMANDS HERE
>#
>DB_TBLS_Status ${my_tablespace} begin | grep "STATUS=NOT ACTIVE"
>if [ ${?} -eq 0 ]
>then
> echo "End backup successful for tablespace ${my_tablespace}"
>else
> echo "End backup failed for tablespace ${my_tablespace}, exiting..."
> exit
>fi
>#END-OF-SCRIPT
>
>
>
>
>Brian P. Mac Lean
>Senior Oracle Database Administrator
>OCPv8/Oracle Master
>HomeBid.Com
>8700 N. Gainey Center Drive
>Scottsdale, AZ 85258
>Tel:480.609.4624
>Cel:602.617.6075
>Fax:480.609.4646
>Net:brian.maclean_at_homebid.com
>
>
>
>-----Original Message-----
>Sent: Thursday, June 15, 2000 11:16 AM
>To: Multiple recipients of list ORACLE-L
>
>
>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
>________________________________________________________________________
>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
>
>--
>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_01BFD7A2.B0B8813E
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>Yup! (#@!$% copy paste)</FONT></P>
<P><FONT SIZE=2>-----Original Message-----</FONT> <BR><FONT SIZE=2>From: David Lee [<A HREF="mailto:dba_lee_at_hotmail.com">mailto:dba_lee_at_hotmail.com</A>]</FONT> <BR><FONT SIZE=2>Sent: Thursday, June 15, 2000 12:28 PM</FONT> <BR><FONT SIZE=2>To: brian.maclean_at_homebid.com</FONT> <BR><FONT SIZE=2>Subject: RE: How to check the status of a sql script</FONT> </P> <BR> <P><FONT SIZE=2>Hi Brian,</FONT> <BR><FONT SIZE=2>This is nice. I am gonna use it.</FONT> <BR><FONT SIZE=2>I am wondering if there is a typo in your script:</FONT> <BR><FONT SIZE=2>The line after the "Backup commands here".</FONT> <BR><FONT SIZE=2>Shouldn't the argument to the function be 'end' instead of 'begin'?</FONT><BR><FONT SIZE=2>I just want to make sure.</FONT> </P>
<P><FONT SIZE=2>Thanks</FONT>
</P>
<P><FONT SIZE=2>David</FONT> </P> <BR> <BR> <P><FONT SIZE=2>>From: Brian MacLean <bmaclean_at_homebid.com></FONT> <BR><FONT SIZE=2>>Reply-To: ORACLE-L_at_fatcity.com</FONT> <BR><FONT SIZE=2>>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com></FONT> <BR><FONT SIZE=2>>Subject: RE: How to check the status of a sql script</FONT> <BR><FONT SIZE=2>>Date: Thu, 15 Jun 2000 11:40:53 -0800</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>A partial Korn shell example to get you going...</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>#!/bin/ksh</FONT> <BR><FONT SIZE=2>>#</FONT> <BR><FONT SIZE=2>># args for DB_TBLS_Status: 1=tablespace_name 2=begin/end</FONT> <BR><FONT SIZE=2>>function DB_TBLS_Status {</FONT> <BR><FONT SIZE=2>> sqlplus / <<EOF</FONT> <BR><FONT SIZE=2>> alter tablespace ${1} ${2} backup;</FONT> <BR><FONT SIZE=2>> select 'STATUS=' || B.status</FONT> <BR><FONT SIZE=2>> from dba_data_files A, v$backup B</FONT> <BR><FONT SIZE=2>> where A.tablespace_name = 'USERS'</FONT> <BR><FONT SIZE=2>> and A.file_id = B.file#;</FONT> <BR><FONT SIZE=2>> exit;</FONT> <BR><FONT SIZE=2>>EOF</FONT> <BR><FONT SIZE=2>>}</FONT> <BR><FONT SIZE=2>>#</FONT> <BR><FONT SIZE=2>>DB_TBLS_Status ${my_tablespace} begin | grep "STATUS=ACTIVE"</FONT> <BR><FONT SIZE=2>>if [ ${?} -eq 0 ]</FONT> <BR><FONT SIZE=2>>then</FONT> <BR><FONT SIZE=2>> echo "Begin backup successful for tablespace ${my_tablespace}"</FONT> <BR><FONT SIZE=2>>else</FONT> <BR><FONT SIZE=2>> echo "Begin backup failed for tablespace ${my_tablespace}, exiting..."</FONT> <BR><FONT SIZE=2>> exit</FONT> <BR><FONT SIZE=2>>fi</FONT> <BR><FONT SIZE=2>>#</FONT> <BR><FONT SIZE=2>># BACKUP COMMANDS HERE</FONT> <BR><FONT SIZE=2>>#</FONT> <BR><FONT SIZE=2>>DB_TBLS_Status ${my_tablespace} begin | grep "STATUS=NOT ACTIVE"</FONT> <BR><FONT SIZE=2>>if [ ${?} -eq 0 ]</FONT> <BR><FONT SIZE=2>>then</FONT> <BR><FONT SIZE=2>> echo "End backup successful for tablespace ${my_tablespace}"</FONT> <BR><FONT SIZE=2>>else</FONT> <BR><FONT SIZE=2>> echo "End backup failed for tablespace ${my_tablespace}, exiting..."</FONT> <BR><FONT SIZE=2>> exit</FONT> <BR><FONT SIZE=2>>fi</FONT> <BR><FONT SIZE=2>>#END-OF-SCRIPT</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>Brian P. Mac Lean</FONT> <BR><FONT SIZE=2>>Senior Oracle Database Administrator</FONT> <BR><FONT SIZE=2>>OCPv8/Oracle Master</FONT> <BR><FONT SIZE=2>>HomeBid.Com</FONT> <BR><FONT SIZE=2>>8700 N. Gainey Center Drive</FONT> <BR><FONT SIZE=2>>Scottsdale, AZ 85258</FONT> <BR><FONT SIZE=2>>Tel:480.609.4624</FONT> <BR><FONT SIZE=2>>Cel:602.617.6075</FONT> <BR><FONT SIZE=2>>Fax:480.609.4646</FONT> <BR><FONT SIZE=2>>Net:brian.maclean_at_homebid.com</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>-----Original Message-----</FONT> <BR><FONT SIZE=2>>Sent: Thursday, June 15, 2000 11:16 AM</FONT> <BR><FONT SIZE=2>>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>Hi all,</FONT> <BR><FONT SIZE=2>>I am writing a shell script to do hot backups. I have a sql script </FONT> <BR><FONT SIZE=2>>embedded</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>in it saying ' alter tablespace ... begin backup' stuff.</FONT> <BR><FONT SIZE=2>>Now I want to put something in my shell script so that if the sql script</FONT> <BR><FONT SIZE=2>>fails, the hot backups are cancelled and tablespaces are taken out of</FONT> <BR><FONT SIZE=2>>hot backup mode. But the thing is, How do I see the exit status of the sql</FONT> <BR><FONT SIZE=2>>script?</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>TIA</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>David</FONT> <BR><FONT SIZE=2>>________________________________________________________________________</FONT> <BR><FONT SIZE=2>>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>--</FONT> <BR><FONT SIZE=2>>Author: David Lee</FONT> <BR><FONT SIZE=2>> INET: dba_lee_at_hotmail.com</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=2>>San Diego, California -- Public Internet access / Mailing Lists</FONT><BR><FONT SIZE=2>></FONT>
<P><FONT SIZE=2>________________________________________________________________________</FONT>Received on Fri Jun 16 2000 - 09:53:53 CDT
![]() |
![]() |