- How to bulk detach / attach databases in SQL Server - February 2, 2024
- How to enforce password policy in an Oracle database - 4 January 2024
- How do we change the name of the user / schema in an Oracle database - 1 December 2023
Data Guard is Oracle's database solution for high availability and disaster recovery. In the article we will analyze how it works and the types it has.
In its use we define our production base as primary (in either single instance or RAC) and we can have it replicated on the same or different network that we define it as standby (in either single instance or RAC).
A primary base can have up to 30 standby different destinations.
Below is the Data Guard infrastructure diagram:
Data Guard application types
With the physical standby that redo logs are generated with the changes in the primary, transferred and applied to the standbys. The standby bases remain closed as they apply the redo logs from the primary.
It allows us to switchover between primary and standby so that we can easily perform maintenance on the base.
Since we have a complete replica of the database, we will be able to backup the database on standby.
Provides its subclass Active Data Guard which gives us the possibility to have the standby base in read-only while at the same time the redo logs are applied. (Belongs to a different licensing, leading to a different charge)
Its ideal use is for disaster recovery.
The logical standby works by converting the redo logs coming from the primary base into SQL queries, which are executed on the standby bases.
Because we do not use reset of redo logs but application of SQL statements, we have the possibility to have a different structure in the database. We can do read access to the database while simultaneously updating with the latest data.
Its ideal use is for reporting without burdening the production system.
The snapshot standby works like the physical standby by transferring the redos to the standby node. Its difference is that it can be temporarily opened in read/write mode and changes can be made in relation to the primary database.
When the base is opened, a restore point of the base is created with its image so far. At the same time, the redo logs stop being applied. At this point we can make any changes we want to the base.
When we finish and decide to turn it into a physical standby, the restore will be done from the restore point and all redos that have been reserved will be applied. This leads to having the same image as the primary base.
Its ideal use is for testing by developers and troubleshooting.
The protection functions in Data Guard
In some cases a business cannot afford to lose data. In other cases the availability of the database is more important than the possibility of losing data. Finally, some applications require the maximum performance of the base.
Let's look in detail at each function that is available
The maximum availability function offers the maximum data protection possible without affecting database availability. Transactions made on the primary database are not committed if the redo logs have not been received on at least one standby database first.
Zero data loss is guaranteed unless we have an operation failure in both standby and primary at the same time.
It is the default mode. Maximum performance offers the maximum data protection possible without affecting database performance. Transactions made on the primary base become commits when they are written to the redo. The redo logs are applied to the standby bases but asynchronously this time.
This mode provides slightly less protection than maximum availability.
The maximum protection function guarantees zero data loss. To achieve this, the transactions made in the primary base are not committed if they have first been written to the redo logs of both the primary and at least one standby base.
Otherwise, to ensure zero data loss, the primary base stops its operation.