[SCRIPT] ORA2MY: Transferring data from oracle to mysql

Previous Requirements

Basic knowledge of:

apache/cgi
Oracle
mysql
bash

Dependencies

OS:

Python
Python-pip

Python modules:

Mysql
Oracle

Installation:

yum -y install MySQL-python python-pip python-devel

pip upgrade:

pip install --upgrade pip

Oracle module:

pip install cx_Oracle

Oracle

cx_Oracle need intantclient to run:

-rw-r--r--   1 root root  51M Jan 16 15:40 oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
-rw-r--r--   1 root root 593K Jan 16 15:40 oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
-rw-r--r--   1 root root 253K Jan 16 15:40 oracle-instantclient12.2-odbc-12.2.0.1.0-2.x86_64.rpm
-rw-r--r--   1 root root 692K Jan 16 15:40 oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
-rw-r--r--   1 root root 922K Jan 16 15:40 oracle-instantclient12.2-tools-12.2.0.1.0-1.x86_64.rpm

Installation example:

AVDLP-MYSQL-101 ~ # rpm -Uvh oracle-instantclient12.2-*
Preparing...                          ################################# [100%]
Updating / installing...
   1:oracle-instantclient12.2-basic-12################################# [ 20%]
   2:oracle-instantclient12.2-devel-12################################# [ 40%]
   3:oracle-instantclient12.2-odbc-12.################################# [ 60%]
   4:oracle-instantclient12.2-sqlplus-################################# [ 80%]
   5:oracle-instantclient12.2-tools-12################################# [100%]

Modify the ld.conf path if you change the instantclient version!!!!

Load libs:

echo "/usr/lib/oracle/12.2/client64/lib" > /etc/ld.so.conf.d/oracle-instant-client.conf
ldconfig

Check:

/usr/bin/sqlplus64

Variables needed:

cat > /etc/profile.d/oracle-instant-client.sh<<EOF
# Set ORACLE_HOME to the directory where the bin and lib directories are located for the oracle client
export ORACLE_HOME=/usr/lib/oracle/12.2/client64
 
# No need to add ORACLE_HOME to the linker search path. oracle-instant-client.conf in
# /etc/ld.so.conf.d should already contain /usr/lib/oracle/11.2/client64.
# Alternately, you can set it here by uncommenting the following line:
# export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
 
# Define the default location where Oracle should look for the server
export TWO_TASK=//adaptive-oracle:1561/listener
 
# Define where to find the tnsnames.ora file
export TNS_ADMIN=/etc/oracle
EOF

You'll have to write down the tnsnames. The best option is to use our centralized tnsnames.ora:

svn://10.52.2.15/oracle/tnsnames/tnsnames.ora

Basic script knowledge

There are 2 scripts:

`ora2my_sync.sh`	shell script which build the database structure
`ora2my_sync.py`	sync script itself

Both scripts need permissions on mysql database to drop/create/insert/delete tables.

Clone from git.ciberterminal.net

https://git.ciberterminal.net/Ciberterminal_Public_Git_Repo/project_takedown

ora2my_sync.sh

The script has 2 ways of run:

sync : just sync a table
recreate : Drop, create and sync table

Run example:

ora2my_sync.sh sync "TABLE1 TABLE2"

This script is multi-threaded, the number of threads can be configured inside the script:

[ ${DEBUG} -eq 0 ] && let MAXTHREADS=1 || let MAXTHREADS=1

In the case of recreate option, the script will:

Drop the table
Create
Create PK
Create additional indexes of the table
Sync

Script configuration

The configuration is done with the config file ora2my_sync.config
You'll find a template in the github repo, so:

cp ora2my_sync.config.tmpl ora2my_sync.config

ora2my_sync.config

########################################################################
#
# VARIABLES
#
########################################################################
 
 
ORACLE_HOME=/usr/lib/oracle/18.5/client64
ORAUSER="USERNAME"
ORAPASSWD="THEPASSWORD"
ORASID="TNSNAME_OF_DATABASE"
ORAHOST="hostname:1521"
SQLPLUS="/usr/bin/sqlplus64 -s ${ORAUSER}/${ORAPASSWD}@${ORASID}"
 
 
 
MYUSER="root"
MYPASSWD="THEPASSWORD"
MYDB="thedatabase"
MYSQL="mysql -u${MYUSER} -p${MYPASSWD}"
 
