Oracle 18c XE auf Oracle Linux 7

Preface

Oracle Linux (OL, formerly known as Oracle Enterprise Linux) is a Linux distribution packaged and freely distributed by Oracle. It is compiled from Red Hat Enterprise Linux (RHEL) source code, replacing Red Hat branding with Oracle's. Users can freely download Oracle Linux through Oracle's E-delivery service (Oracle Software Delivery Cloud) or from a variety of mirror sites, and can deploy and distribute it without cost.

Oracle Database 18c Express (XE) is a free to use for in-house applications. If you have a tight budget but the limits are acceptable you may consider XE on OL for your BPS schema.

The limits of XE are:

Up to 12 GB of user data
Up to 2 GB of database RAM
Up to 2 CPU threads
Up to 3 Pluggable Databases
No support by Oracle, but community support available
No patches from Oracle

In this tutorial linux and oracle are installed in a virtual machine under VMware Workstation 15 Player. Oracle VM VirtualBox should also work, probably with small modifications.

Installation

Download Linux ISO

Download Oracle Linux from https://edelivery.oracle.com

Sign In with your oracle credentials
Search for „Oracle Linux“
Add „DLP: Oracle Linux 7.7.0.0.0 (Oracle Linux)“ to cart
Checkout and select „x86 64 bit“ as platform
Accept license terms
Download „V983339-01.iso Oracle Linux Release 7 Update 7 for x86 (64 bit), 4.4 GB“

Create Virtual Machine

Start VMware Workstation 15 Player

Create a New Virtual Machine
Installer Disc image file (iso): V983339-01.iso
Virtual machine name: vmora18
Location: E:\VM\vmora18
Maximum disk size (GB): 120
Store virtual disk as a single file
Customize Hardware
- Memory: 2 GB
- Processors: 2
- Network Adapter: Bridged
Power on this virtual machine after creation

Install Linux

Show all config steps

Update and DB Preinstall

Open a ssh terminal to „vmora18“ and login as root with the password defined before.

Update the software:

yum -y update

Run Oracle 18c preinstall:

yum -y install oracle-database-preinstall-18c

Install utilities:

yum -y install nano wget

Set a password for user „oracle“ so we can login:

passwd oracle

Optional: Assign a fixed IP in Swisscom Router

Reboot:

reboot

Setup SSH key auth for root

Create ~/.ssh and key

ssh-keygen -t rsa -b 4096
    <enter>
    <enter>
    <enter>

Load my personal key in PuTTYgen, and copy public key to the clipboard

Add my personal key to authorized keys:

nano ~/.ssh/authorized_keys
<paste>

Turn of password auth for ssh:

nano /etc/ssh/sshd_config
...
PasswordAuthentication no
...

Restart ssh:

service sshd restart

Leave SSH terminal yet open in case.

Create and test PuTTY session for root:

Session
- Host Name: vmora18
- Port: 22 / SSH
Connection
- Data
  - Auto-login username: root
- SSH
  - Auth
    - Private key file: (location of my ppk)
Session
- Saved sessions: vmora18 - root
- Save

Test if autologin successful

Motd Logo

Create logo at http://www.network-science.de/ascii/

Text: vmora18
Font: slant
Reflection: no
Adjustment: center
Stretch: no
Width: 80
do it!

Copy logo to clipboard, and paste to motd

nano /etc/motd
<paste>

Install rlwrap for cursor keys in sqlplus

wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
rpm -ivh epel-release-latest-7.noarch.rpm
yum -y install rlwrap

Make SELinux permissive

nano /etc/selinux/config
...
SELINUX=permissive

setenforce Permissive

Disable firewall

systemctl stop firewalld
systemctl disable firewalld

Enable SSH Login for User oracle

Similar as for root

su oracle

ssh-keygen -t rsa -b 4096
    <enter>
    <enter>
    <enter>

nano ~/.ssh/authorized_keys
<paste my personal public key>

Create PuTTY Session

Load session „vmora19 - root“
Change auto-login username: oracle
Save session as „vmora19 - oracle“

Test if working

Append to .bash_profile:

~/.bash_profile

