Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you about how oracle is connected to mysql through dblink. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

This week, because the oracle database of one system of the company needed to collect data from the mysql database of another system, we studied the connection of oracle to mysql through dblink. The test environment was a single instance oracle 10g 64-bit, which took a long time to complete, but the production environment was 10g rac, which was supposed to be smooth, but it didn't work out for more than a day. The problem mainly lies in the monitoring. I originally planned to give up and use middleware to connect MySQL to get the data. However, intuitively, it should be caused by the difference between the listening configuration of single instance and rac, so create a default listener on the rac node separately, and the problem is solved.

The following error is reported when there is a problem with the configuration:

ORA-28545: error diagnosed by Net8 when connecting to an agent

Unable to retrieve text of NETWORK/NCR message 65535

The main problems and solutions encountered are:

1. There should be a problem with oracle 10g 64-bit hsodbc. The file size is 0 and there is no information to execute hsodbc.

Solution: copy the hsodbc under the 32 machine to the $ORACLE_HOME/bin directory and replace the original hsodbc. Note that oracle must have permission. Download: http://space.itpub.net/?uid-28321441-action-viewspace-itemid-750733

2. Rac snooping cannot listen to hsodbc service

Solution: netca chooses single node configuration to create snooping to listen to hsodbc service, and listens port 1522

3. Connect mysql to query data garbled.

Solution: edit / etc/odbc.ini, add

Charset = gbk

STMT = SET NAMES 'GBK'

Where gbk is the MYSQL character set.

The specific implementation steps are as follows:

-- oracle server:

-operating system: Red Hat Enterprise Linux 5.3

-- Database: Oracle RDBMS 10.2.0.3.0

-- mysql server:

-operating system: windows server 2003

-- Database: MySQL 5.5

The operations of a single instance are as follows, and all operations are completed on the oracle server:

-- check the required rpm package. The required package is as follows. Since hsodbc only supports 32-bit, 32-bit package is required:

Libtool-ltdl-1.5.22-6.1.i386.rpm

Libtool-ltdl-1.5.22-6.1.x86_64.rpm

Mysql-5.0.77-3.el5.i386.rpm

Mysql-5.0.77-3.el5.x86_64.rpm

Mysql-connector-odbc-3.51.26r1127-1.el5.i386.rpm

Mysql-connector-odbc-3.51.26r1127-1.el5.x86_64.rpm

Perl-DBI-1.52-2.el5.x86_64.rpm

UnixODBC-2.2.11-7.1.i386.rpm

UnixODBC-2.2.11-7.1.x86_64.rpm

The above unixODBC-2.2.11-7. 1, mysql-5.0.77-3.el5, mysql-connector-odbc are required packages, and others are prerequisites for installing these packages.

Check after installation

# rpm-qa | grep unixODBC

UnixODBC-2.2.11-7.1

UnixODBC-2.2.11-7.1

# rpm-qa | grep mysql

Mysql-5.0.45-7.el5

Mysql-5.0.45-7.el5

Mysql-connector-odbc-3.51.26r1127-1.el5

Mysql-connector-odbc-3.51.26r1127-1.el5

-- Editor / etc/odbc.ini

Vi / etc/odbc.ini

# [ODBC Data Sources]

# myodbc3 = MySQL ODBC 3.51 Driver DSN

[test]

Driver = / usr/lib/libmyodbc3.so

Description = MySQL ODBC 3.51 Driver DSN

SERVER = 24.1.20.149

PORT = 3306

USER = root

Password = laopo

Database = teng_push

OPTION = 3

SOCKET =

Charset = gbk

STMT = SET NAMES 'GBK'

The last two are to solve the problem of Chinese garbled code. Gbk is the MYSQL character set.

-- switch to oracle user

# su-oracle

Edit the environment variable configuration file, mainly LD_LIBRARY_PATH and the last two items

$vi ~ / .bash_profile

Export ORACLE_BASE=/oracle

Export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

Export ORACLE_SID=orcl

Export ORACLE_TERM=xterm

Export PATH=/usr/sbin:$PATH

Export NLS_LANG= "SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

# export LANG=zh_CN.GB18030

Export PATH=.:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:/bin:/usr/bin:/usr/sbin

: / usr/local/bin:/usr/X11R6/bin

Export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:/lib:/usr/lib

Export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

# ulimit-u 16384-n 65536

Umask 022

Export EDITOR=vi

ODBCINI=/etc/odbc.ini; export ODBCINI

ODBCSYSINI=/etc; export ODBCSYSINI

-- make the configuration effective

Source / .bash_profile

-- View odbc version and parameter file path

$odbcinst-j

