Wednesday, November 3, 2021

Oracle Exadata x8m and the PMEMCache and PMEMLog

Oracle Exadata x8m provides Optane persistent memory (PMEM) and in 2 ways PMEMCache and PMEMLog which is configured automatically with installation of the Exadata Software.  Keep in mind that to take full advantages of the PMEMCache and PMEMLog you will need to be running Oracle Database 19c and above using and Exadata x8m with RoCE network.   For databases below 19c blocks even with RoCE network Exadata will acesss the persistent memory via the pre-existing Exadata I/O path to the storage cells which can provide some improvement, but maximum performance advantage is 19c and above databases with Exadata and R0CE will using the Remote Direct Memory Access (RDMA) to access the persistent memory on the storage cells.  The good news is there is no additional configuration, application changes other action you will need to take to get the advantages of the new PMEMCache and PMEMLog on the Exadata.

 

For persistent memory the data access speed is slower than DRAM and faster than SSD.  As a way of comparison (this is approximate and meant to illustrate the large gains this may vary some) DRAM is about 80-100 nanoseconds, Flash SSD storage around 200 microsecond and spinning hard disk is between 1-9 milliseconds, while persistent memory access times sits at about 300 nanoseconds. This shows persistent memory is about 3x slower than DRAM, but is 600x faster than Flash SSD and 3,000x faster than spinning hard disk.  This will allow Exadata to take advantage in some cases of faster than flash disk speed improving performance by having a layer after DRAM and before the Flash Cache.

 

The persistently memory is automatically replicated across all storage servers, this added multi-path access to all data in the persistently memory, but also adds a large layer of resiliency.  The persistent memory also is only accessible via Oracle databases therefore using database access controls as using persistent memory via OS or local access is not possible.  By only allowing the database to access the PMEM you can be ensured that the data is secure as database controls the access and will maintenance the consistently and access control to the data.  Exadata hardware monitoring and fault management is performed via ILOM and includes persistent memory hardware modules.  When the time comes to remove the Exadata or reinstall storage servers secure erase will automatically run on persistent memory modules therefore ensuring that no data remains when deinstall or reinstall is done.

 

The persistent memory (PMEM) PMEMCache adds a storage tier of between DRAM (local server memory) and Flash (flashcache). The Exadata X8M adds 1.5 TB of persistent memory to High Capacity and Extreme Flash Storage Servers. The Persistent memory enables reads to happen at near local memory speed, and ensures writes survive any power failures that may occur and can be accessed via an 19c database from all database servers in the Exadata rack.

 

The Exadata X8M Storage Servers transparently manage the persistent memory in front of flash memory.  The Exadata Database Servers running Oracle Database 19c or above accesses the Optane persistent memory (PMEM) directly in the Exadata Storage Servers which is made possible by the converged Ethernet (RoCE) switches with 100g capability and bypasses the network, storage controller, IO software, interrupts, and context switches which delivers ≤ 19µs latency and as much as 16 million 8K IOPS per rack.  Many database functions and all storage functions are handled by the Exadata Storage Servers freeing up the resource on the Exadata Database Servers improving performance.

 

Database Server (Database 19c or Above)

    |                                         |

PMEM  -> Really Hot            |

                            |                 |

                           FLASH  -> Hot

    |                       |

 DISK      -> Colder

 

In Oracle Database 19c the database can put the redo log directly on the persistent memory (PMEM) PMEMLog  on multiple storage servers using RDMA via the converged Ethernet (RoCE) 100g network.  Keep in mind that is not the database’s entire redo log, it only contains the recently written records.  Since the database uses RDMA for writing the redo logs the redo log writes are up to an 8x faster. Since the redo log is going to PMEM and PMEM is duplicated on multiple storage servers, it provides resilience for the redo.  So, for example if your database seems high log file sync waits at times this could help with that issue.  When on Exadata x8m and Oracle database 19c it is not recommended to have storage cells in write-back mode due to the use of the PMEMLog and would write to both when in write-back mode.

 

The Oracle Database 19c and above has AWR statistics from various Exadata storage server components which includes the persistent memory (PMEM) for both the PMEMCache and PMEMLog in addition to Smart I/O, Smart Flash Cache, Smart Flash Log, PMEM Cache. It includes I/O statistics from both the operating system and the cell server software, and it will perform outlier analysis using the I/O statistics. Statistics from PMEM cache  different because the database issues RDMA I/O directly to PMEM cache and does not go through cellsrv, so the storage server does not see the IOs via RDMA via R0CE therefore there are no cell metrics for PMEM cache I/O. Instead, Oracle Database statistics account for the I/O that is performed using RDMA. 

 

