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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
