Wednesday, June 21, 2023

Oracle Golden Gate 21.3 Micorservice support for DDL replication

In this blog we will see how Oracle Golden 21.3 Microservice supports DDL replication


Verify with our current Golden Gate replication setup, if DDL operations are replicated.

Source parameters:
Type: INTEGRATED
EXTRACT exts
USERIDALIAS cggadmin DOMAIN OracleGoldenGate
EXTTRAIL src/es
SOURCECATALOG OPDB1
DDL INCLUDE MAPPED
TABLE OPDB1_PUBLIC_USER.*;

Receiver parameters:
Type: Parallel Nonintegerated
REPLICAT rept
USERIDALIAS ggadmin_DBCASLNT DOMAIN OracleGoldenGate
--DDL EXCLUDE ALL
DDLERROR default discard
REPERROR (default,discard)
DDLOPTIONS REPORT
SOURCECATALOG OPDB1
MAP OPDB1_PUBLIC_USER.*, TARGET OPDB1_PUBLIC_USER.*;


Operation 1: Create table

Source PDB:

SQL> create table ggreptst_21jun23 as select 1 as id from dual;
Table created.

SQL> desc ggreptst_21jun23
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER

SQL> select * from ggreptst_21jun23;
        ID
----------
         1
SQL>

SQL> select table_name,num_rows,last_analyzed from user_Tables order by 1;
TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- --------------------
GGREPTST_21JUN23                        1 21/JUN/2023 07:31:25 <<<< this one is the true complete GG flyby
JT1                              25000000 14/MAY/2023 20:25:13 <<<< until here expdp/impdp (bottom up), remember we inserted 1 record to test the replication initially
JT2                              25000000 14/MAY/2023 20:25:47
T1                                      2 14/MAY/2023 16:17:44
TESTIDXBLOAT                       960004 14/MAY/2023 16:18:10
TESTIDXBLOAT_TMP                     9999 14/MAY/2023 16:18:22
TESTIDXBLOAT_TMP2                    9999 10/MAY/2023 23:34:58
TESTIDXBLOAT_TMP3                    9999 10/MAY/2023 23:41:42
TESTIDXBLOAT_TMP4                    9999 14/MAY/2023 16:18:22
TESTIDXBLOAT_TMP5                    9999 14/MAY/2023 16:18:22
TESTIDXBLOAT_TMP6                    9999 14/MAY/2023 16:18:22
TESTIDXBLOAT_TMP7                    9999 11/MAY/2023 00:09:52
12 rows selected.

SQL> select object_name,object_type,created,last_ddl_time from user_objects order by 1;
OBJECT_NAME                    OBJECT_TYPE                    CREATED              LAST_DDL_TIME
------------------------------ ------------------------------ -------------------- --------------------
GETSEQFLUSH                    PROCEDURE                      18/JUN/2023 12:21:32 18/JUN/2023 12:21:32 <<< not sure why this object is created here, I have ggadmin schema
GGREPTST_21JUN23               TABLE                          21/JUN/2023 07:31:25 21/JUN/2023 07:31:25 <<< our table,created to test ddl
JT1                            TABLE                          14/MAY/2023 16:46:30 14/MAY/2023 16:46:30
JT2                            TABLE                          14/MAY/2023 17:05:24 14/MAY/2023 17:05:24
MOVETARGETHWM                  PROCEDURE                      18/JUN/2023 12:21:32 18/JUN/2023 12:21:32 <<< not sure why this object is created here, I have ggadmin schema
REPLICATESEQUENCE              PROCEDURE                      18/JUN/2023 12:21:32 18/JUN/2023 12:21:32 <<< not sure why this object is created here, I have ggadmin schema
SEQTRACE                       PROCEDURE                      18/JUN/2023 12:21:32 18/JUN/2023 12:21:32 <<< not sure why this object is created here, I have ggadmin schema
T1                             TABLE                          24/APR/2023 19:49:25 24/APR/2023 19:49:25
TESTIDXBLOAT                   TABLE                          10/MAY/2023 23:06:40 10/MAY/2023 23:10:44
TESTIDXBLOAT_IN                INDEX                          10/MAY/2023 23:10:42 10/MAY/2023 23:10:42
TESTIDXBLOAT_PK                INDEX                          10/MAY/2023 23:07:58 10/MAY/2023 23:07:58
TESTIDXBLOAT_TMP               TABLE                          10/MAY/2023 23:28:25 10/MAY/2023 23:28:25
TESTIDXBLOAT_TMP2              TABLE                          10/MAY/2023 23:34:58 10/MAY/2023 23:34:58
TESTIDXBLOAT_TMP3              TABLE                          10/MAY/2023 23:41:42 10/MAY/2023 23:41:42
TESTIDXBLOAT_TMP4              TABLE                          10/MAY/2023 23:49:06 10/MAY/2023 23:49:06
TESTIDXBLOAT_TMP5              TABLE                          10/MAY/2023 23:53:15 10/MAY/2023 23:53:15
TESTIDXBLOAT_TMP6              TABLE                          11/MAY/2023 00:06:26 11/MAY/2023 00:06:26
TESTIDXBLOAT_TMP7              TABLE                          11/MAY/2023 00:09:52 11/MAY/2023 00:09:52
UPDATESEQUENCE                 PROCEDURE                      18/JUN/2023 12:21:32 18/JUN/2023 12:21:32 <<< not sure why this object is created here, I have ggadmin schema
19 rows selected.

