Oracle AI Database 26ai DBA — Detailed Course Notes

Comprehensive notes covering architecture, installation, Multitenant administration, security, performance tuning, backup and recovery, patching, and automation for Oracle AI Database 26ai (Release 23.26.1).

📑 Table of Contents

Week 1 — Architecture, Installation & Upgrade Foundations

Overview of Oracle AI Database 26ai Architecture

Oracle AI Database 26ai is an enterprise-grade relational database management system designed to handle both traditional OLTP workloads and modern AI-driven analytics. Its architecture consists of several key components:

Instance and Memory Structures

An Oracle instance is the combination of background processes and memory structures that interact with the database. The key memory structures are:

Background Processes

Oracle 26ai runs numerous background processes that manage database operations:

💡 Key Insight: Oracle AI Database 26ai's architecture is designed for scalability and performance, with the SGA providing shared memory for efficient data access and background processes handling all database management tasks automatically.

Understanding Multitenant Architecture

Oracle's Multitenant architecture introduced in 12c has been enhanced in 26ai. It consists of:

Benefits of Multitenant Architecture

Installing Oracle AI Database 26ai

Oracle AI Database 26ai can be installed using multiple methods depending on the platform and deployment requirements.

Installing on Linux and Windows

Linux Installation Steps (Simplified):

# Linux installation example
# Set up environment variables
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/26.0.0/dbhome_1
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$PATH

# Run the installer
cd $ORACLE_HOME
./runInstaller -silent -responseFile /path/to/install.rsp -ignorePrereq

Windows Installation:

Using DBCA and OUI

Oracle Universal Installer (OUI)

OUI is the primary tool for installing Oracle software. It provides a graphical interface for installation and configuration. Key options include:

Database Configuration Assistant (DBCA)

DBCA is used after software installation to create, configure, and manage databases. DBCA supports:

# Create a database using DBCA in silent mode
dbca -silent -createDatabase \
    -templateName General_Purpose.dbc \
    -gdbName ORCL \
    -sid ORCL \
    -responseFile NO_VALUE \
    -characterSet AL32UTF8 \
    -sysPassword password123 \
    -systemPassword password123 \
    -createAsContainerDatabase true \
    -numberOfPDBs 1 \
    -pdbName PDB1 \
    -pdbAdminPassword password123

Creating a Database Manually

Manual database creation using SQL commands provides maximum control over the database configuration:

-- Step 1: Set up environment variables and create directories
-- Step 2: Create a parameter file (init.ora)
-- Step 3: Start the instance in NOMOUNT mode
STARTUP NOMOUNT;

-- Step 4: Run the CREATE DATABASE statement
CREATE DATABASE orcl
    USER SYS IDENTIFIED BY sys_password
    USER SYSTEM IDENTIFIED BY system_password
    LOGFILE GROUP 1 ('/u01/oradata/orcl/redo01a.log') SIZE 200M,
            GROUP 2 ('/u01/oradata/orcl/redo02a.log') SIZE 200M,
            GROUP 3 ('/u01/oradata/orcl/redo03a.log') SIZE 200M
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    CHARACTER SET AL32UTF8
    NATIONAL CHARACTER SET AL16UTF16
    DATAFILE '/u01/oradata/orcl/system01.dbf' SIZE 700M REUSE
    SYSAUX DATAFILE '/u01/oradata/orcl/sysaux01.dbf' SIZE 550M
    DEFAULT TABLESPACE users
        DATAFILE '/u01/oradata/orcl/users01.dbf' SIZE 500M REUSE
    DEFAULT TEMPORARY TABLESPACE temp
        TEMPFILE '/u01/oradata/orcl/temp01.dbf' SIZE 20M REUSE
    UNDO TABLESPACE undotbs1
        DATAFILE '/u01/oradata/orcl/undotbs01.dbf' SIZE 200M REUSE;

-- Step 5: Run catalog.sql and catproc.sql
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql

Upgrading from Oracle 19c/23ai to 26ai

Upgrading to Oracle AI Database 26ai requires careful planning and execution. The recommended approach is:

Pre-Upgrade Steps

Upgrade Methods

