Mac 系统下 Rstudio连接虚拟机(远程)oracle数据库
因为之前做的一个项目需要在mac系统上用Rstudio远程访问Oracle数据库,以对获得的数据进行进一步的分析~但是在网上找了很多都是关于windows系统的相关操作介绍,没有针对Mac系统的。工作需要,同时也是为了沉淀知识,丰富生态,在此分享:
首先明确需要下载的东西(Rtools、R包、Oracle官网的instantclient),这些对于Windows系统和Mac系统都是一样的。
网站如下:
Rtools:https://cran.r-project.org/bin/,进入网站后的页面如下:
根据不同的系统,以及R语言的版本,进行选择。
DBI包:https://cran.r-project.org/web/packages/DBI/,好像也可以直接在R环境中install.packages(“DBI”)进行安装;
ROracle包:https://cran.r-project.org/web/packages/ROracle/,这个包要最后安装,注意linux和windows的区别。在windows中安装要到网站https://www.oracle.com/database/technologies/appdev/roracle.html中去下载相应的zip版本。
几个需要的oracle-instantclient,我主要用了basic、sdk、jdbc、sqlplus~这些在oracle官网都能找到https://www.oracle.com/technetwork/topics/intel-macsoft-096467.html。或者 https://www.oracle.com/database/technologies/instant-client/downloads.html。
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
具体流程如下:(本贴主要介绍mac系统下)
在正式介绍之前,首先提一个重要的网页:https://www.ecourse.org/news.asp?which=3233和一个家喻户晓的网站Stack Overflow。
安装Rtools。
安装instant client
-
下载并解压四个instantclient文件,并将解压后的文件放到一个instantclient_12文件夹内
instantclient-basic-macosx-12.1.zip
instantclient-sqlplus-macosx-12.1.zip
instantclient-sdk-macosx-12.1…zip
instantclient-jdbc-macosx-12.1.zip
我是直接将解压后的文件作为独立文件放在文件夹instantclient_12中的,并没有对解压后的文件路径做处理~ -
创建两个符号连接
cd instantclient_12
ln -s libclntsh.dylib.12.1 libclntsh.dylib
ln -s libocci.dylib.12.1 libocci.dylib
cd … -
创建路径 /usr/local/oracle并将instantclient_12转移过去~
sudo mkdir /usr/local/oracle
sudo mv /users/dadao/Downloads/instantclient_12 /usr/local/oracle/instantclient12 -
打开文件 /etc/profile,配置环境变量
sudo pico /etc/profile
在/etc/profile 的最后添加:
ORACLE_HOME="/usr/local/oracle/instantclient12"
export ORACLE_HOME
DYLD_LIBRARY_PATH=
O
R
A
C
L
E
H
O
M
E
:
ORACLE_HOME:
ORACLEHOME:DYLD_LIBRARY_PATH
export DYLD_LIBRARY_PATH
PATH=
O
R
A
C
L
E
H
O
M
E
:
ORACLE_HOME:
ORACLEHOME:PATH
export PATH
保存~
然后运行source /etc/profile,使刚才的配置生效~
下面的测试我没有进行,有兴趣的小伙伴,可以自己试一下~一般没有问题,不用测试
Test it out with the sqlplus command line app.
sqlplus [username][/password]@//[hostname][:port][/serviceName]
If you can connect with that, then you’re good to go.
5. (optional) Create network/admin folder inside instantclient12 and copy tnsnames.ora file into the folder:
sudo mkdir /usr/local/oracle/instantclient12/network
sudo mkdir /usr/local/oracle/instantclient12/network/admin
sudo mv tnanames.ora /usr/local/oracle/instantclient12/network/admin
chmod 755 /usr/local/oracle/instantclient12/network/admin/tnsnames.ora
Now we can use server alias such as cba11g to make connections.
安装ROracle
- Open terminal and run the following commands to copy Oracle’s library files
sudo cp /usr/local/oracle/instantclient12/.dylib.12.1 /usr/local/lib
sudo cp /usr/local/oracle/instantclient12/.dylib /usr/local/lib
sudo cp /usr/local/oracle/instantclient12/.dylib.12.1 /Library/Frameworks/R.framework/Resources/lib
sudo cp /usr/local/oracle/instantclient12/.dylib /Library/Frameworks/R.framework/Resources/lib
-
Download ROracle package file ROracle_1.2.2.tar.gz,版本随时都会有更新 from https://cran.r-project.org/web/packages/ROracle/index.html and save it anywhere, say /users/xyz/Documents
-
Open R, and run command: install.packages(“DBI”),这一步有很多种实现方式~
-
Go back to terminal, run the following commands to install ROracle
sudo R CMD INSTALL --configure-args=’–with-oci-lib=/usr/local/oracle/instantclient12 --with-oci-inc=/usr/local/oracle/instantclient12/sdk/include’ /users/xyz/Documents/ROracle_1.2-2.tar.gz
我在做的时候是用的下面的命令,因为sdk存储路径的不同导致~
sudo R CMD INSTALL --configure-args=’–with-oci-lib=/usr/local/oracle/instantclient18/ --with-oci-inc=/usr/local/oracle/instantclient18/instantclient_18_1_sdk/sdk/include’ /users/wangdeying/Downloads/ROracle_1.3-1.tar.gz
- Now go to R and run command: library(ROracle)
library(ROracle)
drv <- dbDriver(“Oracle”)
connect_str <-("(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST =10.102.1.1)(PORT = 1521))) (CONNECT_DATA = (SERVER =DEDICATED)(SERVICE_NAME = orcl)))")
conn <- dbConnect(drv,username =“corphy”,password = “corphy”,dbname = connect_str)
sql <- “select ID_ from table1”
mdata <- dbSendQuery(conn,sql)
d<-fetch(mdata)
dbDisconnect(conn)