Oracle Database 23ai: Administration Workshop

This five-day instructor-led course provides database administrators with the knowledge and practical skills required to administer Oracle Database 23ai in enterprise environments. Aligned with the latest Oracle University curriculum, the course equips learners with the expertise needed to create, configure, secure, monitor and manage Oracle Database instances within multitenant architectures.

Through a combination of instructor-led learning and extensive hands-on labs, participants gain practical experience managing Oracle database environments while exploring the latest Oracle Database 23ai capabilities, including AI-powered database features, automation, performance optimisation, and high availability. Successful completion of the course prepares learners to confidently administer Oracle Database 23ai within modern enterprise environments.

The concepts, architecture and administration techniques covered in this course remain relevant to Oracle AI Database 26ai.

Prerequisites

Participants should have:

• A basic understanding of relational database concepts.
• Experience using SQL and Oracle Database.
• Familiarity with Linux or Windows operating systems.
• An understanding of database administration concepts is recommended.
• Previous experience with Oracle Database environments is beneficial.

Target audience

This course is designed for:

• Oracle database administrators.
• Database support engineers.
• Infrastructure and systems administrators responsible for Oracle environments.
• Database professionals transitioning to Oracle Database 23ai.
• IT professionals seeking to strengthen Oracle database administration skills.

By the end of this course, learners will be able to:

• Understand Oracle Database architecture, memory structures and process architecture.
• Create and manage Oracle databases using DBCA and SQL-based methods.
• Administer Container Databases (CDBs) and Pluggable Databases (PDBs).
• Configure Oracle Net Services, listeners and database connectivity.
• Manage database storage, tablespaces, undo data and space utilisation.
• Administer users, roles, privileges and resource limits.
• Implement auditing, compliance monitoring and database security controls.
• Load and transport data using Oracle Data Pump, SQL*Loader and external tables.
• Configure automated maintenance tasks and monitor database performance.
• Analyse SQL execution and apply Oracle performance tuning techniques.

Module 1: Introduction to Oracle Database

• Explore Oracle Database server architecture and instance configurations.
• Understand Oracle Multitenant Container Database concepts and architecture.
• Review Oracle memory structures, process architecture and background processes.
• Explore database sharding concepts and Oracle database architecture tools.

Module 2: Accessing an Oracle Database

• Connect to an Oracle Database instance.
• Review Oracle administration tools including SQL*Plus, Oracle SQL Developer, DBCA and Oracle Enterprise Manager.
• Understand tool choices for database administration and management.
• Explore Oracle Enterprise Manager and Cloud Control capabilities.

Module 3: Creating an Oracle Database by Using DBCA

• Plan an Oracle database deployment.
• Choose database templates and character set options.
• Create databases using Database Configuration Assistant (DBCA).
• Use DBCA in interactive and silent modes.

Module 4: Creating an Oracle Database by Using a SQL Command

• Create a Container Database (CDB) using SQL commands.
• Use SQL clauses to configure database files and pluggable database support.
• Understand options used when manually creating a CDB.
• Review key considerations when creating databases without DBCA.

Module 5: Starting Up and Shutting Down a Database Instance

• Start Oracle database instances.
• Shut down database instances using different shutdown modes.
• Open and close Pluggable Databases (PDBs).
• Configure PDBs to open automatically.

Module 6: Managing Database Instances

• Work with initialisation parameters.
• Modify and view database instance settings.
• Use diagnostic tools including alert logs, trace files and the Automatic Diagnostic Repository.
• Query dynamic performance views and the Oracle data dictionary.

Module 7: Oracle Net Services: Overview

• Understand Oracle Net Services architecture and components.
• Review how clients connect to Oracle Database instances.
• Explore listener concepts and Oracle Net configuration tools.
• Compare dedicated and shared server architectures.

Module 8: Configuring Naming Methods

• Configure database network connections.
• Understand name resolution and user session connectivity.
• Use Easy Connect, Local Naming and Directory Naming.
• Use database services to support workload management.

Module 9: Configuring and Administering the Listener

• Review Oracle Net Listener architecture.
• Configure dynamic and static service registration.
• Manage listener behaviour and database service registration.
• Support reliable client connectivity to Oracle databases.

Module 10: Configuring a Shared Server Architecture

• Understand shared server architecture.
• Compare shared server and dedicated server approaches.
• Enable and manage shared server operations.
• Review configuration considerations for SGA and PGA usage.

Module 11: Creating PDBs from Seed

• Provision new Pluggable Databases (PDBs).
• Create PDBs from PDB$SEED.
• Use file conversion clauses and Oracle Managed Files options.
• Review tools and methods for PDB creation.

Module 12: Using Other Techniques to Create PDBs

• Clone, plug in and relocate Pluggable Databases (PDBs).
• Migrate non-CDB databases to a multitenant environment.
• Explore PDB cloning, relocation and migration options.
• Use DBCA and SQL-based techniques to provision PDBs.

