Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: svrmgrl echo v$database in script

RE: RE: svrmgrl echo v$database in script

From: <Jared.Still_at_radisys.com>
Date: Thu, 03 Oct 2002 13:11:13 -0800
Message-ID: <F001.004E00DF.20021003131113@fatcity.com>


Dan,

I've had a similar problem with glogin.sql and login.sql in the past.

Rather than gut my login scripts, I just edited or removed the SQLPATH environment variable. And set a variable in the korn script to point to the location of the SQL files.

This variable is then used to locate the needed scripts.

Jared

"Fink, Dan" <Dan.Fink_at_mdx.com>
Sent by: root_at_fatcity.com
 10/03/2002 12:43 PM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: RE: svrmgrl echo v$database in script


Dick,
                 That is one method. My rule is that all destructive 
actions are run
from scripts that require the database name as a parameter. If the $ORACLE_SID and parameter don't match, the script terminates. I realize this
is redundant, but better safe than having to perform a production recovery.
I don't have to code in any database specific values like 'DEV', 'PROD', etc. so the script can be used for any database. My 'stub' korn shell script
already has this built in, so I don't even have to remember it.
                 As an aside, one of the problems I have had with putting 
this kind
of code in login.sql or glogin.sql is that it causes problems if the database is not open. I recall spending a few hours troubleshooting a monitoring script. It would work just fine while we were developing and testing, but it would fail overnight in the cron job. The database was down
and the glogin.sql script was terminating. To this day, I still don't put any queries inside login.sql and glogin.sql. One of those things that is probably fixable, but I'd rather work in improving my Buffer Cache Hit Ratio.

Dan

-----Original Message-----
Sent: Thursday, October 03, 2002 11:42 AM To: Fink; Dan; Multiple recipients of list ORACLE-L

Dan,

    You can use a anonymous PL/SQL block like the following in you login.sql
or
script file is necessary, it keeps those errors from happening:

whenever sqlerror exit
declare
  db varchar2(30);
begin
  select global_name into db from global_name;   if(db not like 'DEV%') then raise_application_error(-20100, "Wrong Database");
  end if;
end;
/

Dick Goulet

____________________Reply Separator____________________
Author: "Fink; Dan" <Dan.Fink_at_mdx.com>
Date:       10/3/2002 9:33 AM

What happens if the ORACLE_SID is set to one value and the caller uses SQL*Net to access a different database? While this is not usually the case in scripts, it could be a problem if the script is parted of a called function.
ORACLE_SID=DEV
sqlplus scott/tiger_at_PROD <-- I'm not connected to DEV anymore.

I realize this is nit-picking, but I've done far too many recoveries because
someone thought they were in DEV when they actually connected to PROD. I'd rather know EXACTLY which db I'm connected to, not which one the O/S thinks
I should be in.

My $.02

Dan Fink

-----Original Message-----
[mailto:Scott.Shafer_at_dcpds.cpms.osd.mil] Sent: Thursday, October 03, 2002 9:43 AM To: Multiple recipients of list ORACLE-L

echo $ORACLE_SID >> logfile

Scott Shafer
San Antonio, TX
210.581.6217

> -----Original Message-----
> From: Baker, Barbara [SMTP:bbaker_at_denvernewspaperagency.com]
> Sent: Wednesday, October 02, 2002 6:28 PM
> To: Multiple recipients of list ORACLE-L
> Subject: svrmgrl echo v$database in script
>
>
> Oracle 8.0.5
> Solaris 2.6
>
> List:
> I've created a script (ksh) called from elsewhere that shuts down the
> database. I REALLY want to echo the name of the database into my log
file
> before I shut down. While "select name from v$database" works fine
from
> svrmgrl interactively, it throws up in the script. I'd guess the $ sign
> is
> screwing it up. (I can get other commands to work within the script.)
> However, I don't know what to do about it.
>
> Any ideas?
>
> Thx!!!
>
> Barb
>
> $ svrmgrl
>
> SVRMGR> connect internal
> Connected.
> SVRMGR> select name from v$database;
> NAME
> ---------
> TADENT
> 1 row selected.
>
>
> #!/usr/bin/ksh
> # $Id: stop_db.sh ver.1 10/02/2002 B.Baker Exp $
> # Name: stop_db.sh
> # Author: Barb Baker
> # Purpose: execute shutdown immediate on current database
> # (i.e., database pointed to by current value of
ORACLE_SID)
> ############################################################
> echo "Stop oracle instance \"${ORACLE_SID}\" at `date` "
> ${ORACLE_HOME}/bin/svrmgrl << EOF
> connect internal
> select name from v_$database;
> EOF
>
>
> $ ./stop_db.sh
> Stop oracle instance "tadent" at Wed Oct 2 16:24:59 MDT 2002
>
>
> SVRMGR> Connected.
> SVRMGR> select name from v_
> *
> ORA-00942: table or view does not exist
> SVRMGR>
> Server Manager complete.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Baker, Barbara
> INET: bbaker_at_denvernewspaperagency.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Scott.Shafer_at_dcpds.cpms.osd.mil

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Fink, Dan
  INET: Dan.Fink_at_mdx.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Fink, Dan
  INET: Dan.Fink_at_mdx.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Oct 03 2002 - 16:11:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US