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

1.1Authorization (empower crh_snp users to create dblink permissions)

Under sys, grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK permissions to your users

Grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to crh_snp

1.2 specific creation of db_link

Then log in to the local database as crh_snp and execute the following language name

Create database linkdcrac2

Connect to wlyxpt identified by wlyxpt

Using'(DESCRIPTION =

(ADDRESS_LIST =

)

(CONNECT_DATA =

(SERVICE_NAME = dcrac)

(INSTANCE_NAME = dcrac2)

(failover_mode =

(backup = dcrac1)

(type = select)

(method = preconnect)

)

)

)'

-description: the name of the db_link created this time is: dcrac2, the user name of the other party's oracle database is wlyxpt, the password-- wlyxpt, and the service name is dcrac. Because it is a rac database, the blue tnsname configuration can be given by the relevant dba of the brokerage.

1.3 query ods_ufs.his_his_ deliver table data with crh_snp (table to which obs belongs, wlyxpt user has the corresponding permissions)

Select * fromods_ufs.his_his_deliver@dcrac2

2. Oracle connects to mysql through DBlink

Oracle: system redhat 5.5Database: 11.2.0.1

Mysql: system centos6.3 Database: 5.6.21

[root@rac1 ~] # 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

[root@rac1 ~] # 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. Configuration / 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. Configuration / etc/odbcinst.ini

[MySQL]

Description = ODBC for MySQL

Driver = / usr/lib64/libmyodbc5.so

Setup = / usr/lib64/libodbcmyS.so

FileUsage = 1

2.4. Test connection

[root@rac1] # 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. Configure snooping

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. Configure odbc snooping

Path: $ORACLE_HOME/hs/admin

Note: the name should be the same as the name of the odbc configuration. This is myodbc3.

[oracle@rac1 admin] $cd $ORACLE_HOME/hs/admin

[oracle@rac1 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

[oracle@rac1 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, 2009, 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

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.