Oracle 9i Database Administration

• Fundamentals - I
• Fundamentals - II
• Performance Tuning

FUNDAMENTALS - I

Introduction

• Introduction to Oracle DBA concepts
• Role of the DBA
• Oracle Architectural Components
• Oracle Server, Oracle Database, Oracle Instance
• Understand Connection & Session
• Understand Physical & Logical Structures of DB
• Understand Memory Structures
• Understand Oracle Processes
• Understand the execution of SQL Statement
• Understand & Manage the Oracle Database startup & shutdown Modes

Features of Oracle Universal Installer
Create Oracle Database

• Manual method
• Database Configurator Assistant

Create Data Dictionary Views

• Types  of Data Dictionary Views and its Significance
• Manage the Text parameter file and the Server parameter file
• Understand the Alert log and trace files

Managing the Control files

• Understand the contents of Control files
• Uses of Control file
• Multiplexing Control files
• OMF Control file  creation
• Control file recreation

Managing the Redo Log files

• Understand the contents of Redo log files & its working.
• Log Switches and Checkpoints
• Mulitplexing Redo log files
• Archiving the Redo log files
• Archiver process and Archive destinations

Managing Tablespaces & Datafiles

• Creating Tablespaces
• Understanding locally managed and dictionary managed tablespaces
• Temporary Tablespaces
• Adding space to Tablespaces
• Various operations with Tablespaces
• Absolute and Relative File numbers for Datafiles
• Autoextending Datafiles
• OMF Datafiles

Managing Undo Data

• Understand the difference between Manual and Automatic Undo Management
• Managing the Database in Manual Undo Management
• Managing the Database in Automatic Undo Management

Maintaining Database Objects

• Revisiting the logical Structure
• Various Segments
• Understand Oracle Block Structure
• Create and Manage HOT Tables
• Create and Manage IOT Tables
• Create and Manage Cluster Tables
• Create and Manage Partioned Tables
• Create and  Manage B-Tree Indexes
• Create and Manage Bitmap Indexes
• Create and Manage Partitioned Indexes
• Create and Manage Function Based Indexes

User Management

• Create Database users
• Assign Roles & privileges
• Sysdba & Sysoper privileges
• Create and assign profiles
• Set resource limits for profiles

Maintain Password File

• Auditing and levels of auditing

Top

FUNDAMENTALS - II

Basic Oracle Networking Architecture

• Key Components in Oracle Net Architecture
• Net Services role in Client Server Connections

Oracle Net Services Server Sided Configuration

• Understanding and Configuring Listeners
• Manage Listeners through LSNRCTL utility
• Undestand Dynamic Service Registration
• Securing the listeners

Oracle Net Services Client Sided Configuration

• Understand and implement Host Naming method
• Understand and implement TNS method
• Trouble shooting simple errors

Configuring Shared Servers

• Understand the components of Shared Servers
• Shared Server Architecture
• Configuring Dispatchers and Shared Servers

Backup & Recovery Overview

• Understand the basics of Database backup , Restore and recovery
• Types of failures that can occur in Oracle Environment
• Instance Recovery and the factors that influence it
• Understand the processes, files, memory structures required for recovery
• Understand and perform Logical Backups

User Managed Backups

• Importance of Archive log mode
• Switching the Database to Archivelog mode
• Revisiting Archival process
• Understand and  perform Cold Backups
• Understand and peform Hot backups

Oracle RMAN Overview and Configuration

• Key Features of RMAN
• Components of RMAN
• Configuring RMAN with Catalog Database and without Database
• Creating catalog Database
• Understand Channel Allocations
• RMAN Commands visited

RMAN Miscellanous

• Creating and managing Recovery Catalog
• Registering the databases with recovery catalog

RMAN Backups     

• Perform Full DB backup with archivelogs
• Understand and perform various incremental backups
• Perform Controlfile and spfile backups
• Use COPY command to perform Image copies of Datafiles

User Managed Recoveries

• Case studies involving complete Recoveries
• Case studies involving Incomplete Recoveries

RMAN Recoveries

• Execute Complete Recoveries using RMAN
• Execute Incomplete Recoveries using RMAN

Top

PERFORMANCE TUNING

Overview of Performance Tuning

• Where to Start ?
• Common Performance Problems
• Performance vs availability Tradeoffs

Diagnostic & Tuning Tools

• Log & Trace Files
• OEM, Statspack & AWR
• Getting Information from Data Dictionary
• Methods for collecting Statisitcs

Database Configuration & I/O Issues

• Distributing Datafiles
• Tablespace  considerations
• Full Tablescans
• Checkpoints
• Redolog Management & Performance

Tuning SGA

• Shared pool components and latches
• Monitor and tune Library & Dictionary Cache
• Reserved pool and large pool management
• Buffer cache sizing parameters
• Using Buffer cache Advisory
• Automatic memory management vs Manual memory management
• Resizing SGA components
• Manage Redo log buffer and redo operations
• Manage java pool
• Optimizing Shared Servers

Optimizing Sorts

• Automatic PGA vs Manual PGA
• PGA Sizing Advisor
• Sorts and temporary segments
• Guidelines for  sorts and reducing sorts
• Monitor temporary space usage

SQL Statement Tuning

• Optimizer Modes
• Hints
• SQL Diagnostic tools

Statistics management

• Automatic statistics gathering
• Dynamic Sampling
• Manual collection of statistics at various levels
• System statistics
• Copying statistics across databases

Application Tuning

• Role of the database administrator
• Table movement and redefinition
• Creating and managing indexes and index-organized tables
• Statistics collection
• Considerations for OLTP systems
• Considerations for decision support systems and data warehouses
• Considerations for hybrid systems
• Create and maintain Materialized views

Locks & latches

• Understaning Locks and Latches
• Types of Locks and Latches
• Deadlocks
• Diagnostic tools for Deadlock detection

Top