Real-Time Query in 11gR2

You may have noticed the spectacular 11g (R1) New Feature Real-Time Query already, that makes it possible to use a Physical Standby Database for Reporting while it is still applying Redo-Protocol received from the Primary Database. There are some additions to that feature in 11gR2.

DGMGRL> show configuration
Configuration - myconf
 Protection Mode: MaxAvailability
 Databases:
 prima - Primary database
 physt - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database physt
Database - physt
 Role:            PHYSICAL STANDBY
 Intended State:  APPLY-ON
 Transport Lag:   0 seconds
 Apply Lag:       0 seconds
 Real Time Query: ON
 Instance(s):
 physt
Database Status:
SUCCESS

I think the red sections above are new in 11gR2 and make it easier to recognize that we are using Real-Time Query – therefore we may want to license Active Data Guard, if not done already :-)

Now let’s suppose we make use of the Physical Standby for Reporting but want to make sure that the Reports are up to date with the Primary Changes:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> col name for a30
col value for a20
select name,value from v$dataguard_stats;
NAME                           VALUE
------------------------------ --------------------
transport lag                  +00 00:00:00
apply lag                      +00 00:00:00
apply finish time              +00 00:00:00.000
estimated startup time         25

There is no Apply Lag right now between Primary and Standby. The shown value of OPEN_MODE is also a New Feature in 11gR2, as well as the following parameter:

SQL> connect scott/tiger@physt
Connected.
SQL> alter session set STANDBY_MAX_DATA_DELAY=1;
Session altered.

That setting means that in Scott’s session on the Physical Standby, we will only tolerate a Lag Time of 1 second between the Data on the Standby and the Data on the Primary. A value of zero would require full synchronicity. I will make sure that this condition cannot be met by interrupting the Redo Transport from Primary to Standby from another session:

SQL> connect sys/oracle@prima as sysdba
Connected.
SQL> alter system set log_archive_dest_state_2=defer;
System altered.

Have you noticed that there are now 31 Log Archive Destinations, by the way? I will now change data on the Primary while the Standby cannot apply, thereby creating a Transport Lag and also an Apply Lag. It’s easier for me to demonstrate it that way as by generating a so high load on the Primary that this leads to a significant Apply Lag on the Standby because MRP can’t keep the pace. Same session:

SQL> connect scott/tiger@prima

Connected.
SQL> select * from dept;
 DEPTNO DNAME          LOC
---------- -------------- -------------
 10 ACCOUNTING     NEW YORK
 20 RESEARCH       DALLAS
 30 ACCOUNTING     CHICAGO
 40 OPERATIONS     BOSTON
SQL> update dept set dname='A' where deptno=10;
1 row updated.
SQL> commit;
Commit complete.

Back to the Scott session on the Physical Standby:

SQL> select * from dept;
select * from dept
 *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-03172: STANDBY_MAX_DATA_DELAY of 1 seconds exceeded

The Standby is lagging too far behind the demanded maximum Lag Time. In 11gR1, this would have given the old result set like that:

SQL> alter session set STANDBY_MAX_DATA_DELAY=none;

Session altered.

SQL> select * from dept;

 DEPTNO DNAME          LOC
---------- -------------- -------------
 10 ACCOUNTING     NEW YORK
 20 RESEARCH       DALLAS
 30 ACCOUNTING     CHICAGO
 40 OPERATIONS     BOSTON

Another 11gR2 New Feature is the option to synchronize the Standby explicitly before a query:

SQL> alter session set STANDBY_MAX_DATA_DELAY=1;
 
 Session altered.
SQL> alter session sync with primary;
ERROR:
ORA-03173: Standby may not be synced with primary

SQL> host oerr ora 3173
03173, 00000, "Standby may not be synced with primary"
// *Cause:  ALTER SESSION SYNC WITH PRIMARY did not work because either the
//          standby was not synchronous or in a data-loss mode with regard
//          to the primary, or the standby recovery was terminated.
// *Action: Make the standby synchronous and no-data-loss with regard
//          to the primary.  Make the standby recovery active.

The SYNC is not possible because of the DEFER I did above. I renable Redo Transport and then try again:

SQL> connect sys/oracle@prima as sysdba
Connected.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> connect scott/tiger@physt
Connected.
SQL> alter session set STANDBY_MAX_DATA_DELAY=1;
Session altered.
SQL> alter session sync with primary;

