Oracle9i DBA Fundamentals II Course

Delegates will learn the fundamentals of setting up Oracle Net Services, configuring the shared server architecture, and backup and recovery. This course covers different ways an Oracle9i instance and database can fail and the best options for recovery. Students will learn how to perform backup and recovery with and without RMAN, how to minimize points of failure and reduce the Mean Time To Recover. Through the numerous backup and recovery labs, students will gain confidence that they can recover a database after an Oracle9i server goes down.

Delegates must have an understanding of basic operating system commands (eg move, copy, delete files). Delegates must also have attended course Oracle9i: DBA Fundamentals I.

Topics covered on the 5 day Oracle9i DBA Fundamentals II course

Overview of Backup and Recovery

• Factors Impacting Backup and Recovery
• Understanding why Systems Fail
• Types of Oracle Failures
• Scenario Testing
• Understanding your Hardware
• Backing up Different Environments
• Factors Impacting Strategies
• Oracle Backup Options
• Types of Oracle Recovery
• Database Files
• Views used in Backup and Recovery

Managing Instance Recovery

• The LRU and Dirty Lists
• Server Processes and the Buffer Cache
• DBWR and the Buffer Cache
• Checkpoint Events
• LOG CHECKPOINT Parameters
• MTTR and Checkpoint intervals
• Oracle8i Methods for Controlling Check Pointing
• Methods for Controlling Checkpointing in Oracle9i
• V$INSTANCE_RECOVERY View
• Minimal I/O Recovery
• Setting different levels of Checkpointing
• Checkpoint and File Queues
• Checkpoint Queue
• Instance Recovery
• Rolling Forward and Backward
• FAST_START_PARALLEL_ROLLBACK
• Controlling DBWR Activity
• Multiple DBWR IO Processes
• DB_BLOCK_CHECKING
• DB_BLOCK_CHECKSUM
• Dynamic Views for Parallel Recovery

Fundamentals of Oracle Backups

• Good Design Fundamentals and Guidelines
• Additional Guidelines
• What Needs to Be Backed Up?
• Different Types of Backups
• Logical and Physical Backups
• Operating System Backups
• Cold Backups
• Hot Backups
• Logical Backups
• Performing a Full Cold
• Automating Backup and Recovery Tasks
• Basic Unix commands

Recovery in Noarchivelog Mode (without RMAN)

• Different Recovery Environments
• The Alert Log File
• Oracle Trace Files
• Recovery in NOARCHIVELOG Mode
• Access the Situation Before Performing Recovery
• Recovery from Media Failure
• Restore a Database to a New Location
• Datafile Loss in NOARCHIVELOG Mode
• Advantages and Disadvantages of NOARCHIVELOG Mode
• Managing Control Files
• When to Backup the Control File
• The CREATE CONTROLFILE Command
• Creating a CONTROLFILE Script in Unix
• Recovery using a Backup Control File
• The Create Control File Command
• Managing Multiple Control Files

Redo Log Files and Recovery (without RMAN)

• The Role of the Archiver Process
• Setting up Archiving
• Setting LOG_ARCHIVE_FORMAT
• Archive Destinations
• Version7 Style Archive Destinations
• Converting the ArchiveLog Mode
• Mandatory or Optional Destinations
• Managing Archive Destinations
• Dynamic Archive Parameters
• Archive Information
• Managing Online Redo Log Files
• Getting Information on Redo Logs
• Redo Log Failure Scenarios
• Recovering Redo Log Files
• Recovery After Losing an Online Redo Log File
• Replacing a Redo Log Member
• Determine Invalid Members in a Group
• Clearing a Redo Log Group
• Clearing an Active Redo Log Group
• Accessing Redo Log Information

Recovery in Archivelog Mode (without RMAN)

