使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

0    699    1

Tags:

👉 本文共约15542个字,系统预计阅读时间或需59分钟。

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1)

适用于:

Oracle Database - Enterprise Edition - 版本 11.2.0.3 到 12.2.0.1 [发行版 11.2 到 12.2]
Oracle Database Exadata Cloud Machine - 版本 N/A 和更高版本
Oracle Cloud Infrastructure - Database Service - 版本 N/A 和更高版本
Oracle Database Cloud Exadata Service - 版本 N/A 和更高版本
Oracle Database Cloud Schema Service - 版本 N/A 和更高版本
本文档所含信息适用于所有平台

用途

本文旨在提供使用 DBUA 从 12c 前数据库升级到 12.2.0.1 的指南。

适用范围

数据库管理人员/技术支持

详细信息

Database Upgrade Assistant (DBUA)

  • Database Upgrade Assistant (DBUA) 交互式的协助您一步一步升级,配置新的 12.2 数据库。是推荐的升级数据库主要版本或者补丁集的方式。

  • 它会自动执行为了升级数据库所需的所有步骤,会对数据库的某些特定领域做出推荐,您可以根据推荐做相应的配置修改。

  • DBUA 也支持升级 Oracle Real Application Clusters (Oracle RAC) 数据库。在 Oracle RAC 环境下, DBUA 升级集群中的所有节点上的数据库以及配置文件。

  • DBUA 需要在新安装的数据库 12c 软件的 ORACLE_HOME 下运行。在 windows 环境下,需要使用 administrator 用户或者软件的属主用户来执行 DBUA。

  • DBUA 会执行 Pre-Upgrade Tool,它会自动修正一些升级需要的配置参数。比如 Pre-Upgrade Tool 可以修改初始化参数成升级需要的值。Pre-Upgrade Tool 也会列出需要在升级前就手工解决的问题列表。

  • 它会对数据库的某些特定领域做出推荐,根据推荐做相应的配置修改可以让升级过程更加简单/人性化。

  • 当解决了所有预升级推荐/错误/警告后开始升级,DBUA 会显示升级源库各个组件的进展。

  • 和之前版本的 DBUA 相比,12c 的 DBUA 限制了对隐藏参数的设置。因为除了为了升级而由 Oracle 技术支持推荐的隐含参数外,Oracle 不推荐使用其它的隐藏参数。
    要检查数据库中已设置的隐藏参数,可以使用 AS SYSDBA 权限执行下面的命令:
    SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '_%' ESCAPE '\';

  • DBUA 会在数据库真正升级之前检查一些项目;这些检查也可以手工去做来减少升级的停机时间。

  • DBUA 提供下面的一些选项:

    - 升级 timezone。12.2.0.1 默认的 timezone 版本是 26。

    - 在升级前收集数据字典的统计信息。

    - 把用户表空间设置为只读。

    - 在升级前做 RMAN 备份。

    - 可以恢复数据库备份来回退升级。

    - 可以在升级前或升级后执行自定义的脚本。

    - 显示 alert log 和 DBUA log的路径。

    - 可以把已存在的 listener 升级到最新的 12c Home 下或者在 12.2 目标库 HOME 下创建一个新的 listener。

  • 从 Oracle Database 12c release 2 (12.2) 开始,升级数据库可以不禁用 Oracle Database Vault。不过,如果在升级前禁用了 Oracle Database Vault,那么升级后需要手工启用它。

升级路径/12.2 兼容性矩阵

DBUA 只能对一些支持的版本进行直接升级。

可以直接升级到 12.2 的版本:

源数据库目标数据库
11.2.0.3 或者更高版本12.2.0.x
12.1.0.x (12.1.0.1 - 12.1.0.2)12.2.0.x

间接升级到 12.2:

源数据库升级路径目标数据库
7.3.3 (或更低版本)7.3.4 --> 9.2.0.8 --> 11.2.0.3 或更高版本12.2.0.x
8.0.5 (或更低版本)8.0.6 --> 9.2.0.8 --> 11.2.0.3 或更高版本12.2.0.x
8.1.7(或更低版本)8.1.7 --> 9.2.0.8 --> 11.2.0.3 或更高版本12.2.0.x
9.0.1.3(或更低版本)9.0.1.3 --> 9.2.0.8 --> 11.2.0.3 或更高版本12.2.0.x
9.2.0.7(或更低版本)9.2.0.7 --> 11.2.0.3 或更高版本12.2.0.x
10.2.0.4 (或更低版本)10.2.0.4 --> 11.2.0.3 或更高版本12.2.0.x
11.1.0.611.1.0.6 --> 11.2.0.3 或更高版本12.2.0.x
11.2.0.111.2.0.1 --> 11.2.0.3 或更高版本12.2.0.x

