Message-Id: <10516.107628@fatcity.com> From: Alex Hillman Date: Fri, 2 Jun 2000 20:33:39 -0400 Subject: RE: Re[2]: Big Whoops 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_01BFCCF3.5D0A6B90 Content-Type: text/plain; charset="iso-8859-1" The problem with this approach (as was posted before) is that only users with access to v$session and v$database can use it. I created PL/SQL function owned by sys (or any other user with select privilege for v$session and v$database) and with execute given to public which return sqlprompt string. For this script to work database should have such function. I have a check if such synonym exist. This is the part of glogin.sql set termout off define user_prompt='' variable sql_prompt varchar2(50) declare v_count number; v_sql_prompt varchar2(50); begin select count(*) into v_count from all_synonyms where synonym_name = 'GET_SQLPROMPT_FS'; :sql_prompt := 'SQL->'; if v_count > 0 then execute immediate 'select rtrim(get_sqlprompt_fs) from dual' into v_sql_prompt; :sql_prompt := v_sql_prompt; end if; end; / column x new_value user_prompt select :sql_prompt x from dual; set sqlprompt "&user_prompt" set termout on I also saved this in the c.sql ( after line connect &1 ) and put it into directory $SQLPATH (in registry on NT) Instead of connect aaa/bbb/@ddd I use @c aaa/bbb/@ddd and this will change SQL prompt. This function produce sqlprompt as hostname:dbname:user_name:session_id:serial#-> I such synonym does not exist it will produce sqlprompt as SQL-> Here is the code of function: create or replace function get_sqlprompt_f return varchar2 is v_host_name varchar2(12); v_dbname varchar2(10); v_session_id varchar2(6); v_serial_nm varchar2(6); v_user_name varchar2(10); begin SELECT d.name into v_dbname from v$database d; select substr(s.machine,1,decode (instr(s.machine,'.'), 0, length(s.machine), instr(s.machine,'.') - 1)) into v_host_name FROM V$SESSION s WHERE s.SID=1; select rtrim(to_char(sid)), rtrim(to_char(serial#)) into v_session_id, v_serial_nm FROM v$session WHERE audsid = userenv('SESSIONID'); select user into v_user_name from dual; return v_host_name||':'||v_dbname||':'||v_user_name||':'||v_session_id||':'||v_seri al_nm||'->'; end get_sqlprompt_f; / show errors; Also you need to give execute on this function to PUBLIC and create public synonym get_sqlprompt_fs for this function. Alex Hillman -----Original Message----- From: Ari D Kaplan [mailto:akaplan@interaccess.com] Sent: Wednesday, May 31, 2000 11:52 AM To: Multiple recipients of list ORACLE-L Subject: Re: Re[2]: Big Whoops Ruth, In my reply I cut out a snippet of the entire program to explain about machine names. The entire script must be run for this to work: set heading off set prompt off spool make_prompt.sql SELECT 'set sqlprompt '''|| d.name ||'@' || substr(s.machine,1,decode (instr(s.machine,'.'), 0, length(s.machine), instr(s.machine,'.') - 1)) || '-SQL> ''' FROM V$SESSION s, V$DATABASE D WHERE s.SID=1; spool off @make_prompt.sql set heading on set feedback on -Ari Kaplan www.arikaplan.com On Wed, 31 May 2000, Ruth Gramolini wrote: > Ari, > I ran this script on my production databases. It give the sqlprompt at the > end of the script but they comes back with the SQL> prompt. How can you > make this permanent? > > Thanks! > Ruth > ----- Original Message ----- > To: Multiple recipients of list ORACLE-L > Sent: Monday, May 29, 2000 6:14 PM > > > > I believe the example shows how to embed the machine name. > > > > SELECT 'set sqlprompt '''|| d.name ||'@' || > > substr(s.machine,1,decode (instr(s.machine,'.'), 0, length(s.machine), > > instr(s.machine,'.') - 1)) || > > '-SQL> ''' > > FROM V$SESSION s, V$DATABASE D > > WHERE s.SID=1; > > > > The output will look like: > > set sqlprompt 'PHIS@survlpd-SQL> ' > > > > > > In the above example, "survlpd" is the machine name. > > > > -Ari > > www.arikaplan.com > > On Mon, 29 May 2000, Jared Still wrote: > > > > > > > > In that case, maybe you could provide various methods > > > of embedding the machine name in the SQL prompt as well. > > > > > > Jared > > > > > > On Sat, 27 May 2000, Ari D Kaplan wrote: > > > > > > > I do the SQL prompt change as well... for the listers benefit I am > > > > providing part of my upcoming EOUG speech (how many of you really are > > > > coming?) that explains how to do this... > > > > > > > > -Ari > > > > www.arikaplan.com > > > > > > > > When you first get your Oracle CD-ROM, install and create your > database, > > > > and go to SQL*Plus, you are greeted with the familiar "SQL>" prompt. > What > > > > many Oracle professionals do not know is that this prompt can be > changed. > > > > It could be useful to have the prompt give the time, the username, > which > > > > machine you are on, or any other information particular to an > application. > > > > The prompt is most easily changed by modifying the global or local > login > > > > scripts. The global login script, which is the > > > > $ORACLE_HOME/sqlplus/admin/glogin.sql file, gets run when anyone > connects > > > > to the database. It is here that you can put in SQL to control what > > > > happens when a user invokes SQL*Plus. Common commands are formatting > of > > > > columns, setting PAGESIZE, putting in messages, changing optimizer > goals, > > > > or changing the prompt. For example: > > > > set heading off > > > > select 'Logged in as ' || username from user_users; > > > > set heading on > > > > will print "Logged in as SCOTT" when you connect as SCOTT. > > > > If there is a file in $ORACLE_PATH called "login.sql", it is referred > to > > > > as a local login script. This will override the global login script. > If > > > > you use local or global login scripts, you can change the SQL prompt. > > > > SELECT 'set sqlprompt '''|| d.name ||'@' || > > > > substr(s.machine,1,decode (instr(s.machine,'.'), 0, length(s.machine), > > > > instr(s.machine,'.') - 1)) || > > > > '-SQL> ''' > > > > FROM V$SESSION s, V$DATABASE D > > > > WHERE s.SID=1; > > > > The output will look like: > > > > set sqlprompt 'PHIS@survlpd-SQL> ' > > > > This will use the "SET SQLPROMPT" command to be the instance name and > the > > > > server name. For this to work, you will need to spool the result of > the > > > > SELECT clause to a file, and then run the file. Putting it all > together: > > > > set heading off > > > > set prompt off > > > > spool make_prompt.sql > > > > SELECT 'set sqlprompt '''|| d.name ||'@' || > > > > substr(s.machine,1,decode (instr(s.machine,'.'), 0, length(s.machine), > > > > instr(s.machine,'.') - 1)) || > > > > '-SQL> ''' > > > > FROM V$SESSION s, V$DATABASE D > > > > WHERE s.SID=1; > > > > spool off > > > > @make_prompt.sql > > > > set heading on > > > > set feedback on > > > > You will now have a nice prompt each time you connect. With multiple > > > > windows open on your computer, this is a good way to keep track of > which > > > > database each window is connect to. > > > > > > > > > > > > On Sat, 27 May 2000, Jared Still wrote: > > > > > > > > > > > > > > > Since then I color-code my windows (light red on black for > production > > > > > > windows)... > > > > > > > > > > This seems to work for some people. A fellow DBA here at > > > > > Blue Cross does this. > > > > > > > > > > It never seemed to work for me. Embedding the database name > > > > > in the SQL prompt seems to work best, at least for me. > > > > > > > > > > Jared > > > > > > > > > > > > > > > > > Steve, Rachel - you are not alone... > > > > > > > > > > > > > > > > > > -Ari > > > > > > > > > > > > On Thu, 25 May 2000, Rachel Carmichael wrote: > > > > > > > > > > > > > me.... > > > > > > > > > > > > > > logged into two terminals, one was production, one was test. In > both the > > > > > > > tablespaces had the same names. So I think I am going to drop > the two > > > > > > > tablespaces in test, so I can recreate from production. > > > > > > > > > > > > > > I do alter tablespace offline; > > > > > > > drop tablespace including contents; > > > > > > > > > > > > > > and then scream as I realize I dropped a production tablespace. > I go into my > > > > > > > user's office and tell him he can kill me later, this is what I > did, how > > > > > > > does he want me to proceed to fix it. fortunately the > production one was > > > > > > > static data, and could be recreated. we do so. > > > > > > > > > > > > > > he then tells me I have used up all my screw-ups for the next 5 > years. > > > > > > > > > > > > > > everyone does stupid stuff. the trick is to admit it and know > how to recover > > > > > > > from it > > > > > > > > > > > > > > Rachel > > > > > > > > > > > > > > > > > > > > > >From: "Steve Boyd" > > > > > > > >Reply-To: ORACLE-L@fatcity.com > > > > > > > >To: Multiple recipients of list ORACLE-L > > > > > > > >Subject: Re: Re[2]: Big Whoops > > > > > > > >Date: Thu, 25 May 2000 07:38:38 -0800 > > > > > > > > > > > > > > > >I'd like to post a question for everyone on the list. > > > > > > > > > > > > > > > >How many of you have ever dropped a table, or done something > like that in > > > > > > > >the wrong database(thinking you were connected to say the test > DB), and > > > > > > > >hosed production? > > > > > > > > > > > > > > > >Steve Boyd > > > > > > > > > > > > > > > >>From: dgoulet@vicr.com > > > > > > > >>Reply-To: ORACLE-L@fatcity.com > > > > > > > >>To: Multiple recipients of list ORACLE-L > > > > > > > > >>Subject: Re[2]: Big Whoops > > > > > > > >>Date: Thu, 25 May 2000 06:40:46 -0800 > > > > > > > >> > > > > > > > >>A couple of years ago I was trying to create a new instance on > one of our > > > > > > > >>Unix > > > > > > > >>boxes to support an additional manufacturing line. Since the > first > > > > > > > >>instance was > > > > > > > >>well tuned, I copied the init.ora file to a new name & did the > little > > > > > > > >>editing > > > > > > > >>that I believed necessary, like a new db_name. What I forgot > was the > > > > > > > >>control_file line. You can guess what happen next, yes the > control files > > > > > > > >>got > > > > > > > >>trashed and the original instance terminated with errors. We > had to > > > > > > > >>recover > > > > > > > >>that one from tape since I hadn't backed up the control files > to trace for > > > > > > > >>some > > > > > > > >>time, but I do now at every shutdown. We live & learn from > our mistakes, > > > > > > > >>hopefully NOT on a production server. > > > > > > > >> > > > > > > > >> > > > > > > > >>BTW: My advice to any new DBA out there is that when an > emergency hits > > > > > > > >>you, > > > > > > > >>or > > > > > > > >>an AW SH%$. First step in the process is to step back, take a > deep breath > > > > > > > >>& > > > > > > > >>calm down. Adrenaline and instant reaction are your worst > enemies. > > > > > > > >> > > > > > > > >>Dick Goulet > > > > > > > >>Senior Oracle DBA > > > > > > > >>Vicor Corporation > > > > > > > >> > > > > > > > >>____________________Reply Separator____________________ > > > > > > > >>Author: "Rachel Carmichael" > > > > > > > >>Date: 5/24/00 6:16 PM > > > > > > > >> > > > > > > > >>Lisa, > > > > > > > >> > > > > > > > >>You have backups? Restore from backup. Otherwise..... recreate > and import > > > > > > > >>is > > > > > > > >>the way to go. > > > > > > > >> > > > > > > > >>I gotta say, the fact that you did this, figured out how to > fix it, and > > > > > > > >>didn't freak out about it, would impress me MUCH more on an > interview than > > > > > > > >>the fact that you have your OCP :) > > > > > > > >> > > > > > > > >>Rachel > > > > > > > >> > > > > > > > >> > > > > > > > >> >From: Lisa_Koivu@gelco.com > > > > > > > >> >Reply-To: ORACLE-L@fatcity.com > > > > > > > >> >To: Multiple recipients of list ORACLE-L > > > > > > > > >> >Subject: Big Whoops > > > > > > > >> >Date: Wed, 24 May 2000 14:44:29 -0800 > > > > > > > >> > > > > > > > > >> >This is by far the funniest thing I have ever done. I began > running the > > > > > > > >> >create > > > > > > > >> >database scripts (ON A TEST MACHINE) when the $ORACLE_SID > was set to a > > > > > > > >> >currently > > > > > > > >> >running database. It's now completely and totally hosed. > I'm just > > > > > > > >> >wondering if > > > > > > > >> >there is any possible way of recovering from this. I am > just recreating > > > > > > > >> >the > > > > > > > >> >database and reimporting the data - it's not a big deal, but > for future > > > > > > > >> >reference, I wonder if this really does mean THE END OF THE > DATABASE AS > > > > > > > >>WE > > > > > > > >> >KNOW > > > > > > > >> >IT. My gut feel is YES. > > > > > > > >> > > > > > > > > >> >I just can't stop laughing. I finish my OCP exams and > completely TRASH > > > > > > > >>a > > > > > > > >> >large > > > > > > > >> >database in the same day (and dump pink ice cream on my > white sweater > > > > > > > >> >simultaneously)! Like Kirti said, Who *WANTS* to be a DBA? > Are you out > > > > > > > >>of > > > > > > > >> >your > > > > > > > >> >mind? you better be! > > > > > > > >> > > > > > > > > >> > > > > > > > > >> > > > > > > > > >> > -- Author: Ari D Kaplan INET: akaplan@interaccess.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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). ------_=_NextPart_001_01BFCCF3.5D0A6B90 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Re[2]: Big Whoops

