Oracle Database 19c: Administration Workshop

Duration: 40 Hours (5 Days)

Overview
The Oracle Database 19c: Administration Workshop is a comprehensive course designed to equip learners
with the skills necessary to effectively manage an Oracle Database. This course covers essential topics
that are critical for database administrators, including database architecture, installation, configuration,
instance management, storage structures, security, and maintaining High availability and [Link]
engaging with this course, participants will gain a deep understanding of the Oracle Database architecture
and will learn how to prepare their environment for a smooth Oracle Database 19c installation. They will
also become proficient in configuring Oracle Net Services and managing various database components,
such as Initialization parameters, memory, processes, Control files, Redo logs, and Archive
[Link], the course delves into managing storage structures, securing the database environment
by handling users, roles, and permissions, and implementing strategies for backup, recovery, and
Performance tuning. Learners will also explore High availability features like Oracle Data Guard and
Database replication, ensuring they have the knowledge to maintain a resilient and efficient Oracle
Database [Link] the end of the course, participants will have a solid foundation in Oracle Database
administration, empowering them to manage and optimize Oracle databases effectively for their
organizations.

Audience Profile
The Oracle Database 19c: Administration Workshop is designed for IT professionals seeking to manage
Oracle database systems effectively.
• Database Administrators (DBAs)
• Systems Administrators managing database servers
• Database Architects and Designers
• IT Managers overseeing database operations
• Database Developers needing to understand the underlying architecture
• Data Analysts requiring knowledge of database management
• Technical Consultants involved in Oracle Database projects
• IT Professionals preparing for Oracle Database Administration certification
• Technical Support Engineers responsible for maintaining Oracle Database systems

Course Syllabus
Introduction to Oracle Database
• Objectives 1-2
• Oracle Database Server Architecture: Overview 1-3
• Oracle Multitenant Container Database: Introduction 1-4
• Oracle Multitenant Container Database: Architecture 1-5
• Oracle Database Instance Configurations 1-6
• Database Sharding: Introduction 1-7
• Oracle Database Server: Interactive Architecture Diagram 1-8
• Summary 1-9
Accessing an Oracle Database
• Objectives 2-2
• Connecting to an Oracle Database Instance 2-3
• Oracle Database Tools 2-5
• Database Tool Choices 2-6
• SQL*Plus 2-7
• Oracle SQL Developer 2-9
• Oracle SQL Developer: Connections 2-10
• Oracle SQL Developer: DBA Actions 2-11
• Database Configuration Assistant (DBCA) 2-12
• Oracle Enterprise Manager Database Express 2-13
• Enterprise Manager Cloud Control 13c Features 2-15
• Oracle Enterprise Manager Component Overview 2-17
• Single Pane of Glass for Enterprise Management 2-18
• Oracle Enterprise Manager Database Management 2-19
• Summary 2-21

Creating an Oracle Database by Using DBCA

• Objectives 3-2
• Planning the Database 3-3
• Choosing a Database Template 3-4
• Choosing the Appropriate Character Set 3-5
• How Are Character Sets Used? 3-7
• Setting NLS_LANG Correctly on the Client 3-8
• Using the Database Configuration Assistant 3-9
• Using DBCA in Silent Mode 3-10
• Summary 3-11
• Practice Overview 3-12

Creating an Oracle Database by Using a SQL Command

• Objectives 4-2
• Creating a Container Database (CDB) 4-3
• Creating a CDB by Using a SQL Command: Example 4-4
• Using the SEED FILE_NAME_CONVERT Clause 4-5
• Using the ENABLE PLUGGABLE DATABASE Clause 4-6
• Summary 4-7
• Practice Overview 4-8

Starting Up and Shutting Down a Database Instance

• Objectives 5-2
• Starting the Oracle Database Instance 5-3
• Shutting Down an Oracle Database Instance 5-4
• Comparing SHUTDOWN Modes 5-6
• Opening and Closing PDBs 5-8
• Configuring PDBs to Automatically Open 5-9
• Summary 5-10
• Practice Overview 5-11
Managing Database Instances
• Objectives 6-2
• Working with Initialization Parameters 6-3
• Initialization Parameters 6-5
• Modifying Initialization Parameters 6-7
• Viewing Initialization Parameters 6-9
• Working with the Automatic Diagnostic Repository 6-11
• Automatic Diagnostic Repository 6-12
• Viewing the Alert Log 6-13
• Using Trace Files 6-14
• Administering the DDL Log File 6-16
• Querying Dynamic Performance Views 6-17
• Considerations for Dynamic Performance Views 6-18
• Data Dictionary: Overview 6-19
• Querying the Oracle Data Dictionary 6-20
• Summary 6-22
• Practice Overview 6-23

Oracle Net Services: Overview

• Objectives 7-2
• Connecting to the Database Instance 7-3
• Oracle Net Services: Overview 7-4
• Defining Oracle Net Services Components 7-5
• Tools for Configuring and Managing Oracle Net Services 7-6
• Oracle Net Listener: Overview 7-7
• The Default Listener 7-8
• Comparing Dedicated and Shared Server Architecture 7-9
• Summary 7-10

Configuring Naming Methods

• Objectives 8-2
• Establishing Oracle Network Connections 8-3
• Connecting to an Oracle Database Instance 8-4
• Name Resolution 8-5
• Establishing a Connection 8-6
• User Sessions 8-7
• Naming Methods 8-8
• Easy Connect 8-9
• Local Naming 8-10
• Directory Naming 8-11
• Using Database Services to Manage Workloads 8-12
• Creating Database Services 8-13
• Summary 8-14
• Practice Overview 8-15

Configuring and Administering the Listener

• Objectives 9-2
 • Review: Oracle Net Services Overview 9-3
• Oracle Net Listener: Overview 9-4
• The Default Listener 9-5
• Configuring Dynamic Service Registration 9-6
• Configuring Static Service Registration 9-8
• Summary 9-10
• Practice Overview 9-11

Configuring a Shared Server Architecture

• Objectives 10-2
• Shared Server Architecture: Overview 10-3
• Comparing Dedicated and Shared Server Architecture: Review 10-4
• Enabling Shared Server 10-5
• Controlling Shared Server Operations 10-6
• SGA and PGA Usage 10-7
• Shared Server Configuration Considerations 10-8
• Summary 10-9
• Practice Overview 10-10

Configuring Oracle Connection Manager for Multiplexing and Access

Control
• Objectives 11-2
• Oracle Connection Manager: Overview 11-3
• Oracle Connection Manager Processes 11-4
• Oracle Connection Manager: Architecture 11-5
• Using Filtering Rules 11-6
• Implementing Intranet Access Control 11-7
• Implementing Internet Access Control 11-8
• Using Session Multiplexing 11-9
• Configuring Oracle Connection Manager 11-10
• Configuring the [Link] File 11-11
• Example of a [Link] File 11-12
• Configuring Clients 11-13
• Configuring the Database Server 11-14
• Configuring the Database Server for Multiplexing (Optional) 11-15
• Using the Oracle Connection Manager Control Utility 11-16
• Review of Oracle Connection Manager Features 11-17
• Summary 11-18

Creating PDBs from Seed

• Objectives 12-2
• Provisioning New Pluggable Databases 12-3
• Tools 12-4
• Creating a New PDB from PDB$SEED 12-5
• Using the FILE_NAME_CONVERT Clause 12-6
• Using OMF or the PDB_FILE_NAME_CONVERT Parameter 12-7
• Summary 12-8
 • Practice Overview 12-9

Using Other Techniques to Create PDBs

