Clever Geek Handbook

Oracle Replication and UCP Fast Connection Failover



Sometimes the Java application configuration contains the "Primary" IP address of the database server, which can change, for example, in the following cases:



  • Controlled change of database roles. "Primary" becomes "Standby" and vice versa, "Standby" becomes "Primary". This procedure is usually called "Switchover".
  • Emergency change of "Standby" role to "Primary". This is commonly referred to as "Failover".


In both cases, the application must not only "know" about the IP address of the new "Primary" server, but also be able to access it when needed. The following is a quick guide on how to do this using the Oracle Universal Connection Pool (UCP), as well as a "Switchover" demo.



For the experiment, we will use:



  • MacBook with 16 GB RAM (more than 8 GB required for the experiment)
  • Virtual Box version 6.1.12
  • 2x virtual machines (hereinafter VM) with  CentOS 7 Minimal , each of which has

    • 2x vCPU
    • 2048 GB RAM (up to 8 GB temporarily, one at a time)
    • 40 GB HDD
    • disabled audio to avoid 100% CPU load
  • Oracle Database 19c


Let's follow these steps:



  1. Configuring virtual machines
  2. Installing Oracle
  3. Oracle Replication
  4. Installing and Configuring Oracle Grid
  5. "Switchover" Java








We create virtual machines (hereinafter VMs) with the Linux Red Hat type, start. When Virtual Box starts, it prompts you to select the iso from which to start the VM (in the experiment, CentOS-7-x86_64-Minimal-1908.iso is used). Leave everything by default, reboot, update, install "Virtual Box Guest Additions", turn off firewalld so it doesn't get in the way. "Everyone knows how the polish is cleared", so we only note that after updating VMs, we switch their network interfaces from the NAT adapter to the "virtual host adapter" vboxnet0. For this adapter, which can be created in Virtual Box tools, manually set the address 192.168.56.1/24 and disable DHCP. Basically, this is the IP address of the default gateway for VMs and the address of the Java application. Just for clarity. And if you still need internet on CentOS,then you can enable NAT on macOS:



  1. Switch to root using the 'sudo su -' command.
  2. Allow traffic redirection using the command 'sysctl -w net.inet.ip.forwarding = 1'.
  3. Add the content of the /etc/pf.conf file to the /var/root/pfnat.conf file, in which instead of line 3 we insert the rule for NAT:

    nat on enX from vboxnet0: network to any -> (enX)


    where enX is the name of the network interface with the default route.
  4. Run the command 'pfctl -f pfnat.conf -e' to update the packet filter rules.


Steps 2-4 can be performed in one command. 
sysctl -w net.inet.ip.forwarding=1 \
 && grep -v -E -e '^#.*' -e '^$' /etc/pf.conf | head -n2 > pfnat.conf \
 && INET_PORT=$(netstat -nrf inet | grep default | tr -s ' ' | cut -d ' ' -f 4) \
 && echo "nat on ${INET_PORT} from vboxnet0:network to any -> (${INET_PORT})" >> pfnat.conf \
 && grep -v -E -e '^#.*' -e '^$' /etc/pf.conf | tail -n+3 >> pfnat.conf \
 && pfctl -f pfnat.conf -e




Add uniform entries to / etc / hosts of all VMs so that they can "ping" each other by domain names. 



127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.56.78 oracle1.localdomain oracle1
192.168.56.79 oracle2.localdomain oracle2




Installing Oracle





We perform "Software only" installation on oracle1 and oracle2  using rpm packages , the directory with which can be shared from MacOS via Virtual Box (Machine-> Settings-> Shared Folder). 



yum -y localinstall oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm


yum -y localinstall oracle-database-ee-19c-1.0-1.x86_64.rpm


Next, we create a DBMS instance on VM "oracle1". To do this, run the corresponding script under the oralce user. 



/etc/init.d/oracledb_ORCLCDB-19c configure


This procedure takes some time. After creating an instance on both hosts, add these lines to the /home/oracle/.bash_profile file: 



export ORACLE_HOME="/opt/oracle/product/19c/dbhome_1"
export ORACLE_BASE="/opt/oracle"
export ORACLE_SID="ORCLCDB"
export PATH=$PATH:$ORACLE_HOME/bin


Well, we also configure ssh for convenience, so that you can immediately connect as the oracle user. We connect to the host via ssh and connect to the database under the oracle user. 



