Configuration

Configuration of the OS and Database environment

Install and configure `oraenv`

Download the files from github repository.

sudo -s     # root
cd /usr/local/src

wget ......

Configure the `oraenv` files

su - oracle    # oracle
cp /usr/local/src/oraenv.sh /home/oracle/
cp /usr/local/src/oraenv_sample.conf /home/oracle/oraenv.conf
cp /usr/local/src/oratools.sh /opt/oracle/admin/scripts/

chmod 750 /opt/oracle/admin/scripts/oratools.sh
chmod 640 /home/oracle/oraenv.sh /home/oracle/oraenv.conf

oraenv configuration file

/home/oracle/oraenv.conf
cat > /home/oracle/oraenv.conf <<EOF
[XE]
DB_UNIQUE_NAME=xe
ORACLE_HOSTNAME=RockyLinux8
ORACLE_BASE=/opt/oracle
ORACLE_DATADIR=/opt/oracle/oradata/XE
ORACLE_ARCHIVE=/opt/oracle/archive/XE
ORACLE_BACKUP=/opt/oracle/backup/XE
NLS_DATE_FORMAT=DD.MM.YYYY:HH24:MI:SS
NLS_LANG=AMERICAN_AMERICA.UTF8
ADR_PURGE_AGE=4320
RMAN_RETENTION=2
RMAN_ARCHIVELOG_BACKUPS=
RMAN_PARALLELISM=2
RMAN_COMPRESSED=COMPRESSED
TARGET_CONNECT_STR=/
TARGET_CATALOG_STR=
STANDBY_CONNECT_STR=
EOF

Configure `/home/oracle/.icats_profile`

icats configuration file

/home/oracle/.icats_profile
cat > /home/oracle/.icats_profile <<EOF
export ORDS_HOME="/opt/oracle/ords"
export ORDS_LOGS="\${ORDS_HOME}/logs"
export ORDS_CONFIG="\${ORDS_HOME}/conf"
export JAVA_HOME="\${ORDS_HOME}/jdk-21.0.5+11-jre"
export ICATS_HOME="/opt/icats"
export APEX_HOME="/opt/oracle/apex"
export APEX_IMAGES="\${APEX_HOME}/images"

export PATH="\${PATH}:\${ORDS_HOME}/ords-24.4.0.345.1601/bin:\${JAVA_HOME}/bin"

alias cd.icats.home='cd \${ICATS_HOME}'
alias cd.apex.home='cd \${APEX_HOME}'
alias cd.apex.images='cd \${APEX_IMAGES}'
alias cd.ords.home='cd \${ORDS_HOME}'
alias cd.ords.logs='cd \${ORDS_LOGS}'
alias cd.ords.config='cd \${ORDS_CONFIG}'
alias tail.ords.log='tail -200f \${ORDS_LOGS}'
EOF

Configure `/home/oracle/.bash_profile`

/home/oracle/.bash_profile
echo ". /home/oracle/.icats_profile" >>/home/oracle/.bash_profile
echo ". /home/oracle/oraenv.sh XE; ShowEnv" >>/home/oracle/.bash_profile

# login again as user oracle
exit
su - oracle

Create missing Oracle folder

Executing alias createfolder.

$ createfolder

- --------------------------------------------------
- Create folder for XE
- --------------------------------------------------
- /opt/oracle/oradata/XE created
- /opt/oracle/admin/XE/adump created
- /opt/oracle/admin/pfile
- /opt/oracle/product/21c/dbhomeXE created
- /opt/oracle/archive/XE created
- /opt/oracle/backup/XE created
- /opt/oracle/admin/XE/clone created
- /opt/oracle/admin/XE/scripts created
- /opt/oracle/admin/XE/log created
- /opt/oracle/admin/scripts created

Database configuration

Set run_user and run_group in `externaljob.ora`

This is needed to run Oracle Schedulerjobs with external commands.

ls -l /opt/oracle/product/21c/dbhomeXE/rdbms/admin/externaljob.ora
-rw-r----- 1 root oinstall 1555 23. Jan 18:30 /opt/oracle/product/21c/dbhomeXE/rdbms/admin/externaljob.ora

vim /opt/oracle/product/21c/dbhomeXE/rdbms/admin/externaljob.ora

run_user = oracle
run_group = oinstall

SQL*Net configuration

cd /opt/oracle/product/21c/dbhomeXE/network/admin
ln -s /opt/oracle/homes/OraDBHome21cXE/network/admin/listener.ora
ln -s /opt/oracle/homes/OraDBHome21cXE/network/admin/sqlnet.ora
ln -s /opt/oracle/homes/OraDBHome21cXE/network/admin/tnsnames.ora

tnsnames.ora
listener.ora
sqlnet.ora

tnsnames.ora configuration file

/opt/oracle/homes/OraDBHome21cXE/network/admin/tnsnames.ora
cat > /opt/oracle/homes/OraDBHome21cXE/network/admin/tnsnames.ora <<EOF
XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <HOSTNAME>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

# iCATs PROD environment
PDBICATS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <HOSTNAME>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDBICATS)
    )
  )

