Failover in Oracle9i DataGuard Environments [ID 227196.1]

PURPOSE-------This Note shows the different Failover Types and Limitations for Failover inOracle DataGuard 9i SCOPE & APPLICATION-------------------The contents of this note are valid for 9iR1 (9.0.1) and 9iR2 (9.2.0)DataGuard Configurations, also for Logical Standby Databases. This noteshould help you to understand and describe what a failover is, what Types of Failovers are available, and how to perform them.FAILOVER ON ORACLE DATABASES PROTECTED WITH ORACLE DATAGUARD============================================================ 1. What is a Failover ?Oracle DataGuard is a Disaster Recovery (DR) Solution for Oracle Databases. The basic idea behind DataGuard is a Standby Database performing all changesfrom a Primary Database on itself, too. So the Standby Database should be aconstantly updated copy of a Primary Database. This copy can either be a mounted and in Recovery mode placed (Physical Standby) or an open Database(Logical Standby - Oracle 9iR2 only). In case of a loss of the PrimaryDatabase, the Standby can take over the Primary Role and act as a Primary Database. Also for planned outages of the Primary Database (eg. Hardware upgrades,...), the Standby Database can takeover the Primary Role and switchback to the Standby Role once the Upgrade is complete. Please refer to the following notes for information on creating a StandbyDatabase: Note:180031.1: Creating a Data Guard Configuration Note:186150.1: Data Guard 9i Creating a Logical Standby DatabaseThe Difference between a Switchover and a Failover is, that after a Failover, the Standby Database becoming a Primary now, cannot switchback to become a Standby Database again. In Opposition a Switchover exchanges the DatabaseRoles (The Primary becomes a Standby and the Standby becomes a Primary). Switchovers can be performed arbitrarily, a Failover only once.2. Failover Types2.1 Failover on Physical Standby DatabaseA Failover can be performed when all or most of the information until the Unavailability of the Primary Database was propageted to the Standby. Theusage of Standby RedoLogs ia a great advantage here. If you have no StandbyRedoLogs available, you will always encounter some Data Loss (depending onthe Changes since the latest LogSwitch). To perform a Failover just follow these steps:- The Primary Database is down for any reason- Verify a Standby RedoLog is in use for Primary current Online RedoLog. You   then find in the ALERT.LOG of the Standby something like:    RFS: Successfully opened standby logfile 4:'C:\ORACLE\ORADATA\PRIMARY\STBY01.LOG'- If this is the case run the following commands:   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;    This cancels the normal managed Recovery. To get the Standby RedoLog Information  is still required. Therefore issue this command:   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;   - If a Standby RedoLog is not used for any reason, then run this one:   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH SKIP STANDBY LOGFILE;  Please keep in mind that this one causes (Minimal) Data Loss as the latestet  information from the down Primary Database is not available anymore. - Once this is complete (This performs a complete Recovery or incomplete   Recovery until the last SCN included in the latest archived Log available at   the Standby), you can now make the Standby Database a Primary:     SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY   WARNING: This will only succeed if the correct RECOVER FINISH-statement was          issued before. If you forgot the 'SKIP STANDBY LOGFILE' although you         have no Standby RedoLogs, the COMMIT to Switchover will fail with the         error that more Media Recovery is required here.         - If the COMMIT TO SWITCHOVER fails for any reason you have to use the ACTIVATE  command which forces the Failover (and may cause Data Loss !!)     SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;- Shutdown and restart the Databse after this command ended successfully:   SQL> SHUTDOWN IMMEDIATE   SQL> STARTUP   - Now the Standby is open as a new Primary Database2.2 Failover on a Logical Standby Database (Oracle 9iR2 only):A Failover to a Logical Standby Database is also possible, but not recommendedfor DR, as there are some restrictions on Logical Standby Databases (seeOracle 9i DataGuard Concepts and Administration, Chapter 4.1.4 Determine Support for Datatypes or Tables). So a Failover to a Logical Standby Databasemight always cause a Data Loss.To perform a Failover to a Logical Standby Database, perform the following steps:- The Primary Database is down for any reason- Determine if all Archive Logs have been transfered from the Primary Database  to the Logical Standby Database (Either look for them in the directory and   query the DBA_LOGSTDBY_LOG-view).- Copy all missing ArchiveLogs and the Online RedoLogs from the Primary   Database to the Logical Standby Database if possible.- Register all copied or not already registered LogFiles to the Logical Standby  Database:     SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE '<Patch/Logfilename>';- Turn off Logical Apply Delay Interval (if set):   SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;   SQL> EXECUTE DBMS_LOGSTDBY.APPLY_UNSET('APPLY_DELAY');   SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;- Ensure all registered Logfiles have been applied:   SQL> SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS;   - Stop Logical Apply Services:   SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;   - Activate the Logical Standby Database:   SQL> ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE;