humangas's blog

自分用のメモなので雑です。

ローカルにOracle検証環境を構築するメモ

現場でOracle使ってるが、なんやかんやDBになんかする管理系のツールなりを組む必要があったりする。

で、それを気軽に開発、テストする環境がローカルに欲しく、なんか無いかと調べたらOracle公式のDocker(Oracle Database on Docker)があったのでそれでやることにする構築メモ(メモなので余計なことも一杯書いてあるまま)。

この記事が大いに参考になった。「OS X 10.11で,Oracle Database 12cのDocker Imageを使ってみた」感謝。

Oracleの中の人が書いてるぽい記事もサッとまとまっていそう「公式 Oracle Database の Docker イメージを構築」(終わってから見つけたので、目次ぐらいで特に読んでないけど、他にも関連記事を書かれているようなので今度チェックしとく)。

システム環境

  • ローカルは、macOS Sierra 10.12.6
  • Oracle DBは、Oracle Database 12c Release 2 (12.2.0.1) Enterprise Edition
  • Dockerは、CEの17.06.0-ce-mac19,18663
    • インストールは、$ brew cask install docker でできる
$ brew cask info docker
docker: 17.06.0-ce-mac19,18663
https://www.docker.com/community-edition
/usr/local/Caskroom/docker/1.12.3.13776 (68B)
From: https://github.com/caskroom/homebrew-cask/blob/master/Casks/docker.rb
==> Names
Docker Community Edition
Docker CE
==> Artifacts
Docker.app (app)

やること

  1. Oracle DBをローカルに建てる(Docker)
  2. クライアントセットアップ
  3. SQL*Plus で接続
  4. SQLDeveploer で接続
  5. テストデータ作成
  6. (ついでに)PythonからOracle接続する

Oracle DBをローカルに建てる(Docker)

$ mkdir -p test-oracle-python
$ git clone https://github.com/oracle/docker-images.git
$ cd docker-images/OracleDatabase/dockerfiles
$ ./buildDockerImage.sh -v 11.2.0.2 -x -i

最初は11.2.0.2をインストールしようとした。

-e のEnterpriseにしようとしたら、x.eeファイルが無いと怒られた。11はxeしかないみたい。

-x でもこんどは、rpmがないと言われる。

rpm? ということで、macではそのままできないので、対応してそうなzipでやる12.2のどうせならeeにした。

よく見ると、ちゃんと公式READEMEに買いてあった。バイナリを落としなさいと。 -> https://github.com/oracle/docker-images/tree/master/OracleDatabase#building-oracle-database-docker-install-images

(あれだな。やっぱスッと英語が入ってこないと気づきにくく時間をロスするな。。英語大事)

ダウンロードするバイナリは、12.2.0.1/Dockerfile.ee内に記載があった「 INSTALL_FILE_1=“linuxx64_12201_database.zip” 」というファイルに該当するLinux x86-64のものにした。

あぁ、よく考えればアレか、Dockerでやるわけなので、別にMacに対応している必要はなかった。まぁいいか、11の機能でないとダメという検証をするわけではないので、そのまま12でやることにする。

とはいえ、11も一応DLしておく。11は、XEしか対応してないので、そのリンクを探してDLすること。

DLしたファイルは、各Dockerfileと同じディレクトリに配置しておく。

$ mv ~/Downloads/linuxx64_12201_database.zip 12.2.0.1/
$ mv ~/Downloads/oracle-xe-11.2.0-1.0.x86_64.rpm.zip 11.2.0.2

仕切り直し

$ ./buildDockerImage.sh -v 12.2.0.1 -e -i

さっと立てたいので、-i でMD5 checksumは無視する。

インストール終了まで、10分ほどかかった。(Build completed in 654 seconds.)

どでかいDockerImageが出来ていることが確認できる。

$ docker images
REPOSITORY            TAG                 IMAGE ID            CREATED             SIZE
oracle/database       12.2.0.1-ee         62da8aa5534b        6 minutes ago       13.6GB

起動する

$ docker run -p 1521:1521 --name testdb oracle/database:12.2.0.1-ee

初回起動時に、ログの場所がどうとか色々でてるので一応保存しておく。起動までに10分以上かかった気がする。

ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: mVFszrX4H1g=1

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 17-AUG-2017 08:52:00

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Starting /opt/oracle/product/12.2.0.1/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /opt/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/7a2e6582cc73/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                17-AUG-2017 08:52:02
Uptime                    0 days 0 hr. 0 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/7a2e6582cc73/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
The listener supports no services
The command completed successfully
[WARNING] [DBT-11209] Current available physical memory is less than the required physical memory (2,048MB) for creating the database.
[WARNING] [DBT-10102] The listener configuration is not selected for the database. EM DB Express URL will not be accessible.
   CAUSE: The database should be registered with a listener in order to access the EM DB Express URL.
   ACTION: Select a listener to be registered or created with the database.
