What is a standby database?
A Standby Database is a duplicate copy of a working database on a separate server. The standby database keeps up to date by a replication function that makes sure that each transaction that is applied to the original database is also applied to the standby database. In most cases there is some lag between the time the action is applied to the original database and the standby database. This is typically between 1 and 10 minutes which is generally ok for most businesses and professionals.
Standby Database vs. Backup
A dbvisit standby database is far better than a traditional manual database backup because it can be instantly available in case of disaster. A backup from a traditional backup takes time…time which your system will not be available. A restore may also cause too much impact on other systems. With a standby database there is nothing to do in case of failure as the standby database is always available and ready. It’s possible to switch all system applications over to the standby database in minutes to allow the business to operate as normal.
There are many replication techniques to keep a standby database up to date:
- Physical Standby using redo or archive logs
- Logical Standby using SQL
- Oracle database replication using streams
Which is the best?
Physical standby is the most commonly used for disaster recovery or failure. In concept, the mechanism for keeping the standby database up to date is not hard, but putting this practice into action is difficult and requires a lot of work. There are many things that can go wrong at each step of the process. During this process the standby database must be kept up to date at all times in order to work properly. The last thing you want is your Standby database to be outdated in a time of need.
We have been witness to many instances where businesses have used an in house team to keep the standby database up to date. This appeared fine until the employee that worked on them left, the new employee’s were afraid to work on it in case it failed. In the case of an actual hard disk failure, the business’ employees actually recommended to restore from backup, instead of switching to the standby database out of fear that the system could not be reverted should a failure happen on the standby database.
Additional Benefits of a Standby Database
The standby database can be used for other uses other than just for failure recovery:
- Reporting database. Read Only queries can be done on the standby database to offset loads from the primary database.
- Shadow environments can be set up which can be used to look into issues like database bugs or problems that arise on the primary database. The primary database can continue to run risk free while the issues are looked into on the shadow database.
- Collecting snapshots for batch transactions (point in time). The standby database can be used for rollback purposes in the event 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 performed in the middle of the day to make sure staff are available to fix any problems that come up.
Oracle Licensing and Your Standby Database
You will need a full Oracle license for the standby database. This does not need to be the same as the primary database. For example, if you primary database uses Oracle Enterprise Edition, then your standby database can use Oracle Standard Edition. However, if the primary database is licensed per CPU, then the standby database also needs to be licensed per CPU.