Skip to content

12c/18c升级指南

  1.  DB版本生命周期支持策略

 

PS(Primier Support)是自软件发布之日起为期5年的维护和支持服务,超过这个时间就需要购买3年的Extended Support或者不确定时间的Sustaining Support。
Oracle Database 10gR2以及Oracle Database 11gR1都已结束Paid Extended Support进入Sustaining Support阶段,11gR2开始Extended Support包含Waived Extended Support和Paid Extended Support两种:Waived Extended Support不需要单独购买扩展服务包,而Paid Extended Support将在原来的PS服务费用基础上,第一年加收10%的费用提供支持,第二年加收20%的费用提供支持,第三年也是加收20%的费用提供支持, Extended Support主要目的是客户版本升级缓冲期,在该阶段客户将仍能获得“软件更新、修订和安全预警”。

12.2: 新的发行版本会每年发行,版本号是年份的后两位。原来计划发布的12.2.0.2是18c,原来计划发布的12.2.0.3会是19c。18c和19c被认为和12.2的终身支持政策一致。
19c是一个“长期支持”版本(至少4年PS服务,3年扩展支持服务),也是12.2的最后一个版本, 同时也是Oracle Autonomous database优化的一个基础。

 

 

升级到19c的直接升级路径

具体参考:Oracle 19c – Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 19c (Doc ID 2539778.1)

Source Database Target Database
11.2.0.4 19c
12.1.0.212.2.0.1 19c
18c 19c

 

升级到19c的的间接升级路径

Source Database Intermediate upgrade path Target Database
12.1.0.1 12.1.0.2 19c
更早版本 11.2.0.1 / 11.2.0.2/11.2.0.3 11.2.0.4 19c
11.1.0.6 / 11.1.0.7 11.2.0.4 19c
10.2.0.2/10.2.0.3/10.2.0.4/10.2.0.5 11.2.0.4/12.1.0.2 19c
10.1.0.5 11.2.0.4/12.1.0.2 19c
7.3.3.0.0 (or lower)  7.3.4.x –> 9.2.0.8 9.2.0.8 11.2.0.4 19c
8.0.5.0.0 (or lower) 8.0.6.x –> 9.2.0.8
8.1.7.0.0 (or lower) 8.1.7.4 –> 9.2.0.8
9.0.1.3.0 (or lower) 9.0.1.4 –> 9.2.0.8

 

升级到19c的升级技术方法

升级/迁移Oracle数据库19c的方法,不论是上Oracle Cloud还是本地环境都一样 下面是根据操作系统、字节序、版本、数据库大小、停机时间要求等的不同而采用的常用方法

 

具体参考: Transportable Tablespace (TTS) Restrictions and Limitations: Details, Reference, and Version Where Applicable [Document 1454872.1] Best Practices for Using Transportable Tablespaces (TTS) [Document 1457876.1]

方法 说明
Export / Import  适用所有版本和平台,要使用Data Pump需要10.1.0.2或更高版本,停机时间长
       Transportable Tablespaces  Sets(TTS)

       Cross-Platform Transportable Tablespace Sets(XTTS)

8i及以后:TTS(从8i开始),XTTS(10g开始,支持跨平台)

相同的字符集和国家字符集,如果跨字节序(10g+),需要配合RMAN’s convert 

RMAN’s convert function for Transportable Tablespaces 10g及以后版本,可以跨endianness,字符集要兼容

转换动作可以在SoureTarget完成,需要额外的临时工作空间,不支持SYSTEM/SYSAUX

Transportable DatabaseData Pump Full Transportable 11.2.0.3及以后版本,字符集要兼容,12c开始RMAN支持跨字节序转换
XTTS with RMAN Cross Platform Incremental Backups new 11.2.0.4及以后版本,字符集要兼容
Create Table As Select (CTAS)SQL*LoaderCopy 需注意表属性、约束、数据类型的限制
Dataguard Heterogeneous Primary and Physical Standbys Data Guard异构的限制
Oracle GoldenGate 无法支持的异构或停机时间极小的场景

 

 

 

 

关于升级到oracle 19c的一些需要知道的事情

最近在某出版客户升级数据库到 19c 时,源库 pfile / spfile 中设置的 event 被删除。

查询metalink匹配bug为 BUG 30193505 – SOURCE EVENTS REMOVED BY DBUA IN UPGRADING TO 19C.

 

解决办法如下:

从以下链接下载并应用 Patch 30193505:

https://updates.oracle.com/download/30193505.html

在打完 Patch 后,可以使用 DBUA 的 –keepEvents 参数来保留 event

 

参考文章:DBUA does not retain database events after Oracle 19c upgrade (Doc ID 2618457.1)

19c 数据库使用DBCA升级小版本时候遭遇bug未保留之前设置的 events 的解决办法

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认证

Disabling real-time statistics gathering

Real-time Statistics
This Oracle Database 19c new feature is available on certain Oracle Database platforms. Check the Oracle Database Licensing Guide for more
information.
CHALLENGES TO MAINTAINING ACCURATE OPTIMIZER STATISTICS
As mentioned above, stale statistics can result in sub-optimal SQL execution plans and keeping them accurate in highly volatile
systems can be challenging. High-frequency statistics gathering helps to resolving this, but a more ideal solution would be to maintain
statistics as changes to the data in the database are made.

REAL-TIME STATISTICS
Real-time statistics extends statistic gathering techniques to the conventional DML operations INSERT, UPDATE and MERGE. When
these DML operations are executed on the data in the database, the most essential optimizer statistics are maintained in real time. This
applies both the individual row and bulk operations.

Real-time statistics augment those collected by the automatic statistics gathering job, high-frequency stats gathering or those gathered
manually using the DBMS_STATS API. An accurate picture of the data in the database is therefore maintained at all times, which
results in more optimal SQL execution plans.

Real-time statistics are managed automatically, and no intervention from the database administrator is required. Developers may
choose to disable online statistics gathering for individual SQL statements using the NO_GATHER_OPTIMIZER_STATISTICS hint.

Example:

SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */ …

If you already have a well-established statistics gathering procedure or if for some other reason you want to disable automatic statistics
gathering for your main application schema, consider leaving it on for the dictionary tables. You can do so by changing the value of
AUTOSTATS_TARGET parameter to ORACLE instead of AUTO using DBMS_STATS.SET_GLOBAL_PREFS procedure.

 

exec dbms_stats.set_global_prefs(‘autostats_target’,‘oracle’)

 

SQL> begin
dbms_stats.set_global_prefs(‘autostats_target’,’oracle’);
end;
/

参考:
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-statistics-concepts.html#GUID-769E609D-0312-43A7-9581-3F3EACF10BA9
<<19c New Feature:Real-Time Statistics (文档 ID 2552657.1)>>

 

改特性建议关闭。

Oracle 19c Real-Time Statistics 关闭实时统计信息