Copying database files
1% complete
13% complete
25% complete
Creating and starting Oracle instance
26% complete
30% complete
31% complete
35% complete
38% complete
39% complete
41% complete
Completing Database Creation
42% complete
43% complete
44% complete
46% complete
47% complete
50% complete
Creating Pluggable Databases
55% complete
75% complete
Executing Post Configuration Actions
100% complete
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log" for further details.

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 17 09:05:21 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
System altered.

SQL>
Pluggable database altered.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
#########################
DATABASE IS READY TO USE!
#########################
The following output is now a tail of the alert.log:
Completed: alter pluggable database ORCLPDB1 open
2017-08-17T09:05:19.560351+00:00
ORCLPDB1(3):CREATE SMALLFILE TABLESPACE "USERS" LOGGING  DATAFILE  '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT  AUTO
ORCLPDB1(3):Completed: CREATE SMALLFILE TABLESPACE "USERS" LOGGING  DATAFILE  '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT  AUTO
ORCLPDB1(3):ALTER DATABASE DEFAULT TABLESPACE "USERS"
ORCLPDB1(3):Completed: ALTER DATABASE DEFAULT TABLESPACE "USERS"
2017-08-17T09:05:21.594262+00:00
ALTER SYSTEM SET control_files='/opt/oracle/oradata/ORCLCDB/control01.ctl' SCOPE=SPFILE;
   ALTER PLUGGABLE DATABASE ORCLPDB1 SAVE STATE
Completed:    ALTER PLUGGABLE DATABASE ORCLPDB1 SAVE STATE
2017-08-17T09:14:54.491154+00:00
ORCLPDB1(3):Resize operation completed for file# 10, old size 337920K, new size 348160K
2017-08-17T09:15:46.162119+00:00
Warning: VKTM detected a forward time drift.
Please see the VKTM trace file for more details:
/opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_vktm_1732.trc

これで起動している。バックグラウンドで起動しなかったので、こういう感じ。次からは以下で起動してバックグラウンドで動かそう。

$ docker run -d -p 1521:1521 --name testdb oracle/database:12.2.0.1-ee

あぁ、READMEよく見ると、起動オプションとか書いてある。さらにEMも起動できるようになってる。

なので、次からはこうしよう。パスワードも予めテスト用のパスワードを設定しておくほうが楽そうだ(SID、PDBとかはデフォルトと同様なので設定はどっちでもいいけど忘れるので、一緒にコマンド化しておくほうが楽そう)。

$ docker run --name testdb \
-d \
-p 1521:1521 -p 5500:5500 \
-e ORACLE_SID=ORCLCDB \
-e ORACLE_PDB=ORCLPDB1 \
-e ORACLE_PWD=testdbPass$ \
-e ORACLE_CHARACTERSET=AL32UTF8 \
oracle/database:12.2.0.1-ee

起動後にログインする

起動した後にそのDockerにログインしたければ以下でOK

$ docker exec -it testdb /bin/bash

クライアントセットアップ

Oracle は立ったぽいので、接続するためのクライアントのセットアップをする。

http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html から、Oracle Instant Clientを入手する(アカウントは作っておく必要あり)。いろいろあるが、basic + sqlplus で十分

  • Version 12.1.0.2 (64-bit) Updated 7/5/2017
    • instantclient-basic-macos.x64-12.1.0.2.0.zip
    • instantclient-sqlplus-macos.x64-12.1.0.2.0.zip

インストール場所とかちょっと迷うが、公式にインストール方法が書いてあるのでそれと合わせておく(本当は$HOME直下に入れたくはないが、ちょっとした気持ち悪さの問題なので無視してそのままやる)。

http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html#ic_osx_inst

$ mv ~/Downloads/instantclient-* ~
$ unzip instantclient-basic-macos.x64-12.1.0.2.0.zip
$ unzip instantclient-sqlplus-macos.x64-12.1.0.2.0.zip
$ cd instantclient_12_1
$ ln -s libclntsh.dylib.12.1 libclntsh.dylib
$ ln -s libocci.dylib.12.1 libocci.dylib
$ mkdir ~/lib
$ ln -s ~/instantclient_12_1/libclntsh.dylib ~/lib/
$ vim ~/.zsh.d/oracle.sh
$ cat ~/.zsh.d/oracle.sh
export PATH=~/instantclient_12_1:$PATH
$ source ~/.zshrc

(~/.zsh.d/oracle.shは、オレオレ設定なので、環境変数を上のようにロードできるならなんでもよい)

SQL*Plus で接続

こういう感じで繋ぐ。