ORA2MY="python /home/scripts/The_fucking_bofh.Database.MySQL/ora2my_transfer/ora2my_sync.py"
 
TABLELIST="VW_FINDER_RESTAURANTS USER_PREFERRED_UNITS VW_FINDER_PARKINGS VW_FINDER_GAS_STATIONS"
 
declare -a TABLEXTHREAD
 
# SET DEBUG=0 for debugging
DEBUG=1
#DEBUG=0
 
[ ${DEBUG} -eq 0 ] && let MAXTHREADS=1 || let MAXTHREADS=1
 
########################################################################
#
# / VARIABLES
#
########################################################################

All the options are self-descriptive by its name…

Database Types conversion In the CONSTANTS section inside ora2my_sync.config you'll find the conversion table.

ora2my_sync.py

This script will sync data between Oracle and Mysql. The only parameter is the table name. It uses cx_Oracle for Oracle connection and MySQLdb for MySQL connection (plus some additional python modules).

The script will check if there's a sync table to get changes and sync the table.
If the sync table doesn't exists, It will drop and import all the table data in a single transaction.

Script configuration

The config is done in the ora2my_sync.pycfg file:

From the github repo:

cp ora2my_sync.pycfg.tmpl ora2my_sync.pycfg

ora2my_sync.pycfg

[MySQL]
username = root
password = ***
host = localhost
databasename = mydiva_t01
 
[Oracle]
username = DEVOLUIVA
password = ***
host = 10.63.3.150:1521
databasename = DBTEST

Table synchronization

With sync table

In the 1st synchronization, the sync table MUST NOT EXISTS (so drop or rename it).

The sync table

Sync table name should be the same as the “master” table and adding the sufix _MYSYNC.

Sync table metadata is:

CREATE TABLE "<MASTERTABLE>_MYSYNC"
   (    "<PK_COLNAME>_ID" NUMBER NOT NULL ENABLE,
        "ACTION" VARCHAR(1) NOT NULL ENABLE,
        "WHENITWAS" TIMESTAMP DEFAULT SYSTIMESTAMP
   ) 
  TABLESPACE "<USERSCHEMA>"
;

We'll need a sync trigger:

CREATE OR REPLACE EDITIONABLE TRIGGER "<MASTERTABLE>_MYSYNC"
    AFTER INSERT
    OR UPDATE
    OR DELETE
    ON ACTOR_IMAGES
    FOR EACH ROW
BEGIN
  CASE
    WHEN INSERTING OR UPDATING THEN
        INSERT INTO <MASTERTABLE>_MYSYNC
        VALUES (:NEW.<PK_COLNAME>, 'i', SYSTIMESTAMP);
    WHEN DELETING THEN
        INSERT INTO <MASTERTABLE>_MYSYNC
        VALUES (:OLD.<PK_COLNAME>, 'd', SYSTIMESTAMP);
  END CASE;
END;
/
 
ALTER TRIGGER "<MASTERTABLE>_MYSYNC" ENABLE;

Variables to be replaced on the SQL's:

`<MASTERTABLE>`	Name of the `master` table
`<PK_COLNAME>`	Name of the PK column
`<USERSCHEMA>`	Schema in which the table is created

This synchro mode DOES NOT support master table with a PK multiple (multiple columns on the PK).

Initial synchro example

Disable trigger, rename _MYSYNC table:

ALTER SESSION SET CURRENT_SCHEMA= <OWNER> ;
 
ALTER TRIGGER "<MASTERTABLE>_MYSYNC" DISABLE ;
 
ALTER TABLE "<MASTERTABLE>_MYSYNC" RENAME TO "<MASTERTABLE>_NOSYNC" ;

Launch sync
Reenable it all (this must be done meanwhile the ora2my_syn.sh script is running):

ALTER TRIGGER "<MASTERTABLE>_MYSYNC" ENABLE ;
ALTER TABLE "<MASTERTABLE>_NOSYNC" RENAME TO "<MASTERTABLE>_MYSYNC" ;

Without sync table

The script will perform all the actions on a single transaction, so the data will be available 100% of the time.

View Synchronization

A view over multiple tables must be only sync'ed without sync table (or You'll need to create trigger on any table that the view access, which is not recommended).
Everything is the rest is the same than sync'ing a table.

The view must have pk!!

ALTER VIEW VW_FINDER_RESTAURANTS ADD CONSTRAINT VW_FINDER_RESTAURANTS_PK PRIMARY KEY (RESOURCE_ID)  DISABLE;