Session altered.

Conclusion: With the 11gR2 addition STANDBY_MAX_DATA_DELAY, we can now enforce a certain level of synchronicity during the usage of Real-Time Query in order to make the results we get from the Physical Standby reliable.

About these ads

, , , ,

  1. #1 by Asif Momen on September 27, 2010 - 13:40

    Nice one. I enjoy reading your posts as they are very consice.

  2. #2 by Uwe Hesse on October 7, 2010 - 07:24

    Thank you, Asif, for the nice feedback!

  3. #3 by Daniel.kolar on November 11, 2010 - 14:35

    Really nice, I will upgrade to 11gR2 as soon is possible

  4. #4 by shaohua Qu on July 29, 2011 - 01:30

  5. #5 by Uwe Hesse on July 29, 2011 - 04:45

    shaohua,
    this forum question has nothing to do with Real-Time Query. I look at the OTN Data Guard Forum regularly anyways. Don’t have a clue about that particular problem, though. I recommend that you open up a SR with Oracle Support for this

  6. #6 by Vikas on October 6, 2011 - 22:34

    All your posts are very good with good real time examples which makes your posts very intuitive and easy to understand.

  7. #7 by Uwe Hesse on October 8, 2011 - 08:08

    Glad that you liked them, Vikas!

  8. #8 by Aseem on December 2, 2011 - 18:43

    I am configuring Oracle 11gR2 active data guard on Sun Solaris, but as soon as I start recover using online logfile my read only standby database goes in Mount state itself.. Just want to give you and example of the same…

    SQL> SELECT database_role, open_mode FROM v$database;

    DATABASE_ROLE OPEN_MODE
    —————- ——————–
    PHYSICAL STANDBY MOUNTED

    SQL> alter database open read only;

    Database altered.

    SQL> SELECT database_role, open_mode FROM v$database;

    DATABASE_ROLE OPEN_MODE
    —————- ——————–
    PHYSICAL STANDBY READ ONLY

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

    Database altered.

    SQL> SELECT database_role, open_mode FROM v$database;

    DATABASE_ROLE OPEN_MODE
    —————- ——————–
    PHYSICAL STANDBY MOUNTED

    any suggestion what I am missing here to open my database in read only mode with enabled recovery at the same time.. (Standby Logfiles are available in standby DB)

  9. #9 by Uwe Hesse on December 3, 2011 - 10:36

    That is not how it is supposed to be. Looks like the Standby Logs cannot get used for some reason. Check if they have the same size as the Online Logs of the Primary. Also check the Log Transport Mode – it must not be ARCH. Look at the alert.log of the Standby when you try the command you mentioned.

  10. #10 by Aseem on December 6, 2011 - 15:17

    Hi Uwe,

    I am glad that the problem which I mentioned above is sorted out now..It was really bad at my end..I did an upgrade of my 10gR2 database to 11G and forgot to set Compatible as per 11G :(

    It took 3 days to figure this out not sure why my DB alert not registering this thing..anyways I am happy it is working as expected now :) …..and Just want to tell that I really enjoy reading your documents..

    Cheers

  11. #11 by Uwe Hesse on December 6, 2011 - 18:25

    Thank you for sharing this information! And good to hear that you could resolve that problem :-)

  12. #12 by quirkyoracle on January 24, 2012 - 17:18

    In my testing I found that it is necessary to issue:

    alter system set log_archive_dest_state_2=defer;
    alter system switch logfile;

    Otherwise the ‘defer’ is not picked up immediately and updates continue to propagate to the standby database (you can imagine my surprise when I saw that happen!).

  13. #13 by Uwe Hesse on January 24, 2012 - 17:34

    Thanks for stopping by and sharing that information!
    It is probably better to do
    edit database physt set property logshipping=off;
    instead of the
    alter system set log_archive_dest_state_2=defer;

    I should practice what I preach: When using the Broker, use Broker commands instead of alter system :-)

  1. Real-Time Query for Read-Mostly Applications « The Oracle Instructor
  2. Real-Time Query and Automatic Block Media Recovery in 11gR2 « The Oracle Instructor
  3. Impressions from the #UKOUG2012 « The Oracle Instructor
  4. Parameter to prevent license violation with Active Data Guard | The Oracle Instructor

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,892 other followers

%d bloggers like this: