Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to avoid database ORA-01555 error". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

1. Rollback segment data is overwritten

Reason:

When a DML operation is performed, the undo segment records the state of the data before it was changed (by constructing a consistent block of the original data). If the user hasn't committed yet, others who query this piece of data will see the state of the data before it changes. Because the data read by other users is the data in the original data block in the undo segment, the consistency of data read without commit is guaranteed.

Session A initiates a query on Table T at 9:50, and it takes 10 minutes to print the query results. Session B updates the T table at 9:51 and commits. The undo segment records the reverse operation of session B on update, assuming the name undo1. Session C inserts the T table at 9:52 and commits as well. The undo section records the insert reverse operation, which is assumed to be called undo2. The query of session A has already recorded the current SCN number of the database when it is initiated, assuming that the SCN number is 950 at this time. Because the ILT transaction slot records the latest SCN number, when comparing with the SCN number in the ILT transaction slot at the head of the data block, it is found that the current SCN is greater than 950, so it is necessary to query the undo segment. The SCN of undo2 is found to be greater than 950. The transaction information recorded in undo is searched for the previous data change. At this time, the SCN of undo1 is still greater than 950. The previous undo information is searched again. It is found that the SCN number of undo0 is earlier than 950. At this time, the information of the data recorded in undo0 + the unchanged data block information will be printed to the user. Because undo segments are recorded by overwriting, if the time is too long or if DML operations are performed frequently. Then in the process of searching, there may be no undo segment less than SCN number 950, and a classic error ORA-1555 snapshoot too old will be returned. This is to avoid phantom reading, dirty reading and other phenomena, and ensure the absolute characteristics of read consistency.

Simply speaking, SQL statement execution time is too long, UNDO table space is too small, transaction volume is too large, commit is too frequent, resulting in consistent read in the execution of SQL procedure, the modified original data (UNDO data) in the UNDO table space has been overwritten, can not construct consistent read block.

Avoidance:

1. Increase the capacity of the rollback section

2. Set the retention time after submission in undo_retention parameter to be larger.

2. Block delay clearing

If ORACLE needs to query the rollback segment to obtain the SCN committed by the transaction when a query triggers a delay block cleanup, oracle will not be able to determine the scn size of the query and the SCN committed by the transaction if the pre-transaction mirror information has been overwritten and the SCN queried is future to the minimum committed SCN information recorded in the rollback segment record. Commit is marked as the smallest scn (safe for queries) that can be found in the rollback segment.

If the interval between the latest change and query is too long, a large number of transaction changes have been made in the interval, or transactions have been committed frequently, the data in the undo segment has been overwritten, and the data change and submission time cannot be determined. At this time, oracle will look up and clear the ITL transaction information in the data block and change the commit SCN to the oldest transaction in the rollback segment, that is, the smallest SCN number. In order to ensure that the data can be queried, ORA-1555 error will be reported.

"How to avoid database ORA-01555 error" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.