Redhat 从 Oracle 11.2.0.4升级到18c non-CDB(手动) -- cnDBA.cn

1.1. Linux从11.2.0.4升级到18c non-CDB(手动)

对于12c之前的版本，如果要升级到12c，18c版本，那么也只能升级到non-CDB模式无法直接升级到CDB模式。

Redhat 7.4 Oracle 11.2.0.4升级到18c (DBUA方式)：

https://www.cndba.cn/Expect-le/article/3020

1.1.1. 安装18c数据库软件

1) 新的Oracle环境变量：

ORACLE_BASE=/u02/app/oracle

ORACLE_HOME= /u02/app/oracle/product/18.1.0/dbhome_1

ORACLE_SID=cndba

2) 资源配置：

[root@18c ~]# vim /etc/security/limits.conf

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

oracle soft stack 10240

oracle hard stack 32768

oracle soft memlock 3145728

oracle hard memlock 3145728

3) 创建新目录

mkdir -p /u02/app/oracle/product/18.1.0/dbhome_1

chown -R oracle:oinstall /u02

chmod -R 775 /u02/

4) 解压18c到新的ORACLE_HOME下

unzip -d /u02/app/oracle/product/18.1.0/dbhome_1 Oracle18.1.0.zip

5) 安装数据库软件

只安装数据库软件

如果是单实例则选择第一个

选择企业版

修改为新的ORACLE_BASE目录

默认

安装前检查，内存不符合要求。18c建议的内存为8G

正在安装

执行root.sh脚本

1.1.2. 升级前检查

1) 运行查脚本

脚本可以从MOS(ID 884522.1)中下载，也可以在新的$ORACLE_HOME/rdbms/admin/中找到，直接在源库上运行即可，然后根据检查结果修改即可。

l 在源库上执行脚本检查

[oracle@cndba software]$ /u02/app/oracle/product/18.1.0/dbhome_1/jdk/bin/java -jar /u02/app/oracle/product

/18.1.0/dbhome_1/rdbms/admin/preupgrade.jar

==================

PREUPGRADE SUMMARY

==================

/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log

/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql

/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade log into the database and execute the preupgrade fixups

@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups

@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2018-07-10T15:01:40

preupgrade.log里记录的是检查的详细信息，有不满足的对象可以通过查看该文件获取。根据提示：在升级前要执行preupgrade_fixups.sql脚本来修复不符合要求的对象。升级后需要执行postupgrade_fixups.sql脚本来修复。

l 下面截取部分日志：

Report generated by Oracle Database Pre-Upgrade Information Tool Version

18.0.0.0.0 on 2018-07-10T15:01:39

Upgrade-To version: 18.0.0.0.0

=======================================

Status of the database prior to upgrade

=======================================

Database Name: ORCL

Container Name: Not Applicable in Pre-12.1 database

Container ID: Not Applicable in Pre-12.1 database

Version: 11.2.0.3.0

Compatible: 11.2.0.0.0

Blocksize: 8192

Platform: Linux x86 64-bit

Timezone File: 14

Database log mode: ARCHIVELOG

Readonly: FALSE

Edition: EE

Oracle Component Upgrade Action Current Status

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

Oracle Server [to be upgraded] VALID

JServer JAVA Virtual Machine [to be upgraded] VALID

Oracle XDK for Java [to be upgraded] VALID

Oracle Workspace Manager [to be upgraded] VALID

OLAP Analytic Workspace [to be upgraded] VALID

Oracle Enterprise Manager Repository [to be upgraded] VALID

Oracle Text [to be upgraded] VALID

Oracle XML Database [to be upgraded] VALID

Oracle Java Packages [to be upgraded] VALID

Oracle Multimedia [to be upgraded] VALID

Oracle Spatial [to be upgraded] VALID

Expression Filter [to be upgraded] VALID

Rule Manager [to be upgraded] VALID

Oracle OLAP API [to be upgraded] VALID

…..

INFORMATION ONLY

================

14. Check the Oracle documentation for the identified components for their

specific upgrade procedure.

The database upgrade script will not upgrade the following Oracle

components: OLAP Catalog,OWB

The Oracle database upgrade script upgrades most, but not all Oracle

Database components that may be installed. Some components that are not

