177.  Oracle10g New Features for Administrators

Rev. 1.1.2

 

This class will introduce the experienced Oracle DBA to the new features contained in Oracle Database 10g. As students are introduced to the various new features, labs will reinforce the material presented in this course. Once this course is completed, the student will have gained the foundational knowledge to administer Oracle10g databases.

 

LEARNING OBJECTIVES

 

·         Understand the differences between Oracle 9i and 10g

·         Gain a working knowledge of database administration for Oracle 10g databases

·         Set up and configure Oracle 10g databases

·         Administer users and handle security issues

·         Monitor and tune Oracle 10g databases

 

Course Duration: 5 days

 

Audience: Database administrators

 

Prerequisite: A basic understanding of database administration for Oracle 9i databases

 

1.      Course Introduction

Course Objectives

Course Overview

Using the Workbook

Suggested References

 

2.      Migrating to Oracle10g

The Different Editions of Oracle Database 10g

Oracle Database Installation New Features - . CD Contents

The SYSAUX Tablespace

10g Upgrade Paths and Methods

Database Upgrade Assistant (DBUA)

Manual Upgrade

Preparing to Upgrade to Oracle10g

Rolling Back to a Previous Version

Steps to Roll Back

 

3.      Database Extraction and Loading New Features

Cross-Platform Transportable Tablespaces

Transporting a Tablespace Between Platforms

Converting the Byte Format

New Oracle Database External Table Features

External Table Creation in Parallel

The Oracle Data Pump Architecture

Configuring The Database To Use Oracle Data Pump

Using The expdp and impdp Tools

Import and Export Via The Oracle Data Pump

Using Interactive Mode

4.      Tuning and Management

Basic Initialization Parameters

Automatic Shared Memory Management

Configuring Automatic Shared Memory

FLUSHING the Buffer Cache

Automatic Statistics Collection

Automatic Undo Management and Tuning

Automatic Checkpoint Tuning

Using DBMS_SQLTUNE Package

SQL Trace and DBMS_MONITOR New Features

 

5.      Oracle10g Advisor Framework and Advisors

The Advisor Framework

Automatic Workload Repository

Managing AWR

Automatic Database Diagnostic Monitor

SQL Tuning Advisor

SQL Access Advisor

Segment Advisor

Undo Advisor

 

6.      Oracle10g Database Control

Oracle Database Control Home Page

Starting And Using Database Control

Database Control Performance Page

Administration and Maintenance Home Pages

Determine Which Database Features In Use

MMON

Configuring Server-Generated and Threshold-Generated Alerts

Automating Routine Administrative Tasks

 

7.      New and Updated SQL Features

Partitioned Outer Joins

New CONNECT BY Functionality

The Model Clause (in Narrow Calculations)

MERGE Command New Features

Aggregates in the Returning Clause

Restrictions of Returning Clause Aggregates

TABLE Function New Features

Regular Expressions

REGEXP_LIKE and REGEXP_INSTR

REGEXP_SUBSTR and REGEXP_REPLACE

More on Regular Expressions

The SAMPLE Clause

Case- and Accent-Insensitive Queries

 

8.      Server Management New Features

Cross-Platform Transportable Tablespaces

Default User Tablespaces

Rename Tablespaces

Bigfile Tablespaces

Dropping a Database

Shared Server Changes

Temporary Tablespace Groups

 

9.      New Segment Features

New Table Monitoring Behaviors

Hash-Partitioned Global Indexes

Skipping Unusable Indexes

New Partitioned Index Management Features

Sorted Hash Clusters

Shrinking Segments Online

Estimating Segment Resource Requirements

Materialized Views New Features

 

10. Security New Features

Fine-Grained Auditing New Features

Virtual Private Database New Features

Column-level VPD

The DBMS_ CRYPTO Package

 

11. Availability and Recovery - RMAN

Introducing the Flash Recovery Area

Configuring and Managing the Flash Recovery Area

RMAN Flash Backup and Recovery

RMAN Backup Compression

Simplified Recovery Through Resetlogs

Incremental Backup Changes

DROP DATABASE and UNREGISTER DATABASE

CATALOG and UNCATALOG Commands

OTHER RMAN New Features

 

12. Availability and Recovery - Backup, Recovery and Flashback Database

New Oracle10g Backup Commands

Guaranteed Undo Retention

Flashback Database

FLASHBACK TABLE

Flashback Versions

Flashback Transactions

Flashback Drop

 

13. The Scheduler

Scheduler - Introduction

Architecture of the Scheduler

Job Class and Windows

Scheduler-Related Data Dictionary Views

Repeat Intervals for a Job

Privileges needed for Scheduler Components

Creating Jobs, Programs and Schedules

Administering Jobs, Programs and Schedules

 

14. Automatic Storage Management

ASM Architecture

ASM Configuration

ASM Templates, Directories and Aliases

Administration of ASM Directories

ASM-Related Data Dictionary Views

ASM Disk Groups and Failure Groups

Configuring Disks with ASM

Rebalancing Disks with ASM

POWER Clause

Mirroring Disks with ASM

Creating the ASM Instance

Managing the ASM Instance

Migrating to ASM - RMAN

Disk-Based Migration of a Database to ASM

Migrating a Database to ASM Using Tape Backups

 

15. SQL*Plus New Features

Improvements to the DESCRIBE and SPOOL Commands

Changes to SQL*Plus Profile Files

SQL*Plus Variable Substitution

Other Enhancements in SQL*Plus

Drop Database Command

 

16. Application-Related New Features

New and Improved Bulk Bind Features

User-Specified Quote Characters

LOB-Related New Features and Enhancements

DBMS_PROFILER New Features

The UTL_COMPRESS Package

The UTL_MAIL Package

The DBMS_FILE_TRANSFER Package

Oracle10g New Supplied Packages

New IEEE Floating point types

 

17. PL/SQL New Features

Natively-Compiled PL/SQL Improvements

Compile-Time Warnings

FORALL - Support for Non-Consecutive Indexes in Collections

Nested Table New Improvements

Improved Overloading with Numeric Types

Other PL/SQL New Features

 

18. Networking New Features

New Connect String Format

Buffer Space I/O Configurations

Tuning SEND_BUF_SIZE and RECV_BUF_SIZE Values

New Protocol Support

SDP Protocol Pre-Installation Requirements

Connection Manager New Features

Configuring Connection Manager

 

19. Other Oracle10g New Features

Oracle Net Instant Client

Transaction Monitoring

End-to-End Application Tracing

Log Miner New Features

Resumable Space Management New Features

RBO Obsolete in Oracle Database 10g

Database Resource Manager - Consumer Groups

Database Resource Manager - Setting Idle Timeouts

 

Appendix A.  Real Application Clusters Enhancements

 

Integrated Clusterware Utility

Oracle Cluster Registry

Automatic Workload Management

Workload Management

Single System Image Management

Fast Connection Failover

Zero Downtime Patching and Improved Diagnostics

 

Appendix B.  Oracle10g New Data Dictionary Views

 

New Data Dictionary Views

New Dynamic Performance Views

New Columns in Static Data Dictionary Tables

New Columns in Dynamic Performance Views

 

Appendix C.  Oracle10g New Supplied Packages

 

 

System Requirements

 

Course exercises require Oracle 10g running on a Microsoft Windows, Linux, or multi-user UNIX system.  See the appropriate course Setup Guide for details.

 

A good minimal hardware profile for this course would have a Pentium 500-MHz or equivalent CPU, 512 MB of RAM, and at least 4 GB of free disk space for the Oracle installation,.