In Windows, the local Oracle creates a dblink to connect to the remote mysql, oracledblink

In Windows, the local Oracle creates a dblink to connect to the remote mysql, oracledblink

Preface

In my case, oracle is installed locally (with SQL Developer after installation, you do not need to install instantclient again), and dblink is created to connect to the remote mysql. Some friends may use PL \ SQL locally (instantclient needs to be installed) to connect to remote oracle. After the connection is successful, they create and use dblink to connect to remote mysql, in this case, you must configure the corresponding mysql-odbc in the remote oracle environment. If it still cannot be implemented, let's take a look at my understanding of this process in the article, and then compare it with your own situation to gradually troubleshoot the problem, hoping to help you.

Implementation reference: Oracle connection odbc Data Source http://www.cnblogs.com/mellowsmile/p/5218882.html

Differences between different oracle Installation packages (combined with online answers, you can consolidate and ponder over it. If there is an error, please point it out ):

 

 

Personal Oracle Installation

Oracle Installation Reference Tutorial: https://www.cnblogs.com/hoobey/p/6010804.html

I installed a 32-bit Oracle Database 11g R2 (the reason why I chose 32 bits is that the success stories all use 32 bits. I did not find the basis for the specific selection, if you know anything, please kindly advise. Thank you ~), For the specific path, see

Oracle_home D: \ guowenwen \ product \ 11.2.0 \ dbhome_1. The error message is found in the D: \ guowenwen \ product \ 11.2.0 \ dbhome_1 \ hs \ trace directory.

 

Personal installation records

1. Installation32-bitMysql odbc driver

Https://dev.mysql.com/downloads/connector/odbc/ download path

I chose the. msi package for installation. If you select the zip file, you do not need to install it again. When installing mysql odbc, select 32-bit/64-bit based on the number of digits of your operating system, but the number of digits of the software (Oracle version, I have installed a 32-bit oracle database, so I chose to install a 32-bit mysql odbc. If you do not know how to choose, the 64-bit and 32-bit mysql odbc are installed, you can go to C: \ Program Files (x86) \ Mysql shows the 32-bit odbc and the 64-bit odbc in C: \ Program Files \ MySQL. (The odbc path may be different here, I have provided my installation path to point out that the 32-bit Program is installed in C: \ Program Files (x86), in C: \ Program Files is a 64-bit Program installed, which I have never figured out before)

2. Create32-bitThe system DSN (unicode driver) must be tested successfully.

It should be clear that if 32-bit odbc is installed, a 32-bit system DSN needs to be created.

1] In the Control Panel = "Administrative Tools =" data source (ODBC) = "new system DSN (unicode driver) is 64-bit

2] The system DSN created in the C: \ Windows \ SysWOW64 \ odbcad32.exe path is 32-bit

Note that the Data Source Name here is TESTMYSQL, which will be used in the subsequent configuration. In TCP/IP, enter the IP address of the mysql database you want to remotely connect to. Click Test to Test the database.

3. listener in the Directory D: \ guowenwen \ product \ 11.2.0 \ dbhome_1 \ NETWORK \ ADMIN. add red information to the ora file (if the directory does not contain listener. ora file, create one by yourself. below is all my configuration content)

 

# ORACLE_HOME replacement on demand
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = TESTMYSQL) # Here SID_NAME must be the same as the odbc name created in step 2 (ORACLE_HOME = D:/guowenwen/product/11.2.0/dbhome_1) (PROGRAM = dg4odbc) # Be sure to write dg4odbc) (SID_DESC = (SID_NAME = dg4odbc) (ORACLE_HOME = D: \ guowenwen \ product \ 11.2.0 \ dbhome_1) (PROGRAM = dg4odbc ))
# For me, there is no listener in this directory. in the ora file, I can use SQL Developer to connect to the local oracle database, but I have configured listener. after the ora file, you will not be able to connect to the local oracle # solution is to add the following code block, and then restart the listener.
(SID_DESC = (GLOBAL_DBNAME = ORCL) (ORACLE_HOME = D: \ guowenwen \ product \ 11.2.0 \ dbhome_1) (SID_NAME = ORCL ))) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC) (KEY = PNPKEY) (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521 ))))

