Oracle 12c Database Administration I

Course Description

In this course, you will learn about the Oracle Server architecture, expand the size of the database, implement security, and how to perform specific database administration tasks.

Audience Profile

• Data analysts
• Database administrators
• Systems administrators
• Network managers
• Technical support personnel

Prerequisites

• Knowledge of SQL
• Oracle 12c SQL & SQL PLUS Express Introduction
• Oracle 12c PL/SQL Introduction

Learning Objectives

• Oracle Server Architecture
• Install Oracle Server Software
• Start and Stop an Oracle Database
• Manage Tablespaces and Undo Segments
• Create an Oracle Database
• Create Tables, Constraints, and Indexes
• Create All Other Database Objects
• Manage Redo Logs
• Manage Tablespaces and Datafiles
• Manage Controlfiles
• Manage Security
• Audit the Database
• Manage Oracle Networking
• Perform Appropriate Backups
• Perform Complete and Incomplete Recoveries

Course Outline

Module 1: Introduction and Duties of the DBA
Lessons
• Oracle RDBMS Components
• DBA Responsibilities
Lab: Architecture

Module 2: Tables, Constraints, and Indexes
Lessons
• Introduction to Datatypes and Tables
• Integrity Constraints
• Temporary Tables, Partitioned Tables, External Tables, and IOTs
• Indexes and Skip Scanning
• Online Reorganization
• Database Workspace Management
Lab: Database Startup and Shutdown

Module 3: Other Database Schema Objects
Lessons
• DDL Commands used
• Views, Materialized views
• Sequences, Synonyms, Database Links
• Outlines, Dimensions, Hierarchies
• PL/SQL Program Units
• Supplied Packages
• Oracle Data Dictionary
• Storage of Large Objects (BFILEs, BLOBs, CLOBs)
Lab: Using and Managing SPFILES and Parameters

Module 4: Starting and Stopping the Database
Lessons
• What Tool to Use?
• Connecting as a Privileged User
• Password file authentication
• Starting a Database
• Shutting Down a Database
• Reading the Alert Log / Trace Files
• Quiescing the database
• SPFILE / Parameter management
Lab: Creating Tables, Indexes, and Constraints

Module 5: How Space is Physically Consumed – Blocks and Extents
Lessons
• Basic Definitions
• Blocks and Extents
• Fragmentation / Deallocation
Lab: Creating Other Database Schema Objects

Module 6: How Space is Logically Consumed – Segments
Lessons
• Introduction to Segments
• Managing Undo Segments
• Automatic Undo Management
• Managing Sort Activity
• Managing Data Segments
• Managing Index Segments
• Monitoring Database segments
Lab: Users and Security

Module 7: Managing Tablespaces – Default behavior of CREATE TABLESPACE command
Lessons
• Tablespace Overview
• System Tablespace
• Types of Tablespaces
• Creating additional tablespaces
• Altering Tablespaces
• Temporary Tablespaces – Default Temporary Tablespaces
• Tablespace Quotas
• Monitoring Tablespaces
Lab: Undo Segments

Module 8: Basic Oracle Architecture
Lessons
• Architecture Overview
• Database Structures
• Process Structures
• Memory Structures – SGA
• Memory Structures – PGA
• Alternative Configurations
Lab: Segments

Module 9: Oracle Database Files
Lessons
• Basic Structures of an Oracle RDBMS
• Data Files
• Redo Log Files
• Archived Redo Log Files
• Control Files
• Oracle-Managed Files (OMF)
Lab: Managing Tablespaces

Module 10: Users and Security
Lessons
• Managing Database Users
• System / Object Privileges
• Managing Roles / Profiles
• Resource and Password Management
• The Virtual Private Database
Lab: Multiple Block Size Management

Module 11: Networking
Lessons
• Duties of the DBA for Networking
• Overview of Oracle Network Architecture
• Name Resolution
• Oracle Net Client Configuration
• Oracle Net Server Configuration
• Tools
• Configuring the Listener
• Shared Server
Lab: Advanced Memory Management

Module 12: Physical NOARCHIVELOG Mode Backups
Lessons
• Oracle Database Backups
• Automatic Recovery Architecture
• NOARCHIVELOG Mode
• ARCHIVELOG Mode
• Physical Backups
• Closed Backups in NOARCHIVELOG Mode
Lab: Oracle Database Files

Module 13: Configuring Archivelog Mode
Lessons
Lab: Data Loading

Module 14: Physical ARCHIVELOG Mode Backups
Lessons
• Configuration of the database for archiving
• Setting the ARCHIVELOG mode
• Establishing multiple archiving destinations
• Closed backups in ARCHIVELOG mode
• Open backups in ARCHIVELOG mode
• Controlfile Backup
Lab: Backup and Archivelog Mode

Module 15: Complete Recovery
Lessons
• Recovery Structures and Processes
• Recovery in NOARCHIVELOG and ARCHIVELOG Mode
Lab: Complete Recovery

Module 16: Incomplete Recovery
Lessons
• What is Incomplete Recovery?
• Recovering from Loss of Current Redo Log Files
Lab: Incomplete Recovery

Module 17: Data Loading
Lessons
• SQL*Loader
• External Tables Data Pump