====== [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<
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
########################################################################
#
# 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
\\
\\
[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 "_MYSYNC"
( "_ID" NUMBER NOT NULL ENABLE,
"ACTION" VARCHAR(1) NOT NULL ENABLE,
"WHENITWAS" TIMESTAMP DEFAULT SYSTIMESTAMP
)
TABLESPACE ""
;
We'll need a sync trigger:
CREATE OR REPLACE EDITIONABLE TRIGGER "_MYSYNC"
AFTER INSERT
OR UPDATE
OR DELETE
ON ACTOR_IMAGES
FOR EACH ROW
BEGIN
CASE
WHEN INSERTING OR UPDATING THEN
INSERT INTO _MYSYNC
VALUES (:NEW., 'i', SYSTIMESTAMP);
WHEN DELETING THEN
INSERT INTO _MYSYNC
VALUES (:OLD., 'd', SYSTIMESTAMP);
END CASE;
END;
/
ALTER TRIGGER "_MYSYNC" ENABLE;
Variables to be replaced on the SQL's:
^ '''' | Name of the ''master'' table |
^ '''' | Name of the PK column |
^ '''' | 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= ;
ALTER TRIGGER "_MYSYNC" DISABLE ;
ALTER TABLE "_MYSYNC" RENAME TO "_NOSYNC" ;
\\
Launch sync
\\
Reenable it all (this must be done meanwhile the ''ora2my_syn.sh'' script is running):
ALTER TRIGGER "_MYSYNC" ENABLE ;
ALTER TABLE "_NOSYNC" RENAME TO "_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<
AllowOverride none
Require all denied
DocumentRoot "/var/www/html"
AllowOverride None
Require all granted
Options Indexes FollowSymLinks
AllowOverride None
Require all granted
DirectoryIndex index.html
Require all denied
ErrorLog "logs/error_log"
LogLevel warn
LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "%h %l %u %t \"%r\" %>s %b" common
LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\" %I %O" combinedio
CustomLog "logs/access_log" combined
ScriptAlias /cgi-bin/ "/var/www/cgi-bin/"
AllowOverride None
Options None
Require all granted
TypesConfig /etc/mime.types
AddType application/x-compress .Z
AddType application/x-gzip .gz .tgz
AddType text/html .shtml
AddOutputFilter INCLUDES .shtml
AddDefaultCharset UTF-8
MIMEMagicFile conf/magic
EnableSendfile on
IncludeOptional conf.d/*.conf
ScriptLog logs/cgi.log
EOF
rm -fv /etc/httpd/conf.d/*
cat >/etc/httpd/conf.d/auth.conf<
AuthType basic
AuthName "private area"
AuthBasicProvider file
#AuthBasicUseDigestAlgorithm MD5
AuthUserFile "/var/www/html/auth/.basic_pw"
Require valid-user
EOF
==== Authentication ====
Create folders
mkdir /var/www/html/auth
cd /var/www/html/auth
Add users:
htpasswd -bc .basic_pw dodger
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: --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 |
| ::: | recreate
| recreate mode |
| ''tables'' | table1,table2,table3
| List of tables that will be sync'ed |