Oracle DB 18c - 手动升级到 18c 的完整核对清单 (文档 ID 2469647.1)

Oracle Database Backup Service - 版本 N/A 和更高版本
Oracle Database Exadata Express Cloud Service - 版本 N/A 和更高版本
Oracle Database Cloud Service - 版本 N/A 和更高版本
Oracle Database - Standard Edition - 版本 11.2.0.3 到 18.1.0.0.0 [发行版 11.2 到 18]
Oracle Database Cloud Schema Service - 版本 N/A 和更高版本
本文档所含信息适用于所有平台

用途

本文档可用作手工将 Oracle 11gR2 (11.2) 或者 Oracle 12c Release 1 (12.1) 或者 Oracle 12c Release 2 (12.2) 版本数据库升级至 Oracle 18c 版本数据库的指南与核对表。

适用范围

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

详细信息

步骤 1: 升级到数据库 18.1 的升级路径

能够直接升级到 Oracle 18.1 的数据库最小版本

升级矩阵
源数据库	目标数据库
11.2.0.3/11.2.0.4	18.1
12.1.0.1/12.1.0.2	18.1
12.2.0.1	18.1

以下的数据库版本需要间接升级

间接升级矩阵
源数据库		升级路径		目标数据库
11.2.0.1/11.2.0.2	-->	11.2.0.3/11.2.0.4	-->	18.1
11.1.0.6/11.1.0.7	-->	11.2.0.3/11.2.0.4	-->	18.1
10.2.0.2, 10.2.0.3, 10.2.0.4, 10.2.0.5	-->	11.2.0.3/11.2.0.4/12.1.0.1/12.1.0.2	-->	18.1
10.1.0.5	-->	11.2.0.3/11.2.0.4/12.1.0.1/12.1.0.2	-->	18.1
9.2.0.8 或更低版本	-->	11.2.0.3/11.2.0.4	-->	18.1

对于任何多步骤的升级，因为必须要升级两次，所以需要运行 preupgrade 脚本两次：首先，对于中间升级版本运行脚本一次，之后，对于最终升级到的版本运行脚本一次。比如，如果要升级的数据库是Oracle Database 10g，那么按照下面的步骤：

按照 Oracle Database Upgrade Guide 12c Release 1 (12.1) 的步骤升级 10.2.0.5 到 12.1.0.2，包括为 12.1.0.2 运行 pre-upgrade 脚本。
直接升级 Oracle Database 12c release 1 (12.1.0.2) 到 Oracle Database 18c。按照Oracle Database Upgrade Guide的说明，包括为 18.1 运行 preupgrade 脚本。

如果您打算使用Data Pump export/import来升级，那么这个限制就不存在了。

比如:

如果您要升级的数据库当前是 11.2.0.2 或者 11.1.0.7，那么您必须先要升级到 Oracle Database 11g release 2 (11.2.0.3)。
如果您要升级的数据库当前是 10.2.0.2, 10.2.0.3, 10.2.0.4,10.2.0.5 或者 10.1.0.5，那么您先要升级到版本 11.2. 或者 12.1
如果您要升级的数据库当前是 9.2.0.8, 那么您必须先要升级到一个中间版本:
从 9.2.0.8 升级到 11.2.0.3 或者 11.2.0.4，之后再从11.2升级到18c。

步骤 2: 推荐/需要在源库上完成的

对源库做备份，冷备份或热备份都可以。
禁用所有自定义的 before/after DDL 类型的触发器，完成升级后再启用它们。
在 11g 数据库上定义的 Data security roles 不能自动转换成 ORAS。所以在升级前，需要删除所有在 11g 数据库上定义的 data security roles。升级后可以使用 Analytic Workspace Manager 12c 重新定义 data security roles。
如果从 11g 升级到 12c 之前未删除 data security roles，那么所有的 data security policies 以及 data security role 都会在 12c 上失效。
Timezone 版本应当小于等于目标数据库的 Timezone 版本。
如果源库上已经安装了 APEX 组件，那么 升级数据库前需要先在源库上升级 APEX 组件。
源库中没有失效的对象/组件
如果您当前的数据库是32位的，那么升级后数据库会被自动转换成64位的
升级前执行 Preupgrade 脚本并检查 preupgrade 日志。
执行 dbupgdiag.sql（可以从 Note 556610.1 下载这个脚本），并且确认是否有 SYS/SYSTEM 用户下的失效对象或者失效组件。如果存在的话, 那么需要在升级前解决这些问题。你可以多次执行 utlrp.sql 来解决问题。如果在这样做之后仍然存在失效对象，那么开一个 SR 来解决这个问题。
多次执行脚本 utlrp.sql 确认数据库中没有失效对象。

步骤 3: 推荐/需要在目标库上完成的

需要先检查您的硬件平台/操作系统是否兼容 18.1。点击这里来确定兼容性。
安装数据库软件 18.1.0.0，并确保没有安装方面的问题。
如果有的话，下载并应用最新的 RU / RUR
从源库的 ORACLE_HOME/dbs 下拷贝 spfile 或者 pfile 到目标 ORACLE_HOME/dbs。
- 从参数文件中删除所有废弃的参数。
- 注意升级到 18.1 需要的最低的参数 COMPATIBLE 值为“11.2.0”, 确保参数 COMPATIBLE 值设置为 11.2.0 或者更高。
查看文档 "Patches to apply before upgrading Oracle GI and DB to 18c (Doc ID 2414935.1)" 给出的推荐补丁

