MongoDB ShellからOracle Database 23c Free - Developer Releaseに接続してみた

# はじめに
JSON-Relational Dualityの検証環境を構築するために、Oracle Database 23c Free - Developer Releaseをインストールした環境にOracle Database API for MongoDBを導入し、MongoDBのクライアントであるMongoDB ShellからOracle Database 23cにアクセスしてみました。

# 0.Oracle Database 23c Free - Developer Releaseのインストール

こちらの記事に従って、Oracle Linux 8にOracle Database 23c Free - Developer Releaseをインストールします。

https://qiita.com/500InternalServerError/items/45b4dd0cfc04fb1bab5e

# 1. Java(JDK)のインストール
JDKをインストールします。
```
[opc@23c ~]$ sudo dnf install -y java
Last metadata expiration check: 0:37:18 ago on Mon 15 May 2023 12:48:51 PM GMT.
Dependencies resolved.
==============================================================================================================
 Package                 Architecture       Version                        Repository                    Size
==============================================================================================================
Installing:
 jdk-11.0.10             x86_64             2000:11.0.10-ga                ol8_oci_included             156 M

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

Total download size: 156 M
Installed size: 292 M
Downloading Packages:
jdk-11.0.10+8_linux-x64_bin.rpm                                                46 MB/s | 156 MB     00:03    
--------------------------------------------------------------------------------------------------------------
Total                                                                          46 MB/s | 156 MB     00:03     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                      1/1 
  Installing       : jdk-11.0.10-2000:11.0.10-ga.x86_64                                                   1/1 
  Running scriptlet: jdk-11.0.10-2000:11.0.10-ga.x86_64                                                   1/1 
  Verifying        : jdk-11.0.10-2000:11.0.10-ga.x86_64                                                   1/1 

Installed:
  jdk-11.0.10-2000:11.0.10-ga.x86_64                                                                          

Complete!
[opc@23c ~]$ 
```

# 2.MongoDB Shellのインストール

MongoDBのリポジトリを追加します。
```
[opc@23c ~]$ sudo vi /etc/yum.repos.d/mongodb-org-6.0.repo
```

```text:/etc/yum.repos.d/mongodb-org-6.0.repo
[mongodb-org-6.0]
name=MongoDB Repository
baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/6.0/x86_64/
gpgcheck=1
enabled=1
gpgkey=https://www.mongodb.org/static/pgp/server-6.0.asc
```
MongoDB Shellをインストールします。
```
[opc@23c ~]$ sudo dnf install -y mongodb-mongosh
Last metadata expiration check: 0:03:03 ago on Mon 15 May 2023 01:28:37 PM GMT.
Dependencies resolved.
==============================================================================================================
 Package                      Architecture        Version                  Repository                    Size
==============================================================================================================
Installing:
 mongodb-mongosh              x86_64              1.8.2-1.el8              mongodb-org-6.0               43 M

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

Total download size: 43 M
Installed size: 172 M
Downloading Packages:
mongodb-mongosh-1.8.2.x86_64.rpm                                               24 MB/s |  43 MB     00:01    
--------------------------------------------------------------------------------------------------------------
Total                                                                          24 MB/s |  43 MB     00:01     
MongoDB Repository                                                            964  B/s | 1.7 kB     00:01    
Importing GPG key 0x64C3C388:
 Userid     : "MongoDB 6.0 Release Signing Key <packaging@mongodb.com>"
 Fingerprint: 39BD 841E 4BE5 FB19 5A65 400E 6A26 B1AE 64C3 C388
 From       : https://www.mongodb.org/static/pgp/server-6.0.asc
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                      1/1 
  Installing       : mongodb-mongosh-1.8.2-1.el8.x86_64                                                   1/1 
  Running scriptlet: mongodb-mongosh-1.8.2-1.el8.x86_64                                                   1/1 
  Verifying        : mongodb-mongosh-1.8.2-1.el8.x86_64                                                   1/1 

Installed:
  mongodb-mongosh-1.8.2-1.el8.x86_64                                                                          

Complete!
[opc@23c ~]$ 
```
# 3.ORDSのインストール
oracleユーザにスイッチします。
```
[opc@23c ~]$ sudo su - oracle
Last login: Mon May 15 12:32:08 GMT 2023 on pts/0
[oracle@23c ~]$
```
ORDSを作成するディレクトリを作成します。
```
[oracle@23c ~]$ mkdir /opt/oracle/ords
[oracle@23c ~]$ 
```
作成したディレクトリに移動します。
```
[oracle@23c ~]$ cd /opt/oracle/ords
[oracle@23c ords]$ 
```
ORDSのインストーラをダウンロードします。
```
[oracle@23c ords]$ wget https://download.oracle.com/otn_software/java/ords/ords-23.1.2.115.1944.zip
--2023-05-15 13:34:33--  https://download.oracle.com/otn_software/java/ords/ords-23.1.2.115.1944.zip
Resolving download.oracle.com (download.oracle.com)... 23.45.52.112
Connecting to download.oracle.com (download.oracle.com)|23.45.52.112|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 96775758 (92M) [application/zip]
Saving to: ‘ords-23.1.2.115.1944.zip’

ords-23.1.2.115.1944.zip    100%[=========================================>]  92.29M  77.8MB/s    in 1.2s    

2023-05-15 13:34:35 (77.8 MB/s) - ‘ords-23.1.2.115.1944.zip’ saved [96775758/96775758]

[oracle@23c ords]$
```
ダウンロードしたファイルを解凍します。
```
[oracle@23c ords]$ unzip ords-23.1.2.115.1944.zip 
Archive:  ords-23.1.2.115.1944.zip
   creating: bin/
   creating: linux-support/
   creating: linux-support/man/
   creating: examples/
   creating: examples/application-container/
＜略＞
  inflating: ords.war                
  inflating: examples/plugins/lib/ords-plugin-api-23.1.2.115.1944.jar  
  inflating: examples/plugins/lib/jakarta.servlet-api-4.0.3.jar  
  inflating: examples/plugins/lib/ords-plugin-apt-23.1.2.115.1944.jar  
  inflating: examples/plugins/lib/jakarta.inject-api-2.0.0.jar  
  inflating: examples/plugins/lib/ords-plugin-api-23.1.2.115.1944-javadoc.jar  
[oracle@23c ords]$ 
```
/opt/oracle/ords/binをPATHに追加します。
```
[oracle@23c ~]$  export PATH=$PATH:/opt/oracle/ords/bin
```
ordsのインストールを開始します。

