Skip to content

DBMS_JOB和DBMS_SCHEDULER之间存在内部事务机制,将对DBMS_JOB_MAP表中的重复名称进行内部检查。并且此检查可能导致进程挂起。通

过在一个会话中使用DBMS_JOB创建一个作业,但是不提交,可以非常轻松地重现此内容。然后在第二个会话中创建另一个作业,该作业将挂起。引起其他进程的阻塞

 

id:2645984.1 –在SYS.SCHEDULER $ _DBMSJOB_MAP上删除引起行锁,将补丁30835853应用于Oracle 19c主目录,目前19c linux平台的RU并没有解决此问题,可以提单独的SR申请补丁

 

 

To BottomTo Bottom

In this Document

Symptoms
Changes
Cause
Solution
References

APPLIES TO:

Oracle Database – Enterprise Edition – Version 19.4.0.0.0 and later
Information in this document applies to any platform.
Upgrade done from 12.1.0.2 to 19c

New dictionary table SYS.SCHEDULER$_DBMSJOB_MAP is created in 19c.

SYMPTOMS

Application jobs are slow with “enq: TX – row lock contention” event on the below recursive SQL:

DELETE FROM SYS.SCHEDULER$_DBMSJOB_MAP WHERE JOB_NAME NOT IN (SELECT NAME FROM SYS.OBJ$ WHERE NAME LIKE ‘DBMS_JOB$_%’)

CHANGES

Upgrade to 19c

CAUSE

The issue is due to the below bug:

Bug 30835853 – DBMS_JOB.SUBMIT PROCEDURE BEHAVIOR CHANGE IN 19C

After upgrade database to 19c, the new dictionary table SYS.SCHEDULER$_DBMSJOB_MAP is introduced.

Application jobs may be slow due to below recursive SQL statement causing “enq: TX – row lock contention”.

DELETE FROM SYS.SCHEDULER$_DBMSJOB_MAP WHERE JOB_NAME NOT IN (SELECT NAME FROM SYS.OBJ$ WHERE NAME LIKE ‘DBMS_JOB$_%’)

SOLUTION

Apply the Patch 30835853 for your version

Oracle 19.3.0 升级到 19.7.0需要DBMS_JOB额外补丁以解决hang的问题

日前oracle 19c通过了ol8和红帽8的认证,但是现在还不建议直接上生产

 

Oracle Database 19c is certified on OL8 and RHEL8

Oracle Database 19c is certified on OL8 and RHEL8

 

Oracle Linux 8 版本对19c数据库的一些基本要求

 

Minimum RU: 19.7 (or 19.6 with patches, refer to aforementioned certification notes)

Minimum kernel versions:

    • Oracle Linux 8.1 with the Unbreakable Enterprise Kernel 6: 5.4.17-2011.0.7.el8uek.x86_64 or later,
      or
    • Oracle Linux 8.0 with the Red Hat Compatible kernel: 4.18.0-80.el8.x86_64 or later

ACFS: Please refer to ACFS Supported on OS Platforms for Oracle ACFS certification details (MOS Note: 1369107.1)

 

Red Hat Enterprise Linux 8 版本对19c数据库的一些基本要求

 

Minimum RU: 19.7 (or 19.6 with patches, refer to aforementioned certification notes)

Minimum kernel versions:

    • Oracle Linux 8.0 with the Red Hat Compatible kernel: 4.18.0-80.el8.x86_64 or later

ACFS: Please refer to ACFS Supported on OS Platforms for Oracle ACFS certification details (MOS Note: 1369107.1)

具体参考如下:

Oracle Database 19c已通过OL8和RHEL8认证

新装的oracle 19c grid集群启动报错,日志信息如下:

