Skip to content

01.-Installation-and-Configuration.md

Latest commit

 

History

History
688 lines (446 loc) · 20.2 KB

01.-Installation-and-Configuration.md

File metadata and controls

688 lines (446 loc) · 20.2 KB

1. Software Requirements

2. Configuration

2.1. VM Database

2.1.1. Download and Install VM 23ai Free Database VM

Oracle VM VirtualBox - Downloads | Oracle Technology Network | Oracle Canada

2.1.2. Make Sure Bidirectional Clipboard Sharing is Running

You will need to copy/paste a lot to/from clipboard. So, if sharing is not running, from the VirtualBox window menu (while VM is running):

Devices → Insert Guest Additions CD Image…

This mounts /run/media/oracle/VBox_GAs_*/.

After install run

sudo reboot

2.1.2.1. Troubleshooting: VirtualBox Clipboard Stops Working (Linux Guest)

Symptoms
  • Keyboard and mouse work normally inside the VM
  • Copy/paste between host and VM stops working
Cause

The clipboard integration process (VBoxClient --clipboard) crashes.
This is common with older VirtualBox / Guest Additions versions (e.g., 5.1.x).

Quick Fix (No Reboot Required)
killall VBoxClient
VBoxClient --clipboard &
Optional Convenience Alias

Add this to your ~/.bashrc or ~/.bash_profile:

alias fixclip="killall VBoxClient; VBoxClient --clipboard &"

Then simply run:

fixclip

2.1.3. Make APEX and ORDS on VM Accessible from your Host

During installation, attach VM Network Adapter to NAT and verify default port forwarding:

image-20251027-232806.png

URLs to APEX and ORDS should work right away after the installation.

Try http://localhost:8080/ords/_/landing - it should work

2.1.4. Make the Oracle Database on the VM Accessible via SQL*Net

On the VM:

lsnrctl stop
vi $ORACLE_HOME/network/admin/listener.ora

Set HOST=0.0.0.0 i.e. allow to lsiten on all interfaces. By default it’s set to 127.0.0.1 which listens only on VM Internal NIC

DEFAULT_SERVICE_LISTENER = FREE

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = free)
 (SID_NAME = free)
 (ORACLE_HOME = /opt/oracle/product/23ai/dbhomeFree )
 )
 )

Restart Listener and check if it listens on all NICs

lsnrctl start
netstat -an |grep 1521|grep LIST

# expected:
[oracle@vbox ~]$ netstat -an |grep 1521|grep LIST
tcp        0      0 0.0.0.0:1521            0.0.0.0:*               LISTEN

Connect to the database from SQL Developer, SQLcl on your workstation. Passwords for all accounts is oracle

# PDB 

SQL>  conn sys/oracle@//localhost:1521/freepdb1 as sysdba
Connected.

# CDB
SQL>  conn sys/oracle@//localhost:1521/free as sysdba
Connected.
SQL>

2.1.5. Configure Archivelog and Flashback Database

From VM console try to login with / as sysdba. Most likely you will get an ORA-01017 error:

[oracle@vbox mnt]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Mon Oct 27 23:52:16 2025
Version 23.9.0.25.07

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid credential or not authorized; logon denied
Help: https://docs.oracle.com/error-help/db/ora-01017/

Verify that TWO_TASK variable is set and unset it and ORACLE_SID is in uppercase. Now you should be able to connect. Shutdown and Restart the Database to make sure you can proceed to the next step :

 unset TWO_TASK
 export ORACLE_SID=FREE
 sqlplus / as sysdba

 [oracle@vbox mnt]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Tue Oct 28 00:02:49 2025
Version 23.9.0.25.07

Copyright (c) 1982, 2025, Oracle.  All rights reserved.


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.9.0.25.07

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1603293992 bytes
Fixed Size          4928296 bytes
Variable Size         838860800 bytes
Database Buffers      754974720 bytes
Redo Buffers            4530176 bytes
Database mounted.
Database opened.
SQL>

Now we can configure archivelog mode and flashback database. First, we need to create a folder for the FRA:

mkdir -p /opt/oracle/oradata/fast_recovery_area

Login to SQLPlus ( or SQLcl) on the VM to configure the database:

 sqlplus / as sysdba

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM SET db_recovery_file_dest_size = 50G SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest = '/opt/oracle/oradata/fast_recovery_area' SCOPE=BOTH;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

Verify:

SQL> SELECT flashback_on FROM v$database; 

FLASHBACK_ON
------------------
YES

SQL> SELECT * FROM v$recovery_file_dest;      

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------- ---------- ----------------- --------------- ----------
/opt/oracle/oradata/fast_recovery_area
 5.3687E+10  671319552             0           5      0

Test:

conn sys/oracle@//localhost:1521/freepdb1 as sysdba

CREATE RESTORE POINT test;

--- Do something 
create table t1 as select * from dba_objects;
select count(*) from t1;

ALTER PLUGGABLE DATABASE FREEPDB1  CLOSE IMMEDIATE;
FLASHBACK PLUGGABLE DATABASE FREEPDB1 TO RESTORE POINT TEST;
ALTER PLUGGABLE DATABASE FREEPDB1  OPEN RESETLOGS;

