- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- "Snapshot too old" issue
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Forums
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-12-2002 10:22 PM
тАО06-12-2002 10:22 PM
We are running a data wareshouse with Oracle8i. We always encounter snapshot too old issue when loading data to the database. We already have 20 rollback segments (other than the system rollback segment) and each with 10M initial and 10M next extents. Since the sql is embeded into the application and we are unable to tuning the application to make use of a single large rollback segment. Is it safe to ignore the snapshot too old messge?? Any suggestion to this issue??
Please help,
Many thanks,
Chris,
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-13-2002 12:16 AM
тАО06-13-2002 12:16 AM
Re: "Snapshot too old" issue
"Snapshot too old" Is it safe ? Safe until you need to rollback ;)
As you said the problem is when data is being loaded which i guess would have at the beginning. Try using larger initial rollback segment size and also do not use the 'Optimal' clause for the rollback segments.
Atleast the segment size won't shrink.
...BPK...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-13-2002 12:39 AM
тАО06-13-2002 12:39 AM
Re: "Snapshot too old" issue
You can create a large rollback segment, and before you do the load, offline all the other rollback segments. Hence, you will force the load to use the large rollback segment.
Rgds,
Kawah
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-13-2002 02:14 AM
тАО06-13-2002 02:14 AM
Re: "Snapshot too old" issue
It is save in terms of recover scenarios, as the "snapshot too old" message has to do with the read consitency function of the rollback segment. The Recover is managerd by the redologs.
What happens is that while you are performing a transaction against a table in your database, that existing rows of data that wil be changed are copied out into the rollback segments so that other user may read these data while your transaction runs.
When getting "snapshot too old" the rollback segment have been overwritten by another transaction.
What this means is that Oracle can't present a read consistent view of the data.
If this happen during a large load its should be safe to ignore by the terms of the load, but should be resolved to minimize users problems.
Increase the rolbacksegments MINEXTENTS, set larger extent sizes and a higher OPTIMAL value.
Increaseing MAXEXTENTS will not resolve the case.
Andreas
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-13-2002 08:35 PM
тАО06-13-2002 08:35 PM
SolutionIf you do not have any control over the commit statement, you might want to try reducing the size of your rollback segments. A good place to see if they are too small/large is the "AVEACTIVE" column of the V$ROLLSTAT view. You can more effectivly tune your rollback segment, rather than increasing it to a high number that will cause more problems. Otherwise, creating a single large rollback segment, and altering the rollback segments offline and online to fit the current need is also possible, although should not be used in a 24/7 enviroment.
(P.S. While a Snapshot too old error will only occur on a long running transaction, remember that most applications use cursors, and a cursor is considered to be a transaction until it is closed. Something to remember)
Thanks,
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-13-2002 09:35 PM
тАО06-13-2002 09:35 PM
Re: "Snapshot too old" issue
As the data blocks are read on behalf of the query, only blocks with lower
SCN than the query SCN will be read. If a block has uncommitted changes of
other transactions or changed data with more recent SCN, then the data is
reconstructed using the saved snapshot from the rollback segments. In some
rare situations, if RDBMS is not able to reconstruct the snapshot for a long
running query, the query results in ORA-1555 error.
I just went through two more causes for the snapshot old error.
Apart from the size of the rollback segments or the corrupted rollback segment,
Fetch across commit
This is the situation when a query opens a cursor, then loops through
fetching, changing, and committing the records on the same table
very often ORA-1555 can result. For ex.
A cursor was opened at SCN=10. The execution SCN of the query is then
marked as SCN=10. Every fetch by that cursor now need to get the
read-consistent data from SCN=10. The user program is now fetching
x numbers of records, changing them, and committing them. Let's say they
were committed with SCN=20. If a later fetch happens to retrieve a record
which is in one of the previously committed blocks, then the fetch will
see that the SCN there as 20. Since the fetch has to get the snapshot
from SCN=10 it will try to find it in the rollback segments. If it could
rollback sufficiently backwards as previously explained, then it could
reconstruct the snapshot from SCN=10. If not, then it will result in
ORA-1555 error.
Committing less often which will result in larger rollback segments will
REDUCE the probability of getting 'snapshot too old' error.
Fetch across commits with delayed block clean out
When a data or index block is modified in the database and the transaction
committed, oracle does a fast commit by marking the transaction as
committed in the rollback segment header but does not clean the datablocks
that were modified. The next transaction which does a select on the modified
blocks will do the actual cleanout of the block. This is known as a
delayed block cleanout.
But instead of assuming one table, lets assume that there are two tables in question.
i.e: the cursor is opened and then in a loop, it fetches from one table and
changes records in another, and commits.
Even though the records are getting committed in another table it could
still cause ORA-1555 because cleanout has not been done on the table
from which the records are being fetched.
For this case, a full table scan before opening and fetching through the
cursor will help.
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-14-2002 01:29 AM
тАО06-14-2002 01:29 AM
Re: "Snapshot too old" issue
Do you know how the data is being loaded - by sql-loader or a database package? And how large are the rollback segments? (select rssize from v$rollstat). If you are unable to modify the underlying SQL, you need to drop the existing segments (not the system one) and create a series of much larger ones, disk space permitting. The load transaction will then allocated to a larger rollback segement, hopefully avoiding the ORA-01555.
You must also retain multiple rollback segments to reduce the risk of multiple transactions being allocated to the same segment.
Hope this helps!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-14-2002 01:50 PM
тАО06-14-2002 01:50 PM
Re: "Snapshot too old" issue
No, no, no. Under no circumstances will this corrupt the database, or leave the database in an inconsistant state. All it means is that the transaction has to rollback due to the fact that the transaction cannot find data required to complete the transaction in the rollback segment.
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-15-2002 02:06 AM
тАО06-15-2002 02:06 AM
Re: "Snapshot too old" issue
Maybe due to your application design, etc. Your rollback segments are too small. You will need to use larger rollback segments.
Explanation:
There are numerous reasons why this can occur but basically for the operation that failed the information it needs to construct a read consistent view of the data is no longer available.
Diagnosis:
Is the user FETCHING across a COMMIT ?? If so this breaks the ANSI standards and is not guaranteed by Oracle. Make sure they know this then proceed onto describing the options below which may help.
The main way to avoid this is to use larger rollback segments across the system. This gives you a longer period of grace before 'old' rollback infomation gets overwritten. It is typically no use just enlarging a single rollback segment.
For long running reports, try to run these when the database is lightly loaded so that the chance of a rollback segment wrapping is reduced (as there are fewer transactions in progress and hence less rollback information being generated).
Try using an ORDER BY clause that forces a sort in the query - this should produce the row-source up front so undo is only needed while this ordered row-source is produced.
If this is on a SNAPSHOT update check whether the SNAPSHOT is performing a full or fast refresh. A full refresh could be occuring.
[information from Oracle Metalink]
Hope this helps
Best Regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-17-2002 04:09 AM
тАО06-17-2002 04:09 AM
Re: "Snapshot too old" issue
From Oracle:
"There are a few common cases where the ORA-01555 may appear:
1. Few number of rollback segments with potentially small sizes in a very active database (e.g., multiple users doing on-line transaction processing -- OLTP).
2. Corrupted rollback segment prevents a consistent read requested by a query from being able to read the block in the rollback segment.
3. Fetch across commits within an open cursor (cursors retain the "snapshot" of the query at cursor open time).
4. Fetch across commits with delayed block cleanout (the data block is not updated with the last committed image until the next reader accesses the data block, which requires verification from the transaction table of
the rollback segment)"
So certainly drop and re-create all your segments in case you have corruption.
If error is due to multiple transactions, increasing size and number of rollback segments should help. Also try to run the load at a time when little or no other activity is occuring.
However since it is the load itself which is generating the error it could perhaps be (3) or (4) above. Difficult to know without access to the code. For (4) Oracle recommend running full table scans (select count(*) in rule mode or with a "full" hint) of all involved tables before the start of the transaction.