Oracle XE 21 C on EC2 - qyjohn/Hands-on-Linux GitHub Wiki

Setup Oracle XE 21C on RHEL 8 / CentOS 8

These notes work for both RHEL 8 and CentOS 8. It should be noted that CentOS 8 has reached End Of Life (EOL).

Launch a RHEL 8 or CentOS 8 instance in us-east-1 with 50 GB root EBS volume. (For CentOS 8,Marketplace ami-0d6e9a57f6259ba3a works.)
SSH into the EC2 instance. For RHEL, the username is ec2-user. For CentOS, the username is centos.
If you are using CentOS 8, you will need to fix the repos first. You don't need to do this on RHEL.

cd /etc/yum.repos.d/
sudo sed -i 's/mirrorlist/#mirrorlist/g' /etc/yum.repos.d/CentOS-*
sudo sed -i 's|#baseurl=http://mirror.centos.org|baseurl=http://vault.centos.org|g' /etc/yum.repos.d/CentOS-*

Preparation

cd ~
sudo yum update
sudo yum install -y bc binutils elfutils-libelf elfutils-libelf-devel fontconfig-devel \
gcc gcc-c++ glibc glibc-devel ksh ksh libaio libaio-devel libgcc libnsl libnsl.i686 \
libnsl2 libnsl2.i686 librdmacm-devel libstdc++ libstdc++-devel libX11 libXau libxcb \
libXi libXrender libXrender-devel libXtst make net-tools nfs-utils smartmontools \
sysstat targetcli unixODBC wget telnet

Download

wget https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm
wget https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm

Install

sudo yum localinstall oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm
sudo yum localinstall oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm

Init

sudo /etc/init.d/oracle-xe-21c configure

At the end of this command, you will see the following output:

Connect to Oracle Database using one of the connect strings:
     Pluggable database: ip-xxx-xxx-xxx-xxx.ec2.internal/XEPDB1
     Multitenant container database: ip-xxx-xxx-xxx-xxx.ec2.internal
Use https://localhost:5500/em to access Oracle Enterprise Manager for Oracle Database XE

You can verify that the service is listening on port 1521:

telnet localhost 1521
Trying ::1...
Connected to localhost.
Escape character is '^]'.
^]quit

telnet> quit
Connection closed.

Start / Stop Service

sudo /etc/init.d/oracle-xe-21c start
sudo /etc/init.d/oracle-xe-21c stop

Connect to the default database XEPDB1.

export ORACLE_HOME=/opt/oracle/product/21c/dbhomeXE
export PATH=$PATH:/opt/oracle/product/21c/dbhomeXE/bin
sqlplus system@localhost:1521/XEPDB1

In the sqlplus command, you can replace localhost with the private IP address or the hostname of the EC2 instance.

Create a simple table.

SQL> CREATE TABLE test (id INT, name VARCHAR(50));

Table created.

SQL> INSERT INTO test (id, name) VALUES (1, 'Test');

1 row created.

SQL> SELECT * FROM test;

	ID NAME
---------- --------------------------------------------------
	 1 Test

SQL> quit
Disconnected from Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

Setup Oracle Client (sqlplus) on Ubuntu 20.04 / Amazon Linux 2

On a different EC2 running Ubuntu 20.04, install sqlplus.

sudo apt install alien
wget https://download.oracle.com/otn_software/linux/instantclient/215000/oracle-instantclient-basic-21.5.0.0.0-1.el8.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/215000/oracle-instantclient-sqlplus-21.5.0.0.0-1.el8.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/215000/oracle-instantclient-devel-21.5.0.0.0-1.el8.x86_64.rpm
sudo alien -i oracle-instantclient-basic-21.5.0.0.0-1.el8.x86_64.rpm 
sudo alien -i oracle-instantclient-sqlplus-21.5.0.0.0-1.el8.x86_64.rpm 
sudo alien -i oracle-instantclient-devel-21.5.0.0.0-1.el8.x86_64.rpm

From Ubuntu 20.04, connect to Oracle XE running on CentOS 8.

sqlplus [email protected]:1521/XEPDB1

Run a simple query.

SQL> SELECT * FROM test;

	ID NAME
---------- --------------------------------------------------
	 1 Test

SQL> quit

Adding SSL Support via NLB

Assuming that you have a domain name (abc.com) hosted by Route 53. In Amazon Certificate Manager, request a public certificate for the domain name you would like to use on the network load balancer. In this test, Let's assume that this domain name is xe.abc.com.

In the EC2 Console, create a target group XE-1521, with target type "Instance", protocol "TCP", and port 1521. Add the EC2 instance running Oracle XE to the target group.

Create a network load balancer, with the following two listeners:

TCP listener listens on port 1521, forwards traffic to target group XE-1521.
TLS listener listens on port 5521, forwards traffic to target group XE-1521. The listener uses the above-mentioned ACM certificate.

In Route53, create a CNAME record for xe.abc.com, with the value being the domain name of the network load balancer.

At this point, we have an Oracle XE installation that serves both SSL and non-SSL traffic. The domain name of the Oracle XE installation is xe.abc.com. For SSL traffic, the request goes to port 5521. For non-SSL traffic, the request goes to port 1521.

If you decided to use a self-signed certificate, you can create a self-signed certificate for your domain name (xe.abc.com) with OpenSSL:

# Generate self-sign certificate
openssl req -x509 -nodes -days 365 -newkey rsa:2048 -keyout privateKey.key -out certificate.crt

# Verify the key and certificate generated
openssl rsa -in privateKey.key -check
openssl x509 -in certificate.crt -text -noout

# Convert the key and cert into .pem encoded file
openssl rsa -in privateKey.key -text > private.pem
openssl x509 -inform PEM -in certificate.crt > public.pem

In Amazon Certificate Manager, import the self-signed certificate. Then you can use the self-signed certificate on the NLB.

Validation with SQL Workbench

In SQL Workbench, validate the above-mentioned Oracle XE setup. The JDBC driver was ojdbc8.jar, which was downloaded from the following URL:

https://www.oracle.com/au/database/technologies/appdev/jdbc-downloads.html

For non-SSL connection, we use the following JDBC URL:

jdbc:oracle:thin:@//xe.abc.com:1521/XEPDB1

For SSL connection, we use the following JDBC URL:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=xe.abc.com)(PORT=5521))(CONNECT_DATA=(SERVICE_NAME=XEPDB1)))

When you use a self-signed certificate on the NLB, the connection will fail unless the operating system (or JVM) trusts your self-signed certificate. If the operating system (or JVM) does not trust your self-signed certificate, you will get the following connection error in SQL Workbench:

IO Error: IO Error PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target, connect lapse 273 ms., Authentication lapse 0 ms. [SQL State=08006, DB Errorcode=17002]

If you require hostname validation for the SSL connection, you will need to find out the SSL_SERVER_CERT_DN. This can be done with the following command:

openssl s_client -connect xe.abc.com:5521 < /dev/null | head -10

Useful SQL Statements

Find out the version of the Oracle installation:

SELECT * FROM V$VERSION;

Find out the SID of the Oracle installation:

SELECT SYS_CONTEXT('USERENV', 'INSTANCE_NAME') FROM DUAL;

Find out the SERVICE_NAME we are connected to:

SELECT SYS_CONTEXT('USERENV', 'SERVICE_NAME') FROM DUAL;

Find out the DB_NAME we are connected to:

SELECT SYS_CONTEXT('USERENV', 'DB_NAME') FROM DUAL;