export ORACLE_SID=XE
export ORAENV_ASK=NO
. /opt/oracle/product/18c/dbhomeXE/bin/oraenv
alias sqlplus="rlwrap sqlplus"

exit
reboot

Get Oracle 18c Express Software

Download software:

https://www.oracle.com/database/technologies/xe-downloads.html
Click: Oracle Database 18c Express Edition for Linux x64
Accept license agreement
Click „Download“ button
Login with your oracle credentials
Save file on your desktop PC

Use Filezilla to connect as root with SFTP/SSH, then upload oracle-database-xe-18c-1.0-1.x86_64.rpm to /root

Install DB software and create database

Install the software:

yum -y localinstall oracle-database-xe-18c-1.0-1.x86_64.rpm

Create the database:

/etc/init.d/oracle-xe-18c configure

Enter password for sys, system and pdbadmin 2x

Post-installaton steps

As root

Edit /etc/oratab and set the restart flag 'Y' to have XE started by dbstart:

nano /etc/oratab
...
XE:/opt/oracle/product/18c/dbhomeXE:Y
...

As oracle

Make sure the PDB starts when the instance starts.

sqlplus / as sysdba
--
alter pluggable database xepdb1 open;
alter pluggable database xepdb1 save state;

Automating Database Startup and Shutdown

systemctl daemon-reload
systemctl enable oracle-xe-18c

Enable Enterprise Manager Express

As oracle:

sqlplus / as sysdba
--
exec dbms_xdb_config.sethttpsport(5500);

Test in browser

Administration

Create tablespaces in xepdb1

sqlplus / as sysdba

Create and list tablespaces

ALTER SESSION SET CONTAINER = xepdb1;
CREATE TABLESPACE lu_agrar_dat  DATAFILE '/opt/oracle/oradata/XE/XEPDB1/lu_agrar_dat.dbf' SIZE 10M  AUTOEXTEND ON NEXT 10M;
CREATE TABLESPACE lu_agrar_inx  DATAFILE '/opt/oracle/oradata/XE/XEPDB1/lu_agrar_inx.dbf' SIZE 10M  AUTOEXTEND ON NEXT 10M;
SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------------------
SYSTEM                         ONLINE    PERMANENT
SYSAUX                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
LU_AGRAR_DAT                   ONLINE    PERMANENT
LU_AGRAR_INX                   ONLINE    PERMANENT
7 rows selected.

Check new tablespaces are available in xepdb1 only

ALTER SESSION SET CONTAINER = CDB$ROOT;
SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------------------
SYSTEM                         ONLINE    PERMANENT
SYSAUX                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT

Drop tablespaces in xepdb1

sqlplus / as sysdba

List and drop tablespaces

ALTER SESSION SET CONTAINER = xepdb1;
SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;
DROP TABLESPACE lu_agrar INCLUDING CONTENTS AND DATAFILES;

TNSNAMES entry for clients

VMORA18 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmora18)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xepdb1)
    )
  )

Monitor Used Space

Monitoring the used space is important on XE, since you don't want to run into the maximum 12 GB limit!

What counts to the XE space limit of 12 GB is somewhat unclear for me. Oracle says USER DATA. So the default sys-, undo- and temporary tablespaces are probably unconsidered. Since I am not completely sure about this, I count all data visible in cdb_data_files.

Connect

sqlplus / as sysdba

Check used space per tablespace:

select tablespace_name, round(bytes/1024/1024) MB, round(bytes/1024/1024/1024,3) GB from cdb_data_files;

Sample output:

TABLESPACE_NAME                        MB         GB
------------------------------ ---------- ----------
USERS                                   5       .005
UNDOTBS1                               65       .063
SYSTEM                                840        .82
SYSAUX                                580       .566
SYSTEM                                290       .283
SYSAUX                                430        .42
UNDOTBS1                              220       .215
USERS                                   5       .005
LU_AGRAR_DAT                           50       .049
LU_AGRAR_INX                           50       .049

If you just want the summary of user data:

select round(sum(bytes)/1024/1024) MB, round(sum(bytes)/1024/1024/1024,3) GB from cdb_data_files
where tablespace_name not like 'SYS%' and tablespace_name not like 'UNDO%';