---Repeat and get an error:
select count(*) from t1;

DROP RESTORE POINT test;

Expected Output:

SQL> conn sys/oracle@//localhost:1521/freepdb1 as sysdba
Connected.
SQL> CREATE RESTORE POINT test;

RESTORE POINT TEST created.

SQL> create table t1 as select * from dba_objects;

Table T1 created.

SQL> select count(*) from t1;

COUNT(*) 
________
   80483

1 row selected.

SQL> ALTER PLUGGABLE DATABASE FREEPDB1  CLOSE IMMEDIATE;

Pluggable database FREEPDB1 altered.


Flashback succeeded.

SQL> ALTER PLUGGABLE DATABASE FREEPDB1  OPEN RESETLOGS;

Pluggable database FREEPDB1 altered.

SQL> select count(*) from t1;

Error starting at line : 1 in command -
select count(*) from t1
Error at Command Line : 1 Column : 22
Error report -
SQL Error: ORA-00942: table or view "SYS"."T1" does not exist

SQL> DROP RESTORE POINT test;

RESTORE POINT TEST dropped.

2.1.6. Create a Deployer User

As SYS@FREEPDB1 run the following script ( The latest version is in the repo at scripts/sql/create_cla_deployer.sql

create user if not exists cla_DEPLOYER no authentication 
/

grant dba to cla_DEPLOYER
/

grant connect to cla_DEPLOYER
/

grant APEX_DEPLOYERISTRATOR_ROLE  to cla_DEPLOYER with admin option
/

grant execute on sys.dbms_crypto to cla_DEPLOYER with grant option;

grant execute on sys.dbms_session  to cla_DEPLOYER with grant option;

grant execute on sys.javascript to cla_DEPLOYER with grant option;

Alter user cla_DEPLOYER so that it can connect

alter user cla_DEPLOYER identified by <your_password>

2.1.7. Cleanup Flashback Recovery Area

After certain period of work, the FRA may become full, and database operations may halt failing with errors like these:

ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim xxx bytes disk space from xx bytes limit

To prevent it, you may periodically clean out obsolete files in the FRA. Connect to RMAN from VM console:

unset TWO_TASK
export ORACLE_SID=FREE
rman target /

Only once set retention policy ( if it does not work, reduce recovery window):

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Periodically log in to RMAN to delete obsolete backups and archivedlogs:

DELETE NOPROMPT OBSOLETE;

If you ( as the most people) did not do paeriodic maintanance, you can compeletely clean FRA when it gets cloged.

To comepelety cleanup FRA run the following RMAN script that moves the DB to NOARCHIVELOG, so all backups become obsolete and after that delete them and move the DB back to flashback and archivelog:

ALTER DATABASE FLASHBACK OFF;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE OPEN;

DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT ARCHIVELOG ALL;
delete noprompt backup; 

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

Verify cleanup in the OS:

du -sh /opt/oracle/oradata/fast_recovery_area/FREE/*

and in SQL:

SELECT name, space_limit/1024/1024 AS mb_limit,
       space_used/1024/1024 AS mb_used,
       space_reclaimable/1024/1024 AS mb_reclaimable
FROM v$recovery_file_dest;

2.2. Setup SQLcl Connections

The rest of the documenation and many scripts in the repo will rely on these connection names.

2.2.1. Setup Developer Connections to CLA Databases

To set connections, developers will use the following script: scripts/sql/deployment/create_developer_connections.sql . Before running the script, set defines for your proxy user and its passwords:

define PROXY_USER=AKLUEV
define PROXY_PASSWORD_DEV=****
define PROXY_PASSWORD_TEST=****
define PROXY_PASSWORD_PROD=****
@scripts/sql/deployment/create_developer_connections.sql

This script creates the following connections

Name

Env

User

Comment

proj_dev

DEV

&PROXY_USER.[CLA_DEPLOYER]

Export from Dev account

proj_dev

TEST

&PROXY_USER.[CLA_DEPLOYER]

DBA. Export/Deployment from/to Test

prod_apex

prod_pub

prod_ut

PROD

&PROXY_USER.[SCHEMA]

Export from Prod accounts

2.2.2. Setup DBA Connections to CLA Databases

These connections should be set by the personnel responsible for Prod deployment. Similar to the section above, run the script scripts/sql/deployment/create_dba_connections.sql after providing define variables.

This script creates the following connections:
Name Env User Comment
proj_dev DEV &PROXY_USER.[CLA_DEPLOYER] Export from Dev account
proj_dev TEST &PROXY_USER.[CLA_DEPLOYER] DBA. Export/Deployment from/to Test
proj_prod PROD &PROXY_USER.[CLA_DEPLOYER] Deployment to Prod account

2.2.3. Setup Connections to the VM Database

Setup these connections to VM database on each project member’s workstation. To set up these connections, run the following script scripts/sql/deployment/create_vm_connections copy.sql.

This script creates the following connections:
Name Env User Comment
proj_vm VM-PDB [CLA_DEPLOYER] Export/Deployment from Dev account
vm_sys VM-PDB SYS as SYSDBA PDB DBA
vm-cdb VM-CDB SYS as SYSDBA CDB DBA