Patching Oracle databases involves applying software updates, known as patches, to fix bugs, address security vulnerabilities, and introduce new features or enhancements. Oracle provides regular patch sets, updates, and critical patch updates (CPU) to ensure the stability and security of their database software.
Here are the general steps involved in patching an Oracle database:
Identify the required patch: Determine the appropriate patch for your Oracle database version and platform. Oracle provides patches through their official website, My Oracle Support (MOS). You'll need a valid Oracle support account to access patches.
Download the patch: Once you've identified the necessary patch, download it from the Oracle website or MOS. Make sure to choose the correct patch for your operating system and database version.
Review the patch documentation: Oracle provides detailed documentation with each patch, including installation instructions, prerequisites, and any known issues. Review the documentation carefully to understand the patching process and any specific requirements.
Backup the database: Before applying any patches, it's crucial to back up your database to ensure data protection. Create a full backup of the database, including all relevant files, to revert to a stable state if any issues occur during patching.
Apply the patch: Follow the installation instructions provided with the patch to apply it to your Oracle database. The exact method for applying the patch may vary depending on the patch type and your database environment. Typically, it involves running an installation script or using the Oracle Universal Installer (OUI).
Verify the patch installation: After applying the patch, verify its installation to ensure it was successful. Oracle provides various tools and commands to check the patch level and confirm its application. For example, you can query the database views to check the patch level or use the opatch utility for patch verification.
Test the patched database: Perform thorough testing on the patched database to ensure that it functions correctly and all applications and functionalities are working as expected. This step helps identify any issues introduced by the patch and allows you to address them before moving to production.
Communicate and schedule downtime: If you're patching a production database, it's crucial to communicate with the relevant stakeholders and schedule a maintenance window for applying the patch. This helps minimize disruption to users and ensures a controlled environment for patching.
Apply post-patching tasks: Depending on the patch and Oracle version, there may be additional post-patching tasks to perform. These tasks can include updating optimizer statistics, invalidating and recompiling objects, or executing any necessary scripts provided with the patch.
Monitor and maintain the patched environment: Once the patch is applied, monitor the database for any unexpected behavior or performance issues. Oracle may release further patches or updates in the future, so it's important to stay informed about new releases and consider applying them as needed.
Remember, patching an Oracle database should be performed with caution and in a controlled environment. It's recommended to have a backup strategy in place and to test the patching process in a non-production environment before applying patches to critical systems. Additionally, it's always a good practice to consult the official Oracle documentation or seek guidance from Oracle support for specific patching requirements and recommendations.
High Level steps for applying the Patch on Oracle 12c for single instance database
- Check current version of Opatch Tool
- Upgrade the Opatch utility
- Take the backup of ORACLE_HOME
- Shutdown the Database and Listener
- Apply PSU patch on ORACLE_HOME
- Execute Post installation Scripts (datapatch)
- Startup the Database and Listener
- Check the dba_registry_sqlpatch
Step 1:- Current version of Opatch Tool in our environment
[oracle@Testdb] export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@Testdb] $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.6
Upgrade Opatch utility :-
Step 2:- Backup the existing Opatch folder
[oracle@Testdb] cd $ORACLE_HOME
[oracle@orcl:db_1 orcldemo] mv OPatch/ OPatch_bkp
Step 3:- Unzip 6880880 patch
[oracle@Testdb] unzip p6880880_122010_Linux-x86-64.zip -d $ORACLE_HOME
Step 4:- Check the opatch lsinventory
[oracle@Testdb] $ORACLE_HOME/OPatch/opatch lsinventory
OPatch succeeded.
[oracle@orcl:28163133 orcldemo] $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
OPatch succeeded.
Step 5:- Take Backup of ORACLE_HOME (Rollback plan)
[oracle@Testdb]$ tar -cvf oracle_home_Sep22_2018.tar $ORACLE_HOME
Step 6:- Shutdown Standby Database and Listener
SQL> shut immediate
Step 7 :- Apply PSU patch on ORACLE_HOME.
[oracle@Testdb] unzip p28163133_122010_Linux-x86-64.zip
[oracle@Testdb] export PATH=/oradb/app/oracle/product/12.2.0.1/db_1/OPatch:$PATH
[oracle@Testdb] opatch apply
Step 8:- Startup the Database and Listener
Step 9:- Execute post patch steps and run datapatch command
[oracle@Testdb] ./datapatch -verbose
Patch 28163133 apply: SUCCESS
Step 10 :- After applying PSU patch,Check the DBA_REGISTRY_SQLPATCH
SET LINESIZE 400
SET PAGESIZE 100
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;
SELECT comp_name, version, status FROM dba_registry;
Step 11:- Check opatch lsinventory and list of patches applied in ORACLE_HOME
[oracle@Testdb] /oradb/app/oracle/product/12.2.0.1/db_1/OPatch/opatch lsinventory
High Level steps for applying the Patch on Oracle 19c for single instance database
Follow Same above patching steps as in oracle 12c standalone no changes
High Level steps for applying for PSU (Patch Set Update) Patching on 12C RAC with Data Guard.
Step 1. Disable OEM monitoring agent and another monitor agent (if applicable), setup blackout for all environments to avoid all kind of notification.
Step 2. Stop Replication with DR. Run below command on Production:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=DEFER;
Run below command on StandBy Database:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
oracle@Testdb: srvctl stop database -d Testdb
Step 3: Create ocm file: [only applicable for lower version of 12c ]
Set $ORACLE_HOME for Grid
(oracle) . /home/oracle/grid.env
(oracle) $ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /home/oracle/ocm.rsp
Step 4: Stop GI with unlock mode
(root level) $ . /home/oracle/grid.env
(root level) $ORACLE_HOME/crs/install/rootcrs.pl -unlock
Step 5: Take GRID_HOME and ORACLE_HOME Backup
Step 6: Start HAS on node where want to apply patch
(root level) $ crsctl start has
(root level) $ ps -ef | grep oracle
Use the CRS control utility to check the status of the CRS. The databases for the node should not come up!
(root level) $ crsctl stat res -t
Step 7: Apply GRID and RDBMS Patch separately
$ /u01/app/12.1.0/grid/OPatch/opatchauto /backup/patches/oct2020/26635745 -oh $GI_HOME
$ /u01/app/oracle/12.1.0/dbhome_1/OPatch/opatchauto /backup/patches/oct2020/26635745 -oh $ORACLE_HOME
Step 8: Verify patch inventory for GRID and Oracle
Step 9: Apply JVM Patch
$ $ORACLE_HOME/OPatch/opatch apply -local $PATCH_LOC/$JVM_PATCH_ID
Or
you may use below command:
$ cd $PATCH_LOC/$JVM_PATCH_ID
$ /u01/app/oracle/12.1.0/dbhome_1/OPatch/opatch apply -local
Repeat Step 1-9 on all remaining nodes, one node at a time (Parallel not allowed).
Step 10: Lock the GI on all nodes (one by one) – after completed the above steps and start database
From ROOT User:
$ export ORACLE_SID=+ASM1
$ crsctl stop crs
$ $ORACLE_HOME/crs/install/rootcrs.pl -patch
From ORACLE User:
$ srvctl start home -o $ORACLE_HOME -s /home/oracle/dbhome_list -n `hostname`
# Check all resource and database should be up and running in this stage - verification
$crsctl stat res -t
# if any instance showing down then startup with srvctl command.
$ srvctl start database -d Testdb
$ crsctl stat res -t
Step 11: Patch Post-Installation
Shutdown all instances and then startup 1 instance –
Run Oracle SQL script from 1 node and make sure scripts are running for all database.
$srvctl start instance -d Testdb -i Testdb1 -o open
Enable LOG_ARCHIVE_DEST_STATE [applicable if databases have DR]
On Primary Database:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH SID='*';
For 12c:
cd $ORACLE_HOME/sqlpatch/26635834
sqlplus /nolog
CONNECT / AS SYSDBA
STARTUP
alter system set cluster_database=false scope=spfile; [on both Primary and DR Side]
SHUTDOWN
STARTUP UPGRADE
@postinstall.sql
SHUTDOWN
STARTUP
High Level steps apply Rolling PSU Patch in Oracle Database 12c RAC
Step1: Ensure the Latest OPatch utility is Available
Check Current OPatch Utility Version
Verify if the required OPatch utility as mentioned in readme.txt is available.
$export ORACLE_HOME=/u01/product/12.1.0.2/dbhome1
$export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
$opatch version
OPatch Version: 12.2.0.1.3
OPatch succeeded.
OH homes and GI home on all nodes. Latest OPatch utility for your version is available for Download from My Oracle Support patch 6880880.
To Upgrade the OPatch follow below steps for each OH and GI homes
Download and transfer the OPatch utility file to all the server
#opatch version
OPatch Version: 12.2.0.1.17
OPatch succeeded.
Step2: Perform Complete Cluster Stack Health Check: Strongly Recommended
1.p -eaf|grep tns
2.ps -eaf|grep smon
3.crs_stat -t -v
4.crsctl status resource
5.crsctl check crs
6.crsctl check cluster -all
7.srvctl status asm
8.tar cvf - /u01/app/12.2.0/grid | gzip > /backup/PATCH/Jan2022/Grid_home_bkp1.tar.gz
9.tar cvf - /u01/app/oracle/product/12.2.0.1/dbhome_1 | gzip > /backup/PATCH/Jan2022/ORACLE_HOME_bkp1.tar.gz
If you have latest version of TFA installed and configured ORACheck is part of TFA and already installed in your environment.You can run racheck directly from tfa CLI interface as below
$tfactl
tfactl> orachk
Step3: Prepare the Node for Patching
NOTE: Since the patching is done in rolling mode to avoid any outage for application, ensure the database and services is always available from available node in the cluster
Verify the Database Services Running on Node1
$srvctl status service -d DB1
Service srv1 is running on instance(s) DB11,DB12
$srvctl status service -d DB2
Service srv2 is running on instance(s) DB21,DB22
Stop or relocate the services from the node1 to node2
NOTE: In production environment run this step at least few hours before starting the patching to ensure that all the sessions are properly gone from node1. In my case, since the services are running on all the nodes I will simply stop them on node1 (the node which I am going to patch first)
$srvctl stop service -d DB1 -s srv1 -n node1
$srvctl stop service -d DB2 -s srv2 -n node1
Verify and Confirm that All the Services are now running on 2nd node and no service is running on node1
$srvctl status service -d DB1
Service srv1 is running on instance(s) DB21
$srvctl status service -d DB2
Service srv2 is running on instance(s) DB22
Verify and Ensure that there is no Active Session and Transactions Running on Node1
$ sqlplus "/as sysdba"
select SID,USERNAME,STATUS from v$session where username not in ('SYS','DBSNMP');
no rows selected
col machine for a30
col username for a30
col program for a30 trun
col sid for 99999
set lines 300select sid,USED_UBLK,username,program,machine,systimestamp from v$transaction t, v$session s where SES_ADDR = s.saddr order by 1;
no rows selected
select sid,USED_UBLK,username,program,machine,systimestamp from gv$transaction t, gv$session s where SES_ADDR = s.saddr order by 1;
no rows selected
NOTE: if you are using OPatch utility 12.2.0.1.5 or later you do not need OCM(Oracle Configuration Manager) response file any more
Step4: Validate Patch Inventory for all homes
$opatch lsinventory -detail -oh /u01/grid/product/12.1.0.2/GI > /u01/stage/GI_OH_INV
$opatch lsinventory -detail -oh /u01/product/12.1.0.2/DBHOME > /u01/stage/DB_OH_INV
Step5: Verify and resolve the Patch Conflicts
as root user
# opatchauto apply /u01/stage/29698592 -analyze
or
try the below analyze command on both GI and ORACLEHOME's
#/u01/app/12.1.0.2/grid/OPatch/opatchauto apply /mnt/data/patch/rolling_patch/28349311 -analyze -oh /u01/app/12.1.0.2/grid
Step6: Run opatch auto to patch node1
As root user, execute the following command on node1
#export ORACLE_HOME=/u01/grid/product/12.1.0.2/gi
#export PATH=$PATH:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin
#export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
#opatchauto apply /u01/stage/29698592
or
try the below command opatchauto for GRID HOME on Node1
#/u01/app/12.1.0.2/grid/OPatch/opatchauto apply /mnt/data/patch/rolling_patch/28349311 -oh /u01/app/12.1.0.2/grid
opatchauto for ORACLE_HOME on Node 1
#/u01/app/oracle/product/12.1.0.2/db_1/OPatch/opatchauto apply /mnt/data/patch/rolling_patch/27468957 -oh/u01/app/oracle/product/12.1.0.2/db_1
Note:
OPatchauto automatically patch the typical Grid Infrastructure (GI) and RAC home directories with minimal intervention.
In general, when we invoke opatchauto will patch both the GI stack and the database software stack. Since we have mentioned the -oh it will apply the PSU to the specified home.
The main advantage of opatchauto utility was automatically down the CRS and database services and restart the services after apply patching.
To apply a patch using opatchauto,we need to run as a root user.
OPatchAuto calls datapatch to complete post patch actions upon installation of the binary patch and restart of the database
Step7: Repeat step 3 to 6 for node2.
Step8: Verify the patch Installation
$opatch lsinventory -oh /u01/grid/product/12.1.0.2/gi |grep -e 'patch_id1' -e 'patch_id2' -e 'patch_id3'
SQL> set serverout on;
SQL> exec dbms_qopatch.get_sqlpatch_status;
OR
SQL> select xmltransform(dbms_qopatch.is_patch_installed('29494060'), dbms_qopatch.get_opatch_xslt) "Patch installed?" from dual;
Patching steps on 12c GRID Home and 12c Oracle database home
1)Stop GI Home as root user
2)unlock the GI home as a root user
3)grant permission to the patch directory as a root user
4)As a grid user do precheck like opatch version,opatch lspatches,conflicts precheck,system space check
5)apply patch
6)post verification
7)lock and start GI
1)stop db home as a root user
2)grant permission on patch directory as root user
3)As oracle user do prechecks like opatch version,opatch lspatches,conflicts precheck,system space check
4)apply patch
5)post verification
6)start Oracle_Home as a root user
Check Patches status before apply using below query
SET LINESIZE 5006
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000
COLUMN action_time FORMAT A12
COLUMN action FORMAT A10
COLUMN comments FORMAT A30
COLUMN description FORMAT A60
COLUMN namespace FORMAT A20
COLUMN status FORMAT A10
SELECT TO_CHAR(action_time, ‘YYYY-MM-DD’) AS action_time,action,status,
description,patch_id FROM sys.dba_registry_sqlpatch ORDER by action_time;
col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select comp_id,comp_name,version,status from dba_registry;
Identifying Invalid Objects before patching
COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = ‘INVALID’
ORDER BY owner, object_type, object_name;
Enjoy the work and do like me if you like this article!!!
