In today’s article we will talk about Real-Time Apply vs Archive Log Apply Test.<\/p>\n
After setting our Data Guard to Real -Time Apply, we can test as follows whether the changes to be made on the Primary side are instantly visible on the Standby side. In order to query the data on the standby side, we need to activate the Data Guard.<\/p>\n
1. We create a table on the Primary side to check that the Real – Time Query is working.<\/p>\n
SQL> conn test\/test\r\nConnected.\r\nSQL> show user\r\nUSER is \"TEST\"\r\nSQL> create table employees_yedek as select * from hr.employees;\r\n\r\nTable created.\r\n<\/pre>\n2.\u00a0We check the table immediately from the standby side.<\/p>\n
SQL> select count(*) from test.employees_yedek;\r\n\r\n COUNT(*)\r\n----------\r\n 107\r\n<\/pre>\n3. By disabling Real – Time Apply, Real – Time Apply is disabled to see the Apply process as the Archive is created.<\/p>\n
SQL> alter database recover managed standby database cancel;\r\n\r\nDatabase altered.\r\n\r\nSQL> alter database recover managed standby database disconnect;\r\n\r\nDatabase altered.\r\n<\/pre>\n4. We create a new table on the primary side.<\/p>\n
SQL> select table_name from dba_tables where owner='HR';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nCOUNTRIES\r\nJOB_HISTORY\r\nEMPLOYEES\r\nJOBS\r\nLOCATIONS\r\nREGIONS\r\nDEPARTMENTS\r\n\r\n7 rows selected.\r\n\r\nSQL> create table countries_yedek as select * from hr.countries;\r\n\r\nTable created.\r\n<\/pre>\n5.\u00a0On the standby side, we query the relevant table.<\/p>\n
SQL> select count(*) from test.countries_yedek;\r\nselect count(*) from test.countries_yedek\r\n *\r\nERROR at line 1:\r\nORA-00942: table or view does not exist\r\n<\/pre>\n6.\u00a0Log switch operation is performed and we check whether the archive log is processed on the standby side.<\/p>\n
SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\n\r\nSQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;\r\n\r\nMAX(SEQUENCE#) THREAD#\r\n-------------- ----------\r\n 66 1\r\n 34 2\r\n\r\nSQL> select count(*) from test.countries_yedek;\r\nselect count(*) from test.countries_yedek\r\n *\r\nERROR at line 1:\r\nORA-00942: table or view does not exist\r\n<\/pre>\n7. If it is seen that the table still does not come after the log switch, we will check whether the log with sequnce is applied on the standby side. [The archives with the first 64 sequence numbers in the query result have been removed to avoid crowding.]\n
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;\r\n\r\n SEQUENCE# APPLIED\r\n---------- ---------\r\n 65 YES\r\n 66 NO\r\n\r\n87 rows selected.\r\n<\/pre>\n8. When it is seen that it is not applied, a few more log switch operations are performed on the Primary side and the highest sequence# is learned on the Primary side.<\/p>\n
SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\nSQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\nSQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\nSQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;\r\n\r\nMAX(SEQUENCE#) THREAD#\r\n-------------- ----------\r\n 69 1\r\n 35 2\r\n<\/pre>\n9.We query which archives are processed on the standby side. [The archives with the first 64 sequence numbers in the query result have been removed to avoid crowding.]\n
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;\r\n\r\n SEQUENCE# APPLIED\r\n---------- ---------\r\n 65 YES\r\n 66 YES\r\n 67 YES\r\n 68 YES\r\n 69 NO\r\n\r\n91 rows selected.\r\n<\/pre>\n10. When we see that archive 69 has been processed, we query whether the table has arrived.<\/p>\n
SQL> select count(*) from test.countries_yedek;\r\n\r\n COUNT(*)\r\n----------\r\n 25\r\n<\/pre>\n<\/p>\n
<\/div>\n