Dataguard - Oracle 11g R2

Difference between Logical and Physical Standby Database:




In oracle data guard, Oracle transfers data from the main database 

(Primary DB) to a standby database, and in case of failure,Oracle 

will switch over to the standby database.
We have two ways to create a standby database, Physical and Logical standby:

Physical Standby differs from Logical Standby:

• Physical standby schema matches exactly the source database.

• It is block by block copy of the primary images.

• Archived redo logs and FTP/SCP directly to the standby database and applied the archived redo log files on the standby database.So standby database should be always in RECOVERY Mode.

• This is like, DBA is sitting in remote location and recovering the primary database in different server by applying the archived redo log files.

• We can open the physical standby database in READ-ONLY mode.But, archive redo logs cannot be applied while physical standby database is in READ-ONLY mode.

• We can use physical standby database when we need to go for HIGH-AVAILABILITY solutions. Physical standby is the most commonly used for DISASTER RECOVERY or FAILURE.

Logical Standby differs from Physical Standby:

• Logical standby database does not have to match the schema structure of the source database.

• Logical standby database is always in OPEN mode. The SQL statements which is generated from primary database is applied on logical standby while the database is in OPEN mode.

• Logical standby uses LOGMINER techniques to transform the archived redo logs into native DML statements (INSERT, UPDATE, DELETE).This DML is transported and applied to the standby database.

• Logical standby tables can be open for SQL Queries (READ-ONLY), and all other standby tables can be open for updates.

• Logical standby database can have additional materialized views and indexes added for faster performance.

• We can use logical standby if we want to OFF-LOAD some of the reporting overhead in primary database. The logical standby database is an excellent choice to do queries, summations, and reporting activities, there by off-loading the primary database from those tasks and saving valuable CPU and I/O cycles.

Installing Physical Standby Offers these benefits:

• An identical physical copy of the primary database.

• Disaster recovery and high availability.

• High Data protection.

• Reduction in primary database workload.

• Performance faster.

Installing Logical Standby Offer:

• Simultaneous use for reporting, Summations and queries.

• Efficient use of standby hardware resources.

• Efficient use of standby hardware resources.

• Reduction in primary database workload.

• Some limitations on the use of certain data types.

***************************************************************************

Glossary:

Role transition:

Changing the role of each database component from primary database 

to the secondary database or from secondary database to the primary database.


Switch-over: 

Planned role transition for testing. Manual intervention.

Fail Over:

Unplanned failure. Manual or Automatic intervention. Automatic role transition is the recommended.

Primary Database:

Where the users are connected to access to the database.
  
Standby Database:

Exists in the disaster recover (DR) site. Where the users are connected in the case of planned role transition (Switch over) or in the case of unplanned failure (Fail Over).
  
Data Guard Observer:
  
Process monitors both primary and standby databases and performs automatic fail over when necessary.


The Broker:


The management framework for Oracle Data Guard. It comes integrated into the oracle database enterprise edition.



Fast Start Fail Over (FSFO):
 
Automatic fail over to the standby database occurs in case of 

failure. FSFO requires the broker.





********************************************************************

Physical Standby: In "MOUNT" State, MRP Will apply archive logs.



Logical Standby: In "READ-ONLY" state, LSP will apply archive logs.



Active Data Guard: In "READ-ONLY" State, LSP will run.



Snapshot Physical Standby: In "READ-WRITE" state.



Physical standby DB can be converted to snapshot standby DB, Which will be in "READ-WRITE" mode. Can do any kind of testing, then we can convert back snapshot standby DB to Physical standby DB. And start MRP, Which will apply all pending archives to physical standby.

Background Process:

PRIMARY Database Side: LNSn, LGWR, and ARC0.

LNS - Log Writer Network Services Process:

Which will ship the redo directly to Remote File Server (RFS) on thestandby database.

LGWR - Log Writer:

LGWR collects transaction redo information and updates the online redo logs.

In Synchronous mode, it ships redo information directly to the RFS process on the standby database and waits for a confirmation before proceeding.
In Asynchronous mode, It ships the redo information directly but does not wait before proceeding. And LGWR submits the network I/O request to the LNS process for that destination.

ARC0 - Archiver Process:

ARCn, or a SQL session performing and archival operation.

Creates a copy of the online redo logs locally for use in a primary database recovery.

The ARCn process may also ship the redo stream to the RFS process while simultaneously archiving the online log.
 
ARCn is also responsible for pro-actively detecting and resolving gaps on all standby databases.

Copies completed redo log files to standby.

STANDBY Database Side: RFS, MRP, LSP,ARC0.

RFS - Remote File Server:

RFS receives redo information from the primary database. RFS can write the redo into standby redo logs or directly to archived redo 

logs.

Each LNSn and ARCn process from the primary database has its own 

RFS.

MRP - Managed Recover Process:

MRP applies archived redo log information to the physical standby 

database.
MRP using REDO APPLY.

If you use data guard broker to manage your standby databases, the 

broker always starts the MRP background process for a physical standby database. 

LSP - Logical Standby Process:

LSP is using SQL Apply.

For logical standby database only, LSP controls the applying of archived redo log information to the logical standby database.

ARC0 - Archiver Process:

FAL - Fetch Archive Log:

Physical Standby Database only.

Its points to the Oracle TNS Service.

Provides a Client/Server Mechanism for resolving gaps detected in the range of archived redo logs that are generated at the primary database and received at the standby database.

Applicable for physical standby databases only.

Process is started only when needed, and shuts down as soon as it is finished. Normally, you would not see this running.

DMON - Data Guard Monitor:

The DMON is the data guard broker process.

Enabled or Disabled with the DG_BROKER_START parameter.

ALTER SYSTEM SET DB_BROKER_START=TRUE -- To Enable.

After executing this statement, a DMON process will start. On UNIX OS, it can be verified using the ps -ef command.

To stop the DMON process, set this parameter to FALSE.

*****************************************************************

DATA PROTECTION MODES:

1.MAXIMUM PROTECTION: (Zero Data Loss)
      
 Redo SYNCHRONOUSLY transported to standby database.    
 Redo must be applied to at least one standby before transact   ion on primary can be committed. 
 Processing on primary is suspended if no standby is available.

2.MAXIMUM AVIALABILITY:(Minimal Data Loss)

 Similar to maximum protection.
 If no standby database is available processing continues on primary database.

3.MAXIMUM PERFORMANCE:(Default)
       
 Redo ASYNCHRONOUSLY shipped to standby database.
 If no standby database is available processing continues on primary

On Primary database at MOUNT State:

ALTER DATABASE SET STANDBY TO MAXIMIZE[PROTECTION|AVALILABILITY|PERFORMANCE];

********************************************************************

https://taliphakanozturken.wordpress.com/tag/data-guard-protection-modes/

Oracle Data Guard is the disaster recovery solution. Protects our production database from disasters,reducing the work load on it and more effective use it.

Technology first introduced by setting the standby database manually with Oracle 7.Appeared as a data guard with Oracle 8i.

ORACLE 8i

Read-Only Standby Database

Managed recovery

Remote archiving redo log files

ORALCE 9i

“Zero Data Loss” Integration

Data Guard Broker and Data Guard Manager GUI

Swithcover and Failover operations

Automatical synchronous

Logical Standby Database

Maximum Protection

ORACLE 10g

Real-Time Apply

Forced support for Oracle RAC

Fast-Start Failover

Asynchronous redo transfer

Flashback Database

ORACLE 11g

Active Standby Database (Active Data Guard)

Snapshot Standby

Heterogeneous platform support (Production –Linux, Standby – Windows)

DATA GUARD 11G SYNCHRONOUS REDO TRANSFER PROCESS ARCHITECTURE 

(SYNC) - ZERO DATA LOSS:

'SYNC' Process Flow:

1 - The user initiates a transaction. This transaction is written to a redo buffer. When the user commit the transaction then the LGWR process writes it redo log file.

2 - LNS (Logwriter Network Service) reports to RFS (Remote File Service) committed redo. RFS writes to standby redo log file. If we use physical standby, the MRP (Managed Recovery Process) will apply
to standby database. In logical standby this is made by LSP (Logical Standby Process).

3 - RFS sends information to LNS that data is processed successfully. LNS transmits this information LGWR. Finally, Commit information is send to the user that initiated the transaction.

