[Oracle] [DATAGUARD] Method for confirming the state of logical standby in the same period

Oracle's Dataguard environment has two types of physical standby and logical standby.
Physical standby is transmitted redo to the standby end, and then by the standby side of the MRP process to apply the redo to achieve the same time effect.
LOGICAL Standby is the transmission redo to the standby end, then by the standby side of the LSP process calls Logminer to extract the SQL text, and then execute the SQL text to achieve the same time effect.
Here is a simple way to confirm the status of the same period:

ODM TEST Case
===================
Name = Tc#1010_1

####primary####

Sql> Set Line 300
Sql> select Database_role,open_mode from V$database;
Select Instance_name,status from V$instance;
Database_role Open_mode
------------------------------------------------ ------------------------------------------------------------
PRIMARY READ WRITE

Sql>

instance_name STATUS
------------------------------------------------ ------------------------------------
ORCL OPEN

Sql> create user user_a identified by user_a;

ユーザーが made into されました.

Sql> Grant DBA to User_a;

Copyright limited to が success しました.

Sql> Conn User_a/user_a
Connect Yobitsugi されました.
Sql> CREATE TABLE Tbl_dgtest (
ID char (8),
User_name VARCHAR2 (250)); 2 3

Table が made into されました.

Sql> Declare
VID char (8);
Vtext VARCHAR2 (250);
Begin
Dbms_random.seed (UID);
For I in 1..1000000
Loop
VID: = To_char (i, ' FM00000000 ');
Vtext: = dbms_random.string (' x ', 16);
INSERT into Tbl_dgtest (ID, user_name) VALUES (VID, vtext);
if (mod (i, +) = 0) Then
Commit
End If;
End Loop;
Commit
End
/2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Pl/sqlプロシージャが normal occupies finished しました.

Sql> Select COUNT (*) from tbl_dgtest;

COUNT (*)
----------
1000000

Sql>


####standby####

Sql> Set Line 300
Sql> select Database_role,open_mode from V$database;
Select Instance_name,status from V$instance;
Database_role Open_mode
------------------------------------------------ ------------------------------------------------------------
LOGICAL STANDBY READ write★logical STANDBY

Sql>

instance_name STATUS
------------------------------------------------ ------------------------------------
Orcls OPEN

sql> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; ★begin Applying SQL

データベースが Slew more されました.

Sql> Conn User_a/user_a
Connect Yobitsugi されました.
Sql> Select COUNT (*) from tbl_dgtest;

COUNT (*)
----------
1000000★

Sql>

ODM TEST Case
===================
Name = Tc#1010_2

####on primary,insert 1000000 rows into tbl_dgtest####

Sql> SELECT thread#, sequence# from V$log WHERE status= ' current '; ★you would check that sequence# was growing

thread# sequence#
---------- ----------
1 96

Sql> SELECT thread#, sequence# from V$log WHERE status= ' current '; ★

thread# sequence#
---------- ----------
1 101

Sql> SELECT thread#, sequence# from V$log WHERE status= ' current '; ★

thread# sequence#
---------- ----------
1 102


####use dba_logstdby_log to Moniter SQL application on standby####

Sql> SELECT sequence#,first_change#,next_change#,timestamp,applied from Dba_logstdby_log;

sequence# first_change# next_change# TIMESTAMP Applied
---------- ------------- ------------ ------------------- ------------------------
756171 757560 2017-03-01 20:29:55 YES
Omit >
827738 833358 2017-09-28 14:20:09 YES
833358 834365 2017-09-28 14:57:39 current★93
94 834365 834411 2017-09-28 14:57:39 NO

sequence# first_change# next_change# TIMESTAMP Applied
---------- ------------- ------------ ------------------- ------------------------
834411 834994 2017-09-28 14:57:40 NO
834994 837710 2017-09-28 15:01:28 NO
837710 840227 2017-09-28 15:02:06 NO
98 840227 842731 2017-09-28 15:02:39 NO
842731 845235 2017-09-28 15:03:16 NO
845235 847751 2017-09-28 15:03:49 NO
101 847751 850377 2017-09-28 15:04:27 NO

29 Lines が Drivers されました.