需要及推荐在源库上完成的

  • 在升级前确保所有 oracle 提供的组件和对象都是有效的。

  • 在开始升级或者降级前,Oracle 推荐您先升级源数据库和目标数据库到最新的 Oracle bundle patch 或者 patch set update (BP 或者 PSU)。

  • 确保在 sys 和 system schema 下没有重复存在的对象。对于 1) 和 2),参照:

    Doc Id 556610.1 - Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

    dbupgdiag.sql 是可以在升级前或者升级后对数据库的状态进行诊断并提供用户友好的输出结果的一些 SQL 语句;它会创建一个名为 db_upgdiag_.log 的输出文件。

  • 确保升级前拥有一个可用的源数据库的备份。

  • 禁用所有自定义的 before/after DDL 类型的触发器,完成升级后再启用它们。

  • 在升级前检查数据库的升级/降级兼容性矩阵。

  • 推荐在升级前启用 archive log 模式,这样 DBUA 在升级过程中可以创建/更新 archive log。

  • 如果使用 DBUA 升级的是 RAC 数据库,那么必须保持 CLUSTER_DATABASE 设置成 true。

  • 在升级前确保运行了 pre-upgrade 工具。 检查并且实施 preupgrade log 给出的建议。

  • 在升级前原数据库上的物化视图应该被停掉
    Doc ID 1406586.1 - How to Handle Materialized Views When You Upgrade or Clone a Database

  • 禁止掉客户的计划作业以及 cron job。

推荐/需要在目标库上完成的

  • 先检查您的硬件平台/操作系统是否兼容 12.2 版本。
  • 下载安装 12c 版本2 (12.2.0.1) 软件到一个新的 ORACLE_HOME 并确认没有编译错误。
  • 如果有补丁集(PatchSet)或者最新的 Critical Patch Update(PSU/CPU)的话,下载并安装它们。
  • 确保设置 ORACLE_HOME, PATH, LD_LIBRARY_PATH, LIBPATH 等指向到 12.2 目标库 HOME。

对于Oracle Spatial组件:

升级前在源库上执行下面的语句:

如果有返回任何结果,那么需要在升级前对目标Oracle HOME打下面的补丁。

链接 : https://updates.oracle.com/Orion/Download/process_form/p25293022_122010_Generic.zip?file_id=95607293&aru=21422588
文件名 : p25293022_122010_Generic.zip

如果不打这个补丁会导致升级的post upgrade步骤的utlrp.sql执行缓慢。

在 Windows 上需要对 Oracle Home 做的准备及前提条件

在 Microsoft Windows 平台上升级数据库之前,必须满足以下这些前提条件。基于安全的考虑,为不同的 Oracle Home 配置的不同的 Microsoft Windows 用户,不允许共享同一个 Oracle Base。

  • 数据库升级对于源数据库和目标数据库的 ORACLE_HOME 使用相同的 Windows 用户是支持的。
  • 数据库升级对于源数据库使用 Windows 自带账户是支持的。Oracle Database 12c 之前的版本 (release 11.2 或者之前的版本) 在 Windows 上只支持使用 Windows 自带的用户来作为 Oracle Home 用户。
  • Oracle home 用户可能无法访问在它自己的 Oracle Base 和 Oracle home 之外的文件。如果出现这样的情况,那么在升级时选择不同的 Oracle Base,可能会出现 Oracle 数据库服务无法访问旧 Oracle Base 的情况。使用 DBUA 升级,需要确保 Oracle Home 用户可以访问它自己的 Oracle Base 和 Oracle Home 之外的文件。

升级前步骤

FILE|TERMINAL - 使用 FILE 选项把脚本输出定向到一个文件。使用 TERMINAL 选项把脚本输出打印到屏幕上。如果没有指定,那么默认是 FILE 选项。
TEXT - 使用这个选项来指定输出文件是 Text 格式的。使用 XML 选项则输出为 XML 格式。如果不指定,默认是 Text。
DIR - Log 会创建在指定的目录里。如果不指定那么会创建 log 到默认的路径:如果定义了 ORACLE_BASE 环境变量,那么日志创建在 $ORACLE_BASE/cfgtoollogs//preupgrade/ 否则会创建在 $ORACLE_HOME/cfgtoollogs/db_name/preupgrade/。

比如

源 Oracle Home : /u01/app/oracle/product/12.1.0.1/dbgome_1

目标 Oracle Home : /u01/app/oracle/product/12.2.0.1/dbhome_1

检查 preupgrade.log 文件并且实施推荐。

最新的12.2的 preupgrade 工具可以从下面的文档找到:

How to Download and Run Oracle's Database Pre-Upgrade Utility (Doc ID 884522.1)

检查源库中失效的组件及对象:

如果发现了任何无效组件或者对象,则执行下面的操作以使数据库中的无效对象变为有效:

收集优化器统计信息来减少数据库停机时间

Oracle 强烈推荐升级前收集统计信息。Oracle 推荐使用 DBMS_STATS.GATHER_DICTIONARY_STATS 来收集统计信息,比如,执行下面的 SQL:

确认升级前物化视图刷新都已经完成

用下面的语句检查当前是否有物化视图正在刷新。在升级数据库前,我们需要确认所有的物化视图都已经完成了刷新。

How to Handle Materialized Views When You Upgrade or Clone a Database (Doc ID 1406586.1)

检查 TIMESTAMP WITH TIMEZONE 类型的数据类型

Oracle Database 12c release 2 (12.2) 自带的 time zone 文件版本是 26。

情况1 源数据库的 Timezone 版本等于或者小于 26。

