Oracle Database 19c: Administration Workshop

Introduction to Oracle Database

• Objectives
• Oracle Database Server Architecture: Overview
• Oracle Multitenant Container Database: Introduction
• Oracle Multitenant Container Database: Architecture
• Oracle Database Instance Configurations
• Database Sharding: Introduction
• Oracle Database Server: Interactive Architecture Diagram
• Summary

Accessing an Oracle Database

• Objectives
• Connecting to an Oracle Database Instance
• Oracle Database Tools
• Database Tool Choices
• SQL*Plus
• Oracle SQL Developer
• Oracle SQL Developer: Connections
• Oracle SQL Developer: DBA Actions
• Database Configuration Assistant (DBCA)
• Oracle Enterprise Manager Database Express
• Enterprise Manager Cloud Control 13c Features
• Oracle Enterprise Manager Component Overview
• Single Pane of Glass for Enterprise Management
• Oracle Enterprise Manager Database Management
• Summary

Creating an Oracle Database by Using DBCA

• Objectives
• Planning the Database
• Choosing a Database Template
• Choosing the Appropriate Character Set
• How Are Character Sets Used?
• Setting NLS_LANG Correctly on the Client
• Using the Database Configuration Assistant
• Using DBCA in Silent Mode
• Summary
• Practice Overview

Creating an Oracle Database by Using a SQL Command

• Objectives
• Creating a Container Database (CDB)
• Creating a CDB by Using a SQL Command: Example
• Using the SEED FILE_NAME_CONVERT Clause
• Using the ENABLE PLUGGABLE DATABASE Clause
• Summary
• Practice Overview

Starting Up and Shutting Down a Database Instance

• Objectives
• Starting the Oracle Database Instance
• Shutting Down an Oracle Database Instance
• Comparing SHUTDOWN Modes
• Opening and Closing PDBs
• Configuring PDBs to Automatically Open
• Summary
• Practice Overview

Managing Database Instances

• Objectives
• Working with Initialization Parameters
• Initialization Parameters
• Modifying Initialization Parameters
• Viewing Initialization Parameters
• Working with the Automatic Diagnostic Repository
• Automatic Diagnostic Repository
• Viewing the Alert Log
• Using Trace Files
• Administering the DDL Log File
• Querying Dynamic Performance Views
• Considerations for Dynamic Performance Views
• Data Dictionary: Overview
• Querying the Oracle Data Dictionary
• Summary
• Practice Overview

Oracle Net Services: Overview

• Objectives
• Connecting to the Database Instance
• Oracle Net Services: Overview
• Defining Oracle Net Services Components
• Tools for Configuring and Managing Oracle Net Services
• Oracle Net Listener: Overview
• The Default Listener
• Comparing Dedicated and Shared Server Architecture
• Summary

Configuring Naming Methods

• Objectives
• Establishing Oracle Network Connections
• Connecting to an Oracle Database Instance
• Name Resolution
• Establishing a Connection
• User Sessions
• Naming Methods
• Easy Connect
• Local Naming
• Directory Naming
• Using Database Services to Manage Workloads
• Creating Database Services
• Summary
• Practice Overview

Configuring and Administering the Listener

• Objectives
• Review: Oracle Net Services Overview
• Oracle Net Listener: Overview
• The Default Listener
• Configuring Dynamic Service Registration
• Configuring Static Service Registration
• Summary
• Practice Overview

Configuring a Shared Server Architecture

• Objectives
• Shared Server Architecture: Overview
• Comparing Dedicated and Shared Server Architecture: Review
• Enabling Shared Server
• Controlling Shared Server Operations
• SGA and PGA Usage
• Shared Server Configuration Considerations
• Summary
• Practice Overview

Configuring Oracle Connection Manager for Multiplexing and Access Control

• Objectives
• Oracle Connection Manager: Overview
• Oracle Connection Manager Processes
• Oracle Connection Manager: Architecture
• Using Filtering Rules
• Implementing Intranet Access Control
• Implementing Internet Access Control
• Using Session Multiplexing
• Configuring Oracle Connection Manager
• Configuring the cman.ora File
• Example of a cman.ora File
• Configuring Clients
• Configuring the Database Server
• Configuring the Database Server for Multiplexing (Optional)
• Using the Oracle Connection Manager Control Utility
• Review of Oracle Connection Manager Features
• Summary

Creating PDBs from Seed

• Objectives
• Provisioning New Pluggable Databases
• Tools
• Creating a New PDB from PDB$SEED
• Using the FILE_NAME_CONVERT Clause
• Using OMF or the PDB_FILE_NAME_CONVERT Parameter
• Summary
• Practice Overview

Using Other Techniques to Create PDBs

