Oracle Database 19c: Administration Workshop

At the end of this course, the student should be able to:

• Describe the architecture of the Oracle database
• Describe the architecture and features of Oracle Database Cloud Service (DBCS)
• Create and manage DBCS database deployments
• Configure the database to support your applications
• Manage database security and implement auditing
• Implement basic backup and recovery procedures.
• Move data between databases and files
• Employ basic monitoring procedures and manage performance

Audience profile

• Data Warehouse Administrator
• Database administrator
• Database Designer
• Support Engineer
• technical administrator

Prerequisites

• Oracle Database 19c: SQL Workshop

Intensity and Schedules

• Live virtual training.
• With an intensity of 40 hours, the training is given 3 times a week from 6:30 pm to 9:30 pm Colombia time

Module 1: Introduction to the Oracle Database

• Objectives
• Oracle Database Server Architecture – Overview
• Oracle Multitenant Container Database: Introduction
• Oracle Multitenant Container Database: Architecture
• Oracle Database Instance Configurations
• Database Fragmentation: Introduction
• Oracle Database Server – Interactive Architecture Diagram

 Module 2: Access to an Oracle Database

• Objectives
• Connect to an Oracle Database Instance
• Oracle Database Tools
• Database tools options
• SQL*Plus
• Oracle SQL Developer
• Oracle SQL Developer: Connections
• Oracle SQL Developer: DBA Actions
• Database Configuration Assistant (DBCA)
• Oracle Enterprise Manager Express Database
• Enterprise Manager Cloud Control 13c Features
• Oracle Enterprise Manager Component Overview
• Single dashboard for business management
• Oracle Enterprise Manager Database Management

Module 3: Creating an Oracle Database Using DBCA

• Objectives
• Database Planning
• Choose a database template
• Choose the appropriate character set
• How are character sets used?
• Set NLS_LANG correctly on the client
• Using the Database Configuration Wizard
• Using DBCA in silent mode
• Practice Overview

Module 4: Creating an Oracle Database Using an SQL Command

• Objectives
• Creating a container database (CDB)
• Create a CDB using an SQL command: example
• Using the SEED FILE_NAME_CONVERT clause
• Using the ENABLE PLUGGABLE DATABASE clause
• Practice Overview

Module 5: Starting and Shutting Down a DB Instance

• Objectives
• Starting the Oracle Database Instance
• Shutting Down an Oracle Database Instance
• Comparison of OFF modes
• PDB opening and closing
• Setting PDBs to open automatically
• Practice Overview

Module 6: Managing Database Instances

• Objectives
• Work with initialization parameters
• Initialization Parameters
• Modifying the initialization parameters
• Display of initialization parameters
• Work with the self-diagnostic repository
• Self Diagnostic Repository
• Viewing the alert log
• Use of trace files
• DDL log file management
• Dynamic Performance View Query
• Considerations for dynamic performance views
• Data Dictionary: Overview
• Query the Oracle data dictionary
• Practice Overview

 Module 7: Oracle Net Services – Overview

• Objectives
• Connect to the database instance
• Oracle Network Services – Overview
• Defining Oracle Net Services Components
• Tools to configure and administer Oracle Net Services
• Oracle Net Listener – Overview
• The default listener
• Dedicated and Shared Server Architecture Comparison

Module 8: Configuring Naming Methods

• Objectives
• Establishing Oracle Network Connections
• Connect to an Oracle Database Instance
• name resolution
• establish a connection
• User sessions
• Naming methods
• easy connection
• local denomination
• directory name
• Using database services to manage workloads
• Create database services
• Practice Overview

Module 9: Listener Configuration and Management

• Objectives
• Review: Oracle Net Services Overview
• Oracle Net Listener – Overview
• The default listener
• Dynamic Service Registry Configuration
• Static Service Registry Configuration
• Practice Overview

Module 10: Configuring a Shared Server Architecture

• Objectives
• Shared Server Architecture – Overview
• Dedicated and Shared Server Architecture Comparison: Review
• Enabling Shared Server
• Control of shared server operations
• Use of SGA and PGA
• Shared Server Configuration Considerations
• Practice Overview

 Module 11: Configuring Oracle Connection Manager for Multiplexing and Access Control

