所有由小潘发布的文章

喜欢分享的小潘.
转载文章（档）

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/

FA模块、经验闲谈

Oracle EBS R12.2.6系统资产模块折旧账务未传总账问题处理

2条评论

背景：

某客户在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’ ;

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

系统层级

weblogic server psu

留下评论

参考文件：Critical Patch Update (CPU) Program July 2019 Patch Availability Document (PAD) (文档 ID 2534806.1)

对Oracle EBS（R12.2）系统而言，由于已经集成weblogic服务，特别是双文件系统（RUN、PATCH），只需要对RUN系统进行最新补丁（后面做adop cycle的时候回自动同步，即做patch时切换模式会自动同步）

以下命令是查询系统补丁情况，如果打上了补丁，则会有显示：

cd $FMW_HOME/utils/bsu

./bsu.sh -view -verbose -status=applied -prod_dir=/app/test/apps/fs1/FMW_Home/wlserver_10.3/

 

Content:
========
This patch contains Smart Update patch 8K1U for WebLogic Server 10.3.6.0

Description:
============
WEBLOGIC SAMPLES SPU 10.3.6.0.190716

Patch Installation Instructions:
================================
- copy content of this zip file with the exception of README file to your SmartUpdate cache directory (FMW_HOME/utils/bsu/cache_dir by default)
- apply patch using Smart Update utility
系统层级、经验闲谈

Oracle增加表空间步骤

留下评论

本文只介绍新增文件的方式（其他方式类似可自行研究）：

1、通过命令：show parameter db_block_size，来查询当前数据库单个文件的最大size，如果是8192（8K），最大单个文件为32G，如果是16384（16K），最大单个文件为64G。

2、通过以下命令查询当前系统对应需要增加数据文件的表空间信息。

SELECT Upper(F.TABLESPACE_NAME) “表空间名”,
D.TOT_GROOTTE_MB “表空间大小(M)”,
D.TOT_GROOTTE_MB – F.TOTAL_BYTES “已使用空间(M)”,
To_char(Round((D.TOT_GROOTTE_MB – F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
2),
‘990.99’) || ‘%’ “使用比”,
F.TOTAL_BYTES “空闲空间(M)”,
F.MAX_BYTES “最大块(M)”
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
Round(Max(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;

3、通过以下命令查询表空间对应单个文件的信息（主要是保证新增的数据文件跟原有文件保存信息一致，如保证单个文件一致）。

SELECT “File Name”, “Tablespace”, “Status”, “Size (MB)”, “Used (MB)”, “Used (Proportion)”, “Used (%)”, “Auto Extend” FROM(
select * from (
SELECT /*+ all_rows use_concat */
‘SQLDEV:LINK{#;#}’||USER||’#;#DATAFILE#;#’||ddf.file_name||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “File Name”,
ddf.tablespace_name as “Tablespace”,
ddf.online_status as “Status”,
TO_CHAR(NVL(ddf.bytes / 1024 / 1024, 0), ‘99999990.000’) as “Size (MB)”,
TO_CHAR(DECODE(NVL(u.bytes/1024/1024, 0), 0, NVL((ddf.bytes – NVL(s.bytes, 0))/1024/1024, 0), NVL(u.bytes/1024/1024, 0)), ‘99999999.999’) as “Used (MB)”,
CASE
when ddf.online_status = ‘OFFLINE’ then
‘OFFLINE’
when ddf.online_status = ‘RECOVER’ then
‘RECOVER’
else
‘SQLDEV:GAUGE:0:100:0:0:’|| TRIM(TO_CHAR(DECODE((NVL(u.bytes, 0) / ddf.bytes * 100), 0, NVL((ddf.bytes – NVL(s.bytes, 0)) / ddf.bytes * 100, 0), (NVL(u.bytes, 0) / ddf.bytes * 100)), ‘990’))
end as “Used (Proportion)”,
TO_CHAR(DECODE((NVL(u.bytes, 0) / ddf.bytes * 100), 0, NVL((ddf.bytes – NVL(s.bytes, 0)) / ddf.bytes * 100, 0), (NVL(u.bytes, 0) / ddf.bytes * 100)), ‘990.99’) as “Used (%)”,
ddf.autoextensible as “Auto Extend”
FROM
sys.dba_data_files ddf,
(
SELECT
file_id,
SUM(bytes) bytes
FROM
sys.dba_free_space GROUP BY file_id
) s,
(
SELECT
file_id,
SUM(bytes) bytes
FROM
sys.dba_undo_extents
WHERE
status <> ‘EXPIRED’
GROUP BY file_id
) u
WHERE
(ddf.file_id = s.file_id(+) and ddf.file_id=u.file_id(+))
UNION
SELECT
‘SQLDEV:LINK{#;#}’||USER||’#;#DATAFILE#;#’||v.name||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “File Name”,
dtf.tablespace_name as “Tablespace”,
dtf.status as “Status”,
TO_CHAR(NVL(dtf.bytes / 1024 / 1024, 0), ‘99999990.000’) as “Size (MB)”,
TO_CHAR(NVL(t.bytes_used/1024/1024, 0), ‘99999990.000’) as “Used (MB)”,
CASE
when dtf.status = ‘OFFLINE’ then
‘OFFLINE’
else
‘SQLDEV:GAUGE:0:100:0:0:’|| TRIM(TO_CHAR(NVL(t.bytes_used / dtf.bytes * 100, 0), ‘990.99’))
end as “Used (Proportion)”,
TO_CHAR(NVL(t.bytes_used / dtf.bytes * 100, 0), ‘990’) as “Used (%)”,
dtf.autoextensible as “Auto Extend”
FROM
sys.dba_temp_files dtf,
sys.v_$tempfile v,
v$temp_extent_pool t
WHERE
(dtf.file_name = v.name or dtf.file_id = v.file#)
and dtf.file_id = t.file_id(+)
ORDER BY 1
) sub1 order by 2 asc
)

4、新增文件命令。

alter tablespace APPS_TS_TX_DATA add datafile ‘/u01/DEV/db/data/a_txn_data07.dbf’ size 10000m;