12.2.0.1多租户（主备）升级至19.7 测试文档

windjack

12.2.0.1多租户（主备）升级至19.7

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

环境介绍

升级前主库

ip:192.168.133.120

db_name ：orcl

db_unique_name:orcl

version:12.2.0.1

OS:Centos7.8 x86-64

存储方式：file system

ASM实例：无

PDB:orclpdb   orclpdb2

升级前备库

ip:192.168.133.121

db_name ：orcl

db_unique_name:orcldg

version:12.2.0.1

OS:Centos7.8 x86-64

存储方式：file system

ASM实例：无

PDB:orclpdb   orclpdb2

升级主库

主库切换日志和提交检查点

SQL>alter system checkpoint;

SQL>alter system switch logfile;

SQL>alter system switch logfile;

检查主库和备库日志应用是否同步

SQL>select sequence#,applied from v$archived_log where dest_id<3 order by 1;

主库停止日志传输

SQL>alter system set log_archive_dest_state_2=defer;

备库停止日志应用

SQL> recover managed standby database cancel;

Media recovery complete.

关闭主库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

关闭监听

[oracle@dbserver ~]$ lsnrctl stop

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 08-JUL-2020 13:40:13

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521)))

The command completed successfully

备份主库软件目录和数据库文件

[oracle@dbserver ~]$tar -czvf /u02/app.tar.gz -C /u01 app

[oracle@dbserver ~]$ ll /u02

total 4375448

-rw-r--r-- 1 oracle oinstall 4480458240 Jul  8 13:53 app.tar.gz

创建新数据库软件ORACLE_HOME目录

mkdir /u01/database

解压19.3的数据库安装文件到/u01/database

unzip  LINUX.X64_193000_db_home.zip -d  /u01/database

安装19.3数据库软件

export DISPLAY=192.168.133.1:0.0

/u01/database/runInstaller

【步骤略】

开启旧版本12.2的数据库实例和监听

【略】

升级数据库实例

export DISPLAY=192.168.133.1:0.0

/u01/app/oracle/product/19.3.0/db_1/bin/dbua

C:\Users\lg\AppData\Local\YNote\data\qq627307D638B41EF2A0A9D4AA3FB18ED0\f13f8f8d568941f3b62643f6296f7b35\clipboard.png

C:\Users\lg\AppData\Local\YNote\data\qq627307D638B41EF2A0A9D4AA3FB18ED0\5844aaecec924eca85d2faae6af9068d\clipboard.png

C:\Users\lg\AppData\Local\YNote\data\qq627307D638B41EF2A0A9D4AA3FB18ED0\e11c967fcfec4715aa9329e2d2f9ac7d\clipboard.png

升级完成后更改oracle用户中的oracle_home环境变量。

手动升级数据库到19.7

上传OPATCH(版本12.2.0.1.21)  p6880880_190000_Linux-x86-64.zip并解压到$ORACLE_HOME/OPatch

检查opatch版本：

[oracle@dbserver 30783543]$ /u01/database/OPatch/opatch lsinventory

Oracle Interim Patch Installer version 12.2.0.1.21

Copyright (c) 2020, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/database

Central Inventory : /u01/app/oraInventory

   from           : /u01/database/oraInst.loc

OPatch version    : 12.2.0.1.21

OUI version       : 12.2.0.7.0

上传升级至19.7的补丁包

检查补丁冲突

[oracle@dbserver 30783543]$ /u01/database/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/30783543/30869156/

Oracle Interim Patch Installer version 12.2.0.1.21

Copyright (c) 2020, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/database

Central Inventory : /u01/app/oraInventory

   from           : /u01/database/oraInst.loc

OPatch version    : 12.2.0.1.21

OUI version       : 12.2.0.7.0

Log file location : /u01/database/cfgtoollogs/opatch/opatch2020-07-08_18-48-48PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

上传升级补丁p30783543_190000_DB_Linux-x86-64.zip并解压至/u01/30783543