Data transfer is ensured by the synchronous redo transfer. But there is a disadvantage. If a network failure occurs between production database (primary) and the standby database or primary database can not access to the standby database then the primary database will hang until standby database response. In other words, the primary database can not serve.

To avoid such situation, I tink the most logical one to use "NET_TIMEOUT" parameter. With this parameter you can determine the timeout period. In case of any outage, primary waits until the timeout period and will continue to serve when timeout period expires. Default value of this parameter in 10g is 180s and in 11g is 30s.

DATA GUARD 11G ASYNCHRONOUS REDO TRANSFER PROCESS ARCHITECTURE (ASYNC):

'ASYNC' Redo transfer flow:

1 - The user initiates a transaction. This transaction is written to a redo buffer. When the user commit the transaction then the LGWR process writes it redo log file.

2. LNS (Logwriter Network Service) reports to RFS (Remote File servie) commintted redo. RFS writes to standby redo log file. If we use physical standby, the MRP (Managed Recovery Process) will apply
to standby database. In Logical standby this is made by LSP (Logical Standby Process).
  
3. Once redo buffer is recycled, LNS automatically reads redo log files and begins to send redo from logfiles.

RFS doesnt, send information to LNS that data is processed successfully.

The most common used process architecture.Asynchronous redo transfer does not guarantee zero data loss.The system has recovered with minimal data loss.

DATA GUARD PROTECTION MODES:

There are 3 protection modes, Summarized in the table below.

PHYSICAL STANDBY - REDO APPLY:

Physical standby database is block by block copy of production database.It uses database recovery function to apply the changes. When redo apply is active, it can be opened for reporting and querying in read-only mode (a feature comes with the Active Dataguard - 11g). Also if can be used for backup operations. For opening in Read-Write mode, you must enable flashback log.Once Flashback log enabled you can open it in Read-Write mode after creating restore point. And again you can revert to standby. 


In some cases, You may want to create a time lag between the time when redo data is received from the primary site and when it is applied to the standby database. If you enabled the DELAYED APPLY, then REAL-TIME apply will be disabled.

Real-Time apply can be activated as follows.


SQL>alter database recover managed standby database using current logfile disconnect;

If the REAL-TIME apply feature is enabled as shown above, log apply services (LSN) can apply redo data as it is received, without waiting for the current standby redo log file to be archived. This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins. As the Remove File Server (RFS) process writes the redo data to standby redo log files on the standby database, log apply services can recover redo from 

standby redo log files as they are being filled.

Delayed apply can be activated:

alter database recover managed standby database delay 10;

SNAPSHOT STANDBY DATABASE (11g): ---> READ-WRITE Mode

A snapshot standby database is a fully updatable standby daabase that is created by converting a physical standby database into a snapshot standby database. Snapshot standby is a feature that comes with 11g.
A snapshot standby database receives and archives, but does not apply, redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database. When it is converted back into a physical database, it discard all local updates. You can do test, patch, etc. By opening it in READ-WRITE mode the test.

Logical standby databases are logically to primary databases although the physical organization and structure of the data can be different. Logical standby databases are updated using SQL statements. Logical standby database can be used for recovery and reporting simultaneously. Determine if the primary database contains tables and datatypes that were not supported by a logical standby database.if the primary database contains tables that were unsupported, log apply services will exclude the tables applying to the logical standby database. Run the following queries to see unsupported tables.

select * from dba_logstdby_unsupported;

select * from logstdby_unsupported_tables;

ACTIVE DATAGUARD (11G): --> REDO-ONLY Mode.

Standby database can be open in READ-ONLY mode while redo apply (Read-Only with apply). Therefore, the database is called the Active Standby. If we look at in terms of benefits to us of the  Active Data Guard; We can make our real-time reporting (while continuing to apply redo), backup operations through the active standby database. Thus, we will reduce the processing load of production. Production 

database will serve our customers more effectively.

Prior to 11gR2, We can use 9 standby databases at the same time.

Now, with 11gR2 we can use 32 standby databases at the same time.

SWITCHOVER and FAILOVER:

Switch-over is the planned role change. It does not require re-installation of a new database. It can be used to test interoperability of standby database, OS and hardware maintenance.

Fail-over is the deformation of the production (Primary) database and activating standby database as the primary. It is not reversible. When enabled, re-create the standby database.

********************************************************************************************