Oracle Database Exadata Express Cloud Service - 版本 N/A 和更高版本
Oracle Database Exadata Cloud Machine - 版本 N/A 和更高版本
Oracle Cloud Infrastructure - Database Service - 版本 N/A 和更高版本
Oracle Database Backup Service - 版本 N/A 和更高版本
Oracle Database Cloud Exadata Service - 版本 N/A 和更高版本
本文档所含信息适用于所有平台

用途

本文档可用作手工将 Oracle 10gR2 (10.2) 或者 Oracle 11gR1 (11.1) 或者 Oracle 11gR2 (11.2) 或者 Oracle 12cR1 (12.1) 版本数据库升级至 Oracle 12cR2 (12.2) 版本数据库的指南与核对表。

适用范围

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

详细信息

步骤1：升级到数据库 12.2 的升级路径

能够直接升级到 Oracle 12c Release 2 (12.2) 的数据库最小版本：

源数据库	目标数据库
11.2.0.3 / 11.2.0.4	12.2.x
12.1.0.1 / 12.1.0.2	12.2.x

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

源数据库		升级路径		目标数据库
11.2.0.1 / 11.2.0.2	-->	11.2.0.4	-->	12.2.x
11.1.0.6 / 11.1.0.7	-->	11.2.0.4	-->	12.2.x
10.2.0.2/10.2.0.3/10.2.0.4/10.2.0.5	-->	11.2.0.4 / 12.1.0.2	-->	12.2.x
10.1.0.5	-->	11.2.0.4 / 12.1.0.2	-->	12.2.x
9.2.0.8	-->	11.2.0.3 / 11.2.0.4	-->	12.2.x

比如

如果源库是 11.2.0.2 或者 11.1.0.7，那么你需要先升级至 11.2.0.4。
如果源库是 10.2.0.2，10.2.0.3，10.2.0.4，10.2.0.5 或者 10.1.0.5，需要先升级至 11.2.0.4 或者 12.1.0.2。
对于 9.2.0.8 版本的数据库，需要先升级至一个中间版本，比如：9.2.0.8 -> 11.2.0.3 或者 11.2.0.4 -> 12.1。

步骤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 组件。

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

需要先检查您的硬件平台/操作系统是否兼容 12.2。点击此处来确定兼容性。
安装数据库软件 12.2.0.1，并确保没有安装方面的问题。
如果有的话，下载并应用最新的 PSU。
从源库的 ORACLE_HOME/dbs 下拷贝 spfile 或者 pfile 到目标 ORACLE_HOME/dbs。
从参数文件中删除所有废弃的参数。
注意升级到 12.2 需要的最低的参数 COMPATIBLE 值为”11.2.0”，确保参数 COMPATIBLE 值设置为 11.2.0 或者更高。
查看文档 "Patches to apply before upgrading Oracle GI and DB to 12.2.0.1 (Doc ID 2180188.1)" 给出的推荐补丁
在目标 ORACLE_HOME 应用补丁 patch 29213893 来避免 ORA-01422 错误 - 参考: Database Upgrade to 12.2, 18c, 19c fails with ORA-01422, ORA-06512 for SYS.DBMS_STATS (Doc ID 2525596.1)

步骤 4：检查源库的健康状况

执行 dbupgdiag.sql（可以从 note 556610.1 下载这个脚本），并且确认是否有 SYS/SYSTEM 用户下的失效对象或者失效组件。如果存在的话，那么需要在升级前解决这些问题。你可以多次执行 utlrp.sql 来解决问题。如果在这样做之后仍然存在失效对象，那么开一个 SR 来解决这个问题。
多次执行脚本 utlrp.sql 确认数据库中没有失效对象。

步骤5：升级前检查

清理数据库

清空回收站
检查 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;

性能方面

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

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

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

检查时区设置

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

备份数据库

备份数据库，创建 guaranteed flashback restore point。
在升级数据库窗口前应最少测试一下回滚策略。
确保回滚策略不仅考虑到升级中，同时也要考虑到升级后的失败。
RMAN 连接到 RMAN

