Applying patch is one of the important task for DBA. I will discuss here how to apply PSU patch on your RAC database including DR for 12c and 11g.
Patch Set Update: A collection of proactive, stabilizing cumulative patches for a particular product version (base release or patch set). PSUs are cummulative and include all of the security fixes from SPU patches (formerly known as CPU), plus additional fixes. Oracle releases PSU every quarter [17th January, 17th April, 17th July & 17th October].
Prerequisites: Can run anytime as there is not impact on live database
- PSU # 26636028 -> GI Patch # 26635745 and JVM # 26635834
- download latest OPatch binary/software to all servers on your local file system (you may use /backup/patchs)
- Copy PSU patch to all servers (/backup/patches)
- Unzip Patch Binary
- Check Existing OPatch version on your database sever [ check required OPatch version from readme file of latest patch binary]
- $ Set Grid_HOME or ORACLE_HOME with export ORACLE_HOME=your grid/db home
$ opatch version - Update OPatch (if required)
- Download correct version(from patching readme file) of OPatch
From Oracle User: $ cd $ORACLE_HOME $ mv OPatch OPatch.bak $ unzip /u01/oracle/patches/*
- Have current backup of database? Make sure Full Database backup has taken.
- Run Prerequisites check for GRID/Database and JVM Patch and make sure there is no error on below commands.
Environment Variable:
GRID_HOME=/u01/app/12.1.0/grid ORACLE_HOME=/u01/app/oracle/12.1.0/dbhome_1 PATCH_LOC=/backup/patches/oct2019 GI_PATCH_ID=/backup/patches/oct2019/26635745 JVM_PATCH_ID=/backup/patches/oct2019/26635834
Patching Prerequisite command:
# For RAC:
# For GRID
$ export PATH=$GRID_HOME/OPatch:$PATH
$ export ORACLE_HOME=$GRID_HOME
$ which opatch
> /u01/app/12.1.0/grid/OPatch/opatch
$ cd $PATCH_LOC/$DB_PATCH_ID
$ $ORACLE_HOME/OPatch/opatchauto apply $PATCH_LOC/$DB_PATCH_ID -analyze
# For JVM on RAC
$ export PATH=$ORACLE_HOME/OPatch:$PATH
$ which opatch
> /u01/app/oracle/12.1.0/dbhome_1/OPatch/opatch
$ cd $PATCH_LOC/$JVM_PATCH_ID opatch prereq CheckConflictAgainstOHWithDetail -ph ./ -invPtrLoc $ORACLE_HOME/oraInst.loc
# For Non-RAC:
# For DB Patch:
$ export PATH=$ORACLE_HOME/OPatch:$PATH
$ which opatch
> /u01/app/oracle/12.1.0/dbhome_1/OPatch/opatch
$ cd $PATCH_LOCATION/$DB_PATCH_ID
$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./ -invPtrLoc $ORACLE_HOME/oraInst.loc
# For JVM Patch:
$ cd $PATCH_LOC/$JVM_PATCH_ID
$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./ -invPtrLoc $ORACLE_HOME/oraInst.loc
Patching Activity: (During Patching Window)
Assume there is only oracle user for GRID and Oracle Home.
- (o) => run from oracle user
- (r) => run from root user
NOTE:::If Database has DR Configuration: Has to apply Step 1 – 8 on DR servers. During this time Production database don’t need to down. (Must not allowed to run parallel in two nodes)
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; # If Multiple DR exist
Run below command on StandBy Database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; (o) $ srvctl stop database -d orcl
If you have multiple database with ORACLE_HOME then can stop all databases using below commands:
$ cd /home/oracle $touch dbhome_list Note: Add all DB Name on this File: orcl, payrol, hr then run below command to stop all databases on particular server/host $cat dbhome_list orcl payroll hr (o) $ srvctl stop home -o $ORACLE_HOME -s /home/oracle/dbhome_list -n hostname
Step 3: Create ocm file: [only applicable for lower version of 12c ]
Set $ORACLE_HOME for Grid (o) . /home/oracle/grid.env (o) $ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /home/oracle/ocm.rsp
Step 4: Stop GI with unlock mode
(r) $ . /home/oracle/grid.env (r) $ORACLE_HOME/crs/install/rootcrs.pl -unlock
Step 5: Take GRID_HOME and ORACLE_HOME Backup
This is highly commanded to take backup of GRID/ORACLE HOME if incase need to rollback does not complete properly.
# RUN FROM ROOT USER: (If you have GRID User then run from GRID) GI Home: $ echo $GRID_HOME > /u01/app/12.1.0/grid $ tar cfp /backup/patches/oct2019/GI_HOME.tar $GRID_HOME ORACLE Home: $ echo $ORACLE_HOME > /u01/app/oracle/12.1.0/dbhome_1 # For AIX $ tar cfp /backup/patches/oct2019/rdbms_home.tar $ORACLE_HOME # For Linux $ tar -Pcf /backup/patches/oct2019/rdbms_home.tar $ORACLE_HOME Binary Backup Validation: $ tar tvf /backup/patches/oct2017/GI_HOME.tar | more $ tar tvf /backup/patches/oct2019/rdbms_home.tar | more
Step 6: Start HAS on node where want to apply patch
(r) $ crsctl start has (r) $ 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!
(r) $ crsctl stat res -t
Step 7: Apply GRID and RDBMS Patch
GI and RDBMS Home Patch: Apply the patch for GI and RDBMS:
For 11g or lower version:
$ which opatch > /u01/app/12.1.0/grid/OPatch/opatch $ echo $ORACLE_HOME > /u01/app/11.1.0/grid $ /u01/app/11.1.0/grid/OPatch/opatch auto <UNZIPPED_PATCH_LOCATION>/26635745 -ocmrf <ocm response file - response file created in Step 3> Example: $ /u01/app/11.1.0/grid/OPatch/opatchauto apply /backup/patches/oct2019/26636028/26635745 -ocmrf /home/oracle/ocm.rsp
Step 7a. Apply the patch for GI and RDBMS HOME together:
For 12c or above Version:
$ echo $GRID_HOME > /u01/app/12.1.0/grid $ export PATH=$GRID_HOME/bin:$GRID_HOME/OPatch:$PATH $ which opatch $ export ORACLE_SID=+ASM1 # for Instance 2 it will be +ASM2 # Apply the patch for GI and RDBMS together: $ cd $PATCH_LOC/$DB_PATCH_ID $ $GRID_HOME/OPatch/opatchauto apply $PATCH_LOC/$DB_PATCH_ID
Step 7b: Apply the patch for GI and RDBMS separately:
$ cd $PATCH_LOC/$DB_PATCH_ID From GRID OPatch Location: $ export ORACLE_SID=+ASM1 $ echo $ORACLE_HOME > /u01/app/12.1.0/grid $ echo $GRID_HOME > /u01/app/12.1.0/grid $ which opatch > /u01/app/12.1.0/grid/OPatch/opatch $ /u01/app/12.1.0/grid/OPatch/opatchauto <UNZIPPED_PATCH_LOCATION>/$PATCH_ID -oh <GI_HOME> Example: $ /u01/app/12.1.0/grid/OPatch/opatchauto /backup/patches/oct2019/26635745 -oh $GI_HOME RDBMS Patch: $ echo $ORACLE_HOME > /u01/app/oracle/12.1.0/dbhome_1 $ /u01/app/oracle/12.1.0/dbhome_1/OPatch/opatchauto <UNZIPPED_PATCH_LOCATION>/$PATCH_ID -oh <ORACLE_HOME> Example: $ /u01/app/oracle/12.1.0/dbhome_1/OPatch/opatchauto /backup/patches/oct2019/26635745 -oh $ORACLE_HOME
Step 8: Verify patch inventory for GRID and Oracle
$ which opatch > /u01/app/12.1.0/grid/OPatch/opatch $ opatch lsinv $ which opatch > /u01/app/oracle/12.1.0/dbhome_1/OPatch/opatch $ opatch lsinv
Step 9: Apply JVM Patch
$ echo $ORACLE_HOME > /u01/app/oracle/12.1.0/dbhome_1 $ which opatch > /u01/app/oracle/12.1.0/dbhome_1/OPatch/opatch $ $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 orcl $ 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 orcl -i orcl1 -o open
Enable LOG_ARCHIVE_DEST_STATE [applicable if databases have DR]
On Primary Database: SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH SID='*';
Start MRP process on Standby Database to apply post database Installation on Standby
On StandBy Database: SQL> startup nomount SQL> alter database mount standby database; SQL> alter database recover managed standby database disconnect from session;
Starting the Database for Post Installation Steps in an Oracle RAC Environment
On only one node, perform the following steps to start an Oracle RAC database in startup upgrade mode.
For 11g:
cd $ORACLE_HOME/sqlpatch/JVM_PATCH_ID SQL> sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> STARTUP SQL> alter system set cluster_database=false scope=spfile; [on both Primary and DR Side] SQL> SHUTDOWN SQL> STARTUP UPGRADE SQL> @postinstall.sql SQL> SHUTDOWN SQL> STARTUP
The catbundle.sql execution is reflected in the dba_registry_history view by a row associated with bundle series PSU.
cd $ORACLE_HOME/rdbms/admin SQL> sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> STARTUP SQL> @catbundle.sql psu apply SQL> QUIT SQL> alter system set cluster_database=true scope=spfile; SQL> SHUTDOWN SQL> STARTUP
For 12c:
cd $ORACLE_HOME/sqlpatch/26635834 SQL> sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> STARTUP SQL> alter system set cluster_database=false scope=spfile; [on both Primary and DR Side] SQL> SHUTDOWN SQL> STARTUP UPGRADE SQL> @postinstall.sql SQL> SHUTDOWN SQL> STARTUP
For 11g and 12c:
After installing the SQL portion of the patch, some packages could become INVALID. This will get recompiled upon access or you can run utlrp.sql to get them back into a VALID state.
$ cd $ORACLE_HOME/rdbms/admin SQL> sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> @utlrp.sql
Step 12. Upgrade Oracle Recovery Manager Catalog
If you are using the Oracle Recovery Manager, the catalog needs to be upgraded. Enter the following command to upgrade it:
$ rman catalog username/password@alias RMAN> UPGRADE CATALOG SQL> SELECT version, status FROM dba_registry WHERE comp_id='JAVAVM';