[root@luda bin]# crsctl start crs
CRS-41053: checking Oracle Grid Infrastructure for file permission issues
PRVH-0112 : Path “/etc/oracle/maps” with permissions “rwxrwxr-x” does not have write permissions for others on node “rac1”.
PRVH-0100 : Restricted deletion flag is not set for path “/etc/oracle/maps” on node “rac1”.
PRVH-0100 : Restricted deletion flag is not set for path “/oracle/19c/grid/crsdata/rac1/shm” on node “rac1”.
PRVG-11960 : Set user ID bit is not set for file “/oracle/19c/19.3.0/grid/bin/jssu” on node “rac1”.
PRVH-0147 : Set group ID bit is not set for file “/oracle/19c/19.3.0/grid/bin/extproc” on node “rac1”.
PRVG-11960 : Set user ID bit is not set for file “/oracle/19c/19.3.0/grid/bin/extjob” on node “rac1”.
PRVG-11960 : Set user ID bit is not set for file “/oracle/19c/19.3.0/grid/bin/oracle” on node “rac1”.
PRVH-0147 : Set group ID bit is not set for file “/oracle/19c/19.3.0/grid/bin/oracle” on node “rac1”.
PRVG-11960 : Set user ID bit is not set for file “/oracle/19c/19.3.0/grid/bin/oradism” on node “rac1”.
CRS-4124: Oracle High Availability Services startup failed.
CRS-4000: Command Start failed, or completed with errors.

 

检查发现权限有问题,通过增加权限解决

 

chmod a+s /oracle/19c/19.3.0/grid/bin/extjob
chmod a+s /oracle/19c/19.3.0/grid/bin/oracle
chmod a+s /oracle/19c/19.3.0/grid/bin/oradism
chmod +t /oracle/19c/grid/crsdata/rac1/shm

Oracle 19c RAC PRVG-11960 0147

在12.2之前,如果你想获得实时的数据,那么在利用query rewrite前,你必须得用on commit的刷新方式刷新物化视图。但是on commit的刷新方式有众多限制。所以,以往的方式中需要采用on command的方式来进行刷新(不管是全量刷新还是增量刷新)。

在使用on command刷新的时候,通过设置job来定时的刷新物化视图,一次job运行之后,下一次job到来之前,如果基表有数据变化是不会体现到物化视图的查询结果里的,实时物化视图就是解决这种问题的,实时获取数据而且免去频繁刷新mv。

 

下面的测试仅供参考:

 

SQL> create table t1 (x not null primary key, y not null) as
2 select rownum x, mod(rownum, 10) y from dual connect by level <= 1000000;

Table created.

SQL> create materialized view log on t1 with rowid (x, y) including new values;

Materialized view log created.

SQL>
SQL> create materialized view mv_old
2 refresh fast on demand
3 enable on query computation
4 enable query rewrite
5 as
6 select y , count(*) c1
7 from t1
8 group by y;

Materialized view created.

create mv时的关键字:enable on query computation

SQL> create table t2 (x not null primary key, y not null) as
2 select rownum x, mod(rownum, 10) y from dual connect by level <= 1000000;

Table created.

SQL> create materialized view log on t2 with rowid (x, y) including new values;

Materialized view log created.

SQL>
SQL> create materialized view mv_new
2 refresh fast on demand
3 enable on query computation
4 enable query rewrite
5 as
6 select y , count(*) c1
7 from t2
8 group by y;

Materialized view created.

SQL> show parameter rewrite

NAME TYPE VALUE
———————————— ———– ——————————
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced

SQL> set autotrace on explain stat

SQL> select mview_name,staleness,on_query_computation from user_mviews;

MVIEW_NAME STALENESS O
—————————————- ——————- –
MV_OLD FRESH N
MV_NEW FRESH Y

SQL>
SQL> select count(*) from MLOG$_T1;

COUNT(*)
———-
0

SQL> select count(*) from MLOG$_T2;

COUNT(*)
———-
0

SQL>

SQL> insert into t1
2 select 1000000+rownum, mod(rownum, 3) from dual connect by level <= 999;

999 rows created.

SQL>
SQL> insert into t2
2 select 1000000+rownum, mod(rownum, 3) from dual connect by level <= 999;

999 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select mview_name,staleness,on_query_computation from user_mviews;

MVIEW_NAME STALENESS O
—————————————- ——————- –
MV_OLD NEEDS_COMPILE N
MV_NEW NEEDS_COMPILE Y

SQL>
SQL> select count(*) from MLOG$_T1;

COUNT(*)
———-
999

SQL> select count(*) from MLOG$_T2;

COUNT(*)
———-
999

SQL>

由于数据stale,且没有set query_rewrite_integrity=stale_tolerated,传统mv没有进行query write。
SQL> select y as y_new_parse1, count(*) from t1
2 group by y;