# iCATs TEST environment
PDBICATSTST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <HOSTNAME>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDBICATSTST)
    )
  )

# iCATs DEV environment
PDBICATSDEV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <HOSTNAME>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDBICATSDEV)
    )
  )
EOF

listener.ora configuration file

/opt/oracle/homes/OraDBHome21cXE/network/admin/listener.ora
cat > /opt/oracle/homes/OraDBHome21cXE/network/admin/listener.ora <<EOF
DEFAULT_SERVICE_LISTENER = XE

XE =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <HOSTNAME>)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
EOF

sqlnet.ora configuration file

/opt/oracle/homes/OraDBHome21cXE/network/admin/sqlnet.ora
cat > /opt/oracle/homes/OraDBHome21cXE/network/admin/sqlnet.ora <<EOF
NAMES.DIRECTORY_PATH = (TNSNAMES)
EOF

Database parameter configuration `/opt/oracle/dbs/spfileXE.ora`

$ sq
SQL>
-- Remove pga_aggregate_target and sga_target from spfile
alter system reset pga_aggregate_target scope=spfile;
alter system reset sga_target scope=spfile;

-- Add memory_target to spfile
alter system set memory_target=2048M  scope=spfile;

alter system set audit_trail = 'none' scope=spfile;
alter system set local_listener = 'XE';
alter system set open_cursors = 600;
alter system set processes = 600 scope=spfile;

alter profile default limit password_life_time unlimited;

-- Configure spfile in case of enabled Online Backup
alter system set log_archive_dest_1='LOCATION=/opt/oracle/archive/XE' scope=spfile;
alter system set log_archive_format='XE_%t_%s_%r.arc' scope=spfile ;

-- restart database instance
shutdown immediate
startup mount;

-- alter database archivelog; -- In case of you want to enable Online Backup
alter database open;

Delete default pluggable database XEPDB1

$ sq
SQL>
alter pluggable database XEPDB1 close IMMEDIATE;
drop pluggable database XEPDB1 INCLUDING datafiles;
exit

rmdir /opt/oracle/oradata/XE/XEPDB1

Create a new iCATs database

Info

Example names for PDB's

PDBICATS (PDB=Pluggable database, iCATs, PROD environment
PDBICATSDEV (PDB=Pluggable database, iCATs, DEV=Development environment
PDBICATSTST (PDB=Pluggable database, iCATs, TST=Test environment

$ sq    -- sqlplus / as sysdba
SQL>
create pluggable database "PDBICATSTST"
admin user "PDBADMIN"
identified by "xxxxxx"
file_name_convert = ('/opt/oracle/oradata/XE/pdbseed/','/opt/oracle/oradata/XE/PDBICATSTST/');

alter pluggable database PDBICATSTST open;
alter pluggable database PDBICATSTST save state;

alter session set container=PDBICATSTST;

alter profile default limit password_life_time unlimited;

exit

Check the status of the databases

$ lcdb

Available Container(s)
======================

CON_ID CDB_NAME 		      SAVED_STATE     OPEN_MODE  RESTRICTED OPEN_TIME
------ ------------------------------ --------------- ---------- ---------- -------------------
     3 PDBICATSTST		      OPEN	      READ WRITE NO	    2025.01.13 12:44:46
     1 CDB$ROOT 				      READ WRITE NO	    2025.01.13 10:51:13
     2 PDB$SEED 				      READ ONLY  NO	    2025.01.13 10:51:13

Creating a Systemd Service for the Oracle XE database

Info

If you prefer to stop and start the database manually after the OS has been started you can use the following commands.

-- logon as user oracle
su - oracle

-- Start the database
oratools.sh -s XE -c dbstartup -o

-- Stop the database
oratools.sh -s XE -c dbshutdown -o

Otherwise you can implementing the Systemd service as followed.

/etc/systemd/system/oracle.service
cat > /etc/systemd/system/oracle.service <<EOF
[Unit]
Description=Oracle database service
After=network.target

[Service]
User=oracle
Group=oinstall

ExecStart=/opt/oracle/admin/scripts/oratools.sh -s XE -c dbstartup -o
ExecStop=/opt/oracle/admin/scripts/oratools.sh -s XE -c dbshutdown -o
RemainAfterExit=yes

TimeoutStopSec=300
SendSIGKILL=no

[Install]
WantedBy=multi-user.target
EOF

Set permission and enable the Systemd service

chmod 644 /etc/systemd/system/oracle.service

systemctl daemon-reload

systemctl enable oracle
systemctl status oracle
systemctl start oracle

Install and configure oraenv​

Download the files from github repository.​

Configure the oraenv files​

Configure /home/oracle/.icats_profile​

Configure /home/oracle/.bash_profile​

Create missing Oracle folder​

Database configuration​

Set run_user and run_group in externaljob.ora​

SQL*Net configuration​

Database parameter configuration /opt/oracle/dbs/spfileXE.ora​

Delete default pluggable database XEPDB1​

Create a new iCATs database​

Check the status of the databases​

Creating a Systemd Service for the Oracle XE database​

Set permission and enable the Systemd service​