Oracle Autonomous Database 21c へsqlplus接続してHRスキーマ作成してみてみた

Database接続テスト実施時に HRスキーマが必要なことがあります。
ということで、Autonomous Database へHRスキーマ作成してみてみます。
ついでに、古いバージョン12.2.0.1のClientで、新しい21cのAutonomous Databaseへ接続してみてみます。


# ■ 環境

|項目|内容|
|---|---|
Autonomous Database|		21c(21.1.0.0.0)
Oracle Instant Client| 	12.2.0.1, 19.8.0.0.0, or 21.1.0.0.0
Client OS |Oracle Linux Server release 7.9
Client OS の glibc | 2.17
$ORACLE_BASE		|	$HOME/oracle
$ORACLE_HOME		|	$HOME/oracle/instantclient_12_2|
$TNS_ADMIN		|	$ORACLE_HOME/network/admin|

Autonomous Databaseは事前に作成しておきます。
・参考: [Oracle Cloud：Autonomous DatabaseにSQLcl接続してみてみた](https://qiita.com/shirok/items/86355be72a47a840d10e)

# ■ Oracle Instant Client設定

## ● glibc Version確認
Instant Client for Linux x86-64 (64-bit) Version 21.1.0.0.0の場合、 glibc 2.14が要件なので、インストールできるかglibcバージョンを確認しておきます

```shell
[opc@oci-inst01 ~]$ rpm -q glibc
    glibc-2.17-317.0.1.ksplice1.el7.x86_64
```

## ● Download
以下URLからOracle Instant Clientをダウンロード
https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

今回、古いVersion 12.2.0.1.0 をダウンロードしてAutonomous Database 21cに接続できるか検証してみます。

 	●Instant Clientパッケージ（ZIP）
	　instantclient-basic-linux.x64-12.2.0.1.0.zip 
	　instantclient-sqlplus-linux.x64-12.2.0.1.0.zip 
 


**・ダウンロード確認**

```shell
[opc@oci-inst01 tmp]$ ls -l /tmp
	-rw-rw-r--. 1 opc opc 68965195  1月 18 08:28 instantclient-basic-linux.x64-12.2.0.1.0.zip
	-rw-r--r--. 1 opc opc   904309  1月 19 03:43 instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
```

## ● 環境変数設定
以下変数を Clinet OSユーザーに設定

```shell
export ORACLE_BASE=$HOME/oracle
export ORACLE_HOME=$HOME/oracle/instantclient_12_2
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME
export PATH=$PATH:$ORACLE_HOME/bin
```

## ● Install
ダウンロードしたOracle Instant Clientをインストール
今回Install Userの $HOME/oracle配下にインストールしてみます

**1) $ORACLE_BASEディレクトリ作成**

```shell
[opc@oci-inst01 ~]$ mkdir ~/oracle
```


**2) Oracle Instant Client配置**

```shell
[opc@oci-inst01 ~]$ cd ~/oracle
[opc@oci-inst01 oracle]$ unzip /tmp/instantclient-basic-linux.x64-12.2.0.1.0.zip
	Archive:  instantclient-basic-linux.x64-12.2.0.1.0.zip
	inflating: instantclient_12_2/adrci
	inflating: instantclient_12_2/BASIC_README
	inflating: instantclient_12_2/genezi
	inflating: instantclient_12_2/libclntshcore.so.12.1
	inflating: instantclient_12_2/libclntsh.so.12.1
	inflating: instantclient_12_2/libipc1.so
	inflating: instantclient_12_2/libmql1.so
	inflating: instantclient_12_2/libnnz12.so
	inflating: instantclient_12_2/libocci.so.12.1
	inflating: instantclient_12_2/libociei.so
	inflating: instantclient_12_2/libocijdbc12.so
	inflating: instantclient_12_2/libons.so
	inflating: instantclient_12_2/liboramysql12.so
	inflating: instantclient_12_2/ojdbc8.jar
	inflating: instantclient_12_2/uidrvci
	inflating: instantclient_12_2/xstreams.jar
```

**3) Oracle Instant Client sqlplus配置**

```shell
[opc@oci-inst01 oracle]$ unzip /tmp/instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
	Archive:  /tmp/instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
	inflating: instantclient_12_2/glogin.sql
	inflating: instantclient_12_2/libsqlplusic.so
	inflating: instantclient_12_2/libsqlplus.so
	inflating: instantclient_12_2/sqlplus
	inflating: instantclient_12_2/SQLPLUS_README
```


# ■ Autonomous database sqlplus接続テスト

## ● Download the Client Credentials
Autonomous Databaseに接続するための資格証明のzipファイルを作成したDatabase画面からダウンロードします




**1) Autonomous Database画面**
OCIコンソールから、作成したAutonomous Database画面にある [DB接続]をクリック
![01_ATP接続.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109087/33bfa88f-d3c0-9797-25ce-b1a51f8c2f7f.png)


**2) クライアント資格証明(ウォレット)のダウンロード画面**
[ウォレットのダウンロード]ボタンをクリックして、Client Credentialsをダウンロード
![01_ATP接続02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109087/5c08f06f-5371-1eba-1177-1284ee37b646.png)


## ● 環境変数設定

```shell
export ORACLE_HOME=/home/opc/oracle/instantclient_12_2
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME
export PATH=$PATH:$ORACLE_HOME
```


## ● Wallet Client Credentials配置
Autonomous Databaseに接続するための資格証明

**1) TNS_ADMINディレクトリ作成**

```shell
[opc@oci-inst01 ~]$ mkdir -p $ORACLE_HOME/network/admin
```


**2) Wallet Client Credentials配置と解凍**


```shell
[opc@oci-inst01 ~]$ cd $TNS_ADMIN
[opc@oci-inst01 admin]$ mv /tmp/Wallet_ATP.zip  $TNS_ADMIN/
[opc@oci-inst01 admin]$ unzip Wallet_ATP.zip
[opc@oci-inst01 admin]$ ls -l
	合計 80
	-rw-rw-r--. 1 opc opc  4280  1月 18 11:14 DataSourceSample.java
	-rw-rw-r--. 1 opc opc   317  1月 18 04:10 README
	-rw-rw-r--. 1 opc opc  5713  1月 18 11:21 UCPSample.java
	-rw-r--r--. 1 opc opc 20546  1月 18 10:52 Wallet_ATP.zip
	-rw-rw-r--. 1 opc opc  6733  1月 18 04:10 cwallet.sso
	-rw-rw-r--. 1 opc opc  6688  1月 18 04:10 ewallet.p12
	-rw-rw-r--. 1 opc opc  3275  1月 18 04:10 keystore.jks
	-rw-rw-r--. 1 opc opc   691  1月 18 04:10 ojdbc.properties
	-rw-rw-r--. 1 opc opc   114  1月 18 04:10 sqlnet.ora
	-rw-rw-r--. 1 opc opc  1771  1月 18 04:10 tnsnames.ora
	-rw-rw-r--. 1 opc opc  3336  1月 18 04:10 truststore.jks
```

## ● sqlnet.ora 修正
デフォルト sqlnet.ora ファイルの DIRECTORY パスは、"?/network/admin" になっているため、$TNS_ADMIN が "$ORACLE_HOME/network/admin" ではない場合、変更する必要があります。

**--修正前(デフォルト)**

```shell
[opc@oci-inst01 ~]$ cat sqlnet.ora
	WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))
	SSL_SERVER_DN_MATCH=yes
```


**--修正後**
```shell
[opc@oci-inst01 ~]$ cat sqlnet.ora
	WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/opc/oracle/instantclient_12_2/network/admin")))
	SSL_SERVER_DN_MATCH=yes
```


## ● tnsnames.ora確認
sqlplus接続するためのTNS接続名を確認

資格証明のzipファイルで提供されるtnsnames.oraファイルには、tpurgent、tp、high、mediumおよびlowと識別できる5つのデータベース・サービス名が含まれています。