user@macbook:~$ ssh oracle@oracle1
Last login: Wed Aug 12 16:17:05 2020
[oracle@oracle1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 12 16:19:44 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>


Actually, Oracle. For the experiment, we also need to set up replication.



Oracle Replication.







To configure replication, we will use a slightly updated instruction :



  1. We transfer the database on the oracle1 server to "Archive Mode". To do this, execute the commands in sqlplus: 



    SHUTDOWN IMMEDIATE;


    STARTUP MOUNT;


    ALTER DATABASE ARCHIVELOG;


    ALTER DATABASE OPEN;
  2. Without leaving sqlplus, enable "Force logging" on the oracle1 server. 



    ALTER DATABASE FORCE LOGGING;


    ALTER SYSTEM SWITCH LOGFILE;
  3. Create "redo log" files on the oracle1 server. 

    ALTER DATABASE
      ADD STANDBY LOGFILE
      THREAD 1 GROUP 10 ('/opt/oracle/oradata/ORCLCDB/standby_redo01.log')
      SIZE 209715200;


    ALTER DATABASE
      ADD STANDBY LOGFILE
      THREAD 1 GROUP 11 ('/opt/oracle/oradata/ORCLCDB/standby_redo02.log')
      SIZE 209715200;


    ALTER DATABASE
      ADD STANDBY LOGFILE
      THREAD 1 GROUP 12 ('/opt/oracle/oradata/ORCLCDB/standby_redo03.log')
      SIZE 209715200;


    ALTER DATABASE
      ADD STANDBY LOGFILE
      THREAD 1 GROUP 13 ('/opt/oracle/oradata/ORCLCDB/standby_redo04.log')
      SIZE 209715200;
  4. Turn on "FLASHBACK" on the oracle1 server, it won't work without it.

    SQL> host

    [oracle @ oracle1 ~] $ mkdir / opt / oracle / recovery_area

    [oracle @ oracle1 ~] $ exit

    SQL> alter system set db_recovery_file_dest_size = 2g scope = both;

    SQL> alter system set db_recovery_file_dest = '/ opt / oracle / recovery_area' scope = both;

    SQL> ALTER DATABASE FLASHBACK ON;
  5. We enable something automatic on the oracle1 server.

    SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
  6. tnsnames.ora listener.ora oracle1 oracle2 :

    oracle1, $ORACLE_HOME/network/admin/tnsnames.ora 
    
LISTENER_ORCLCDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1.localdomain)(PORT = 1521))

ORCLCDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ORCLCDB)
    )
  )

ORCLCDB_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle2.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ORCLCDB)
    )
  )




    oracle1, $ORACLE_HOME/network/admin/listener.ora 
    
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLCDB_DGMGRL)
      (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
      (SID_NAME = ORCLCDB)
    )
  )

ADR_BASE_LISTENER = /opt/oracle




    oracle2, $ORACLE_HOME/network/admin/tnsnames.ora 
    
LISTENER_ORCLCDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracle2.localdomain)(PORT = 1521))

ORCLCDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ORCLCDB)
    )
  )

ORCLCDB_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle2.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ORCLCDB)
    )
  )




    oracle2, $ORACLE_HOME/network/admin/listener.ora 
    
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle2.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLCDB_STBY_DGMGRL)
      (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
      (SID_NAME = ORCLCDB)
    )
  )