• Recovery in ARCHIVELOG Mode
• Recovery Information
• Performing Media Recovery in Parallel
• Media Recovery
• Closed Database Recovery
• Issues of Backing up Online Redo Log Files
• The RECOVER Command
• Recovery Options
• ALTER DATABASE Examples
• Database Recovery Guidelines
• Recovering a SYSTEM or Rollback Segment Data File 5
• Recovering a Data File
• Recovering a Data File With No Backup
• Cancel-Based Recovery
• Time-Based Recovery
• Changed-Based Recovery:
• Resetting Redo Log Files
• Running Hot Backups
• Backing Up Read-Only Tablespaces
• Logging and Recovery

Oracle Utilities and Managing Data

• Oracle Utilities
• The Export Utility
• Export Modes
• Export Parameters
• Import Utility
• Executing the Import Utility
• Import Parameters
• Rebuilding a Database
• Logical Backups
• Using Multiple Export Dump Files
• Using a Query Filter on Export
• Exporting Outlines
• Transportable Tablespaces
• Steps to Transport a Tablespace
• Self Containing Tablespaces
• Oracle9i Features in Oracle Utilities
• Exporting Statistics
• Importing Statistics
• New Parameters for Export and Import
• Tablespace Level Exports
• Direct-Load Inserts
• SQL*Loader
• SQL*Loader Files
• Conventional versus Direct Path Loads
• SQL*Loader Parameters
• The Control File
• Loadjava and Dropjava

Diagnostics and Troubleshooting

• Detecting Software Corrupt Blocks
• DBV
• DBVERIFY Parameters
• DBVERIFY Output
• The DBMS_REPAIR Package
• DBMS_REPAIR Restrictions
• DBMS_REPAIR Constants
• DBMS_REPAIR Exceptions
• DBMS_REPAIR.ADMIN_TABLES Procedure
• DBMS_REPAIR.CHECK_OBJECT Procedure
• DBMS_REPAIR.FIX_CORRUPT_BLOCKS Procedure
• DBMS_REPAIR.SKIP_CORRUPT_BLOCKS Procedure
• Issues of Skipping Corrupt Blocks
• DBMS_REPAIR.DUMP_ORPHAN_KEYS Procedure
• DBMS_REPAIR.REBUILD_FREELISTS Procedure
• The Log Miner Utility
• LogMiner Packages
• V$LOGMNR_CONTENTS
• Creating a LogMiner Data Dictionary File
• Using LogMiner to Analyze Log Files
• Analyzing the Log Files with DBMS_LOGMNR
• START_LOGMNR and END_LOGMNR
• ANALYZE VALIDATE STRUCTURE Command

Recovery Manager Overview

• Issues with Managing Backups
• What is Recovery Manager?
• RMAN Interfaces
• Working with Recovery Manager
• RMAN Compatibility
• Using Recovery Manager
• Using RMAN with Multiple Platforms
• Advantages of RMAN
• Disadvantages of RMAN
• Oracle9i RMAN Enhancements
• Improved Error Output
• Recovery Manager Terminology

Recovery Catalog

• Recovery Manager without the Recovery Catalog
• Contents of the Recovery Catalog
• Catalog Considerations
• Advantages of the Recovery Catalog
• Disadvantages of the Recovery Catalog
• Creating a Catalog Database 0
• Connecting to a Catalog Database
• Connecting without a Recovery Catalog
• Connect to a Target Database with a Recovery Catalog
• Connecting to an Auxiliary Instance
• Connect Using a Script:
• Register the Target Database with the Recovery Catalog
• Unregister the Target Database with the Recovery Catalog
• Resynching the Catalog and Target Databases

Managing Recovery Manager

• Types of RMAN Commands
• Standalone and Job Commands
• Executing Recovery Manager Commands
• Customizable Configuration Parameters
• CONFIGURE - SHOW ALL
• New SHOW Parameters
• Channel Allocation
• Persistent Channel Settings
• NEW_FILE_SPEC Option
• Server Parameter and Control File Autobackup
• SPFILE Option
• Cleaning Up Backups or Copies
• Cleanup Commands
• The Change ... Delete Command
• Force Option of DELETE Command
• Performing Trial Recovery
• REPORT Command
• REPORT Need Backup and Obsolete

