とりあえず動かしてみたので、行った作業を記録として残しておきます。
今回の作業で参照したドキュメントは以下です。
systemctl stop ords
dnf -y --repofrompath ol8_oracle_software,http://yum.oracle.com/repo/OracleLinux/OL8/oracle/software/x86_64 update ords
[root@apex3 ~]# systemctl stop ords
[root@apex3 ~]# dnf -y --repofrompath ol8_oracle_software,http://yum.oracle.com/repo/OracleLinux/OL8/oracle/software/x86_64 update ords
Failed to set locale, defaulting to C.UTF-8
Added ol8_oracle_software repo from http://yum.oracle.com/repo/OracleLinux/OL8/oracle/software/x86_64
Oracle Linux 8 BaseOS Latest (x86_64) 109 kB/s | 3.6 kB 00:00
Oracle Linux 8 BaseOS Latest (x86_64) 66 MB/s | 64 MB 00:00
Oracle Linux 8 Application Stream (x86_64) 133 kB/s | 3.9 kB 00:00
Oracle Linux 8 Application Stream (x86_64) 65 MB/s | 49 MB 00:00
Latest Unbreakable Enterprise Kernel Release 7 for Oracle Linux 8 (x8 98 kB/s | 3.0 kB 00:00
Latest Unbreakable Enterprise Kernel Release 7 for Oracle Linux 8 (x8 36 MB/s | 23 MB 00:00
ol8_oracle_software 71 kB/s | 2.9 kB 00:00
ol8_oracle_software 870 kB/s | 105 kB 00:00
Dependencies resolved.
======================================================================================================
Package Architecture Version Repository Size
======================================================================================================
Upgrading:
ords noarch 23.3.0-10.el8 ol8_oracle_software 107 M
Transaction Summary
======================================================================================================
Upgrade 1 Package
Total download size: 107 M
Downloading Packages:
ords-23.3.0-10.el8.noarch.rpm 42 MB/s | 107 MB 00:02
------------------------------------------------------------------------------------------------------
Total 42 MB/s | 107 MB 00:02
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Running scriptlet: ords-23.3.0-10.el8.noarch 1/1
Running scriptlet: ords-23.3.0-10.el8.noarch 1/2
Upgrading : ords-23.3.0-10.el8.noarch 1/2
Running scriptlet: ords-23.3.0-10.el8.noarch 1/2
INFO: Before starting ORDS, run the below command as user oracle to migrate
INFO: your configuration from previous versions to 22.1.0:
ords --config /etc/ords/config install --legacy-config <path_to_legacy_config> --admin-user <db_admin_user>
INFO: To enable the ORDS service during startup, run the below command:
sudo systemctl enable ords
Running scriptlet: ords-23.2.3-2.el8.noarch 2/2
Cleanup : ords-23.2.3-2.el8.noarch 2/2
Running scriptlet: ords-23.3.0-10.el8.noarch 2/2
Running scriptlet: ords-23.2.3-2.el8.noarch 2/2
Verifying : ords-23.3.0-10.el8.noarch 1/2
Verifying : ords-23.2.3-2.el8.noarch 2/2
Upgraded:
ords-23.3.0-10.el8.noarch
Complete!
[root@apex3 ~]#
ords.warが最新（この時点では23.3.0）にアップデートされました。続けて、データベースにインストールされているORDS_METADATAのスキーマをアップグレードします。
[root@apex3 ~]# su - oracle
Last login: Tue Oct 24 11:17:01 JST 2023
[oracle@apex3 ~]$ cd /etc/ords/config
[oracle@apex3 config]$ ords --config /etc/ords/config install
ORDS: Release 23.3 Production on Tue Oct 24 02:28:13 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
/etc/ords/config/
Oracle REST Data Services - Interactive Install
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 [1]:
Enter a number to select the database pool to use or specify the database connection
[1] default jdbc:oracle:thin:@//localhost:1521/freepdb1
[2] Specify the database connection
Choose [1]:
Provide database user name with administrator privileges.
Enter the administrator username: sys
Enter the database password for SYS AS SYSDBA: **********
Connecting to database user: SYS AS SYSDBA url: jdbc:oracle:thin:@//localhost:1521/freepdb1
Retrieving information.
Connecting to database user: ORDS_PUBLIC_USER url: jdbc:oracle:thin:@//localhost:1521/freepdb1
2023-10-24T02:28:26.212Z INFO Upgrading Oracle REST Data Services schema 23.2.3.r2421937 to version 23.3.0.r2891830 in FREEPDB1
------------------------------------------------------------
Date : 24 Oct 2023 02:28:26
Release : Oracle REST Data Services 23.3.0.r2891830
Type : ORDS Upgrade from 23.2.3.r2421937 to 23.3.0.r2891830
Database : Oracle Database 23c Free
DB Version : 23.3.0.23.09
------------------------------------------------------------
Container Name: FREEPDB1
------------------------------------------------------------
[*** Info: Upgrade intialize ]
[*** script: ords_alter_session_script.sql]
PL/SQL procedure successfully completed.
[*** script: ords_init_upgrade.sql]
Session altered.
PL/SQL procedure successfully completed.
[中略]
INFO: 11:28:31 Validating objects for Oracle REST Data Services.
VALIDATION: 11:28:31 Starting validation for schema: ORDS_METADATA
VALIDATION: 11:28:31 Validating objects
VALIDATION: 11:28:33 Validating roles granted to ORDS_METADATA and
ORDS_PUBLIC_USER
VALIDATION: 11:28:33 Validating ORDS Public Synonyms
VALIDATION: 11:28:33 Total objects: 340, invalid objects: 0, missing objects: 0
VALIDATION: 11:28:33 97 INDEX
VALIDATION: 11:28:33 3 LOB
VALIDATION: 11:28:33 24 PACKAGE
VALIDATION: 11:28:33 23 PACKAGE BODY
VALIDATION: 11:28:33 1 PROCEDURE
VALIDATION: 11:28:33 60 PUBLIC SYNONYM
VALIDATION: 11:28:33 1 SEQUENCE
VALIDATION: 11:28:33 34 TABLE
VALIDATION: 11:28:33 34 TRIGGER
VALIDATION: 11:28:33 20 TYPE
VALIDATION: 11:28:33 6 TYPE BODY
VALIDATION: 11:28:33 37 VIEW
VALIDATION: 11:28:33 Validation completed.
INFO: 11:28:33 Completed validating objects for Oracle REST Data Services.
PL/SQL procedure successfully completed.
Commit complete.
[*** script: ords_alter_session_script.sql]
PL/SQL procedure successfully completed.
2023-10-24T02:28:33.607Z INFO Completed upgrade for Oracle REST Data Services version 23.3.0.r2891830. Elapsed time: 00:00:07.292
[*** Info: Upgrade complete ]
[*** Info: Completed upgrade for Oracle REST Data Services version 23.3.0.r2891830. Elapsed time: 00:00:07.292
]
[oracle@apex3 config]$
cd /etc/dnf/vars
echo oci.oraclecloud.com > ocidomain
echo .us-ashburn-1 > ociregion
echo us-ashburn-1 > region
[root@apex3 vars]# cd /etc/dnf/vars
[root@apex3 vars]# echo oci.oraclecloud.com > ocidomain
[root@apex3 vars]# echo .us-ashburn-1 > ociregion
[root@apex3 vars]# echo us-ashburn-1 > region
[ol8_oci_included]
name=Oracle Software for OCI users on Oracle Linux $releasever ($basearch)
baseurl=https://yum$ociregion.$ocidomain/repo/OracleLinux/OL8/oci/included/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1[root@apex3 yum.repos.d]# cat oci-included-ol8.repo
[ol8_oci_included]
name=Oracle Software for OCI users on Oracle Linux $releasever ($basearch)
baseurl=https://yum$ociregion.$ocidomain/repo/OracleLinux/OL8/oci/included/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1
[root@apex3 yum.repos.d]#
rpm -qa | grep jdk
dnf -y remove jdk-17-17.0.8-9.x86_64
[root@apex3 ~]# rpm -qa | grep jdk
jdk-17-17.0.8-9.x86_64
[root@apex3 ~]# dnf -y remove jdk-17-17.0.8-9.x86_64
Failed to set locale, defaulting to C.UTF-8
Dependencies resolved.
======================================================================================================
Package Architecture Version Repository Size
======================================================================================================
Removing:
jdk-17 x86_64 2000:17.0.8-9 @@commandline 303 M
Transaction Summary
======================================================================================================
Remove 1 Package
Freed space: 303 M
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Running scriptlet: jdk-17-2000:17.0.8-9.x86_64 1/1
Erasing : jdk-17-2000:17.0.8-9.x86_64 1/1
Running scriptlet: jdk-17-2000:17.0.8-9.x86_64 1/1
Verifying : jdk-17-2000:17.0.8-9.x86_64 1/1
Removed:
jdk-17-2000:17.0.8-9.x86_64
Complete!
[root@apex3 ~]#
GraalVMのEnterprise Edition 22、JDK17のJavaとJavaScriptのモジュールをインストールします。Oracle Cloud以外の環境では、無印のGraalVMをインストールすると思います。
dnf -y install graalvm22-ee-17-jdk graalvm22-ee-17-javascript
[root@apex3 ~]# dnf -y install graalvm22-ee-17-jdk graalvm22-ee-17-javascript
Failed to set locale, defaulting to C.UTF-8
Oracle Software for OCI users on Oracle Linux 8 (x86_64) 73 MB/s | 100 MB 00:01
Oracle Linux 8 BaseOS Latest (x86_64) 71 MB/s | 64 MB 00:00
Oracle Linux 8 Application Stream (x86_64) 71 MB/s | 49 MB 00:00
Latest Unbreakable Enterprise Kernel Release 7 for Oracle Linux 8 (x8 49 MB/s | 23 MB 00:00
Dependencies resolved.
======================================================================================================
Package Architecture Version Repository Size
======================================================================================================
Installing:
graalvm22-ee-17-javascript x86_64 22.3.4-1.el8 ol8_oci_included 57 M
graalvm22-ee-17-jdk x86_64 22.3.4-1.el8 ol8_oci_included 228 M
Transaction Summary
======================================================================================================
Install 2 Packages
Total download size: 286 M
Installed size: 691 M
Downloading Packages:
(1/2): graalvm22-ee-17-javascript-22.3.4-1.el8.x86_64.rpm 48 MB/s | 57 MB 00:01
(2/2): graalvm22-ee-17-jdk-22.3.4-1.el8.x86_64.rpm 34 MB/s | 228 MB 00:06
------------------------------------------------------------------------------------------------------
Total 42 MB/s | 286 MB 00:06
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : graalvm22-ee-17-jdk-22.3.4-1.el8.x86_64 1/2
Running scriptlet: graalvm22-ee-17-jdk-22.3.4-1.el8.x86_64 1/2
Installing : graalvm22-ee-17-javascript-22.3.4-1.el8.x86_64 2/2
Running scriptlet: graalvm22-ee-17-javascript-22.3.4-1.el8.x86_64 2/2
Verifying : graalvm22-ee-17-javascript-22.3.4-1.el8.x86_64 1/2
Verifying : graalvm22-ee-17-jdk-22.3.4-1.el8.x86_64 2/2
Installed:
graalvm22-ee-17-javascript-22.3.4-1.el8.x86_64 graalvm22-ee-17-jdk-22.3.4-1.el8.x86_64
Complete!
[root@apex3 ~]#
インストールされたJavaのバージョンおよび、GraalVMが使われていることを確認します。
[root@apex3 ~]# java -version
java version "17.0.9" 2023-10-17 LTS
Java(TM) SE Runtime Environment GraalVM EE 22.3.4 (build 17.0.9+11-LTS-jvmci-22.3-b28)
Java HotSpot(TM) 64-Bit Server VM GraalVM EE 22.3.4 (build 17.0.9+11-LTS-jvmci-22.3-b28, mixed mode, sharing)
[root@apex3 ~]#
ORDSを起動します。
[root@apex3 ~]# systemctl start ords
[root@apex3 ~]#
ORDSにアクセスし、ORDSが正常に起動していることを確認します。
https://github.com/oracle/db-sample-schemas/releases/latest
Source codeのリンクをコピーします。
curl -OL https://github.com/oracle-samples/db-sample-schemas/archive/refs/tags/v23.2.zip
[oracle@apex3 ~]$ cd $HOME
[oracle@apex3 ~]$ curl -OL https://github.com/oracle-samples/db-sample-schemas/archive/refs/tags/v23.2.zip
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0
100 11.6M 0 11.6M 0 0 4521k 0 --:--:-- 0:00:02 --:--:-- 4962k
[oracle@apex3 ~]$
[oracle@apex3 ~]$ unzip v23.2.zip
Archive: v23.2.zip
a780e5b01ae238266f94bb1e6ab1edce028b41e0
creating: db-sample-schemas-23.2/
inflating: db-sample-schemas-23.2/CONTRIBUTING.md
inflating: db-sample-schemas-23.2/LICENSE.txt
inflating: db-sample-schemas-23.2/README.md
inflating: db-sample-schemas-23.2/README.txt
inflating: db-sample-schemas-23.2/SECURITY.md
creating: db-sample-schemas-23.2/customer_orders/
inflating: db-sample-schemas-23.2/customer_orders/README.md
inflating: db-sample-schemas-23.2/customer_orders/README.txt
inflating: db-sample-schemas-23.2/customer_orders/co_create.sql
[中略]
inflating: db-sample-schemas-23.2/sales_history/sh_install.sql
inflating: db-sample-schemas-23.2/sales_history/sh_populate.sql
inflating: db-sample-schemas-23.2/sales_history/sh_uninstall.sql
inflating: db-sample-schemas-23.2/sales_history/supplementary_demographics.csv
inflating: db-sample-schemas-23.2/sales_history/times.csv
[oracle@apex3 ~]$
HRのサンプル・スキーマが含まれるディレクトリに移動します。
[oracle@apex3 ~]$ cd db-sample-schemas-23.2/human_resources/
hr_install.sqlを実行し、HRのサンプル・スキーマをインストールします。
[oracle@apex3 human_resources]$ . oraenv
ORACLE_SID = [FREE] ? FREE
The Oracle base remains unchanged with value /opt/oracle
[oracle@apex3 human_resources]$ sqlplus sys/******@localhost/freepdb1 as sysdba
SQL*Plus: Release 23.0.0.0.0 - Production on Tue Oct 24 12:15:19 2023
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> @hr_install
Thank you for installing the Oracle Human Resources Sample Schema.
This installation script will automatically exit your database session
at the end of the installation or if any error is encountered.
The entire installation will be logged into the 'hr_install.log' log file.
Enter a password for the user HR: ********
Enter a tablespace for HR [USERS]:
Do you want to overwrite the schema, if it already exists? [YES|no]:
****** Creating REGIONS table ....
Table created.
Index created.
[中略]
Commit complete.
Installation
-------------
Verification:
Table provided actual
----------- ---------- ----------
regions 5 5
countries 25 25
departments 27 27
locations 23 23
employees 107 107
jobs 19 19
job_history 10 10
Thank you!
--------------------------------------------------------
The installation of the sample schema is now finished.
Please check the installation verification output above.
You will now be disconnected from the database.
Thank you for using Oracle Database!
Disconnected from Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
[oracle@apex3 human_resources]$
以上でサンプル・スキーマのHRが作成されます。
[oracle@apex3 ~]$ sqlplus sys/********@localhost/freepdb1 as sysdba
SQL*Plus: Release 23.0.0.0.0 - Production on Tue Oct 24 12:22:16 2023
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> set echo on
SQL> @enable-graphql
SQL> begin
2 -- Set HR as REST enabled schema.
3 ords_admin.enable_schema(
4 p_enabled => true
5 ,p_schema => 'HR'
6 );
7 -- Set all tables and views as REST enabled objects.
8 for r in (
9 select owner, object_type, object_name from dba_objects
10 where owner = 'HR' and object_type in ('TABLE','VIEW')
11 )
12 loop
13 ords_admin.enable_object(
14 p_schema => r.owner
15 ,p_object => r.object_name
16 ,p_object_type => r.object_type
17 );
18 end loop;
19 end;
20 /
PL/SQL procedure successfully completed.
SQL>
10.3.1 Accessing Protected REST-Enabled Objects
oracle.dbtools.autorest.any.schemaoracle.dbtools.role.autorest.<SCHEMANAME>.<OBJECTNAME>
PMのJeff Smithさんのブログ記事の5. The GraphQL Schema definition is computed and then CACHEDにありますが、GraphQLが扱うスキーマ情報は毎回問い合わせるのではなく、キャッシュされた情報にアクセスします。
[root@apex3 ~]# systemctl restart ords
[root@apex3 ~]#
--header 'Content-Type: application/json' \
--data '{
"query": "{employees { employee_id first_name last_name }}"
}'
% curl --location 'http://***.***.***.***/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "{employees { employee_id first_name last_name }}"
}'
{"data":{"employees":[{"employee_id":174,"first_name":"Ellen","last_name":"Abel"},{"employee_id":166,"first_name":"Sundar","last_name":"Ande"},{"employee_id":130,"first_name":"Mozhe","last_name":"Atkinson"},{"employee_id":116,"first_name":"Shelli","last_name":"Baida"},{"employee_id":167,"first_name":"Amit","last_name":"Banda"},{"employee_id":172,"first_name":"Elizabeth","last_name":"Bates"},{"employee_id":192,"first_name":"Sarah","last_name":"Bell"},{"employee_id":151,"first_name":"David","last_name":"Bernste
[中略]
"},{"employee_id":200,"first_name":"Jennifer","last_name":"Whalen"},{"employee_id":105,"first_name":"David","last_name":"Williams"},{"employee_id":101,"first_name":"Neena","last_name":"Yang"},{"employee_id":149,"first_name":"Eleni","last_name":"Zlotkey"}]}}
