  
                     
|
| |
 |
ORACLE 9i DBA FUNDAMENTALS I
| Course Description |
|
This class will develop the key fundamental skills necessary to be an Oracle DBA. Students will design and create a 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. Each student will create a toolkit of administration scripts for database management and tuning by the end of this class. This class will also address issues for DBAs having to support Oracle8i and Oracle9i databases.
Course Length: 5 Days
Course Tuition: $1890 (US)
|
| Prerequisites |
|
|
Introduction to Oracle8i or Introduction to Oracle9i or at least six months working in an Oracle technical environment. An understanding of relational database concepts, SQL and PL/SQL programming skills are required. A solid understanding of Oracle schema is recommended. |
• Understanding the Oracle9i Server Architecture
Responsibilities of a DBA
Changing DBA Environments
What is an Oracle Server?
Oracle Versioning
Server Architectural Overview
Operating System Environment
Important Subdirectories in ORACLE_HOME
Oracle Product Subdirectories
Oracle Instance Components
Database, Control, and Redo Log Files
Logical Storage Structures
Oracle Block Sizes
Blocks, Extents, and Segments
Different Types of Segments
Tablespaces
Logical Structural Design
System Change Numbers
Different Versions of Oracle
Oracle8.0 - The Object-Relational Release
Oracle Ceilings
Additional Database Limits
Oracle8i - The Internet Release
Oracle9i Release 1- The eBusiness Database
Oracle9i Release 2- The Object-Relational OLAP Database
Oracle Products and Architectures
DBA Management Pack
Understanding your Oracle Server Layout
• Oracle Memory Structures and Processes
Oracle Instance Review
Database Buffer Cache
The LRU and Dirty Lists
Server Processes and the Buffer Cache
DBWR and the Buffer Cache
MTTR and Checkpoint intervals
Checkpoints
V$INSTANCE_RECOVERY View
MTTR Advisor
Minimal I/O Recovery
Defining Multiple DB Cache Buffer Pools
Defining the Database Buffer Pools (Oracle8i style)
Database Buffer Pool Parameters (Oracle8i style)
The Buffer Pool Parameter
Defining Storage for Buffer Pools
Calculating the Hit Ratio for Buffer Pools and Buffer Pool Sizes
Latch Contention
Caching Tables
Calculating Cache Usage of Schema Objects
Redo Log Buffer Cache
Shared and Large Pool Area
Java Pool Size
The Oracle JVM
Impacting Server Process Memory
Background and Required Background Processes
Additional Background Processes
Listing Processes
• Server Processes and Executing SQL
Server Processes
Program and User Global Areas
Dedicated and Shared Server Architecture
Deprecated and Shared Server Parameters
The Parse Phase
Execution Plan Phase
Execution and Fetch Phase
Processing DML Statements
Rule-based and Cost-based Optimizers
Tuning Parallel Query
Parallel DML
• Using the Data Dictionary
What is the Oracle Data Dictionary?
Base Tables, Static Views, and Dynamic Views in the Data Dictionary
ALL_% Static Views
DBA_% Static Views
USER_% Static Views
USER_% Static Views
V$ Dynamic Views
GV$ Dynamic Views
GV$ Dynamic Views
Database Data Dictionary Views
• Starting and Shutting Down an Oracle Server
Database Utilities
The Server Manager Tool (Obsolete in Oracle9i)
SQL*Plus Features and Commands
Additional Commands
The Parameter File
Oracle Universal Installer
The Database Configuration Assistant
Setting up a Parameter File
Important Parameters for Database Creation
Starting an Oracle Server
The STARTUP command
Startup and Shutdown Privileges
The SHUTDOWN Command
ALTER DATABASE Command
Alert and Trace Files
Working with Data Dictionary Views
Dynamic versus Static Parameters
• Server Parameter File
Definition of the Server Parameter File (SPFILE)
Operation of the SPFILE
Creating an SPFILE
Changing Parameters in the SPFILE
Backing up the SPFILE
Gathering Information About Parameters
SPFILE FAQs
• Dynamic Memory Management
Setting up SQL Work Areas
Defining Automatic PGA Memory Management
SQL Work Areas versus the PGA Aggregate Target
Setting the PGA Target Memory Size
Displaying Workarea Information
Using a PGA Workarea
Dynamic SGA
Granules
Changing the Database Buffer Cache
V$BUFFER_POOL
Changing the Shared Pool Size
Database Buffer Cache Parameters
Multiple Database Buffer Caches
Deprecated and Advisory Parameters
V$DB_CACHE_ADVICE
• Managing Tablespaces and Data Files
Review Logical and Physical Structure Relationships
The SYSTEM Tablespace
CREATE TABLESPACE
Locally-Managed Tablespaces
Temporary Tablespaces
Temporary Segments
Database Default Temporary Tablespace
Creating a Default Temporary Tablespace
Read-only Tablespaces
Viewing Active Transactions
Coalescing Dictionary Managed Tablespaces
Using the ALTER TABLESPACE command
Taking Tablespaces ONLINE and OFFLINE
Renaming a Data File
Adding More Space to a Database
Autoextending a Data File
DROP TABLESPACE Command
Dictionary-Managed Tablespaces
Dictionary versus Locally Managed Tablespaces
Converting Dictionary and Local Tablespaces
Migrating the Tablespace Management Type
Data Dictionary Views
• Managing Rollback Segments
Managing Before-Image Data
Rollback Segments
Private versus Public Rollback Segments
Second Rollback Segment in SYSTEM
Sizing Rollback Segments
The OPTIMAL Parameter
CREATE ROLLBACK SEGMENT Command
ALTER/DROP ROLLBACK SEGMENT Commands
Using the SET TRANSACTION Command
Data Dictionary Views for Rollback Segments
Tablespace Management
Automatic Undo Management
Creating an Undo Tablespace
Automatic Mode for Managing Undo Space
Automatic Undo Initialization Parameters
The UNDO_RETENTION Parameter
Defining an Undo Tablespace When Creating a Database
Determining the Size of the Undo Tablespace Data File
Dropping an Undo Tablespace
Switching Undo Tablespaces
Data Dictionary Views for Undo Information
Undo versus Rollback Segments
Multiple Block Size Support
Setting up Multiple Block Sizes
• Segment Management
High Throughput Systems
Automatic Segment-Space Management
Space Management
Creating Objects in Automatic Segment-Space Managed Tablespaces
Space Deallocation With Automatic Segment-Space Management
The DBMS_SPACE Package
The SPACE_USAGE Parameters
Space Deallocation Statements
Viewing Space Usage Information
|
• Managing Tables
Creating Tables
Create the SALE_TAB Table
ROWIDs
Creating Segments in Tablespaces
Column Datatypes
Global Temporary Tables
Clustered Tables
Database Block
Block Utilization Parameters
Row Migration and Chaining
Storage Clause Options and Parameters
Managing Extent Growth
Moving, Renaming, and Redefining Tables
Removing Data From Tables
Modifying Column Definitions
Defining Columns to be Unused
Dropping Columns
Segment High Water Mark and Segment Header
Calculate the HWM of a Segment
Online Table Redefinitions
DBMS_REDEFINITION Package
Validate Structure Online
The TRUNCATE Command
Data Dictionary Views for Tables
• Managing Indexes
Index Types
B*-tree Indexes
Index Attributes
Maintaining Indexes
Composite Indexes
Skip Scans
Reverse Key Indexes
Bitmap Indexes
Cluster (B*Tree) Indexes
Index Organized Tables
Function-Based Indexes
Domain Indexes
Index Maintenance
Analyzing, Rebuilding, and Coalescing the Index
Online Index Rebuilds
Index Organized Table High Availability Features
Bitmap Join Indexes
Indexes on Primary Keys
Export and Import of Indexes
SQL*Loader and Indexes
• Managing Constraints
Data Integrity
Inline and Out-of-Line Constraints
Constraint Names
Primary and Foreign Key Constraints
Unique, Not Null, and Check Constraints
Viewing Constraint Information
Enabling and Disabling Constraints
Creating and Setting Deferred Constraints
Setting the RELY Flag
Explicit Index Definition
USING INDEX Syntax
Dropping or Disabling Constraints and Indexes
Reducing Locking on Foreign Key Tables
Caching Primary Key Values
• Administration of Control Files
Control File Contents
Working with Control Files
Multiplexing Control Files
The CREATE CONTROLFILE Command
Log Sequence Numbers
Reviewing Checkpoints
Instance Recovery
Managing Checkpointing
Multiple DBWR IO Processes
DB_BLOCK_CHECKING
DB_BLOCK_CHECKSUM
FAST_START_PARALLEL_ROLLBACK
• Administration of Redo Log Files
Review of Redo Log Files
Writing to Redo Log Files
Adding and Clearing Online Redo Log Files
Dropping and Renaming Online Redo Log Files
Archivelog Mode
Setting up Archiving
Mandatory or Optional Destinations
Managing Archive Destinations
Dynamic Archive Parameters
ARCHIVE LOG LIST Command
Data Dictionary Views for Redo Log Files
Oracle Managed Files
Why Use Oracle Managed Files?
The Mechanism of OMF
OMF Data Files and Naming Conventions
O9iR2 OMF Naming Conventions
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
Using OMF in Database Creation
OMF and Online Redo Log Files
OMF, Data Files, and Tablespaces
Renaming OMF Data Files
OMF and Control Files
• Managing Users and Profiles
Creating Users
Assigning Quotas to Users
ALTER USER Command
Protecting the System Tablespace
Creating Profiles
Database Resource Limits
Security Guidelines
• Security Administration
System Privileges
Roles
Viewing System Privileges
Privileges and the User Group PUBLIC
Revoking System Privileges and Roles
Object Privileges
Database Authentication
Setting up a Role
Parent versus Child Roles
Working with Default Roles
Secure Application Role
Enabling and Disabling Roles
Guidelines and Standards for Roles
Additional Security Features
• Additional Security Features
Enhanced Security
Password Management Features
Password Resource Limits
Password Complexity
Setting up Password Management
Defining a Profile with Password Limits
Defining a Profile for a User
Data Encryption
Encryption: Random Key Generation
• Oracle Utilities and Managing Data
Oracle Utilities
The Export Utility
Export Modes and Parameters
Import Utility and Parameters
Rebuilding a Database
Logical Backups
Using Multiple Export Dump Files
Using a Query Filter on Export
Exporting Outlines
Transportable Tablespaces
Self Containing Tablespaces
New 9i Features in Oracle Utilities
Exporting and Importing Statistics
New Parameters for Export and Import
Tablespace Level Exports
Direct-Load Inserts
SQL*Loader
Conventional versus Direct Path Loads
The Control File
DBVERIFY
Loadjava and Dropjava
• Creating an Oracle Instance and Database
Preparation, Methods, and Steps for Creating a Database
Requirements for a Minimal Database
Setup for UNIX and NT
Removing Oracle Servers and Software
Things to Consider When Creating a Database
Optimal Flexible Architecture
Follow OFA Guidelines
OFA Directory Layouts
File Naming Conventions
The CREATE DATABASE Command
Creating a Oracle9i Database Script
What is Created?
Preinstalled Users and Roles
After Database Creation
Managing PL/SQL Packages
Troubleshooting Database
Download a detailed course outline (PDF)
Contact us for course schedules or more
information.
|
|
|
|
|
|
|