Commands

• The LIST Command
• Additional LIST Options
• Recovery Manager Data Dictionary Views:
• Reserved words
• Oracle9i Recommended Syntax

Backup and Restore Workshop

• Types of RMAN Backups
• Automatic Tags for Backups
• RMAN Backup Integrity Check
• Perform a Full Database Consistent Backup
• Crosschecking the Backup
• Retention Policies
• Retention Policy - Removal of Obsolete Backup Copies
• REPORT OBSOLETE Example
• DELETE OBSOLUTE Example
• RMAN Restore Capabilities
• Restore Command
• Restoring a Database
• Restoring the Database to a New Location
• Restoring the Database to a New Location without a Catalog
• Restoring with a RAC Environment

Backup and Recovery Workshop

• Create a Stored Script
• Backup a Datafile
• Making Backups of Tablespaces
• Views to Determine if Recovery is Needed
• Recover a Tablespace that is Accessible
• Recover an Inaccessible Tablespace

Backup and Recovery Workshop Part II

• Backup Archived Redo Logs
• Backups of Archived Logs Needing Backup
• The Backup Archivelog ... Delete Input Command
• Managing Space When Restoring Archived Logs
• Backing up Archived Redo Logs Enhancements
• Restore Archived Redo Logs
• Archive Log Features
• Catalog Operating System Backups
• Image Copies
• Incomplete Recovery Using Until Time
• Incomplete Recovery Using SCN

Introduction to Oracle Net Services

• What is Oracle Net Services?
• Oracle Net Services Fundamentals
• Oracle Net Services Terminology
• Oracle Net Services Configurations
• The Dedicated Server
• The Shared Server Option
• Connection Manager (CMAN)
• The Communication Stack
• The OSI Communication Stack Model
• Standard Communication Stack
• Internet Inter-ORB Protocol (IIOP) Stack
• The Java Database Connectivity Stack
• Oracle JDBC Thin Client
• The Oracle JDBC Thin Client Stack 1
• The JDBC Server
• Name Resolution
• Oracle Net Services Management and Configuration

Server Side Configuration

• Oracle Network Terminology
• The Role of the Listener
• Server Processes
• Different Types of Listeners
• The LISTENER.ORA File
• LISTENER.ORA File Format
• Section One, The Header Section:
• Section Two, The Listener Address
• Section three, the SID_LIST
• Section Four, The Control Parameters
• The TNSNAMES.ORA File
• The Automatic Service Registration Feature
• Methods for Configuring the LISTENER.ORA File
• The LSNRCTL Utility
• LSNRCTL Operational Commands
• LSNRCTL Modifiers
• LSNRCTL Informational Commands
• LSNRCTL Command Utility Operational Commands
• Troubleshooting the Listener
• Error Messages
• ORACLE NET SERVICES Log Files
• ORACLE NET SERVICES Trace Files
• Trace Assistant
• Other Problem Solving Techniques

Oracle Net Services Client Configuration

• Terminology
• Client Configuration Files
• Configuration Files: TNSNAMES.ORA
• Configuration Files: SQLNET.ORA
• Installing Net Services on the Client
• Troubleshooting

The Shared Server Architecture

• The Shared Server Architecture
• Server Processes Review
• Program Global Areas
• User Global Area
• Dedicated Server Architecture (Review)
• What is the Shared Server Architecture?
• Shared Server Architecture
• The Shared Server Architecture
• Dispatcher Processes
• The Listener Process
• Dispatcher Syntax
• Dispatcher Option Definitions
• Setting up the Shared Server
• Configuring the Shared Server Architecture
• Connection Pooling
• Setting up Connection Pooling
• Dispatcher Definitions
• Client Configurations
• Processing Requests and Results
• Deprecated Parameters
• Shared Server Parameters
• Data Dictionary Views