Oracle to PostgreSQL – Part 2

by | Feb 11, 2016 | Oracle | 4 comments

Andy Kerber (@dbakerber), Senior Consultant

In part one, I covered how Oracle’s recent price increase for its Standard Edition database may drive some users to migrate from Oracle to other database versions such as PostgreSQL. I also described the process of copying an Oracle database from Oracle to PostgreSQL and the tools like Oracle XE, PostgreSQL, and ora2pg can be used to facilitate the migration. In part two of the blog, I will focus specifically on one way replication using SymmetricDS configuration.

SymmetricDS Configuration

Our chosen replication package is SymmetricDS, available here:  http://www.symmetricds.org/download.  SymmetricDS requires fairly extensive configuration to work properly.  This example is a one way replication configuration, in a future post I will show an example of two-way replication.

After downloading the SymmetricDS file, unzip it into the directory that you wish to run it from.  For ease of use, I downloaded the .zip into /home/oracle, and unzipped it there.  At that point, the SymmetricDS files are in the directory structure /home/oracle/symmetric-server-3.7.28.

SymmetricDS uses the concept of engines for the replication, each engine has a .properties file in the engines directory under the top level SymmetricDS directory. For the purposes of this demo, we are calling the source system engine file soes-000.properties, and the destination engine file soed-001.properties.  This follows the conventions from the default demo configuration that SymmetricDS walks you through in their tutorial.  These files have been placed in the directory /home/oracle/symmetric-server-3.7.28/engines.  What follows are step by step instructions for setting up one way replication.

  1. Set up the date source.  For the source database, the properties file should have the following lines (soes-000.properties):
 engine.name=soes-000
 db.driver=oracle.jdbc.driver.OracleDriver
 db.url=jdbc:oracle:thin:@pgoraclone:1521:dbatest
 # The user to login as who can create and update tables
 db.user=soe
 # The password for the user to login as
 db.password=soe
 # Pgoraclone is the name of my database server, yours will be different
 registration.url=http://pgoraclone.localdomain:8080/sync/soes-000
 sync.url=http://pgoraclone.localdomain:8080/sync/soes-000
 group.id=soes
 external.id=000
 job.purge.period.time.ms=7200000
 # 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=1000
 # This is how often the pull job will be run.
 job.pull.period.time.ms=1000
 # Kick off initial load, set to true if tables need to be created during initial load
 initial.load.create.first=true

 

  1. Set up the destination (soed-001.properties):
engine.name=soed-001
 db.driver=org.postgresql.Driver
 # note that the database is required in teh db.url
 db.url=jdbc:postgresql://postgres3.localdomain:5432/repuser
 # The user to login as who can create and update tables
 db.user=repuser
 # The password for the user to login as
 db.password=topsecret
 # The HTTP URL of the root node to contact for registration
 # Registration URL will be the source database
 # Sync URL is on the destination database
 # pgoraclone is my database server, your name will change.
 registration.url=http://pgoraclone.localdomain:8080/sync/soes-000
 sync.url=http://postgres3.localdomain:8080/sync/soed-001
 # Do not change these for running the demo
 group.id=soed
 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=1000
 # This is how often the pull job will be run.
 job.pull.period.time.ms=1000
 auto.config.database=true

 

  1. Create the tables SymmetricDS requires for replication.  SymmetricDS creates a set of tables within your source schema for replication.  They all begin with the name sym_, and are excluded from the replication process automatically.  This command creates these tables (either run this from the engines directory, or make sure it is in your path):
 ../bin/symadmin --engine soes-000 create-sym-tables