• Objectives
• Cloning Regular PDBs
• Migrating Data from a Non-CDB into a CDB
• Plugging a Non-CDB into CDB Using DBMS_PDB
• Replicating a Non-CDB into a CDB by Using GoldenGate
• Cloning a Non-CDB or Remote PDB
• Using DBCA to Clone a Remote PDB
• Plugging an Unplugged Regular PDB into CDB
• Plugging in a PDB Using an Archive File
• Cloning Remote PDBs in Hot Mode
• Near-Zero Downtime PDB Relocation
• Using DBCA to Relocate a Remote PDB
• Proxy PDB: Query Across CDBs Proxying Root Replica
• Creating a Proxy PDB
• Summary

Managing PDBs

• Objectives
• Changing the PDB Mode
• Modifying PDB Settings
• Impact of Changing Initialization Parameters
• Changing Initialization Parameters: Example
• Using the ALTER SYSTEM Command in a PDB
• Configuring Host Name and Port Number per PDB
• Dropping PDBs
• Summary
• Practice Overview

Database Storage Overview

• Objectives
• Database Storage Architecture
• Logical and Physical Database Structures
• Segments, Extents, and Blocks
• Tablespaces and Data Files
• Default Tablespaces in a Multitenant Container Database
• SYSTEM and SYSAUX Tablespaces
• Types of Segments
• How Table Data Is Stored
• Database Block Content
• Understanding Deferred Segment Creation
• Controlling Deferred Segment Creation
• Monitoring Tablespace Space Usage
• Oracle Database Storage Structures: Interactive Architecture Diagram
• Summary

Creating and Managing Tablespaces

• Objectives
• Creating Tablespaces
• Creating a Tablespace: Clauses
• Creating Permanent Tablespaces in a CDB
• Defining Default Permanent Tablespaces
• Temporary Tablespaces
• Altering and Dropping Tablespaces
• Viewing Tablespace Information
• Implementing Oracle Managed Files (OMF)
• Enlarging the Database
• Moving or Renaming Online Data Files
• Examples: Moving and Renaming Online Data Files
• Summary
• Practice Overview

Improving Space Usage

• Objectives
• Space Management Features
• Block Space Management
• Row Chaining and Migration
• Free Space Management Within Segments
• Allocating Extents
• Using Unusable Indexes
• Using Temporary Tables
• Creating Global Temporary Tables
• Creating Private Temporary Tables
• Table Compression: Overview
• Table Compression: Concepts
• Compression for Direct-Path Insert Operations
• Advanced Row Compression for DML Operations
• Specifying Table Compression
• Using the Compression Advisor
• Resolving Space Usage Issues
• Reclaiming Space by Shrinking Segments
• Shrinking Segments
• Results of a Shrink Operation
• Managing Resumable Space Allocation
• Using Resumable Space Allocation
• Resuming Suspended Statements
• What Operations Are Resumable?
• Summary
• Practice Overview

Managing Undo Data

• Objectives
• Undo Data: Overview
• Transactions and Undo Data
• Storing Undo Information
• Comparing Undo Data and Redo Data
• Managing Undo
• Comparing SHARED Undo Mode and LOCAL Undo Mode
• Configuring Undo Retention
• Categories of Undo
• Guaranteeing Undo Retention
• Changing an Undo Tablespace to a Fixed Size
• Temporary Undo: Overview
• Temporary Undo Benefits
• Enabling Temporary Undo
• Monitoring Temporary Undo
• Summary
• Practice Overview

Creating and Managing User Accounts

• Objectives
• Database User Accounts
• Oracle-Supplied Administrator Accounts
• Creating Oracle Database Users in a Multitenant Environment
• Creating Common Users in the CDB and PDBs
• Creating Schema Only Accounts
• Authenticating Users
• Using Password Authentication
• Using Password File Authentication
• Using OS Authentication
• OS Authentication for Privileged Users
• Assigning Quotas
• Summary
• Practice Overview

Configuring Privilege and Role Authorization

• Objectives
• Privileges
• System Privileges
• System Privileges for Administrators
• Object Privileges
• Granting Privileges in a Multitenant Environment
• Granting Privileges: Example
• Using Roles to Manage Privileges
• Assigning Privileges to Roles and Assigning Roles to Users
• Oracle-Supplied Roles
• Granting Roles in a Multitenant Environment
• Granting Roles: Example
• Making Roles More Secure
• Revoking Roles and Privileges
• Granting and Revoking System Privileges
• Granting and Revoking Object Privileges
• Summary
• Practice Overview

Configuring User Resource Limits

• Objectives
• Profiles and Users
• Creating Profiles in a Multitenant Architecture
• Creating Profiles: Example
• Profile Parameters: Resources
• Profile Parameters: Locking and Passwords
• Oracle-Supplied Password Verification Functions
• Assigning Profiles in a Multitenant Architecture
• Summary
• Practice Overview

