はじめに
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をインストールします。
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
/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に接続できました。