Linux 7.9 平台下 Oracle DG 19.3升级至19.12

Linux 7.9 平台下 Oracle DG 19.3升级至19.12

在19c 的Data Guard 环境，我们RU的升级可以直接按照Patch readme中的步骤进行，如下： 1.停备库 2.升级Opatch 3.升级备库（RU） 4.启动备库 5.停主库 6.升级OPatch 7.升级主库（RU+datapatch+utlrp） 8.启动主库

1.检查主库状态

SQL> col status for a15SQL> col error for a20SQL> select status,error from v$archive_dest where dest_id=2;STATUS ERROR--------------- --------------------VALIDSQL> select process,status,sequence# from v$managed_standby;PROCESS STATUS SEQUENCE#--------------------------- --------------- ----------DGRD ALLOCATED 0ARCH CLOSING 28DGRD ALLOCATED 0ARCH CLOSING 28ARCH CONNECTED 0ARCH CLOSING 29DGRD ALLOCATED 0LGWR WRITING 308 rows selected.检查备库SQL> select process,status,sequence# from v$managed_standby;PROCESS STATUS SEQUENCE#--------------------------- ------------------------------------ ----------ARCH CONNECTED 0DGRD ALLOCATED 0DGRD ALLOCATED 0ARCH CLOSING 29ARCH CLOSING 28ARCH CONNECTED 0RFS IDLE 0RFS IDLE 30RFS IDLE 0MRP0 APPLYING_LOG 3010 rows selected.

2 关闭备库

关闭备库实例，监听，退出所有的sqlplus 窗口。

[oracle@dg02 ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 19 12:10:06 2021Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> quitDisconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0[oracle@dg02 ~]$ lsnrctl stopLSNRCTL for Linux: Version 19.0.0.0.0 - Production on 19-SEP-2021 12:12:58Copyright (c) 1991, 2019, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg01)(PORT=1521)))The command completed successfully

3 升级备库OPatch

[oracle@dg02 32545013]$ cd $ORACLE_HOME/OPatch/[oracle@dg02 OPatch]$ ./opatch versionOPatch Version: 12.2.0.1.17OPatch succeeded.[oracle@dg02 32545013]$ cd $ORACLE_HOME[oracle@dg02 dbhome_1]$ mv OPatch/ OPatchbak [oracle@dg02 dbhome_1]$ unzip /tmp/p6880880_190000_Linux-x86-64.zip -d $ORACLE_HOME[oracle@dg02 OPatch]$ cd /u01/app/oracle/product/19.3.0/dbhome_1/OPatch[oracle@dg02 OPatch]$ ./opatch versionOPatch Version: 12.2.0.1.27OPatch succeeded.

4 升级备库RU