如果源数据库的 Timezone 版本小于 26,那么不需要打 DST 补丁到源数据库 Home 或者目标 12cR2 home。

情况2 源数据库的 Timezone 版本高于 26。

如果源数据库的 Timezone 版本高于 26,升级前必须打补丁把目标 12cR2 $ORACLE_HOME 升级到源库的 Timezone 版本一致。

确保没有数据文件需要介质恢复(media recovery)或处于备份的状态

执行下面的语句检查备份的状态:

确保没有文件需要介质恢复:

升级前清空回收站

DBUA 也可以清空回收站。

从 12.2 开始,升级前可以不禁用 Oracle Database Vault 功能

从 Oracle Database 12c release 2 (12.2) 开始,升级前可以不禁用 Oracle Database Vault 功能。不过,如果在升级前禁用了 Oracle Database Vault,那么升级后需要手工启用它。
从 Oracle Database 12c release 2 (12.2) 开始,如果启用了 Database Vault,那么可以不禁用 Database Vault 功能就升级数据库。在升级后,Oracle Database Vault 默认启用并使用升级前的设置。不过,如果在升级前禁用了 Oracle Database Vault,那么升级后需要手工启用它。

可以使用 dvsys.dbms_macadm.enable_dv() 在升级后启用 Oracle Database Vault(请确认您确实需要启用 Database Vault)。使用拥有 DV_OWNER 或者 DV_ADMIN 权限来执行。执行这个 procedure 后需要重新启动数据库实例来让设置生效。

使用了 Oracle Label Security 和 Oracle Database Vault 的低于 12.1 版本的数据库升级前准备工作

如果使用了 Oracle Label Security 或者 Oracle Database Vault,在开始升级前需要完成下面的步骤。

Audit Table 升级前准备

如果要升级使用了 Oracle Label Security (OLS) 和 Oracle Database Vault 的低于 12.1 版本的数据库,必须运行 OLS preprocess 脚本, olspreupgrade.sql,来处理 aud$ 表的内容。它会把 AUD$ 从 SYSTEM 用户迁移到 SYS 用户下。

在 Oracle Database Release 11.2 上运行 olspreupgrade.sql

如果要升级的低版本源库安装了 Oracle Label Security,那么必须运行 OLS preprocess olspreupgrade.sql 脚本。

如果在你的 11.2 数据库上没有安装 Oracle Database Vault,那么可以忽略这个部分的第2,3,6和7步骤。

升级前在 11.2 数据库上执行 OLS preprocess 脚本:

  1. 从 12.2 的 $ORACLE_HOME/rdbms/admin 下拷贝 olspreupgrde.sql 脚本到源库的 $ORACLE_HOME/rdbms/admin 下。
  2. 启动 SQL*Plus 并以 DVOWNER 登录到要升级的数据库。
  3. 执行下面的SQL:
    SQL> GRANT DV_PATCH_ADMIN to SYS;
  4. 使用 SYS as SYSDBA 登陆数据库:
    CONNECT SYS AS SYSDBA
  5. 执行 OLS preprocess 脚本:
    ORACLE_HOME/rdbms/admin/olspreupgrade.sql
    在执行 OLS preprocess 脚本前,仍然可以运行应用。
  6. 在 olspreupgrade.sql 执行完毕后启动 SQL*Plus 并以 DVOWNER 登陆数据库。
  7. 执行下面的 SQL:
    SQL> REVOKE DV_PATCH_ADMIN from SYS;

备份 Oracle EM DB Control 配置及数据 (对于低于 12.1 版本的数据库)

如果在升级数据库到 12.2 版本后,有需要再降级,那么我们必须在升级前使用 emdwgrd 工具备份 Database Control 的文件,这样在降级后可以恢复这些文件。

备份数据的步骤:

  1. 安装 12.2 的数据库软件。
  2. 设置 ORACLE_HOME 到旧的数据库版本。
  3. 设置 ORACLE_SID 为要升级的数据库 SID。
  4. 设置 PATH, LD_LIBRARY_PATH 和 SHLIB_PATH 到旧的 ORACLE_HOME 相关的目录下。
  5. 切换目录到目标数据库软件。
  6. 执行 emdwgrd。
单实例数据库:

Oracle Real Application Clusters (Oracle RAC) 数据库:

需要跨节点远程拷贝。定义一个环境变量 EM_REMCP 来指向远程拷贝的命令,比如: export EM_REMCP /usr/bin/scp

  1. 提供要升级的数据库的 SYS 密码。

使用 emremove.sql 删除 DB control (对于低于 12.1 版本的数据库)

警告:
下面的步骤不能在升级 Enterprise Manager Cloud Control Repository 时操作,否则 EM Cloud Control Repository 数据库会不可用。

从 Oracle Database 12c 版本1 (12.1) 开始,作为升级的一部分,DB Control 会被删除。

停掉 DB Console:

emctl stop dbconsole

执行 emremove.sql 脚本。脚本存放在目标 12.2 的 OH/rdbms/admin/。

在 emremove.sql 执行完毕后,必须从系统中手工删除 ORACLE_HOME/HOSTNAME_SID 和 ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID 目录。

