Oracle RAC - Apply patch with Standby in Configuration

-

Oracle RAC - Apply patch with Standby in Configuration 


DBAs have to apply patches quarterly as the PSU patch have been released. In order to make task easy, We have come up with steps to Patch RAC environment with standby in configuration. 

Below are the steps and details:
 
Environment Details:
Nodes to be Patched:
Primary
Node 1 - PRIMARYRAC01
Node 2 - PRIMARYRAC02
DB to be patched: test,abd

Standby
Node 3 - STDBYRAC01
Node 4 - STDBYRAC01
DB to be patched: ntulearndr,sbblearn

Pre-Patch Installation Instruction:

Validation of Oracle Inventory.
a) Switch to Grid User
# su – grid
$ /u01/app/12.1.0.2/grid/OPatch/optach lsinventory >  /backup/patch/backup/Cluster_invn_bck.log

b) Switch to Oracle User
# su – oracle
$ /u01/app/oracle/product/12.1.0.2/dbhome_1/OPatch/optach lsinventory > /backup/patch/backup/Cluster_invn_bck.log

Collect each Database information using below command (Using sysdba):
a) Create pfile for database
SQL> create pfile='/backup/patch/backup/inittest_25052019.ora' from spfile;
b) Run Utlrp for Database
SQL> @?\rdbms\admin\utlrp.sql
c) Collect Database info:
SQL> spool /backup/patch/backup/test_info.log
SQL> select instance_name from gv$instance;
SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID';
SQL> select comp_id,comp_name,version,status from dba_registry;
SQL> select * from sys.registry$history order by 1 desc;
SQL> select * from registry$sqlpatch;
SQL> spool off

Patch Installation Instruction:

Upgrade OPATCH
OPatch needs to be upgraded on GRID_HOME and RDBMS_HOME of all nodes
1. Download OPatch version 12.2.0.1.14 to following folder path: 
/backup/patch/p6880880_121010_Linux-x86-64.zip

2. Check current OPatch version
Run below commands against GRID_HOME or RDBMS_HOME as  grid owner user and oracle owner user respectively
$ export ORACLE_HOME=<GRID_HOME or RDBMS_HOME>
$ export PATH=$ORACLE_HOME/OPatch:$PATH
$ opatch version

3. Upgrade OPatch on Database software and verify 
On all nodes as ORACLE user
$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
$ mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch_bkp_121013
$ unzip /backup/patch/p6880880_121010_Linux-x86-64.zip -d $ORACLE_HOME
$ opatch version

4. Upgrade OPatch on Grid software and verify 
On all nodes as GRID user
$ export ORACLE_HOME=/u01/app/12.1.0.2/grid
$ mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch_bkp_121013
$ unzip /backup/patch/p6880880_121010_Linux-x86-64.zip -d $ORACLE_HOME
$ opatch version

Validate Oracle Inventory
It is important to ensure that Oracle Inventory is consistent on each GRID_HOME and RDBMS_HOME that are to be patched, follow below steps to confirm:

For Oracle Grid Infrastructure: as GRID user on all nodes

$ export ORACLE_HOME=/u01/app/12.1.0.2/grid
$ $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME
The successful execution of command will list Oracle components and patches, if any for Oracle Home.
For Oracle RDBMS: as ORACLE user on all nodes
$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
$ $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME

Apply PSU

1. Download Patch PSUNUM (Completed)
The installation of patch will be carried-out as root user, however it is must that owner of the zip file is grid user.
As root perform below steps to ensure this:
# cd /backup/patch/
# unzip pPSUNUM_121020_Linux-x86-64.zip
# chown grid:oinstall –R /backup/patch/PSUNUM

2. Check for any conflicts (***)
Although no interim patches are applied thus check would pass. As root user run below command to check for conflicts in
both  GRID_HOME and RDBMS_HOME on node PRIMARYRAC01 and STDBYRAC01,  run as root :
# export ORACLE_HOME=/u01/app/12.1.0.2/grid
# export PATH=$ORACLE_HOME/OPatch:$PATH
# opatchauto apply /backup/patch/PSUNUM -analyze

3. Stop logshipping from PRIMARY and stop Managed Recovery Process on STANDBY
Defer respective log_archive_dest_n parameter and cancel managed recovery.

Stop MRP on standby server(STDBYRAC01/B):
SYS@testdr1>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Stop Archive send standby side (PRIMARYRAC01/B):
SYS@test1> ALTER SYSTEM SET log_archive_dest_state_2 = DEFER sid='*' ;
4. Apply patch on STANDBY Nodes
Logon to STDBYRAC01 and STDBYRAC01 as root user, and follow below steps on one node at a time

Source environment for GRID HOME and Patch GRID_HOME on Node 1 and Node 2

NODE 1 (STDBYRAC01)
# export ORACLE_HOME=/u01/app/12.1.0.2/grid
# export PATH=$ORACLE_HOME/OPatch:$PATH
[root@STDBYRAC01 PSUNUM]# opatchauto apply /backup/patch/PSUNUM -oh /u01/app/12.1.0.2/grid

