Oracle Cloud(OCI)のIaaSからDbaaSへSQLPlusで接続してみた

*2019/1/27 新規作成*
***

# やりたいこと
![image.png](https://qiita-image-store.s3.amazonaws.com/0/109270/27a63240-6eb3-d90b-4a93-8c22602f7bee.png)


事前に作成済みのcompute instance(vm)からDbaaS(vm)へSQL plusでつなぐだけ。

1. Oracle DBのクライアントをcomputeインスタンスにインストール
2. SQLplusをcomputeインスタンスにインストール+接続設定
3. SQLplusでDbaaSへ接続

※今回はDBクライアントとして、[oracle instant client](https://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html)を利用。OUI使ったり（そのためにX転送の設定したり）、ユーザー作ったりだなんだと準備しなくて良いので大変楽です。

# 前提
- OCIリソースは作成済み（今回はComputeインスタンスとDBインスタンスは同一リージョン、同一VCN）
- セキュリティリストとかは適切に設定済み

# 1. DB Clientのインストール

### oracle instant clientをインストールできるリポジトリの準備
以下の記事で説明。
 \> [Oracle Cloud(OCI)のリージョン内yumリポジトリを使ってみた](https://qiita.com/feifo/items/6dc8d9a77bb4798ec675)

```
[opc@testinstance ~]$ sudo yum list oracle-instantclient*
Loaded plugins: langpacks, ulninfo
ol7_UEKR5                                                | 2.5 kB     00:00
ol7_addons                                               | 2.5 kB     00:00
ol7_developer                                            | 2.5 kB     00:00
ol7_developer_EPEL                                       | 2.5 kB     00:00
ol7_latest                                               | 3.0 kB     00:00
ol7_oci_included                                         | 2.2 kB     00:00
ol7_optional_latest                                      | 2.5 kB     00:00
ol7_preview                                              | 2.5 kB     00:00
(1/11): ol7_UEKR5/x86_64/updateinfo                        |  27 kB   00:00
(2/11): ol7_UEKR5/x86_64/primary_db                        | 3.2 MB   00:00
(3/11): ol7_latest/x86_64/updateinfo                       | 767 kB   00:00
(4/11): ol7_oci_included/x86_64/primary_db                 |  20 kB   00:00
(5/11): ol7_developer_EPEL/x86_64/primary_db               | 9.8 MB   00:00
(6/11): ol7_developer/x86_64/primary_db                    | 293 kB   00:00
(7/11): ol7_latest/x86_64/primary_db                       |  14 MB   00:00
(8/11): ol7_optional_latest/x86_64/updateinfo              | 592 kB   00:00
(9/11): ol7_optional_latest/x86_64/primary_db              | 3.3 MB   00:00
(10/11): ol7_preview/x86_64/primary_db                     |  18 kB   00:00
(11/11): ol7_preview/x86_64/updateinfo                     |   71 B   00:00
Available Packages
oracle-instantclient12.2-basic.x86_64         12.2.0.1.0-1      ol7_oci_included
oracle-instantclient12.2-basiclite.x86_64     12.2.0.1.0-1      ol7_oci_included
oracle-instantclient12.2-devel.x86_64         12.2.0.1.0-1      ol7_oci_included
oracle-instantclient12.2-jdbc.x86_64          12.2.0.1.0-1      ol7_oci_included
oracle-instantclient12.2-odbc.x86_64          12.2.0.1.0-1      ol7_oci_included
oracle-instantclient12.2-precomp.x86_64       12.2.0.1.0-1      ol7_oci_included
oracle-instantclient12.2-sqlplus.x86_64       12.2.0.1.0-1      ol7_oci_included
oracle-instantclient12.2-tools.x86_64         12.2.0.1.0-1      ol7_oci_included
oracle-instantclient18.3-basic.x86_64         18.3.0.0.0-2      ol7_developer
oracle-instantclient18.3-basiclite.x86_64     18.3.0.0.0-1      ol7_oci_included
oracle-instantclient18.3-devel.x86_64         18.3.0.0.0-1      ol7_oci_included
oracle-instantclient18.3-jdbc.x86_64          18.3.0.0.0-1      ol7_oci_included
oracle-instantclient18.3-odbc.x86_64          18.3.0.0.0-1      ol7_oci_included
oracle-instantclient18.3-precomp.x86_64       18.3.0.0.0-1      ol7_oci_included
oracle-instantclient18.3-sqlplus.x86_64       18.3.0.0.0-1      ol7_oci_included
oracle-instantclient18.3-tools.x86_64         18.3.0.0.0-1      ol7_oci_included
```

### oracle-instantclientをインストール

`$ yum install oracle-instantclient18.3-basic.x86_64`

```
[opc@testinstance ~]$ sudo yum install oracle-instantclient18.3-basic.x86_64
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package oracle-instantclient18.3-basic.x86_64 0:18.3.0.0.0-2 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package                          Arch     Version        Repository       Size
================================================================================
Installing:
 oracle-instantclient18.3-basic   x86_64   18.3.0.0.0-2   ol7_developer    52 M

Transaction Summary
================================================================================
Install  1 Package

Total download size: 52 M
Installed size: 220 M
Is this ok [y/d/N]: y
Downloading packages:
oracle-instantclient18.3-basic-18.3.0.0.0-2.x86_64.rpm     |  52 MB   00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : oracle-instantclient18.3-basic-18.3.0.0.0-2.x86_64           1/1
  Verifying  : oracle-instantclient18.3-basic-18.3.0.0.0-2.x86_64           1/1

Installed:
  oracle-instantclient18.3-basic.x86_64 0:18.3.0.0.0-2

Complete!
```


### 共有ライブラリのパスを通す

`$ sh -c "echo /usr/lib/oracle/18.3/client64/lib > /etc/ld.so.conf.d/oracle-instantclient.conf"`
`$ ldconfig`

```
[opc@testinstance ~]$ sudo sh -c "echo /usr/lib/oracle/18.3/client64/lib > /etc/ld.so.conf.d/oracle-instantclient.conf"
[opc@testinstance ~]$ sudo ldconfig
```

# 2. SQL plusのインストールと接続設定

### SQL Plusインストール

`$ yum install oracle-instantclient18.3-sqlplus.x86_64`

```
[opc@testinstance ~]$ sudo yum install oracle-instantclient18.3-sqlplus.x86_64
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package oracle-instantclient18.3-sqlplus.x86_64 0:18.3.0.0.0-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package                          Arch   Version         Repository        Size
================================================================================
Installing:
 oracle-instantclient18.3-sqlplus x86_64 18.3.0.0.0-1    ol7_oci_included 701 k

Transaction Summary
================================================================================
Install  1 Package

Total download size: 701 k
Installed size: 3.1 M
Is this ok [y/d/N]: y
Downloading packages:
oracle-instantclient18.3-sqlplus-18.3.0.0.0-1.x86_64.rpm   | 701 kB   00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : oracle-instantclient18.3-sqlplus-18.3.0.0.0-1.x86_64         1/1
  Verifying  : oracle-instantclient18.3-sqlplus-18.3.0.0.0-1.x86_64         1/1

Installed:
  oracle-instantclient18.3-sqlplus.x86_64 0:18.3.0.0.0-1

Complete!
[opc@testinstance ~]$ sudo ldconfig
```

### PATHを通す

`$ export PATH=/usr/lib/oracle/18.3/client64/bin:$PATH`

### tnsnames.oraの設定

tnsname.oraを書くべき場所の確認

```
[opc@testinstance  ~]$ cd /usr/lib/oracle/18.3/client64/lib/network/admin
[opc@testinstance  admin]$ ll
total 4
-rw-r--r--. 1 root root 502 Oct 20 00:39 README
[opc@testinstance  admin]$ cat README
============================================================================
This is the default directory for Oracle Network and Oracle Client
configuration files. You can place files such as tnsnames.ora, sqlnet.ora
and oraaccess.xml in this directory.
NOTE:
If you set an environment variable TNS_ADMIN to another directory containing
configuration files, they will be used instead of the files in this default
directory.
============================================================================
```

tnsnames.oraを書く

```
[opc@testinstance  admin]$ sudo vi tnsname.ora
[opc@testinstance  admin]$ cat tnsnames.ora
TESTORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <対象DbaaSのホスト名/IPアドレス>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb01.pubsubnet2.vcn1.oraclevcn.com)
    )
  )
```

# 3. SQL plusで接続

`sqlplus <ユーザー名>@ネットサービス名`

```
[opc@instance-20190128-0100 admin]$ sqlplus test@TESTORCL

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 27 16:07:12 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Sun Jan 27 2019 15:39:55 +00:00

Connected to:
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
Version 18.2.0.0.0

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl
```

# 参考
・[Oracle Instant Client RPMs Now Available on Oracle Linux Yum Servers in OCI](https://blogs.oracle.com/linux/oracle-instant-client-rpms-now-available-on-oracle-linux-yum-servers-in-oci)
リージョン内のOracle linux yumミラーで、Oracle Instant Clientが使えるようになったよ、の記事

・[Oracle Technology Network > Database > Database Features > Database Instant Client](https://www.oracle.com/technetwork/jp/database/features/instant-client/linuxx86-64soft-092277.html)
下の方に、Instant Clientのインストール手順が書かれている(rpmダウンロードしてくる方の手順)


以上