```
[oracle@23c ords]$ ords install
2023-05-15T14:01:16.192Z INFO        Your configuration folder /opt/oracle/ords is located in ORDS product folder.  Oracle recommends to use a different configuration folder.

ORDS: Release 23.1 Production on Mon May 15 14:01:16 2023

Copyright (c) 2010, 2023, Oracle.

Configuration:
  /opt/oracle/ords/

The configuration folder /opt/oracle/ords does not contain any configuration files.

Oracle REST Data Services - Interactive Install
```
インストール・タイプには2を指定します。
```

  Enter a number to select the type of installation
    [1] Install or upgrade ORDS in the database only
    [2] Create or update a database pool and install/upgrade ORDS in the database
    [3] Create or update a database pool only
  Choose [2]: 2
```
データベースの接続タイプは1を指定します。
```
  Enter a number to select the database connection type to use
    [1] Basic (host name, port, service name)
    [2] TNS (TNS alias, TNS directory)
    [3] Custom database URL
  Choose [1]: 1
```
ホスト名はlocalhost、ポートは1521、サービス名はfreepdb1を指定します。
```
  Enter the database host name [localhost]: 
  Enter the database listen port [1521]: 
  Enter the database service name [orcl]: freepdb1
```
管理者ユーザ名にはsys、パスワードにはsysユーザのパスワードを入力します。
```
  Provide database user name with administrator privileges.
  Enter the administrator username: sys
  Enter the database password for SYS AS SYSDBA: 
```
デフォルト・テーブルスペースはSYSAUX、一時表領域はTEMPを指定します。
（いずれもデフォルト）