# Using DBUA for upgrade
dbua -silent -oracleHome $NEW_ORACLE_HOME -sid ORCL

# Manual upgrade using catctl.pl
cd $NEW_ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 -l /upgrade/logs catupgrd.sql

Post-Upgrade Tasks

Navigating the New Command-Line Installer New

Oracle AI Database 26ai introduces a new command-line installer for simplified deployments:

# New command-line installer
./install.sh -silent -responseFile /path/to/install.rsp -prereqCheck

# Install with automatic prerequisites
./install.sh -silent -oracleBase /u01/app/oracle -oracleHome /u01/app/oracle/product/26.0.0/dbhome_1

Week 2 — Multitenant Administration & PDB Management Multitenant

CDB and PDB Architecture in Depth

The Multitenant architecture provides several container types:

PDB Lifecycle Management

PDBs go through several states during their lifecycle:

📌 Pro Tip: Always use the ADMIN user when performing PDB operations to ensure proper permissions and avoid privilege issues.

Creating, Cloning, Plugging, and Unplugging PDBs

Creating PDBs

-- Connect to the root container
CONNECT sys@localhost:1521/orcl AS SYSDBA

-- Create a new PDB from the seed
CREATE PLUGGABLE DATABASE pdb1
    ADMIN USER pdb_admin IDENTIFIED BY password123
    FILE_NAME_CONVERT = ('/u01/oradata/orcl/pdbseed/', '/u01/oradata/orcl/pdb1/');

-- Open the PDB
ALTER PLUGGABLE DATABASE pdb1 OPEN;

-- Switch to the PDB
ALTER SESSION SET CONTAINER = pdb1;

-- Create users and objects inside the PDB
CREATE USER app_user IDENTIFIED BY app_password;
GRANT CONNECT, RESOURCE TO app_user;

Cloning PDBs

-- Clone an existing PDB
CREATE PLUGGABLE DATABASE pdb1_clone
    FROM pdb1
    FILE_NAME_CONVERT = ('/u01/oradata/orcl/pdb1/', '/u01/oradata/orcl/pdb1_clone/');

-- Clone a remote PDB (from another CDB)
CREATE PLUGGABLE DATABASE remote_pdb_clone
    FROM remote_pdb@dblink
    FILE_NAME_CONVERT = ('/remote/pdb/', '/local/pdb/');

Plugging an Unplugged PDB

-- First, unplug the PDB to generate the XML manifest
ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO '/path/to/pdb1.xml';

-- Drop the PDB from the source CDB (optional)
DROP PLUGGABLE DATABASE pdb1 INCLUDING DATAFILES;

-- In the target CDB, plug the PDB using the XML manifest
CREATE PLUGGABLE DATABASE pdb1_plugged
    USING '/path/to/pdb1.xml'
    NOCOPY
    SOURCE_FILE_NAME_CONVERT = ('/old/path/', '/new/path/');

Unplugging PDBs

-- Close the PDB
ALTER PLUGGABLE DATABASE pdb1 CLOSE;

-- Unplug the PDB
ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO '/path/to/pdb1.xml';

-- Drop the PDB (keep data files for later plugging)
DROP PLUGGABLE DATABASE pdb1 KEEP DATAFILES;

Managing PDB Resource Allocation and Priority Settings New

Oracle AI Database 26ai introduces improved PDB resource management with priority-based allocation:

-- Create a resource plan
BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.CREATE_PLAN(
        plan => 'PDB_PLAN',
        comment => 'PDB resource allocation plan'
    );
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan => 'PDB_PLAN',
        plan_directive => 'PDB1_DIRECTIVE',
        pdb_name => 'pdb1',
        cpu_shares => 4,          -- Higher shares = more CPU
        max_iops => 1000,
        max_mbps => 50,
        utilization_limit => 80   -- Max CPU utilization %
    );
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan => 'PDB_PLAN',
        plan_directive => 'PDB2_DIRECTIVE',
        pdb_name => 'pdb2',
        cpu_shares => 1,
        max_iops => 500,
        max_mbps => 25,
        utilization_limit => 60
    );
    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
END;
/

-- Set the resource plan for the CDB
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'PDB_PLAN';