rman "target / nocatalog"

执行 RMAN 命令来备份

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';
}

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';

}

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

执行下面的语句：

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

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

清空回收站

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

SQL> PURGE DBA_RECYCLEBIN

1	SQL> PURGE DBA_RECYCLEBIN

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

备份 Oracle EM DB Control 配置及数据

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

备份数据的步骤

安装 12.2 的数据库软件。
设置 ORACLE_HOME 到旧的数据库版本。
设置 ORACLE_SID 为要升级的数据库 SID。
设置 PATH， LD_LIBRARY_PATH 和 SHLIB_PATH 到旧的 ORACLE_HOME 相关的目录下。
切换目录到 12c 数据库软件的 ORACLE_HOME/bin。
执行 emdwgrd。
对于单数据库实例（非 RAC）运行下面的命令：

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

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

Oracle Real Application Clusters（Oracle RAC）数据库：
需要跨节点远程拷贝。定义一个环境变量 EM_REMCP 来指向远程拷贝的命令，比如：export EM_REMCP /usr/bin/scp

emdwgrd -save -cluster -sid old_SID -path save_directory

1	emdwgrd -save -cluster -sid old_SID -path save_directory

输入 SYS 密码。

使用 emremove.sql 手工删除 DB control

关闭 DB control

emctl stop dbconsole

1	emctl stop dbconsole

使用 sysdba 登陆

SQL>SET ECHO ON
SQL>SET SERVEROUTPUT ON
SQL>@emremove.sql >> 脚本位于新的 12c ORACLE_HOME/rdbms/admin

SQL>SET ECHO ON

SQL>SET SERVEROUTPUT ON

SQL>@emremove.sql >> 脚本位于新的 12c ORACLE_HOME/rdbms/admin

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

删除 JSON-Enabled Context search 索引

如果源库版本为 12.1.0.2 并且创建了 JSON search index 那么 Oracle 推荐先删除这些索引，在升级后再创建回来。

检查使用大小写不敏感密码的用户

使用管理用户登录 SQL*Plus，并且执行下面的语句：

SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

1	SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

如果存在10g 版本的用户，建议根据 Oracle 文档解决这个问题，否则升级后用户会被 LOCK。

删除 Unified Auditing Schema and Roles

注意：如果在 Oracle 12.1 数据库上已经创建了 AUDIT_ADMIN 或者 AUDIT_VIEWER 用户或者 roles，或者数据库是在 12.1 版本上创建（而不是升级上来的），那么不需要删除这些 role 和 AUDSYS 用户。

使用 SYS 以 SYSDBA 权限登录 SQL*Plus，删除 AUDSYS，如果存在的话。以 migrate 模式启动数据库并删除 AUDSYS 用户

SQL> startup migrate pfile=$T_WORK/t_init1.ora
ORACLE instance started.
SQL> drop user audsys cascade;

SQL> startup migrate pfile=$T_WORK/t_init1.ora

ORACLE instance started.

SQL> drop user audsys cascade;

删除 AUDIT_ADMIN 和 AUDIT_VIEWER roles

DROP ROLE AUDIT_ADMIN;
DROP ROLE AUDIT_VIEWER;

1 2	DROP ROLE AUDIT_ADMIN; DROP ROLE AUDIT_VIEWER;

在升级过程中把某些 schema 的表空间置于 offline

记下所有在升级过程中需要 offline 的表空间，使用 -T 选项指定表空间的名字

dbupgrade –T

1	dbupgrade –T

从 Oracle database 12.2 开始，可以在并行升级时使用 -T 参数来 offline 一些用户表空间。把用户表空间 offline 可以减少升级前的备份工作。并行升级工具（catctl.pl）可以自动选取正确的表空间来设置为只读。这个工具不会把任何包含Oracle 自带的对象的表空间设置为只读。

注意: 不要执行这个步骤，因为 dbupgrade -T 会执行升级的动作。我们在 Pre-upgrade 阶段提及这个命令只是让大家记下 tablespace 的名字，这样在稍后真正升级的步骤里可以把 tablespace name 指定到 dbupgrade 的 -T 参数之后。