如果已经安装了 Oracle Spatial (SDO),那么需要应用 merge patch 29715479

如果源库上已经安装了 Oracle Spatial (SDO),那么需要在升级前在目标 HOME 上安装 merge patch 29715479 来避免在以下文档中提到的问题:
Doc ID 2547520.1 - SDO_GEOMETRY Type Body becomes Invalid after Upgrade to 12.2.0.1

不再支持 OLAP Catalog (OLAP AMD) (对于低于 12.1 版本的数据)

从 Oracle Database 12c 开始,OLAP Catalog (OLAP AMD) 不再支持,并且作为升级的一部分会从数据库中删除。为了减少停机时间,也可以在升级前手工删除它。可以执行脚本 $ORACLE_HOME/olap/admin/catnoamd.sql 来删除它。请不要在 UPGRADE 模式下执行这个脚本。

删除 JSON-Enabled Context search 索引 (仅适用于从 12.1 升级到 12.2)

如果源库版本为 12.1 并且创建了 JSON search index 那么 Oracle 推荐在升级前先删除这些索引。

如果源库是 12.1.0.2 并且创建了 JSON search index,Oracle 推荐删除索引并在升级后再使用 CREATE SEARCH INDEX 语句创建新的 search index。更详细的信息请参照 "Oracle Database JSON Developer’s Guide"。

复制 Transparent Encryption Oracle 钱包

如果使用了带 Oracle 钱包的 Transparent Data Encryption (TDE),并且要使用 Database Upgrade Assistant (DBUA) 来升级数据库,那么拷贝 thesqlnet.ora 和 wallet 文件到新的 12.2 Oracle home。

在升级前需要手工拷贝 sqlnet.ora 和 wallet 文件。

  1. 以授权用户身份登录。

  2. 手工拷贝 sqlnet.ora,wallet 文件以及 ewallet.p12,到新的 Oracle home。

  3. 以 mount 模式打开数据库 wallet。

例如:
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN;

检查用户使用了大小写敏感的密码的版本

以管理员账号登陆 SQL*Plus 并执行下面的语句
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

如果存在 10g 版本的密码,建议参照 Oracle 文档来解决 10g 版本问题,否则,升级完成后用户会被锁。

关于密码大小写敏感

确保没有设置废弃的参数 SEC_CASE_SENSITIVE_LOGON 为 FALSE。

使用了 Oracle Warehouse Builder (OWB) 组件的数据库的升级前要求

11.2.0.3 之前版本的 OWB 无法和 Oracle Database 12c 兼容。安装 Oracle Database 12c 软件并不会安装 OWB 组件。之前版本的 OWB 组件在升级过程中并不会被升级。但是你仍然可以在 Oracle Database 12c 上使用 11.2.0.3 版本的OWB。

启动 DBUA

在所有前提检查都完成后,执行 DBUA,请确保所有的环境变量都指向目标 12.2 home。

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

取消ORACLE_PATH的参数设置,如果已经设置了的话

DBUA (步骤1)

我们需要选择要升级到 12.2.0.1 的数据库的 SID,在这个例子里是 orcl。

image-20211206162144991

DBUA (步骤2)

在下面的屏幕上,DBUA 会做很多检查。它会执行 preupgrade 脚本(preupgrade.jar) 并且返回警告/错误。

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA (步骤3)

一旦 pre-upgrade 警告被解决,下一个屏幕会显示各种选项,比如启用并行升级,在升级后的步骤里重新编译失效对象,升级 timezone,升级前收集统计信息,在升级中把用户表空间设置为只读。当然,您也可以指定在升级前后要执行的任何SQL脚本。

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA (步骤4)

在选择选项之后,下个屏幕会显示一些 Recovery 选项,比如在哪里创建 Guaranteed Restore Point,或者做一个 RMAN 备份或者已经在 DBUA 外创建了备份,那么"I have my own backup and restore stratege "选项可以被选中。

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA (步骤5)

在这个屏幕里,可以创建一个新的监听或者升级当前的监听到 12.2.0.1。

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA (步骤6)

这个屏幕来配置 EM express 或者注册这个数据库到 EM Cloud control。

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA (步骤7)

这是在真正的升级开始前显示的汇总页面,点击 Finish 来开始真正的升级。

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA (步骤8)

DBUA 开始升级 orcl 数据库,它会完成升级前步骤,升级以及升级后步骤。

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA 会升级数据库的组件比如 Oracle server, XML等。

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA (步骤9)

最后的屏幕会显示升级的结果。

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

它会显示日志的路径以及升级的各个步骤花费的时间。

升级后步骤

执行 dbupgdiag.sql 脚本来验证对象以及组件的状态 。 如果存在失效对象,那么运行 utlrp.sql 来编译所有的失效对象:

已知问题

下面是已知问题:

Doc ID 2279575.1 - Upgrade to 12.2.0.1 Fails With Error "ORA-00001: Unique Constraint (SYS.I_DAM_LAST_ARCH_TS$) Violated"

Complete Checklist for Upgrading to Oracle Database 12c Release 2 (12.2) using DBUA (Doc ID 2189854.1)