必须关闭数据库、监听

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@dbserver ~]$ lsnrctl stop

安装OS包：yum -y install psmisc*

应用OJVM补丁

[oracle@dbserver 30783543]$ /u01/database/OPatch/opatch apply -local /u01/30783543/30805684/

Oracle Interim Patch Installer version 12.2.0.1.21

Copyright (c) 2020, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/database

Central Inventory : /u01/app/oraInventory

   from           : /u01/database/oraInst.loc

OPatch version    : 12.2.0.1.21

OUI version       : 12.2.0.7.0

Log file location : /u01/database/cfgtoollogs/opatch/opatch2020-07-08_18-53-32PM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches:   30805684  

Do you want to proceed? [y|n]

y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/database')

Is the local system ready for patching? [y|n]

Could not recognize input. Please re-enter.

y

User Responded with: Y

Backing up files...

Applying interim patch '30805684' to OH '/u01/database'

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.javavm.server.core, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patch 30805684 successfully applied.

Log file location: /u01/database/cfgtoollogs/opatch/opatch2020-07-08_18-53-32PM_1.log

OPatch succeeded.

应用DB RU补丁

注意：确保oracle_home所在的目录需要至少有6个G的空间，否则报错

exit[oracle@dbserver OPatch]$ ./opatch apply -local /u01/30783543/30869156/

Oracle Interim Patch Installer version 12.2.0.1.21

Copyright (c) 2020, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/database

Central Inventory : /u01/app/oraInventory

   from           : /u01/database/oraInst.loc

OPatch version    : 12.2.0.1.21

OUI version       : 12.2.0.7.0

Log file location : /u01/database/cfgtoollogs/opatch/opatch2020-07-08_19-26-37PM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches:   30869156  

Do you want to proceed? [y|n]

y

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/database')

Is the local system ready for patching? [y|n]

y

User Responded with: Y

Backing up files...