• Objectives
• Oracle Connection Manager – Overview
• Oracle connection manager processes
• Oracle Connection Manager: Architecture
• Use of filter rules
• Implementation of intranet access control
• Implementation of Internet access control
• Use of session multiplexing
• Oracle Connection Manager Configuration
• Configuring the cman.ora file
• Example of a cman.ora file
• Client configuration
• Database server configuration
• Configuring the database server for multiplexing (optional)
• Using the Oracle Connection Manager Control Utility
• Oracle Connection Manager Features Review
• Practice Overview

Module 12: Creating PDBs from Seeds

• 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
• Practice Overview

Module 13: Using Other Techniques to Create PDBs

• Objectives
• Cloning regular PDBs 3 Migrating data from a non-CDB to a CDB
• Connect a non-CDB to CDB using DBMS_PDB
• Replicate a non-CBD into a CBD using GoldenGate
• Cloning a remote or non-CDB PDB
• Using DBCA to clone a remote PDB
• Connect a regular offline PDB in CDB
• Connect a PDB using a storage file
• Cloning remote PDBs in active mode
• Near-zero downtime PDB relocation
• Using DBCA to relocate a remote PDB
• PDB Proxy: Query through CDB Proxy Root Replica
• Create a proxy PDB
• Practice Overview

Module 14: PDB Management

• Objectives
• Changing the PDB mode
• Modifying the PDB configuration
• Impact of changing initialization parameters
• Changing initialization parameters: example
• Using the ALTER SYSTEM command in a PDB
• Hostname and port number configuration per PDB
• Discard PDB
• Practice Overview

 Module 15 – Database Storage Overview

• Objectives
• Database storage architecture
• Logical and physical database structures
• Segments, extensions and blocks
• Tablespaces and data files
• Default tablespaces in a multi-user container database
• SYSTEM and SYSAUX table spaces
• segment types
• How table data is stored
• Database block content
• Description of deferred segment creation
• Control of deferred segment creation
• Table space usage monitoring

Module 16: Creating and Managing Tablespaces

• Objectives
• Creating table spaces
• Create a Tablespace: Clauses
• Creating permanent table spaces in a CDB
• Defining default permanent table spaces
• temporary tablespaces
• Altering and Deleting Tablespaces
• Viewing table space information
• Deploying Oracle Managed Files (OMF)
• Database expansion
• Move or rename data files online
• Examples: moving and renaming data files online
• Practice Overview

Module 17: Improving the use of space

• Objectives
• Space management features
• Block space management
• Row chaining and migration
• Free space management within segments
• Assignment of extensions
• Use of 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
• Table Compression Specification
• Using Compression Advisor
• Troubleshooting space usage
• Space reclamation by shrinking segments
• shrinking segments
• Results of a reduce operation
• Resumable Space Allocation Management
• Using Resumable Space Allocation
• Resumption of suspended extracts
• What operations are resumable?
• Practice Overview

 Module 18: Undo Data Management

• Objectives
• Undo Data – Overview
• Transactions and undo data
• Undo Information Storage
• Comparison of Undo Data and Redo Data
• Manage Undo
• Comparison of SHARED Undo Mode and LOCAL Undo Mode
• Undo Hold Settings
• Undo Categories
• Ensure Undo Retention
• Change an Undo Tablespace to a Fixed Size
• Temporary Undo: Overview
• Benefits of Undo temporarily
• Enable Temporary Undo
• Temporary Undo Monitoring
• Practice Overview

Module 19: Creating and Managing User Accounts

• Objectives
• Database user accounts
• Oracle-provided administrator accounts
• Creating Oracle Database Users in a Multitenant Environment
• Creating common users in CDB and PDB
• Create schema-only accounts
• User authentication
• Using password authentication
• Using password file authentication
• Using operating system authentication
• Operating system authentication for privileged users
• Quota Allocation
• Practice Overview

 Module 20: Configuring Feature and Privilege Authorization

• Objectives
• Privileges
• system privileges
• System privileges for administrators
• object privileges
• Granting Privileges in a Multitenant Environment
• Privilege Grant: Example
• Using roles to manage privileges
• Assigning privileges to roles and assigning roles to users
• Oracle-provided functions
• Feature Grant in a Multitenant Environment
• Feature Grant: Example
• Make roles more secure
• Revoking Roles and Privileges
• Granting and revoking system privileges
• Granting and revoking object privileges
• Practice Overview

Module 21: Configuring User Resource Limits

• Objectives
• Profiles and Users
• Profiling in a multi-tenant architecture
• Profiling: example
• Profile Parameters: Resources
• Profile parameters: lock and passwords
• Password verification functions provided by Oracle
• Assigning profiles in a multi-tenant architecture
• Practice Summary

