Oracle Databases represent the core of an organisation's information infrastructure. To maintain those databases it's critical for administrators to be well-trained. This is a hands-on course where you gain the knowledge and skills to create and configure Oracle, manage memory and storage and deploy essential procedures to maintain your database and ensure its availability.

 

This course is also useful for delegates using Oracle 9i. Course content can be focused on 9i while using 10g, and highlighting differences between the versions as required.

To conduct administrative duties on an Oracle database with efficiency and confidence.

• Create, configure and maintain Oracle 10g databases
• Optimise space and performance for effective storage management
• Ensure database security by managing user privileges and roles
• Manage data import and export

Managing Structural Components

 

CHAPTER 1: Oracle Architectural Components

 

Oracle Server

Oracle Instance

Oracle Database

Memory Structure

System Global Area

Shared Pool

Database Buffer Cache

Redo Log Buffer

Mandatory background processes

Database Writer (DBWn)

Log Writer (LGWR)

System Monitor (SMON)

Process Monitor (PMON)

Checkpoint (CKPT)

Program Global Area

Physical Structures

Logical Structures

Server Process

User Process

Connecting to an Oracle instance

Processing SQL Statements

 

CHAPTER 2: Getting Started with the Oracle Server

 

Database Administrator Users

SQL*Plus

Initialization Parameter Files

SPFILE spfileSID.ora

PFILE initSID.ora

Creating a PFILE

Creating an SPFILE

Starting Up a Database NOMOUNT

Starting Up a Database MOUNT

Starting Up a Database OPEN

Change from NOMOUNT to MOUNT to OPEN

Opening a Database in Restricted Mode

Opening a Database in Read-Only Mode

Shutting Down the Database

Shutting Down ABORT

Shutting Down IMMEDIATE

Shutting Down TRANSACTIONAL

Shutting Down NORMAL

 

CHAPTER 3: Monitoring a Database Instance

 

Dynamic Performance Views

Data Dictionary Views

Diagnostic files

The Alert Log

Background Trace Files

User Trace Files

Enabling or Disabling User Tracing

 

CHAPTER 4: Creating a Database

 

Planning and Organizing a Database

Oracle Software and File Locations

Creation Prerequisites

Authentication Methods for Database Administrators

Using Password File Authentication

Creating a Database

Creating a Database Using Database Configuration Assistant

Creating a Database Manually

CREATE DATABASE Command

Troubleshooting

After Database Creation

 

CHAPTER 5: The Data Dictionary

 

Data Dictionary Contents

Base tables

Data dictionary views

Data Dictionary View Categories

Availability of Data Dictionary Views

Availability of Dynamic Performance Views

Administrative Script Naming Conventions

Sysaux Tablespace

How the Sysaux Tablespace is used

 

CHAPTER 6: Maintaining the Control File

 

Function of the Control File

Control File Contents

Multiplexing the Control File When Using SPFILE

Multiplexing the Control File When Using PFILE

Creating a control file backup (binary)

Creating a control file trace.

Changing Control file settings

Obtaining Control File Information

 

CHAPTER 7: Maintaining Online Redo Log Files

 

Function of Online Redo Log Files

How Online Redo Log Files Work

Log Switches and Checkpoints

Log Groups and Log Members

Adding Online Redo Log File Groups

Adding Online Redo Log File Members

Dropping Online Redo Log Groups

Dropping Online Redo Log File Members

Relocating or Renaming Online Redo Log Files

Clearing Online Redo Log Files

Sizing Redo Log Groups

Obtaining Group and Member Information

Archived Redo Log Files

 

CHAPTER 8: Managing Tablespaces and Data Files

 

Tablespaces and Data Files

Types of Tablespaces (locally managed, Dictionary managed, big file, small file)

Creating Tablespaces

Default Tablespace

Space Management in Tablespaces

Undo Tablespace

Temporary Tablespaces

Default Temporary Tablespace

Read-Only Tablespaces

Taking a Tablespace Offline

