This article covers the process to follow after a graceful switchover or failover occurred to an Oracle standby database using Dbvisit Standby. The Oracle Database Administrator (DBA) faces the challenge of switching clients to the newly activated standby database which is now running as the new primary database. Previously these clients were connected to the former primary database, and so they must be reconnected to the new primary database. Dbvisit Standby, as well as Oracle Data Guard, does not provide a feature to support this. Generally, there are 2 options for the DBA to achieve this:
- Change of the database host using either the connect string or DNS.
- Use of Transparent Application Failover (TAF) technique.
1. Change of database host
Our examples use the following host and service names:
host name primary server: dbprod
host name standby server: dbstandby
service name: orclnet
service name: orcl_haIn the case of example 1-a, we are going to change the hostname within the TNS connect string after a graceful switchover or failover and change the IP from the primary server's to the standby server's.
The tnsnames.ora entry:
orcl_ha=
(description=
(address = (host = dbprod)(protocol = tcp)(port = 1521))
(connect_data = (service_name = orcl))
)will be changed after the graceful switchover or failover into:
orcl_ha=
(description=
(address = (host = dbstandby)(protocol = tcp)(port = 1521))
(connect_data = (service_name = orcl))
)Or alternatively by using a JDBC-connect:
jdbc:oracle:thin:@dbprod:1521/orclwill be changed after the switchover / failover to
jdbc:oracle:thin:@dbstandby:1521/orclThis solution seems to be the easiest way if the changes to be made are limited, or on a single point. This type of configuration is mostly found with clients that have:
- centralized tnsnames.ora, or
- net service resolution via Oracle Internet Directory, or
- Active Directory, or any connect string at the Application Server
This method is only efficient in a centralized names configuration. The DBA could switch to option 1-b if there is a DNS alias used for the name services. In addition to the hostnames defined above, we define an extra DNS CNAME as dbha, which initially points at the host dbprod.
The tnsnames.ora as well as the JDBC connect string stay as they are after a graceful switchover or failover:
orcl_ha=
(description=
(address = (host = dbha)(protocol = tcp)(port = 1521))
(connect_data = (service_name = orcl))
)Or:
jdbc:oracle:thin:@dbha:1521/orclAfter failover the DNS CNAME dbha is redirected to the host name dbstandby. The advantage of this method is that it works well for centralized as well as for the de-centralized names configuration services. The disadvantage with this method is that the connect failover can have serious time delays, because the new DNS configuration first has to be replicated to the DNS servers. The name service caches of clients also may need to be flushed. So even though the database might be online and available, some of the clients may still not be able to connect because of the DNS update being delayed.
2. Transparent Application Failover (TAF)
The most recommended and most flexible solution is using a dynamic connection failover. To achieve this the DBA defines dbprod as well as dbstandby into a common connect string and to establish a failover between them both.
The related tnsnames.ora entry would be as follows:
orcl_ha=
(description=
(address = (host = dbprod)(protocol = tcp)(port = 1521))
(address = (host = dbstandby)(protocol = tcp)(port = 1521))
(failover = yes)
(connect_data =
(service_name = orcl_taf)
(failover_mode =
(type = select)
(method = basic)
)
)
)Also for JDBC the DBA would have to use the complete connect string:
jdbc:oracle:thin:@(description=(address=(host=dbprod)(protocol=tcp)(port=1521))(address=(host=dbstandby)
(protocol=tcp)(port=1521))
(failover=yes)(connect_data=(service_name=orcl_taf)
(failover_mode=(type=select)(method=basic))))
In this configuration it's essentially to ensure that the service orcl_taf is up only at the present primary side. Otherwise it's possible that you run into the problem that the client connections are directed to the standby database, which produces the following error message output:
ORA-01033: ORACLE initialization or shut down in progress
The correct service state can be achieved in different ways, depending on the infrastructure the databases are running on.
a) With a stand-alone database, the DBA has to register the service name at the primary instance:
SQL> alter system set service names='orcl_taf', other_service_names ;
SQL> alter system register;
and unregister it from the standby instance, respectively:
SQL> alter system set service names= other_service_names ;
SQL> alter system register;
b) If the database is controlled by Oracle Clusterware 10g or11g (RAC or cold failover), a cluster service would be defined to ease the service management. This has to be done on both sides, primary and standby:
srvctl add service -d orcl -s orcl_taf
The DBA now would start the cluster service at the primary side
srvctl start service -d orcl -s orcl_taf
and stop it at the standby, accordingly
srvctl stop service -d orcl -s orcl_taf –f
c) With a Dbvisit Standby backed solution based on Oracle Grid Infrastructure 11gR2 or Oracle Restart,it would be possible to fully automate the start and shut downs of the service. This is done by the newly introduced -l role parameter of the srvctl command. On both sides, primary and standby, the DBA defines the same services and starts them both
srvctl add service -d orcl -s orcl_taf -l primary
srvctl start service -d orcl -s orcl_taf
Grid Infrastructure or Oracle Restart Permanently Controls the Database.
If the database role changes from PRIMARY to PHYSICAL STANDBY, the service will automatically be shut down. Vice versa, the orcl_taf service will start up automatically along with the former PHYSICAL STANDBYdatabase becoming the new PRIMARY. Dbvisit Standby ensures the databases roles are changed correctly in case of graceful switchover or failover so that this solution is completely automated.
Author: Thilo Solbrig (Oracle Certified Master) www.aspicon.de
Comments