** AWR Report Examples are from Oracle documentation found here:

https://docs.oracle.com/en/engineered-systems/exadata-database-machine/sagug/exadata-storage-server-monitoring.html#GUID-0A5BBB1E-1C0D-4D6A-BFF9-AD2931B913CB

 

The AWR Report will have a section the shows the PMEM configuration the example show with write through.

 


The AWR Report will report on PEME Cache space usage as a summary as well as detail per storage cell.


The section on the PMEM Cache Internal Writes are from the RDMA writes made to the PMEM that are populating the PMEM Cache.


We can also see PMEM information from each storage cells using the cellcli command line utility for Example:

 


CellCLI> LIST METRICDEFINITION ATTRIBUTES NAME,DESCRIPTION  WHERE OBJECTTYPE = "PMEMCACHE";

         PC_BY_ALLOCATED        "Number of megabytes allocated in PMEM cache"

 

CellCLI> list metriccurrent where name = 'PC_BY_ALLOCATED' ;

         PC_BY_ALLOCATED         PMEMCACHE       1,541,436 MB

 

 

CellCLI> list metriccurrent where name = 'DB_PC_BY_ALLOCATED' ;

         DB_PC_BY_ALLOCATED      ASM                    0.000 MB

         DB_PC_BY_ALLOCATED      DT4DB1               802,271 MB

         DB_PC_BY_ALLOCATED      DT4DB2                14,096 MB

         DB_PC_BY_ALLOCATED      DT4DB3               141,912 MB

         DB_PC_BY_ALLOCATED      DT4DB4               154,608 MB

         DB_PC_BY_ALLOCATED      DT4DB5               426,958 MB

         DB_PC_BY_ALLOCATED      DT4DB6                 1,506 MB

         DB_PC_BY_ALLOCATED      _OTHER_DATABASE_      76.125 MB

 

CellCLI> list metriccurrent where name like '.*PC.*';

         DB_PC_BY_ALLOCATED      ASM                     0.000 MB

         DB_PC_BY_ALLOCATED      DT4ARIES                802,465 MB

         DB_PC_BY_ALLOCATED      DT4ETLSTG               14,114 MB

         DB_PC_BY_ALLOCATED      DT4MPI                  141,867 MB

Wednesday, January 6, 2021

View Transactions from MySQL Binlogs to Resolve Replication Issues

Summary

Sometimes Replication abends need investigation and the first step is to determine the transaction that is failing.  So we can determine the transaction based on logfile and position from the replication abend is the most common reason I go after transactions from the bin logs so I know what we need to do to fix.

Replication Abend Message

2017-03-20T14:16:10.383779Z 287072 [ERROR] Slave SQL for channel 'group_replication_recovery': Worker 0 failed executing

transaction '57671649-2432-4b1a-af66-74f16f43c510:1588295' at master log mynode-bin.000387, end_log_pos 33127552;

Could not execute Delete_rows event on table myschema.tablename; Can't find record in 'tablename',

Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos 33127552, Error_code: 1032

 

In the example message we can determine the log file name and position

mynode-bin.000387

33127552

Mysql Binlog Utility Options

--base64-output=DECODE-ROWS

Required for the ROW based replication format

 

--verbose

Required to verbose the output in a readable text format

 

MySQL Bin log to a text file to be able to search for records

Example:

mysqlbinlog --base64-output=DECODE-ROWS --verbose  mynode-bin.000387 > mynode.binlog.00387.txt

 

 

 

Search the binlog text file for Records

Edit the text file created in the previous and then search for the log positon

 

vi mynode.binlog.00387.txt

/33127552

 

#170318  2:42:06 server id 101  end_log_pos 33127552    Delete_rows: table id 595 flags: STMT_END_F

### DELETE FROM `myschema`.`tablename`

### WHERE

###   @1='05d7490c-0971-408f-82f5-d5024922dcd7'

###   @2='received.x12.005010x222a1'

###   @3=526

###   @4=9223372036854775807

###   @5='claims/claims'

###   @6=1489819270

### DELETE FROM `myschema`.`tablename`

### WHERE

###   @1='05d7490c-0971-408f-82f5-d5024922dcd7'

###   @2='received.x12.005010x223a2'

###   @3=1

###   @4=9223372036854775807

###   @5='claims/claims'