upgraded may have their own upgrade scripts, or they may be deprecated or

obsolete.

ORACLE GENERATED FIXUP SCRIPT

=============================

All of the issues in database ORCL

which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by

executing the following

SQL>@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

l 执行修复脚本

会自动修复不满足升级条件的问题，如果有没有修复的问题需要手动去修复。

SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql

Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script

Version: 18.0.0.0.0 Build: 1

Generated on: 2018-07-10 15:01:33

For Source Database: ORCL

Source Database Version: 11.2.0.3.0

For Upgrade to Version: 18.0.0.0.0

Preup Preupgrade

Action Issue Is

Number Preupgrade Check Name Remedied Further DBA Action

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

1. min_recovery_area_size NO Manual fixup required.

2. parameter_min_val NO Manual fixup recommended.

3. em_present NO Manual fixup recommended.

4. invalid_objects_exist NO Manual fixup recommended.

5. amd_exists NO Manual fixup recommended.

6. apex_manual_upgrade NO Manual fixup recommended.

7. dictionary_stats YES None.

8. trgowner_no_admndbtrg NO Informational only.

Further action is optional.

9. pre_fixed_objects YES None.

10. tablespaces_info NO Informational only.

Further action is optional.

The fixup scripts have been run and resolved what they can. However,

there are still issues originally identified by the preupgrade that

have not been remedied and are still present in the database.

Depending on the severity of the specific issue, and the nature of

the issue itself, that could mean that your database is not ready

for upgrade. To resolve the outstanding issues, start by reviewing

the preupgrade_fixups.sql and searching it for the name of

the failed CHECK NAME or Preupgrade Action Number listed above.

There you will find the original corresponding diagnostic message

from the preupgrade which explains in more detail what still needs

to be done.

PL/SQL procedure successfully completed.

u 修改DB_RECOVERY_FILE_DEST_SIZE

alter system set DB_RECOVERY_FILE_DEST_SIZE=10g scope=spfile;

u 修改processes

alter system set processes=300 scope=spfile;

u 移除EM

SET ECHO ON;

SET SERVEROUTPUT ON;

@/u02/app/oracle/product/18.1.0/dbhome_1/rdbms/admin/emremove.sql

u 升级APEX

从18c开始，Oracle不会自动更新APEX组件，需要手动更新。

下载APEX：http://www.oracle.com/technetwork/developer-tools/apex/all-archives-099381.html

要写切换到解压的apex所在的目录下再以sysdba身份登录数据库，执行脚本升级

[oracle@cndba apex]$ pwd

/software/apex

sqlplus / as sysdba

SQL > @apexins.sql SYSAUX SYSAUX TEMP /i/

检查APEX版本

COL COMP_NAME FORMAT A40

COL STATUS FORMAT A12

SELECT COMP_NAME, STATUS, VERSION

FROM DBA_REGISTRY

WHERE COMP_NAME='Oracle Application Express';

COMP_NAME STATUS VERSION

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

Oracle Application Express VALID 5.1.4.00.08

u 移除OLAP Catalog

从12c开始，不再支持OLAP Catalog。执行脚本移除

@$ORACLE_HOME/olap/admin/catnoamd.sql

2) 再次检查SYS/SYSTEM用户下是否有无效对象

由于升级APEX造成有无效对象，但是不是SYS、SYSTEM用户下的无效对象。可以忽略

SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type;

OWNER OBJECT_TYPE COUNT(*)

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

APEX_030200 PACKAGE 1

APEX_030200 PACKAGE BODY 32

APEX_030200 SYNONYM 2

APEX_030200 VIEW 4

SCOTT FUNCTION 1

3) 开启归档和闪回

查看是否开启闪回和归档

SQL> select log_mode,flashback_on from v$database;

LOG_MODE FLASHBACK_ON

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

ARCHIVELOG YES

没有则手动开启归档和闪回。

shutdown immediate;

startup mount;

alter database archivelog;

alter database flashback on;

1.1.3. (可选)如果是RAC，则修改CLUSTER_DATABASE为FALSE

ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;

1.1.4. 关闭源库

SQL> SHUTDOWN IMMEDIATE

1.1.5. 检查以下内容

1) ORACLE_SID是否设置正确

