Real-Time Query Standby - Oracle 11g

By far, the biggest enhancements to Oracle Database 11g Data Guard physical standby are the real-time query standby and snapshot standby features. The real-time query standby feature is marketing terminology for the physical standby database that is capable of applying redo while the database is open in read-only mode. Prior to Oracle Database 11g, when a database was open in read-only mode via alter database open or alter database open read only, subsequent archivelogs from the primary host could no longer be applied to the reporting database or disaster recovery site. This restriction is cleared and makes Data Guard truly a real-time reporting database in Oracle Database 11g Data Guard. The redo apply process continues to remain active while the database is open in read-only mode. The real-time query standby database continues to provide the protection modes (Maximum Protection, Maximum Availability, and Maximum Performance). The real-time query reporting database feature maximizes the return on investment of the budget allocated for the Data Guard database.

Note Using real-time query or RMAN block change tracking on a Data Guard standby database requires a license for Oracle Active Data Guard.

No one can argue that the real-time query standby database is by far one of the most innovative improvements to Oracle Database 11g. Not only does Oracle provide a disaster recovery solution, but at the same time, it also provides a complete return on investment. The real-time query feature is the same as the physical standby database that you are accustomed to, except now you can have the database open in read-only mode with almost real-time synchronization of your mission-critical data from your primary database.

There are two important restrictions to opening the physical standby database for readonly with redo apply. First, the compatibility initialization parameter must be set to 11.1.0 or higher. Second, the alter database set transaction read only command has to be issued prior to performing any kind of distributed queries over the database link.

Start the Real-Time Query Standby

To open the standby database in real-time read-only mode, you must stop the redo apply using the alter database recover managed standby database cancel command. Once the database is open, you can start or stop the redo apply at any time. The following steps are all that is required to change a standby database for real-time reporting:

SQL> alter database recover managed standby database cancel;
Database altered.

SQL> alter database open;
Database altered.

SQL> alter database recover managed standby database disconnect;
Database altered.

The managed recovery process, initiated after the database is open, is the key difference in implementing the real-time query standby database. In previous releases, the managed recovery process is started while the database is mounted.

Validate the Real-Time Query Standby Database

Now you have enabled the real-time query capability in the standby database. Let’s perform a simple validation to prove that DDL (and even user creation in this example) can be propagated in the real-time query standby database. On the primary database, we will create a user called rodba to demonstrate how the real-time query standby database applies archivelogs while the database is in read-only mode. Once the archivelog is applied, you will be able to log in as rodba.

Immediately after the user account is created on the primary database, you can confirm that you cannot connect to the rodba user on the real-time query standby database, as shown here:

DBA11gDR $ sqlplus rodba/rodba

SQL*Plus: Release - Production on Wed Aug 15 05:50:02 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ORA-01017: invalid username/password; logon denied

To validate that the real-time query standby database can apply archivelogs while the database is open in read-only mode, manually issue a log switch command, and check the sequence number from the V$LOG view on the primary database. Immediately after, confirm that the sequence numbers on the primary and physical standby databases match to ensure that both databases are synchronized. First, force a log switch on the primary database, as displayed here:

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$log;

Next, confirm on the real-time query standby database that the maximum sequence is at 13, as shown here:

SQL> select max(sequence#) from v$log;


Since the archivelog that housed the create user DDL is successfully applied, you should now be able to connect as the rodba user on the real-time query standby database. The following SQL*Plus connection proves that the real-time query feature is capable of applying DDL even while the database is open for read-only transactions:

DBA11gDR $ sqlplus rodba/rodba

Copyright (c) 1982, 2007, Oracle. All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

You can offload all your reporting requirements to the real-time query standby database at the local data center or at the disaster recovery site. The primary database can be the recipient of all OLTP traffic while the real-time query standby database(s) can handle all your reporting needs.

Design the Real-Time Query Database

Just as you can have a farm of web servers to scale out your web applications, you can architect a farm of real-time standby reporting databases. This implementation technique is referred to as a reader farm. A reader farm allows you to integrate multiple real-time read-only physical standby databases as an alternative architecture to provide scaled-out read performance. In addition, a reader farm provides a higher level of availability by isolating faults to a specified read-only standby database and provides greater scalability by enabling more workload capacity on the primary database by offloading all reports.

By adopting RAC in conjunction with real-time query standby with flashback technologies, companies can achieve the maximum availability architecture.

Tip :You can implement invisible indexes on the primary database to create a customized indexing requirement for your reporting databases. The only caveat is that the application must be able to hard-code hints to be able to access the invisible indexes.

There are additional design considerations with real-time query standby databases. If your reporting application has the requirement to create temporary tables or perform aggregated inserts to staging tables, you can use database links to a non–Data Guard target database. This implies that the target database must be local and must have a high-speed, low-latency network.

Because real-time query standby can participate in Maximum Protection configuration (no data loss), you can effectively architect true real-time reporting configurations. Since data is synchronously replicated to the standby database, companies that invest in redundant networks with low latency and high throughput can take advantage of this benefit. Fortunately, the majority of the companies do not have real-time requirements for reports. Companies that can opt for near real-time reports can implement Maximum Availability or Maximum Performance configurations.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 11g Topics