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.