Active Data Guard and snapshot standby databases are two very important new features of Oracle 11g. With Active Data Guard, it's possible to use a physical standby database read-only mode while the replication is ongoing. The snapshot standby feature is used to run a standby database in a read-write mode for testing purposes where all the changes made to the snapshot standby can be reverted. This chapter includes details of these features along with several other advanced techniques.
The following features will be covered in this chapter:
Earlier versions of Oracle 11g standby databases have limitations as they can be used either for a recovery purpose or for a read-only purpose without recovery. From 11gR1 onwards, Oracle introduced more features, and standby databases can now work in recovery even in an open status. While recovery is in progress, real-time data can be accessible to the users just as production data is. Real time query can be used if Oracle Active Data Guard option has been purchased. Apart from that, some additional benefits of Active Data Guard are as follows:
STANDBY_MAX_DATA_DELAY
parameter using triggers, they can immediately be redirected to the primary database.Most of the customers choose the Active Data Guard license for both disaster protection and also for securing read-only access to the applications. Note that all the applications (for example, SAP) don't support Active Data Guard; further, we will discuss several top applications that support Active Data Guard. If we pick any live database, most of the query ratios will be of the read-only (select
queries) operations and there will be few read and write (insert
/update
/delete
/merge
) transactions. I would like to highlight a sample example in order to differentiate between read-only and read-write operations in any business. There are no restrictions on the storage of data types, DML, or DDL operations. Reports can view the latest data from the standby as real-time apply is on. Moreover, you can simplify tuning and Active Data Guard that is certified with Exadata.
The following are the basic requirements and licenses that you must have in order to use Data Guard for a standby system:
These all are the necessary requirements needed to build a standby system. You must have already made lot of effort to configure a standby database for high availability; thus it is worth to add an additional license of Active Data Guard. Of course you can question, why should I choose Active Data Guard and what benefits can we gain from this additional costly step?
In any business, as we discussed earlier, most of the operations are not transactional (Select
statements). For read-only queries, if 1000 user sessions are concurrently accessing the production database and the resources are allocated to the user sessions, then the users can either perform a physical or logical I/O depending on the data being cached into a buffer cache. Even though you have a standby database performing all of the previous transactions/operations from a production database, it can have additional disadvantages as follows:
The following diagram illustrates a database without Active Data Guard:
Apart from read-only and read-write operations on the database, there may be other scheduled backup jobs configured on the database such as EXPDP, RMAN backup jobs, and gathering of statistics on a daily or round-robin basis. In the previous diagram, the standby site is just performing recovery, and there will be no load until and unless a switchover takes place in case of disaster recovery. Active Data Guard is not limited to simply reporting; you can use the OLTP query workload with the required modifications on the applications. Overall, it's a simple administration process because no tasks are required to detect and resolve the data conflicts, and no troubleshooting is necessary for any trail errors. Of course, you may need to adjust the settings and tune the old standby database in case the primary database is unavailable. Oracle Active Data Guard can be configured from a primary standalone to a standalone database or from an RAC primary to a standby standalone database or from an RAC primary to RAC standby databases also. In order to maintain business continuity in case of disaster recovery, we can implement Active Data Guard with fast-start failover. By implementing Active Data Guard, it can enable the flexible use of resources for multiple purposes. The following diagram illustrates the discussed points:
The previous diagram explains how to eliminate contention between read-only and read-write operations. Now we will discuss what are the jobs that can be moved from the primary database if Active Data Guard is enabled and how to offload these operations to a physical standby database(s) to avoid additional processing from the production database. In this chapter, we have explained with examples how to use ASH reports with Active Data Guard and other options. It can be further understood with the help of the following diagram:
Even though applications are used from a standby system, they can connect to a primary database anytime whenever read-write operations are required.
Before you implement and use Active Data Guard, it is necessary to understand the licensing involved with this feature. If you are implementing Active Data Guard, then both the primary and standby servers must be licensed. For the licensing prices, you must always visit Oracle Technology price list for the Active Data Guard. The price may vary depending on the license of the named user or processor license. If licensing is done by the processor, the licenses may not match due to variance in core factors between the times the respective programs were licensed. For any future reference, you can check for the latest updates on http://www.oracle.com/in/corporate/pricing/index.html.
With the Enterprise edition, Active Data Guard is accessible, but you must have the license to use this feature. You can also verify whether Active Data Guard is used or not, using the view v$option
as follows:
SQL> select parameter,value from v$option where parameter='Active Data Guard'; PARAMETER VALUE -------------------- ------- Active Data Guard TRUE
Enabling Active Data Guard is not a challenging task; it requires minimal effort. Here we are not making any changes at the database level, we are just enhancing the option of the Enterprise edition by enabling Active Data Guard. There are no changes to be made to the primary database, and we just need to ensure that redo transport is an LGWR
process so that real-time data can be viewed by the users.