Oracle Database Administration

Audience

The course is suitable for new database administrators, system administrators and support staff.

Prerequisites

Delegates should have a good knowledge of their operating system, together with Oracle SQL*PLUS and PL/SQL skills. Our Oracle SQL and PL/SQL Part 1 courses are ideal for this.

Duration

5 days. Hands on.

Course Objectives

The course is designed for technical personnel who are existing or new database administrators and will enable the delegate to create and maintain Oracle databases at version 11g. The course is structured in such a way as to take the delegate step-by-step through installation, creation of the database, addition of users, creation of objects and relational constraints. Exercises on each lesson provide the delegates with plenty of hands on experience.

At the end of the course the delegate should be able to:

• Understand the Oracle architecture.
• Create, configure and maintain an Oracle Database.
• Start up and shut down the database.
• Create new users and assign them appropriate access.
• Monitor the database and take corrective action.

Course Content

The basics of an Oracle Database
What Is an Oracle Instance and Oracle Database?
The components Of an Oracle Installation

The System Global Area.
DB Block Buffers
REDO Log Buffer
Shared Pool
ASMM - Automatic Shared Memory Management

The Oracle Background Processes
- DBW0, LGWR, CKPT, ARC0, SMON, PMON, MMAN, MMNL, etc.

The Oracle Disk Areas
Control Files
REDO Logs
Data Files
Parameter & Password Files
ASM – Automatic Storage Management Overview

How Oracle Processes a User Request
Manipulating Data
Commit Rollback Cycle

Designing and Creating Your Database
OFA Optimal Flexible Architecture
Sizing and positioning of Disk Structures
The Database Configuration Assistant

Using Server Manager and SQLPLUS Line mode
SYSDBA, SYSOPER, Normal Modes
Password Authentication

Using Enterprise Manager Console
DB Control or Grid Control?
Administering the Database
Overview of Advisor Central

Maintaining Redo Logs
Adding and Removing Redo Log Groups and Members
Resizing or Re-Positioning Redo Logs
Mirroring and Multiplexing Redo Logs

Maintaining Control Files
Multiplexing Controlfiles
Creating Controlfiles

Sizing and creating Tablespaces.
Types of Tablespace
The System and Sysaux Tablespaces
Creating a Tablespaces
Sizing and Positioning of Tablespace Datafiles
Locally Managed Tablespaces
Bigfile Tablespaces
Tablespace Encryption

Managing Undo Segments.
Undo Segments and Their Use
Manual and Automatic Undo Managment
Required number and Size of Rollback Segments
Creating Undo Segments

24 Hour Operations.
Configuring Archivelog Mode
Archive Log files and the Flash Recovery Area
Avoiding the Pit Falls.

Creating Undo Segments

Alert and Trace Files

Identifying problems using the Alert and Trace Files

Starting and shutting down the database.
Using SQLPLUS Line mode
Nomount, Mount, Open Database Modes
Restricted Access Mode

Connecting to a database
PGA Program Global Area
User and Server Processes
SYSDBA, SYSOPER and Normal Modes

User Access
Users and User Schemas
Managing User Sessions

Creating Users
User Profiles
Defining User Storage Areas
Restricting User Resources

User security and assigning roles
Assigning and Re-setting User Passwords
System and Object Level Privileges
Roles and Assigning Roles to Users
Controlling user resources using profiles

Monitoring and maintaining users

- View User information in the Data Dictionary

Tables & Indexes
Creating Tables
Table Storage Parameters
Partitioning Tables
Enforcing Data Integrity and Relationships
Enabling and Disabling Constraints
Object Types LOBS, CLOBS, NCLOBS, Bfiles
VARRAYS , REF, Nested Tables, Indexed Organised Tables
- Types of Indexes B*tree, Bitmap, Reverse Indexes
Creating Indexes
Index Storage Parameters
Partitioning Indexes
Global & Local Index Partitions
Invisible indexes

Using DATA PUMP utility
Configuring Data Pump
Using the Data Pump

The Data Dictionary
Viewing Dictionary Information
Using the Data Dictionary to Monitor the Database

Automating Database Maintenance Tasks.

This course is suitable for all recent versions of Oracle, up to and including 11G.