Infrastructure at your Service

In a precedent blog (http://blog.dbi-services.com/discovering-shareplex-for-oracle/) about SharePlex, we presented how to setup a replication between 2 environments. In this article we will try to see how SharePlex deals with replicating DDL statements.
Before starting we present below our environment and our configuration file. We are just  replicating scott_atlas to scott_atlasrep2. We suppose that SharePlex is already configured.

Server Name Database Name Oracle Version What
atlas.localdomain SPLEXDB 12.1.0.2 Source
atlasrep2.localdomain SPLEXSTR2 12.1.0.2 Target
[[email protected] config]$ cat ddl_config_atlasrep2.cnf
datasource:o.SPLEXDB

#source tables            target tables            routing map

expand scott_atlas.%       scott_atlasrep2.%       [email protected]

 

Let’s start SharePlex on both source and target

[[email protected] ~]$ $SP_HOME/bin/sp_cop -u $NAME_FOR_ALERTING &
[1] 2617
[[email protected] ~]$

*******************************************************
* SharePlex for Oracle Startup
* Copyright 2016 Dell, Inc.
* ALL RIGHTS RESERVED.
* Protected by U.S. Patents: 7,461,103 and 7,065,538
* Version: 8.6.4.66-m64-oracle120
* VarDir : /u01/app/shareplex/vardir/splexdb_864_12_2105
* Port : 2105
*******************************************************
[[email protected] ~]$ $SP_HOME/bin/sp_cop -u $NAME_FOR_ALERTING &
[1] 2437
[[email protected] ~]$

*******************************************************
* SharePlex for Oracle Startup
* Copyright 2016 Dell, Inc.
* ALL RIGHTS RESERVED.
* Protected by U.S. Patents: 7,461,103 and 7,065,538
* Version: 8.6.4.66-m64-oracle120
* VarDir : /u01/app/shareplex/vardir/splexstr2_864_12_2105
* Port   : 2105
*******************************************************

Now let’s verify that SharePlex processes are running on both source and target

[[email protected] ~]$ $SP_HOME/bin/sp_ctrl


sp_ctrl (atlas:2105)> show

Process    Source                               Target                 State                   PID
---------- ------------------------------------ ---------------------- -------------------- ------
Capture    o.SPLEXDB                                                   Running                2618
Read       o.SPLEXDB                                                   Running                2620
Export     atlas                                atlasrep2              Running
[[email protected] ~]$  rlwrap $SP_HOME/bin/sp_ctrl

sp_ctrl (atlasrep2:2105)> show

Process    Source                               Target                 State                   PID
---------- ------------------------------------ ---------------------- -------------------- ------
Import     atlas                                atlasrep2              Running                2734
Post       o.SPLEXDB-atlas                      o.SPLEXSTR2            Running                2438

SharePlex provides many parameters to handle DDL replication. We are going to see some of them. To display SharePlex parameters, just execute

sp_ctrl (atlas:2105)> list param

SP_OCT_REPLICATE_DDL:  Possible values 0/1/2/3

Manages the replication of ALTER and TRUNCATE statements. It can have the following values

0: No replication of both ALTER and TRUNCATE

1: Only ALTER replication

2: Only TRUNCATE replication

3: Both replication of ALTER and TRUNCATE  –default value

Let’s show an example with the default value 3, ALTER and TRUNCATE DDL performed in scott_atlas schema should be replicated. From the source and the target we can see that table bonus is not empty

From the source

SQL> select name from v$database;

NAME
---------
SPLEXDB
SQL> conn scott_atlas/tiger
Connected.
SQL> select * from bonus;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
Moise      IT              2300        200

 

From the target

SQL> select name from v$database;

NAME
---------
SPLEXSTR

SQL> conn scott_atlasrep2/tiger
Connected.
SQL> select * from bonus;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
Moise      IT              2300        200

Let’s truncate table bonus and let’s add 2 new columns on the source

SQL> show user
USER is "SCOTT_ATLAS"
SQL> truncate table bonus;

Table truncated.

SQL> alter table bonus add (col_net number,col_var number);

Table altered.

SQL>  select * from bonus;

no rows selected

We can see that both DDL are replicated on the target

SQL> show user
USER is "SCOTT_ATLASREP2"
SQL> select * from bonus;

no rows selected

SQL> desc bonus
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 SAL                                                NUMBER
 COMM                                               NUMBER
 COL_NET                                            NUMBER
 COL_VAR                                            NUMBER

SP_OCT_AUTOADD_ENABLE:  Possible values 0/1

Manages newly created objects in the active configuration (objects in the configuration file). The default value is 1, meaning that newly created table in scott_atlas schema will be automatically replicated on the source. SharePlex will also replicated all corresponding DML.
Let’s create a table on the source and let’s insert some data

SQL> show user
USER is "SCOTT_ATLAS"
SQL> create table mytable (id number primary key,message long);

Table created.

SQL> insert into mytable values (1,'Ceci est un message important concernant...');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mytable;

        ID
----------  MESSAGE
------------------------------------------------------------
         1
Ceci est un message important concernant...

We can verify that the new table and corresponding data are replicated on the target.

SQL> show user
USER is "SCOTT_ATLASREP2"
SQL> select * from mytable;

        ID
----------
MESSAGE
--------------------------------------------------------------------------------
         1
Ceci est un message important concernant...

 

SP_OCT_REPLICATE_ALL_DDL: Possible values 0/1

Manages  DDL replication  for certain objects that are not listed in the configuration file. The default value is 0 and means that DDL concerning objects which are not in the active configuration will not be replicated. Let’s take an example with a toto schema (Just remember that in our configuration file only scott_atlas objects are being replicated).
From the source let’s create a table test1 in toto schema

SQL> connect toto/toto

Connected.

SQL> create table test1(id number);

 
Table created.

As expected we can verify that table test1 is not replicated in toto schema on the target

SQL> conn toto/toto
Connected.
SQL> desc test1
ERROR:
ORA-04043: object test1 does not exist

Now let’s set the value of the parameter to 1 on the source

sp_ctrl (atlas:2105)> set param SP_OCT_REPLICATE_ALL_DDL 1

And let’s create a table test2 on toto schema and let’s insert a row on the source

SQL> create table test2(id number);

Table created.

SQL> insert into test2 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test2;

        ID
----------
         1

We can see that DDL is replicated (table creation) on target but not the corresponding DML.

SQL> select * from test2;

no rows selected

SQL>

That means that if an object is not in the active replication and if the parameter SP_OCT_REPLICATE_ALL_DDL is set to 1, DDL will be replicated but data will not.

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mouhamadou Diaw
Mouhamadou Diaw

Consultant