Changing Storage Settings

Resizing a Tablespace

Enabling Automatic Extension of Data Files

Manually Resizing a Data File

Adding Data Files to a Tablespace

Moving Data Files

Dropping Tablespaces

Obtaining Tablespace Information

 

CHAPTER 9: Storage Structure and Relationships

 

Segments Extents and Blocks

Types of Segments

Extent Allocation and Deallocation

Used and Free Extents

Database Blocks

Multiple Block Size Support

Standard Block Size

Nonstandard Block Size

Creating Nonstandard Block Size Tablespaces

Multiple Block Sizing Rules

Choosing a Block Size

Database Block Contents

Block Space Utilization Parameters

Data Block Management

Configuring Automatic Segment-Space Management

Manual Data Block Management

Block Space Usage

Obtaining Storage Information

 

CHAPTER 10: Managing Undo Data

 

Function of Undo Segments

Read Consistency, Rollback, Transaction Recovery

Types of Undo Segments

Undo Configuration

Undo Initialization Parameters

Creating an UNDO Tablespace

Altering an UNDO Tablespace

Switching UNDO Tablespaces

Dropping an UNDO Tablespace

Additional UNDO Parameters

Undo Retention Policy

Sizing an UNDO Tablespace

Managing Flashback Requirements

Flashback Queries, FlashbackTransaction and Flashback Table

Undo Data Statistics

Obtaining Undo Segment Information

Managing Schema Objects

 

 

Type of Table Structure:

Heap Table

Index Organized Table

Clustered Table

Partitioned Table

Choosing a Table Structure

Oracle Built-in Data Types

ROWID Format

Structure of a Row

Creating a Table

Creating a Table: Guidelines

Creating Temporary Tables

Manually Allocating Extents

Truncating a Table

Dropping a Table

The 'Flashback Table to Before Drop' command

Dropping a Column

Renaming a Column

Using the UNUSED Option

Row Migration and Chaining

Setting PCTFREE and PCTUSED

Changing Storage and Block Utilization Parameters

Nonpartitioned Table Reorganization

Obtaining Table Information

 

CHAPTER 12: Managing Indexes

 

Structure of a B-Tree Index

Sequential Index problems

Reverse Key Indexes

Problems of low selectivity

Bitmap Indexes

Comparing B-Tree and Bitmap Indexes

Function Based Indexes

Creating Indexes: Guidelines

Creating B-Tree Indexes

Creating Bitmap Indexes

Creating Functional Indexes

Index fragmentation

Coalescing Indexes

Rebuilding Indexes

Rebuilding Indexes Online

Dropping Indexes

Changing Storage Parameters for Indexes

Allocating and Deallocating Index Space

Checking Index Validity

Identifying Unused Indexes

Obtaining Index Information

 

CHAPTER 13: Maintaining Data Integrity

 

Data Integrity

Types of Constraints

Constraint States

Constraint Checking

Defining Constraints
Immediate or Deferred

Primary and Unique Key Enforcement

Foreign Key Considerations

Defining Constraints While Creating a Table

Guidelines for Defining Constraints

Enabling Constraints

Disabling Constraints

Renaming Constraints

Constraint violations and the EXCEPTIONS Table

Obtaining Constraint Information

Managing Users

 

CHAPTER 14: Managing Password Security and Resource Limits

 

Using Profiles: Password Settings

Password Management

Enabling Password Management

Password Account Locking

Password Expiration and Aging

Password History

Password Verification

Password Verification Function VERIFY_FUNCTION

Using Profiles: Enabling Resource Limits

Setting Resource Limits at Session Level

Setting Resource Limits at Call Level

Creating a Profile: Resource Limit

Altering a Profile

Dropping a Profile

Obtaining Password and Resource Limit Information

 

CHAPTER 15: Creating Users

 

Users and Security

Creating Users

Default Tablespace

Default Temporary Tablespace

Authentication Method

Manipulating User Passwords

User Quota on Tables