In a previous blog I discussed some of the questions you need to consider when setting up a high availability environment for your Oracle databases. One of the biggest questions is, “How much availability is enough?”
In this blog, I’ll discuss how Quest SharePlex can provide the basis for a high availability system without the complexities of Oracle RAC. This can be “just enough” availability for many uses. Note that in this case, we’ll be talking about providing high availability for a database, not providing high availability for SharePlex itself. In future blogs, I’ll cover using SharePlex in an environment that already uses RAC, so that you can eliminate the database as a single point of failure; and how to provide high availability for SharePlex.
One of the major features of SharePlex is that it performs database replication at the “logical” or SQL transaction level. So, you’re not stuck with lower level replication and the need to perform a database recovery before your “standby” or replica database is available for use. The SharePlex target database is ALWAYS available, in full read/write mode.
Another advantage of using SharePlex for high availability is that it’s easy to avoid single points of failure. Unlike Oracle RAC, where the database or physical storage is shared; since SharePlex replicates into a completely separate target database, you can avoid using shared storage.
Configuring SharePlex for HA
When using SharePlex, rather than referring to “Primary” or “Secondary” databases, we use the terms “Source” and “Target”, since all databases in a SharePlex replication environment are open and in read/write mode. For the remainder of this blog, I’ll use “source” to refer to the “primary” or “normal” database, and “target” to refer to the database that will support HA.
Here are some things to consider when using a SharePlex target for HA:
- Use a TNS Alias instead of a BEQUEATH connection for your databases. This will make failover and reverse replication a bit simpler.
- Use a TNS Alias, different from those created in step #1 and Virtual IP or Hostname for your source database. Use this in your applications, to facilitate failover from the source to the target.
- Consider setting up “reverse” replication, from target to source. This will allow you to capture transactions applied to the target after failover, and facilitate “fail back” to the source with no data loss.
- You may want to consider using hardware or software “load balancers” to help automate the actual failover process.
The first step in any SharePlex replication is to instantiate your target with exactly the same data as contained in the source. You can find recommendations on how to accomplish this with no downtime using Oracle tools such as RMAN, or other tools, in the SharePlex Administration Guide.
Once you’ve instantiated your target database, or as a part of the instantiation if you’re doing a “now downtime” instantiation, you’ll need to build a configuration or config file. If you’re replicating entire schemas, you can use the wildcard “expand” function to replicate each schema in one line. For example, to replicate your GL schema your config file would look something like this:
EXPAND GL.% GL.% <Target HostName>@o.<TargetDB TNS ALIAS>
After you verify and activate your config file, transactions will flow from the source to the target, and your target is ready for use.
Configuring your systems and applications for failover
As mentioned above, you’ll want to use TNS Aliases for both the source and target databases. You’ll also want to set up a “Virtual” TNS Alias and IP address that “floats” between the source and the target servers/databases. This third alias is the one to which you’ll point your applications.
You’ll need some sort of “heartbeat” or failure detection; so that a script or program on the target can detect a failure on the source. This could be a network ping, a “tnsping” or a remote database access.
When the target detects a failure, your script should then attempt to shut down the source (to avoid network conflicts), change the virtual alias to point to the target, and, if set up, start replication from the target back to the source. You’ll probably also want to send an email or raise some sort of alert.
Using a Load Balancer
You can eliminate all of most of the coding required for failover by using a hardware or software load balancer that first detects the fact that one of the nodes is down, and then routes traffic only to the surviving node. If your application is written or can be modified to support multiple-master databases, then the load balancer can even be used to direct traffic to both nodes. I’ll cover that specific use case in a future blog.
In a failure, your applications will notice a disconnection from one database, and so will need to be coded to reconnect; and the failover script will take a small amount of time, usually less than a second, to notice the failure and change the virtual IP/Alias of the database, Also, since there are two separate databases involved, and SharePlex uses asynchronous replication, there are some failover scenarios that can result in a loss of a small number of committed transactions; but SharePlex can be queried to determine if this occurred, and once the original source database is restored, can be re-synchronized. This is the “good enough” part of using SharePlex without RAC for High Availability. If you need shorter recovery time, or more transparency; you may want to consider using RAC complemented by SharePlex, which I’ll cover in my next blog.
Test and Test again
It’s been said that a backup is no better than the last restore; and that’s also the case with high availability systems. Until they’re tested, in the “real” environment; you can never be certain that things will work as planned. So, the last phase of your implementation plan should be to test failover, at least once, and possibly more than once. Once you’ve tested your failover, you should be able to rest easy; with assurance that SharePlex will help you meet or exceed your agreed Service Levels.
Here are some additional resources to help you get the most out of SharePlex