###   @6=1489819270

### DELETE FROM `myschema`.`tablename`

### WHERE

###   @1='087f46ce-c8a2-490b-89c7-a4dd77584070'

###   @2='processed'

###   @3=24

###   @4=9223372036854775807

###   @5='claims/claims'

###   @6=1489819270

 

** note in this case 3 delete statements, if you just skipped the position in replication you would skip all 3 delete statements probably no exactly what you would want to do.

 

Resolve the Replication Issue

Based on key value of table find which of the 3 deletes is an issue.

 

After that we can insert a record into the table that would satisfy the delete so that replication can successfully remove the dummy record and continue.


Tuesday, January 5, 2021

Group Replication will not rejoin group due to connection issue

 

Summary

Group replication left as a member of the group and will not rejoin.

Track down reason why it will not rejoin group, example here is it will not reconnect due to a credential issue.

 

1.       Check what error is connection issues is happening trying to rejoin the group in the local database

 

select * from performance_schema.replication_connection_status ;

 

Example:

| group_replication_recovery |                                      |                                      |      NULL | OFF           |                         0 | 0000-00-00 00:00:00.000000 |                                                                                                                                                                                                                      |             13117 | Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated. | 2021-01-05 08:38:15.014830 |                         | 0000-00-00 00:00:00.000000                        | 0000-00-00 00:00:00.000000                         | 0000-00-00 00:00:00.000000                    | 0000-00-00 00:00:00.000000                  |                      | 0000-00-00 00:00:00.000000                     | 0000-00-00 00:00:00.000000                      | 0000-00-00 00:00:00.000000                 |

** In this case it is stating that empty username is being used attempting to connect in.

 

2.       check the channel definition

 

select channel_name, host, port, user, network_interface, auto_position from performance_schema.replication_connection_configuration ;

 

Example:

+----------------------------+--------------------------+------+----------+-------------------+---------------+

| channel_name               | host                     | port | user     | network_interface | auto_position |

+----------------------------+--------------------------+------+----------+-------------------+---------------+

|                            | aglqapmyp01              | 3306 | rpl_user |                   | 0             |

| group_replication_applier  | <NULL>                   |    0 |          |                   | 1             |

| group_replication_recovery | aglqapmyp05.xxxxxxxx.xxx | 3306 |          |                   | 1             |

+----------------------------+--------------------------+------+----------+-------------------+---------------+

3 rows in set (0.00 sec)

 

** Note in this case the user column is missing

 

 

3.       Since it appears that the credentials are missing for the channel we can reset them and give a username and password for the channel.

 

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='xxxxxxx' FOR CHANNEL 'group_replication_recovery';

 

 

4.       Recheck that our change for the credential took affect

 

select channel_name, host, port, user, network_interface, auto_position from performance_schema.replication_connection_configuration ;

 

Example:

+----------------------------+--------------------------+------+----------+-------------------+---------------+

| channel_name               | host                     | port | user     | network_interface | auto_position |

+----------------------------+--------------------------+------+----------+-------------------+---------------+

|                            | aglqapmyp01              | 3306 | rpl_user |                   | 0             |

| group_replication_applier  | <NULL>                   |    0 |          |                   | 1             |

| group_replication_recovery | aglqapmyp05.xxxxxxxx.xxx | 3306 | rpl_user |                   | 1             |

+----------------------------+--------------------------+------+----------+-------------------+---------------+

3 rows in set (0.00 sec)

 

 

 

 

5.       Restart database to restart group replication, a good way to do that is restart the database with the mysql group replication start on boot on, this way we are completely clean.

 

Example:

/etc/rc.d/init.d/mysql.server stop

/etc/rc.d/init.d/mysql.server start

 

 

6.       Check group replication status

 

SELECT MEMBER_HOST,MEMBER_STATE,MEMBER_ROLE,MEMBER_VERSION FROM performance_schema.replication_group_members order by MEMBER_HOST;

 

Example:

+--------------------------+--------------+-------------+----------------+

| MEMBER_HOST              | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |

+--------------------------+--------------+-------------+----------------+

| aglqapmyp04.xxxxxxxx.xxx | ONLINE       | SECONDARY   | 8.0.22         |

| aglqapmyp05.xxxxxxxx.xxx | ONLINE       | PRIMARY     | 8.0.22         |

| aglqapmyp06.xxxxxxxx.xxx | ONLINE       | SECONDARY   | 8.0.22         |

+--------------------------+--------------+-------------+----------------+