• Objectives 13-2
• Cloning Regular PDBs 13-3
• Migrating Data from a Non-CDB into a CDB 13-4
• Plugging a Non-CDB into CDB Using DBMS_PDB 13-5
• Replicating a Non-CDB into a CDB by Using GoldenGate 13-6
• Cloning a Non-CDB or Remote PDB 13-7
• Using DBCA to Clone a Remote PDB 13-8
• Plugging an Unplugged Regular PDB into CDB 13-9
• Plugging in a PDB Using an Archive File 13-10
• Cloning Remote PDBs in Hot Mode 13-11
• Near-Zero Downtime PDB Relocation 13-12
• Using DBCA to Relocate a Remote PDB 13-14
• Proxy PDB: Query Across CDBs Proxying Root Replica 13-15
• Creating a Proxy PDB 13-16
• Summary 13-17

Managing PDBs
• Objectives 14-2
• Changing the PDB Mode 14-3
• Modifying PDB Settings 14-4
• Impact of Changing Initialization Parameters 14-5
• Changing Initialization Parameters: Example 14-6
• Using the ALTER SYSTEM Command in a PDB 14-7
• Configuring Host Name and Port Number per PDB 14-8
• Dropping PDBs 14-9
• Summary 14-10
• Practice Overview 14-11

Database Storage Overview

• Objectives 15-2
• Database Storage Architecture 15-3
• Logical and Physical Database Structures 15-5
• Segments, Extents, and Blocks 15-7
• Tablespaces and Data Files 15-8
• Default Tablespaces in a Multitenant Container Database 15-9
• SYSTEM and SYSAUX Tablespaces 15-10
• Types of Segments 15-11
• How Table Data Is Stored 15-12
• Database Block Content 15-13
• Understanding Deferred Segment Creation 15-14
• Controlling Deferred Segment Creation 15-15
• Monitoring Tablespace Space Usage 15-16
• Oracle Database Storage Structures: Interactive Architecture Diagram 15-17
• Summary 15-18
Creating and Managing Tablespaces
• Objectives 16-2
• Creating Tablespaces 16-3
• Creating a Tablespace: Clauses 16-4
• Creating Permanent Tablespaces in a CDB 16-7
• Defining Default Permanent Tablespaces 16-8
• Temporary Tablespaces 16-9
• Altering and Dropping Tablespaces 16-10
• Viewing Tablespace Information 16-12
• Implementing Oracle Managed Files (OMF) 16-13
• Enlarging the Database 16-15
• Moving or Renaming Online Data Files 16-16
• Examples: Moving and Renaming Online Data Files 16-17
• Summary 16-18
• Practice Overview 16-19

Improving Space Usage

• Objectives 17-2
• Space Management Features 17-3
• Block Space Management 17-4
• Row Chaining and Migration 17-5
• Free Space Management Within Segments 17-6
• Allocating Extents 17-7
• Using Unusable Indexes 17-8
• Using Temporary Tables 17-9
• Creating Global Temporary Tables 17-10
• Creating Private Temporary Tables 17-11
• Table Compression: Overview 17-12
• Table Compression: Concepts 17-13
• Compression for Direct-Path Insert Operations 17-14
• Advanced Row Compression for DML Operations 17-15
• Specifying Table Compression 17-16
• Using the Compression Advisor 17-17
• Resolving Space Usage Issues 17-18
• Reclaiming Space by Shrinking Segments 17-19
• Shrinking Segments 17-20
• Results of a Shrink Operation 17-21
• Managing Resumable Space Allocation 17-22
• Using Resumable Space Allocation 17-23
• Resuming Suspended Statements 17-25
• What Operations Are Resumable? 17-27
• Summary 17-28
• Practice Overview 17-29

Managing Undo Data

• Objectives 18-2
• Undo Data: Overview 18-3
 • Transactions and Undo Data 18-5
• Storing Undo Information 18-6
• Comparing Undo Data and Redo Data 18-7
• Managing Undo 18-8
• Comparing SHARED Undo Mode and LOCAL Undo Mode 18-9
• Configuring Undo Retention 18-10
• Categories of Undo 18-11
• Guaranteeing Undo Retention 18-12
• Changing an Undo Tablespace to a Fixed Size 18-13
• Temporary Undo: Overview 18-14
• Temporary Undo Benefits 18-15
• Enabling Temporary Undo 18-16
• Monitoring Temporary Undo 18-17
• Summary 18-18
• Practice Overview 18-19