2) oratab文件是否指向新的ORACLE_HOME

[oracle@cndba ~]$ tail -1 /etc/oratab

orcl:/u02/app/oracle/product/18.1.0/dbhome_1:N

3) 以下环境变量都要指向新的Oracle数据库目录

l ORACLE_HOME

l PATH

[oracle@cndba ~]$ env|grep ORACLE_HOME

ORACLE_HOME=/u02/app/oracle/product/18.1.0/dbhome_1

[oracle@cndba ~]$ env|grep ORACLE_SID

ORACLE_SID=orcl

[oracle@cndba ~]$ env|grep PATH

LD_LIBRARY_PATH=/u02/app/oracle/product/18.1.0/dbhome_1/lib:/lib:/usr/lib:/u02/app/oracle/ogg/lib

PATH=/usr/sbin:/usr/sbin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/u01/app/oracle/product/11.2.0/db_1/bin:/u02/app/oracle/product/18.1.0/dbhome_1/bin

CLASSPATH=/u02/app/oracle/product/18.1.0/dbhome_1/jlib:/u02/app/oracle/product/18.1.0/dbhome_1/rdbms/jlib

1.1.6. 切换到新的ORACLE_HOME下

[oracle@cndba ~]$ cd $ORACLE_HOME/rdbms/admin

[oracle@cndba admin]$ pwd

/u02/app/oracle/product/18.1.0/dbhome_1/rdbms/admin

[oracle@cndba admin]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 Production on Tue Jul 10 16:54:05 2018

Version 18.1.0.0.0

Connected to an idle instance.

SQL>

1.1.7. 将spfile拷贝到新的ORACLE_HOME下，以UPGRADE方式打开数据库

$cp /u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora /u02/app/oracle/product/18.1.0/dbhome_1/dbs/

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 1015019680 bytes

Fixed Size 8665248 bytes

Variable Size 650117120 bytes

Database Buffers 352321536 bytes

Redo Buffers 3915776 bytes

Database mounted.

Database opened.

1.1.8. 退出SQLPLUS，执行升级脚本

SQL> exit

[oracle@cndba admin]$ cd /u02/app/oracle/product/18.1.0/dbhome_1/bin/

从12.2开始可以通过dbupgrade命令来调用catctl.pl脚本升级。

-n表示指定用于并行操作的进程数，默认值和CPU_COUNT参数值一样。

[oracle@cndba bin]$ ./dbupgrade -n 4 -d /u02/app/oracle/product/18.1.0/dbhome_1/rdbms/admin/ -l /tmp/

Argument list for [/u02/app/oracle/product/18.1.0/dbhome_1/rdbms/admin/catctl.pl]

Run in c = 0

Do not run in C = 0

Input Directory d = /u02/app/oracle/product/18.1.0/dbhome_1/rdbms/admin/

Echo OFF e = 1

Simulate E = 0

Forced cleanup F = 0

Log Id i = 0

Child Process I = 0

Log Dir l =/tmp --日志文件存放目录

Priority List Name L = 0

Upgrade Mode active M = 0

SQL Process Count n = 4 --和CPU_COUNT参数一样即可

SQL PDB Process Count N = 0

Open Mode Normal o = 0

Start Phase p = 0

End Phase P = 0

Reverse Order r = 0

AutoUpgrade Resume R = 0

Script s = 0

Serial Run S = 0

RO User Tablespaces T = 0

Display Phases y = 0

Debug catcon.pm z = 0

Debug catctl.pl Z = 0

catctl.pl VERSION: [18.0.0.0.0]

STATUS: [Production]

BUILD: [RDBMS_18.1CLOUD_LINUX.X64_180131.2]

/u02/app/oracle/product/18.1.0/dbhome_1/rdbms/admin/orahome = [/u02/app/oracle/product/18.1.0/dbhome_1]

/u02/app/oracle/product/18.1.0/dbhome_1/bin/orabasehome = [/u02/app/oracle/product/18.1.0/dbhome_1]

catctlGetOrabase = [/u02/app/oracle/product/18.1.0/dbhome_1]

Analyzing file /u02/app/oracle/product/18.1.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20180710171737]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20180710171737/catupgrd_catcon_13545.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20180710171737/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20180710171737/catupgrd_*.lst] files for spool files, if any

