Step By Step Apply Combo Patch on Primary & Standby (DATAGUARD) Database in Oracle

DBsGuru 45 mins6
()

Combo Patch Apply on Primary & Standby Database in Oracle

In this article, we are going to demonstrate step by step apply combo patches on Oracle Primary & Standby (DATAGUARD) which released in January 2021, combo patch contains PSU (31985579) and  OJVM (32119956) patches.

Below is the database, system,  patch id, etc. details.

COMPONENTSPRIMARYSTANDBY
SERVERLINUX7 (64 bit)LINUX7 (64 bit)
INSTANCE NAMESUNCDBSUNCDB_S
DB VERSION12.1.02 12.1.02
OPATCH VERSION12.2.0.1.2312.2.0.1.23
PATCH ID32126886 (12.1.0.2.210119)32126886 (12.1.0.2.210119)
ORACLE HOME/u01/app/oracle/product/12.1.0/db_1/u01/app/oracle/product/12.1.0/db_1


High-Level plan to apply combo patch in DATAGUARD.

  • Download patch & copy to primary & standby database servers.
  • Unzip patch on both primary & standby database servers.
  • Validate archive in sync and gap between primary & standby database
  • Perform prechecks like the backup of ORACLE HOME, services, capture applied patches, DB backup, blackout in OEM, disable cronjobs, DBMS scheduler jobs, etc.
  • Stop all dependent application jobs (Application responsibility).
  • Validate version of opatch utility & perform patch conflicts
  • Stop MRP, Stop services of  ORACLE HOME, and apply combo patch on standby database.
  • Post patch only start services of patched ORACLE HOME on standby.
  • Stop services of  ORACLE HOME and apply combo patch on the primary.
  • Start services of patched ORACLE HOME, Apply post-patch only on primary databases.
  • Start MRP process on standby database and validate archive in sync and gap.
  • Start remaining services like delete blackout in OEM, enable cron jobs, scheduler jobs, etc.
  • Application checkout

Let’s start step by step patches apply first on standby database server then Primary database server. Make sure before the patch applies we have a valid full database along with ORACLE HOME backup or at least any one.


1. Prechecks on Standby Database Server


1.1 Download patch according to DB version and OS platform. Click here for Oracle Critical Patch IDs along with a download link for January 2021.

p32126886_121020_Linux-x86-64.zip


1.2 Unzip the combo patch. 

[oracle@ol6-112-lab2 COMBO_PSU_JAN21]$pwd
/home/oracle/COMBO_PSU_JAN21/
[oracle@ol6-112-lab2 COMBO_PSU_JAN21]$ unzip p32126886_121020_Linux-x86-64.zip
Archive:  p32126886_121020_Linux-x86-64.zip
   creating: 32126886/
  inflating: 32126886/PatchSearch.xml
   creating: 32126886/31985579/
---------------Trimmed Data---------------
  inflating: 32126886/32119956/README.html
  inflating: 32126886/README.html
  inflating: PatchSearch.xml
  inflating: 32126886/32119956/README.html
  inflating: 32126886/README.html
  inflating: PatchSearch.xml
[oracle@ol6-112-lab2 COMBO_PSU_JAN21]$ ls -lrt
total 3245564
drwxrwxrwx 4 oracle oinstall       4096 Jan 19 10:44 32126886
-rw-rw-r-- 1 oracle oinstall     239154 Jan 19 15:33 PatchSearch.xml
-rw-r--r-- 1 oracle oinstall 3201084695 Feb  3 16:58 p32126886_121020_Linux-x86-64.zip
[oracle@ol6-112-lab2 COMBO_PSU_JAN21]$ cd  32126886/
[oracle@ol6-112-lab2 32126886]$ ls -lrt
total 48
drwxr-xr-x  4 oracle oinstall  4096 Nov 26 01:21 32119956  ==> Oracle JavaVM(OJVM)
drwxr-xr-x 28 oracle oinstall  4096 Jan  8 19:33 31985579  ==> Database Patch Set (PSU) 
-rw-rw-r--  1 oracle oinstall 14629 Jan 15 16:24 PatchSearch.xml
-rw-r--r--  1 oracle oinstall 20802 Jan 19 10:44 README.html


