howto:ora18oln7

Oracle 18c XE on Oracle Linux 7

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.

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

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

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

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

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>
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
nano /etc/selinux/config
...
SELINUX=permissive
setenforce Permissive
systemctl stop firewalld
systemctl disable firewalld

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

Download software:

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

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;
systemctl daemon-reload
systemctl enable oracle-xe-18c

As oracle:

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

Test in browser

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
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)
    )
  )
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%';

Sample output:

        MB         GB
---------- ----------
      2535      2.476

You may vant to disable password expire at least for system and the BPS superuser.

As system, check used profiles:

select profile from DBA_USERS where username = 'SYSTEM';
select profile from DBA_USERS where username = 'LU_AGRAR';

Check settings of the profiles, for example the DEFAULT profile:

select resource_name,limit from dba_profiles where profile='DEFAULT';

Set to unlimited:

alter profile DEFAULT limit password_life_time UNLIMITED;
  • howto/ora18oln7.txt
  • Last modified: 2020/04/09 11:37
  • by ibk