Monday, June 22, 2020

Restore Single Table from MySQL Dump


Summary

There are times when you need to restore a table and what you have is a MySQL dump of the entire database.  We want to be able to get a single table restored without having to restore the entire database.  Here we will cover a way to do that.

Process

1.       Get your Full MySQL Dump in a location that we can worth with that has the space we need to be able to unzip and work with the file without filling up the space.
2.       Make sure the mysql dump is not zipped/compressed if so, make sure it is uncompressed as we need it in the text format.

3.       Determine the table(s) you want to restore that exist in the mysql dump backup that you have.  Keep in mind that tables can have dependencies for foreign keys and when restoring tables to prior points in time you may need to actually restore table dependencies.

4.       First Lets extract the table(s) we want from the mysql dump to restore from the full dump into files that are just the tables.  On a Linux/unix system we can use sed to accomplish this.

Example:
sed -n -e '/CREATE TABLE.*``/,/CREATE TABLE/p'

For Example we want the cr_pads and the projects tables from the mysql dump
sed -n -e '/CREATE TABLE.*`cr_pads`/,/CREATE TABLE/p' MySQLDB_06-03-2020.sql > c_pads_06_03_2020.dump

sed -n -e '/CREATE TABLE.*`projects`/,/CREATE TABLE/p' MySQLDB_06-03-2020.sql > projects_06_03_2020.dump


5.       Now that we have the tables we want to restore out into separate dump files we need to check the file at the end for any drop/create for the next table in the dump file as our sed command extracted everything between the create table for the table we want to restore and the next create table in the file.

tail -100

** Find out how many lines are at the end of the file that we do not need, look for drop table, create table those for sure we need to remove.


6.       If the dumpfile contains drop table and create table of next table we need to remove those from the dump file.  We can do this with vi or use head command to remove the last so many lines

Example:
head --lines=-{number of lines to remove} {dumpfile} > new_dumpfile

Real Example:
head --lines=-5 projects_06_03_2020.dump > projects_06_03_2020.sql
head --lines=-5 cr_pads_06_03_2020.dump > cr_pads_06_03_2020.sql


7.       Now that we have actual table extracted we are ready to replace our table or load table into another temporary database to work with.

Example to a new temporary database:
mysql -u root -p
create databse mytemp
use mytemp
source projects_06_03_2020.sql
source cr_pads_06_03_2020.sql


To replace table in existing database, keep in mind existing state of table will be destroyed:
mysql -u root -p
use mydatabase
source projects_06_03_2020.sql
source cr_pads_06_03_2020.sql


8.       Now you have the copy of the table(s) back in the databases as of the last mysql dump.

Tuesday, May 5, 2020

Change Oracle Group UUID for ORACLE_HOME

** NOTE: This procedure while has been tested on on an Oracle Linux server and has shown to allow the change of the UUID for the HOME
This is meant to help you in a situation where a UUID mismatch has happened and correct without having to reinstall.  Keep in mind while
it has been tested and used and has worked this is not assured to work for you, but may so shown here in case it can help you.

1. copy the permission.pl script found in Appendix A of this document into the root home location on each dbatabase node in the cluster.

scp permission.pl root@nodename


2. Log into each of the database nodes as root

ssh root@nodename


3. On each database node disable crs and shutdown all database and grid infrastructure components as well as any processes being run by oracle

. oraenv
+ASM1
crsctl disable crs
crsctl stop crs


4. On each node in the cluster capture the u01 permissions and ownership prior to change of UUID using permission.pl script process as root execute for the base location for oracle installations in our example we will use /u01
** Note: Your environment may not use /u01 and may have installs in multiple places in those cases run for each location.

perl permission.pl /u01
** See Script in Appendiz A of this document

script will create a script that ends in .cmd
example: restore-perm-Fri-Mar-13-20-27-33-2020.cmd


5. Set permission on this file for execution

Example: chmod 770 restore-perm-Fri-Mar-13-20-27-33-2020.cmd


6. Change the oracle owner and or group UUIDs in /etc/passwd and/or /etc/group


7. Check that the change has taken affect


8. Run the script process create by the permission.pl execution in prior step, this will restore the permissions and ownership for Oracle binaries

Example: ./restore-perm-Fri-Mar-13-20-27-33-2020.cmd


9. Post u01 restore permissons deal with ownership and permissions outside u01
** This uses 18.8.0.0 binaries and bianry locations you will need to adjust for your database and grid home locations.