Keep noted below patch ids which are going to apply on both primary & standby databases.

31985579 ==> Database Patch Set (PSU)
32119956 ==> Oracle JavaVM(OJVM)

1.3 Execute backup of ORACLE HOME along with the capture of running services, listeners, oratab, patch inventory, etc associated to ORACLE HOME where we will apply the patch.

[oracle@ol6-112-lab2 32126886]$ mkdir -p /home/oracle/COMBO_PSU_JAN21/prechecks
[oracle@ol6-112-lab2 32126886]$ /home/oracle/COMBO_PSU_JAN21/prechecks
[oracle@ol6-112-lab2 prechecks]$ ps -ef |grep pmon > /home/oracle/COMBO_PSU_JAN21/prechecks/current_instance.txt
[oracle@ol6-112-lab2 prechecks]$ ps -ef |grep tns > /home/oracle/COMBO_PSU_JAN21/prechecks/current_tns.txt
[oracle@ol6-112-lab2 prechecks]$ cat /etc/oratab > /home/oracle/COMBO_PSU_JAN21/prechecks/current_oratab.txt 
[oracle@ol6-112-lab2 prechecks]$ $ORACLE_HOME/OPatch/opatch lsinventory > /home/oracle/COMBO_PSU_JAN21/prechecks/before_current_lsinventory.txt 
[oracle@ol6-112-lab2 prechecks]$ tar -cvf /backup/oracle_home1212_04Feb2021.tar $ORACLE_HOME
[oracle@ol6-112-lab2 prechecks]$ ls -lrt
total 24
-rw-r--r-- 1 oracle oinstall   124 Feb  4 11:58 current_instance.txt
-rw-r--r-- 1 oracle oinstall   227 Feb  4 11:58 current_tns.txt
-rw-r--r-- 1 oracle oinstall  1001 Feb  4 11:58 current_oratab.txt
-rw-r--r-- 1 oracle oinstall 10670 Feb  4 11:58 before_current_lsinventory.txt


1.4 Validate the version of the opatch utility along with the most latest applied patch if needed upgrade the opatch utility to the latest version or minimum to recommended version as per patch readme.

[oracle@ol6-112-lab2 COMBO_PSU_JAN21]$ . oraenv
ORACLE_SID = [suncdb] ? suncdb_s
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ol6-112-lab2 32126886]$ export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@ol6-112-lab2 32126886]$ opatch version
OPatch Version: 12.1.0.1.10

OPatch succeeded.
[oracle@ol6-112-lab2 COMBO_PSU_JAN21]$ opatch lspatches
27001733;Database PSU 12.1.0.2.180116, Oracle JavaVM Component (JAN2018)
26925311;Database Patch Set Update : 12.1.0.2.180116 (26925311)

OPatch succeeded.


1.5 Determine whether any currently installed interim patches conflict with the patch being installed. If conflict any act according to that.

Conflict check for Database PSU

[oracle@ol6-112-lab2 COMBO_PSU_JAN21]$ cd 32126886/31985579
[oracle@ol6-112-lab2 31985579]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.1.0.1.10
Copyright (c) 2021, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version    : 12.1.0.1.10
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2021-02-04_12-06-52PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" is not executed.

The details are:
Unable to create Patch Object.
Exception occured : /home/oracle/COMBO_PSU_JAN21/32126886/31985579/27547329/etc/config/actions.xml with Version field of the component "delete" in actions file cannot be <null> or empty. Please check patch metadata.

OPatch failed with error code 2


Here you can see the above conflict command failed because the version of the opatch utility doesn’t meet to require opatch version, so it’s required to upgrade. Click here to check the steps for Opatch Upgrade.

Followed the above article and upgraded the opatch version to 12.2.0.1.23. Now we have to validate the again conflict, it should be pass now.

[oracle@ol6-112-lab2 31985579]$ opatch version
OPatch Version: 12.2.0.1.23

