Pickleball spielen

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.

A Practical Guide to Data Guard: Real-Time Query https://t.co/77dmMULkZF #Oracle Learning Streams: FREE for OCP, OCE pic.twitter.com/5Nv9v3Q9OX

— Uwe Hesse (@UweHesse) October 23, 2015