Re: Snapshot too old in undo tablespace in 9i?
Daniel, I have just finished reading your document on UNdo Internals and Tims Cats, Dogs and ORA-1555s. Thanks for the documents they were both great. There is something I don't understand and I am not sure about it. You have said below: When a transaction is bound to an undo segment, it allocates a slot in the tx table. I thought that transactions were no longer bound to UNDO segments and this was one of the improvements in 9i. I have pasted an extract from Tims document: Into the future: Oracle9i UNDO tablespaces. As you may have observed, one of the reasons space management for rollback segments is so difficult is due to the fact that a transaction is assigned irrevocably to a single rollback segment. Each rollback segment can only handle a finite number of transactions (due to block-level contention for the transaction table in the header block), so there must be multiple rollback segments to handle potentially large numbers of transactions. UNDO tablespaces in Oracle9i allow an entire tablespace to become a single, large pool of undo blocks for use by any and all transactions. Instead of having available space carved up into many smaller rollback segments, a single transaction can utilize all of the space in the UNDO tablespace, if necessary. Many, many transactions can share that space also, because the controlling transaction table is no longer contained in a single database block, avoiding contention for this important resource. I guess I am jumping to the wrong assumption in Tims extract - can you clarify it for me. thanks, stephen Phone: 01737 27 5564 [EMAIL PROTECTED] Daniel Fink [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] un.com cc: Sent by: Subject: Re: Snapshot too old in undo tablespace in 9i? [EMAIL PROTECTED] ity.com 05/08/03 22:29 Please respond to ORACLE-L Abraham, Setting the retention time may not solve the problem. One of the ways that an ORA-1555 can be triggered is when the transaction table slot is overwritten. This is caused by having many small, serial transactions in the database while the export is running. In each undo segment (or rollback segment), there is a structure called the transaction(tx) table. This contains transaction - undo segment binding/status information. The number of slots is block-size dependent. I don't recall the exact numbers. When a transaction is bound to an undo segment, it allocates a slot in the tx table. This provides the links between the data/index block and the undo entries. If the data block points to a slot that has been reused, there is not a way to reconstruct the data, so it throws a 1555. The undo information may be preserved in the segments, but the link necessary has been lost. For illustration purposes, let's say you have 10 undo segments and each of them has a transaction table containing 40 slots. You have 400 slots available. If you have 100 transactions per minute (no more than 10 concurrently (so as to prevent new undo segments being created)), a slot will be reused every 4 minutes. If I have not sufficiently bored you, more detail can be found at www.optimaldba.com/library.html. Look for the documents on Undo Internals
Re: Snapshot too old in undo tablespace in 9i?
Stephen, Tim's statement is correct, but can be construed incorrectly if you read it and think of TEMP segments. AUM still uses undo segments (same basic structure as rollback segments). However, one of the space management steps is to allow an undo segment to 'steal' extents from another undo segment. This means that all extents (other than those currently in use or extent 0 (and perhaps 1)) are available to any other segment should it require them. A single transaction cannot start in undo segment #1, allocate space in it and then move to undo segment #2. However, the transaction can cause undo segment #1 to allocate space currently allocated to #2. I hope this clears up the disparity between the statements. Daniel [EMAIL PROTECTED] wrote: Daniel, I have just finished reading your document on UNdo Internals and Tims Cats, Dogs and ORA-1555s. Thanks for the documents they were both great. There is something I don't understand and I am not sure about it. You have said below: When a transaction is bound to an undo segment, it allocates a slot in the tx table. I thought that transactions were no longer bound to UNDO segments and this was one of the improvements in 9i. I have pasted an extract from Tims document: Into the future: Oracle9i UNDO tablespaces. As you may have observed, one of the reasons space management for rollback segments is so difficult is due to the fact that a transaction is assigned irrevocably to a single rollback segment. Each rollback segment can only handle a finite number of transactions (due to block-level contention for the transaction table in the header block), so there must be multiple rollback segments to handle potentially large numbers of transactions. UNDO tablespaces in Oracle9i allow an entire tablespace to become a single, large pool of undo blocks for use by any and all transactions. Instead of having available space carved up into many smaller rollback segments, a single transaction can utilize all of the space in the UNDO tablespace, if necessary. Many, many transactions can share that space also, because the controlling transaction table is no longer contained in a single database block, avoiding contention for this important resource. I guess I am jumping to the wrong assumption in Tims extract - can you clarify it for me. thanks, stephen Phone: 01737 27 5564 [EMAIL PROTECTED]begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
Re: Snapshot too old in undo tablespace in 9i?
Daniel, that make sense , thanks. Stephen Hodgkinson Oracle DBA Total Gas Power Ltd Phone: 01737 27 5564 [EMAIL PROTECTED] Daniel Fink [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] un.com cc: Sent by: Subject: Re: Snapshot too old in undo tablespace in 9i? [EMAIL PROTECTED] ity.com 15/08/03 15:26 Please respond to ORACLE-L Stephen, Tim's statement is correct, but can be construed incorrectly if you read it and think of TEMP segments. AUM still uses undo segments (same basic structure as rollback segments). However, one of the space management steps is to allow an undo segment to 'steal' extents from another undo segment. This means that all extents (other than those currently in use or extent 0 (and perhaps 1)) are available to any other segment should it require them. A single transaction cannot start in undo segment #1, allocate space in it and then move to undo segment #2. However, the transaction can cause undo segment #1 to allocate space currently allocated to #2. I hope this clears up the disparity between the statements. Daniel [EMAIL PROTECTED] wrote: Daniel, I have just finished reading your document on UNdo Internals and Tims Cats, Dogs and ORA-1555s. Thanks for the documents they were both great. There is something I don't understand and I am not sure about it. You have said below: When a transaction is bound to an undo segment, it allocates a slot in the tx table. I thought that transactions were no longer bound to UNDO segments and this was one of the improvements in 9i. I have pasted an extract from Tims document: Into the future: Oracle9i UNDO tablespaces. As you may have observed, one of the reasons space management for rollback segments is so difficult is due to the fact that a transaction is assigned irrevocably to a single rollback segment. Each rollback segment can only handle a finite number of transactions (due to block-level contention for the transaction table in the header block), so there must be multiple rollback segments to handle potentially large numbers of transactions. UNDO tablespaces in Oracle9i allow an entire tablespace to become a single, large pool of undo blocks for use by any and all transactions. Instead of having available space carved up into many smaller rollback segments, a single transaction can utilize all of the space in the UNDO tablespace, if necessary. Many, many transactions can share that space also, because the controlling transaction table is no longer contained in a single database block, avoiding contention for this important resource. I guess I am jumping to the wrong assumption in Tims extract - can you clarify it for me. thanks, stephen Phone: 01737 27 5564 [EMAIL PROTECTED] ** This insert confirms that this email message and all associated attachments have been swept by TotalFinaElf using Network Associates VirusScan for the presence of computer viruses during the receipt of this message. ** (See attached file: daniel.fink.vcf
Re: Snapshot too old in undo tablespace in 9i?
Abraham, Setting the retention time may not solve the problem. One of the ways that an ORA-1555 can be triggered is when the transaction table slot is overwritten. This is caused by having many small, serial transactions in the database while the export is running. In each undo segment (or rollback segment), there is a structure called the transaction(tx) table. This contains transaction - undo segment binding/status information. The number of slots is block-size dependent. I don't recall the exact numbers. When a transaction is bound to an undo segment, it allocates a slot in the tx table. This provides the links between the data/index block and the undo entries. If the data block points to a slot that has been reused, there is not a way to reconstruct the data, so it throws a 1555. The undo information may be preserved in the segments, but the link necessary has been lost. For illustration purposes, let's say you have 10 undo segments and each of them has a transaction table containing 40 slots. You have 400 slots available. If you have 100 transactions per minute (no more than 10 concurrently (so as to prevent new undo segments being created)), a slot will be reused every 4 minutes. If I have not sufficiently bored you, more detail can be found at www.optimaldba.com/library.html. Look for the documents on Undo Internals and Automatic Undo Internals. Tim Gorman also has a great paper called Cats, Dogs and ORA-1555s on his site (www.evdbt.com). Daniel Guerra, Abraham J wrote: Thanks. Abraham -Original Message- Sent: Tuesday, August 05, 2003 12:35 PM To: Multiple recipients of list ORACLE-L retention time --- set it for a couple of hours longer than you think the export will take. Guerra, Abraham J AGUERRA To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @amfam.com cc: Sent by: Subject: Snapshot too old in undo tablespace in 9i? ml-errors 08/05/2003 01:24 PM Please respond to ORACLE-L Hello Group, I just upgraded a database to Oracle 9.2.0.3 from 8.1.7. I created an undo tablespace with 10 (default) undo segments... however, during an export I got the following message: ORA-01555: snapshot too old: rollback segment number 15 with name _SYSSMU15$ too small I thought this was a thing of the past... According to the documentation, if an undo segment gets full, it starts using idle ones... also, the undo tablespace still had a lot of room to grow... Any insights will be welcome. Thanks Abraham Guerra Oracle DBA American Family Insurance Attachment Removed : Notebook.jpg -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: Guerra, Abraham J 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).begin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
Re: Snapshot too old in undo tablespace in 9i?
retention time --- set it for a couple of hours longer than you think the export will take. Guerra, Abraham J AGUERRA To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @amfam.com cc: Sent by: Subject: Snapshot too old in undo tablespace in 9i? ml-errors 08/05/2003 01:24 PM Please respond to ORACLE-L Hello Group, I just upgraded a database to Oracle 9.2.0.3 from 8.1.7. I created an undo tablespace with 10 (default) undo segments... however, during an export I got the following message: ORA-01555: snapshot too old: rollback segment number 15 with name _SYSSMU15$ too small I thought this was a thing of the past... According to the documentation, if an undo segment gets full, it starts using idle ones... also, the undo tablespace still had a lot of room to grow... Any insights will be welcome. Thanks Abraham Guerra Oracle DBA American Family Insurance Attachment Removed : Notebook.jpg -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: Snapshot too old in undo tablespace in 9i?
Thanks. Abraham -Original Message- Sent: Tuesday, August 05, 2003 12:35 PM To: Multiple recipients of list ORACLE-L retention time --- set it for a couple of hours longer than you think the export will take. Guerra, Abraham J AGUERRA To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @amfam.com cc: Sent by: Subject: Snapshot too old in undo tablespace in 9i? ml-errors 08/05/2003 01:24 PM Please respond to ORACLE-L Hello Group, I just upgraded a database to Oracle 9.2.0.3 from 8.1.7. I created an undo tablespace with 10 (default) undo segments... however, during an export I got the following message: ORA-01555: snapshot too old: rollback segment number 15 with name _SYSSMU15$ too small I thought this was a thing of the past... According to the documentation, if an undo segment gets full, it starts using idle ones... also, the undo tablespace still had a lot of room to grow... Any insights will be welcome. Thanks Abraham Guerra Oracle DBA American Family Insurance Attachment Removed : Notebook.jpg -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: Guerra, Abraham J 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: Re: Snapshot too old during stress test... how to avoid
With optimal, the rollback segment shrinks back to its optimal size as soon as the update, insert, or delete transaction is committed. However, there may be a long running query that is using that rollback segment for a consistent view of the database. If the rollback segment shrinks, the query cannot find the data that it needs. This results in a snapshot too old error. rgaffuri @cox.netTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: rootcc: Subject: Re: Re: Snapshot too old during stress test... how to avoid 06/05/2003 08:31 AM Please respond to ORACLE-L why would removing optimal setting help with snapshot too old? what I dont understand is that each of my DML transactions are independent of each other. They query and perform DML on different tables. None of them overlap. The only time they overlap is when they hit the data dictionary for some brief queries. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/06/05 Thu AM 03:25:36 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Snapshot too old during stress test... how to avoid Speaking of this trick with a txn in each RBS, I've got a shell script on my website (http://www.evdbt.com/tools.htm) that does just that. It is named prevent1555.sh which uses a stored procedure created by a SQL script named prevent1555_ddl.sql... As Jared mentioned, it is kind of a last resort, but it works... on 6/4/03 4:05 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: .. and if it still doesn't work, use the trick of putting a transaction in each of the rollback segments while the system is otherwise quiesced, and *do not* commit or rollback the transactions. This forces the rollbacks to extend if necessary, they will never wrap back to the first extent ( actually the second) as long as those transactions are not committed. It just uses a lot of disk space. Disk is cheap, right? :) Consider offlining all your production RBS and creating temporary ones that you can easily drop later. I've used it with SAP client copies, which will not run to completion without doing this, at least on our system. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/04/2003 09:45 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Snapshot too old during stress test... how to avoid Try removing optimal setting, and shrinking RBS to the min extents (or even below) before running your tests. - Kirti --- Garry Gillies [EMAIL PROTECTED] wrote: From memory (of a course attended looong ago), Oracle recommends one rollback segment for every three to four users. Four rollback segments between thirty six processes does seem a little mean. Garry [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/06/03 13:59 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Snapshot too old during stress test... how to avoid Im testing worst case scenarios right now. So Im doing batch updates,inserts,deletes and 'create table as' from a staging tablespace of approximately 5GBs to a master tablespace of approximately 11GBs. Ive got my job queue processes set to 36 and Im running 36 at a time in the background in order to gather statistics and timing under worst case scenarios. Im in 8.1.7.3 and I have
RE: Re: Snapshot too old during stress test... how to avoid
the thing is Im not doing any of that. Here is waht Im doing. update,inserts and deletes on tables select on data dictionary. Im also doing 'create table as' Transactions do NOT overlap. for example update,insert,and delete will not use the same tables in different transactions. and Im not updating the data dictionary. From: Ganesh Raja [EMAIL PROTECTED] Date: 2003/06/05 Thu AM 10:09:52 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Re: Snapshot too old during stress test... how to avoid Snapshot Too Old Maybe caused bcos of Delayed Block Cleanout Problems. Now this Bock was updated an Hour ago and u are now selecting it.. It finds that there are ITL Entries in there and oracle wants to see if the Transaction has been commited. He Goes back to the Transaction Table of the RBS Pointed by this ITL and he finds that there is No Rollback entry available.. Why is this ??? The Optimal Parameter Shrunk the RBS Down and with it all the Undo Information needed to do a Block Clean out. HTH Best Regards, Ganesh R DID : +65-6215-8413 HP : +65-9067-8474 === Live to learn... forget... and learn again. === -Original Message- [EMAIL PROTECTED] Sent: Thursday, June 05, 2003 8:32 PM To: Multiple recipients of list ORACLE-L why would removing optimal setting help with snapshot too old? what I dont understand is that each of my DML transactions are independent of each other. They query and perform DML on different tables. None of them overlap. The only time they overlap is when they hit the data dictionary for some brief queries. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/06/05 Thu AM 03:25:36 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Snapshot too old during stress test... how to avoid Speaking of this trick with a txn in each RBS, I've got a shell script on my website (http://www.evdbt.com/tools.htm) that does just that. It is named prevent1555.sh which uses a stored procedure created by a SQL script named prevent1555_ddl.sql... As Jared mentioned, it is kind of a last resort, but it works... on 6/4/03 4:05 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: .. and if it still doesn't work, use the trick of putting a transaction in each of the rollback segments while the system is otherwise quiesced, and *do not* commit or rollback the transactions. This forces the rollbacks to extend if necessary, they will never wrap back to the first extent ( actually the second) as long as those transactions are not committed. It just uses a lot of disk space. Disk is cheap, right? :) Consider offlining all your production RBS and creating temporary ones that you can easily drop later. I've used it with SAP client copies, which will not run to completion without doing this, at least on our system. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/04/2003 09:45 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Snapshot too old during stress test... how to avoid Try removing optimal setting, and shrinking RBS to the min extents (or even below) before running your tests. - Kirti --- Garry Gillies [EMAIL PROTECTED] wrote: From memory (of a course attended looong ago), Oracle recommends one rollback segment for every three to four users. Four rollback segments between thirty six processes does seem a little mean. Garry [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/06/03 13:59 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Snapshot too old during stress test... how to avoid Im testing worst case scenarios right now. So Im doing batch updates,inserts,deletes and 'create table as' from a staging tablespace of approximately 5GBs to a master tablespace of approximately 11GBs. Ive got my job queue processes set to 36 and Im running 36 at a time in the background in order to gather statistics and timing under worst case scenarios. Im in 8.1.7.3 and I have increased the size of my RBS tablespace to 11GB for this test. I have 4 standard RBS with optimal size set to 1GB. Why would I get a snapshot too old? I would think that 11GBs of rollback would be big enough. Would increasing the number of Rollback segments avoid this even though I have the same amount of space in the tablespace? In reality Im going to seriallize the process to avoid this and to improve
RE: Re: Snapshot too old during stress test... how to avoid
Snapshot Too Old Maybe caused bcos of Delayed Block Cleanout Problems. Now this Bock was updated an Hour ago and u are now selecting it.. It finds that there are ITL Entries in there and oracle wants to see if the Transaction has been commited. He Goes back to the Transaction Table of the RBS Pointed by this ITL and he finds that there is No Rollback entry available.. Why is this ??? The Optimal Parameter Shrunk the RBS Down and with it all the Undo Information needed to do a Block Clean out. HTH Best Regards, Ganesh R DID : +65-6215-8413 HP : +65-9067-8474 === Live to learn... forget... and learn again. === -Original Message- [EMAIL PROTECTED] Sent: Thursday, June 05, 2003 8:32 PM To: Multiple recipients of list ORACLE-L why would removing optimal setting help with snapshot too old? what I dont understand is that each of my DML transactions are independent of each other. They query and perform DML on different tables. None of them overlap. The only time they overlap is when they hit the data dictionary for some brief queries. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/06/05 Thu AM 03:25:36 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Snapshot too old during stress test... how to avoid Speaking of this trick with a txn in each RBS, I've got a shell script on my website (http://www.evdbt.com/tools.htm) that does just that. It is named prevent1555.sh which uses a stored procedure created by a SQL script named prevent1555_ddl.sql... As Jared mentioned, it is kind of a last resort, but it works... on 6/4/03 4:05 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: .. and if it still doesn't work, use the trick of putting a transaction in each of the rollback segments while the system is otherwise quiesced, and *do not* commit or rollback the transactions. This forces the rollbacks to extend if necessary, they will never wrap back to the first extent ( actually the second) as long as those transactions are not committed. It just uses a lot of disk space. Disk is cheap, right? :) Consider offlining all your production RBS and creating temporary ones that you can easily drop later. I've used it with SAP client copies, which will not run to completion without doing this, at least on our system. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/04/2003 09:45 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Snapshot too old during stress test... how to avoid Try removing optimal setting, and shrinking RBS to the min extents (or even below) before running your tests. - Kirti --- Garry Gillies [EMAIL PROTECTED] wrote: From memory (of a course attended looong ago), Oracle recommends one rollback segment for every three to four users. Four rollback segments between thirty six processes does seem a little mean. Garry [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/06/03 13:59 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Snapshot too old during stress test... how to avoid Im testing worst case scenarios right now. So Im doing batch updates,inserts,deletes and 'create table as' from a staging tablespace of approximately 5GBs to a master tablespace of approximately 11GBs. Ive got my job queue processes set to 36 and Im running 36 at a time in the background in order to gather statistics and timing under worst case scenarios. Im in 8.1.7.3 and I have increased the size of my RBS tablespace to 11GB for this test. I have 4 standard RBS with optimal size set to 1GB. Why would I get a snapshot too old? I would think that 11GBs of rollback would be big enough. Would increasing the number of Rollback segments avoid this even though I have the same amount of space in the tablespace? In reality Im going to seriallize the process to avoid this and to improve performance, however, I want to stress the system. any advice? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Re: Snapshot too old during stress test... how to avoid
Actually when are u getting this error.. There are a Lot Of reasons for ORA-01555 and primary one is DB Block Cleanout or Long Running quries... Best Regards, Ganesh R DID : +65-6215-8413 HP : +65-9067-8474 === Live to learn... forget... and learn again. === -Original Message- [EMAIL PROTECTED] Sent: Thursday, June 05, 2003 11:10 PM To: Multiple recipients of list ORACLE-L the thing is Im not doing any of that. Here is waht Im doing. update,inserts and deletes on tables select on data dictionary. Im also doing 'create table as' Transactions do NOT overlap. for example update,insert,and delete will not use the same tables in different transactions. and Im not updating the data dictionary. From: Ganesh Raja [EMAIL PROTECTED] Date: 2003/06/05 Thu AM 10:09:52 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Re: Snapshot too old during stress test... how to avoid Snapshot Too Old Maybe caused bcos of Delayed Block Cleanout Problems. Now this Bock was updated an Hour ago and u are now selecting it.. It finds that there are ITL Entries in there and oracle wants to see if the Transaction has been commited. He Goes back to the Transaction Table of the RBS Pointed by this ITL and he finds that there is No Rollback entry available.. Why is this ??? The Optimal Parameter Shrunk the RBS Down and with it all the Undo Information needed to do a Block Clean out. HTH Best Regards, Ganesh R DID : +65-6215-8413 HP : +65-9067-8474 === Live to learn... forget... and learn again. === -Original Message- [EMAIL PROTECTED] Sent: Thursday, June 05, 2003 8:32 PM To: Multiple recipients of list ORACLE-L why would removing optimal setting help with snapshot too old? what I dont understand is that each of my DML transactions are independent of each other. They query and perform DML on different tables. None of them overlap. The only time they overlap is when they hit the data dictionary for some brief queries. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/06/05 Thu AM 03:25:36 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Snapshot too old during stress test... how to avoid Speaking of this trick with a txn in each RBS, I've got a shell script on my website (http://www.evdbt.com/tools.htm) that does just that. It is named prevent1555.sh which uses a stored procedure created by a SQL script named prevent1555_ddl.sql... As Jared mentioned, it is kind of a last resort, but it works... on 6/4/03 4:05 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: .. and if it still doesn't work, use the trick of putting a transaction in each of the rollback segments while the system is otherwise quiesced, and *do not* commit or rollback the transactions. This forces the rollbacks to extend if necessary, they will never wrap back to the first extent ( actually the second) as long as those transactions are not committed. It just uses a lot of disk space. Disk is cheap, right? :) Consider offlining all your production RBS and creating temporary ones that you can easily drop later. I've used it with SAP client copies, which will not run to completion without doing this, at least on our system. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/04/2003 09:45 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Snapshot too old during stress test... how to avoid Try removing optimal setting, and shrinking RBS to the min extents (or even below) before running your tests. - Kirti --- Garry Gillies [EMAIL PROTECTED] wrote: From memory (of a course attended looong ago), Oracle recommends one rollback segment for every three to four users. Four rollback segments between thirty six processes does seem a little mean. Garry [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/06/03 13:59 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Snapshot too old during stress test... how to avoid Im testing worst case scenarios right now. So Im doing batch updates,inserts,deletes and 'create table as' from a staging tablespace of approximately 5GBs to a master tablespace of approximately 11GBs. Ive got my job queue processes set to 36 and Im running 36 at a time in the background in order to gather statistics and timing under worst case scenarios. Im in 8.1.7.3
Re: Snapshot too old during stress test... how to avoid
Are you hitting ORA-1555 because of a fetch across commit?
[EMAIL PROTECTED] wrote:
why would removing optimal setting help with snapshot too old?
what I dont understand is that each of my DML transactions are independent of each other. They query and perform DML on different tables. None of them overlap. The only time they overlap is when they hit the data dictionary for some brief queries.
From: Tim Gorman [EMAIL PROTECTED]
Date: 2003/06/05 Thu AM 03:25:36 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Speaking of this trick with a txn in each RBS, I've got a shell script on my
website (http://www.evdbt.com/tools.htm) that does just that. It is named
"prevent1555.sh" which uses a stored procedure created by a SQL script named
"prevent1555_ddl.sql"...
As Jared mentioned, it is kind of a last resort, but it works...
on 6/4/03 4:05 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:
.. and if it still doesn't work, use the trick of putting a transaction
in
each of the rollback segments while the system is otherwise quiesced,
and *do not* commit or rollback the transactions.
This forces the rollbacks to extend if necessary, they will never wrap
back to the first extent ( actually the second) as long as those
transactions
are not committed.
It just uses a lot of disk space. Disk is cheap, right? :)
Consider offlining all your production RBS and creating temporary ones
that you can easily drop later.
I've used it with SAP client copies, which will not run to completion
without
doing this, at least on our system.
Jared
Kirtikumar Deshpande [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
06/04/2003 09:45 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject: Re: Snapshot too old during stress test... how to avoid
Try removing optimal setting, and shrinking RBS to the min extents (or
even below) before running
your tests.
- Kirti
--- Garry Gillies [EMAIL PROTECTED] wrote:
From memory (of a course attended looong ago),
Oracle recommends one rollback segment for every
three to four users.
Four rollback segments between thirty six processes
does seem a little mean.
Garry
[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
04/06/03 13:59
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Snapshot too old during stress test... how to
avoid
Im testing worst case scenarios right now. So Im doing batch
updates,inserts,deletes and 'create table as' from a staging tablespace
of
approximately 5GBs to a master tablespace of approximately 11GBs.
Ive got my job queue processes set to 36 and Im running 36 at a time in
the background in order to gather statistics and timing under worst case
scenarios.
Im in 8.1.7.3 and I have increased the size of my RBS tablespace to 11GB
for this test. I have 4 standard RBS with optimal size set to 1GB. Why
would I get a snapshot too old? I would think that 11GBs of rollback
would
be big enough. Would increasing the number of Rollback segments avoid
this
even though I have the same amount of space in the tablespace?
In reality Im going to seriallize the process to avoid this and to
improve
performance, however, I want to stress the system.
any advice?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tim Gorman
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).
--
Daniel W. Fink
http://www.optimaldba.com
Re: Snapshot too old during stress test... how to avoid
The rbs does not shrink to optimal when the transaction commits. The 2nd transaction to 'find' that the rbs has extended beyond optimal will cause the rbs to shrink. -- Daniel W. Fink http://www.optimaldba.com Thomas Day wrote: With optimal, the rollback segment shrinks back to its optimal size as soon as the update, insert, or delete transaction is committed. However, there may be a long running query that is using that rollback segment for a consistent view of the database. If the rollback segment shrinks, the query cannot find the data that it needs. This results in a snapshot too old error. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink 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: Re: Snapshot too old during stress test... how to avoid
Not as soon as the Update is Over.. Only after a Certain period of Time. This operation is done By SMON. HTH Best Regards, Ganesh R DID : +65-6215-8413 HP : +65-9067-8474 === Live to learn... forget... and learn again. === -Original Message- Sent: Thursday, June 05, 2003 10:05 PM To: Multiple recipients of list ORACLE-L With optimal, the rollback segment shrinks back to its optimal size as soon as the update, insert, or delete transaction is committed. However, there may be a long running query that is using that rollback segment for a consistent view of the database. If the rollback segment shrinks, the query cannot find the data that it needs. This results in a snapshot too old error. rgaffuri @cox.netTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: rootcc: Subject: Re: Re: Snapshot too old during stress test... how to avoid 06/05/2003 08:31 AM Please respond to ORACLE-L why would removing optimal setting help with snapshot too old? what I dont understand is that each of my DML transactions are independent of each other. They query and perform DML on different tables. None of them overlap. The only time they overlap is when they hit the data dictionary for some brief queries. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/06/05 Thu AM 03:25:36 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Snapshot too old during stress test... how to avoid Speaking of this trick with a txn in each RBS, I've got a shell script on my website (http://www.evdbt.com/tools.htm) that does just that. It is named prevent1555.sh which uses a stored procedure created by a SQL script named prevent1555_ddl.sql... As Jared mentioned, it is kind of a last resort, but it works... on 6/4/03 4:05 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: .. and if it still doesn't work, use the trick of putting a transaction in each of the rollback segments while the system is otherwise quiesced, and *do not* commit or rollback the transactions. This forces the rollbacks to extend if necessary, they will never wrap back to the first extent ( actually the second) as long as those transactions are not committed. It just uses a lot of disk space. Disk is cheap, right? :) Consider offlining all your production RBS and creating temporary ones that you can easily drop later. I've used it with SAP client copies, which will not run to completion without doing this, at least on our system. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/04/2003 09:45 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Snapshot too old during stress test... how to avoid Try removing optimal setting, and shrinking RBS to the min extents (or even below) before running your tests. - Kirti --- Garry Gillies [EMAIL PROTECTED] wrote: From memory (of a course attended looong ago), Oracle recommends one rollback segment for every three to four users. Four rollback segments between thirty six processes does seem a little mean. Garry [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/06/03 13:59 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Snapshot too old during stress test... how to avoid Im testing worst case scenarios right now. So Im doing batch updates,inserts,deletes and 'create table as' from a staging tablespace of approximately 5GBs to a master tablespace of approximately 11GBs. Ive got my job queue processes set to 36 and Im running 36 at a time in the background in order to gather statistics and timing under worst case scenarios. Im in 8.1.7.3 and I have increased the size of my RBS tablespace to 11GB for this test. I have 4 standard RBS with optimal size set to 1GB. Why would I get a snapshot too old? I would think that 11GBs of rollback would be big enough. Would increasing the number of Rollback segments avoid this even though I have the same amount of space in the tablespace? In reality Im going to seriallize the process to avoid this and to improve performance, however, I want to stress the system. any advice? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar
Re: Snapshot too old during stress test... how to avoid
Is the error only occurring when you do the create table as select? Are any other processes modifying data in the source table? What is the db_block_size? [EMAIL PROTECTED] wrote: nope. My process is rather simple. A flag comes in... either we do a create table as, drop the old table, and create the indexes based on the data in the stage tablespace or, we do insert select Commit; update commit; delete commit; The snapshot too old is occurring during the process of create table as, all the data is getting created in the new table, then Im getting a snapshot too old. I didnt have error handling there to know EXACTLY when its happening, but right after I do the create table as, I have a query of the data dictionary, then I drop the old table. The old table is not being dropped and the new table is being created. Im at a loss. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink 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: Re: Snapshot too old during stress test... how to avoid
It could be that your own process is causing the error by committing between DML operations. Take a look at metalink note 40689.1. My hazy understanding of which is as follows: - When you change a block in the buffer cache, a pointer to the rollback entry is placed in the block header. When the transaction is committed, the rollback entry is freed, but the buffer is not updated to reflect this. When the block is revisited, the process must find the rollback entry to determine whether the transaction has been committed or not. If the entry has been overwritten, then you get snapshot too old. The answer is not to commit between the insert select, update and delete, or alternatively, to force the block cleanout by doing a full scan of the table (for instance by doing a select count(*) from my_table) after each commit. Regards David Lord -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 05 June 2003 18:40 To: Multiple recipients of list ORACLE-L Subject: Re: Re: Snapshot too old during stress test... how to avoid nope. My process is rather simple. A flag comes in... either we do a create table as, drop the old table, and create the indexes based on the data in the stage tablespace or, we do insert select Commit; update commit; delete commit; The snapshot too old is occuring during the process of create table as, all the data is getting created in the new table, then Im getting a snapshot too old. I didnt have error handling there to know EXACTLY when its happening, but right after I do the create table as, I have a query of the data dictionary, then I drop the old table. The old table is not being dropped and the new table is being created. Im at a loss. From: Daniel W. Fink [EMAIL PROTECTED] Date: 2003/06/05 Thu AM 10:55:05 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Snapshot too old during stress test... how to avoid Are you hitting ORA-1555 because of a fetch across commit? [EMAIL PROTECTED] wrote: why would removing optimal setting help with snapshot too old? what I dont understand is that each of my DML transactions are independent of each other. They query and perform DML on different tables. None of them overlap. The only time they overlap is when they hit the data dictionary for some brief queries. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/06/05 Thu AM 03:25:36 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Snapshot too old during stress test... how to avoid Speaking of this trick with a txn in each RBS, I've got a shell script on my website (http://www.evdbt.com/tools.htm) that does just that. It is named prevent1555.sh which uses a stored procedure created by a SQL script named prevent1555_ddl.sql... As Jared mentioned, it is kind of a last resort, but it works... on 6/4/03 4:05 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: .. and if it still doesn't work, use the trick of putting a transaction in each of the rollback segments while the system is otherwise quiesced, and *do not* commit or rollback the transactions. This forces the rollbacks to extend if necessary, they will never wrap back to the first extent ( actually the second) as long as those transactions are not committed. It just uses a lot of disk space. Disk is cheap, right? :) Consider offlining all your production RBS and creating temporary ones that you can easily drop later. I've used it with SAP client copies, which will not run to completion without doing this, at least on our system. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/04/2003 09:45 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Snapshot too old during stress test... how to avoid Try removing optimal setting, and shrinking RBS to the min extents (or even below) before running your tests. - Kirti --- Garry Gillies [EMAIL PROTECTED] wrote: From memory (of a course attended looong ago), Oracle recommends one rollback segment for every three to four users. Four rollback segments between thirty six processes does seem a little mean. Garry [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/06/03 13:59 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Snapshot too old during stress test... how to avoid Im testing worst case scenarios right now. So Im doing batch updates,inserts,deletes and 'create table as' from a staging tablespace
Re: Re: Snapshot too old during stress test... how to avoid
why would removing optimal setting help with snapshot too old? what I dont understand is that each of my DML transactions are independent of each other. They query and perform DML on different tables. None of them overlap. The only time they overlap is when they hit the data dictionary for some brief queries. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/06/05 Thu AM 03:25:36 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Snapshot too old during stress test... how to avoid Speaking of this trick with a txn in each RBS, I've got a shell script on my website (http://www.evdbt.com/tools.htm) that does just that. It is named prevent1555.sh which uses a stored procedure created by a SQL script named prevent1555_ddl.sql... As Jared mentioned, it is kind of a last resort, but it works... on 6/4/03 4:05 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: .. and if it still doesn't work, use the trick of putting a transaction in each of the rollback segments while the system is otherwise quiesced, and *do not* commit or rollback the transactions. This forces the rollbacks to extend if necessary, they will never wrap back to the first extent ( actually the second) as long as those transactions are not committed. It just uses a lot of disk space. Disk is cheap, right? :) Consider offlining all your production RBS and creating temporary ones that you can easily drop later. I've used it with SAP client copies, which will not run to completion without doing this, at least on our system. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/04/2003 09:45 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Snapshot too old during stress test... how to avoid Try removing optimal setting, and shrinking RBS to the min extents (or even below) before running your tests. - Kirti --- Garry Gillies [EMAIL PROTECTED] wrote: From memory (of a course attended looong ago), Oracle recommends one rollback segment for every three to four users. Four rollback segments between thirty six processes does seem a little mean. Garry [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/06/03 13:59 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Snapshot too old during stress test... how to avoid Im testing worst case scenarios right now. So Im doing batch updates,inserts,deletes and 'create table as' from a staging tablespace of approximately 5GBs to a master tablespace of approximately 11GBs. Ive got my job queue processes set to 36 and Im running 36 at a time in the background in order to gather statistics and timing under worst case scenarios. Im in 8.1.7.3 and I have increased the size of my RBS tablespace to 11GB for this test. I have 4 standard RBS with optimal size set to 1GB. Why would I get a snapshot too old? I would think that 11GBs of rollback would be big enough. Would increasing the number of Rollback segments avoid this even though I have the same amount of space in the tablespace? In reality Im going to seriallize the process to avoid this and to improve performance, however, I want to stress the system. any advice? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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: [EMAIL PROTECTED] 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
Re: Snapshot too old during stress test... how to avoid
From memory (of a course attended looong ago), Oracle recommends one rollback segment for every three to four users. Four rollback segments between thirty six processes does seem a little mean. Garry [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/06/03 13:59 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Snapshot too old during stress test... how to avoid Im testing worst case scenarios right now. So Im doing batch updates,inserts,deletes and 'create table as' from a staging tablespace of approximately 5GBs to a master tablespace of approximately 11GBs. Ive got my job queue processes set to 36 and Im running 36 at a time in the background in order to gather statistics and timing under worst case scenarios. Im in 8.1.7.3 and I have increased the size of my RBS tablespace to 11GB for this test. I have 4 standard RBS with optimal size set to 1GB. Why would I get a snapshot too old? I would think that 11GBs of rollback would be big enough. Would increasing the number of Rollback segments avoid this even though I have the same amount of space in the tablespace? In reality Im going to seriallize the process to avoid this and to improve performance, however, I want to stress the system. any advice? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Garry Gillies 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: Snapshot too old during stress test... how to avoid
Try removing optimal setting, and shrinking RBS to the min extents (or even below) before running your tests. - Kirti --- Garry Gillies [EMAIL PROTECTED] wrote: From memory (of a course attended looong ago), Oracle recommends one rollback segment for every three to four users. Four rollback segments between thirty six processes does seem a little mean. Garry [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/06/03 13:59 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Snapshot too old during stress test... how to avoid Im testing worst case scenarios right now. So Im doing batch updates,inserts,deletes and 'create table as' from a staging tablespace of approximately 5GBs to a master tablespace of approximately 11GBs. Ive got my job queue processes set to 36 and Im running 36 at a time in the background in order to gather statistics and timing under worst case scenarios. Im in 8.1.7.3 and I have increased the size of my RBS tablespace to 11GB for this test. I have 4 standard RBS with optimal size set to 1GB. Why would I get a snapshot too old? I would think that 11GBs of rollback would be big enough. Would increasing the number of Rollback segments avoid this even though I have the same amount of space in the tablespace? In reality Im going to seriallize the process to avoid this and to improve performance, however, I want to stress the system. any advice? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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: Snapshot too old during stress test... how to avoid
... and if it still doesn't work, use the trick of putting a transaction in each of the rollback segments while the system is otherwise quiesced, and *do not* commit or rollback the transactions. This forces the rollbacks to extend if necessary, they will never wrap back to the first extent ( actually the second) as long as those transactions are not committed. It just uses a lot of disk space. Disk is cheap, right? :) Consider offlining all your production RBS and creating temporary ones that you can easily drop later. I've used it with SAP client copies, which will not run to completion without doing this, at least on our system. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/04/2003 09:45 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Snapshot too old during stress test... how to avoid Try removing optimal setting, and shrinking RBS to the min extents (or even below) before running your tests. - Kirti --- Garry Gillies [EMAIL PROTECTED] wrote: From memory (of a course attended looong ago), Oracle recommends one rollback segment for every three to four users. Four rollback segments between thirty six processes does seem a little mean. Garry [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/06/03 13:59 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Snapshot too old during stress test... how to avoid Im testing worst case scenarios right now. So Im doing batch updates,inserts,deletes and 'create table as' from a staging tablespace of approximately 5GBs to a master tablespace of approximately 11GBs. Ive got my job queue processes set to 36 and Im running 36 at a time in the background in order to gather statistics and timing under worst case scenarios. Im in 8.1.7.3 and I have increased the size of my RBS tablespace to 11GB for this test. I have 4 standard RBS with optimal size set to 1GB. Why would I get a snapshot too old? I would think that 11GBs of rollback would be big enough. Would increasing the number of Rollback segments avoid this even though I have the same amount of space in the tablespace? In reality Im going to seriallize the process to avoid this and to improve performance, however, I want to stress the system. any advice? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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: 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: Snapshot too old during stress test... how to avoid
Speaking of this trick with a txn in each RBS, I've got a shell script on my website (http://www.evdbt.com/tools.htm) that does just that. It is named prevent1555.sh which uses a stored procedure created by a SQL script named prevent1555_ddl.sql... As Jared mentioned, it is kind of a last resort, but it works... on 6/4/03 4:05 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: .. and if it still doesn't work, use the trick of putting a transaction in each of the rollback segments while the system is otherwise quiesced, and *do not* commit or rollback the transactions. This forces the rollbacks to extend if necessary, they will never wrap back to the first extent ( actually the second) as long as those transactions are not committed. It just uses a lot of disk space. Disk is cheap, right? :) Consider offlining all your production RBS and creating temporary ones that you can easily drop later. I've used it with SAP client copies, which will not run to completion without doing this, at least on our system. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/04/2003 09:45 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Snapshot too old during stress test... how to avoid Try removing optimal setting, and shrinking RBS to the min extents (or even below) before running your tests. - Kirti --- Garry Gillies [EMAIL PROTECTED] wrote: From memory (of a course attended looong ago), Oracle recommends one rollback segment for every three to four users. Four rollback segments between thirty six processes does seem a little mean. Garry [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/06/03 13:59 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Snapshot too old during stress test... how to avoid Im testing worst case scenarios right now. So Im doing batch updates,inserts,deletes and 'create table as' from a staging tablespace of approximately 5GBs to a master tablespace of approximately 11GBs. Ive got my job queue processes set to 36 and Im running 36 at a time in the background in order to gather statistics and timing under worst case scenarios. Im in 8.1.7.3 and I have increased the size of my RBS tablespace to 11GB for this test. I have 4 standard RBS with optimal size set to 1GB. Why would I get a snapshot too old? I would think that 11GBs of rollback would be big enough. Would increasing the number of Rollback segments avoid this even though I have the same amount of space in the tablespace? In reality Im going to seriallize the process to avoid this and to improve performance, however, I want to stress the system. any advice? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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: snapshot too old
2. Fix: Increase Rll Back segments 1. Why: Oracle RDBMS tries to provide "Read consistent " viiew of the data ( changed or clean ) at the "statement level" read consitency and "transaction level" read consistency This isone of the whys- an Oracle DBA's makeshis living in their employment as Oracle DBA. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Friday, March 28, 2003 12:19 PMTo: Multiple recipients of list ORACLE-LSubject: snapshot too old why a select statement can give snapshot too old erorr . there is no update , yes but its a long running query . what is fix for this ? -ak
RE: snapshot too old
AK Take a look at this. It explains it better than I could. http://home.clara.net/dwotton/dba/snapshot2.htm http://home.clara.net/dwotton/dba/snapshot2.htm Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 28, 2003 12:19 PM To: Multiple recipients of list ORACLE-L why a select statement can give snapshot too old erorr . there is no update , yes but its a long running query . what is fix for this ? -ak -- 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: snapshot too old
AK... this happens with LONG queries too... this means that you do not have enough rollback segments... or... that they are very small... HTH JL --- AK [EMAIL PROTECTED] wrote: why a select statement can give snapshot too old erorr . there is no update , yes but its a long running query . what is fix for this ? -ak __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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: Snapshot Too Old Error on Export !!!
Jackson, As you have discovered , the issue is that other transactions are overwriting your read consistent view of the tables. Options could include 1) Running the export at a quite time (sounds like you have tried that but with only partial suucess) 2) Taking a direct export which reduces the time very considerably (especially if you do it at weekend) 3) Take subsets of the data using either a parameter file with export with a list of tables you are interested in and multiple exports (different parameter files of course) 4) Using the query option of export to again take subsets of data from the biggest tables Using options 3+ 4 means you need to put in some sort of means of capturing changes ( a IUD trigger to capture rowid's) but it will be very difficult to ensure integrity. Your last sentence is very worrying, I hope you are not using export as a means of backing up the database because from what you have said that is totally unreliable. Out of the above options only 1) will give you any sort of consistent view of the tables. John -Original Message- Sent: 21 February 2003 10:00 To: Multiple recipients of list ORACLE-L Hi all I have a problem when doing an export in one of ourt production databases. The export fails with ORA-01555, snapshot too old error. I have increased the number of rollback segments and their sizes on the database. Also I have went to an extent of specifying the parameter constent=n on my script but backups fails. The worst part is this export runs for a long time and then fails, more than 24 hours. The only time that this export succeed is over the weekend, because most of the time few people are working or not at all. Now I have tried to start it after hours but as I said it still takes long and end up failing the next day. Could somebody help me here, this is very critical to be running production without proper backups .! Thanx ___ http://www.webmail.co.za the South-African free email service NetWiseGurus.Com Portal - Your Own Internet Business Today! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jackson Dumas 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: [EMAIL PROTECTED] 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: Snapshot Too Old Error on Export !!!
You might try running hot backups at the OS level instead. It sounds like your export is just taking too long. To get it to work with active users on board you might have to increase your rollback segments to truly ridiculous levels especially if you have large batches running while the export is running. To really understand the issue go to http://www.ixora.com.au and look at Steve Adams' material on snapshot too old. Allan -Original Message- Sent: Friday, February 21, 2003 4:00 AM To: Multiple recipients of list ORACLE-L Hi all I have a problem when doing an export in one of ourt production databases. The export fails with ORA-01555, snapshot too old error. I have increased the number of rollback segments and their sizes on the database. Also I have went to an extent of specifying the parameter constent=n on my script but backups fails. The worst part is this export runs for a long time and then fails, more than 24 hours. The only time that this export succeed is over the weekend, because most of the time few people are working or not at all. Now I have tried to start it after hours but as I said it still takes long and end up failing the next day. Could somebody help me here, this is very critical to be running production without proper backups .! Thanx ___ http://www.webmail.co.za the South-African free email service NetWiseGurus.Com Portal - Your Own Internet Business Today! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jackson Dumas 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). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan 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: Snapshot Too Old Error on Export !!!
I would echo a previous post that you can't backup a database with the
export utility. I suspect you get your error because you are using
consistent=y in the export. The database is trying to give you data as of
the time you started the export. The fact that you are getting the snapshot
too old message is evidence that the data is changing while you are
exporting -- to the extent that the database is eventually unable to keep
doing it -- but these data changes will not be in the export file.
A genuine backup using rman or the old alter tablespace begin backup method
is the only proper backup.
If you are trying to get production data to move into a test/development
database schema, then export is certainly the most convenient way of doing
it. If you are, in fact, trying to backup the database, here is something
to get you started.
If the filesystem to which your export file is going is big enough, consider
using that filesystem to store a database backup instead of an export.
Assuming it is /where/it/goes, try the following:
Make sure sys (or system, if you prefer) has been granted sysdba in the
database.
Create two files with text similar to the following:
for file named backup_database.rcv:
-- snip -
run {
allocate channel ch1 type disk format '/where/it/goes/%U_DATA';
set command id to 'rman';
backup
tag backup_db_full
(database include current controlfile);
release channel ch1;
}
- snip --
This will backup the database.
for file named backup_arch.rcv
- snip
run {
allocate channel ch1 type disk format '/where/it/goes/%U_ARCH';
set command id to 'rman';
change archivelog all crosscheck;
backup
(archivelog all delete input);
backup ***This line and the next if you are duplexing archived logs***
(archivelog like '/directory/where/duplexed/archivelogs/are/%' delete
input);
release channel ch1;
allocate channel ch1 type disk format '/where/it/goes/%U_CONTROL';
backup current controlfile tag='backup';
release channel ch1;
sql ALTER DATABASE BACKUP CONTROLFILE TO
''/where/it/goes/CONTROL_FILE.BAK'' REUSE;
}
snip
This will backup the archived logs and the control file. Note that the last
command tells the database to make a physical copy of the control file. The
reason for this is that rman has been writing backup info to the control
while the backup is running. So you make a copy of it after the backup has
completed in case you lose all copies of your control files. If your
database and all control files got completely blown away, you can copy the
control file copy back to where it was and start restoring. You might note
that I backup the control file ... and back it up ... and back it up.
That's just paranoia. You can put the whole thing into one file. The
reason for having them separate is in case you need to free up space in the
archive_log_dest by backing up just the archived logs.
To run a backup, type in the following commands:
rman nocatalog
connect target sys/qwerty@DBNAME
@backup_database.rcv
@backup_arch.rcv
exit
Now, make sure you backup /where/it/goes directory to tape with whatever
operating system backup utility you are using.
One thing that can be added, if you want to be extra thorough, is to put in
a log switch followed by an archive log current, after you run the
archivelog backup. Then you run ANOTHER archivelog backup. In the world of
Murphy's Law, you do it this way because your archive_log_dest will, some
day, at the worst possible time, be unable to accommodate a log switch and
archive log current. So you clean it out first prior to the log switch.
It might be useful to know how to restore the database ... that's just
something I saw written on a toilet stall wall. It seems reasonable. (...
He who reads these words of wit, eats those little balls of ... ) I think
the subject is probably more extensive than can be covered in a simple
e-mail; so I won't try to cover it all. But, in it's simplest form, a
recovery looks like:
If the last rman backup has been deleted from /where/it/goes, restore those
files from tape.
startup mount the database (assuming the control file is NOT the thing you
are restoring)
rman nocatalog
connect target sys/qwerty@DBNAME
allocate channel ch1 type disk;
restore database;
restore archivelog all;
recover database;
release channel ch1;
open the database.
See, rman isn't so bad. It's biggest problem is that, if you start to like
it, then it can lead to other things such as liking vi, growing a beard, and
wearing suspenders.
(Obviously, I have a rare day here where I don't have a lot to do. Hence,
the verbose reply.)
-Original Message-
Could somebody help me here, this is very critical to be running
production without proper backups .!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephen Lee
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051
Re: Snapshot Too Old Error on Export !!!
In addition to the other comments about export not being a backup, let me add a few things. Managing the trade off between many rollback segments (good for oltp performance) and large rollback segments (necessary to avoid 1555) is often a tough one. As somebody else said, you can optimize export performance by doing direct path, but besides that, the time it takes to export each individual table (that would be the entire export if consistent=yes) must be smaller than the time it takes for any rollback segment to wrap (note, this is not the WRAP column of v$rollstat, rather you should compare the growth of the WRITES column with the RSSSIZE column). If need an idea on a running system about how long the longest query can be without running into 1555, the attached can be used. It basically looks at v$rollstat twice with 10 seconds between them, and estimates how many hours it will take for the most rapidly used rollback segment to wrap. On a less busy system, you may want to modify the 10 seconds to something larger. BTW, at a ct. I was working on, we had to go to 100 segments (due to OLTP requirements) of 1GB each (due to requirements to allow 4-5 hour queries to run without 1555 risk). Yes, this is 100Gb of rollback:-) /Bjørn. Jackson Dumas wrote: Hi all I have a problem when doing an export in one of ourt production databases. The export fails with ORA-01555, snapshot too old error. I have increased the number of rollback segments and their sizes on the database. Also I have went to an extent of specifying the parameter constent=n on my script but backups fails. The worst part is this export runs for a long time and then fails, more than 24 hours. The only time that this export succeed is over the weekend, because most of the time few people are working or not at all. Now I have tried to start it after hours but as I said it still takes long and end up failing the next day. Could somebody help me here, this is very critical to be running production without proper backups .! Thanx ___ http://www.webmail.co.za the South-African free email service NetWiseGurus.Com Portal - Your Own Internet Business Today! -- Bjørn Engsig, Miracle A/S Member of Oak Table Network [EMAIL PROTECTED] - http://MiracleAS.dk rem before running this, do rem SQL create table temprollstat as select * from v$rollstat where 1=42 rem variable varchar2(100); insert into temprollstat select * from v$rollstat / exec : := to_char(sysdate,'DD-MON- HH24:MI:SS'); prompt hang on, waiting 10s to inspect undo bytes written... host sleep 10 select l.usn, t.writes - l.writes byteswritten, to_char((sysdate-to_date(:,'DD-MON- HH24:MI:SS'))*24*t.rssize/( t.writes - l.wr ites ), '99.9') hoursbeforewrap from temprollstat l, v$rollstat t where l.usn = t.usn and t.writes != l.writes order by byteswritten / rollback /
RE: Snapshot Too Old Error on Export !!!
Stephen,
My understanding is having consistent=y uses no rollback, since the changes
occurring
during the export are not being captured in the export dump.
on few occasions i've still got the spurious ora-1555(snap shot too
old)error on exporting a 80GB highly
transactional database which terminates the export.
am i thinking wrong?? help!!
Sunil Nookala
DBA
3-4502
907-9255(pager)
-Original Message-
Sent: Friday, February 21, 2003 10:35 AM
To: Multiple recipients of list ORACLE-L
I would echo a previous post that you can't backup a database with the
export utility. I suspect you get your error because you are using
consistent=y in the export. The database is trying to give you data as of
the time you started the export. The fact that you are getting the snapshot
too old message is evidence that the data is changing while you are
exporting -- to the extent that the database is eventually unable to keep
doing it -- but these data changes will not be in the export file.
A genuine backup using rman or the old alter tablespace begin backup method
is the only proper backup.
If you are trying to get production data to move into a test/development
database schema, then export is certainly the most convenient way of doing
it. If you are, in fact, trying to backup the database, here is something
to get you started.
If the filesystem to which your export file is going is big enough, consider
using that filesystem to store a database backup instead of an export.
Assuming it is /where/it/goes, try the following:
Make sure sys (or system, if you prefer) has been granted sysdba in the
database.
Create two files with text similar to the following:
for file named backup_database.rcv:
-- snip -
run {
allocate channel ch1 type disk format '/where/it/goes/%U_DATA';
set command id to 'rman';
backup
tag backup_db_full
(database include current controlfile);
release channel ch1;
}
- snip --
This will backup the database.
for file named backup_arch.rcv
- snip
run {
allocate channel ch1 type disk format '/where/it/goes/%U_ARCH';
set command id to 'rman';
change archivelog all crosscheck;
backup
(archivelog all delete input);
backup ***This line and the next if you are duplexing archived logs***
(archivelog like '/directory/where/duplexed/archivelogs/are/%' delete
input);
release channel ch1;
allocate channel ch1 type disk format '/where/it/goes/%U_CONTROL';
backup current controlfile tag='backup';
release channel ch1;
sql ALTER DATABASE BACKUP CONTROLFILE TO
''/where/it/goes/CONTROL_FILE.BAK'' REUSE;
}
snip
This will backup the archived logs and the control file. Note that the last
command tells the database to make a physical copy of the control file. The
reason for this is that rman has been writing backup info to the control
while the backup is running. So you make a copy of it after the backup has
completed in case you lose all copies of your control files. If your
database and all control files got completely blown away, you can copy the
control file copy back to where it was and start restoring. You might note
that I backup the control file ... and back it up ... and back it up.
That's just paranoia. You can put the whole thing into one file. The
reason for having them separate is in case you need to free up space in the
archive_log_dest by backing up just the archived logs.
To run a backup, type in the following commands:
rman nocatalog
connect target sys/[EMAIL PROTECTED]
backup_database.rcv
backup_arch.rcv
exit
Now, make sure you backup /where/it/goes directory to tape with whatever
operating system backup utility you are using.
One thing that can be added, if you want to be extra thorough, is to put in
a log switch followed by an archive log current, after you run the
archivelog backup. Then you run ANOTHER archivelog backup. In the world of
Murphy's Law, you do it this way because your archive_log_dest will, some
day, at the worst possible time, be unable to accommodate a log switch and
archive log current. So you clean it out first prior to the log switch.
It might be useful to know how to restore the database ... that's just
something I saw written on a toilet stall wall. It seems reasonable. (...
He who reads these words of wit, eats those little balls of ... ) I think
the subject is probably more extensive than can be covered in a simple
e-mail; so I won't try to cover it all. But, in it's simplest form, a
recovery looks like:
If the last rman backup has been deleted from /where/it/goes, restore those
files from tape.
startup mount the database (assuming the control file is NOT the thing you
are restoring)
rman nocatalog
connect target sys/[EMAIL PROTECTED]
allocate channel ch1 type disk;
restore database;
restore archivelog all;
recover database;
release channel ch1;
open the database.
See, rman isn't so bad. It's biggest problem is that, if you
RE: Snapshot Too Old Error on Export !!!
I'm certainly not an Oracle internals expert. My understanding of ANY consistent set of data is provided by the rollback segments. It goes something like this: A select is run The database notes the SCN where the select started Consistent data is given to the query by pulling data from the table(s) and -rolling back- the data to the SCN where your query started. So Oracle has to hang on to all this rollback info until 1. Your query finishes 2. The rollback storage blows up. -Original Message- My understanding is having consistent=y uses no rollback, since the changes occurring during the export are not being captured in the export dump. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: Snapshot Too Old Error on Export !!!
Your understanding of consistent=y is not correct. With consistent=yes,
export needs to be able to get all the rollback generated since the start
of the export until its complete, hence consistent=y typically has prohibitively
high rollback requirements. Please see my other mail for some more info.
/Bjrn.
[EMAIL PROTECTED] wrote:
Stephen,
My understanding is having consistent=y uses no rollback, since the changes
occurring
during the export are not being captured in the export dump.
on few occasions i've still got the spurious ora-1555(snap shot too
old)error on exporting a 80GB highly
transactional database which terminates the export.
am i thinking wrong?? help!!
Sunil Nookala
DBA
3-4502
907-9255(pager)
-Original Message-
Sent: Friday, February 21, 2003 10:35 AM
To: Multiple recipients of list ORACLE-L
I would echo a previous post that you can't backup a database with the
export utility. I suspect you get your error because you are using
consistent=y in the export. The database is trying to give you data as of
the time you started the export. The fact that you are getting the snapshot
too old message is evidence that the data is changing while you are
exporting -- to the extent that the database is eventually unable to keep
doing it -- but these data changes will not be in the export file.
A genuine backup using rman or the old alter tablespace begin backup method
is the only "proper backup".
If you are trying to get production data to move into a test/development
database schema, then export is certainly the most convenient way of doing
it. If you are, in fact, trying to backup the database, here is something
to get you started.
If the filesystem to which your export file is going is big enough, consider
using that filesystem to store a database backup instead of an export.
Assuming it is /where/it/goes, try the following:
Make sure sys (or system, if you prefer) has been granted sysdba in the
database.
Create two files with text similar to the following:
for file named backup_database.rcv:
-- snip -
run {
allocate channel ch1 type disk format '/where/it/goes/%U_DATA';
set command id to 'rman';
backup
tag backup_db_full
(database include current controlfile);
release channel ch1;
}
- snip --
This will backup the database.
for file named backup_arch.rcv
- snip
run {
allocate channel ch1 type disk format '/where/it/goes/%U_ARCH';
set command id to 'rman';
change archivelog all crosscheck;
backup
(archivelog all delete input);
backup ***This line and the next if you are duplexing archived logs***
(archivelog like '/directory/where/duplexed/archivelogs/are/%' delete
input);
release channel ch1;
allocate channel ch1 type disk format '/where/it/goes/%U_CONTROL';
backup current controlfile tag='backup';
release channel ch1;
sql "ALTER DATABASE BACKUP CONTROLFILE TO
''/where/it/goes/CONTROL_FILE.BAK'' REUSE";
}
snip
This will backup the archived logs and the control file. Note that the last
command tells the database to make a physical copy of the control file. The
reason for this is that rman has been writing backup info to the control
while the backup is running. So you make a copy of it after the backup has
completed in case you lose all copies of your control files. If your
database and all control files got completely blown away, you can copy the
control file copy back to where it was and start restoring. You might note
that I backup the control file ... and back it up ... and back it up.
That's just paranoia. You can put the whole thing into one file. The
reason for having them separate is in case you need to free up space in the
archive_log_dest by backing up just the archived logs.
To run a backup, type in the following commands:
rman nocatalog
connect target sys/[EMAIL PROTECTED]
backup_database.rcv
backup_arch.rcv
exit
Now, make sure you backup /where/it/goes directory to tape with whatever
operating system backup utility you are using.
One thing that can be added, if you want to be extra thorough, is to put in
a log switch followed by an archive log current, after you run the
archivelog backup. Then you run ANOTHER archivelog backup. In the world of
Murphy's Law, you do it this way because your archive_log_dest will, some
day, at the worst possible time, be unable to accommodate a log switch and
archive log current. So you clean it out first prior to the log switch.
It might be useful to know how to restore the database ... that's just
something I saw written on a toilet stall wall. It seems reasonable. ("...
He who reads these words of wit, eats those little balls of ... ") I think
the subject is probably more extensive than can be covered in a simple
e-mail; so I won't try to cover it all. But, in it's simplest form, a
recovery looks like:
If the last rman backup has been deleted from /where/it/goes, restore those
files from
RE: Snapshot Too Old Error on Export !!!
It would have to be using rollback segments. That's where it would read the
consistent view of data blocks from that had been changed since the start of the
export.
[EMAIL PROTECTED] 02/21/03 01:14PM
Stephen,
My understanding is having consistent=y uses no rollback, since the changes
occurring
during the export are not being captured in the export dump.
on few occasions i've still got the spurious ora-1555(snap shot too
old)error on exporting a 80GB highly
transactional database which terminates the export.
am i thinking wrong?? help!!
Sunil Nookala
DBA
3-4502
907-9255(pager)
-Original Message-
Sent: Friday, February 21, 2003 10:35 AM
To: Multiple recipients of list ORACLE-L
I would echo a previous post that you can't backup a database with the
export utility. I suspect you get your error because you are using
consistent=y in the export. The database is trying to give you data as of
the time you started the export. The fact that you are getting the snapshot
too old message is evidence that the data is changing while you are
exporting -- to the extent that the database is eventually unable to keep
doing it -- but these data changes will not be in the export file.
A genuine backup using rman or the old alter tablespace begin backup method
is the only proper backup.
If you are trying to get production data to move into a test/development
database schema, then export is certainly the most convenient way of doing
it. If you are, in fact, trying to backup the database, here is something
to get you started.
If the filesystem to which your export file is going is big enough, consider
using that filesystem to store a database backup instead of an export.
Assuming it is /where/it/goes, try the following:
Make sure sys (or system, if you prefer) has been granted sysdba in the
database.
Create two files with text similar to the following:
for file named backup_database.rcv:
-- snip -
run {
allocate channel ch1 type disk format '/where/it/goes/%U_DATA';
set command id to 'rman';
backup
tag backup_db_full
(database include current controlfile);
release channel ch1;
}
- snip --
This will backup the database.
for file named backup_arch.rcv
- snip
run {
allocate channel ch1 type disk format '/where/it/goes/%U_ARCH';
set command id to 'rman';
change archivelog all crosscheck;
backup
(archivelog all delete input);
backup ***This line and the next if you are duplexing archived logs***
(archivelog like '/directory/where/duplexed/archivelogs/are/%' delete
input);
release channel ch1;
allocate channel ch1 type disk format '/where/it/goes/%U_CONTROL';
backup current controlfile tag='backup';
release channel ch1;
sql ALTER DATABASE BACKUP CONTROLFILE TO
''/where/it/goes/CONTROL_FILE.BAK'' REUSE;
}
snip
This will backup the archived logs and the control file. Note that the last
command tells the database to make a physical copy of the control file. The
reason for this is that rman has been writing backup info to the control
while the backup is running. So you make a copy of it after the backup has
completed in case you lose all copies of your control files. If your
database and all control files got completely blown away, you can copy the
control file copy back to where it was and start restoring. You might note
that I backup the control file ... and back it up ... and back it up.
That's just paranoia. You can put the whole thing into one file. The
reason for having them separate is in case you need to free up space in the
archive_log_dest by backing up just the archived logs.
To run a backup, type in the following commands:
rman nocatalog
connect target sys/[EMAIL PROTECTED]
backup_database.rcv
backup_arch.rcv
exit
Now, make sure you backup /where/it/goes directory to tape with whatever
operating system backup utility you are using.
One thing that can be added, if you want to be extra thorough, is to put in
a log switch followed by an archive log current, after you run the
archivelog backup. Then you run ANOTHER archivelog backup. In the world of
Murphy's Law, you do it this way because your archive_log_dest will, some
day, at the worst possible time, be unable to accommodate a log switch and
archive log current. So you clean it out first prior to the log switch.
It might be useful to know how to restore the database ... that's just
something I saw written on a toilet stall wall. It seems reasonable. (...
He who reads these words of wit, eats those little balls of ... ) I think
the subject is probably more extensive than can be covered in a simple
e-mail; so I won't try to cover it all. But, in it's simplest form, a
recovery looks like:
If the last rman backup has been deleted from /where/it/goes, restore those
files from tape.
startup mount the database (assuming the control file is NOT the thing you
are restoring)
rman nocatalog
connect target sys/[EMAIL
RE: Snapshot too old
Patrick, A code around I've used is to periodically close and reopen the cursor. Logic is as follows define v_loop_size number := 5000; v_prev_unique_id number := 0; v_hold_unique_id number := 0; main_cur (c_start_unique_id in number) is select ... from ... where unique_id = c_start_unique_id ... order by unique_id; begin loop for main_rec in main_cur (v_prev_unique_id + 1) loop v_prev_unique_id := main_rec.unique_id; some processing if main_cur%rowcount = v_loop_size then exit; end if; end loop; --end for loop -- -- hold the last unique id processed so we know when to exit this loop if v_hold_unique_id = v_prev_unique_id then exit; else v_hold_unique_id := v_prev_unique_id; end if; end loop; end; Todd Arave Senior Programmer/Analyst [EMAIL PROTECTED] 01/07/03 03:54PM Dear, No, the different jobs use different tables. As I already mentioned I did find in 5 programs something like this : Cursor c1 is select * from x where id = xx; For c1rec in c1 loop Blabla Blabla Update table x set id = NULL where id = xx; Commit; End loop; This is for me a clear case of fetch across commit. The syntax is not completely correct off course, but the programs are already corrected in the mean time and they never crash again. Still in 2 jobs , I can not put my finger on it. I do not want to spit in millions of lines of code. If some command,tool,trace,event whatever can make my life easier, let me know. Rgds, Patrick -Original Message- WILLIAMS Sent: dinsdag 7 januari 2003 22:10 To: Multiple recipients of list ORACLE-L Patrick - Do any of these jobs update the same tables? Or do any jobs read a table that other jobs are updating? Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, January 07, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Dear, Since a few weeks I am tuning a big conversion batch written in PL/SQL (millions of lines of code split over 7 batches) When the job is running, certain batches stop with ORA-1555 : Snapshot too old. Other batches run well till the end. Bizarre is that not always the same job stops. When I do a trace I see nothing. With a normal trace I am pretty sure that I will never see it. Rollback segments are rarely used. So making the rollbacks bigger or smaller is not the solution. They also tried to change the commit rate. That was not the solution. When I modified the optimal size to NULL value to avoid shrinking and cached 3 heavily used sequences some runs went all the way but since a week it stops again with the same annoying error. After that I put an event in the init.ora file : event = 1555 trace name processstate forever, level 10 A trace file was generated but I could not find the error in the trace file. I am pretty sure that Oracle just dumps all open cursors in a file. Since there are 100 of cursors opened I do not have a clue which one is provoking the error. I already looked at the batches and I have identified in 5 of them a fetch across commit. Still they have the error. But in the 2 remaining I can not find this.(surely the 2 biggest ones, nice !) So my question is : How can I know where in the code the error is generated ? Must I change the definition of the event ? (I know there are other options but I can not find them right away) Should I use DBMS_PROFILER ? (it generates massive files !) Must they write exceptions everywhere in their code ? Can somebody help me ? Please do not send me an explanation of the snapshot too old error. I wake up with it and I go asleep with it. Patrick -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Patrick Van der Sande 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
RE: Snapshot too old
Patrick - Do any of these jobs update the same tables? Or do any jobs read a table that other jobs are updating? Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, January 07, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Dear, Since a few weeks I am tuning a big conversion batch written in PL/SQL (millions of lines of code split over 7 batches) When the job is running, certain batches stop with ORA-1555 : Snapshot too old. Other batches run well till the end. Bizarre is that not always the same job stops. When I do a trace I see nothing. With a normal trace I am pretty sure that I will never see it. Rollback segments are rarely used. So making the rollbacks bigger or smaller is not the solution. They also tried to change the commit rate. That was not the solution. When I modified the optimal size to NULL value to avoid shrinking and cached 3 heavily used sequences some runs went all the way but since a week it stops again with the same annoying error. After that I put an event in the init.ora file : event = 1555 trace name processstate forever, level 10 A trace file was generated but I could not find the error in the trace file. I am pretty sure that Oracle just dumps all open cursors in a file. Since there are 100 of cursors opened I do not have a clue which one is provoking the error. I already looked at the batches and I have identified in 5 of them a fetch across commit. Still they have the error. But in the 2 remaining I can not find this.(surely the 2 biggest ones, nice !) So my question is : How can I know where in the code the error is generated ? Must I change the definition of the event ? (I know there are other options but I can not find them right away) Should I use DBMS_PROFILER ? (it generates massive files !) Must they write exceptions everywhere in their code ? Can somebody help me ? Please do not send me an explanation of the snapshot too old error. I wake up with it and I go asleep with it. Patrick -- 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: Snapshot too old
Dear, No, the different jobs use different tables. As I already mentioned I did find in 5 programs something like this : Cursor c1 is select * from x where id = xx; For c1rec in c1 loop Blabla Blabla Update table x set id = NULL where id = xx; Commit; End loop; This is for me a clear case of fetch across commit. The syntax is not completely correct off course, but the programs are already corrected in the mean time and they never crash again. Still in 2 jobs , I can not put my finger on it. I do not want to spit in millions of lines of code. If some command,tool,trace,event whatever can make my life easier, let me know. Rgds, Patrick -Original Message- WILLIAMS Sent: dinsdag 7 januari 2003 22:10 To: Multiple recipients of list ORACLE-L Patrick - Do any of these jobs update the same tables? Or do any jobs read a table that other jobs are updating? Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, January 07, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Dear, Since a few weeks I am tuning a big conversion batch written in PL/SQL (millions of lines of code split over 7 batches) When the job is running, certain batches stop with ORA-1555 : Snapshot too old. Other batches run well till the end. Bizarre is that not always the same job stops. When I do a trace I see nothing. With a normal trace I am pretty sure that I will never see it. Rollback segments are rarely used. So making the rollbacks bigger or smaller is not the solution. They also tried to change the commit rate. That was not the solution. When I modified the optimal size to NULL value to avoid shrinking and cached 3 heavily used sequences some runs went all the way but since a week it stops again with the same annoying error. After that I put an event in the init.ora file : event = 1555 trace name processstate forever, level 10 A trace file was generated but I could not find the error in the trace file. I am pretty sure that Oracle just dumps all open cursors in a file. Since there are 100 of cursors opened I do not have a clue which one is provoking the error. I already looked at the batches and I have identified in 5 of them a fetch across commit. Still they have the error. But in the 2 remaining I can not find this.(surely the 2 biggest ones, nice !) So my question is : How can I know where in the code the error is generated ? Must I change the definition of the event ? (I know there are other options but I can not find them right away) Should I use DBMS_PROFILER ? (it generates massive files !) Must they write exceptions everywhere in their code ? Can somebody help me ? Please do not send me an explanation of the snapshot too old error. I wake up with it and I go asleep with it. Patrick -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Patrick Van der Sande 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: Snapshot too old
Patrick - Just an idea for you, given that the jobs don't share tables. Sounds as if you may have fetch across commit problem like Dick mentioned. The best solution would be to fix the programs. A stopgap method in the meantime would be to assign each job to its own rollback segment so the blocks wouldn't be aged out quite so unpredictably. Nah, make 'em fix the program. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, January 07, 2003 4:55 PM To: Multiple recipients of list ORACLE-L Dear, No, the different jobs use different tables. As I already mentioned I did find in 5 programs something like this : Cursor c1 is select * from x where id = xx; For c1rec in c1 loop Blabla Blabla Update table x set id = NULL where id = xx; Commit; End loop; This is for me a clear case of fetch across commit. The syntax is not completely correct off course, but the programs are already corrected in the mean time and they never crash again. Still in 2 jobs , I can not put my finger on it. I do not want to spit in millions of lines of code. If some command,tool,trace,event whatever can make my life easier, let me know. Rgds, Patrick -Original Message- WILLIAMS Sent: dinsdag 7 januari 2003 22:10 To: Multiple recipients of list ORACLE-L Patrick - Do any of these jobs update the same tables? Or do any jobs read a table that other jobs are updating? Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, January 07, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Dear, Since a few weeks I am tuning a big conversion batch written in PL/SQL (millions of lines of code split over 7 batches) When the job is running, certain batches stop with ORA-1555 : Snapshot too old. Other batches run well till the end. Bizarre is that not always the same job stops. When I do a trace I see nothing. With a normal trace I am pretty sure that I will never see it. Rollback segments are rarely used. So making the rollbacks bigger or smaller is not the solution. They also tried to change the commit rate. That was not the solution. When I modified the optimal size to NULL value to avoid shrinking and cached 3 heavily used sequences some runs went all the way but since a week it stops again with the same annoying error. After that I put an event in the init.ora file : event = 1555 trace name processstate forever, level 10 A trace file was generated but I could not find the error in the trace file. I am pretty sure that Oracle just dumps all open cursors in a file. Since there are 100 of cursors opened I do not have a clue which one is provoking the error. I already looked at the batches and I have identified in 5 of them a fetch across commit. Still they have the error. But in the 2 remaining I can not find this.(surely the 2 biggest ones, nice !) So my question is : How can I know where in the code the error is generated ? Must I change the definition of the event ? (I know there are other options but I can not find them right away) Should I use DBMS_PROFILER ? (it generates massive files !) Must they write exceptions everywhere in their code ? Can somebody help me ? Please do not send me an explanation of the snapshot too old error. I wake up with it and I go asleep with it. Patrick -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Patrick Van der Sande 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
RE: snapshot too old error - strange
no , i do not . Thanks DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Tue, May 28, 2002 4:53 AM To: Multiple recipients of list ORACLE-L Hello Andrey, Do you use autonomous transaction? Monday, May 27, 2002, 7:53:19 PM, you wrote: AB Dear list ! AB There is something strange going on in my production DB. AB There is a program that reads fom 2 huge tables (A and B - select only) and AB writes a fraction of records into some third table (let's call it C - AB inserts only). AB Now , NO ONE carries a DML agains A or B . AB But occasionally i get the Ora-1555 - snapshot too old error during the AB run of the aforementioned program. AB Any ideas , please ? AB TIA AB DBAndrey AB * 03-9254520 AB * 058-548133 AB * mailto:[EMAIL PROTECTED] -- Best regards, Sergeymailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergey V Dolgov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: snapshot too old error - strange
How about DML before (ie before your query starts)? If there are massive amount of blocks to be cleaned out, this can lead to ora-1555 hth connor --- Andrey Bronfin [EMAIL PROTECTED] wrote: I meant , no one runs a DML against those tables during the running of the program. DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Tue, May 28, 2002 1:13 AM To: Multiple recipients of list ORACLE-L 2 huge tables A and B Now , NO ONE carries a DML agains A or B. If no one ever does DML against those two tables, how did they end up having so many rows? Do you run large batch update or insert operations against these tables from time to time? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 http://www.oracledba.co.uk http://www.oaktable.net Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: snapshot too old error - strange
On Mon, 27 May 2002, Nirmal Kumar Muthu Kumaran wrote: Increase rollback segment size for this transaction and make sure that the transaction will use the huge rollback segment I don't think that solution is correct. This is a common misconception about snapshot too old. Assigning your long-running select to a giant rollback segment will not help solve the problem. Here a short article on this misconception: http://www.speakeasy.org/~jwilton/oracle/snapshot-too-old.html I suspect the original poster is encountering snapshot too old as a result of block cleanouts. See the following article for more information: http://home.clara.net/dwotton/dba/snapshot2.htm -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -Original Message- From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] There is something strange going on in my production DB. There is a program that reads fom 2 huge tables (A and B - select only) and writes a fraction of records into some third table (let's call it C - inserts only). Now , NO ONE carries a DML agains A or B . But occasionally i get the Ora-1555 - snapshot too old error during the run of the aforementioned program. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: snapshot too old error - strange
I meant , no one runs a DML against those tables during the running of the program. Right. If there is no DML while your program is running, then the snapshot error is due to DML that was run earlier. The solution is simple. In the programs that do large batch inserts or updates on these tables, simply put an ANALYZE TABLE statement at the end. Since the tables have undergone significant change, this is good practice because it will update statistics for the CBO. Also, it will visit the changed blocks in the table and perform block cleanout, and that will solve your snapshot error problem. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: snapshot too old error - strange
Title: RE: snapshot too old error - strange Increase rollback segment size for this transaction and make sure that the transaction will use the huge rollback segment hth nirmal -Original Message- From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] Sent: Mon, May 27, 2002 15:53 To: Multiple recipients of list ORACLE-L Subject: snapshot too old error - strange Dear list ! There is something strange going on in my production DB. There is a program that reads fom 2 huge tables (A and B - select only) and writes a fraction of records into some third table (let's call it C - inserts only). Now , NO ONE carries a DML agains A or B . But occasionally i get the Ora-1555 - snapshot too old error during the run of the aforementioned program. Any ideas , please ? TIA DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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: snapshot too old error - strange
Hi Andrey, Are there any LOB columns in the tables ? I read something on Metalink about bugs on ORA-01555 messages (followed by a ORA-22924), on tables with LOBs ... HTH, Remco -Oorspronkelijk bericht- Van: Andrey Bronfin [mailto:[EMAIL PROTECTED]] Verzonden: maandag 27 mei 2002 14:53 Aan: Multiple recipients of list ORACLE-L Onderwerp: snapshot too old error - strange Dear list ! There is something strange going on in my production DB. There is a program that reads fom 2 huge tables (A and B - select only) and writes a fraction of records into some third table (let's call it C - inserts only). Now , NO ONE carries a DML agains A or B . But occasionally i get the Ora-1555 - snapshot too old error during the run of the aforementioned program. Any ideas , please ? TIA DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: snapshot too old error - strange
Check size of your rollback segs Vs the job of selecting from a big table to insert into another big table -might have to increase size of segs. -Original Message- Sent: Monday, May 27, 2002 5:03 PM To: Multiple recipients of list ORACLE-L Hi Andrey, Are there any LOB columns in the tables ? I read something on Metalink about bugs on ORA-01555 messages (followed by a ORA-22924), on tables with LOBs ... HTH, Remco -Oorspronkelijk bericht- Van: Andrey Bronfin [mailto:[EMAIL PROTECTED]] Verzonden: maandag 27 mei 2002 14:53 Aan: Multiple recipients of list ORACLE-L Onderwerp: snapshot too old error - strange Dear list ! There is something strange going on in my production DB. There is a program that reads fom 2 huge tables (A and B - select only) and writes a fraction of records into some third table (let's call it C - inserts only). Now , NO ONE carries a DML agains A or B . But occasionally i get the Ora-1555 - snapshot too old error during the run of the aforementioned program. Any ideas , please ? TIA DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Simon Waibale INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: snapshot too old error - strange
no , no lobs. thanks ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Mon, May 27, 2002 4:03 PM To: Multiple recipients of list ORACLE-L Hi Andrey, Are there any LOB columns in the tables ? I read something on Metalink about bugs on ORA-01555 messages (followed by a ORA-22924), on tables with LOBs ... HTH, Remco -Oorspronkelijk bericht- Van: Andrey Bronfin [mailto:[EMAIL PROTECTED]] Verzonden: maandag 27 mei 2002 14:53 Aan: Multiple recipients of list ORACLE-L Onderwerp: snapshot too old error - strange Dear list ! There is something strange going on in my production DB. There is a program that reads fom 2 huge tables (A and B - select only) and writes a fraction of records into some third table (let's call it C - inserts only). Now , NO ONE carries a DML agains A or B . But occasionally i get the Ora-1555 - snapshot too old error during the run of the aforementioned program. Any ideas , please ? TIA DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Daemen, Remco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: snapshot too old error - strange
2 huge tables A and B Now , NO ONE carries a DML agains A or B. If no one ever does DML against those two tables, how did they end up having so many rows? Do you run large batch update or insert operations against these tables from time to time? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: snapshot too old error - strange
Greg Moore wrote: 2 huge tables A and B Now , NO ONE carries a DML agains A or B. If no one ever does DML against those two tables, how did they end up having so many rows? Do you run large batch update or insert operations against these tables from time to time? One additional thought : no trigger referring to A and/or B on the updated table ? -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: snapshot too old
Seema Singh wrote: Hi At time of export i am getting following error EXP-8: ORACLE error 1555 encountered ORA-01555: snapshot too old: rollback segment number 6 with name R03 too small Please suggest Thanks -Seema Mladen, any idea ? -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: snapshot too old
Stephane... You're mean... :-) -Original Message- Sent: Thursday, January 17, 2002 6:25 AM To: Multiple recipients of list ORACLE-L Seema Singh wrote: Hi At time of export i am getting following error EXP-8: ORACLE error 1555 encountered ORA-01555: snapshot too old: rollback segment number 6 with name R03 too small Please suggest Thanks -Seema Mladen, any idea ? -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: snapshot too old
Oh, no! Not Gogala M Laden! -Original Message- Stephane... You're mean... :-) -Original Message- Sent: Thursday, January 17, 2002 6:25 AM To: Multiple recipients of list ORACLE-L Seema Singh wrote: Hi At time of export i am getting following error EXP-8: ORACLE error 1555 encountered ORA-01555: snapshot too old: rollback segment number 6 with name R03 too small Please suggest Thanks -Seema Mladen, any idea ? -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: snapshot too old
There are several ideas, but I'm working on my charming and likable personality. I must do something before the duhveleopers organize a lynch mob and burn me at stake. -Original Message- Sent: Thursday, January 17, 2002 11:11 AM To: Multiple recipients of list ORACLE-L Stephane... You're mean... :-) -Original Message- Sent: Thursday, January 17, 2002 6:25 AM To: Multiple recipients of list ORACLE-L Seema Singh wrote: Hi At time of export i am getting following error EXP-8: ORACLE error 1555 encountered ORA-01555: snapshot too old: rollback segment number 6 with name R03 too small Please suggest Thanks -Seema Mladen, any idea ? -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: snapshot too old
Don't be afraid. I'm not hungry today. -Original Message- Sent: Thursday, January 17, 2002 11:31 AM To: Multiple recipients of list ORACLE-L Oh, no! Not Gogala M Laden! -Original Message- Stephane... You're mean... :-) -Original Message- Sent: Thursday, January 17, 2002 6:25 AM To: Multiple recipients of list ORACLE-L Seema Singh wrote: Hi At time of export i am getting following error EXP-8: ORACLE error 1555 encountered ORA-01555: snapshot too old: rollback segment number 6 with name R03 too small Please suggest Thanks -Seema Mladen, any idea ? -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: snapshot too old
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 16, 2002 23:55 Hi At time of export i am getting following error EXP-8: ORACLE error 1555 encountered ORA-01555: snapshot too old: rollback segment number 6 with name R03 too small please refer to http://www.ixora.com.au/tips/admin/ora-1555.htm for a good explanation of the problem and the ways to avoid it hth, Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: snapshot too old
Seema, The ORA-1555 snapshot too old occurs when export tries to construct a read consistent view of your data from rollback segments, which has been overwritten or reused for new transactions. Other sessions are updating data while the export is running in consistent mode (using consistent = Y) To avoid the ORA-1555 - use consistent=N (default) - use larger rollback segments - increase the number of rollback segments - do exports during quieter periods ie. when not many users are manipulating data Regards $uhen Hi At time of export i am getting following error EXP-8: ORACLE error 1555 encountered ORA-01555: snapshot too old: rollback segment number 6 with name R03 too small Please suggest Thanks -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Suhen Pather INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