保留降级的能力

如果计划把数据库降级到之前的版本，那么需要在源库上打 patch 20898997，否则不能降级。

在源库执行：

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed |grep –i "20898997"

1	$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed \|grep –i "20898997"

如果尚未应用这个补丁，那么从 MOS 下载 patch 20898997 并安装。

关于 Audit table 的升级前要求

如果要升级的数据库版本低于 12.1 并且使用了 Oracle database Vault，Oracle Label Security，那么必须先执行 olspreupgrade.sql。从目标 Oracle_home 拷贝 $ORACLE_HOME/rdbms/admin/olspreupgrade.sql 到源库的ORACLE_HOME，使用 DVOWNER 登陆源库

SQL> GRANT DV_PATCH_ADMIN to SYS;

1	SQL> GRANT DV_PATCH_ADMIN to SYS;

在把 DV_PATCH_ADMIN 权限赋给 SYS 后，使用 SYSDBA 登陆

CONNECT SYS AS SYSDBA

执行 olspreupgrade.sql

SQL>ORACLE_HOME/rdbms/admin/olspreupgrade.sql

1	SQL>ORACLE_HOME/rdbms/admin/olspreupgrade.sql

执行完毕后，以 DVOWNER 登陆数据库并收回 SYS 的 DV_PATCH_ADMIN 权限

SQL> REVOKE DV_PATCH_ADMIN from SYS;

1	SQL> REVOKE DV_PATCH_ADMIN from SYS;

集群数据库的需要

在升级数据库前需要先升级 GI 软件。如果是 RAC，那么需要把参数文件中的 CLUSTER_DATABASE 参数设置为 false。

注意！在升级完毕后需要把CLUSTER_DATABASE再设置回来

升级前先解决Outstanding分布式事务

要解决 outstanding 分布式事务:

执行下面的语句：

SQL> Select * from dba_2pc_pending;

1	SQL> Select * from dba_2pc_pending;

如果有返回数据，则执行下面的语句：

SQL> select local_tran_id FROM dba_2pc_pending;

SQL> execute dbms_transaction.purge_lost_db_entry('');

SQL> commit;

SQL> select local_tran_id FROM dba_2pc_pending;

SQL> execute dbms_transaction.purge_lost_db_entry('');

SQL> commit;

其它检查

确保 ARCHIVE_LOG 以及 FLASHBACK 目录有足够的磁盘空间。
执行下面的查询来检查是否有和SDO_GEOMETRY关联的表

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;

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;

如果有返回行数，那么在升级前需要往12.2的ORACLE HOME上打补丁 patch 25293022

如果安装了 Oracle Spatial (SDO)，那么为了避免碰到 Note 2547520.1 中提到的问题，您需要在升级前应用补丁 29715479

步骤6：Preupgrade 步骤

在源库执行 Preupgrade 脚本

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

1	$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 - 日志会创建在指定的这个目录中

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

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');

1	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 下载。

步骤7：升级数据库到 12.2

关闭数据库

SQL> SHUTDOWN IMMEDIATE

1	SQL> SHUTDOWN IMMEDIATE

Windows平台的步骤 :

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

如果操作系统是Windows，那么完成下面的步骤：
a. 停掉要升级的数据库 OracleServiceSID Oracle service，这里的SID是实例名。比如，如果SID是ORCL，那么执行下面的命令：

C:\> NET STOP OracleServiceORCL

1	C:\> NET STOP OracleServiceORCL

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

C:\> ORADIM -DELETE -SID ORCL

1	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

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

设置环境变量指向目标 ORACLE_HOME

export ORACLE_HOME=<Oracle 12.2的目录>
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE=<安装时指定的Oracle_Base目录>

export ORACLE_HOME=<Oracle 12.2的目录>

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_BASE=<安装时指定的Oracle_Base目录>

注意：删除所有仍指向旧的ORACLE_HOME的环境变量

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

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

CONNECT / AS SYSDBA

SQL> startup upgrade;

SQL> exit

在 Linux/Unix 上

