Oralce数据库升级-RedHat 7.4 从Oracle 11.2.0.4升级到18.0.0(DBUA)

最新推荐文章于 2024-08-16 08:51:28 发布

执笔画情ora

最新推荐文章于 2024-08-16 08:51:28 发布

阅读量371

点赞数

原文链接：https://blog.csdn.net/qianglei6077/article/details/86711141

版权

DBUA是Oracle极力推荐使用的升级工具，如果环境允许的话尽量使用DBUA来升级数据库。可以升级CDB,PDB,non-CDB。

1 安装18c数据库软件

2 升级前检查

运行检查脚本

脚本可以从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 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_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

（2）移除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无法确定在哪里创建文件。