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