步骤 4: 升级前检查

清理数据库

清空回收站
检查 SYS 及 SYSTEM 用户的失效对象
检查 SYS 及 SYSTEM 用户下的重复对象
检查失效的、必需的、废弃的组件

检查所有的物化视图

检查所有的物化视图的状态，刷新所有没有刷新的物化视图。
检查物化视图日志的大小，如果物化视图日志的行数非零，那么刷新物化视图。
检查 direct loader 日志以及 PMOP 日志（分区维护操作日志），如果 direct loader log 或者 PMOP 日志非空，那么刷新日志显示的物化视图。
升级数据库前，必须确保所有的物化视图都已经刷新完毕。

执行下面的 SQL 查询:

 
   SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

复制 Transparent Encryption Oracle 钱包

如果使用了带 Oracle 钱包的 Transparent Data Encryption (TDE)，那么拷贝 thesqlnet.ora 和 wallet 文件到新的Oracle home。在升级前需要手工拷贝 sqlnet.ora 和 wallet 文件。

以授权用户身份登录。
手工拷贝 sqlnet.ora，wallet 文件以及 ewallet.p12，到新的 Oracle home。

以 mount 模式打开数据库 wallet。
例如:

         SQL> STARTUP MOUNT; 
  
       SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN

理解密码大小写敏感

从 Oracle Database 12c release 2 (12.2) 开始，默认的基于密码验证的协议排除了大小写不敏感的 10g 版本的密码。默认的SQLNET.ORA文件中参数SQLNET.ALLOWED_LOGON_VERSION_SERVER被设置成了 12 （排他模式）。

为了安全起见，Oracle建议使用大小写敏感的密码验证。这是默认的设置。但是在升级数据库的时候可以短暂的关闭大小写敏感的密码验证。在升级后，可以再决定是否启用大小写敏感的密码验证。

在升级前，Oracle建议您检查是否新的密码验证会影响您的应用。可以做下面的检查：

检查是否有用户使用了 10g 大小写不敏感的密码验证方式。
检查是否使用了尚未安装 CPUOct2012 补丁的11.2.0.3或者更早版本的客户端，或者应用了这个补丁但尚未启用大小写敏感的密码版本。
确认您并未设置SEC_CASE_SENSITIVE_LOGON成FALSE。设置SEC_CASE_SENSITIVE_LOGON为FALSE就无法启用大小写敏感的密码版本了（11G和12C的密码版本）

更多信息请参考18.1 Oracle database documentation

对只读表空间升级

以 -T 参数使用 Parallel Upgrade Utility 可以在升级时把用户表空间置为只读。因为数据库可以读取之前版本创建的数据文件 header，所以在升级时我们不需要做额外的操作。当升级完成后，表空间被置为读写时，文件 header 会自动被更新。如果升级失败，无法把表空间重新 online，那么检查升级日志。日志中包含把表空间重新 online 的语句。可以在数据库中或者每个pdb里手工执行来 online 表空间。

在升级日志文件中找到表空间相关的命令

如果升级失败可以检查升级的日志 (Oracle_base/cfgtoologs/dbua)，并且手工执行日志中的命令来 online 表空间。可以检查如下日志：

Non-CDB升级 : catupgrd0.log
PDB 数据库 : catupgrdpdbname0.log, 这里 pdbname 是要升级的pdb的名字。

在每个日志文件的开始部分，可以找到把表空间置为只读的命令

   SQL> ALTER TABLESPACE <Tablespace Name> READ ONLY; 
  
 Tablespace altered.

而在每个日志文件的结尾部分，可以找到把表空间置为读写的SQL命令：

   SQL> ALTER TABLESPACE <Tablespace Name> READ WRITE; 
  
 Tablespace altered.

为升级新的Oracle Home做准备

从要升级的数据库 Home 拷贝配置文件到新的版本的Oracle Home中。
如果您有一个 password 文件，那么把它从旧的 Oracle home 拷贝到新的 Oracle home。
从参数文件中删除所有废弃的参数。在新的版本的数据库里有一些参数已经被废弃。从要启动新版本的数据库的参数文件中删除所有被废弃的参数，否则会在启动时产生错误。同时，修改那些在新版本里格式已经被改变的参数。
如果要升级的是集群数据库，那么需要在升级前修改参数 CLUSTER_DATABASE 为 FALSE 。

在Windows平台为升级新的Oracle Home做准备

在 Microsoft Windows 平台升级数据库前需要先确保系统已经满足升级条件。

出于安全考虑，不同的 Windows 账户配置为 Oracle home 不允许共享同一个 Oracle Base。

当源库和目标库的 Oracle home 使用同一个 Windows 账户作为 oracle home 用户，数据库升级是支持的。
数据库升级对于源数据库使用 Windows 自带账户是支持的。Oracle Database 12c 之前的版本 (release 11.2 或者之前的版本) 在 Windows 上只支持使用 Windows 自带的用户来作为 Oracle Home 用户。
Oracle home 用户可能没有权限访问自己的 Oracle Base 和 Oracle home 之外的文件。因此，如果您的升级使用不同的 Oracle Base，Oracle 数据库服务可能没有权限访问旧的 Oracle Base 下的文件。
在手工升级或者在需要访问旧的Oracle Base之外的文件（比如，wallets, 配置文件及其它文件）之前，需要确保 Oracle Home 用户可以访问这些文件；或者拷贝这些文件到新的 Oracle Base。

