{"id":3801,"date":"2022-01-31T07:00:00","date_gmt":"2022-01-31T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=3801"},"modified":"2024-03-09T23:40:52","modified_gmt":"2024-03-09T20:40:52","slug":"pos-energopoioyme-oracle-data-guard-me-aytomato-failover-gi","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-energopoioyme-oracle-data-guard-me-aytomato-failover-gi\/","title":{"rendered":"How we enable Oracle Data Guard with automatic failover for High Availability"},"content":{"rendered":"

In previous<\/a> 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. <\/p>\n\n\n\n

This technology is called Oracle Data Guard Fast Start Failover<\/strong> and below is the diagram of its infrastructure: <\/p>\n\n\n

\n
\"\"
01 (docs.oracle.com)<\/figcaption><\/figure>\n<\/div>\n\n\n

The difference with simple Oracle Data Guard on physical standby is that the commit is done synchronously<\/strong>, 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 <\/strong>which records the communication to both sites (primary, standby).<\/p>\n\n\n\n

Prerequisites<\/h2>\n\n\n\n

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<\/a>.<\/p>\n\n\n\n

Also, if the machine where the observer is installed is different, it must have been installed Oracle Database Software<\/strong> or his Oracle Database Client<\/strong>. As well as to pass the records to tnsnames.ora<\/strong> as they have been on the other two sites.<\/p>\n\n\n\n

The installation<\/h2>\n\n\n\n

We connect from the primary (oradev1) to the broker:<\/p>\n\n\n\n

dgmgrl \"sys\/Kwdikos1!\"@ORADEV<\/pre>\n\n\n\n
We disable the Data Guard configuration if it is not already:<\/p>\n\n\n\n
disable configuration;<\/pre>\n\n\n\n
We change the log transport to synchronous and the time waiting for the standby to be updated (net timeout) from 30 to 10 seconds.<\/p>\n\n\n\n
edit database oradev set property 'LogXptMode'=sync;\n\nedit database oradevdr set property 'LogXptMode'=sync;\n\nedit database oradev set property 'Nettimeout'=10;\n\nedit database oradevdr set property 'Nettimeout'=10;<\/pre>\n\n\n\n
Then we change the protection mode from Max Performance to Max Availability.<\/p>\n\n\n\n
edit configuration set protection mode as maxavailability;<\/pre>\n\n\n\n
We activate the configuration, after first seeing that the configuration does not show us any error:<\/p>\n\n\n\n
show configuration verbose;\n\nenable configuration;<\/pre>\n\n\n\n
Then we enable Fast Start failover:<\/p>\n\n\n\n
enable fast_start failover;<\/pre>\n\n\n\n
At the end we connect from the server that will run the observer:<\/p>\n\n\n\n
dgmgrl \"sys\/Kwdikos1!\"@ORADEVDR<\/pre>\n\n\n\n
And we start the observer:<\/p>\n\n\n\n
start observer;<\/pre>\n\n\n\n
We can run it as a background process on Unix with the following command:<\/p>\n\n\n\n
nohup dgmgrl \"sys\/Kwdikos1!\"@ORADEVDR \"start observer file='$HOME\/fsfo.dat'\" -logfile $HOME\/observer.log &<\/pre>\n\n\n\n
We can also monitor the observer's log for its actions and errors:<\/p>\n\n\n\n
tail -f $HOME\/observer.log<\/pre>\n\n\n\n
\"\"
02<\/figcaption><\/figure>\n\n\n\n
If we now look at the configuration we will see that it runs without problems:<\/p>\n\n\n\n
dgmgrl \"sys\/Kwdikos1!\"@ORADEV<\/pre>\n\n\n\n
show configuration verbose;<\/pre>\n\n\n\n
\"\"
03<\/figcaption><\/figure>\n\n\n\n
How do we test if automatic failover is working?<\/h2>\n\n\n\n
Very simply we can go to the primary and kill the Oracle process monitor service:<\/p>\n\n\n\n
ps -ef |grep pmon<\/pre>\n\n\n\n
After finding the process number, we kill it:<\/p>\n\n\n\n
kill -9 6188<\/pre>\n\n\n\n
\"\"
04<\/figcaption><\/figure>\n\n\n\n
Alternatively we run from sqlplus <\/strong><\/em>the following command:<\/p>\n\n\n\n
shutdown abort;<\/pre>\n\n\n\n
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):<\/p>\n\n\n\n
\"\"
05<\/figcaption><\/figure>\n\n\n\n
When we then go and raise the service of the primary (oracledev1) in mount state:<\/p>\n\n\n\n
sqlplus \/ as sysdba<\/pre>\n\n\n\n
startup mount;<\/pre>\n\n\n\n
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:<\/p>\n\n\n\n
\"\"
06<\/figcaption><\/figure>\n\n\n\n
When the reinstatement is complete, we can return to the primary site at any time:<\/p>\n\n\n\n
dgmgrl \"sys\/Kwdikos1!\"@ORADEVDR<\/pre>\n\n\n\n
switchover to oradev;<\/pre>\n\n\n\n
How to connect automatically to any site that is active<\/h2>\n\n\n\n
By using it Transparent Application Failover<\/a> we can by registering in tnsnames.ora automatically connect to the standby in case the primary goes down. <\/p>\n\n\n\n
In the tnsnames.ora<\/strong> but we should not make use of the base name as service_name<\/strong>, as if we do this it won't connect<\/strong> 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<\/strong> as the Oracle process suddenly crashed with the database listener still up.<\/p>\n\n\n\n
To overcome this problem we should go to primary (oracledev1) and make one Dynamic<\/strong> Service<\/strong>. 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.<\/p>\n\n\n\n
The footsteps<\/h4>\n\n\n\n
First we create the service defining its name and what it is about high availability<\/em> via Fast<\/strong> Application<\/strong> Notification<\/strong> (FAN) with the parameter aq_ha_notifications<\/strong>:<\/p>\n\n\n\n
begin\ndbms_service.create_service('oradev_primary','oradev_primary',aq_ha_notifications=>true,failover_method=>'BASIC',failover_type=>'SELECT',failover_retries=>120,failover_delay=>1);\nend;\n\n\/<\/pre>\n\n\n\n
We try to start the service we created:<\/p>\n\n\n\n
begin\n dbms_service.start_service('oradev_primary');\nend;\n\n\/<\/pre>\n\n\n\n
After it starts without problem we can fix it too start-up trigger<\/strong> where the service will start:<\/p>\n\n\n\n
create or replace trigger start_oradev_primary_service after startup on database\ndeclare\n\trole varchar(50);\nbegin\n\tselect database_role into role from v$database;\n\tif role='PRIMARY' then\n\t\tdbms_service.start_service('oradev_primary');\n\telse\n\t\tdbms_service.stop_service('oradev_primary');\n\tend if;\nend;\n\n\/<\/pre>\n\n\n\n
We do not need to repeat the procedure in standby as Data Guard will transfer the service settings by itself.<\/p>\n\n\n\n
Now we can go to tnsnames<\/strong>.ora<\/strong> and add the following entry configuring hosts and service_name accordingly to what we just created:<\/p>\n\n\n\n
ORADEV_TAF =\n  (DESCRIPTION = \n    (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)\n    (FAILOVER = ON)\n    (LOAD_BALANCE = OFF)\n    (ADDRESS_LIST =    \n      (ADDRESS = (PROTOCOL = TCP)(HOST = oracledev1)(PORT = 1521))\n      (ADDRESS = (PROTOCOL = TCP)(HOST = oracledev2)(PORT = 1521))\n    )\n    (CONNECT_DATA =\n      (SERVICE_NAME = oradev_primary)\n    )\n  )<\/code><\/pre>\n\n\n\n
 How we remove Data Guard with Fast-Start Failover<\/h2>\n\n\n\n
 If we want to remove Data Guard completely then: <\/p>\n\n\n\n
dgmgrl \"sys\/Kwdikos1!\"@ORADEV<\/pre>\n\n\n\n
disable fast_start failover force;\ndisable configuration;\nremove configuration;\nexit;<\/pre>\n\n\n\n
sqlplus \/ as sysdba<\/pre>\n\n\n\n
alter system set dg_broker_start=false;<\/pre>\n\n\n\n
Sources:<\/h2>\n\n\n\n