ADR_BASE_LISTENER = /opt/oracle


  7. oracle1 listener-.

    [oracle@oracle1 ~]$ lsnrctl reload


    [oracle@oracle1 ~]$ lsnrctl status



    LSNRCTL for Linux: Version 19.0.0.0.0 — Production on 15-AUG-2020 08:17:24



    Copyright © 1991, 2019, Oracle. All rights reserved.



    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle1.localdomain)(PORT=1521)))

    STATUS of the LISTENER

    — Alias LISTENER

    Version TNSLSNR for Linux: Version 19.0.0.0.0 — Production

    Start Date 15-AUG-2020 08:09:57

    Uptime 0 days 0 hr. 7 min. 26 sec

    Trace Level off

    Security ON: Local OS Authentication

    SNMP OFF

    Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora

    Listener Log File /opt/oracle/diag/tnslsnr/oracle1/listener/alert/log.xml

    Listening Endpoints Summary…

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1.localdomain)(PORT=1521)))

    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle1.localdomain)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))

    Services Summary…

    Service «ORCLCDB» has 1 instance(s).

    Instance «ORCLCDB», status READY, has 1 handler(s) for this service…

    Service «ORCLCDBXDB» has 1 instance(s).

    Instance «ORCLCDB», status READY, has 1 handler(s) for this service…

    Service «ac8d8d741e3e2a52e0534e38a8c0602d» has 1 instance(s).

    Instance «ORCLCDB», status READY, has 1 handler(s) for this service…

    Service «orclpdb1» has 1 instance(s).

    Instance «ORCLCDB», status READY, has 1 handler(s) for this service…

    The command completed successfully


    ( Data Guard: ORCLCDB_DGMGRL)
    [oracle@oracle1 ~]$ lsnrctl status



    LSNRCTL for Linux: Version 19.0.0.0.0 — Production on 15-AUG-2020 08:17:32



    Copyright © 1991, 2019, Oracle. All rights reserved.



    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle1.localdomain)(PORT=1521)))

    STATUS of the LISTENER

    — Alias LISTENER

    Version TNSLSNR for Linux: Version 19.0.0.0.0 — Production

    Start Date 15-AUG-2020 08:09:57

    Uptime 0 days 0 hr. 7 min. 34 sec

    Trace Level off

    Security ON: Local OS Authentication

    SNMP OFF

    Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora

    Listener Log File /opt/oracle/diag/tnslsnr/oracle1/listener/alert/log.xml

    Listening Endpoints Summary…

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1.localdomain)(PORT=1521)))

    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle1.localdomain)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))

    Services Summary…

    Service «ORCLCDB» has 1 instance(s).

    Instance «ORCLCDB», status READY, has 1 handler(s) for this service…

    Service «ORCLCDBXDB» has 1 instance(s).

    Instance «ORCLCDB», status READY, has 1 handler(s) for this service…

    Service «ORCLCDB_DGMGRL» has 1 instance(s).

    Instance «ORCLCDB», status UNKNOWN, has 1 handler(s) for this service…

    Service «ac8d8d741e3e2a52e0534e38a8c0602d» has 1 instance(s).

    Instance «ORCLCDB», status READY, has 1 handler(s) for this service…

    Service «orclpdb1» has 1 instance(s).

    Instance «ORCLCDB», status READY, has 1 handler(s) for this service…

    The command completed successfully
  8. SYS oracle1.

    SQL> alter user sys identified by "pa_SSw0rd";
  9. oracle2 listener .

    [oracle@oracle2 ~]$ lsnrctl start

    [oracle@oracle2 ~]$ orapwd file=$ORACLE_BASE/product/19c/dbhome_1/dbs/orapwORCLCDB entries=10 password=pa_SSw0rd

    [oracle@oracle2 ~]$ echo "*.db_name='ORCLCDB'" > /tmp/initORCLCDB_STBY.ora

    [oracle@oracle2 ~]$ mkdir -p $ORACLE_BASE/oradata/ORCLCDB/pdbseed

    [oracle@oracle2 ~]$ mkdir -p $ORACLE_BASE/oradata/ORCLCDB/ORCLPDB1

    [oracle@oracle2 ~]$ mkdir -p $ORACLE_BASE/admin/ORCLCDB/adump

    [oracle@oracle2 ~]$ mkdir /opt/oracle/recovery_area

    [oracle@oracle2 ~]$ sqlplus / as sysdba

    SQL> STARTUP NOMOUNT PFILE='/tmp/initORCLCDB_STBY.ora'

    [oracle@oracle2 ~]$ rman TARGET sys/pa_SSw0rd@ORCLCDB AUXILIARY sys/pa_SSw0rd@ORCLCDB_STBY

    RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET db_unique_name = 'ORCLCDB_STBY' COMMENT 'Is standby' NOFILENAMECHECK;
  10. Run Data Guard on both servers (oracle1 and oracle2).

    SQL> ALTER SYSTEM SET dg_broker_start = true;
  11. On the oracle1 server, connect to the Data Guard management console and create a configuration.

    [oracle @ oracle1 ~] $ dgmgrl sys / pa_SSw0rd @ ORCLCDB

    DGMGRL> CREATE CONFIGURATION my_dg_config AS PRIMARY DATABASE IS ORCLCDB CONNECT IDENTIFIER IS ORCLCDB;

    DGMGRL> ADD DATABASE ORCLCDB_STBY AS CONNECT IDENTIFIER IS ORCLCDB_STBY MAINTAINED AS PHYSICAL;

    DGMGRL> enable configuration;