Y_NEW_PARSE1 COUNT(*)
———— ———-
1 100333
6 100000
2 100333
4 100000
5 100000
8 100000
3 100000
7 100000
9 100000
0 100333

10 rows selected.

Execution Plan
———————————————————-
Plan hash value: 136660032

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 10 | 30 | 515 (4)| 00:00:01 |
| 1 | HASH GROUP BY | | 10 | 30 | 515 (4)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 1000K| 2929K| 498 (1)| 00:00:01 |
—————————————————————————

Statistics
———————————————————-
1975 recursive calls
30 db block gets
4167 consistent gets
1786 physical reads
5440 redo size
754 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
131 sorts (memory)
0 sorts (disk)
10 rows processed

SQL>

real time进行了query rewrite,且查到的数据是最新实时数据

SQL> select y as y_new_parse1, count(*) from t2
2 group by y;

Y_NEW_PARSE1 COUNT(*)
———— ———-
6 100000
4 100000
5 100000
8 100000
3 100000
7 100000
9 100000
1 100333
2 100333
0 100333

10 rows selected.

Execution Plan
———————————————————-
Plan hash value: 542978159

——————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————————
| 0 | SELECT STATEMENT | | 12 | 312 | 22 (14)| 00:00:01 |
| 1 | VIEW | | 12 | 312 | 22 (14)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | VIEW | VW_FOJ_0 | 10 | 290 | 9 (12)| 00:00:01 |
|* 4 | HASH JOIN FULL OUTER | | 10 | 240 | 9 (12)| 00:00:01 |
| 5 | VIEW | | 1 | 7 | 6 (17)| 00:00:01 |
| 6 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | MLOG$_T2 | 999 | 21978 | 5 (0)| 00:00:01 |
| 8 | VIEW | | 10 | 170 | 3 (0)| 00:00:01 |
| 9 | MAT_VIEW ACCESS FULL | MV_NEW | 10 | 60 | 3 (0)| 00:00:01 |
| 10 | VIEW | | 2 | 52 | 13 (16)| 00:00:01 |
| 11 | UNION-ALL | | | | | |
|* 12 | FILTER | | | | | |
| 13 | NESTED LOOPS OUTER | | 1 | 32 | 6 (17)| 00:00:01 |
| 14 | VIEW | | 1 | 26 | 6 (17)| 00:00:01 |
|* 15 | FILTER | | | | | |
| 16 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 |
|* 17 | TABLE ACCESS FULL | MLOG$_T2 | 999 | 21978 | 5 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV_NEW | 1 | 6 | 0 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 35 | 7 (15)| 00:00:01 |
| 20 | VIEW | | 1 | 29 | 6 (17)| 00:00:01 |
| 21 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 |
|* 22 | TABLE ACCESS FULL | MLOG$_T2 | 999 | 21978 | 5 (0)| 00:00:01 |
|* 23 | MAT_VIEW ACCESS BY INDEX ROWID| MV_NEW | 1 | 6 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | I_SNAP$_MV_NEW | 1 | | 0 (0)| 00:00:01 |
——————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

3 – filter(“AV$0″.”OJ_MARK” IS NULL)
4 – access(SYS_OP_MAP_NONNULL(“SNA$0″.”Y”)=SYS_OP_MAP_NONNULL(“AV$0”.”GB0″))
7 – filter(“MAS$”.”SNAPTIME$$”>TO_DATE(‘ 2017-07-12 14:35:01’, ‘syyyy-mm-dd hh24:mi:ss’))
12 – filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL)
15 – filter(SUM(1)>0)
17 – filter(“MAS$”.”SNAPTIME$$”>TO_DATE(‘ 2017-07-12 14:35:01’, ‘syyyy-mm-dd hh24:mi:ss’))
18 – access(“MV_NEW”.”SYS_NC00003$”(+)=SYS_OP_MAP_NONNULL(“AV$0”.”GB0″))
22 – filter(“MAS$”.”SNAPTIME$$”>TO_DATE(‘ 2017-07-12 14:35:01’, ‘syyyy-mm-dd hh24:mi:ss’))
23 – filter(“MV_NEW”.”C1″+”AV$0″.”D0″>0)
24 – access(SYS_OP_MAP_NONNULL(“Y”)=SYS_OP_MAP_NONNULL(“AV$0”.”GB0″))

