标签归档：undo

转载文章（档）

oracle数据库报错：ORA-01555: 快照过旧: 回退段号 10 (名称为 “_SYSSMU10_3550978943$”) 过小

留下评论

前缀：

比较奇怪的是，请求爆这个错误后，我在程序里面加了个when others的异常，然后就正常了。

oracle undo表空间

undo表空间用于存放undo数据，当执行DML操作（insert、update、delete）时，oracle会将这些操作的旧数据写入到undo段。

undo数据的作用

1.回退事务

当执行DML操作修改数据后，旧数据被存放在undo段中。只要数据为提交、回滚段未写满或者回滚段为超时的情况下，旧数据都能被回滚回来。

2.读一致性

通过DML操作后的数据没有提交之前，其他用户读取的数据都是回滚段里面的旧数据。

使用undo参数

1.undo_management

该初始化参数用于指定undo数据的管理方式。如果要使用自动管理模式，必须设置为auto，如果使用手工管理模式必须设置该参数为manual，使用自动管理模式时，oracle会使用undo表空间管理，使用手工管理模式时，oracle会使用回滚段管理undo数据。需要注意,使用自动管理模式时,如果没有配置初始化参数UNDO_TABLESPACE,oracle会自动选择第一个可用的UNDO表空间存放UNDO数据,如果没有可用的UNDO表空间,oracle会使用SYSTEM回滚段存放UNDO记录,并在ALTER文件中记载警告。

2,UNDO_TABLESPACE

该初始化参数用于指定例程所要使用的UNDO表空间,使用自动UNDO管理模式时,通过配置该参数可以指定例程所要使用的UNDO表空间.

在RAC(Real Application Cluster)结构中,因为一个UNDO表空间不能由多个例程同时使用,所有必须为每个例程配置一个独立的UNDO表空间.

3,UNDO_RETENTION

该初始化参数用于控制UNDO数据的最大保留时间,其默认值为900秒,从9i开始,通过配置该初始化参数,可以指定undo数据的保留时间,从而确定倒叙查询特征(Flashback Query)可以查看到的最早时间点.

手工管理回滚段的规划：

SQL> show parameter  undo;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

undo_management                string      AUTO

undo_retention                       integer    900

undo_tablespace                    string      UNDOTBS1

SQL> show parameter  transactions;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

transactions                         integer     187             ———-系统准备支持的事务连接数量。

transactions_per_rollback_segment    integer     5       ————–每个回滚段支持的事务连接数量。回滚段数=187/5

SQL> show  parameter rollback;

NAME                                 TYPE        VALUE

———————————— ———– ———-

fast_start_parallel_rollback         string      LOW

rollback_segments                      string ———————-设置回滚段的数量

transactions_per_rollback_segment    integer     5

错误原因：

SQL语句执行时间太长，或者UNDO表空间过小，或者事务量过大，或者过于频繁的提交，导致执行SQL过程中进行一致性读时，SQL执行后修改的 前镜像（即UNDO数据）在UNDO表空间中已经被覆盖，不能构造一致性读块（CR blocks）。  这种情况最多。

解决办法：

第1种情况解决的办法：

（1）增加UNDO表空间大小

（2）增加undo_retention 时间，默认只有15分钟

alter  system set undo_retention=14400 ；

undo_retention这个值可以根据情况调大一些。

（3）优化出错的SQL，减少查询的时间，首选方法

（4）避免频繁的提交

还有一种情况，可能是oracle BUG，官网说法ORA-01555BUG比较多，

其他参考：

https://www.cnblogs.com/Richardzhu/archive/2013/03/25/2981610.html

查看表空间使用情况
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                                    900       835.687        64.313 7.15%
UNDOTBS1                                24576        53.875     24522.125 99.78%
USERS                                       5         1.312         3.688 73.75%
SYSTEM                                   4170      4160.687         9.313 .22%
USER_DATA                                 150       105.062        44.938 29.96%

计算所需undo表空间的大小:

1.计算业务高峰期每秒产生undo数据块的个数
SQL> select max(undoblks / ((end_time – begin_time)*24*3600)) from v$undostat;

MAX(UNDOBLKS/((END_TIME-BEGIN_
——————————
11.305

2.得到undo数据块在undo表空间中可以保留的最长时间
SQL> show parameter undo_retention;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
undo_retention                       integer     86400

3.得到数据块大小
SQL> show parameter db_blo

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TYPICAL
db_block_size                        integer     8192

4.将以上三者的数据相乘就是所需undo表空间的大小数
SQL> select (11.305*86400*8192)/1024/1024/1024 undoTablespace_GB from dual;

UNDOTABLESPACE_GB
—————–
7.4520263671875

发现undo表空间不够的时候，赶紧增加undo表空间的大小，执行语句如下：
alter tablespace undotbs1 add datafile ‘/u01/database/instance_name/undotbs02.dbf’ size 100M autoextend on next 128M maxsize 24G;
alter tablespace undotbs1 add datafile ‘/u01/database/instance_name/undotbs03.dbf’ size 100M autoextend on next 128M maxsize 24G;
alter tablespace undotbs1 add datafile ‘/u01/database/instance_name/undotbs04.dbf’ size 100M autoextend on next 128M maxsize 24G;

–查看会话数
select count(*) from v$session;

–查看进程数
select count(*) from v$process;

–查看数据库的并发连接数
select * from v$session where status=’ACTIVE’;

–查看当前数据库建立的会话
SELECT SID,SERIAL#,USERNAME,PROGRAM,MACHINE,STATUS FROM V$SESSION;

–查看数据库允许的最大连接数
SELECT value FROM V$PARAMETER WHERE NAME=’processes’

–查看数据库允许的最大会话数
SELECT value FROM V$PARAMETER WHERE NAME=’sessions’
–查看后台正在运行着的sql语句
select a.program,b.spid,c.sql_text from v$session a,v$process b,v$sqlarea c where a.paddr=b.addr and a.sql_hashvalue=c.hash_value and a.username is not null;

查询所有数据库的连接数
select schemaname,count(*)from v$session group by schemaname;

查询终端用户使用数据库的连接情况。
select osuser,schemaname,count(*) fromv $session group by schemaname,osuser;

查看当前不为空的连接
select * from v$session where username is not null

查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username

转自：https://blog.csdn.net/qiuzhi__ke/article/details/78937078

 

转载文章（档）

Oracle数据库评估undo 表空间大小

留下评论

如何估算Oracle数据库所需的UNDO表空间的大小:

How To Size UNDO Tablespace For Automatic Undo Management (文档 ID 262066.1)

要确定Oracle需要的UNDO 表空间的大小，需要以下三条信息：

UR 以秒为单位的UNDO_RETENTION

UPS 每秒生成的还原数据块的数量

DBS db_block_size

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

UNDO_RETENTION是一个参数，此参数控制为提供读一致性而保留的还原数据量，以秒为单位定义，可以在初始化文件中设置，或使用 ALTER SYSTEM 命令来动态修改。

SQL>ALTER SYSTEM SET UNDO_RETENTION=900;

SQL> show parameter undo_retention

NAME TYPE VALUE

———————————— ———– ——————————

undo_retention integer 900

如果值为900，则可以使还原数据保留 15 分钟，当然需要足够的存储空间才行。

那么如何计算每秒生成的还原数据块的数量呢，可以通过v$undostat视图的begin_time、end_time和undoblks三个字段的值查询出来，计算的SQL语句如下：

SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS “Bytes” FROM (SELECT value AS UR  FROM v$parameter WHERE name = ‘undo_retention’),(SELECT (SUM(undoblks)/SUM(((end_time -begin_time)*86400))) AS UPS  FROM v$undostat),  (SELECT value AS DBS FROM v$parameter  WHERE name = ‘db_block_size’);

Bytes

———-

445814.844

详解：

一般应该在一天中数据库负载最繁重的时候进行计算。

对于UNDO表空间大小的定义需要考虑UNDO_RETNETION参数、产生的UNDO BLOCKS/秒、UNDO BLOCK的大小。undo_retention：对于UNDO表空间的数据文件属性为autoextensible,则undo_retenion参数必须设置，UNDO信息将至少保留至undo_retention 参数设定的值内，但UNDO表空间将会自动扩展。对于固定UNDO表空间，将会通过表空间的剩余空间来最大限度保留UNDO信息。如果FIXED UNDO表空间没有对保留时间作GUARANTEE（alter tablespace xxx retention guarantee;），则undo_retention参数将不会起作用。（警告：如果设置UNDO表空间为retention guarantee，则未过期的数据不会被复写，如果表空间不够则会导致DML操作失败或者transation挂起）

Oracle 10g 有自动Automatic Undo Retention Tuning 这个特性。设置的 undo_retention 参数只是一个指导值,，Oracle 会自动调整 Undo (会跨过 undo_retention 设定的时间) 来保证不会出现 Ora-1555 错误.。通过查询V$UNDOSTAT（该视图记录4天以内的UNDO表空间使用情况，超过4天可以查询DBA_HIST_UNDOSTAT视图） 的 tuned_undoretention （该字段在10G版本才有，9I是没有的）字段可以得到Oracle 根据事务量（如果是文件不可扩展，则会考虑剩余空间）采样后的自动计算出最佳的 retenton 时间.。这样对于一个事务量分布不均匀的数据库来说,，就会引发潜在的问题–在批处理的时候可能 Undo 会用光， 而且这个状态将一直持续， 不会释放。

SQL查询tuned_undoretention：

select to_char(begin_time,’DD-MON-RR HH24:MI’) begin_time,to_char(end_time,’DD-MON-RR HH24:MI’) end_time,tuned_undoretention from v$undostat order by end_time;

检查一天平均每秒产生的UNDO BLOCK

select (sum(undoblks)/sum((end_time-begin_time)*86400) from v$undostat;

生成的结果是UNDO BLOCK，如果需要计算出实际大小，则需要乘以db_block_size（通过show parameter db_block_size查出来）

如何计算合适的UNDO表空间大小：

select (UR*(UPS*DBS))+(DBS*24) as “bytes” from (select value as UR from v$parameter where name=’undo_retention’),(select (sum(undoblks)/sum(((end_time-begin_time)*86400))) as ups from v$undostat),(select value as DBS from v$parameter where name=’db_block_size’);

转自：http://blog.itpub.net/31397003/viewspace-2147515/

转载文章（档）

Oracle ORA-01555快照过旧的错误（转载）

留下评论

转载地址：http://blog.csdn.net/liaoyuanzi/article/details/7712682

关于Oracle ORA-01555快照过旧的错误


首先了解Oracle在什么情况下会产生ORA-01555错误: 


假设有一张6000万行数据的testdb表，预计testdb全表扫描1次需要2个小时，参考过程如下： 


1、在1点钟，用户A发出了select * from testdb;此时不管将来testdb怎么变化，正确的结果应该是用户A会看到在1点钟这个时刻的内容。


2、在1点30分，用户B执行了update命令，更新了testdb表中的第4100万行的这条记录，这时，用户A的全表扫描还没有到达第4100万条。毫无疑问，这个时候，第4100万行的这条记录是被写入了回滚段，假设是回滚段UNDOTS1，如果用户A的全表扫描到达了第4100万行，是应该会正确的从回滚段UNDOTS1中读取出1点钟时刻的内容的。 


3、这时，用户B将他刚才做的操作提交了，但是这时，系统仍然可以给用户A提供正确的数据，因为那第4100万行记录的内容仍然还在回滚段UNDOTS1里，系统可以根据SCN到回滚段里找到正确的数据，但要注意到，这时记录在UNDOTS1里的第4100万行记录已经发生了重大的改变：就是第4100万行在回滚段UNDOTS1里的数据有可能随时被覆盖掉，因为这条记录已经被提交了！ 


4、由于用户A的查询时间漫长，而业务在一直不断的进行，UNDOTS1回滚段在被多个不同的transaction使用着，这个回滚段里的extent循环到了第4100万行数据所在的extent，由于这条记录已经被标记提交了，所以这个extent是可以被其他transaction覆盖掉的！ 


5、到了1点45分，用户A的查询终于到了第4100万行，而这时已经出现了第4条说的情况，需要到回滚段UNDOTS1去找数据，但是已经被覆盖掉了，这时就出现了ORA-01555错误。 以上此段非本人原创 


 


原因分析："报表"程序执行时间漫长，在程序查询的过程中其他用户对"报表"进行了更新，被更新的数据写入了回滚段，当程序到回滚段找数据时，发现数据已经被覆盖掉，于是就出现了ORA-01555错误。另外"报表"程序执行效率不高也会造成ORA-01555错误。
解决办法：
1、扩大回滚段，因为回滚段是循环使用的，如果回滚段足够大，那么那些被提交的数据就能保存足够长的时间，使那些大事务完成一致性读取。之前EBS系统UNDO表空间为9GB，目前为10GB。见下图:



2、增加undo_retention时间，因为UNDO回滚段是循环使用，里面的数据可能随时被循环覆盖掉，如果设置undo_retention时间更长，那么在retention规定的时间内，任何其他事务都不能覆盖这些数据。目前EBS系统undo_retention为10800秒(3个小时)。见下图：
 


3、最重要的一点就是优化程序相关查询语句，减少查询语句的一致性读，降低读取不到回滚段数据的风险。所有的出错信息都会纪录到数据库日志alert_PROD.log文件中，下图红线部分是一条SQL查询词句，ORA-01555很有可能是这条语句造成，把这条语句提供给开发人员来分析和优化程序代码。


————————————————————————————————————————–

ORA-01555 原因与解决：

前面提到了ORA-01555错误，那么现在来看一下ORA-01555错误是怎样产生的。由于回滚段是循环使用的，当事务提交以后，该事务占用的回滚段事务会被标记为非活动，回滚段空间可以被覆盖重用。那么一个问题就出现了，如果一个查询需要使用被覆盖的回滚段构造前镜像实现一致性读，那么此时就会出现Oracle著名的ORA-01555错误。

ORA-01555错误的另外一个原因是因为延迟块清除（Delayed Block Cleanout）。当一个查询触发延迟块清除时，Oracle需要去查询回滚段获得该事务的提交SCN，如果事务的前镜像信息已经被覆盖，并且查询SCN也小于回滚段中记录的最小提交SCN，那么Oracle将无从判断查询SCN和事务提交SCN的大小，此时出现延迟块清除导致的ORA-01555错误。

另外一种导致ORA-01555错误的情况出现在使用sqlldr直接方式加载（direct=true）数据时。当通过sqlldr direct=true 方式加载数据时，由于不产生重做和回滚信息，Oracle直接指定Cached Commit SCN 给加载数据，在访问这些数据时，有时会产生ORA-01555错误。

看下图的描述：假定在时间T用户A发出一条更新语句，更新SCOTT用户的SAL；用户B在Ty时间发出查询语句，查询SCOTT用户的SAL；用户A的更新在Tx时间提交，提交可能为快速提交块清除，也可能是延迟块清除；用户B的查询在Tz时间输出。

来看一下数据库在不同情况下的内部处理：

如果 Ty < T < Tz < Tx ，那么查询需要构造一致性读，由于事务尚未提交，可以通过回滚段构造前镜像，完成一致性读取。
·如果 Ty < T < Tx < Tz ，由于Ty查询时间小于T事务更新时间，那么数据库需要构造一致性读取，而Tz查询完成时间大于Tx提交时间，那么前镜像就有可能被覆盖，不可获取。

如果Tx的提交方式为Fast Block Cleanout，那么回滚段信息不可用时就会出现一致性读ORA-01555错误。

如果Tx的提交方式为Delayed Block Cleanout，那么回滚段信息不可用时Oracle将无法判断Ty和Tx的时间先后关系。如果 Ty > Tx ，那么Oracle可以正常进行块清除，并将块清除后的数据返回给用户B；如果 Ty < T ，那么Oracle需要继续构造一致性读返回给用户B；Oracle无法判断这两种情况，就会出现延迟块清除ORA-01555错误。

ORA-01555的直观解释是“snapshot too old”，也就是快照太旧，其根本含义就是查询需要的前镜像过于“久远”，已经无法找到了。可以想象，如果一个历时数个小时或十几个小时的查询，如果最后遭遇ORA-01555错误而失败，会是多么令人沮丧的一件事。一直以来，ORA-01555都是ORACLE最为头痛的问题之一。

在Oracle 9i的文档中这样描述ORA-01555错误：

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 other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
//          setting. Otherwise, use larger rollback segments

可以看到，在Oracle 9i自动管理UNDO表空间模式下，UNDO_RETENTION参数的引入正是为了减少ORA-01555错误的出现。这个参数设置当事务提交之后（回滚段变得非活跃），回滚段中的前镜像数据在被覆盖前保留的时间，该参数以秒为单位，9iR1初始值为900秒，在Oracle 9iR2增加为10800秒。

显然该参数设置的越高就越能减少ORA-01555错误的出现，但是保留时间和存储空间是紧密相关的，如果UNDO表空间的存储空间有限，那么Oracle就会选择回收已提交事务占用的空间，置UNDO_RETENTION参数于不顾。

在Oracle 9i的AUM模式下，UNDO_RETENTION实际上是一个非担保（NO Guaranteed）限制。也就是说，如果有其他事务需要回滚空间，而空间出现不足时，这些信息仍然会被覆盖；从Oracle 10g开始，Oracle对于UNDO增加了Guarantee控制，也就是说，可以指定UNDO表空间必须满足UNDO_RETENTION的限制。当UNDO表空间设置为Guarantee，那么提交事务的回滚空间必须被保留足够的时间，如果UNDO表空间的空间不足，那么新的事务会因空间不足而失败，而不是选择之前的覆盖。

从各个不同版本回滚段的管理变迁，我们可以看出Oracle一直在进步。

Oracle提供了一个内部事件（10203事件）可以用来跟踪数据库的块清除操作，10203事件可以通过以下命令设置，设置后需要重新启动数据库该参数方能生效：


 

alter system set event="10203 trace name context forever" scope=spfile;


 

需要注意的是，可能存在另外一种情况，就是当执行延迟块清除时，回滚段或原回滚表空间已经被删除，此时Oracle仍然可以通过字典表UNDO$来获得SCN信息，执行块清除。

关于Oracle的提交处理及块清除机制是一个极其复杂的过程，本文对这部分内容进行了适当简化说明，旨在使大家能够对Oracle的回滚机制、块清除机制有所了解。

– The End –