Multi-user practice setup / lab for Oracle 19c NON-CDB with N trainees

By /

Requirements

  • 1 Oracle 19c NON-CDB
  • 8 trainees connecting simultaneously
  • Practice:
    • SQL
    • PL/SQL
    • Tables, views, procedures, triggers
  • No one should break the database
  • Easy reset / cleanup
  • Simulates production discipline

🏗️ HIGH-LEVEL DESIGN (BEST PRACTICE)

Oracle Server (RHEL 7)
│
├── SYSTEM / SYSAUX (DBA controlled)
├── USERS tablespace (readonly to trainees)
├── TRAIN_DATA_TS   (common sample data)
├── TRAINEE_TS01    (trainee 1)
├── TRAINEE_TS02    (trainee 2)
├── ...
├── TRAINEE_TS08
│
├── User: trainee01
├── User: trainee02
├── ...
├── User: trainee08
│
└── Common Roles (NO DBA access)

🔹 STEP 1: Core Database Hardening (DBA – Once)

Login as SYS:

sqlplus / as sysdba

1️⃣ Enable ARCHIVELOG (Production Simulation)

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

Verify:

archive log list;

2️⃣ Configure Basic Resource Limits

alter system set resource_limit=TRUE;

🔹 STEP 2: Tablespace Strategy (CRITICAL)

1️⃣ Create Training Tablespaces

-- Common read-only sample data
CREATE TABLESPACE train_data_ts
DATAFILE '/u01/app/oracle/oradata/ORCL/train_data01.dbf'
SIZE 2G AUTOEXTEND ON NEXT 100M;

-- Individual trainee tablespaces
BEGIN
  FOR i IN 1..8 LOOP
    EXECUTE IMMEDIATE '
      CREATE TABLESPACE trainee_ts' || LPAD(i,2,'0') || '
      DATAFILE ''/u01/app/oracle/oradata/ORCL/trainee' || LPAD(i,2,'0') || '.dbf''
      SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE 2G';
  END LOOP;
END;
/

🔹 STEP 3: Create Controlled Roles (NO DBA)

1️⃣ SQL Practice Role

CREATE ROLE trainee_role;

GRANT
  create session,
  create table,
  create view,
  create sequence,
  create procedure,
  create trigger,
  create synonym
TO trainee_role;

❌ DO NOT GRANT

  • DBA
  • UNLIMITED TABLESPACE
  • ALTER SYSTEM

2️⃣ Optional: Read-only Role

CREATE ROLE trainee_readonly;
GRANT select any table TO trainee_readonly;

(Grant later if required)

🔹 STEP 4: Create Trainee Users (ISOLATED)

BEGIN
  FOR i IN 1..8 LOOP
    EXECUTE IMMEDIATE '
      CREATE USER trainee' || LPAD(i,2,'0') || '
      IDENTIFIED BY trainee' || LPAD(i,2,'0') || '
      DEFAULT TABLESPACE trainee_ts' || LPAD(i,2,'0') || '
      TEMPORARY TABLESPACE temp
      QUOTA 500M ON trainee_ts' || LPAD(i,2,'0');
      
    EXECUTE IMMEDIATE '
      GRANT trainee_role TO trainee' || LPAD(i,2,'0');
  END LOOP;
END;
/

Example:

trainee01 / trainee01
trainee02 / trainee02
...

🔹 STEP 5: Sample Data Schema (Trainer-Only)

Create one schema that holds common practice data.

CREATE USER train_data
IDENTIFIED BY train_data
DEFAULT TABLESPACE train_data_ts
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON train_data_ts;

GRANT create session, create table TO train_data;

Load Sample Tables

  • EMP / DEPT
  • BANK_ACCOUNTS
  • TRANSACTIONS
  • LOANS
  • CUSTOMERS

Then:

GRANT SELECT ON train_data.customers TO trainee_role;
GRANT SELECT ON train_data.accounts  TO trainee_role;

👉 Everyone can query, nobody can destroy data.

🔹 STEP 6: Profiles (Simulate Production Controls)

CREATE PROFILE trainee_profile LIMIT
SESSIONS_PER_USER 2
CPU_PER_SESSION UNLIMITED
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 5;

Apply:

ALTER USER trainee01 PROFILE trainee_profile;
-- repeat for all

🔹 STEP 7: Resource Manager (Optional but PRO)

Prevent one trainee from killing performance.

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(
    simple_plan => 'TRAINING_PLAN',
    consumer_group1 => 'TRAINEES',
    group1_cpu => 60
  );
END;
/

Assign users:

BEGIN
  FOR i IN 1..8 LOOP
    DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP(
      user => 'TRAINEE' || LPAD(i,2,'0'),
      consumer_group => 'TRAINEES');
  END LOOP;
END;
/

🔹 STEP 8: Connection Setup (SQL Developer)

Give trainees:

  • Hostname
  • Port: 1521
  • Service: ORCL
  • Username/password

NO SYS access

🔹 STEP 9: Backup & RESET Strategy (VERY IMPORTANT)

Nightly Logical Backup

expdp system/password schemas=trainee01,trainee02,... directory=DATA_PUMP_DIR dumpfile=trainees.dmp

Weekly Reset (Trainer)

DROP USER trainee01 CASCADE;
-- recreate users

🔹 STEP 10: Security Rules (STRICT)

RuleReason
No SYS / SYSTEMPrevent damage
No DBA roleSafety
Tablespace quotasPrevent disk fill
Resource ManagerFair usage
ArchivelogProduction feel

✅ FINAL LAB READINESS CHECKLIST

✔ Database open & stable
✔ Listener auto-start
✔ 8 isolated users
✔ Shared read-only data
✔ No destructive privileges
✔ Reset & backup plan

🔐 Trainee Login Details (Standard Lab Setup)

Based on the setup we discussed:

TraineeUsernamePasswordTablespace
Trainee 1trainee01trainee01trainee_ts01
Trainee 2trainee02trainee02trainee_ts02
Trainee 3trainee03trainee03trainee_ts03
Trainee 4trainee04trainee04trainee_ts04
Trainee 5trainee05trainee05trainee_ts05
Trainee 6trainee06trainee06trainee_ts06
Trainee 7trainee07trainee07trainee_ts07
Trainee 8trainee08trainee08trainee_ts08

👉 Each trainee has his/her own isolated schema.

🧩 HOW A TRAINEE CONNECTS

They can connect in two common ways.

🟢 OPTION 1: Using Oracle SQL Developer (Recommended)

Connection Details to Share

Connection Name: trainee01_lab
Username       : trainee01
Password       : trainee01
Hostname       : <your_server_ip_or_hostname>
Port           : 1521
Service Name   : ORCL

Click Test → Success → Connect

🟢 OPTION 2: Using SQL*Plus (Command Line)

From their local machine (if client installed) or from server:

sqlplus trainee01/trainee01@//<server_ip>:1521/ORCL

Example:

sqlplus trainee01/trainee01@//192.168.1.10:1521/ORCL
Scroll to Top