使用了 Oracle Label Security 和 Oracle Database Vault 的数据库

Audit Table升级及归档的要求

如果要升级的源库版本低于12.1并且安装了 Oracle Label Security和Oracle Database Vault，那么必须运行 OLS preprocess olspreupgrade.sql 脚本。

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

如果要升级的数据库低于12.1，并且使用了 Oracle Label Security (OLS) 和 Oracle Database Vault，那么在升级前运行 olspreupgrade.sql 是必须的。一旦数据库升级到了12.1，那么就不需要执行OLS preprocessing 步骤了。

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

1. 从 18.1 的 Oracle Home 下拷贝 olspreupgrde.sql 脚本到源库的 Oracle Home 下。

        ORACLE_HOME/rdbms/admin/olspreupgrade.sql
        ORACLE_HOME/rdbms/admin/emremove.sql
        ORACLE_HOME/rdbms/admin/catnoamd.sql

2. 启动 SQL*Plus 并以 DVOWNER 登录到要升级的数据库。

3. 执行下面的SQL：

       SQL> GRANT DV_PATCH_ADMIN to SYS; 
 

4. 使用 SYS as SYSDBA 登陆数据库：

       CONNECT SYS AS SYSDBA 
 

5. 执行 Data Vault preprocess 脚本：

       ORACLE_HOME/rdbms/admin/olspreupgrade.sql 
  
     ORACLE_HOME/rdbms/admin/emremove.sql 
  
     ORACLE_HOME/rdbms/admin/catnoamd.sql

6. 执行完毕后，以 DVOWNER 登陆数据库

7. 执行下面的SQL：

       SQL> REVOKE DV_PATCH_ADMIN from SYS; 
 

对于Database Vault，赋予SYS以DV_PATCH_ADMIN的角色

如果启用了Database Vault，那么也需要做对应的检查，检查步骤需要执行下面的SQL脚本 - olspreupgrade.sql, emremove.sql, catnoamd.sql

以 DVOWNER 登陆要升级的数据库

执行下面的SQL：

       SQL> GRANT DV_PATCH_ADMIN to SYS; 
 

备份数据库

建议在运行 Pre-Upgrade Information Tool 之后备份数据库。创建 guaranteed flashback restore point。测试备份，确保出现问题后有回退方案。

     rman "target / nocatalog" 

     RUN 
     { 
         ALLOCATE CHANNEL chan_name TYPE DISK; 
         BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade; 
         BACKUP CURRENT CONTROLFILE FORMAT 'controlfile location and name'; 
     } 

使用 emremove.sql 手工删除 DB control

关闭 DB control

 
   emctl stop dbconsole  
 

使用 sysdba 登陆

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

从系统中手工删除 ORACLE_HOME/HOSTNAME_SID/ and ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID 目录。
如果是 windows 系统则删除 OracleDBConsoleSID

确保升级前所有的文件都没有处于备份模式

执行下面的语句:

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

清空回收站

要清空回收站，执行下面的语句:

 
   SQL> PURGE DBA_RECYCLEBIN  
 

注意: 升级前务必清空回收站来避免 ORA-00600 错误并且减少升级时间。

性能方面

保存性能相关指标
检查网络性能
收集优化器统计信息

收集统计信息可以减少停机时间，Oracle建议使用 DBMS_STATS.GATHER_DICTIONARY_STATS 来收集这些统计信息，比如：

   SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; 
 

检查时区设置

源库的 time zone 文件版本应该小于或者等于目标库的 time zone 文件版本。如果源库的 time zone 文件版本更高，那么需要升级目标库的 time zone 文件版本来对应源库的 time zone 文件。

关于升级 Oracle OLAP Data Security Policies

在 11g 数据库上定义的 Data security roles 不能自动转换成 ORAS。所以在升级前，需要删除所有在 11g 数据库上定义的 data security roles。升级后可以使用新版本的 Analytic Workspace Manager 重新定义 data security roles。

如果从 11g 升级到 12c 之前未删除 data security roles，那么所有的 data security policies 以及 data security role 都会在新版本上失效。

步骤 5: Preupgrade 步骤

在源库执行 Preupgrade 脚本

 
   $Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar FILE TEXT DIR output_dir  
 

FILE - 使用这个参数把输出写入输出文件
TEXT - 使用这个参数指定日志格式为 TEXT 模式（如果不指定的话则为 XML 格式）
DIR - 日志会创建在<output_dir>指定的这个目录中

建议执行 pre-upgrade 的 fixup 脚本，如果发现的问题是可以使用这个脚本修复的话。

Preupgrade fixup 脚本

执行 Preupgrade fixup 脚本 preupgrade_fixups.sql

Network Utility 包的依赖关系

执行下面的语句

 
   SQL> SELECT * FROM DBA_DEPENDENCIES WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');  
 

在升级测试中，确保使用新的访问控制。在升级后确保这些包是可用的，在升级后，根据源库的使用情况赋予正确的权限。

检查 Time zone 文件版本

检查目标数据库的 time zone 文件版本是否低于源库的 time zone 文件版本，如果是的话，需要升级目标数据库的 time zone 文件版本。数据库 DST 补丁可以从 Note 412160.1 下载。

步骤 6: 升级数据库到 18c

关闭数据库

 
   SQL> SHUTDOWN IMMEDIATE  
 

Windows平台的步骤 :

如果操作系统是Windows，那么完成下面的步骤:

