我尝试使用MongoDB Shell连接到Oracle Database 23c Free – Developer Release
首先
我在安装了Oracle Database 23c Free – Developer Release的环境中，引入了MongoDB的客户端Oracle Database API for MongoDB来构建JSON-Relational Duality验证环境，并通过MongoDB Shell访问Oracle Database 23c。
0.安装免费的Oracle数据库23c开发者版本
根据这篇文章的指示，在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
[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]$
使MongoAPI能够在ORDS中使用。
[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]$
创建一个可以通过MongoDB Shell连接的数据库用户。
使用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
创建一个名为mongo_test的用户并授予所需的角色，使用MongoDB Shell进行连接。
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。
我将使用MongoDB Shell连接到Oracle Database 23c的mongo_test用户。
由于ORDS使用自签名证书，我将使用–tlsAllowInvalidCertificates选项。
[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。