SQL>

Target NonCDB:

SQL> desc GGREPTST_21JUN23
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
SQL>

SQL> select * from GGREPTST_21JUN23;
        ID
----------
         1

SQL> select table_name,num_rows,last_analyzed from user_Tables order by 1;
TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- --------------------
GGREPTST_21JUN23                        1 21/JUN/2023 07:31:54 <<<< this one is the true complete GG flyby, see the last analyzed timestamps difference
JT1                              25000000 14/MAY/2023 20:25:13
JT2                              25000000 14/MAY/2023 20:25:47
T1                                      2 14/MAY/2023 16:17:44
TESTIDXBLOAT                       960004 14/MAY/2023 16:18:10
TESTIDXBLOAT_TMP                     9999 14/MAY/2023 16:18:22
TESTIDXBLOAT_TMP2                    9999 10/MAY/2023 23:34:58
TESTIDXBLOAT_TMP3                    9999 10/MAY/2023 23:41:42
TESTIDXBLOAT_TMP4                    9999 14/MAY/2023 16:18:22
TESTIDXBLOAT_TMP5                    9999 14/MAY/2023 16:18:22
TESTIDXBLOAT_TMP6                    9999 14/MAY/2023 16:18:22
TESTIDXBLOAT_TMP7                    9999 11/MAY/2023 00:09:52
12 rows selected.

SQL> select object_name,object_type,created,last_ddl_time from user_objects order by 1;
OBJECT_NAME                    OBJECT_TYPE                    CREATED              LAST_DDL_TIME
------------------------------ ------------------------------ -------------------- --------------------
GETSEQFLUSH                    PROCEDURE                      18/JUN/2023 21:17:43 18/JUN/2023 21:17:43 <<< not sure why this object is created here, I have ggadmin schema
GGREPTST_21JUN23               TABLE                          21/JUN/2023 07:31:28 21/JUN/2023 07:31:28 <<< our DDL test table
JT1                            TABLE                          18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
JT2                            TABLE                          18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
MOVETARGETHWM                  PROCEDURE                      18/JUN/2023 21:17:43 18/JUN/2023 21:17:43 <<< not sure why this object is created here, I have ggadmin schema
REPLICATESEQUENCE              PROCEDURE                      18/JUN/2023 21:17:43 18/JUN/2023 21:17:43 <<< not sure why this object is created here, I have ggadmin schema
SEQTRACE                       PROCEDURE                      18/JUN/2023 21:17:43 18/JUN/2023 21:17:43 <<< not sure why this object is created here, I have ggadmin schema
T1                             TABLE                          18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
TESTIDXBLOAT                   TABLE                          18/JUN/2023 21:04:36 18/JUN/2023 21:17:48
TESTIDXBLOAT_IN                INDEX                          18/JUN/2023 21:17:44 18/JUN/2023 21:17:47
TESTIDXBLOAT_PK                INDEX                          18/JUN/2023 21:17:48 18/JUN/2023 21:17:48
TESTIDXBLOAT_TMP               TABLE                          18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
TESTIDXBLOAT_TMP2              TABLE                          18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
TESTIDXBLOAT_TMP3              TABLE                          18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
TESTIDXBLOAT_TMP4              TABLE                          18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
TESTIDXBLOAT_TMP5              TABLE                          18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
TESTIDXBLOAT_TMP6              TABLE                          18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
TESTIDXBLOAT_TMP7              TABLE                          18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
UPDATESEQUENCE                 PROCEDURE                      18/JUN/2023 21:17:43 18/JUN/2023 21:17:44 <<< not sure why this object is created here, I have ggadmin schema
19 rows selected.