Module 22: Implementing Oracle Database Auditing

• Objectives
• Database Security
• Monitoring for Compliance
• Types of activities to audit
• Mandatorily Audited Activities
• Understanding the implementation of the audit
• Viewing audit policy information
• Values-Based Audit
• detailed audit
• FGA Policy
• Audited DML Statement: Considerations
• FGA Guidelines
• Archive and purge the audit log
• Purge Audit Trail Logs
• Practice Overview

 Module 23: Introduction to Charging and Transporting Data

• Objectives
• Move data: general architecture
• Oracle Data Pump – Overview
• Oracle Data Pump: Benefits
• SQLLoader – Overview

Module 24: Data Upload

• Objectives
• SQL Loader: review
• Create the SQL*Loader Control File
• SQL*Loader Load Methods
• Protection against data loss
• SQL*Loader Express mode
• Using SQL*Loader to load a table into a PDB
• Practice Overview

 Module 25: Data Transport

• Objectives
• Data Pump Export Export and Import Clients
• Data Pump Export Modes and Interfaces
• Data Pump Export Import Transformations
• Using Oracle Data Pump with PDBs
• Export from a non-CDB and import to a PDB
• Export and import between PDBs
• Fully transportable Export/Import
• Full Transportable Export/Import: Example
• Transporting a database over the network: example
• Using RMAN to transport data between platforms
• RMAN CONVERT command
• Data transport with minimal downtime
• Transporting a Tablespace Using Image Copies
• Determining the Endian format of a platform
• Data transport with Backup Sets
• Transporting a Tablespace
• Inconsistent Tablespace Transport
• Database Transport: Data Files
• Transport of a database
• Transporting a database: conversion
• Transporting a database: example
• Transporting a database: considerations
• Transporting a database with backup sets
• Practice Overview

 Module 26: Using External Tables to Load and Transport Data

• Objectives
• External Tables
• External Tables: Benefits
• ORACLE_LOADER Access Driver
• ORACLE_DATAPUMP Access Driver
• Viewing information about External Tables
• Practice Overview

Module 27: Automated Maintenance Tasks – Overview

• Objectives
• Proactive database maintenance infrastructure
• Automated Maintenance Tasks – Components
• Predefined automated maintenance task
• maintenance windows
• Predefined maintenance windows
• Viewing maintenance window details
• Automated maintenance tasks

Module 28: 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
• consultative framework
• Performance Tuning Methodology

Module 29 – Monitoring Database Performance

• Objectives
• Server generated alerts
• Setting Metric Thresholds
• reaction to alerts
• Types of alerts and clearing alerts
• Database server statistics and metrics
• performance monitoring
• Viewing statistics information
• Wait event monitoring
• Follow-up sessions
• Monitoring Services
• Practice Overview

 Module 30: Database Processes

• Objectives
• Process architecture
• process structures
• Database writer process (DBWn and BWnn)
• Register writing process (LGWR and LGnn)
• Checkpoint Process (CKPT)
• System Monitoring Process (SMON)
• Process Monitoring Process (PMON)
• Process Manager (PMAN)
• Recovery Process (RECO)
• Listener Registration Process (LREG)
• Manageability Monitoring Process (MMON)
• Archiver Processes (ARCn)
• Interaction with an Oracle database: memory, processes and storage
• Practice Overview

Module 31: Memory Management

• Objectives
• Memory Component Management
• Shared pool
• Database buffer cache
• Redo Log Buffer
• Big pool
• java group
• stream pool
• Program Global Area (PGA)
• Memory Component Management
• Efficient Memory Usage: Guidelines
• automatic memory management
• Automatic memory management monitoring
• Automatic shared memory management
• Description of automatic shared memory management
• Oracle database memory parameters
• Management of the EMS for PDB
• Program Global Area (PGA) Management
• Manage the PGA for PDB
• Practice Overview

Module 32: SQL Analysis and Path Optimization

• Objectives
• SQL tuning process
• Oracle Optimizer
• Optimizer Statistics
• Collecting Optimizer Statistics
• Optimizer Statistics Preference Settings
• Optimizer Statistics Advisor
• Optimizer Stats Advisor Report
• Running Optimizer Statistics Advisor Tasks
• SQL Plan Policies
• Adaptive Execution Plans
• SQL Optimization Advisor – Overview
• SQL Access Advisor – Overview
• SQL Performance Analyzer – Overview
• Management of automated tuning tasks
• Practice Overview