sqlplus sys/<your password>@//localhost:1521/<your SID> as sysdba
sqlplus system/<your password>@//localhost:1521/<your SID>
sqlplus pdbadmin/<your password>@//localhost:1521/<Your PDB name>

パスワードは、オプションを指定しなければ初回起動時に自動生成され、ログの一発目に表示されている。ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: mVFszrX4H1g=1 という具合で。

繋いでみると、以下のエラーが出る。

エラー:

$ sqlplus pdbadmin/mVFszrX4H1g=1@localhost:1521/ORCLPDB1

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 17 18:40:18 2017

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

ERROR:
ORA-21561: OID generation failed

これは名前解決出来てない時に出るらしい(詳しくは見てない)。なので、コンピュータ名を/etc/hostsに書いてやると解決する。

解決手順:

$ uname -n
xxx.local
$ sudo vim /etc/hosts
$ cat /etc/hosts
...
127.0.0.1       localhost xxx.local
...

再接続:成功した

$ sqlplus pdbadmin/mVFszrX4H1g=1@localhost:1521/ORCLPDB1

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 17 18:45:58 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
SQL> select * from dual;

D
-
X

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQLDeveploer で接続

http://www.oracle.com/technetwork/jp/developer-tools/sql-developer/downloads/index.html からDLする(もちろん、Mac OSXを選択する)。

zipを解凍したらApplicationフォルダに移動して、起動。

以下で接続できる。

  • 接続名: test1(任意)
  • ユーザー名:pdbadmin
  • パスワード:mVFszrX4H1g=1(前述手順参照)
  • 接続タイプ:基本/ロール:デフォルト
  • ホスト名:localhost
  • ポート:1521
  • サービス名:ORCLPDB1

スクリーンショット 2017-08-17 19.21.03 f:id:Humangas:20170818053334p:plain

最初、SIDにORCLCDBを入れて接続してみたが、パスワード違うと怒られた。ので↑の通りやったらイケた。

2017/08/18追記:

ORCLCDBをサービス名に入れれば接続できる。接続するDBが違うということ。

  • PDBに接続:ORCLPDB1(プラガブル・データベース)
  • CDBに接続:ORCLCDB(マルチテナント・コンテナ・データベース)

これは、12cから追加された機能でマルチテナント・アーキテクチャというらしい。PDBがこれまで11までで使ってたDBという感じでスキーマや表領域やデータのセットで論理的なデータベース。CDBは、一つ以上のPDBを管理するデータベース管理システム(SQL実行やトランザクションの管理等)。要は役割を分けたよ。というアーキテクチャ。監査表のON設定をPDBでやろうと思ったらエラーが出てCDBでやれと言われたので頭が繋がった。

TNS名で接続できるようにする

TNS_ADMINを設定して、任意の場所にtnsnames.oraを配置する。