OPatch succeeded.
[oracle@ol6-112-lab2 COMBO_PSU_JAN21]$ cd 32126886/31985579
[oracle@ol6-112-lab2 31985579]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version    : 12.2.0.1.23
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2021-02-04_12-23-35PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.


Conflict check for OJVM

[oracle@ol6-112-lab2 31985579]$ cd /home/oracle/COMBO_PSU_JAN21/32126886/32119956
[oracle@ol6-112-lab2 32119956]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version    : 12.2.0.1.23
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2021-02-04_12-24-37PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
[oracle@ol6-112-lab2 32119956]$


1.6 Validate applied archives.

STANDBY> set lines 222 pages 111
STANDBY> select sequence#, first_time, next_time from v$archived_log order by first_time desc,sequence#;

 SEQUENCE# FIRST_TIME           NEXT_TIME
---------- -------------------- --------------------
        28 04-FEB-2021 11:18:34 04-FEB-2021 11:21:07
        27 04-FEB-2021 10:32:21 04-FEB-2021 11:18:34
        26 04-FEB-2021 10:29:39 04-FEB-2021 10:32:21
        25 04-FEB-2021 10:26:36 04-FEB-2021 10:29:39
        24 04-FEB-2021 10:23:53 04-FEB-2021 10:26:36
        23 04-FEB-2021 10:22:05 04-FEB-2021 10:23:53


2. Prechecks on Primary Database Server.


2.1 Download patch according to DB version and OS platform, best option to do scp from the standby database server.

p32126886_121020_Linux-x86-64.zip


2.2 Unzip the combo patch.

[oracle@ol6-112-lab1 COMBO_PSU_JAN21]$ unzip p32126886_121020_Linux-x86-64.zip
Archive:  p32126886_121020_Linux-x86-64.zip
   creating: 32126886/
  inflating: 32126886/PatchSearch.xml
   creating: 32126886/31985579/
---------------Trimmed Data---------------
  inflating: 32126886/32119956/README.html
  inflating: 32126886/README.html
  inflating: PatchSearch.xml
  inflating: 32126886/32119956/README.html
  inflating: 32126886/README.html
  inflating: PatchSearch.xml
[oracle@ol6-112-lab1 COMBO_PSU_JAN21]$ ls -lrt
total 3245564
drwxrwxrwx 4 oracle oinstall       4096 Jan 19 10:44 32126886
-rw-rw-r-- 1 oracle oinstall     239154 Jan 19 15:33 PatchSearch.xml
-rw-r--r-- 1 oracle oinstall 3201084695 Feb  3 16:58 p32126886_121020_Linux-x86-64.zip


2.3 Execute backup of ORACLE HOME along with the capture of running services, listeners, oratab, patch inventory, etc associated to ORACLE HOME where we will apply the patch.

[oracle@ol6-112-lab1 32126886]$ mkdir -p /home/oracle/COMBO_PSU_JAN21/prechecks
[oracle@ol6-112-lab1 32126886]$ /home/oracle/COMBO_PSU_JAN21/prechecks
[oracle@ol6-112-lab1 prechecks]$ ps -ef |grep pmon > /home/oracle/COMBO_PSU_JAN21/prechecks/current_instance.txt
[oracle@ol6-112-lab1 prechecks]$ ps -ef |grep tns > /home/oracle/COMBO_PSU_JAN21/prechecks/current_tns.txt
[oracle@ol6-112-lab1 prechecks]$ cat /etc/oratab > /home/oracle/COMBO_PSU_JAN21/prechecks/current_oratab.txt 
[oracle@ol6-112-lab1 prechecks]$ $ORACLE_HOME/OPatch/opatch lsinventory > /home/oracle/COMBO_PSU_JAN21/prechecks/before_current_lsinventory.txt 
[oracle@ol6-112-lab1 prechecks]$ tar -cvf /backup/oracle_home1212_04Feb2021.tar $ORACLE_HOME
[oracle@ol6-112-lab1 prechecks]$ ls -lrt
total 24
-rw-r--r-- 1 oracle oinstall   122 Feb  4 11:57 current_instance.txt
-rw-r--r-- 1 oracle oinstall   227 Feb  4 11:57 current_tns.txt
-rw-r--r-- 1 oracle oinstall  1048 Feb  4 11:57 current_oratab.txt
-rw-r--r-- 1 oracle oinstall 10670 Feb  4 11:58 before_current_lsinventory.txt