・参考: [事前定義されたAutonomous Transaction Processingのデータベース・サービス名](https://docs.oracle.com/cd/E83857_01/paas/atp-cloud/atpug/connect-predefined.html#GUID-9747539B-FD46-44F1-8FF8-F5AC650F15BE)

```shell
[opc@oci-inst01 admin]$ cat tnsnames.ora

	atp_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=test_atp_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

	atp_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=test_atp_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

	atp_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=test_atp_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

	atp_tp = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=test_atp_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

	atp_tpurgent = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=test_atp_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))
```


## ● SQL*PLUS接続テスト
sqlplusでDatabaseへ接続できることを確認

```sql
[opc@oci-inst01 admin]$ sqlplus admin/Password@atp_high

	SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 19 04:16:29 2021

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

	Last Successful login time: Tue Jan 19 2021 00:21:17 +00:00

	Connected to:
	Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

	SQL> select BANNER_FULL from v$version

		BANNER_FULL
		--------------------------------------------------------------------------------
		Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
		Version 21.1.0.0.0
```



## ● SQL*PLUS接続で libnsl.so.1 エラーが出力された場合
次のエラーが出力された場合、libnsl.so.1 をインストールします。
**・ エラー内容**
```
[opc@oci-inst01 admin]$ sqlplus admin/<Password>
sqlplus: error while loading shared libraries: libnsl.so.1: cannot open shared object file: No such file or directory
```

**・ libnsl.so.1 Install**
```
sudo yum install /lib64/libnsl.so.1
sudo yum install libnsl.so.1
```
# ■ HR Sample Schema作成
JDBCコード・サンプル DataSourceSample.java はhrスキーマが必要なのでhrスキーマ作成

## ● Download the Sample Schemas scripts 
**1) ダウンロード**
GitHubからhrスキーマのあるSample Schemas scriptsのdb-sample-schemas-19.2.zip をダウンロード
・GitHub: [Oracle Database Sample Schemas](https://github.com/oracle/db-sample-schemas/releases/tag/v19.2)


**2) zip解凍**

```shell
[opc@oci-inst01 tmp]$ unzip db-sample-schemas-19.2.zip
[opc@oci-inst01 tmp]$ ls -l
	drwxrwxr-x. 10 opc                opc                    4096  8月 23  2019 db-sample-schemas-19.2
	-rw-r--r--.  1 opc                opc                28972417  1月 19 05:18 db-sample-schemas-19.2.zip
```


## ● Create the HR database user
**1) adminユーザーsqlplus接続**

```shell
[opc@oci-inst01 ~]$ sqlplus admin/Password@atp_high
```


**2) hrユーザー作成**
hrユーザーのパスワードを決めて、以下SQLを実行

```sql
SQL> CREATE USER hr IDENTIFIED BY Password;
SQL> ALTER USER hr DEFAULT TABLESPACE DATA QUOTA UNLIMITED ON DATA;
SQL> ALTER USER hr TEMPORARY TABLESPACE TEMP;
SQL> GRANT CREATE SESSION, CREATE VIEW, ALTER SESSION, CREATE SEQUENCE TO hr;
SQL> GRANT CREATE SYNONYM, CREATE DATABASE LINK, RESOURCE , UNLIMITED TABLESPACE TO hr;
SQL> GRANT execute ON sys.dbms_stats TO hr;
```


## ● create the HR schema objects
hrユーザーで接続して、ダウンロードしたsqlファイルで HR schema objects作成

**1) hrユーザー接続**

```shell
[opc@oci-inst01 ~]$ sqlplus hr/Password@atp_high
```

**2) schema objects作成**

```sql
SQL> @/tmp/db-sample-schemas-19.2/human_resources/hr_cre.sql
SQL> @/tmp/db-sample-schemas-19.2/human_resources/hr_popul.sql
SQL> @/tmp/db-sample-schemas-19.2/human_resources/hr_idx.sql
SQL> @/tmp/db-sample-schemas-19.2/human_resources/hr_code.sql 
SQL> @/tmp/db-sample-schemas-19.2/human_resources/hr_comnt.sql
SQL> @/tmp/db-sample-schemas-19.2/human_resources/hr_analz.sql
```

**3) schema objects作成確認**
作成されたTABLE、employees表の行数など確認してschema objects作成されたことを確認

```sql
SQL> select count(*) from employees;

	COUNT(*)
	----------
		107
```


```sql
SQL> select TABLE_NAME from user_tables;

	TABLE_NAME
	----------------------------
	REGIONS
	COUNTRIES
	LOCATIONS
	DEPARTMENTS
	JOBS
	EMPLOYEES
	JOB_HISTORY

	7 rows selected.
```


## ■ 参考
**● GitHub**
・[Oracle Database Sample Schemas](https://github.com/oracle/db-sample-schemas/releases/tag/v19.2)

**● Oracle Instant Client**
・[Oracle Instant Client](https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html)

**● ORACLE マニュアル**
・[クラウドのドキュメント](https://docs.oracle.com/cd/E83857_01/paas/index.html)
・[Oracle Database Database Sample Schemas, 21](https://docs.oracle.com/en/database/oracle/oracle-database/21/comsc/index.html)
・[Installation of the Sample Schemas](https://docs.oracle.com/en/database/oracle/oracle-database/21/comsc/installing-sample-schemas.html#GUID-1E645D09-F91F-4BA6-A286-57C5EC66321D)