Oracle Data Guard 11gR2 Administration : Beginner's Guide: If you're an Oracle Database Administrator it's almost essential to know how to protect and preserve your data. This is the perfect primer to Data Guard that covers all the bases with a totally practical, user-friendly approach.

The Physical standby database is a block-based copy of the primary database. In a physical standby environment, in addition to containing the same database objects and same data, the primary and standby databases are identical on a block-for-block basis. Physical standby databases use Redo Apply method to apply changes. Redo Apply uses Managed recovery process (MRP) in order to manage application of the change in information on redo.

In Version 11g, a physical standby database can be accessible in read-only mode while Redo Apply is working, which is called Active Data Guard. Using the Active Data Guard feature, we can offload report jobs from the primary to physical standby database.

Logical standby database is a feature introduced in Version 9iR2. In this configuration, redo data is first converted into SQL statements and then applied to the standby database. This process is called SQL Apply. This method makes it possible to access the standby database permanently and allows read/write while the replication of data is active. Thus, you're also able to create database objects on the standby database that don't exist on the primary database. So a logical standby database can be used for many other purposes along with high availability and disaster recovery.

Due to the basics of SQL Apply, a logical standby database will contain the same data as the primary database but in a different structure on the disks.

One discouraging aspect of the logical standby database is the unsupported data types, objects, and DDLs. The following data types are not supported to be replicated in a logical standby environment:

The logical standby database doesn't guarantee to contain all primary data because of the unsupported data types, objects, and DDLs. Also, SQL Apply consumes more hardware resources. Therefore, it certainly brings more performance issues and administrative complexities than Redo Apply.

Principally, a snapshot standby database is a special condition of a physical standby database. Snapshot standby is a feature that is available with Oracle Database Version 11g. When you convert a Physical standby database into a snapshot standby database, it becomes accessible for read/write. You can run tests on this database and change the data. When you're finished with the snapshot standby database, it's possible to reverse all the changes made to the database and turn it back to a physical standby again.

An important point here is that a snapshot standby database can't run Redo Apply. Redo transfer continues but standby is not able to apply redo.

The functionality of keeping a duplicate database in a separate server, which can be synchronized with the primary database, came with Oracle database Version 7.3 under the name of standby database. This standby database was constantly in recovery mode waiting for the archived redo logs to be synchronized. However, this feature was not able to automate the transfer of archived redo logs. Database administrators had to find a way to transfer archived redo logs and apply them to the standby server continuously. This was generally accomplished by a script running in the background.

The only aim of Version 7.3 of the standby database was disaster recovery. It was not possible to query the standby database or to open it for any purpose other than activating it in the event of failure of the primary database. Once the standby database was activated, it couldn't be returned to the standby recovery mode again.

Oracle database Version 8i brought the much-awaited features to the standby database and made the archived log shipping and apply process automatic, which is now called managed standby environment and managed recovery, respectively. However, some users were choosing to apply the archived logs manually because it was not possible to set a delay in the managed recovery mode. This mode was bringing the risk of the accidental operations to reflect standby database quickly.

Along with the "managed" modes, 8i made it possible to open a standby database with the read-only option and allowed it to be used as a reporting database.

Even though there were new features that made the tool more manageable and practical, there were still serious deficiencies. For example, when we added a datafile or created a tablespace on the primary database, these changes were not being replicated to the standby database. Database administrators had to take care of this maintenance on the standby database. Also when we opened the primary database with resetlogs or restored a backup control file, we had to re-create the standby database.

First of all, with this version Oracle8i standby database was renamed to Oracle9i Data Guard. 9i Data Guard includes very important new features, which makes the product much more reliable and functional. The following features were included:

In Version 9i Release 2, which was introduced in May 2002, one year after Release 1, there were again very important features announced. They are as follows:

The 10g version again introduced important features of Data Guard but we can say that it perhaps fell behind expectations because of the revolutionary changes in release 9i. The following new features were introduces in Version 10g:

10g Release 2 also introduced new features to Data Guard, but these features again were not satisfactory enough to make a jump to the Data Guard technology. The two most important features were Fast-Start Failover and the use of Guaranteed restore point:

Oracle database version 11g offered the expected jump in the Data Guard technology, especially with two new features, which are called Active Data Guard and snapshot standby. The following features were introduced:

We've gone through the evolution of Oracle Data Guard from its beginning until today. As you may notice, Data Guard started its life as a very simple database property revealed to keep a synchronized database copy with a lot of manual work and now it's a complicated tool with advanced automation, precaution, and monitoring features. Now let's move on with the architecture and components of Oracle Data Guard 11gR2.

