Sqoop Oracle ORA-01555 Snapshot old error when used ____oracle

About an old error with Oracle ORA-01555 snapshots
First, understand under what circumstances Oracle will produce ORA-01555 errors:

Assuming that there is a testdb table of 60 million rows of data, it is expected that testdb full table scanning 1 times will take 2 hours, the reference process is as follows:
1. At 1 o'clock, User a issued a select * from TestDB; no matter how the TestDB changes in the future, the correct result should be that user A will see the content at the 1 o'clock moment.
2. At 1:30, User B executes the update command, updating this record in line No. 41 million of the TestDB table, when user A's full table scan has not reached No. 41 million. This time, no doubt, this record in line No. 41 million is written to the rollback segment, assuming that the rollback segment is UNDOTS1, and if User A's full table scan reaches line No. 41 million, it should be correct to read the contents of 1 o'clock from the rollback segment UNDOTS1.
3. At this point, User B submits the operation he just made, but at this point, the system can still provide user A with the correct data, because the NO. 41 million line of records is still in the rollback segment UNDOTS1, the system can according to the SCN to the rollback section to find the correct data, but to note that, A significant change has taken place in the No. 41 million line of records recorded in UNDOTS1: It is possible that the data from the No. 41 million line in the rollback segment UNDOTS1 may be overwritten at any time because the record has been submitted.
4, because the user a long query time, and the business has been ongoing, UNDOTS1 rollback segment in the use of a number of different transaction, the rollback section of the extent loop to the No. 41 million row of data in the extent, because this record has been marked submitted, So this extent can be covered by other transaction.
5, to 1:45, user A's query finally to the NO. 41 million line, and then there has been a 4th said the situation, need to rollback segment UNDOTS1 to find data, but has been covered off, then there are ORA-01555 errors.
↑↑
The above paragraph is not my original




Cause Analysis: "Report" Program execution time is long, in the process of query other users of the "report" has been updated, the updated data written to the rollback segment, when the program to the rollback segment to find data, found that the data has been covered, so there has been a ORA-01555 error. In addition, "Report" program execution efficiency is not high also can cause ORA-01555 error.
Solution:
1, expand the rollback segment, because the rollback segment is recycled, if the rollback segment is large enough, then those submitted data can be saved long enough to make those large transactions to complete the consistent read. Before the EBS System Undo table space is 9GB, currently 10GB. See figure below:


2, increase undo_retention time, because the undo rollback segment is recycled, the data inside may be recycled at any time, if the set undo_retention longer, then in the retention specified time, any other transaction can not overwrite the data. The EBS system currently undo_retention 10,800 seconds (3 hours). See figure below:


3, the most important point is the optimization program-related query statements, reduce the consistency of query statements read, reduce the risk of reading the rollback segment data. All error messages are recorded in the database log Alert_prod.log file, the following figure Red section is a SQL query phrase, ORA-01555 is likely to be the result of this statement to provide developers to analyze and optimize the program code.

--------------------------------------------------------------------------------------------------------------- -----------

ORA-01555 Reasons and Solutions:

The ORA-01555 error is mentioned earlier, so now let's look at how the ORA-01555 error occurred. Because the rollback segment is recycled, the rollback segment transaction occupied by the transaction is marked inactive when the transaction is committed, and the rollback segment space can be overridden for reuse. Then a problem arises, if a query needs to be read consistent with the overridden rollback segment construction, then Oracle's famous ORA-01555 error occurs.

Another reason for the ORA-01555 error is the delay block cleanup (delayed blocks cleanout). When a query triggers a delay block cleanup, Oracle needs to query the rollback segment to obtain the submission SCN for the transaction, and if the front mirror information for the transaction has been overwritten and the query SCN is less than the smallest submission SCN recorded in the rollback segment, then Oracle will have no way of judging the size of the query SCN and transaction submission SCN , there is a ORA-01555 error caused by the delay block cleanup.

