This blog post is part 2 of my series "Remote Listener and Standard Edition 2". The first blog post was about the setup of the Remote Listener, now the second is how one can use a Remote Listener for a more or less transparent failover of new connects to a database which is createad as high available standby environment.
Brief recap:
There are two database servers with a primary standby database (ORCL) and a remote listener set up on a third server. (While one could use "Connection Manager" (CMAN) for Enterprise Edition
databases, the license does not allow to use this for Standard Edition 2
databases.) Which kind of primary-standby solution is used does not matter - as they all work same more or less for Standard Edition Databases (depending on archive redo log shipping).
The solution cookbook (which also works with Standard Edition High Availability (SEHA)).
The PDB is called jsotestpdb and does not yet have its own service for the application. However, as this makes sense, a service with the name JSO_APP_SERVICE is created.
The service can be created and started on the primary database using the following syntax:
exec dbms_service.create_service('JSO_APP_SERVICE','JSO_APP_SERVICE');
After the service is created, it can be started:
exec dbms_service.start_service ('JSO_APP_SERVICE');
As it makes sense for high availability reasons that this service is also started automatically during a switchover, either a “SAVE STATE” of the PDB must be executed or a trigger must be created that starts this service when the PDB is opened (the third option would be to create and start a service in the grid infrastructure). Services are only started when the PDB is opened, this means, it is ensured that this service is never started on the standby side.
The simplest method to execute an automated service start is certainly the save state command (when the service is active):
alter pluggable database jsotestpdb save state;
On the listener side, this now looks as follows (the remote listener host name is hidden):
Also highlighted is the new created and started PDB application service.
Everything is in place now to prepare the client connections. Therefore an entry is created in TNSNames.ora with the name of the remote listener host as HOST (remote listener host name is hidden in the following picture).
As one can see, the connection was routed to the PDB running on the WINTEST-1 node, because this is the primary database. The standby is running on the WINTEST-2 node and the application service is not running there (the PDB is mounted, not opened). Now doing a switchover (with the toolset of your standby software provider) and reconnect again, after the PDB was opened. We still use the same tnsnames.ora which points to the remote listener host and the service registered there.