2.4 Validate the version of the opatch utility along with the most latest applied patch if needed upgrade opatch utility to the latest version or minimum to recommended version as per patch readme.

[oracle@ol6-112-lab1 prechecks]$ opatch version
OPatch Version: 12.2.0.1.23

OPatch succeeded.
[oracle@ol6-112-lab1 prechecks]$ opatch lspatches
27001733;Database PSU 12.1.0.2.180116, Oracle JavaVM Component (JAN2018)
26925311;Database Patch Set Update : 12.1.0.2.180116 (26925311)

OPatch succeeded.


2.5 Determine whether any currently installed interim patches conflict with the patch being installed. If conflict any act according to that.


Conflict check for Database PSU

[oracle@ol6-112-lab1 COMBO_PSU_JAN21]$ cd 32126886/31985579
[oracle@ol6-112-lab1 31985579]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version    : 12.2.0.1.23
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2021-02-04_12-27-47PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.


Conflict check for OJVM

[oracle@ol6-112-lab1 32119956]$ cd /home/oracle/COMBO_PSU_JAN21/32126886/32119956
[oracle@ol6-112-lab1 32119956]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version    : 12.2.0.1.23
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2021-02-04_12-28-29PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.


2.6 Validate applied archives along with gap. Make sure there should not be any gap between primary to standby archives.

PRIMARY> set lines 222 pages 111
PRIMARY> select sequence#, first_time, next_time from v$archived_log order by first_time desc,sequence#;

 SEQUENCE# FIRST_TIME           NEXT_TIME
---------- -------------------- --------------------
        28 04-FEB-2021 11:18:34 04-FEB-2021 11:21:07
        27 04-FEB-2021 10:32:21 04-FEB-2021 11:18:34
        26 04-FEB-2021 10:29:39 04-FEB-2021 10:32:21
        25 04-FEB-2021 10:26:36 04-FEB-2021 10:29:39
        24 04-FEB-2021 10:23:53 04-FEB-2021 10:26:36
        23 04-FEB-2021 10:22:05 04-FEB-2021 10:23:53
        22 04-FEB-2021 10:12:17 04-FEB-2021 10:22:05


PRIMARY> col STANDBY_DEST for a15;
SELECT MAX(SEQUENCE#),THREAD#,APPLIED,STANDBY_DEST FROM GV$ARCHIVED_LOG GROUP BY THREAD#,APPLIED,STANDBY_DEST ;SQL>

MAX(SEQUENCE#)    THREAD# APPLIED   STANDBY_DEST
-------------- ---------- --------- ---------------
            28          1 NO        YES
			
PRIMARY> select thread#,low_sequence#,high_sequence# from v$archive_gap;

no rows selected


2.7. Disable the second archive location for the standby database.

PRIMARY> alter system set log_archive_dest_state_2=defer scope=both;

System altered.

PRIMARY> show parameter log_archive_dest_state_2

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
log_archive_dest_state_2             string                           DEFER


3. Patches Apply on Standby Database.


3.1 Cancel MRP process and stop all services associated with ORACLE HOME.

STANDBY> alter database recover managed standby database cancel;

Database altered.

STANDBY> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
STANDBY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ol6-112-lab2 32119956]$ lsnrctl stop LISTENER

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 04-FEB-2021 12:58:50

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol6-112-lab2.localdomain)(PORT=1521)))
The command completed successfully


3.2. Patch apply on first standby database server.


PSU patch apply.

[oracle@ol6-112-lab2 32119956]$ cd /home/oracle/COMBO_PSU_JAA21/32126886/31985579
[oracle@ol6-112-lab2 31985579]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version    : 12.2.0.1.23
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2021-02-04_13-00-19PM_1.log