These services are the vital points of a Data Guard configuration. Database administrators should decide and use the proper configuration to supply the business needs and tune these services to comply with SLAs.

Redo transport services

In a primary database, when a user commits a transaction, the relevant redo data is written into online redo logfiles from memory (Redo Log Buffer). After the online redo log group becomes full it is archived into an archived redo logfile with a log switch. It's possible to configure Data Guard sending the redo data to standby databases from the log buffer as the transactions are committed (by LGWR process) or from the online redo logfiles when they're being archived (by ARCn processes). Shipping redo data with ARCH will result in more data loss in the case of primary database failure because the data change information in the current online log of primary will be lost.

The following diagram shows the Data Guard configuration with ARCH transportation mode:

Here are the important properties of the log transport with the ARCH attribute:

• Logs are sent by the ARCH process; the LNS process is not in use

• Standby redo logs are not mandatory on the standby database

• Data in the unarchived online redo log will be lost in a failover

If LGWR is used for the redo transportation, it's possible to guarantee zero data loss failovers by creating a Data Guard configuration in which the primary database waits for confirmation from the standby database that redo has been received, before it informs that the commit is completed. This configuration is called Synchronous redo transport (SYNC). However, this may affect the performance of the primary database.

The following diagram shows the Data Guard configuration with LGWR and SYNC transportation mode:

The following points explain the diagram in a better way:

• Redo is read and sent to the standby database directly from the log buffer by the LNS process

• Acknowledgment needed from the standby database (RFS to LNS and LNS to LGWR) to send COMMIT ACK to the database user

• It's mandatory to use standby redo logs

• Zero data loss in failover can be guaranteed with this configuration

• There maybe slower response times on the primary database

• The primary database stops giving service in a network disruption incident between primary and standby

Tip

If SYNC redo transport is chosen in an 11g Data Guard configuration, the performance decrease on the primary database will be less than the earlier releases. Previously, the primary database used to finish writes to the online redo log first and then send redo to the standby database. There were two consecutive I/O operations that the primary database needs to wait for in order to complete the commit. In 11g these two I/O operations run in parallel. The primary database does not wait for finishing writes to online redo log and it sends the redo data to standby at the same time.

The other option is to use the Asynchronous redo transport (ASYNC) method, which avoids the impact to primary database performance. In this method, the primary database never waits for any acknowledgment from the standby database in order to complete the commit. In the ASYNC redo transport method we have the performance gain; however, this method does not guarantee zero data loss failovers because it does not guarantee all the committed transactions being received by the standby database at any moment.

The following points explain the diagram in a better way:

• No acknowledgment needed from standby to send the COMMIT ACK to the database user

• Redo is read and sent to standby from the Redo Log Buffer or online redo logs by the LNS process. If LNS cannot catch the send data in the Redo Log Buffer before it is recycled, it automatically reads and sends redo data from the online redo log.

• The committed transactions that weren't shipped to standby yet, may be lost in a failover

• Potential slower response time on primary database with SYNC mode is not valid here

Protection modes

Data Guard offers three data protection modes, which serve different business needs in terms of data protection and performance. You can find the properties of these modes in the following comparison table:

Mode	Redo transport	Action with no standby database connection	Risk of data loss
Maximum Protection	SYNC and LGWR	The primary database needs to write redo to at least one standby database. Otherwise it will shut down.	Zero data loss is guaranteed.
Maximum Availability	SYNC and LGWR	Normally works with SYNC redo transport. If the primary database cannot write redo to any of its standby databases, it continues processing transactions as in ASYNC mode.	Zero data loss in normal operation, but not guaranteed.
Maximum Performance	ASYNC and LGWR/ARCH	Never expects acknowledgment from the standby database.	Potential for minimal data loss in a normal operation.

You have just seen the Redo Apply behavior on different Data Guard configurations such as delayed, non-delayed, and real-time apply. You learned how to query the status of the important Data Guard processes MRP and RFS on the standby database.

Q1. What's the risk of using real time apply and how can we overcome this risk?

Role transitions basically enable users to change the roles of the databases in a Data Guard configuration. There are two role transition options in Data Guard, which are switchover and failover.

There are three options for a database administrator to manage a Data Guard environment, which are SQL*Plus command-line interface, Oracle Enterprise Manager, and Data Guard broker command-line interface (DGMGRL). In almost every IT infrastructure management interface, command-line tools offer great flexibility and detailed options and the graphical interfaces are user friendly, simple, and automated.

You have just seen examples of monitoring the Data Guard environment with three different interfaces. These examples are just intended to give you a first impression of what these interfaces look like. Properties and details of the tools in question will be covered in the next chapters.