APPLIES TO:

Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.2.0.1 [Release 11.2 to 12.2]
Oracle Database Backup Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

PURPOSE

The purpose of this article is to perform the upgrade of the pre-12c database using DBUA to 12.2.0.1.

SCOPE

DBA, Support

DETAILS

Database Upgrade Assistant (DBUA)

  • Database Upgrade Assistant (DBUA) interactively steps you through the upgrade process. configures the database for the new Oracle Database 12c release 2. It is the recommended method for performing a major release upgrade or patchset release upgrade.

  • DBUA automates the upgrade process by performing all of the tasks. DBUA makes appropriate recommendations for configuration options and then you can act on these recommendations.

  • DBUA provides support for Oracle Real Application Clusters (Oracle RAC) databases. In an Oracle RAC environment, DBUA upgrade all the database and configuration files on all nodes in the cluster.

  • DBUA, graphical user interface must be invoked within the new Oracle home where the Oracle Database 12c R2 software has been installed.
    For windows, Only an Administrator or Installed owner should invoke DBUA for Windows systems.

  • DBUA starts the Pre-Upgrade Tool, which automatically fixes some configuration settings to the values required for the upgrade. For example, the Pre-Upgrade Tool can change initialization parameters to values required for the upgrade. The Pre-Upgrade Tool also provides you with a list of items that you need to fix manually before you can continue with the upgrade.

  • It also gives certain recommendations on certain areas belonging to the database. The recommendations can then be acted on making the upgrade process user friendly and easy.

  • Once, you address / fix the pre-upgrade recommendation / warnings /errors and continue with the upgrade, DBUYA shows the progress of the upgrade for each component of source database.

  • As with previous releases of DBUA, 12c DBUA restricts the carry over of hidden parameters since Oracle recommends not to have hidden parameters other than those suggested via support during the upgrade.
    To view existing hidden parameters execute the following command while connected AS SYSDBA:
    SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '_%' ESCAPE '\';

  • DBUA performs some of the checks before actually starting the database upgrade. Some of the checks can be done manually to reduce downtime for the upgrade.

  • DBUA provides below options:

    - Upgrade timezone. The default timezone vetrsion in 12.2.0.1 is 26.

    - Gather dictionary statistics before upgrade.

    - Make user tablespaces read only.

    - Take RMAN backup before upgrade.

    - Restore database backup to rollback upgrade

    - Option to execute Custom scripts before and after upgrade

    - show the location of DBUA logs and Alert log files.

    - Option to upgrade existing listener to 12c home or create a new listener in 12.2 target home.

  • Starting with Oracle Database 12c release 2 (12.2), you can upgrade the database without disabling Oracle Database Vault. However, if you disabled Oracle Database Vault, then you must enable it manually after an upgrade.

Upgrade Path / Compatibility Matrix for 12.2 Oracle Database.

DBUA can upgrade only supported versions of direct upgrade.

Direct Upgrade to 12.2:

Source DatabaseTarget Database
11.2.0.3 and higher12.2.0.x
12.1.0.x (12.1.0.1 - 12.1.0.2)12.2.0.x

Indirect Upgrade to 12.2:

Source DatabaseUpgrade PathTarget Database
7.3.3 (lower)7.3.4 --> 9.2.0.8 --> 11.2.0.3 or higher12.2.0.x
8.0.5 ( or lower )8.0.6 --> 9.2.0.8 --> 11.2.0.3 or higher12.2.0.x
8.1.7 ( or lower )8.1.7 --> 9.2.0.8 --> 11.2.0.3 or higher12.2.0.x
9.0.1.3 ( or lower )9.0.1.3 --> 9.2.0.8 --> 11.2.0.3 or higher12.2.0.x
9.2.0.7 ( or lower )9.2.0.7 --> 9.2.0.8 --> 11.2.0.3 or higher12.2.0.x
10.2.0.4 ( or lower )10.2.0.4 --> 11.2.0.3 or higher12.2.0.x
11.1.0.611.1.0.6 --> 11.2.0.3 or higher12.2.0.x
11.2.0.111.2.0.1 --> 11.2.0.3 or higher12.2.0.x

Requirements and Recommendation for source database.

  • Ensure that all database components/objects provided by Oracle are VALID in the source database prior to starting the upgrade.

  • Before you start an upgrade or downgrade process, Oracle strongly recommends that you update both your earlier release and your new release (12.2) Oracle Database to the latest Oracle bundle patch or patch set update (BP or PSU).

  • Ensure that you do not have duplicate objects in the SYS and SYSTEM schema. For 1 and 2 refer to:

    Doc Id 556610.1 - Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

    dbupgdiag.sql script is a set of sql statements intended to provide a user friendly output to diagnose the status of the database either before (or) after upgrade. The script will create a output file called db_upgdiag_.log

  • Make sure to have a valid backup of source database prior to upgrade.

  • Disable any custom triggers that would get executed before / after DDL statements. Re-enable after the upgrade.

  • Check the database server upgrade/downgrade compatibility matrix before upgrading the database.

  • Set Archive Log ON during upgrade. Oracle recommends that you set Archive Log ON in order for DBUA to create and update the log file for the upgrade process.

  • For Oracle RAC, if you upgrade a cluster database using DBUA, then you must leave the CLUSTER_DATABASE initialization parameter set to TRUE.

    本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
  • Ensure to run the pre-upgrade utility prior to upgrading the database.
    Examine and follow the recommendation given in the preupgrade log file.

  • Materialized views in source database should be stopped before upgrade
    Doc ID 1406586.1 - How to Handle Materialized Views When You Upgrade or Clone a Database

  • Disable scheduled database custom jobs / cron jobs.

  • IF APEX is installed then it is recommended to upgrade APEX in the source DB first before upgrading DB.

