Some things never change! Like standby databases (apart from updating of course). They are what they are. And explanation of what they are has resulted in one of our most popular posts. So this week we are going back to basics with a classic post from our archives, updated!
What is a standby database?
A Standby Database is a duplicate copy of an operational database on another server. The standby database is being kept up to date by a replication mechanism that ensures each transaction that is applied to the operational or primary database is also applied to the standby database. In most cases there is some lag between when the transaction is applied to the primary database and when it is applied to the standby database. This is typically between 1 and 10 minutes which is acceptable for most businesses.
Standby Database vs. Backup
A standby database is far superior to a normal backup as it is instantly available in the event of a disaster or failure. To restore a backup takes time, and during the restore time the system is not available. A restore may also cause too much impact on other systems. With a standby database there is nothing to restore in the event of a disaster or failure as the standby database is always available. It is possible to switch applications over to the standby database in a matter of minutes to allow business continuity.
There are several replication mechanisms to keep a standby database up to date:
- Physical Standby using redo or archive logs
- Logical Standby using SQL
- Oracle replication using streams
Which is the best?
Physical standby is the most commonly used for disaster recovery or failure. Conceptually the mechanism for keeping the standby database updated is not difficult, but putting this into practice to get a robust solution is complex and requires a lot of work. There are so many things that can go wrong at each step of the process and the standby database must be kept up to date no matter what. And if it is not keeping up to date then alerts should be raised. The last thing you want to happen is that the standby database is out of date, when you need it most.
I have seen instances where companies have used an in house solution to keep the standby database up to date. This appeared fine until the DBA that wrote them left, the new DBA’s were too scared to touch it. As a result, no testing was even done. In the case of an actual hard disk failure, the DBA recommended to restore from backup, rather than switch to the standby database, because they were not sure if it was kept up to date and how the process could be reversed once the primary server was operational again. At another company, the standby database was last updated 2 years ago!
Additional Benefits of a Standby Database
The standby database can be used for other purposes other than just for disaster recovery:
- Reporting database. READ ONLY queries can be performed on the standby database to offset loads from the primary database.
- Shadow environments can be set up which can be used to investigate issues like database bugs or inconsistencies that arise on the primary database. The primary database can continue to run risk free while the issues are investigated on the shadow database.
- Capturing snapshots for batch transactions (point in time). The standby database can be used for rollback purposes in case of errors during batch processing.
- Perform backup on the standby database rather than the primary database to offset loads to the primary server. Backups can be done in the middle of the day to ensure staff are available to resolve any backup issues.
Oracle Licensing and Your Standby Database
You will need a full Oracle license for the standby database. This does not needs to be the same metric as the primary database. So if you primary database is Enterprise Edition, then your standby database can be Standard Edition. But if the primary database is licensed per CPU, then the standby database also needs to be licenced per CPU.
So there you go, a classic from the past, but still as relevant today! Do you have your standby database sorted? Let us know what your setup is like in the comments.
