Oracle over the years has come out with a bewildering array of features to minimise downtime for databases. In fact, the number of methods to accomplish this goal is at least a dozen. Over the years of experience with Oracle databases, there is one feature, far more than any others, that has proved itself again and again in the real world; I speak of course, of the Oracle Physical Standby feature.
The Oracle Physical Standby feature is not new at all; I was using it back in 1995 when I first started working as an Oracle DBA with version 7; perhaps that’s why it’s as reliable as it is. Although there have been cases where standby databases do encounter bugs, with proper monitoring (for example, with the monitoring done by Database Rx) this is not an issue.
So, why am I recommending Oracle Physical Standby databases over the other methods you can use to insure uptime? What makes it so special?
- An Oracle Physical Standby database has nothing shared with the primary database; it (typically) runs on a separate server, using its own storage, it’s own memory, and it’s own network interface; machines/disks/memory/network cards all break sooner or later (it’s not “if”, it’s “when”). Some folks say that RAC serves this purpose, but in fact it does not; all nodes in a RAC cluster read and write to the same storage subsystem, so that is a potential single point of failure.
- If you can afford the network connection necessary, you can physically place your physical standby database in a separate data center, so even if your primary data center burns up, your data is safe.
- This feature is available in all versions of Oracle, though for Standard Edition and lower, you will need to come up with scripts to move archivelogs and apply them on the standby, as we do for our Remote DBA clients.
- For Standard Edition, you can tune it so that you will lose a maximum of one archived redo log worth of data worst-case, if your primary database disappears.
- If using the Enterprise Edition, you can have Oracle itself manage it, and in fact use advanced automatic failover methods using a feature called Dataguard, that can switch primary/standby database roles very quickly and easily.
- If using the Enterprise Edition, you can set parameters to delay the application of data from the primary to the standby by a certain amount of time. This would be useful if you suffered logical corruption on the primary database (ie someone dropping a table) and could catch it fast enough.
- If using the Enterprise Edition, you can use the Flashback Database feature to allow the standby to recover from logical errors on the primary database even easier,
- Also, if using the Enterprise Edition, you can actually make the standby stay up-to-date with the primary database in near-real-time, by making the log-writer (LGWR) process on the primary, ship redo directly to the standby, even before that redo is archived.
- Although there are restrictions, you can actually open up a standby database in read-only mode for reporting. In versions before Oracle 11g, the standby cannot apply changes from the primary while it is open this way; however, there is a new feature in 11G called ‘Active Data Guard’ that allows this.
- In my experience, Oracle Logical standby databases are not as reliable as compared with Physical Standbys.
- Another advantage; you can use your physical standby database to do RMAN backups from, which can dramatically reduce the load on your primary database.
The main disadvantage to setting up a standby database, is that you will double your hardware cost; you will need a server/disk able to handle your peak production loads. It will also cost you an extra Oracle license for that instance. This will double the amount of space in your datacenter used, and also double the amount of electricity/heat produced. For many businesses though, this cost is far less then the cost incurred by a lengthy database recovery.
There is nothing more relieving than when a primary database malfunctions, and you are able to continue production with failover times usually measured in under 10 minutes, rather than potentially waiting days/weeks for new hardware and a full restore from backup. There is no other feature that as dramatically improves the potential uptime of an Oracle Database as much, as using a Physical Standby database.