Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Data Guard Failover to physical standby Tips

Oracle Tips by Burleson Consulting

December 9, 2011

Oracle Data Guard Failover to physical standby

Data Guard Switchover/failover to standby
 
The standby database will be activated to serve as the primary database at some point in its life cycle. There are normally two situations when this operation will be performed: a planned outage for maintenance of the primary database or disaster recovery. A switchover operation occurs when a standby database is transitioned into the primary database role and the primary database into the standby database role. In the switchover operation, no data is lost.
 
The switchover operation is performed for maintenance of the primary database. In case of an unplanned outage on the primary site, the standby database will be activated as the primary database. This is called failover. There are two types of failover operations: Graceful or “no-data-loss” failover and Forced or “minimal-data-loss” failover.  Once the standby database is transitioned into primary database status in either switchover or failover, the life of the database as the
standby ends and its service as the primary database begins.

 

Failover Operation

A failover operation is a true disaster recovery operation. A failover operation should only be considered when all of the alternative options for primary database recovery are not feasible. This section will detail the step-by-step procedure for failover operation to the physical and logical standby database.

Physical Standby Database failover with Data Guard

As previously mentioned, the physical standby database is an exact copy of the primary database on a block-per-block basis. The archived redo logs on a physical database are applied using the physical rowed values. Hence, all the segments in the primary and the standby database must be the same.
 
The physical database can be in recovery mode or read-only mode, but it cannot operate in both modes at the same time. When in recovery mode, the archived redo logs from the primary database are applied to keep it current and in sync with the primary database. When in read-only mode the standby database can be used for reporting.
 
The physical standby database in read-only mode can never be open for updates, because updates in a standby database will create a different rowed value; therefore, further recovery will not be possible.
 
In Oracle8i, there are two types of recovery mode supported: managed recovery and manual recovery. In Oracle9i and beyond , only managed recovery mode is mentioned; however, a physical standby database can still be put in manual recovery mode the same way that is was done in Oracle8i.
 
In managed recovery mode, the archive redo logs from the primary database are transferred automatically to the standby database provided that a Net8 connection is established between the primary and the standby database. The logs are then applied to the standby database by the log apply services; therefore, there is no need for manual intervention. The details of the processes involved in the transfer and application of redo logs to the standby database are given in Chapter 2, “Data Guard Architecture”.
 
The manual recovery mode of Oracle8i required the manual transfer of archived redo logs from the primary to the standby database.  The application of the logs was achieved manually by using SQL*Plus commands. This is a useful option if a Net8 connection between the primary and standby databases is not possible.  Due to the significant number of manual tasks involved in this mode, it is not an obvious choice for disaster protection.
 
The read-only mode is used for ad-hoc reporting and can be very useful for offloading some of the reporting tasks from the primary database to the standby database. In this mode, the archived redo logs cannot be applied to the standby database, and at this point, the primary and the standby databases diverge. If there is only one standby database in the configuration, the DBA should be very protective about the archived redo logs as long as the standby database is in read-only mode.
 
The mode of operation of standby databases can be changed between recovery mode and read-only mode and vice versa; however, the standby database can only be in one mode at a time.
 
The physical standby database performs better than the logical standby database because it uses media recovery to apply archived redo logs. Moreover, there are fewer limitations on a physical standby database compared to a logical standby database.

 

Failover to a Physical Standby Database

A failover to a physical Oracle instance is considered a ‘graceful’ failover. This is similar to a switchover operation except that the original primary database will have to be discarded after the failover. Depending on the protection mode of the primary database, the graceful failover can recover all data resulting in minimal or no data loss. The next step is to study the overall procedure for a graceful failover to a physical standby database. Use the following procedure when the data protection mode is in MAXIMUM PROTECTION or MAXIMUM AVAILABILITY:

  • Before starting the failover operation, update the initialization parameter file on the physical Oracle instance that is used for failover.  Include other standby databases in the Data Guard environment used as the archival destination so that after the failover, the redo data can be archived from the new primary database to other standby databases.
     

  • In order to initiate the failover operation, the target physical Oracle instance should be placed in MAXIMUM PERFORMANCE data protection mode using the following statement:

ALTER DATABASE SET Oracle instance TO MAXIMIZE PERFORMANCE.

  • Allow the MRP to finish applying the redo data from the archived redo log file and the standby redo log files. If the database was running in MAXIMUM PROTECTION or             MAXIMUM AVAILABILITY mode prior to loss of the primary database, there should be no requirement to manually transfer and register any archived or partial online log file from the primary database. Issuing the following statement will cause the MRP to terminate when it has applied all the available redo data:

Failover to a Physical Standby Database

ALTER DATABASE RECOVER MANAGED Oracle instance FINISH;

  •  If the standby redo log files on the target physical Oracle instance are corrupt, or it is not desirable to apply the changes that are in the standby redo log file, MRP can be completed by skipping the standby redo logs by using the following statement:

ALTER DATABASE RECOVER MANAGED Oracle instance FINISH SKIP STANDBY LOGFILE;

  • One such scenario is when the DBA want to recover from application or user error and the data within the standby log file contains the error and the DBA does not want to post it in the database.
     

  • Once the MRP has finished applying all the redo data, issue the following statement to transition the physical Oracle instance to the primary role. Status of managed recovery process can be obtained from the v$managed_standby view or from the alert log file.

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

  • On all other standby databases, both physical and logical, register the standby redo log file from the new primary database. The following statement will register a log file with a database, and if the recovery process is running on a database, the redo data from the log file will be applied:

ALTER DATABASE REGISTER LOGFILE ‘filespec’;

  • Finally, restart the new primary database to enable the read/write operation. Update the Oracle Net configuration so that the new primary database will start serving the requests from applications.

Failover to a Physical Standby Database

If the primary database was running in MAXIMUM PERFORMANCE mode prior to failure, the procedure of failover is slightly different. In this case the following issues should be evaluated:

  • Since the protection mode is MAXIMUM PERFORMANCE, the physical Oracle instance may not have standby redo logs configured. After the loss of the primary database, there may be an archive gap on the standby database. These gaps should be resolved before transitioning the standby into the primary role.
     

  • If possible, manually copy the online redo logs from the original primary database to the target physical Oracle instance and register the online redo log files using the ALTER DATABASE statement. After registering the online redo log files, check the alert log file to verify the recovery process has applied the redo from these log files.

The failover to a physical Oracle instance is similar to a switchover operation. Oracle provides the functionality of converting a physical Oracle instance into the primary database without considering any of the above mentioned data recovery options. This is kind of intentional data loss failover and can be achieved using the following statement:

ALTER DATABASE ACTIVATE STANDBY DATABASE;


If a standby log file has been created on the physical standby database, issuing the above-mentioned statement will result in an Oracle error. The activation of the physical Oracle instance can be force by skipping the standby log files:

ALTER DATABASE ACTIVATE Oracle instance SKIP STANDBY LOGFILE;

 


The above text is an excerpt from the book: Oracle Data Guard Handbook


��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.