UnixODBC 2.2.11

DRIVERS.: / usr/local/etc/odbcinst.ini

SYSTEM DATA SOURCES: / usr/local/etc/odbc.ini

USER DATA SOURCES..: / usr/local/etc/odbc.ini

-- Test My SQL ODBC driver

$isql test-v (Driver= / usr/lib/libmyodbc3.so in / etc/odbc.ini needs to be modified to / usr/lib64/libmyodbc3.so before testing, and restored after testing)

Isql test oracle manager-v

+-+

| | Connected! |

| | |

| | sql-statement |

| | help [tablename] |

| | quit |

| | |

+-+

SQL > quit

-- configure HSODBC programs

Vi $ORACLE_HOME/hs/admin/inittest.ora

HS_FDS_CONNECT_INFO = test

HS_FDS_TRACE_LEVEL = off

HS_FDS_SHAREABLE_NAME=/usr/lib/libmyodbc3.so

Set DBCINI=/etc/odbc.ini

-- make sure that the configuration of hsodbc is correct. If correct, you can see the version number.

-- Note: if it is a 64-bit oracle, since hsodbc only supports 32 bits, you need to copy the hsodbc on the 32-bit machine to $ORACLE_HOME/bin to replace the original hsodbc.

$hsodbc

Oracle Corporation-FRIDAY DEC 06 2012 12 14 20 14 40 40

Heterogeneous Agent Release 10.2.0.3.0-Production Built with

Driver for ODBC

-- modify the listening file and add the following services:

(SID_DESC =

(PROGRAM = hsodbc)

(ORACLE_HOME = / oracle/product/10.2.0/db_1)

(SID_NAME = test)

(ENVS=LD_LIBRARY_PATH = / oracle/product/10.2.0/db_1/lib32:/usr/lib:/oracle

/ product/10.2.0/db_1/lib)

)

$vi $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = / oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(PROGRAM = hsodbc)

(ORACLE_HOME = / oracle/product/10.2.0/db_1)

(SID_NAME = test)

(ENVS=LD_LIBRARY_PATH = / oracle/product/10.2.0/db_1/lib32:/usr/lib:/oracle

/ product/10.2.0/db_1/lib)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))

(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0))

)

)

-- modify tnsnames.ora

$vi $ORACLE_HOME/network/admin/tnsnames.ora

Test =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))

)

(CONNECT_DATA =

(SID= test)

)

(HS=OK)

)

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

-- restart monitoring, with test service required

$lsnrctl stop

$lsnrctl start

Services Summary...

Service "PLSExtProc" has 1 instance (s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler (s) for this service...

Service "orcl" has 1 instance (s).

Instance "orcl", status UNKNOWN, has 1 handler (s) for this service...

Service "test" has 1 instance (s).

Instance "test", status UNKNOWN, has 1 handler (s) for this service...

The command completed successfully

-- tnsping testing service

$tnsping test

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 24.1.20.30) (PORT = 1521)) (CONNECT_DATA = (SID= test)) (HS=OK)

OK (0 msec)

-- create a dblink

CREATE PUBLIC DATABASE LINK mysql CONNECT TO "root" IDENTIFIED BY "laopo" USING 'test'

-- testing

Select * from "emp" @ mysql where "id" = 1

At this point, the single node configuration is complete.

Each node in the rac environment needs to be configured, and the only difference from a single instance is the configuration of listening. I tried to configure listening in many ways, but none of them listened to hsodbc's test service. Finally, it was successful to listen to test service by selecting single node configuration to create listening through netca. Note that the listening port is changed to 1522.

The listening file for rac environment node 1 is as follows:

$vi $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER_RAC01 =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = / opt/ora10g/product/10.2.0/db_1)

(PROGRAM = extproc)

)

)

LISTENER_RAC01 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = rac01-vip) (PORT = 1521) (IP = FIRST))

(ADDRESS = (PROTOCOL = TCP) (HOST = 24.1.20.246) (PORT = 1521) (IP = FIRST))

)

)

SID_LIST_LISTENER1 =

(SID_LIST =

(SID_DESC =

(PROGRAM=hsodbc)

(ORACLE_HOME=/opt/ora10g/product/10.2.0/db_1)

(SID_NAME=test)

(ENVS=LD_LIBRARY_PATH=/opt/ora10g/product/10.2.0/db_1/lib32:/lib:/usr/lib:

/ opt/ora10g/product/10.2.0/db_1/lib)

)

)

LISTENER1 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 24.1.20.246) (PORT = 1522))

)

)

This is how the oracle shared by Xiaobian is implemented through dblink connecting mysql. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.