User Tools

Site Tools


mysql:oracle_2_mysql_data_transfer

[SCRIPT] ORA2MY: Transferring data from oracle to mysql

GIT repo

https://github.com/VoxelGroup/Infrastructure.Database.MySQL

To use this repo, generate a ssh keypair and add it to the settings of the repo as readonly!!

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 github

mkdir /home/scripts
cd /home/scripts
git clone git@github.com:VoxelGroup/Infrastructure.Database.MySQL.git
cd /home/scripts/Infrastructure.Database.MySQL/ora2my_transfer

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/Infrastructure.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@voxelgroup.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 jholgado <thepassword>

Launch Script:

cp /home/scripts/Infrastructure.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 jholgado:<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
recreate
recreate mode
tables
table1,table2,table3
List of tables that will be sync'ed

The scripts

ora2my_sync.sh
#!/bin/bash
 
# Exit codes:
#  1 : 
#  2 : 
#  3 : 
#  4 : 
 
 
 
########################################################################
#
# CONFIG FILE
#
########################################################################
 
exec 2>&1
 
CONFIGFILE="$(dirname $(readlink -f $0))/$(basename $(readlink -f $0) .sh).config"
 
. ${CONFIGFILE}
 
########################################################################
#
#/CONFIG FILE
#
########################################################################
 
########################################################################
#
# FUNCTIONS
#
########################################################################
 
press_to_continue()
{   
    echo "Press enter to continue..."
    read -t 10
}
 
debug_me()
{
  if [[ ${DEBUG} -eq 0 ]] ; then
    echo -e "${LIGHTBLUE}DEBUG: ${RESET}$*"
	press_to_continue
#    echo "press enter to continue"
#    read
  fi
}
 
 
 
usage()
{
    echo -e "${LIGHTRED}USAGE:${RESET}
    $0 sync|recreate \"table list\"\n"
    # VERY INITIAL CHECKS
}
 
printmsg()
{
    echo "# $(date) : $*"
}
 
drop_create_database()
{
    printmsg "Dropping Database ${MYDB}"
    echo "DROP DATABASE ${MYDB} ;" | ${MYSQL}
    printmsg "Creating Database ${MYDB}"
    #echo "CREATE DATABASE ${MYDB} ;" | ${MYSQL}
    echo "CREATE DATABASE ${MYDB} DEFAULT CHARACTER SET utf8mb4 ;" | ${MYSQL}
}
 
check_table_changed()
{
    local TABLE="$1"
}
 
