Performing a failover in Data Guard using SQLPlus is an essential task for database administrators. This process ensures that systems remain available and data integrity is maintained during unplanned outages. In this tutorial, we will guide you through executing a failover in Oracle 19c Data Guard using SQLPlus. The failover process allows the standby database to transition to the primary role, ensuring continuity of operations. This operation is critical in disaster recovery scenarios.
Types of Data Guard Failover
Manual DG Failover
Manual failover is performed by the DBA. It includes two types:
Complete Data Guard Failover
- Attempts to minimize data loss by applying all available redo data on the standby, depending on the protection mode.
- This type of failover avoids disabling any standby databases that are not the Data Guard failover target.
- It is the default and recommended type of failover.
Immediate Failover
- No additional redo data is applied on the standby.
- This is a faster DG failover method.
- Requires recreating all standbys that were not the failover target, including the former primary.
Performing the Data Guard Failover Process
In case of a severe issue with the Data Guard primary database, if it is not available for production, we can activate the standby database as the primary production database.
Checking the Database Role and Open Mode
First, log in to the standby server and check the database role and open mode.
SELECT name, open_mode, database_role FROM v$database;
You should see that the DATABASE_ROLE is PHYSICAL STANDBY.
Canceling the MRP Process
Cancel the Managed Recovery Process (MRP) on the standby database.
ps -ef | grep mrp
Then, in SQL*Plus:
SQL> recover managed standby database cancel;
Media recovery complete.
Completing the Data Guard Failover
The following commands will transition the standby to the primary role:
SQL> alter database recover managed standby database finish;
Database altered.
SQL> alter database activate standby database;
Database altered.
The standby database is now the primary database.
Verifying the New Primary Database
Restart the database and verify its role.
SQL> shutdown immediate;
SQL> startup;
Then check the database role again:
SELECT name, open_mode, database_role FROM v$database;
The DATABASE_ROLE should now be PRIMARY.
Conclusion
Performing a Data Guard failover using SQL*Plus is a critical operation that ensures the continuity and availability of your database systems during unplanned outages. By following this tutorial, we have outlined the necessary steps to prepare, execute, and verify the failover process. Properly configuring the standby database and ensuring it is ready for failover is crucial. This guide simplifies the process, allowing for seamless role transitions and maintaining data integrity throughout.
See more on Oracle’s website!
Be Oracle Dataguard Certified Professional, this world is full of opportunities for qualified DBAs!
