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" <[EMAIL PROTECTED]> 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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).