Number of Cpus = 1

Database Name = orcl

DataBase Version = 11.2.0.3.0

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u02/app/oracle/product/18.1.0/dbhome_1/cfgtoollogs/orcl/upgrade20180710171739/catupgrd_catcon_13545.lst]

catcon::set_log_file_base_path: catcon: See [/u02/app/oracle/product/18.1.0/dbhome_1/cfgtoollogs/orcl/upgrade20180710171739/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u02/app/oracle/product/18.1.0/dbhome_1/cfgtoollogs/orcl/upgrade20180710171739/catupgrd_*.lst] files for spool files, if any

Log file directory = [/u02/app/oracle/product/18.1.0/dbhome_1/cfgtoollogs/orcl/upgrade20180710171739]

Parallel SQL Process Count = 4

Components in [orcl]

Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ]

Not Installed [DV EM MGW ODM OLS RAC WK]

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

Phases [0-108] 分108阶段升级 Start Time:[2018_07_10 17:17:40]

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

*********** Executing Change Scripts ***********

Serial Phase #:0 [orcl] Files:1

Time: 215s

*************** Catalog Core SQL ***************

Serial Phase #:1 [orcl] Files:5 Time: 85s

Restart Phase #:2 [orcl] Files:1 Time: 1s

*********** Catalog Tables and Views ***********

Parallel Phase #:3 [orcl] Files:19 Time: 39s

Restart Phase #:4 [orcl] Files:1 Time: 0s

************* Catalog Final Scripts ************

Serial Phase #:5 [orcl] Files:7 Time: 30s

***************** Catproc Start ****************

Serial Phase #:6 [orcl] Files:1

省略……..

**************** Summary report ****************

Serial Phase #:105 [orcl] Files:1 Time: 1s

*** End PDB Application Upgrade Post-Shutdown **

Serial Phase #:106 [orcl] Files:1 Time: 1s

Serial Phase #:107 [orcl] Files:1 Time: 0s

Serial Phase #:108 [orcl] Files:1 Time: 25s

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

Phases [0-108] End Time:[2018_07_10 18:21:38]

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

Grand Total Time: 3841s

LOG FILES: (/u02/app/oracle/product/18.1.0/dbhome_1/cfgtoollogs/orcl/upgrade20180710171739/catupgrd*.log)

Upgrade Summary Report Located in:

/u02/app/oracle/product/18.1.0/dbhome_1/cfgtoollogs/orcl/upgrade20180710171739/upg_summary.log

Grand Total Upgrade Time: [0d:1h:4m:1s]

1.1.9. 打开数据库

在执行完升级脚本后，会自动将数据库关闭，所以需要手动启动数据库。这个重启过程，Oracle刷新所有缓存，清除缓冲区，并执行其他内务活动。这些动作都是为了保证更新后的数据库的整体性和一致性。

SQL> STARTUP

1.1.10. 查看数据库版本

SQL> select * from v$version;

BANNER

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

BANNER_FULL

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

BANNER_LEGACY

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

CON_ID

----------

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.1.0.0.0

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

1.1.11. 重新编译PL/SQL和java代码

[oracle@cndba admin]$ pwd

/u02/app/oracle/product/18.1.0/dbhome_1/rdbms/admin

[oracle@cndba admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u02/app/oracle/product/18.1.0/dbhome_1/rdbms/admin/utlrp_catcon_8519.lst]

catcon::set_log_file_base_path: catcon: See [/u02/app/oracle/product/18.1.0/dbhome_1/rdbms/admin/utlrp*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u02/app/oracle/product/18.1.0/dbhome_1/rdbms/admin/utlrp_*.lst] files for spool files, if any

catcon.pl: completed successfully

1.1.12. 执行脚本

升级完成后，需要执行前面的修复脚本postupgrade_fixups.sql。如果有没有自动修复成功，则需要手动修复。

SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script

Version: 18.0.0.0.0 Build: 1

Generated on: 2018-07-10 15:01:39

For Source Database: ORCL

Source Database Version: 11.2.0.3.0

For Upgrade to Version: 18.0.0.0.0

Preup Preupgrade

Action Issue Is

