Oracle Cloud Infrastructure, OCI had introduced new naming term "Oracle Base Database Service" for previously known as Database Cloud Service or VMDB.
This base database service enables you to deploy 2-node or single node RAC DB systems on virtual machines. You can choose storage type between File storage or Grid Infrastructure.
I happen to experimentally asked to test application failover during database switchover in base database service. Can you configure Transparent Application Continuity (TAC) in OCI Base database service? Of course you can.
First of all, you need to enable a dataguard or active dataguard connectivity between your base database services. When you finished enabling the dataguard association, the details for your source database and standby database will be displayed as Primary or Standby.
Should you Switchover or Failover ?
So, switchover operation switches database roles between each other. Each database remains part of the Oracle Data Guard association with its new role. Hence, database failover transitions the standby database into the primary role if existing primary database fails or becomes unreachable. A failover might result in some data loss when you but switchover operation guarantees no data loss.
You can manage these operations from base service using the Console, the API, the Oracle Cloud Infrastructure CLI, the Database CLI (DBCLI), Enterprise Manager, or SQL Developer.
Okay, what about application continuity?
Application Continuity is an Oracle Database feature that enables rapid and nondisruptive replays of requests against the database after a recoverable error that made the database session unavailable.
First you need to create service in base database service.
srvctl add service -db orcl_fra1qs -service orcl_pdb_svc -pdb orcl_pdb1 -role PRIMARY -notification TRUE -session_state dynamic -failovertype transaction -failovermethod basic -commit_outcome TRUE -failoverretry 30 -failoverdelay 10 -replay_init_time 900 -clbgoal SHORT -rlbgoal SERVICE_TIME -retention 3600 -verbose srvctl start service -db orcl_fra1qs -service orcl_pdb_svc
Then you can test by adding the below entry in TNSNAMES.ora file.
TAC_PDB= (DESCRIPTION= (FAILOVER=on)(LOAD_BALANCE=on)(CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=SOURCE)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=TARGET)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=orcl_pdb_svc.prod.net)))
And run tnsping test if it is working.
[oracle@simo-src ~]$ tnsping tac_pdb TNS Ping Utility for Linux: Version 22.214.171.124.0 - Production on 14-NOV-2022 11:06:11 Copyright (c) 1997, 2022, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/126.96.36.199/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION=(FAILOVER=on)(LOAD_BALANCE=on)(CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=SOURCE)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=TARGET)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl_pdb_svc.prod.net))) OK (0 msec)
[oracle@simo-src ~]$ dgmgrl sys/password@ORCL_FRA1QS DGMGRL for Linux: Release 188.8.131.52.0 - Production on Fri Nov 4 15:14:50 2022 Version 184.108.40.206.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "orcl_fra1qs" Connected as SYSDBA. DGMGRL> show configuration Configuration - orcl_fra1qs_orcl_fra39z Protection Mode: MaxAvailability Members: orcl_fra1qs - Primary database orcl_fra39z - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 13 seconds ago) DGMGRL> switchover to orcl_fra39z Performing switchover NOW, please wait... Operation requires a connection to database "orcl_fra39z" Connecting ... Connected to "orcl_fra39z" Connected as SYSDBA. New primary database "orcl_fra39z" is opening... Oracle Clusterware is restarting database "orcl_fra1qs" ... Connected to "orcl_fra1qs" Connected to "orcl_fra1qs" Switchover succeeded, new primary is "orcl_fra39z" DGMGRL> exit
Okay so what happens? How do you test this capability if you dont have an application connection? I found a very good tool Swingbench for testing this operation. I added my failover connection string in Swingbench config and tested.
In the demonstration below you can see that there are three different charts.
First chart refers to number of transactions per second and the last chart shows nubmer of DML operations during switchover.
During switchover operation number of transactions dropped to zero for about a minute, as you can tell a gap in chart, which refers to switchover downtime. Connection has been then restored as soon as switchover operation finished.
My logged on users were able to continue without losing my swingbench connection or seeing any kinds of error.
If you didn't have TAC configuration, you'd see transaction rollback error when you switchover to standby server. If you'd like to know more about OCI and Oracle database management, feel free to contact us.
Thanks for reading.