Verifying environment and performing prerequisite checks...

--------------------------------------------------------------------------------
Start OOP by Prereq process.
Launch OOP...

Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version    : 12.2.0.1.23
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2021-02-04_13-01-08PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   27338041  27547329  28259833  28729169  29141015  29494060  29918340  30340202  30700212  31113348  31550110  31985579

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/app/oracle/product/12.1.0/db_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '27338041' to OH '/u01/app/oracle/product/12.1.0/db_1'
ApplySession: Optional component(s) [ oracle.assistants.asm, 12.1.0.2.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.network.rsf, 12.1.0.2.0...

Patching component oracle.assistants.server, 12.1.0.2.0...
---------------Trimmed Data---------------
Patching component oracle.ldap.owm, 12.1.0.2.0...

Patching component oracle.ctx, 12.1.0.2.0...
Composite patch 31985579 successfully applied.
Log file location: u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2021-02-04_13-01-08PM_1.log

OPatch succeeded.


OJVM patch apply.

[oracle@ol6-112-lab2 Desktop]$ cd /home/oracle/COMBO_PSU_JAA21/32126886/32119956/
[oracle@ol6-112-lab2 32119956]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version    : 12.2.0.1.23
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2021-02-04_13-30-30PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   32119956  

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/app/oracle/product/12.1.0/db_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '32119956' to OH '/u01/app/oracle/product/12.1.0/db_1'
ApplySession: Optional component(s) [ oracle.sqlj, 12.1.0.2.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.javavm.server, 12.1.0.2.0...

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

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...

Patching component oracle.rdbms, 12.1.0.2.0...

Patching component oracle.javavm.client, 12.1.0.2.0...

Patching component oracle.dbjava.jdbc, 12.1.0.2.0...

Patching component oracle.dbjava.ic, 12.1.0.2.0...

																Patch 32119956 successfully applied.
Sub-set patch [27001733] has become inactive due to the application of a super-set patch [32119956].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2021-02-04_13-30-30PM_1.log
							
OPatch succeeded.


3.3 Validate the applied patches.

[oracle@ol6-112-lab2 Desktop]$ opatch lspatches
32119956;Database PSU 12.1.0.2.210119,Oracle JavaVM Component (JAN2021)
31985579;Database Patch Set Update : 12.1.0.2.210119 (31985579)

[oracle@ol6-112-lab2 prechecks]$ $ORACLE_HOME/OPatch/opatch lsinventory > /home/oracle/COMBO_PSU_JAN21/prechecks/after_current_lsinventory.txt
[oracle@ol6-112-lab2 prechecks]$ ls -lrt after_current_lsinventory.txt
-rw-r--r-- 1 oracle oinstall 19216 Feb  4 14:14 after_current_lsinventory.txt


4. Patches Apply on Primary Database. 


4.1 After successful completion of combo patch on standby database server, now we will apply same on Primary database server. Stop all services associated to ORACLE HOME along with disable/stop dependent jobs like crontab, DBMS scheduler, OEM, application etc.

PRIMARY>shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
PRIMARY>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ol6-112-lab1 ~]$ lsnrctl stop LISTENER

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 04-FEB-2021 13:47:28

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol6-112-lab1.localdomain)(PORT=1521)))
The command completed successfully


4.2. Patch apply on primary database server.

PSU patch apply.

[oracle@ol6-112-lab1 32119956]$ cd /home/oracle/COMBO_PSU_JAA21/32126886/31985579
[oracle@ol6-112-lab1 31985579]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version    : 12.2.0.1.23
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2021-02-04_13-50-26PM_1.log

Verifying environment and performing prerequisite checks...