Number Preupgrade Check Name Remedied Further DBA Action

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

11. old_time_zones_exist NO Manual fixup recommended.

12. post_dictionary YES None.

13. post_fixed_objects NO Informational only.

Further action is optional.

14. upg_by_std_upgrd YES None.

The fixup scripts have been run and resovled what they can. However,

there are still issues originally identified by the preupgrade that

have not been remedied and are still present in the database.

Depending on the severity of the specific issue, and the nature of

the issue itself, that could mean that your database upgrade is not

fully complete. To resolve the outstanding issues, start by reviewing

the postupgrade_fixups.sql and searching it for the name of

the failed CHECK NAME or Preupgrade Action Number listed above.

There you will find the original corresponding diagnostic message

from the preupgrade which explains in more detail what still needs

to be done.

PL/SQL procedure successfully completed.

Elapsed: 00:16:09.55

Session altered.

Elapsed: 00:00:00.01

可以看到有两个问题需要手动去修复：time zone版本问题和无效对象。

1.1.12.1. 更新TIMEZONE版本

首先从MOS上下载所需的升级脚本(文档 ID 1585343.1)，从Oracle 11.2开始提供了自动升级的脚本，非常方便。

l 查看当前版本

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

l 解压文件，执行检查脚本

[oracle@cndba DBMS_DST_scriptsV1.9]$ ll

total 68

-rw-r--r-- 1 oracle oinstall 6294 Jan 8 2015 countstarTSTZ.sql

-rw-r--r-- 1 oracle oinstall 7213 Mar 17 18:30 countstatsTSTZ.sql

-rw-r--r-- 1 oracle oinstall 19502 Aug 22 2014 upg_tzv_apply.sql

-rw-r--r-- 1 oracle oinstall 31010 Aug 22 2014 upg_tzv_check.sql

a) 检查当前环境

SQL> @/software/DBMS_DST_scriptsV1.9/upg_tzv_check.sql

INFO: Starting with RDBMS DST update preparation.

INFO: NO actual RDBMS DST update will be done by this script.

INFO: If an ERROR occurs the script will EXIT sqlplus.

INFO: Doing checks for known issues ...

INFO: Database version is 18.0.0.0 .

INFO: Database RDBMS DST version is DSTv14 .

INFO: No known issues detected.

INFO: Now detecting new RDBMS DST version.

A prepare window has been successfully started.

INFO: Newest RDBMS DST version detected is DSTv31 .

INFO: Next step is checking all TSTZ data.

INFO: It might take a while before any further output is seen ...

A prepare window has been successfully ended.

INFO: A newer RDBMS DST version than the one currently used is found.

INFO: Note that NO DST update was yet done.

INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.

INFO: Note that the upg_tzv_apply.sql script will

INFO: restart the database 2 times WITHOUT any confirmation or prompt.

b) 更新TIMEZONE版本

这里需要注意，执行该脚本会自动重启数据库两次。如下：

SQL> @/software/DBMS_DST_scriptsV1.9/upg_tzv_apply.sql

INFO: If an ERROR occurs the script will EXIT sqlplus.

INFO: The database RDBMS DST version will be updated to DSTv31 .

WARNING: This script will restart the database 2 times

WARNING: WITHOUT asking ANY confirmation.

WARNING: Hit control-c NOW if this is not intended.

INFO: Restarting the database in UPGRADE mode to start the DST upgrade.

INFO: Upgrading all non-SYS TSTZ data.

INFO: It might take time before any further output is seen ...

INFO: Do NOT start any application yet that uses TSTZ data!

INFO: Next is a list of all upgraded tables:

Table list: "APEX_050100"."WWV_FLOW_DEBUG_MESSAGES"

Number of failures: 0

Table list: "APEX_050100"."WWV_FLOW_DEBUG_MESSAGES2"

Number of failures: 0

Table list: "APEX_050100"."WWV_FLOW_FEEDBACK"

Number of failures: 0

Table list: "APEX_050100"."WWV_FLOW_FEEDBACK_FOLLOWUP"

Number of failures: 0

Table list: "APEX_050100"."WWV_FLOW_WORKSHEET_NOTIFY"

Number of failures: 0

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"

Number of failures: 0

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"

Number of failures: 0

