Re: Interesting lesson on ARCHIVELOG mode

Hi James,

Hopefully the other lesson you've learnt is the importance of training, even for "Junior DBAs" so that such fundamentally basic but potentially costly mistakes can be avoided.

Cheers

Richard

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, April 02, 2003 11:58 PM

> Some of you on the list might find this interesting.
>
> I just wanted to relate a story with respect to an incident
experienced in
> the last few days on one of our test databases. Environment is
Compaq Tru64
> Unix / Oracle 8.1.7.4.
>
> A few days ago, I remember talking to a junior DBA who assists me in
the
> Oracle area, concerning excessive space usage on one of the Unix
machines
> running a test database environment. I noted that the database was
running
> in ARCHIVELOG mode with automatic archiving (of course), and
generating a
> great many archived logs since there was considerable activity on
that
> instance/database. We discussed the matter and agreed that there
was no
> need to have ARCHIVELOG mode turned on in this case. So I told my
assistant
> DBA to go ahead and make the database NOARCHIVELOG, which I thought
she
> understood.
>
> Yesterday, she comes to me with a host of problems she has been
experiencing
> on that test database, one of which was many failed attempts to
import a 2
> Million row table from another database's export. It seemed that
the import
> would just hang after importing about 130,000 rows. She repeatedly
> cancelled the import, resorted to cycling the database, creating a
another
> table with just a subset of the columns of the original, limiting
the number
> of rows imported at one time, fooling with the "buffer" parameters
of the
> import control file, trying SQL*LOADER, and so on. Quite
frustrated, she
> came to me for advice.
>
> I had forgotten about the ARCHIVELOG mode issue a few days earlier,
so I
> began scratching my head as I looked unsuccessfully for signs of
trouble in
> alert logs and traces. I thought maybe a rollback segment had run
out of
> room, lost its brains, or maybe temp space had become a problem.
But again,
> no sign of any of these issues in alerts or traces. Suspecting
database
> corruption, I took a full export to see if export would report any
corrupted
> blocks. That worked flawlessly. I began to wonder if we should
just start
> from scratch and recreate the database. Then something interesting
became
> apparant.
>
> Looking at V$DATABASE, I noticed that the database was still in
ARCHIVELOG
> mode! When I asked about this, it seems that she thought that simply
> commenting out the init.ora parameters:
> > log_archive_start=true
> > log_archive_dest=whatever
> > log_archive_format=whatever
> and then recycling the database would take care of the whole issue of
> ARCHIVELOG mode, making the database become NOARCHIVELOG mode.
Well, guess
> what.....it didn't.
>
> The lesson learned was that with the database still in ARCHIVELOG
mode and
> automatic archiving turned off, obviously enough DML would cause the
> database to hang whenever it did a log switch, awaiting us DBAs to
manually
> archive the filled redo logs. Realizing this, of course we then did
the
> prudent thing:
> > alter database noarchivelog
> and lived happily ever after.
>
> Had I continued to assume database corruption and just had her
recreate the
> database, it WOULD have indeed solved the problem BUT ONLY because
the
> database would have come up in NOARCHIVELOG mode. However, it
certainly
> would have bothered me as to why the database had become corrupted
in the
> first place.
>
> I am very happy to know what actually happened, that the database
wasn't
> corrupted at all. It was just someone's misunderstanding in not
realizing
> that "ARCHIVELOG mode" and "automatic archiving" are two related but
totally
> different things!
>
> Jim Damiano
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: James Damiano
> INET: jadam_at_nhes.state.nh.us
>
> 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.net
-- 
Author: Richard Foote
  INET: richard.foote_at_telstra.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).