Oracle Database data synchronization using SymmetricDS – Overview
Here I am going to setup a real time table data synchronization between two Oracle Databases located in different locations. Symmetric DS is open source software that is free to use. It includes command line tools, documentation, and example configuration files for getting setup and running.
Environment details
|
Source |
Target |
|
|
Instance Type |
Production |
Reporting |
|
Oracle Database version |
Oracle 11gR2 Standard Edition (11.2.0.4) |
Oracle 11gR2 Standard Edition (11.2.0.4) |
|
DB Schema Name |
SCOTT1 |
SCOTT1 |
|
Operating System |
Oracle Linux 7.9 |
Oracle Linux 7.9 |
|
Symetric DS Version |
SymmetricDS 3.12.10 |
SymmetricDS 3.12.10 |
|
Symetric DS Location |
/home/oracle/symdshome/ symmetric-server-3.12.10 |
/home/oracle/symds/symmetric-server-3.12.10 |
|
IP Address |
158.101.7.75 |
129.146.254.56 |
We will install SymmetricDS on two servers One on production and other on target. One will represent the Source server and one will represent the Target server.
Each installed copy of SymmetricDS will be responsible for one database, and thus each copy acts as a single “node” in SymmetricDS terminology. This is the most common configuration of SymmetricDS – one installed copy of the software is responsible for one single database and represents one node.
We can replicate multiple schemas via SymmetricDS in other words You can replicate multiple source and target schemas if the jdbc connection and your database vendor supports it.
It is not a SymmetricDS limitation it would be based on each database and how you setup your permissions. If you can connect into SCOTT with user ABC and can still access SCOTT1 then your set if your database does not allow that then you cannot replicate multiple schemas via SymmetricDS. SymmetricDS does not limit it is only based on what is allowed by your database
Download Latest version of the symmetricds from the symmetricds website https://www.symmetricds.org/
Latest version available - symmetric-server-3.12.10.zip
Software Installation steps
On source (Production)
I have copied the symmetricsds zip file to /home/oracle/symdshome folder, execute the unzip symmetric-server-3.12.1.zip command.
[oracle@symdssrc symdshome]$ pwd
/home/oracle/symdshome
[oracle@symdssrc symdshome]$ ls -lrt
total 42480
-rwxrwxrwx 1 oracle oinstall 43492343 Jun 17 14:41 symmetric-server-3.12.10.zip
[oracle@symdssrc symdshome]$ unzip symmetric-server-3.12.1.zip
chown -R oracle:oinstall symmetric-server-3.12.10
chmod 775 symmetric-server-3.12.10
On Target (Reporting)
Kindly perform the same steps and as mentioned below.
[oracle@symds]$ mkdir -p /home/oracle/symds/
[oracle@symds symds] $ cd /home/oracle/symds/
/home/oracle/symds
[oracle@symds symds]$ ls -l
-rw-r--r-- 1 oracle oinstall 43492343 Jun 18 06:55 symmetric-server-3.12.10.zip
[oracle@symds symds]$unzip symmetric-server-3.12.10.zip
chown -R oracle:oinstall /home/oracle/symds/symmetric-server-3.12.10
chmod 775 /home/oracle/symds/symmetric-server-3.12.10
SymmetricDS Configuration at Source & Target servers –
On Source we need mainly look in to two configuration files symmetric-server.properties & sym_service.conf, files located under conf folder on symetricds location
From symmetric-server.properties file ensure the below properties are uncommented and the ports listed are open for communication at source & target servers.
http.port=31415
https.port=31417
I have used the default parameter configuration and copied sample engine property file (corp-000.properties) from sample folder and create a source-000.properties under engines folder
Key configuration parameters for source-000
We have to modify the key parameters from the corp-000.properties file to configure source engine for replication.
engine.name=source-000
db.driver=oracle.jdbc.driver.OracleDriver
db.url=jdbc:oracle:thin:@11.11.11.14:1521/symdssrc_dd.symdsonprem.symmetricds.oraclevcn.com
db.user=SCOTT
db.password=TIGER123
registration.url=
sync.url=http://158.101.7.75:31415/sync/source-000
group.id=source
external.id=000
db.validation.query=select 1 from dual
cluster.lock.enabled=false
Note: registration.url is left blank to indicate this is the root/source node.
Key configuration parameter for dest-001
On target we will create file dest-001.properties under engines directory. Copy store-001.properties from sample folder and create it under the engine folder.
engine.name=dest-001
db.driver=oracle.jdbc.driver.OracleDriver
db.url=jdbc:oracle:thin:@11.11.11.2:1521/dbsystgt_db.symdspub.symmetricds.oraclevcn.com
db.user=SCOTT1
db.password=TIGER123
registration.url=http://158.101.7.75:31415/sync/source-000
sync.url=http://129.146.254.56:31415/sync/dest-001
group.id=dest
external.id=001
db.validation.query=select 1 from dual
cluster.lock.enabled=false
Pre-requisites prior creating SymmetricDS specific tables & initiate replication.
Step :1 (Create Replication User)
Create SCOTT user at source and target database and provide appropriate privileges.
Minimum privileges required for SymmetricDS replication is shared below.
CONNECT
RESOURCE
CREATE ANY TRIGGER
EXECUTE ON UTL_RAW
Step:2 (Create objects for Replication)
Create dummy table for replication at source and target. Insert few dummy records @ source DB only.
create
table TEST1 (a1 number, desc01 varchar2(10));
create table TEST2 (a1
number, desc01 varchar2(10));
create table TEST3 (a1
number, desc01 varchar2(10));
insert
into TEST1 values(1,’DAS’);
insert into TEST1 values(2,’SINGH’);
insert into TEST1 values(3,’SHIBIN’);
insert into TEST2 values(2,’DEV’);
insert into TEST3 values(3,’PLATO’);
commit;
Creation of SymmetricS specific tables in Production database
we need to create SymmetricDS-specific tables in the Production database. These tables will contain the configuration for synchronization. The following command uses the auto-creation feature to create all the necessary SymmetricDS system tables. thie command is required to be run only on production server.
cd /home/oracle/symdshome/symmetric-server-3.12.10/bin
./symadmin -engine source-000 create-sym-tables
[oracle@symdssrc bin]$ ./symadmin -engine source-000 create-sym-tables
Log output will be written to /home/oracle/symds/symmetric-server-3.12.10/logs/symmetric.log
[] - AbstractCommandLauncher - Option: name=engine, value={source-000}
[] - SymmetricUtils -
_____ __ _ ____ _____
/ ___/ __ _____ __ ___ __ ___ _/ /_ ____(_)___ / __ | / ___/
\__ \ / / / / _ `_ \/ _ `_ \/ _ \/_ __// __/ / __/ / / / / \__ \
___/ // /_/ / // // / // // / __// / / / / / /_ / /_/ / ___/ /
/____/ \__ /_//_//_/_//_//_/\___/ \_/ /_/ /_/\__/ /_____/ /____/
/____/
+-----------------------------------------------------------------+
| Copyright (C) 2007-2021 JumpMind, Inc. |
| |
| Licensed under the GNU General Public License version 3. |
| This software comes with ABSOLUTELY NO WARRANTY. |
| See http://www.gnu.org/licenses/gpl.html |
+-----------------------------------------------------------------+
[source-000] - AbstractSymmetricEngine - Initializing connection to database
[source-000] - JdbcDatabasePlatformFactory - Detected database 'Oracle', version '11', protocol 'oracle'
[source-000] - JdbcDatabasePlatformFactory - The IDatabasePlatform being used is org.jumpmind.db.platform.oracle.OracleDatabasePlatform
[source-000] - OracleSymmetricDialect - The DbDialect being used is org.jumpmind.symmetric.db.oracle.OracleSymmetricDialect
[source-000] - StagingManager - The staging directory was initialized at the following location: /home/oracle/symds/symmetric-server-3.12.10/tmp/source-000
[source-000] - ClientExtensionService - Found 7 extension points from spring that will be registered
[source-000] - AbstractSymmetricEngine - Initializing SymmetricDS database
[source-000] - OracleSymmetricDialect - Installing SymmetricDS database object:
CREATE OR REPLACE FUNCTION sym_blob2clob (blob_in IN BLOB) RETURN CLOB AS v_clob CLOB := null; v_varchar VARCHAR2(32767); v_start PLS_INTEGER := 1; v_buffer PLS_INTEGER := 999; BEGIN IF blob_in IS NOT NULL THEN IF DBMS_LOB.GETLENGTH(blob_in) > 0 THEN DBMS_LOB.CREATETEMPORARY(v_clob, TRUE); FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer) LOOP v_varchar := UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.base64_encode(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start))); v_varchar := REPLACE(v_varchar,CHR(13)||CHR(10)); DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar); v_start := v_start + v_buffer; END LOOP; END IF; END IF; RETURN v_clob; END sym_blob2clob;
[source-000] - OracleSymmetricDialect - Just installed sym_blob2clob
[source-000] - OracleSymmetricDialect - Installing SymmetricDS database object:
CREATE OR REPLACE function sym_transaction_id return varchar is begin return DBMS_TRANSACTION.local_transaction_id(false); end;
[source-000] - OracleSymmetricDialect - Just installed sym_transaction_id
[source-000] - OracleSymmetricDialect - Installing SymmetricDS database object:
CREATE OR REPLACE function sym_trigger_disabled return varchar is begin return sym_pkg.disable_trigger; end;
[source-000] - OracleSymmetricDialect - Just installed sym_trigger_disabled
[source-000] - OracleSymmetricDialect - Installing SymmetricDS database object:
CREATE OR REPLACE package sym_pkg as disable_trigger pls_integer; disable_node_id varchar(50); procedure setValue (a IN number); procedure setNodeValue (node_id IN varchar); end sym_pkg;
[source-000] - OracleSymmetricDialect - Just installed sym_pkg
[source-000] - OracleSymmetricDialect - Installing SymmetricDS database object:
CREATE OR REPLACE package body sym_pkg as procedure setValue(a IN number) is begin sym_pkg.disable_trigger:=a; end; procedure setNodeValue(node_id IN varchar) is begin sym_pkg.disable_node_id := node_id; end; end sym_pkg;
[source-000] - OracleSymmetricDialect - Just installed sym_pkg
[source-000] - OracleSymmetricDialect - Installing SymmetricDS database object:
CREATE OR REPLACE FUNCTION sym_wkt2geom (
clob_in IN CLOB,
srid_in IN INTEGER)
RETURN SDO_GEOMETRY
AS
v_out SDO_GEOMETRY := NULL;
BEGIN
IF clob_in IS NOT NULL THEN
IF DBMS_LOB.GETLENGTH(clob_in) > 0 THEN
v_out := SDO_GEOMETRY(clob_in, srid_in);
END IF;
END IF;
RETURN v_out;
END sym_wkt2geom;
[source-000] - OracleSymmetricDialect - Just installed sym_wkt2geom
[source-000] - OracleSymmetricDialect - Checking if SymmetricDS tables need created or altered
[source-000] - OracleSymmetricDialect - There are SymmetricDS tables that needed altered
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_CHANNEL"(
"CHANNEL_ID" VARCHAR2(128) NOT NULL,
"PROCESSING_ORDER" NUMBER(22) DEFAULT 1 NOT NULL,
"MAX_BATCH_SIZE" NUMBER(22) DEFAULT 1000 NOT NULL,
"MAX_BATCH_TO_SEND" NUMBER(22) DEFAULT 60 NOT NULL,
"MAX_DATA_TO_ROUTE" NUMBER(22) DEFAULT 100000 NOT NULL,
"EXTRACT_PERIOD_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"ENABLED" NUMBER(3) DEFAULT 1 NOT NULL,
"USE_OLD_DATA_TO_ROUTE" NUMBER(3) DEFAULT 1 NOT NULL,
"USE_ROW_DATA_TO_ROUTE" NUMBER(3) DEFAULT 1 NOT NULL,
"USE_PK_DATA_TO_ROUTE" NUMBER(3) DEFAULT 1 NOT NULL,
"RELOAD_FLAG" NUMBER(3) DEFAULT 0 NOT NULL,
"FILE_SYNC_FLAG" NUMBER(3) DEFAULT 0 NOT NULL,
"CONTAINS_BIG_LOB" NUMBER(3) DEFAULT 0 NOT NULL,
"BATCH_ALGORITHM" VARCHAR2(50) DEFAULT 'default' NOT NULL,
"DATA_LOADER_TYPE" VARCHAR2(50) DEFAULT 'default' NOT NULL,
"DESCRIPTION" VARCHAR2(255),
"QUEUE" VARCHAR2(25) DEFAULT 'default' NOT NULL,
"MAX_NETWORK_KBPS" NUMBER(10,3) DEFAULT 0.000 NOT NULL,
"DATA_EVENT_ACTION" CHAR(1),
"CREATE_TIME" TIMESTAMP,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_CHANNEL"
ADD CONSTRAINT "SYM_CHANNEL_PK" PRIMARY KEY ("CHANNEL_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_CONFLICT"(
"CONFLICT_ID" VARCHAR2(50) NOT NULL,
"SOURCE_NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"TARGET_NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"TARGET_CHANNEL_ID" VARCHAR2(128),
"TARGET_CATALOG_NAME" VARCHAR2(255),
"TARGET_SCHEMA_NAME" VARCHAR2(255),
"TARGET_TABLE_NAME" VARCHAR2(255),
"DETECT_TYPE" VARCHAR2(128) NOT NULL,
"DETECT_EXPRESSION" CLOB,
"RESOLVE_TYPE" VARCHAR2(128) NOT NULL,
"PING_BACK" VARCHAR2(128) NOT NULL,
"RESOLVE_CHANGES_ONLY" NUMBER(3) DEFAULT 0,
"RESOLVE_ROW_ONLY" NUMBER(3) DEFAULT 0,
"CREATE_TIME" TIMESTAMP NOT NULL,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP NOT NULL
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_CONFLICT"
ADD CONSTRAINT "SYM_CONFLICT_PK" PRIMARY KEY ("CONFLICT_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_CONTEXT"(
"NAME" VARCHAR2(80) NOT NULL,
"CONTEXT_VALUE" CLOB,
"CREATE_TIME" TIMESTAMP,
"LAST_UPDATE_TIME" TIMESTAMP
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_CONTEXT"
ADD CONSTRAINT "SYM_CONTEXT_PK" PRIMARY KEY ("NAME")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE SEQUENCE "SEQ_SYM_DATA_DATA_ID" CACHE 1000 ORDER
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_DATA"(
"DATA_ID" NUMBER(38) NOT NULL ,
"TABLE_NAME" VARCHAR2(255) NOT NULL,
"EVENT_TYPE" CHAR(1) NOT NULL,
"ROW_DATA" CLOB,
"PK_DATA" CLOB,
"OLD_DATA" CLOB,
"TRIGGER_HIST_ID" NUMBER(22) NOT NULL,
"CHANNEL_ID" VARCHAR2(128),
"TRANSACTION_ID" VARCHAR2(255),
"SOURCE_NODE_ID" VARCHAR2(50),
"EXTERNAL_DATA" VARCHAR2(50),
"NODE_LIST" VARCHAR2(255),
"IS_PREROUTED" NUMBER(3) DEFAULT 0 NOT NULL,
"CREATE_TIME" TIMESTAMP
)
[source-000] - OracleSymmetricDialect - DDL applied: CREATE UNIQUE INDEX "SYM_IDX_D_CHANNEL_ID" ON "SYM_DATA" ("DATA_ID", "CHANNEL_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_DATA"
ADD CONSTRAINT "SYM_DATA_PK" PRIMARY KEY ("DATA_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE OR REPLACE TRIGGER "TRG_SYM_DATA_DATA_ID" BEFORE INSERT ON "SYM_DATA" FOR EACH ROW WHEN (new."DATA_ID" IS NULL)
BEGIN SELECT "SEQ_SYM_DATA_DATA_ID".nextval INTO :new."DATA_ID" FROM dual; END;
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_DATA_EVENT"(
"DATA_ID" NUMBER(38) NOT NULL,
"BATCH_ID" NUMBER(38) NOT NULL,
"CREATE_TIME" TIMESTAMP
)
[source-000] - OracleSymmetricDialect - DDL applied: CREATE INDEX "SYM_IDX_DE_BATCHID" ON "SYM_DATA_EVENT" ("BATCH_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_DATA_EVENT"
ADD CONSTRAINT "SYM_DATA_EVENT_PK" PRIMARY KEY ("DATA_ID", "BATCH_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_DATA_GAP"(
"START_ID" NUMBER(38) NOT NULL,
"END_ID" NUMBER(38) NOT NULL,
"CREATE_TIME" TIMESTAMP NOT NULL,
"LAST_UPDATE_HOSTNAME" VARCHAR2(255)
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_DATA_GAP"
ADD CONSTRAINT "SYM_DATA_GAP_PK" PRIMARY KEY ("START_ID", "END_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_EXTENSION"(
"EXTENSION_ID" VARCHAR2(50) NOT NULL,
"EXTENSION_TYPE" VARCHAR2(10) NOT NULL,
"INTERFACE_NAME" VARCHAR2(255),
"NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"ENABLED" NUMBER(3) DEFAULT 1 NOT NULL,
"EXTENSION_ORDER" NUMBER(22) DEFAULT 1 NOT NULL,
"EXTENSION_TEXT" CLOB,
"CREATE_TIME" TIMESTAMP,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_EXTENSION"
ADD CONSTRAINT "SYM_EXTENSION_PK" PRIMARY KEY ("EXTENSION_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_EXTRACT_REQUEST"(
"REQUEST_ID" NUMBER(38) NOT NULL,
"NODE_ID" VARCHAR2(50) NOT NULL,
"QUEUE" VARCHAR2(128),
"STATUS" CHAR(2),
"START_BATCH_ID" NUMBER(38) NOT NULL,
"END_BATCH_ID" NUMBER(38) NOT NULL,
"TRIGGER_ID" VARCHAR2(128) NOT NULL,
"ROUTER_ID" VARCHAR2(50) NOT NULL,
"LOAD_ID" NUMBER(38),
"TABLE_NAME" VARCHAR2(255),
"EXTRACTED_ROWS" NUMBER(38) DEFAULT 0 NOT NULL,
"EXTRACTED_MILLIS" NUMBER(38) DEFAULT 0 NOT NULL,
"TRANSFERRED_ROWS" NUMBER(38) DEFAULT 0 NOT NULL,
"TRANSFERRED_MILLIS" NUMBER(38) DEFAULT 0 NOT NULL,
"LAST_TRANSFERRED_BATCH_ID" NUMBER(38),
"LOADED_ROWS" NUMBER(38) DEFAULT 0 NOT NULL,
"LOADED_MILLIS" NUMBER(38) DEFAULT 0 NOT NULL,
"LAST_LOADED_BATCH_ID" NUMBER(38),
"TOTAL_ROWS" NUMBER(38),
"LOADED_TIME" TIMESTAMP,
"PARENT_REQUEST_ID" NUMBER(38) DEFAULT 0 NOT NULL,
"LAST_UPDATE_TIME" TIMESTAMP,
"CREATE_TIME" TIMESTAMP
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_EXTRACT_REQUEST"
ADD CONSTRAINT "SYM_EXTRACT_REQUEST_PK" PRIMARY KEY ("REQUEST_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_FILE_INCOMING"(
"RELATIVE_DIR" VARCHAR2(255) NOT NULL,
"FILE_NAME" VARCHAR2(128) NOT NULL,
"LAST_EVENT_TYPE" CHAR(1) NOT NULL,
"NODE_ID" VARCHAR2(50) NOT NULL,
"FILE_MODIFIED_TIME" NUMBER(38)
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_FILE_INCOMING"
ADD CONSTRAINT "SYM_FILE_INCOMING_PK" PRIMARY KEY ("RELATIVE_DIR", "FILE_NAME")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_FILE_SNAPSHOT"(
"TRIGGER_ID" VARCHAR2(128) NOT NULL,
"ROUTER_ID" VARCHAR2(50) NOT NULL,
"RELATIVE_DIR" VARCHAR2(255) NOT NULL,
"FILE_NAME" VARCHAR2(128) NOT NULL,
"CHANNEL_ID" VARCHAR2(128) DEFAULT 'filesync' NOT NULL,
"RELOAD_CHANNEL_ID" VARCHAR2(128) DEFAULT 'filesync_reload' NOT NULL,
"LAST_EVENT_TYPE" CHAR(1) NOT NULL,
"CRC32_CHECKSUM" NUMBER(38),
"FILE_SIZE" NUMBER(38),
"FILE_MODIFIED_TIME" NUMBER(38),
"LAST_UPDATE_TIME" TIMESTAMP NOT NULL,
"LAST_UPDATE_BY" VARCHAR2(50),
"CREATE_TIME" TIMESTAMP NOT NULL
)
[source-000] - OracleSymmetricDialect - DDL applied: CREATE INDEX "SYM_IDX_F_SNPSHT_CHID" ON "SYM_FILE_SNAPSHOT" ("RELOAD_CHANNEL_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_FILE_SNAPSHOT"
ADD CONSTRAINT "SYM_FILE_SNAPSHOT_PK" PRIMARY KEY ("TRIGGER_ID", "ROUTER_ID", "RELATIVE_DIR", "FILE_NAME")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_FILE_TRIGGER"(
"TRIGGER_ID" VARCHAR2(128) NOT NULL,
"CHANNEL_ID" VARCHAR2(128) DEFAULT 'filesync' NOT NULL,
"RELOAD_CHANNEL_ID" VARCHAR2(128) DEFAULT 'filesync_reload' NOT NULL,
"BASE_DIR" VARCHAR2(255) NOT NULL,
"RECURSE" NUMBER(3) DEFAULT 1 NOT NULL,
"INCLUDES_FILES" VARCHAR2(255),
"EXCLUDES_FILES" VARCHAR2(255),
"SYNC_ON_CREATE" NUMBER(3) DEFAULT 1 NOT NULL,
"SYNC_ON_MODIFIED" NUMBER(3) DEFAULT 1 NOT NULL,
"SYNC_ON_DELETE" NUMBER(3) DEFAULT 1 NOT NULL,
"SYNC_ON_CTL_FILE" NUMBER(3) DEFAULT 0 NOT NULL,
"DELETE_AFTER_SYNC" NUMBER(3) DEFAULT 0 NOT NULL,
"BEFORE_COPY_SCRIPT" CLOB,
"AFTER_COPY_SCRIPT" CLOB,
"CREATE_TIME" TIMESTAMP NOT NULL,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP NOT NULL,
"DESCRIPTION" CLOB
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_FILE_TRIGGER"
ADD CONSTRAINT "SYM_FILE_TRIGGER_PK" PRIMARY KEY ("TRIGGER_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_FILE_TRIGGER_ROUTER"(
"TRIGGER_ID" VARCHAR2(128) NOT NULL,
"ROUTER_ID" VARCHAR2(50) NOT NULL,
"ENABLED" NUMBER(3) DEFAULT 1 NOT NULL,
"INITIAL_LOAD_ENABLED" NUMBER(3) DEFAULT 1 NOT NULL,
"TARGET_BASE_DIR" VARCHAR2(255),
"CONFLICT_STRATEGY" VARCHAR2(128) DEFAULT 'source_wins' NOT NULL,
"CREATE_TIME" TIMESTAMP NOT NULL,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP NOT NULL,
"DESCRIPTION" CLOB
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_FILE_TRIGGER_ROUTER"
ADD CONSTRAINT "SYM_FILE_TRIGGER_ROUTER_PK" PRIMARY KEY ("TRIGGER_ID", "ROUTER_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_GROUPLET"(
"GROUPLET_ID" VARCHAR2(50) NOT NULL,
"GROUPLET_LINK_POLICY" CHAR(1) DEFAULT 'I' NOT NULL,
"DESCRIPTION" VARCHAR2(255),
"CREATE_TIME" TIMESTAMP NOT NULL,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP NOT NULL
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_GROUPLET"
ADD CONSTRAINT "SYM_GROUPLET_PK" PRIMARY KEY ("GROUPLET_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_GROUPLET_LINK"(
"GROUPLET_ID" VARCHAR2(50) NOT NULL,
"EXTERNAL_ID" VARCHAR2(255) NOT NULL,
"CREATE_TIME" TIMESTAMP NOT NULL,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP NOT NULL
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_GROUPLET_LINK"
ADD CONSTRAINT "SYM_GROUPLET_LINK_PK" PRIMARY KEY ("GROUPLET_ID", "EXTERNAL_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_INCOMING_BATCH"(
"BATCH_ID" NUMBER(38) NOT NULL,
"NODE_ID" VARCHAR2(50) NOT NULL,
"CHANNEL_ID" VARCHAR2(128),
"STATUS" CHAR(2),
"ERROR_FLAG" NUMBER(3) DEFAULT 0,
"SQL_STATE" VARCHAR2(10),
"SQL_CODE" NUMBER(22) DEFAULT 0 NOT NULL,
"SQL_MESSAGE" CLOB,
"LAST_UPDATE_HOSTNAME" VARCHAR2(255),
"LAST_UPDATE_TIME" TIMESTAMP,
"CREATE_TIME" TIMESTAMP,
"SUMMARY" VARCHAR2(255),
"IGNORE_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"BYTE_COUNT" NUMBER(38) DEFAULT 0 NOT NULL,
"LOAD_FLAG" NUMBER(3) DEFAULT 0,
"EXTRACT_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"SENT_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"LOAD_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"RELOAD_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"OTHER_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"DATA_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"EXTRACT_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"LOAD_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"DATA_INSERT_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"DATA_UPDATE_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"DATA_DELETE_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"EXTRACT_INSERT_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"EXTRACT_UPDATE_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"EXTRACT_DELETE_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"LOAD_INSERT_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"LOAD_UPDATE_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"LOAD_DELETE_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"NETWORK_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"FILTER_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"LOAD_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"ROUTER_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"EXTRACT_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"TRANSFORM_EXTRACT_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"TRANSFORM_LOAD_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"LOAD_ID" NUMBER(38),
"COMMON_FLAG" NUMBER(3) DEFAULT 0,
"FALLBACK_INSERT_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"FALLBACK_UPDATE_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"IGNORE_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"MISSING_DELETE_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"SKIP_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"FAILED_ROW_NUMBER" NUMBER(22) DEFAULT 0 NOT NULL,
"FAILED_LINE_NUMBER" NUMBER(22) DEFAULT 0 NOT NULL,
"FAILED_DATA_ID" NUMBER(38) DEFAULT 0 NOT NULL
)
[source-000] - OracleSymmetricDialect - DDL applied: CREATE INDEX "SYM_IDX_IB_TIME_STATUS" ON "SYM_INCOMING_BATCH" ("CREATE_TIME", "STATUS")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE INDEX "SYM_IDX_IB_IN_ERROR" ON "SYM_INCOMING_BATCH" ("ERROR_FLAG")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_INCOMING_BATCH"
ADD CONSTRAINT "SYM_INCOMING_BATCH_PK" PRIMARY KEY ("BATCH_ID", "NODE_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_INCOMING_ERROR"(
"BATCH_ID" NUMBER(38) NOT NULL,
"NODE_ID" VARCHAR2(50) NOT NULL,
"FAILED_ROW_NUMBER" NUMBER(38) NOT NULL,
"FAILED_LINE_NUMBER" NUMBER(38) DEFAULT 0 NOT NULL,
"TARGET_CATALOG_NAME" VARCHAR2(255),
"TARGET_SCHEMA_NAME" VARCHAR2(255),
"TARGET_TABLE_NAME" VARCHAR2(255) NOT NULL,
"EVENT_TYPE" CHAR(1) NOT NULL,
"BINARY_ENCODING" VARCHAR2(10) DEFAULT 'HEX' NOT NULL,
"COLUMN_NAMES" CLOB NOT NULL,
"PK_COLUMN_NAMES" CLOB NOT NULL,
"ROW_DATA" CLOB,
"OLD_DATA" CLOB,
"CUR_DATA" CLOB,
"RESOLVE_DATA" CLOB,
"RESOLVE_IGNORE" NUMBER(3) DEFAULT 0,
"CONFLICT_ID" VARCHAR2(50),
"CREATE_TIME" TIMESTAMP,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP NOT NULL
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_INCOMING_ERROR"
ADD CONSTRAINT "SYM_INCOMING_ERROR_PK" PRIMARY KEY ("BATCH_ID", "NODE_ID", "FAILED_ROW_NUMBER")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_JOB"(
"JOB_NAME" VARCHAR2(50) NOT NULL,
"JOB_TYPE" VARCHAR2(10) NOT NULL,
"REQUIRES_REGISTRATION" NUMBER(3) DEFAULT 1 NOT NULL,
"JOB_EXPRESSION" CLOB,
"DESCRIPTION" VARCHAR2(255),
"DEFAULT_SCHEDULE" VARCHAR2(50),
"DEFAULT_AUTO_START" NUMBER(3) DEFAULT 1 NOT NULL,
"NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"CREATE_BY" VARCHAR2(50),
"CREATE_TIME" TIMESTAMP,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_JOB"
ADD CONSTRAINT "SYM_JOB_PK" PRIMARY KEY ("JOB_NAME")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_LOAD_FILTER"(
"LOAD_FILTER_ID" VARCHAR2(50) NOT NULL,
"LOAD_FILTER_TYPE" VARCHAR2(10) NOT NULL,
"SOURCE_NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"TARGET_NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"TARGET_CATALOG_NAME" VARCHAR2(255),
"TARGET_SCHEMA_NAME" VARCHAR2(255),
"TARGET_TABLE_NAME" VARCHAR2(255),
"FILTER_ON_UPDATE" NUMBER(3) DEFAULT 1 NOT NULL,
"FILTER_ON_INSERT" NUMBER(3) DEFAULT 1 NOT NULL,
"FILTER_ON_DELETE" NUMBER(3) DEFAULT 1 NOT NULL,
"BEFORE_WRITE_SCRIPT" CLOB,
"AFTER_WRITE_SCRIPT" CLOB,
"BATCH_COMPLETE_SCRIPT" CLOB,
"BATCH_COMMIT_SCRIPT" CLOB,
"BATCH_ROLLBACK_SCRIPT" CLOB,
"HANDLE_ERROR_SCRIPT" CLOB,
"CREATE_TIME" TIMESTAMP NOT NULL,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP NOT NULL,
"LOAD_FILTER_ORDER" NUMBER(22) DEFAULT 1 NOT NULL,
"FAIL_ON_ERROR" NUMBER(3) DEFAULT 0 NOT NULL
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_LOAD_FILTER"
ADD CONSTRAINT "SYM_LOAD_FILTER_PK" PRIMARY KEY ("LOAD_FILTER_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_LOCK"(
"LOCK_ACTION" VARCHAR2(50) NOT NULL,
"LOCK_TYPE" VARCHAR2(50) NOT NULL,
"LOCKING_SERVER_ID" VARCHAR2(255),
"LOCK_TIME" TIMESTAMP,
"SHARED_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"SHARED_ENABLE" NUMBER(22) DEFAULT 0 NOT NULL,
"LAST_LOCK_TIME" TIMESTAMP,
"LAST_LOCKING_SERVER_ID" VARCHAR2(255)
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_LOCK"
ADD CONSTRAINT "SYM_LOCK_PK" PRIMARY KEY ("LOCK_ACTION")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_NODE"(
"NODE_ID" VARCHAR2(50) NOT NULL,
"NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"EXTERNAL_ID" VARCHAR2(255) NOT NULL,
"SYNC_ENABLED" NUMBER(3) DEFAULT 0,
"SYNC_URL" VARCHAR2(255),
"SCHEMA_VERSION" VARCHAR2(50),
"SYMMETRIC_VERSION" VARCHAR2(50),
"CONFIG_VERSION" VARCHAR2(50),
"DATABASE_TYPE" VARCHAR2(50),
"DATABASE_VERSION" VARCHAR2(50),
"DATABASE_NAME" VARCHAR2(50),
"BATCH_TO_SEND_COUNT" NUMBER(22) DEFAULT 0,
"BATCH_IN_ERROR_COUNT" NUMBER(22) DEFAULT 0,
"CREATED_AT_NODE_ID" VARCHAR2(50),
"DEPLOYMENT_TYPE" VARCHAR2(50),
"DEPLOYMENT_SUB_TYPE" VARCHAR2(50)
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NODE"
ADD CONSTRAINT "SYM_NODE_PK" PRIMARY KEY ("NODE_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_NODE_COMMUNICATION"(
"NODE_ID" VARCHAR2(50) NOT NULL,
"QUEUE" VARCHAR2(25) DEFAULT 'default' NOT NULL,
"COMMUNICATION_TYPE" VARCHAR2(10) NOT NULL,
"LOCK_TIME" TIMESTAMP,
"LOCKING_SERVER_ID" VARCHAR2(255),
"LAST_LOCK_TIME" TIMESTAMP,
"LAST_LOCK_MILLIS" NUMBER(38) DEFAULT 0,
"SUCCESS_COUNT" NUMBER(38) DEFAULT 0,
"FAIL_COUNT" NUMBER(38) DEFAULT 0,
"SKIP_COUNT" NUMBER(38) DEFAULT 0,
"TOTAL_SUCCESS_COUNT" NUMBER(38) DEFAULT 0,
"TOTAL_FAIL_COUNT" NUMBER(38) DEFAULT 0,
"TOTAL_SUCCESS_MILLIS" NUMBER(38) DEFAULT 0,
"TOTAL_FAIL_MILLIS" NUMBER(38) DEFAULT 0,
"BATCH_TO_SEND_COUNT" NUMBER(38) DEFAULT 0,
"NODE_PRIORITY" NUMBER(22) DEFAULT 0
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NODE_COMMUNICATION"
ADD CONSTRAINT "SYM_NODE_COMMUNICATION_PK" PRIMARY KEY ("NODE_ID", "QUEUE", "COMMUNICATION_TYPE")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_NODE_CHANNEL_CTL"(
"NODE_ID" VARCHAR2(50) NOT NULL,
"CHANNEL_ID" VARCHAR2(128) NOT NULL,
"SUSPEND_ENABLED" NUMBER(3) DEFAULT 0,
"IGNORE_ENABLED" NUMBER(3) DEFAULT 0,
"LAST_EXTRACT_TIME" TIMESTAMP
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NODE_CHANNEL_CTL"
ADD CONSTRAINT "SYM_NODE_CHANNEL_CTL_PK" PRIMARY KEY ("NODE_ID", "CHANNEL_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_NODE_GROUP"(
"NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"DESCRIPTION" VARCHAR2(255),
"CREATE_TIME" TIMESTAMP,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NODE_GROUP"
ADD CONSTRAINT "SYM_NODE_GROUP_PK" PRIMARY KEY ("NODE_GROUP_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_NODE_GROUP_CHANNEL_WND"(
"NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"CHANNEL_ID" VARCHAR2(128) NOT NULL,
"START_TIME" TIMESTAMP NOT NULL,
"END_TIME" TIMESTAMP NOT NULL,
"ENABLED" NUMBER(3) DEFAULT 0 NOT NULL
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NODE_GROUP_CHANNEL_WND"
ADD CONSTRAINT "SYM_NODE_GROUP_CHANNEL_WND_PK" PRIMARY KEY ("NODE_GROUP_ID", "CHANNEL_ID", "START_TIME", "END_TIME")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_NODE_GROUP_LINK"(
"SOURCE_NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"TARGET_NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"DATA_EVENT_ACTION" CHAR(1) DEFAULT 'W' NOT NULL,
"SYNC_CONFIG_ENABLED" NUMBER(3) DEFAULT 1 NOT NULL,
"IS_REVERSIBLE" NUMBER(3) DEFAULT 0 NOT NULL,
"CREATE_TIME" TIMESTAMP,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NODE_GROUP_LINK"
ADD CONSTRAINT "SYM_NODE_GROUP_LINK_PK" PRIMARY KEY ("SOURCE_NODE_GROUP_ID", "TARGET_NODE_GROUP_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_NODE_HOST"(
"NODE_ID" VARCHAR2(50) NOT NULL,
"HOST_NAME" VARCHAR2(60) NOT NULL,
"INSTANCE_ID" VARCHAR2(60),
"IP_ADDRESS" VARCHAR2(50),
"OS_USER" VARCHAR2(50),
"OS_NAME" VARCHAR2(50),
"OS_ARCH" VARCHAR2(50),
"OS_VERSION" VARCHAR2(50),
"AVAILABLE_PROCESSORS" NUMBER(22) DEFAULT 0,
"FREE_MEMORY_BYTES" NUMBER(38) DEFAULT 0,
"TOTAL_MEMORY_BYTES" NUMBER(38) DEFAULT 0,
"MAX_MEMORY_BYTES" NUMBER(38) DEFAULT 0,
"JAVA_VERSION" VARCHAR2(50),
"JAVA_VENDOR" VARCHAR2(255),
"JDBC_VERSION" VARCHAR2(255),
"SYMMETRIC_VERSION" VARCHAR2(50),
"TIMEZONE_OFFSET" VARCHAR2(6),
"HEARTBEAT_TIME" TIMESTAMP,
"LAST_RESTART_TIME" TIMESTAMP NOT NULL,
"CREATE_TIME" TIMESTAMP NOT NULL
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NODE_HOST"
ADD CONSTRAINT "SYM_NODE_HOST_PK" PRIMARY KEY ("NODE_ID", "HOST_NAME")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_NODE_HOST_CHANNEL_STATS"(
"NODE_ID" VARCHAR2(50) NOT NULL,
"HOST_NAME" VARCHAR2(60) NOT NULL,
"CHANNEL_ID" VARCHAR2(128) NOT NULL,
"START_TIME" TIMESTAMP NOT NULL,
"END_TIME" TIMESTAMP NOT NULL,
"DATA_ROUTED" NUMBER(38) DEFAULT 0,
"DATA_UNROUTED" NUMBER(38) DEFAULT 0,
"DATA_EVENT_INSERTED" NUMBER(38) DEFAULT 0,
"DATA_EXTRACTED" NUMBER(38) DEFAULT 0,
"DATA_BYTES_EXTRACTED" NUMBER(38) DEFAULT 0,
"DATA_EXTRACTED_ERRORS" NUMBER(38) DEFAULT 0,
"DATA_BYTES_SENT" NUMBER(38) DEFAULT 0,
"DATA_SENT" NUMBER(38) DEFAULT 0,
"DATA_SENT_ERRORS" NUMBER(38) DEFAULT 0,
"DATA_LOADED" NUMBER(38) DEFAULT 0,
"DATA_BYTES_LOADED" NUMBER(38) DEFAULT 0,
"DATA_LOADED_ERRORS" NUMBER(38) DEFAULT 0,
"DATA_LOADED_OUTGOING" NUMBER(38) DEFAULT 0,
"DATA_BYTES_LOADED_OUTGOING" NUMBER(38) DEFAULT 0,
"DATA_LOADED_OUTGOING_ERRORS" NUMBER(38) DEFAULT 0
)
[source-000] - OracleSymmetricDialect - DDL applied: CREATE INDEX "SYM_IDX_ND_HST_CHNL_STS" ON "SYM_NODE_HOST_CHANNEL_STATS" ("NODE_ID", "START_TIME", "END_TIME")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NODE_HOST_CHANNEL_STATS"
ADD CONSTRAINT "SYM_NODE_HOST_CHANNEL_STATS_PK" PRIMARY KEY ("NODE_ID", "HOST_NAME", "CHANNEL_ID", "START_TIME", "END_TIME")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_NODE_HOST_JOB_STATS"(
"NODE_ID" VARCHAR2(50) NOT NULL,
"HOST_NAME" VARCHAR2(60) NOT NULL,
"JOB_NAME" VARCHAR2(50) NOT NULL,
"START_TIME" TIMESTAMP NOT NULL,
"END_TIME" TIMESTAMP NOT NULL,
"PROCESSED_COUNT" NUMBER(38) DEFAULT 0,
"TARGET_NODE_ID" VARCHAR2(50),
"TARGET_NODE_COUNT" NUMBER(22) DEFAULT 0
)
[source-000] - OracleSymmetricDialect - DDL applied: CREATE INDEX "SYM_IDX_ND_HST_JOB" ON "SYM_NODE_HOST_JOB_STATS" ("NODE_ID", "START_TIME", "END_TIME")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NODE_HOST_JOB_STATS"
ADD CONSTRAINT "SYM_NODE_HOST_JOB_STATS_PK" PRIMARY KEY ("NODE_ID", "HOST_NAME", "JOB_NAME", "START_TIME", "END_TIME")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_NODE_HOST_STATS"(
"NODE_ID" VARCHAR2(50) NOT NULL,
"HOST_NAME" VARCHAR2(60) NOT NULL,
"START_TIME" TIMESTAMP NOT NULL,
"END_TIME" TIMESTAMP NOT NULL,
"RESTARTED" NUMBER(38) DEFAULT 0 NOT NULL,
"NODES_PULLED" NUMBER(38) DEFAULT 0,
"TOTAL_NODES_PULL_TIME" NUMBER(38) DEFAULT 0,
"NODES_PUSHED" NUMBER(38) DEFAULT 0,
"TOTAL_NODES_PUSH_TIME" NUMBER(38) DEFAULT 0,
"NODES_REJECTED" NUMBER(38) DEFAULT 0,
"NODES_REGISTERED" NUMBER(38) DEFAULT 0,
"NODES_LOADED" NUMBER(38) DEFAULT 0,
"NODES_DISABLED" NUMBER(38) DEFAULT 0,
"PURGED_DATA_ROWS" NUMBER(38) DEFAULT 0,
"PURGED_DATA_EVENT_ROWS" NUMBER(38) DEFAULT 0,
"PURGED_BATCH_OUTGOING_ROWS" NUMBER(38) DEFAULT 0,
"PURGED_BATCH_INCOMING_ROWS" NUMBER(38) DEFAULT 0,
"TRIGGERS_CREATED_COUNT" NUMBER(38),
"TRIGGERS_REBUILT_COUNT" NUMBER(38),
"TRIGGERS_REMOVED_COUNT" NUMBER(38)
)
[source-000] - OracleSymmetricDialect - DDL applied: CREATE INDEX "SYM_IDX_ND_HST_STS" ON "SYM_NODE_HOST_STATS" ("NODE_ID", "START_TIME", "END_TIME")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NODE_HOST_STATS"
ADD CONSTRAINT "SYM_NODE_HOST_STATS_PK" PRIMARY KEY ("NODE_ID", "HOST_NAME", "START_TIME", "END_TIME")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_NODE_IDENTITY"(
"NODE_ID" VARCHAR2(50) NOT NULL
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NODE_IDENTITY"
ADD CONSTRAINT "SYM_NODE_IDENTITY_PK" PRIMARY KEY ("NODE_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_NODE_SECURITY"(
"NODE_ID" VARCHAR2(50) NOT NULL,
"NODE_PASSWORD" VARCHAR2(50) NOT NULL,
"REGISTRATION_ENABLED" NUMBER(3) DEFAULT 0,
"REGISTRATION_TIME" TIMESTAMP,
"INITIAL_LOAD_ENABLED" NUMBER(3) DEFAULT 0,
"INITIAL_LOAD_TIME" TIMESTAMP,
"INITIAL_LOAD_END_TIME" TIMESTAMP,
"INITIAL_LOAD_ID" NUMBER(38),
"INITIAL_LOAD_CREATE_BY" VARCHAR2(255),
"REV_INITIAL_LOAD_ENABLED" NUMBER(3) DEFAULT 0,
"REV_INITIAL_LOAD_TIME" TIMESTAMP,
"REV_INITIAL_LOAD_ID" NUMBER(38),
"REV_INITIAL_LOAD_CREATE_BY" VARCHAR2(255),
"FAILED_LOGINS" NUMBER(3) DEFAULT 0,
"CREATED_AT_NODE_ID" VARCHAR2(50)
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NODE_SECURITY"
ADD CONSTRAINT "SYM_NODE_SECURITY_PK" PRIMARY KEY ("NODE_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_MONITOR"(
"MONITOR_ID" VARCHAR2(128) NOT NULL,
"NODE_GROUP_ID" VARCHAR2(50) DEFAULT 'ALL' NOT NULL,
"EXTERNAL_ID" VARCHAR2(255) DEFAULT 'ALL' NOT NULL,
"TYPE" VARCHAR2(50) NOT NULL,
"EXPRESSION" CLOB,
"THRESHOLD" NUMBER(38) DEFAULT 0 NOT NULL,
"RUN_PERIOD" NUMBER(22) DEFAULT 0 NOT NULL,
"RUN_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"SEVERITY_LEVEL" NUMBER(22) DEFAULT 0 NOT NULL,
"ENABLED" NUMBER(3) DEFAULT 0 NOT NULL,
"CREATE_TIME" TIMESTAMP,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_MONITOR"
ADD CONSTRAINT "SYM_MONITOR_PK" PRIMARY KEY ("MONITOR_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_MONITOR_EVENT"(
"MONITOR_ID" VARCHAR2(128) NOT NULL,
"NODE_ID" VARCHAR2(50) NOT NULL,
"EVENT_TIME" TIMESTAMP NOT NULL,
"HOST_NAME" VARCHAR2(60),
"TYPE" VARCHAR2(50) NOT NULL,
"THRESHOLD" NUMBER(38) DEFAULT 0 NOT NULL,
"EVENT_VALUE" NUMBER(38) DEFAULT 0 NOT NULL,
"EVENT_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"SEVERITY_LEVEL" NUMBER(22) DEFAULT 0 NOT NULL,
"IS_RESOLVED" NUMBER(3) DEFAULT 0 NOT NULL,
"IS_NOTIFIED" NUMBER(3) DEFAULT 0 NOT NULL,
"DETAILS" CLOB,
"LAST_UPDATE_TIME" TIMESTAMP
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_MONITOR_EVENT"
ADD CONSTRAINT "SYM_MONITOR_EVENT_PK" PRIMARY KEY ("MONITOR_ID", "NODE_ID", "EVENT_TIME")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_NOTIFICATION"(
"NOTIFICATION_ID" VARCHAR2(128) NOT NULL,
"NODE_GROUP_ID" VARCHAR2(50) DEFAULT 'ALL' NOT NULL,
"EXTERNAL_ID" VARCHAR2(255) DEFAULT 'ALL' NOT NULL,
"SEVERITY_LEVEL" NUMBER(22) DEFAULT 0 NOT NULL,
"TYPE" VARCHAR2(50) NOT NULL,
"EXPRESSION" CLOB,
"ENABLED" NUMBER(3) DEFAULT 0 NOT NULL,
"CREATE_TIME" TIMESTAMP,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NOTIFICATION"
ADD CONSTRAINT "SYM_NOTIFICATION_PK" PRIMARY KEY ("NOTIFICATION_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_OUTGOING_BATCH"(
"BATCH_ID" NUMBER(38) NOT NULL,
"NODE_ID" VARCHAR2(50) NOT NULL,
"CHANNEL_ID" VARCHAR2(128),
"STATUS" CHAR(2),
"ERROR_FLAG" NUMBER(3) DEFAULT 0,
"SQL_STATE" VARCHAR2(10),
"SQL_CODE" NUMBER(22) DEFAULT 0 NOT NULL,
"SQL_MESSAGE" CLOB,
"LAST_UPDATE_HOSTNAME" VARCHAR2(255),
"LAST_UPDATE_TIME" TIMESTAMP,
"CREATE_TIME" TIMESTAMP,
"SUMMARY" VARCHAR2(255),
"IGNORE_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"BYTE_COUNT" NUMBER(38) DEFAULT 0 NOT NULL,
"LOAD_FLAG" NUMBER(3) DEFAULT 0,
"EXTRACT_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"SENT_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"LOAD_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"RELOAD_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"OTHER_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"DATA_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"EXTRACT_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"LOAD_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"DATA_INSERT_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"DATA_UPDATE_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"DATA_DELETE_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"EXTRACT_INSERT_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"EXTRACT_UPDATE_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"EXTRACT_DELETE_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"LOAD_INSERT_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"LOAD_UPDATE_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"LOAD_DELETE_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"NETWORK_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"FILTER_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"LOAD_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"ROUTER_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"EXTRACT_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"TRANSFORM_EXTRACT_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"TRANSFORM_LOAD_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"LOAD_ID" NUMBER(38),
"COMMON_FLAG" NUMBER(3) DEFAULT 0,
"FALLBACK_INSERT_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"FALLBACK_UPDATE_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"IGNORE_ROW_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"MISSING_DELETE_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"SKIP_COUNT" NUMBER(22) DEFAULT 0 NOT NULL,
"TOTAL_EXTRACT_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"TOTAL_LOAD_MILLIS" NUMBER(22) DEFAULT 0 NOT NULL,
"EXTRACT_JOB_FLAG" NUMBER(3) DEFAULT 0,
"EXTRACT_START_TIME" TIMESTAMP,
"TRANSFER_START_TIME" TIMESTAMP,
"LOAD_START_TIME" TIMESTAMP,
"FAILED_DATA_ID" NUMBER(38) DEFAULT 0 NOT NULL,
"FAILED_LINE_NUMBER" NUMBER(38) DEFAULT 0 NOT NULL,
"CREATE_BY" VARCHAR2(255)
)
[source-000] - OracleSymmetricDialect - DDL applied: CREATE INDEX "SYM_IDX_OB_NODE_STATUS" ON "SYM_OUTGOING_BATCH" ("NODE_ID", "STATUS")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE INDEX "SYM_IDX_OB_STATUS" ON "SYM_OUTGOING_BATCH" ("STATUS")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE INDEX "SYM_IDX_OB_IN_ERROR" ON "SYM_OUTGOING_BATCH" ("ERROR_FLAG")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_OUTGOING_BATCH"
ADD CONSTRAINT "SYM_OUTGOING_BATCH_PK" PRIMARY KEY ("BATCH_ID", "NODE_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_PARAMETER"(
"EXTERNAL_ID" VARCHAR2(255) NOT NULL,
"NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"PARAM_KEY" VARCHAR2(80) NOT NULL,
"PARAM_VALUE" CLOB,
"CREATE_TIME" TIMESTAMP,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_PARAMETER"
ADD CONSTRAINT "SYM_PARAMETER_PK" PRIMARY KEY ("EXTERNAL_ID", "NODE_GROUP_ID", "PARAM_KEY")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_REGISTRATION_REDIRECT"(
"REGISTRANT_EXTERNAL_ID" VARCHAR2(255) NOT NULL,
"REGISTRATION_NODE_ID" VARCHAR2(50) NOT NULL
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_REGISTRATION_REDIRECT"
ADD CONSTRAINT "SYM_REGISTRATION_REDIRECT_PK" PRIMARY KEY ("REGISTRANT_EXTERNAL_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_REGISTRATION_REQUEST"(
"NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"EXTERNAL_ID" VARCHAR2(255) NOT NULL,
"STATUS" CHAR(2) NOT NULL,
"HOST_NAME" VARCHAR2(60) NOT NULL,
"IP_ADDRESS" VARCHAR2(50) NOT NULL,
"ATTEMPT_COUNT" NUMBER(22) DEFAULT 0,
"REGISTERED_NODE_ID" VARCHAR2(50),
"ERROR_MESSAGE" CLOB,
"CREATE_TIME" TIMESTAMP NOT NULL,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP NOT NULL
)
[source-000] - OracleSymmetricDialect - DDL applied: CREATE INDEX "SYM_IDX_REG_REQ_1" ON "SYM_REGISTRATION_REQUEST" ("NODE_GROUP_ID", "EXTERNAL_ID", "STATUS", "HOST_NAME", "IP_ADDRESS")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE INDEX "SYM_IDX_REG_REQ_2" ON "SYM_REGISTRATION_REQUEST" ("STATUS")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_REGISTRATION_REQUEST"
ADD CONSTRAINT "SYM_REGISTRATION_REQUEST_PK" PRIMARY KEY ("NODE_GROUP_ID", "EXTERNAL_ID", "CREATE_TIME")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_ROUTER"(
"ROUTER_ID" VARCHAR2(50) NOT NULL,
"TARGET_CATALOG_NAME" VARCHAR2(255),
"TARGET_SCHEMA_NAME" VARCHAR2(255),
"TARGET_TABLE_NAME" VARCHAR2(255),
"SOURCE_NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"TARGET_NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"ROUTER_TYPE" VARCHAR2(50) DEFAULT 'default' NOT NULL,
"ROUTER_EXPRESSION" CLOB,
"SYNC_ON_UPDATE" NUMBER(3) DEFAULT 1 NOT NULL,
"SYNC_ON_INSERT" NUMBER(3) DEFAULT 1 NOT NULL,
"SYNC_ON_DELETE" NUMBER(3) DEFAULT 1 NOT NULL,
"USE_SOURCE_CATALOG_SCHEMA" NUMBER(3) DEFAULT 1 NOT NULL,
"CREATE_TIME" TIMESTAMP NOT NULL,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP NOT NULL,
"DESCRIPTION" CLOB
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_ROUTER"
ADD CONSTRAINT "SYM_ROUTER_PK" PRIMARY KEY ("ROUTER_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_SEQUENCE"(
"SEQUENCE_NAME" VARCHAR2(50) NOT NULL,
"CURRENT_VALUE" NUMBER(38) DEFAULT 0 NOT NULL,
"INCREMENT_BY" NUMBER(22) DEFAULT 1 NOT NULL,
"MIN_VALUE" NUMBER(38) DEFAULT 1 NOT NULL,
"MAX_VALUE" NUMBER(38) DEFAULT 9999999999 NOT NULL,
"CYCLE_FLAG" NUMBER(3) DEFAULT 0,
"CACHE_SIZE" NUMBER(22) DEFAULT 0 NOT NULL,
"CREATE_TIME" TIMESTAMP,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP NOT NULL
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_SEQUENCE"
ADD CONSTRAINT "SYM_SEQUENCE_PK" PRIMARY KEY ("SEQUENCE_NAME")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_TABLE_RELOAD_REQUEST"(
"TARGET_NODE_ID" VARCHAR2(50) NOT NULL,
"SOURCE_NODE_ID" VARCHAR2(50) NOT NULL,
"TRIGGER_ID" VARCHAR2(128) NOT NULL,
"ROUTER_ID" VARCHAR2(50) NOT NULL,
"CREATE_TIME" TIMESTAMP NOT NULL,
"CREATE_TABLE" NUMBER(3) DEFAULT 0 NOT NULL,
"DELETE_FIRST" NUMBER(3) DEFAULT 0 NOT NULL,
"RELOAD_SELECT" CLOB,
"BEFORE_CUSTOM_SQL" CLOB,
"RELOAD_TIME" TIMESTAMP,
"LOAD_ID" NUMBER(38),
"PROCESSED" NUMBER(3) DEFAULT 0 NOT NULL,
"CHANNEL_ID" VARCHAR2(128),
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP NOT NULL
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_TABLE_RELOAD_REQUEST"
ADD CONSTRAINT "SYM_TABLE_RELOAD_REQUEST_PK" PRIMARY KEY ("TARGET_NODE_ID", "SOURCE_NODE_ID", "TRIGGER_ID", "ROUTER_ID", "CREATE_TIME")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_TABLE_RELOAD_STATUS"(
"LOAD_ID" NUMBER(38) NOT NULL,
"SOURCE_NODE_ID" VARCHAR2(50) NOT NULL,
"TARGET_NODE_ID" VARCHAR2(50) NOT NULL,
"START_TIME" TIMESTAMP,
"END_TIME" TIMESTAMP,
"COMPLETED" NUMBER(3) DEFAULT 0 NOT NULL,
"CANCELLED" NUMBER(3) DEFAULT 0 NOT NULL,
"FULL_LOAD" NUMBER(3) DEFAULT 0 NOT NULL,
"START_DATA_BATCH_ID" NUMBER(38),
"END_DATA_BATCH_ID" NUMBER(38),
"SETUP_BATCH_COUNT" NUMBER(38) DEFAULT 0 NOT NULL,
"DATA_BATCH_COUNT" NUMBER(38) DEFAULT 0 NOT NULL,
"FINALIZE_BATCH_COUNT" NUMBER(38) DEFAULT 0 NOT NULL,
"SETUP_BATCH_LOADED" NUMBER(38) DEFAULT 0 NOT NULL,
"DATA_BATCH_LOADED" NUMBER(38) DEFAULT 0 NOT NULL,
"FINALIZE_BATCH_LOADED" NUMBER(38) DEFAULT 0 NOT NULL,
"TABLE_COUNT" NUMBER(38) DEFAULT 0 NOT NULL,
"ROWS_LOADED" NUMBER(38) DEFAULT 0 NOT NULL,
"ROWS_COUNT" NUMBER(38) DEFAULT 0 NOT NULL,
"ERROR_FLAG" NUMBER(3) DEFAULT 0 NOT NULL,
"SQL_STATE" VARCHAR2(10),
"SQL_CODE" NUMBER(22) DEFAULT 0 NOT NULL,
"SQL_MESSAGE" CLOB,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP NOT NULL
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_TABLE_RELOAD_STATUS"
ADD CONSTRAINT "SYM_TABLE_RELOAD_STATUS_PK" PRIMARY KEY ("LOAD_ID", "SOURCE_NODE_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_TRANSFORM_TABLE"(
"TRANSFORM_ID" VARCHAR2(50) NOT NULL,
"SOURCE_NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"TARGET_NODE_GROUP_ID" VARCHAR2(50) NOT NULL,
"TRANSFORM_POINT" VARCHAR2(10) NOT NULL,
"SOURCE_CATALOG_NAME" VARCHAR2(255),
"SOURCE_SCHEMA_NAME" VARCHAR2(255),
"SOURCE_TABLE_NAME" VARCHAR2(255) NOT NULL,
"TARGET_CATALOG_NAME" VARCHAR2(255),
"TARGET_SCHEMA_NAME" VARCHAR2(255),
"TARGET_TABLE_NAME" VARCHAR2(255),
"UPDATE_FIRST" NUMBER(3) DEFAULT 0,
"UPDATE_ACTION" VARCHAR2(255) DEFAULT 'UPDATE_COL' NOT NULL,
"DELETE_ACTION" VARCHAR2(10) NOT NULL,
"TRANSFORM_ORDER" NUMBER(22) DEFAULT 1 NOT NULL,
"COLUMN_POLICY" VARCHAR2(10) DEFAULT 'SPECIFIED' NOT NULL,
"CREATE_TIME" TIMESTAMP,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP,
"DESCRIPTION" CLOB
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_TRANSFORM_TABLE"
ADD CONSTRAINT "SYM_TRANSFORM_TABLE_PK" PRIMARY KEY ("TRANSFORM_ID", "SOURCE_NODE_GROUP_ID", "TARGET_NODE_GROUP_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_TRANSFORM_COLUMN"(
"TRANSFORM_ID" VARCHAR2(50) NOT NULL,
"INCLUDE_ON" CHAR(1) DEFAULT '*' NOT NULL,
"TARGET_COLUMN_NAME" VARCHAR2(128) NOT NULL,
"SOURCE_COLUMN_NAME" VARCHAR2(128),
"PK" NUMBER(3) DEFAULT 0,
"TRANSFORM_TYPE" VARCHAR2(50) DEFAULT 'copy',
"TRANSFORM_EXPRESSION" CLOB,
"TRANSFORM_ORDER" NUMBER(22) DEFAULT 1 NOT NULL,
"CREATE_TIME" TIMESTAMP,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP,
"DESCRIPTION" CLOB
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_TRANSFORM_COLUMN"
ADD CONSTRAINT "SYM_TRANSFORM_COLUMN_PK" PRIMARY KEY ("TRANSFORM_ID", "INCLUDE_ON", "TARGET_COLUMN_NAME")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_TRIGGER"(
"TRIGGER_ID" VARCHAR2(128) NOT NULL,
"SOURCE_CATALOG_NAME" VARCHAR2(255),
"SOURCE_SCHEMA_NAME" VARCHAR2(255),
"SOURCE_TABLE_NAME" VARCHAR2(255) NOT NULL,
"CHANNEL_ID" VARCHAR2(128) NOT NULL,
"RELOAD_CHANNEL_ID" VARCHAR2(128) DEFAULT 'reload' NOT NULL,
"SYNC_ON_UPDATE" NUMBER(3) DEFAULT 1 NOT NULL,
"SYNC_ON_INSERT" NUMBER(3) DEFAULT 1 NOT NULL,
"SYNC_ON_DELETE" NUMBER(3) DEFAULT 1 NOT NULL,
"SYNC_ON_INCOMING_BATCH" NUMBER(3) DEFAULT 0 NOT NULL,
"NAME_FOR_UPDATE_TRIGGER" VARCHAR2(255),
"NAME_FOR_INSERT_TRIGGER" VARCHAR2(255),
"NAME_FOR_DELETE_TRIGGER" VARCHAR2(255),
"SYNC_ON_UPDATE_CONDITION" CLOB,
"SYNC_ON_INSERT_CONDITION" CLOB,
"SYNC_ON_DELETE_CONDITION" CLOB,
"CUSTOM_BEFORE_UPDATE_TEXT" CLOB,
"CUSTOM_BEFORE_INSERT_TEXT" CLOB,
"CUSTOM_BEFORE_DELETE_TEXT" CLOB,
"CUSTOM_ON_UPDATE_TEXT" CLOB,
"CUSTOM_ON_INSERT_TEXT" CLOB,
"CUSTOM_ON_DELETE_TEXT" CLOB,
"EXTERNAL_SELECT" CLOB,
"TX_ID_EXPRESSION" CLOB,
"CHANNEL_EXPRESSION" CLOB,
"EXCLUDED_COLUMN_NAMES" CLOB,
"INCLUDED_COLUMN_NAMES" CLOB,
"SYNC_KEY_NAMES" CLOB,
"USE_STREAM_LOBS" NUMBER(3) DEFAULT 0 NOT NULL,
"USE_CAPTURE_LOBS" NUMBER(3) DEFAULT 0 NOT NULL,
"USE_CAPTURE_OLD_DATA" NUMBER(3) DEFAULT 1 NOT NULL,
"USE_HANDLE_KEY_UPDATES" NUMBER(3) DEFAULT 1 NOT NULL,
"STREAM_ROW" NUMBER(3) DEFAULT 0 NOT NULL,
"CREATE_TIME" TIMESTAMP NOT NULL,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP NOT NULL,
"DESCRIPTION" CLOB
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_TRIGGER"
ADD CONSTRAINT "SYM_TRIGGER_PK" PRIMARY KEY ("TRIGGER_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_TRIGGER_HIST"(
"TRIGGER_HIST_ID" NUMBER(22) NOT NULL,
"TRIGGER_ID" VARCHAR2(128) NOT NULL,
"SOURCE_TABLE_NAME" VARCHAR2(255) NOT NULL,
"SOURCE_CATALOG_NAME" VARCHAR2(255),
"SOURCE_SCHEMA_NAME" VARCHAR2(255),
"NAME_FOR_UPDATE_TRIGGER" VARCHAR2(255),
"NAME_FOR_INSERT_TRIGGER" VARCHAR2(255),
"NAME_FOR_DELETE_TRIGGER" VARCHAR2(255),
"TABLE_HASH" NUMBER(38) DEFAULT 0 NOT NULL,
"TRIGGER_ROW_HASH" NUMBER(38) DEFAULT 0 NOT NULL,
"TRIGGER_TEMPLATE_HASH" NUMBER(38) DEFAULT 0 NOT NULL,
"COLUMN_NAMES" CLOB NOT NULL,
"PK_COLUMN_NAMES" CLOB NOT NULL,
"LAST_TRIGGER_BUILD_REASON" CHAR(1) NOT NULL,
"ERROR_MESSAGE" CLOB,
"CREATE_TIME" TIMESTAMP NOT NULL,
"INACTIVE_TIME" TIMESTAMP
)
[source-000] - OracleSymmetricDialect - DDL applied: CREATE INDEX "SYM_IDX_TRIGG_HIST_1" ON "SYM_TRIGGER_HIST" ("TRIGGER_ID", "INACTIVE_TIME")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_TRIGGER_HIST"
ADD CONSTRAINT "SYM_TRIGGER_HIST_PK" PRIMARY KEY ("TRIGGER_HIST_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_TRIGGER_ROUTER"(
"TRIGGER_ID" VARCHAR2(128) NOT NULL,
"ROUTER_ID" VARCHAR2(50) NOT NULL,
"ENABLED" NUMBER(3) DEFAULT 1 NOT NULL,
"INITIAL_LOAD_ORDER" NUMBER(22) DEFAULT 1 NOT NULL,
"INITIAL_LOAD_SELECT" CLOB,
"INITIAL_LOAD_DELETE_STMT" CLOB,
"PING_BACK_ENABLED" NUMBER(3) DEFAULT 0 NOT NULL,
"CREATE_TIME" TIMESTAMP NOT NULL,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP NOT NULL,
"DESCRIPTION" CLOB
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_TRIGGER_ROUTER"
ADD CONSTRAINT "SYM_TRIGGER_ROUTER_PK" PRIMARY KEY ("TRIGGER_ID", "ROUTER_ID")
[source-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_TRIGGER_ROUTER_GROUPLET"(
"GROUPLET_ID" VARCHAR2(50) NOT NULL,
"TRIGGER_ID" VARCHAR2(128) NOT NULL,
"ROUTER_ID" VARCHAR2(50) NOT NULL,
"APPLIES_WHEN" CHAR(1) NOT NULL,
"CREATE_TIME" TIMESTAMP NOT NULL,
"LAST_UPDATE_BY" VARCHAR2(50),
"LAST_UPDATE_TIME" TIMESTAMP NOT NULL
)
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_TRIGGER_ROUTER_GROUPLET"
ADD CONSTRAINT "SYM_TRIGGER_ROUTER_GROUPLET_PK" PRIMARY KEY ("GROUPLET_ID", "TRIGGER_ID", "ROUTER_ID", "APPLIES_WHEN")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_CONFLICT"
ADD CONSTRAINT "SYM_FK_CF_2_GRP_LNK" FOREIGN KEY ("SOURCE_NODE_GROUP_ID", "TARGET_NODE_GROUP_ID") REFERENCES "SYM_NODE_GROUP_LINK" ("SOURCE_NODE_GROUP_ID", "TARGET_NODE_GROUP_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_FILE_TRIGGER_ROUTER"
ADD CONSTRAINT "SYM_FK_FTR_2_FTRG" FOREIGN KEY ("TRIGGER_ID") REFERENCES "SYM_FILE_TRIGGER" ("TRIGGER_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_FILE_TRIGGER_ROUTER"
ADD CONSTRAINT "SYM_FK_FTR_2_RTR" FOREIGN KEY ("ROUTER_ID") REFERENCES "SYM_ROUTER" ("ROUTER_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_GROUPLET_LINK"
ADD CONSTRAINT "SYM_FK_GPLTLNK_2_GPLT" FOREIGN KEY ("GROUPLET_ID") REFERENCES "SYM_GROUPLET" ("GROUPLET_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NODE_GROUP_LINK"
ADD CONSTRAINT "SYM_FK_LNK_2_GRP_SRC" FOREIGN KEY ("SOURCE_NODE_GROUP_ID") REFERENCES "SYM_NODE_GROUP" ("NODE_GROUP_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NODE_GROUP_LINK"
ADD CONSTRAINT "SYM_FK_LNK_2_GRP_TGT" FOREIGN KEY ("TARGET_NODE_GROUP_ID") REFERENCES "SYM_NODE_GROUP" ("NODE_GROUP_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NODE_IDENTITY"
ADD CONSTRAINT "SYM_FK_IDENT_2_NODE" FOREIGN KEY ("NODE_ID") REFERENCES "SYM_NODE" ("NODE_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_NODE_SECURITY"
ADD CONSTRAINT "SYM_FK_SEC_2_NODE" FOREIGN KEY ("NODE_ID") REFERENCES "SYM_NODE" ("NODE_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_ROUTER"
ADD CONSTRAINT "SYM_FK_RT_2_GRP_LNK" FOREIGN KEY ("SOURCE_NODE_GROUP_ID", "TARGET_NODE_GROUP_ID") REFERENCES "SYM_NODE_GROUP_LINK" ("SOURCE_NODE_GROUP_ID", "TARGET_NODE_GROUP_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_TRANSFORM_TABLE"
ADD CONSTRAINT "SYM_FK_TT_2_GRP_LNK" FOREIGN KEY ("SOURCE_NODE_GROUP_ID", "TARGET_NODE_GROUP_ID") REFERENCES "SYM_NODE_GROUP_LINK" ("SOURCE_NODE_GROUP_ID", "TARGET_NODE_GROUP_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_TRIGGER"
ADD CONSTRAINT "SYM_FK_TRG_2_CHNL" FOREIGN KEY ("CHANNEL_ID") REFERENCES "SYM_CHANNEL" ("CHANNEL_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_TRIGGER"
ADD CONSTRAINT "SYM_FK_TRG_2_RLD_CHNL" FOREIGN KEY ("RELOAD_CHANNEL_ID") REFERENCES "SYM_CHANNEL" ("CHANNEL_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_TRIGGER_ROUTER"
ADD CONSTRAINT "SYM_FK_TR_2_TRG" FOREIGN KEY ("TRIGGER_ID") REFERENCES "SYM_TRIGGER" ("TRIGGER_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_TRIGGER_ROUTER"
ADD CONSTRAINT "SYM_FK_TR_2_RTR" FOREIGN KEY ("ROUTER_ID") REFERENCES "SYM_ROUTER" ("ROUTER_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_TRIGGER_ROUTER_GROUPLET"
ADD CONSTRAINT "SYM_FK_TRGPLT_2_GPLT" FOREIGN KEY ("GROUPLET_ID") REFERENCES "SYM_GROUPLET" ("GROUPLET_ID")
[source-000] - OracleSymmetricDialect - DDL applied: ALTER TABLE "SYM_TRIGGER_ROUTER_GROUPLET"
ADD CONSTRAINT "SYM_FK_TRGPLT_2_TR" FOREIGN KEY ("TRIGGER_ID", "ROUTER_ID") REFERENCES "SYM_TRIGGER_ROUTER" ("TRIGGER_ID", "ROUTER_ID")
[source-000] - ClusterService - This node picked a server id of symdssrc
[source-000] - TriggerRouterService - Synchronizing triggers
[source-000] - TriggerRouterService - Done synchronizing triggers
[source-000] - OracleSymmetricDialect - Done with auto update of SymmetricDS tables
[source-000] - ConfigurationService - Auto-configuring config channel
[source-000] - ConfigurationService - Auto-configuring reload channel
[source-000] - ConfigurationService - Auto-configuring monitor channel
[source-000] - ConfigurationService - Auto-configuring heartbeat channel
[source-000] - ConfigurationService - Auto-configuring default channel
[source-000] - ConfigurationService - Auto-configuring dynamic channel
[source-000] - ExtensionService - Found 0 extension points from the database that will be registered
[source-000] - ClientExtensionService - Found 7 extension points from spring that will be registered
[source-000] - AbstractSymmetricEngine - Inserting rows for node, security, identity and group for registration server
[source-000] - AbstractSymmetricEngine - Done initializing SymmetricDS database
[oracle@symdssrc bin]$
The above steps will create new tables name starting with
SYM_ in the SCOTT schema and few indexes and it might create few triggers used
for replication.
Register target database from Source
./symadmin -engine source-000 open-registration dest 001
[oracle@symdssrc bin]$ ./symadmin -engine source-000 open-registration dest 001
Log output will be written to /home/oracle/symds/symmetric-server-3.12.10/logs/symmetric.log
[] - AbstractCommandLauncher - Option: name=engine, value={source-000}
[] - SymmetricUtils -
_____ __ _ ____ _____
/ ___/ __ _____ __ ___ __ ___ _/ /_ ____(_)___ / __ | / ___/
\__ \ / / / / _ `_ \/ _ `_ \/ _ \/_ __// __/ / __/ / / / / \__ \
___/ // /_/ / // // / // // / __// / / / / / /_ / /_/ / ___/ /
/____/ \__ /_//_//_/_//_//_/\___/ \_/ /_/ /_/\__/ /_____/ /____/
/____/
+-----------------------------------------------------------------+
| Copyright (C) 2007-2021 JumpMind, Inc. |
| |
| Licensed under the GNU General Public License version 3. |
| This software comes with ABSOLUTELY NO WARRANTY. |
| See http://www.gnu.org/licenses/gpl.html |
+-----------------------------------------------------------------+
[source-000] - AbstractSymmetricEngine - Initializing connection to database
[source-000] - JdbcDatabasePlatformFactory - Detected database 'Oracle', version '11', protocol 'oracle'
[source-000] - JdbcDatabasePlatformFactory - The IDatabasePlatform being used is org.jumpmind.db.platform.oracle.OracleDatabasePlatform
[source-000] - OracleSymmetricDialect - The DbDialect being used is org.jumpmind.symmetric.db.oracle.OracleSymmetricDialect
[source-000] - ExtensionService - Found 0 extension points from the database that will be registered
[source-000] - StagingManager - The staging directory was initialized at the following location: /home/oracle/symds/symmetric-server-3.12.10/tmp/source-000
[source-000] - ExtensionService - Found 0 extension points from the database that will be registered
[source-000] - ClientExtensionService - Found 7 extension points from spring that will be registered
[source-000] - RegistrationService - Just opened registration for external id of 001 and a node group of dest and a node id of 001
Opened registration for node group of 'dest' external ID of '001'
[oracle@symdssrc bin]$
Symmetricds table configuration for replication
The below script has to be executed for the data synchronization configuration between source to target. On target we will only create the table structure and initial load of data will happen to target once we start the service on source and target
insert into sym_node_group_link (source_node_group_id, target_node_group_id,data_event_action) values ('source', 'dest', 'P');
insert into sym_channel (channel_id, processing_order, max_batch_size,enabled, description) values('ho_sync_capricon', 1, 100000, 1, 'outbound channel');
insert into sym_trigger (trigger_id, source_table_name, channel_id, last_update_time, create_time,sync_on_incoming_batch) values ('outbound_tr_t1', 'TEST1','ho_sync_capricon',sysdate,sysdate,1);
insert into sym_trigger (trigger_id, source_table_name, channel_id, last_update_time, create_time,sync_on_incoming_batch) values ('outbound_tr_t2', 'TEST2','ho_sync_capricon',sysdate,sysdate,1);
insert into sym_trigger (trigger_id, source_table_name, channel_id, last_update_time, create_time,sync_on_incoming_batch) values ('outbound_tr_t3', 'TEST3','ho_sync_capricon',sysdate,sysdate,1);
insert into sym_router (router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time) values('ho-capricon', 'source', 'dest', 'default',current_timestamp, current_timestamp);
insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('outbound_tr_t1','ho-capricon', 1, current_timestamp, current_timestamp);
insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('outbound_tr_t2','ho-capricon', 1, current_timestamp, current_timestamp);
insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('outbound_tr_t3','ho-capricon', 1, current_timestamp, current_timestamp);
The above scripts updates the symmetric tables sym_node_group_link, sym_channel, sym_trigger,sym_trigger_router tables with the information on triggers, trigger routes & group link properties for the objects we replicate from source to target.
For initial load we are updating below tables in SCOTT schema this enables the initial load from source to target.
update SYM_NODE_SECURITY set initial_load_enabled=1 where node_id=001;update SYM_NODE set sync_enabled=1 where node_id=001;
commit;
Execute the above SQL statements for starting the initial load of table data from source to target databaseAn initial load is the process of seeding tables at a target node with data from a source node. Instead of capturing data, data is selected from the source table using a SQL statement and then it is streamed to the client.
The store nodes were pre-configured to do an initial load after registration. However, it is worth noting that Initial Loads can be sent using the following commands:
..bin/symadmin --engine corp-000 reload-node 001
[oracle@symdssrc bin]# ./symadmin --engine source-000 reload-node 001
Log output will be written to /home/oracle/symdshome/symmetric-server-3.12.10/logs/symmetric.log
[] - AbstractCommandLauncher - Option: name=engine, value={source-000}
[] - SymmetricUtils -
_____ __ _ ____ _____
/ ___/ __ _____ __ ___ __ ___ _/ /_ ____(_)___ / __ | / ___/
\__ \ / / / / _ `_ \/ _ `_ \/ _ \/_ __// __/ / __/ / / / / \__ \
___/ // /_/ / // // / // // / __// / / / / / /_ / /_/ / ___/ /
/____/ \__ /_//_//_/_//_//_/\___/ \_/ /_/ /_/\__/ /_____/ /____/
/____/
+-----------------------------------------------------------------+
| Copyright (C) 2007-2021 JumpMind, Inc. |
| |
| Licensed under the GNU General Public License version 3. |
| This software comes with ABSOLUTELY NO WARRANTY. |
| See http://www.gnu.org/licenses/gpl.html |
+-----------------------------------------------------------------+
[source-000] - AbstractSymmetricEngine - Initializing connection to database
[source-000] - JdbcDatabasePlatformFactory - Detected database 'Oracle', version '11', protocol 'oracle'
[source-000] - JdbcDatabasePlatformFactory - The IDatabasePlatform being used is org.jumpmind.db.platform.oracle.OracleDatabasePlatform
[source-000] - OracleSymmetricDialect - The DbDialect being used is org.jumpmind.symmetric.db.oracle.OracleSymmetricDialect
[source-000] - ExtensionService - Found 0 extension points from the database that will be registered
[source-000] - StagingManager - The staging directory was initialized at the following location: /home/oracle/symdshome/symmetric-server-3.12.10/tmp/source-000
[source-000] - ExtensionService - Found 0 extension points from the database that will be registered
[source-000] - ClientExtensionService - Found 7 extension points from spring that will be registered
Successfully enabled initial load for node 001
[oracle@symdssrc bin]#
Start SymmetricDS services @ source & Target server
First Start the service @ source and then on target from Oracle OS user
Source command –
Goto the path - /home/oracle/symdshome/symmetric-server-3.12.10/bin
Execute the command
nohup ./sym > /home/oracle/symdshome/symmetric-server-3.12.10/sym_06242021.log 2>&1 &
To monitor the symmetricds status you can tail the log file found on source location
tail -f /home/oracle/symdshome/symmetric-server-3.12.10/bin/sym_06242021.log
tail -f /home/oracle/symdshome/symmetric-server-3.12.10/logs/symmetric.log
Start services at the target server from Oracle OS user
Goto the location - /home/oracle/symds/symmetric-server-3.12.10/bin
./sym_service start
tail -f /home/oracle/symds/symmetric-server-3.12.10/logs/symmetric.log
Source & Target Startup details in below logs.
[oracle@symdssrc bin]$ nohup ./sym > sym_06242021.log 2>&1 &
[1] 75574
[oracle@symdssrc bin]$
Target services startup traces.
[root@symds bin]# ./sym_service start
Waiting for server to start
Waiting for server to start
......
Started
[root@symds bin]# ./sym_service status
Installed: true
Running: true
Wrapper PID: 34978
Wrapper Running: true
Server PID: 34996
Server Running: true
Once the services are started review the logs mentioned for any errors related to source-000 & dest-001 engines.
SymmetricDS logs & traces are seen below,
Log output will be written to /home/oracle/symdshome/symmetric-server-3.12.10/logs/symmetric.log
[startup] - SymmetricUtils -
_____ __ _ ____ _____
/ ___/ __ _____ __ ___ __ ___ _/ /_ ____(_)___ / __ | / ___/
\__ \ / / / / _ `_ \/ _ `_ \/ _ \/_ __// __/ / __/ / / / / \__ \
___/ // /_/ / // // / // // / __// / / / / / /_ / /_/ / ___/ /
/____/ \__ /_//_//_/_//_//_/\___/ \_/ /_/ /_/\__/ /_____/ /____/
/____/
+-----------------------------------------------------------------+
| Copyright (C) 2007-2021 JumpMind, Inc. |
| |
| Licensed under the GNU General Public License version 3. |
| This software comes with ABSOLUTELY NO WARRANTY. |
| See http://www.gnu.org/licenses/gpl.html |
+-----------------------------------------------------------------+
[startup] - SymmetricWebServer - About to start SymmetricDS web server on 0.0.0.0:31415:HTTP/1.1
[startup] - SymmetricEngineHolder - Current directory is /home/oracle/symdshome/symmetric-server-3.12.10
[startup] - SymmetricEngineHolder - Starting in single-server mode
[source-000] - AbstractSymmetricEngine - Initializing connection to database
[startup] - SymmetricWebServer - Joining the web server main thread
[source-000] - JdbcDatabasePlatformFactory - Detected database 'Oracle', version '11', protocol 'oracle'
[source-000] - JdbcDatabasePlatformFactory - The IDatabasePlatform being used is org.jumpmind.db.platform.oracle.OracleDatabasePlatform
[source-000] - OracleSymmetricDialect - The DbDialect being used is org.jumpmind.symmetric.db.oracle.OracleSymmetricDialect
[source-000] - ExtensionService - Found 0 extension points from the database that will be registered
[source-000] - StagingManager - The staging directory was initialized at the following location: /home/oracle/symdshome/symmetric-server-3.12.10/tmp/source-000
[source-000] - ExtensionService - Found 0 extension points from the database that will be registered
[source-000] - ClientExtensionService - Found 8 extension points from spring that will be registered
[source-000] - AbstractSymmetricEngine - Initializing SymmetricDS database
[source-000] - OracleSymmetricDialect - Checking if SymmetricDS tables need created or altered
[source-000] - AbstractSymmetricEngine - Done initializing SymmetricDS database
[source-000] - AbstractSymmetricEngine - SymmetricDS database version : 3.12.10
[source-000] - AbstractSymmetricEngine - SymmetricDS software version : 3.12.10
[source-000] - AbstractSymmetricEngine - Starting registered node [group=source, id=000, nodeId=000]
[source-000] - ClusterService - This node picked a server id of symdssrc
[source-000] - TriggerRouterService - Synchronizing triggers
[source-000] - TriggerRouterService - Done synchronizing triggers
[source-000] - RouterJob - Starting Routing on periodic schedule: every 5000ms with the first run at 2021-06-24T18:39:02.413+0000
[source-000] - PushJob - Starting Push on periodic schedule: every 10000ms with the first run at 2021-06-24T18:39:02.420+0000
[source-000] - PullJob - Starting Pull on periodic schedule: every 10000ms with the first run at 2021-06-24T18:39:02.421+0000
[source-000] - JobManager - Job Offline Push not configured for auto start
[source-000] - JobManager - Job Offline Pull not configured for auto start
[source-000] - OutgoingPurgeJob - Starting job 'Purge Outgoing' with cron expression: '0 0 */4 * * *'
[source-000] - IncomingPurgeJob - Starting job 'Purge Incoming' with cron expression: '0 0 */4 * * *'
[source-000] - StatisticFlushJob - Starting job 'Stat Flush' with cron expression: '0 0/5 * * * *'
[source-000] - SyncTriggersJob - Starting job 'SyncTriggers' with cron expression: '0 0 0 * * *'
[source-000] - HeartbeatJob - Starting Heartbeat on periodic schedule: every 900000ms with the first run at 2021-06-24T18:39:02.429+0000
[source-000] - WatchdogJob - Starting Watchdog on periodic schedule: every 3600000ms with the first run at 2021-06-24T18:39:02.429+0000
[source-000] - StageManagementJob - Starting job 'Stage Management' with cron expression: '0 0 * * * *'
[source-000] - JobManager - Job Refresh Cache not configured for auto start
[source-000] - JobManager - Job File Sync Tracker not configured for auto start
[source-000] - JobManager - Job File Sync Pull not configured for auto start
[source-000] - JobManager - Job File Sync Push not configured for auto start
[source-000] - InitialLoadExtractorJob - Starting Initial Load Extract on periodic schedule: every 10000ms with the first run at 2021-06-24T18:39:02.432+0000
[source-000] - MonitorJob - Starting Monitor on periodic schedule: every 60000ms with the first run at 2021-06-24T18:39:02.432+0000
[source-000] - JobManager - Job Report Status not configured for auto start
[source-000] - JobManager - Job Log Miner not configured for auto start
[source-000] - InitialLoadJob - Starting Initial Load Queue on periodic schedule: every 10000ms with the first run at 2021-06-24T18:39:02.433+0000
[source-000] - AbstractSymmetricEngine - SymmetricDS Node STARTED:
nodeId=000
groupId=source
type=server
subType=null
name=source-000
softwareVersion=3.12.10
databaseName=Oracle
databaseVersion=11.2
driverName=Oracle JDBC driver
driverVersion=18.3.0.0.0
uptime=0 sec.
[source-000] - DataGapFastDetector - Full gap analysis is running
[source-000] - NodeCommunicationService - pull will use 10 threads
[source-000] - DataGapFastDetector - Querying data in gaps from database took 10 ms
[source-000] - DataGapFastDetector - Full gap analysis is done after 11 ms
[source-000] - NodeCommunicationService - push will use 10 threads
[source-000] - RouterService - Routed 1 data events in 125 ms
[source-000] - PushService - Push data sent to dest:001:001
[source-000] - PushService - Pushed data to node dest:001:001. 1 data and 1 batches were processed. (sym_node_host)
[oracle@symdssrc symmetric-server-3.12.10]$
Target server Symmetric services traces,
2021-06-24 17:46:09,960 INFO [startup] [SymmetricUtils] [main]
_____ __ _ ____ _____
/ ___/ __ _____ __ ___ __ ___ _/ /_ ____(_)___ / __ | / ___/
\__ \ / / / / _ `_ \/ _ `_ \/ _ \/_ __// __/ / __/ / / / / \__ \
___/ // /_/ / // // / // // / __// / / / / / /_ / /_/ / ___/ /
/____/ \__ /_//_//_/_//_//_/\___/ \_/ /_/ /_/\__/ /_____/ /____/
/____/
+-----------------------------------------------------------------+
| Copyright (C) 2007-2021 JumpMind, Inc. |
| |
| Licensed under the GNU General Public License version 3. |
| This software comes with ABSOLUTELY NO WARRANTY. |
| See http://www.gnu.org/licenses/gpl.html |
+-----------------------------------------------------------------+
2021-06-24 17:46:10,077 INFO [startup] [SymmetricWebServer] [main] About to start SymmetricDS web server on 0.0.0.0:31415:HTTP/1.1
2021-06-24 17:46:10,516 INFO [startup] [SymmetricEngineHolder] [main] Current directory is /home/oracle/symds/symmetric-server-3.12.10
2021-06-24 17:46:10,516 INFO [startup] [SymmetricEngineHolder] [main] Starting in single-server mode
2021-06-24 17:46:10,770 INFO [dest-001] [AbstractSymmetricEngine] [symmetric-engine-startup-1] Initializing connection to database
2021-06-24 17:46:12,521 INFO [dest-001] [JdbcDatabasePlatformFactory] [symmetric-engine-startup-1] Detected database 'Oracle', version '11', protocol 'oracle'
2021-06-24 17:46:12,547 INFO [dest-001] [JdbcDatabasePlatformFactory] [symmetric-engine-startup-1] The IDatabasePlatform being used is org.jumpmind.db.platform.oracle.OracleDatabasePlatform
2021-06-24 18:26:54,143 INFO [startup] [AbstractCommandLauncher] [main] Option: name=max-idle-time, value={90000}
2021-06-24 18:26:54,151 INFO [startup] [AbstractCommandLauncher] [main] Option: name=no-log-console, value={}
2021-06-24 18:26:54,306 INFO [startup] [SymmetricUtils] [main]
_____ __ _ ____ _____
/ ___/ __ _____ __ ___ __ ___ _/ /_ ____(_)___ / __ | / ___/
\__ \ / / / / _ `_ \/ _ `_ \/ _ \/_ __// __/ / __/ / / / / \__ \
___/ // /_/ / // // / // // / __// / / / / / /_ / /_/ / ___/ /
/____/ \__ /_//_//_/_//_//_/\___/ \_/ /_/ /_/\__/ /_____/ /____/
/____/
+-----------------------------------------------------------------+
| Copyright (C) 2007-2021 JumpMind, Inc. |
| |
| Licensed under the GNU General Public License version 3. |
| This software comes with ABSOLUTELY NO WARRANTY. |
| See http://www.gnu.org/licenses/gpl.html |
+-----------------------------------------------------------------+
2021-06-24 18:26:54,428 INFO [startup] [SymmetricWebServer] [main] About to start SymmetricDS web server on 0.0.0.0:31415:HTTP/1.1
2021-06-24 18:26:54,890 INFO [startup] [SymmetricEngineHolder] [main] Current directory is /home/oracle/symds/symmetric-server-3.12.10
2021-06-24 18:26:54,890 INFO [startup] [SymmetricEngineHolder] [main] Starting in single-server mode
2021-06-24 18:26:55,216 INFO [dest-001] [AbstractSymmetricEngine] [symmetric-engine-startup-1] Initializing connection to database
2021-06-24 18:26:57,424 INFO [dest-001] [JdbcDatabasePlatformFactory] [symmetric-engine-startup-1] Detected database 'Oracle', version '11', protocol 'oracle'
2021-06-24 18:26:57,468 INFO [dest-001] [JdbcDatabasePlatformFactory] [symmetric-engine-startup-1] The IDatabasePlatform being used is org.jumpmind.db.platform.oracle.OracleDatabasePlatform
2021-06-24 18:26:57,474 INFO [startup] [SymmetricWebServer] [main] Joining the web server main thread
2021-06-24 18:26:57,777 INFO [dest-001] [OracleSymmetricDialect] [symmetric-engine-startup-1] The DbDialect being used is org.jumpmind.symmetric.db.oracle.OracleSymmetricDialect
2021-06-24 18:26:57,901 INFO [dest-001] [ExtensionService] [symmetric-engine-startup-1] Found 0 extension points from the database that will be registered
2021-06-24 18:26:57,916 INFO [dest-001] [StagingManager] [symmetric-engine-startup-1] The staging directory was initialized at the following location: tmp/dest-001
2021-06-24 18:26:58,688 INFO [dest-001] [ExtensionService] [symmetric-engine-startup-1] Found 0 extension points from the database that will be registered
2021-06-24 18:26:58,689 INFO [dest-001] [ClientExtensionService] [symmetric-engine-startup-1] Found 8 extension points from spring that will be registered
2021-06-24 18:26:58,705 INFO [dest-001] [AbstractSymmetricEngine] [symmetric-engine-startup-1] Initializing SymmetricDS database
2021-06-24 18:26:59,328 INFO [dest-001] [OracleSymmetricDialect] [symmetric-engine-startup-1] Checking if SymmetricDS tables need created or altered
2021-06-24 18:27:01,524 INFO [dest-001] [AbstractSymmetricEngine] [symmetric-engine-startup-1] Done initializing SymmetricDS database
2021-06-24 18:27:01,524 INFO [dest-001] [AbstractSymmetricEngine] [symmetric-engine-startup-1] SymmetricDS database version : 3.12.10
2021-06-24 18:27:01,524 INFO [dest-001] [AbstractSymmetricEngine] [symmetric-engine-startup-1] SymmetricDS software version : 3.12.10
2021-06-24 18:27:01,614 INFO [dest-001] [AbstractSymmetricEngine] [symmetric-engine-startup-1] Starting registered node [group=dest, id=001, nodeId=001]
2021-06-24 18:27:01,617 INFO [dest-001] [ClusterService] [symmetric-engine-startup-1] This node picked a server id of symds
2021-06-24 18:27:01,617 INFO [dest-001] [TriggerRouterService] [symmetric-engine-startup-1] Synchronizing triggers
2021-06-24 18:27:01,664 INFO [dest-001] [TriggerRouterService] [symmetric-engine-startup-1] Done synchronizing triggers
2021-06-24 18:27:01,685 INFO [dest-001] [RouterJob] [symmetric-engine-startup-1] Starting Routing on periodic schedule: every 5000ms with the first run at 2021-06-24T18:27:09.051+0000
2021-06-24 18:27:01,689 INFO [dest-001] [PushJob] [symmetric-engine-startup-1] Starting Push on periodic schedule: every 10000ms with the first run at 2021-06-24T18:27:09.055+0000
2021-06-24 18:27:01,690 INFO [dest-001] [PullJob] [symmetric-engine-startup-1] Starting Pull on periodic schedule: every 10000ms with the first run at 2021-06-24T18:27:09.056+0000
2021-06-24 18:27:01,690 INFO [dest-001] [JobManager] [symmetric-engine-startup-1] Job Offline Push not configured for auto start
2021-06-24 18:27:01,690 INFO [dest-001] [JobManager] [symmetric-engine-startup-1] Job Offline Pull not configured for auto start
2021-06-24 18:27:01,694 INFO [dest-001] [OutgoingPurgeJob] [symmetric-engine-startup-1] Starting job 'Purge Outgoing' with cron expression: '0 0 */4 * * *'
2021-06-24 18:27:01,695 INFO [dest-001] [IncomingPurgeJob] [symmetric-engine-startup-1] Starting job 'Purge Incoming' with cron expression: '0 0 */4 * * *'
2021-06-24 18:27:01,696 INFO [dest-001] [StatisticFlushJob] [symmetric-engine-startup-1] Starting job 'Stat Flush' with cron expression: '0 0/5 * * * *'
2021-06-24 18:27:01,697 INFO [dest-001] [SyncTriggersJob] [symmetric-engine-startup-1] Starting job 'SyncTriggers' with cron expression: '0 0 0 * * *'
2021-06-24 18:27:01,698 INFO [dest-001] [HeartbeatJob] [symmetric-engine-startup-1] Starting Heartbeat on periodic schedule: every 900000ms with the first run at 2021-06-24T18:27:09.064+0000
2021-06-24 18:27:01,698 INFO [dest-001] [WatchdogJob] [symmetric-engine-startup-1] Starting Watchdog on periodic schedule: every 3600000ms with the first run at 2021-06-24T18:27:09.064+0000
2021-06-24 18:27:01,699 INFO [dest-001] [StageManagementJob] [symmetric-engine-startup-1] Starting job 'Stage Management' with cron expression: '0 0 * * * *'
2021-06-24 18:27:01,699 INFO [dest-001] [JobManager] [symmetric-engine-startup-1] Job Refresh Cache not configured for auto start
2021-06-24 18:27:01,699 INFO [dest-001] [JobManager] [symmetric-engine-startup-1] Job File Sync Tracker not configured for auto start
2021-06-24 18:27:01,700 INFO [dest-001] [JobManager] [symmetric-engine-startup-1] Job File Sync Pull not configured for auto start
2021-06-24 18:27:01,700 INFO [dest-001] [JobManager] [symmetric-engine-startup-1] Job File Sync Push not configured for auto start
2021-06-24 18:27:01,700 INFO [dest-001] [InitialLoadExtractorJob] [symmetric-engine-startup-1] Starting Initial Load Extract on periodic schedule: every 10000ms with the first run at 2021-06-24T18:27:09.066+0000
2021-06-24 18:27:01,700 INFO [dest-001] [MonitorJob] [symmetric-engine-startup-1] Starting Monitor on periodic schedule: every 60000ms with the first run at 2021-06-24T18:27:09.066+0000
2021-06-24 18:27:01,701 INFO [dest-001] [JobManager] [symmetric-engine-startup-1] Job Report Status not configured for auto start
2021-06-24 18:27:01,701 INFO [dest-001] [JobManager] [symmetric-engine-startup-1] Job Log Miner not configured for auto start
2021-06-24 18:27:01,701 INFO [dest-001] [InitialLoadJob] [symmetric-engine-startup-1] Starting Initial Load Queue on periodic schedule: every 10000ms with the first run at 2021-06-24T18:27:09.067+0000
2021-06-24 18:27:01,704 INFO [dest-001] [AbstractSymmetricEngine] [symmetric-engine-startup-1] SymmetricDS Node STARTED:
nodeId=001
groupId=dest
type=server
subType=null
name=dest-001
softwareVersion=3.12.10
databaseName=Oracle
databaseVersion=11.2
driverName=Oracle JDBC driver
driverVersion=18.3.0.0.0
uptime=0 sec.
2021-06-24 18:27:07,281 INFO [dest-001] [ConfigurationChangedDatabaseWriterFilter] [dest-001-dataloader-1] About to refresh the cache of node security because new configuration came through the data loader
2021-06-24 18:27:07,397 INFO [dest-001] [DataLoaderService] [qtp932582590-16] 2 data and 2 batches loaded during push request from source:000:000
2021-06-24 18:27:09,086 INFO [dest-001] [NodeCommunicationService] [dest-001-job-3] pull will use 10 threads
2021-06-24 18:27:09,088 INFO [dest-001] [DataGapFastDetector] [dest-001-job-1] Full gap analysis is running
2021-06-24 18:27:09,099 INFO [dest-001] [DataGapFastDetector] [dest-001-job-1] Querying data in gaps from database took 7 ms
2021-06-24 18:27:09,100 INFO [dest-001] [DataGapFastDetector] [dest-001-job-1] Full gap analysis is done after 8 ms
2021-06-24 18:29:16,964 INFO [dest-001] [DataLoaderService] [qtp932582590-15] 1 data and 1 batches loaded during push request from source:000:000
Any errors related to source and destination engines can be identified from the above log traces.
Testing the SYMMETRICDS replication with table from source to target.
As per our example we are performing the test in SCOTT1 user for the table TEST2.
Step -1 - Review the source and target database schemas and tables.
Source database details -
Target database details –
Step -2 -Insert data into source and check how it replicates at target database.
Logs/Traces from the source & Target shows that 3 data/1 batch is processed.
Step -3 – Verify the source & Target symmetric logs.
Verify the timestamp of the SQLPLUS Commit and the traces from symmetricDS source server logs above where 3 data/1 batch data proccessed of Test2.
Target logs shows the 3 data rows/1 batch loaded to table TEST2 from source-000.
Step -4 – Check the data sync status –
Query the destination/target database for data sync status.
The rows are replicated at target.
Note: DML changes are replicated, DDL changes doesn’t get replicated via SymmetricDS.
References - Blogs -
https://www.symmetricds.org/doc/3.12/html/tutorials.html#_initial_load
https://www.jumpmind.com/downloads/symmetricds/preview/3.11/user-guide.html#_preface
https://oracleprolab.com/oracle-database-data-synchronization-using-symmetricds-oracle-ee-oracle-xe-table-data-replication/