The problem with this approach (as was posted before) = is that only users with access to v$session and v$database
can use it.

I created PL/SQL function owned by sys (or any other = user with select privilege for v$session and v$database) and with = execute given to public which return sqlprompt string. For this script = to work database should have such function. I have a check if such = synonym exist. This is the part of glogin.sql

set termout off
define user_prompt=3D''

variable sql_prompt varchar2(50)

declare
 v_count number;
 v_sql_prompt varchar2(50);
begin
 select count(*) into v_count from all_synonyms = where synonym_name =3D 'GET_SQLPROMPT_FS';
 :sql_prompt :=3D 'SQL->';
 if v_count > 0 then
  execute immediate 'select = rtrim(get_sqlprompt_fs) from dual' into v_sql_prompt;
  :sql_prompt :=3D v_sql_prompt;
 end if;
end;
/

column x new_value user_prompt

select :sql_prompt x from dual;

set sqlprompt "&user_prompt"

set termout on


I also saved this in the c.sql ( after line connect = &1 )
and put it into directory $SQLPATH (in registry on = NT)

Instead of connect aaa/bbb/@ddd I use @c aaa/bbb/@ddd = and this will change SQL prompt.

This function produce sqlprompt as = hostname:dbname:user_name:session_id:serial#->
I such synonym does not exist it will produce = sqlprompt as SQL->


