Standby database Introduction

Standby database is an important aspect in oracle, its useful in disaster recovery scenarios and to carry out testing on production data without hurting production database performance. We will discuss more about standby database.

• Standby database is database replica created from backup of primary database
• By applying archived redo logs from primary database to standby database, once can keep two databases in synchronized
• Purpose of having standby database 
• Disaster protection
• Data corruption protection
• Supplemental reporting or testing

Configuration Options

We can setup a standby database in several different ways depending on method for

• Transferring archived redo logs
• Applying archived redo logs

Example: 

Managed standby environment allows primary database to automatically archives redo logs to standby database site so long as standby instance is started

Non-managed standby environment makes it compulsory to transfer archived redo logs manually

Managed standby mode - it automatically applies logs received from primary database

manual recovery mode - apply logs manually

Pros and Cons of standby database

Pros

• A standby database is powerful configuration for both disaster recovery and supplementary reporting and testing 
• We can maintain several standby database in geographically diverse locations
• Maintain primary and standby database on same machine on different drive/file system
• We can make standby database the new primary database with minimal loss of time and data
• Standby database provides protection against
• erroneous batch jobs
• user errors
• applying corruption on primary by not applying corrupt data on standby site

Cons

• It requires additional machine if you want to maximize disaster protection by keeping standby database on separate host
• Implementation and maintenance of Net8
• Additional system resource and cost
• Extra efforts for administration of standby site. 

Types of standby databases

In oracle standby database can be one of these types

1. Physical standby database
2. Logical standby database
3. Snapshot standby database

Physical standby database

• Physical standby database is an exact block for block copy of primary database
• This physical standby database uses process called redo apply i.e. redo received from primary database gets applied to standby database using recovery process
• This can be opened for read only access to execute number of reporting and testing queries
• With license of active data guard redo can be applied during database is open mode
• Benefits:
• Disaster recovery and high availability
• Data protection
• Reduction in primary database workload performance

Logical standby database

• Initially logical standby database is identical copy of primary database but later can be altered to have different structure
• This type of standby database is updated or brought up in sync by executing SQL statements.
• Oracle data guard automatically applies data from archived redo logs on standby database by transforming data into SQL statements and then executing SQL statements on logical standby database
• Database must remain open as it uses SQL statements to update logical standby database
• Although this database remain in read/write mode its target table will be only available for read only operations
• Benefits: 
• Ideal for High Availability
• Minimizes downtime on software update
• support for reporting and decision support requirements

Snapshot standby database

• Snapshot standby database is type of updatable standby database has full data protection for primary database
• A snapshot standby database receives and archives but does not apply redo data from its primary database
• Redo data received from primary database is applied when snapshot standby database is converted back into physical standby database.
• The data of primary database is fully protected as snapshot standby database can be converted to physical standby database any time and redo is then applied
• Benefits
• It provides exact replica of production database for development and testing purpose while data protection at all time
• It can be easily refreshed to contain current production data by converting to physical standby database and re synchronizing

In next article we are going to discuss about different protection modes in oracle for data guard configuration.