Requirements and Recommendations for Target database

  • Verify whether your operating system / platform is certified for 12.2 release.
  • Download and Install Oracle 12c Release 2 (12.2.0.1) in a new Oracle_Home and make sure there are no binary relinking errors.
  • Download and Install the latest available Patch Set Update (PSU) or Bundle Patch (BP) (If available) from My Oracle Support (MOS).
  • Make sure to set the ORACLE_HOME, PATH, LD_LIBRARY_PATH, LIBPATH etc. to 12.2 target home.
  • Apply patch 29213893 on target ORACLE_HOME to avoid ORA-01422 error - refer: Database Upgrade to 12.2, 18c, 19c fails with ORA-01422, ORA-06512 for SYS.DBMS_STATS (Doc ID 2525596.1)

Recommendation for Oracle Spatial Component:

Execute the below query on source database prior to 12.2 upgrade:
col owner format a15
col table_name format a30
col column_name format a30
SELECT owner,table_name,column_name
FROM dba_tab_columns
WHERE data_type = 'SDO_GEOMETRY'
AND owner != 'MDSYS' ORDER BY 1,2,3;

If the above query return row(s) then below patch need to be applied on target 12.2 Oracle Home before performing the database upgrade.

Link : https://updates.oracle.com/Orion/Download/process_form/p25293022_122010_Generic.zip?file_id=95607293&aru=21422588
FileName : p25293022_122010_Generic.zip

If the patch apply will be skipped on target 12.2 home then utlrp.sql script during post upgrade phase may take more time to complete and could impact the outage.

Prerequisites for Preparing Oracle Home on Windows

Your system must meet these requirements before you can upgrade Oracle Database on Microsoft Windows platforms. For security reasons, different Microsoft Windows user accounts configured as Oracle
home users for different Oracle homes are not allowed to share the same Oracle Base.

  • Database upgrade is supported when the same Windows user account is used as the Oracle home user in both the source and destination Oracle homes.
  • Database upgrade is supported when the Oracle home from which the database is being upgraded uses the Windows Built-in Account. Releases earlier than Oracle Database 12c (release 11.2 and earlier) only supported the built-in account option for the Oracle home user on Windows.
  • The Oracle home user may not have access to files outside its own Oracle Base and Oracle home. If that is the case, then if you choose a different Oracle Base during upgrade, it is possible that Oracle Database services cannot access files in the older Oracle Base. Using DBUA for database upgrade ensures that the Oracle home user has access to files outside of its own Oracle Base and its own Oracle home.

Pre-Upgrade

$Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar [FILE|TERMINAL] [TEXT|XML] [DIR output_dir]

FILE|TERMINAL - Use this option to direct script output to a file. Use TERMINAL to direct output to the terminal. If it is not specified then defaultr is FILE.
TEXT - Use this option to specify log should be in Text format. Use TEXT to specify text output. Use XML to specify XML output. If you do not specify an output type, then the default is text.
DIR - Logs will be created under . Directs the output to a specific directory. If you do not specify an output directory with the DIR option, then the output is directed to one of the default locations: If you define ORACLE_BASE environment variable then the generated scripts and log files will be created under $ORACLE_BASE/cfgtoollogs//preupgrade/ location else it will create under $ORACLE_HOME/cfgtoollogs/db_name/preupgrade/.

For example,

Examine the preupgrade.log file and follow the recommendation.

The latest preupgrade utility for 12.2 can be found from :

How to Download and Run Oracle's Database Pre-Upgrade Utility (Doc ID 884522.1)

Check for Invalid Objects / Components:

If you find invalid objects and/or database components then try to VALIDATE the invalid objects and/or database components by executing the following steps:

Run $ORACLE_HOME/rdbms/admin/utlrp.sql to validate the invalid objects in the database. You can execute the utlrp.sql scripts multiple times to validate the invalid objects.

Gathering Optimizer Statistics to Decrease Oracle Database Downtime

Oracle strongly recommends that you use this procedure to gather statistics before performing Oracle Database upgrades.Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, enter the following SQL statement:

Verifying Materialized View Refreshes are Complete Before Upgrade

Use this procedure to query the system to determine if there are any materialized view refreshes still in progress. Before upgrading Oracle Database, you must wait until all materialized views have
completed refreshing.

How to Handle Materialized Views When You Upgrade or Clone a Database (Doc ID 1406586.1)

Check of TIMESTAMP WITH TIMEZONE Datatype

The time zone files that are supplied with Oracle Database 12c release 2 (12.2) is version 26.

Case 1 Timezone version of source database is lower or equal 26.