Another scenario that causes a ORA-01555 error occurs when you use SQLLDR to load (direct=true) data in a direct manner. When loading data by Sqlldr Direct=true, Oracle directly specifies the cached Commit SCN to load data because no redo and rollback information is generated, sometimes generating ORA-01555 errors when accessing the data.

Look at the description of the following diagram: Assume that in time t user a emits an UPDATE statement that updates the Scott user's Sal, User B issues a query at Ty time, queries the Scott user's Sal; User A's updates are submitted at TX time, commit may be cleared for quick commit blocks, or delay block cleanup The query for User B is output in TZ time.

Look at the internal processing of the database in different situations:

• If Ty < T < Tz < Tx, then the query needs to construct consistent read, because the transaction has not yet been committed, you can complete the consistent read by rolling back the segment before constructing the mirror.
• If Ty < T < TX < Tz, because the Ty query time is less than the T transaction update time, then the database needs to construct the consistent read, and the TZ query completion time is greater than the TX submission time, then the former mirror may be overwritten, not be obtained.

If the submit mode of TX is fast block cleanout, a consistency read ORA-01555 error occurs when the rollback segment information is not available.

If the submit mode of TX is delayed block cleanout, Oracle will not be able to determine the time relationship between Ty and TX if the rollback segment information is not available. If Ty > Tx, then Oracle can perform block cleanup normally and return the block data to User B; if Ty < T, then Oracle needs to continue to construct the consistency read back to the user b;oracle cannot judge both cases. There will be a delay block clear ORA-01555 error.

ORA-01555 's intuitive interpretation is "snapshot too old", that is, the snapshot is too older, the fundamental meaning is that the query needs before the mirror is too "remote", has been unable to find. It can be imagined how frustrating it would be if a query that lasted for hours or more than 10 of an hour would fail at the end of a ORA-01555 error. ORA-01555 has been one of Oracle's most vexing problems.

This describes the ORA-01555 error in Oracle 9i documentation:

01555, 00000, "snapshot too old:rollback segment number%s with name \"%s\ "too small"
*cause:rollback records needed by a reader for consistent read are
Overwritten by the other writers
*action:if in Automatic Undo Management mode, increase undo_retention
Setting. Otherwise, use larger rollback segments

As you can see, in Oracle 9i Automatic Management UNDO tablespace mode, the Undo_retention parameter is introduced to reduce the appearance of ORA-01555 errors. This parameter is set when a transaction commits (the rollback segment becomes inactive), the time that the front mirror data in the rollback segment is retained before being overwritten, in seconds, the 9ir1 initial value is 900 seconds, and the Oracle 9ir2 is incremented to 10,800 seconds.

Obviously the higher the setting of this parameter will reduce the ORA-01555 error, but the retention time and storage space are closely related, if the undo tablespace is limited in storage space, then Oracle will choose to reclaim the space occupied by the committed transaction, ignoring the undo_retention parameter.

In Oracle 9i AUM mode, Undo_retention is actually an unsecured (NO guaranteed) restriction. That is, if there are other transactions that need to be rolled back and the space is not sufficient, the information will still be overwritten, and Oracle has increased guarantee control of undo from Oracle 10g, that is, you can specify that the undo Tablespace must meet the Undo_ Retention restrictions. When the Undo table space is set to guarantee, the rollback space for the commit transaction must be kept for sufficient time, and if there is insufficient space in the undo table space, the new transaction will fail due to insufficient space, rather than the previous overwrite.

We can see that Oracle has been making progress in managing changes in the rollback segment from various versions.

Oracle provides an internal event (10203 event) that can be used to track the block cleanup operations of the database, and the 10203 event can be set by the following command, which requires a restart of the database after it has been set for the parameter to take effect:

Alter system set event= "10203 Trace name Context Forever" scope=spfile;

Note that there may be another situation where the rollback segment or the original rollback table space has been deleted when the delay block cleanup is performed, and Oracle can still obtain the SCN information through the dictionary table undo$, and perform block cleanup.

Regarding Oracle's commit processing and block clearing mechanism is an extremely complex process, this paper makes a proper simplification of this part, which aims to make the Oracle rollback mechanism and block clearing mechanism understood.

-The end-