```
Connecting to database user: SYS AS SYSDBA url: jdbc:oracle:thin:@//localhost:1521/freepdb1

Retrieving information.
  Enter the default tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [SYSAUX]: 
  Enter the temporary tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [TEMP]: 
```
有効にする機能は1を選択します。
```
  Enter a number to select additional feature(s) to enable:
    [1] Database Actions  (Enables all features)
    [2] REST Enabled SQL and Database API
    [3] REST Enabled SQL
    [4] Database API
    [5] None
  Choose [1]: 
```
2を入力して、設定のみを行います。
```
  Enter a number to configure and start ORDS in standalone mode
    [1] Configure and start ORDS in standalone mode
    [2] Skip
  Choose [1]:2
```
しばらく待つと、ORDSのインストールが完了します。
```
＜略＞
INFO: 14:09:04 Validating objects for Oracle REST Data Services.
VALIDATION: 14:09:04 Starting validation for schema: ORDS_METADATA
VALIDATION: 14:09:04 Validating objects
VALIDATION: 14:09:05 Validating roles granted to ORDS_METADATA and
ORDS_PUBLIC_USER
VALIDATION: 14:09:05 Validating ORDS Public Synonyms
VALIDATION: 14:09:05 Total objects: 328, invalid objects: 0, missing objects: 0
VALIDATION: 14:09:05     94  INDEX
VALIDATION: 14:09:05      3  LOB
VALIDATION: 14:09:05     23  PACKAGE
VALIDATION: 14:09:05     22  PACKAGE BODY
VALIDATION: 14:09:05      1  PROCEDURE
VALIDATION: 14:09:05     58  PUBLIC SYNONYM
VALIDATION: 14:09:05      1  SEQUENCE
VALIDATION: 14:09:05     32  TABLE
VALIDATION: 14:09:05     32  TRIGGER
VALIDATION: 14:09:05     20  TYPE
VALIDATION: 14:09:05      6  TYPE BODY
VALIDATION: 14:09:05     36  VIEW
VALIDATION: 14:09:05 Validation completed.
INFO: 14:09:05 Completed validation for Oracle REST Data Services.


PL/SQL procedure successfully completed.


Commit complete.

2023-05-15T14:09:05.280Z INFO        Completed installation for Oracle REST Data Services version 23.1.2.r1151944. Elapsed time: 00:00:19.867 

[*** Info: Completed installation for Oracle REST Data Services version 23.1.2.r1151944. Elapsed time: 00:00:19.867 
 ]
[oracle@23c ords]$
```
ORDSでMongoAPIを使用可能にします。
```
[oracle@23c ords]$ ords config set mongo.enabled true
2023-05-15T14:09:55.400Z INFO        Your configuration folder /opt/oracle/ords is located in ORDS product folder.  Oracle recommends to use a different configuration folder.

ORDS: Release 23.1 Production on Mon May 15 14:09:55 2023

Copyright (c) 2010, 2023, Oracle.

Configuration:
  /opt/oracle/ords/

The global setting named: mongo.enabled was set to: true
[oracle@23c ords]$
```
ORDSを起動します。
```
[oracle@23c ords]$ ords serve &
[1] 9473
[oracle@23c ords]$ 2023-05-15T14:18:06.889Z INFO        Your configuration folder /opt/oracle/ords is located in ORDS product folder.  Oracle recommends to use a different configuration folder.

ORDS: Release 23.1 Production on Mon May 15 14:18:06 2023

Copyright (c) 2010, 2023, Oracle.

Configuration:
  /opt/oracle/ords/

2023-05-15T14:18:07.385Z INFO        Disabling document root because the specified folder does not exist: /opt/oracle/ords/global/doc_root
2023-05-15T14:18:07.671Z INFO        Oracle API for MongoDB listening on port: 27017
2023-05-15T14:18:07.672Z INFO        The Oracle API for MongoDB connection string is: 
         mongodb://[{user}:{password}@]localhost:27017/{user}?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true
2023-05-15T14:18:13.021Z INFO        Configuration properties for: |default|lo|
db.servicename=freepdb1
awt.toolkit=sun.awt.X11.XToolkit
java.specification.version=11
conf.use.wallet=true
sun.cpu.isalist=
sun.jnu.encoding=UTF-8
user.region=US
java.class.path=/opt/oracle/ords/ords.war
java.vm.vendor=Oracle Corporation
sun.arch.data.model=64
nashorn.args=--no-deprecation-warning
mongo.enabled=true
java.vendor.url=https://openjdk.java.net/
resource.templates.enabled=false
user.timezone=UTC
db.port=1521
java.vm.specification.version=11
os.name=Linux
sun.java.launcher=SUN_STANDARD
user.country=US
sun.boot.library.path=/usr/java/jdk-11.0.10/lib
sun.java.command=/opt/oracle/ords/ords.war serve
jdk.debug=release
sun.cpu.endian=little
user.home=/home/oracle
oracle.dbtools.launcher.executable.jar.path=/opt/oracle/ords/ords.war
user.language=en
java.specification.vendor=Oracle Corporation
java.version.date=2021-01-19
database.api.enabled=true
java.home=/usr/java/jdk-11.0.10
db.username=ORDS_PUBLIC_USER
file.separator=/
java.vm.compressedOopsMode=Zero based
line.separator=

restEnabledSql.active=true
java.specification.name=Java Platform API Specification
java.vm.specification.vendor=Oracle Corporation
java.awt.graphicsenv=sun.awt.X11GraphicsEnvironment
feature.sdw=true
java.awt.headless=true
db.hostname=localhost
db.password=******
sun.management.compiler=HotSpot 64-Bit Tiered Compilers
security.requestValidationFunction=ords_util.authorize_plsql_gateway
java.runtime.version=11.0.10+8-LTS-162
user.name=oracle
path.separator=:
os.version=5.15.0-100.96.32.el8uek.x86_64
java.runtime.name=Java(TM) SE Runtime Environment
file.encoding=UTF-8
java.vm.name=Java HotSpot(TM) 64-Bit Server VM
java.vendor.version=18.9
java.vendor.url.bug=https://bugreport.java.com/bugreport/
java.io.tmpdir=/tmp
oracle.dbtools.cmdline.ShellCommand=ords
java.version=11.0.10
user.dir=/opt/oracle/ords
os.arch=amd64
java.vm.specification.name=Java Virtual Machine Specification
java.awt.printerjob=sun.print.PSPrinterJob
oracle.dbtools.cmdline.home=/opt/oracle/ords
sun.os.patch.level=unknown
java.library.path=/usr/java/packages/lib:/usr/lib64:/lib64:/lib:/usr/lib
java.vendor=Oracle Corporation
java.vm.info=mixed mode
java.vm.version=11.0.10+8-LTS-162
sun.io.unicode.encoding=UnicodeLittle
db.connectionType=basic
java.class.version=55.0

2023-05-15T14:18:13.023Z WARNING     *** jdbc.MaxLimit in configuration |default|lo| is using a value of 20, this setting may not be sized adequately for a production environment ***
2023-05-15T14:18:13.023Z WARNING     *** jdbc.InitialLimit in configuration |default|lo| is using a value of 3, this setting may not be sized adequately for a production environment ***
2023-05-15T14:18:19.246Z INFO        

Mapped local pools from /opt/oracle/ords/databases:
  /ords/                              => default                        => VALID     


2023-05-15T14:18:19.389Z INFO        Oracle REST Data Services initialized
Oracle REST Data Services version : 23.1.2.r1151944
Oracle REST Data Services server info: jetty/10.0.12
Oracle REST Data Services java info: Java HotSpot(TM) 64-Bit Server VM 11.0.10+8-LTS-162


[oracle@23c ords]$ 
```
# 4.MongoDB Shellから接続するDBユーザの作成

