Oracle 11g Database Administration Training

This Oracle Database Administration Training will develop the key fundamental skills necessary to be an Oracle DBA. Students will design and create a server using the Optimal Flexible Architecture (OFA), configure logical and physical structures, set up database and user security, add and administer users, and monitor and tune main server areas. Each student will create a toolkit of administration scripts for database management and tuning by the end of this class.

The course is inteded for non-Oracle database administrators, application developers, and system administrators.

Goals

Learn to administer Oracle databases.

Outline

Overview of Oracle Database

ORACLE_HOME and ORACLE_SID

The Oracle Database vs. The Oracle Instance

Instance Memory Structures

Background Processes

Server Processes

Datafiles

Blocks, Extents, and Segments

Control Files

Redo Logs

The Oracle Architecture

SYS and SYSTEM Users

Starting and Shutting Down an Oracle Database

SYSDBA and SYSOPER System Privileges

Oracle Database Startup

Oracle Database Shutdown

Altering Database Availability

Suspending and Resuming a Database

Quiescing a Database

Tracking Database Activity - The Alert Log

Data Dictionary Views for Startup and Shutdown

Using The Oracle Data Dictionary

Introducing the Data Dictionary

DBA, ALL, and USER Data Dictionary Views

V$ Dynamic Performance Views

Using Oracle Documentation to Locate Data Dictionary Views

Combining Data Dictionary Views

Oracle Database Users and Schemas

Users and Schemas

Oracle Default Database Users

Oracle Sample Schemas

Creating Users

Altering and Dropping Users

Data Dictionary Views For Users

Oracle System Privileges

Overview of System Privileges

Granting and Revoking System Privileges

Data Dictionary Views for System Privileges

Roles

Creating and Removing Roles

Data Dictionary Views for Roles

Predefined Roles

User Group PUBLIC

Parameter Files

Oracle Database Parameters

The Parameter File (PFILE) and the Server Parameter File (SPFILE)

Dynamic vs. Static Parameters

Determining Current Parameter Settings

Benefits of SPFILEs over PFILEs

Creating an SPFILE from a PFILE and Back Again

Understanding Scope

Session-Level Parameters

Data Dictionary Views for Parameters

Datafiles and Tablespaces

Datafiles Overview

Tablespaces Overview

SYSTEM and SYSAUX Tablespaces

Creating Tablespaces

Dictionary- and Locally Managed Tablespaces

Locally Managed Tablespace Extent Allocation

Temporary Tablespaces

Temporary Tablespace Groups

Default Tablespaces

Tablespace Quotas

Dropping and Altering a Tablespace

Renaming a Tablespace

Renaming or Relocating Datafiles

Bigfile Tablespaces

Data Dictionary Views for Datafiles and Tablespaces

Control Files

Control Files Overview

Database Parameters for Control Files

Backing Up Control Files

Restoring Control Files from Multiplexed Copies

Restoring Control Files from Backups

Moving Control Files

Data Dictionary Views for Control Files

Oracle Physical Structures - Online Redo Logs

Redo

Redo Log Files

Database Parameters for Redo

Sizing the Redo Log Files

How Many Redo Log Groups?

Creating Redo Logs

Removing Redo Logs

Renaming Redo Log Files

Forcing Log Switches

Archiving Redo Logs

Configuring Archive Log Mode

Database Parameters for Archiving

Data Dictionary Views for Redo Logs

Oracle Physical Structures - Undo Segments

Undo Overview

Database Parameters for Undo

Creating Undo Tablespaces

Altering and Dropping Undo Tablespaces

Switching Undo Tablespaces

Undo Advisor

Data Dictionary Views for Undo

Segment Space Management

Blocks, Extents and Segments

Segment Space Management

Fragmentation

Coalescing Fragmented Space

Row Migration and Chaining

Manual Segment Space Usage

Automatic Segment Space Management

Data Dictionary Views for Physical Objects

Tables

Tables Overview

Physical Properties of Tables

Creating Tables

Table Storage and Logging

Altering Tables

Dropping Tables

The Recycle Bin

The TRUNCATE COMMAND

Temporary Tables

Clustered Tables

Indexed-Organized Tables

Creating an IOT

Data Dictionary Views for Tables

Indexes

Indexes

B-tree Indexes

NULL Values and Indexes