Here is the code of function:

create or replace function get_sqlprompt_f
return varchar2
is

v_host_name varchar2(12);
v_dbname varchar2(10);
v_session_id varchar2(6);
v_serial_nm varchar2(6);
v_user_name varchar2(10);

begin

SELECT d.name into v_dbname from v$database d;

select substr(s.machine,1,decode = (instr(s.machine,'.'), 0, length(s.machine), instr(s.machine,'.') - 1)) = into v_host_name

FROM V$SESSION s WHERE s.SID=3D1;

select rtrim(to_char(sid)), rtrim(to_char(serial#)) = into v_session_id, v_serial_nm
FROM v$session WHERE audsid =3D = userenv('SESSIONID');

select user into v_user_name from dual;

return = v_host_name||':'||v_dbname||':'||v_user_name||':'||v_session_id||':'||v_= serial_nm||'->';

end get_sqlprompt_f;
/
show errors;


Also you need to give execute on this function to = PUBLIC and create public synonym get_sqlprompt_fs for this = function.


Alex Hillman

-----Original Message-----
From: Ari D Kaplan [mailto:akaplan@interaccess.com]
Sent: Wednesday, May 31, 2000 11:52 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Re[2]: Big Whoops


Ruth,

In my reply I cut out a snippet of the entire program = to explain about
machine names. The entire script must be run for = this to work:

set heading off
set prompt off
spool make_prompt.sql
SELECT 'set sqlprompt '''|| d.name ||'@' ||
substr(s.machine,1,decode (instr(s.machine,'.'), 0, = length(s.machine),
instr(s.machine,'.') - 1)) ||
'-SQL> '''
FROM V$SESSION s, V$DATABASE D
WHERE s.SID=3D1;
spool off
@make_prompt.sql
set heading on
set feedback on

-Ari Kaplan
www.arikaplan.com

On Wed, 31 May 2000, Ruth Gramolini wrote:

> Ari,
> I ran this script on my production = databases.  It give the sqlprompt at the
> end of the script but they comes back with the = SQL>  prompt. How can you
> make this permanent?
>
> Thanks!
> Ruth
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L = <ORACLE-L@fatcity.com>
> Sent: Monday, May 29, 2000 6:14 PM
>
>
> > I believe the example shows how to embed = the machine name.
> >
> > SELECT 'set sqlprompt '''|| d.name ||'@' = ||
> >    = substr(s.machine,1,decode (instr(s.machine,'.'), 0, = length(s.machine),
> >    instr(s.machine,'.') - = 1)) ||
> >    '-SQL> '''
> > FROM V$SESSION s, V$DATABASE D
> > WHERE s.SID=3D1;
> >
> > The output will look like:
> > set sqlprompt 'PHIS@survlpd-SQL> = '
> >
> >
> > In the above example, "survlpd" = is the machine name.
> >
> > -Ari
> > www.arikaplan.com
> > On Mon, 29 May 2000, Jared Still = wrote:
> >
> > >
> > > In that case, maybe you could provide = various methods
> > > of embedding the machine name in the = SQL prompt as well.
> > >
> > > Jared
> > >
> > > On Sat, 27 May 2000, Ari D Kaplan = wrote:
> > >
> > > > I do the SQL prompt change as = well... for the listers benefit I am
> > > > providing part of my upcoming = EOUG speech (how many of you really are
> > > > coming?) that explains how to do = this...
> > > >
> > > > -Ari
> > > > www.arikaplan.com
> > > >
> > > > When you first get your Oracle = CD-ROM, install and create your
> database,
> > > > and go to SQL*Plus, you are = greeted with the familiar "SQL>" prompt.
> What
> > > > many Oracle professionals do not = know is that this prompt can be
> changed.
> > > > It could be useful to have the = prompt give the time, the username,
> which
> > > > machine you are on, or any other = information particular to an
> application.
> > > > The prompt is most easily = changed by modifying the global or local
> login
> > > > scripts. The global login = script, which is the
> > > > = $ORACLE_HOME/sqlplus/admin/glogin.sql file, gets run when anyone
> connects
> > > > to the database. It is here that = you can put in SQL to control what
> > > > happens when a user invokes = SQL*Plus. Common commands are formatting
> of
> > > > columns, setting PAGESIZE, = putting in messages, changing optimizer
> goals,
> > > > or changing the prompt. For = example:
> > > > set heading off
> > > > select 'Logged in as ' || = username from user_users;
> > > > set heading on
> > > > will print "Logged in as = SCOTT" when you connect as SCOTT.
> > > > If there is a file in = $ORACLE_PATH called "login.sql", it is referred
> to
> > > > as a local login script. This = will override the global login script.
> If
> > > > you use local or global login = scripts, you can change the SQL prompt.
> > > > SELECT 'set sqlprompt '''|| = d.name ||'@' ||
> > > > substr(s.machine,1,decode = (instr(s.machine,'.'), 0, length(s.machine),
> > > > instr(s.machine,'.') - 1)) = ||
> > > > '-SQL> '''
> > > > FROM V$SESSION s, V$DATABASE = D
> > > > WHERE s.SID=3D1;
> > > > The output will look = like:
> > > > set sqlprompt = 'PHIS@survlpd-SQL> '
> > > > This will use the "SET = SQLPROMPT" command to be the instance name and
> the
> > > > server name. For this to work, = you will need to spool the result of
> the
> > > > SELECT clause to a file, and = then run the file. Putting it all
> together:
> > > > set heading off
> > > > set prompt off
> > > > spool make_prompt.sql
> > > > SELECT 'set sqlprompt '''|| = d.name ||'@' ||
> > > > substr(s.machine,1,decode = (instr(s.machine,'.'), 0, length(s.machine),
> > > > instr(s.machine,'.') - 1)) = ||
> > > > '-SQL> '''
> > > > FROM V$SESSION s, V$DATABASE = D
> > > > WHERE s.SID=3D1;
> > > > spool off
> > > > @make_prompt.sql
> > > > set heading on
> > > > set feedback on
> > > > You will now have a nice prompt = each time you connect. With multiple
> > > > windows open on your computer, = this is a good way to keep track of
> which
> > > > database each window is connect = to.
> > > >
> > > >
> > > > On Sat, 27 May 2000, Jared Still = wrote:
> > > >
> > > > >
> > > > > > Since then I = color-code my windows (light red on black for
> production
> > > > > > windows)...
> > > > >
> > > > > This seems to work for some = people.  A fellow DBA here at
> > > > > Blue Cross does = this.
> > > > >
> > > > > It never seemed to work for = me.  Embedding the database name
> > > > > in the SQL prompt seems to = work best, at least for me.
> > > > >
> > > > > Jared
> > > > >
> > > > > >
> > > > > > Steve, Rachel - you = are not alone...
> > > > > >
> > > > > >
> > > > > > -Ari
> > > > > >
> > > > > > On Thu, 25 May 2000, = Rachel Carmichael wrote:
> > > > > >
> > > > > > > me....
> > > > > > >
> > > > > > > logged into two = terminals, one was production, one was test. In
> both the
> > > > > > > tablespaces had = the same names. So I think I am going to drop
> the two
> > > > > > > tablespaces in = test, so I can recreate from production.
> > > > > > >
> > > > > > > I do alter = tablespace <xxx> offline;
> > > > > > > drop tablespace = <xxx> including contents;
> > > > > > >
> > > > > > > and then scream = as I realize I dropped a production tablespace.
> I go into my
> > > > > > > user's office and = tell him he can kill me later, this is what I
> did, how
> > > > > > > does he want me = to proceed to fix it.  fortunately the
> production one was
> > > > > > > static data, and = could be recreated. we do so.
> > > > > > >
> > > > > > > he then tells me = I have used up all my screw-ups for the next 5
> years.
> > > > > > >
> > > > > > > everyone does = stupid stuff. the trick is to admit it and know
> how to recover
> > > > > > > from it
> > > > > > >
> > > > > > > Rachel
> > > > > > >
> > > > > > >
> > > > > > > >From: = "Steve Boyd" <pimaco_oracle@hotmail.com>
> > > > > > > >Reply-To: = ORACLE-L@fatcity.com
> > > > > > > >To: Multiple = recipients of list ORACLE-L <ORACLE-L@fatcity.com>
> > > > > > > >Subject: Re: = Re[2]: Big Whoops
> > > > > > > >Date: Thu, 25 = May 2000 07:38:38 -0800
> > > > > > > >
> > > > > > > >I'd like to = post a question for everyone on the list.
> > > > > > > >
> > > > > > > >How many of = you have ever dropped a table, or done something
> like that in
> > > > > > > >the wrong = database(thinking you were connected to say the test
> DB), and
> > > > > > > >hosed = production?
> > > > > > > >
> > > > > > > >Steve = Boyd
> > > > > > > >
> > > > > > > >>From: = dgoulet@vicr.com
> > > > > > > >>Reply-To: = ORACLE-L@fatcity.com
> > > > > > > >>To: = Multiple recipients of list ORACLE-L
> <ORACLE-L@fatcity.com>
> > > > > > > >>Subject: = Re[2]: Big Whoops
> > > > > > > >>Date: = Thu, 25 May 2000 06:40:46 -0800
> > > > > > > >>
> > > > > > > >>A couple = of years ago I was trying to create a new instance on
> one of our
> > > > > > > = >>Unix
> > > > > > > >>boxes to = support an additional manufacturing line.  Since the
> first
> > > > > > > >>instance = was
> > > > > > > >>well = tuned, I copied the init.ora file to a new name & did the
> little
> > > > > > > = >>editing
> > > > > > > >>that I = believed necessary, like a new db_name.  What I forgot
> was the
> > > > > > > = >>control_file line.  You can guess what happen next, yes = the
> control files
> > > > > > > >>got=
> > > > > > > >>trashed = and the original instance terminated with errors.  We
> had to
> > > > > > > = >>recover
> > > > > > > >>that one = from tape since I hadn't backed up the control files
> to trace for
> > > > > > > = >>some
> > > > > > > >>time, but = I do now at every shutdown.  We live & learn from
> our mistakes,
> > > > > > > >>hopefully = NOT on a production server.
> > > > > > > >>
> > > > > > > >>
> > > > > > > >>BTW: My = advice to any new DBA out there is that when an
> emergency hits
> > > > > > > = >>you,
> > > > > > > >>or
> > > > > > > >>an AW = SH%$.  First step in the process is to step back, take a
> deep breath
> > > > > > > = >>&
> > > > > > > >>calm = down.  Adrenaline and instant reaction are your worst
> enemies.
> > > > > > > >>
> > > > > > > >>Dick = Goulet
> > > > > > > >>Senior = Oracle DBA
> > > > > > > >>Vicor = Corporation
> > > > > > > >>
> > > > > > > = >>____________________Reply Separator____________________
> > > > > > > >>Author: = "Rachel Carmichael" <carmichr@hotmail.com>
> > > > > > > = >>Date:       5/24/00 6:16 = PM
> > > > > > > >>
> > > > > > > = >>Lisa,
> > > > > > > >>
> > > > > > > >>You have = backups? Restore from backup. Otherwise..... recreate
> and import
> > > > > > > >>is
> > > > > > > >>the way = to go.
> > > > > > > >>
> > > > > > > >>I gotta = say, the fact that you did this, figured out how to
> fix it, and
> > > > > > > >>didn't = freak out about it, would impress me MUCH more on an
> interview than
> > > > > > > >>the fact = that you have your OCP :)
> > > > > > > >>
> > > > > > > = >>Rachel
> > > > > > > >>
> > > > > > > >>
> > > > > > > >> = >From: Lisa_Koivu@gelco.com
> > > > > > > >> = >Reply-To: ORACLE-L@fatcity.com
> > > > > > > >> >To: = Multiple recipients of list ORACLE-L
> <ORACLE-L@fatcity.com>
> > > > > > > >> = >Subject: Big Whoops
> > > > > > > >> = >Date: Wed, 24 May 2000 14:44:29 -0800
> > > > > > > >> = >
> > > > > > > >> >This = is by far the funniest thing I have ever done.  I began
> running the
> > > > > > > >> = >create
> > > > > > > >> = >database scripts (ON A TEST MACHINE) when the $ORACLE_SID
> was set to a
> > > > > > > >> = >currently
> > > > > > > >> = >running database.  It's now completely and totally = hosed.
> I'm just
> > > > > > > >> = >wondering if
> > > > > > > >> = >there is any possible way of recovering from this.  I = am
> just recreating
> > > > > > > >> = >the
> > > > > > > >> = >database and reimporting the data - it's not a big deal, but
> for future
> > > > > > > >> = >reference, I wonder if this really does mean THE END OF THE
> DATABASE AS
> > > > > > > >>WE
> > > > > > > >> = >KNOW
> > > > > > > >> = >IT.  My gut feel is YES.
> > > > > > > >> = >
> > > > > > > >> >I = just can't stop laughing.  I finish my OCP exams and
> completely TRASH
> > > > > > > >>a
> > > > > > > >> = >large
> > > > > > > >> = >database in the same day (and dump pink ice cream on my
> white sweater
> > > > > > > >> = >simultaneously)!  Like Kirti said, Who *WANTS* to be a = DBA?
> Are you out
> > > > > > > >>of
> > > > > > > >> = >your
> > > > > > > >> = >mind?  you better be!
> > > > > > > >> = >
> > > > > > > >> = >
> > > > > > > >> = >
> > > > > > > >> =