a. 停掉要升级的数据库 OracleServiceSID Oracle service，这里的SID是实例名。比如，如果SID是ORCL，那么执行下面的命令:

    C:\> NET STOP OracleServiceORCL 
  

b. 使用ORADIM来删除 Oracle service。请参考平台相关的文档来获取ORADIM命令的格式。
比如，如果您的SID是ORCL，那么执行下面的命令

    C:\> ORADIM -DELETE -SID ORCL 
  

c. 使用新ORACLE软件的ORADIM来创建 service。
比如:

    C:\> ORADIM -NEW -SID SID -SYSPWD PASSWORD -MAXUSERS USERS  -STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA 
  

对于 Unix/Linux

设置环境变量指向目标 ORACLE_HOME

  export ORACLE_HOME=<path to Oracle 18c>  
 export PATH=$ORACLE_HOME/bin:$PATH  
 export ORACLE_BASE=<path to Oracle_Base set during installation>  

从旧的Oracle home下拷贝 SPFILE.ORA 或者 INIT.ORA到目标Oracle home

使用目标 ORACLE_HOME（设置 ORACLE_HOME 为目标 ORACLE_HOME）启动数据库到 upgrade 模式

 
   CONNECT / AS SYSDBA  
  
   SQL> startup upgrade;  
  
   SQL> exit

在 Linux/Unix 上

  cd $ORACLE_HOME/bin  
 ./dbupgrade      
 

在 Windows 上

  cd %ORACLE_HOME%\bin  
 dbupgrade  

执行 Post-Upgrade Status 工具，utlu122s.sql 并且检查升级的日志。在新的版本下执行 Post-Upgrade Status 工具。

  $ sqlplus "/as sysdba"  
 SQL> STARTUP  
 SQL> @utlu122s.sql  

检查升级日志看是否脚本 catuppst.sql 已被执行。如果尚未执行，那么在新的 ORACLE_HOME 里手工执行。这个脚本被放置在 $ORACLE_HOME/rdbms/admin 目录。

 
   SQL> @catuppst.sql    
 

在另一个 session 里执行 utlrp.sql 来编译 stored PL/SQL 和 Java 代码。

 
   SQL> @utlrp.sql  
 

检查诊断升级/迁移相关的状态的 Oracle 数据字典。dbupgdiag.sql 脚本可以收集和升级迁移诊断信息有关的数据字典的信息，可以在升级的数据库上以 SYS 用户来执行它，关于更多信息，请参考文档 Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

如果脚本 dbupgdiag.sql 发现了失效对象，执行 $ORACLE_HOME/rdbms/admin/utlrp.sql (多次) 来使它们生效，直到失效对象的个数不再改变。之后重新执行 dbupgdiag.sql 并确保没有任何问题。

如果使用了集群，那么必须升级这个数据库的 Oracle Clusterware keys，运行 srvctl 来完成，比如：

 
   ORACLE_HOME/bin/srvctl upgrade database -db name -o ORACLE_HOME  
 

步骤 7: 升级后步骤

在 Linux 和 Unix 上设置环境变量

确保下面的环境变量指向了新的 ORACLE_HOME 对应的目录：

ORACLE_HOME
PATH

更新 oratab 文件

修改 /etc/oratab 文件对应的条目指向新的 ORACLE_HOME 目录

Post-upgrade fixup 脚本

执行 pre-upgrade 产生的 post-upgrade fixup 脚本

   SQL> @postupgrade_fixups.sql 
 

在升级数据库后升级 Recovery Catalog

如果使用的recovery catalog版本低于rman客户端的版本，我们必须升级它。可以通过命令 UPGRADE CATALOG 来升级 Recovery catalog。

关于具体的步骤信息，请参照 Upgrading the Recovery Catalog。

在升级数据库后升级 Time Zone文件版本

如果 Pre-Upgrade Information Tool 要求我们在升级数据库后升级 time zone 文件，那么使用 DBMS_DST PL/SQL package 来升级 RDBMS DST（timezone）版本

按照 Note 1509653.1 "Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST" 的 "Steps to Upgrade Time Zone File and Timestamp with Time Zone Data" 部分来进行升级

升级那些使用 DBMS_STATS 创建的统计信息表（Statistics Tables）

如果我们使用 DBMS_STATS.CREATE_STAT_TABLE 手工创建了一些统计信息表（statistics tables），那么执行下面的命令来升级这些表（如果没有创建过统计信息表，那这一步骤可以忽略）。例如：

 
   EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS', 'dictstattab');  
 

对每个统计信息表都要执行一遍上面的命令。

Oracle 18c - Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 18c (文档 ID 2418045.1)

In this Document

Purpose

Scope

Details

Step 1: Upgrade Path for 18.1 Oracle database

Minimum version of the database that can be directly upgraded to Oracle 18.1

Intermediate upgrades needs to be carried for following releases

Step 2: Requirements and recommendations for source database

Step3: Requirements and recommendations for target ORACLE_HOME

Step 4: Pre-upgrade checks

Clean up database

Check materialized views

Copying Transparent Encryption Oracle Wallets

Understanding Password Case Sensitivity

Running Upgrades with Read-Only Tablespaces

Preparing the New Oracle Home for Upgrading

Prerequisites for Preparing Oracle Home on Windows

Databases That Use Oracle Label Security and Oracle Database Vault

Backing Up Oracle Database for Upgrading

Manually remove DB control with emremove.sql

Ensure no files are in Back up mode before starting the upgrade

