LoginSignup
1
3
@shirok(Shiro Kobayashi)in日本オラクル株式会社

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

Last updated at Posted at 2021-01-19

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接続してみてみた

■ Oracle Instant Client設定

● glibc Version確認

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

[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

・ダウンロード確認

[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ユーザーに設定

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ディレクトリ作成

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

2) Oracle Instant Client配置

[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配置

[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

2) クライアント資格証明(ウォレット)のダウンロード画面
[ウォレットのダウンロード]ボタンをクリックして、Client Credentialsをダウンロード
01_ATP接続02.png

● 環境変数設定

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ディレクトリ作成

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

2) Wallet Client Credentials配置と解凍

[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" ではない場合、変更する必要があります。

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

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

--修正後

[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のデータベース・サービス名

[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へ接続できることを確認

[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

2) zip解凍

[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接続

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

2) hrユーザー作成
hrユーザーのパスワードを決めて、以下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ユーザー接続

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

2) schema objects作成

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> select count(*) from employees;

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

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

● Oracle Instant Client
・Oracle Instant Client

● ORACLE マニュアル
・クラウドのドキュメント
・Oracle Database Database Sample Schemas, 21
・Installation of the Sample Schemas

1
3
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
1
3