Operation 2: Add a column to GGREPTST_21JUN23 table

Source table:

SQL> alter table GGREPTST_21JUN23 add (name varchar2(100));
Table altered.
SQL>

07:52:54 SQL> desc GGREPTST_21JUN23
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             NUMBER
 NAME                                                           VARCHAR2(100)

07:52:58 SQL> select * from GGREPTST_21JUN23;
        ID NAME
---------- --------------------
         1
Elapsed: 00:00:00.01
07:54:09 SQL>

07:55:46 SQL> select object_name,object_type,created,last_ddl_time from user_objects order by 1;
OBJECT_NAME                    OBJECT_TYPE                    CREATED              LAST_DDL_TIME
------------------------------ ------------------------------ -------------------- --------------------
..
GGREPTST_21JUN23               TABLE                          21/JUN/2023 07:31:25 21/JUN/2023 07:51:43 <<< so this is the DDL time


Target table:

07:53:04 SQL> desc GGREPTST_21JUN23
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             NUMBER
 NAME                                                           VARCHAR2(100)

07:55:07 SQL> select * from GGREPTST_21JUN23;
        ID NAME
---------- ----------------------------------------
         1
Elapsed: 00:00:00.00
07:55:11 SQL>

07:55:36 SQL> select object_name,object_type,created,last_ddl_time from user_objects order by 1;
OBJECT_NAME                    OBJECT_TYPE                    CREATED              LAST_DDL_TIME
------------------------------ ------------------------------ -------------------- --------------------
..
GGREPTST_21JUN23               TABLE                          21/JUN/2023 07:31:28 21/JUN/2023 07:51:47 <<< so this is the DDL time
It looks like we have a 4sec lag; which is quite good.


Operation 3: Truncate the table

Source:

07:57:18 SQL> truncate table GGREPTST_21JUN23;
Table truncated.
Elapsed: 00:00:00.20

07:57:25 SQL> select * from GGREPTST_21JUN23;
no rows selected
Elapsed: 00:00:00.03
07:57:39 SQL>

07:57:39 SQL> select object_name,object_type,created,last_ddl_time from user_objects order by 1;
OBJECT_NAME                    OBJECT_TYPE                    CREATED              LAST_DDL_TIME
------------------------------ ------------------------------ -------------------- --------------------
..
GGREPTST_21JUN23               TABLE                          21/JUN/2023 07:31:25 21/JUN/2023 07:57:25 <<< last ddl time

Target:

07:55:41 SQL> select * from GGREPTST_21JUN23;
no rows selected
Elapsed: 00:00:00.00
07:57:32 SQL>

07:57:32 SQL> select object_name,object_type,created,last_ddl_time from user_objects order by 1;
OBJECT_NAME                    OBJECT_TYPE                    CREATED              LAST_DDL_TIME
------------------------------ ------------------------------ -------------------- --------------------
..
GGREPTST_21JUN23               TABLE                          21/JUN/2023 07:31:28 21/JUN/2023 07:57:28 <<< last ddl time
this time 3secs lag. Note my machines are in sync in terms of time :)

Operation 4: Drop the table

Source:

07:59:02 SQL> drop table GGREPTST_21JUN23 purge;
Table dropped.
Elapsed: 00:00:00.78
08:00:11 SQL>

08:00:32 SQL> select * from GGREPTST_21JUN23;
select * from GGREPTST_21JUN23
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.06

08:00:41 SQL> select count(1) from user_objects where upper(object_name)='GGREPTST_21JUN23';
  COUNT(1)
----------
         0
Elapsed: 00:00:00.69
08:01:18 SQL>

Target:

08:00:27 SQL> select * from GGREPTST_21JUN23;
select * from GGREPTST_21JUN23
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.00

08:00:46 SQL> select count(1) from user_objects where upper(object_name)='GGREPTST_21JUN23';
  COUNT(1)
----------
         0
Elapsed: 00:00:00.14
08:01:10 SQL>

How does it look in ADMIN console:

Extract:
                                        


Replicat:






So this looks fine, we created a table, modified it, truncated it, dropped it. 

All of them reflected fine with our current replication setting.

Next Step: We perform some stress test on GG Replication.

at
Labels: , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Flashback data archive steps

 Objective: Ways to track DML changes in a table Detailed objective: We should be able to track the changes in the table at any point in tim...