-- View PDB resource usage
SELECT pdb_name, cpu_usage_pct, io_usage_pct
FROM V$PDB_RESOURCE_STATS;

PDB-Level Administration

Administration within a PDB is similar to a non-CDB database but restricted to the PDB context:

Managing Users and Privileges

-- Connect to a specific PDB
ALTER SESSION SET CONTAINER = pdb1;

-- Create local users
CREATE USER local_user IDENTIFIED BY local_password;
GRANT CONNECT, RESOURCE, CREATE SESSION TO local_user;

-- Grant schema-level privileges
GRANT SELECT ANY TABLE ON SCHEMA hr TO local_user;

Managing Tablespaces

-- Create tablespace within a PDB
CREATE TABLESPACE app_data
    DATAFILE '/u01/oradata/orcl/pdb1/app_data01.dbf' SIZE 500M
    AUTOEXTEND ON NEXT 100M MAXSIZE 2G;

-- Add data file to tablespace
ALTER TABLESPACE app_data
    ADD DATAFILE '/u01/oradata/orcl/pdb1/app_data02.dbf' SIZE 500M;

Backup and Recovery of Individual PDBs Using RMAN

RMAN supports PDB-level backup and recovery operations:

-- Backup an individual PDB
RMAN> BACKUP PLUGGABLE DATABASE pdb1;

-- Backup specific tablespaces within a PDB
RMAN> BACKUP TABLESPACE pdb1:app_data;

-- Restore and recover a PDB
RMAN> RESTORE PLUGGABLE DATABASE pdb1;
RMAN> RECOVER PLUGGABLE DATABASE pdb1;

-- Duplicate a PDB from backup
RMAN> DUPLICATE PLUGGABLE DATABASE pdb1 TO pdb1_copy;

Monitoring PDB Performance and Resource Usage

-- View PDB resource consumption
SELECT pdb_name, cpu_usage, iops, mbps
FROM V$PDB_RESOURCE_STATS
WHERE pdb_name != 'CDB$ROOT';

-- View PDB session counts
SELECT pdb_name, COUNT(*) AS session_count
FROM V$SESSION
WHERE pdb_name IS NOT NULL
GROUP BY pdb_name;

-- View PDB memory usage
SELECT pdb_id, pdb_name, sga_target_mb, pga_target_mb
FROM V$PDB_MEMORY_STATS;

Best Practices for Multitenant Deployment

Week 3 — Security Management: Auditing, Encryption & Access Control Security

Unified Auditing Framework New

Starting with Oracle AI Database 26ai, Unified Auditing is the primary method for performing database auditing. It replaces traditional auditing methods and offers greater flexibility and control.

Key benefits of Unified Auditing:

🔒 Security First: Unified Auditing is mandatory in 26ai for full audit coverage. Ensure migration from traditional auditing is completed during the upgrade.

Configuring Audit Policies

-- Create an audit policy for AI workloads
CREATE AUDIT POLICY ai_workload_audit_policy
    ACTIONS
        ALTER, CREATE, DROP ON VECTOR
        ALTER, CREATE, DROP ON ONNX_MODEL
    PRIVILEGES
        ADMINISTER VECTOR INDEX
        CREATE MINING MODEL
    CONDITIONS
        USERNAME = 'APP_USER'
    EVALUATE PER STATEMENT;

-- Enable the audit policy
AUDIT POLICY ai_workload_audit_policy;

-- Create a policy for vector operations
CREATE AUDIT POLICY vector_ops_audit_policy
    ACTIONS
        SELECT ON VECTOR
        VECTOR_DISTANCE, VECTOR_EMBEDDING
    CONDITIONS
        SYS_CONTEXT('USERENV', 'IP_ADDRESS') NOT LIKE '192.168.%'
    EVALUATE PER STATEMENT;

AUDIT POLICY vector_ops_audit_policy;

-- View audit records
SELECT username, action_name, obj_name, timestamp
FROM unified_audit_trail
WHERE audit_type = 'UNIFIED'
ORDER BY timestamp DESC
FETCH FIRST 100 ROWS ONLY;

Improvements to Encryption, Auditing, and Real-Time Threat Detection New