If the source database is using a timezone file lower than version 26 then there is no DST patch to apply in source oracle home or target 12cR2 home.

Case 2 Timezone version of source database is higher than 26.

If the source database uses a Timezone version higher than 26 then BEFORE the upgrade you MUST patch the target 12cR2 $ORACLE_HOME with a timezone data file of the SAME version as the one used in the source release database.

Ensuring That No Files Are in Backup Mode and no files need media recovery Before Upgrading

Execute below query to check for the status of the backup:

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

Ensure that no files require media recovery:

Purging Recycle Bin before upgrade

we can purge the recycle bin via DBUA.

Starting with Oracle Database 12c release 2 (12.2), you can upgrade the database without disabling Oracle Database Vault.

Starting with Oracle Database 12c release 2 (12.2), you can upgrade the database without disabling Oracle Database Vault. However, if you disabled Oracle Database Vault, then you must enable it manually after an upgrade.
Starting with Oracle Database 12c release 2 (12.2), if you have Oracle Database Vault enabled, then you can upgrade the Oracle Database without first disabling Oracle Database Vault. After the upgrade, Oracle Database Vault is enabled by default with the enforcement settings that you had in place before the upgrade. If you manually disable Oracle Database Vault before the upgrade, then you must enable Oracle Database Vault manually after the upgrade.

Enable Oracle Database Vault in the upgraded database by using the procedure dvsys.dbms_macadm.enable_dv(). Run this procedure with a user account that is granted either DV_OWNER or DV_ADMIN. After you run the procedure, restart the database instance so that the procedure takes effect.

Requirements for Upgrading Databases That Use Oracle Label Security and Oracle Database Vault (For Oracle Database releases earlier than 12.1)

You must complete these tasks before starting an upgrade with a database using Oracle Label Security or Oracle Database Vault.

Audit Table Preupgrade

If you are upgrading from a database earlier than Oracle Database release 12.1 that uses Oracle Label Security (OLS) and Oracle Database Vault, then you must first run the OLS preprocess script, olspreupgrade.sql, to process the aud$ table contents. The OLS upgrade moves the aud$ table from the SYSTEM schema to the SYS schema. The olspreupgrade.sql script is a preprocessing script required for this move.

Running olspreupgrade.sql on Oracle Database Release 11.2

If Oracle Label Security is installed in the earlier release that you are upgrading, then you must run the OLS preprocess olspreupgrade.sql script.

If Oracle Database Vault is not installed with your release 11.2 database, then you can skip steps 2, 3, 6, and 7 in this section.

To run the OLS preprocess script on a release 11.2 database before upgrading:

\1. Copy the ORACLE_HOME/rdbms/admin/olspreupgrade.sql script from the newly installed Oracle home to the Oracle home of the database that you want to upgrade.
\2. Start SQLPlus and connect as DVOWNER to the database that you want to upgrade.
\3. Run the following statement:
SQL> GRANT DV_PATCH_ADMIN to SYS;
\4. At the system prompt, connect SYS as SYSDBA:
CONNECT SYS AS SYSDBA
\5. Run the OLS preprocess script:
ORACLE_HOME/rdbms/admin/olspreupgrade.sql
You may continue to run your applications on the database while the OLS preprocess script is running.
\6. After the olspreupgrade.sql completes its run successfully, start SQL
Plus and connect to the database as DVOWNER.
\7. Run the following SQL statement:
SQL> REVOKE DV_PATCH_ADMIN from SYS;

Save OEM DB Control Configuration (For Oracle Database releases earlier than 12.1)

If you plan to downgrade database after upgrading to 12.2, then before starting the upgrade save the DB Control configuration and data using emdwgrd utility, so that you can restore the files after downgrading.

Steps to Save data:

\1. Install the software for the new Oracle Database 12c release.
\2. Set ORACLE_HOME to your old Oracle home.
\3. Set ORACLE_SID to the SID of the database being upgraded.
\4. Set PATH, LD_LIBRARY_PATH, and SHLIB_PATH to point to the upgraded Oracle Database Oracle home.
\5. Change directory to the new Oracle Database release Oracle home.
\6. Run emdwgrd

Single-instance databases:

emdwgrd[sh|bat] -save -sid old_SID -path save_directory

Oracle Real Application Clusters (Oracle RAC) databases:

Remote copy must be enabled across all cluster member nodes. Use EM_REMCP environment variable, to indicate which remote copy is configured, for example: export EM_REMCP /usr/bin/scp

emdwgrd -save -cluster -sid old_SID -path save_directory

\7. Enter the SYS password for the database that you want to upgrade.

Removing DB Control with emremove.sql (For Oracle Database releases earlier than 12.1)

Warning :
The below step should not be executed during the upgrade of Enterprise Manager Cloud Control Repository database. Doing so your EM Cloud Control Repository DB will become unusable.


Starting with Oracle Database 12c release 1 (12.1), DB Control is removed as part of the upgrade process.

Stop DB Console:

emctl stop dbconsole

Execute emremove.sql script. The script willbe located in target 12.2 OH/rdbms/admin/.

SQL>SET ECHO ON
SQL>SET SERVEROUTPUT ON
SQL>@emremove.sql >> Script located in new 12c ORACLE_HOME/rdbms/admin

