OracleDBインスタンスを1から作成して、アプリと連携する

#はじめに

普段、業務でSQLを作成してデータを引っ張ってきたりテーブル作ったりしてきたが、実際DB自体の理解が乏しく、
インスタンスなんてものも最近初めて知った。
以外と周りの人で、DB構築はできないだとかインスタンスって存在はしっているけど
何のパラメータがあって、何をいじればどう変わるのかが自分を含めて説明できないなと感じて
勉強がてらに構築した手順をメモとして上げる。

__※構築にあたって、DBCAを使用して構築するのが主流だと思いますが、簡単に完了して、構築に何が必要かが見逃してしまいそうなので1から作成していきます！__

#環境
* VMWare Ver16
* CentOs8.2.2004
* Oracle database 12c release2 for Linux (12.2.0.1.0)
* アプリ（WinForms C#)

#構築前にインスタンについて
###インスタンスとは
>一時的に使用するメモリ（SGA + プロセス群）のこと。

ざっくり図で表すとこんな感じ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/b108195a-0361-4d40-5350-d41201301787.png)
基本的には、アプリなどのユーザから問い合わせがあれば、直接データベースを操作するのではなく、
インスタンスを介して操作を行う。
なので、普段何気なく行っているINSERT分などは、高速なメモリ（インスタンス）に一時的に反映した後
データベースに反映をしている。

### インスタンスの構成			
##### 1.システムグローバル領域（SGA)			
Oracleサーバが稼働するPCのメインメモリ内に生成される共有メモリ領域のこと。
⇒実行したSQLを再利用するために格納したり、REDOログ情報などが格納される

- 領域内に構成されているものは以下６つのものがある。

【データベースバッファキャッシュ、共有プール、JAVAプール、ラージプール、ストリームプール、REDOログバッファ】
	それぞれがなにかについては、下記サイトの表5-2参照
	[Oracle doc](https://docs.oracle.com/cd/E96517_01/admqs/managing-the-oracle-instance.html#GUID-A3319550-AB7A-4429-9A58-4B90E4B3D0F5)	
			
基本的には、自動管理機能があるためあまり気にしなくてよいことが多いが、
場合によって「バッファキャッシュヒット率・ライブラリキャッシュヒット率やディクショナリキャッシュヒット率」
の低下などを個別に調整する場合に気にする必要がある。
##### 2.バックグラウンドプロセス
ざっくりいうと、インスタンス障害のリカバリだったりリソース解放などのクリーンアップなど
DBの裏側を支えている大事なプロセスのこと。
		
プロセスの種類に関しては、Oracleのバージョンによって異なり、必須プロセスや動的に起動する必要のあるプロセスが左右される。
代表的なものでは「LGWRやCKPT、SMON」などがあげられる。

# 構築		
### 1.DBサーバ環境の用意
下記サイトから入手したものでセットアップ
[VMWareのダウンロード先](https://my.vmware.com/en/web/vmware/downloads/info/slug/desktop_end_user_computing/vmware_workstation_player/16_0)
[CentOsのisoファイル](https://www.centos.org/download/)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/5ee3f811-a29b-693c-382b-ba567cd26fd5.png)
### 2.Oracle導入
[Oracleの入手](https://www.oracle.com/jp/database/technologies/oracle-database-software-downloads.html)
上記サイトからOracleをインストールする
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/d65b65a2-9e43-f4f3-c199-2a33091ed120.png)

入手したらWinSCPなどで、仮想環境へ送る
__※直接仮想環境からインストールした場合は不要__
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/9713da0e-3dfa-5467-0d8e-ae607a83034e.png)

仮想環境に行って、インストールしたものを解凍

```shell
unzip linuxx64_12201_database.zip
```

解凍
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/3427806a-445b-b676-04a0-42dfaa8fd974.png)

### 3.カーネルパラメータの設定
カーネルの個々の機能やドライバが解釈して、振る舞いを変更できるようにするためのもの（パラメータ名＝値の形で設定）
カーネルパラメータの変更箇所は2種類のうち①を対象にしていく

①/etc/sysctl.conf・・・設定ファイルに記述することで、恒久的に設定が維持される
②「/proc」以下・・・ファイル単位で扱いファイル＝設定となる。再起動すると初期化されるため一時的の設定となる

| パラメータ名 | 値 |
|:-----------|:------------|
| semmsl | 250 |
| semmns | 32000 |
| semopm | 100 |
| semmni | 128 |
| shmail | 128 |
| shmall | 物理メモリーサイズの40%(単位:ページ) |
| shmmax | 物理メモリー最右の半分(単位:バイト) |
| smmni | 4096 |
| file-max | 6815744 |
| panic_on_oops | 1 |
| aio-max-nr | 1048576 |
| ip_local_port_range | 最小:9000 最大:65500 |
| rmen_default | 262144 |
| rmem_max | 4194304 |
| wmem_default | 262144 |
| wmem_max | 1048576 |
<br>

以下コマンドでカーネルパラメータをチェックしていく

```shell
sysctl -a | grep sem
sysctl -a | grep shm
sysctl -a | grep file-max
sysctl -a | grep ip_local_port_range
sysctl -a | grep rmem
sysctl -a | grep wmem
sysctl -a | grep aio-max-nr
```
修正箇所がある場合は、修正

``` shell
vi /etc/sysctl.conf
```
物理メモリについて

``` shell
free -mt
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/2706986b-468b-a3b0-cfa6-4d1baa23d01d.png)
物理メモリーサイズの半分以上でよいため、私の場合は設定した１GBから計算して
__1 * 1024 * 1024 * 1024 / 2 = 536,870,912__
となる。

私の場合はこんな感じでした
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/088c4d8f-4018-c03e-f4f3-9544c96dfc3b.png)


修正
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/2d0468c5-9956-f336-4608-f2990cf0299a.png)


反映

``` shell
sysctl -p
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/00b40c57-48ed-f31d-7eef-5341bb05ea91.png)