[oracle@oel64node engines]$ ../bin/symadmin --engine soes-000 create-sym-tables
 Log output will be written to /home/oracle/symmetric-server-3.7.28/logs/symmetric.log
 [] - AbstractCommandLauncher - Option: name=engine, value={soes-000}
 [soes-000] - JdbcDatabasePlatformFactory - Detected database 'Oracle', version '11', protocol 'oracle'
 [soes-000] - JdbcDatabasePlatformFactory - The IDatabasePlatform being used is org.jumpmind.db.platform.oracle.OracleDatabasePlatform
 [soes-000] - OracleSymmetricDialect - The DbDialect being used is org.jumpmind.symmetric.db.oracle.OracleSymmetricDialect
 [soes-000] - StagingManager - The staging directory was initialized at the following location: /home/oracle/symmetric-server-3.7.28/tmp/soes-000
 [soes-000] - ClusterService - This node picked a server id of oel64node.localdomain
 [soes-000] - ClientExtensionService - Found 8 extension points from spring that will be registered
 [soes-000] - AbstractSymmetricEngine - Initializing SymmetricDS database
 [soes-000] - OracleSymmetricDialect - Checking if SymmetricDS tables need created or altered
 [soes-000] - OracleSymmetricDialect - There are SymmetricDS tables that needed altered
 [soes-000] - OracleSymmetricDialect - DDL applied: CREATE TABLE "SYM_CHANNEL"(
 "CHANNEL_ID" VARCHAR2(128) NOT NULL,
 "PROCESSING_ORDER" NUMBER(22) DEFAULT 1 NOT NULL,
 .
 .
 .

 

[soes-000] - OracleSymmetricDialect - Just installed sym_wkt2geom
 [soes-000] - ConfigurationService - Auto-configuring config channel
 [soes-000] - ConfigurationService - Auto-configuring reload channel
 [soes-000] - ConfigurationService - Auto-configuring heartbeat channel
 [soes-000] - ConfigurationService - Auto-configuring default channel
 [soes-000] - ConfigurationService - Auto-configuring dynamic channel
 [soes-000] - ConfigurationService - Auto-configuring filesync channel
 [soes-000] - ConfigurationService - Auto-configuring filesync_reload channel
 [soes-000] - AbstractSymmetricEngine - Done initializing SymmetricDS database

 

  1. Configure the SymmetricDS tables for one way replication.  Below are the SQL commands for configuring one way replication from the engine defined in soes-000.properties to soed-001.properties:

This section sets up the identification information

 insert into soe.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 ('000','soes','000',1,null,null,null,null,null,current_timestamp,null,0,0,'000');
 insert into soe.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','soed','001',1,null,null,null,null,null,current_timestamp,null,0,0,'000');
 insert into soe.sym_node_identity values ('000');
 insert into soe.sym_node_security (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 soe.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 sym_node_group (node_group_id) values ('soes');
 insert into sym_node_group (node_group_id) values ('soed');
 commit;

 

This section sets up the how to replicate information

insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('soes', 'soed', 'W');
 insert into sym_channel
 (channel_id, processing_order, max_batch_size, enabled, description)
 values('oe_transaction', 1, 100000, 1, 'soe transaction');
 insert into sym_trigger (trigger_id, source_table_name, channel_id, last_update_time, create_time)
 values ('soes_outbound', '*','oe_transaction',sysdate,sysdate);
 insert into sym_router
 (router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
 values('soes_2_soed', 'soes', 'soed', 'default',current_timestamp, current_timestamp);
 insert into sym_trigger_router
 (trigger_id,router_id,initial_load_order,last_update_time,create_time)
 values('soes_outbound','soes_2_soed', 100, current_timestamp, current_timestamp);
Note that setting the initial load order to number less than 0 will  disable the initial load.
 commit;

 

The command below is used to load data in large batch mode. (If the database is of any significant size, you will want to use this.)

update sym_channel set max_batch_size=100000, data_loader_type='postgres_bulk' where channel_id='reload';
 commit;

 

Please note that this is the simplest replication case, no transformations are being done, and replication is only moving from the Oracle database defined in soes-000 to the PostgreSQL database defined in soed-001.

  1. Open registration for the destination node on the source server (this is required to get the replication to begin properly):
[oracle@oel64node engines]$ ../bin/symadmin --engine soes-000 open-registration soed 001
 Log output will be written to /home/oracle/symmetric-server-3.7.28/logs/symmetric.log
 [] - AbstractCommandLauncher - Option: name=engine, value={soes-000}
 [soes-000] - JdbcDatabasePlatformFactory - Detected database 'Oracle', version '11', protocol 'oracle'
 [soes-000] - JdbcDatabasePlatformFactory - The IDatabasePlatform being used is org.jumpmind.db.platform.oracle.OracleDatabasePlatform
 [soes-000] - OracleSymmetricDialect - The DbDialect being used is org.jumpmind.symmetric.db.oracle.OracleSymmetricDialect
 [soes-000] - ExtensionService - Found 0 extension points from the database that will be registered
 [soes-000] - StagingManager - The staging directory was initialized at the following location: /home/oracle/symmetric-server-3.7.28/tmp/soes-000
 [soes-000] - ClusterService - This node picked a server id of oel64node.localdomain
 [soes-000] - ExtensionService - Found 0 extension points from the database that will be registered
 [soes-000] - ClientExtensionService - Found 8 extension points from spring that will be registered
 [soes-000] - RegistrationService - Registration was already enabled for 001.  No need to reenable it
 Opened registration for node group of 'soed' external ID of '001'

 

  1. Start the replication engine, using port 8080: ../bin/sym –port 8080.

SymmetricDS will default to using a high port number (32141).  However, our experimentation has found that it generally will not work with the high port.  Set the port to 8080, or another unused low port number, on the SymmetricDS command line.

You will note that SymmetricDS does not run in a detached mode by default.  You will want to manually set it up to run in a separate session.  You can do this by using a nohup command similar to this:

nohup ../bin/sym --port 8080 > /tmp/sym.log 2>&1 &

 

You can also set up SymmetricDS to run as a *nix daemon, and instructions can be found at:  http://www.symmetricds.org/doc/3.7/html/user-guide.html#_running_as_a_linux_unix_daemon.

At this point, the initial load will be running, and changes will be replicated over to the destination.

4 Comments

  1. in step 4 what is the procedure to execute the sql. ?

    Reply
    • The command to log into the postgres sql command line is:

      psql -d mydb -U myuser

      You will then be prompted for a password.

      From there you can run sql statements.

      Reply
  2. in step 4 while inserting configuration into table im getting constraint error .how may i resolve this issue plus is symmetric is required to be installed on destination node as well

    Reply
    • If you are getting a constraint error, it means that the data was loaded to the tables when the replication process was initialized, so you can ignore the constraint error and continue. This is new behavior on the latest versions of SymmetricDS I believe.

      Next, no SymmetricDS does not need to be installed on the destination node. It only needs to be on one node.

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *