In previous article we configured Data Guard Observer. We did some heavy configurations on the server side. Now, it's time to take it to the client side. In this article we will add few more configurations so our client side tools work seamlessly with our active data guard environment. When I say seamlessly, I mean: you can perform switchover(s) to your heart's desire and the applications will never disconnect from the backend, not even a hiccup. Pretty cool, right? Well that's the idea. Database failover(s) / switchover(s) should be seamless to the application as well as to your users.

SSH Connections
I know I am working in a cloud. We built active data guard in Amazon cloud. When I am in the cloud, it means my servers are located somewhere and I must be connecting to these servers remotely, right? Quite precisely, I am using command line SSH to connect with the cloud servers. All good.

Now, I want to connect to these database servers via Oracle client or a simple application. What should I do? Since I am using SSH client, I will setup port forwarding. Let's setup port forwarding for the primary database.

ssh -i DevOps.pem -L 5902:localhost:1521 ec2-user@primary_server_ip_address

So what exactly are we doing in the command above? Sure enough we are invoking SSH client. Since I am connected to AWS and so I am referring to my .pem file, a key file. Next is the syntax for port forwarding.

-L 5902:localhost:1521 ec2-user@primary_server_ip_address: I will use localhost on port 5902 to connect with primary database server on port 1521.

Now let's set up another port forwarding for the standby database.

ssh -i DevOps.pem -L 5903:localhost:1521 ec2-user@standby_server_ip_address

Exact same command, except we will be using port 5903 on localhost to connect with standby database server on port 1521.

Configure TNSNAMES on the Client
I downloaded Oracle 11g instant client from Oracle. Next I created network/admin directory to host my tnsnames.ora file.

Below is the content of tnsnames.ora on my client.

CSSAPP =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 5902))
     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 5903))
   )
 (CONNECT_DATA =
   (SERVER=dedicated)
   (SERVICE_NAME = cssapp)
 )
)

I am using CSSAPP as my service name. Pay attention to how I am using localhost on ports 5902 and 5903. Pretty easy, right?

Notice we are using network service name CSSAPP. None of our database servers are aware of this service. Therefore, let's create this service on the primary database server.

Go to SQL prompt on the primary server.

/*create the service*/
begin
 dbms_service.create_service('cssapp','cssapp');
end;
/

/*start the service*/
begin
 DBMS_SERVICE.START_SERVICE('cssapp');
end;
/

No need to create this service manually on the standby server, why? Well, we are running an active data guard and therefore these commands will be applied to the standby on the next redo transport. Cool, I am loving it.

Service Trigger

Next we need to create a trigger which will ensure that CSSAPP service is only offered when a database is in a primary role. We need this trigger, but why?

Stay with me because what I am about to explain is a bit tricky.

Let's say you did not create the trigger. Now you point the application to the primary database. Yes, application connects just fine with the primary database. Because we already have a CSSAPP service running and our primary database is open and ready for business. All peachy.

Now, our DBA performs a switchover. Original primary database becomes a physical standby and the original physical standby becomes a primary database.

You'd think so what? Now my application should connect to CSSAPP service on the new primary database, right? Nope, wrong. Now trying to connect the application you will get an error: ORA-01033: Oracle initialization or shutdown in progress. Yuck! That's not what we expected.

Do you know why that happened? Remember: CSSAPP service is always available on both databases even if one of the databases is in a mounted mode. After switchover our primary database was converted to a physical standby mode. Which means, it is not open but is only mounted but the CSSAPP service is still available. Therefore, when trying to connect with our application it will pickup CSSAPP on the primary and throw ORA-01033:Oracle initialization or shutdown in progress. It picked up the first available CSSAPP service from tnsnames file. In other words, it did not even reach the CSSAPP on the new primary database. Which is an expected behavior without a trigger. What we need to do is offer CSSAPP service only if the database is in a PRIMARY role. Stop CSSAPP service on the physical standby server.

Let's look at the trigger now.

create trigger trgCssApp after startup on database
declare
 v_role varchar(30);
begin
 select database_role into v_role from v$database;
 if v_role = 'PRIMARY' then
 DBMS_SERVICE.START_SERVICE('cssapp');
 else
 DBMS_SERVICE.STOP_SERVICE('cssapp');
 end if;
end;
/

Let's understand this trigger now. We are querying v$database view to identify the role of the database server. If the role is PRIMARY then start and offer CSSAPP service else stop it. Now when application tries to connect to the database, it will only see one CSSAPP service which is running on the primary database server.

Runtime Failover

We performed a manual switchover but what about runtime failover(s)? We need to make a minor modification to our CSSAPP service for it to function seamlessly in a runtime failover situation.

Go to SQL prompt on the primary server.

begin
 dbms_service.modify_service
 ('cssapp',
 FAILOVER_METHOD => 'BASIC',
 FAILOVER_TYPE => 'SELECT',
 FAILOVER_RETRIES => 200,
 FAILOVER_DELAY => 1);
end;
/

Notice FAILOVER_RETRIES. Oracle net service will try to connect to the service and upon failure it will move on to next server for the connection.

You can perform as many switchover(s) / failover(s) and now your application will function seamlessly. Look Maa! I am always connected..

Finally, I thank you for staying with me throughout the series. Now go build your active data guard, will ya?

See you again!