Oracle Database 11g DBA - Part II — A 5-Day Course

 Open Public Course Closed In-House Course

Synopsis

This course introduces the delegate to advanced administration tools. Participants will learn how to plan and implement a backup and recovery strategy using Recovery Manager. They will also learn how to control database resources using the Resource Manager; how to automate administration tasks with the Scheduler and how to carry out performance monitoring and tuning.

This course is also suitable for delegates who need to be able to improve the database performance.

Course Objectives

This course introduces delegates to the main concepts of Oracle backup and recovery. They will learn how to plan and implement a backup and recovery strategy using RMAN. Delegates will also learn to manage memory effectively, carry out some evaluation and tuning exercises, control database resources using Resource Manager, automate tasks with the Scheduler and use Automatic Storage Management (ASM).

The delegate will practise:

• Planning a backup strategy and a disaster recovery strategy
• Altering a database to ARCHIVELOG and NOARCHIVELOG modes
• Performing online and offline backups
• Performing complete and incomplete database recovery
• Recovering a database from non-critical losses
• Performing logical backups with the export and import utilities
• Using Recovery Manager to perform backups and restorations
• Using Flashback technology to recover a database
• Reviewing database logs for diagnostic purposes
• Using Database advisors and views to monitor and improve database performance
• Controlling database resources using Resource Manager
• Automating management tasks with the scheduler

Suitable For

This course is intended for:

• Database administrators and technical support staff who are required to plan and implement database backup and recovery strategies for Oracle 11g databases.
• Administrators will also learn about the tools available to monitor database performance and the steps that can be taken to improve database performance.

Prerequisites

A working knowledge of Oracle SQL and Oracle Database Administration. This can be obtained by attendance on the pre-requisite courses:

• Oracle SQL
• Oracle Database 11g DBA - Part I
• Knowledge of PL/SQL would be beneficial

Publicly scheduled dates, locations, and prices

Outline Course Contents

Administration and Course Materials

• Course Structure and Agenda
• Delegate and Trainer Introductions

Backup and Recovery Considerations

• Oracle Suggested Backup Strategy
• Plan and Implement a Backup and Recovery Strategy
• Define a Backup Strategy
• Define a Disaster Recovery Plan
• The Advantages and Disadvantages of Different Backup Methods
• Test a Backup and Recovery Plan

Oracle Recovery Structures and Processes

• Architectural Components of Backup and Restore
• Redo Logs, Checkpoints and Archives
• Multiplexing Control Files and Redo Logs
• Types of Database Failure

Memory Management

• Automatic Shared Memory Management
• PGA Memory Management
• Automatic Memory Management
• The Memory Advisor
• Memory Allocation Advisors
• Memory Management Data Dictionary Views

Using ARCHIVELOG and NOARCHIVELOG Modes

• The Archive Modes of a Database
• Altering the Archive Mode of a Database
• Configuring Multiple Archival Destinations
• Configuring Multiple ARCH Processes
• Data Dictionary Tables for Archiving

Physical Backup without RMAN

• Closed and Open Backups
• Performing Offline Closed Backups
• Performing Online Open Backups
• Backup in Logging and Nologging Modes
• Control File Backups
• Read Only Tablespaces
• Data Dictionary Views for Backup Operations

Complete Database Recovery without RMAN

• Recovery in NOARCHIVELOG Mode
• Recovery in ARCHIVELOG Mode
• Recovery Data Dictionary Views

Incomplete Recovery with Archiving

• When to use Incomplete Recovery
• Performing an incomplete Recovery
• Recover from Losing Current and Inactive Noarchived Redo Log Files

Additional Recovery Issues

• Minimizing Downtime
• Rapid Crash Recovery
• Checkpointing and Cache Recovery
• Parallel Recovery
• Trial Recovery
• Recover a Database with Missing Data Files
• Rebuilding Lost or Damaged Control Files
• Recovering Read-Only Tablespaces
• Recover from Loss of Redo Log Files
• Redo from the Loss of an Archived Redo Log File
• Recover Non Database Files

Troubleshooting Utilities

• Database Synchronization
• Using Log and Trace Files to Diagnose Problems
• Detecting Block Corruption
• DB_BLOCK_CHECKSUM
• DB_BLOCK_CHECKING
• The ANALYZE Command
• The DBMS_REPAIR Package
• Use DBVERIFY to Validate the Database File Structure
• Block Media Recovery

Recovery Manager Overview

• Overview of Recovery Manager
• RMAN Features and Components
• Connect to RMAN
• Start Up and Shut Down a Target Database Using RMAN

