How we enable Oracle Data Guard with automatic failover for High Availability
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
In previous article we had seen how we activate Oracle Data Guard in physical standby. Now we will take the installation one step further with a modern commit that will allow us to activate the automatic failover from the primary to the standby and vice versa, without our intervention.
This technology is called Oracle Data Guard Fast Start Failover and below is the diagram of its infrastructure:
The difference with simple Oracle Data Guard on physical standby is that the commit is done synchronously, that is, for a transaction to be successfully passed to the primary, it must first have been passed to the standby. Also, to activate the automatic failover feature, one must be running on a site observer which records the communication to both sites (primary, standby).
Prerequisites
To begin with, we should have followed all the steps to create an Oracle Data Guard with physical standby. How we can do it, we will see step by step in the article here.
Also, if the machine where the observer is installed is different, it must have been installed Oracle Database Software or his Oracle Database Client. As well as to pass the records to tnsnames.ora as they have been on the other two sites.
The installation
We connect from the primary (oradev1) to the broker:
dgmgrl "sys/Kwdikos1!"@ORADEV
We disable the Data Guard configuration if it is not already:
disable configuration;
We change the log transport to synchronous and the time waiting for the standby to be updated (net timeout) from 30 to 10 seconds.
edit database oradev set property 'LogXptMode'=sync; edit database oradevdr set property 'LogXptMode'=sync; edit database oradev set property 'Nettimeout'=10; edit database oradevdr set property 'Nettimeout'=10;
Then we change the protection mode from Max Performance to Max Availability.
edit configuration set protection mode as maxavailability;
We activate the configuration, after first seeing that the configuration does not show us any error:
show configuration verbose; enable configuration;
Then we enable Fast Start failover:
enable fast_start failover;
At the end we connect from the server that will run the observer:
dgmgrl "sys/Kwdikos1!"@ORADEVDR
And we start the observer:
start observer;
We can run it as a background process on Unix with the following command:
nohup dgmgrl "sys/Kwdikos1!"@ORADEVDR "start observer file='$HOME/fsfo.dat'" -logfile $HOME/observer.log &
We can also monitor the observer's log for its actions and errors:
tail -f $HOME/observer.log
If we now look at the configuration we will see that it runs without problems:
dgmgrl "sys/Kwdikos1!"@ORADEV
show configuration verbose;
How do we test if automatic failover is working?
Very simply we can go to the primary and kill the Oracle process monitor service:
ps -ef |grep pmon
After finding the process number, we kill it:
kill -9 6188
Alternatively we run from sqlplus the following command:
shutdown abort;
If we then look at the observer's log, we will see that it cannot communicate with the primary and within 30 seconds, which is the default value for timeout and failover, it went to standby (oracledevdr):
When we then go and raise the primary service (oracledev1) in mount state:
sqlplus / as sysdba
startup mount;
We will see in the observer's log that after a while he connected to the database and reinstated it with the data from the standby which is temporarily primary:
When the reinstatement is complete, we can return to the primary site at any time:
dgmgrl "sys/Kwdikos1!"@ORADEVDR
switchover to oradev;
How to connect automatically to any site that is active
By using it Transparent Application Failover we can by registering in tnsnames.ora automatically connect to the standby in case the primary goes down.
In the tnsnames.ora but we should not make use of the base name as service_name, as if we do this it won't connect only in whichever instance is the primary at the moment but in any instance that has the base listener uploaded. This will cause a problem when the standby can be open for reading (Active Data Guard) or if it becomes cold Fail over as the Oracle process suddenly crashed with the database listener still up.
To overcome this problem we should go to primary (oracledev1) and make one Dynamic Service. His job will be to distinguish which base has the primary role and will help us to always be connected to the right one. It is dynamic as it will only trigger when the base has the primary roll.
The footsteps
First we create the service defining its name and what it is about high availability via Fast Application Notification (FAN) with the parameter aq_ha_notifications:
begin
dbms_service.create_service('oradev_primary','oradev_primary',aq_ha_notifications=>true,failover_method=>'BASIC',failover_type=>'SELECT',failover_retries=>120,failover_delay=>1);
end;
/
We try to start the service we created:
begin
dbms_service.start_service('oradev_primary');
end;
/
After it starts without problem we can fix it too start-up trigger where the service will start:
create or replace trigger start_oradev_primary_service after startup on database
declare
role varchar(50);
begin
select database_role into role from v$database;
if role='PRIMARY' then
dbms_service.start_service('oradev_primary');
else
dbms_service.stop_service('oradev_primary');
end if;
end;
/
We do not need to repeat the procedure in standby as Data Guard will transfer the service settings by itself.
Now we can go to tnsnames.ora and add the following entry configuring hosts and service_name accordingly to what we just created:
ORADEV_TAF =
(DESCRIPTION =
(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
(FAILOVER = ON)
(LOAD_BALANCE = OFF)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledev1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledev2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oradev_primary)
)
)How we remove Data Guard with Fast-Start Failover
If we want to remove Data Guard completely then:
dgmgrl "sys/Kwdikos1!"@ORADEV
disable fast_start failover force; disable configuration; remove configuration; exit;
sqlplus / as sysdba
alter system set dg_broker_start=false;
