Beiträge getaggt mit Active Data Guard

Real-Time Query in 11gR2

Veröffentlicht von Uwe Hesse in TOI am September 27, 2010

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.


			

											
, , , , 

										

												17 Kommentare