How to srart or stop Redo apply on physical Standby database

Start Redo Apply on Physical Standby database:

To start log apply services on a physical standby database, ensure the physical standby database is started and mounted and then start Redo Apply using the SQL

- To start a foreground session that recovers a database using the archived redo
log on the physical standby database, issue the SQL statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

Note: If you started a foreground session, by default, control is not returned to the
command prompt until recovery is canceled by another session.

- To start a background process that recovers a database using the archived redo log on the physical standby database, you must use the DISCONNECT keyword on the SQL statement

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Data Guard Switchover/Failover Scenarios

Switchovers Involving a Physical Standby Database:

Step 1. On primary database check the status:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS;
-----------------
TO STANDBY
1 row selected

It should give: TO STANDBY or SESSION ACTIVE

Step 2. Initiate the switchover on the primary database.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
(If Switchover_status is to_standby)

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
(If Switchover_status is session active)

Step 3. Shut down the former primary instance, and restart and mount the database:

SQL> SHUTDOWN IMMEDIATE;SQL> STARTUP MOUNT;
At this point in the switchover process, both databases are configured as standby databases

On the target physical standby database:

Step 4.
On standby database check the status:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

It should give:
TO PRIMARY or SESSION ACTIVE

Step 5.
Switch the target physical standby database role to the primary role.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
(If Switchover_status is to_primary)

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
(If Switchover_status is session active)

Step 6.
If the standby database has never been opened before in readonly mode then open the database:
SQL> Alter database open; (10gR2)

Or else: (10gR1)
SQL> Shutdown immediate;
SQL> Startup;

Step7. If necessary, restart redo apply service on standby database( if you need details check How to start or stop redo apply heading)

Step 8. Perform a log switch on primary database

SQl> Alter system switch logfile;


Failovers Involving a Physical Standby Database:


Step 1:
Identify and resolve any gaps in the archived redo log files.

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 90 92

In this example the gap compressed in the seq# 90,91 and 92.

Copy all the missing archived redo log files from primary and register it to the standby.

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

- Repeate the step until all gaps are resolved

Step 2
Copy any other missing archived redo log files and register it to standby database.

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#)
2> OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
THREAD LAST
---------- ----------
1 100

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

After all available archived redo log files have been registered, query the
V$ARCHIVE_GAP to verify no additional gaps were introduced

Step 3
Initiate a failover on the target physical standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

Step4
Convert the physical standby database to the primary role.

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Step 5
If the standby database has never been opened before in readonly mode then open the database:
SQL> Alter database open;
Or else:
SQL> Shutdown immediate;
SQL> Startup;

Data Guard Related Questions

1. What is the difference between LGWR SYNC and ASYNC in Oracle DataGuard configuraton?

LGWR is an attribute of the LOG_ARCHIVE_DEST_n parameter which is used to specify the network transmission mode.


Specifying the SYNC attribute (which is the default), tells the LGWR process to synchronously archive to the local online redo log files at the same time it transmits redo data to archival destinations.


Specifically, the SYNC atrribute performs all network I/O synchornously in conjunction with each write operation to the online redo log file. Transactions are not committed on the primary database until the redo data necessary to recover the transactions is received by the destination.