Module 13: Managing PDBs

• Manage PDB modes, settings and lifecycle operations.
• Configure PDB-specific initialisation parameters.
• Administer PDB connectivity and configuration settings.
• Manage PDB maintenance and removal activities.

Module 14: Database Storage Overview

• Understand Oracle logical and physical storage structures.
• Review segments, extents, blocks, tablespaces and data files.
• Understand default tablespaces in a multitenant database.
• Monitor tablespace space usage.

Module 15: Creating and Managing Tablespaces

• Create, alter and drop tablespaces.
• Configure permanent and temporary tablespaces.
• View tablespace information and manage database files.
• Implement Oracle Managed Files (OMF).

Module 16: Improving Space Usage

• Manage database space allocation and storage efficiency.
• Improve storage usage using Oracle space management features.
• Apply compression and segment management techniques.
• Resolve common database space usage issues.

Module 17: Managing Undo Data

• Understand undo data and transaction handling.
• Compare undo and redo data.
• Configure undo retention and undo tablespaces.
• Use temporary undo and monitor undo behaviour.

Module 18: Creating and Managing User Accounts

• Create Oracle Database users in a multitenant environment.
• Manage common users, local users and schema-only accounts.
• Configure authentication options.
• Assign quotas and manage user account settings.

Module 19: Configuring Privilege and Role Authorization

• Grant and revoke system, schema-level and object privileges.
• Use roles to simplify privilege management.
• Configure privileges in a multitenant environment.
• Review Oracle-supplied roles and secure role management.

Module 20: Configuring User Resource Limits

• Create and manage profiles.
• Configure password and locking policies.
• Set resource limits for users.
• Assign profiles within a multitenant architecture.

Module 21: Implementing Oracle Database Auditing

• Understand database auditing and compliance monitoring.
• Create and manage unified audit policies.
• Configure system-wide, object-specific and conditional auditing.
• Implement Fine-Grained Auditing (FGA).
• Archive and purge audit trail records.

Module 22: Introduction to Loading and Transporting Data

• Understand Oracle data movement architecture.
• Review Oracle Data Pump capabilities.
• Explore SQL*Loader concepts.
• Identify approaches for loading and transporting data.

Module 23: Loading Data

• Use SQL*Loader to load data into Oracle tables.
• Create and manage SQL*Loader control files.
• Use different loading methods and Express Mode.
• Load data into tables within a PDB.

Module 24: Transporting Data

• Use Oracle Data Pump export and import clients.
• Transport data between non-CDB, CDB and PDB environments.
• Use full transportable export/import.
• Use RMAN options for cross-platform data transport.
• Transport tablespaces and data with reduced downtime.

Module 25: Using External Tables to Load and Transport Data

• Understand external table concepts and benefits.
• Use ORACLE_LOADER and ORACLE_DATAPUMP access drivers.
• View and manage external table information.
• Support data loading and transport using external tables.

Module 26: Automated Maintenance Tasks: Overview

• Understand Oracle’s proactive database maintenance infrastructure.
• Review predefined automated maintenance tasks.
• Understand maintenance windows and automated task components.
• Explore how automated maintenance supports database health.

Module 27: Automated Maintenance Tasks: Managing Tasks and Windows

• Configure automated maintenance tasks.
• Enable and disable maintenance tasks.
• Create and manage maintenance windows.
• Configure resource allocation for maintenance activities.

Module 28: Database Monitoring and Tuning Performance Overview

• Understand performance management activities.
• Use Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM).
• Review the Oracle advisory framework.
• Apply a structured performance tuning methodology.

Module 29: Monitoring Database Performance

• Configure and respond to server-generated alerts.
• Monitor database server statistics and metrics.
• Review wait events, sessions and services.
• Use performance monitoring information to identify issues.

Module 30: Analyzing SQL and Optimizing Access Paths

• Understand Oracle SQL tuning and optimisation techniques.
• Manage optimizer statistics and execution plans.
• Use SQL Tuning Advisor, SQL Access Advisor and SQL Performance Analyzer.
• Analyse and improve SQL query performance.

Exams and assessments

Learners reinforce their knowledge through practical hands-on labs, instructor-led demonstrations and knowledge-based exercises covering Oracle Database architecture, multitenant administration, security, data movement, maintenance and performance tuning. There is no formal examination included as part of this course.

Hands-on learning

This course includes:

• Practical Oracle Database 23ai administration labs.

• Hands-on exercises creating and managing Container Databases (CDBs) and Pluggable Databases (PDBs).
• Guided activities using Oracle Net Services, listeners and database administration tools.
• Practical exercises covering user administration, security, auditing and data movement.
• Real-world database administration, monitoring and SQL tuning scenarios.
• Instructor-led demonstrations covering Oracle Database administration best practices.