cd /home
chown -R oracle:oinstall oracle
cd /u01/app/oracle/Oeda/Software/Patches
chown -R oracle:oinstall *
cd /u01/app/oracle/product/18.0.0.0/dbhome_1/.patch_storage
chown -R oracle:oinstall *
cd /u01/app/18.0.0.0/grid/.patch_storage
chown -R oracle:oinstall *
cd /var/tmp
chown -R oracle:oinstall GridSetupActions*
chown -R oracle:oinstall InstallActions*
chown -R oracle:oinstall hsperfdata_oracle
chown -R oracle:oinstall CVU_18.0.0.0.0_resource
chown -R oracle:oinstall CVU_18.0.0.0.0_oracle
cd /etc
chown oracle:oinstall oratab
chgrp oinstall oracle
cd /etc/oracle/scls_scr/agoprdd4dbadm*/oracle/ # Adjust for the node name of each node or wild card for Rack Group
chown oracle cssfatal
cd /etc/oracle/scls_scr/agoprdd4dbadm*/ # Adjust for the node name of each node or wild card for Rack Group
chown oracle oracle
cd /etc/oracle/scls_scr/agoprdd4dbadm0*/root # Adjust for the node name of each node or wild card for Rack Group
chgrp oinstall ohasdrun
chgrp oinstall ohasdstr
cd /etc/oracle/scls_scr/
chgrp oinstall agoprdd4dbadm* # Adjust for the node name of each node or wild card for Rack Group
cd /etc/oracle/
chgrp oinstall scls_scr
chgrp oinstall oprocd
chgrp oinstall olr.loc
chgrp oinstall ocr.loc
chgrp oinstall lastgasp
chgrp oinstall maps
chgrp oinstall setasmgid
cd /etc/oracle/oprocd
chgrp oinstall *
cd /etc/oracle/maps
chown oracle:oinstall *
cd /var/tmp
chgrp oinstall .oracle
cd /var/tmp/.oracle
rm -f *


10. Now that permissions are reset we can restart the cluster and check

. oraenv
+ASM1
crsctl start crs
ps -ef | grep grid
ps -ef | grep smon
crsctl check crs


Appedix A: permissions.pl
** Script was obtain from Oracle.

#!/usr/bin/perl -w
#
# Captures file permissions and the owner of the files
# useage : perm1.pl
#
# MODIFIED
#     uaswatha   12th March 2018  address filename with spaces (request from customer)

use strict;
use warnings;
use File::Find;
use POSIX();

my (@dir) = @ARGV;
my $linecount=0 ;

#print @ARGV, $#ARGV;

if ($#ARGV < 0) {
   print "\n\nOpps....Invalid Syntax !!!!\n" ;
   print "Usage   : ./perm1.pl \n\n" ;
   print "Example : ./perm1.pl /home/oralce\n\n" ;
   exit ;
}
my $logdir=$dir[0] ;
#my ($sec, $min, $hr, $day, $mon, $year) = localtime;
##my ($dow,$mon,$date,$hr,$min,$sec,$year) = POSIX::strftime( '%a %b %d %H %M %S %Y', localtime);
my $date = POSIX::strftime( '%a-%b-%d-%H-%M-%S-%Y', localtime);
my $logfile="permission-".$date;
my $cmdfile="restore-perm-".$date.".cmd" ;

open LOGFILE, "> $logfile" or die $! ;
open CMDFILE, "> $cmdfile" or die $! ;
find(\&process_file,@dir);

print "Following log files are generated\n" ;
print "logfile      : ".$logfile. "\n" ;
print "Command file : ".$cmdfile. "\n" ;
print "Linecount : ".$linecount."\n" ;
close (LOGFILE) ;
close (CMDFILE) ;


sub process_file {
    my ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size, $atime,$mtime,$ctime,$blksize,$blocks,$username,$user,$pass,$comment,$home,$shell,
$group);
    my %uiduname = () ;
    my %gidgname = () ;
    my $filename = $File::Find::name;


#### Building uid, username hash

    open (PASSWDFILE, '/etc/passwd') ;

    while ( ) {
       ($user,$pass,$uid,$gid,$comment,$home,$shell)=split (/:/) ;
       $uiduname{$uid}=$user ;
    }
    close (PASSWDFILE) ;


#### Building gid, groupname hash

    open (GRPFILE, '/etc/group') ;

    while ( ) {
       ($group,$pass,$gid)=split (/:/) ;
       $gidgname{$gid}=$group ;
    }
    close (GRPFILE) ;

    ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size, $atime,$mtime,$ctime,$blksize,$blocks) = stat("$filename");
