====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). [[https://yum.oracle.com/oracle-linux-isos.html|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=== [[https://oracle-base.com/articles/21c/oracle-db-21c-installation-on-oracle-linux-8|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. [[https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm|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. [[https://www.oracle.com/database/technologies/oracle21c-linux-downloads.html|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=== [[https://oracle-base.com/articles/23c/oracle-db-23c-free-rpm-installation-on-oracle-linux-8|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". {{:tools:oracle_pdbs.jpg?300}} {{:tools:oracle_create.jpg?300}} {{:tools:oracle_done.jpg?300}} ===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 ==TEST== #! /usr/bin/php -q ==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 ===Resources=== [[https://docs.oracle.com/en/database/oracle/oracle-database/23/xeinl/starting-and-stopping-oracle-database.html|Starting Oracle 23c]] [[https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html|Oracle Instant Client]] [[https://www.php.net/manual/en/oci8.installation.php|PHP OCI8 Installation Guide]] [[https://www.php.net/manual/en/ref.oci8.php|PHP OCI8 Reference Guide]]