Purge Recycle bin

Performance

Checking Time zone settings

About Upgrading Oracle OLAP Data Security Policies

Step 5: Preupgrade step

Preupgrade fixup script

Dependencies on Network Utility Packages

Check Time zone version

Step 6: Upgrade Database to 18c

Step 7: Post-upgrade

Setting Environment variables on Linux and Unix

Update oratab entries

Post-upgrade fixup script

Recovery Catalog Upgrade

Upgrade the Time Zone File Version After Upgrading Oracle Database

Upgrading Statistics Tables

References

APPLIES TO:

Oracle Database Cloud Service - Version N/A and later
Oracle Database - Standard Edition - Version 11.2.0.3 to 18.1.0.0.0 [Release 11.2 to 18]
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 18.1.0.0.0 [Release 11.2 to 18]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Information in this document applies to any platform.

PURPOSE

This document is created for use as a guideline and checklist when manually upgrading from Oracle 11gR2 (11.2) or Oracle 12c Release 1 (12.1) or Oracle 12c Release 2 (12.2) to Oracle 18c Release

SCOPE

Database Administrators, Support

DETAILS

Step 1: Upgrade Path for 18.1 Oracle database

Minimum version of the database that can be directly upgraded to Oracle 18.1

Upgrade Matrix
Source	Target
11.2.0.3/11.2.0.4	18.1
12.1.0.1/12.1.0.2	18.1
12.2.0.1	18.1

Intermediate upgrades needs to be carried for following releases

Indirect Upgrade Matrix
Source Database		Intermediate upgrade path		Target database
11.2.0.1/11.2.0.2	-->	11.2.0.3/11.2.0.4	-->	18.1
11.1.0.6/11.1.0.7	-->	11.2.0.3/11.2.0.4	-->	18.1
10.2.0.2, 10.2.0.3, 10.2.0.4, 10.2.0.5	-->	11.2.0.3/11.2.0.4/12.1.0.1/12.1.0.2	-->	18.1
10.1.0.5	-->	11.2.0.3/11.2.0.4/12.1.0.1/12.1.0.2	-->	18.1
9.2.0.8 or earlier	-->	11.2.0.3/11.2.0.4	-->	18.1

For any multi-step upgrade, if you must carry out two upgrades to upgrade to the current release, then you must run the preupgrade script twice: First, for the intermediate upgrade release, and second, for the target upgrade target release. For example, if the database from which you are upgrading is running Oracle Database 10g, then follow these steps:

Upgrade release 10.2.0.5 to release 12.1.0.2 using the instructions in Oracle Database Upgrade Guide 12c Release 1 (12.1), including running the pre-upgrade script for 12.1.0.2.
Upgrade Oracle Database 12c release 1 (12.1.0.2) directly to Oracle Database 18c. Use the instructions in this book, Oracle Database Upgrade Guide, including running the preupgrade script for 18.1.

Upgrade to an intermediate Oracle Database release that can be directly upgraded to the current release. Upgrade Oracle Database releases that are not supported for direct upgrade in this release to an intermediate Oracle Database release that is supported for direct upgrade.

This restriction does not apply if you use Oracle Data Pump export/import to migrate data to the new release.

For example:

If you are upgrading from release 11.2.0.2 or 11.1.0.7, then you must first upgrade to Oracle Database 11g release 2 (11.2.0.3).
If you are upgrading from release 10.2.0.2, 10.2.0.3, 10.2.0.4,10.2.0.5 or 10.1.0.5, then you must first upgrade to release 11.2. or 12.1
If you are upgrading from release 9.2.0.8, then you must first upgrade to a sequence of intermediate Oracle Database releases:
Upgrade from release 9.2.0.8 to release 11.2.0.3 or 11.2.0.4. Then upgrade from release 11.2 to 18c

Step 2: Requirements and recommendations for source database

Take a cold or hot back up of the source database.
Disable any custom triggers that would get executed before / after DDL. You can enable it once upgrade is completed.
Data security roles defined in a release 11g Oracle Database instance are not automatically converted to ORAS. Before you upgrade an 11g database to Oracle Database 12c, you must delete any data security roles that are defined in the 11g database. After the upgrade, you may use Analytic Workspace Manager 12c to define the data security roles again.
If you upgrade an 11g database to Oracle Database 12c without deleting the 11g data security roles, then any data security policies that include a data security role are invalid in the Oracle Database 12c database.
Timezone should less than or equal to target database timezone version.
IF APEX is installed then it is recommended to upgrade APEX in the source DB first before upgrading DB
No INVALID object / Components in Source
If you are installing 64-bit Oracle Database software, and your existing Oracle Database is a 32-bit Oracle Database installation, then your existing Oracle Database is automatically converted to 64-bit during the upgrade to the new Oracle Database release.
Execute Preupgrade scripts before upgrade and review the preupgrade log for any issues.
Execute dbupgdiag.sql (refer Note 556610.1 to download this script) and verify whether there are any INVALID components or objects owned by SYS/SYSTEM . If any, fix them before proceeding to upgrade the database . You can execute utlrp.sql multiple times to VALIDate them, if still objects are INVALID, create a service request with Oracle support.
Execute utlrp.sql multiple times and verify there are no INVALID objects.

Step3: Requirements and recommendations for target ORACLE_HOME

