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

Today, I would like to talk to you about how to deal with oracle 11g rac ORA-01555 snapshots. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something from this article.

Old ORA-01555 snapshots are a common mistake in databases, such as when our transactions need to use undo to build CR blocks

At this time, the corresponding undo no longer exists, and the error of ORA-01555 will be reported at this time.

The environment is Oracle 11g RAC. Because the customer executes a relatively complex SQL, an error occurs after 88 minutes of running with PLSQL. This is a SQL that wants to view the report.

The temporary treatment is as follows:

The following is the virtual machine simulation operation. It is recommended that this parameter must be adjusted and optimized in advance when the database is installed, and do not use the default value.

[root@ysdb1] # su-oracle [oracle@ysdb1 ~] $sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 23 10:39:44 2016Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSYS@ysdb1 > show parameter undo NAME TYPE VALUE-- undo_management string AUTOundo_retention Integer 900-default 15 minutes undo_tablespace string UNDOTBS1SYS@ysdb1 > SYS@ysdb1 > alter system set undo_retention=10800 scope=both System altered.

-- View tablespace capacity

SYS@ysdb1 > col tablespace_name for a15SYS@ysdb1 > col free_rate for a15SYS@ysdb1 > SELECT a.tablespace_name, ROUND (a.total_size) "total_size (MB)", ROUND (a.total_size)-ROUND (b.free_size, 3) "used_size (MB)", ROUND (b.free_size, 3) "free_size (MB)", ROUND (b.free_size / total_size * 100) 2) | |'% 'free_rate FROM (SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size FROM dba_data_files GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name (+) TABLESPACE_NAME total_size (MB) used_size (MB) free_size (MB) FREE_RATE--SYSAUX 178.625 421 .375 70.23%UNDOTBS1 200 56.062 143.938 71.97%USERS 5 14 80%SYSTEM 287.5 412.5 58.93%UNDOTBS2 200 6.687 193.313 96.66%SYS@ysdb1 > SELECT tablespace_name Status, SUM (bytes) / 1024 / 1024 "Bytes (M)" FROM dba_undo_extents GROUP BY tablespace_name, status TABLESPACE_NAME STATUS Bytes (M) TABLESPACE_NAME STATUS Bytes (M)-UNDOTBS1 UNEXPIRED 11UNDOTBS2 UNEXPIRED 2.125UNDOTBS1 EXPIRED 44.0625UNDOTBS2 EXPIRED 3.5625

1. When an ORA-01555 error occurs, there are usually two situations:

1) when the execution time of SQL statement is too long, or the UNDO table space is too small, or the transaction volume is too large, or the submission is too frequent, when the consistent read is performed during SQL, the pre-image (that is, UNDO data) modified by SQL after execution has been overwritten in the UNDO table space, and a consistent read block (CR blocks) cannot be constructed. This is the most common situation.

2) during the execution of the SQL statement, the accessed block cannot determine the priority of the transaction commit time of the block and the start time of SQL execution when delaying block cleanup. This is rare.

two。 The solution to the first situation:

1) increase the size of UNDO tablespace

2) increase undo_retention time. Default is only 15 minutes.

3) optimize the wrong SQL, reduce the query time, the preferred method

4) avoid frequent submissions

After reading the above, do you have any further understanding of the handling of over-reported errors in oracle 11g rac ORA-01555 snapshots? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.