RE: Interesting lesson on ARCHIVELOG mode
Good Posting, JIM . I had once gone thru the exactly same experience. Oracle database has some processes that work both independently , but again, they work in tandem. Here is the peice where LGWR (in Archivelog) and ARCH(s) are expected to work in tandem with each other (in ARCHIVELOG mode). In Noarchivelog mode, ARCH(s) are not needed. SO LGWR works independently of ARCHs. but you need to tell that to LGWR ( that archiving is NOT happening , the command alter database noarchivelog basically tells LGWR precisely that) so that LGWR can recycle the log files to do a log switch ( log1 -- log2 --log1). In archivelogmode it will be log1 --(arch_log2_ by arch) --log2 --( arch_log1_+1 by arch) -- log1 I think, a good understanding of the Concepts DOC is the most imporatnt thing for a Oracle DBA. Pradip -Original Message- Sent: Wednesday, April 02, 2003 7:29 PM To: Multiple recipients of list ORACLE-L 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
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 [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
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: [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).
