CloneEngine
A tool for cloning database tables; from Postgres into Oracle
Summary:
- Cast one or multiple tables (from a Postgres source) as comparable Oracle
Tables (or rebuild existing ones). Cloning Operations are run in-memory,
and can be configured to run either asynchronously or synchronously.
Usage:
- This module is purposed to clone data tables from within a Postgres
database environment into an Oracle database environment. The process used
does not write to file, but rather stores entire tables in memory while
offloading the data into Oracle. Size your cloning operations accordingly.
Know the sizes of your tables versus what memory resources are available to
the system running this module versus how many cloning operations are being
called at run-time before proceeding. As these operations are done in-memory,
it may be necessary to run only one cloning operation at a time (i.e.
synchronously rather than asynchronously).
Things to keep in mind:
- As a guide, expect a laptop to handle realistically between 2 to 10
Gb of data. That being the maximum amount of data run per cloning operation. As
clone operations can be configured to run either synchronously or asynchronously,
running a large number of larger sized operations asynchronously could quickly
overwhelm a small system or network.
- While CloneEngine tries to be responsible handling database connection
closures, keep in mind the more cloning operations running asynchronously,
the more database connections that are opened against a database. Each
database has limits with the amount of connections that it can have open at
one time. As CloneEngine offloads data, database connections remain open
per cloning operation until the data offloading process is complete for that
operation. Also keep in mind that connection time can quickly increase as
resources are stretched handling larger operations asynchronously.
Requirements:
- Oracle Instant Client installed and configured on local machine
Add-on Modules:
Data Type Conversions:
- Currently resolves the following Postgres data types to the following Oracle
Data Types
Postgres Type | Oracle Type |
---|
varchar | varchar2 |
numeric | number |
int4 | integer |
timestamp | varchar2 |
bool | varchar2 |
text | varchar2 |
Message/Feedback Emitters:
Message Emitter Types
Type | Behavior | Category |
---|
start | Emits on start only | activity |
connection | Emits when connection is opened or closed | activity |
rowsToProcess | Emits the row count number of the table to be cloned | activity |
process | Emits when data is being processed | activity |
bytesCached | Emits the number of bytes cashed | activity |
countsMatch | Emits true if source and destination row counts match | activity |
finish | Emits on finish only | activity |
operation | Emits a summary of operation only | summary |
ERROR! | Emits when errors occur | activity |
Activity Messages
Emitter Property | Property Description |
---|
activityId | Operation step unique ID |
operation | Operation name |
operationId | Operation unique ID |
msgType | Operation message type (start, connection, process, finish, operation, ERROR!) |
step | Operation step number |
time | Operation ISO formatted timestamp |
description | Operation description |
Quick Start:
A) Oracle Instant Client Download
-
Download the following TWO Oracle Instant Client Packages (here: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html ). Please make sure to download the correct packages for your system architecture (i.e. 64 bit vs 32 bit)
-
Instant Client Package - Basic or Basic Lite: Contains files required to run OCI, OCCI, and JDBC-OCI applications
-
Instant Client Package - SDK: Contains additional header files and an example makefile for developing Oracle applications with Instant Client
B) Oracle Instant Client Installation and Configuration (this example procedure is for Mac OS X 64bit ONLY)
From a terminal window:
- Unzip your Oracle Instant Client files to
~/oracle
unzip instantclient-basic-macos.x64-12.1.0.2.0.zip -d ~/oracle
unzip instantclient-sdk-macos.x64-12.1.0.2.0.zip -d ~/oracle
- Update your .bashrc file by appending and saving the following block of code:
export OCI_HOME=~/oracle/instantclient_12_1
export OCI_LIB_DIR=$OCI_HOME
export OCI_INC_DIR=$OCI_HOME/sdk/include
export OCI_INCLUDE_DIR=$OCI_HOME/sdk/include
export DYLD_LIBRARY_PATH=$OCI_LIB_DIR
- Create the following symbolic links from within your Instant Client directory (e.g. /oracle/instantclient_12_1):
ln -s ~/oracle/instantclient_12_1/libclntsh.dylib.12.1 ~/oracle/instantclient_12_1/libclntsh.dylib
ln -s ~/oracle/instantclient_12_1/libocci.dylib.12.1 ~/oracle/instantclient_12_1/libocci.dylib
- Restart your Terminal application OR type the following
source ~/.bashrc
C) CloneEngine Installation
npm install cloneengine
D) Run CloneEngine Operations
"use strict";
var CloneEngine = require('cloneengine');
const SOURCE_DB = {
dbMake : 'postgres',
database : 'myPostgresDb',
user : 'me',
password : 'myPassWord',
host : 'my.db.com'
};
const DESTINATION_DB = {
dbMake : 'oracle',
database : 'myOracleDb',
user : 'me',
password : 'myPassWord',
host : 'myother.db.com',
port : 12345,
service : 'myother.db.com'
};
const OVERWRITE_FOR_ALL_OPS = 'yes';
const TIMEZONE = 'local';
const DISPLAY_MESSAGES_ON_CONSOLE = 'yes';
const RUN_TYPE = 'synchronous';
const STOP_ON_ERROR = 'yes';
var handleEmitterOutput = function(msg){
if (DISPLAY_MESSAGES_ON_CONSOLE === 'yes') {
if(msg.msgType === 'operation'){console.log(msg);}
else if(msg.msgType === 'ERROR!'){
console.log(
msg.activityId +' '+msg.operationId+' '+msg.step+' '+msg.operation+' \x1b[31m'+msg.msgType+'\x1b[0m'+' '+msg.time+' => '+msg.description);
}else{
console.log(
msg.activityId +' '+msg.operationId+' '+msg.step+' '+msg.operation+' '+msg.msgType+' '+msg.time+' => '+msg.description);
}
}
};
function runCloneEngineOperation (plan) {
let engine = new CloneEngine(SOURCE_DB,DESTINATION_DB,TIMEZONE);
engine.on('start',function(msg){handleEmitterOutput(msg);})
engine.on('connection',function(msg){handleEmitterOutput(msg);})
engine.on('rowsToProcess',function(msg){handleEmitterOutput(msg);})
engine.on('process',function(msg){handleEmitterOutput(msg);})
engine.on('bytesCached',function(msg){handleEmitterOutput(msg);})
engine.on('countsMatch',function(msg){handleEmitterOutput(msg);})
engine.on('finish',function(msg){handleEmitterOutput(msg);})
engine.on('ERROR!',function(msg){handleEmitterOutput(msg);})
engine.on('operation',function(msg){handleEmitterOutput(msg);})
engine.run(plan);
return new Promise(function(resolve, reject){
STOP_ON_ERROR == 'no' ?
engine.on('ERROR!',function(msg){if (msg){resolve(msg);}}) :
engine.on('ERROR!',function(msg){if (msg){reject(msg);}})
engine.on('finish',function(msg){if (msg) {resolve(true);}})
})
};
if (RUN_TYPE === 'synchronous') {
runCloneEngineOperation({
sourceTableName : 'table1',
destinationTableName : 'clone_a',
overwriteDestTblIfExists : OVERWRITE_FOR_ALL_OPS
})
.then(function(){
runCloneEngineOperation({
sourceTableName : 'table2',
destinationTableName : 'clone_b',
overwriteDestTblIfExists : OVERWRITE_FOR_ALL_OPS
})
.then(function(){
runCloneEngineOperation({
sourceTableName : 'table3',
destinationTableName : 'clone_c',
overwriteDestTblIfExists : OVERWRITE_FOR_ALL_OPS
})
.then(function(){
runCloneEngineOperation({
sourceTableName : 'table4',
destinationTableName : 'clone_d',
overwriteDestTblIfExists : OVERWRITE_FOR_ALL_OPS
})
}).catch(function(err){console.log(err);})
}).catch(function(err){console.log(err);})
}).catch(function(err){console.log(err);});
}
if (RUN_TYPE === 'asynchronous') {
runCloneEngineOperation({
sourceTableName : 'table1',
destinationTableName : 'clone_a',
overwriteDestTblIfExists : OVERWRITE_FOR_ALL_OPS
})
runCloneEngineOperation({
sourceTableName : 'table2',
destinationTableName : 'clone_b',
overwriteDestTblIfExists : OVERWRITE_FOR_ALL_OPS
})
runCloneEngineOperation({
sourceTableName : 'table3',
destinationTableName : 'clone_c',
overwriteDestTblIfExists : OVERWRITE_FOR_ALL_OPS
})
runCloneEngineOperation({
sourceTableName : 'table4',
destinationTableName : 'clone_d',
overwriteDestTblIfExists : OVERWRITE_FOR_ALL_OPS
})
}