create_table()
{
    local TABLE="$1"
    MYTABLEDDL="CREATE TABLE ${TABLE}("
    for LINE in $(echo -e "${NOHEAD}\n${CSV}\nSELECT '\"' || COLUMN_NAME ||'\";\"'|| DATA_TYPE || '\";\"'|| DATA_LENGTH || '\"' FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='${TABLE}' ORDER BY COLUMN_ID ;" | ${SQLPLUS}) ; do
        COLUMN=( ${LINE//;/ } )
        # quote cleanup
        for ((x=0; x<${#COLUMN[@]}; x++)) ; do 
            COLUMN[${x}]="${COLUMN[$x]//\"/}"
        done
 
        # DATA_TYPE cleanup
        COLUMN[1]="$(echo ${COLUMN[1]} | awk -F\( '{print $1}')"  ##### coloring failing here, this is a comment'"
 
        # Conversion from ORACLE to MYSQL data types
        COLUMN[1]="$(printf "%s${CONVERSIONTABLE}" | egrep "^${COLUMN[1]//\"/};" | awk -F\; '{print $2}')"
        #printf "%s${COLUMN[1]}"
 
 
        # for numbers, PRESICION Must be used...
        if [[ "${COLUMN[1]}" =~ .*DECIMAL.* ]] ; then
            local PRECISION="$(echo -e "${NOHEAD}\nselect DATA_PRECISION || ',' || data_scale from ALL_TAB_COLUMNS WHERE TABLE_NAME LIKE '${TABLE}' and COLUMN_NAME LIKE '${COLUMN[0]}' ;" | ${SQLPLUS})"
            [[ "${PRECISION}" =~ ^[0-9]{1,3},[0-9]{1,3}$ ]] && COLUMN[2]="${PRECISION}"
        fi
 
        # CHAR types in oracle are double the size on ALL_TAB_COLUMNS, adapting
        # CHAR Types bigger than 2000 chars, MUST BE TEXT, other case it will crash
        if [[ "${COLUMN[1]}" =~ .*CHAR.* ]] ; then
            # BUT ONLY is necessary for cols with +1000 char wide (for PK's)
            if [ ${COLUMN[2]} -gt 1000 ]; then
                COLUMN[1]="TEXT"
            fi
        fi
 
        # for LONGBLOB, LONGTEXT size is not necessary
        if [[ "${COLUMN[1]}" =~ .*\([0-9]{1,12}\)$ || "${COLUMN[1]}" =~ ^(LONGBLOB|LONGTEXT|MEDIUMTEXT|TEXT)$ ]] ; then
            MYTABLEDDL="${MYTABLEDDL}
            \`${COLUMN[0]}\` ${COLUMN[1]},"
        else
            MYTABLEDDL="${MYTABLEDDL}
            \`${COLUMN[0]}\` ${COLUMN[1]}(${COLUMN[2]}),"
        fi
    done
    MYTABLEDDL="${MYTABLEDDL}
        \`SUPERROWID\` VARCHAR(20) )
        Engine=InnoDB
        ROW_FORMAT=COMPRESSED ;"
#    printmsg "table ddl"
#    printf "%s${MYTABLEDDL}\n"
#    printmsg "Press enter to continue"
#    read
    printf "%sDROP TABLE ${TABLE} ; \n" |${MYSQL} ${MYDB} 2>>/dev/null
    printf "%s${MYTABLEDDL}\n" |${MYSQL} ${MYDB}
 
    RES=$?
    return ${RES}
#    if [ ${RES} -ne 0 ] ; then
#        echo -e "${MYTABLEDDL}\n"
#        printmsg "Thread ${TINDEX}, ERROR CREATING TABLE : ${TNAME}"
#    else
#        printmsg "Thread ${TINDEX}, Successfully created : ${TNAME}"
#    fi
}
 
get_all_tables()
{
    #local TABLELIST="$(echo  -e "${NOHEAD}\nSELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME ;" |${SQLPLUS})"
    local TABLELIST="$(echo  -e "${NOHEAD}\nSELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_NAME LIKE 'VW_FINDER_RESTAURANTS' ORDER BY OBJECT_NAME ;" |${SQLPLUS})"
    local let TINDEX=0
 
    for TABLE in ${TABLELIST} ; do
        echo "${TABLE}"
        TABLEXTHREAD[${TINDEX}]="${TABLEXTHREAD[${TINDEX}]} ${TABLE}"
        let TINDEX++
        [ ${TINDEX} -eq ${MAXTHREADS} ] && TINDEX=0
    done
    unset TINDEX
    unset TABLELIST
}
 
build_threads()
{
    local let TINDEX=0
    for TABLE in ${TABLELIST} ; do
        TABLEXTHREAD[${TINDEX}]="${TABLEXTHREAD[${TINDEX}]} ${TABLE}"
        let TINDEX++
        [ ${TINDEX} -eq ${MAXTHREADS} ] && TINDEX=0
    done
    unset TINDEX
}
 
sync_table()
{
    echo 
 
    # habría que re-crear la tabla secundaria ...
}
 
build_json()
{
    local ADDTHIS="$*"
    RESULTJSON="${RESULTJSON} ${ADDTHIS}"
}
 
launch_thread()
{
    local FIRST=0
    local let TINDEX=$1
    for TNAME in ${TABLEXTHREAD[${TINDEX}]} ; do
        [[ ${FIRST} -ne 0 ]] && build_json ", "
        build_json "{\"TABLENAME\": \"${TNAME}\", \"OPERATIONS\": {"
        if [[ "${OPTMODE}" = "recreate" ]] ; then
            debug_me "Thread ${TINDEX}, Creating Table : ${TNAME}"
            build_json "\"CREATION\": \""
            create_table ${TNAME}
            [[ $? -eq 0 ]] && build_json "sucess" || build_json "fail"
            build_json "\","
            debug_me "Thread ${TINDEX}, Importing Table : ${TNAME}"
            build_json "\"SYNC\": \""
#            set -x
            ${ORA2MY} "${TNAME}"
            #set +x 
            [[ $? -eq 0 ]] && build_json "sucess" || build_json "fail"
            build_json "\","
 
            debug_me "Thread ${TINDEX}, Creating table PK: ${TNAME}"
            build_json "\"PK\": \""
            create_pk "${TNAME}"
            [[ $? -eq 0 ]] && build_json "sucess" || build_json "fail"
            build_json "\","
            debug_me "Thread ${TINDEX}, Creating table Indexes: ${TNAME}"
            build_json "\"INDEXES\": \""
            create_index "${TNAME}"
            [[ $? -eq 0 ]] && build_json "sucess" || build_json "fail"
            # take care here, last item in json does not has final ,
            build_json "\""
 
            debug_me "Thread ${TINDEX}, Done Table : ${TNAME}"
        elif [[ "${OPTMODE}" = "sync" ]] ; then
            debug_me "Thread ${TINDEX}, synching Table : ${TNAME}"
 
            build_json "\"SYNC\": \""
#            set -x
            ${ORA2MY} "${TNAME}"
            #set +x 
            [[ $? -eq 0 ]] && build_json "sucess" || build_json "fail"
            build_json "\""
 
            debug_me "Thread ${TINDEX}, Done Table : ${TNAME}"
        fi
        build_json " } }"
        FIRST=1
    done
 
}
 
create_index()
{
    local TABLE="$1"
    local INDEXLIST=""
    local INDEXNAME=""
    local COLUMNLIST=""
    local COLUMN=""
    local INDEXCOLUMNS=""
    local RES=0
 
    local QUERY="SELECT INDEX_NAME
    FROM ALL_INDEXES
    WHERE OWNER='${ORAUSER}'
    AND TABLE_NAME='${TABLE}' 
    AND INDEX_NAME NOT LIKE '%PK'
    ;"
    local INDEXLIST="$(echo  -e "${NOHEAD}\n${QUERY}"|${SQLPLUS})"
 
    for INDEXNAME in ${INDEXLIST} ; do
        local QUERY="SELECT COLUMN_NAME
        FROM ALL_IND_COLUMNS
        WHERE INDEX_NAME='${INDEXNAME}'
        AND INDEX_OWNER='${ORAUSER}' ;"
 
        local COLUMNLIST="$(echo  -e "${NOHEAD}\n${QUERY}" |${SQLPLUS})"
        for COLUMN in ${COLUMNLIST} ; do
            [[ "${INDEXCOLUMNS}" ]] && INDEXCOLUMNS="${INDEXCOLUMNS},\`${COLUMN}\`" || INDEXCOLUMNS="\`${COLUMN}\`"
        done
        # if there's a user-defined column in the index, querying metadata directly
        if [[ "${COLUMNLIST}" =~ .*SYS_NC[0-9]{5}\$.* ]] ; then
            #echo "get ddl from oracle"
            local QUERY="SELECT DBMS_METADATA.GET_DDL('INDEX','${INDEXNAME}','${ORAUSER}') TXT FROM DUAL ;"
            local INDEXDDL="$(echo  -e "${NOHEAD}\n${QUERY}" |${SQLPLUS}| egrep "CREATE INDEX"| awk 'BEGIN{FS="[()]"}{print $2}'| sed 's,",\`,g' | sed 's/, 1//g')"
            INDEXDDL="CREATE INDEX ${INDEXNAME} ON ${TABLE}(${INDEXDDL}) ;"
        else
            local INDEXDDL="CREATE INDEX ${INDEXNAME} ON ${TABLE}(${INDEXCOLUMNS}) ;"
        fi
        echo "${INDEXDDL}" | ${MYSQL} ${MYDB}
        RES=$?
        COLUMNLIST=""
        INDEXCOLUMNS=""
    done
    return ${RES}
}
 
create_pk()
{
    local TABLE="$1"
    local PK=""
    local COLUMN=""
    local COLUMNLIST=""
 
    local QUERY="SELECT COLUMN_NAME 
    FROM ALL_CONS_COLUMNS 
    WHERE OWNER='${ORAUSER}' 
    AND CONSTRAINT_NAME = (SELECT CONSTRAINT_NAME 
        FROM ALL_CONSTRAINTS 
        WHERE OWNER='${ORAUSER}'
        AND TABLE_NAME = '${TABLE}'
        AND CONSTRAINT_TYPE='P'
        ); "
    local COLUMNLIST="$(echo  -e "${NOHEAD}\n${QUERY}" |${SQLPLUS})"
 
    if [[ "${COLUMNLIST}" ]] ; then
        for COLUMN in ${COLUMNLIST} ; do
            [[ "${PK}" ]] && PK="${PK},\`${COLUMN}\`" || PK="\`${COLUMN}\`"
        done
        echo "ALTER TABLE \`${TABLE}\` ADD PRIMARY KEY (${PK});" | ${MYSQL} ${MYDB}
        RES=$?
        if [ ${RES} -ne 0 ] ; then
            # Searching varchar cols within pk cols
            QUERY="SELECT TABLE_NAME,
                            COLUMN_NAME,
                            DATA_TYPE,
                            CHARACTER_MAXIMUM_LENGTH,
                            CHARACTER_SET_NAME,
                            COLLATION_NAME
            FROM COLUMNS 
            WHERE TABLE_NAME='${TABLE}'
            AND COLUMN_NAME IN ( $(echo ${PK}| sed "s,\`,',g") )
            AND DATA_TYPE LIKE '%CHAR%'
            AND COLLATION_NAME LIKE 'utf8%_general_ci'
            AND TABLE_SCHEMA='${MYDB}' ;"
            echo -e "-- ######################################################"
            echo -e "-- Modifying column collation to UTF8_BIN or UTF8MB4_BIN to re-create the index"
            while read LINE ; do
                echo "-- Rollback to original:"
                echo "${LINE}" | awk '{print "ALTER TABLE "$1" CHANGE "$2" "$2" "$3"("$4") COLLATE "$6" ;"}'
                echo "${LINE}" | awk '{print "ALTER TABLE "$1" CHANGE "$2" "$2" "$3"("$4") COLLATE "$5"_bin ;"}' | ${MYSQL} ${MYDB}
            done< <(echo -e ${QUERY} | ${MYSQL} "information_schema" | egrep -v "^TABLE_NAME.*COLLATION_NAME$|^[[:space:]]{0,}$")
            echo -e "-- ######################################################"
 
            echo "ALTER TABLE \`${TABLE}\` ADD PRIMARY KEY (${PK});" | ${MYSQL} ${MYDB}
            RES=$?
            #if [ ${RES} -ne 0 ] ; then
            #    echo -e "-- ERROR creating PK"
            #    echo -e "-- ######################################################"
            #    echo -e "ALTER TABLE \`${TABLE}\` ADD PRIMARY KEY (${PK});\n-- ######################################################"
            #fi
        fi
    else
        # this will mean that there's no colums, something failed...
        return 1
    fi
    return ${RES}
}
 
 
########################################################################
#
# / FUNCTIONS
#
########################################################################
 
########################################################################
#
# MAIN
#
########################################################################
 
 
OPTMODE="$1"
OPTTABLELIST="$2"
# converting table names to upper to avoid problems #113906
OPTTABLELIST="${OPTTABLELIST^^}"
 
RESULTJSON="{ \"action\": \"${OPTMODE}\", \"tables\": [ "
 
if [[ "${OPTTABLELIST}" ]] ; then
#    echo "Tables: ${OPTTABLELIST}"
    TABLELIST="${OPTTABLELIST}"
else
    usage
    exit 0
#    echo "Tables: ${TABLELIST}"
fi
 
build_threads
 
for ((i=0; i<${MAXTHREADS}; i++)) ; do
#    if [ ${DEBUG} -eq 0 ]; then
#        # DEBUGGING? Threading disabled
        launch_thread ${i}
#    else
#        launch_thread ${i} &
#    fi
done
 
build_json "], \"ressult\": \""
 
if [[ "${RESULTJSON}" =~ \"[fF]ail\", ]] ; then
    build_json "Fail"
    echo -e "[${LIGHTRED}FAIL${RESET}]"
    RES=1
else
    build_json "Success"
    echo -e "[${LIGHTGREEN}OK${RESET}]"
    RES=0
fi
 
build_json "\"}"
 
debug_me "ALL Done"
 
#echo -e "${RESULTJSON}"
 
exit ${RES}
 
########################################################################
#
# / MAIN
#
########################################################################
ora2my_sync.py
#!/usr/bin/python
 
########################################################################
#
# Modules & globals
#
########################################################################
 
# Mysql Module
import MySQLdb as mdb
# Debugging Module
#import warnings
#import datetime
import os
#import codecs
import time
import re
import ConfigParser
import string
 
# pdb module for tracing
#import pdb
 
os.environ["NLS_LANG"] = "AMERICAN_AMERICA.UTF8"
os.environ["NLS_TIMESTAMP_FORMAT"] = "yyyy-mm-dd hh:mi:ss"
import cx_Oracle
 
import sys
reload(sys)
# Forced charset for NCLOB
sys.setdefaultencoding('utf-8')
 
accept2dyear=0
 
oraquery=""
myquery=""
tname=""
mycolumns=""
values_template=""
dupeupdate=""
ExitCode=0
 
# MOVED TO .pycfg  set it there
# SET to 0 for debugging
#DEBUG=0
#DEBUG=1
 
########################################################################
#
# CONSTANTS
#
########################################################################
 
config = ConfigParser.RawConfigParser()
#config.read('ora2my_sync.pycfg')
config.read(os.path.dirname(os.path.realpath(__file__)) + '/ora2my_sync.pycfg')
 
DEBUG = int(config.get('GENERAL', 'debug'))
# DEGREE of parallelism in oracle
PARALLEL = config.get('GENERAL', 'parallel')
# Number of rows for fetchmany()
FETCHTHIS = int(config.get('GENERAL', 'fetchthis'))
# commit after insert
COMMITONLOOP= config.get('GENERAL', 'commit_on_loop')
 
# PROD
orauser = config.get('Oracle','username')
orapasswd = config.get('Oracle','password')
orahost = config.get('Oracle','host')
orasid = config.get('Oracle','databasename')
orastring = orauser + '/' + orapasswd + '@' + orahost + '/' + orasid
 
myuser = config.get('MySQL','username')
mypasswd = config.get('MySQL','password')
myhost = config.get('MySQL','host')
mysid = config.get('MySQL','databasename')
 
#"
########################################################################
#
# FUNCTIONS
#
########################################################################
 
def ShowProgress(Counter):
    if DEBUG == 0:
        if re.match('^[0-9]{1,}000$',str(Counter)):
            print "%s : %d Rows processed" % (tname, Counter)
 
def DebugMe(Message):
    if DEBUG == 0:
        print "(%s) %s" % (time.strftime("%Y-%m-%d %H:%M:%S"), Message)
 
def hasSyncTable(tname):
    oraQuery = 'SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE \'' + tname + '_MYSYNC\' '
    oracur.execute(oraQuery)
    tSync = oracur.fetchone()
    if tSync:
        if tSync[0] == tname + '_MYSYNC':
            return tSync[0]
    return "none"
 
 
def getTablePK(tname):
    oraQuery = 'select COLUMN_NAME FROM ALL_CONS_COLUMNS WHERE OWNER=\'' + orauser + '\' AND CONSTRAINT_NAME like \'%\' || (select constraint_name from ALL_constraints where table_name=\'' + tname + '\' and constraint_type=\'P\') || \'%\''
    oracur.execute(oraQuery)
    Columns = set(row[0] for row in oracur.fetchall())
    oraColumns = '"' + '", "'.join(Columns) + '"'
 
    if len(Columns)>1:
        #print 'UNSUPPORTED TABLE FOR SYNCKING WITH SYNC TABLE'
        return
    else:
        return oraColumns
    return
 
 
def getTableCols(tname):
    colnumber = 0
    # COLUMNS not TIMESTAMP NOR DATE
    oraquery = 'SELECT COLUMN_NAME FROM SYS.ALL_TAB_COLUMNS WHERE OWNER = \'' + orauser + '\' AND NOT (DATA_TYPE LIKE \'%SDO%\' OR DATA_TYPE LIKE \'TIMESTAMP%\' OR DATA_TYPE LIKE \'DATE\') AND TABLE_NAME = \'' + tname + '\''
    oracur.execute(oraquery)
    keys = set(row[0] for row in oracur.fetchall())
    oracolumns = '"' + '", "'.join(keys) + '"'
    mycolumns = '`' + '`, `'.join(keys) + '`'
    values_template = ", ".join(["%s"] * len(keys))
    keys = None
 
    # TIMESTAMP & DATE COLUMNS
    oraquery = 'SELECT COLUMN_NAME FROM SYS.ALL_TAB_COLUMNS WHERE OWNER = \'' + orauser + '\' AND (DATA_TYPE LIKE \'TIMESTAMP%\' OR DATA_TYPE LIKE \'DATE\') AND TABLE_NAME = \'' + tname + '\''
    oracur.execute(oraquery)
    keys = set(row[0] for row in oracur.fetchall())
    if len(keys) > 0:
        mycolumns = mycolumns + ", " + ", ".join(keys)
        oracolumns = oracolumns + ', TO_CHAR("' + '", \'YYYY-MM-DD HH24:MI:SS\'), TO_CHAR("'.join(keys) + '", \'YYYY-MM-DD HH24:MI:SS\')'
        values_template = values_template + ", " + ", ".join(["%s"] * len(keys)) + ', %s'
        colnumber += len(keys)
    else:
        values_template = values_template + ', %s'
    keys = None
 
    # if the table has a sync table, I'll launch different sql's
    SyncTable = hasSyncTable(tname)
    if SyncTable == "none":
         #dupeupdate = ""
        myQuery = "insert into %s (%s, `SUPERROWID`) values (%s)" % (tname, mycolumns, values_template)
        oraQuery = 'SELECT /*+ PARALLEL(' + PARALLEL + ') */ ' + oracolumns + ', NULL FROM ' + tname
        pkColumns = ""
    else:
        # MERGE PART
        # COLUMNS not TIMESTAMP NOR DATE
        oraquery = 'SELECT COLUMN_NAME FROM SYS.ALL_TAB_COLUMNS ' + \
        '    WHERE OWNER = \'' + orauser + '\' AND NOT (DATA_TYPE LIKE \'%SDO%\' OR DATA_TYPE LIKE \'TIMESTAMP%\' OR DATA_TYPE LIKE \'DATE\') ' + \
        '    AND TABLE_NAME = \'' + tname + '\'' + \
        '     AND COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM SYS.ALL_IND_COLUMNS' + \
        '        WHERE INDEX_OWNER=\'' + orauser + '\'' + \
        '        AND TABLE_NAME=\'' + tname + '\'' + \
        '        AND INDEX_NAME LIKE \'%PK%\'' + \
        '        )'
        oracur.execute(oraquery)
        keys = set(row[0] for row in oracur.fetchall())
        oraUpdateColumns = '"' + '", "'.join(keys) + '"'
        dupeupdate = ''
        for updatecolum in keys:
            if len(dupeupdate)>0:
                dupeupdate = dupeupdate + '+' + updatecolum + '="%s"'
            else:
                dupeupdate = updatecolum + '="%s"'
        colnumber = len(keys)
        keys = None
 
        # TIMESTAMP & DATE COLUMNS
        oraquery = 'SELECT COLUMN_NAME FROM SYS.ALL_TAB_COLUMNS ' + \
        '    WHERE OWNER = \'' + orauser + '\' AND (DATA_TYPE LIKE \'%SDO%\' OR DATA_TYPE LIKE \'TIMESTAMP%\' OR DATA_TYPE LIKE \'DATE\') ' + \
        '    AND TABLE_NAME = \'' + tname + '\'' + \
        '     AND COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM SYS.ALL_IND_COLUMNS' + \
        '        WHERE INDEX_OWNER=\'' + orauser + '\'' + \
        '        AND TABLE_NAME=\'' + tname + '\'' + \
        '        AND INDEX_NAME LIKE \'%PK%\'' + \
        '        )'
        oracur.execute(oraquery)
        keys = set(row[0] for row in oracur.fetchall())
        if len(keys) > 0:
            oraUpdateColumns = oraUpdateColumns + ', TO_CHAR("' + '", \'YYYY-MM-DD HH24:MI:SS\'), TO_CHAR("'.join(keys) + '", \'YYYY-MM-DD HH24:MI:SS\')'
            colnumber += len(keys)
            for updatecolum in keys:
                if len(dupeupdate)>0:
                    dupeupdate = dupeupdate + '+' + updatecolum + '="%s"'
                else:
                    dupeupdate = updatecolum + '="%s"'
        keys = None
 
        #Getting pk columns
        pkColumns = getTablePK(tname)
 
        myQuery = "insert into %s (%s, `SUPERROWID`) values (%s) ON DUPLICATE KEY UPDATE %s" % (tname, mycolumns, values_template, dupeupdate)
        DebugMe(myQuery)
        oraQuery = 'SELECT /*+ PARALLEL(' + PARALLEL + ') */ ' + oracolumns + ', NULL, ' + oraUpdateColumns + ' FROM ' + tname
        DebugMe(oraQuery)
 
 
    #print "INSIDE FUNCTION " + myQuery
    return myQuery, oraQuery, pkColumns
 
 
########################################################################
#
# MAIN
#
########################################################################
 
 
DebugMe("DEBUG MODE ON")
 
if PARALLEL == None:
    DebugMe("Parallel not enabled")
    PARALLEL=1
else:
    DebugMe("Parallelism set to %s" % PARALLEL)
 
if FETCHTHIS == None:
    DebugMe("fetchmany to default: 1000")
    FETCHTHIS = 1000
else:
    DebugMe("fetchmany set to default: %d" % FETCHTHIS)
 
 
 
 
# uncomment for tracing
#pdb.set_trace()
 
oracon = cx_Oracle.connect(orastring)
oracur = oracon.cursor()
 
mycon = mdb.connect(host=myhost,user=myuser,passwd=mypasswd,db=mysid,charset='utf8');
mycur = mycon.cursor()
 
 
if len(sys.argv) > 1:
    oraquery = 'SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_NAME LIKE \'' + sys.argv[1] + '\' '
    DebugMe(oraquery)
else:
    #raise RuntimeError("Table name requiered")
    ExitCode=1
 
oracur.execute(oraquery)
alltables = oracur.fetchall()
 
if len(alltables)==0:
    #raise RuntimeError("No tables with that name in oracle")
    DebugMe("Table not found, exitting")
    ExitCode=1
 
 
for tablename in alltables:
    tname = tablename[0]
    DebugMe(tname)
 
    myquery, oraquery, pkcolumns = getTableCols(tname)
 
    Counter=0
    # if pkcolumns is set, then we have sync table
    SyncTable = hasSyncTable(tname)
    if SyncTable == tname + '_MYSYNC':
        #print "%s : has sync table" % (tname)
        # Here we have a _MYSYNC table"
        auxQuery = "SELECT " + pkcolumns + ", ACTION, WHENITWAS FROM " + tname + "_MYSYNC ORDER BY WHENITWAS ASC"
        oracur.execute(auxQuery)
        allSyncData = oracur.fetchall()
        startTime=""
        endTime=""
        if len(allSyncData)>0:
            #print allSyncData
            #print len(allSyncData)
            for rowSyncData in allSyncData:
                if startTime == "":
                    startTime = rowSyncData[2]
                if (rowSyncData[1] == "i" or rowSyncData[1] == "u"):
                    auxQuery = oraquery + " WHERE " + pkcolumns + "= %s" % str(rowSyncData[0])
                    oracur.execute(auxQuery)
                    oraRow = oracur.fetchone()
                    try:
                        mycur.execute(myquery,oraRow)
                    except mdb.Error, e:
                        try:
                            #print "MySQL Error [%d]: %s" % (e.args[0], e.args[1])
                            ExitCode=1
                        except IndexError:
                            #print "MySQL Error: %s" % str(e)
                            ExitCode=1
                elif rowSyncData[1] == "d":
                    auxColumn = string.replace(pkcolumns, '"', '')
                    #auxQuery = "DELETE FROM " + tname + " WHERE " + pkcolumns + "=%s" % str(rowSyncData[0])
                    auxQuery = "DELETE FROM " + tname + " WHERE " + auxColumn + "=%s" % str(rowSyncData[0])
                    mycur.execute(auxQuery)
                else:
                    #print "Wrong action : %s" % rowSyncData[1]
                    ExitCode=1
                mycon.commit()
                ShowProgress(Counter)
            Counter+=1
            ShowProgress(Counter)
            endTime = rowSyncData[2]
 
            # Debug
            #auxQuery = "SELECT COUNT(*) FROM " + tname + "_MYSYNC WHERE WHENITWAS>=TO_TIMESTAMP('" + str(startTime) + "', 'YYYY-MM-DD HH24:MI:SS.FF') AND WHENITWAS<=TO_TIMESTAMP('" + str(endTime) + "', 'YYYY-MM-DD HH24:MI:SS.FF')"
            #oracur.execute(auxQuery)
            #auxRessult = oracur.fetchall()
            #print auxRessult
            # Debug
 
            # DELETING DATA FROM ORACLE
            #auxQuery = "DELETE FROM " + tname + "_MYSYNC WHERE WHENITWAS>=TO_TIMESTAMP('" + str(startTime) + "', 'YYYY-MM-DD HH24:MI:SS.FF') AND WHENITWAS<=TO_TIMESTAMP('" + str(endTime) + "', 'YYYY-MM-DD HH24:MI:SS.FF')"
            auxQuery = "DELETE FROM " + tname + "_MYSYNC WHERE WHENITWAS>=TO_TIMESTAMP('" + str(startTime) + "', 'YYYY-MM-DD HH24:MI:SS.FF') AND WHENITWAS<=TO_TIMESTAMP('" + str(endTime) + "', 'YYYY-MM-DD HH24:MI:SS.FF') AND ACTION <>'d'"
            oracur.execute(auxQuery)
            oracon.commit()
    # there's NO sync table
    else:
        Counter = 0
        auxQuery ="DELETE FROM " + tname + " ;"
        mycur.execute(auxQuery)
        DebugMe("Deleted mysql rows")
        auxQuery = "select count(*) from " + tname
        oracur.execute(auxQuery)
        totalrows = oracur.fetchone()
        DebugMe("Total rows = %d" % totalrows[0])
        DebugMe(oraquery)
        oracur.execute(oraquery)
        #allrows = oracur.fetchmany(1000)
        allrows = oracur.fetchmany(FETCHTHIS)
        #allrows = oracur.fetchall()
        #mycur.executemany(myquery,allrows)
        #DebugMe("Fetched All rows from Oracle: %d" % oracur.rowcount )
        while oracur.rowcount < totalrows[0]:
            try:
                mycur.executemany(myquery,allrows)
                if COMMITONLOOP == 'yes':
                    mycon.commit()
                DebugMe("Inserted %d out of %d rows" % (oracur.rowcount, totalrows[0]))
            except mdb.Error, e:
                try:
                    #print "MySQL Error [%d]: %s" % (e.args[0], e.args[1])
                    ExitCode=1
                except IndexError:
                    #print "MySQL Error: %s" % str(e)
                    ExitCode=1
            #allrows = oracur.fetchmany(1000)
            allrows = oracur.fetchmany(FETCHTHIS)
            Counter+=1
            ShowProgress(Counter)
        mycur.executemany(myquery,allrows)
        DebugMe("Inserted %d out of %d rows" % (oracur.rowcount, totalrows[0]))
        mycon.commit()
        mycolumns = None
        oracolumns = None
        NclobBegin = None
        NclobEnd = None
 
oracur.commit()
oracur.rollback()
oracur.close()
oracon.close()
mycon.commit()
mycur.close()
mycon.close()
sys.exit(ExitCode)

The config files

ora2my_sync.config.tmpl
########################################################################
#
# CONSTANTS
#
########################################################################
 
# colors
LIGHTGREEN="\033[1;32m"
LIGHTRED="\033[1;31m"
WHITE="\033[0;37m"
RESET="\033[0;00m"
 
NOHEAD="SET LINESIZE 32767
SET LONG 1048576
SET LONGC 1048576
SET PAGESIZE 0
SET SERVEROUT ON SIZE UNLIMITED
SET ECHO OFF
SET HEADS OFF
SET HEADING   OFF
SET FEEDBACK  OFF
set space 0
COL TXT FORMAT A10000"
 
 
export TNS_ADMIN="/etc/oracle"
export ORACLE_HOME="/usr/lib/oracle/12.1/client64"
 
 
CSV="SET COLSEP \";\""
 
# due error
# ERROR 1118 (42000) at line 1: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
# VARCHAR bigger than 2000 chars, must be changed to TEXT, that is done in the create_table function
CONVERSIONTABLE="BFILE;VARCHAR(255)
BINARY_FLOAT;FLOAT
BINARY_DOUBLE;DOUBLE
BLOB;LONGBLOB
CHAR;VARCHAR
CHARACTER;VARCHAR
CLOB;LONGTEXT
DATE;DATETIME(6)
DEC;DEC
DECIMAL;DECIMAL
FLOAT;DOUBLE
INT;DECIMAL
INTEGER;DECIMAL
LONG;LONGTEXT
LONG RAW;LONGBLOB
NCHAR;NCHAR
NVARCHAR;NCHAR
NCLOB;NVARCHAR(4294967295)
NVARCHAR2;NVARCHAR
RAW;VARBINARY
REAL;DOUBLE
ROWID;CHAR(10)
TIMESTAMP;TIMESTAMP(6)
UROWID;VARCHAR
VARCHAR;VARCHAR
VARCHAR2;VARCHAR
XMLTYPE;LONGTEXT
NUMBER;DECIMAL
SDO_GEOMETRY;VARCHAR(255)"
 
 
########################################################################
#
# / CONSTANTS
#
########################################################################
 
########################################################################
#
# 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/Infrastructure.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
#
########################################################################
ora2my_sync.pycfg.tmpl
[GENERAL]
debug=0
parallel=8
fetchthis=1000
commit_on_loop=no
 
 
 
[MySQL]
username = root
password = thepassword
host = localhost
databasename = thedatabase
 
[Oracle]
username = USERNAME
password = thepassword
host = hostname:1521
databasename = DBTEST
mysql/oracle_2_mysql_data_transfer.txt · Last modified: 2020/07/22 13:43 by dodger