### 4.OSグループの設定
次のOS認証に必要なグループを設定していく

| グループ名 | グループID | 備考 |
|:-----------|------------:|:------------:|
| oinstall | 54321 | Oracle Inventory グループ |
| dba | 54322 | Database 用 OSDBA グループ |
| oper | 54323 | Database 用 OSOPER グループ |
| backupdba | 54324 | Database 用 OSBACKUPDBA グループ |
| dgdba | 54325 | Database 用 OSDGDBA グループ |
| kmdba | 54326 | Database 用 OSKMDBA グループ |
| racdba | 54327 | OSRACDBA グループ |

``` shell
groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
groupadd -g 54324 backupdba
groupadd -g 54325 dgdba
groupadd -g 54326 kmdba
groupadd -g 54327 racdba
```
追加されているか確認

``` shell
cat  /etc/group
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/c0f374b2-a597-b0eb-5611-640f750b7003.png)

### 5.Oracleユーザ作成

| 項目名 | 値 |
|:-----------|:------------|
| ユーザ名 | oracle |
| ユーザID | 1200 |
| プライマリ・グループ | oinstall |
| セカンダリ・グループ | dba,oper,backupdba,dgdba,kmdba,racdbahome |
| ディレクトリ | /home/oracle |
``` shell
useradd -u 1200 -g oinstall -G dba,oper,backupdba,dgdba,kmdba,racdba -d /home/oracle oracle
```
任意のパスワードを設定（覚えておいてください）

``` shell
passwd oracle
```

### 6.インストール・ディレクトリの作成

$ORACLE_BASEに設定するディレクトリの作成

``` shell
mkdir -p /opt/app/oracle
chown oracle:oinstall /opt/app/oracle
chmod -R 775 /opt/app/oracle
```
インベントリディレクトリの作成

``` shell
mkdir -p /opt/app/oraInventory
chown oracle:oinstall /opt/app/oraInventory
chmod -R 775 /opt/app/oraInventory
```

### 7.リソース権限の設定

``` shell
vi /etc/security/limits.conf
```
以下を追記

```
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768
```
<details><summary>リソース権限の説明</summary>
フォーマットとしては`<domain>　<type>　<item>　<value>`となっており
ぞれぞれ、`<ユーザ名> <リミットやデフォルトの最大値> <制限項目> <閾値>`である
上の設定で例を出すと
`oracle soft nproc 2047`
この場合はoracleユーザの最大プロセス数は2047までで、それ以上は強制的に停止される.
__ただし、その次の`oracle hard proc 16384`は`ulimitコマンド`で最大16384までプロセス最大数を増やせる__
- itemの説明
memlock：占有可能なメモリスペース（KB）
nofile：開くことができる最大ファイル数
stack：スタック（メモリ）最大サイズ （KB）
nproc： 最大プロセス数</details>
### 8.レスポンスファイルの作成
インストーラをサイレント・モードまたはレスポンス・ファイル・モードで使用する際に必要になります

``` shell
su oracle
vi database/response/db_install.rsp
```
下記の内容で修正

``` txt:db_install.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.1.0
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/opt/app/oraInventory
SELECTED_LANGUAGES=ja,en
ORACLE_BASE=/opt/app/oracle
ORACLE_HOME=/opt/app/oracle/product/12.1.0/dbhome_1
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oper
oracle.install.db.BACKUPDBA_GROUP=backupdba
oracle.install.db.DGDBA_GROUP=dgdba
oracle.install.db.KMDBA_GROUP=kmdba
DECLINE_SECURITY_UPDATES=true
```
__※上記で設定したフォルダパスは、存在しなければ作成してください。__

### 9.インストールに必要なものを準備

__●Java__
下記サイトからrpmをインストール
[Javaのrpmインストール](https://www.oracle.com/java/technologies/javase/javase-jdk8-downloads.html)
仮想環境に配置して、配置した場所を指定して下記コマンド実行

``` shell
rpm -ihv /root/jdk-8u271-linux-x64.rpm
java -version
```
パスが通っていたらOK
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/9a692c24-78d0-2b38-5c51-6371b86f8a7d.png)
●libnsl.so.1のインストール
__※CentOS8だと存在しない？　←環境によっては存在する可能性があるので、不要かもしれない__

``` shell
yum install libnsl 
```
### 10.インストール

``` shell
/home/oracle/database/runInstaller -ignoreSysPrereqs -waitforcompletion -silent -responseFile /home/oracle/database/response/db_install.rsp
```

・各パラメータ

| 項目名 | 説明 |
|:-----------|:------------|
| ignoreSysPrereqs | システムの前提条件チェックを無視します。 |
| waitforcompletion | インストーラーがフォアグランドで動きます。インストールが完了するまでコンソールは戻りません。 |
| silent | サイレントモードでインストールを指定する。 |
| responseFile | レスポンスファイルを絶対パスで指定する。 |
インストールが成功
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/5e6c6f1e-0df3-fded-c8a1-12c3dce99ed8.png)

インストールメッセージに記述があるシェルを実行する

``` shell
/opt/app/oraInventory/orainstRoot.sh
/opt/app/oracle/product/12.2.0/dbhome_1/root.sh
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/f14ebc00-300b-2e87-a6db-9de586ae52d3.png)

インストールを確認

``` shell
ls /opt/app/oracle/product/12.2.0/dbhome_1
```
いろいろインストールされている
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/b512d8d2-4c5c-6079-abde-5898c6e611fc.png)

__※インストール実行後、以下のエラーが発生した場合__
容量が足りていないので、追加してください。
私の場合は、`/sda3`にメモリを20G追加して成功しました
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/97f5865b-c214-0fe6-30c0-0f0990c06a35.png)
### 11.環境変数・サーバパラメータファイルの作成

環境変数の設定

``` shell
vi .bash_profile
```
以下を追記
__※ORACLE_SIDはインスタンス名なので好きなものを設定する__

```
umask 022
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/product/12.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LANG=ja_JP.UTF-8
export NLS_LANG=Japanese_Japan.AL32UTF8
export ORACLE_SID=mydb
```
<details><summary>環境変数の説明</summary>
`umask 022`はアクセス権のことです.
設定する種類は[ここを参照](https://docs.oracle.com/cd/E19683-01/816-3967/secfiles-62/index.html)
`LANG`:OS言語の設定
`NLS_LANG`:クライアント側の言語設定</details>

保存したら即時反映

``` shell
source ~/ .bash_profile
```
サーバパラメータファイルの作成
既にインストールによって、用意されているテンプレートを、命名インスタンス用にコピー

``` shell
cp $ORACLE_HOME/dbs/init.ora $ORACLE_HOME/dbs/init【インスタンス名】.ora
```
作成したパラメータファイルを修正

``` shell
vi $ORACLE_HOME/dbs/init【インスタンス名】.ora
```
修正箇所は<ORACLE_BASE>とdb_nameのみ

``` txt:インスタンス名.ora
db_name='mydb'
memory_target=1G
processes = 150
audit_file_dest='/opt/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/opt/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/opt/app/oracle/'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'
```
__※設定したフォルダパスが存在しない場合は、作成してください。__
重要な項目についての意味

| 項目名 | 説明 |
|:-----------|:------------|
| memory_target | 11g以降追加された項目で、SGAとPGAのメモリー管理が自動化される。自動化することで、「DB_CACHE_SIZE、SHARED_POOL_SIZE、LARGE_POOL_SIZE、JAVA_POOL_SIZE、STREAMS_POOL_SIZEを、システム負荷などに応じて、Oracleが自動で効果的にメモリを割り当ててくれる |
| processes | 200 + 全OP数*7 + 同時接続EE数*3 で設定する |
| db_block_size | サイズを大きくすると、ディスクとメモリーのI/O効率が向上する |
| db_domain | DBが作成されるネットワーク・ドメインの指定 |
| db_recovery_file_dest | 高速リカバリ領域の場所 |
| db_recovery_file_dest_size | 高速リカバリ領域で使用される最大層バイト数 |
| undo_tablespace | UNDOデータを格納するためのUNDO表領域 |
| control_files | 制御ファイルの指定 |
| compatible | DB互換性レベルの指定 |

サーバパラメータファイルの作成

``` shelll
sqlplus / as sysdba
```
``` sql
CREATE SPFILE FROM PFILE;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/68bafa0c-03d0-2c4d-d990-6de7967d3995.png)

`spfile【インスタンス名】.ora`ファイルが作成されていたらOK

``` shell
ls -l $ORACLE_HOME/dbs
```
### 12.インスタンスの起動

インスタンスの起動

``` shell
sqlplus / as sysdba
```
``` sql
STARTUP NOMOUNT
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/0a7d757c-4ba5-98a4-c6cb-8ca0104cb887.png)
__※インスタンスの起動失敗で以下のメッセージが出た場合__
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/4efec095-c3c9-c8f9-6eee-4d1aaa0b924b.png)

