Interesting lesson on ARCHIVELOG mode

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).