Oracle Training : Oracle8i Database Administration

Oracle8i Database Administration

Oracle Training Overview

This course provides you with the skill-set necessary to create, maintain and manage an Oracle8i database. We focus on the techniques and skills necessary for day-to-day database administration tasks. This course has been completely updated for Oracle8i. This class will prepare you for OCP DBA Exam 2 and help prepare you for OCP DBA Exam 3. (Exam 1 topics are covered in Introduction to Oracle for Developers class.)

Oracle Training Prerequisites

Oracle SQL, SQL*Plus and PL/SQL experience. This can be obtained in Introduction to Oracle8i for Developers class.

Oracle Training Course duration

5 Days

Oracle Training Objectives

After successfully completing this course, you will be able to:

Create and manage a large Oracle8i database
Understand and gain effective use of database creation and startup parameters
Design and implement an effective Backup and Recovery strategy, including the use of Recovery Manager
Estimate storage requirements for tables, indexes and other segments
Effectively use storage parameters such as INITIAL, NEXT, PCTINCREASE, INITRANS and MAXTRANS to manage disk storage
Manage rollback segments
Use the Oracle8i Data Dictionary to help manage the database
Create and control database users, including the use of default and temporary tablespaces and the new Oracle8i password management features
Design and implement an effective security strategy including the use of Roles
Understand Oracle8i database architecture including SGA, shared pool, library cache, buffer cache and more
Implement partitioned tables, including the new 8i features Hash Partitioning, Composite Partitioning, Merge Partitions and Row Movement
Create 8i indexes including b-tree, bitmap, function-based, reverse key and index-organized tables
Review other new Oracle8i database enhancements such as Logminer, database event triggers, suspend database, tablespace reorganization and more

Oracle Training Course outline

Oracle Architecture

Objectives
Database vs. Instance
Architecture Diagram
The Database
Database Files (Datafiles)
Control File
Redo Log Files
The Oracle Instance
The Processes
Instance Memory Areas
Buffer Cache
Default, Recycle, Keep Pools
Shared Pool - Library Cache
SQL Statement Processing
Shared Pool - Data Dictionary Cache
Redo Log Buffer

Startup & Shutdown Instance

Objectives
Startup Steps
The DBA Userid
SYSDBA/SYSOPER Roles
Using SQL*Plus
Stages Of Startup
Startup Command
Startup Examples
STARTUP RESTRICT
Restricted Access - ALTER
3 Shutdown Modes For An Instance
Shutting Down
ALTER DATABASE

Creating a new Database

Objectives
Major Steps
The Parameter File - INIT.ORA
Critical Parameters
Displaying Parameter Values
v$parameter Table
The Oracle SID
Creating NT Services
Listing Active Instances
Stopping Active Instances
Database Configuration Assistant
Step 2: Select Database Type
Step 3: Select Environment
Step 4: Concurrent Users
Step 5: Server Mode
Step 6: Options
Step 7: Database & SID
Step 8: INTERNAL Account
Step 9: Control Files
Step 10: Tablespaces
Step 11: Redo Log Files
Step 12: Logging Information
Step 13: SGA Parameters
Step 14: Trace Files
Step 15: Finish
DB Create Script
CREATE DATABASE Command
DB Create Script

Managing Control Files

Control File Review
Multiplex Control Files
Steps To Multiplex Control Files
Updating the Control File
CREATE CONTROLFILE
Corrupted Control File

Managing Redo Logs

Redo Log Basics
Creating Log Files
Log Switch Delays
Adding Log Files
Multiplexing Log Files
Redo Log Tuning

Managing Storage