--------------------------------------------------------------------------------
Start OOP by Prereq process.
Launch OOP...

Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version    : 12.2.0.1.23
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2021-02-04_13-51-07PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   27338041  27547329  28259833  28729169  29141015  29494060  29918340  30340202  30700212  31113348  31550110  31985579

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/app/oracle/product/12.1.0/db_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '27338041' to OH '/u01/app/oracle/product/12.1.0/db_1'
ApplySession: Optional component(s) [ oracle.assistants.asm, 12.1.0.2.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.network.rsf, 12.1.0.2.0...

Patching component oracle.assistants.server, 12.1.0.2.0...
---------------Trimmed Data---------------
Patching component oracle.ldap.owm, 12.1.0.2.0...

Patching component oracle.ctx, 12.1.0.2.0...
Composite patch 31985579 successfully applied.
Log file location: /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2021-02-04_13-51-07PM_1.log

OPatch succeeded.


OJVM patch apply.

[oracle@ol6-112-lab1 31985579]$ cd /home/oracle/COMBO_PSU_JAA21/32126886/32119956/
[oracle@ol6-112-lab1 32119956]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version    : 12.2.0.1.23
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2021-02-04_14-06-32PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   32119956

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/app/oracle/product/12.1.0/db_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '32119956' to OH '/u01/app/oracle/product/12.1.0/db_1'
ApplySession: Optional component(s) [ oracle.sqlj, 12.1.0.2.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.javavm.server, 12.1.0.2.0...

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

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...

Patching component oracle.rdbms, 12.1.0.2.0...

Patching component oracle.javavm.client, 12.1.0.2.0...

Patching component oracle.dbjava.jdbc, 12.1.0.2.0...

Patching component oracle.dbjava.ic, 12.1.0.2.0...
Patch 32119956 successfully applied.
Sub-set patch [27001733] has become inactive due to the application of a super-set patch [32119956].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2021-02-04_14-06-32PM_1.log

OPatch succeeded.


4.3 Validate the applied patches.

[oracle@ol6-112-lab1 32119956]$ $ORACLE_HOME/OPatch/opatch lsinventory > /home/oracle/COMBO_PSU_JAN21/prechecks/after_current_lsinventory.txt
[oracle@ol6-112-lab1 32119956]$ ls -lrt /home/oracle/COMBO_PSU_JAN21/prechecks/after_current_lsinventory.txt
-rw-r--r-- 1 oracle oinstall 19216 Feb  4 14:14 /home/oracle/COMBO_PSU_JAN21/prechecks/after_current_lsinventory.txt
[oracle@ol6-112-lab1 32119956]$ opatch lspatches
32119956;Database PSU 12.1.0.2.210119,Oracle JavaVM Component (JAN2021)
31985579;Database Patch Set Update : 12.1.0.2.210119 (31985579)

OPatch succeeded.


NOTE: If you are applying in RAC environment, followed patch apply steps on all nodes in a rolling fashion i.e no downtime require for patches apply. Click here for elaborated steps.

5. Post Patches Apply on Primary Database.


5.1 Start all services associated with patched ORACLE HOME.

[oracle@ol6-112-lab1 32119956]$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 04-FEB-2021 14:18:01

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

Starting /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ol6-112-lab1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol6-112-lab1.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol6-112-lab1.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                04-FEB-2021 14:18:23
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ol6-112-lab1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol6-112-lab1.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@ol6-112-lab1 32119956]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 4 14:18:31 2021

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

PRIMARY>startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             666894440 bytes
Database Buffers          398458880 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
PRIMARY>alter pluggable database all open; ==> Applicable only in  Multitenant (CDB/PDB) architecture.
Pluggable database altered.
PRIMARY>exit


5.2 Execute SQL patch (datapatch) command.

[oracle@ol6-112-lab1 32119956]$ cd $ORACLE_HOME/OPatch
[oracle@ol6-112-lab1 OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Thu Feb  4 14:21:41 2021
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_21380_2021_02_04_14_21_41/sqlpatch_invocation.log

Connecting to database...OK
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 SQL patches:
Patch 22139226 (Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016)):
  Not installed in the binary or the SQL registry
Patch 24315824 (Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)):
  Not installed in the binary or the SQL registry