Launching synchronizations through http

Following this instructions You'll have a http server listening for calls and launch refresh's if the call is correct. It will answer with a JSON with the ressults.

Instructions (CENTOS7)

Install apache 2.4

yum -y install httpd httpd-tools
systemctl enable httpd

Remove unused modules

cd /etc/httpd/conf.modules.d
for i in 00-dav.conf 00-lua.conf 00-proxy.conf ; do mv $i ${i}.disabled ; done

Configuration

cp /etc/httpd/conf/httpd.conf /etc/httpd/conf/httpd.conf.orig
cat >/etc/httpd/conf/httpd.conf<<EOF
ServerRoot "/etc/httpd"
Listen 80
Include conf.modules.d/*.conf
User apache
Group apache
ServerAdmin sysadmin@ciberterminal.net
 
# CGI-BIN LONG TIMEOUT
TimeOut 600
<Directory />
    AllowOverride none
    Require all denied
</Directory>
DocumentRoot "/var/www/html"
<Directory "/var/www">
    AllowOverride None
    Require all granted
</Directory>
<Directory "/var/www/html">
    Options Indexes FollowSymLinks
    AllowOverride None
    Require all granted
</Directory>
<IfModule dir_module>
    DirectoryIndex index.html
</IfModule>
<Files ".ht*">
    Require all denied
</Files>
ErrorLog "logs/error_log"
LogLevel warn
<IfModule log_config_module>
    LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined
    LogFormat "%h %l %u %t \"%r\" %>s %b" common
    <IfModule logio_module>
      LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\" %I %O" combinedio
    </IfModule>
    CustomLog "logs/access_log" combined
</IfModule>
<IfModule alias_module>
    ScriptAlias /cgi-bin/ "/var/www/cgi-bin/"
</IfModule>
<Directory "/var/www/cgi-bin">
    AllowOverride None
    Options None
    Require all granted
</Directory>
<IfModule mime_module>
    TypesConfig /etc/mime.types
    AddType application/x-compress .Z
    AddType application/x-gzip .gz .tgz
    AddType text/html .shtml
    AddOutputFilter INCLUDES .shtml
</IfModule>
AddDefaultCharset UTF-8
<IfModule mime_magic_module>
    MIMEMagicFile conf/magic
</IfModule>
EnableSendfile on
IncludeOptional conf.d/*.conf
 
ScriptLog logs/cgi.log
EOF
 
 
rm -fv /etc/httpd/conf.d/*
cat >/etc/httpd/conf.d/auth.conf<<EOF
<Location "/">
    AuthType basic
    AuthName "private area"
    AuthBasicProvider file
    #AuthBasicUseDigestAlgorithm MD5
    AuthUserFile "/var/www/html/auth/.basic_pw"
    Require valid-user
</Location>
EOF

Authentication

Create folders

mkdir /var/www/html/auth
cd /var/www/html/auth

Add users:

htpasswd -bc .basic_pw dodger <thepassword>

Launch Script:

cp /home/scripts/The_fucking_bofh.Database.MySQL/ora2my_transfer/launch_sync.sh /var/www/cgi-bin
chmod +x /var/www/cgi-bin/launch_sync.sh

Sample url:

http://10.52.2.xx/cgi-bin/test_POST.sh?mode=recreate&tables=VW_FINDER_RESTAURANTS,USER_PREFERRED_UNITS,VW_FINDER_PARKINGS,VW_FINDER_GAS_STATIONS

Sample run:

curl -s -u dodger:<thepassword> --basic "http://10.63.3.170/cgi-bin/launch_sync.sh?mode=sync&tables=USER_PREFERRED_UNITS" | jq .

POST variables are:

VARIABLE	Values	Description
`mode`	sync	sync mode
`mode`	recreate	recreate mode
`tables`	table1,table2,table3	List of tables that will be sync'ed

Ciberterminal Wiki

Table of Contents

[SCRIPT] ORA2MY: Transferring data from oracle to mysql

Previous Requirements

Dependencies

Oracle

Basic script knowledge

Clone from git.ciberterminal.net

ora2my_sync.sh

Script configuration

ora2my_sync.py

Script configuration

Table synchronization

With sync table

The sync table

Initial synchro example

Without sync table

View Synchronization

Launching synchronizations through http

Instructions (CENTOS7)

Install apache 2.4

Remove unused modules

Configuration

Authentication