If real-time apply is enabled, the apply services can apply redo data without waiting for the current standby redo logfile to be archived. This allows faster role transitions because you avoid waiting for a redo log to be transported to the standby database and then applied. In this example, we'll see how changes are transferred and applied to the standby database. The redo log that includes changes is not archived on primary.
LGWR
. Run the following query on the primary database and check the log archive destination configuration.SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------- -------- ---------- log_archive_dest_2 string SERVICE=INDIA LGWR ASYNC VALID_FOR =(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=INDIA_UN
USING CURRENT LOGFILE
option.SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; Database altered.
MRP0
process is APPLYING LOG
:SQL> SELECT THREAD#,SEQUENCE#,PROCESS,CLIENT_PROCESS,STATUS,BLOCK#,BLOCKS FROM V$MANAGED_STANDBY; THREAD# SEQUENCE# PROCESS CLIENT_P STATUS BLOCK# BLOCKS ------- ---------- --------- -------- -------- -------- ---------- 0 0 ARCH ARCH CONNECTED 0 0 0 0 ARCH ARCH CONNECTED 0 0 0 0 ARCH ARCH CONNECTED 0 0 0 0 ARCH ARCH CONNECTED 0 0 1 149 ARCH ARCH CLOSING 61440 1244 0 0 RFS N/A IDLE 0 0 1 150 RFS LGWR IDLE 8823 1 1 150 MRP0 N/A APPLYING_LOG 23 204800
SQL> create table packt.oracle as select * from scott.emp; Table created. SQL> select count(*) from packt.oracle; COUNT(*) ---------- 81920
The redo blocks for the primary database:
SQL> SELECT THREAD#,SEQUENCE#,PROCESS,CLIENT_PROCESS,STATUS,BLOCK#,BLOCKS FROM V$MANAGED_STANDBY; THREAD# SEQUENCE# PROCESS CLIENT_P STATUS BLOCK# BLOCKS ------- -------- ------- ------ --------- ---------- ---------- 1 143 ARCH ARCH CLOSING 1 2 0 0 ARCH ARCH CONNECTED 0 0 0 0 ARCH ARCH CONNECTED 0 0 1 149 ARCH ARCH CLOSING 61440 1244 1 146 ARCH ARCH CLOSING 2049 1868 1 150 LNS LNS WRITING 9016 1
The redo blocks for the standby database:
SQL> SELECT THREAD#,SEQUENCE#,PROCESS,CLIENT_PROCESS,STATUS,BLOCK#,BLOCKS FROM V$MANAGED_STANDBY; THREAD# SEQUENCE# PROCESS CLIENT_P STATUS BLOCK# BLOCKS ------- -------- ------- ------ ----------- ---------- ---------- 0 0 ARCH ARCH CONNECTED 0 0 0 0 ARCH ARCH CONNECTED 0 0 0 0 ARCH ARCH CONNECTED 0 0 0 0 ARCH ARCH CONNECTED 0 0 1 149 ARCH ARCH CLOSING 61440 1244 0 0 RFS N/A IDLE 0 0 1 150 RFS LGWR IDLE 8910 1 1 150 MRP0 N/A APPLYING_LOG 8910 204800
V$DATAGUARD_STATS
view in terms of time. Run the following query on the standby database:SQL> SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like 'apply lag'; NAME VALUE DATUM_TIME TIME_COMPUTED ---------- ------------ ------------------- ------------------- apply lag +00 00:00:00 08/05/2012 22:14:16 08/05/2012 22:14:18
The apply lag metric is zero, which means there's no lag. This value is calculated with the data periodically received from the primary database. The DATUM_TIME
parameter shows when this data was last sent from primary to the standby database. The TIME_COMPUTED
column shows when the apply lag value was calculated. Normally, the difference between these two values should be less than 30 seconds.
The following query to the V$STANDBY_EVENT_HISTOGRAM
view shows the history of apply lag values since the standby instance was last started:
SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0; NAME TIME UNIT COUNT LAST_TIME_UPDATED ---------- ---------- ------------- -------- ----------------- apply lag 0 seconds 431 08/05/2012 22:14:21 apply lag 1 seconds 7 08/05/2012 22:13:31
SQL> select count(*) from packt.oracle; COUNT(*) ---------- 81920
We can see that the changes were applied on the standby database without waiting for a log switch either on the primary or standby database. This is achieved by the LGWR
redo transport mode on primary and real-time Redo Apply mode on the standby database.
The recommended Redo Apply method, real-time apply, is verified and we've seen that the redo switch is not required to apply changes to the standby database in the real-time apply mode.
In order to check if network latency and bandwidth have any effect on real-time apply, run an insert operation on the primary and commit. Right after the commit, query the physical standby database to see if the changes are applied immediately. You may see some seconds of delay, which is most probably caused by network performance.