How to connect Oracle Server to Mysql - dg4odbc

Connect - oracle to mysql

linux console:

. /home/oracle/.bash_profile

Verify version dg4odbc - 64bits or 32

file $ORACLE_HOME/bin/dg4odbc

from http://unixODBC.org download last version

cd ~/Downloads
wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.4.tar.gz
tar -zxvf unixODBC-2.3.4.tar.gz
cd unixODBC-2.3.4
export CFLAGS="-m64"
./configure --prefix=/usr/local/unixODBC
make
make install


cd ~/Downloads
wget http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm
yum localinstall mysql57-community-release-el6-7.noarch.rpm

Verify if repository got installed
yum repolist enabled | grep "mysql.*-community.*"

yum install mysql-connector-odbc-setup.x86_64
At warning message Retrieving key - type Y

update index for search on your server linux

updatedb

search
locate libmyodbc5w.so

At results should apear /usr/lib64/libmyodbc5w.so ( Driver Mysql for odbc)

at profile file from user oracle

vi /home/oracle/.bash_profile

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/unixODBC/lib:/usr/lib64:/u01/app/oracle/product/12.1.0/db_1/hs/lib
export ODBCINI=/usr/local/unixODBC/etc/odbc.ini
export ODBCSYSINI=/usr/local/unixODBC/etc
export NLS_LANG=ROMANIAN_ROMANIA.AL32UTF8

vi /usr/local/unixODBC/etc/odbcinst.ini

append or add

[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/lib/psqlodbc.so
Setup=/usr/lib/libodbcpsqlS.so
Driver64=/usr/lib64/psqlodbc.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1
UsageCount=3

[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1
UsageCount=3

[MySQL ODBC 5.3 Unicode Driver]
Driver=/usr/lib64/libmyodbc5w.so
SETUP=/usr/lib64/libmyodbc5S.so
UsageCount=1

[MySQL ODBC 5.3 ANSI Driver]
Driver=/usr/lib64/libmyodbc5a.so
SETUP=/usr/lib64/libmyodbc5S.so
UsageCount=1

vi /usr/local/unixODBC/etc/odbc.ini

append or add

[SQLSERVER1]
Driver          = /usr/lib64/libmyodbc5w.so
PORT            = 3306
SERVER          = 192.168.0.33
USER            = user
PASSWORD        = password
Database        = database_name
Option          = 3
QuotedId        = Yes
EnableQuotedIdentifiers         = 1


We need to edit

vi /u01/app/oracle/product/12.1.0/db_1/hs/admin/initSQLSERVER1.ora
add

HS_FDS_CONNECT_INFO = SQLSERVER1
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/local/unixODBC/lib/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
set ODBCINI=/usr/local/unixODBC/etc/odbc.ini
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15


vi /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
add

SQLSERVER1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1521))
    (CONNECT_DATA =
#      (SERVER = DEDICATED)
      (SERVICE_NAME = SQLSERVER1)
    )
    (HS=OK)
  )



vi /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
add

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=SQLSERVER1)
      (ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1)
      (PROGRAM=dg4odbc)
      (ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/db_1/lib:/lib:/usr/lib:/usr/local/unixODBC/lib:/usr/lib64:/u01/app/oracle/product/12.1.0/db_1/hs/lib)
    )
  )


We can verify if we can connect to mysql from our server with unixodbc

cd /usr/local/unixODBC/bin
./isql -v SQLSERVER1

SQL console should apear -  we make our test with a select statement and if something apear, than we are good to go.

quit

To be able accessing from oracle instance:



su oracle
. /home/oracle/.bash_profile

lsnrctl stop
lsnrctl start
lsnrctl status

sqlplus / as sysdba

Create dblink for mysql
CREATE PUBLIC DATABASE LINK SQLSERVER1 CONNECT TO "user" IDENTIFIED BY "password" USING 'SQLSERVER1';

and we test with a select statment

select * from "users"@SQLSERVER1;

( If we want to drop ddblink, use: DROP PUBLIC DATABASE LINK SQLSERVER1; )

Setup hostname and FQDN on CentOS 7

Step 1: Edit /etc/hosts file

vi /etc/hosts

192.168.56.101 mondor.freeddns.org mondor

Step 2: Edit /etc/hostname

vi /etc/hostname

mondor

Alternatively - hostnamectl --static set-hostname mondor

if FQDN is not taking effect

systemctl restart systemd-hostnamed

Step 3: Check

[root@host ~]# hostname

[root@host ~]# mondor

[root@host ~]# hostname -f

[root@host ~]# mondor.freeddns.org

Configure Socks 5 - Dante on Ubuntu

apt-get install gcc+ make build-essential curl autoconf automake libtool autotools-dev dpkg-dev fakeroot
apt-get install dpkg debconf debhelper lintian

wget http://www.inet.no/dante/files/dante-1.4.1.tar.gz
tar -xvf dante-1.4.1.tar.gz
cd dante-1.4.1
mkdir /home/dante
./configure --prefix=/home/dante

if u missing
PAM:               Disabled, security/pam_appl.h missing
apt-get install libpam0g-dev
and rerun the configure command

Libwrap:           Disabled, tcpd.h missing
apt-get install libwrap0 libwrap0-dev
and rerun the configure command

BSD Auth:          Disabled, usable bsd_auth.h not found
apt-get install python-dev
and rerun the configure command

make
make install
The binary will be in /home/dante/sbin/sockd (yes it appears to be called sockd now).

/home/dante/sbin/sockd -v
Dante v1.4.1. Copyright (c) 1997 - 2014 Inferno Nettverk A/S, Norway

vi /home/dante/danted.conf

logoutput: /var/log/socks.log

internal: eth0 port = 1080
external: eth0

method: username
user.privileged: root
user.notprivileged: nobody

client pass {
        from: 0.0.0.0/0 to: 0.0.0.0/0
        log: error connect disconnect
}


client block {
        from: 0.0.0.0/0 to: 0.0.0.0/0
        log: connect error
}

pass {
        from: 0.0.0.0/0 to: 0.0.0.0/0
        log: error connect disconnect
}

block {
        from: 0.0.0.0/0 to: 0.0.0.0/0
        log: connect error
}
#dante-server configuration

Save

Launch danted. -f specifies the configuration file and -D makes it run in the background:
/home/dante/sbin/sockd -f /home/dante/danted.conf -D

Check if it is running:
netstat -tulp

To stop dante:
pkill sockd

Create user who need to login and who will not have shell access.
useradd -s /sbin/nologin phil
passwd phil


Make scripts for starting and stoping dante.

vi /home/dante/start-dante.sh
#!/bin/bash
sleep 10
/home/dante/sbin/sockd -f /home/dante/danted.conf -D

Save
chmod +x /home/dante/start-dante.sh   ( to make executable )

vi /home/dante/stop-dante.sh
#!/bin/bash
/usr/bin/pkill sockd

Save
chmod +x /home/dante/stop-dante.sh   ( to make executable )

Setup to run at boot
crontab -e
@reboot /home/dante/start-dante.sh > /dev/null 2>&1   ( You may have to increase the sleep time, if it does not work. )

AWS - EC2 - Change PEM Key

Before starting, save on your instance the key-first-one.pem ( orginal pem file which you get it at configure of the instance first time )

1. From the AWS EC2 Console -> Key Pairs and generate a new key pair (eg: keynew.pem);
Download the generated pem key, and chmod it to 0666

2. Use the new pem key to generate a public key
 $ ssh-keygen -y

 When prompted, enter the path to keynew.pem ( /path/to/keynew.pem ) . This will generate on console not in file
 Touch keynew.pub and paste his content form generated process.

 Tip: Edit keynew.pub and append the key's name so you can identify it easier in the next steps.
 Just append a single space, then a tag/name for the key

3. Add the new public key to your instance

   $ cat /path/to/keynew.pub | ssh -i /path/to/key-firstone.pem user@ip-private-from-aws "cat >> .ssh/authorized_keys"

4. Test the new key by logging in and removing the old/original key

 $ ssh -i /path/to/keynew.pem user@ip-private-from-aws

 $ nano ~/.ssh/authorized_keys

 Find the line containing the "original name" key and remove it.

UPGRADE OPENSSL FROM 0.9.X TO OPENSSL 1.0.2 – UBUNTU

apt-get remove –purge openssl libssl-dev

wget https://www.openssl.org/source/openssl-1.0.2f.tar.gz –no-check-certificate

tar -xvzf openssl-1.0.2f.tar.gz

mv openssl-1.0.2f openssl

cd openssl/

nano openssl.ld

OPENSSL_1.0.0 {

global:

*;

local:

*;

};

OPENSSL_1.0.1 {

} OPENSSL_1.0.0;

OPENSSL_1.0.1d {

} OPENSSL_1.0.1;

OPENSSL_1.0.2 {

} OPENSSL_1.0.1d;

./configure -fPIC shared -Wl,–version-script=/root/openssl/openssl.ld -Wl,-Bsymbolic-functions

make

make install

Check your new version

openssl version -a

and add PATH to env

export OPENSSL_INCLUDE_DIR=/usr/local/ssl/include/

export OPENSSL_LIBRARIES=’/usr/local/ssl/lib/libssl.so;/usr/local/ssl/lib/libcrypto.so’

export LD_LIBRARY_PATH=/usr/local/ssl/lib/


— The End —