Patch 24917972 (Database PSU 12.1.0.2.170117, Oracle JavaVM Component (JAN2017)):
  Not installed in the binary or the SQL registry
Patch 27001733 (Database PSU 12.1.0.2.180116, Oracle JavaVM Component (JAN2018)):
  Installed in CDB$ROOT PDB$SEED only
Patch 32119956 (Database PSU 12.1.0.2.210119,Oracle JavaVM Component (JAN2021)):
  Installed in the binary registry only
Bundle series PSU:
  ID 210119 in the binary registry and ID 180116 in PDB CDB$ROOT, ID 180116 in PDB PDB$SEED

Adding patches to installation queue and performing prereq checks...
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED
    The following patches will be rolled back:
      27001733 (Database PSU 12.1.0.2.180116, Oracle JavaVM Component (JAN2018))
    The following patches will be applied:
      32119956 (Database PSU 12.1.0.2.210119,Oracle JavaVM Component (JAN2021))
      31985579 (DATABASE PATCH SET UPDATE 12.1.0.2.210119)

Installing patches...
Patch installation complete.  Total patches installed: 6

Validating logfiles...
Patch 27001733 rollback (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/27001733/21728084/27001733_rollback_SUNCDB_CDBROOT_2021Feb04_14_23_22.log (no errors)
------------------TRIMMED DATA------------------
SQL Patching tool complete on Thu Feb  4 14:44:20 2021


5.3 Validate applied patches in database.

PRIMARY> SET LINESIZE 400
COLUMN action_time FORMAT A20
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A40
COLUMN version FORMAT A10
COLUMN bundle_series FORMAT A10

SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time, action,status,description, version,patch_id,bundle_series
FROM sys.dba_registry_sqlpatch ORDER by action_time;PRIMARY>PRIMARY>PRIMARY>PRIMARY>PRIMARY>PRIMARY>PRIMARY>PRIMARY>PRIMARY>PRIMARY>  2

ACTION_TIME          ACTION     STATUS     DESCRIPTION                              VERSION      PATCH_ID BUNDLE_SER
-------------------- ---------- ---------- ---------------------------------------- ---------- ---------- ----------
04-FEB-2021 14:44:05 APPLY      SUCCESS    Database PSU 12.1.0.2.210119,Oracle Java 12.1.0.2     32119956
                                           VM Component (JAN2021)

04-FEB-2021 14:44:05 ROLLBACK   SUCCESS    Database PSU 12.1.0.2.180116, Oracle Jav 12.1.0.2     27001733
                                           aVM Component (JAN2018)

04-FEB-2021 14:44:09 APPLY      SUCCESS    DATABASE PATCH SET UPDATE 12.1.0.2.21011 12.1.0.2     31985579 PSU
                                           9


5.4 Validate generated archives in between post patch apply along with max log sequence. Same up to max log sequence will be applied on standby database to sync in.

PRIMARY>set lines 222 pages 111
PRIMARY>select sequence#, first_time, next_time from v$archived_log order by first_time desc,sequence#;

 SEQUENCE# FIRST_TIME           NEXT_TIME
---------- -------------------- --------------------
        43 04-FEB-2021 15:15:40 04-FEB-2021 15:18:37
        43 04-FEB-2021 15:15:40 04-FEB-2021 15:18:37
        42 04-FEB-2021 15:01:39 04-FEB-2021 15:15:40
        42 04-FEB-2021 15:01:39 04-FEB-2021 15:15:40
        41 04-FEB-2021 15:01:18 04-FEB-2021 15:01:39
        41 04-FEB-2021 15:01:18 04-FEB-2021 15:01:39
        40 04-FEB-2021 14:44:15 04-FEB-2021 15:01:18

PRIMARY>col STANDBY_DEST for a15;
PRIMARY>SELECT MAX(SEQUENCE#),THREAD#,APPLIED,STANDBY_DEST FROM GV$ARCHIVED_LOG GROUP BY THREAD#,APPLIED,STANDBY_DEST ;

MAX(SEQUENCE#)    THREAD# APPLIED   STANDBY_DEST
-------------- ---------- --------- ---------------
            43          1 NO        YES



5.5 Enable second archive location for standby database. This step also can be execute after start MRP process on standby database. 

PRIMARY>alter system set log_archive_dest_state_2=enable scope=both;
PRIMARY>show parameter log_archive_dest_state_2

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
log_archive_dest_state_2             string                           ENABLE

NOTE: Follow all above post-patch steps on all databases running from the same patched ORACLE HOME. And if you are applying in RAC databases then need to execute only in any one available instance.


6. Post Patches Apply on Standby Database.


6.1 Start all services associated with patched ORACLE HOME.

STANDBY> startup mount
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             713031784 bytes
Database Buffers          352321536 bytes
Redo Buffers                5455872 bytes
Database mounted.
SQL> exit

[oracle@ol6-112-lab2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 04-FEB-2021 15:30:24

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

Starting /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ol6-112-lab2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol6-112-lab2.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol6-112-lab2.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                04-FEB-2021 15:30:26
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ol6-112-lab2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol6-112-lab2.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "suncdb_s" has 1 instance(s).
  Instance "suncdb_s", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


6.2 Start MRP process.

STANDBY> alter database recover managed standby database disconnect;

Database altered.


6.3 Validate applied archives after start MRP process and also review database alert log.

STANDBY> set lines 222 pages 111
STANDBY> select sequence#, first_time, next_time from v$archived_log order by first_time desc,sequence#;

 SEQUENCE# FIRST_TIME           NEXT_TIME
---------- -------------------- --------------------
        43 04-FEB-2021 15:15:40 04-FEB-2021 15:18:37
        42 04-FEB-2021 15:01:39 04-FEB-2021 15:15:40
        41 04-FEB-2021 15:01:18 04-FEB-2021 15:01:39
        40 04-FEB-2021 14:44:15 04-FEB-2021 15:01:18
        39 04-FEB-2021 14:38:57 04-FEB-2021 14:44:15


7. Start/enable all dependent database jobs like crontab, DBMS scheduler, OEM, application etc.


Click here for more patch related articles.


This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments /subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

DBsGuru

Hello and welcome to DBsGuru,DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training. Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.Thanks for the visits!Share Learn Grow!

6 thoughts on “Step By Step Apply Combo Patch on Primary & Standby (DATAGUARD) Database in Oracle”

  2. In step “5.1” database is started “startup” then “5.2” datapatch verbose is executed. so here my question is to execute datapatch verbose database should be started in upgrade mode. i.e “startup upgrade”. please clear me if i am wrong.

    Thank you.

    1. Thanks, Akshay for your concern. Partially you are correct but now it’s not mandated to start DB in upgrade for post patch (OJVM). Refer to below notes and always the best way to go through readme for each patch ID before applying

      “Beginning with the Jan2017 OJVM PSU patchset for 11.2.0.4 and for 12.1.0.2, Oracle Development is relaxing the requirement to restart the database in upgrade mode.”

      Hopefully, it will clear your concerns.

      Thanks.
      Team DBsGuru

  3. is the need to not use startup upgrade primarily for RAC environments though?

    “The procedures apply to RAC, RAC One, Data Guard, and GoldenGate.”

    I have similar issue wanting to patch a physical standby (not data guard) and trying to identify how post install database patches would get applied for both db and ojvm as standby can’t be started to run the database portion there.

    1. Hi Shaun……Below is clarification

      is the need to not use startup upgrade primarily for RAC environments though?
      Partially no, refer to the last reply on the same thread.

      “The procedures apply to RAC, RAC One, Data Guard, and GoldenGate.”
      Depends on your environment like if you want to apply a RAC environment then follow RAC steps.

      I have similar issue wanting to patch a physical standby (not data guard) and trying to identify how post install database patches would get applied for both db and ojvm as standby can’t be started to run the database portion there.
      Physical standby is dataguard only, you can follow the same sequences of steps as we have demonstrated.

      Thanks.
      Team DBsGuru

Comments are closed.

Related Articles