NODE 2 (STDBYRAC01)
# export ORACLE_HOME=/u01/app/12.1.0.2/grid
# export PATH=$ORACLE_HOME/OPatch:$PATH
[root@STDBYRAC01 PSUNUM]# opatchauto apply /backup/patch/PSUNUM -oh /u01/app/12.1.0.2/grid

Source environment for RDBMS HOME and Patch RDBMS_HOME on both nodes
We will shut down standby database before patching RDBMS_HOME, this is because opatchauto will attempt to load modified SQLs in database by running datapatch utility.
As oracle user stop database:
$ srvctl stop database -d testdr

NODE 1 (STDBYRAC01)
# export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
# export PATH=$ORACLE_HOME/OPatch:$PATH
[root@STDBYRAC01 PSUNUM]# opatchauto apply /backup/patch/PSUNUM -oh /u01/app/oracle/product/12.1.0/dbhome_1

NODE 2 (STDBYRAC01)
# export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
# export PATH=$ORACLE_HOME/OPatch:$PATH
[root@STDBYRAC01 PSUNUM]# opatchauto apply /backup/patch/PSUNUM -oh /u01/app/oracle/product/12.1.0/dbhome_1

You may now start the standby database as oracle user: (STDBYRAC01/B)
$ srvctl start database -d tester -o mount

5. Apply Patch on PRIMARY Nodes (as root user)
Apply on one node at a time:
NODE 1 (PRIMARYRAC01)
# export ORACLE_HOME=/u01/app/12.1.0.2/grid
# export PATH=$ORACLE_HOME/OPatch:$PATH
[root@PRIMARYRAC01 PSUNUM]# opatchauto apply /backup/patch/PSUNUM

NODE 2 (PRIMARYRAC02)
# export ORACLE_HOME=/u01/app/12.1.0.2/grid
# export PATH=$ORACLE_HOME/OPatch:$PATH
[root@PRIMARYRAC02 PSUNUM]# opatchauto apply /backup/patch/PSUNUM

6. Start Logshipping on PRIMARY and Managed Recovery Process on STANDBY
Enabled log_archive_dest_n parameter on primary and start MRP on standby.

Start Archive send standby side:
SYS@ntulearn1> ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE sid='*' ;

Stop MRP on standby server:
SYS@ntulearndr1>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
7. Verify Patch.(Check Pre-Patch Installation Instruction output for comparison).


=================================================================
Please check our other blogs for  Oracle RAC & Cluster.

Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Restart Innodb MySQL Cluster after Complete outage(All node Down)

-
Image
Restart Innodb MySQL Cluster after Complete outage(All node Down) In DBA World, Their could be activity(like DC Migration) where all node of an MySQL Innodb cluster need to brought down. In MySQL Innodb cluster, We need to follow below steps in order to bring up cluster. 1) Start all node in the MySQL Innodb cluster. systemctl start myql 2) Try to connect cluster using mysqlsh and check status. 3) Strat cluster using complete outage command. Restart Cluster using below command: var cluster = dba.rebootClusterFromCompleteOutage(); root@mysqlhost01:~# mysqlsh --uri clusteradmin@mysqlhost01:3306 Please provide the password for 'clusteradmin@mysqlhost01:3306': ************************ Save password for 'clusteradmin@mysqlhost01:3306'? [Y]es/[N]o/Ne[v]er (default No): MySQL Shell 8.0.17 Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of...
Read more

Oracle Block Corruption - Detection and Resolution

-
Oracle Block Corruption - Detection and Resolution Any kind of corruption in database can  lead to stand still operation of the organization and can cause sever impact to organisation. In this blog , We are going to discuss about Block Corruption of the oracle database. Block Corruption :  Oracle Block Corruption occurs due inconsistent block Header ,Footer ,Checksum or Structure. Depending on the kind of insistency ,We can divide corruption into Physical and Logical corruption. Physical Corruption is due to Bad header, header or footer mismatch ,Invalid Block checksum & misplaced block. Usually this kind of the block corruption are due to external reason and difficult to resolve by oracle utilities alone.  Logical Corruption is due to corrupt or invalid Block structure when block checksum is valid. This kind of the Corruption can be resolved by the Oracle Utilities. Detection of Block Corruption Whenever a corrupt block is read Oracle reports an error in the alert l...
Read more

Add or Remove a node in MySQL Innodb Cluster

-
Add or Remove a node in MySQL Innodb Cluster In any database Cluster Solution ,it's regular activity to add or remove node from the cluster. In this blog , We will discuss about addition and removal of the cluster node from the MySQL Innodb cluster. Remove a node from the MySQL Innodb cluster . 1) Connect to primary node and check status. mysqlsh --uri clusteradmin@mysqlhost01:3306 MySQL  mysqlhost01:3306 ssl  JS > cluster.status() {     "clusterName": "mysqlcluster",     "defaultReplicaSet": {         "name": "default",         "primary": "mysqlhost01:3306",         "ssl": "REQUIRED",         "status": "OK",         "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",         "topology": {     ...
Read more