#    printf  "%o %s %s %s\n", $mode & 07777, $uiduname{$uid}, $gidgname{$gid}, $filename ;
     printf LOGFILE "%o %s %s %s\n", $mode & 07777, $uiduname{$uid}, $gidgname{$gid}, "\"$filename\"" ;
     printf CMDFILE "%s %s%s%s %s\n", "chown ",$uiduname{$uid}, ":", $gidgname{$gid}, "\"$filename\"" ;
     printf CMDFILE "%s %o %s\n", "chmod ",$mode & 07777, "\"$filename\"" ;
        #    printf  "%o %s %s %s\n", $mode & 07777, $uiduname{$uid}, $gidgname{$gid}, \",$filename,\" ;
    $linecount++ ;
}

Update to New 18c Patch Level using New ORACLE_HOME

** Procedure Assumes new ORACLE_HOME at updated patch level.

1. shutdown any golden gate processes when Golden Gate in Use.


2. for each database that is to go to 18 Patch level and new ORACLE_HOME
. oraenv

srvctl stop database -d  


3. modify the /etc/oratab for the database instances to goto new ORACLE_HOME
Example:
/u01/app/oracle/product/18.0.0.0/dbhome_1


4. move the glogin.sql out of the way for the changes, when the glogin.sql is utilized.
mv $ORACLE_HOME/sqlplus/admin/glogin.sql $ORACLE_HOME/sqlplus/admin/glogin.sql.save


5. for each database from one node run the datapatch this will set to new ORACLE_HOME at new level setup
   Execute this on the same node where original datapatch was execute for update originally.
. oraenv

srvctl stop database -d  
cd $ORACLE_HOME/OPatch
sqlplus / as sysdba
startup nomount
alter system set cluster_database=false scope=spfile;
shutdown immediate
startup upgrade
exit
./datapatch -verbose
sqlplus / as sysdba
alter system set cluster_database=true scope=spfile;
shutdown immediate
exit


6. Change the ORACLE_HOME for the cluster config for each database
. oraenv

srvctl modify database -d -oraclehome /u01/app/oracle/product/18.0.0.0/dbhome_1


7. Restart database with new home via cluster config
srvctl start database -d


8. put the glogin back in place when glogin.sql is used.
mv $ORACLE_HOME/sqlplus/admin/glogin.sql.save $ORACLE_HOME/sqlplus/admin/glogin.sql


9. Update Golden Gate prm files for new ORACLE_HOME


10. Restart Golden Gate Processes
cd /ggs
./ggsci
start mgr
start jagent
start *

Rollback 18 Patch back to Prior Patch Level with Change ORACLE_HOME

** Procedure assumes ORACLE_HOME rolling back to is already in place at patch level you want to rollback.

1. Stop any golden gate processes when golden gate in use for database affected.


2. for each database go back to prior 18c Patch Level with different ORACLE_HOME
. oraenv

srvctl stop database -d  


3. modify the /etc/oratab for the database instances to goto new ORACLE_HOME
Example:
/u01/app/oracle/product/18.0.0.0/dbhome_2


4. move the glogin.sql out of the way for the changes, this would be for the 18 home in this case when used
mv $ORACLE_HOME/sqlplus/admin/glogin.sql $ORACLE_HOME/sqlplus/admin/glogin.sql.save



5. for each database from one node run the datapatch this will set back to prior 18 patch level
   Execute this on the same node where original datapatch was execute for update originally.
. oraenv

srvctl stop database -d  
cd $ORACLE_HOME/OPatch
sqlplus / as sysdba
startup nomount
alter system set cluster_database=false scope=spfile;
shutdown immediate
startup upgrade
exit
./datapatch -verbose
sqlplus / as sysdba
alter system set cluster_database=true scope=spfile;
shutdown immediate
exit


6. Change the ORACLE_HOME for the cluster config for each database
. oraenv

srvctl modify database -d -oraclehome /u01/app/oracle/product/18.0.0.0/dbhome_2


7. Restart database with new home via cluster config
srvctl start database -d


8. put the glogin back in place when used
mv $ORACLE_HOME/sqlplus/admin/glogin.sql.save $ORACLE_HOME/sqlplus/admin/glogin.sql


9. Update Golden Gate prm files for new ORACLE_HOME


10. Restart Golden Gate Processes
cd /ggs
./ggsci
start mgr
start jagent
start *