Verify the whether your operating system is certified for 18.1. Click here to launch certification portal
Install 18.1.0.0, verify there are no installation related issues.
Download and install latest RU / RUR if any
Copy spfile or pfile from source ORACLE_HOME (under $ORACLE_HOME/dbs) to target home
- Remove any _ (underscore) parameter, obsolete and deprecated parameters in pfile
- Note min value of COMPATIBLE parameter to upgrade 18.1 is “11.2.0”, make sure you have COMPATIBLE parameter is set to 11.2.0 or greater
Review patch recommendations as given in the article "Patches to apply before upgrading Oracle GI and DB to 18c (Doc ID 2414935.1)"
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)

Step 4: Pre-upgrade checks

Clean up database

Empty the recycle bin
Check for INVALID objects in SYS and SYSTEM
Check for duplicate objects in SYS and SYSTEM
Check for INVALID, mandatory, obsolete components

Check materialized views

Check the status of all materialized views (MV), and refresh any materialized views that are not fresh.
Check the size of your materialized view logs. If any materialized view logs have non-zero rows, then refresh the base table materialized views.
Check the size of direct loader logs and PMOP logs (partition maintenance operation logs). If any direct loader logs or PMOP logs have non-zero rows, then refresh the MVs indicated by the logs
Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.

Run the following SQL query:

 
   SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

Copying Transparent Encryption Oracle Wallets

If Oracle wallet used with Transparent Data Encryption (TDE), then copy the sqlnet.ora and wallet file to the new Oracle home. You must copy the sqlnet.ora and the wallet file manually before starting the upgrade.

Log in as an authorized user.
Manually copy the sqlnet.ora file, and the wallet file, ewallet.p12, to the new release Oracle home.

Open the Oracle wallet in mount.

For example:

         SQL> STARTUP MOUNT; 
  
       SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN

Understanding Password Case Sensitivity

Starting with Oracle Database 12c release 2 (12.2), the default password-based authentication protocol configuration excludes the use of the case-insensitive 10G password version. By default, the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12, which is an Exclusive Mode.

For greater security, Oracle recommends that you leave case-sensitive password-based authentication enabled. This setting is the default. However, you can temporarily disable case-sensitive authentication during the upgrade to new Oracle Database releases. After the upgrade, you can then decide if you want to enable the case-sensitive password-based authentication feature as part of your implementation plan to manage your password versions.

Before upgrading, Oracle recommends that you determine if this change to the default password-based authentication protocol configuration affects you. Perform the following checks:

Identify if you have accounts that use only 10G case-insensitive password authentication versions.
Identify if you have Oracle Database 11g release 2 (11.2.0.3) database or earlier clients that have not applied critical patch update CPUOct2012, or a later patch update, and have any account that does not have the case-insensitive 10G password version.
Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE. Setting this parameter to FALSE prevents the use of the case-sensitive password versions (the 11G and 12C password versions) for authentication.

For more information refer 18.1 Oracle database documentation

Running Upgrades with Read-Only Tablespaces

Use the Parallel Upgrade Utility with the -T option to take schema-based tablespaces offline during upgrade. Oracle Database can read file headers created in earlier releases, so you are not required to do anything to them during the upgrade. The file headers of READ ONLY tablespaces are updated when they are changed to READ WRITE. If the upgrade suffers a catastrophic error, so that the upgrade is unable to bring the tablespaces back online, then review the upgrade log files. The log files contain the actual SQL statements required to make the tablespaces available. To bring the tablespaces back online, you must run the SQL statements in the log files for the database, or run the log files for each PDB.

Viewing Tablespace Commands In Upgrade Log Files

If a catastrophic upgrade failure occurs, then you can navigate to the log directory (Oracle_base/cfgtoologs/dbua), and run commands in the log files manually to bring up tablespaces. You can view tablespace commands in the following log files:

Non-CDB Upgrades : catupgrd0.log
PDB databases : catupgrdpdbname0.log, where pdbname is the name of the PDB that you are upgrading.

At the beginning of each log file, you find SQL statements such as the following, which sets tables to READ ONLY:

   SQL> ALTER TABLESPACE <Tablespace Name> READ ONLY; 
  
 Tablespace altered.

Near the end of each log file, you find SQL statements to reset tables to READ WRITE:

   SQL> ALTER TABLESPACE <Tablespace Name> READ WRITE; 
  
 Tablespace altered.

Preparing the New Oracle Home for Upgrading

Copy configuration files from the Oracle home of the database being upgraded to the new release Oracle Database Oracle home.
If you have a password file that resides within the old environment Oracle home, then move or copy the password file to the new Oracle home.
Remove desupported initialization parameters and adjust deprecated initialization parameters. In new releases, some parameters are desupported, and other parameters are deprecated. Remove all desupported parameters from any parameter file that starts the new Oracle Database instance. Desupported parameters can cause errors in new Oracle Database releases. Also, alter any parameter whose syntax has changed in the new release.
If you are upgrading a cluster database, then you need to change CLUSTER_DATABASE=FALSE before starting upgrade

Prerequisites for Preparing Oracle Home on Windows

System must meet these requirements before starting Oracle Database upgrade 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.
Before upgrading manually, or before using the custom files from the older Oracle Base (for example, wallets, configuration files and other custom files ), you must grant access to the Oracle home user for these outside files, or copy these files to the new Oracle Base.

Databases That Use Oracle Label Security and Oracle Database Vault

Audit Table Preupgrade and Archive Requirements

