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
安装18c数据库软件
- 新的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
- 创建新目录
mkdir -p /u02/app/oracle/product/18.1.0/dbhome_1
chown -R oracle:oinstall /u02
chmod -R 775 /u02/
- 解压18c到新的ORACLE_HOME下
unzip -d /u02/app/oracle/product/18.1.0/dbhome_1 Oracle18.1.0.zip
- 安装数据库软件
只安装数据库软件 - 如果是单实例则选择第一个
- 选择企业版
- 修改为新的ORACLE_BASE目录
- 默认
- 安装前检查，内存不符合要求。18c建议的内存为8G
- 正在安装
- 执行root.sh脚本
升级前检查
- 运行查脚本
脚本可以从MOS(ID 884522.1)中下载，也可以在新的$ORACLE_HOME/rdbms/admin/中找到，直接在源库上运行即可，然后根据检查结果修改即可。
- 在源库上执行脚本检查
[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脚本来修复。
- 下面截取部分日志：
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 Statu
---------------- -------------- --------------
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
- 执行修复脚本
会自动修复不满足升级条件的问题，如果有没有修复的问题需要手动去修复。
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.
- 修改DB_RECOVERY_FILE_DEST_SIZE
alter system set DB_RECOVERY_FILE_DEST_SIZE=10g scope=spfile;
- 修改processes
alter system set processes=300 scope=spfile;
- 移除EM
SET ECHO ON;
SET SERVEROUTPUT ON;
@/u02/app/oracle/product/18.1.0/dbhome_1/rdbms/admin/emremove.sql
- 升级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
- 移除OLAP Catalog
从12c开始，不再支持OLAP Catalog。执行脚本移除
@$ORACLE_HOME/olap/admin/catnoamd.sql
- 再次检查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
- 开启归档和闪回
查看是否开启闪回和归档
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;
(可选)如果是RAC，则修改CLUSTER_DATABASE为FALSE
ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;
关闭源库
SQL> SHUTDOWN IMMEDIATE
检查以下内容
- ORACLE_SID是否设置正确
- oratab文件是否指向新的ORACLE_HOME
[oracle@cndba ~]$ tail -1 /etc/oratab
orcl:/u02/app/oracle/product/18.1.0/dbhome_1:N
- 以下环境变量都要指向新的Oracle数据库目录
- ORACLE_HOME
- 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
切换到新的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 sysdb
SQL*Plus: Release 18.0.0.0.0 Production on Tue Jul 10 16:54:05 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to an idle instance
SQL>
将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.
退出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] --一共一小时4分钟
打开数据库
在执行完升级脚本后，会自动将数据库关闭，所以需要手动启动数据库。这个重启过程，Oracle刷新所有缓存，清除缓冲区，并执行其他内务活动。这些动作都是为了保证更新后的数据库的整体性和一致性。
SQL> STARTUP
查看数据库版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
CON_ID
----------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
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
0
重新编译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
执行脚本
升级完成后，需要执行前面的修复脚本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版本问题和无效对象。
更新TIMEZONE版本
首先从MOS上下载所需的升级脚本(文档 ID 1585343.1)，从Oracle 11.2开始提供了自动升级的脚本，非常方便。
- 查看当前版本
SQL> SELECT version FROM v$timezone_file;
VERSION
---------
14
- 解压文件，执行检查脚本
[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.
- 检查版本
版本号已成功更新为31
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
31
1 row selected.
收集固定对象的统计信息
固定对象的统计信息能够为Oracle优化器提供必需的信息用来找到更有效的执行计划。
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
PL/SQL procedure successfully completed.
查看组件是否正常
执行脚本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
可以看到都正常。
查看无效对象
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.
修改COMPATITLE参数
在所有测试完成后且结果都正常，那么就可以将COMPATITLE参数设置为18.1.0。该操作需要重启才能生效。再次打开数据库过程中Oracle会做一个“名称转换”操作，该操作需要几分钟时间。
- 查看COMPATITLE
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.0.0
noncdb_compatible boolean FALSE
- 修改COMPATITLE
SQL> alter system set compatible='18.0.0' scope=spfile;
System altered.
- 重启之前要删除所有的还原点
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.
- 重启数据库
SQL> shutdown immediate
SQL> startup
- 再次查看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调用期间发生。