So, as a result of creating a replica and enabling Data Guard, we have the following state: 



DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  orclcdb      - Primary database
    orclcdb_stby - Physical standby database 
      Warning: ORA-16854: apply lag could not be determined

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 40 seconds ago)


This is a bad condition. Let's wait a little ... 



DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  orclcdb      - Primary database
    orclcdb_stby - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 55 seconds ago)


Now the state is good! However, the replica is very passive, it does not accept read requests (OPEN MODE: MOUNTED). 



SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
 
	 2 PDB$SEED			  MOUNTED
	 3 ORCLPDB1			  MOUNTED


Let's make the replica active so that it accepts read requests. Then we, in the future, will be able to unload the server with the "Primary" base. This is what is called "Active Data Guard", or active standby . On the oracle2 server, execute the following sequence of commands in sqlplus: 

alter database 
      recover managed standby database cancel;


alter database open;


alter database 
      recover managed standby database 
      using current logfile 
      disconnect from session;


alter pluggable database all open;


Now you can read from the replica (OPEN MODE: READ ONLY): 

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
 
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB1			  READ ONLY  NO


And in the DataGuard console on the oracle1 server, everything looks good: 

DGMGRL> show configuration;

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  orclcdb      - Primary database
    orclcdb_stby - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 19 seconds ago)


Despite the fact that we now have Data Guard Active, the cluster is still largely passive. He still won't say a word to us and our amazing Java application that Primary is no longer Primary. To do this, another service, ONS (Oracle Notification Services), must be running on the cluster servers. And it looks like installing Oracle Database is not enough to run this service, we will need to install Oracle Grid.







Installing and configuring Oracle Grid.