SQL*PlusからsystemユーザとしてPDBに接続します。
```
[oracle@23c ~]$ sqlplus system/Demo#1Demo#1@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon May 15 14:22:39 2023
Version 23.2.0.0.0

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

Last Successful login time: Mon May 15 2023 12:16:28 +00:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
```
MongoDB Shellから接続するユーザmongo_testを作成して必要なロールを付与します。
```
SQL> CREATE USER mongo_test IDENTIFIED BY "MyPassword1!";

User created.

SQL> GRANT SODA_APP, CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE, CREATE JOB, CREATE TRIGGER, UNLIMITED TABLESPACE TO mongo_test;

Grant succeeded.

SQL> 
```
mongo_testユーザとしてPDBに接続します。
```
SQL> connect mongo_test/MyPassword1!@freepdb1
Connected.
SQL> 
```
スキーマに対してORDSを有効化します。
```
SQL> exec ORDS.ENABLE_SCHEMA;

PL/SQL procedure successfully completed.

SQL>
```
SQL*Plusを終了します。
```
SQL> exit
Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
[oracle@23c ~]$ 
```
# 4. MongoDB ShellからOracle Database 23c Freeに接続

MongoDB ShellからOracle Database 23cのmongo_testユーザに接続します。
ORDSが自己署名の証明書を使用しているので、--tlsAllowInvalidCertidicatesオプションを使用します。
```
[oracle@23c ~]$ mongosh --tlsAllowInvalidCertificates 'mongodb://mongo_test:MyPassword1!@localhost:27017/mongo_test?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'
Current Mongosh Log ID:	646240af54b0d29c19113685
Connecting to:		mongodb://<credentials>@localhost:27017/mongo_test?authMechanism=PLAIN&authSource=%24external&ssl=true&retryWrites=false&loadBalanced=true&serverSelectionTimeoutMS=2000&tlsAllowInvalidCertificates=true&appName=mongosh+1.8.2
Using MongoDB:		4.2.14
Using Mongosh:		1.8.2

For mongosh info see: https://docs.mongodb.com/mongodb-shell/


To help improve our products, anonymous usage data is collected and sent to MongoDB periodically (https://www.mongodb.com/legal/privacy-policy).
You can opt-out by running the disableTelemetry() command.

mongo_test> db.collections.find();

mongo_test>
```
MongoDB ShellからOracle Database 23cに接続できました。