Redhat7。x从11。2。0。4升级到18。0。0（DBUA）

　　DBUA是Oracle极力推荐使用的升级工具，如果环境允许的话尽量使用DBUA来升级数据库。可以升级CDB,PDB,non-CDB。安装18c数据库软件新的Oracle环境变量：ORACLE_BASE=/u02/app/oracle ORACLE_HOME= /u02/app/oracle/product/18.1.0/dbhome_1 ORACLE_SID=cndba资源配置：[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.zipDBCA安装数据库软件
　　只安装数据库软件
　　如果是单实例则选择第一个
　　选择企业版
　　修改为新的ORACLE_BASE目录
　　默认
　　安装前检查，内存不符合要求。18c建议的内存为8G
　　正在安装
　　执行root.sh脚本
　　升级前检查运行检查脚本
　　脚本可以从MOS(ID 884522.1)中下载，也可以在新的$ORACLE_HOME/rdbms/admin/中找到，直接在源库上运行即可，然后根据检查结果修改即可。在源库上执行脚本检查[oracle@cndba software]nbsp;/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 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执行修复脚本
　　会自动修复不满足升级条件的问题，如果有没有修复的问题需要手动去修复。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_SIZEalter system set DB_RECOVERY_FILE_DEST_SIZE=10g scope=spfile;修改processesalter system set processes=300 scope=spfile;移除EMSET 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;DBUA升级
　　在升级期间，DBUA会自动运行升级脚本并最小化停机时间。
　　在先决条件阶段，DBUA运行升级前检查脚本，并使用以下逻辑修改或创建所需的新表空间:如果数据文件是自动扩展的，并且有足够的磁盘空间来扩展，那么DBUA将继续升级。如果数据文件不是自动扩展的，那么DBUA会提示你并使文件自动扩展。如果表空间是自动扩展的，并且MAXSIZE初始化参数需要调整，那么DBUA会提示进行调整，并调整MAXSIZE参数。如果没有足够的磁盘空间来增长，那么DBUA会提示你通过添加更多的数据文件来创建空间。DBUA不会自动添加新的数据文件，因为DBUA无法确定在哪里创建文件。切换到新的ORACLE_HOME下运行dbuacd /u02/app/oracle/product/18.1.0/dbhome_1/bin/ ./dbua
　　选择要升级的数据库，并输入sysdba用户及密码
　　dbua再次执行升级前的检查脚本
　　检查结果，要确保满足升级条件：这里的无效对象是非SYS/SYSTEM用户的是升级APEX造成的。生效一个升级期间会自动修复。
　　DBUA会检查以下内容：回收站里是否有对象无效对象废弃和不支持的初始化参数时间区域数据文件版本
　　并行升级：根据CPU的个数来决定并行数，从而加快升级的速度
　　重新编译无效对象：在升级完成后，Oracle会自动编译无效对象 。如果没有勾选，则需要在升级后手动编译。
　　更新Time Zone：会更新Time Zone数据文件版本 。如果没有勾选，则手动更新。
　　创建一个还原点或已有的还原点，如果升级失败可以将数据库还原到原来状态。
　　创建一个RMAN全库备份或已有的备份。
　　有自己的备份和恢复策略。
　　使用之前的监听
　　这里不配置EM，可根据自己的需要配置
　　检查升级数据库的概要信息
　　开始升级，由于是虚拟机，升级更慢
　　查看组件是否正常
　　执行脚本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.