Everything is quite simple here: as in the process of installing Oracle Database, we will simply follow the official instructions .



  1.   oracle1  oracle2 root    Oracle Grid, gcc-c++, oracle   asm.   Virtual Box, Oracle, Oracle Grid    . 

    mkdir -p /opt/oracle/product/19c/grid \
        && cp -r /mnt/oracle_grid_19300/LINUX/* /opt/oracle/product/19c/grid/ \
        && chown -R oracle:oinstall /opt/oracle/product/19c/grid


    yum install -y gcc-c++


    groupadd asm && usermod -aG asm oracle


  2. ,   oracle, Oracle Grid. 

    cd /opt/oracle/product/19c/grid/ && ./runcluvfy.sh stage -pre hacfg


    Verifying Physical Memory ...FAILED

    Required physical memory = 8GB

    Verifying Swap Size ...FAILED

    Required = 2.6924GB (2823138.0KB); Found = 2GB (2097148.0KB)]



      - ,     swap, Oracle Grid ,   .
  3. oracle2, oracle1.

    SQL> shutdown immediate


    # poweroff
  4. oracle1  8192 ,  VM   swap root. 

    dd if=/dev/zero of=/swap_file bs=1G count=7 \
            && chmod 600 /swap_file && mkswap /swap_file \
            && echo '/swap_file  swap  swap  defaults  0 0' >> /etc/fstab \
            && swapoff -a && swapon -a
  5. , , .

    [oracle@oracle1 grid]$ cd /opt/oracle/product/19c/grid/ && ./runcluvfy.sh stage -pre hacfg

    Pre-check for Oracle Restart configuration was successful.


    ! Oracle Grid.
  6. oracle   oracle1  /opt/oracle/product/19c/grid/ grid_configwizard.rsp. 

    cd /opt/oracle/product/19c/grid/ && touch grid_configwizard.rsp


    grid_configwizard.rsp , oracle restart, ASM   .

    oracle.install.responseFileVersion=/oracle/install/rspfmt_crsinstall_response_schema_v19.0.0

    INVENTORY_LOCATION=/opt/oracle/oraInventory

    oracle.install.option=CRS_SWONLY

    ORACLE_BASE=/opt/oracle

    oracle.install.asm.OSDBA=oinstall

    oracle.install.asm.OSASM=asm

    oracle.install.asm.SYSASMPassword=oracle

    oracle.install.asm.diskGroup.name=data

    oracle.install.asm.diskGroup.redundancy=NORMAL

    oracle.install.asm.diskGroup.AUSize=4

    oracle.install.asm.diskGroup.disksWithFailureGroupNames=/dev/sdb

  7.   oracle grid   . 

    ./gridSetup.sh -silent \
               -responseFile /opt/oracle/product/19c/grid/grid_configwizard.rsp
  8.   , oracle grid root,   . 

    /opt/oracle/product/19c/grid/root.sh
  9. , oracle restart root roothas.sh. 

    cd /opt/oracle/product/19c/grid/crs/install/ && ./roothas.sh


  10.   oracle   runInstaller. 

    cd /opt/oracle/product/19c/grid/oui/bin/ \
                            && ./runInstaller -updateNodeList \
                                ORACLE_HOME=/opt/oracle/product/19c/grid \
                                -defaultHomeName CLUSTER_NODES= CRS=TRUE
  11. oracle1 /etc/fstab , /swap_file, 2048 RAM.
  12. " Oracle Grid" VM oracle2.
  13. ,   ,   Oracle Restart.    oracle1 oracle     Oracle Restart. 

    srvctl add database -db ORCLCDB \
            -oraclehome /opt/oracle/product/19c/dbhome_1 \
            -role PRIMARY


    /opt/oracle/product/19c/grid/bin/crsctl modify \
            res ora.cssd -attr "AUTO_START=always" -unsupported


    /opt/oracle/product/19c/grid/bin/crsctl stop has


    /opt/oracle/product/19c/grid/bin/crsctl start has
  14.   oracle2 oracle     Oracle Restart. 

    /opt/oracle/product/19c/grid/bin/crsctl modify \
            res ora.cssd -attr "AUTO_START=always" -unsupported


    /opt/oracle/product/19c/grid/bin/crsctl stop has


    /opt/oracle/product/19c/grid/bin/crsctl start has


    srvctl add database -db ORCLCDB_STBY \
           -oraclehome /opt/oracle/product/19c/dbhome_1 \
           -role PHYSICAL_STANDBY \
           -spfile /opt/oracle/product/19c/dbhome_1/dbs/spfileORCLCDB.ora \
           -dbname ORCLCDB -instance ORCLCDB
  15. On both servers, oracle1 and oracle2, under the oracle user add a listener to the Oracle Restart configuration. 

    srvctl add listener
  16. On the oracle1 server, under the oracle user, add to the configuration and start the database service, ORCLCDB. 

    srvctl add service -db ORCLCDB -service orclpdb -l PRIMARY -pdb ORCLPDB1


    srvctl start service -db ORCLCDB -service orclpdb
  17. On the oracle2 server, under the oracle user, add the database service, ORCLCDB_STBY to the configuration, and start the database instance 

    srvctl add service -db ORCLCDB_STBY -service orclpdb -l PRIMARY -pdb ORCLPDB1


    srvctl start database -db ORCLCDB_STBY
  18. On the oracle1 and oracle2 server, start the ons service. 

    srvctl enable ons


    srvctl start ons




Demo "Switchover" on a Java test application







As a result, we have 2 oracle servers with replication in active-standby mode and an ons service to which switch events will be sent, and the Java application will be able to process these events as they arrive.



Create a test user and table in Oracle.
  oracle1 oracle   sqlplus   

  • sqlplus / as sysdba
  • alter session set container=ORCLPDB1;
  • CREATE USER testus 
       IDENTIFIED BY test 
       DEFAULT TABLESPACE USERS 
       QUOTA UNLIMITED ON USERS;
  • GRANT CONNECT, 
      CREATE SESSION, 
      CREATE TABLE 
      TO testus;
  • CREATE TABLE TESTUS.MESSAGES (TIMEDATE TIMESTAMP, MESSAGE VARCHAR2(100));




After creating a test user and table, we connect to the oracle2 server and "open" the instance for reading to make sure that replication is working. 

 
 [oracle@oracle2 ~]$ sqlplus / as sysdba
 SQL> alter pluggable database all open;
 SQL> alter session set container=ORCLPDB1;
 SQL> column table_name format A10;
 SQL> column owner format A10;
 SQL> select table_name, owner from dba_tables where owner like '%TEST%';

TABLE_NAME OWNER
---------- ----------
MESSAGES   TESTUS


The test application consists of only one class Main, in a loop it tries to add a record to the test table, and then read the same record (see the "putNewMessage ()" and "getLastMessage ()" methods). There is also the "getConnectionHost ()" method, which from the "connection" object using the "Reflection API" gets the IP address of the connection to the database. As you can see in the console, after "switchover" this address changes.



Launch the test application and execute the "switchover" in the Data Guard console. 



DGMGRL> switchover to orclcdb_stby;


We see how the connection IP address changes in the application log, the downtime is 1 minute: 



[Wed Aug 26 23:56:55 MSK 2020]: Host 192.168.56.78: - 2020-08-26 23:56:55|  ?
[Wed Aug 26 23:56:56 MSK 2020]: Host 192.168.56.78: - 2020-08-26 23:56:56|  ?
[Wed Aug 26 23:56:57 MSK 2020]: Host 192.168.56.78: - 2020-08-26 23:56:57|  ?
[Wed Aug 26 23:56:58 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:02 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:06 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:10 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:14 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:18 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:23 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:27 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:31 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:35 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:39 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:43 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:47 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:51 MSK 2020]: Host 192.168.56.79: - 2020-08-26 23:57:52|  ?
[Wed Aug 26 23:57:53 MSK 2020]: Host 192.168.56.79: - 2020-08-26 23:57:53|  ?
[Wed Aug 26 23:57:54 MSK 2020]: Host 192.168.56.79: - 2020-08-26 23:57:54|  ?


Switch back to be sure. 



DGMGRL> switchover to orclcdb;


The situation is symmetrical. 



[Wed Aug 26 23:58:54 MSK 2020]: Host 192.168.56.79: - 2020-08-26 23:58:54|  ?
[Wed Aug 26 23:58:55 MSK 2020]: Host 192.168.56.79: - 2020-08-26 23:58:55|  ?
[Wed Aug 26 23:58:56 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:00 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:04 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:08 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:12 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:16 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:20 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:24 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:28 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:32 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:36 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:40 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:44 MSK 2020]: Host 192.168.56.78: - 2020-08-26 23:59:45|  ?
[Wed Aug 26 23:59:46 MSK 2020]: Host 192.168.56.78: - 2020-08-26 23:59:46|  ?


Also, let's pay attention to TCP connections on the oralce1 and oracle2 hosts. The data port is busy with connections only on the primary, the ONS port is busy on both the primary and replica.



Before switchover. 

oracle1
Every 1.0s: ss -tapn | grep 192.168.56.1 | grep ESTAB | grep -v ":22"

ESTAB 0 0 192.168.56.78:1521 192.168.56.1:49819 users:(("oracle_21115_or"...))
ESTAB 0 0 192.168.56.78:1521 192.168.56.1:49822 users:(("oracle_21117_or"...))
ESTAB 0 0 [::ffff:192.168.56.78]:6200 [::ffff:192.168.56.1]:49820 users:(("ons"...))




oracle2
Every 1.0s: ss -tapn | grep 192.168.56.1 | grep ESTAB | grep -v ":22"

ESTAB 0 0 [::ffff:192.168.56.79]:6200 [::ffff:192.168.56.1]:49821 users:(("ons"...))


After switchover. 

oracle1
Every 1.0s: ss -tapn | grep 192.168.56.1 | grep ESTAB | grep -v 22  Wed Aug 26 16:57:57 2020

ESTAB 0 0 [::ffff:192.168.56.78]:6200 [::ffff:192.168.56.1]:51457 users:(("ons"...))




oracle2
Every 1.0s: ss -tapn | grep 192.168.56.1 | grep ESTAB | grep -v ":22" Wed Aug 26 16:58:35 2020

ESTAB 0 0 192.168.56.79:1521 192.168.56.1:52259 users:(("oracle_28212_or"...))
ESTAB 0 0 192.168.56.79:1521 192.168.56.1:52257 users:(("oracle_28204_or"...))
ESTAB 0 0 [::ffff:192.168.56.79]:6200 [::ffff:192.168.56.1]:51458 users:(("ons"...))


Conclusion



Thus, we simulated a Data Guard cluster in a lab environment with a minimum number of settings and implemented a failover connection of a test Java application. Now we know what the developer wants from the DBA, and the DBA wants from the developer. It remains to launch and test Fast Start Failover, but, perhaps, within the framework of a separate article.


More articles:


All Articles