cd $ORACLE_HOME/bin
./dbupgrade

1 2	cd $ORACLE_HOME/bin ./dbupgrade

在 Windows 上

cd %ORACLE_HOME%\bin
dbupgrade

1 2	cd %ORACLE_HOME%\bin dbupgrade

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

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

$ sqlplus "/as sysdba"

SQL> STARTUP

SQL> @utlu122s.sql

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

SQL> @catuppst.sql

1	SQL> @catuppst.sql

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

SQL> @utlrp.sql

1	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

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

步骤8：升级后步骤

在 Linux 和 Unix 上设置环境变量

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

ORACLE_HOME
PATH

更新 oratab 文件

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

Post-upgrade fixup 脚本

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

升级依赖 Oracle-Maintained 类型的表

从 Oracle 12.2 开始，必须手工升级依赖 Oracle-Maintained 类型的用户表。

在数据库升级后确认需要升级的用户表，使用 SYSDBA 连接到数据库并执行下面的语句来列出这些表：

COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
SELECT DISTINCT owner, table_name
FROM dba_tab_cols
WHERE data_upgraded = 'NO'
ORDER BY 1,2;

COLUMN owner FORMAT A30

COLUMN table_name FORMAT A30

SELECT DISTINCT owner, table_name

FROM dba_tab_cols

WHERE data_upgraded = 'NO'

ORDER BY 1,2;

使用 sysdba 权限的用户，或者有权限 alter 所有这些表的用户执行脚本 utluptabdata.sql：

SET SERVEROUTPUT ON
@utluptabdata.sql

1 2	SET SERVEROUTPUT ON @utluptabdata.sql

启用新的 Extended Data Type 功能（并不是必须的）

启用 extended data types 需要一些特定的操作。

Oracle 数据库 12c 引入了扩展 VARCHAR2，NVARCHAR2，以及 RAW 数据类型的大小的功能。设置 MAX_STRING_SIZE = EXTENDED 可以扩展这些数据类型最大限制到32767字节。

要实现这个功能，初始化参数 COMPATIBLE 要设置为 12.0.0.0 或者更高。关于更多信息，请参考Note 1570297.1

升级 Recovery Catalog

如果 recovery catalog schema 比要备份的数据库版本低，那么必须升级它。可以使用 UPGRADE CATALOG 命令来升级。

请参照Oracle 文档的"Upgrading the Recovery Catalog" 部分来得到更多信息。

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

如果 Pre-Upgrade Information 工具要求在升级数据库后升级 time zone 文件版本，那么需要使用 DBMS_DST PL/SQL 包来更新 RDBMS DST (timezone) 版本。

参照 Oracle 文档的"Steps to Upgrade Time Zone File and Timestamp with Time Zone Data" 部分以及 Note 1509653.1 "Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST"

升级统计信息表

如果之前使用 DBMS_STATS.CREATE_STAT_TABLE 创建了一些统计信息表，那么使用 DBMS_STATS.UPGRADE_STAT_TABLE 来升级这些表。在下面的例子里，统计信息表的名字是'dictstattab'，而 SYS 是这个表的 owner。

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

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

对所有的统计信息表都执行类似上面的语句。

升级外部验证的 SSL 用户

如果是从 9.2 或者 10.1 的数据库升级上来的，并且使用了外部验证的 SSL 用户，那么需要执行 SSL external users conversion (extusrupgrade) 脚本来升级这些用户。

ORACLE_HOME/rdbms/bin/extusrupgrade --dbconnectstring hostname:port_no:sid --dbuser db_admin --dbuserpassword password -a

在升级数据库后安装 Oracle Text Supplied Knowledge Bases

Oracle Text-supplied knowledge bases 是数据库 12c 的扩展产品的一部分，并不会在升级后立刻可用。所有依赖这个产品的 Oracle Text 功能在升级后都会不可用。如需重新启用这些功能，需要从安装介质安装 Oracle Text supplied knowledge bases。

升级数据库后更新 Oracle Application Express Configuration

