shareplex 8.6.6 Default support for Oracle DDL测试

dongxujian

Default support for Oracle DDL

SharePlex provides some basic DDL support by default.

DDL for existing objects

By default SharePlex replicates the TRUNCATE TABLE command and the ALTER TABLE command to ADD or DROP columns when:

the affected object exists in the source and target at the time of activation and

its name is listed in the configuration file (explicitly or through wildcard).

This functionality is controlled by the SP_OCT_REPLICATE_DDL parameter. A setting of 1 replicates ALTER but not TRUNCATE. A setting of 2 replicates TRUNCATE but not ALTER. The default setting of 3 replicates ALTER and TRUNCATE.

DDL for objects added after activation

By default, the SharePlex Auto-Add feature is also enabled to provide DDL support for tables and indexes that are created after activation. When SharePlex detects a CREATE statement for one of these objects and its name satisfies a wildcard in the configuration file, SharePlex does the following:

replicates the CREATE to add the object to the target

adds the object to replication

maintains that object through future DDL and DML changes

The Auto-Add feature is controlled by the SP_OCT_AUTOADD_ENABLE parameter, which is set to 1 (enabled) by default.

Node1 源端查看参数：

sp_ctrl (node1:2100)> list param

Analyze parameters:

Parameter  Name                Actual Value                         Units Set At
------------------------------  ------------------------------------  -------  ---------------
SP_OCT_AUTOADD_ENABLE          1                                  boolean  Restart Process
SP_OCT_REPLICATE_DDL          3                                           Live

源端执行 ddl 操作：

[oracle@node1 config]$ sqlplus source/source

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 6 11:17:56 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from t1;

  COUNT(*)
----------
   100

SQL> truncate table t1;

Table truncated.

SQL> select count(*) from t1;

  COUNT(*)
----------
   0

SQL> show user
USER is "SOURCE"
SQL> create table t3 (id int primary key);

Table created.

SQL> insert into t3 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t3 add (name varchar2(20));

Table altered.

SQL> DESC T3;
Name    Null? Type
----------------------------------------- -------- ----------------------------
ID    NOT NULL NUMBER(38)
NAME       VARCHAR2(20)

SQL> alter table t3 drop column name;

Table altered.

SQL> desc t3;
Name    Null? Type
----------------------------------------- -------- ----------------------------
ID    NOT NULL NUMBER(38)

SQL>

目标端验证：

SQL> select tname from tab;

TNAME
------------------------------
T1
TTT

SQL> select tname from tab;

TNAME
------------------------------
T1
T3
TTT

SQL> select count(*) from t3;

  COUNT(*)
----------
   1

SQL> desc t3;
Name    Null? Type
----------------------------------------- -------- ----------------------------
ID    NOT NULL NUMBER(38)

SQL>

目标端日志：
opo_ddl  2017-04-06 12:06:48.554410 12010 3179902816  FILE: /u01/app/vardir2100/log/orcl_node1_opo_ddl_01.log - LOG#: 1

opo_ddl  2017-04-06 12:14:47.407475 12072 4197078880  FILE: /u01/app/vardir2100/log/orcl_node1_opo_ddl_01.log - LOG#: 1

opo_ddl  2017-04-06 12:51:09.101085 12429 3930859360  FILE: /u01/app/vardir2100/log/orcl_node1_opo_ddl_01.log - LOG#: 1

opo_ddl  2017-04-06 13:16:27.789993 12429 3883120384 s:3 Drop table command "DROP TABLE "TARGET"."T2"  " failed with return code 942
opo_ddl  2017-04-06 13:19:11.910616 12429 3883120384 s:3 Create table DDL posted:
opo_ddl  2017-04-06 13:19:11.910661 12429 3883120384 s:3 SQL = "CREATE TABLE "TARGET"."TTT" (id int primary key)"
opo_ddl  2017-04-06 13:26:04.792968 12429 3881072384 s:8 Create table DDL posted:
opo_ddl  2017-04-06 13:26:04.793001 12429 3881072384 s:8 SQL = "CREATE TABLE "TARGET"."T3" (id int primary key)"
opo_ddl  2017-04-06 13:28:30.927799 12429 3881072384 s:8 Alter table DDL posted:
opo_ddl  2017-04-06 13:28:30.927829 12429 3881072384 s:8 SQL = "ALTER TABLE "TARGET"."T3" add (name varchar2(20))"
opo_ddl  2017-04-06 13:28:53.952399 12429 3880048384 s:5 Alter table DDL posted:
opo_ddl  2017-04-06 13:28:53.952437 12429 3880048384 s:5 SQL = "ALTER TABLE "TARGET"."T3" drop column name"

See the SharePlex Reference Guide for details about this parameter.

图文精华

shareplex 8.6.6 Default support for Oracle DDL测试