For Oracle Database releases earlier than 12.1 using Oracle Label Security and Oracle Database Vault, you must run the OLS preprocess script before you upgrade.

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 the olspreupgrade.sql script before upgrading is mandatory for upgrading databases earlier than Oracle Database release 12.1 that use Oracle Label Security and Oracle Database Vault. Once you have upgraded to Oracle Database release 12.1, you do not have to perform the OLS preprocessing procedure going forward to patch or upgrade the database.

Oracle Database Vault and Upgrades of Oracle Database Release 11.2

If Oracle Label Security is installed in the earlier release that you are upgrading, then grant the DV_PATCH_ADMIN role to SYS.

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

1. Copy the following scripts script from the newly installed Oracle home (18.1) to the Oracle home of the database that needs to be upgraded (11.2):

        ORACLE_HOME/rdbms/admin/olspreupgrade.sql
        ORACLE_HOME/rdbms/admin/emremove.sql
        ORACLE_HOME/rdbms/admin/catnoamd.sql

2. Start SQL*Plus 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 preprocess scripts for Data Vault

       ORACLE_HOME/rdbms/admin/olspreupgrade.sql 
  
     ORACLE_HOME/rdbms/admin/emremove.sql 
  
     ORACLE_HOME/rdbms/admin/catnoamd.sql

    You may continue to run your applications on the database while the preprocess scripts are 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; 
 

Granting the DV_PATCH_ADMIN Role to SYS for Oracle Database Vault

If Oracle Database Vault is enabled, then to perform checks for Oracle Data Vault, the upgrade process requires running three SQL scripts - olspreupgrade.sql, emremove.sql, catnoamd.sql

Start SQL*Plus and connect as DVOWNER to the database that you want to upgrade.

Run the following statement:

       SQL> GRANT DV_PATCH_ADMIN to SYS; 
 

Backing Up Oracle Database for Upgrading

Suggested to backup Oracle database after you run the Pre-Upgrade Information Tool. Take backup or create a guaranteed restore point or both. Test your backup. Ensure there is a proper fallback plan in case of any issues.

     rman "target / nocatalog" 

     RUN 
     { 
         ALLOCATE CHANNEL chan_name TYPE DISK; 
         BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade; 
         BACKUP CURRENT CONTROLFILE FORMAT 'controlfile location and name'; 
     } 

Manually remove DB control with emremove.sql

Stop/shutdown DB control

 
   emctl stop dbconsole  
 

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

Manually remove ORACLE_HOME/HOSTNAME_SID/ and ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID directory from the system
On windows delete DB Console service OracleDBConsoleSID

Ensure no files are in Back up mode before starting the upgrade

Run the following statement:

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

Purge Recycle bin

To empty the database recycle bin, run the following command:

 
   SQL> PURGE DBA_RECYCLEBIN  
 

Note: The database recycle bin must be empty during the upgrade process to avoid possible ORA-00600 errors, and to minimize the upgrade time.

Performance

Preserve performance statistics
Check network performance
Gather Optimizer statistics

To decrease the amount of downtime, gather statistics. Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, enter the following SQL statement:

   SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; 
 

Checking Time zone settings

Time zone should less than or equal to target database time zone version. If source is having higher time zone, then apply time zone patch on target ORACLE_HOME to match the source.

About Upgrading Oracle OLAP Data Security Policies

Data security roles defined in a release 11g Oracle Database instance are not automatically converted to ORAS. Before upgrading Oracle Database Release 11g to a current Oracle Database release, delete any data security roles that are defined in the 11g database. After the upgrade, you can use the new release Oracle Database Analytic Workspace Manager to define the data security roles again.

If DB us upgraded from an 11g database without deleting the 11g data security roles, then any data security policies that include a data security role are invalid in the later Oracle Database releases.

Step 5: Preupgrade step

Execute Preupgrade script from source home

 
   $Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar FILE TEXT DIR output_dir  
 

FILE - Use this option to direct output to a file
TEXT - Use this option to specify log should be in Text format (other option is to have XML output)
DIR - Logs will be created under <output_dir>

It is recommended to execute pre-upgrade fixup script, if any, which are AUTO FIXABLE

Preupgrade fixup script

Execute Preupgrade fixup scripts preupgrade_fixups.sql

Dependencies on Network Utility Packages

Execute the following query

 
   SQL> SELECT * FROM DBA_DEPENDENCIES WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');  
 

To ensure that the new access controls are part of your upgrade testing, prepare a post-upgrade script to make the scripts available in your database environment. After the upgrade, grant specific required privileges. Access is based on the usage in the original database.

Check Time zone version

Check if target database's time zone version is lower than the source database time zone version. If yes, before starting upgrade time zone should be upgrade without fail. RDBMS DST patches are available in Note 412160.1

Step 6: Upgrade Database to 18c

Shut down the database.

 
   SQL> SHUTDOWN IMMEDIATE  
 

Steps specific to Windows :

If your operating system is Windows, then complete the following steps:

a. Stop the OracleServiceSID Oracle service of the database you are upgrading,where SID is the instance name. For example, if your SID is ORCL, then enter the following at a command prompt:

    C:\> NET STOP OracleServiceORCL 
  

b. Delete the Oracle service at a command prompt using ORADIM. Refer to your platform guide for a complete list of the ORADIM syntax and commands.
For example, if your SID is ORCL, then enter the following command.

    C:\> ORADIM -DELETE -SID ORCL 
  

c. Create the service for the new release Oracle Database at a command prompt using the ORADIM command of the new Oracle Database release.
For example:

    C:\> ORADIM -NEW -SID SID -SYSPWD PASSWORD -MAXUSERS USERS  -STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA 
  