前述で設定した環境変数をちょっと直す(~/.zshrcから~/.zsh.d/*.shをロードする設定が入ってる=オレオレ設定)。

$ cat ~/.zsh.d/oracle.sh
# Env oracle client settings
export ORACLE_HOME=~/instantclient_12_1
export TNS_ADMIN=~/.config/oracle
export PATH=$ORACLE_HOME:$PATH

$ source ~/.zshrc

tnsname.oraを作成する( ~/.config/oracle は、XDG 基本ディレクトリ仕様(※)にならった)。

$ mkdir -p ~/.config/oracle
$ vim ~/.config/oracle/tnsnames.ora
$ cat ~/.config/oracle/tnsnames.ora
testdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLPDB1)
    )
  )

※: https://debian-handbook.info/browse/ja-JP/stable/sect.filesystem-hierarchy.html > B.2.2. ユーザのホームディレクト

これらの設定ファイルはユーザのホームディレクトリ直下に保存され、まとめてドットファイルと呼ばれており、増え続けることでユーザのホームディレクトリを散らかす原因となっています。幸いなことに、FreeDesktop.org の傘下で行われた努力により「XDG 基本ディレクトリ仕様」が生まれました。これは設定ファイルと設定ディレクトリを一掃することを目的とする仕様です。XDG 基本ディレクトリ仕様によれば、設定ファイルは ~/.config に、キャッシュファイルは ~/.cache に、アプリケーションデータファイルは ~/.local (およびそのサブディレクトリ) に保存すると決められています。XDG 基本ディレクトリ仕様はゆっくりと勢いを増しつつあり、一部のアプリケーション (特にグラフィカルアプリケーション) はこの仕様に従うようになっています。

sqlplusで接続してみる。tnsnameで接続できた。

$ sqlplus pdbadmin/mVFszrX4H1g=1@testdb

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 17 19:59:29 2017

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

Last Successful login time: Thu Aug 17 2017 19:21:10 +09:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from dual
  2  ;

D
-
X

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

tnsname * SQLDeveloper で接続

まず、tnsnameがどこにあるか設定する必要がある。Use tnsnames.ora in Oracle SQL Developerに、書いてあるが、以下の通りににする。

  1. メニューバー > Preferences…
  2. データベース > 拡張
  3. Tnsnamesディレクトリ: ~/.config/oracle

この設定後に、TNSを選択するとプルダウンから設定したtns名が表示されるはず。あとは普通に接続すればいい。

スクリーンショット 2017-08-17 20.28.00 f:id:Humangas:20170818053338p:plain

テストデータ作成

ここまでくれば、もう出来たようなものだが、テスト環境作る上での工程として一応簡単なやつを書いておく。

なんでもいいが、手元にたまたまあった株価データをDBに入れてみる(↓こういうの)。

スクリーンショット 2017-08-17 20.42.06 f:id:Humangas:20170818053342p:plain

テストテーブル作成

その前にテストユーザーを作成する。

sys as sysdba で接続し、以下を発行する。必要な権限もつける(connectを付けないとログインできない)。

create user testuser 
identified by "testuserpass"
grant connect to testuser
grant create any table to testuser
alter user testuser quota unlimited on USERS

作成したテストユーザーで接続し、以下でテーブル作成(カラム名はめんどくさくなったので超適当)。

create table stock_list (
  stock_date date,
  stock_code char(4),
  stock_code2 char(2),
  stock_name varchar2(30),
    id1 varchar2(30),
    id2 varchar2(30),
    id3 varchar2(30),
    id4 varchar2(30),
    id5 varchar2(30),
    market varchar2(30),
  primary key( stock_code )
)

テストデータ投入

csvをダァと入れることを想定。色々方法はあるが、Instant ClientだとSQL Loaderがついてないので、sql developerの機能でやるのが手軽か? あとは、Emblukのプラグインhttps://github.com/embulk/embulk-output-jdbc/tree/master/embulk-output-oracle とかもあるので、何回もやるならそういうコマンドで操れるツールのほうが良さそうではある。今回は試してない。

SQL Developerなら簡単で、以下のようにやるだけでOKだった。

  1. テーブル選択 > 右クリック > データのインポート
  2. 文字コード調整(※)やヘッダ有無などに注意すればあとは特にいじらない
  3. あとは、ウィザードに従って進むのみ(見れば分かる系。特に何も設定せずに次へを押下しただけだった)

※:Windowsで作りました系だとMS932選んどけばだいたい文字化けは治る。文字コード不明であれば、選んでいけば、どうなるかプレビューされるのでそれで確認すればいい。

スクリーンショット 2017-08-18 2.28.10 f:id:Humangas:20170818053346p:plain

NOTE:

なんかよく分からんが、一回列の定義をミスるとゴミ設定でマッピングしようとしてウィザードが進まなくなる? まぁ、おかしいなぁーと思えば、ウィザードを一回閉じてやり直したほうが早い。

PythonからOracle接続する

今の現場だとpythonでなんか書くことが多いので、そこまでやって構築メモを終わることにする。

セットアップ

pyenv + pyenv-virtualenv(※)でやる。oracleに接続するライブラリはシンプルなrecordsを使う(内部でSQLAlchemy -> cx-Oracleを利用している模様)。

$ mkdir -p ~/src/work/test-oralce-python
$ cd $_
$ pyenv virtualenv 2.7.13 2713_test-oracle-python
$ pyenv shell 2713_test-oracle-python
$ pip install records
$ pip install cx_oracle

pyenv + pyenv-virtualenv ?

pythonの仮想環境を作るツールで、pyenvはpythonの色んなバージョンを管理でき、pyenv-virtualenvは色々好きなだけpip環境を作れるもの、とざっくり書いておく。

$ brew install pyenv
$ brew install pyenv-virtualenv
$ vim ~/.zshrc
$ cat ~/.zshrc | grep pyenv
eval "$(pyenv init -)" 
eval "$(pyenv virtualenv-init -)"
$ source ~/.zshrc
$ pyenv install 2.7.13

接続テスト

まず、テストソースを書く。

$ vim test-oracle.py
$ cat test-oracle.py
# -*- coding: utf-8 -*-

import records

def main():
    db = records.Database('oracle://testuser:testuserpass@testdb')
    rows = db.query('select * from dual', fetchall=True)
    print rows[0].dummy

if __name__ == '__main__':
    main()

実行してみる。内部で"select * from dual" してるだけなので、X が返ってくればOK

$ python test-oracle.py
X

なんか前は、DYLD_LIBRARY_PATHとか、LD_LIBRARY_PATH なども設定しないとダメだったような気がするが、ここに書いてある以上のことは何もせずにうまく行った。前は11だったからか? よく分からんが、うまくいったのでコレで環境構築検証は終わり!