如果要升级的数据库包含了 Oracle Application Express release 3.2 或更高版本，那么不需要做任何额外的操作。但是如果 Oracle Application Express 存在 registry里，那么 Oracle Application Express 是从低版本升级而来，那么需要设置open_cursors 参数最小为200。

对额外的 Network Services 配置访问控制列表（ACLs）

如果应用程序使用了 UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, 或者 UTL_INADDR 包，那么升级后需要配置这些包的网络访问控制列表 (ACLs)来让它们像之前版本一样正常工作。如果没有正确配置访问控制列表 (ACLs)，应用程序会碰到错误"ORA-24247: network access denied by access control list (ACL)."

升级后启用 Oracle Database Vault

如果有需要的话，参照下面的文档来启用 Oracle Database Vault：
Note 453903.1 - Enabling and Disabling Oracle Database Vault in UNIX
Note 453902.1 - Enabling and Disabling Oracle Database Vault in WINDOWS

检查参数 SQLNET.ALLOWED_LOGON_VERSION

10g 之前的客户端连接到 12c 数据库会碰到 ORA-28040: No matching authentication protocol 错误，请参考 Oracle 文档来解决 ORA-28040: No matching authentication protocol 问题。

已知问题

已知问题请参考 Oracle Database Upgrade Known issues - 12.2 (Doc ID 2243613.1)

从12.2开始utluiobj.sql不在可用。在升级前，Pre-Upgrade Information Tool会把系统SYS/SYSTEM的失效对象写入registry$sys_inv_objs，并且把其它用户的失效对象写入registry$nonsys_inv_objs，我们可以使用这些表来比较升级后的失效对象。

参考

NOTE:2243613.1 - Oracle Database Upgrade Known issues - 12.2

Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 12c Release 2 (12.2) (Doc ID 2173141.1)

APPLIES TO:

Oracle Database Cloud Schema 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
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - 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 10gR2 (10.2) , Oracle 11gR1 (11.1) or Oracle 11gR2 (11.2) or Oracle 12c Release 1 (12.1) to Oracle 12c Release 2 (12.2)

SCOPE

Database Administrators, Support

DETAILS

Step 1: Upgrade Path for 12.2 Oracle database

Minimum version of the database that can be directly upgraded to Oracle 12c Release 2 (12.2)

Source Database	Target Database
11.2.0.3 / 11.2.0.4	12.2.x
12.1.0.1 / 12.1.0.2	12.2.x

Intermediate upgrades needs to be carried for following releases

Source Database		Intermediate upgrade path		Target database
11.2.0.1 / 11.2.0.2	-->	11.2.0.4	-->	12.2.x
11.1.0.6 / 11.1.0.7	-->	11.2.0.4	-->	12.2.x
10.2.0.2/10.2.0.3/10.2.0.4/10.2.0.5	-->	11.2.0.4 / 12.1.0.2	-->	12.2.x
10.1.0.5	-->	11.2.0.4 / 12.1.0.2	-->	12.2.x
9.2.0.8	-->	11.2.0.3 / 11.2.0.4	-->	12.2.x

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.4).
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.0.4 or 12.1.0.2.
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.0.4 to 12.2

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

Step3: Requirements and recommendations for target ORACLE_HOME

Verify the whether your operating system is certified for 12.2. Click here to launch certification portal
Install 12.2.0.1, verify there are no installation related issues.
Download and install latest PSU if any
Copy spfile or pfile from source ORACLE_HOME (under $ORACLE_HOME/dbs) to target home
Remove any _parameter, obsolete and deprecated parameters in pfile
Note min value of COMPATIBLE parameter to upgrade 12.2 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 12.2.0.1 (Doc ID 2180188.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: Check the health of the source database

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.

Step 5: 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.

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

\1. 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;

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

Default time zone for Oracle database 12.2 is V26
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.

Back up the database

Back up the database, create a guaranteed flashback restore point, or both.
Test your fallback strategies at least once before your upgrade window.
Ensure that you have fallback strategies for issues both during upgrade, and after upgrade.
Connect to RMAN

rman "target / nocatalog"

Execute RMAN command to backup

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';
}

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.