INFO: Total failures during update of TSTZ data: 0 .

An upgrade window has been successfully ended.

INFO: Your new Server RDBMS DST version is DSTv31 .

INFO: The RDBMS DST update is successfully finished.

INFO: Make sure to exit this sqlplus session.

INFO: Do not use it for timezone related selects.

l 检查版本

版本号已成功更新为31

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

1 row selected.

1.1.12.2. 收集固定对象的统计信息

固定对象的统计信息能够为Oracle优化器提供必需的信息用来找到更有效的执行计划。

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

PL/SQL procedure successfully completed.

1.1.13. 查看组件是否正常

执行脚本utlu122s.sql验证所有组件是否工作正常。

SQL> @$ORACLE_HOME/rdbms/admin/utlu122s.sql

19:30:01 SQL> @$ORACLE_HOME/rdbms/admin/utlu122s.sql

Oracle Database Release 18 Post-Upgrade Status Tool 07-10-2018 19:30:0

Component Current Full Elapsed Time

Name Status Version HH:MM:SS

Oracle Server VALID 18.1.0.0.0 00:28:06

JServer JAVA Virtual Machine VALID 18.1.0.0.0 00:06:31

Oracle XDK VALID 18.1.0.0.0 00:00:55

Oracle Database Java Packages VALID 18.1.0.0.0 00:00:27

OLAP Analytic Workspace VALID 18.1.0.0.0 00:00:49

Oracle Text VALID 18.1.0.0.0 00:01:28

Oracle Workspace Manager VALID 18.1.0.0.0 00:02:00

Oracle Real Application Clusters OPTION OFF 18.1.0.0.0 00:00:00

Oracle XML Database VALID 18.1.0.0.0 00:03:43

Oracle Multimedia VALID 18.1.0.0.0 00:03:12

Spatial VALID 18.1.0.0.0 00:07:48

Oracle OLAP API VALID 18.1.0.0.0 00:00:37

Upgrade Datapatch 00:00:21

Final Actions 00:04:56

Post Upgrade 00:00:43

Post Upgrade Datapatch 00:00:14

Post Compile 00:25:19

Total Upgrade Time: 01:27:43

Database time zone version is 14. It is older than current release time

zone version 31. Time zone upgrade is needed using the DBMS_DST package.

Summary Report File = /u02/app/oracle/product/18.1.0/dbhome_1/cfgtoollogs/orcl/upgrade20180710171739/upg_summary.log

可以看到都正常。

1.1.14. 查看无效对象

SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type;

OWNER OBJECT_TYPE COUNT(*)

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

APEX_050100 PACKAGE BODY 2

SCOTT FUNCTION 1

2 rows selected.

1.1.15. 修改COMPATITLE参数

在所有测试完成后且结果都正常，那么就可以将COMPATITLE参数设置为18.1.0。该操作需要重启才能生效。再次打开数据库过程中Oracle会做一个“名称转换”操作，该操作需要几分钟时间。

l 查看COMPATITLE

SQL> show parameter compatible

NAME TYPE VALUE

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

compatible string 11.2.0.0.0

noncdb_compatible boolean FALSE

l 修改COMPATITLE

SQL> alter system set compatible='18.0.0' scope=spfile;

System altered.

l 重启之前要删除所有的还原点

SQL> col name for a30

SQL> select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from v$restore_point;

SCN GUA NAME

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

1493536 YES GRP_1531210432335

1 row selected.

SQL> drop restore point GRP_1531210432335;

Restore point dropped.

l 重启数据库

SQL> shutdown immediate

SQL> startup

l 再次查看COMPATITLE

COMPATITLE参数一旦修改了，就无法降级数据库到原来的版本了。

SQL> show parameter compatible

NAME TYPE VALUE

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

compatible string 18.0.0

noncdb_compatible boolean FALSE

注：当Oracle Database COMPATIBLE参数更新到18.0.0时，对数据库的第一次Java调用将启动“名称转换”操作。此操作可能需要几分钟才能完成。在增加兼容性参数之后，第一次对数据库进行Java调用时，应该会出现此延迟。执行名称转换的初始延迟仅在初始Java调用期间发生。

Oracle 18c 升级

签到成功

CNDBA社区