The ASYNC attribute perfoms all network I/O asynchronously and control is returned to the executing application or user immediately. When this attribute is specified, the LGWR process archives to the local online redo log file and submits the network I/O request to the network server (LNSn process for that destination, and the LGWR process continues processing the next request without waiting for the network I/O to complete.




2. What happens if the network between the Primary and Standby [database] is lost with LGWR SYNC and ASYNC?



This is dependent upon the database mode you have set. If you have set Maximum Protection, you have chosen a configuration that guarantees that no data loss will occur. You have set this up by specifying the LWGR, SYNC, and AFFIRM attributes of the LOG_ARCHIVE_DEST_n parameter for at least one standby database.


This mode provides the highest level of data protection possible and to achieve this the redo data needed to reocver each transaction must be written to both the local online redo log and the standby redo log on at least one standby database before the transaction commits. To ensure data loss cannot occur, the primary database shuts down if a fault (such as the network going down) prevents it from writing its redo stream to at least one remote standby redo log.



If you have set the Maximum Availability mode, you have chosen a configuration that provides the highest level of data protection that is possible without compromising the availablity of the primary database.


Like the maximum protection mode, a transaction will not commit until the redo needed to recover that transaction is written to the local online redo log and to at least one remote standby redo log. Unlike maximum protection mode, the primary database does not shut down if a fault prevents it from writing its redo stream to a remote standby redo log. Instead, the primary database operates in maximum performance mode until the fault is corrected and all gaps in redo log files are resolved. When all gaps are resolved, the primary database automatically resumes operating in maximum availabitly mode. This guarantees that no data loss will occur if the primary database fails, but only if a second fault does not complete set of redo data being sent from the primary database to at least one standby database.



If you have set the Maximum Performance mode (the default), you have chosen a mode that provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by allowing a transaction to commit as soon as the redo data needed to recover the transaction is written to the local online redo log. The primary database's redo data stream is also written to at least one standby database, bu that the redo stream is written asynchronously with respect to the commitment of the transactions that create the redo data.
The maximum performance mode enables you to either set the LGWR and AYSNC attributes, or set the ARCH attribute on the LOG_ARCHIVE_DEST_n parameter for the standby database destination. If the primary database fails, you can reduce the amount of data that is not received on the standby destination by setting the LGWR and ASYNC attributes.




2. What happens if the standby database is shutdown with LGWR SYNC and ASYNC?
This goes back to what mode you have chosen. See the answer to question 2 for the details




3. If LGWR SYNC or ASYNC is deployed, what process(es) bring(s) the standby database back into sync with the primary [database] if the network is lost and is then restored? How does it do it?


Again, this is dependent upon the mode you have chosen for you database. The LGWR process (and possibly the LNSn process if you have multiple standby databases) is responsible for closing the gap.




When the network to the standby is lost with SYNC or ASYNC, where is the information queued and how is it retransmitted once the network has been re-established?






This implies that your database has been set to either maximum availability or maximum performance mode. You cannot use the ASYNC attribute with maximum protection mode. The information is queued in the local online redo log and the LGWR (and the LNSn) process will transmit the data to the standby database's online redo log file to close the gap once the network connectivity has been re-established.




Gap recovery is handled through the polling mechanism. For physical and logical standby databases, Oracle Change Data Capture, and Oracle Streams, Data Guard performs gap detection and resolution by automatically retrieving missing archived redo log files from the primary database. No extra configuration settings are required to poll the standby database(s) to detect any gaps or to resolve the gaps.




The important consideration here is that automatic gap recovery is contingent upon the availability of the primary database. If the primary database is not available and you have a configuration with multiple physical standby databases, you can set up additional initialization parameters so that the Redo Apply can resolve archive gaps from another standby database.
It is possible to manually determine if a gap exists and to resolve those archive gaps.




To manually determine if a gap exists, query the V$ARCHIVE_GAP view on your physical standby database. If a gap is found, you will then need to locate the archived log files on your primary database, copy them to your standby database, and register them.

Data Guard Introduction for 9i - tutorial presentation

datagaurd intro 9i oracle - Upload a Document to Scribd

Data Guard config on Sun cluster

Reference: http://all-about-oracle.blogspot.com/2006/10/creating-data-guard-configuration-on.html

Oracle9i Data Guard Switchover/Failover Best Practices

Reference: http://whitepapers.zdnet.com/abstract.aspx?docid=115061

( White paper : pdf Available for download )

Data Guard - Standby Database Failover

Reference: http://www.rampant-books.com/art_kumar_dg_broker_architiecture.htm

Figure1: Diagram showing relation between layers of Broker Management model.

Figure 2: Diagram showing server side components of Data Guard Broker.

Data Guard Broker Architecture

This article is an excerpt from Oracle Dataguard — Standby Database Failover Handbook by Rampant TechPress. - By Bipul KumarDBAzine

Data Guard Broker is an assisting utility provided with Oracle Database Server software to facilitate the creation and management of data guard configurations. (Note: Data Guard Broker is available with Oracle 9i Release 2 or later.) Data Guard broker consists of server side and client side components implementing a distributed computing architecture. The server side component includes a Data Guard Monitor (DMON) process and configuration files. The client side component includes Data Guard Manager and Command Line interface.

Data Guard Broker manages a data guard configuration using a broker management model. The broker management model is a hierarchal structure comprised of Configuration, Site and Database resources. Broker can manage all three layers of the management model. We will see more about the broker management model later in this section.

Data Guard Broker can manage the primary database and up to nine standby databases in one configuration. These nine standby databases can be a mixture of physical and logical standby databases. You can create a standby database using Data Guard broker, or add an existing standby database to a configuration.

When you create a standby database using broker, broker takes care of the entire set of supporting files; e.g., SPFILE, Oracle Net configuration files, and so on. If you need to add an existing standby database to a data guard configuration, you must configure the SPFILE and Oracle Net files manually before adding the standby database to a configuration. If you plan to use Data Guard Broker for the management of standby databases, you must use the persistent server parameter file (SPFILE) to hold the initialization parameters on all the participating databases.

Some advantages of using Data Guard broker are as follows:
* It is a centralized management tool that can be used to manage the entire configuration using a GUI or CLI interface.
* It provides an extensive health check mechanism for the primary database, standby databases and supporting services in the configuration.
* It reduces the complexity of role management services. Switchover and Failover operations can be performed from a centralized console.
* It can be used to gather useful statistics to fine tune the log transfer and log apply services.
(Note: In Oracle 9i, Data Guard Broker cannot be used with Oracle Real Application Cluster. RAC support is provided with Oracle 10g.)
* The following subsections will explain the broker management model and the broker components in brief.

Configuration, Sites and Resources
Broker management model is a three layer hierarchical framework. The logical units of the management model are Configuration, Site and Database Resources. These three layers bear a parent-child relationship as shown in the fig 1.

A Database resource is the smallest unit managed by Data Guard Broker. In the broker management model, a database resource represents an instance of the primary or standby database. In addition, a service; e.g., log apply service can be classified as a database resource in context of a management model. Site in this model is a collection of database resources. In other words, a site is equivalent to a host machine on which a database instance is running. Configuration is the largest unit in the broker management model consisting of the primary site and one or more standby sites.

Data Guard Broker can manage all three layers of the management model. Any operation performed on a higher level of the model is applicable to all the child objects of that layer. For example, if the status of a site is changed to offline, then all the resources under that site will be offline. We will learn more about the management model in Chapter 7.

Server Side Components of the Broker
The server side component of Data Guard Broker includes the Data Guard Monitor (DMON) process and a configuration file. The Data Guard Monitor is a background process that runs on each of the sites managed by Data Guard Broker. The configuration file is a binary file that contains the properties and status of all the sites in a configuration. The DMON process is responsible for managing a consistent copy of the configuration file across the entire configuration. The DMON processes in a configuration communicate over Oracle Net to manage the role management and log management services. In addition, the DMON process gathers statistics about the health of a site that can be used for monitoring and fine-tuning. The following diagram shows a sample data guard configuration managed by data guard broker.

Client Side Components of the Broker
The Data Guard Manager and Command Line Interface (DGMGRL) make up the client side components of Data Guard Broker. Data Guard Manager is a graphical user interface integrated with Oracle Enterprise Manager. It contains several wizards to ease the management of a data guard configuration. DGMGRL or Command Line Interface provides most of the functionalities of Data Guard Manager and can be very useful to write custom scripts to automate data guard tasks. Chapter 7 provides the practical details of data guard broker. You will learn more about the server side and client side components in that chapter.

-- Bipul Kumar is a Senior Oracle Database Administrator and Database Architect for an Electronic Media Company based in London, United Kingdom. His responsibilities include Oracle architectural design, defining policies and best practices, administering and maintaining the database, supervising oracle development and implementing new database technologies. He has more than seven years of experience in creating and managing Oracle Databases of varying sizes, primarily on UNIX operating systems.
Mr. Kumar has a Masters degree in Science from the Indian Institute of Technology (IIT), Kharagpur, India and nearly ten years of experience in computing. In addition, he has acquired Oracle DBA certification for 7.3, 8.0, and 8i. His most recent book is Oracle Dataguard — Standby Database Failover Handbook.

Questions related to Data Guard

Data Guard Failure case

Reference: http://neworacledba.blogspot.com/2008/09/dataguard-failover-to-logical-standby.html

Data Guard failure to a logical database

Failover involves role transitioning the standby database to take up the primary role.It basically involves the steps:By failover we recover data as much as possible and do role transition.
1) Initialization parameter file on the target standby database must be updated with the logical standby database as the archival destinations. Physical standby databases must be disabled.
2) DBMS_LOGSTDBY might have set the lag value for the log apply services. Remove itIdentify gap using find_gap.sql script.
3) Manually copy files from primary/physical standby database archival destinations.
4) Register the archivelogs as follows:sql>alter database register logical logfile 'file';
5) Copy the online redo log of primary database to logical standby database manually and register them.
6) Verify log apply code progress using log_progress.sql.Once apply is over, stop recovery process on standby site and make it primarysql>alter database stop logical standby apply;sql>alter database activate logical standby database;
7) Configure oracle net to send all requests to the new primary database
8) Create database links to other logical standby databases to support future switchover operations.

Questions related to Data Guard

Data Guard - Introduction and Setting up

Reference: http://advait.wordpress.com/2007/06/12/setting-up-oracle-dataguard-for-10g/

Reference: http://onlineappsdba.com/index.php/2008/05/19/configugre-oracle-data-guard-part-ii/ - Two parts for configuring oracle data guard

Also check out: Questions related to Data Guard