[root@dg02 ~]# yum install -y psmisc[oracle@dg02 32904851]$ cd /tmp/32904851/[oracle@dg02 32904851]$ $ORACLE_HOME/OPatch/opatch applyOracle Interim Patch Installer version 12.2.0.1.27Copyright (c) 2021, Oracle Corporation. All rights reserved.Oracle Home : /u01/app/oracle/product/19.3.0/dbhome_1Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19.3.0/dbhome_1/oraInst.locOPatch version : 12.2.0.1.27OUI version : 12.2.0.7.0Log file location : /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2021-09-19_12-35-39PM_1.logVerifying environment and performing prerequisite checks...--------------------------------------------------------------------------------Start OOP by Prereq process.Launch OOP...Oracle Interim Patch Installer version 12.2.0.1.27Copyright (c) 2021, Oracle Corporation. All rights reserved.Oracle Home : /u01/app/oracle/product/19.3.0/dbhome_1Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19.3.0/dbhome_1/oraInst.locOPatch version : 12.2.0.1.27OUI version : 12.2.0.7.0Log file location : /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2021-09-19_12-36-31PM_1.logVerifying environment and performing prerequisite checks...OPatch continues with these patches: 32904851 Do you want to proceed? [y|n]yUser Responded with: YAll checks passed.Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.(Oracle Home = '/u01/app/oracle/product/19.3.0/dbhome_1')Is the local system ready for patching? [y|n]yUser Responded with: YBacking up files...Applying interim patch '32904851' to OH '/u01/app/oracle/product/19.3.0/dbhome_1'ApplySession: Optional component(s) [ oracle-work.gsm, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle-work.cman, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle-.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 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.perlint, 5.28.1.0.0...Patching component oracle.rdbms.locator, 19.0.0.0.0...Patching component oracle.rdbms.rsf, 19.0.0.0.0...Patching component oracle.rdbms.util, 19.0.0.0.0...Patching component oracle.rdbms, 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.dbjava.ic, 19.0.0.0.0...Patching component oracle.dbjava.jdbc, 19.0.0.0.0...Patching component oracle.dbjava.ucp, 19.0.0.0.0...Patching component oracle.duma, 19.0.0.0.0...Patching component oracle.javavm.client, 19.0.0.0.0...Patching component oracle.ldap.owm, 19.0.0.0.0...Patching component oracle.ldap.rsf, 19.0.0.0.0...Patching component oracle.marvel, 19.0.0.0.0...Patching component oracle-work.rsf, 19.0.0.0.0...Patching component oracle.oracore.rsf, 19.0.0.0.0...Patching component oracle.precomp.common.core, 19.0.0.0.0...Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...Patching component oracle.rdbms.deconfig, 19.0.0.0.0...Patching component oracle.rdbms.oci, 19.0.0.0.0...Patching component oracle.rhp.db, 19.0.0.0.0...Patching component oracle.sdo, 19.0.0.0.0...Patching component oracle.sdo.locator.jrf, 19.0.0.0.0...Patching component oracle.sqlplus, 19.0.0.0.0...Patching component oracle.sqlplus.ic, 19.0.0.0.0...Patching component oracle.wwg.plsql, 19.0.0.0.0...Patching component oracle.odbc, 19.0.0.0.0...Patching component oracle.rdbms.drdaas, 19.0.0.0.0...Patching component oracle.xdk.parser.java, 19.0.0.0.0...Patching component oracle.dbtoolslistener, 19.0.0.0.0...Patching component oracle.ctx.atg, 19.0.0.0.0...Patching component oracle.javavm.server, 19.0.0.0.0...Patching component oracle.ldap.security.osdt, 19.0.0.0.0...Patching component oracle.rdbms.hs_common, 19.0.0.0.0...Patching component oracle.ons, 19.0.0.0.0...Patching component oracle.precomp.rsf, 19.0.0.0.0...Patching component oracle.oraolap.api, 19.0.0.0.0...Patching component oracle.ons.ic, 19.0.0.0.0...Patching component oracle.rdbms.install.common, 19.0.0.0.0...Patching component oracle.xdk, 19.0.0.0.0...Patching component oracle.rdbms.dv, 19.0.0.0.0...Patching component oracle.ldap.rsf.ic, 19.0.0.0.0...Patching component oracle.rdbms.scheduler, 19.0.0.0.0...Patching component oracle.rdbms.lbac, 19.0.0.0.0...Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...Patching component oracle.rdbms.rman, 19.0.0.0.0...Patching component oracle.oraolap, 19.0.0.0.0...Patching component oracle.rdbms.crs, 19.0.0.0.0...Patching component oracle.dbdev, 19.0.0.0.0...Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...Patching component oracle-work.client, 19.0.0.0.0...Patching component oracle.xdk.xquery, 19.0.0.0.0...Patching component oracle.ldap.client, 19.0.0.0.0...Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...Patching component oracle.ctx.rsf, 19.0.0.0.0...Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...Patching component oracle.xdk.rsf, 19.0.0.0.0...Patching component oracle-work.listener, 19.0.0.0.0...Patching component oracle.ovm, 19.0.0.0.0...Patching component oracle.sdo.locator, 19.0.0.0.0...Patching component oracle.mgw.common, 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.precomp.lang, 19.0.0.0.0...Patching component oracle.jdk, 1.8.0.201.0...Patch 32904851 successfully applied.Sub-set patch [29517242] has become inactive due to the application of a super-set patch [32904851].Please refer to Doc ID 2161861.1 for any possible further required actions.Log file location: /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2021-09-19_12-36-31PM_1.logOPatch succeeded

