By Mouhamadou Diaw

In a precedent blog (http://dbi-services.com/blog/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
1
2
3
4
5
6
[oracle@atlas 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

1
2
3
4
5
6
7
8
9
10
11
12
13
[oracle@atlas ~]$ $SP_HOME/bin/sp_cop -u $NAME_FOR_ALERTING &
[1] 2617
[oracle@atlas ~]$
*******************************************************
* 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
*******************************************************
1
2
3
4
5
6
7
8
9
10
11
12
13
[oracle@atlasrep2 ~]$ $SP_HOME/bin/sp_cop -u $NAME_FOR_ALERTING &
[1] 2437
[oracle@atlasrep2 ~]$
*******************************************************
* 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

1
2
3
4
5
6
7
8
9
10
[oracle@atlas ~]$ $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
1
2
3
4
5
6
7
8
[oracle@atlasrep2 ~]$  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

1
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

1
2
3
4
5
6
7
8
9
10
11
12
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

1
2
3
4
5
6
7
8
9
10
11
12
13
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

1
2
3
4
5
6
7
8
9
10
11
12
13
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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.

1
2
3
4
5
6
7
8
9
10
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

1
2
3
4
5
6
7
8
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

1
2
3
4
5
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

1
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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.

1
2
3
4
5
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.