Save Oracle EM DB Control Configuration and Data

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.

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

Drop JSON-Enabled Context search Indexes

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.

Check the accounts use Case-Insensitive password version

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.

Remove Unified Auditing Schema and Roles

Note: If you have created AUDIT_ADMIN and AUDIT_VIEWER users or roles with Oracle Database 12*c* release 1 (12.1) or if the database was created in 12.1, then you do not drop these roles and AUDSYS user.

For more information refer to URL https://docs.oracle.com/database/122/UPGRD/unified-auditing-audit_admin-audit_viewer-changes.htm#UPGRD60010

Log into SQL*Plus as SYS with SYSDBA privilege. Drop AUDSYS schema if exists. Start DB in migrate mode and drop AUDSYS user

SQL> startup migrate pfile=$T_WORK/t_init1.ora
ORACLE instance started.
SQL> drop user audsys cascade;

Drop AUDIT_ADMIN and AUDIT_VIEWER roles

DROP ROLE AUDIT_ADMIN;
DROP ROLE AUDIT_VIEWER;

Put Schema based tablespaces offline during upgrade

Note down the table space names which needs to be offline during upgrade. Use -T option followed by table space name

dbupgrade –T

Starting with Oracle Database 12c release 2, you can run the Parallel Upgrade Utility with the -T option to have schema-based tablespaces (user tablespaces) taken offline during the upgrade. Taking these tablespaces offline can reduce the necessity of backing up before upgrades. The Parallel Upgrade Utility (catctl.pl) analyzes tablespaces, and automatically selects the right set of tablespaces to set to read only. The utility does not set to READ ONLY any tablespaces that contain Oracle-Maintained objects

Note: Do not execute this step, since it will start the upgrade. This section is given in Pre-upgrade phase because you can note down the tablespace names, so that when you execute dbupgrade script you can provide the -T option followed by tablespace names.

Preserve Downgrade capability

If you have any plans to downgrade the database to its previous version, then you must have patch 20898997 installed in source ORACLE_HOME, else downgrade will not be possible.

On source ORACLE_HOME

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed |grep –i "20898997"

If patch is not installed, download patch 20898997 from MOS and install

Audit table preupgrade requirements

If upgrading from pre 12.1 release and using Oracle database Vault, Oracle Label Security, then you must first run olspreupgrade.sql. Copy $ORACLE_HOME/rdbms/admin/olspreupgrade.sql from target home (12.2). Connect as DVOWNER to the source db

SQL> GRANT DV_PATCH_ADMIN to SYS;

Grant DV_PATCH_ADMIN to SYS. Connect SYS as SYSDBA

CONNECT SYS AS SYSDBA

Execute olspreupgrade.sql

SQL>ORACLE_HOME/rdbms/admin/olspreupgrade.sql

After completion, connect as DVOWNER and revoke DV_PATCH_ADMIN to SYS

SQL> REVOKE DV_PATCH_ADMIN from SYS;

Cluster DB Requirements

Upgrade GI first before upgrading the database. If RAC, then database set CLUSTER_DATABASE to false in the init parameter file.

Note: After completing post upgrade steps set CLUSTER_DATABASE to true.

Resolve Outstanding Distributed Transactions Before Upgrade

To resolve outstanding distributed transactions:

\1. Issue the following statement:

SQL> Select * from dba_2pc_pending;

\2. If the query in the previous step returns any rows, then issue the following statements:

SQL> select local_tran_id FROM dba_2pc_pending;
SQL> execute dbms_transaction.purge_lost_db_entry('');
SQL> commit;

Other Checks

\1. Ensure you have enough space in ARCHIVE_LOG and FLASHBACK location.

\2. Check whether there are tables with SDO_GEOMETRY by executing below query.

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 it returns a row(s), then apply Patch 25293022 on the 12.2 Oracle home, before starting the upgrade.

\3. Check Oracle Spatial (SDO) is installed, if yes apply Patch 29715479 onto ORACLE_HOME before upgrade to avoid issue reported in Note 2547520.1