失敗したので調べてみると、どうやら、MEMORY_MAX_TARGET を設定する際、
MEMORY_MAX_TARGET に割り当てる物理メモリサイズよりも大きな領域を 
`/dev/shm`に割り当てておかなければならないという仕様
現在の設定では、MEMORY_TARGETは１G のうち、`/dev/shm`が以下の通り
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/e8927d8a-0426-ec81-e445-77aa51715b2d.png)
とりあえず__３G__ に拡張して成功しました
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/d10a3753-fa6c-968d-af92-521054083282.png)
### 13.データベースの作成

ログファイル、DBファイルを格納するためのディレクトリを作っておく

``` shell
mkdir -p $ORACLE_HOME/oradata/【インスタンス名】
```
データベースを作成
CREATE DBに関しての情報は[公式を参照](https://docs.oracle.com/cd/E82638_01/sqlrf/CREATE-DATABASE.html#GUID-ECE717DF-F116-4151-927C-2E51BB9DD39C)

``` sql
CREATE DATABASE 【データベース名】
   USER SYS IDENTIFIED BY sys_password
   USER SYSTEM IDENTIFIED BY system_password
   LOGFILE GROUP 1 ('/opt/app/oracle/product/12.2.0/dbhome_1/oradata/mydb/redo01.log') SIZE 100M,
           GROUP 2 ('/opt/app/oracle/product/12.2.0/dbhome_1/oradata/mydb/redo02.log') SIZE 100M,
           GROUP 3 ('/opt/app/oracle/product/12.2.0/dbhome_1/oradata/mydb/redo03.log') SIZE 100M
   MAXLOGHISTORY 1
   MAXLOGFILES 16
   MAXLOGMEMBERS 3
   MAXDATAFILES 1024
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/opt/app/oracle/product/12.2.0/dbhome_1/oradata/mydb/system01.dbf'
     SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
   SYSAUX DATAFILE '/u01/app/oracle/oradata/mydb/sysaux01.dbf'
     SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
   DEFAULT TABLESPACE users
      DATAFILE '/opt/app/oracle/product/12.1.0/dbhome_1/oradata/mydb/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/opt/app/oracle/product/12.1.0/dbhome_1/oradata/mydb/temp01.dbf'
      SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
   UNDO TABLESPACE undotbs1
      DATAFILE '/opt/app/oracle/product/12.1.0/dbhome_1/oradata/mydb/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
   USER_DATA TABLESPACE usertbs
      DATAFILE '/opt/app/oracle/product/12.1.0/dbhome_1/oradata/mydb/usertbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
```

テーブル表領域とインデックス表領域作成
CREATE TABLESPACEに関しては[公式を参照](https://docs.oracle.com/cd/E82638_01/sqlrf/CREATE-TABLESPACE.html#GUID-51F07BF5-EFAF-4910-9040-C473B86A8BF9)

``` sql
-- テーブル表領域
CREATE TABLESPACE apps_tbs LOGGING 
     DATAFILE '/opt/app/oracle/product/12.2.0/dbhome_1/oradata/mydb/apps01.dbf' 
     SIZE 500M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED 
     EXTENT MANAGEMENT LOCAL;
-- インデックス表領域
CREATE TABLESPACE indx_tbs LOGGING 
     DATAFILE '/opt/app/oracle/product/12.2.0/dbhome_1/oradata/mydb/indx01.dbf' 
     SIZE 100M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED 
     EXTENT MANAGEMENT LOCAL;
```
スクリプトの実行
データ・ディクショナリ、ビュー、シノニム、PL/SQLなどの有効化を行う

``` sql
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/sqlplus/admin/pubbld.sql
```
# アプリで連携
### ネットワークの設定
ファイアウォールを起動し、自動起動をON

``` shell
systemctl start firewalld
firewall -cmd --state
systemctl enable firewalld
```
ポートの確認

``` shell
firewall-cmd --list -all
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/e70ca2f6-cc41-2489-45e1-5c83963fa2aa.png)

5500と1521のポート穴あけ

``` shell
firewall-cmd --zone=public --add-port=5500.tcp --permanent
firewall-cmd --zone=public --add-port=1521.tcp --permanent
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/eef37176-bdd6-f648-5903-25373463efcf.png)

反映(portの場所に設定されていたらOK）

``` shell
firewall-cmd --reload
firewall-cmd --list-all
```
### リスナー設定

ログイン用のユーザ作成__(好きな名前とパスワードで）__

``` sql
 CREATE USER developer INDENTIFIED BY pass12 ACOUNT UNLOCK;
```
作成したユーザに権限を振っておきます

``` sql
grant create session,create table,create view,create sequence,create trigger,create synonym,unlimited tablespace to developer 2 ;
```
リスナー起動

``` shelll
lsnrctl start
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/3614f4da-3a13-3e76-2806-ad4a43f7983c.png)
sqldevなどのツールで確認したところ接続できたのでOK
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/e3b6634d-64e4-42e1-d3ba-adac9ae42616.png)

### アプリと連携
あとは好きな環境で、接続した結果を表示
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/498588/b958cbd3-12be-164b-1111-a7b7067a3cf4.png)