After emremove.sql completes, you must manually remove ORACLE_HOME/HOSTNAME_SID and ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID directories from your file system.

If Oracle Spatial (SDO) is installed, then apply the merge patch 29715479

If Oracle Spatial (SDO) compoent is installed in source database, then apply the merge patch 29715479 against the target 12.2.0.1 ORACLE_HOME before the upgrade to avoid the issue reported in below document:
Doc ID 2547520.1 - SDO_GEOMETRY Type Body becomes Invalid after Upgrade to 12.2.0.1

Desupport of OLAP Catalog (OLAP AMD) (For Oracle Database releases earlier than 12.1)

Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is desupported. The OLAP Catalog (OLAP AMD) will be marked as OPTION OFF after the database upgrade.

This step can be manually performed before the upgrade to reduce downtime. It can be removed by running the SQL script $ORACLE_HOME/olap/admin/catnoamd.sql script. Do not run the script in UPGRADE mode.

Drop JSON-Enabled Context search Indexes (Only for the upgrade of 12.1 DB to 12.2)

If you are upgrading from Oracle Database 12c release 1 (12.1) to 12c release 2 (12.2), then Oracle recommends that you drop the JSON-enabled context index before upgrading.
Oracle recommends that you drop your existing JSON-enabled index.

If you created a JSON search index using Oracle Database 12c Release 1 (12.1.0.2) then Oracle recommends that you drop that index and create a new search index for use with later releases, using CREATE SEARCH INDEX statement. Please refer "Oracle Database JSON Developer’s Guide" for more details.

Copying Transparent Encryption Oracle Wallets

If you use Oracle wallet with Transparent Data Encryption (TDE), and you use Database Upgrade Assistant (DBUA) to upgrade the database, then copy thesqlnet.ora and wallet file to the new 12.2 Oracle home.

You must copy the sqlnet.ora and the wallet file manually before starting the upgrade.
\1. Log in as an authorized user.
\2. Manually copy the sqlnet.ora file, and the wallet file, ewallet.p12, to the new release Oracle home.
\3. Open the Oracle wallet in mount.

For example:

Check the accounts use Case-Insensitive password version

Log in to SQL*Plus as an administrative user, and enter the following SQL query
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

If there are any 10g versions, suggest you to refer Oracle documentation to fix 10g versions, failing to do so, user accounts with LOCKED after upgrade is completed.

About Password Case Sensitivity

Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE.

Requirement for Upgrading Databases that Use Oracle Warehouse Builder (OWB)

OWB releases earlier than release 11.2.0.3 do not work with Oracle Database 12c. OWB is not installed as part of the software for Oracle Database 12c, and OWB components that may exist in earlier releases are not upgraded as part of the Oracle Database upgrade process. However, you can use OWB release 11.2.0.3 with Oracle Database 12c.

Invoke DBUA

run DBUA, once all Prerequisite checks are successful. please make sure that environment variables are pointing to target 12.2 home.

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

unset the ORACLE_PATH environment variable, if it is already set.

DBUA (Step 1 of 9)

we need to choose the database SID for upgrade to 12.2.0.1. In this case, it is orcl database.

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA (step 2of 9)

In below screen, DBUA will perform various pre-checks. It will execute the preupgrade script (preupgrade.jar) and return warnings / errors.

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA (Step 3 of 9)

once the pre-upgrade warnings has been adressed, next screen will show various options like Enable Parallel Upgrade, Recompile invalid objects in post upgrade phase, upgrading timezone, gather statistics before the upgrade, setting the user tablespaces to read only during upgrade. also, you can provide any SQL scripts before or after the upgrade.

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA (Step 4 of 9)

After selecting option, The next screen will show various Recovery options where it will create Guaranteed Restore Point, or Take an RMAN backup or the backup is already taken outside DBUA then "I have my own backup and restore stratege " option can be chosen.

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA (Step 5 of 9)

in this screen, we can create a new listener or upgrade the existing listener to 12.2.0.1.

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA (Step 6 of 9)

This screen is for configure EM express or register the upgraded database with EM Cloud control.

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA (step 7 of 9)

This is the summary screen before the actual upgrade starts. Click on Finish to proceed with the upgrade.

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA (Step 8 of 9)

The DBUA will start the upgrade process of orcl database. It will perform pre-upgrade, database upgrade and post upgrade steps.

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA will perform the upgrade of database components like Oracle server, XML etc.

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

DBUA Step 9 of 9)

The final screen will show the result of upgrade.

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

It will show the log file location and the time taken by various steps of upgrade.

使用 DBUA 升级数据库到 Database 12c 版本2(12.2)的完整核对清单 (Doc ID 2364820.1、2189854.1)

Post-Upgrade

Execute dbupgdiag.sql script to verify status of objects and components. If there are invalid objects then run utlrp.sql to recompile the invalid objects as follows:

Known Issues

Below are the known issues:

Doc ID 2279575.1 - Upgrade to 12.2.0.1 Fails With Error "ORA-00001: Unique Constraint (SYS.I_DAM_LAST_ARCH_TS$) Violated"

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

19 + 13 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部