Linux下Oracle 11g升级19c实录

听雪楼主.

已于 2024-08-16 14:44:28 修改

阅读量1.4k

点赞数 8

CC 4.0 BY-SA版权

文章标签： oracle 数据库数据库升级

于 2024-08-16 08:51:28 首次发布

本文链接：https://blog.youkuaiyun.com/DBDeep/article/details/141243211

1.组件信息

source /home/oracle/.bash_profile11g && sqlplus "/ as sysdba"<<EOF

set line 200

col COMP_NAME for a40

select comp_name,VERSION,STATUS from dba_registry;

exit;

EOF

COMP_NAME VERSION STATUS

---------------------------------------- ------------------------------ ----------------------

OWB 11.2.0.4.0 VALID

Oracle Application Express 3.2.1.00.12 VALID

Oracle Enterprise Manager 11.2.0.4.0 VALID

OLAP Catalog 11.2.0.4.0 VALID

Spatial 11.2.0.4.0 VALID

Oracle Multimedia 11.2.0.4.0 VALID

Oracle XML Database 11.2.0.4.0 VALID

Oracle Text 11.2.0.4.0 VALID

Oracle Expression Filter 11.2.0.4.0 VALID

Oracle Rules Manager 11.2.0.4.0 VALID

Oracle Workspace Manager 11.2.0.4.0 VALID

COMP_NAME VERSION STATUS

---------------------------------------- ------------------------------ ----------------------

Oracle Database Catalog Views 11.2.0.4.0 VALID

Oracle Database Packages and Types 11.2.0.4.0 VALID

JServer JAVA Virtual Machine 11.2.0.4.0 VALID

Oracle XDK 11.2.0.4.0 VALID

Oracle Database Java Packages 11.2.0.4.0 VALID

OLAP Analytic Workspace 11.2.0.4.0 VALID

Oracle OLAP API 11.2.0.4.0 VALID

18 rows selected.

废弃的组件：EM

从18c开始,Oracle不会自动更新APEX组件，需要手动更新。

OLAP删除

2.查看刷新的物化视图

source /home/oracle/.bash_profile11g && sqlplus "/ as sysdba"<<EOF

SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

exit;

EOF

升级前确保无物化视图正在刷新

SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

no rows selected

3.删除EM组件

source /home/oracle/.bash_profile11g && emctl stop dbconsole &&sqlplus '/ as sysdba'<<EOF

SET ECHO ON;

SET SERVEROUTPUT ON;

@/u01/app/oracle/product/19c/dbhome_1/rdbms/admin/emremove.sql

exit;

EOF

$ cd $ORACLE_HOME

$ rm -rf HOSTNAME_SID

$ rm -rf oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID

结果：

[oracle@19crac1 ~]$ source /home/oracle/.bash_profile11g && sqlplus '/ as sysdba'<<EOF

> @/u01/app/oracle/product/19c/dbhome_1/rdbms/admin/emremove.sql

> exit;

> EOF

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 9 17:31:40 2019

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> old 70: IF (upper('&LOGGING') = 'VERBOSE')

new 70: IF (upper('VERBOSE') = 'VERBOSE')

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

4.删除OLAP组件

source /home/oracle/.bash_profile11g && sqlplus "/ as sysdba"<<EOF

@?/olap/admin/catnoamd.sql;

exit;

EOF

5.手动升级APEX组件

Ø 下载补丁包：http://www.oracle.com/technetwork/developer-tools/apex/all-archives-099381.html

[oracle@19crac1 ~]$ source .bash_profile11g

[oracle@19crac1 ~]$ cd apex

[oracle@19crac1 apex]$ ls -ltr

[oracle@19crac1 apex]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 9 20:16:40 2019

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/

...set_appun.sql

PL/SQL procedure successfully completed.

检查APEX版本

source /home/oracle/.bash_profile11g && sqlplus '/ as sysdba'<<EOF

COL COMP_NAME FORMAT A40

COL STATUS FORMAT A12

Set lines 200

SELECT COMP_NAME, STATUS, VERSION FROM DBA_REGISTRY WHERE COMP_NAME='Oracle Application Express';

exit;

EOF

结果

COMP_NAME STATUS VERSION

---------------------------------------- ------------ ------------------------------

Oracle Application Express VALID 3.2.1.00.12

Ø 一定要注意归档空间的使用率，有可能归档满了，升级卡住

Ø 详细日志

6.确认兼容性参数

source /home/oracle/.bash_profile11g && sqlplus '/ as sysdba'<<EOF

show parameter compatible;

exit;

EOF

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL>

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

compatible string 11.2.0.4.0

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

注意升级 19c 的 COMPATIBLE 参数的最小值为“11.2.0”, 确保您的 COMPATIBLE 参数设置为11.2.0或更高

7.配置FRA

source /home/oracle/.bash_profile11g && sqlplus '/ as sysdba'<<EOF

show parameter db_recovery_file_dest;

exit;

EOF

8.清理回收站

source /home/oracle/.bash_profile11g && sqlplus "/ as sysdba"<<EOF

purge dba_recyclebin;

exit;

EOF

9.创建闪回点

source /home/oracle/.bash_profile11g && sqlplus "/ as sysdba"<<EOF

create restore point syncdb_20200924_19c guarantee flashback database;

select name,guarantee_flashback_database,to_char(scn) from v\$restore_point;

exit;

EOF

结果：

SQL>

Restore point created.

SQL>

NAME

--------------------------------------------------------------------------------

GUA TO_CHAR(SCN)

--- ----------------------------------------

SYNCDB_20200924_19C

YES 994270

10.收集数据字典统计信息

source /home/oracle/.bash_profile11g && sqlplus '/ as sysdba'<<EOF

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

exit;

EOF

11.备份数据库

$ . .bash_profile11g

$ rman target /

RUN

{

ALLOCATE CHANNEL chan_name TYPE DISK;

BACKUP DATABASE FORMAT '/tmp/orcl_2_%U' TAG before_upgrade;

BACKUP CURRENT CONTROLFILE FORMAT '/tmp/controlfile';

}

12.升级前预检查

拷贝jar到/tmp

19c环境

[root@19crac1 ~]# su - oracle

最低0.47元/天解锁文章