You are here: start » tools » oracle

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 ISO Downloads

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

Install guide

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.

Preinstall RPM 

$ 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.

Install RPM 

$ 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

Install guide 

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_pdbs.jpg

oracle_create.jpg

oracle_done.jpg

Oracle DB Archive log

To enable backups with RMAN turn on archivelog mode.

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
List
RMAN> list archivelog all;
list archivelog all;
List of Archived Log Copies for database with db_unique_name FREE
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
1       1    61      A 23-FEB-24
        Name: /opt/oracle/product/23c/dbhomeFree/dbs/arch1_61_1160003622.dbf

2       1    62      A 23-FEB-24
        Name: /opt/oracle/product/23c/dbhomeFree/dbs/arch1_62_1160003622.dbf

3       1    63      A 23-FEB-24
        Name: /opt/oracle/product/23c/dbhomeFree/dbs/arch1_63_1160003622.dbf

4       1    64      A 25-FEB-24
        Name: /opt/oracle/product/23c/dbhomeFree/dbs/arch1_64_1160003622.dbf

5       1    65      A 25-FEB-24
        Name: /opt/oracle/product/23c/dbhomeFree/dbs/arch1_65_1160003622.dbf

6       1    66      A 25-FEB-24
        Name: /opt/oracle/product/23c/dbhomeFree/dbs/arch1_66_1160003622.dbf

7       1    67      A 25-FEB-24
        Name: /opt/oracle/product/23c/dbhomeFree/dbs/arch1_67_1160003622.dbf

8       1    68      A 25-FEB-24
        Name: /opt/oracle/product/23c/dbhomeFree/dbs/arch1_68_1160003622.dbf



RMAN>
Disable
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 noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /opt/oracle/product/23c/dbhomeFree/dbs/arch
Oldest online log sequence     69
Current log sequence           68
Delete
RMAN> delete archivelog all;
delete archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
List of Archived Log Copies for database with db_unique_name FREE
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
1       1    61      A 23-FEB-24
        Name: /opt/oracle/product/23c/dbhomeFree/dbs/arch1_61_1160003622.dbf

2       1    62      A 23-FEB-24
        Name: /opt/oracle/product/23c/dbhomeFree/dbs/arch1_62_1160003622.dbf

3       1    63      A 23-FEB-24
        Name: /opt/oracle/product/23c/dbhomeFree/dbs/arch1_63_1160003622.dbf

4       1    64      A 25-FEB-24
        Name: /opt/oracle/product/23c/dbhomeFree/dbs/arch1_64_1160003622.dbf

5       1    65      A 25-FEB-24
        Name: /opt/oracle/product/23c/dbhomeFree/dbs/arch1_65_1160003622.dbf

6       1    66      A 25-FEB-24
        Name: /opt/oracle/product/23c/dbhomeFree/dbs/arch1_66_1160003622.dbf

7       1    67      A 25-FEB-24
        Name: /opt/oracle/product/23c/dbhomeFree/dbs/arch1_67_1160003622.dbf

8       1    68      A 25-FEB-24
        Name: /opt/oracle/product/23c/dbhomeFree/dbs/arch1_68_1160003622.dbf


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/opt/oracle/product/23c/dbhomeFree/dbs/arch1_61_1160003622.dbf RECID=1 STAMP=1161701923
deleted archived log
archived log file name=/opt/oracle/product/23c/dbhomeFree/dbs/arch1_62_1160003622.dbf RECID=2 STAMP=1161727348
deleted archived log
archived log file name=/opt/oracle/product/23c/dbhomeFree/dbs/arch1_63_1160003622.dbf RECID=3 STAMP=1161841884
deleted archived log
archived log file name=/opt/oracle/product/23c/dbhomeFree/dbs/arch1_64_1160003622.dbf RECID=4 STAMP=1161846158
deleted archived log
archived log file name=/opt/oracle/product/23c/dbhomeFree/dbs/arch1_65_1160003622.dbf RECID=5 STAMP=1161871223
deleted archived log
archived log file name=/opt/oracle/product/23c/dbhomeFree/dbs/arch1_66_1160003622.dbf RECID=6 STAMP=1161885796
deleted archived log
archived log file name=/opt/oracle/product/23c/dbhomeFree/dbs/arch1_67_1160003622.dbf RECID=7 STAMP=1161900306
deleted archived log
archived log file name=/opt/oracle/product/23c/dbhomeFree/dbs/arch1_68_1160003622.dbf RECID=8 STAMP=1161929059
Deleted 8 objects



RMAN>

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 operate 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

Path

Not specifying a file path will create a tablespace file and archive logs in this directory for 23c. 

/opt/oracle/product/23c/dbhomeFree/dbs
Tablespace

Drop

To remove a tablespace file various options are required when dropping it. 

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
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
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
 */
?>

Resources

Starting Oracle 23c

Oracle Instant Client

PHP OCI8 Installation Guide

PHP OCI8 Reference Guide

This website uses cookies. By using the website, you agree with storing cookies on your computer. Also you acknowledge that you have read and understand our Privacy Policy. If you do not agree leave the website.More information about cookies