Storage Fundamentals
Storage Management Goals
Storage Clause
Storage Clause Syntax
Storage Clause Defaults
Storage Clause Example
PCTINCREASE
Storage Clause - Example
PCTFREE & PCTUSED
FREELISTS & PCTUSED
PCTFREE
PCTUSED
PCTFREE/PCTUSED Scenarios
PCTFREE/PCTUSED Summary
INITRANS & MAXTRANS
INITRANS/MAXTRANS Example
Data Dictionary Views
DBA_SEGMENTS
Estimating Storage Requirement
Storage Overhead
Disk Space Calculations
Multiple Extents
Detecting Excessive Extents
Repairing Excessive Extents
Monitoring Free Space
Fixing Under/Over-Allocation
Monitoring Chained Rows
Eliminating Chained Rows
Avoiding Chained Rows

Tablespaces

Tablespace Review
Creating Tablespaces
Oracle Flexible Architecture (OFA)
Managing the SYSTEM Tablespace
Increasing Space
Dropping Tablespaces
ALTER Tablespace
Locally Managed Tablespaces
Creating LM Tablespaces
Problems & Restrictions

Managing Tables and Constraints

Basic CREATE TABLE
Object Name Rules
Oracle Datatypes
Intro to Constraints
Violating a Constraint
Constraint States
ENABLE State
DISABLE State
Identifying Constraint Exceptions
Deferred Constraint Checking
Constraint Information
Automated Table Monitoring
LOGGING Parameter
DROP Column
DROP Column Syntax
Immediate DROP Column
Deferred DROP Column
DROP Restrictions
Index Organized Tables
Table Reorganization
Move Syntax
Using Move Table
Good Things To Know About Move Table
ONLINE Option
Temporary Tables Concepts - Basic
Temporary Tables Concepts - Data
Temporary Tables Concepts - Management
Temporary Tables Concepts - Types
Creating Temporary Tables
Using Temporary Tables
Temporary Table Limitations

Managing Rollback Segments

RBS Basics
Creating a RB Segment
Monitoring RB Segments
Fixing Wraps
ORA-1555 Snapshot Too Old
Rollback Segment Tips

Temporary Segments

Temporary Segment Basics
Avoiding Disk Sorts
Temporary Segment Creation
TEMPORARY Parameter

Indexes

Objectives
Index Basics
B-Tree Indexes
Creating B-Tree Indexes
Updating Statistics
Choosing Columns
Selectivity

Managing Users

Objectives
User Management Basics
Users Created
CREATE USER Example
DBA_USERS
ALTER USER
DROP USER
TEMPORARY Tablespace
Killing a User Session
Quota
Providing Quota
ALTERing Quota
Query Existing Quotas
Externally Identified Users
Creating an Externally Identified User
User Profiles
Creating and Assigning Profiles
Exceeding Profile Limits
Alter and Drop Profile
Password Management
Password Aging
Manual Password Expiration
Account Locking
Password History
Password Complexity
Product User Profiles
When to Run PUPBLD
Disabling Commands
Auditing
Monitoring Login Attempts
Audit GRANT/REVOKE
Object-Level Audits
Displaying Object Audit Activity
The SYS.AUD$ Table
Auditing with Triggers

Backup and Recovery

"Common" Failure Scenarios
Backup Strategies
System Change Number (SCN)
What Should Be Backed Up
Backup Options
Cold Backup Without Archiving
Cold Backup
Media Recovery - No Archive
ARCHIVELOG Mode
init.ora Parameters
Switching to Archiving
Archive Log List Command
Cold Backup with ARCHIVELOG
Cold Backup with Archiving
Complete Recovery with Archiving
Incomplete Recovery
Hot Backup
Hot Backup - Steps
Recovering File to New Location
Useful Data Dictionary Views

Managing Security

Security Basics
"ANY" System Privileges
Insufficient Privileges
Granting Of System Privileges
The PUBLIC Keyword
The ADMIN Option
Revoking System Privileges
System Privs and the Data Dictionary
Object-level Privileges
GRANT Object-level Privileges
INSERT
UPDATE
The ALL Option
WITH GRANT OPTION
Which Object-Level Privileges Exist
Roles: Concepts
Creating a New Role
Password Protected Roles
Dropping A Role
Giving System And Object Privileges To Roles
Granting Roles
The ADMIN OPTION
Granting Roles to Roles
Revoking Roles
Enabling And Disabling Roles
The SET ROLE Command
SET ROLE Command Example
SET ROLE Options
SET ROLE Options Example

