Andy Kerber (@dbakerber), Senior Consultant
SymmetricDS (SDS) is open source software that can be used for Oracle SQL replication from one database to another. It can be configured either unidirectional or bidirectional. Bidirectional invariably means that the user will need to be prepared to deal with complex conflict resolution issues.
In our example, we are setting up a simple one-way replication from an Oracle in house database to an AWS Relational Database Services (RDS) database. For this example, the RDS instance is an Oracle Standard Edition database running at the free tier level.
The Oracle supplied option for replication, Oracle Golden Gate, is expensive, whereas SymmetricDS is free, open source software. The intent of this blog is to show the Oracle user on a budget (is that an oxymoron?) an inexpensive method for replicating data to the cloud.
Let’s start with an overview of SymmetricDS concepts. SymmetricDS uses replication groups, so that replication runs from one group to another, or within a single group. Each database instance is a ‘node’ in SymmetricDS parlance, and each node is part of a group. When replication is defined, it is always defined as between groups rather than between database instances.
Next, we’ll review a summary of the environment (server names and IP address are changed). The source (on-prem) database server is named oelafd1. The bastion server in AWS is named awsbast. This server allows external connections from the Internet, and also has connection to the Oracle database in RDS inside the Virtual Private Cloud (VPC). The name for the RDS server we are using is awsrds and the SID we are using is symdb.
Installing Required Software
We need to install SymmetricDS, which requires Java, so you will need to install Java on the bastion server. It is not installed by default in Amazon’s version of Linux. You may also want to install XWindows. In order to install Java, from the ec2-user sign on, type these commands:
sudo su – yum install java yum install xterm (optional)
Next, download SymmetricDS at: https://www.symmetricds.org/download.
After downloading the file to your Linux server, just unzip the file, and it is installed.
Configuring the Engine
First, we set the parameters for the SymmetricDS server itself. Below is the symmetric-server.properties file. The only non-default value is the auto.registration setting:
symmetric-server.properties # Specify the hostname/IP address to bind to. (Default 0.0.0.0 will bind to all interfaces.) host.bind.name=0.0.0.0 # Enable synchronization over HTTP. http.enable=true # Port number for synchronization over HTTP. http.port=31415 # Enable synchronization over HTTPS (HTTP over SSL). https.enable=false # Port number for synchronization over HTTPS (HTTP over SSL). https.port=31417 # Use a trust manager that allows self-signed server SSL certificates. https.allow.self.signed.certs=true # List host names that are allowed for server SSL certificates. https.verified.server.names=all auto.registration=true # Enable Java Management Extensions (JMX) web console. jmx.http.enable=true # Port number for Java Management Extensions (JMX) web console. jmx.http.port=31416
The next step in configuring SymmetricDS is to define your engines. Each database instance involved in replication has an engine defined for it. In this case, the source database engine (on-prem) is source-000 and the destination database (in AWS) is dest-001.
source-000.properties engine.name=source-000 # The class name for the JDBC Driver db.driver=oracle.jdbc.driver.OracleDriver # The JDBC URL used to connect to the database db.url=jdbc:oracle:thin:@ipaddressforinhouse:1521:symdb # The user to login as who can create and update tables db.user=biway2 # The password for the user to login as db.password=tiger # The HTTP URL of the root node to contact for registration sync.url=http://awsbastpublicname:31415/sync/source-000 registration.url=http://awsbastpublicname:31415/sync/source-000 group.id=source external.id=000 # This is how often the routing job will be run in milliseconds job.routing.period.time.ms=5000 # This is how often the push job will be run. job.push.period.time.ms=10000 # This is how often the pull job will be run. job.pull.period.time.ms=1000 dest-001.properties engine.name=dest-001 # The class name for the JDBC Driver db.driver=oracle.jdbc.driver.OracleDriver # The JDBC URL used to connect to the database db.url=jdbc:oracle:thin:@nameforrdsinvpc:1521:symdb # The user to login as who can create and update tables db.user=biway2 # The password for the user to login as db.password=tiger # The HTTP URL of the root node to contact for registration registration.url=http://awsbastprivateip:31415/sync/source-000 sync.url=http://awsbastprivateip:31415/sync/dest-001 group.id=dest external.id=001 # This is how often the routing job will be run in milliseconds job.routing.period.time.ms=5000 # This is how often the push job will be run. job.push.period.time.ms=10000 # This is how often the pull job will be run. job.pull.period.time.ms=10000
Configuring Security Groups
Next, we need to configure the AWS security groups for communication. From looking at the engines, we can see that we need to open ports 1521, 31415, 31416, and 31417 across all the nodes involved in this replication. The instructions for opening ports in AWS are available here.
it will vary by site whether or not it is necessary to open a port on the in house database. In this case, I initially tried setting up port forwarding using my ISP provider router, but it would not allow that to be implemented, so I set up an SSH tunnel for forwarding. Learn more about configuring an SSH tunnel in this article.
At this point, your server and database environment should be set up properly.
Configuring the Software (Oracle)
Node/Group Identification Concepts
The command to create the driver tables is simple. Normally you create the tables in the source node. The command to create the replication driver tables is:
./bin/symadmin --engine source-000 create-sym-tables
This command creates a series of tables prefixed with sym_ in the schema specified in the engine driver file. These tables both identify the nodes involved in the replication, and define the replication rules. Most of the driver information for the node defined in the specified engine are loaded by the command above, but additional data must be loaded for the second node.
- The individual nodes are defined in the sym_node table, which is just a list of the individual nodes involved in the replication process.
- The sym_node_identity table contains the name of the current node.
- The sym_node_security table assigns the created connection information among the nodes.
- The sym_node_group table contains the names of the groups involved in replication.
- The sym_node_group_link table defines how data is replicated between groups. If the data_event_action column is set to ‘P’, data is pushed when events occur. If it is set to ‘W’ the group waits for a pull event. Note that for this example, we have two groups (source and destination) and two nodes.
Driver Table Concepts
At the lowest level, the sym_channel table allocates a channel for replication. For large loads, multiple channels can be created.
Next, the sym_trigger table defines what tables are replicated, what events trigger specific actions, and what channel the event is sent to.
The sym_router table defines routers that route events between groups. A router can have the same group as the source and destination, which would imply that bidirectional replication is configured.
The sym_trigger_router table identifies what events are sent to which routers.
So, the basic concept is that you define a channel, define trigger event and assign it to a channel, define a router, and assign a trigger to a router.
Below is the complete set of commands for loading the tables, including those commands that have already been run using the create-sym-tables command. They are noted and provided for reference. Only the single insert command (as noted) is run using the create-sym-tables command, and includes the identity information for the database instance where the tables were initially created. The identity information for the second node does not get loaded by the create-sym-tables command however. Instead, the information for the second node is replicated across once the replication engine is started.
insert into biway2.sym_node – Run by create command
(node_id,node_group_id,external_id,sync_enabled,sync_url,schema_version,
symmetric_version,
database_type,database_version,heartbeat_time,timezone_offset,batch_to_send_count,
batch_in_error_count,created_at_node_id) values
('000','source','000',1,null,null,null,null,null,current_timestamp,null,0,0,'000');
insert into biway2.sym_node
(node_id,node_group_id,external_id,sync_enabled,sync_url,schema_version,symmetric_version,
database_type,database_version,heartbeat_time,timezone_offset,batch_to_send_count,
batch_in_error_count,created_at_node_id) values
('001','dest','001',1,null,null,null,null,null,current_timestamp,null,0,0,'000');
insert into biway2.sym_node_identity values ('000'); – Run by create command
insert into biway2.sym_node_security – Run by create command
(node_id,node_password,registration_enabled,registration_time,initial_load_enabled,initial_load_time,
created_at_node_id) values
('000','5d1c92bbacbe2edb9e1ca5dbb0e481',0,current_timestamp,0,current_timestamp,'000');
insert into biway2.sym_node_security
(node_id,node_password,registration_enabled,registration_time,initial_load_enabled,initial_load_time,
created_at_node_id) values
('001','5d1c92bbacbe2edb9e1ca5dbb0e481',1,null,1,current_timestamp,'000');
-- note: the insert above sets initial_load_enabled to 1 to enable initial load
insert into biway2.sym_node_group (node_group_id) values – Run by create command
('source');
insert into biway2.sym_node_group (node_group_id)
values ('dest');
commit;
At this point, the identities of your nodes are defined, and it’s time to set up replication. In our schema, biway2, we have three tables defined, each with three columns – id, name, value. The names of the tables are test1, test2, and, test3:
SQL> desc test1 Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER NAME VARCHAR2(30) VALUE VARCHAR2(60)
The primary key defined for each is the ID.
Below are the commands to configure the replication. The first table, sym_node_group_link, defines the direction of replication. For bidirectional replication, there will be two entries, but in this example there is only one:
insert into sym_node_group_link (source_node_group_id, target_node_group_id,
data_event_action) values ('source', 'dest', 'P');
The sym_channel contains the definition of the channels used for replication. It appears that SDS will create one or more processes for each channel.
insert into sym_channel (channel_id, processing_order, max_batch_size,
enabled, description) values('source_outpound_ch', 1, 100000, 1, 'outbound channel');
The sym_trigger table contains the triggering events and the channel name used to transmit activity from these triggers.
insert into sym_trigger (trigger_id, source_table_name, channel_id,
last_update_time, create_time,sync_on_incoming_batch) values
('source_outbound_tr_t1', 'TEST1','source_outpound_ch',sysdate,sysdate,1);
insert into sym_trigger (trigger_id, source_table_name, channel_id,
last_update_time, create_time,sync_on_incoming_batch) values
('source_outbound_tr_t2', 'TEST2','source_outpound_ch',sysdate,sysdate,1);
insert into sym_trigger (trigger_id, source_table_name, channel_id,
last_update_time, create_time,sync_on_incoming_batch) values
('source_outbound_tr_t3', 'TEST3','source_outpound_ch',sysdate,sysdate,1);
The sym_router table has the rules for routing change data.
insert into sym_router (router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time) values('source_2_dest',
'source', 'dest', 'default',current_timestamp, current_timestamp);
The sym_trigger_router table assigns trigger actions to routers.
insert into
sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('source_outbound_tr_t1','source_2_dest',
1, current_timestamp, current_timestamp);
insert into
sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('source_outbound_tr_t2','source_2_dest',
1, current_timestamp, current_timestamp);
insert into
sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('source_outbound_tr_t3','source_2_dest',
1, current_timestamp, current_timestamp);
commit;
Note that in our example, all three tables use the same channel and router, but each have their own trigger. However, it would be possible to define a router and/or channel for each table. It is also possible to define a single trigger for all tables, in which case the table name in the sym_trigger table would be ‘*’.
At this point, you are ready to start the replication. The command to start the replication is ./sym. You can also configure it in Linux to start as a service.
After starting SDS, it can take some time for it to get going smoothly. Watch the messages scroll by for at least 10 minutes before becoming concerned about problems. At startup for the first time, you will see a series of messages about copying tables, creating triggers, etc.
Note that when we created the sym_node_security table, we set initial_load_enabled to 1. This means that SDS will start by copying the data from all tables that exist on both the source and destination instance to the destination node. In the case of larger databases, you would normally disable this (set initial_load_enabled to 0) and start with the data loaded via Oracle Data Pump or a similar option. Even in this small database, it can take several minutes to get all the data copied after a startup.
The initial startup will have output similar to this, note the creation of tables and triggers:
2019-01-29 23:22:47,151 INFO [source-000] [OracleSymmetricDialect] [main] There are SymmetricDS tables that needed altered
2019-01-29 23:22:47,271 INFO [source-000] [OracleSymmetricDialect] [main] 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
)
2019-01-29 23:22:47,479 INFO [source-000] [OracleSymmetricDialect] [main] DDL applied: ALTER TABLE "SYM_CHANNEL"
ADD CONSTRAINT "SYM_CHANNEL_PK" PRIMARY KEY ("CHANNEL_ID")
2019-01-29 23:22:47,559 INFO [source-000] [OracleSymmetricDialect] [main] 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
)
2019-01-29 23:22:47,619 INFO [source-000] [OracleSymmetricDialect] [main] DDL applied: ALTER TABLE "SYM_CONFLICT"
ADD CONSTRAINT "SYM_CONFLICT_PK" PRIMARY KEY ("CONFLICT_ID")
2019-01-29 23:22:47,684 INFO [source-000] [OracleSymmetricDialect] [main] DDL applied: CREATE TABLE "SYM_CONTEXT"(
"NAME" VARCHAR2(80) NOT NULL,
"CONTEXT_VALUE" CLOB,
"CREATE_TIME" TIMESTAMP,
"LAST_UPDATE_TIME" TIMESTAMP
….
2019-01-29 23:22:57,165 INFO [source-000] [OracleSymmetricDialect] [main] 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;
2019-01-29 23:22:57,311 INFO [source-000] [OracleSymmetricDialect] [main] Just installed sym_blob2clob
2019-01-29 23:22:57,429 INFO [source-000] [OracleSymmetricDialect] [main] Installing SymmetricDS database object:
CREATE OR REPLACE function sym_transaction_id return varchar is begin return DBMS_TRANSACTION.local_transaction_id(false); end;
2019-01-29 23:22:57,490 INFO [source-000] [OracleSymmetricDialect] [main] Just installed sym_transaction_id
…..
C trigger for BIWAY2.SYM_NODE_GROUP
2019-01-30 14:22:53,632 INFO [source-000] [OracleSymmetricDialect] [source-000-sync-triggers-2] Creating SYM_ON_U_FOR_SYM_ND_GRP_SRC trigger for BIWAY2.SYM_NODE_GROUP
2019-01-30 14:22:53,781 INFO [source-000] [OracleSymmetricDialect] [source-000-sync-triggers-2] Creating SYM_ON_D_FOR_SYM_ND_GRP_SRC trigger for BIWAY2.SYM_NODE_GROUP
2019-01-30 14:22:55,432 INFO [source-000] [OracleSymmetricDialect] [source-000-sync-triggers-1] Creating SYM_ON_I_FOR_SYM_ND_GRP_LNK trigger for BIWAY2.SYM_NODE_GROUP_LINK
2019-01-30 14:22:55,583 INFO [source-000] [OracleSymmetricDialect] [source-000-sync-triggers-1] Creating SYM_ON_U_FOR_SYM_ND_GRP_LNK trigger for BIWAY2.SYM_NODE_GROUP_LINK
2019-01-30 14:22:55,737 INFO [source-000] [OracleSymmetricDialect] [source-000-sync-triggers-1] Creating SYM_ON_D_FOR_SYM_ND_GRP_LNK trigger for BIWAY2.SYM_NODE_GROUP_LINK
2019-01-30 14:22:59,138 INFO [source-000] [OracleSymmetricDialect] [source-000-sync-triggers-2] Creating SYM_ON_I_FOR_SYM_LD_FLTR_SRC trigger for BIWAY2.SYM_LOAD_FILTER
2019-01-30 14:22:59,310 INFO [source-000] [OracleSymmetricDialect] [source-000-sync-triggers-2] Creating SYM_ON_U_FOR_SYM_LD_FLTR_SRC trigger for BIWAY2.SYM_LOAD_FILTER
2019-01-30 14:22:59,464 INFO [source-000] [OracleSymmetricDialect] [source-000-sync-triggers-2] Creating SYM_ON_D_FOR_SYM_LD_FLTR_SRC trigger for BIWAY2.SYM_LOAD_FILTER
2019-01-30 14:23:01,563 INFO [source-000] [OracleSymmetricDialect] [source-000-sync-triggers-3] Creating SYM_ON_I_FOR_SYM_ND_SCRTY_SRC trigger for BIWAY2.SYM_NODE_SECURITY
2019-01-30 14:23:01,718 INFO [source-000] [OracleSymmetricDialect] [source-000-sync-triggers-3] Creating SYM_ON_U_FOR_SYM_ND_SCRTY_SRC trigger for BIWAY2.SYM_NODE_SECURITY
2019-01-30 14:23:01,877 INFO [source-000] [OracleSymmetricDialect] [source-000-sync-triggers-3] Creating SYM_ON_D_FOR_SYM_ND_SCRTY_SRC trigger for BIWAY2.SYM_NODE_SECURITY
2019-01-30 14:23:03,515 INFO [source-000] [OracleSymmetricDialect] [source-000-sync-triggers-2] Creating SYM_ON_I_FOR_SYM_XTNSN_SRC trigger for BIWAY2.SYM_EXTENSION
…..
2019-01-30 15:17:28,693 INFO [source-000] [ConfigurationChangedDataRouter] [source-000-job-1] About to refresh the cache of nodes because new configuration came through the data router
2019-01-30 15:17:28,743 INFO [source-000] [RouterService] [source-000-job-1] The 'heartbeat' channel is NOT in common batch mode
2019-01-30 15:17:32,293 INFO [source-000] [PushService] [source-000-push-default-2] Push data sent to dest:001:001
2019-01-30 15:17:32,308 WARN [source-000] [PushService] [source-000-push-default-2] Registration was not open at dest:001:001
2019-01-30 15:17:36,019 INFO [source-000] [RouterService] [source-000-job-1] Routed 4 data events in 14080 ms
2019-01-30 15:17:41,506 INFO [source-000] [DataGapFastDetector] [source-000-job-20] Full gap analysis is running
2019-01-30 15:17:41,530 INFO [source-000] [PushService] [source-000-push-default-3] Push data sent to dest:001:001
2019-01-30 15:17:41,535 WARN [source-000] [PushService] [source-000-push-default-3] Registration was not open at dest:001:001
2019-01-30 15:17:41,954 INFO [source-000] [DataGapFastDetector] [source-000-job-20] Querying data in gaps from database took 448 ms
2019-01-30 15:17:41,954 INFO [source-000] [DataGapFastDetector] [source-000-job-20] Full gap analysis is done after 448 ms
2019-01-30 15:17:47,383 INFO [source-000] [DataGapFastDetector] [source-000-job-8] Full gap analysis is running
2019-01-30 15:17:47,564 INFO [source-000] [DataGapFastDetector] [source-000-job-8] Querying data in gaps from database took 181 ms
2019-01-30 15:17:47,565 INFO [source-000] [DataGapFastDetector] [source-000-job-8] Full gap analysis is done after 182 ms
2019-01-30 15:17:51,509 INFO [source-000] [PushService] [source-000-push-default-4] Push data sent to dest:001:001
2019-01-30 15:17:51,510 WARN [source-000] [PushService] [source-000-push-default-4] Registration was not open at dest:001:001
2019-01-30 15:17:53,001 INFO [source-000] [DataGapFastDetector] [source-000-job-15] Full gap analysis is running
2019-01-30 15:17:53,705 INFO [source-000] [DataGapFastDetector] [source-000-job-15] Querying data in gaps from database took 703 ms
2019-01-30 15:17:53,705 INFO [source-000] [DataGapFastDetector] [source-000-job-15] Full gap analysis is done after 703 ms
2019-01-30 15:17:59,203 INFO [source-000] [DataGapFastDetector] [source-000-job-16] Full gap analysis is running
2019-01-30 15:17:59,397 INFO [source-000] [DataGapFastDetector] [source-000-job-16] Querying data in gaps from database took 194 ms2019-01-30 15:17:59,397 INFO [source-000] [DataGapFastDetector] [source-000-job-16] Full gap analysis is done after 194 ms
2019-01-30 15:18:01,492 INFO [source-000] [PushService] [source-000-push-default-5] Push data sent to dest:001:001
2019-01-30 15:18:01,494 WARN [source-000] [PushService] [source-000-push-default-5] Registration was not open at dest:001:001
2019-01-30 15:18:04,945 INFO [source-000] [DataGapFastDetector] [source-000-job-6] Full gap analysis is running
2019-01-30 15:18:05,131 INFO [source-000] [DataGapFastDetector] [source-000-job-6] Querying data in gaps from database took 186 ms
2019-01-30 15:18:05,131 INFO [source-000] [DataGapFastDetector] [source-000-job-6] Full gap analysis is done after 186 ms
2019-01-30 15:18:10,622 INFO [source-000] [DataGapFastDetector] [source-000-job-12] Full gap analysis is running
2019-01-30 15:18:10,813 INFO [source-000] [DataGapFastDetector] [source-000-job-12] Querying data in gaps from database took 190 ms
2019-01-30 15:18:10,814 INFO [source-000] [DataGapFastDetector] [source-000-job-12] Full gap analysis is done after 191 ms
2019-01-30 15:18:11,507 INFO [source-000] [PushService] [source-000-push-default-6] Push data sent to dest:001:001
Below is output from a normal startup (after initialization startup):
[oracle@ip-172-31-32-171 symmetric-server-3.8.41]$ ./bin/sym Log output will be written to /home/oracle/symmetric-server-3.8.41/logs/symmetric.log [startup] - SymmetricWebServer - About to start SymmetricDS web server on host:port 0.0.0.0:3145 [startup] - / - Initializing Spring root WebApplicationContext [source-000] - AbstractSymmetricEngine - Initializing connection to database [dest-001] - AbstractSymmetricEngine - Initializing connection to database [dest-001] - JdbcDatabasePlatformFactory - Detected database 'Oracle', version '12', protocol 'oracle' [dest-001] - JdbcDatabasePlatformFactory - The IDatabasePlatform being used is org.jumpmind.db.platform.oracle.OracleDatabasePlatform [dest-001] - OracleSymmetricDialect - The DbDialect being used is org.jumpmind.symmetric.db.oracle.OracleSymmetricDialect [dest-001] - ExtensionService - Found 0 extension points from the database that will be registered [dest-001] - StagingManager - The staging directory was initialized at the following location: /home/oracle/symmetric-server-3.8.41/tmp/dest-001 [dest-001] - ClusterService - This node picked a server id of ip-172-31-32-171.us-east-2.compute.internal [startup] - / - Initializing Spring FrameworkServlet 'rest' [dest-001] - ExtensionService - Found 0 extension points from the database that will be registered [dest-001] - ClientExtensionService - Found 8 extension points from spring that will be registered [dest-001] - AbstractSymmetricEngine - Initializing SymmetricDS database [dest-001] - OracleSymmetricDialect - Checking if SymmetricDS tables need created or altered [startup] - SymmetricWebServer - Starting JMX HTTP console on port 31416 [startup] - SymmetricWebServer - Joining the web server main thread HttpAdaptor version 3.0.1 started on port 31416 [source-000] - JdbcDatabasePlatformFactory - Detected database 'Oracle', version '18', 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/symmetric-server-3.8.41/tmp/source-000 [source-000] - ClusterService - This node picked a server id of ip-172-31-32-171.us-east-2.compute.internal [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 [dest-001] - AbstractSymmetricEngine - Done initializing SymmetricDS database [dest-001] - AbstractSymmetricEngine - SymmetricDS database version : 3.8.41 [dest-001] - AbstractSymmetricEngine - SymmetricDS software version : 3.8.41 [dest-001] - AbstractSymmetricEngine - Starting registered node [group=dest, id=001, externalId=001] [dest-001] - TriggerRouterService - Synchronizing triggers [dest-001] - TriggerRouterService - Done synchronizing triggers [dest-001] - RouterJob - Starting job.routing on periodic schedule: every 5000ms with the first run at Thu Jan 31 20:20:56 UTC 2019 [dest-001] - PushJob - Starting job.push on periodic schedule: every 10000ms with the first run at Thu Jan 31 20:20:56 UTC 2019 [dest-001] - PullJob - Starting job.pull on periodic schedule: every 10000ms with the first run at Thu Jan 31 20:20:56 UTC 2019 [dest-001] - JobManager - Job job.offline.push not configured for auto start [dest-001] - JobManager - Job job.offline.pull not configured for auto start [dest-001] - OutgoingPurgeJob - Starting job.purge.outgoing with cron expression: 0 0 0 * * *. Next scheduled time is: Fri Feb 01 00:00:00 UTC 2019 [dest-001] - IncomingPurgeJob - Starting job.purge.incoming with cron expression: 0 0 0 * * *. Next scheduled time is: Fri Feb 01 00:00:00 UTC 2019 [dest-001] - StatisticFlushJob - Starting job.stat.flush with cron expression: 0 0/5 * * * *. Next scheduled time is: Thu Jan 31 20:25:00 UTC 2019 [dest-001] - SyncTriggersJob - Starting job.synctriggers with cron expression: 0 0 0 * * *. Next scheduled time is: Fri Feb 01 00:00:00 UTC 2019 [dest-001] - HeartbeatJob - Starting job.heartbeat on periodic schedule: every 900000ms with the first run at Thu Jan 31 20:20:56 UTC 2019 [dest-001] - WatchdogJob - Starting job.watchdog on periodic schedule: every 3600000ms with the first run at Thu Jan 31 20:20:56 UTC 2019 [dest-001] - StageManagementJob - Starting job.stage.management with cron expression: 0 0 * * * *. Next scheduled time is: Thu Jan 31 21:00:00 UTC 2019 [dest-001] - JobManager - Job job.refresh.cache not configured for auto start [dest-001] - JobManager - Job job.file.sync.tracker not configured for auto start [dest-001] - JobManager - Job job.file.sync.pull not configured for auto start [dest-001] - JobManager - Job job.file.sync.push not configured for auto start [dest-001] - InitialLoadExtractorJob - Starting job.initial.load.extract on periodic schedule: every 10000ms with the first run at Thu Jan 31 20:20:56 UTC 2019 [dest-001] - MonitorJob - Starting job.monitor on periodic schedule: every 60000ms with the first run at Thu Jan 31 20:20:56 UTC 2019 [dest-001] - JobManager - Job job.report.status not configured for auto start [dest-001] - SyncConfigJob - Starting job.sync.config with cron expression: 0 0/10 1 * * *. Next scheduled time is: Fri Feb 01 01:00:00 UTC 2019 [dest-001] - AbstractSymmetricEngine - Started SymmetricDS [dest-001] - AbstractSymmetricEngine - SymmetricDS: type=server, name=dest-001, version=3.8.41, groupId=dest, externalId=001, databaseName=Oracle, databaseVersion=12.1, driverName=Oracle JDBC driver, driverVersion=11.2.0.3.0 [dest-001] - RouterService - Could not queue up a load for 000 because a node group link is NOT configured over which a load could be delivered [dest-001] - NodeCommunicationService - pull will use 10 threads [dest-001] - DataGapFastDetector - Full gap analysis is running [dest-001] - DataGapFastDetector - Querying data in gaps from database took 12 ms [dest-001] - DataGapFastDetector - Full gap analysis is done after 12 ms [dest-001] - RouterService - Could not queue up a load for 000 because a node group link is NOT configured over which a load could be delivered [dest-001] - DataGapFastDetector - Full gap analysis is running [dest-001] - DataGapFastDetector - Querying data in gaps from database took 5 ms [dest-001] - DataGapFastDetector - Full gap analysis is done after 5 ms
At this point, your configuration should up and running with one-way replication working. Run some simple update and insert commands to verify.
On AWS instance:
SQL> select id, name, value from test1; ID NAME VALUE ---------- -------------------- -------------------- 1 NAME VALUE1 2 NAME VALUE2 3 NAME VALUE4 4 NAME VALUE3
On premises:
SQL> select id, name, value from test1; ID NAME VALUE ---------- -------------------- -------------------- 1 NAME VALUE1 2 NAME VALUE2 3 NAME VALUE4 4 NAME VALUE3
SQL> update test1 set value='VALUETEST' where id=1; 1 row updated. SQL> commit; Commit complete. SQL> select id, name, value from test1; ID NAME VALUE ---------- -------------------- -------------------- 1 NAME VALUETEST 2 NAME VALUE2 3 NAME VALUE4 4 NAME VALUE3 SQL>
In AWS:
SQL> column name format a20 SQL> column value format a20 SQL> select id, name, value from test1; SQL> / ID NAME VALUE ---------- -------------------- -------------------- 1 NAME VALUETEST 2 NAME VALUE2 3 NAME VALUE4 4 NAME VALUE3 Replication is working.
In this article, we have discussed how to set up one-way replication from an on-premises Oracle database to an Amazon RDS instance using SymmetricDS. Using SymmetricDS can offer substantial savings over using Oracle technologies such as Data Guard or Golden Gate.
Please note: this blog contains code examples provided for your reference. All sample code is provided for illustrative purposes only. Use of information appearing in this blog is solely at your own risk. Please read our full disclaimer for details.