Step 6: 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

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

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

Step7: Upgrade Database to 12.2

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

Set the environment variables to point to target ORACLE_HOME

export ORACLE_HOME=<path to Oracle 12.2>
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE=

Note: Remove any references to old ORACLE_HOME in the environment variables.

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 12c to upgrade the database. For example:

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

Step 8: 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.

Upgrading Tables Dependent on Oracle-Maintained Types

Starting with Oracle Database 12c release 2 (12.2), you must manually upgrade user tables that depend on Oracle-Maintained types.

To identify tables that need to be upgraded after the database upgrade, connect AS SYSDBA and run the following query:

COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
SELECT DISTINCT owner, table_name
FROM dba_tab_cols
WHERE data_upgraded = 'NO'
ORDER BY 1,2;

Execute utluptabdata.sql script either with a user account with the privileges to ALTER all of the tables dependent on Oracle-Maintained types, or with a user granted the SYSDBA system privileges that is logged in AS SYSDBA.

SET SERVEROUTPUT ON
@utluptabdata.sql

Enabling the New Extended Data Type Capability

Enabling a system to take advantage of the new extended data types requires specific upgrade actions.

Oracle Database 12c introduces MAX_STRING_SIZE to control the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL. Setting MAX_STRING_SIZE = EXTENDED enables the 32767 byte limit introduced in Oracle Database 12c.

You must set the COMPATIBLE initialization parameter to 12.0.0.0 or higher to be able to set MAX_STRING_SIZE = EXTENDED. For more information on this refer to Note 1570297.1

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.

Upgrade Externally Authenticated SSL Users

If you are upgrading from Oracle9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), and you are using externally authenticated SSL users, then you must run the SSL external users conversion (extusrupgrade) script to upgrade those users.

ORACLE_HOME/rdbms/bin/extusrupgrade --dbconnectstring hostname:port_no:sid --dbuser db_admin --dbuserpassword password -a

Install Oracle Text Supplied Knowledge Bases After Upgrading Oracle Database

The Oracle Text-supplied knowledge bases are part of the companion products for Oracle Database 12c and are not immediately available after an upgrade to Oracle Database 12c. Any Oracle Text features dependent on the supplied knowledge bases which were available before the upgrade do not function after the upgrade. To re-enable such features, you must install the Oracle Text supplied knowledge bases from the installation media.

Update Oracle Application Express Configuration After Upgrading Oracle Database

If the Oracle Database release that you upgrade includes Oracle Application Express release 3.2 or later, then you do not need to carry out additional configuration after upgrading to Oracle Database 12c. However, if Oracle Application Express is in the registry, so that Oracle Application Express is included in the upgrade, then set the open_cursors parameter to a minimum of 200.

Configure Access Control Lists (ACLs) to External Network Services

if you have applications that use UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages, then after upgrading Oracle Database you must configure network access control lists (ACLs) in the database before these packages can work as they did in earlier releases. Without the ACLs, your applications may fail with the error "ORA-24247: network access denied by access control list (ACL)."

Enabling Oracle Database Vault After Upgrading Oracle Database

Refer to the following documents for enabling Oracle Database Vault:
Note 453903.1 - Enabling and Disabling Oracle Database Vault in UNIX
Note 453902.1 - Enabling and Disabling Oracle Database Vault in WINDOWS

Check for the SQLNET.ALLOWED_LOGON_VERSION Parameter Behavior

Connections to Oracle Database from clients earlier than release 10g fail with the error ORA-28040: No matching authentication protocol. Refer to Oracle documentation for fixing the ORA-28040: No matching authentication protocol.

Known issues

For known issues refer to Oracle Database Upgrade Known issues - 12.2 (Doc ID 2243613.1)

utluiobj.sql is not available from 12.2. Before the upgrade, the list of invalid SYS/SYSTEM objects is written to registry$sys_inv_objs and non-SYS/SYSTEM objects was written to registry$nonsys_inv_objs by the Pre-Upgrade Information Tool, we can use these tables to query the INVALID objects after upgrade and compare with the current status.