Oracle AI Database 26ai introduces several security enhancements:

Managing Users, Roles, and Privileges in a Multitenant Environment

User management in a CDB is hierarchical:

-- Create a common user (in root)
CREATE USER C##APP_USER IDENTIFIED BY password123
    CONTAINER = ALL;

-- Grant common privileges
GRANT CREATE SESSION TO C##APP_USER CONTAINER = ALL;
GRANT CREATE PLUGGABLE DATABASE TO C##APP_USER CONTAINER = ALL;

-- Create a local user (in a specific PDB)
ALTER SESSION SET CONTAINER = pdb1;
CREATE USER local_user IDENTIFIED BY local_password;
GRANT CONNECT, RESOURCE TO local_user;

Transparent Data Encryption (TDE) and Backup Encryption

TDE encrypts data at rest, protecting against unauthorized access to data files and backups.

-- Set up TDE
-- Step 1: Create a wallet directory
-- Step 2: Configure sqlnet.ora
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/orcl/wallet)))

-- Step 3: Create the master encryption key
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/orcl/wallet' IDENTIFIED BY wallet_password;

-- Step 4: Open the keystore
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY wallet_password;

-- Step 5: Set the master key
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY wallet_password WITH BACKUP;

-- Encrypt a tablespace
ALTER TABLESPACE users ENCRYPTION ONLINE USING 'AES256' ENCRYPT;

-- Create an encrypted tablespace
CREATE TABLESPACE secure_data
    DATAFILE '/u01/oradata/orcl/secure_data01.dbf' SIZE 500M
    ENCRYPTION USING 'AES256'
    DEFAULT STORAGE (ENCRYPT);

-- Encrypt backups
RMAN> CONFIGURE ENCRYPTION FOR BACKUP ON;
RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES256';

Schema-Level Privileges New

Schema-level privileges simplify access management by granting privileges on all objects in a schema:

-- Grant schema-level privileges
GRANT SELECT ANY TABLE ON SCHEMA hr TO app_user;
GRANT EXECUTE ANY PROCEDURE ON SCHEMA hr TO app_user;
GRANT INSERT ANY TABLE ON SCHEMA app_schema TO analyst_user;

-- Revoke schema-level privileges
REVOKE SELECT ANY TABLE ON SCHEMA hr FROM app_user;

Deprecated and Desupported Features Important

Enterprise User Security (EUS) — Deprecated Deprecated

Enterprise User Security (EUS) is deprecated in Oracle AI Database 26ai. Organizations using EUS should plan migration to:

EM Express — Desupported Desupported

Enterprise Manager Database Express (EM Express) is desupported in Oracle AI Database 26ai. Recommended replacements:

⚠️ Migration Required: Plan migration from EM Express and EUS before upgrading to 26ai. Review the upgrade guide for detailed migration steps.

Week 4 — Performance Tuning, Diagnostics & AI-Driven Optimization Performance

AI-Driven Performance Architecture AI

Oracle AI Database 26ai introduces AI-driven diagnostics, self-healing, and self-optimization capabilities that automatically detect and resolve performance issues:

🤖 AI-Powered DBA: Oracle 26ai's self-driving capabilities reduce manual DBA intervention, allowing DBAs to focus on strategic tasks rather than routine maintenance.

Automatic Workload Repository (AWR) and ADDM

AWR and ADDM are the foundation of Oracle's performance monitoring and tuning framework:

AWR (Automatic Workload Repository)

AWR captures performance statistics and SQL execution data over time:

-- Generate AWR report
-- Run @$ORACLE_HOME/rdbms/admin/awrrpt.sql
-- Or use the following SQL to generate a report
SELECT DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    l_dbid => (SELECT DBID FROM V$DATABASE),
    l_inst_num => 1,
    l_bid => 12345,
    l_eid => 12346
) FROM DUAL;

-- View AWR snapshots
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC
FETCH FIRST 20 ROWS ONLY;

ADDM (Automatic Database Diagnostic Monitor)

ADDM analyzes AWR data to identify performance problems and provide recommendations:

-- Generate ADDM report for a specific time period
DECLARE
    v_report CLOB;
