====== [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:
<code bash>
yum -y install MySQL-python python-pip python-devel
</code>

''pip'' upgrade:
<code bash>
pip install --upgrade pip
</code>


Oracle module:
<code bash>
pip install cx_Oracle
</code>

===== Oracle =====
''cx_Oracle'' need ''intantclient'' to run:
<code bash>
-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
</code>

Installation example:
<code bash>
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%]
</code>

<WRAP center round important 60%>
Modify the ld.conf path if you change the instantclient version!!!!
</WRAP>


Load libs:
<code bash>
echo "/usr/lib/oracle/12.2/client64/lib" > /etc/ld.so.conf.d/oracle-instant-client.conf
ldconfig
</code>

Check:
<code bash>
/usr/bin/sqlplus64
</code>

Variables needed:
<code bash>
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
</code>


You'll have to write down the tnsnames. The best option is to use our centralized tnsnames.ora:
<code>
svn://10.52.2.15/oracle/tnsnames/tnsnames.ora
</code>


====== 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:
<code bash>
ora2my_sync.sh sync "TABLE1 TABLE2"
</code>


This script is multi-threaded, the number of threads can be configured inside the script:
<code bash>
[ ${DEBUG} -eq 0 ] && let MAXTHREADS=1 || let MAXTHREADS=1
</code>

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:
<code>
cp ora2my_sync.config.tmpl ora2my_sync.config
</code>

<file bash 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
#
########################################################################
</file>

All the options are self-descriptive by its name...

<WRAP center round info 60%>
**Database Types conversion**
In the ''CONSTANTS'' section inside ''ora2my_sync.config'' you'll find the conversion table.
</WRAP>

===== 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:
<code>
cp ora2my_sync.pycfg.tmpl ora2my_sync.pycfg
</code>
\\
\\
<file python ora2my_sync.pycfg>
[MySQL]
username = root
password = ***
host = localhost
databasename = mydiva_t01

[Oracle]
username = DEVOLUIVA
password = ***
host = 10.63.3.150:1521
databasename = DBTEST
</file>

====== Table synchronization ======
===== With sync table =====

<WRAP center round important 60%>
In the 1st synchronization, the sync table **MUST NOT EXISTS** (so drop or rename it).
</WRAP>




==== The sync table  ====
Sync table name should be the same as the "master" table and adding the sufix ''_MYSYNC''.

Sync table metadata is:
<code sql>
CREATE TABLE "<MASTERTABLE>_MYSYNC"
   (    "<PK_COLNAME>_ID" NUMBER NOT NULL ENABLE,
        "ACTION" VARCHAR(1) NOT NULL ENABLE,
        "WHENITWAS" TIMESTAMP DEFAULT SYSTIMESTAMP
   ) 
  TABLESPACE "<USERSCHEMA>"
;
</code>

We'll need a sync trigger:
<code sql>
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;
</code>

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 |

<WRAP center round alert 60%>
This synchro mode **DOES NOT** support master table with a PK multiple (multiple columns on the PK).
</WRAP>

==== Initial synchro example ====

Disable trigger, rename ''_MYSYNC'' table:
<code sql>
ALTER SESSION SET CURRENT_SCHEMA= <OWNER> ;

ALTER TRIGGER "<MASTERTABLE>_MYSYNC" DISABLE ;

ALTER TABLE "<MASTERTABLE>_MYSYNC" RENAME TO "<MASTERTABLE>_NOSYNC" ;
</code>
\\
Launch sync
\\
Reenable it all (this must be done meanwhile the ''ora2my_syn.sh'' script is running):
<code sql>
ALTER TRIGGER "<MASTERTABLE>_MYSYNC" ENABLE ;
ALTER TABLE "<MASTERTABLE>_NOSYNC" RENAME TO "<MASTERTABLE>_MYSYNC" ;
</code>



===== 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.
\\
<WRAP center round info 60%>
The view must have pk!!
\\
<code sql>
ALTER VIEW VW_FINDER_RESTAURANTS ADD CONSTRAINT VW_FINDER_RESTAURANTS_PK PRIMARY KEY (RESOURCE_ID)  DISABLE;
</code>
</WRAP>





====== 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 ====

<code bash>
yum -y install httpd httpd-tools
systemctl enable httpd
</code>


==== Remove unused modules ====

<code bash>
cd /etc/httpd/conf.modules.d
for i in 00-dav.conf 00-lua.conf 00-proxy.conf ; do mv $i ${i}.disabled ; done
</code>
==== Configuration ====
<code bash>
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
</code>


==== Authentication ====
Create folders
<code bash>
mkdir /var/www/html/auth
cd /var/www/html/auth
</code>
Add users:
<code bash>
htpasswd -bc .basic_pw dodger <thepassword>
</code>

Launch Script:
<code bash>
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
</code>

Sample url:
<code>
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
</code>

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

POST variables are:
^ VARIABLE ^ Values ^ Description ^
| ''mode'' | <code>sync</code> | sync mode |
|   :::   | <code>recreate</code> | recreate mode |
| ''tables'' | <code>table1,table2,table3</code> | List of tables that will be sync'ed |