Creating and Managing User Accounts

• Objectives 19-2
• Database User Accounts 19-3
• Oracle-Supplied Administrator Accounts 19-5
• Creating Oracle Database Users in a Multitenant Environment 19-6
• Creating Common Users in the CDB and PDBs 19-7
• Creating Schema Only Accounts 19-8
• Authenticating Users 19-9
• Using Password Authentication 19-10
• Using Password File Authentication 19-11
• Using OS Authentication 19-12
• OS Authentication for Privileged Users 19-14
• Assigning Quotas 19-15
• Summary 19-17
• Practice Overview 19-18

Configuring Privilege and Role Authorization

• Objectives 20-2
• Privileges 20-3
• System Privileges 20-4
• System Privileges for Administrators 20-6
• Object Privileges 20-7
• Granting Privileges in a Multitenant Environment 20-8
• Granting Privileges: Example 20-9
• Using Roles to Manage Privileges 20-10
• Assigning Privileges to Roles and Assigning Roles to Users 20-11
• Oracle-Supplied Roles 20-12
• Granting Roles in a Multitenant Environment 20-13
• Granting Roles: Example 20-14
• Making Roles More Secure 20-15
• Revoking Roles and Privileges 20-16
• Granting and Revoking System Privileges 20-17
 • Granting and Revoking Object Privileges 20-18
• Summary 20-19
• Practice Overview 20-20

Configuring User Resource Limits

• Objectives 21-2
• Profiles and Users 21-3
• Creating Profiles in a Multitenant Architecture 21-4
• Creating Profiles: Example 21-5
• Profile Parameters: Resources 21-6
• Profile Parameters: Locking and Passwords 21-8
• Oracle-Supplied Password Verification Functions 21-10
• Assigning Profiles in a Multitenant Architecture 21-11
• Summary 21-12
• Practice Overview 21-13

Implementing Oracle Database Auditing

• Objectives 22-2
• Database Security 22-3
• Monitoring for Compliance 22-5
• Types of Activities to be Audited 22-6
• Mandatorily Audited Activities 22-7
• Understanding Auditing Implementation 22-8
• Administering the Roles Required for Auditing 22-9
• Database Auditing: Overview 22-10
• Configuring Auditing 22-11
• Creating a Unified Audit Policy 22-12
• Creating an Audit Policy: System-Wide Audit Options 22-13
• Creating an Audit Policy: Object-Specific Actions 22-14
• Creating an Audit Policy: Specifying Conditions 22-15
• Enabling and Disabling Audit Policies 22-16
• Auditing Actions in the CDB and PDBs 22-17
• Modifying a Unified Audit Policy 22-19
• Auditing Top-Level Statements Only 22-20
• Viewing Audit Policy Information 22-21
• Value-Based Auditing 22-22
• Fine-Grained Auditing 22-24
• FGA Policy 22-25
• Audited DML Statement: Considerations 22-27
• FGA Guidelines 22-28
• Archiving and Purging the Audit Trail 22-29
• Purging Audit Trail Records 22-30
• Summary 22-31
• Practice Overview 22-32

Introduction to Loading and Transporting Data

• Objectives 23-2
• Moving Data: General Architecture 23-3
 • Oracle Data Pump: Overview 23-4
• Oracle Data Pump: Benefits 23-5
• SQL Loader: Overview 23-7
• Summary 23-9

Loading Data
• Objectives 24-2
• SQL Loader: Review 24-3
• Creating the SQL*Loader Control File 24-4
• SQL*Loader Loading Methods 24-6
• Protecting Against Data Loss 24-7
• SQL*Loader Express Mode 24-8
• Using SQL*Loader to Load a Table in a PDB 24-9
• Summary 24-10
• Practice Overview 24-1

