Guarantee data currency using Real-Time Apply

The note describers how Real-Time Apply guarantees data currency. The demo is performed on a previously established DR environment (see note Creating both physical and logical standby database in a DR environment).


DGMGRL> show configuration

Configuration - DB11R2_DG_CONFIG

 Protection Mode: MaxPerformance
 Databases:
 DB11R2   - Primary database
 DB11R2DR - Physical standby database
 DB11R2LS - Logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

The physical database is currently running as Active Data Guard. As shown below, there is no reply lag occurring at the moment.

DGMGRL> show database 'DB11R2DR';

Database - DB11R2DR

 Role:            PHYSICAL STANDBY
 Intended State:  APPLY-ON
 Transport Lag:   0 seconds
 Apply Lag:       0 seconds
 Real Time Query: ON
 Instance(s):
 DB11R2DR

Database Status:
SUCCESS

Set apply lag tolerance to 10 seconds on standby database

sys@db11r2dr> alter session set standby_max_data_delay=30;
ERROR:
ORA-03174: STANDBY_MAX_DATA_DELAY does not apply to SYS users

Well, let us logon as a normal user

<pre>
<pre>
<pre>sys@db11r2dr> conn guang/guang
guang@db11r2dr> alter session set standby_max_data_delay=30;
</pre>
</pre>

Simulate a reply delay by turning off apply service


DGMGRL> edit database 'DB11R2DR' set state='apply-off';


Now make some modifications on primary database

<pre>
sys@db11r2dr> conn guang/guang
guang@db11r2> insert into rta_test_t select * from rta_test_t where rownum < 1;
guang@db11r2> commit;

guang@db11r2> select count(*) from rta_test_t;

 COUNT(*)
----------
 11

Check the result from standby database

DGMGRL> show database 'DB11R2DR';

Database - DB11R2DR

 Role:            PHYSICAL STANDBY
 Intended State:  APPLY-OFF
 Transport Lag:   0 seconds
 Apply Lag:       11 seconds
 Real Time Query: OFF
 Instance(s):
 DB11R2DR

guang@db11r2dr> select count(*) from rta_test_t;

 COUNT(*)
----------
 10

Some time after, run the check again

DGMGRL> show database 'DB11R2DR';

Database - DB11R2DR

 Role:            PHYSICAL STANDBY
 Intended State:  APPLY-OFF
 Transport Lag:   0 seconds
 Apply Lag:       27 seconds
 Real Time Query: OFF
 Instance(s):
 DB11R2DR

guang@db11r2dr> select count(*) from rta_test_t;
select * from rta_test_t
*
ERROR at line 1:
ORA-03172: STANDBY_MAX_DATA_DELAY of 30 seconds exceeded

Restart apply service

DGMGRL> edit database 'DB11R2DR' set state='apply-off';

Short after


DGMGRL> show database 'DB11R2DR';

Database - DB11R2DR

 Role:            PHYSICAL STANDBY
 Intended State:  APPLY-ON
 Transport Lag:   0 seconds
 Apply Lag:       0 seconds
 Real Time Query: ON
 Instance(s):
 DB11R2DR

Database Status:
SUCCESS


Now the query on standby returns latest data value.

guang@db11r2dr> select count(*) from rta_test_t;

 COUNT(*)
----------
 11

To check current apply lag history


select * from v$standby_event_histogram where name = 'apply lag' order by last_time_updated;

NAME                                 TIME UNIT                  COUNT LAST_TIME_UPDATED
------------------------------ ---------- ---------------- ---------- --------------------
.....
apply lag                              58 seconds                   4 02/20/2011 21:52:50
apply lag                              59 seconds                   3 02/20/2011 21:52:51
apply lag                               2 minutes                 223 02/20/2011 21:53:50
apply lag                               2 seconds                 346 02/20/2011 21:54:26
apply lag                               0 seconds               22365 02/20/2011 21:55:01

Real-time Query Restrictions

The apply lag control and Redo Apply synchronization mechanisms described above require that the client be connected and issuing queries to a physical standby database that is in real-time query mode. The following additional restrictions apply if  STANDBY_MAX_DATA_DELAY is set to 0 or if the ALTER SESSION SYNC WITH PRIMARY SQL statement is used:

The standby database must receive redo data via the SYNC transport.

The redo transport status at the standby database must be SYNCHRONIZED and the primary database must be running in either maximum protection mode or maximum availability mode.

Real-time apply must be enabled.

 

Advertisements

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

%d bloggers like this: