Access to MySql from Oracle

A. Introduction
According to Oracle note, Oracle Database Gateway for ODBC is intended for low-end data integration solutions requiring the dynamic query capability to connect from an Oracle database to non-Oracle systems. Any data source compatible with the ODBC standards described in this chapter can be accessed using Oracle Database Gateway for ODBC.


Figure 1-1 shows an example of a configuration in which an Oracle and non-Oracle database are on separate machines, communicating through Oracle Database Gateway for ODBC. The client connects to the non-Oracle system through a network.

Figure 1-1 Oracle and Non-Oracle Systems on a Separate Machines

Description of "Figure 1-1 Oracle and Non-Oracle Systems on a Separate Machines"

complete article :
http://docs.oracle.com/cd/B28359_01/gateways.111/e10311/intro.htm#CEGCBAIE.




B. Installation
You need to install an ODBC driver to use Oracle Database Gateway for ODBC.
In my case, i have downloaded  mysql-connector-odbc-5.1.10-1.rhel5.x86_64.rpm from //www.mysql.org
and installed it on my Oracle server (RedHat x86_64), /usr/lib64.




C. Environnement
Oracle host      : Linux Redhat x86_64
Oracle version : 11.2.0.1


Mysql server  : Linux Redhat x86_64
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name                     | Value                     |
+-------------------------+---------------------+
| protocol_version                  | 10                          |
| version                                 | 5.1.47                    |
| version_comment                 | Source distribution  |
| version_compile_machine     | x86_64                  |
| version_compile_os              | redhat-linux-gnu     |
+-------------------------+---------------------+




D. Set up

 1.  /etc/odbc.ini

Using the root user, i have to instantiate the odbc.ini

I'm be using the [mysql] DSN entry.

[mysql]
Driver                         =  /usr/lib64/libmyodbc5.so
UsageCount               = 1
DATABASE               = my_mysql_db 
PORT                         = 3306
SERVER                    = @IP MySql Host
USER                         = mysl_user
PASSWORD              = pwd_mysl_user
CHARSET                 = utf8 ---------------> used while creating tables on  my_mysl_db 
TRACE                      = OFF
TDS_Version             = 8

2. Test the MySql ODBC Driver
You can easily test the MySql driver using the unixODBC tool : isql
[oracle@xxxx etc]$ isql mysql -v

+---------------------------------------+

| Connected!                                |

|                                                  |

| sql-statement                             |

| help [tablename]                        |

| quit                                           |

|                                                 |

+---------------------------------------+




2. Configure  dg4odbc process.


2.1 The listener needs a new entry :

SID_DESC =      
         (SID_NAME=dg4odbc)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
         (PROGRAM=dg4odbc)                     
           (ENVS=LD_LIBRARY_PATH=              
            /u01/app/oracle/product/11.2.0/dbhome_1/   
            :/usr/lib64/:/usr/lib/
            :/u01/app/oracle/product/11.2.0/dbhome_1/lib)
           )     


[oracle@xxxx etc]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-MAR-2012 13:44:22
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521)))
The command completed successfully
[oracle@xxx etc]$

2.2 The tnsnames needs a new entry : 

dg4odbc=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521))
    (CONNECT_DATA=(SID=dg4odbc))
    (HS=OK)
  ) 

The (HS=OK) parameter must be outside the SID section and specifies that this connector 

uses the Oracle Heterogeneous Service Option.

[oracle@SRVD-SSM etc]$ tnsping dg4odbc
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 23-MAR-2012 13:45:04
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521)) (CONNECT_DATA=(SID=dg4odbc)) (HS=OK))
OK (0 msec).


2.3 init.ora

The SID is also relevant for the init.ora file of the gateway. The name of the file is init.ora.  In this example it is called  initdg4odbc.ora.

The file is located at $ORACLE_HOME/hs/admin.

/u01/app/oracle/product/11.2.0/dbhome_1/hs/admin/initdg4odbc.ora :
HS_FDS_CONNECT_INFO=mysql ----------> odbc.ini    [mysql]
 HS_FDS_TRACE_LEVEL=0
HS_FDS_SHAREABLE_NAME=/usr/lib64/libmyodbc5.so
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
HS_FDS_SQLLEN_INTERPRETATION=64
set ODBCINI=/etc/odbc.ini
set ODBCSYSINI=/etc

2.4 Configuring the environment:
I create a database link from my oracle user to the mysql user :



create database link linkmysql
  connect to "mysl_user"         -------------------> odbc.ini    USER = mysl_user
  identified by "pwd_mysl_user" -------------------> odbc.ini    PASSWORD  = pwd_mysl_user
  using 'dg4odbc';

Username and password must be in double quotes !!


2.5 Test
in the mysql database, i create the table my_sql_table (id number, dest char(5)), and i insert the following data :  
id             dest
----          ----
1              dest1
2              dest2

 from my oracle user, i execute : select * from  " my_sql_table"@ linkmysql;

SQL> select * from " my_sql_table"@linkmysql;
ERROR:
ORA-28528: Heterogeneous Services datatype conversion error
ORA-02063: preceding line from LINKMYSQL
no rows selected
SQL>


To avoid the ORA-28528 error add to the initdg4odbc.ora :  HS_FDS_FETCh_ROWS=1
Metalink note [ID 1080048.1].


connect with a new oracle session :

SQL> select * from " my_sql_table"@linkmysql;

id             dest
----          ----
1              dest1
2              dest2  

SQL>

             

Oracle active Data Guard for standby database : RMAN

You can use Oracle active Data Guard to create a physical standby database by using RMAN backups.
For my example, the primary database is STATTAR (host=hots1), and the standby is STATSTDB (host=hots2).
The OS is linux x86_64 and the oracle version is 11.2.0.1.

I. Set up Standby database

1. Ensure that  STATTAR  is in archivelog mode, otherwise convert it.

2. Enable the logging mode

SQL> ALTER DATABASE FORCE LOGGING;

3. do a complete backup of STATTAR
export ORACLE_SID=STATTAR
rman target /
RMAN> backup database plus archivelog;


4. tnsnames
STATSTDB  =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STATSTDB)
    )
  )

5. listner (host2)
(SID_DESC =
       (GLOBAL_DBNAME =  STATSTDB )
       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME      =  STATSTDB )
     )
 
Check with the SQL*Net configuration using the following commands on the Primary AND Standby


from host1 server, check the communication beetween the 2 databases :

tnsping  STATSTDB
tnsping STATTAR

6. on host2, create the directories  for data, controlfile and redo log files
 mkdir -p /u01/app/oracle/admin/ STATSTDB /adump
 mkdir -p /u01/app/oracle/admin/ STATSTDB /dpdump
 mkdir -p /u01/app/oracle/admin/ STATSTDB /pfile
 mkdir -p /u01/app/oracle/admin/ STATSTDB /scripts
 mkdir -p /u02/oracle/oradata/ STATSTDB /controlfile/
 mkdir -p /u03/oracle/oradata/ STATSTDB /controlfile/
 mkdir -p /u04/oracle/oradata/ STATSTDB /controlfile/
 mkdir -p /u02/oracle/oradata/ STATSTDB /data_system
 mkdir -p /u02/oracle/oradata/ STATSTDB /data
 mkdir -p /u02/oracle/oradata/ STATSTDB /data
 mkdir -p /u03/oracle/oradata/ STATSTDB /data
 mkdir -p /u04/oracle/oradata/ STATSTDB /data
 mkdir -p /u02/oracle/oradata/ STATSTDB /redo
 mkdir -p /u03/oracle/oradata/ STATSTDB /redo
 mkdir -p /u04/oracle/oradata/ STATSTDB /redo


7. create pfile for standby (host2)
db_name=STATTAR
db_unique_name=STATSTDB
REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
db_block_size=8192
memory_max_target=1500M
memory_target=1500M

8. create pwdfile : the same as the target DB (host2)
orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapw STATSTDB  password=<syspwd>



9. startup  STATSTDB  in nomount mode
  export ORACLE_SID=STATSTDB
  sqlplus / as sysdba
  SQL> startup nomount pfile='/u01/app/oracle/admin/STATSTDB/pfile/STATSTDB_init.ora';

10. Create stanby redo log on the on primary db STATTAR

SQL> alter database add standby logfile group 4 ('/u02/oracle/oradata/ STATTAR /redostdb.ora') size 50M;

11. Modify the primary initialization parameter for dataguard on primary db STATTAR
 -- primary DB

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(STATTAR,STATSTDB)';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u06/oracle/oradata/STATTAR/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STATTAR';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=STATSTDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STATSTDB';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.

SQL> alter system set FAL_SERVER=STATSTDB;
System altered.

SQL> alter system set FAL_CLIENT=STATTAR;
System altered.

SQL> alter system set DB_FILE_NAME_CONVERT= '/u02/oracle/oradata/STATSTDB/redo',
                          '/u02/oracle/oradata/STATTAR/redo',
                          '/u03/oracle/oradata/STATSTDB/redo',
                          '/u03/oracle/oradata/STATTAR/redo',
'/u04/oracle/oradata/STATSTDB/redo',
                          '/u04/oracle/oradata/STATTAR/redo' scope=spfile;
System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT= '/u02/oracle/oradata/STATSTDB/data_system',
                        '/u02/oracle/oradata/STATTAR/data_system',
'/u02/oracle/oradata/STATSTDB/data',
          '/u02/oracle/oradata/STATTAR/data',
'/u03/oracle/oradata/STATSTDB/data',
        '/u03/oracle/oradata/STATTAR/data',
'/u04/oracle/oradata/STATSTDB/data',
          '/u04/oracle/oradata/STATTAR/data' scope=spfile;
System altered.


12. on primary host (host1) invoke RMAN :
 rman target sys/<password>@STATTAR

RMAN>
connect auxiliary sys/<password>@STATSTDB
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;

duplicate target database for standby from active database
spfile
  parameter_value_convert 'STATTAR','STATSTDB'
  set db_unique_name='STATSTDB'
  set db_file_name_convert='/STATTAR/','/STATSTDB/'
  set log_file_name_convert='/STATTAR/','/STATSTDB/'
  set control_files='/u02/oracle/oradata/STATSTDB/controlfile/control01.ctl','/u03/oracle/oradata/STATSTDB/controlfile/control02.ctl','/u04/oracle/oradata/STATSTDB/controlfile/control03.ctl'
  set log_archive_max_processes='5'
  set fal_client='STATSTDB'
  set fal_server='STATTAR'
  set standby_file_management='AUTO'
  set log_archive_config='dg_config=(STATTAR,STATSTDB)'
  set log_archive_dest_1='service=STATTAR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=STATTAR'
;
}

...

 ''STATMAC'' comment=
 '''' scope=spfile";
   sql clone "alter system set  standby_file_management =
 ''AUTO'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_config =
 ''dg_config=(STATMAC,STATSTDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''service=STATMAC ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=                                                                                                                                                              STATMAC'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/STAT                                                                                                                                                              STDB/adump'' comment= '''' scope=spfile

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=STATST                                                                                                                                                              DBXDB)'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''STATSTDB'' comment= '''' sc                                                                                                                                                              ope=spfile

sql statement: alter system set  db_file_name_convert =  ''/STATMAC/'', ''/STATS                                                                                                                                                              TDB/'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/STATMAC/'', ''/STAT                                                                                                                                                              STDB/'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''/u02/oracle/oradata/STATSTDB                                                                                                                                                              /controlfile/control01.ctl'', ''/u03/oracle/oradata/STATSTDB/controlfile/control                                                                                                                                                              02.ctl'', ''/u04/oracle/oradata/STATSTDB/controlfile/control03.ctl'' comment= ''                                                                                                                                                              '' scope=spfile

sql statement: alter system set  log_archive_max_processes =  5 comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''STATSTDB'' comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''STATMAC'' comment= '''' scope=spfile

sql statement: alter system set  standby_file_management =  ''AUTO'' comment= ''                                                                                                                                                              '' scope=spfile

sql statement: alter system set  log_archive_config =  ''dg_config=(STATMAC,STAT                                                                                                                                                              STDB)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''service=STATMAC ASYNC v                                                                                                                                                              alid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=STATMAC'' comment= '''' sc                                                                                                                                                              ope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1570009088 bytes

Fixed Size                     2213696 bytes
Variable Size                905971904 bytes
Database Buffers             637534208 bytes
Redo Buffers                  24289280 bytes
allocated channel: stby
channel stby: SID=63 device type=DISK

....

input datafile copy RECID=156 STAMP=778006506 file name=/u03/oracle/oradata/STATSTDB/data/TBS_FMA_DATA_01.ora

datafile 49 switched to datafile copy

input datafile copy RECID=157 STAMP=778006506 file name=/u03/oracle/oradata/STATSTDB/data/TBS_FMA_DATA_02.ora

Finished Duplicate Db at 15-MAR-12

released channel: prmy1

released channel: prmy2

released channel: prmy3

released channel: prmy4

released channel: stby

exit;

II. Manage Standby database

1. Stop the managed recovery process and open STATSTDB: 
export ORACLE_SID=STATSTDB
  sqlplus / as sysdba
SQL>  select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 STATSTDB
pprod_db.local
11.2.0.1.0        15-MAR-12 MOUNTED      NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> SQL> alter database open;
Database altered.
SQL>

2. Restart the managed recovery process

SQL> alter database recover managed standby database 
             using current logfile disconnect;

3. Test the active data guard:
on the primary database for user SCOTT:

create table my_test (id number);
insert into my_test(id) vlaues (1);
commit;
witth sys user : alter system switch logfile;

on the auxiliary database :
the table my_test is created with one record.

Duplicate Database on the Same Host Using RMAN

You can use RMAN to create a duplicate database on the same server by using RMAN backups.
For my example, the target database is STATTAR, and the duplicate is STATDUP.
The OS is linux x86_64 and the oracle version is 11.2.0.1.

1. Ensure that  STATTAR  is in archivelog mode, otherwise convert it.

2. Enable the logging mode

SQL> ALTER DATABASE FORCE LOGGING;

3. do a complete backup of STATTAR
export ORACLE_SID=STATTAR
rman target /
RMAN> backup database plus archivelog;


4. tnsnames 
STATDUP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STATDUP)
    )
  )

5. listner
(SID_DESC =
       (GLOBAL_DBNAME = STATDUP)
       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME      = STATDUP)
     )
   
6. create the directories  for data, controlfile and redo log files
 mkdir -p /u01/app/oracle/admin/STATDUP/adump
 mkdir -p /u01/app/oracle/admin/STATDUP/dpdump
 mkdir -p /u01/app/oracle/admin/STATDUP/pfile
 mkdir -p /u01/app/oracle/admin/STATDUP/scripts
 mkdir -p /u02/oracle/oradata/STATDUP/controlfile/
 mkdir -p /u03/oracle/oradata/STATDUP/controlfile/
 mkdir -p /u04/oracle/oradata/STATDUP/controlfile/
 mkdir -p /u02/oracle/oradata/STATDUP/data_system
 mkdir -p /u02/oracle/oradata/STATTAR/data
 mkdir -p /u02/oracle/oradata/STATDUP/data
 mkdir -p /u03/oracle/oradata/STATDUP/data
 mkdir -p /u04/oracle/oradata/STATDUP/data
 mkdir -p /u02/oracle/oradata/STATDUP/redo
 mkdir -p /u03/oracle/oradata/STATDUP/redo
 mkdir -p /u04/oracle/oradata/STATDUP/redo



7. create pfile 
db_name=STATDUP
db_block_size=8192
compatible='11.2.0.0.0'
remote_login_passwordfile=exclusive
control_files = ('/u02/oracle/oradata/STATDUP/controlfile/control01.ctl',
                 '/u03/oracle/oradata/STATDUP/controlfile/control01.ctl',
                 '/u04/oracle/oradata/STATDUP/controlfile/control01.ctl')
db_file_name_convert = ('/u02/oracle/oradata/STATTAR/data_system',
                        '/u02/oracle/oradata/STATDUP/data_system',
'/u02/oracle/oradata/STATTAR/data',
         '/u02/oracle/oradata/STATDUP/data',
'/u03/oracle/oradata/STATTAR/data',
         '/u03/oracle/oradata/STATDUP/data',
'/u04/oracle/oradata/STATTAR/data',
         '/u04/oracle/oradata/STATDUP/data'
                        )
log_file_name_convert = ('/u02/oracle/oradata/STATTAR/redo',
                         '/u02/oracle/oradata/STATDUP/redo',
                         '/u03/oracle/oradata/STATTAR/redo',
                         '/u03/oracle/oradata/STATDUP/redo',
'/u04/oracle/oradata/STATTAR/redo',
                         '/u04/oracle/oradata/STATDUP/redo'
)


8. create pwdfile : the same as the target DB
orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwSTATDUP password=<syspwd>



9. startup STATDUP in nomount mode
  export ORACLE_SID=STATDUP
  sqlplus / as sysdba
  SQL> startup nomount pfile='/u01/app/oracle/admin/STATDUP/pfile/STATDUP_init.ora';


10. duplicate the target DB
 export ORACLE_SID=STATDUP
 rman target sys/<syspwd>@STATTAR auxiliary/

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 6 10:46:38 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: STATTAR (DBID=2026*****)
connected to auxiliary database: STATDUP (not mounted)

RMAN > duplicate target database to STATDUP;

Starting Duplicate Db at 06-MAR-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=57 device type=DISK

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     233861120 bytes

Fixed Size                     2212088 bytes
Variable Size                159387400 bytes
Database Buffers              67108864 bytes
Redo Buffers                   5152768 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''STATTAR'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''STATDUP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''STATTAR'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''STATDUP'' comment= ''Modified by RMAN duplicate'' scope=spfile

.....

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 06-MAR-12


exit;