Oracle 11.2.0.4单实例数据库升级18C
源数据库 | 目标数据库 | |
---|---|---|
ORACLE_HOME目录 | /u02/app/oracle/product/11.2.0/db_1/ | /u01/app/oracle/product/18.3.0/db_1/ |
数据库版本 | 11.2.0.4 | 18.3.0 |
SID | ora11g | ora11g |
在将11.2.0.4升级到18C过程中,需要确保18C的数据库软件已经安装,不必dbca创建实例。附之前博客中18C安装博文
https://blog.csdn.net/kiral07/article/details/87183986
1、在Oracle 11g目录执行预升级环境检查
[ora11g@ora18c ~]$ /u01/app/oracle/product/18.3.0/db_1/jdk/bin/java -jar
/u01/app/oracle/product/18.3.0/db_1/rdbms/admin/preupgrade.jar
==================
PREUPGRADE SUMMARY
==================
/u02/app/oracle/cfgtoollogs/ora11g/preupgrade/preupgrade.log
/u02/app/oracle/cfgtoollogs/ora11g/preupgrade/preupgrade_fixups.sql
/u02/app/oracle/cfgtoollogs/ora11g/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade log into the database and execute the preupgrade fixups
@/u02/app/oracle/cfgtoollogs/ora11g/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u02/app/oracle/cfgtoollogs/ora11g/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2019-04-04T16:24:24
2、执行预安装自动修复脚本
[ora11g@ora18c ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 4 16:26:02 2019
Copyright © 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
自动修复脚本
SQL> @/u02/app/oracle/cfgtoollogs/ora11g/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: 2019-04-04 16:24:17
For Source Database: ORA11G
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 18.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. purge_recyclebin YES None.
2. em_present NO Manual fixup recommended.
3. amd_exists NO Manual fixup recommended.
4. apex_manual_upgrade NO Manual fixup recommended.
5. dictionary_stats YES None.
6. trgowner_no_admndbtrg NO Informational only.
Further action is optional.
7. pre_fixed_objects YES None.
8. tablespaces_info NO Informational only.
Further action is optional.
9. min_archive_dest_size 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.
以上输出结果为NO的需要我们手动修复
3、移除EM
SET ECHO ON;
SET SERVEROUTPUT ON;
@/u01/app/oracle/product/18.3.0/db_1/rdbms/admin/emremove.sql
4、升级APEX组件,需要手动更新
下载APEX:http://www.oracle.com/technetwork/developer-tools/apex/all-archives-099381.html
选择18.1版本即可,升级过程在15min左右
进入到apex目录并以sysdba升级脚本
[ora11g@ora18c apex]$ unzip apex_18.1_en.zip
[ora11g@ora18c apex]$ pwd
/u02/apex/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 18.1.0.00.45
5、移除OLAP Catalog
SQL> @$ORACLE_HOME/olap/admin/catnoamd.sql
6、检查数据库是否开启归档模式以及闪回,如未开启请开启
如遇数据库升级失败可通过闪回或者RMAN备份进行回滚
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=10g scope=spfile;
SQL> alter system set db_recovery_file_dest='/u02/fra' scope=spfile;
SQL> alter database flashback on;
Database altered.
select log_mode,flashback_on from v$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG YES
7、执行dbua数据库升级
/u01/app/oracle/product/18.3.0/db_1/bin/dbua
选择实例
忽略警告
由于已开启了数据库闪回,如果升级失败可通过闪回恢复数据库
选择18c的监听
升级耗时1小时
8、升级完成后检查数据库版本
SQL> select banner_full from v$version;
BANNER_FULL
-----------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> set pagesize 200 linesize 200
col time for a30
col action for a6
col version for a10
col BUNDLE_SERIES for a5
col COMMENTS for a30
SQL> select to_char(ACTION_TIME,'yyyy-MM-dd hh24:mi:ss') time,action,VERSION,BUNDLE_SERIES,COMMENTS from dba_registry_history;
TIME ACTION VERSION BUNDL COMMENTS
------------------------------ -------------------- ---------- ----- -------------------------------------------------
2013-08-24 12:03:45 APPLY 11.2.0.4 PSU Patchset 11.2.0.2.0
2019-03-03 22:28:16 APPLY 11.2.0.4 PSU Patchset 11.2.0.2.0
2019-03-04 09:54:15 APPLY 11.2.0.4 PSU PSU 11.2.0.4.181016
BOOTSTRAP 18 RDBMS_18.3.0.0.0DBRU_LINUX.X64_180627
2019-04-05 11:18:47 VIEW INVALIDATE view invalidation
2019-04-05 11:22:11 UPGRADE 18.0.0.0.0 Upgraded from 11.2.0.4.0 to 18.3.0.0.0
2019-04-05 11:23:44 jvmpsu.sql 18.3.0.0.1 RAN jvmpsu.sql
80717OJVMR
U
2019-04-05 11:23:44 APPLY 18.3.0.0.1 OJVM RU post-install
80717OJVMR
U
参考:
如何下载并运行Oracle数据库预升级实用程序 (文档 ID 1577379.1)
Database Preupgrade tool check list. (文档 ID 2380601.1)
The Graphical User Interface Method for Upgrading Oracle Database
https://docs.oracle.com/en/database/oracle/oracle-database/18/upgrd/choose-an-upgrade-method-for-oracle-database.html#GUID-814C68FB-7802-4B25-957E-6253757ACC7C