Applying interim patch '30869156' to OH '/u01/database'

ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.oraolap.mgmt, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.options.olap.awm, 19.0.0.0.0 ] , [ oracle.sqlj, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.assistants.asm, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.xdk.parser.java.jaxb2, 19.0.0.0.0 ] , [ oracle.assistants.usm, 19.0.0.0.0 ] , [ oracle.jdk, 1.8.0.191.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patching component oracle.rdbms.util, 19.0.0.0.0...

Patching component oracle.assistants.acf, 19.0.0.0.0...

Patching component oracle.assistants.deconfig, 19.0.0.0.0...

Patching component oracle.assistants.server, 19.0.0.0.0...

Patching component oracle.buildtools.rsf, 19.0.0.0.0...

Patching component oracle.ctx, 19.0.0.0.0...

Patching component oracle.ldap.rsf, 19.0.0.0.0...

Patching component oracle.network.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.sdo, 19.0.0.0.0...

Patching component oracle.sqlplus, 19.0.0.0.0...

Patching component oracle.dbjava.ic, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...

Patching component oracle.dbjava.jdbc, 19.0.0.0.0...

Patching component oracle.dbdev, 19.0.0.0.0...

Patching component oracle.network.client, 19.0.0.0.0...

Patching component oracle.xdk.parser.java, 19.0.0.0.0...

Patching component oracle.rdbms.deconfig, 19.0.0.0.0...

Patching component oracle.oracore.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.lbac, 19.0.0.0.0...

Patching component oracle.ldap.rsf.ic, 19.0.0.0.0...

Patching component oracle.ldap.security.osdt, 19.0.0.0.0...

Patching component oracle.ons, 19.0.0.0.0...

Patching component oracle.sdo.locator, 19.0.0.0.0...

Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...

Patching component oracle.marvel, 19.0.0.0.0...

Patching component oracle.rdbms.install.common, 19.0.0.0.0...

Patching component oracle.install.deinstalltool, 19.0.0.0.0...

Patching component oracle.xdk.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.oci, 19.0.0.0.0...

Patching component oracle.rdbms.drdaas, 19.0.0.0.0...

Patching component oracle.precomp.rsf, 19.0.0.0.0...

Patching component oracle.javavm.client, 19.0.0.0.0...

Patching component oracle.precomp.common.core, 19.0.0.0.0...

Patching component oracle.xdk, 19.0.0.0.0...

Patching component oracle.sqlplus.ic, 19.0.0.0.0...

Patching component oracle.odbc, 19.0.0.0.0...

Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...

Patching component oracle.ldap.owm, 19.0.0.0.0...

Patching component oracle.bali.ice, 11.1.1.7.0...

Patching component oracle.ctx.rsf, 19.0.0.0.0...

Patching component oracle.ovm, 19.0.0.0.0...

Patching component oracle.rdbms.rman, 19.0.0.0.0...

Patching component oracle.dbjava.ucp, 19.0.0.0.0...

Patching component oracle.rdbms.dv, 19.0.0.0.0...

Patching component oracle.duma, 19.0.0.0.0...

Patching component oracle.network.listener, 19.0.0.0.0...

Patching component oracle.oraolap, 19.0.0.0.0...

Patching component oracle.sdo.locator.jrf, 19.0.0.0.0...

Patching component oracle.rdbms.scheduler, 19.0.0.0.0...

Patching component oracle.rdbms.crs, 19.0.0.0.0...

Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0...

Patching component oracle.precomp.common, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf.core, 19.0.0.0.0...

Patching component oracle.perlint, 5.28.1.0.0...

Patching component oracle.precomp.lang, 19.0.0.0.0...

Patching component oracle.jdk, 1.8.0.201.0...

Patch 30869156 successfully applied.

Sub-set patch [29517242] has become inactive due to the application of a super-set patch [30869156].

Please refer to Doc ID 2161861.1 for any possible further required actions.

Log file location: /u01/database/cfgtoollogs/opatch/opatch2020-07-08_19-26-37PM_1.log

OPatch succeeded.

修改TNSNAMES.ora

升级后需要修改新$ORACLE_HOME/network/admin/tnsnames.ora文件，将原文件配置复制进去，尤其是有备库的情况一定要修改。

如果原来12.2中有静态监听，还需修改新的$ORACLE_HOME/network/admin/listener.ora文件。

启动数据库，查看版本：

SQL> select banner_full from v$version;

BANNER_FULL

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

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.7.0.0.0

打开所有的PDB

SQL> alter pluggable database all open;

Pluggable database altered.

编绎无效对象

SQL> @?/rdbms/admin/utlrp.sql

执行补丁字典信息更新

[oracle@dbserver admin]$ cd /u01/database/OPatch

[oracle@dbserver OPatch]$ ./datapatch -verbose

执行完毕后查看补丁更新信息

SQL> select PATCH_ID,PATCH_TYPE,ACTION,STATUS,TARGET_VERSION from dba_registry_sqlpatch;

  PATCH_ID PATCH_TYPE ACTION          STATUS                    TARGET_VERSION

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

  29517242 RU              APPLY           SUCCESS                   19.3.0.0.0

  30805684 INTERIM    APPLY           SUCCESS                   19.3.0.0.0

30869156 RU              APPLY           INITIALIZE                  19.7.0.0.0     

  30869156 RU              APPLY           SUCCESS                   19.7.0.0.0

红色这行不用理会，表示曾经执行过datapatch -verbose未成功（我主动终止的）

升级备库

• 关实例，关监听，创建新的oracle_home目录并修改ORACLE_HOME环境变量
• 升级19.3数据库软件、更新opatch版本、应用两个补丁30805684、30869156
  

              （步骤跟主库操作一样）

• 修改新的oracle_home下的tnsnames.ora 和listener.ora，注意修改ORACLE_HOME.
• 打开备库监听、启动实例到mount,注意当前的ORACLE_HOME要生效为新版本的目录
• 复制主库的口令文件到备库
• 开启主库参数log_archive_dest_state_2为enable
• 开启日志应用
• 日志应用完毕后open备库