Failover in Oracle9i DataGuard Environments

PURPOSE
-------

This Note shows the different Failover Types and Limitations for Failover in
Oracle 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 note
should 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 changes
from a Primary Database on itself, too. So the Standby Database should be a
constantly 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 Primary
Database, 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 Standby
Database:

Note 180031.1: Creating a Data Guard Configuration
Note 186150.1: Data Guard 9i Creating a Logical Standby Database


The 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 Database
Roles (The Primary becomes a Standby and the Standby becomes a Primary).
Switchovers can be performed arbitrarily, a Failover only once.



2. Failover Types

2.1 Failover on Physical Standby Database

A Failover can be performed when all or most of the information until the
Unavailability of the Primary Database was propageted to the Standby. The
usage of Standby RedoLogs ia a great advantage here. If you have no Standby
RedoLogs available, you will always encounter some Data Loss (depending on
the 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 Database



2.2 Failover on a Logical Standby Database (Oracle 9iR2 only):

A Failover to a Logical Standby Database is also possible, but not recommended
for DR, as there are some restrictions on Logical Standby Databases (see
Oracle 9i DataGuard Concepts and Administration, Chapter 4.1.4 Determine
Support for Datatypes or Tables). So a Failover to a Logical Standby Database
might 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 '';

- 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;