BEGIN
    v_report := DBMS_ADDM.ANALYZE_DB(
        begin_snap => 12345,
        end_snap => 12346,
        dbid => (SELECT DBID FROM V$DATABASE),
        inst_num => 1
    );
    DBMS_OUTPUT.PUT_LINE(v_report);
END;
/

-- ADDM report for a specific task
SELECT DBMS_ADDM.GET_REPORT('ADDM_TASK_123') FROM DUAL;

Cluster Health Monitor Improvements New

Cluster Health Monitor enhancements in 26ai include:

-- View Cluster Health Monitor status
SELECT status, last_scan_time, next_scan_time
FROM V$CLUSTER_HEALTH_MONITOR;

-- View health check results
SELECT component, check_time, status, message
FROM DBA_HEALTH_CHECKS
ORDER BY check_time DESC
FETCH FIRST 20 ROWS ONLY;

SQL Diagnostic Report New

The SQL Diagnostic Report provides deep-level HTML diagnostic reports including plan history, optimizer statistics, and index details:

-- Generate SQL Diagnostic Report for a specific SQL ID
SELECT DBMS_SQLDIAG.REPORT_SQL_DIAGNOSTIC(
    sql_id => 'dcttgua9qy5x4',
    type => 'HTML',
    diagnostic_details => 'ALL'
) FROM DUAL;

-- Generate report with specific options
SELECT DBMS_SQLDIAG.REPORT_SQL_DIAGNOSTIC(
    sql_id => 'dcttgua9qy5x4',
    type => 'TEXT',
    diagnostic_details => 'PLAN_HISTORY,OPTIMIZER_STATS,INDEX_DETAILS'
) FROM DUAL;

Managing AI Vector Search Workloads Alongside OLTP AI

Oracle AI Database 26ai enables managing AI Vector Search workloads alongside high-volume OLTP transactions in the same engine:

-- View vector workload statistics
SELECT pdb_name, vector_memory_used_mb, vector_index_scan_count
FROM V$VECTOR_WORKLOAD_STATS;

-- Configure vector memory
ALTER SYSTEM SET VECTOR_MEMORY_SIZE = 4G;
ALTER SYSTEM SET VECTOR_INDEX_MEMORY_LIMIT = 2G;

Performance Tuning for Vector Indexes (HNSW) with Concurrent DML New

HNSW (Hierarchical Navigable Small World) indexes require specific tuning for concurrent DML operations:

-- Create an HNSW index optimized for concurrent DML
CREATE VECTOR INDEX products_vec_idx ON products(embedding)
ORGANIZATION HNSW
DISTANCE COSINE
WITH TARGET ACCURACY 95
PARAMETERS ('optimize_for_concurrent_dml=true');

-- Rebuild index with concurrent DML optimization
ALTER VECTOR INDEX products_vec_idx
REBUILD PARAMETERS ('optimize_for_concurrent_dml=true');

-- Monitor vector index performance
SELECT index_name, status, vector_memory_usage_mb,
       concurrent_dml_ops, avg_query_time_ms
FROM V$VECTOR_INDEX_STATS;

Telemetry Streaming New

Telemetry Streaming provides ultra-fast metrics ingestion for observability using REST, SQL, and PromQL:

-- Enable telemetry streaming
ALTER SYSTEM SET TELEMETRY_STREAMING_ENABLED = TRUE;

-- Configure Prometheus endpoint
ALTER SYSTEM SET PROMETHEUS_EXPOSER_ENABLED = TRUE;
ALTER SYSTEM SET PROMETHEUS_EXPOSER_PORT = 9090;

-- Query telemetry data
SELECT metric_name, value, timestamp
FROM V$TELEMETRY_METRICS
WHERE metric_name IN ('cpu_usage', 'memory_usage', 'vector_query_latency')
ORDER BY timestamp DESC
FETCH FIRST 100 ROWS ONLY;

Priority Transactions New

Priority transactions handle lock contention by prioritising critical transactions over less important ones:

-- Set transaction priority
SET TRANSACTION PRIORITY HIGH;

-- Perform critical operation
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 12345;

-- Commit immediately to release locks
COMMIT;

-- View transaction priority stats
SELECT priority, COUNT(*) AS transaction_count,
       AVG(wait_time_ms) AS avg_wait_time
FROM V$TRANSACTION_HISTORY
GROUP BY priority;

Week 5 — Backup, Recovery & High Availability Backup & Recovery

RMAN Fundamentals

Recovery Manager (RMAN) is Oracle's primary backup and recovery tool. RMAN offers several backup types:

-- Configure RMAN settings
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/%F';

-- Perform a full database backup
RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

-- Perform incremental backups (recommended for large databases)
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG DELETE INPUT;
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE PLUS ARCHIVELOG DELETE INPUT;

Desupported Feature: Data Recovery Advisor (DRA) Desupported

The Data Recovery Advisor (DRA) feature is desupported in Oracle AI Database 26ai. The associated RMAN commands (LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE) are no longer available.

⚠️ Important: DRA is desupported in 26ai. DBAs must adapt RMAN workflows to use direct RMAN commands for recovery operations. The recommended approach is to use RMAN's RESTORE and RECOVER commands with appropriate validation.

Adapting RMAN Workflows

Instead of using DRA, use the following RMAN approach:

-- Validate database before restore
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;

-- Validate specific tablespaces
RMAN> BACKUP VALIDATE TABLESPACE users;

-- Restore and recover using RMAN (alternative to DRA)
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;

-- Restore specific tablespace
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;

Configuring Backup Strategies for CDB and PDB Environments

Backup strategies differ for CDB and PDB environments:

-- Backup the entire CDB (all PDBs)
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

-- Backup specific PDBs
RMAN> BACKUP PLUGGABLE DATABASE pdb1, pdb2;

-- Backup specific tablespaces within a PDB
RMAN> BACKUP TABLESPACE pdb1:users, pdb1:sysaux;

-- Backup the root container only
RMAN> BACKUP DATABASE ROOT;

Using Oracle Data Pump

Oracle Data Pump (expdp/impdp) is the recommended tool for logical backups and data movement. The original EXP utility is desupported.

# Export a PDB
expdp system/password@localhost:1521/pdb1 \
    directory=DATA_PUMP_DIR \
    dumpfile=pdb1_export.dmp \
    schemas=hr,app_schema \
    parallel=4

# Import a PDB
impdp system/password@localhost:1521/pdb1 \
    directory=DATA_PUMP_DIR \
    dumpfile=pdb1_export.dmp \
    schemas=hr,app_schema \
    table_exists_action=replace \
    parallel=4

# Export entire CDB (root + PDBs)
expdp system/password@localhost:1521/orcl \
    directory=DATA_PUMP_DIR \
    dumpfile=cdb_export.dmp \
    full=y \
    parallel=4

Point-in-Time Recovery and Tablespace Recovery

-- Point-in-time recovery for a PDB
RMAN> RUN {
    SET UNTIL TIME "TO_DATE('01-06-2026 14:00:00','DD-MM-YYYY HH24:MI:SS')";
    RESTORE PLUGGABLE DATABASE pdb1;
    RECOVER PLUGGABLE DATABASE pdb1;
}

-- Tablespace point-in-time recovery (TSPITR)
RMAN> RECOVER TABLESPACE users UNTIL TIME "TO_DATE('01-06-2026 14:00:00','DD-MM-YYYY HH24:MI:SS')";

Setting Up Oracle Data Guard

Oracle Data Guard provides disaster recovery and high availability:

-- Primary database configuration
-- Step 1: Enable force logging
ALTER DATABASE FORCE LOGGING;

-- Step 2: Create standby redo logs
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/oradata/orcl/stby_redo04.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/oradata/orcl/stby_redo05.log') SIZE 200M;

-- Step 3: Create and configure the standby control file
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/standby_control.ctl';

-- Step 4: Configure Data Guard Broker (recommended)
ALTER SYSTEM SET DG_BROKER_START = TRUE;
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1 = '/u01/app/oracle/product/26.0.0/dbhome_1/dbs/dr1orcl.dat';
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2 = '/u01/app/oracle/product/26.0.0/dbhome_1/dbs/dr2orcl.dat';