For Unix/Linux

Set the environment variables to point to target ORACLE_HOME

  export ORACLE_HOME=<path to Oracle 18c>  
 export PATH=$ORACLE_HOME/bin:$PATH  
 export ORACLE_BASE=<path to Oracle_Base set during installation>  

Copy the SPFILE.ORA or INIT.ORA file from the old Oracle home to the target Oracle home.

Start DB in upgrade mode from target ORACLE_HOME

 
   CONNECT / AS SYSDBA  
  
   SQL> startup upgrade;  
  
   SQL> exit

On Linux/Unix

  cd $ORACLE_HOME/bin  
 ./dbupgrade      
 

On Windows

  cd %ORACLE_HOME%\bin  
 dbupgrade  

Execute Post-Upgrade Status Tool, utlu122s.sql and review the upgrade spool log file. You run the Post-Upgrade Status Tool in the environment of the new release.

  $ sqlplus "/as sysdba"  
 SQL> STARTUP  
 SQL> @utlu122s.sql  

Verify the upgrade log whether catuppst.sql has been executed or not. If not, execute it manually from new ORACLE_HOME, located at $ORACLE_HOME/rdbms/admin directory

 
   SQL> @catuppst.sql    
 

Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.

 
   SQL> @utlrp.sql  
 

Check the state of the Oracle Data Dictionary for diagnosing upgrades and migrations. The dbupgdiag.sql script collects upgrade and migration diagnostic information about the current state of the data dictionary. You can run the script in SQL*Plus on the upgraded database as the SYS user. Refer Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects. After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.

If you are using Oracle Clusterware, then you must upgrade the Oracle Clusterware keys for the database. Run srvctl for Oracle Database 18c to upgrade the database. For example:

 
   ORACLE_HOME/bin/srvctl upgrade database -db name -o ORACLE_HOME  
 

Step 7: Post-upgrade

Setting Environment variables on Linux and Unix

Confirm that the following environment variables point to the directories of the new Oracle home:

ORACLE_HOME
PATH

Update oratab entries

Modify the corresponding entry in the /etc/oratab file to point to the new ORACLE_HOME location.

Post-upgrade fixup script

Execute post-upgrade fixup scripts generated by the pre-upgrade script.

   SQL> @postupgrade_fixups.sql 
 

Recovery Catalog Upgrade

If you use a version of the recovery catalog schema that is older than that required by the RMAN client, then you must upgrade it. You can upgrade the Recovery catalog by executing the UPGRADE CATALOG command

Please refer to Oracle documentation under "Upgrading the Recovery Catalog" for complete information and steps

Upgrade the Time Zone File Version After Upgrading Oracle Database

If the Pre-Upgrade Information Tool instructed you to upgrade the time zone files after completing the database upgrade,
then use the DBMS_DST PL/SQL package to update the RDBMS DST (timezone) version.

Follow the procedure in Oracle documentation under "Steps to Upgrade Time Zone File and Timestamp with Time Zone Data" and Note 1509653.1 "Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST"

Upgrading Statistics Tables

If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by running DBMS_STATS.UPGRADE_STAT_TABLE. In the following example, SYS is the owner of the statistics table and 'dictstattab' is the name of the statistics table.

 
   EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS', 'dictstattab');  
 

Perform this procedure for each statistics table.

About Me

  ........................................................................................................................  
  ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除  
  ● 本文在itpub（  http://blog.itpub.net/26736162  ）、博客园（  http://www.cnblogs.com/lhrbest  ）和个人weixin公众号（  xiaomaimiaolhr  ）上有同步更新  
  ● 本文itpub地址：  http://blog.itpub.net/26736162   
  ● 本文博客园地址：  http://www.cnblogs.com/lhrbest   
  ● 本文pdf版、个人简介及小麦苗云盘地址：  http://blog.itpub.net/26736162/viewspace-1624453/   
  ● 数据库笔试面试题库及解答：  http://blog.itpub.net/26736162/viewspace-2134706/   
  ● DBA宝典今日头条号地址：  http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826   
  ........................................................................................................................  
  ● QQ群号：  230161599  （满）  、618766405   
  ● weixin群：可加我weixin，我拉大家进群，非诚勿扰  
  ● 联系我请加QQ好友  （   646634621   ）  ，注明添加缘由  
  ● 于 2019-04-01 06:00 ~ 2019-04-31 24:00 在魔都完成  
  ● 最新修改时间：2019-03-01 06:00 ~ 2019-03-31 24:00  
  ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解  
  ● 版权所有，欢迎分享本文，转载请保留出处  
  ........................................................................................................................  
  ●  小麦苗的微店  ：  https://weidian.com/s/793741433?wfr=c&ifr=shopdetail   
  ●  小麦苗出版的数据库类丛书  ：  http://blog.itpub.net/26736162/viewspace-2142121/   
  ●  小麦苗OCP、OCM、高可用网络班  ：  http://blog.itpub.net/26736162/viewspace-2148098/   
  ●  小麦苗腾讯课堂主页  ：  https://lhr.ke.qq.com/   
  ........................................................................................................................  
 使用   weixin客户端   扫描下面的二维码来关注小麦苗的weixin公众号（  xiaomaimiaolhr  ）及QQ群（DBA宝典）、添加小麦苗weixin，  学习最实用的数据库技术。  
 
  ........................................................................................................................  