Oracle 19cをCentOS7にCUIからインストール 

#Oracle 19cをCentOS7.7にCUIからインストールしてみる

参考
[Database Installation Guide for Linux](https://docs.oracle.com/en/database/oracle/oracle-database/19/ladbi/lot.html)

##環境
CentOS Linux release 7.7.1908

##事前準備
###Oracle Preinstallation RPM をインストール
参考: [Installing Oracle Database Using RPM Packages - Running RPM Packages to Install Oracle Database](https://docs.oracle.com/en/database/oracle/oracle-database/19/ladbi/running-rpm-packages-to-install-oracle-database.html#GUID-BB7C11E3-D385-4A2F-9EAF-75F4F0AACF02)


/tmp など適当な場所にダウンロードしてからyumでlocalinstallで実行する

```
# curl -OL https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
# yum -y localinstall oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
```

##Oracle本体のインストール

以下のサイトからインストール用のrpmをダウンロード
https://www.oracle.com/database/technologies/oracle-database-software-downloads.html
https://www.oracle.com/technetwork/jp/database/enterprise-edition/downloads/index.html

ダウンロードしてきたrpmファイルを指定してインストールを実行

```
# yum -y localinstall oracle-database-ee-19c-1.0-1.x86_64.rpm

Loaded plugins: fastestmirror, langpacks
Examining oracle-database-ee-19c-1.0-1.x86_64.rpm: oracle-database-ee-19c-1.0-1.x86_64
Marking oracle-database-ee-19c-1.0-1.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-ee-19c.x86_64 0:1.0-1 will be installed
--> Finished Dependency Resolution
gitlab_gitlab-ee/x86_64/signature                                                       |  836 B  00:00:00
gitlab_gitlab-ee/x86_64/signature                                                       | 1.0 kB  00:00:00 !!!
gitlab_gitlab-ee-source/signature                                                       |  836 B  00:00:00
gitlab_gitlab-ee-source/signature                                                       |  951 B  00:00:00 !!!

Dependencies Resolved

==============================================================================================================
 Package                          Arch             Version      Repository                               Size
==============================================================================================================
Installing:
 oracle-database-ee-19c           x86_64           1.0-1        /oracle-database-ee-19c-1.0-1.x86_64     6.9 G

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

Total size: 6.9 G
Installed size: 6.9 G
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : oracle-database-ee-19c-1.0-1.x86_64                                                        1/1
[INFO] Executing post installation scripts...
[INFO] Oracle home installed successfully and ready to be configured.
To configure a sample Oracle Database you can execute the following service configuration script 
 as root: /etc/init.d/oracledb_ORCLCDB-19c configure
  Verifying  : oracle-database-ee-19c-1.0-1.x86_64                                                        1/1

Installed:
  oracle-database-ee-19c.x86_64 0:1.0-1

Complete!
```
デフォルトで /opt/oracle 以下にインストールされる。



##Databaseの作成とconfigure

※ORACLE_SID と ORACLE_HOME を環境変数に設定し、configureを実行

```
# export ORACLE_BASE=/opt/oracle
# export ORACLE_HOME=$ORACLE_BASE/product/19c/dbhome_1
# export ORACLE_SID=ORCLCDB
# /etc/init.d/oracledb_ORCLCDB-19c configure

Configuring Oracle Database ORCLCDB.
[WARNING] [DBT-11209] Current available memory is less than the required available memory (1,515MB) 
 for creating the database.
   CAUSE: Following nodes do not have required available memory :
 Node:localhost         Available memory:930.7305MB (953068.0KB)

Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
43% complete
46% complete
Completing Database Creation
51% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/ORCLCDB.
Database Information:
Global Database Name:ORCLCDB
System Identifier(SID):ORCLCDB
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log" for further details.

Database configuration completed successfully.
 The passwords were auto generated, you must change them by connecting 
 to the database using 'sqlplus / as sysdba' as the oracle user.
```

##oradata以下のファイル構成

デフォルトで以下のような感じになっている
※ ORACLE_SID=ORCLCDB

```
/opt/oracle/oradata
|--ORCLCDB
|  |--control01.ctl
|  |--control02.ctl
|  |--ORCLPDB1
|  |  |--sysaux01.dbf
|  |  |--system01.dbf
|  |  |--temp01.dbf
|  |  |--undotbs01.dbf
|  |  |--users01.dbf
|  |--pdbseed
|  |  |--sysaux01.dbf
|  |  |--system01.dbf
|  |  |--temp012019-11-02_02-33-18-414-AM.dbf
|  |  |--undotbs01.dbf
|  |--redo01.log
|  |--redo02.log
|  |--redo03.log
|  |--sysaux01.dbf
|  |--system01.dbf
|  |--temp01.dbf
|  |--undotbs01.dbf
|  |--users01.dbf
```

##ポートを開放

```
# firewall-cmd --permanent --add-port=1521/tcp
# firewall-cmd --permanent --add-port=5500/tcp
# firewall-cmd --reload
```

###ユーザ:oracle でDBを使用するための環境設定
oracleユーザに切り替え

```
# su - oracle
```

~/.bash_profileに環境変数を追加し、$ORACLE_HOME/bin へのパスも通す

```
export ORACLE_SID=ORCLCDB
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19c/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
```
※ORACLE_SID, ORACLE_HOMEはconfigure実行時と同じ設定  
(先にこちらを設定してからsudoでconfigureしても良いかも)

###シェルに設定を反映

```
$ source ~/.bash_profile
```

##SQLclで接続
sqlplusでも良いが、出力整形が楽で見やすいのでSQLclを使用

```
$ sql / as sysdba
```

確認

```
# 出力を整形して見やすく設定
SQL> set sqlformat ansiconsole

# コンテナ内を確認
SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
NAME         CON_ID         DBID      CON_UID GUID
CDB$ROOT          1   2794305375            1 86B637B62FDF7A65E053F706E80A27CA
PDB$SEED          2   1304945324   1304945324 964D5C96F6B71383E055000000000001
ORCLPDB1          3   3849451649   3849451649 964D860AFD092C9CE055000000000001

# PDBステータスを確認
SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
  PDB_ID PDB_NAME   STATUS
       2 PDB$SEED   NORMAL
       3 ORCLPDB1   NORMAL

# PDBのOPEN_MODEを確認
SQL> SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
NAME       OPEN_MODE    RESTRICTED   OPEN_TIME
PDB$SEED   READ ONLY    NO           02-NOV-19 02.44.48.418000000 AM +09:00
ORCLPDB1   READ WRITE   NO           02-NOV-19 02.45.10.232000000 AM +09:00
```


## sys, system のパスワードを設定
```
SQL> alter user sys identified by sys_passwd;
SQL> alter user system identified by system_passwd;
```
※デフォルトで、change_on_install、managerなどのパスワードではログインできなくなっている

##接続先DBの変更
現在の接続先を確認

```
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
```
↑ CDBに接続している

###接続先をORCLPDB1に変更
```
SQL> alter session set container = ORCLPDB1;
```

##TableSpaceの作成

```
CREATE TABLESPACE tbs_01
  DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbs_01.dbf' SIZE 10M 
  AUTOEXTEND ON NEXT 500K MAXSIZE 100M
  /

CREATE TEMPORARY TABLESPACE tbs_temp_01
  TEMPFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbs_temp_01.dbf'
  SIZE 5M AUTOEXTEND ON TABLESPACE GROUP tbs_grp01
  /
```

##userの作成
```
SQL> CREATE USER user01 IDENTIFIED BY passwd01
  DEFAULT TABLESPACE tbs_01
  TEMPORARY TABLESPACE tbs_temp_01
  /

SQL> GRANT CONNECT TO user01;
SQL> GRANT RESOURCE TO user01;
```

###PDB(ORCLPDB1)に接続してみる
構文: `sql [user]/[password]@//[host]:[port]/[接続先DB];`

```
$ sql user01/passwd01@//localhost:1521/ORCLPDB1
```

##クライアントPCから SQL Developerで接続

###ユーザー(user01)での接続設定
※user01は orclpdb1に作成したので、orclpdb1 をサービス名に指定する
![SQLDeveloperConnectionSetting.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/226021/ac0673c1-02d8-c104-9a96-5759b8168e29.jpeg)

###sysでのCDBへの接続設定
※CDBのサービス名は ORCLCDBを指定
![SQLDeveloperConnectionSetting_sys.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/226021/8f70d372-5cdb-ddcc-5532-64f7aa49eff2.jpeg)





###サービス名はlsnrctlで確認可能
```
$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 02-NOV-2019 19:32:52

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                02-NOV-2019 02:23:37
Uptime                    0 days 17 hr. 9 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=5500))
    (Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "964d860afd092c9ce055000000000001" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "orclpdb1" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully
```

## Enterprise Manager Expressに接続
参考: [3.4.3 Starting EM Express for a PDB](https://docs.oracle.com/en/database/oracle/oracle-database/19/admqs/getting-started-with-database-administration.html#GUID-F1C3023C-35DC-4086-A63F-F58F3FFC2923)

接続先をCDB$ROOT に変更し、CDBのグローバルポートを有効化する

```
SQL> alter session set container=CDB$ROOT;
SQL> exec dbms_xdb_config.setglobalportenabled(TRUE);
```

ブラウザから Enterprise Manager ExpressのURLにアクセス

```
https://192.168.137.71:5500/em
```

###sys でPDB(orclpdb1)を指定してログイン
![emLogin.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/226021/8a64f69b-f7c3-a19c-4f23-27d127e0e4bb.jpeg)
![em_express.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/226021/748b0d75-12e0-ef67-c21c-96d13a5fa8bd.jpeg)




## 使用した値
| 項目 | 値 |
|:-----------------|:------------------|
| 検証DBサーバーIP | 192.168.137.71 |
| 接続ポート |1521 |
| em用ポート |5500 |
| ORACLE\_SID |ORCLCDB |
| ORACLE\_BASE | /opt/oracle |
| ORACLE\_HOME | $ORACLE\_BASE/product/19c/dbhome\_1 |
| user01 | passwd01 |
| sys | sys\_passwd |
| system | system\_passwd |