Oracle 配置透明网关访问 MySQL 详细教程

cd /soft
./OracleShellInstall.sh -i 10.211.55.102 -txh Y
1.
2.

## 首先 root 用户下，授权 oracle 访问 /soft 目录
chown -R oracle:oinstall /soft
## 切换到 oracle 用户下
su - oracle
## 解压透明网关安装包
unzip -q p13390677_112040_Linux-x86-64_5of7.zip
## 配置 vnc 访问图形化界面
vncserver
1.
2.
3.
4.
5.
6.
7.
8.

/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/11.2.0/db/root.sh
1.
2.

yum install -y unixODBC*
rpm -ivh mysql-connector-odbc-8.0.27-1.el7.x86_64.rpm
1.
2.

cat<<EOF>/etc/odbc.ini
[lucifer]
Description     = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc8w.so
Server          = 10.211.55.101
Port            = 3306
User            = lucifer
Password        = lucifer
Database        = lucifer 
EOF
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

[lucifer]
Description     = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc8w.so
Server          = 10.211.55.101
Port            = 3306
User            = lucifer
Password        = lucifer
Database        = lucifer 
[lucifer1]
Description     = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc8w.so
Server          = 10.211.55.105
Port            = 3306
User            = lucifer1
Password        = lucifer1
Database        = lucifer1
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

cat<<EOF>$ORACLE_HOME/hs/admin/initlucifer.ora
##HS Configuration
HS_FDS_CONNECT_INFO = lucifer
HS_FDS_TRACE_LEVEL = debug
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.UTF8
HS_NLS_NCHAR = UCS2
##ODBC Configuration
set ODBCINI=/etc/odbc.ini
EOF
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

cat<<EOF>>$TNS_ADMIN/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC=
  (SID_NAME=lucifer)
  (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db)
  (PROGRAM=dg4odbc)
  )
)
EOF
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

cat<<EOF>>$TNS_ADMIN/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
  (SID_NAME = lucifer)
  (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db)
  (PROGRAM=dg4odbc)
  )
  (SID_DESC =
  (SID_NAME = lucifer1)
  (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db)
  (PROGRAM=dg4odbc)
  )
)
EOF
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

lsnrctl stop
lsnrctl start
lsnrctl status
1.
2.
3.

isql lucifer
1.

cat<<EOF>>$TNS_ADMIN/tnsnames.ora
lucifer =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.211.55.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = lucifer)
    )
   (HS = OK)
  )
EOF
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

create public database link lucifer connect to "lucifer" identified by "lucifer" using 'lucifer';
1.

select * from "lucifer"@lucifer order by "id";
delete from "lucifer"@lucifer where "id"=1;
1.
2.

ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from DLK
1.
2.
3.

ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[
1.
2.
3.

ERROR at line 1:
ORA-00942: table or view does not exist
[MySQL][ODBC 8.0(w) Driver][mysqld-5.7.18-log]Table 'test.T1' doesn't exist
{42S02,NativeErr = 1146}
ORA-02063: preceding 2 lines from DLK
1.
2.
3.
4.
5.

ERROR at line 1:
ORA-28546: connection initialization failed, probable Net8 admin error
ORA-02063: preceding line from LUCIFER
1.
2.
3.