How to Install Oracle Database 23ai, ORDS and Oracle APEX 24.1 on Oracle Cloud OEL Instance.
In this Part 1 Article, we will see how to?
Part 2 & part 6 of this article can be read here
1. Create Cloud Compute Instance.
Login to?cloud.oracle.com . In the left navigation, select Compute and then Instances.
Please note: If you would like to provision an always-free Oracle Autonomous Database 23ai instance, skip to Step 20 of this article.
This?will list your instances. Click?on?the Create Instance button and choose your compartment rather than creating an Instance in your root compartment.
Provide Instance name, select region, and availability domain.
You can go with Oracle Linux 8 and AMD VM Standard E4 Flex ( 1 or 2 cores)
Alternatively, you can also choose Oracle Linux 9 with Intel hardware (Installation is not covered in this article)
Select or create a new VCN.?
Note1:?You can select VCN (Virtual cloud network) if you already have one in the same compartment and region; if not, click?on?the Create new virtual cloud network.
Note2: Instead of Oracle Enterprise Linux 8 you can also choose Oracle Enterprise Linux 9 the downloads and installation will slightly differ than the one shown here in this article
Generate or Upload a public SSH key (.pub) file.
If you wish to generate your own SSH key pair, you can do so as
ssh-keygen -b 2048 -t rsa
If you choose Generate a key pair for me option,?then?save private and public keys; we need that later for SSH purposes. Click on Create button
We should be good to go from here - in a few minutes, our compute Instance will be running; copy the public IP address; we will need this later.
Click on the Virtual Cloud Network > Public subnet of this compute instance.
Important: Please consult with your network administrator or security expert before opening any network ports externally.
2. SSH into compute instance
cd <your keys directory>
ssh -i <your key file>.key opc@<your public ip address>
[opc@inddb23ai ~]$ pwd
/home/opc
[opc@inddb23ai ~]$ sudo su -
Last login: Mon May 6 13:40:04 GMT 2024 on pts/0
3. Install Oracle Database 23ai
Download?Oracle Database 23ai , and copy the download link address from Oracle Database Free Get Started page.?
Yum Update Oracle Enterprise Linux 8.
# yum update
Download and Install Oracle Database 23ai,
Note: You also have other ways of running Oracle Database 23ai, such as using Docker or Oracle VM VirtualBox or downloading and installing on your?own?laptop or desktop machine rather than using a cloud computing Instance.?
In our case we will go with this option.
# mkdir downloads
# cd downloads/
# wget https://download.oracle.com/otn-pub/otn_software/db-free/oracle-database-free-23ai-1.0-1.el8.x86_64.rpm
$ ls
oracle-database-free-23ai-1.0-1.el8.x86_64.rpm
dnf install -y oracle-database-free*
[INFO] Executing post installation scripts...
[INFO] Oracle home installed successfully and ready to be configured.
To configure Oracle Database Free, optionally modify the parameters in '/etc/sysconfig/oracle-free-23ai.conf' and then run '/etc/init.d/oracle-free-23ai configure' as root.
If you had made a choice to go with Oracle Linux 9 then
Download oracle-database-free-23ai-1.0-1.el9.x86_64.rpm
Run --
dnf install -y oracle-database-free*
Run /etc/init.d/oracle-free-23ai configure
4. Configure Database and set SYS Password
-- Configure Oracle Database 23ai --
# /etc/init.d/oracle-free-23ai configure
Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be
used for SYS, SYSTEM and PDBADMIN accounts:
Confirm the password:
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database FREE.
Enter SYS user password:
***********
Enter SYSTEM user password:
*************
Enter PDBADMIN User Password:
if you see this screen, we should be good with our installation.
lets try sqlplus
# sqlplus
-bash: sqlplus: command not found
time to set few paths -
# cd $HOME
# ls -a
.bash_profile
# vi .bash_profile
Add the following lines
# User specific environment and startup programs
export ORACLE_SID=FREE
export ORACLE_HOME=/opt/oracle/product/23ai/dbhomeFree
export ORAENV_ASK=NO
export PATH=$ORACLE_HOME/bin:$PATH
Our .bash_profile file would look like this.
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_SID=FREE
export ORACLE_HOME=/opt/oracle/product/23ai/dbhomeFree
export ORAENV_ASK=NO
export PATH=$ORACLE_HOME/bin:$PATH
PATH=$PATH:$HOME/bin
export PATH
Save and source the profile file.
# source .bash_profile
# sqlplus
SQL*Plus: Release 23.0.0.0.0 - Production on Mon May 6 17:32:01 2024
Version 23.4.0.24.05
Copyright (c) 1982, 2024, Oracle. All rights reserved.
So far so good, lets try to connect to database as SYS user. replace <Your-SYS-Password> as you had setup during installation.
sqlplus sys/<Your-SYS-Password>@localhost:1521/FREEPDB1 as sysdba
or
sqlplus sys@localhost:1521/FREEPDB1 as sysdba
Run a simple select statement
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
SQL> select sysdate;
SYSDATE
---------
06-MAY-24
SQL> exit;
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
5. Create Database User.
Login as an SYS user and create a new Database user; we will use this user later in our Oracle APEX application
SQL> create user induser identified by <YourPassword> quota unlimited on users;
User created.
SQL> grant connect, resource to induser;
Grant succeeded.
if you come across this error message
ERROR at line 1:
ORA-65096: common user or role name must start with prefix C##
Help: https://docs.oracle.com/error-help/db/ora-65096/
then create user with c##
create user c##induser identified by Welcome123# quota unlimited on users;
grant connect, resource to c##induser;
6. Install Oracle APEX
Copy the file download link from Oracle APEX download page.
Updated on July 2024 ( we can see Oracle APEX Release 24.1 is available)
mkdir downloads
cd downloads
wget https://download.oracle.com/otn_software/apex/apex_23.2_en.zip
unzip apex_23.2_en.zip -d /opt/oracle/product
--- If you plan to download 24.1 then ---
wget https://download.oracle.com/otn_software/apex/apex_24.1.zip
unzip apex_24.1.zip -d /opt/oracle/product
-- this will create apex folder at /opt/oracle/product
cd /opt/oracle/product/apex
-- connect to database as sys user
sqlplus sys@localhost:1521/FREEPDB1 as sysdba
-- run the APEX Installer script.
-- General format
-- @apxrtins.sql tablespace_apex tablespace_files tablespace_temp images
SYS> @apexins.sql SYSAUX SYSAUX TEMP /i/
-- change password
SYS> @apxchpwd.sql
SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY <Your-Apex-user-pawd>;
Note: > if you forget to unlock the APEX_PUBLIC_USER you might get following error, Account is locked 570
Set Ora Env
# . /opt/oracle/product/23ai/dbhomeFree/bin/oraenv
The Oracle base has been set to /opt/oracle
Configuring RESTful Services and set passwords for
SQL> @apex_rest_config.sql
Enter the administrator's username [ADMIN] <just hit enter button>
User "ADMIN" does not yet exist and will be created.
Enter a password for the APEX_LISTENER user *******
Enter a password for the APEX_REST_PUBLIC_USER user *****
7. Download and Install JDK
Get JDK 17 URL from Java Download page.
cd $HOME/downloads
--Download JDK 17 --
wget https://download.oracle.com/java/17/latest/jdk-17_linux-x64_bin.rpm
ls
jdk-17_linux-x64_bin.rpm
yum -y install jdk-17_linux-x64_bin.rpm
After Installation we should see
Installed: jdk-17-2000:22.0.1-8.x86_64
Complete!
# java -version
java version "17.0.1"
-- or you might see the one below
java version "17.0.11" 2024-04-16 LTS
Java(TM) SE Runtime Environment (build 17.0.11+7-LTS-207)
Java HotSpot(TM) 64-Bit Server VM (build 17.0.11+7-LTS-207, mixed mode, sharing)
8. Install ORDS
# yum install ords
Package ords-24.1.0-16.el8.noarch is installed.
Dependencies resolved.
-- or you might see the one below --
Installed:
ords-24.2.0-6.el8.noarch
Most Important step here is to configure ORDS
[opc@devdb23ai ~]$ sudo su -
Last login: Mon May 6 17:15:19 GMT 2024 on pts/0
# ords --config /etc/ords/config install
ORDS: Release 24.1 Production on Mon May 06 18:43:46 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config
...
Enter a number to select the TNS net service name to use or specify the database connection
[1] FREE SERVICE_NAME=FREE
[S] Specify the database connection
Choose [1]: 1
...
Provide database user name with administrator privileges.
Enter the administrator username: SYS
Enter the database password for SYS AS SYSDBA: *****
....
PDB FREEPDB1 - install ORDS 24.1.0.r1080942
PDB FREEPDB1 - configure PL/SQL gateway user APEX_PUBLIC_USER in ORDS version 24.1.0.r1080942
...
Install ORDS in the database
[1] Yes
[2] No
Choose [1]: 1
...
Enter a number to update the value or select option A to Accept and Continue
[1] Connection Type: TNS
[2] TNS Connection: TNS_NAME=FREE TNS_FOLDER=/opt/oracle/product/23ai/dbhomeFree/network/admin
Administrator User: SYS AS SYSDBA
[3] Database password for ORDS runtime user (ORDS_PUBLIC_USER): <generate>
[4] ORDS runtime user and schema tablespaces: Default: SYSAUX Temporary TEMP
[5] Additional Feature: Database Actions
[6] Configure and start ORDS in Standalone Mode: Yes
[7] Protocol: HTTP
[8] HTTP Port: 8080
[9] APEX static resources location:
[A] Accept and Continue - Create configuration and Install ORDS in the database
[Q] Quit - Do not proceed. No changes
Choose [A]: A
...
standalone.static.context.path=/ords
database.api.enabled=true
db.username=ORDS_PUBLIC_USER
standalone.http.port=8080
restEnabledSql.active=true
resource.templates.enabled=false
plsql.gateway.mode=proxied
feature.sdw=true
config.required=true
db.connectionType=tns
....
Oracle REST Data Services version : 24.1.0.r1080942
Oracle REST Data Services server info: jetty/10.0.20
Oracle REST Data Services java info: Java HotSpot(TM) 64-Bit Server VM 22.0.1+8-16
Note > : if the above installation does not return back to command prompt, then press control + c button on keyboard to proceed.
Start ORDS
# /etc/init.d/ords start
INFO: Starting Oracle REST Data Services...
INFO: Oracle REST Data Services started with PID 64712
INFO: Obtaining the port binding information...
INFO: Oracle REST Data Services is bounded to ports 8080
9. Set up network and open Linux firewall ports.
Please check step 1 while setting up compute instance to open any required network ports on cloud.oracle.com VCN.
In the Linux console we will alter the firewall settings as shown below.
firewall-cmd --permanent --zone=public --add-port=1521/tcp
firewall-cmd --permanent --zone=public --add-port=8080/tcp
firewall-cmd --reload
9A Optional - creating docuser (for RAG Application used in part 2)
You can ignore this section 9A unless you want to create a RAG Application as discussed in detail in part 2 of this article.
-- login to SQL as SYS user ---
-- Important: please be careful on how much .dbf file size that you allocate,
-- as per your requirements
SQL> CREATE TABLESPACE tbs1
DATAFILE 'tbs5.dbf' SIZE 10G AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE M
SQL> CREATE TABLESPACE tbs1
DATAFILE 'tbs5.dbf' SIZE 10G AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SQL> create user docuser identified by ******** DEFAULT TABLESPACE tbs1 quota unlimited on tbs1;
SQL> grant DB_DEVELOPER_ROLE to docuser;
-- directory where our PDFs have been stored
SQL> create or replace directory VEC_DUMP as '/tmp/my_local_dir/pdfs';
SQL> grant read, write on directory VEC_DUMP to docuser;
SQL> commit;
SQL> exit;
10. Access Oracle APEX environment
Reality check access https://<your-public-ip>:8080/ords
change PDB to freepdb1 and click on Go button.
You may get this following error message
There is a problem with your environment because the Oracle APEX files have not been loaded. Please verify that you have copied the images directory to your application server as instructed in the Installation Guide. In addition, please verify that your image prefix path is correct. Your current path is /i/ (it should contain both starting and ending forward slashes, such as the default /i/). Use the SQL script reset_image_prefix.sql if you need to change it.
How do we fix this issue? the APEX page seem to be broken.
11. Troubleshooting APEX Installation
In your VSCode create a Database connection as shown below.
I have installed SQL Developer Extension for VSCode and created a database connection as SYS user
Connection Parameters:
Hostname <Public IP Address>
User SYS as SYSDBA, Port 1521 and Service Name as FREEPDB1
领英推荐
Open the worksheet. and run this
begin
apex_instance_admin.set_parameter(p_parameter => 'IMAGE_PREFIX',
p_value => 'https://static.oracle.com/cdn/apex/23.2.0/' );
commit;
end;
if you have a different version of APEX installed the CDN URL might change
Check updated CDN urls on this page
For APEX 23.2.0, use https://static.oracle.com/cdn/apex/23.2.0/.
For APEX 23.2.1, use https://static.oracle.com/cdn/apex/23.2.1/.
For APEX 23.2.2, use https://static.oracle.com/cdn/apex/23.2.2/.
For APEX 23.2.3, use https://static.oracle.com/cdn/apex/23.2.3/.
For APEX 23.2.4, use https://static.oracle.com/cdn/apex/23.2.4/.
For APEX 23.2.5, use https://static.oracle.com/cdn/apex/23.2.5/.
For APEX 24.1.0, use https://static.oracle.com/cdn/apex/24.1.0/
July 2024 update for Oracle APEX 24.1
begin
apex_instance_admin.set_parameter(p_parameter => 'IMAGE_PREFIX',
p_value => 'https://static.oracle.com/cdn/apex/24.1.0/' );
commit;
end;
Now let us access the ORACLE APEX page again at https://<public-ip>:8080/ords
and then select Oracle APEX
This works like a charm
12. Create Oracle APEX workspace
Login as ADMIN user into INTERNAL workspace
Click on Create Workspace
Protect Workspace Name, Workspace Description
Re-use existing schema = Yes (since we already have created user)
Schema Name <Select from the one shown in the list>
Provide Admin username and password
Click Next and Create Workspace
Login to our new workspace with the user previously created in Step 5.
Sign in to APEX workspace.
Let us now create a Sample AI Vector Table.
13. Create a Vector Database table.
From Oracle APEX workspace, in the top navigation access SQL Workshop > SQL Commands
CREATE TABLE my_vectors (id NUMBER, embedding VECTOR);
CREATE TABLE my_vectors2 (id NUMBER, embedding VECTOR(768, INT8)) ;
Our tables have been successfully created.
14. Register ORDS Schema
Copy and paste the REST URL into a web browser or any REST client.
15. Troubleshooting ORDS HTTP 503 error
You might come across this page
The database user for the connection pool named |default|lo|, is not able to proxy to the schema named APEX_PUBLIC_USER. This could be a configured restriction on the maximum number of database sessions or an authorization failure.
Solution:
-- Restart ORDS
# /etc/init.d/ords restart
INFO: Stopping Oracle REST Data Services...
INFO: Oracle REST Data Services stopped
INFO: Starting Oracle REST Data Services...
INFO: Oracle REST Data Services started with PID 93702
INFO: Obtaining the port binding information...
INFO: Oracle REST Data Services is bounded to ports 8080
Now we should see
16. Grant access to Oracle SQL Web Developer
Connect to Database on our VSCode client as SYS user and run this. to enable SQL Web Developer access, in this below case replace <db-username>with the Database user that we created in Step 5)
BEGIN
ords_admin.enable_schema(
p_enabled => TRUE,
p_schema => '<db-username>',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => '<db-username>',
p_auto_rest_auth => NULL
);
commit;
END;
Login to ORDS Home at https://<your-public-ip>:8080/ords, change PDB to FREEPDB1 and click on Go
We should see following Database Actions available
Select SQL and run the following or a similar SQL query to check this feature
select * from emp where rownum < 5;
17. Insert into Vector table using python script.
Lets use SQL Web Developer to create following table.
CREATE TABLE vector_table (
v32 vector(3, float32),
v64 vector(3, float64),
v8 vector(3, int8)
)
Python code to insert Vector data in a table.
Update our oracledb python driver on our client machine, in my case i am using Macbook pro laptop.
python3 -m pip install oracledb --upgrade --user
Please refer python-oracledb installation guide if required.
We already have vector_table created in previous step 17, so lets use Python code insert-vector.py to insert few records.
import array
import sys
import oracledb
# determine whether to use python-oracledb thin mode or thick mode
# we will user the database user that we had created earlier, that is induser
un = 'induser'
cs = '<Your-public-IP>/FREEPDB1'
pw = '<Your Password>'
connection = oracledb.connect(
user=un,
password=pw,
dsn=cs,
)
# this script works with thin mode, or with thick mode using Oracle Client 23.4
# or later
if not connection.thin:
sys.exit(
"This example requires python-oracledb thin mode. Try vector_string.py"
)
with connection.cursor() as cursor:
# Single-row insert
vector1_data_32 = array.array("f", [1.625, 1.5, 1.0])
vector1_data_64 = array.array("d", [11.25, 11.75, 11.5])
vector1_data_8 = array.array("b", [1, 2, 3])
cursor.execute(
"insert into vector_table (v32, v64, v8) values (:1, :2, :3)",
[vector1_data_32, vector1_data_64, vector1_data_8],
)
# Multi-row insert
vector2_data_32 = array.array("f", [2.625, 2.5, 2.0])
vector2_data_64 = array.array("d", [22.25, 22.75, 22.5])
vector2_data_8 = array.array("b", [4, 5, 6])
vector3_data_32 = array.array("f", [3.625, 3.5, 3.0])
vector3_data_64 = array.array("d", [33.25, 33.75, 33.5])
vector3_data_8 = array.array("b", [7, 8, 9])
rows = [
(vector2_data_32, vector2_data_64, vector2_data_8),
(vector3_data_32, vector3_data_64, vector3_data_8),
]
cursor.executemany(
"insert into vector_table (v32, v64, v8) values (:1, :2, :3)",
rows,
)
# Query
cursor.execute("select * from vector_table")
# Each vector is represented as an array.array type
for row in cursor:
print('Row -------------')
print(row)
#Commit changes to Database
connection.commit()
Run the Python code from your client machine (Laptop), please note that we are not SSHing here.
% python3 insert-vector.py
Row -------------
(array('f', [1.625, 1.5, 1.0]), array('d', [11.25, 11.75, 11.5]), array('b', [1, 2, 3]))
Row -------------
(array('f', [2.625, 2.5, 2.0]), array('d', [22.25, 22.75, 22.5]), array('b', [4, 5, 6]))
Row -------------
(array('f', [3.625, 3.5, 3.0]), array('d', [33.25, 33.75, 33.5]), array('b', [7, 8, 9]))
Check the Data Inserted in Oracle APEX
18. Uploading/Downloading files to/from cloud machine with SCP
Sometimes, you may need to transfer files from your local desktop machine/laptop directly to a cloud compute instance; this can?be done?as follows. This is very handy utility to know. we will use this knowledge in part 2 of this article.
First, disconnect from the VPN if you have already connected.
-- SCP Uploading file from laptop to cloud compute instance --
madhusudhanrao@madhusudhanrao-mac keys %
scp -rp -i <ssh-key-file>.key /<local_folder>/<file_name> opc@<public-ip>:/tmp
hello.txt
100% 39 0.2KB/s 00:00
for example:
scp -rp -i ssh-key-2023-09-29.key /Users/Madhu/Uploads/hello.txt [email protected]:/tmp
-- SCP Downloading file from cloud compute instance to laptop/local machine --
for example:
scp -rp -i <ssh-key-file>.key [email protected]:/tmp/onnx /Users/Madhu/Downloads/
19. Handling Out of space errors - purging files in Oracle APEX
Updated on July 2024
What if we ran out of 12 GB space and we get Oracle APEX error message that we have reached ran out of 12 GB space?
Solution is Simple
First locate tables that you do not want and delete them
SQL Workshop > Object Browser, Select and Delete table.
Similarly repeat for other entities like Views, Procedure, etc. whatever is not needed.
Second, Delete unwanted applications this will save lots of space.
Under App Builder, Delete unwanted applications, please be extra careful here
Finally, Purge and recover space.
So, we are back on track related to space, also you can login to OCI compute instance and delete /tmp files or any unwanted files especially the .zip or .gz files
20 How to check Oracle APEX version?
Login to Oracle APEX workspace and under SQL Workshop > SQL Commands run the following SQL.
select version_no from apex_release;
21 Create Always Free Autonomous Database 23ai on Oracle Cloud
Updated on July 2024
Conclusion: This?ends part 1 of the article. We will continue this article by demonstrating some of the cool new features of Oracle Database 23ai.
Thanks for reading, liking and sharing
Regards
Click to Action:
My Related Articles:
References:
Cloud Advisory Innovation Principal Manager at Accenture Enkitec Group
4 个月Thanks for very nice article. I've tested on Apex 23.2. Some minor adjustment were required. Thanks a lot for this article.
Good step by step - looking forward to the demo
Principal Product Manager - DB Platform Technology Solutions at Oracle
6 个月Madhu, congratulations on your recent publication. You are a great example to the rest of the company!