A few weeks back I had a client try out Oracle Data Guard (ODG) and completely fall in love with it. However, he said that he couldn’t switch roles, so we tried to help him figure out how to do this. We finally found a solution.
Oracle Data Guard (ODG) is a technology that is a major part of Oracle’s disaster-recovery solution. If you’ve been following my blog, you know that I’ve been working with Oracle for about a year and a half now. One of the things I’ve done is try to understand how ODG works and how it fits into the disaster recovery system that Oracle has.
The Data Guard is a critical component of your Oracle database. But, you usually don’t know a lot about it until you have a big performance problem. Then, you have to go deeper to solve the actual problem. And, that’s where this blog post comes in – to help you understand what Data Guard is, how it works, and how you can use it to solve your problems.
Roll management services
In the Data Guard configuration, the database operates in one of two mutually exclusive roles: 1. Principal Role 2. Backup role (physical, logical, snapshot subtype) You can use role management services to dynamically change primary and backup roles as part of a planned transition called a failover operation or after a database failure as part of a failover operation. For example, you can perform a failover operation to switch the primary database to the backup database role and switch the backup database to the primary database role to perform ongoing maintenance tasks.
Role Transitions: Switching and protection against interferences
Switching
You can use the switch function to switch the role of the primary database to one of the available backup databases. The selected backup database becomes the primary database and the original primary database becomes the backup database. There is no need to recreate the databases involved in the failover process. After a successful failover, there is no data difference between the original database and the new primary database.
Tolerance of errors
You invoke failover when an error occurs in the primary database and there is no way to recover the primary database in time. In the case of a failover, the standby database takes over the role of the primary database. You call a failover operation on the standby database that you want to switch to the main. Message: You can enable fast failover to automatically switch to a new database when the primary database becomes unavailable. When Fast Failover is enabled, the broker detects the need for a failover and initiates a failover to the specified target backup database automatically, without DBA intervention and without data loss See Automatic, without DBA intervention and without data loss for more details.
Switching
The failover operation reassigns the primary database to the standby database and reassigns the standby database to the primary database without resetting the replay logs of the new primary database. The primary database must be shut down and restarted at the beginning of the failover process. The standby physical database is not stopped or restarted during failover. If the physical backup database is in active data protection mode, it is shut down for the duration of the migration and reopened when the failover is complete, but it is never completely shut down, so a restart is not required. If the failover operation is for a standby logical database, it is not necessary to shut down and restart the primary database or any of the standby databases. Logical backup databases do not need to be shut down and restarted. Message:
If necessary, the broker automatically starts and stops all but one instance of the Real Application Clusters (RAC) environment for the underlying database. If the broker is not used, this must be done manually. Secondary RAC instances in the backup physical database do not need to be shut down during failover. They can remain in the mounted or active status of Data Guard. The primary y gg further assumes that only one instance is running during failover.
Switching: to
As an example, let’s assume the primary database is in San Francisco and the physical backup database is in Boston. Failover starts only in the primary database and ends in the target standby database. You can connect to any database in the configuration via DGMGRL to execute the SWITCHOVER command.
Switching: after
After the switch, each database has a different role than before the switch. In this example, Boston is now the primary database and San Francisco is now the backup database. Since Data Guard does not automatically switch sessions from one database to another, active sessions must be reconnected for each system. For more information about configuring automatic session recovery methods, see the lesson on Advanced Client Login in Data Guard.
Performing a failover using Enterprise Manager
When Enterprise Manager is used to switch: a. Verifies that the primary database and the backup database are not faulty and that broker control for the primary database is enabled and online b. Automatically closes all active sessions connected to the master database during the switchover. First change the primary database role to the backup database role, then change the backup database role to the primary role. Opens the target database if it is a mounted physical backup database and restarts the old master database. To initiate a failover with Enterprise Manager:
- On the Data Guard page, select the backup database that you want to make the primary database.
- Press Toggle. The login information page may appear if you are not logged into the backup database. The confirmation page for switching Data Guard is displayed.
- Click the Browse Sessions link in the primary database to view the active sessions.
- Press Yes to continue or No to cancel. You cannot cancel a circuit once it has started.
The Data Guard Changeover Processing page shows the progress of the changeover process:
- Switching roles between the primary database and the backup database (If the target of the switch is a physical backup database and it is mounted, it will be opened without rebooting. The old primary database will be restarted).
- Wait for the Data Guard Broker to complete the initialization tasks required to change the database roles.
You can view the alert logs for the primary and backup databases by clicking on the corresponding View Alert Log links. A new browser window opens with the contents of the alarm log. When the switchover process is complete, return to the Data Monitoring page.
Check databases before
The VALIDATE DATABASE command performs a complete set of database checks before switching roles. The checks use the information available in the various Oracle Data Guard views and in the repository for automatic diagnostics. The VALIDATE DATABASE command provides summary information about the database and reports any errors or warnings found. VALIDATE DATABASE VERBOSE displays everything in the summary, plus the items that were checked. The VALIDATE DATABASE command must be executed for both the primary and backup databases before a switch can be made. An example of detailed output for a physical backup database looks like this: DGMGRL> validate database verbose database role :
Physical standby database Primary database: boston Switch availability: Yes Fault tolerance preparation : Yes (Main Race) Performance Information : Database Instances Threads boston 1 1 london 1 1 Temporary Table Space File information: boston TEMP files: 3 london TEMP files: 3 flashback database status: boston : Outside London: From the online database to the move in progress: Boston: No, London: No reservation applicable-Relevant information : Bring in the report: Delay in execution of work :
Application time 0 seconds: 0 minutes Transport information : Transportation included: Yes Separation status : No enable transport delay: 0 seconds Transport status: Deleted success log files: Boston standby redo log files: The read logs have been deleted: Removes the configuration of the current log file groups: Thread # Online Redo Log Groups Standby Redo Log Groups (boston) (london) 1 3 2 Future Log File Groups Configuration : Thread # online redo log groups standby redo log groups (london) (boston) 1 3 0 Current size of configuration log files : Thread # Smallest Online Redo Smallest Standby Redo Log File Size Log File Size (boston) (london) 1 50 MBytes 50 MBytes Apply-Related Property Settings : Property value boston value london DelayMins 0 0 ApplyParallel AUTO AUTO Transport-related property parameters :
Property boston value london value LogXptMode ASYNC ASYNC Dependency DelayMins 0 0 Binding optional optional MaxFailure 0 0 MaxConnections 1 1 ReopenSecs 300 300 NetTimeout 30 30 RedoCompression DISABLE DISABLE LogShipping ON ON Automatic Diagnostic agnostic Repository Errors : Error boston london No log operation NO NO Corruption control file NO NO SRL group not available NO NO System data file missing NO NO System data file corrupt NO NO System data file offline NO NO User data file missing NO NO User data file corrupt NO NO User data file offline NO Block damage found NO NO NO
Switching with DGMGRL
After verifying the requirements for performing the switchover operation, execute the SWITCHOVER command to perform the switchover operation. DGMGRL> SWITCHOVER TO ‘london’ ; Switch NOW, please be patient…. The operation requires a connection to a London instance on the London database Connecting to a London instance… Connected as a SYSDG. New primary database to be opened in London….. The operation requires running a Boston instance on the Boston database…. An ORACLE instance is running. The database is docked. The changeover has been successful, the new prefix is London. The Data Guard Broker performs the following operations:
- Verifies that the primary database and the target backup database are in the correct state.
- Disable all instances, if necessary
- Change of role between the master database and the backup database
- Update the broker’s configuration file with the new role information.
- Restarting a new backup database (old primary database) while Redo Apply is active
- Open a new primary database in read/write mode Open a new primary database in read/write mode and start the transport redo services.
Preparing for the changeover to the euro with SQL
Before running the SWITCHOVER command, make sure the backup redo log files are configured on the primary database. The configuration of the backup redo log must be identical on the primary database and on all physical backup databases. Although redundant readback logs are not used when the database plays the primary role, it is recommended to configure redundant readback logs on the primary database to be prepared for a possible failover. If you switch to a physical backup database, you must ensure that the primary database is open and that Redo Apply is active on the backup database. If you switch to a logical backup database, make sure that the primary and backup database instances are open and that SQL Apply is active. The VALID FOR VALID_FOR element of the LOG ARCHIVE DEST LOG_ARCHIVE_DEST_n parameter can be configured for both the primary database role and the backup database role. By creating separate goals for each role in advance, there is no need to adjust the settings during the changeover.
Making changes with SQL
1. Make sure the target backup database is ready: SQL> ALTER DATABASE SWITCHOVER TO ‘london’ VERIFY ; The database has changed. 2 Initiate failover to the primary database: SQL> ALTER DATABASE SWITCHOVER TO ‘london’; The database has been modified. 3. Open a new master database. 4. Mount the new physical backup database. 5. Run Redo Apply for the new physical backup database. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; modified database If you are not using the Data Guard broker, you must run several commands to switch. Each database involved in the failover must be executed accordingly (MOUNT for the standby database, OPEN READ WRITE for the primary database) by establishing connections to each instance individually. If the settings file or server settings file was not created for a role change, you may need to adjust the settings accordingly.
Considerations for switching to a logical backup database
- Unlike migration to a physical backup database, migration to a logical backup database does not require shutting down the primary database.
- When you switch to a standby logical database, you do not need to shut down any applications connected to the current primary database or the standby logical database, because neither database is shut down during the switch. However, because sessions on the old primary database may stop working after the failover process is complete and database protection is enabled, you should terminate these open sessions now. Database security prevents users from making changes to the logical backup database.
- If you are migrating to a logical backup database, keep in mind that you may not have all the data that was in your primary database if the logical backup database is only a subset of the primary database.
- Switching to a logical standby database removes and disables all physical and snapshot standby databases in the configuration. You must recreate the physical backup databases from the new backup of the primary database before you can reactivate them.
Situations that prevent switching
You cannot make the change if :
- Archived redo log files are not available: If there is a gap in the archived redo log files of the backup database, you cannot switch to this backup database.
- Reclaiming points is imperative: When you switch to the backup database, you always switch to the current state of the primary database. You can’t go back to a time in the past.
- The master database has not been opened and cannot be opened: The failover is started on the primary database while it is in the open state. If you cannot open the primary database, switching is not possible.
Tolerance of errors
You invoke failover when an error occurs in the primary database and there is no way to recover the primary database in time. During a failover operation, the primary database is disconnected from the Data Guard environment and the backup database takes over the role of the primary database. The switch to the backup database is a one-way process. You cannot go back and restore the database to its previous role as a backup database. Therefore, only initiate the changeover in an emergency. It is not always necessary to switch to a backup database. In some cases, it may be faster to restore the master database. Most failures can be resolved in the primary database within a reasonable time. In a tipping operation:
- It is assumed that the original primary database has been lost. (If desired, you can restore or re-create the original primary database as a new backup database. You can then perform a failover operation to restore the databases to their original roles).
- Standby databases that are online during a failover operation, but not involved in a role transition, do not need to be stopped and restarted unless they are ahead of the failover target due to differences in application latency situations. In that case, they may need to be restored at the time the backup system became the primary system or was newly created.
Error types
Manual failover is possible via Enterprise Manager, DGMGRL or SQL. There are two types of manual tipping:
- Finish up: The maximum amount of redo data for the protection mode is restored. With this type of failover, the broker avoids disabling backup databases that are not the purpose of the failover. Full tilt is the standard and recommended type of tilt.
- Right away: After a switchover, additional redo data is not transferred to the backup database. This is the fastest way to switch. After an immediate failover, you must recreate or restore the original primary database and any backup databases that were not the target of the failover.
Message: You should always try to perform a full tilt. Perform an immediate failover only if a full failover fails. Depending on the attributes of the redo transport service target, a full failover may occur without data loss, while an immediate failover usually results in data loss. You can configure a fast failover run so that the broker automatically switches to the selected backup database if the primary database is lost.
Integrated safety considerations
During a failover, the standby database takes over the role of the primary database and the old primary database can no longer participate in the configuration. Depending on the protection mode in which the old primary database operated before the failover, little or no data may be lost during the failover. Failover is generally only used when the primary database is no longer functional and there is no way to successfully failover or recover it within a reasonable time. The specific actions taken during a failover depend on this:
- Whether the failover operation involves a logical or physical backup database.
- Status of the configuration at the time of switchover
- Specific SQL commands used to initiate failover
Performing a failover using Enterprise Manager
Perform a failover only if there is a software or system failure that results in the loss of the underlying database. A failed primary database is disabled by the broker and must be restored or re-created. The backup database then takes over the role of the primary database. To initiate a failover with Enterprise Manager: 1. On the Data Guard page, select the backup database that you want to make the primary database. 2. Click on Failover. 3. On the Data Guard Failover Confirmation page, specify the type of failover you want to perform:
- Finish up: All available redos are applied to the backup database. Oracle recommends specifying this type of failover.
- Right away: The backup database is not overwritten with additional data, so there is no data loss. Immediate tilt should only be used when full tilt is not possible.
4. Select the toggle option and press Yes to confirm the toggle operation. After clicking Yes, the Switch Processing page displays the progress of the switch. You cannot cancel this process once it has started.
Performing a failover to a physical standby database
During a failover, the selected standby database takes over the role of the primary database. The failover target (physical standby database) is restarted. When the process is complete, the Data Guard summary page displays the updated configuration. After a full or immediate failover, the primary database and some backup databases may need to be restored or recreated. To restore the database, click the Database needs to be restored link. Then click the Restore button on the General Properties page. Enterprise Manager restores the database as a backup database to the new primary database. Message: The flashback database must be enabled on the database before failover, and the required flashback logs must be available on that database for successful recovery. In addition, the restored database and the new primary database must have a network connection. The Flashback database will be discussed later in this course.
Performing a crash on a logical backup database
When you perform the failover of the logical standby database, all physical standby databases in the configuration are permanently disabled after the failover and can no longer be used. These databases must be re-created from the new master database.
Performing a manual switchover with DGMGRL
1. Run the FAILOVER command to start a failover operation to the backup database: DGMGRL> FAILOVER TO ‘london’ [IMMEDIATE] ; 2. Reset the security mode (if necessary). 3. Restore or recreate the old master database to serve as a backup database in the configuration. 4. restore or recreate other backup databases that are disabled in the configuration. After determining that the primary database cannot be restored in time, run the FAILOVER command to perform a failover. If you specify the IMMEDIATE option, no attempt is made to apply an unapplied repeat received. The Data Guard broker performs the following operations to completely bypass the error: 1. Checks that the target backup database is enabled. 2. Stop Redo Apply after all redo data not yet applied has been applied to the target backup database. 3. Moves the target standby database to the main database role:
- Open a new master database in read/write mode Open a new master database in read/write mode.
- Determine if the backup databases should be restored or recreated.
- Introduction of reprocessing services in transport.
For immediate switching, the broker does not wait for all available redo data to be transferred (as described in step 2).
Restart of restricted databases with DGMGRL
If the database can be restored, it has the following status after a full failover: ORA-16661: The backup database must be restored Message: To restore the database, flashback must be enabled on the database for failover and the flashback logs must be available. To restore the database : 1. Start the database instance and connect the database. 2. Call DGMGRL and connect to the new primary database. 3. Use the REINSTATE DATABASE DGMGRL command to restore the database. DGMGRL> DATABASE boston REINSTATE ; If the database needs to be rebuilt, it has the following status after a failover: ORA-16795: The backup database must be created again You must recreate the backup database from a copy of the primary database and then reactivate it using the ENABLE DATABASE DGMGRL command. DGMGRL> DATABASE ENABLE boston ;Oracle RAC has been one of the most commonly used Oracle database technologies. However, it is often misunderstood by people who do not have any experience with it, which is why I’d like to explain the basics of Oracle RAC and try to give the reader a chance to learn how to switch roles in Oracle Data Guard. With that said, let’s get started.. Read more about upgrade to 19c with data guard and let us know what you think.
Related Tags:
how to disable data guard in oracle 12cupgrade to 19c with data guarddata guard upgrade 11g to 12coracle data guard upgradeupgrade physical standby database from 12c to 19cdatabase data guard broker operation needs instance count to be reduced to 1,People also search for,Privacy settings,How Search works,how to disable data guard in oracle 12c,upgrade physical standby database from 12c to 19c,database data guard broker operation needs instance count to be reduced to 1,upgrade to 19c with data guard,how do i upgrade my physical standby database to 19c,data guard upgrade 11g to 12c,oracle data guard upgrade,upgrade oracle database from 11g to 19c using the rman backup