5. 启动备库

按照正常的流程，升级RU 后还需要执行datapatch工具来将变化的SQL加到实例中，但因为备库是只读的，所以是无法执行datapatch工具的。 待主库升级完成之后，会自动将变化的数据，同步到备库。

[oracle@dg02 ~]$ lsnrctl startLSNRCTL for Linux: Version 19.0.0.0.0 - Production on 19-SEP-2021 13:19:45Copyright (c) 1991, 2021, Oracle. All rights reserved.Starting /u01/app/oracle/product/19.3.0/dbhome_1/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 19.0.0.0.0 - ProductionSystem parameter file is /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.oraLog messages written to /u01/app/oracle/diag/tnslsnr/dg02/listener/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg02)(PORT=1521)))Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg02)(PORT=1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 19.0.0.0.0 - ProductionStart Date 19-SEP-2021 13:19:46Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.oraListener Log File /u01/app/oracle/diag/tnslsnr/dg02/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg02)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))Services Summary...Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully[oracle@dg02 ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 19 13:19:51 2021Version 19.12.0.0.0Copyright (c) 1982, 2021, Oracle. All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 2516581448 bytesFixed Size 9141320 bytesVariable Size 654311424 bytesDatabase Buffers 1845493760 bytesRedo Buffers 7634944 bytesDatabase mounted.Database opened.SQL> alter database recover managed standby database disconnect;Database altered.

6.关闭主库

关闭主库实例，监听，退出所有的sqlplus 窗口

[oracle@dg01 ~]$ lsnrctl stopLSNRCTL for Linux: Version 19.0.0.0.0 - Production on 19-SEP-2021 13:22:35Copyright (c) 1991, 2019, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg01)(PORT=1521)))The command completed successfully[oracle@dg01 ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 19 13:22:42 2021Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> quitDisconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0

7.升级主库Opatch

[oracle@dg01 32545013]$ cd $ORACLE_HOME/OPatch/[oracle@dg01 OPatch]$ ./opatch versionOPatch Version: 12.2.0.1.17OPatch succeeded.[oracle@dg01 32545013]$ cd $ORACLE_HOME[oracle@dg01 dbhome_1]$ mv OPatch/ OPatchbak [oracle@dg01 dbhome_1]$ unzip /tmp/p6880880_190000_Linux-x86-64.zip -d $ORACLE_HOME[oracle@dg01 OPatch]$ cd /u01/app/oracle/product/19.3.0/dbhome_1/OPatch[oracle@dg01 OPatch]$ ./opatch versionOPatch Version: 12.2.0.1.27OPatch succeeded.

8.升级主库RU

[root@dg01 ~]# yum install -y psmisc[oracle@dg01 ~]$ cd /tmp/32904851/[oracle@dg01 32904851]$ $ORACLE_HOME/OPatch/opatch applyOracle Interim Patch Installer version 12.2.0.1.27Copyright (c) 2021, Oracle Corporation. All rights reserved.Oracle Home : /u01/app/oracle/product/19.3.0/dbhome_1Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19.3.0/dbhome_1/oraInst.locOPatch version : 12.2.0.1.27OUI version : 12.2.0.7.0Log file location : /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2021-09-19_13-26-06PM_1.logVerifying environment and performing prerequisite checks...--------------------------------------------------------------------------------Start OOP by Prereq process.Launch OOP...Oracle Interim Patch Installer version 12.2.0.1.27Copyright (c) 2021, Oracle Corporation. All rights reserved.Oracle Home : /u01/app/oracle/product/19.3.0/dbhome_1Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19.3.0/dbhome_1/oraInst.locOPatch version : 12.2.0.1.27OUI version : 12.2.0.7.0Log file location : /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2021-09-19_13-26-48PM_1.logVerifying environment and performing prerequisite checks...OPatch continues with these patches: 32904851 Do you want to proceed? [y|n]yUser Responded with: YAll checks passed.Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.(Oracle Home = '/u01/app/oracle/product/19.3.0/dbhome_1')Is the local system ready for patching? [y|n]yUser Responded with: YBacking up files...Applying interim patch '32904851' to OH '/u01/app/oracle/product/19.3.0/dbhome_1'ApplySession: Optional component(s) [ oracle-work.gsm, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle-work.cman, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle-.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 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.perlint, 5.28.1.0.0...Patching component oracle.rdbms.locator, 19.0.0.0.0...Patching component oracle.rdbms.rsf, 19.0.0.0.0...Patching component oracle.rdbms.util, 19.0.0.0.0...Patching component oracle.rdbms, 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.dbjava.ic, 19.0.0.0.0...Patching component oracle.dbjava.jdbc, 19.0.0.0.0...Patching component oracle.dbjava.ucp, 19.0.0.0.0...Patching component oracle.duma, 19.0.0.0.0...Patching component oracle.javavm.client, 19.0.0.0.0...Patching component oracle.ldap.owm, 19.0.0.0.0...Patching component oracle.ldap.rsf, 19.0.0.0.0...Patching component oracle.marvel, 19.0.0.0.0...Patching component oracle-work.rsf, 19.0.0.0.0...Patching component oracle.oracore.rsf, 19.0.0.0.0...Patching component oracle.precomp.common.core, 19.0.0.0.0...Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...Patching component oracle.rdbms.deconfig, 19.0.0.0.0...Patching component oracle.rdbms.oci, 19.0.0.0.0...Patching component oracle.rhp.db, 19.0.0.0.0...Patching component oracle.sdo, 19.0.0.0.0...Patching component oracle.sdo.locator.jrf, 19.0.0.0.0...Patching component oracle.sqlplus, 19.0.0.0.0...Patching component oracle.sqlplus.ic, 19.0.0.0.0...Patching component oracle.wwg.plsql, 19.0.0.0.0...Patching component oracle.odbc, 19.0.0.0.0...Patching component oracle.rdbms.drdaas, 19.0.0.0.0...Patching component oracle.xdk.parser.java, 19.0.0.0.0...Patching component oracle.dbtoolslistener, 19.0.0.0.0...Patching component oracle.ctx.atg, 19.0.0.0.0...Patching component oracle.javavm.server, 19.0.0.0.0...Patching component oracle.ldap.security.osdt, 19.0.0.0.0...Patching component oracle.rdbms.hs_common, 19.0.0.0.0...Patching component oracle.ons, 19.0.0.0.0...Patching component oracle.precomp.rsf, 19.0.0.0.0...Patching component oracle.oraolap.api, 19.0.0.0.0...Patching component oracle.ons.ic, 19.0.0.0.0...Patching component oracle.rdbms.install.common, 19.0.0.0.0...Patching component oracle.xdk, 19.0.0.0.0...Patching component oracle.rdbms.dv, 19.0.0.0.0...Patching component oracle.ldap.rsf.ic, 19.0.0.0.0...Patching component oracle.rdbms.scheduler, 19.0.0.0.0...Patching component oracle.rdbms.lbac, 19.0.0.0.0...Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...Patching component oracle.rdbms.rman, 19.0.0.0.0...Patching component oracle.oraolap, 19.0.0.0.0...Patching component oracle.rdbms.crs, 19.0.0.0.0...Patching component oracle.dbdev, 19.0.0.0.0...Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...Patching component oracle-work.client, 19.0.0.0.0...Patching component oracle.xdk.xquery, 19.0.0.0.0...Patching component oracle.ldap.client, 19.0.0.0.0...Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...Patching component oracle.ctx.rsf, 19.0.0.0.0...Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...Patching component oracle.xdk.rsf, 19.0.0.0.0...Patching component oracle-work.listener, 19.0.0.0.0...Patching component oracle.ovm, 19.0.0.0.0...Patching component oracle.sdo.locator, 19.0.0.0.0...Patching component oracle.mgw.common, 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.precomp.lang, 19.0.0.0.0...Patching component oracle.jdk, 1.8.0.201.0...Patch 32904851 successfully applied.Sub-set patch [29517242] has become inactive due to the application of a super-set patch [32904851].Please refer to Doc ID 2161861.1 for any possible further required actions.Log file location: /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2021-09-19_13-26-48PM_1.logOPatch succeeded.

9.启动主库

[oracle@dg01 32904851]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 19 13:42:36 2021Version 19.12.0.0.0Copyright (c) 1982, 2021, Oracle. All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 2516581448 bytesFixed Size 9141320 bytesVariable Size 654311424 bytesDatabase Buffers 1845493760 bytesRedo Buffers 7634944 bytesDatabase mounted.Database opened.SQL> alter pluggable database all open;Pluggable database altered.SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 HBHE READ WRITE NO

10.执行Datapatch工具

安装补丁之后，还需要将有变化的SQL加载到数据库中，这里可以直接运行Datapatch工具将这些修改的SQL重新加载到数据库中，在DG 环境中，只需要在主库执行，备库不需要执行。

[oracle@dg01 32904851]$ $ORACLE_HOME/OPatch/datapatch -verboseSQL Patching tool version 19.12.0.0.0 Production on Sun Sep 19 13:47:28 2021Copyright (c) 2012, 2021, Oracle. All rights reserved.Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_25116_2021_09_19_13_47_28/sqlpatch_invocation.logConnecting to database...OKGathering database info...doneNote: Datapatch will only apply or rollback SQL fixes for PDBs that are in an open state, no patches will be applied to closed PDBs. Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation (Doc ID 1585822.1)Bootstrapping registry and package to current versions...doneDetermining current state...doneCurrent state of interim SQL patches: No interim patches foundCurrent state of release update SQL patches: Binary registry: 19.12.0.0.0 Release_Update 210716141810: Installed PDB CDB$ROOT: Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 14-SEP-21 10.39.01.130089 PM PDB HBHE: Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 14-SEP-21 10.48.07.538379 PM PDB PDB$SEED: Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 14-SEP-21 10.48.07.538379 PMAdding patches to installation queue and performing prereq checks...doneInstallation queue: For the following PDBs: CDB$ROOT PDB$SEED HBHE No interim patches need to be rolled back Patch 32904851 (Database Release Update : 19.12.0.0.210720 (32904851)): Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.12.0.0.0 Release_Update 210716141810 No interim patches need to be appliedInstalling patches... Patch installation complete. Total patches installed: 3Validating logfiles...donePatch 32904851 apply (pdb CDB$ROOT): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32904851/24343243/32904851_apply_ORCL_CDBROOT_2021Sep19_13_50_14.log (no errors)Patch 32904851 apply (pdb PDB$SEED): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32904851/24343243/32904851_apply_ORCL_PDBSEED_2021Sep19_14_03_19.log (no errors)Patch 32904851 apply (pdb HBHE): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32904851/24343243/32904851_apply_ORCL_HBHE_2021Sep19_14_03_17.log (no errors)Automatic recompilation incomplete; run utlrp.sql to revalidate. PDBs: HBHE PDB$SEEDSQL Patching tool complete on Sun Sep 19 14:14:04 2021

11.处理无效对象

[oracle@dg01 32904851]$ cd $ORACLE_HOME/rdbms/admin[oracle@dg01 admin]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 19 14:22:53 2021Version 19.12.0.0.0Copyright (c) 1982, 2021, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.12.0.0.0SQL> @utlrp.sqlSession altered.TIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_BGN 2021-09-19 14:22:55DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalidDOC> objects in the database. Recompilation time is proportional to theDOC> number of invalid objects in the database, so this command may takeDOC> a long time to execute on a database with a large number of invalidDOC> objects.DOC>DOC> Use the following queries to track recompilation progress:DOC>DOC> 1. Query returning the number of invalid objects remaining. ThisDOC> number should decrease with time.DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);DOC>DOC> 2. Query returning the number of objects compiled so far. This numberDOC> should increase with time.DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;DOC>DOC> This script automatically chooses serial or parallel recompilationDOC> based on the number of CPUs available (parameter cpu_count) multipliedDOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).DOC> On RAC, this number is added across all RAC nodes.DOC>DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallelDOC> recompilation. Jobs are created without instance affinity so that theyDOC> can migrate across RAC nodes. Use the following queries to verifyDOC> whether UTL_RECOMP jobs are being created and run correctly:DOC>DOC> 1. Query showing jobs created by UTL_RECOMPDOC> SELECT job_name FROM dba_scheduler_jobsDOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';DOC>DOC> 2. Query showing UTL_RECOMP jobs that are runningDOC> SELECT job_name FROM dba_scheduler_running_jobsDOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';DOC>#PL/SQL procedure successfully completed.TIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_END 2021-09-19 14:22:57DOC> The following query reports the number of invalid objects.DOC>DOC> If the number is higher than expected, please examine the errorDOC> messages reported with each object (using SHOW ERRORS) to see if theyDOC> point to system misconfiguration or resource constraints that must beDOC> fixed before attempting to recompile these objects.DOC>#OBJECTS WITH ERRORS------------------- 0DOC> The following query reports the number of exceptions caught duringDOC> recompilation. If this number is non-zero, please query the errorDOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errorsDOC> are due to misconfiguration or resource constraints that must beDOC> fixed before objects can compile successfully.DOC> Note: Typical compilation errors (due to coding errors) are notDOC> logged into this table: they go into DBA_ERRORS instead.DOC>#ERRORS DURING RECOMPILATION--------------------------- 0Function created.PL/SQL procedure successfully completed.Function dropped.PL/SQL procedure successfully completed.

