1.1 Empowering (assigning CRH_SNP users the ability to create Dblink permissions specifically)
Under the SYS user, grant the Create public database Link,drop public database LINK permission to your users
Grant CREATE Public Database Link,drop publicly database LINK to CRH_SNP;
1.2 Specific creation of Db_link
Then log in to the local database as a crh_snp user, performing the following names
Create DATABASE linkdcrac2
Connect to WLYXPT identified by WLYXPT
Using ' (DESCRIPTION =
(Address_list =
(address = (protocol = TCP) (host = 192.19.0.81) (port = 1521))
)
(Connect_data =
(service_name = Dcrac)
(instance_name = DCRAC2)
(Failover_mode =
(Backup = DCRAC1)
(type = Select)
(method = Preconnect)
)
)
)‘;
---Description: The Db_link name created is: DCRAC2, the other Oracle database user name is WLYXPT, password--wlyxpt, the service name is Dcrac, because it is a RAC database The blue Tnsname configuration can be given by the broker-related DBA
1.3 to CRH_SNP Querying ods_ufs.his_his_delive table Data (OBS-owned tables, WLYXPT users have appropriate permissions)
SELECT * [email protected];
2. Oracle connects MySQL via Dblink
Oracle: System Redhat 5.5 database: 11.2.0.1
MySQL: System centos6.3 database: 5.6.21
2.1 First MYSQL-CONNECTOR-ODBC and UNIXODBC
[Email protected] ~]# rpm-qa | grep mysql
mysql-5.0.77-4.el5_4.2
mysql-5.0.77-4.el5_4.2
Mysql-connector-odbc-5.1.13-1.rhel5
[Email protected] ~]# rpm-qa | grep ODBC
unixodbc-devel-2.2.11-7.1
unixodbc-2.2.11-7.1
unixodbc-devel-2.2.11-7.1
unixodbc-2.2.11-7.1
2.2. Configure/etc/odbc.ini
[MYODBC3]
Driver =/usr/lib64/libmyodbc5.so
Description = MySQL ODBC 5.1 Driver DSN
SERVER = 192.1.1.200
PORT = 3306
USER = Bi
Password = 123456
Database = Chanpin
OPTION = 3
SOCKET =
charset = UTF8
2.3. Configure/etc/odbcinst.ini
[MySQL]
Description = ODBC for MySQL
Driver =/usr/lib64/libmyodbc5.so
Setup =/usr/lib64/libodbcmys.so
FileUsage = 1
2.4. Test the connection
[Email protected] ~]# isql myodbc3-v
+---------------------------------------+
| connected! |
| |
| sql-statement |
| Help [TableName] |
| Quit |
| |
+---------------------------------------+
Sql>
2.5. Configure Oracle Environment variables
Export Oracle_base=/u01/app/oracle
Export Grid_home=/u01/grid
Export Oracle_home= $ORACLE _base/product/11.2.0/db_1
Export ORACLE_SID=RAC1
Export Base_path=/usr/sbin: $PATH
Export path= $ORACLE _home/bin: $BASE _path:/usr/bin
Export ld_library_path= $ORACLE _home/lib:/lib:/usr/lib:/usr/local/lib: $ORACLE _home/hs/lib:/usr/lib64
Export classpath= $ORACLE _home/jre: $ORACLE _home/jlib: $ORACLE _home/rdbms/jlib
Odbcini=/etc/odbc.ini; Export Odbcini
ODBCSYSINI=/ETC; Export Odbcsysini
Odbcinstini=/etc/odbc.ini
Export Odbcinstini
2.6. Configuring monitoring
Listener.ora file:
LISTENER =
(Description_list =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.1.1.100) (PORT = 1522))
)
)
Sid_list_listener=
(sid_list=
(sid_desc=
(program = DG4ODBC)
(Sid_name= MYODBC3)
(Oracle_home=/u01/app/oracle/product/11.2.0/db_1)
(envs=ld_library_path=/u01/app/oracle/product/11.2.0/db_1/lib:/lib:/usr/lib:/usr/local/lib:/u01/app/oracle/ PRODUCT/11.2.0/DB_1/HS/LIB:/USR/LIB64)
)
)
Tnsname.ora file:
myodbc3=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.1.1.100) (PORT = 1522))
(Connect_data =
(SID = MYODBC3))
(HS = OK)
)
2.7. Configuring ODBC Monitoring
Path: $ORACLE _home/hs/admin
Note: The name needs to be the same as the ODBC configuration. I'm MYODBC3 here.
[[email protected] admin] $cd $ORACLE _home/hs/admin
[email protected] admin]$ cat Initmyodbc3.ora
Hs_fds_connect_info = MYODBC3
Hs_fds_trace_level = On
Hs_fds_trace_file_name = Odbc_test.log
Hs_fds_trace_level = 4
Hs_fds_shareable_name =/usr/lib64/libodbc.so
Hs_fds_support_statisctics = FALSE
Hs_language = American_america. We8iso8859p1
#HS_FDS_SQLLEN_INTERPRETATION =32
Set Odbcini =/etc/odbc.ini
2.8. Test monitoring
[Email protected] admin]$ tnsping MYODBC3
TNS Ping Utility for linux:version 11.2.0.1.0-production on 25-nov-2014 03:02:16
Copyright (c) 1997, Oracle. All rights reserved.
Used parameter files:
Used TNSNames Adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.1.1.100) (PORT = 1522)) (Connect_data = (SID = MYODBC3)) (HS = OK))
OK (0 msec)
2.9. Create Dblink
Sql>create Public Database link MYODBC connect to "BI" identified by "123456" using ' MYODBC3 ';
Sql>select Count (*) from "T_user" @myodbc;
COUNT (*)
----------
53980
3. Delete Dblink
Droppublicdatabaselink Tomysql;
This article is from the "My World I am the Master" blog, please be sure to keep this source http://xinyi168.blog.51cto.com/6660639/1913493
Oracle connects Oracle via Dblink