In today’s article we will talk about Real-Time Apply vs Archive Log Apply Test.
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.
1. We create a table on the Primary side to check that the Real – Time Query is working.
|
1 2 3 4 5 6 7 |
SQL> conn test/test Connected. SQL> show user USER is "TEST" SQL> create table employees_yedek as select * from hr.employees; Table created. |
2. We check the table immediately from the standby side.
|
1 2 3 4 5 |
SQL> select count(*) from test.employees_yedek; COUNT(*) ---------- 107 |
3. By disabling Real – Time Apply, Real – Time Apply is disabled to see the Apply process as the Archive is created.
|
1 2 3 4 5 6 7 |
SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database recover managed standby database disconnect; Database altered. |
4. We create a new table on the primary side.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> select table_name from dba_tables where owner='HR'; TABLE_NAME ------------------------------ COUNTRIES JOB_HISTORY EMPLOYEES JOBS LOCATIONS REGIONS DEPARTMENTS 7 rows selected. SQL> create table countries_yedek as select * from hr.countries; Table created. |
5. On the standby side, we query the relevant table.
|
1 2 3 4 5 |
SQL> select count(*) from test.countries_yedek; select count(*) from test.countries_yedek * ERROR at line 1: ORA-00942: table or view does not exist |
6. Log switch operation is performed and we check whether the archive log is processed on the standby side.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> alter system switch logfile; System altered. SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; MAX(SEQUENCE#) THREAD# -------------- ---------- 66 1 34 2 SQL> select count(*) from test.countries_yedek; select count(*) from test.countries_yedek * ERROR at line 1: ORA-00942: table or view does not exist |
7. 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.]
|
1 2 3 4 5 6 7 8 |
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# APPLIED ---------- --------- 65 YES 66 NO 87 rows selected. |
8. 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; MAX(SEQUENCE#) THREAD# -------------- ---------- 69 1 35 2 |
9.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.]
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# APPLIED ---------- --------- 65 YES 66 YES 67 YES 68 YES 69 NO 91 rows selected. |
10. When we see that archive 69 has been processed, we query whether the table has arrived.
|
1 2 3 4 5 |
SQL> select count(*) from test.countries_yedek; COUNT(*) ---------- 25 |