12 验证RU

主库验证SQL> select patch_id,patch_type,action,status,action_time,description from dba_registry_sqlpatch; PATCH_ID PATCH_TYPE ACTION STATUS---------- ------------------------------ ---------- ----------ACTION_TIME------------------------------DESCRIPTION------------------------------------------------------------ 29517242 RU APPLY SUCCESS14-SEP-21 10.39.01.130089 PMDatabase Release Update : 19.3.0.0.190416 (29517242) 32904851 RU APPLY SUCCESS19-SEP-21 02.13.14.078356 PMDatabase Release Update : 19.12.0.0.210720 (32904851) PATCH_ID PATCH_TYPE ACTION STATUS---------- ------------------------------ ---------- ----------ACTION_TIME------------------------------DESCRIPTION------------------------------------------------------------SQL> select ACTION_TIME,VERSION,COMMENTS from dba_registry_history;ACTION_TIME VERSION------------------------------ -------------------------COMMENTS-------------------------------------------------------------------------------- 19RDBMS_19.12.0.0.0DBRU_LINUX.X64_21071514-SEP-21 10.38.51.749595 PM 19.0.0.0.0Patch applied on 19.3.0.0.0: Release_Update - 19041012272019-SEP-21 02.02.22.067552 PM 19.0.0.0.0Patch applied from 19.3.0.0.0 to 19.12.0.0.0: Release_Update - 210716141810备库验证SQL> col status for a10SQL> col action for a10SQL> col action_time for a30SQL> col description for a60SQL> col version for a25SQL> col comments for a80SQL> select patch_id,patch_type,action,status,action_time,description from dba_registry_sqlpatch; PATCH_ID PATCH_TYPE ACTION STATUS---------- ------------------------------ ---------- ----------ACTION_TIME------------------------------DESCRIPTION------------------------------------------------------------ 29517242 RU APPLY SUCCESS14-SEP-21 10.39.01.130089 PMDatabase Release Update : 19.3.0.0.190416 (29517242) 32904851 RU APPLY SUCCESS19-SEP-21 02.13.14.078356 PMDatabase Release Update : 19.12.0.0.210720 (32904851) PATCH_ID PATCH_TYPE ACTION STATUS---------- ------------------------------ ---------- ----------ACTION_TIME------------------------------DESCRIPTION------------------------------------------------------------SQL> select ACTION_TIME,VERSION,COMMENTS from dba_registry_history;ACTION_TIME VERSION------------------------------ -------------------------COMMENTS-------------------------------------------------------------------------------- 19RDBMS_19.12.0.0.0DBRU_LINUX.X64_21071514-SEP-21 10.38.51.749595 PM 19.0.0.0.0Patch applied on 19.3.0.0.0: Release_Update - 19041012272019-SEP-21 02.02.22.067552 PM 19.0.0.0.0Patch applied from 19.3.0.0.0 to 19.12.0.0.0: Release_Update - 210716141810

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。