Oracle Active Data Guard
Oracle Active Data Guard increases performance, availability, data protection, and return on investment wherever Data Guard is used for real-time data protection and availability. An Oracle Active Data Guard standby database can be used to offload a primary database of reporting, ad hoc queries, data extracts, and backups, making it a very effective way to insulate interactive users and critical business tasks on the production system from the overhead of long-running operations. Oracle Active Data Guard provides read-only access to a physical standby database while it is synchronized with a primary database, enabling minimal latency between reporting and production data. Unlike other replication methods, Active Data Guard is very simple to use, transparently supports all data types, and offers very high performance with complete read consistency at the reporting database. Oracle Active Data Guard automatically repairs physical corruption on either the primary or standby database, increasing availability and maintaining data protection at all times.
Oracle Active Data Guard 12c reduces downtime for Oracle Database upgrades and other database maintenance while avoiding error-prone manual procedures. Oracle Active Data Guard 12c Far Sync enables zero data loss disaster protection (DR) across any distance without impacting database performance. Oracle Active Data Guard 12c also includes Global Data Services to extend the concepts of automated service failover and workload management to globally distributed systems, and application continuity to provide transparent failover of in-flight transactions in Data Guard configurations that do not use Oracle Real Application Cluster (Oracle RAC).
Using Real-Time Query
With Oracle Active Data Guard, you can use a physical standby database for queries while redo is applied to the physical standby database. This feature enables you to use a physical standby database for disaster recovery and to offload work from the primary database during normal operation. The physical standby is in a read-only mode, so no additional indexes or materialized views may be created to support reporting activities.
In addition, this feature provides a loosely coupled read/write clustering mechanism for OLTP workloads when configured as follows:
- Primary database: Recipient of all update traffic
- Several readable standby databases: Used to distribute the query workload
The physical standby database can be opened in read-only mode only if all files were recovered up to the same system change number (SCN). Otherwise, the open fails.
Enabling Real-Time Query
A physical standby database instance cannot be opened if Redo Apply is active on a mounted instance of that database. In order to enable real-time query:
1. Stop the Redo Apply process:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2. Open the database for read-only access:
SQL> ALTER DATABASE OPEN READ ONLY;
3. Restart the Redo Apply with the real-time option that is now the default mode for Oracle Database 12c Release 1:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
The COMPATIBLE database initialization parameter must be set to 11.0 or higher to use the real-time query feature of the Oracle Active Data Guard option.
Note: When using the Oracle Data Guard broker it is not necessary to stop Redo Apply and When using the Oracle Data Guard broker, it is not necessary to stop Redo Apply and restart Redo Apply when enabling real-time query. Real-time query is not the same as real-time apply, which was covered in the previous lesson. Real-time apply allows the recovery mechanisms to read from the standby redo logs at the same time that redo is being written to the standby redo logs. In the normal physical standby mode of operation, the database is only at the mount mode and would not allow any queries against user tables, even though real-time apply is enabled. Real-time query with Oracle Active Data Guard extends real-time apply by allowing the database to be opened and queries performed against it by allowing the database to be opened and queries performed against it.
Disabling Real-Time Query
To disable real-time query, you must shut down the standby database instance and restart it in MOUNT mode.
1. Shut down the standby database instance.
SQL> SHUTDOWN IMMEDIATE;
2. Restart the standby database instance in MOUNT mode.
SQL> STARTUP MOUNT;
3. Restart Redo Apply services (real-time apply).
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Checking the Standby’s Open Mode
You can use the OPEN_MODE column of V$DATABASE to check the open mode of a physical standby database. If the physical standby database stops Redo Apply in order to open the database in read-only mode, then the OPEN_MODE column will indicate “READ ONLY.”
A physical standby database opened in read-only mode:
SQL> SELECT open_mode FROM V$DATABASE; OPEN_MODE -------------------- READ ONLY
A physical standby database opened in real-time query mode:
SQL> SELECT open mode FROM V$DATABASE; _ OPEN_MODE -------------------- READ ONLY WITH APPLY
After the database has been opened read-only, Redo Apply can be restarted to enable Active Data Guard real-time query mode with the following command:
SQL> alter database recover managed standby database disconnect;
After Redo Apply has been started on an open read-only physical standby database, the OPEN_MODE column will indicate “READ ONLY WITH APPLY.”
Understanding Lag in an Active Data Guard Configuration
Active Data Guard can improve performance by off-loading a read-only workload to a physical standby database. However, due to hardware and network issues, the data on a standby database may lag behind the data on the primary database. The standby database may not always be current with the primary database if it does not have the capacity to apply redo as quickly as it is received. Limited bandwidth may prevent the primary database from shipping redo as quickly as it is generated, particularly during periods of peak workload.
Oracle Database 12c Release 1 (12.1) includes features to enable you to determine the lag time and take appropriate action. You can establish a tolerance level for data staleness by configuring a maximum value for apply lag. Query results are returned to the application if the lag is within the acceptable tolerance level; otherwise, an error results.
If you determine that you want your application to receive the results of a query, regardless of the “staleness” of the data, you can monitor the apply lag via the V$DATAGUARD_STATS view and then take appropriate action if the lag is unacceptable.
Monitoring Apply Lag: V$DATAGUARD_STATS
Apply lag is a measure of the degree to which a standby database lags behind the primary database, due to delays in propagating and applying redo to the standby database. The current apply lag is the difference, in elapsed time, between when the last applied change became visible on the standby and when that same change was first visible on the primary. This metric is computed to the nearest second.
The apply lag metric is computed using data that is periodically received from the primary database. The DATUM_TIME column contains a timestamp of when this data was last received by the standby database. The TIME_COMPUTED column contains a timestamp taken when the apply lag metric was calculated. The difference between the values in these columns should be less than 30 seconds If the difference is larger than this the columns should be less than 30 seconds. If the difference is larger than this, the apply lag metric may not be accurate.
SQL> SELECT name, value, datum_time, time_computed 2> FROM v$dataguard_stats 3> WHERE name like 3> WHERE name like 'apply lag apply lag ;' NAME VALUE DATUM_TIME TIME_COMPUTED --------- ------------- -------------------- -------------------- apply lag +00 00:00:00 27-MAY-2009 08:54:16 27-MAY-2009 08:54:17
Monitoring Apply Lag: V$STANDBY EVENT HISTOGRAM
V$STANDBY_EVENT_HISTOGRAM is a new view that is available on the standby database. This view displays the histogram of the apply lag on the physical standby database.
Use the histogram to focus on periods of time when the apply lag exceeds desired levels. Determine the cause of the lag during those time periods and take steps to resolve the excessive lag. To evaluate the apply lag over a time period, take a snapshot of V$STANDBY_EVENT_HISTOGRAM at the beginning of the time period and compare that snapshot with one taken at the end of the time period.
SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM 2> WHERE NAME = 'apply lag' AND COUNT > 0; NAME TIME UNIT COUNT LAST_TIME_UPDATED --------- --------- -------- ----------- ------------------------ apply lag 0 seconds 79681 06/18/2009 10:05:00 apply lag 1 seconds 1006 06/18/2009 10:03:56 apply lag 2 seconds 96 06/18/2009 09:51:06 apply lag 3 seconds 4 06/18/2009 04:12:32 apply lag 4 seconds 1 06/17/2009 11:43:51 apply lag 5 seconds 1 06/17/2009 11:43:52 6 rows selected
Each distinct value of apply lag has its own bucket. The count in the bucket is incremented when the physical standby database samples its data delay.
Allowed Staleness of Standby Query Data
You can configure a limit through the use of the STANDBY_MAX_DATA_DELAY session parameter. Use this session parameter to specify a limit for the amount of time (in seconds) allowed to elapse between when changes are committed on the primary database and when those same changes can be queried on the active standby database.
ALTER SESSION SET STANDBY_MAX_DATA_DELAY = {INTEGER|NONE}
If the specified limit cannot be met, an error is returned to the query as follows:
ORA-3172 STANDBY_MAX_DATA_DELAY has been exceeded
This guarantees that a query will not receive a “stale result” if the apply lag exceeds the service level agreement. In addition, a warning message is written to the standby database alert log. This setting is ignored for the SYS user.
The default value is NONE, which indicates that queries issued to the physical standby database will be executed regardless of the apply lag on that database.
Configuring Zero Lag Between the Primary and Standby Databases
Certain applications have zero tolerance for any lag. A query on the standby database must return the same result as if it were executed on the primary database. You can ensure that your application querying data on the standby database see all data that has been committed on the primary database by setting STANDBY_MAX_DATA_DELAY to 0.
A query does not execute until the query SCN on the standby database has advanced to a value equal to that of the current SCN on the primary database at the time the query was issued. To support zero lag, the primary database must operate in maximum availability or maximum protection mode. Protection modes will be discussed later in the course.
Specify SYNC for redo transport mode. Real-time query must be enabled as a prerequisite for configuring zero lag.
Setting STANDBY_MAX_DATA_DELAY by Using an AFTER LOGON Trigger
Create an AFTER LOGON trigger that is database role–aware and uses DATABASE_ROLE, a new attribute in the USERENV context. SQL and PL/SQL clients can retrieve the database role programmatically using the SYS_CONTEXT function. It should also enable you to write role-specific triggers.
It should set STANDBY_MAX_DATA_DELAY when the application logs on to a real log on to a real-time query time query–enabled standby database and Allows for configuration of a maximum data delay without changing the application source code.
You can create an AFTER LOGON trigger that sets the STANDBY_MAX_DATA_DELAY session parameter when the database is a physical standby database that is operating in real-time query mode. In Oracle Database 12c Release 1 (12.1), the DATABASE_ROLE attribute of the USERENV context enables you to determine the role of the database. SQL and PL/SQL clients can retrieve this information by using the SYS_CONTEXT function. This enables you to write triggers that perform certain actions based on the database role.
Example: Setting STANDBY_MAX_DATA_DELAY by Using an Using an AFTER LOGON Trigger
The example below shows an AFTER LOGON trigger that is used to set the value of STANDBY_MAX_DATA_DELAY based on the database role.
CREATE OR REPLACE TRIGGER sla_logon_trigger
AFTER LOGON
ON APP.SCHEMA
BEGIN
IF (SYS_CONTEXT('USERENV', 'DATABASE_ROLE')
IN ('PHYSICAL STANDBY'))
THEN execute immediate
'alter session set standby_max_data_delay=5';
ENDIF;
END;
Forcing Redo Apply Synchronization
You can execute the ALTER SESSION SYNC WITH PRIMARY command to ensure that the standby database is completely synchronized with the primary database at the time of execution. The use of this command is particularly applicable in a reporting environment.
ALTER SESSION SYNC WITH PRIMARY performs a blocking wait on the standby database upon execution. This command causes the application to be blocked until the standby database is in sync with the primary database as of the time this command is executed.
When the ALTER SESSION command returns control to the session, the session can continue to process queries without having to wait for Redo Apply on the standby database. If Redo Apply is not active or is canceled before the standby database is in sync with the primary database, an ORA-3173 Standby may not be synced with primary error is returned.
Creating an AFTER LOGON Trigger for Synchronization
This type of trigger is useful when you are using the standby database for reporting and want to be sure that the reports have the most current data. The standby-only AFTER LOGON trigger executes the ALTER SESSION SYNC WITH PRIMARY command to force a wait for synchronization between the primary database and the standby database. A standby-only trigger is created and enabled on the primary database, and then becomes part of the redo that is propagated to the standby database. However, the trigger logic is designed only to take certain actions if the database role is set to “physical standby.”
CREATE TRIGGER adg_logon_sync_trigger
AFTER LOGON ON user.schema
BEGIN
IF (SYS_CONTEXT('USERENV','DATABASE_ROLE') IN
('PHYSICAL STANDBY'))
THEN
execute immediate 'alter session sync with primary';
END IF;
END;
Active Data Guard: DML on Temporary Tables
Redo generation on a read-only database is not allowed. When a data manipulation language (DML) operation makes a change to a global temporary table, the change itself does not generate redo because it is only a temporary table. However, the undo generated for the change does, in turn, generate redo. Prior to Oracle Database 12c Release 1 (12.1), global temporary tables could not be used on Active Data Guard standbys, which are read-only.
However, in Oracle Database 12c Release 1 (12.1), the temporary undo feature allows the undo for changes to a global temporary table to be stored in the temporary tablespace instead of in the undo tablespace. Undo stored in the temporary tablespace does not generate redo, thus enabling redo-less changes to global temporary tables. This allows DML operations on global temporary tables on Oracle Active Data Guard standbys global temporary tables on Oracle Active Data Guard standbys.
To enable temporary undo on the primary database, use the TEMP_UNDO_ENABLED initialization parameter. On an Active Data Guard standby, temporary undo is always enabled by default, so the TEMP_UNDO_ENABLED parameter has no effect.
TEMP_UNDO_ENABLED = false | true
The temporary undo feature requires that the database initialization parameter COMPATIBLE be set to 12.0.0 or higher. The temporary undo feature on Active Data Guard instances does not support temporary binary and character large objects.
Active Data Guard: Support for Global Sequences
To support read-mostly applications by using Active Data Guard, you might have to use sequences with global temporary tables. In an Active Data Guard environment, sequences created by the primary database with the default CACHE and NOORDER options can be accessed from standby databases as well. When a standby database accesses such a sequence for the first time, it requests that the primary database allocate a range of sequence numbers. The range is based on the cache size and other sequence properties specified when the sequence was created. Then the primary database allocates those sequence numbers to the requesting standby database by adjusting the corresponding sequence entry in the data dictionary. When the standby has used all numbers in the range, it requests another range of numbers.
Because the standby’s requests for a range of sequences involve a round-trip to the primary, be sure to specify a large enough value for the CACHE keyword when you create a sequence for an Active Data Guard standby. Otherwise, performance could suffer. In addition, the terminal standby should have a defined LOG_ARCHIVE_DEST_n parameter that points back to the primary.
Active Data Guard: Support for session Sequences.
A session sequence is a special type of sequence that is specifically designed to be used with global temporary tables that have session visibility. Unlike existing regular sequences (referred to as “global” sequences for the sake of comparison), a session sequence returns a unique range of sequence numbers only within a session, not across sessions. Session sequences are not persistent. If a session goes away, so does the state of the session sequences that were accessed during the session.
Session sequences support most of the sequence properties that are specified when the sequence is defined. However, the CACHE/NOCACHE and ORDER/NOORDER options are not relevant to them and are ignored. Session sequences must be created by a read-writ database, but they can be accessed on any read-write or read-only database (either a regular database temporarily open as read-only or a standby database).
To create a session sequence:
SQL> CREATE SEQUENCE ... SESSION;
Benefits: Temporary Undo and Sequences
Prior to Oracle Database 12c Release 1 (12.1), the inability to use global temporary tables and sequences for Active Data Guard limited some read-mostly applications from being offloaded from the primary database to a standby database.
The new temporary undo and sequences support for Active Data Guard provides many benefits. Additional reporting workload can now be migrated to a standby system to reduce the overhead of system resources in general. Because temporary undo reduces the amount of redo generated, the amount of redo needed to be shipped to standby database systems is reduced and results in network performance improvements. The reduction in redo also implies less redo being written to standby redo logs and local archiving of standby redo logs. Therefore, performance improvements are realized on the primary and standby database systems and on the network between the two systems.
Supporting Read-Mostly Applications
Reporting applications that are predominantly read-only, but require limited read-write database access are referred to as “read-mostly” applications. Active Data Guard enables a standby database to support the read-only portion of read-mostly applications if writes are redirected to the primary database or a local database.
- Read-mostly applications are predominantly read-only applications, but require limited read-write database access.
- Active Data Guard supports the read-only portion of read- mostly applications if writes are redirected to the primary database or a local database.
- Redirection of read-write workload does not require application code changes.
- Writes can be transparently redirected to the primary Writes can be transparently redirected to the primary database if the application adheres to the following:
- Modified objects must not be qualified by a schema name.
- SQL commands must be issued directly from the client, not in stored procedures.
Example: Transparently Redirecting Writes to the Primary Database
Consider an application as described below:
– The application executes as user U, reading the U.R table and writing to the U.W table. The application connects as user U when executing on the primary database. User S accesses U.R with the S.R synonym and U.W with the S.W synonym.
Create an AFTER LOGON trigger on the standby database:
CREATE TRIGGER adg_logon_switch_schema_trigger
AFTER LOGON ON u.schema
BEGIN
IF (SYS CONTEXT('USERENV' 'DATABASE ROLE') IF (SYS_CONTEXT('USERENV','DATABASE_ROLE')
IN ('PHYSICAL STANDBY'))
THEN
execute immediate
'alter session set current_schema = S';
END IF;
END;
The AFTER LOGON trigger is created on the standby database for another user, R. When the application executes on the standby database, it connects as the U user. The AFTER LOGON trigger fires and transparently switches to the S schema. All reads on R execute as reads to the U.R table on the standby database. All reads and writes to W execute as reads and writes to U.W@primary.