Partitioning

Objectives
What is Partitioning?
Oracle8 Support
Partitioning Example
PARTITION Clause Notes
MAXVALUE and NULLs
Partition Storage Attributes
Multiple-Column Partitioning
Hash Partitioning
Composite Partitioning
Row Movement
Maintenance: Rolling In
Maintenance: Rolling Out
Merge Partitions
Other Maintenance Operations
Data Dictionary Implications
Finding the Partitioning Key
Finding the Partitioning Bounds
Explicit Referencing
Table Partitioning Restrictions
Summary of Benefits

Partitioned Indexes

Local vs. Global Partitioned Indexes
Equi-Partitioning
Equi-Partitioning Example
Benefits of Equi-Partitioning
Prefixed Vs. Non-Prefixed
Prefixed Index
Non-Prefixed Indexes
Local Prefixed Index
Local Non-Prefixed Index
Global Indexes
What About Uniqueness!
Local Index Maintenance
Global Index Maintenance
Index Unusable Attribute

Database Event Triggers

Database Event Trigger Execution
System Event Example
DDL Trigger Example
Enable/Disable Triggers
Security Privileges
Trigger Dependencies
Triggers & the Data Dictionary

Oracle8i New Features

Plan Stability
Plan Stability Concepts
Plan Stability - How To?
Other Important Points
Statistics Collection and Management
DBMS_STATS Concepts
How to Use DBMS_STATS
Other Useful Routines
Automatic Statistics Gathering
Index Statistics
Transportable Tablespaces
Concepts
Using Transport
Transport Limitations
Locally Managed Tablespaces
Concepts
Creating Locally Managed Tablespaces
Multiple Archive Destinations
What's New?
Archiving to Multiple Locations
Rollback Segment Configurations
Oracle Recommends
New RBS Calculation
Constraint Management Enhancements
Disable Validate State
RELY Attribute
Additional Enhancements
What's Here?
Database Resource Manager
Fast-Start Checkpointing
Monitor Long Running Operations
Suspend Database
Multiple Buffer Pools
Online Index Rebuilds
Online Rebuild Limitations
Index Coalesce
Increased Database Limits
Direct-Load INSERT
NOLOGGING Clause
NOLOGGING Clause Examples
Parallel DML
Enabling Parallel DML
Parallel DML Restrictions
Deferred Constraint Checking
Enforced Constraints

SQL*Loader

SQL*LOADER Basics
Direct Path Load
Conventional Path Load
Parallel Direct Load
Executing SQL*Loader
Command Line Options
The Control File
Input Data - Variable Format
Loading Variable Format Data
LOG File
Input Data - Fixed Format
Loading Fixed Format Data
Discarding Records
Discarding Records Example

LogMiner

Objectives
Concepts
How to Use LogMiner
Create Dictionary
Create Log File List
Run LogMiner
View Results

Hardware/Software Requirements

Hardware Requirements

Minimal Processor: Pentium 166 or Pentium 200
Recommended Processor: Pentium 233 or Pentium 266
RAM: 128 MB (256 MB recommended)

Oracle Corporation also recommends increasing virtual memory to 200 MB. (Modify it in the Performance tab of System Properties in the Control Panel.)

Available Disk Space (FAT or HTFS): 3 GB
Video: 256 color
A connection to the Internet is desirable (but not absolutely necessary) to show students various Oracle-related sites.

Software Requirements

Windows NT 4.0 or Windows 2000
Oracle8i 8.1.6 or above
Microsoft Internet Explorer 5 or Netscape Navigator 4.5
Adobe Acrobat Reader or Acrobat 4.0 or later (for the course presentation)*
Student Files
Text Editor*