-- Step 5: Create and configure the standby database on the standby server

Configuring Data Guard Broker

-- Connect to the primary database using DGMGRL
$ dgmgrl sys/password@orcl

-- Create the configuration
DGMGRL> CREATE CONFIGURATION 'DR_ORCL' AS PRIMARY DATABASE IS 'orcl' CONNECT IDENTIFIER IS 'orcl';

-- Add the standby database
DGMGRL> ADD DATABASE 'orcl_standby' AS CONNECT IDENTIFIER IS 'orcl_standby' MAINTAINED AS PHYSICAL;

-- Enable the configuration
DGMGRL> ENABLE CONFIGURATION;

-- Verify the configuration
DGMGRL> SHOW CONFIGURATION;

-- Switchover (planned failover)
DGMGRL> SWITCHOVER TO 'orcl_standby';

-- Failover (unplanned)
DGMGRL> FAILOVER TO 'orcl_standby';

Flashback Technologies

Flashback technologies enable quick recovery without restoring from backups:

-- Enable Flashback Database
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/flashback';
ALTER DATABASE FLASHBACK ON;

-- Flashback database to a specific time
FLASHBACK DATABASE TO TIMESTAMP (TO_DATE('01-06-2026 14:00:00','DD-MM-YYYY HH24:MI:SS'));

-- Flashback table to a specific time
FLASHBACK TABLE employees TO TIMESTAMP (TO_DATE('01-06-2026 14:00:00','DD-MM-YYYY HH24:MI:SS'));

-- Flashback table to a specific SCN
FLASHBACK TABLE employees TO SCN 123456789;

-- View flashback status
SELECT flashback_on FROM V$DATABASE;

Week 6 — Patching, Automation, Fleet Management & Capstone Project Automation

Patching Strategy for 26ai

Oracle AI Database 26ai offers two primary patching approaches:

🔧 Best Practice: Use gold images for consistent, tested deployments across the enterprise. Use individual patches only for specific fixes that cannot wait for the next gold image release.

Oracle Update Advisor New

The Oracle Update Advisor provides software maintenance recommendations and patch status:

-- Generate update advisor report
SELECT DBMS_UPDATE_ADVISOR.GET_UPDATE_RECOMMENDATIONS(
    target_type => 'ORACLE_DATABASE',
    target_version => '26.0.0.0.0'
) FROM DUAL;

-- View current patch status
SELECT patch_id, patch_name, patch_description, status
FROM DBA_UPDATE_PATCHES
ORDER BY patch_id DESC;

-- Check for recommended patches
SELECT patch_id, severity, category, description
FROM DBA_UPDATE_ADVISOR_RECOMMENDATIONS
WHERE severity IN ('CRITICAL', 'SECURITY')
ORDER BY severity DESC;

Oracle Fleet Patching and Provisioning (FPP) New

Oracle Fleet Patching and Provisioning (FPP) automates gold image creation and deployment:

# Create a gold image
$ fpp createImage -source /u01/oracle/home -image /images/db_26.0.0.0.0.img -name DB_26ai_Base

# Add patches to the gold image
$ fpp addPatch -image /images/db_26.0.0.0.0.img -patch /patches/oct2025_patch.zip -name DB_26ai_Oct2025

# Deploy the gold image to a target server
$ fpp deployImage -target server1.example.com -image /images/db_26ai_Oct2025.img -oracleHome /u01/oracle/product/26.0.0/dbhome_1

# List available images
$ fpp listImages

# Rollback to a previous image
$ fpp rollbackImage -target server1.example.com -image /images/db_26.0.0.0.0.img

Automating Routine DBA Tasks with Oracle Scheduler

Oracle Scheduler automates routine DBA tasks:

-- Create a job for statistics gathering
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'GATHER_STATS_JOB',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN DBMS_STATS.GATHER_DATABASE_STATS; END;',
        start_date      => SYSDATE,
        repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
        enabled         => TRUE,
        comments        => 'Daily statistics gathering at 2 AM'
    );
END;
/

