This is an old revision of the document!
Table of Contents
Oracle
This is a demo install of Oracle Database Server 21c on Oracle Linux 8.
Environment
VirtualBox or any other virtual machine manager.
Operating system
Oracle Linux 8 is preferred since version 9 is not supported by Oracle database server RPMs at this time (Feb 2024).
Oracle Linux 8.5
OracleLinux-R8-U5-x86_64-dvd.iso
Oracle Linux 8.9
OracleLinux-R8-U9-x86_64-dvd.iso
Oracle Linux 9.3
OracleLinux-R9-U3-x86_64-dvd.iso
Install
Virtualbox
2 CPU, 2GB RAM, 40GB HDD FIXED SIZE, 64MB VMSVGA
Take note of MAC address and configure DHCP server with a fixed address.
Install Oracle Linux 8.X
During install do not use scaled mode because it can cause problems with the GUI. CTRL+C will exit scaled mode if enabled (CTRL+ALT will return you to the desktop).
Setup as follows:
- Select language: English (United Kingdom)
- Time & Date: Etc → Coordinated Universal Time
- Software Selection: Server → Headless management, system tools
- Installation Destination: X, automatic partitioning
- Network and Host: ON → hostname.kewl.org
- Root Password
Begin installation… Reboot System
KVM
virt-install
--location OracleLinux-R8-U9-x86_64-dvd.iso,kernel=isolinux/vmlinuz,initrd=isolinux/initrd.img
Login
$ ssh root@hostname.kewl.org
Update
$ yum update Y $ reboot
Virtualbox Additions
Virtualbox needs the VBox Linux Additions so we can mount a share on the VM. This is to access downloaded files on the host machine with a shared folder.
Install kernel and tools
$ yum install kernel-uek kernel-uek-devel make perl Y $ reboot
Install module
$ uname -a Linux hostname.kewl.org 5.4.17-2136.307.3.1.el8uek.x86_64 #2 SMP Mon May 9 17:29:47 PDT 2022 x86_64 x86_64 x86_64 GNU/Linux $ mount /dev/sr0 /mnt $ /mnt/VBoxLinuxAdditions.run
Oracle DB Server 21c
The install guide is mostly for the ZIP version but we shall install the RPM for OL8.
Preinstall
The O/S needs to be setup for Oracle, a preinstall RPM does this automatically for us.
$ yum install ./oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm Y $ reboot
Install
Now we can install the Database Server and afterward configure it with the example database.
$ yum install ./oracle-database-ee-21c-1.0-1.ol8.x86_64.rpm Y Downloading Packages: Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Running scriptlet: oracle-database-ee-21c-1.0-1.x86_64 1/1 Installing : oracle-database-ee-21c-1.0-1.x86_64 1/1 Running scriptlet: oracle-database-ee-21c-1.0-1.x86_64 1/1 [INFO] Executing post installation scripts... [INFO] Oracle home installed successfully and ready to be configured. To configure a sample Oracle Database you can execute the following service configuration script as root: /etc/init.d/oracledb_ORCLCDB-21c configure Verifying : oracle-database-ee-21c-1.0-1.x86_64 1/1 Installed: oracle-database-ee-21c-1.0-1.x86_64 Complete!
Oracle DB Server 23c
dnf install -y oraclelinux-developer-release-el8 dnf install -y oracle-database-preinstall-23c
$ wget "https://download.oracle.com/otn-pub/otn_software/db-free/oracle-database-free-23c-1.0-1.el8.x86_64.rpm" $ dnf -y localinstall ./oracle-database-free-23c-1.0-1.el8.x86_64.rpm Last metadata expiration check: 0:18:03 ago on Thu 01 Feb 2024 21:48:54 UTC. Dependencies resolved. =============================================================================================================== Package Architecture Version Repository Size =============================================================================================================== Installing: oracle-database-free-23c x86_64 1.0-1 @commandline 1.6 G Transaction Summary =============================================================================================================== Install 1 Package Total size: 1.6 G Installed size: 4.0 G Downloading Packages: Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Running scriptlet: oracle-database-free-23c-1.0-1.x86_64 1/1 Installing : oracle-database-free-23c-1.0-1.x86_64 1/1 Running scriptlet: oracle-database-free-23c-1.0-1.x86_64 1/1 [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-23c.conf' and then run '/etc/init.d/oracle-free-23c configure' as root. Verifying : oracle-database-free-23c-1.0-1.x86_64 1/1 Installed: oracle-database-free-23c-1.0-1.x86_64 Complete!
Oracle DB Demo Database
Setup the example database if required for evaluation.
The example can be deleted later if not needed.
21c
$ /etc/init.d/oracledb_ORCLCDB-21c configure Configuring Oracle Database ORCLCDB. Prepare for db operation 8% complete Copying database files 31% complete Creating and starting Oracle instance 32% complete 36% complete 40% complete 43% complete 46% complete Completing Database Creation 51% complete 54% complete Creating Pluggable Databases 58% complete 77% complete Executing Post Configuration Actions 100% complete Database creation complete. For details check the logfiles at: /opt/oracle/cfgtoollogs/dbca/ORCLCDB. Database Information: Global Database Name:ORCLCDB System Identifier(SID):ORCLCDB Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log" for further details. Database configuration completed successfully. The passwords were auto generated, you must change them by connecting to the database using 'sqlplus / as sysdba' as the oracle user.
23c
# /etc/init.d/oracle-free-23c 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: [root@vm3 ~]# /etc/init.d/oracle-free-23c 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. [WARNING] [DBT-11205] Specified shared pool size does not meet the recommended minimum size requirement. This might cause database creation to fail. ACTION: Specify at least (529 MB) for shared pool size. Enter SYS user password: ******* Enter SYSTEM user password: ******** Enter PDBADMIN User Password: ******** Prepare for db operation 7% complete Copying database files 29% complete Creating and starting Oracle instance 30% complete 33% complete 36% complete 39% complete 43% complete Completing Database Creation 47% complete 49% complete 50% complete Creating Pluggable Databases 54% complete 71% complete Executing Post Configuration Actions 93% complete Running Custom Scripts 100% complete Database creation complete. For details check the logfiles at: /opt/oracle/cfgtoollogs/dbca/FREE. Database Information: Global Database Name:FREE System Identifier(SID):FREE Look at the log file "/opt/oracle/cfgtoollogs/dbca/FREE/FREE.log" for further details. Connect to Oracle Database using one of the connect strings: Pluggable database: vm3.kewl.org/FREEPDB1 Multitenant container database: vm3.kewl.org
Oracle DB Systemd
23c
Start
systemctl daemon-reload systemctl enable oracle-free-23c systemctl start oracle-free-23c
Status
/etc/init.d/oracle-free-23c status
Oracle DB Client
Profile
Create bash profile for oracle user in /home/oracle
Ownership is oracle:oinstall
21c
# .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export TMP=/tmp export TMPDIR=/tmp export ORACLE_HOSTNAME=hostname.kewl.org export ORACLE_BASE=/opt/oracle export ORA_INVENTORY=$ORACLE_BASE/oraInventory export DATA_DIR=$ORACLE_BASE/oradata export ORACLE_HOME=$ORACLE_BASE/product/21c/dbhome_1 export PATH=/bin:/usr/bin:/sbin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib export ORACLE_SID=ORCLCDB export ORACLE_UNQNAME=ORCLCDB export PDB_NAME=ORCLPDB1 export DISPLAY=excellent:0.0
23c
For 23c the only valid SID for the free edition is FREE.
# .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export TMP=/tmp export TMPDIR=/tmp export ORACLE_HOSTNAME=vm3.kewl.org export ORACLE_BASE=/opt/oracle export ORA_INVENTORY=$ORACLE_BASE/oraInventory export DATA_DIR=$ORACLE_BASE/oradata export ORACLE_HOME=$ORACLE_BASE/product/23c/dbhomeFree export PATH=/bin:/usr/bin:/sbin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib export ORACLE_SID=FREE export ORACLE_UNQNAME=FREE export PDB_NAME=FREEPDB1 export DISPLAY=excellent:0.0
Password
Set oracle user password and logout.
$ passwd oracle $ exit
Login
$ ssh oracle@hostname.kewl.org
Connect
Connect to database using password `oracle'
$ sqlplus sys as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Sat May 14 03:33:26 2022 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 SQL>
Oracle DB Setup
23c
First remove the database example using script or dbca tool (if installed).
/etc/init.d/oracle-free-23c Usage: /etc/init.d/oracle-free-23c {start|stop|restart|configure|delete|status}
/etc/init.d/oracle-free-23c delete
With dbca create a database named FREE.
In this example a database with 16 PDBs with the PREFIX DB is created using the “Advanced configuration”.
Oracle DB Archive log
To enable backups with RMAN turn on archivelog mode.
Login
[oracle@vm3 ~]$ sqlplus sys as sysdba SQL*Plus: Release 23.0.0.0.0 - Production on Fri Feb 23 14:57:06 2024 Version 23.3.0.23.09 Copyright (c) 1982, 2023, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09
Enable
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1603679416 bytes Fixed Size 5313720 bytes Variable Size 973078528 bytes Database Buffers 620756992 bytes Redo Buffers 4530176 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered.
Status
SQL> select name,log_mode from v$database; NAME LOG_MODE --------- ------------ FREE ARCHIVELOG SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oracle/product/23c/dbhomeFree/dbs/arch Oldest online log sequence 69 Next log sequence to archive 68 Current log sequence 67
Oracle PDB
Access
Access a specific PDB
$ sqlplus sys as sysdba SQL*Plus: Release 23.0.0.0.0 - Production on Fri Feb 9 15:53:18 2024 Version 23.3.0.23.09 Copyright (c) 1982, 2023, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09 SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> ALTER SESSION SET CONTAINER = DB1; Session altered. SQL> show con_name CON_NAME ------------------------------ DB1
Autostart
Change state of PDB to OPEN and keep that state on restart.
SQL> alter pluggable database DB1 open; Pluggable database altered. SQL> alter pluggable database DB1 save state; Pluggable database altered. SQL>
Tablespace
Create a tablespace inside the PDB
SQL> create tablespace data datafile '/opt/oracle/oradata/FREE/DB1/data.dbf' size 1M autoextend on next 1M; Tablespace created.
User
Create a user which can connect to the server and operated on the PDB tablespace.
CREATE USER db1user IDENTIFIED BY db1passwd CONTAINER=CURRENT; GRANT CREATE SESSION TO db1user CONTAINER=CURRENT; GRANT CREATE TABLE TO db1user CONTAINER=CURRENT; ALTER USER db1user QUOTA UNLIMITED ON USERS CONTAINER=CURRENT;
Drop
Drop a PDB if not required and reclaim disk space.
SQL> DROP PLUGGABLE DATABASE DB16 INCLUDING DATAFILES; Pluggable database dropped.
Miscellaneous
Tablespaces
Not specifying a file path will create a tablespace file in this directory for 23c.
/opt/oracle/product/23c/dbhomeFree/dbs
To remove the tablespace file various options are required when dropping a tablespace.
drop tablespace TABLE_SPACE INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
PHP
PHP8.X and tools
apt install build-essential php php-cli php-pear php-dev libaio1
OCI8
Fetch
cd /opt wget "https://download.oracle.com/otn_software/linux/instantclient/2113000/instantclient-basic-linux.x64-21.13.0.0.0dbru.zip" wget "https://download.oracle.com/otn_software/linux/instantclient/2113000/instantclient-sdk-linux.x64-21.13.0.0.0dbru.zip"
Unzip
unzip instantclient-basic-linux.x64-21.13.0.0.0dbru.zip unzip instantclient-sdk-linux.x64-21.13.0.0.0dbru.zip
Build
For path enter “instantclient,/opt/instantclient_21_13”
pecl install oci8 ... You should add "extension=oci8.so" to php.ini
TEST
#! /usr/bin/php -q <?php /* * PHP test for Oracle 23c */ $table = "test"; /* * Connect to PDB */ $con = oci_connect('db1user', 'db1passwd', 'vm3.kewl.org/DB1'); if ($con === false) die("Connect error\n"); /* * Create TABLE */ $statement = oci_parse($con, "CREATE TABLE IF NOT EXISTS {$table} (id INTEGER)"); if ($statement === false) die("Parse error (CREATE TABLE IF NOT EXISTS)\n"); $rc = oci_execute($statement); if ($rc === false) die("Exec error (CREATE TABLE IF NOT EXISTS)\n"); /* * Delete */ $statement = oci_parse($con, "DELETE FROM {$table}"); if ($statement === false) die("Parse error (DELETE FROM)\n"); $rc = oci_execute($statement); if ($rc === false) die("Exec error (DELETE FROM)\n"); /* * Insert */ $statement = oci_parse($con, "INSERT INTO {$table} (id) VALUES (:id)"); if ($statement === false) die("Parse error (INSERT INTO)\n"); $id = rand(1, 100); $rc = oci_bind_by_name($statement, ":id", $id); if ($rc === false) die("Bind error (INSERT INTO)\n"); $rc = oci_execute($statement); if ($rc === false) die("Exec error (INSERT INTO)\n"); /* * Select */ $statement = oci_parse($con, "SELECT id FROM {$table}"); if ($statement === false) die("Parse error (SELECT FROM)\n"); $rc = oci_execute($statement); if ($rc === false) die("Exec error (SELECT FROM)\n"); $arr = array(); $nrows = oci_fetch_all($statement, $arr); print_r($arr); /* * Close */ $rc = oci_close($con); if ($rc === false) die("Close error\n"); /* * vim: shiftwidth=4 tabstop=4 softtabstop=4 expandtab */ ?>
Config
Add extension=oci8.so to “/etc/php/8.2/cli/php.ini” and “/etc/php/8.2/apache2/php.ini”
Add instant client to library path
echo "/opt/instantclient_21_13" >> /etc/ld.so.conf ldconfig