Define SLOs, Workload Classes, Data Model, Multitenant/HA Choices, Capacity, and Architecture Record

Learning outcomes

ServiceHub's architecture review says only “99.9% uptime, fast queries, backups, and HA.” That statement is impossible to design or test: it does not say which requests matter, how much data loss is tolerable, whether planned maintenance counts, what latency percentile is contractual, or what budget/licensing boundaries exist. A production database architecture begins with measurable Service Level Objectives (SLOs), not product names.

1. Define the operational words before using them

Availability is the fraction of agreed service time in which a defined service can perform its required function. Durability asks whether acknowledged committed data survives defined failures. RPO is the maximum tolerable committed-data loss measured in time or another explicit business unit. RTO is the maximum tolerated time to restore the required service after an incident. A latency objective should identify the transaction, percentile and load context, for example “p95 create-work-order database time under the normal peak workload,” not “queries under 100 ms.”

2. Convert business statements into measurable SLO rows

BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh29_arch_decisions PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh29_slo PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/CREATE TABLE sh29_slo (  slo_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  workload_class VARCHAR2(30) NOT NULL,  metric_name VARCHAR2(40) NOT NULL,  objective_text VARCHAR2(500) NOT NULL,  measurement_source VARCHAR2(200) NOT NULL,  acceptance_window VARCHAR2(100) NOT NULL);INSERT INTO sh29_slo(  workload_class,metric_name,objective_text,  measurement_source,acceptance_window) VALUES(  'OLTP','DB_LATENCY',  'Measure p95/p99 create-work-order database time at the agreed peak arrival rate; final threshold comes from load test, not this tutorial.',  'application timer + SQL execution evidence',  'rolling production window excluding approved maintenance only if contract says so');INSERT INTO sh29_slo VALUES(  DEFAULT,'RECOVERY','RPO',  'No more committed data loss than the approved business RPO after the defined site/storage failure.',  'backup/redo/standby timestamps + drill log',  'each disaster-recovery drill');INSERT INTO sh29_slo VALUES(  DEFAULT,'RECOVERY','RTO',  'Restore write service within the approved business RTO after declared incident start.',  'incident/drill timestamps',  'each restore/failover drill');INSERT INTO sh29_slo VALUES(  DEFAULT,'SECURITY','PRIVILEGE',  'Runtime identity has no direct application-table DML outside the approved PL/SQL API.',  'DBA_TAB_PRIVS + failed misuse test',  'each release and privilege review');COMMIT;

The numeric threshold is intentionally not invented here. The architecture record tells later lessons exactly what evidence must be captured before a threshold is approved.

3. Classify workloads before choosing one topology

Capacity planning uses the concurrency, arrival rate, working set, data growth and failure behavior of each class. A reporting query that is acceptable at 02:00 may violate the OLTP SLO at noon.

4. Capture the real baseline topology

SELECT  SYS_CONTEXT('USERENV','DB_NAME') AS db_name,  SYS_CONTEXT('USERENV','CON_NAME') AS con_name,  SYS_CONTEXT('USERENV','SERVICE_NAME') AS service_name,  SYS_CONTEXT('USERENV','INSTANCE_NAME') AS instance_nameFROM dual;SELECT  database_role,  open_mode,  protection_mode,  protection_level,  platform_name,  log_mode,  flashback_onFROM v$database;SELECT  instance_name,  host_name,  version_full,  statusFROM v$instance;SELECT name,valueFROM v$parameterWHERE name IN (  'compatible','cluster_database',  'sga_target','pga_aggregate_target',  'db_recovery_file_dest',  'db_recovery_file_dest_size')ORDER BY name;

Expected Free evidence is one primary, non-RAC instance. This does not prove that the eventual production topology should be single-instance; it proves the lab topology and prevents silently claiming RAC/Data Guard behavior that is not present.

5. Multitenant choice is an isolation and lifecycle decision

A Container Database (CDB) hosts one or more Pluggable Databases (PDBs). PDBs share the CDB instance/SGA/process infrastructure while providing database-level application lifecycle and namespace isolation. Current Free licensing permits up to 16 PDBs, but Free's global 2-core/2-GB/12-GB limits still constrain the logical environment.

Use one application PDB when ServiceHub has one lifecycle/ownership boundary. Separate PDBs when operational isolation, patch/clone/unplug ownership, administrative separation, or application-container architecture justifies it—not merely because multiple teams exist.

6. Service names separate application routing from instance names

Applications connect to a service. The service is the unit that later HA designs can relocate or associate with workload policies. Do not hard-code a node/instance SID into every application merely because the Free lab has one instance.

SELECT  name,  network_name,  con_id,  failover_type,  reset_stateFROM v$active_servicesORDER BY name;

For the capstone, FREEPDB1 remains the mandatory local service. Production would normally create a named ServiceHub service and manage it with DBMS_SERVICE for standalone/PDB deployments or SRVCTL under Clusterware.

7. HA mechanisms solve different failures

Do not buy RAC because the requirement says “backups,” or call partitioning disaster recovery. Chapter 29 must be defensible at the failure-domain level.