4. In the "D: \ guowenwen \ product \ 11.2.0 \ dbhome_1 \ NETWORK \ ADMIN" directory, tnsnames. add red information to the ora file (if the directory does not contain tnsnames. ora file, create one by yourself. below is all my configuration content)

 

# The content here corresponds to listener. ora.
LISTENER_ORCL = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL) TESTMYSQL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521) # IP address of the ORACLE server. I created a dblink on the local oracle server. Therefore, enter localhost (CONNECT_DATA = (SID = TESTMYSQL) for the HOST )) # The SID here is the same as the odbc name in step 2 (HS = OK) # Be sure to add) dg4odbc = (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = localhost) (PORT = 1521) (CONNECT_DATA = (SID = dg4odbc) (HS = OK ))

 

5. Create initXXX in the Directory D: \ guowenwen \ product \ 11.2.0 \ dbhome_1 \ hs \ admin. ora file. The name of XXX must be the same as that of odbc in step 2. In this example, create initTESTMYSQL. add the following code to the ora file:

HS_FDS_CONNECT_INFO = TESTMYSQL # note the HS_FDS_TRACE_LEVEL = debug # debug status. If a connection error occurs, you can see the error message in path D: \ guowenwen \ product \ 11.2.0 \ dbhome_1 \ hs \ trace.

 

6. Restart the listener.

Two options: graphical interface operation and cmd operation.

Right-click cmd and run it as Administrator (otherwise The prompt might be: TNS-01190: the user is not authorized to execute The requested listener command), enter the following command (both must be executed successfully, find a solution if there is an error)

C:\Users\guowenwen>lsnrctl stop

C:\Users\guowenwen>lsnrctl start

C:\Users\guowenwen>tnsping MYSQL_LOCAL

 

7. Open SQL Developer and create DBLINK

If an error occurs when dblink is used, view the error message of Oracle_HOME \ hs \ trace. If Access denied for users 'mysql username' @ 'mysql IP address 'is displayed ', the possible cause is that "" is not added to the user name and password during dblink creation.

Create database link MYSQL_LOCAL connect to "mysql user" identified by "mysql user password" using 'mysql _ local'; // The name after using should be the same as the odbc name in step 2.

 

8. Use DBLINK

Select * from "table name" @ MYSQL_LOCAL;

 

Personal principles

Reference: oracle HS deep parsing and collaborative Gateway workflow http://www.cnblogs.com/wwwiori/archive/2009/06/22/1508068.html

Key points (for my personal understanding, I have not studied it in depth. If there are any errors, please kindly advise them): oracle -- dg4odbc -- odbc -- mysql

When dblink is used on a local oracle Database (Step 7 and 8), oracle connects to HS and HS searches for the configured Agent Componet Code in the corresponding directory (step 5 ), because I directly installed Oracle database (ODBC Agent installed by default), the corresponding Agent Componet codeis dg4odbcc.exe (corresponding to step 3, 4, which is also the reason for setting PROGRAM = dg4odbc in step 4 ), agent Componet Code: Find the Driver (the Driver of the database to be connected, that is, the operation in step 1). After connecting to the Driver, the Driver connects to the database (that is, step 2, the meaning is to let the Driver know how to connect to the corresponding DB), the remote DB receives commands from the local Oracle, runs and returns them step by step.

Summary

In the process of implementing the requirements, when searching for the solution to the problem, I found that some articles say that Database Gateways are needed (for example, sqlserver ), at that time, I didn't know whether the connection to mysql was successful because I didn't install Gateways (although I do not need to connect to mysql now), so I went to learn about them. However, the problem is that I have been searching for the download path of gateways for a long time, let alone others. Now I have posted relevant links and provided some reference to friends who need it to reduce the search time.

Gateway reference: http://www.cnblogs.com/tuyile006/p/4142395.html

Oracle database gateway for odbc: https://docs.oracle.com/database/122/ODBCU/database-gateway-for-odbc-introduction.htm#ODBCU100

 

Finally, if you find any problems during your reading, please kindly advise me. Thank you ~~