LoginSignup
9
7

More than 5 years have passed since last update.

@feifo(fei feifo)

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

Last updated at Posted at 2019-01-27

2019/1/27 新規作成

やりたいこと

image.png

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

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

※今回はDBクライアントとして、oracle instant clientを利用。OUI使ったり（そのためにX転送の設定したり）、ユーザー作ったりだなんだと準備しなくて良いので大変楽です。

前提

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

1. DB Clientのインストール

oracle instant clientをインストールできるリポジトリの準備

以下の記事で説明。
> Oracle Cloud(OCI)のリージョン内yumリポジトリを使ってみた

[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
リージョン内のOracle linux yumミラーで、Oracle Instant Clientが使えるようになったよ、の記事

・Oracle Technology Network > Database > Database Features > Database Instant Client
下の方に、Instant Clientのインストール手順が書かれている(rpmダウンロードしてくる方の手順)

以上

9
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
9
7