All of these interfaces can be used to monitor and manage the Data Guard; however, they all have their own pros and cons. If you already use Enterprise Manager Cloud Control in your current IT infrastructure, Data Guard installations must be added as targets in order to take advantage of its visual and easy monitoring and management potential. If you don't have Cloud Control but have multiple Data Guard installations, you should think about using it to overcome the challenges of central monitoring.

In a Data Guard configuration we can see some Oracle Data Guard specific background processes in both, primary and standby databases. These processes perform the operations of redo transport and apply services. Data Guard broker also has some specific background processes. We can see the description and duties of the most important Data Guard processes as follows:

Storage-base replication solutions technologies are based upon the storage-array based replication of data. Thus, the source of data does not matter. All kinds of application and database data can be replicated to a remote location, where Data Guard is only able to replicate Oracle databases.

In general there are two kinds of storage-based replication: synchronous and asynchronous replication. Synchronous replication means that each update to the source storage unit must also be updated in the target storage unit before another update can process. This guarantees zero data loss in the case of primary site failure. However, synchronous replication affects the I/O respond performance of the primary system depending on the distance between sites and network capacity. Therefore, this technology is distance limited. Synchronous replication technologies support up to 300 km distance between sites in the current technology level.

Asynchronous replication provides a long-distance replication solution with minimal impact on performance. In some products, the main problem with the asynchronous mode is the data consistency on the secondary site. The primary site sends a periodic, incremental copy of updates to the secondary site instead of a constant stream of updates. So there is no guarantee that dependent write operations on the primary site are transferred and applied to the remote destination in the same sequence.

Using storage-based replication solutions, it's not possible to start an Oracle instance and query database on the secondary site using the disks with the replicated data because of the data inconsistency issue. However Data Guard offers Active Data Guard, which enables users to query the standby database while replication is on the go. Some other advantages of Data Guard over storage-based replication solutions are enhanced corruption detection and prevention, automated database failover (fast-start failover), and RMAN backup offloading features that may not benefit from the use of storage-based replication solutions.

GoldenGate is a data replication and integration tool for heterogeneous environments. It provides real-time capture, transformation, routing, and delivery of database transactions across heterogeneous systems (Oracle, DB2, MySQL, SQL Server, Sybase, Teradata, Netezza, and so on). Oracle agreed to acquire GoldenGate software in 2009 and then released 10.4, 11.1, and 11.2 versions with new enhancements. On the other hand, Streams is a built-in feature of the Oracle database that was first announced with database Version 9.2 and allows information sharing within an Oracle database or between Oracle databases.

Their common property is their capability of capturing, propagating, and applying data changes between Oracle databases.

On the other hand their main differences are:

Because of the GoldenGate's wider technology infrastructure and flexibility over Streams, Oracle announced that Oracle Streams will continue to be supported, but will not be actively enhanced and the best elements of Oracle Streams will be evaluated for inclusion with Oracle GoldenGate. It was also indicated that GoldenGate is the strategic product of Oracle on data distribution and data integration.

An important feature of GoldenGate that makes the product different from its counterparts is the bidirectional replication capability, which is also called active-active replication. With this feature the primary and standby concepts are replaced by two active primary sites. Updates on site A are replicated to site B, and updates on site B are replicated to site A. The main challenges here are conflict handling and loop detection. A conflict is likely to occur in a bi-directional environment, where the same row or field is being updated at both sides and the changes are replicated. In this situation, a decision needs to be made if both transactions fail, or one transaction overwrites the other. The other key point is loop detection. If an update is replicated from site A to site B and then the same update from site B to site A, and so on, this loop needs to be detected and solved. The following diagram shows the general structure of an active-active GoldenGate configuration:

GoldenGate is a preferred solution to extract data from production databases in order to feed the data warehouse. It offers much flexibility to select specific data on the database and if needed transform the data before it hits the target.

The replication market's leaders, namely, Data Guard, storage-based replication products, and GoldenGate are compared in the following table with their most important features. Streams is out of this comparison because of the strategy mentioned by Oracle on its replication products:

After reviewing the comparison table, it's obvious that Data Guard has better properties for high availability and disaster recovery purposed Oracle database replication. Storage-based replication products offer disaster recovery solution for the complete IT infrastructure data; however, when the case is Oracle databases, they cannot offer the Oracle integrated, flexible, and automatized features as in Data Guard. On the other side, we can see that GoldenGate was positioned especially for ETL and data integration requirements and it has great flexibility in this field. However, it also cannot reach Data Guard standards on data protection and disaster recovery.