-- Create a job for AWR snapshot
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'AWR_SNAPSHOT_JOB',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT; END;',
        start_date      => SYSDATE,
        repeat_interval => 'FREQ=HOURLY; INTERVAL=1',
        enabled         => TRUE,
        comments        => 'Hourly AWR snapshots'
    );
END;
/

-- Create a job for backup
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'BACKUP_JOB',
        job_type        => 'EXECUTABLE',
        job_action      => '/u01/scripts/backup.sh',
        start_date      => SYSDATE,
        repeat_interval => 'FREQ=DAILY; BYHOUR=1; BYMINUTE=0; BYSECOND=0',
        enabled         => TRUE,
        comments        => 'Daily database backup at 1 AM'
    );
END;
/

-- View job status
SELECT job_name, state, last_start_date, next_run_date
FROM DBA_SCHEDULER_JOBS
ORDER BY job_name;

Managing Oracle AI Database in Cloud and Hybrid Environments

Using Operating System Certificate Store for Database Authentication New

Oracle AI Database 26ai supports using the operating system certificate store for database authentication instead of client wallets:

-- Configure operating system certificate store
-- Linux: /etc/pki/tls/certs/
-- Windows: Windows Certificate Store

-- Set up SSL/TLS using OS certificates
-- In sqlnet.ora
SSL_CLIENT_AUTHENTICATION = TRUE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA)

-- In listener.ora
SSL_CLIENT_AUTHENTICATION = TRUE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA)

-- Verify SSL/TLS configuration
SELECT status, protocol, cipher_suite
FROM V$SSL_CONNECTIONS;

Best Practices for Database Maintenance, Monitoring Alerts, and Capacity Planning

Maintenance Best Practices

Monitoring and Alerting

-- Set up alert thresholds
-- CPU utilization alert
EXEC DBMS_SERVER_ALERT.SET_THRESHOLD(
    metrics_id => DBMS_SERVER_ALERT.CPU_UTILIZATION,
    warning_operator => DBMS_SERVER_ALERT.OP_GE,
    warning_value => '80',
    critical_operator => DBMS_SERVER_ALERT.OP_GE,
    critical_value => '90',
    observation_period => 5,
    consecutive_occurrences => 3,
    instance_name => 'orcl'
);

-- Tablespace usage alert
EXEC DBMS_SERVER_ALERT.SET_THRESHOLD(
    metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
    warning_operator => DBMS_SERVER_ALERT.OP_GE,
    warning_value => '80',
    critical_operator => DBMS_SERVER_ALERT.OP_GE,
    critical_value => '90',
    observation_period => 5,
    consecutive_occurrences => 2
);

-- View alert history
SELECT object_name, severity, message, timestamp
FROM DBA_ALERT_HISTORY
ORDER BY timestamp DESC
FETCH FIRST 100 ROWS ONLY;

Capacity Planning

-- View database growth trends
SELECT tablespace_name, SUM(bytes)/1024/1024/1024 AS size_gb
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY size_gb DESC;

-- View segment growth
SELECT owner, segment_name, segment_type, bytes/1024/1024 AS size_mb
FROM dba_segments
WHERE bytes > 1024*1024*100  -- Segments > 100 MB
ORDER BY bytes DESC
FETCH FIRST 50 ROWS ONLY;

-- Monitor redo generation
SELECT to_char(first_time, 'YYYY-MM-DD') AS day,
       count(*) AS log_switches,
       sum(blocks*block_size)/1024/1024/1024 AS redo_gb
FROM v$log_history
WHERE first_time > SYSDATE - 7
GROUP BY to_char(first_time, 'YYYY-MM-DD')
ORDER BY day;

Capstone Project

Project Objective: Build and manage a complete Oracle AI Database 26ai environment from installation to production, implementing security, performance tuning, backup strategies, and automated patching.

Project Deliverables:

🎯 Capstone Goal: Deploy, secure, and maintain a production-ready Oracle AI Database 26ai environment with Multitenant, Data Guard, and automated patching, demonstrating mastery of all DBA skills covered in the course.

📚 References:

All documentation links are for Oracle AI Database 26ai (Release 23.26.1).

© Copyright Westminster College of AI and Quantum Computing Limited, 2026. | info@wcc.co.uk | https://wcc.co.uk