Create a Recovery Catalog

• Recovery Catalog Overview
• Create a Recovery Catalog
• Use a Flashback Recovery Area
• Register and Unregister a Database
• Control File Information
• RESYNC CATALOG command
• Virtual Private Catalogs
• Merge Catalogs
• Backup a Recovery Catalog

Backing up with RMAN

• Channel Allocation
• The Configure Channel Command
• Types of RMAN Backup
• Recovery Manager Commands
• Performing Backups with RMAN
• The Backup Command
• The Copy Command
• Backup File Image Copies
• Perform Full, Incremental and Cumulative Backups
• Use Data Dictionary Tables

Maintain a Recovery Catalog

• Reset Database Command
• Change the Availability Status of Backups and Copies
• Catalog Backups Made with Operating System Commands
• Generate Reports and Lists
• Configuration Values
• Cross Check Backups and Copies
• Delete Backups
• Drop Database

Restore and Recover with RMAN

• Overview of Restore
• The Restore Command
• Restore a Database in Noarchivelog Mode
• Restore and Recover a Database in Archivelog Mode
• Incomplete Recovery using Until Time and Until Sequence
• Restore and Recover a Tablespace
• Restore and Recover Datafiles
• Recover a Datafile Not Backed Up
• Apply Incremental Backups to Restore Datafile Image Copies
• The Switch command

Create a Duplicate Database with RMAN

• Overview of Database Duplication
• Database Duplication
• Duplication Options
• Create Files for the Duplicate Database
• Prepare an Auxiliary Instance for Duplication
• Create a Duplicate Database
• Clone a Running Database without a Backup

Tablespace Point-in-Time Recovery

• RMAN TSPITR Terminology and Concepts
• Overview of TSPITR
• Determine Recovery Set Tablespaces
• Perform RMAN Tablespace Point-in-Time Recovery
• Troubleshoot Tablespace Point-in-Time Recovery

Monitor, Tune and Troubleshoot Backup & Recovery

• Recovery Manager Tuning Overview
• Tuning RMAN Backup Performance
• Instance Recovery Performance Tuning
• RMAN Messages
• Troubleshoot RMAN
• RMAN Detected Block Corruptions

Flashback

• Configure and Use Flashback Database
• Monitor Flashback Database
• Flash Recovery Area Space Usage
• Flashback Query
• Retrieve Row History with Flashback Versions Query
• Audit or Recover from Transactions with Flashback Transactions Query
• Flashback Table
• Recover Dropped Tables with Flashback Drop
• The Recycle Bin
• Recover Tables to a Point in Time with Flashback Table
• Flashback Database
• Flashback Restore Points
• Flashback Transaction Backout
• Flashback Data Archive

Automate Tasks with the Scheduler

• Introduction to the Scheduler
• Scheduler Related Parameters
• Access Rights
• Create a Program
• Drop a Program
• Create a Schedule
• Create a Job
• Job Classes
• Resource Windows
• Window Group
• Events and Chains
• Monitoring and Logging

Segment Advisor

• Tablespace Monitoring
• Segment Management Enhancements
• Segment Advisor
• Segment Space Estimation
• Online Segment Shrink
• Proactive Tablespace Management

Database Performance Tuning

• Tuning Overview
• Resolving Performance Issues
• Oracle Tuning Principles
• Items to Evaluate
• Tips for Avoiding Problematic Queries
• Where to Look
• Plan a Routine Monitoring Regime
• Automatic Workload Repository
• Application and SQL Tuning
• SQL Tuning Advisor
• SQL Access Adviso0r

Automatic Storage Management

• Overview of ASM
• ASM Instance Creation
• SYSASM Role
• Administer ASM Disk Groups
• ASM Files
• ASM Startup and Shutdown
• ASM Data Dictionary Views
• Migrate a Database to ASM Using RMAN
• The ASM Command Line Tool

Managing Resources with Resource Manager

• Overview of the Database Resource Manager
• Create Resource Plans
• Create Resource Consumer Groups
• Create Resource Plan Directives
• Control Resources by User Groups
• Specify an Instance Plan
• Switch Consumer Group for a Connected User
• Set Idle Timeout Values
• Revert to Original Consumer Group
• Automatic Session Assignment
• Monitor the Resource Manager
• Data Dictionary Views

Globalization

• Overview of globalization
• Language encoding schemes
• NLS character sets
• NLS language and territory definitions
• NLS data dictionary views
• Multi language linguistic sorts
• Date times with timezones
• Display formats
• Date functions
• Case and accent insensitive sorts
• Course summary
• Course assessment