Transporting Data
• Objectives 25-2
• Data Pump Export and Import Clients 25-3
• Data Pump Interfaces and Modes 25-4
• Data Pump Import Transformations 25-6
• Using Oracle Data Pump with PDBs 25-7
• Exporting from a Non-CDB and Importing into a PDB 25-8
• Exporting and Importing Between PDBs 25-9
• Full Transportable Export/Import 25-10
• Full Transportable Export/Import: Example 25-12
• Transporting a Database Over the Network: Example 25-13
• Using RMAN to Transport Data Across Platforms 25-14
• RMAN CONVERT Command 25-15
• Transporting Data with Minimum Down Time 25-16
• Transporting a Tablespace by Using Image Copies 25-17
• Determining the Endian Format of a Platform 25-18
• Transporting Data with Backup Sets 25-19
• Transporting a Tablespace 25-20
• Transporting Inconsistent Tablespaces 25-22
• Database Transport: Data Files 25-23
• Transporting a Database 25-24
• Transporting a Database: Conversion 25-25
• Transporting a Database: Example 1 25-26
• Transporting a Database: Example 2 25-27
• Transporting a Database: Considerations 25-28
• Transporting a Database with Backup Sets 25-29
• Summary 25-31
• Practice Overview 25-32

Using External Tables to Load and Transport Data

• Objectives 26-2
• External Tables 26-3
 • External Tables: Benefits 26-4
• ORACLE_LOADER Access Driver 26-5
• ORACLE_DATAPUMP Access Driver 26-6
• External Tables 26-7
• Viewing Information About External Tables 26-8
• Summary 26-9
• Practice Overview 26-10

Automated Maintenance Tasks: Overview

• Objectives 27-2
• Proactive Database Maintenance Infrastructure 27-3
• Automated Maintenance Tasks: Components 27-4
• Predefined Automated Maintenance Tasks 27-5
• Maintenance Windows 27-6
• Predefined Maintenance Windows 27-7
• Viewing Maintenance Window Details 27-8
• Automated Maintenance Tasks 27-9
• Summary 27-10

Automated Maintenance Tasks: Managing Tasks and Windows

• Objectives 28-2
• Configuring Automated Maintenance Tasks 28-3
• Enabling and Disabling Maintenance Tasks 28-4
• Creating and Managing Maintenance Windows 28-5
• Resource Allocations for Automated Maintenance Tasks 28-6
• Changing Resource Allocations for Maintenance Tasks 28-7
• Summary 28-8
• Practice Overview 28-9

Database Monitoring and Tuning Performance Overview

• Objectives 29-2
• Performance Management Activities 29-3
• Performance Planning Considerations 29-4
• Database Maintenance 29-6
• Automatic Workload Repository (AWR) 29-7
• Automatic Database Diagnostic Monitor (ADDM) 29-8
• Configuring Automatic ADDM Analysis at the PDB Level 29-9
• Advisory Framework 29-10
• Performance Tuning Methodology 29-12
• Summary 29-13

Monitoring Database Performance

• Objectives 30-2
• Server-Generated Alerts 30-3
• Setting Metric Thresholds 30-4
• Reacting to Alerts 30-5
• Alert Types and Clearing Alerts 30-6
 • Database Server Statistics and Metrics 30-7
• Performance Monitoring 30-8
• Viewing Statistics Information 30-9
• Monitoring Wait Events 30-11
• Monitoring Sessions 30-12
• Monitoring Services 30-13
• Summary 30-14

Analyzing SQL and Optimizing Access Paths

• Objectives 31-2
• SQL Tuning Process 31-3
• Oracle Optimizer 31-4
• Optimizer Statistics 31-5
• Optimizer Statistics Collection 31-6
• Setting Optimizer Statistics Preferences 31-8
• Optimizer Statistics Advisor 31-10
• Optimizer Statistics Advisor Report 31-11
• Executing Optimizer Statistics Advisor Tasks 31-12
• SQL Plan Directives 31-13
• Adaptive Execution Plans 31-14
• SQL Tuning Advisor: Overview 31-16
• SQL Access Advisor: Overview 31-18
• SQL Performance Analyzer: Overview 31-19
• Managing Automated Tuning Tasks 31-21
• Summary 31-22