{"id":8444,"date":"2016-06-24T20:30:20","date_gmt":"2016-06-24T18:30:20","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/"},"modified":"2023-06-08T15:11:51","modified_gmt":"2023-06-08T13:11:51","slug":"manage-ddl-inside-shareplex","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/","title":{"rendered":"Manage DDL inside SharePlex"},"content":{"rendered":"

By Mouhamadou Diaw<\/strong><\/p>\n

In a precedent blog (http:\/\/dbi-services.com\/blog\/discovering-shareplex-for-oracle\/<\/a>) 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.
\nBefore starting we present below\u00a0our environment and our configuration file. We are just \u00a0replicating scott_atlas\u00a0to scott_atlasrep2. We suppose that SharePlex is already configured.<\/p>\n\n\n\n\n\n
Server Name<\/strong><\/td>\nDatabase Name<\/strong><\/td>\nOracle Version<\/strong><\/td>\nWhat<\/strong><\/td>\n<\/tr>\n
atlas.localdomain<\/td>\nSPLEXDB<\/td>\n12.1.0.2<\/td>\nSource<\/td>\n<\/tr>\n
atlasrep2.localdomain<\/td>\nSPLEXSTR2<\/td>\n12.1.0.2<\/td>\nTarget<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n<\/td>\n
\n
\n
[oracle@atlas config]$ <\/code>cat<\/code> ddl_config_atlasrep2.cnf<\/code><\/div>\n
datasource:o.SPLEXDB<\/code><\/div>\n
<\/div>\n
#source tables\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 target tables\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 routing map<\/code><\/div>\n
<\/div>\n
expand<\/code> scott_atlas.%\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 scott_atlasrep2.%\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 atlasrep2@o.SPLEXSTR2<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Let\u2019s start SharePlex on both source and target<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n<\/td>\n
\n
\n
[oracle@atlas ~]$ $SP_HOME<\/code>\/bin\/sp_cop<\/code> -u $NAME_FOR_ALERTING &<\/code><\/div>\n
[1] 2617<\/code><\/div>\n
[oracle@atlas ~]$<\/code><\/div>\n
<\/div>\n
*******************************************************<\/code><\/div>\n
* SharePlex <\/code>for<\/code> Oracle Startup<\/code><\/div>\n
* Copyright 2016 Dell, Inc.<\/code><\/div>\n
* ALL RIGHTS RESERVED.<\/code><\/div>\n
* Protected by U.S. Patents: 7,461,103 and 7,065,538<\/code><\/div>\n
* Version: 8.6.4.66-m64-oracle120<\/code><\/div>\n
* VarDir : <\/code>\/u01\/app\/shareplex\/vardir\/splexdb_864_12_2105<\/code><\/div>\n
* Port : 2105<\/code><\/div>\n
*******************************************************<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n
\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n<\/td>\n
\n
\n
[oracle@atlasrep2 ~]$ $SP_HOME<\/code>\/bin\/sp_cop<\/code> -u $NAME_FOR_ALERTING &<\/code><\/div>\n
[1] 2437<\/code><\/div>\n
[oracle@atlasrep2 ~]$<\/code><\/div>\n
<\/div>\n
*******************************************************<\/code><\/div>\n
* SharePlex <\/code>for<\/code> Oracle Startup<\/code><\/div>\n
* Copyright 2016 Dell, Inc.<\/code><\/div>\n
* ALL RIGHTS RESERVED.<\/code><\/div>\n
* Protected by U.S. Patents: 7,461,103 and 7,065,538<\/code><\/div>\n
* Version: 8.6.4.66-m64-oracle120<\/code><\/div>\n
* VarDir : <\/code>\/u01\/app\/shareplex\/vardir\/splexstr2_864_12_2105<\/code><\/div>\n
* Port\u00a0\u00a0 : 2105<\/code><\/div>\n
*******************************************************<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Now let’s verify that SharePlex processes are running on both source and target<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n<\/td>\n
\n
\n
[oracle@atlas ~]$ $SP_HOME<\/code>\/bin\/sp_ctrl<\/code><\/div>\n
<\/div>\n
<\/div>\n
sp_ctrl (atlas:2105)> show<\/code><\/div>\n
<\/div>\n
Process\u00a0\u00a0\u00a0 Source\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 State\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PID<\/code><\/div>\n
---------- ------------------------------------ ---------------------- -------------------- ------<\/code><\/div>\n
Capture\u00a0\u00a0\u00a0 o.SPLEXDB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Running\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2618<\/code><\/div>\n
Read\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 o.SPLEXDB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Running\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2620<\/code><\/div>\n
Export\u00a0\u00a0\u00a0\u00a0 atlas\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 atlasrep2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Running<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n
\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n<\/td>\n
\n
\n
[oracle@atlasrep2 ~]$\u00a0 rlwrap $SP_HOME<\/code>\/bin\/sp_ctrl<\/code><\/div>\n
<\/div>\n
sp_ctrl (atlasrep2:2105)> show<\/code><\/div>\n
<\/div>\n
Process\u00a0\u00a0\u00a0 Source\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 State\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PID<\/code><\/div>\n
---------- ------------------------------------ ---------------------- -------------------- ------<\/code><\/div>\n
Import\u00a0\u00a0\u00a0\u00a0 atlas\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 atlasrep2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Running\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2734<\/code><\/div>\n
Post\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 o.SPLEXDB-atlas\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 o.SPLEXSTR2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Running\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2438<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

SharePlex provides many parameters to handle DDL replication. We are going to see some of them. To display SharePlex parameters, just execute<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n<\/td>\n
\n
\n
sp_ctrl (atlas:2105)> list param<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

SP_OCT_REPLICATE_DDL: \u00a0Possible values 0\/1\/2\/3<\/b><\/span><\/p>\n

Manages the replication of ALTER and TRUNCATE statements. It can have the following values<\/p>\n

0: No replication of both ALTER and TRUNCATE<\/p>\n

1: Only ALTER replication<\/p>\n

2: Only TRUNCATE replication<\/p>\n

3: Both replication of ALTER and TRUNCATE\u00a0 –default value<\/p>\n

Let\u2019s 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<\/p>\n

From the source<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>select<\/code> name from <\/code>v<\/code>$database;<\/code><\/div>\n
<\/div>\n
NAME<\/code><\/div>\n
---------<\/code><\/div>\n
SPLEXDB<\/code><\/div>\n
SQL> conn scott_atlas<\/code>\/tiger<\/code><\/div>\n
Connected.<\/code><\/div>\n
SQL> <\/code>select<\/code> * from bonus;<\/code><\/div>\n
<\/div>\n
ENAME\u00a0\u00a0\u00a0\u00a0\u00a0 JOB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SAL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COMM<\/code><\/div>\n
---------- --------- ---------- ----------<\/code><\/div>\n
Moise\u00a0\u00a0\u00a0\u00a0\u00a0 IT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2300\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 200<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

From the target<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>select<\/code> name from <\/code>v<\/code>$database;<\/code><\/div>\n
<\/div>\n
NAME<\/code><\/div>\n
---------<\/code><\/div>\n
SPLEXSTR<\/code><\/div>\n
<\/div>\n
SQL> conn scott_atlasrep2<\/code>\/tiger<\/code><\/div>\n
Connected.<\/code><\/div>\n
SQL> <\/code>select<\/code> * from bonus;<\/code><\/div>\n
<\/div>\n
ENAME\u00a0\u00a0\u00a0\u00a0\u00a0 JOB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SAL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COMM<\/code><\/div>\n
---------- --------- ---------- ----------<\/code><\/div>\n
Moise\u00a0\u00a0\u00a0\u00a0\u00a0 IT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2300\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 200<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Let\u2019s truncate table bonus and let\u2019s add 2 new columns on the source<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n<\/td>\n
\n
\n
SQL> show user<\/code><\/div>\n
USER is <\/code>\"SCOTT_ATLAS\"<\/code><\/div>\n
SQL> truncate table bonus;<\/code><\/div>\n
<\/div>\n
Table truncated.<\/code><\/div>\n
<\/div>\n
SQL> alter table bonus add (col_net number,col_var number);<\/code><\/div>\n
<\/div>\n
Table altered.<\/code><\/div>\n
<\/div>\n
SQL>\u00a0 <\/code>select<\/code> * from bonus;<\/code><\/div>\n
<\/div>\n
no rows selected<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

We can see that both DDL are replicated on the target<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n<\/td>\n
\n
\n
SQL> show user<\/code><\/div>\n
USER is <\/code>\"SCOTT_ATLASREP2\"<\/code><\/div>\n
SQL> <\/code>select<\/code> * from bonus;<\/code><\/div>\n
<\/div>\n
no rows selected<\/code><\/div>\n
<\/div>\n
SQL> desc bonus<\/code><\/div>\n
\u00a0<\/code>Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Null?\u00a0\u00a0\u00a0 Type<\/code><\/div>\n
\u00a0<\/code>----------------------------------------- -------- ----------------------------<\/code><\/div>\n
\u00a0<\/code>ENAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VARCHAR2(10)<\/code><\/div>\n
\u00a0<\/code>JOB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VARCHAR2(9)<\/code><\/div>\n
\u00a0<\/code>SAL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUMBER<\/code><\/div>\n
\u00a0<\/code>COMM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUMBER<\/code><\/div>\n
\u00a0<\/code>COL_NET\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUMBER<\/code><\/div>\n
\u00a0<\/code>COL_VAR\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUMBER<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

SP_OCT_AUTOADD_ENABLE: \u00a0Possible values 0\/1<\/b><\/span><\/p>\n

Manages newly created objects in the active configuration <\/span>(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.
\nLet’s create a table on the source and let’s insert some data<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n
20<\/div>\n
21<\/div>\n<\/td>\n
\n
\n
SQL> show user<\/code><\/div>\n
USER is <\/code>\"SCOTT_ATLAS\"<\/code><\/div>\n
SQL> create table mytable (<\/code>id<\/code> number primary key,message long);<\/code><\/div>\n
<\/div>\n
Table created.<\/code><\/div>\n
<\/div>\n
SQL> insert into mytable values (1,<\/code>'Ceci est un message important concernant...'<\/code>);<\/code><\/div>\n
<\/div>\n
1 row created.<\/code><\/div>\n
<\/div>\n
SQL> commit;<\/code><\/div>\n
<\/div>\n
Commit complete.<\/code><\/div>\n
<\/div>\n
SQL> <\/code>select<\/code> * from mytable;<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>ID<\/code><\/div>\n
----------\u00a0 MESSAGE<\/code><\/div>\n
------------------------------------------------------------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>1<\/code><\/div>\n
Ceci est un message important concernant...<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

We can verify that the new table and corresponding data are replicated on the target.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n<\/td>\n
\n
\n
SQL> show user<\/code><\/div>\n
USER is <\/code>\"SCOTT_ATLASREP2\"<\/code><\/div>\n
SQL> <\/code>select<\/code> * from mytable;<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>ID<\/code><\/div>\n
----------<\/code><\/div>\n
MESSAGE<\/code><\/div>\n
--------------------------------------------------------------------------------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>1<\/code><\/div>\n
Ceci est un message important concernant...<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

SP_OCT_REPLICATE_ALL_DDL: Possible values 0\/1<\/b><\/span><\/p>\n

Manages \u00a0DDL replication \u00a0for certain objects that are\u00a0not listed\u00a0in the configuration file.\u00a0The default value is 0 and means that DDL concerning objects which are not in the active configuration will not be replicated. Let\u2019s take an example with a toto schema (Just remember that in our configuration file only scott_atlas objects are being replicated).
\nFrom the source let’s create a table test1 in toto schema<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n<\/td>\n
\n
\n
SQL> connect toto<\/code>\/toto<\/code><\/div>\n
<\/div>\n
Connected.<\/code><\/div>\n
<\/div>\n
SQL> create table test1(<\/code>id<\/code> number);<\/code><\/div>\n
<\/div>\n
\u00a0<\/code><\/div>\n
Table created.<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

As expected we can verify that table test1 is not replicated in toto schema on the target<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n<\/td>\n
\n
\n
SQL> conn toto<\/code>\/toto<\/code><\/div>\n
Connected.<\/code><\/div>\n
SQL> desc test1<\/code><\/div>\n
ERROR:<\/code><\/div>\n
ORA-04043: object test1 does not exist<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Now let\u2019s set the value of the parameter to 1 on the source<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n<\/td>\n
\n
\n
sp_ctrl (atlas:2105)> <\/code>set<\/code> param SP_OCT_REPLICATE_ALL_DDL 1<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

And let\u2019s create a table test2 on toto schema and let\u2019s insert a row on the source<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n<\/td>\n
\n
\n
SQL> create table test2(<\/code>id<\/code> number);<\/code><\/div>\n
<\/div>\n
Table created.<\/code><\/div>\n
<\/div>\n
SQL> insert into test2 values (1);<\/code><\/div>\n
<\/div>\n
1 row created.<\/code><\/div>\n
<\/div>\n
SQL> commit;<\/code><\/div>\n
<\/div>\n
Commit complete.<\/code><\/div>\n
<\/div>\n
SQL> <\/code>select<\/code> * from test2;<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>ID<\/code><\/div>\n
----------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>1<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

We can see that DDL is replicated (table creation) on target but not the corresponding DML.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>select<\/code> * from test2;<\/code><\/div>\n
<\/div>\n
no rows selected<\/code><\/div>\n
<\/div>\n
SQL><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

That means that if an object is not in the active replication <\/span> and if the parameter SP_OCT_REPLICATE_ALL_DDL is set to 1<\/span>, DDL\u00a0will be replicated but data will not.<\/p>\n","protected":false},"excerpt":{"rendered":"

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\u00a0our environment and our configuration file. We are just \u00a0replicating scott_atlas\u00a0to scott_atlasrep2. We suppose that SharePlex […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[],"type_dbi":[],"class_list":["post-8444","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring"],"acf":[],"yoast_head":"\nManage DDL inside SharePlex - dbi Blog<\/title>\n<meta name=\"description\" content=\"oracle, Logical replication, SharePlex,\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Manage DDL inside SharePlex\" \/>\n<meta property=\"og:description\" content=\"oracle, Logical replication, SharePlex,\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-06-24T18:30:20+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-06-08T13:11:51+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Manage DDL inside SharePlex\",\"datePublished\":\"2016-06-24T18:30:20+00:00\",\"dateModified\":\"2023-06-08T13:11:51+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/\"},\"wordCount\":486,\"commentCount\":0,\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/\",\"name\":\"Manage DDL inside SharePlex - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-06-24T18:30:20+00:00\",\"dateModified\":\"2023-06-08T13:11:51+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"oracle, Logical replication, SharePlex,\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Manage DDL inside SharePlex\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Manage DDL inside SharePlex - dbi Blog","description":"oracle, Logical replication, SharePlex,","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/","og_locale":"en_US","og_type":"article","og_title":"Manage DDL inside SharePlex","og_description":"oracle, Logical replication, SharePlex,","og_url":"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/","og_site_name":"dbi Blog","article_published_time":"2016-06-24T18:30:20+00:00","article_modified_time":"2023-06-08T13:11:51+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Manage DDL inside SharePlex","datePublished":"2016-06-24T18:30:20+00:00","dateModified":"2023-06-08T13:11:51+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/"},"wordCount":486,"commentCount":0,"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/","url":"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/","name":"Manage DDL inside SharePlex - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-06-24T18:30:20+00:00","dateModified":"2023-06-08T13:11:51+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"oracle, Logical replication, SharePlex,","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/manage-ddl-inside-shareplex\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Manage DDL inside SharePlex"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8444","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=8444"}],"version-history":[{"count":1,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8444\/revisions"}],"predecessor-version":[{"id":25676,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8444\/revisions\/25676"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=8444"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=8444"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=8444"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=8444"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}