8. Deliberately wrong architecture: “RAC + Data Guard + sharding everywhere”

This stack can be valid at very large scale, but using every HA feature without a requirement multiplies licenses, databases, network paths, patch sequences, recovery states and failure modes. A design that cannot be rehearsed inside the operational budget is not safer merely because it has more boxes.

CREATE TABLE sh29_arch_decisions (  decision_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  topic VARCHAR2(50) NOT NULL,  decision VARCHAR2(100) NOT NULL,  status_code VARCHAR2(12) NOT NULL    CHECK (status_code IN ('CHOSEN','REJECTED','DEFERRED')),  rationale VARCHAR2(1000) NOT NULL,  license_assumption VARCHAR2(500) NOT NULL,  evidence_needed VARCHAR2(500) NOT NULL);INSERT INTO sh29_arch_decisions(  topic,decision,status_code,rationale,  license_assumption,evidence_needed) VALUES(  'HA','RMAN + tested restore','CHOSEN',  'Mandatory baseline for media/user/corruption recovery.',  'Core RMAN features used by the lab are available in Free.',  'RESTORE VALIDATE plus destructive restore drill in an isolated logical environment');INSERT INTO sh29_arch_decisions VALUES(  DEFAULT,'HA','DATA GUARD','DEFERRED',  'Site-level RPO/RTO may justify standby; Free cannot reproduce redo apply.',  'Redo Apply is not available in Free; production requires an entitled offering.',  'measured redo rate, network RTT/bandwidth, apply lag and role-transition drill');INSERT INTO sh29_arch_decisions VALUES(  DEFAULT,'HA','RAC','REJECTED',  'Current business case asks for site DR and restore assurance, not shared-database multi-instance scale.',  'RAC is not available in Free and is an extra-cost option on EE/EE-ES.',  'would require measured node-failure/scale requirement before reconsideration');INSERT INTO sh29_arch_decisions VALUES(  DEFAULT,'DISTRIBUTION','SHARDING','REJECTED',  'Current tenant volume fits one database and cross-tenant operations remain frequent.',  'Current Free matrix allows limited sharding but production topology still adds major operational cost.',  'tenant-locality percentage, per-tenant hotspot and regional sovereignty requirement');COMMIT;

9. Capacity planning starts from measured rates and headroom

Do not size production from Free's resource cap. Capture:

• Peak and sustained transactions/queries per second by workload class.
• Active-session concurrency and connection-pool queue wait.
• Redo bytes/s, undo generation, logical/physical reads and temporary-space use.
• Working-set memory, SGA/PGA pressure and vector-pool needs where applicable.
• Daily/monthly data/index/LOB growth plus retention/purge windows.
• Backup duration, archive generation, recovery bandwidth and DR link capacity.

SELECT name,valueFROM v$sysstatWHERE name IN (  'user commits',  'user rollbacks',  'redo size',  'session logical reads',  'physical reads',  'physical writes')ORDER BY name;SELECT name,value,unitFROM v$sysmetricWHERE metric_name IN (  'Host CPU Utilization (%)',  'Physical Read Total Bytes Per Sec',  'Physical Write Total Bytes Per Sec',  'Redo Generated Per Sec');

Dynamic metrics are snapshots/short intervals; they are not a capacity forecast by themselves. Retain time-series application/database/OS evidence and test failure headroom.

10. Security and budget are architecture constraints

Record data classification, encryption-at-rest/transport requirements, tenant/admin segregation, audit-retention expectations and secrets-management ownership. Then record edition/options/packs that the design actually uses. Current 26ai Free includes many options for learning, but commercial EE/EE-ES can require separate licenses for the same feature. A lab that works on Free is not a license quote for production.

11. Architecture acceptance query

SELECT topic,decision,status_code,evidence_neededFROM sh29_arch_decisionsWHERE status_code='DEFERRED'ORDER BY topic,decision;SELECT workload_class,metric_name,objective_textFROM sh29_sloORDER BY workload_class,metric_name;

A production review should fail if an SLO has no measurement method or a chosen architecture has no rollback/drill/entitlement owner.

12. Production judgment

The capstone architecture starts with one CDB/PDB/service and core RMAN because that matches the mandatory Free environment; it adds RAC/Data Guard/sharding only when an explicit failure, locality or scaling objective demonstrates the need. Capacity numbers come from measured workload, not from the tutorial.

RU reference is 23.26.3; this lesson does not change COMPATIBLE, instance parameters or topology. Architecture-record tables can remain through Lesson 5 so later performance and drill evidence can close deferred decisions. Lesson 2 now implements the schema/API/security boundary that the architecture record assumes.

Authoritative references

• Oracle AI Database Licensing Information — feature/option/pack availability by offering
• Oracle AI Database Free Licensing Restrictions — 2 CPU/2 GB/12 GB/one-install limits
• Oracle Multitenant Administrator's Guide — CDB/PDB lifecycle and isolation
• Oracle Net Services Administrator's Guide — services/connectivity architecture
• Oracle AI Database Reference — dynamic performance/service/resource evidence