Oracle 11g Database Administration

This class 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.
£2,990.00 (GBP)
£2,000.00 (GBP)

Delivery: Blended learning
OR On-site (Private delivery)
Minimum number of delegates: 1:1
Maximum class size: 20
Delivery date: (as requested by customer)
Offers: contact for details however 20% consecutive discount on all group bookings.
Next availability: Immediate
Applicable for: Application Developer, Data Analyst, Programmer
Offers: contact for details

This class 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.


Non-Oracle database administrators, application developers, and system administrators.


Introduction to Oracle 11g, or at least six months working in an Oracle technical environment.

An understanding of relational database concepts, SQL, and PL/SQL programming skills are required.

A solid understanding of Oracle schema is recommended.

Course content and modules:

1. Course Introduction

Course Objectives

Course Overview

Using the Workbook

Suggested References

2. Overview of Oracle Database


The Oracle Database vs. The Oracle Instance

Instance Memory Structures

Background Processes

Server Processes


Blocks, Extents, and Segments

Control Files

Redo Logs

The Oracle Architecture

SYS and SYSTEM Users

3. 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

4. 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

5. 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

6. Oracle System Privileges

Overview of System Privileges

Granting and Revoking System Privileges

Data Dictionary Views for System Privileges


Creating and Removing Roles

Data Dictionary Views for Roles

Predefined Roles

User Group PUBLIC

7. 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

8. 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

9. 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

10. Oracle Physical Structures — Online Redo Logs


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

11. 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

12. Segment Space Management

Blocks, Extents and Segments

Segment Space Management


Coalescing Fragmented Space

Row Migration and Chaining

Manual Segment Space Usage

Automatic Segment Space Management

Data Dictionary Views for Physical Objects

13. Tables

Tables Overview

Physical Properties of Tables

Creating Tables

Table Storage and Logging

Altering Tables

Dropping Tables

The Recycle Bin


Temporary Tables

Clustered Tables

Indexed-Organized Tables

Creating an IOT

Data Dictionary Views for Tables

14. 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

15. 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



Creating and Replacing Views

Data Dictionary Views for Views

Security Through Views

Altering and Dropping Views

Dependencies and Views

Updating Data Through Views

17. Object Privileges

Object Privileges

Granting and Revoking Object Privileges


Revoking and the GRANT OPTION

Data Dictionary Views for Object Privileges

18. Synonyms


Private and Public Synonyms

Creating and Dropping Synonyms

Security and Synonyms

Object Precedence

19. The Optimizer and Statistics

Optimizer Overview

Optimizer Statistics

Data Dictionary Views and Statistics

Collecting Statistics

Removing Statistics

Automated Statistics Collection

20. 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

21. 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

22. SQL*Loader and External Tables


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

23. Back Up Your Database

Physical Backups vs. Logical Backups

Offline Backup Basics

Oracle Offline Backup Steps

Other Backup Considerations

Advanced Backup Concepts


24. Recover Your Database

Offline Recovery Basics

Restoration Using Offline Backups for a NOARCHIVE Database

Restoring Other Files

Other Recovery Considerations

25. 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

26. 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

De-installing Oracle Software

27. Appendix B – Creating a Database Using 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

28. Appendix C – Manual Database Creation

Manual Database Creation

Prepare the System

Creating the Parameter File


Manually Create the Database

Finalizing the Creation


Creating the Service on Windows

29. Appendix D – Auditing the Database

Auditing Explained

Data Dictionary Views for Auditing

Audit Trail, OS, and DB

Statement Auditing

Privilege Auditing

Object Auditing