Oracle 12c: Database Administration I

COURSE OUTLINE:

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

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

Prerequisites

  • Knowledge of SQL

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

1. Introduction and Duties of the DBA

  • Oracle RDBMS Components
  • DBA Responsibilities

2. Tables, Constraints, and Indexes

  • Introduction to Datatypes and Tables
  • Integrity Constraints
  • Temporary Tables, Partitioned Tables, External Tables, and IOTs
  • Indexes and Skip Scanning
  • Online Reorganization
  • Database Workspace Management

3. Other Database Schema Objects

  • 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)

4. Starting and Stopping the Database

  • 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

5. How Space is Physically Consumed - Blocks and Extents

  • Basic Definitions
  • Blocks and Extents
  • Fragmentation / Deallocation

6. How Space is Logically Consumed - Segments

  • Introduction to Segments
  • Managing Undo Segments
  • Automatic Undo Management
  • Managing Sort Activity
  • Managing Data Segments
  • Managing Index Segments
  • Monitoring Database segments

7. Managing Tablespaces - Default behavior of CREATE TABLESPACE command

  • Tablespace Overview
  • System Tablespace
  • Types of Tablespaces
  • Creating additional tablespaces
  • Altering Tablespaces
  • Temporary Tablespaces - Default Temporary Tablespaces
  • Tablespace Quotas
  • Monitoring Tablespaces

8. Basic Oracle Architecture

  • Architecture Overview
  • Database Structures
  • Process Structures
  • Memory Structures - SGA
  • Memory Structures - PGA
  • Alternative Configurations

9. Oracle Database Files

  • Basic Structures of an Oracle RDBMS
  • Data Files
  • Redo Log Files
  • Archived Redo Log Files
  • Control Files
  • Oracle-Managed Files (OMF)

10. Users and Security

  • Managing Database Users
  • System / Object Privileges
  • Managing Roles / Profiles
  • Resource and Password Management
  • The Virtual Private Database

11. Networking

  • 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

12. Physical NOARCHIVELOG Mode Backups

  • Oracle Database Backups
  • Automatic Recovery Architecture
  • NOARCHIVELOG Mode
  • ARCHIVELOG Mode
  • Physical Backups
  • Closed Backups in NOARCHIVELOG Mode

13. Configuring Archivelog Mode

14. Physical ARCHIVELOG Mode Backups

  • 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

15. Complete Recovery

  • Recovery Structures and Processes
  • Recovery in NOARCHIVELOG and ARCHIVELOG Mode

16. Incomplete Recovery

  • What is Incomplete Recovery?
  • Recovering from Loss of Current Redo Log Files

17. Data Loading

  • SQL*Loader
  • External Tables Data Pump