sql> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; ★begin SQL Application

データベースが Slew more されました.

Sql> SELECT sequence#,first_change#,next_change#,timestamp,applied from Dba_logstdby_log;

sequence# first_change# next_change# TIMESTAMP Applied
---------- ------------- ------------ ------------------- ------------------------
756171 757560 2017-03-01 20:29:55 YES
Omit >
827738 833358 2017-09-28 14:20:09 YES
833358 834365 2017-09-28 14:57:39 yes★sql application was over
94 834365 834411 2017-09-28 14:57:39 yes★sql application was over

sequence# first_change# next_change# TIMESTAMP Applied
---------- ------------- ------------ ------------------- ------------------------
834411 834994 2017-09-28 14:57:40 yes★sql application was over
834994 837710 2017-09-28 15:01:28 yes★sql application was over
837710 840227 2017-09-28 15:02:06 current★sql application is on going
98 840227 842731 2017-09-28 15:02:39 current★sql application is on going
842731 845235 2017-09-28 15:03:16 no★sql application is on line
845235 847751 2017-09-28 15:03:49 no★
101 847751 850377 2017-09-28 15:04:27 no★

29 Lines が Drivers されました.

Sql>/

sequence# first_change# next_change# TIMESTAMP Applied
---------- ------------- ------------ ------------------- ------------------------
756171 757560 2017-03-01 20:29:55 YES
Omit >
827738 833358 2017-09-28 14:20:09 YES
833358 834365 2017-09-28 14:57:39 YES
94 834365 834411 2017-09-28 14:57:39 YES

sequence# first_change# next_change# TIMESTAMP Applied
---------- ------------- ------------ ------------------- ------------------------
834411 834994 2017-09-28 14:57:40 YES
834994 837710 2017-09-28 15:01:28 YES
837710 840227 2017-09-28 15:02:06 YES
98 840227 842731 2017-09-28 15:02:39 YES
842731 845235 2017-09-28 15:03:16 current★sql application are on going
845235 847751 2017-09-28 15:03:49 current★sql application is on going
101 847751 850377 2017-09-28 15:04:27 NO

29 Lines が Drivers されました.

Sql>/

sequence# first_change# next_change# TIMESTAMP Applied
---------- ------------- ------------ ------------------- ------------------------
756171 757560 2017-03-01 20:29:55 YES
Omit >
827738 833358 2017-09-28 14:20:09 YES
833358 834365 2017-09-28 14:57:39 YES
94 834365 834411 2017-09-28 14:57:39 YES

sequence# first_change# next_change# TIMESTAMP Applied
---------- ------------- ------------ ------------------- ------------------------
834411 834994 2017-09-28 14:57:40 YES
834994 837710 2017-09-28 15:01:28 YES
837710 840227 2017-09-28 15:02:06 YES
98 840227 842731 2017-09-28 15:02:39 YES
842731 845235 2017-09-28 15:03:16 YES
845235 847751 2017-09-28 15:03:49 current★sql application is on going
101 847751 850377 2017-09-28 15:04:27 current★sql application is on going

29 Lines が Drivers されました.

Sql>/

sequence# first_change# next_change# TIMESTAMP Applied
---------- ------------- ------------ ------------------- ------------------------
756171 757560 2017-03-01 20:29:55 YES
Omit >
827738 833358 2017-09-28 14:20:09 YES
833358 834365 2017-09-28 14:57:39 YES
94 834365 834411 2017-09-28 14:57:39 YES

sequence# first_change# next_change# TIMESTAMP Applied
---------- ------------- ------------ ------------------- ------------------------
834411 834994 2017-09-28 14:57:40 YES
834994 837710 2017-09-28 15:01:28 YES
837710 840227 2017-09-28 15:02:06 YES
98 840227 842731 2017-09-28 15:02:39 YES
842731 845235 2017-09-28 15:03:16 YES
845235 847751 2017-09-28 15:03:49 yes★sql application was over
101 847751 850377 2017-09-28 15:04:27 yes★sql application was over

29 Lines が Drivers されました.

Sql>

[Oracle] [DATAGUARD] Method for confirming the state of logical standby in the same period