Redhat 7.4 Oracle 11.2.0.4升级到18c non-CDB(手动)

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数据库软件

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. 运行查脚本

脚本可以从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:39Upgrade-To version: 18.0.0.0.0=======================================
Status of the database prior to upgrade
=======================================Database Name:  ORCLContainer Name:  Not Applicable in Pre-12.1 databaseContainer ID:  Not Applicable in Pre-12.1 databaseVersion:  11.2.0.3.0Compatible:  11.2.0.0.0Blocksize:  8192Platform:  Linux x86 64-bitTimezone File:  14Database log mode:  ARCHIVELOGReadonly:  FALSEEdition:  EEOracle 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 theirspecific upgrade procedure.The database upgrade script will not upgrade the following Oraclecomponents:  OLAP Catalog,OWBThe Oracle database upgrade script upgrades most, but not all OracleDatabase components that may be installed.  Some components that are notupgraded may have their own upgrade scripts, or they may be deprecated orobsolete.ORACLE GENERATED FIXUP SCRIPT=============================All of the issues in database ORCLwhich are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved byexecuting 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 ScriptVersion: 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

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;

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

ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;

关闭源库

SQL> SHUTDOWN IMMEDIATE

检查以下内容

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数据库目录

• 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 = 0Do not run in         C = 0Input Directory       d = /u02/app/oracle/product/18.1.0/dbhome_1/rdbms/admin/Echo OFF              e = 1Simulate              E = 0Forced cleanup        F = 0Log Id                i = 0Child Process         I = 0Log Dir               l =/tmp  --日志文件存放目录Priority List Name    L = 0Upgrade Mode active   M = 0SQL Process Count     n = 4   --和CPU_COUNT参数一样即可SQL PDB Process Count N = 0Open Mode Normal      o = 0Start Phase           p = 0End Phase             P = 0Reverse Order         r = 0AutoUpgrade Resume    R = 0Script                s = 0Serial Run            S = 0RO User Tablespaces   T = 0Display Phases        y = 0Debug catcon.pm       z = 0Debug catctl.pl       Z = 0catctl.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.sqlLog 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 scriptscatcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20180710171737/catupgrd_*.lst] files for spool files, if anyNumber of Cpus        = 1Database Name         = orclDataBase Version      = 11.2.0.3.0catcon::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 scriptscatcon::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 anyLog file directory = [/u02/app/oracle/product/18.1.0/dbhome_1/cfgtoollogs/orcl/upgrade20180710171739]Parallel SQL Process Count            = 4Components 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:1Time: 215s***************   Catalog Core SQL   ***************Serial   Phase #:1    [orcl] Files:5    Time: 85sRestart  Phase #:2    [orcl] Files:1    Time: 1s***********   Catalog Tables and Views   ***********Parallel Phase #:3    [orcl] Files:19   Time: 39sRestart  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: 1sSerial   Phase #:107  [orcl] Files:1    Time: 0sSerial   Phase #:108  [orcl] Files:1     Time: 25s------------------------------------------------------Phases [0-108]         End Time:[2018_07_10 18:21:38]------------------------------------------------------Grand Total Time: 3841sLOG 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.logGrand 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 - Production0

重新编译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 scriptscatcon::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 anycatcon.pl: completed successfully

执行脚本

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

SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sqlExecuting Oracle POST-Upgrade Fixup ScriptAuto-Generated by:       Oracle Preupgrade ScriptVersion: 18.0.0.0.0 Build: 1
Generated on:            2018-07-10 15:01:39For Source Database:     ORCL
Source Database Version: 11.2.0.3.0
For Upgrade to Version:  18.0.0.0.0Preup                             PreupgradeAction                            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 thathave 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.55Session 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.sqlINFO: 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 willINFO: restart the database 2 times WITHOUT any confirmation or prompt.

b) 更新TIMEZONE版本

这里需要注意，执行该脚本会自动重启数据库两次。如下：SQL> @/software/DBMS_DST_scriptsV1.9/upg_tzv_apply.sqlINFO: 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 timesWARNING: 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: 0Table list: "APEX_050100"."WWV_FLOW_DEBUG_MESSAGES2"Number of failures: 0Table list: "APEX_050100"."WWV_FLOW_FEEDBACK"Number of failures: 0Table list: "APEX_050100"."WWV_FLOW_FEEDBACK_FOLLOWUP"Number of failures: 0Table list: "APEX_050100"."WWV_FLOW_WORKSHEET_NOTIFY"Number of failures: 0Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"Number of failures: 0Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"Number of failures: 0INFO: 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----------311 row selected.

收集固定对象的统计信息

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

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATSPL/SQL procedure successfully completed.

查看组件是否正常

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

SQL> @$ORACLE_HOME/rdbms/admin/utlu122s.sql19:30:01 SQL> @$ORACLE_HOME/rdbms/admin/utlu122s.sqlOracle Database Release 18 Post-Upgrade Status Tool    07-10-2018 19:30:0Component                               Current         Full     Elapsed TimeName                                    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:19Total Upgrade Time: 01:27:43Database 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        2SCOTT                FUNCTION            12 rows selected.

修改COMPATITLE参数

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

• 查看COMPATITLE

SQL> show parameter compatibleNAME                   TYPE        VALUE------------------------------------ ----------- ------------------------------compatible               string      11.2.0.0.0noncdb_compatible        boolean     FALSE

• 修改COMPATITLE

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

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

SQL> col name for a30SQL> select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from v$restore_point;SCN    GUA     NAME------------ ---------  ------------------------------1493536  YES    GRP_15312104323351 row selected.SQL> drop restore point GRP_1531210432335;Restore point dropped.

• 重启数据库

SQL> shutdown immediateSQL> startup

• 再次查看COMPATITLE

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

SQL> show parameter compatibleNAME                   TYPE        VALUE------------------------------------ ----------- ------------------------------compatible               string      18.0.0noncdb_compatible        boolean     FALSE

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

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！