NULL Values and Unique Indexes

Creating Indexes

Monitoring Index Usage

Dropping Indexes

Rebuilding and Moving Indexes

Coalescing Indexes

Bitmap Indexes

Special Index Types

Index Key Compression

Data Dictionary Views for Indexes

Constraints

Integrity Constraints

Constraint Names and Syntax

Constraint Checking

Managing Primary Key Constraints

Managing NOT NULL Constraints

Managing Check Constraints

Managing Foreign Key Constraints

Data Dictionary Views for Constraints

Views

Views

Creating and Replacing Views

Data Dictionary Views for Views

Security Through Views

Altering and Dropping Views

Dependencies and Views

Updating Data Through Views

Object Privileges

Object Privileges

Granting and Revoking Object Privileges

Using WITH GRANT OPTION

Revoking and the GRANT OPTION

Data Dictionary Views for Object Privileges

Synonyms

Synonyms

Private and Public Synonyms

Creating and Dropping Synonyms

Security and Synonyms

Object Precedence

The Optimizer and Statistics

Optimizer Overview

Optimizer Statistics

Data Dictionary Views and Statistics

Collecting Statistics

Removing Statistics

Automated Statistics Collection

Oracle Net Services

Oracle Networking Explained

The Net Configuration Assistant

Configuring the Listener - GUI

Configuring the Listener - Manually

Configuring the Client - GUI

Configuring the Client - Manually

Data Pump

Oracle Data Pump Architecture

Configuring for Data Pump

Command-Line Utilities

Exporting With expdp

Importing With impdp

Interactive Mode

Attaching to a Running Job

Parallel Data Pump

Data Dictionary Views for Data Pump

SQL*Loader and External Tables

SQL*Loader

SQL*Loader Control File

Loading Data with SQL*Loader

Conventional vs. Direct Path Loads

External Tables Overview

Creating an External Table

Creating a Writable External Table

Back Up Your Database

Physical Backups vs. Logical Backups

Offline Backup Basics

Oracle Offline Backup Steps

Other Backup Considerations

Advanced Backup Concepts

RMAN

Recover Your Database

Offline Recovery Basics

Restoration Using Offline Backups for a NOARCHIVE Database

Restoring Other Files

Other Recovery Considerations

Oracle Enterprise Manager

Introducing Oracle Enterprise Manager

OEM Architecture and Physical Structure

Installing OEM When Using DBCA

Using EMCA to Install OEM

Managing OEM

Removing OEM

Troubleshooting OEM

Securing OEM

Starting OEM

Introducing the OEM Home Pages

Using OEM - Managing the Job Scheduler

Using OEM - Using Metrics, Alerts, and Thresholds

Automatic Workload Repository

The Automatic Database Diagnostic Monitor - ADDM

OEM and Advisors

The SQL Access Advisor

Appendix A - Installing Oracle Software

Optimal Flexible Architecture

OFA Directory Layouts

The Oracle Universal Installer

OUI Installation Modes

Preparing To Install Software

Starter Database

Installing the Oracle Software

Deinstalling Oracle Software

Appendix B - Creating a Database Using the DBCA

The DBCA

Select a Database Template

Specify the Database Name

Set System Account Passwords

Specify File Locations

Install Sample Schemas

Security Settings

Automatic Maintenance Tasks

Specify Storage Parameters

Create Your Oracle Database

Oracle Net Configuration Assistant

Configure the Listener

Set TCP Parameters

Removing a Database with the DBCA

Appendix C - Manual Database Creation

Manual Database Creation

Prepare the System

Creating the Parameter File

The CREATE DATABASE Statement

Manually Create the Database

Finalizing the Creation

SQL.BSQ File

Creating the Service on Windows

Appendix D - Auditing the Database

Auditing Explained

Data Dictionary Views for Auditing

Audit Trail, OS, and DB

Class Materials

Each student in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.

What people say about our training

Very good online class. It provides you with real-world examples and shows you how to solve them.

Ying Sun

University of Michigan

Learning Oracle was made simple.

Mahmood Ahmed

Snap On Inc

This is a really good course to start your Oracle career.

Guojian Cai

TD Bank Canada

This is the 21st century training at it's best. Documentation is perfect. Training material is perfect. Examples and class environment exceeded my expectations. I made a great leap forward to keep up with today's technology in just a week!