The different modes of setup for Oracle 12c Data Guard allow for different configurations that are adaptive and that are dependent on available hardware, processes, and ultimately business needs.
The modes for the Data Guard configuration include maximum protection, maximum availability, and maximum performance. The Data Guard configuration can also have more than one standby database. Having multiple standby databases is even recommended in modes like maximum protection in order to make sure at least one standby is available.
Maximum protection is designed for zero data loss, while the redo transport is synchronous. Synchronous transport means that you’re applying the database transactions at the same time on the primary and secondary database servers. The primary waits for a response telling it that the transaction has been applied to the standby database before it commits the transaction. Having two standbys or even a RAC setup on the secondary site is recommended in this situation because if the standby fails, the primary will be halted in this mode.
Maximum availability also has the goal of zero data loss (again, with the redo transport being synchronous). The difference is that if the standby fails or if there is a connectivity issue, it will allow the primary to continue and the standby to fall slightly behind. It is not as critical to have more than one standby for this mode because of the fault tolerance.
Maximum performance has the possibility of minimal data loss, but performance is the concern on the primary because the redo transport is done asynchronously, and it doesn’t have to check back with the primary before the primary does a commit. So, if transport is a concern for slowing down the primary database and the performance risk is higher than any data loss, the maximum performance mode will allow for that.
It can be especially hard with discussions regarding costs and business expectations to come to an agreement on which mode to use. It is probably simplest to set the protection mode; the ability to use the standby server in very practical ways should help defray concerns. To set the protection mode for the database, issue the following statement:
The Oracle Database 12c now allows for confirmation that the transaction has been received even if still in memory with the NOAFFIRM attribute. FASTSYNC doesn’t wait for the actual write because if the system has slower disk or I/O contention, it will be able to receive the information the transport executed. FASTSYNC allows the primary database to perform as expected in a maximum available configuration and not be dependent on I/O performance of the standby database.
Each mode has different services that take care of the transport and application:
- Transport Services are the pieces that handle the synchronous and asynchronous transport. These services move the log or transactions to available standby servers and verify that they are being applied to these servers. Synchronous transports validate that transactions have been applied on both primary and standby servers before committing the transactions. The asynchronous transport will validate that transactions have been sent, but transactions will be committed on the primary even if not completed on the standby.
- Apply Services take care of the SQL Apply or the Redo Apply. Apply Services take the SQL statements or redo logs and control how they’re applied to the standby databases. SQL Apply takes the redo and transforms it into SQL statements. After running the SQL statements on primary and standby databases, the standby matches the primary database and can be used as a logical standby database. Redo logs are used for keeping the physical standby database consistent with the primary database. Redo information is applied to the standby databases by Redo Apply and controlled by the Apply Services.
- Role Management Service enables you to switch from standby to primary. This is used either for a planned switchover or for the failover due to an outage of one of the servers.
Figure 1 shows a configuration of the Data Guard environment and how the primary and standby servers do not even need to be in the same location. The servers can be in the same data center, down the street, or cities apart from each other, depending on the purpose and need. Having the servers in a different city provides high availability even in the event of a disaster in the location of the primary database. Also in Figure 1, notice the options that are available for the standby server to use, such as reporting, system testing, or even running backups, to take this type of load off of the primary server.
With Redo Apply, the standby database can be opened for read-only queries. The recovery on the standby is canceled and the database is then opened. If you’re using the Active Data Guard option, the Redo Apply can be started again to allow the standby to have real-time data while having the database open. This can be done in sqlplus on the standby database:
You can also use the standby database as a snapshot database that can be updated and used for testing. During this time, no logs are applied. After testing, the snapshot database is converted back to a standby database by using a restore point with a flashback database:
When using a snapshot database with SQL Apply, stopping the apply of the SQL statements is not necessary because the standby is kept synchronized by having the same SQL applied on both the primary and the standby. So, while the standby is still being synchronized, it is also available for queries, running reports, or backups. The commands shown here for setting up the standby for read access are not necessary, but are done during the initial setup to show that SQL Apply is being used: