RE: Cannot allocate new log - checkpoint not complete
Dennis, This is our actual distribution: Datafiles belonging to data in a separate disk, name it /baandata Datafiles belonging to index in a separate disk, name it /baanindex And 3 redolog files, two of them in another two separate disks, and the third one located in the same device as the data files (/baandata). All of them are mirrored disks. Your comment makes sense, but if keeping datafiles and one of the redolog files in the same device should affect performance, then I wonder why the cannot allocate new log, checkpoint not complete message is affecting to the 3 redolog files and not only to the one located in that datafile device. I did not think on this. Anyway I have no more disks in which I can split the redologs... I can not wait for your comments! Regards, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 03 de abril de 2003 17:04 Para: Multiple recipients of list ORACLE-L Asunto: RE: Cannot allocate new log - checkpoint not complete Fermin - Connor's reply sparked an idea. By any chance do you have your redo logs on the same device as your data files? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, April 03, 2003 5:04 AM To: Multiple recipients of list ORACLE-L Basically as the message suggests the redo cannot be recycled until the checkpoint has completed flushing out the cache. A *workaround* is to add redo log (size or number) but its really a heads-up about your I/O subsystem not being up to keep up under stress. hth connor --- Fermin Bernaus Berraondo [EMAIL PROTECTED] wrote: I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: DENNIS WILLIAMS INET: [EMAIL PROTECTED
RE: Cannot allocate new log - checkpoint not complete
No problem with the archive log management scripts, I back up archived log files daily, no matter how many of them we have. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Reardon, Bruce (CALBBAY) Enviado el: viernes, 04 de abril de 2003 3:28 Para: Multiple recipients of list ORACLE-L Asunto: RE: Cannot allocate new log - checkpoint not complete There are some other effects I can think of. Up to you if these are important / significant to your users. If you make them bigger and you have a standby database then the standby might end up being further behind production (unless you have a script to workaround this) and also in this case you may then not get the files transferring successfully (depends on your network etc). Also, if you make them bigger and you have a loss of all redo logs (hopefully unlikely if they are mirrored on mirrored disks) then you will lose more data as it will be a longer period of time since the last archive log was created. And what about your archive log management scripts - do they keep x days worth of files (in which case the volume of archive log on disk will not change) or do they keep y files - in this case the volume of disks would increase unless the script(s) are altered. Regards, Bruce Reardon -Original Message- Sent: Friday, 4 April 2003 3:44 AM To: Multiple recipients of list ORACLE-L Correct. The only potential disadvantage is that recovery will take longer when bringing up the database after a crash. Jay Miller -Original Message- Sent: Thursday, April 03, 2003 9:39 AM To: Multiple recipients of list ORACLE-L I dissagree, they will be bigger but there will be less of them. If the amount of processed data does not change, I do not think changing the size of the redolog files should affect the total amount fo bytes to be backeup up Thanks for the recommendation anyway ;) Cheers, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Stefan Jahnke Enviado el: jueves, 03 de abril de 2003 15:44 Para: Multiple recipients of list ORACLE-L Asunto: AW: Cannot allocate new log - checkpoint not complete Hi I would suggest to increase the redo log size. Doesn't effect you during daily operation, but prevents the database from hanging during nightly batches. No side effects I can think of (except for the fact that, of course, it will take you longer to backup the archived logs since the files are bigger, duh ;). Good luck Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Fermin Bernaus Berraondo [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. April 2003 10:04 An: Multiple recipients of list ORACLE-L Betreff: Cannot allocate new log - checkpoint not complete I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised
AW: Cannot allocate new log - checkpoint not complete
Sorry, that wasn't clear enough: I meant the backup time for one individual redo log will increase. Of course your right, ... the overall bytes to be backed up don't increase. Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. April 2003 19:44 An: Multiple recipients of list ORACLE-L Betreff: RE: Cannot allocate new log - checkpoint not complete Correct. The only potential disadvantage is that recovery will take longer when bringing up the database after a crash. Jay Miller -Original Message- Sent: Thursday, April 03, 2003 9:39 AM To: Multiple recipients of list ORACLE-L I dissagree, they will be bigger but there will be less of them. If the amount of processed data does not change, I do not think changing the size of the redolog files should affect the total amount fo bytes to be backeup up Thanks for the recommendation anyway ;) Cheers, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Stefan Jahnke Enviado el: jueves, 03 de abril de 2003 15:44 Para: Multiple recipients of list ORACLE-L Asunto: AW: Cannot allocate new log - checkpoint not complete Hi I would suggest to increase the redo log size. Doesn't effect you during daily operation, but prevents the database from hanging during nightly batches. No side effects I can think of (except for the fact that, of course, it will take you longer to backup the archived logs since the files are bigger, duh ;). Good luck Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Fermin Bernaus Berraondo [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. April 2003 10:04 An: Multiple recipients of list ORACLE-L Betreff: Cannot allocate new log - checkpoint not complete I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work
AW: Cannot allocate new log - checkpoint not complete
hi, see inline comments below ... There are some other effects I can think of. Up to you if these are important / significant to your users. If you make them bigger and you have a standby database then the standby might end up being further behind production (unless you have a script to workaround this) and also in this case you may then not get the files transferring successfully (depends on your network etc). - His redo logs are 50M right now, that's pretty small. I think it won't hurt to double the size (unless you run the server on really small hardware). Also, if you make them bigger and you have a loss of all redo logs (hopefully unlikely if they are mirrored on mirrored disks) then you will lose more data as it will be a longer period of time since the last archive log was created. - That's a VERY good point. Haven't thought about it. The bigger the log you loose, the more data is lost (of course). And what about your archive log management scripts - do they keep x days worth of files (in which case the volume of archive log on disk will not change) or do they keep y files - in this case the volume of disks would increase unless the script(s) are altered. Regards, Bruce Reardon -Original Message- Sent: Friday, 4 April 2003 3:44 AM To: Multiple recipients of list ORACLE-L Correct. The only potential disadvantage is that recovery will take longer when bringing up the database after a crash. Jay Miller -Original Message- Sent: Thursday, April 03, 2003 9:39 AM To: Multiple recipients of list ORACLE-L I dissagree, they will be bigger but there will be less of them. If the amount of processed data does not change, I do not think changing the size of the redolog files should affect the total amount fo bytes to be backeup up Thanks for the recommendation anyway ;) Cheers, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Stefan Jahnke Enviado el: jueves, 03 de abril de 2003 15:44 Para: Multiple recipients of list ORACLE-L Asunto: AW: Cannot allocate new log - checkpoint not complete Hi I would suggest to increase the redo log size. Doesn't effect you during daily operation, but prevents the database from hanging during nightly batches. No side effects I can think of (except for the fact that, of course, it will take you longer to backup the archived logs since the files are bigger, duh ;). Good luck Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Fermin Bernaus Berraondo [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. April 2003 10:04 An: Multiple recipients of list ORACLE-L Betreff: Cannot allocate new log - checkpoint not complete I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com
RE: Cannot allocate new log - checkpoint not complete
Its not really a particular redo log that is the issue. You've used up redo's (say) 1, 2, 3 and you want to cycle around to 1 but the checkpoint that would free up redo 1 is not yet finished. Thus its not a single redo log that is the problem - the IO rate of the checkpoint is not sufficient quick to avoid the redo cycling around...If one of your redo's is on common datafile disk, this could contribute to this hth connor --- Fermin Bernaus Berraondo [EMAIL PROTECTED] wrote: Dennis, This is our actual distribution: Datafiles belonging to data in a separate disk, name it /baandata Datafiles belonging to index in a separate disk, name it /baanindex And 3 redolog files, two of them in another two separate disks, and the third one located in the same device as the data files (/baandata). All of them are mirrored disks. Your comment makes sense, but if keeping datafiles and one of the redolog files in the same device should affect performance, then I wonder why the cannot allocate new log, checkpoint not complete message is affecting to the 3 redolog files and not only to the one located in that datafile device. I did not think on this. Anyway I have no more disks in which I can split the redologs... I can not wait for your comments! Regards, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 03 de abril de 2003 17:04 Para: Multiple recipients of list ORACLE-L Asunto: RE: Cannot allocate new log - checkpoint not complete Fermin - Connor's reply sparked an idea. By any chance do you have your redo logs on the same device as your data files? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, April 03, 2003 5:04 AM To: Multiple recipients of list ORACLE-L Basically as the message suggests the redo cannot be recycled until the checkpoint has completed flushing out the cache. A *workaround* is to add redo log (size or number) but its really a heads-up about your I/O subsystem not being up to keep up under stress. hth connor --- Fermin Bernaus Berraondo [EMAIL PROTECTED] wrote: I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858
RE: Cannot allocate new log - checkpoint not complete
So do you think the following distribution will contribute to a better performance: data datafiles - device a index datafiles - device b redolog1 - device c redolog2 - device d redolog3 - device c instead of: data datafiles - device a index datafiles - device b redolog1 - device c redolog2 - device d redolog3 - device a Because I only have 5 devices available. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Connor McDonald Enviado el: viernes, 04 de abril de 2003 11:49 Para: Multiple recipients of list ORACLE-L Asunto: RE: Cannot allocate new log - checkpoint not complete Its not really a particular redo log that is the issue. You've used up redo's (say) 1, 2, 3 and you want to cycle around to 1 but the checkpoint that would free up redo 1 is not yet finished. Thus its not a single redo log that is the problem - the IO rate of the checkpoint is not sufficient quick to avoid the redo cycling around...If one of your redo's is on common datafile disk, this could contribute to this hth connor --- Fermin Bernaus Berraondo [EMAIL PROTECTED] wrote: Dennis, This is our actual distribution: Datafiles belonging to data in a separate disk, name it /baandata Datafiles belonging to index in a separate disk, name it /baanindex And 3 redolog files, two of them in another two separate disks, and the third one located in the same device as the data files (/baandata). All of them are mirrored disks. Your comment makes sense, but if keeping datafiles and one of the redolog files in the same device should affect performance, then I wonder why the cannot allocate new log, checkpoint not complete message is affecting to the 3 redolog files and not only to the one located in that datafile device. I did not think on this. Anyway I have no more disks in which I can split the redologs... I can not wait for your comments! Regards, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 03 de abril de 2003 17:04 Para: Multiple recipients of list ORACLE-L Asunto: RE: Cannot allocate new log - checkpoint not complete Fermin - Connor's reply sparked an idea. By any chance do you have your redo logs on the same device as your data files? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, April 03, 2003 5:04 AM To: Multiple recipients of list ORACLE-L Basically as the message suggests the redo cannot be recycled until the checkpoint has completed flushing out the cache. A *workaround* is to add redo log (size or number) but its really a heads-up about your I/O subsystem not being up to keep up under stress. hth connor --- Fermin Bernaus Berraondo [EMAIL PROTECTED] wrote: I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo 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
RE: Cannot allocate new log - checkpoint not complete
Fermin, The checkpoint incomplete message is a statement that your DBWR is not keeping up with the pace of redo generation. You can try to paint over the problem by adding redo log files. This works when you create enough redo log file space that your peak redo generation event concludes before your system needs to reuse an online redo log file. The real root cause of this problem is that your DBWR is unable to keep up. Often, this occurs because DBWR has to compete with high I/O loads to your database files. Probably the most common cause of that high I/O load is one or more nasty SQL queries hammering on the device(s). Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Chicago, London, Reykjavik, Ottawa, Denver - Visit www.hotsos.com for schedule details... - IOUG-A Live 2003, Orlando, 10am Monday 28 April: Oracle Operational Timing Data -Original Message- Berraondo Sent: Friday, April 04, 2003 1:19 AM To: Multiple recipients of list ORACLE-L Dennis, This is our actual distribution: Datafiles belonging to data in a separate disk, name it /baandata Datafiles belonging to index in a separate disk, name it /baanindex And 3 redolog files, two of them in another two separate disks, and the third one located in the same device as the data files (/baandata). All of them are mirrored disks. Your comment makes sense, but if keeping datafiles and one of the redolog files in the same device should affect performance, then I wonder why the cannot allocate new log, checkpoint not complete message is affecting to the 3 redolog files and not only to the one located in that datafile device. I did not think on this. Anyway I have no more disks in which I can split the redologs... I can not wait for your comments! Regards, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 03 de abril de 2003 17:04 Para: Multiple recipients of list ORACLE-L Asunto: RE: Cannot allocate new log - checkpoint not complete Fermin - Connor's reply sparked an idea. By any chance do you have your redo logs on the same device as your data files? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, April 03, 2003 5:04 AM To: Multiple recipients of list ORACLE-L Basically as the message suggests the redo cannot be recycled until the checkpoint has completed flushing out the cache. A *workaround* is to add redo log (size or number) but its really a heads-up about your I/O subsystem not being up to keep up under stress. hth connor --- Fermin Bernaus Berraondo [EMAIL PROTECTED] wrote: I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he
RE: Cannot allocate new log - checkpoint not complete
Fermin I would definitely go for your second configuration. I don't think there is a problem with several redo logs on the same device, since Oracle is only writing to one log at a time. Actually, if I could devote two devices to redo logs, I would mirror the redo logs. You can easily recover from the loss of a data file, but not easily from the loss of an online redo log. And while we're on the subject, mirror your control files as well. The problem I've experienced with placing redo logs on the same device is data files is that when your database experiences a really large number of transactions, Oracle is trying desperately to write changed data blocks to disk at the same time it is trying to write redo data. At some point, Oracle can't service new transactions until something completes. From the outside you experience this as a freeze. In this situation I find it very handy to submit a STATSPACK snapshot, or run a query that will reveal the system wait events. You've received a lot of excellent advice on this subject from some really excellent people (and so-so advice from me). I would highly recommend that you print all the replies and study them to understand what these people are really saying. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, April 04, 2003 4:14 AM To: Multiple recipients of list ORACLE-L So do you think the following distribution will contribute to a better performance: data datafiles - device a index datafiles - device b redolog1 - device c redolog2 - device d redolog3 - device c instead of: data datafiles - device a index datafiles - device b redolog1 - device c redolog2 - device d redolog3 - device a Because I only have 5 devices available. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Connor McDonald Enviado el: viernes, 04 de abril de 2003 11:49 Para: Multiple recipients of list ORACLE-L Asunto: RE: Cannot allocate new log - checkpoint not complete Its not really a particular redo log that is the issue. You've used up redo's (say) 1, 2, 3 and you want to cycle around to 1 but the checkpoint that would free up redo 1 is not yet finished. Thus its not a single redo log that is the problem - the IO rate of the checkpoint is not sufficient quick to avoid the redo cycling around...If one of your redo's is on common datafile disk, this could contribute to this hth connor --- Fermin Bernaus Berraondo [EMAIL PROTECTED] wrote: Dennis, This is our actual distribution: Datafiles belonging to data in a separate disk, name it /baandata Datafiles belonging to index in a separate disk, name it /baanindex And 3 redolog files, two of them in another two separate disks, and the third one located in the same device as the data files (/baandata). All of them are mirrored disks. Your comment makes sense, but if keeping datafiles and one of the redolog files in the same device should affect performance, then I wonder why the cannot allocate new log, checkpoint not complete message is affecting to the 3 redolog files and not only to the one located in that datafile device. I did not think on this. Anyway I have no more disks in which I can split the redologs... I can not wait for your comments! Regards, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 03 de abril de 2003 17:04 Para: Multiple recipients of list ORACLE-L Asunto: RE: Cannot allocate new log - checkpoint not complete Fermin - Connor's reply sparked an idea. By any chance do you have your redo logs on the same device as your data files? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, April 03, 2003 5:04 AM To: Multiple recipients of list ORACLE-L Basically as the message suggests the redo cannot be recycled until the checkpoint has completed flushing out the cache. A *workaround* is to add redo log (size or number) but its really a heads-up about your I/O subsystem not being up to keep up under stress. hth connor --- Fermin Bernaus Berraondo [EMAIL PROTECTED] wrote: I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan
Cannot allocate new log - checkpoint not complete
I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermn Bernaus Berraondo Dpto. de Informtica SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo 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).
Re: Cannot allocate new log - checkpoint not complete
Basically as the message suggests the redo cannot be recycled until the checkpoint has completed flushing out the cache. A *workaround* is to add redo log (size or number) but its really a heads-up about your I/O subsystem not being up to keep up under stress. hth connor --- Fermin Bernaus Berraondo [EMAIL PROTECTED] wrote: I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
AW: Cannot allocate new log - checkpoint not complete
Hi I would suggest to increase the redo log size. Doesn't effect you during daily operation, but prevents the database from hanging during nightly batches. No side effects I can think of (except for the fact that, of course, it will take you longer to backup the archived logs since the files are bigger, duh ;). Good luck Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Fermin Bernaus Berraondo [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. April 2003 10:04 An: Multiple recipients of list ORACLE-L Betreff: Cannot allocate new log - checkpoint not complete I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo 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: Stefan Jahnke 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).
RE: Cannot allocate new log - checkpoint not complete
Fermin - Connor's reply sparked an idea. By any chance do you have your redo logs on the same device as your data files? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, April 03, 2003 5:04 AM To: Multiple recipients of list ORACLE-L Basically as the message suggests the redo cannot be recycled until the checkpoint has completed flushing out the cache. A *workaround* is to add redo log (size or number) but its really a heads-up about your I/O subsystem not being up to keep up under stress. hth connor --- Fermin Bernaus Berraondo [EMAIL PROTECTED] wrote: I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: DENNIS WILLIAMS 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).
RE: Cannot allocate new log - checkpoint not complete
I dissagree, they will be bigger but there will be less of them. If the amount of processed data does not change, I do not think changing the size of the redolog files should affect the total amount fo bytes to be backeup up Thanks for the recommendation anyway ;) Cheers, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Stefan Jahnke Enviado el: jueves, 03 de abril de 2003 15:44 Para: Multiple recipients of list ORACLE-L Asunto: AW: Cannot allocate new log - checkpoint not complete Hi I would suggest to increase the redo log size. Doesn't effect you during daily operation, but prevents the database from hanging during nightly batches. No side effects I can think of (except for the fact that, of course, it will take you longer to backup the archived logs since the files are bigger, duh ;). Good luck Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Fermin Bernaus Berraondo [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. April 2003 10:04 An: Multiple recipients of list ORACLE-L Betreff: Cannot allocate new log - checkpoint not complete I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo 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: Stefan Jahnke 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: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network
RE: Cannot allocate new log - checkpoint not complete
Correct. The only potential disadvantage is that recovery will take longer when bringing up the database after a crash. Jay Miller -Original Message- Sent: Thursday, April 03, 2003 9:39 AM To: Multiple recipients of list ORACLE-L I dissagree, they will be bigger but there will be less of them. If the amount of processed data does not change, I do not think changing the size of the redolog files should affect the total amount fo bytes to be backeup up Thanks for the recommendation anyway ;) Cheers, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Stefan Jahnke Enviado el: jueves, 03 de abril de 2003 15:44 Para: Multiple recipients of list ORACLE-L Asunto: AW: Cannot allocate new log - checkpoint not complete Hi I would suggest to increase the redo log size. Doesn't effect you during daily operation, but prevents the database from hanging during nightly batches. No side effects I can think of (except for the fact that, of course, it will take you longer to backup the archived logs since the files are bigger, duh ;). Good luck Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Fermin Bernaus Berraondo [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. April 2003 10:04 An: Multiple recipients of list ORACLE-L Betreff: Cannot allocate new log - checkpoint not complete I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo 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: Stefan Jahnke 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
RE: Cannot allocate new log - checkpoint not complete
Not necessarily depending on how you've set log_checkpoint_... or the new parameters related to bounded recovery time. hth connor --- [EMAIL PROTECTED] wrote: Correct. The only potential disadvantage is that recovery will take longer when bringing up the database after a crash. Jay Miller -Original Message- Sent: Thursday, April 03, 2003 9:39 AM To: Multiple recipients of list ORACLE-L I dissagree, they will be bigger but there will be less of them. If the amount of processed data does not change, I do not think changing the size of the redolog files should affect the total amount fo bytes to be backeup up Thanks for the recommendation anyway ;) Cheers, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Stefan Jahnke Enviado el: jueves, 03 de abril de 2003 15:44 Para: Multiple recipients of list ORACLE-L Asunto: AW: Cannot allocate new log - checkpoint not complete Hi I would suggest to increase the redo log size. Doesn't effect you during daily operation, but prevents the database from hanging during nightly batches. No side effects I can think of (except for the fact that, of course, it will take you longer to backup the archived logs since the files are bigger, duh ;). Good luck Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Fermin Bernaus Berraondo [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. April 2003 10:04 An: Multiple recipients of list ORACLE-L Betreff: Cannot allocate new log - checkpoint not complete I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo 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: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing
RE: Cannot allocate new log - checkpoint not complete
There are some other effects I can think of. Up to you if these are important / significant to your users. If you make them bigger and you have a standby database then the standby might end up being further behind production (unless you have a script to workaround this) and also in this case you may then not get the files transferring successfully (depends on your network etc). Also, if you make them bigger and you have a loss of all redo logs (hopefully unlikely if they are mirrored on mirrored disks) then you will lose more data as it will be a longer period of time since the last archive log was created. And what about your archive log management scripts - do they keep x days worth of files (in which case the volume of archive log on disk will not change) or do they keep y files - in this case the volume of disks would increase unless the script(s) are altered. Regards, Bruce Reardon -Original Message- Sent: Friday, 4 April 2003 3:44 AM To: Multiple recipients of list ORACLE-L Correct. The only potential disadvantage is that recovery will take longer when bringing up the database after a crash. Jay Miller -Original Message- Sent: Thursday, April 03, 2003 9:39 AM To: Multiple recipients of list ORACLE-L I dissagree, they will be bigger but there will be less of them. If the amount of processed data does not change, I do not think changing the size of the redolog files should affect the total amount fo bytes to be backeup up Thanks for the recommendation anyway ;) Cheers, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Stefan Jahnke Enviado el: jueves, 03 de abril de 2003 15:44 Para: Multiple recipients of list ORACLE-L Asunto: AW: Cannot allocate new log - checkpoint not complete Hi I would suggest to increase the redo log size. Doesn't effect you during daily operation, but prevents the database from hanging during nightly batches. No side effects I can think of (except for the fact that, of course, it will take you longer to backup the archived logs since the files are bigger, duh ;). Good luck Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Fermin Bernaus Berraondo [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. April 2003 10:04 An: Multiple recipients of list ORACLE-L Betreff: Cannot allocate new log - checkpoint not complete I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web