一、install the Oracle Instant Client
注意:Each step must be performed as root.
oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm (必须的)
oracle-instantclient12.1-odbc-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-jdbc-12.1.0.2.0-1.x86_64.rpm
(2) Install the packages.
[root@localhost ~]# rpm -ivh oracle-instantclient12.1-*
(3) 在/etc/ld.so.conf.d目录下创建一个配置文件
oracle-instant-client.conf 用来将Oracle client libraries 添加到
LD_LIBRARY_PATH
[root@localhost ~]# cat > /etc/ld.so.conf.d/oracle-instant-client.conf << EOL
> /usr/lib/oracle/12.1/client64/lib
> EOL
这样编辑的好处是输入目录时可以用Tab键补全。
Rebuild the LD_LIBRARY_PATH:
[root@localhost ~]# ldconfig
(4)使用sqlplus连接数据库,如果你使用RPMs来安装client,则它们已经存在你的PATH中了。(运行之前确保网络没问题)
[root@localhost ~]# sqlplus64 username/password@remoteIP:1521/databaseName
SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 15 22:58:53 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
sqlplus登录命令常用的几种方式:
sqlplus username/password 如:普通用户登录 sqlplus scott/tiger
sqlplus username/password@net_service_name 如: sqlplus scott/tiger@orcl
sqlplus username/password as sysdba 如:sqlplus sys/admin as sysdba
sqlplus username/password@//host:port/sid
以上步骤均通过后则第一步成功。
二、Connecting to an Oracle Database with an ODBC Driver
注意:首先要确保安装了unixODBC包
(1)Configure the Oracle ODBC Driver. odbcinst.ini 必须被包含在/etc目录下
[root@localhost ~]# vi /etc/odbcinst.ini将以下内容添加到末尾
[Oracle 12g ODBC driver]
Description = Oracle ODBC driver for Oracle 12g
Driver = /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
Setup =
FileUsage =
CPTimeout =
CPReuse =
Driver Logging = 7
[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log
ForceTrace = Yes
Pooling = No
DEBUG = 1
(2)Run ldd to verify that driver dependencies are installed and working.
[root@localhost ~]# ldd /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
ldd: warning: you do not have execution permission for `/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1'
linux-vdso.so.1 => (0x00007fffa24f9000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f7890f18000)
libm.so.6 => /lib64/libm.so.6 (0x00007f7890c16000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f78909f9000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f78907e0000)
librt.so.1 => /lib64/librt.so.1 (0x00007f78905d8000)
libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1 (0x00007f788d61a000)
libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007f788d408000)
libc.so.6 => /lib64/libc.so.6 (0x00007f788d047000)
/lib64/ld-linux-x86-64.so.2 (0x00007f78915a1000)
libmql1.so => /usr/lib/oracle/12.1/client64/lib/libmql1.so (0x00007f788cdd0000)
libipc1.so => /usr/lib/oracle/12.1/client64/lib/libipc1.so (0x00007f788ca52000)
libnnz12.so => /usr/lib/oracle/12.1/client64/lib/libnnz12.so (0x00007f788c348000)
libons.so => /usr/lib/oracle/12.1/client64/lib/libons.so (0x00007f788c102000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007f788bf00000)
libclntshcore.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1 (0x00007f788b98e000)
libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f788b783000)
注意:这里报了一个warning: you do not have execution permission for `/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1'",需要运行下列命令:
[root@localhost ~]# chmod 755 /usr/lib/oracle/12.1/client64/lib/lib*
再次运行ldd,此时不会有warning了。
(3)在/etc目录下创建odbc.ini,并写入以下内容
[DSN]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle 12g ODBC driver
DSN = DSN
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MaxLargeData = 0
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = caihua
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID = username
Password = password
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F
MaxTokenSize=8192
上面需要改的有Driver,ServerName,DSN,UserID,Password.
注意点:最开始的
[DSN]非常重要,可以改成其他的,比如
[ODBC],但必须要有,这是数据源的名称Data Source Name,对应于R中odbcConnect("DSN",uid=..,pwd=..),ServerName对应于tnsnames.ora的开头部分。UserID和Password可以为空
(4) 创建一个目录/etc/oracle,专门放tnsnames.ora,编写tnsnames.ora,内容为
caihua =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = hostIP )(Port = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
[root@localhost ~]# mkdir /etc/oracle
[root@localhost ~]# cd /etc/oracle
[root@localhost oracle]# vi tnsnames.ora
可调用
sqlplus username/password@net_service_name 来判断tnsnames.ora是否正确
其中net_service_name对应的是tnsnames.ora的第一个单词,即caihua,也对应odbc.ini的ServerName。
(5)在/etc/profile.d目录下创建一个配置脚本oracle-instant-client.sh,用来被系统启动时调用,这个脚本主要设置ORACLE_HOME, TWO_TASK, LD_LIBRARY_PATH 和 TNS_ADMIN环境变量。
# Set ORACLE_HOME to the directory where the bin and lib directories are located for the oracle client
export ORACLE_HOME=/usr/lib/oracle/12.1/client64
# No need to add ORACLE_HOME to the linker search path. oracle-instant-client.conf in
# /etc/ld.so.conf.d should already contain /usr/lib/oracle/12.1/client64.
# Alternately, you can set it here by uncommenting the following line:
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
# Define the default location where Oracle should look for the server
export TWO_TASK=//remotehostIP:1521/listener
# Define where to find the tnsnames.ora file
export TNS_ADMIN=/etc/oracle
remotehostIP:远程数据库的ip地址
(6)Source the oracle-instant-client.sh script 并确保每个环境变量都正确配置
[root@localhost oracle]# source /etc/profile.d/oracle-instant-client.sh
[root@localhost oracle]# echo $ORACLE_HOME
/usr/lib/oracle/12.1/client64
[root@localhost oracle]# echo $LD_LIBRARY_PATH
:/usr/lib/oracle/12.1/client64/lib
[root@localhost oracle]# echo $TNS_ADMIN
/etc/oracle
(7) 确保 Oracle ODBC driver 正常工作
isql -v 数据源
[root@localhost oracle]# isql -v DSN
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
通过以上完成了第二步。
三、R中加载RODBC访问数据库
1.安装R
yum install libXt-devel
yum install readline-devel
yum install gcc
yum install gcc-c++
yum install gcc-gfortran
tar -zxvf R-3.2.3.tar.gz
cd R-3.2.3
./configure
make
更改环境变量:
先看一下/etc/profile和/etc/profile.d/的对比:两个文件都是设置环境变量文件的,/etc/profile是永久性的环境变量,是全局变量,/etc/profile.d/设置所有用户生效;/etc/profile.d/比/etc/profile好维护,不想要什么变量直接删除/etc/profile.d/下对应的shell脚本即可,不用像/etc/profile需要改动此文件
在/etc/profile.d/目录下创建一个文件R.sh,加入以下内容
export R_HOME= R_Home_Path
export PATH=$PATH:$R_HOME/bin
然后运行下面命令
source /etc/profile.d/R.sh
2.下载RODBC包
首先要下载unixODBC、unixODBC-devel包
install.packages("RODBC",dependencies=TRUE,repos="http://cran.mirror.com")
3.加载RODBC包
library(RODBC)
4.创建连接
channel