Basic knowledge of:

• apache/cgi
• Oracle
• mysql
• bash

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

cxOracle 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.x8664.rpm -rw-r–r– 1 root root 593K Jan 16 15:40 oracle-instantclient12.2-devel-12.2.0.1.0-1.x8664.rpm -rw-r–r– 1 root root 253K Jan 16 15:40 oracle-instantclient12.2-odbc-12.2.0.1.0-2.x8664.rpm -rw-r–r– 1 root root 692K Jan 16 15:40 oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x8664.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>

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 ORACLEHOME to the directory where the bin and lib directories are located for the oracle client export ORACLEHOME=/usr/lib/oracle/12.2/client64 # No need to add ORACLEHOME 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 LDLIBRARYPATH=$LDLIBRARYPATH:$ORACLEHOME/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: ^ ora2mysync.sh | shell script which build the database structure |

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

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

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

The configuration is done with the config file ora2mysync.config
You'll find a template in the github repo, so: <code> cp ora2mysync.config.tmpl ora2mysync.config </code> <file bash ora2my_sync.config> ######################################################################## # # VARIABLES # ######################################################################## ORACLEHOME=/usr/lib/oracle/18.5/client64 ORAUSER=“USERNAME” ORAPASSWD=“THEPASSWORD” ORASID=“TNSNAMEOF_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/Thefuckingbofh.Database.MySQL/ora2mytransfer/ora2mysync.py” TABLELIST=“VWFINDERRESTAURANTS USERPREFERREDUNITS VWFINDERPARKINGS VWFINDERGAS_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…

===== ora2my_sync.py ===== This script will sync data between Oracle and Mysql. The only parameter is the table name. It uses cxOracle 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.

The config is done in the ora2mysync.pycfg file:

From the github repo: <code> cp ora2mysync.pycfg.tmpl ora2mysync.pycfg </code>

<file python ora2mysync.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 =====

==== 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” ( “<PKCOLNAME>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 ACTORIMAGES FOR EACH ROW BEGIN CASE WHEN INSERTING OR UPDATING THEN INSERT INTO <MASTERTABLE>MYSYNC VALUES (:NEW.<PKCOLNAME>, 'i', SYSTIMESTAMP); WHEN DELETING THEN INSERT INTO <MASTERTABLE>MYSYNC VALUES (:OLD.<PKCOLNAME>, '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 | ^ <PKCOLNAME> | Name of the PK column |

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 ora2mysyn.sh script is running):

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

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

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.

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

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.

yum -y install httpd httpd-tools
systemctl enable httpd

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

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

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:

sync

recreate

table1,table2,table3