Note
—–
– dynamic statistics used: dynamic sampling (level=2)
– this is an adaptive plan

Statistics
———————————————————-
906 recursive calls
64 db block gets
1232 consistent gets
14 physical reads
10548 redo size
744 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
64 sorts (memory)
0 sorts (disk)
10 rows processed

对比结果还是比较清楚的。

Real time mv利用原来的已经stale的物化视图,结合mv log,通过计算后,帮你获取实时的数据。即能获得实时数据,又避免频繁的刷新mv。

12c以后 Real-time materialized view 实时物化视图的应用

2018年2月15日Oracle官方在MOS上新发布预警文档《Recommended patches and actions for Oracle databases versions 12.1.0.1, 11.2.0.3 and earlier – before June 2019 (Doc ID 2361478.1)》中强烈建议所有DBA在2019年6月之前将Oracle数据库版本11.1.0.7、11.2.0.3和12.1.0.1打补丁到下面提到的patchset/PSU级别,以解决未来dblinks互操作性方面的潜在问题。

Oracle只是定义为潜在问题,实际关键是oracle的高版本(11.2.4/12.1.0.2/12.2及以上)数据库在2019年06月23日,默认的SCN兼容性会进行自动变化。

2.1 SCN基础概念

系统更改号(SCN)是Oracle数据库使用的一个逻辑的内部时间戳。SCN对数据库中发生的事件进行排序,这是满足事务的ACID属性所必需的。

 

数据库使用SCN来查询和跟踪更改。例如,如果事务更新了一行,那么数据库将记录更新发生时的SCN。此事务中的其他修改通常具有相同的SCN。当事务提交时,数据库将为此提交记录SCN。每个事务在提交后增加SCN。

 

 

2.2 SCN limit 

SCN以单调递增的顺序出现,Oracle数据库可以使用的SCN数量上限非常大。也称为Reasonable SCN Limit,简称RSL。这个值是一个限制,避免数据库的SCN无限制地增大,甚至达到了SCN的最大值。这个值大约是这样一个公式计算出来的:(当前时间-1988年1月1日)*24*3600*16,384 (16K/sec)。兼容性版本1

由于存在上限,任何给定的Oracle数据库都不能耗尽可用的SCN,这一点很重要。Oracle数据库使用基于时间的配给系统来确保不会发生这种情况。

这样做可以确保Oracle数据库随着时间的推移对SCN进行定量配给,从而允许任何使用版本12.2.0.1或更低版本的Oracle数据库进行超过500年的数据处理。从12.2.0.1开始,数据库的兼容性设置为12.2,即使SCN以96K/sec(兼容性版本3)的速度消耗,Oracle数据库也将允许近300万年的数据处理。

 

2.3 SCN headroom

数据库使用的当前SCN与“不超过”上限之间的差异称为SCN headroom 。

 

然而,Oracle已经确定,一些软件错误可能会导致数据库试图超过当前最大SCN值(或者超出所保证的范围)。

 

通常,如果数据库确实试图超过当前的最大SCN值,那么引起此事件的事务将被数据库取消,应用程序将看到一个错误。下一秒,这个限制就会增加,所以通常应用程序在处理过程中会继续出现轻微的打嗝。然而,在一些非常罕见的情况下,数据库确实需要关闭以保持其完整性。在任何情况下,数据都不会丢失或损坏。

 

2.4 SCN兼容性

SCN 兼容级别是限制SCN增速和SCN RSL。

 

2.5 Auto-rollover 

AUTO-ROLLVOER是一种类例JOB的定时任务,定时修改SCN 兼容性级别。

禁用了AUTO-ROLLOVER 到2019-06-23后SCN兼容级别就不会自动调整,还保持原来的限制。 禁用了Auto-RollOver,可以手动调整scn兼容性(前提是应用了补丁)。

 

 


 三 关于这次SCN兼容性自动触发的原理 

在高版本的数据库中引入了SCN 兼容性参数( Compatibility),而且在这个特性中设置了时间限制。

在Oracle数据库软件内核中, 引入了一个: Auto-RollOver 的机制 。

也就是说Oracle 为不同 SCN 兼容性设定了触发时间,随着时间推移自动迭代,用户会在不知情的情况下自动应用了新的SCN 兼容性。

