Oracle connects Oracle via Dblink

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