Message-Id: <10530.109551@fatcity.com> From: Brian MacLean Date: Fri, 16 Jun 2000 07:53:53 -0700 Subject: 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@hotmail.com] Sent: Thursday, June 15, 2000 12:28 PM To: brian.maclean@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 >Reply-To: ORACLE-L@fatcity.com >To: Multiple recipients of list ORACLE-L >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 / < 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@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@hotmail.com > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California -- Public Internet access / Mailing Lists > ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com ------_=_NextPart_001_01BFD7A2.B0B8813E Content-Type: text/html; charset="windows-1252" RE: How to check the status of a sql script

Yup! (#@!$% copy paste)


-----Original Message-----
From: David Lee [mailto:dba_lee@hotmail.com]
Sent: Thursday, June 15, 2000 12:28 PM
To: brian.maclean@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@homebid.com>
>Reply-To: ORACLE-L@fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L@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@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@hotmail.com
>
>Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California        -- Public Internet access / Mailing Lists
>

________________________________________________________________________