当在2019年6月23日后,整体数据库环境中,有高版本(定义参看上)创建后, 由于SCN允许最高SCN新增速率会有可能有较高的SCN,而未打补丁的数据库处于较低的SCN级别(可能会出现通过dblink连接这两个数据库,由于DBLINK的特性(两端数据库必须同步SCN),如果SCN增加修改数据库的同步超出它的允许SCN率或当前最大的SCN限制,无法建立连接,那么dblink被拒绝,导致业务中断。

 

 


四 查看SCN兼容性级别和调整时间

数据库版本在11.2.0.3.9之前没有打dblink的补丁则没有DBMS_SCN包。

 

在11g版本可以通过sqlplus连接至数据库执行以下语句

set serveroutput on

declare

rsl number;

headroom_in_scn number;

headroom_in_sec number;

cur_scn_compat number;

next_compat number;

max_scn_compat number;

auto_rollover_ts date;

is_enabled boolean;

begin

dbms_scn.getcurrentscnparams(rsl,headroom_in_scn,headroom_in_sec,cur_scn_compat,max_scn_compat);

dbms_scn.getscnautorolloverparams(auto_rollover_ts,next_compat,is_enabled);

dbms_output.put_line(‘RSL=’||rsl);

dbms_output.put_line(‘headroom_in_scn=’||headroom_in_scn);

dbms_output.put_line(‘headroom_in_sec=’||headroom_in_sec);

dbms_output.put_line(‘CUR_SCN_COMPAT=’||cur_scn_compat);

dbms_output.put_line(‘NEXT_COMPAT=’||next_compat);

dbms_output.put_line(‘MAX_SCN_COMPAT=’||max_scn_compat);

dbms_output.put_line(‘auto_rollover_ts=’||to_char(auto_rollover_ts,’YYYY-MM-DD’));

if(is_enabled) then

dbms_output.put_line(‘Auto_rollover is enabled!’);

else

dbms_output.put_line(‘Auto_rollover is disabled!’);

end if;

end;

/

样例输出

 

当前auto rollover是启用且在2019年6月23日自动调整SCN兼容级别为3

五 如何判断是否需要应用补丁调整策略

5.1符合以下条件则无需关注SCN兼容性自动变化及相关补丁信息

所有有DBLINK传输数据数据库的集合版本均为低版本(11.1.0.7/11.2.0.3/12.1.0.1之前版本)或均为高版本(11.2.4/12.1.0.2/12.2及以上),且未来不会有版本和dblink交叉传输数据需求的变化

 

5.2对于11.1.0.7、11.2.0.3和12.1.0.1版本的数据库

 

这些补丁增加了数据库当前最大SCN限制,从16k/s到96k/s,且默认为2019年6月24日启动生效,允许更高的SCN速率,使数据库能够支持比早期版本高许多倍的事务速率。

 

在2019年6月23日之前安装补丁后,可以通关闭autorollover的特性,来规避scn兼容性自动变更到3。命令如下:

sqlplus连接至数据库

Exec dbms_scn. DisableAutoRollover ;

在2019年6月23日之后安装的高版本(定义参看上)数据库环境设置scn 兼容性至1.命令如下:

sqlplus连接至数据库

ALTER DATABASE SET SCN COMPATIBILITY 1;

 

5.3对于上表中Oracle官方没有提及的数据库版本如10.2或更老的数据库 

充分评估业务将来可能出现的dblink传输数据的场景,考虑升级数据库或不使用dblink通过JDBC等其他方式进行数据传输。

对于10g数据库在安装完10.2.0.5.171017的PSU以及补丁14121009。

 

 

5.4安装相应补丁后将SCN的异常情况记录到alert日志中 

我们建议通过控制SCN Headroom的告警阀值使得数据库可以尽可能的将DBLINK传播SCN的行为在alert日志中提现出来,目前我们建议将_external_scn_logging_threshold_seconds设置为600秒左右,使得通过DBLINK传递导致SCN Headroom过低产生alert告警。

alter system set “_external_scn_logging_threshold_seconds”=600 scope=spfile;

 

参考《Recommended patches and actions for Oracle databases versions 12.1.0.1, 11.2.0.3 and earlier – before June 2019 (Doc ID 2361478.1)》

关于Oracle高版本SCN兼容性 变化的处理建议