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.
- In order to use real-time apply, the redo transport service from primary to standby must use
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
- In the standby database, start Redo Apply using the
USING CURRENT LOGFILEoption.SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; Database altered.
- Check the current status of processes related to Data Guard in the physical standby database. You need to verify that the status of the
MRP0process isAPPLYING 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
- Create a table in the primary database by selecting the data logs from another table.
SQL> create table packt.oracle as select * from scott.emp; Table created. SQL> select count(*) from packt.oracle; COUNT(*) ---------- 81920
- Now monitor the number of redo blocks for the current redo log, written on primary, sent to standby, and applied on standby.
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
- You can also check the apply lag on the standby database using the
V$DATAGUARD_STATSview 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_TIMEparameter shows when this data was last sent from primary to the standby database. TheTIME_COMPUTEDcolumn 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_HISTOGRAMview 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
- On the physical standby database (which is read-only and in the real-time apply mode), query the row number for the table that we created on primary.
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
LGWRredo 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.