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 EBS R12.2.6系统资产模块折旧账务未传总账问题处理

背景:

某客户在10月8号运行9月折旧后,创建会计发现请求报黄(但是能把9月的折旧数据正常传过去),于是看创建会计科目输出报表(或者看资产模块“子分类帐期间关闭例外报表”),可以发现里面有大量7月31号的数据,都是很明确的提示如下所示内容,无效期间:

原因探索:

此类问题原因很多,本人遇到过以下几种情况,以频率排序:
1、资产传总账时未正常传至GL模块(总账期间正常打开,此种情况可根据例外报表的提示处理,再次传总账即可);

2、资产传总账时未正常传至GL模块(总账期间关闭);

3、交叉验证规则导致生成账务问题(这种情况很少,一般在新机构建立时可能有)。

具体到本次问题,经过追溯,发现用户在7月31号晚上5点03分提交了运行折旧的请求,但是在请求未完成时,立即提交了创建会计科目到总账模块(默认会将资产相关账务处理传总账)。导致7月新增资产数据科目和中转科目都正常到了总账模块(用户检查时检查了中转科目为0,未再做深入核实,8月折旧也出问题,用户未察觉)。

方案1:打开7月期间正常传至7月总账模块,由于用户反馈7、8月份报表已经出具(金融行业),不同意再开7月期间。

方案2:后台修复数据至当期(9月),具体执行命令不再赘述,见以下脚本(根据自身情况限制条件)

–1、备份三个表的问题数据(具体条件根据自身问题限制)
select t.*
from xla_ae_headers t
where 1 = 1
and to_char(t.accounting_date, ‘yyyy-mm-dd’) = ‘2019-07-31’
and t.gl_transfer_status_code = ‘N’;

select *
from xla_ae_lines t
where 1 = 1
and to_char(t.accounting_date, ‘yyyy-mm-dd’) = ‘2019-07-31’
and t.ae_header_id in
(select t.ae_header_id
from xla_ae_headers t
where 1 = 1
and to_char(t.accounting_date, ‘yyyy-mm-dd’) = ‘2019-07-31’
and t.gl_transfer_status_code = ‘N’);

select *
from xla.xla_events# t
where t.event_type_code = ‘DEPRECIATION’
and to_char(t.event_date, ‘yyyy-mm-dd’) = ‘2019-07-31’
order by t.transaction_date desc;

–2、更新三张表,按顺序以免后续无法关联,更新的时候最好看一下条数是否正确
update xla_ae_lines t
set t.accounting_date = to_date(‘2019-09-30’, ‘yyyy-mm-dd’)
where 1 = 1
and to_char(t.accounting_date, ‘yyyy-mm-dd’) = ‘2019-07-31’
and t.ae_header_id in
(select t.ae_header_id
from xla_ae_headers t
where 1 = 1
AND to_char(t.accounting_date, ‘yyyy-mm-dd’) = ‘2019-07-31’
and t.gl_transfer_status_code = ‘N’);

update xla_ae_headers t
set t.accounting_date = to_date(‘2019-09-30’, ‘yyyy-mm-dd’),
t.period_name = ‘2019-09’
where 1 = 1
and to_char(t.accounting_date, ‘yyyy-mm-dd’) = ‘2019-07-31’
and t.gl_transfer_status_code = ‘N’;

update xla_events t
set t.transaction_date = to_date(‘2019-09-30’, ‘yyyy-mm-dd’),
t.event_date = to_date(‘2019-09-30’, ‘yyyy-mm-dd’),
t.event_status_code = ‘P’,
t.process_status_code = ‘P’
where t.event_type_code = ‘DEPRECIATION’
and to_char(t.event_date, ‘yyyy-mm-dd’) = ‘2019-07-31′;

扩展:

LOOKUP_TYPE LANGUAGE LOOKUP_CODE MEANING DESCRIPTION
XLA_EVENT_PROCESS_STATUS US D Draft Draft
XLA_EVENT_PROCESS_STATUS US E Error Error
XLA_EVENT_PROCESS_STATUS US I Invalid Invalid
XLA_EVENT_PROCESS_STATUS US P Processed Processed
XLA_EVENT_PROCESS_STATUS US R Related Event In Error Related Event In Error
XLA_EVENT_PROCESS_STATUS US U Unprocessed Unprocessed

LOOKUP_TYPE LANGUAGE LOOKUP_CODE MEANING DESCRIPTION
XLA_EVENT_STATUS US I Incomplete Incomplete
XLA_EVENT_STATUS US N No Action No Action
XLA_EVENT_STATUS US P Processed Processed
XLA_EVENT_STATUS US U Unprocessed Unprocessed

SELECT * FROM APPS.FND_LOOKUP_VALUES WHERE LOOKUP_TYPE =’XLA_EVENT_PROCESS_STATUS’ ;

SELECT * FROM APPS.FND_LOOKUP_VALUES WHERE LOOKUP_TYPE =’XLA_EVENT_STATUS’ ;

日期无论改到哪天都会在期间的最后一天。