Hello Friend's,
In this post we will look at Database patching from 19.3 to 19.8
Download the patch from oracle metalink. from patches and update section
unzip the patch p31281355_190000_Linux-x86-64.zip
But be informed to take a zip backup of oracle home and grid home before starting the patching
cd $ORACLE_HOME
tar -cvf oracle_home_24thSep_2020.tar $ORACLE_HOME
Before Patch Status :
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,LOG_MODE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
SQL> SQL> SQL>
INST_ID INSTANCE_NAME DB_NAME HOST_NAME DATABASE_ROLE LOG_MODE OPEN_MODE DB_VERSION LOGINS DB UP TIME
---------- ---------------- --------- ------------------------------ ---------------- ------------ ---------- ----------------- ---------- -----------------------------
1 CDBEM CDBEM OEM.database.com PRIMARY ARCHIVELOG READ WRITE 19.0.0.0.0 ALLOWED 24-SEP-2020 17:52:33
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 EMPDBREPOS READ WRITE NO
SQL>
SQL> select BANNER_FULL from v$version;
BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
SQL> SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000
COLUMN action_time FORMAT A35
COLUMN action FORMAT A10
COLUMN patch_type FORMAT A10
COLUMN description FORMAT A55
COLUMN status FORMAT A10
COLUMN version FORMAT A10
select CON_ID,
action_time,
PATCH_ID,
PATCH_TYPE,
ACTION,
DESCRIPTION,
SOURCE_VERSION,
TARGET_VERSION
from CDB_REGISTRY_SQLPATCH
order by CON_ID, action_time, patch_id;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10
CON_ID ACTION_TIME PATCH_ID PATCH_TYPE ACTION DESCRIPTION SOURCE_VERSION TARGET_VERSION
---------- ----------------------------------- ---------- ---------- ---------- ------------------------------------------------------- --------------- ---------------
1 14-MAR-20 12.58.47.999129 AM 29517242 RU APPLY Database Release Update : 19.3.0.0.190416 (29517242) 19.1.0.0.0 19.3.0.0.0
1 24-SEP-20 04.15.29.233264 PM 30125133 RU APPLY Database Release Update : 19.5.0.0.191015 (30125133) 19.3.0.0.0 19.5.0.0.0
3 14-MAR-20 01.06.50.862344 AM 29517242 RU APPLY Database Release Update : 19.3.0.0.190416 (29517242) 19.1.0.0.0 19.3.0.0.0
3 24-SEP-20 04.15.39.188784 PM 30125133 RU APPLY Database Release Update : 19.5.0.0.191015 (30125133) 19.3.0.0.0 19.5.0.0.0
Stop The database :
[oracle@OEM OPatch]$ srvctl stop database -d CDBEM
[oracle@OEM OPatch]$
[oracle@OEM OPatch]$ ps -ef |grep pmon
grid 3112 1 0 17:52 ? 00:00:00 asm_pmon_+ASM
oracle 7855 3858 0 18:16 pts/0 00:00:00 grep --color=auto pmon
Check conflict :
[oracle@OEM 31281355]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /oracle/app/orawork/product/19.3.0.0/db_1
Central Inventory : /oracle/app/oraInventory
from : /oracle/app/orawork/product/19.3.0.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.21
OUI version : 12.2.0.7.0
Log file location : /oracle/app/orawork/product/19.3.0.0/db_1/cfgtoollogs/opatch/opatch2020-09-24_18-23-57PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
Actual Patch Apply :
[oracle@OEM 31281355]$ opatch apply /oracle/31281355/
Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation. All rights reserved.
Oracle Home : /oracle/app/orawork/product/19.3.0.0/db_1
Central Inventory : /oracle/app/oraInventory
from : /oracle/app/orawork/product/19.3.0.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.21
OUI version : 12.2.0.7.0
Log file location : /oracle/app/orawork/product/19.3.0.0/db_1/cfgtoollogs/opatch/opatch2020-09-24_18-24-59PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 31281355
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 = '/oracle/app/orawork/product/19.3.0.0/db_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '31281355' to OH '/oracle/app/orawork/product/19.3.0.0/db_1'
ApplySession: Optional component(s) [ oracle.network.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.oraolap.mgmt, 19.0.0.0.0 ] , [ oracle.xdk.parser.java.jaxb2, 19.0.0.0.0 ] , [ oracle.options.olap.awm, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.assistants.asm, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.sqlj, 19.0.0.0.0 ] , [ oracle.assistants.usm, 19.0.0.0.0 ] , [ oracle.options.olap, 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, 19.0.0.0.0...
Patching component oracle.rdbms.util, 19.0.0.0.0...
Patching component oracle.rdbms.rsf, 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.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.rdbms.deconfig, 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.xdk, 19.0.0.0.0...
Patching component oracle.rdbms.crs, 19.0.0.0.0...
Patching component oracle.install.deinstalltool, 19.0.0.0.0...
Patching component oracle.dbjava.ucp, 19.0.0.0.0...
Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...
Patching component oracle.rdbms.lbac, 19.0.0.0.0...
Patching component oracle.dbdev, 19.0.0.0.0...
Patching component oracle.marvel, 19.0.0.0.0...
Patching component oracle.network.listener, 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.ldap.rsf.ic, 19.0.0.0.0...
Patching component oracle.network.client, 19.0.0.0.0...
Patching component oracle.ctx.atg, 19.0.0.0.0...
Patching component oracle.ctx.rsf, 19.0.0.0.0...
Patching component oracle.rdbms.drdaas, 19.0.0.0.0...
Patching component oracle.duma, 19.0.0.0.0...
Patching component oracle.javavm.server, 19.0.0.0.0...
Patching component oracle.odbc, 19.0.0.0.0...
Patching component oracle.rdbms.oci, 19.0.0.0.0...
Patching component oracle.rdbms.rman, 19.0.0.0.0...
Patching component oracle.rdbms.dv, 19.0.0.0.0...
Patching component oracle.bali.ice, 11.1.1.7.0...
Patching component oracle.ovm, 19.0.0.0.0...
Patching component oracle.dbjava.jdbc, 19.0.0.0.0...
Patching component oracle.oracore.rsf, 19.0.0.0.0...
Patching component oracle.rdbms.scheduler, 19.0.0.0.0...
Patching component oracle.rdbms.install.common, 19.0.0.0.0...
Patching component oracle.ldap.owm, 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.rdbms.install.plugins, 19.0.0.0.0...
Patching component oracle.sdo.locator, 19.0.0.0.0...
Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...
Patching component oracle.xdk.rsf, 19.0.0.0.0...
Patching component oracle.sqlplus.ic, 19.0.0.0.0...
Patching component oracle.oraolap, 19.0.0.0.0...
Patching component oracle.xdk.parser.java, 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 31281355 successfully applied.
Sub-set patch [30125133] has become inactive due to the application of a super-set patch [31281355].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /oracle/app/orawork/product/19.3.0.0/db_1/cfgtoollogs/opatch/opatch2020-09-24_18-24-59PM_1.log
OPatch succeeded.
[oracle@OEM 31281355]$
Datapacth execution :
[oracle@OEM 31281355]$ datapatch -verbose
SQL Patching tool version 19.8.0.0.0 Production on Thu Sep 24 19:03:14 2020
Copyright (c) 2012, 2020, Oracle. All rights reserved.
Log file for this invocation: /oracle/app/orawork/cfgtoollogs/sqlpatch/sqlpatch_21728_2020_09_24_19_03_15/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: 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...done
Determining current state...done
Current state of interim SQL patches:
No interim patches found
Current state of release update SQL patches:
Binary registry:
19.8.0.0.0 Release_Update 200703031501: Installed
PDB CDB$ROOT:
Applied 19.5.0.0.0 Release_Update 190909180549 successfully on 24-SEP-20 04.15.29.233264 PM
PDB EMPDBREPOS:
Applied 19.5.0.0.0 Release_Update 190909180549 successfully on 24-SEP-20 04.15.39.188784 PM
PDB PDB$SEED:
Applied 19.5.0.0.0 Release_Update 190909180549 successfully on 24-SEP-20 04.15.42.002394 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT EMPDBREPOS
No interim patches need to be rolled back
Patch 31281355 (Database Release Update : 19.8.0.0.200714 (31281355)):
Apply from 19.5.0.0.0 Release_Update 190909180549 to 19.8.0.0.0 Release_Update 200703031501
No interim patches need to be applied
For the following PDBs: PDB$SEED
No interim patches need to be rolled back
Patch 31281355 (Database Release Update : 19.8.0.0.200714 (31281355)):
Apply from 19.5.0.0.0 Release_Update 190909180549 to 19.8.0.0.0 Release_Update 200703031501
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 3
Validating logfiles...done
Patch 31281355 apply (pdb CDB$ROOT): SUCCESS
logfile: /oracle/app/orawork/cfgtoollogs/sqlpatch/31281355/23688465/31281355_apply_CDBEM_CDBROOT_2020Sep24_19_04_39.log (no errors)
Patch 31281355 apply (pdb EMPDBREPOS): SUCCESS
logfile: /oracle/app/orawork/cfgtoollogs/sqlpatch/31281355/23688465/31281355_apply_CDBEM_EMPDBREPOS_2020Sep24_19_13_11.log (no errors)
Patch 31281355 apply (pdb PDB$SEED): SUCCESS
logfile: /oracle/app/orawork/cfgtoollogs/sqlpatch/31281355/23688465/31281355_apply_CDBEM_PDBSEED_2020Sep24_19_19_42.log (no errors)
SQL Patching tool complete on Thu Sep 24 19:23:39 2020
Post Patch Status :
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 EMPDBREPOS READ WRITE NO
SQL>
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,LOG_MODE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
SQL> SQL> SQL>
INST_ID INSTANCE_NAME DB_NAME HOST_NAME DATABASE_ROLE LOG_MODE OPEN_MODE DB_VERSION LOGINS DB UP TIME
---------- ---------------- --------- ------------------------------ ---------------- ------------ ---------- ----------------- ---------- -----------------------------
1 CDBEM CDBEM OEM.database.com PRIMARY ARCHIVELOG READ WRITE 19.0.0.0.0 ALLOWED 24-SEP-2020 18:59:18
SQL> select BANNER_FULL from v$version;
BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000
COLUMN action_time FORMAT A35
COLUMN action FORMAT A10
COLUMN patch_type FORMAT A10
COLUMN description FORMAT A55
COLUMN status FORMAT A10
COLUMN version FORMAT A10
select CON_ID,
action_time,
PATCH_ID,
PATCH_TYPE,
ACTION,
DESCRIPTION,
SOURCE_VERSION,
TARGET_VERSION
from CDB_REGISTRY_SQLPATCH
order by CON_ID, action_time, patch_id;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10
CON_ID ACTION_TIME PATCH_ID PATCH_TYPE ACTION DESCRIPTION SOURCE_VERSION TARGET_VERSION
---------- ----------------------------------- ---------- ---------- ---------- ------------------------------------------------------- --------------- ---------------
1 14-MAR-20 12.58.47.999129 AM 29517242 RU APPLY Database Release Update : 19.3.0.0.190416 (29517242) 19.1.0.0.0 19.3.0.0.0
1 24-SEP-20 04.15.29.233264 PM 30125133 RU APPLY Database Release Update : 19.5.0.0.191015 (30125133) 19.3.0.0.0 19.5.0.0.0
1 24-SEP-20 07.23.30.128321 PM 31281355 RU APPLY Database Release Update : 19.8.0.0.200714 (31281355) 19.5.0.0.0 19.8.0.0.0
3 14-MAR-20 01.06.50.862344 AM 29517242 RU APPLY Database Release Update : 19.3.0.0.190416 (29517242) 19.1.0.0.0 19.3.0.0.0
3 24-SEP-20 04.15.39.188784 PM 30125133 RU APPLY Database Release Update : 19.5.0.0.191015 (30125133) 19.3.0.0.0 19.5.0.0.0
3 24-SEP-20 07.23.32.285084 PM 31281355 RU APPLY Database Release Update : 19.8.0.0.200714 (31281355) 19.5.0.0.0 19.8.0.0.0
Any databases that have invalid objects after the execution of datapatch should have utlrp.sql run to revalidate those objects.
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql
If you are using the Oracle Recovery Manager, the catalog needs to be upgraded. Enter the following command to upgrade it.
The UPGRADE CATALOG command must be entered twice to confirm the upgrade.
$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;
RMAN> UPGRADE CATALOG;
RMAN> EXIT;
Patching activity completed sucessfully now, Hope This helps.
Regards
Sultan Khan
ConversionConversion EmoticonEmoticon