SYSTEMS & SOFTWARE
Training Limited
Oracle™ 10g Database Administration
This course is suitable for anyone new to Oracle™ 10g
Course Content 1 Day

Overview

In this course students develop the key fundamental skills necessary to be an Oracle™ DBA. They design and create an Oracle™ Database 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.

Oracle™ 10g Database Training Objectives

All attendees will learn how to:
Start and shutdown Oracle™
Use the Oracle™ data dictionary
Manage database users and schemas
Set system privileges
Create and Modify Control parameter files
Mange Work with datafiles and tablespaces
Work with redo logs and Undo segments
Manage segment space
Create and manage tables and indexes
Apply constraints
Build views
Set object privileges
Use synonyms
Use the Optimizer
Use SQL*Loader
Backup databases
Recover databases
Use the Oracle™ Enterprise Manager

Oracle™ 10g Database Training Outline

Overview of Oracle™ Database

Installing Oracle™
Understanding the Oracle™ Architecture
The Oracle™ Database and Oracle™ Instances
Instance Memory Structures
Background Processes
Server Processes
Datafiles
Blocks, Extents, and Segments
Control Files
Redo Logs
SYS and SYSTEM Users
Overview of


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


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


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


Oracle™ System Privileges

Overview of System Privileges
Granting and Revoking System Privileges
Data Dictionary Views for System Privileges
Roles
Creating and Removing Roles
Data Dictionary Views for Roles
Pre-defined Roles
User Group PUBLIC


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


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


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


Oracle™ Physical Structures — Online Redo Logs

Redo
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


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


Segment Space Management

Blocks, Extents and Segments
Fragmentation
Coalescing Fragmented Space
Manual Segment Space Usage
Automatic Segment Space Management
Data Dictionary Views for Physical Objects


Tables

Tables Overview
Physical Properties of Tables
Row Chaining and Row Migration
Creating Tables
Table Storage and Logging
Altering Tables
Dropping Tables
The Recycle Bin
The TRUNCATE COMMAND
Temporary Tables
Clustered Tables
Indexed-Organized Tables
Creating an IOT
Data Dictionary Views for Tables


Indexes

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


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


Views

Views
Creating and Replacing Views
Data Dictionary Views for Views
Security Through Views
Altering and Dropping Views
Dependencies and Views
Updating Data Through Views


Object Privileges

Object Privileges
Granting and Revoking Object Privileges
Using WITH GRANT OPTION
Revoking and the GRANT OPTION
Data Dictionary Views for Object Privileges


Synonyms

Synonyms
Private and Public Synonyms
Creating and Dropping Synonyms
Security through Synonyms
Object Precedence


The Optimizer and Statistics

Optimizer Overview
Optimizer Statistics
Data Dictionary Views and Statistics
Collecting Statistics
Removing Statistics
Automated Statistics Collection


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


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


SQL*Loader and External Tables

SQL*Loader 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


Backup Your Database

Physical Backups vs. Logical Backups
Offline Backup Basics
Oracle™ Offline Backup Steps
Other Backup Considerations
Advanced Backup Concepts
RMAN


Recover Your Database

Offline Recovery Basics
Restoration Using Offline Backups for a NOARCHIVE Database
Restoring Other Files
Other Recovery Considerations


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


Conclusion