Implementing Oracle Database Auditing

• Objectives
• Database Security
• Monitoring for Compliance
• Types of Activities to be Audited
• Mandatorily Audited Activities
• Understanding Auditing Implementation
• Administering the Roles Required for Auditing
• Database Auditing: Overview
• Configuring Auditing
• Creating a Unified Audit Policy
• Creating an Audit Policy: System-Wide Audit Options
• Creating an Audit Policy: Object-Specific Actions
• Creating an Audit Policy: Specifying Conditions
• Enabling and Disabling Audit Policies
• Auditing Actions in the CDB and PDBs
• Modifying a Unified Audit Policy
• Auditing Top-Level Statements Only
• Viewing Audit Policy Information
• Value-Based Auditing
• Fine-Grained Auditing
• FGA Policy
• Audited DML Statement: Considerations
• FGA Guidelines
• Archiving and Purging the Audit Trail
• Purging Audit Trail Records
• Summary
• Practice Overview

Introduction to Loading and Transporting Data

• Objectives
• Moving Data: General Architecture
• Oracle Data Pump: Overview
• Oracle Data Pump: Benefits
• SQL Loader: Overview
• Summary

Loading Data

• Objectives
• SQL Loader: Review
• Creating the SQL*Loader Control File
• SQL*Loader Loading Methods
• Protecting Against Data Loss
• SQL*Loader Express Mode
• Using SQL*Loader to Load a Table in a PDB
• Summary
• Practice Overview

Transporting Data

• Objectives
• Data Pump Export and Import Clients
• Data Pump Interfaces and Modes
• Data Pump Import Transformations
• Using Oracle Data Pump with PDBs
• Exporting from a Non-CDB and Importing into a PDB
• Exporting and Importing Between PDBs
• Full Transportable Export/Import
• Full Transportable Export/Import: Example
• Transporting a Database Over the Network: Example
• Using RMAN to Transport Data Across Platforms
• RMAN CONVERT Command
• Transporting Data with Minimum Down Time
• Transporting a Tablespace by Using Image Copies
• Determining the Endian Format of a Platform
• Transporting Data with Backup Sets
• Transporting a Tablespace
• Transporting Inconsistent Tablespaces
• Database Transport: Data Files
• Transporting a Database
• Transporting a Database: Conversion
• Transporting a Database: Example 1
• Transporting a Database: Example 2
• Transporting a Database: Considerations
• Transporting a Database with Backup Sets
• Summary
• Practice Overview

Using External Tables to Load and Transport Data

• Objectives
• External Tables
• External Tables: Benefits
• ORACLE_LOADER Access Driver
• ORACLE_DATAPUMP Access Driver
• External Tables
• Viewing Information About External Tables
• Summary
• Practice Overview

Automated Maintenance Tasks: Overview

• Objectives
• Proactive Database Maintenance Infrastructure
• Automated Maintenance Tasks: Components
• Predefined Automated Maintenance Tasks
• Maintenance Windows
• Predefined Maintenance Windows
• Viewing Maintenance Window Details
• Automated Maintenance Tasks
• Summary

Automated Maintenance Tasks: Managing Tasks and Windows

• Objectives
• Configuring Automated Maintenance Tasks
• Enabling and Disabling Maintenance Tasks
• Creating and Managing Maintenance Windows
• Resource Allocations for Automated Maintenance Tasks
• Changing Resource Allocations for Maintenance Tasks
• Summary
• Practice Overview

Database Monitoring and Tuning Performance Overview

• Objectives
• Performance Management Activities
• Performance Planning Considerations
• Database Maintenance
• Automatic Workload Repository (AWR)
• Automatic Database Diagnostic Monitor (ADDM)
• Configuring Automatic ADDM Analysis at the PDB Level
• Advisory Framework
• Performance Tuning Methodology
• Summary

Monitoring Database Performance

• Objectives
• Server-Generated Alerts
• Setting Metric Thresholds
• Reacting to Alerts
• Alert Types and Clearing Alerts
• Database Server Statistics and Metrics
• Performance Monitoring
• Viewing Statistics Information
• Monitoring Wait Events
• Monitoring Sessions
• Monitoring Services
• Summary

Analyzing SQL and Optimizing Access Paths

• Objectives
• SQL Tuning Process
• Oracle Optimizer
• Optimizer Statistics
• Optimizer Statistics Collection
• Setting Optimizer Statistics Preferences
• Optimizer Statistics Advisor
• Optimizer Statistics Advisor Report
• Executing Optimizer Statistics Advisor Tasks
• SQL Plan Directives
• Adaptive Execution Plans
• SQL Tuning Advisor: Overview
• SQL Access Advisor: Overview
• SQL Performance Analyzer: Overview
• Managing Automated Tuning Tasks
• Summary