Installing and Setting Up the Warehouse Builder Environment
What Is Oracle Warehouse Builder?
Basic Process Flow of Design and Deployment
Oracle Warehouse Builder Licensing and Connectivity Options
Installing Oracle Warehouse Builder 11.2
OWBSYS Schema
Using OWB 11.2 with Database 10g R2
Using the Repository Assistant to Manage Workspaces
Supported operating systems (OS), sources, targets, and optional components
Getting Started with Warehouse Builder
Logging In to OWB Design Center
Overview of the Design Center
OWB Projects
Overview of Objects within a Project
Overview of Objects within an Oracle Module
Organizing Metadata Using Foldering
Locations Navigator and Global Navigator panels
Setting Projects Preferences: Recent Logons
Understanding the Warehouse Builder Architecture
Warehouse Builder Development Cycle
Overview of the Architecture for Design, Deployment, Execution
Overview of Configurations, Control Centers, and Locations
Creating Target Schemas
Registering DB User as an OWB User
Roles and Privileges of Warehouse Builder Users
Registering an Oracle Workflow User
Defining Source Metadata
Data warehouse implementation: Typical steps
Difference Between Obtaining Relational and Flat File Source Metadata
Creating Flat File Module
Sampling Simple Delimited File

Sampling Multi-record Flat File
Creating an Oracle Module
Selecting the Tables for Import
Defining ETL Mappings for Staging Data
Purpose of a Staging Area
Define OWB Mappings
Mapping Editor Interface: Grouping, Ungrouping, and Spotlighting
Creating External Tables
Create and Bind process
Levels of Synchronizing Changes
Using the Automapper in the Mapping Editor
Set loading type and target load ordering
Using the Data Transformation Operators
Component Palette
Using a Joiner
Lookup Operator: Handling Multiple Match Rows
Using the Subquery Filter Operator
Using the Set, Sequence, and Splitter Operators
Pivot and Unpivot Operators
Using the Aggregator, Constant, Transformation, and Pre/Post Mapping Operators
Deploying and Executing in Projects Navigator Panel
Cleansing and Match-Merging Name and Address Data
Integrating Data Quality into ETL
Name and Address Data Cleansing
Name and Address Server
Name and Address Software Providers
Settings in the Name and Address Operator
Reviewing a Name and Address Mapping
Consolidating Data Using the Match Merge Operator
Using the Match Merge Operator in a Mapping
Using Process Flows
Process Flow Concepts
Creating a Process Flow Module, a Process Flow Package and a Process Flow
Types of Activities: Fork, And, Mapping, End Activity
Creating Transitions Between Activities
Some More Activities: Manual, SQLPLUS, Email
Generating the Process Flow Package
Deploying and Reporting on ETL Jobs
Logical Versus Physical Implementation
Setting Object Configuration
Deployment Concepts
Invoking the Control Center Manager
Deploy Options and Preferences
Repository Browser
Starting OWB Browser Listener and the Repository Browser
Browsing Design Center and Control Center Reports

Using the Mapping Debugger
Overview of the Mapping Debugger
Initializing a Mapping Debugging Session
Preparing the testing environment and test data
Setting breakpoints and watch points
Evaluating the flow of data to detect mapping errors
Enhancing ETL Performance
Performance Tuning at Various Levels
Performance-Related Parameters in ETL Design
Configuring Mappings for Operating Modes, DML Error Logging, Commit Control, and Default Audit Levels
Enabling Partition Exchange Loading (PEL) for Targets
Performance-Related Parameters in Schema Design
Configuring Indexes, Partitions, Constraints
Enabling Parallelism and Parallel DML
Setting Tablespace Properties and Gathering Schema Statistics
Managing Backups, Development Changes, and Security
Overview of Metadata Loader Utilities (MDL)
Managing Metadata Changes by Using Snapshots
Using Change Manager
Version Management of Design Objects
Graphical UI for Security Management
Object-Level Security
Setting Security Parameters
Integrating with Oracle Business Intelligence Enterprise Edition (OBI EE)
Business Justification: Tools Integration
Integrating with OBI EE and OBI SE
Transferring BI Metadata to OBI EE Server
Setting Up the UDML File Location
Deriving the BI Metadata (OBI EE)
Deploying the BI Module
Converting the UDML File for OBI EE
Oracle BI Admin and Answers Tool
Administrative Tasks in Warehouse Builder
Enterprise ETL License Extends Core In-Database ETL
Multiple Named Configurations: Why and How
Using Multiple Named Configurations
Using Configuration Templates
Steps for Setting Up OWB in a RAC Environment
Creating an OWB Schedule
Managing Metadata
Using Lineage and Impact Analysis Diagrams
Invoking Lineage and Impact Analysis
Using the Change Propagation Dialog
User-Defined Properties, Icons, and Objects
Using Pluggable Mappings
Advanced Activity Types in Process Flows
Native Relational Object Support

Heterogeneous Predefined SQL Transformations
Accessing Non-Oracle Sources
Extensible Framework of OWB 11g Release 2
Benefits of Extensible Code Templates
Location of Seeded Code Templates
Creating New Code Templates
Defining New Integration Platforms in OWB
Designing Mappings with the Oracle Data Integration Enterprise Edition License
Traditional Versus Code Template (CT) Mappings
Execution Units in a CT Mapping
Execution View Versus Logical View
Assigning a Code Template to an Execution Unit
Convert a Classic Mapping to a CT Mapping That Utilizes Data Pump
CT Mappings Deploy to Control Center Agents
Right-Time Data Warehousing with OWB
What Is Meant by Real-Time Data Warehousing
What Refresh Frequency Does OWB Support
Building a Trickle Feed Mapping
Using Advanced Queues in Trickle Feed Mappings
Using CDC Code Templates in Mappings for Change Data Capture
Starting CDC Capture Process
Defining Relational Models
Defining Dimensions Using Wizards and Editors
Defining Dimension Attributes, Levels, and Hierarchies
Binding Dimension Attributes to the Implementation Table
Using the Create Time Dimension Wizard
Defining a Cube
Specifying a Cube's Attributes and Measures
Designing Mappings Using Relational Dimensions and Cubes
More Relational Dimensional Modeling
Initial Versus Incremental Data Warehouse Loads
Updating Data and Metadata
Capturing Changed Data for Refresh
Setting Loading Properties
Choosing the DML Load Type
How OWB Manages Orphans
Support for Cube-Organized Materialized Views
Creating a Type 2 Slowly Changing Dimension
Modeling Multidimensional OLAP Dimensions and Cubes
What Is OLAP
Multidimensional Data Types
Analytic Workspace
Dimensional Modeling Using OWB
OWB Calculated Measures
OWB Calculated Measures

 

 

Overview of Oracle Database Administration
Structure of a Relational Database
SQL
Components of an Oracle Instance and Database
Basic Tasks of an Oracle DBA
Tools Used to Administer an Oracle Database
Installing Oracle Database and Creating the Database
Using the Oracle Universal Installer to Install the Oracle Database Software
Using DBCA to Create a Database
Using DBCA to Manage Templates
Using Oracle Enterprise Manager Database Control and SQL*Plus

Overview of Administering the Database Using Enterprise Manager
Starting the Enterprise Manager Console Process
Granting Enterprise Manager Administrative Privileges
Setting Enterprise Manager Preferred Credentials
Using SQL*Plus to Access Your Database

Configuring the Oracle Network Environment
Tools for Configuring and Managing the Oracle Network
Using the Enterprise Manager Net Services Administration Page
Managing the Oracle Net Listener with Enterprise Manager
Using the Listener Control Utility
Oracle Net Naming Methods
Using Enterprise Manager to Configure Local Naming
Using Oracle Net Manager to Configure Local Naming

Managing the Oracle Instance
Oracle Instance Management
Starting the Instance and Opening the Database
Viewing and Modifying Initialization Parameters
Managing Memory Components
Viewing the Alert Log

Managing Database Storage Structures
Oracle Database Structures
Multiplexing the Redo Log
Enlarging the Database
Creating, Modifying, and Dropping Tablespaces
Reclaiming Space in Your Tablespace
Managing Undo Data
Using the Undo Advisor to Compute the Minimum Undo Tablespace Size

Administering Users and Security
Overview of Users and Security
Creating, Modifying, and Dropping Users
Unlocking a User Account and Resetting the Password
Setting the Password Policy
Administering Privileges and Roles

Managing Schema Objects
Creating, Modifying, and Dropping Tables
Understanding Data Integrity and Defining Constraints
Viewing the Contents of a Table
Creating and Dropping Indexes
Managing Views
Managing Program Code Stored in the Database
Managing Access to Schema Objects
Loading Data

Performing Backup and Recovery
Backup, Restore, and Recovery Concepts
Configuring the Database for Backup and Recovery
Configuring Backup Settings
Backing Up the Database
Using the Oracle-Suggested Backup Strategy
Performing Backup Maintenance Tasks
Using Oracle Advised Recovery to Recover the Database
Performing Flashback Table and Flashback Drop
Monitoring the Database and Using the Advisors
Monitoring General Database State, Workload, and Performance
Setting Metric Thresholds
Setting Up Direct Alert Notification
Viewing ADDM Performance Analysis and Responding to Findings
Using the SQL Tuning and SQL Access Advisors
Configuring the Automatic SQL Tuning Advisor
Viewing Automatic SQL Tuning Results
Investigating, Reporting, and Resolving Problems
Using the Enterprise Manager Support Workbench
Workflow for Investigating, Reporting, and Resolving a Problem
Performing the Tasks to Investigate and Report a Critical Error
Managing Oracle Database Software
Understanding Software Management and Patch Releases
Patching Oracle Database Software
Using the Database Upgrade Assistant to Upgrade the Database

 

 

 

Introduction
Development Tools
Oracle SQL Developer
Enterprise Manager
Sample Schemas used

 

Data Warehouse Design: Overview
What is a Data Warehouse?
Characteristics of a Data Warehouse
Comparing OLTP and Data Warehouses
Data Warehouse Architectures
Data Warehouse Design
Data Warehouse objects
Data Warehouse Schemas
Star Transformation

 

Partitioning Basics
Partitioned Tables and Indexes
Partitioning Methods
Partitioning Types
Partition Pruning and Star queries

 

Parallelism Concepts
Operations That Can Be Parallelized
How Parallel Execution Works
Degree of Parallelism
Parallel execution plan

 

Parallel Operations in Data Warehouses
Parallel Query
Parallel DDL
Parallel DML
Tuning Parameters for Parallel Execution
Balancing the Workload

 

ETL: Extraction and Transportation
Extraction Methods
Capturing Data With Change Data Capture
Sources and Modes of Change Data Capture
Publish and Subscribe Model: The Publisher and the Subscriber

Synchronous and Asynchronous CDC
Asynchronous AutoLog Mode and Asynchronous HotLog Mode
Transportation in a Data Warehouse
Transportable Tablespaces

 

ETL: Loading
Loading Mechanisms
Applications of External Tables
Defining external tables with SQL*Loader
Populating external tables with Data Pump
Other Loading Methods

 

ETL: Transformation
Data transformation
Transformation Mechanisms
Transformation Using SQL
Table Functions
DML error logging

 

Materialized Views
The Need for Summary Management
Types of Materialized Views
Using Materialized Views for Summary Management
Materialized View Dictionary views

 

Refreshing Materialized Views
Refresh Options
Refresh Modes
Conditions That Effect Possibility of Fast Refresh
Materialized View Logs
Partition Change Tracking (PCT) Refresh
Refresh Performance Improvements

 

Working With Dimensions
What Are Dimensions
Creating Dimensions and Hierarchies
Dimensions and Privileges
Dimension Restrictions
Verifying Relationships in a Dimension
Dimension Invalidation

 

Query Rewrite
Query Rewrite: Overview
What Can be Rewritten
Conditions Required for Oracle to Rewrite a Query
Query Rewrite guidelines
Setting Initialization Parameters for Query Rewrite
Query Rewrite Methods
Partition Change Tracking (PCT) and Query Rewrite
Query Rewrite Enhancement to Support Queries Containing Inline Views

 

SQL Access Advisor: Usage Model
Setting Initial Options
Specifying the Workload Source
Recommendation Options
Schedule and Review
PL/SQL Procedure Flow
Tuning Materialized Views for Fast Refresh and Query Rewrite
Table Compression and Resumable Sessions

 

Exploring the Oracle Database Architecture
Oracle Database Architecture Overview
Interacting with an Oracle Database
Process Architecture
Database Storage Architecture
Logical and Physical Database Structures
Tablespaces and Data Files
SYSTEM and SYSAUX Tablespaces
Segments, Extents, and Blocks

 

Preparing the Database Environment
Tasks of an Oracle Database Administrator
Tools Used to Administer an Oracle Database
Installation: System Requirements
Optimal Flexible Architecture (OFA)
Setting Environment Variables
Oracle Universal Installer (OUI)
Database Configuration Options
Advanced Installation Options

 

Creating an Oracle Database
Planning the Database
Configuring the Listener
Using the DBCA to Create a Database
Password Management
Creating a Database Design Template
Using the DBCA to Delete a Database
Using DBCA For Additional Tasks

 

Managing the Oracle Instance
Management Framework
Oracle Enterprise Manager
Initialization Parameter
Database Startup and Shutdown
Shutdown Modes
Viewing the Alert History
Using Trace Files
Data Dictionary: Overview

 

Configuring the Oracle Network Environment
Oracle Net Services & Listener
Establishing Net Connections
Tools for Configuring and Managing the Oracle Network

Net Services Administration Pages
Naming Methods
Directory Naming
SGA and PGA
When Not to Use a Shared Server

 

Managing Database Storage Structures
Storage Structures
How Table Data Is Stored
Anatomy of a Database Block
Space Management in Tablespaces
Tablespaces in the Preconfigured Database
Actions with Tablespaces
Oracle Managed Files (OMF)
ASM: Concepts

 

Administering User Security
Database User Accounts
Predefined Accounts: SYS and SYSTEM
Administrator Authentication
Benefits of Roles
Predefined Roles
Implementing Password Security Features
Assigning Quota to Users

 

Managing Schema Objects
What Is a Schema?
Accessing Schema Objects
Specifying Data Types in Tables
Creating and Modifying Tables
Understanding Data Integrity
Indexes & Views
Sequences
Temporary Tables

 

Managing Data and Concurrency
Manipulating Data Through SQL
PL/SQL
Administering PL/SQL Objects
Functions, Procedures, Packages & Triggers
Data Concurrency
Enqueue Mechanism
Resolving Lock Conflicts Using SQL
Deadlocks

 

Managing Undo Data
Data Manipulation
Transactions and Undo Data
Undo Data Versus Redo Data
Configuring Undo Retention
Guaranteeing Undo Retention
Using the Undo Advisor

Viewing System Activity

 

Implementing Oracle Database Security
Industry Security Requirements
Principle of Least Privilege
Monitoring for Compliance
Value-Based Auditing
Fine-Grained Auditing
DBA Auditing
Security Updates
Applying Security Patches

 

Database Maintenance
Introducing Terminology
Optimizer Statistics
Automatic Workload Repository (AWR)
Automatic Database Diagnostic Monitor (ADDM)
Advisory Framework
Automated Maintenance Tasks
Server-Generated Alerts
Reacting to Alerts

 

Performance Management
Performance Monitoring
Managing Memory Components
Enabling Automatic Memory Management (AMM)
Automatic Shared Memory Advisor
Using Memory Advisors
Dynamic Performance Statistics
Troubleshooting and Tuning Views
Invalid and Unusable Objects

 

Backup and Recovery Concepts
Part of Your Job
Statement Failure
User Error
Understanding Instance Recovery
Phases of Instance Recovery
Using the MTTR Advisor
Media Failure
Archive Log Files

 

Performing Database Backups
Backup Solutions: Overview
Oracle Secure Backup
User-Managed Backup
Terminology
Recovery Manager (RMAN)
Configuring Backup Settings
Backing Up the Control File to a Trace File
Monitoring the Flash Recovery Area

 

Performing Database Recovery
Opening a Database
Data Recovery Advisor
Loss of a Control File
Loss of a Redo Log File
Data Recovery Advisor
Data Failures
Listing Data Failures
Data Recovery Advisor Views

 

Moving Data
Moving Data: General Architecture
Directory Object: Overview
SQL*Loader: Overview
Data Pump: Overview
Performance Initialization Parameters
Using Enterprise Manager to Monitor Data Pump Jobs
Data Dictionary

 

Enhancing Database Capabilities
Using EM Support Workbench
Create a Service Request
Package and upload diagnostic data to Oracle Support
Track the SR and Implement Repairs
Incident Packaging Configuration
Working with Oracle Support
MetaLink Integration
Managing Patches

 

 

 

Database Architecture and ASM
Oracle Database Architecture Overview
ASM General Architecture
Creating an ASM Instance
Using Enterprise Manager to Manage ASM Users
ASM Storage Concepts
The ASMCMD Utility

ASM Scalability and Performance

 

Configuring for Recoverability
Purpose of Backup and Recovery Functionality
Typical Backup and Recovery Tasks
Oracle Backup and Recovery Solutions
Using Recovery Manager (RMAN)
Using a Flash Recovery Area
Flash Recovery Area Space Management

 

Using the RMAN Recovery Catalog
RMAN Repository Data Storage: Comparison of Options
Storing Information in the Recovery Catalog
Registering a Database in the Recovery Catalog
Recovery Catalog Resynchronization: Concepts
Using RMAN Stored Scripts
Backing Up and Recovering the Recovery Catalog
Using a Virtual Private Catalog

 

 

Configuring Backup Specifications
Using RMAN to Create Backups
Configuring Persistent Settings for RMAN
Using Enterprise Manager to Configure RMAN Settings
Control File Auto-backups
Managing Persistent Settings
Configuring Devices for Backup
Configuring and Allocating Channels for Use in Backups
Configuring Backup Optimization

 

Creating Backups
Creating an Oracle-Suggested Backup
Creating Backup Sets & Image Copies
Creating a Whole Database Backup
RMAN Backup Types
Monitoring Block Change Tracking
Creating Duplex Backup Sets
Archival Backups: Concepts
Encrypting Backups

 

Performing User-Managed Backup and Recovery
Restoring and Recovering
Causes of File Loss
Critical Versus Non-critical
Recovering from a TEMPFILE Loss
Re-creating Indexes
Recovering from a Lost Index Tablespace
Authentication Methods for Database Administrators
Recovering a Read-Only Tablespace

 

Using RMAN to Perform Recovery
Using RMAN RESTORE and RECOVER Commands
Performing Recovery Using Enterprise Manager
Recovery Using Incrementally Updated Backups
Perform a Fast Switch to Image Copies
Restoring and Recovering the Database on a New Host
Performing Disaster Recovery

 

Using RMAN to Duplicate a Database
Using RMAN to Create a Duplicate Database
Creating a Duplicate Database
Creating an Initialization Parameter File for the Auxiliary Instance
Allocating Auxiliary Channels
Understanding the RMAN Duplication Operation
Using EM to Clone a Database
Cloning a Running Database

 

Performing Tablespace Point-in-time Recovery
Tablespace Point-in-Time Recovery (TSPITR) Concepts
Tablespace Point-in-Time Recovery: Architecture
Understanding When to Use TSPITR
Determining the Tablespaces for the Recovery Set
Using Enterprise Manager to Perform TSPITR
Understanding TSPITR Processing
Troubleshooting RMAN TSPITR

 

Monitoring and Tuning RMAN
Parallelization of Backup Sets
Monitoring RMAN Sessions
Interpreting RMAN Message Output
Using the DEBUG Option
Tuning RMAN
RMAN Multiplexing
Comparing Synchronous and Asynchronous I/O
Tape Subsystem Performance Rules

 

Using Flashback Technology
Flashback Technology
Transactions and Undo
Guaranteeing Undo Retention
Preparing Your Database for Flashback
Flashback Drop and the Recycle Bin
Using Flashback Technology to Query Data
Possible Workflow
Flashback Transaction Wizard

 

Using Flashback Database
Flashback Table
Enabling Row Movement on a Table
Enabling Row Movement on a Table
Flashback Database Architecture
Configuring Flashback Database
Performing Flashback Database Using EM
Flashback Database Considerations
Flashback Data Archive

 

Diagnosing the Database
Automatic Diagnostic Workflow & Repository
Location for Diagnostic Traces
Viewing the Alert Log Using Enterprise Manager
The Support Workbench and Oracle Configuration Manager
Create a Service Request
Package and upload diagnostic data to Oracle Support
Incident Packaging Configuration
Health Monitor Overview & Data Recovery Advisor

 

Managing Memory
Oracle Memory Structures
Automatic Memory Management Overview
Oracle Database Memory Parameters
Auto Memory Parameter Dependency
Automatic Shared Memory Management: Overview
Automatic PGA Memory Management
Using the Memory Advisor to Size the SGA
Memory Tuning Guidelines for the Library Cache

 

Managing Database Performance
Tuning Activities
Performance Planning & Performance Tuning Methodology
Instance Tuning
Troubleshooting and Tuning Views
Automatic Workload Repository
SQL Tuning & SQL Advisors
Using the SQL Tuning Advisor
SQL Access Advisor: Overview

 

Using the Segment Advisor
Space Management: Overview
Proactive Tablespace Monitoring
Thresholds and Resolving Space Problems
Monitoring Tablespace Space Usage
Shrinking Segments
Space Reclamation with ASSM
Automatic Segment Advisor
Minimum Compatibility Level

 

Managing Resources
Database Resource Manager: Overview

Database Resource Manager Concepts
Accessing Resource Plans
Default Maintenance Resource Manager Plan
Creating Consumer Groups
Resource Allocation Methods for Resource Plans
Resource Allocation Methods for Resource Plans
Monitoring the Resource Manager

 

Automating Tasks with the Scheduler
Simplifying Management Tasks
Key Components and Steps
Creating an Event-Based Schedule
Creating Complex Schedules
Creating Job Chains
Creating Job Chains

 

Administering the Scheduler
Advanced Scheduler Concepts
Job Classes
Windows
Prioritizing Jobs Within a Window
Remote Jobs
Installing the Scheduler Agent
Dictionary Views Supporting Remote Jobs

 

Globalization
Globalization Support Features
What Every DBA Needs to Know
Understanding Unicode
Database Character Sets and National Character Sets
Language- and Territory-Dependent Parameters
Linguistic Searching and Sorting
Case- and Accent-Insensitive Search and Sort
NLS Data Conversion with Oracle Utilities

 

Introduction to Oracle Data Guard
Causes of Data Loss
Oracle Data Guard Architecture
Data Protection Modes

 

Creating a Physical Standby Database by Using SQL and RMAN Commands
Preparing the Primary Database
Creating a Physical Standby Database

 

Overview of the Data Guard Broker
Oracle Data Guard Broker Features
Oracle Data Guard Broker Configurations

Using DGMGRL to Create a Data Guard Broker Configuration
Creating the Broker Configuration
Adding the Standby Database to the Configuration

 

Creating a Physical Standby Database by Using Enterprise Manager Grid Control
Using the Add Standby Database Wizard
Verifying a Configuration
Viewing the Data Guard Configuration Status

Monitoring a Data Guard Broker Configuration
Monitoring the Data Guard Configuration
Verifying the Configuration
Using Enterprise Manager Data Guard Metrics
Viewing Log File Details

 

Managing Data Protection Modes
Determining Which Data Protection Mode to Use
Setting Up Standby Redo Logs
Setting the Data Protection Mode

Optimizing a Data Guard Configuration
Monitoring Configuration Performance
Optimizing Network Configuration
Implementing Cascaded Destinations

 

Using Flashback Database in a Data Guard Configuration
Using Flashback Database and Real-time Apply
Using Flashback Database Instead of Apply Delay
Recovering Data from the Standby Database From a Past Point-in-time

Performing Role Transitions
Understanding Roles in an Oracle Data Guard Configuration
Performing a Switchover to a Physical Standby Database
Performing a Failover to a Physical Standby Database
Re-enabling a Disabled Database After a Role Transition

Enabling Fast-Start Failover
Configuring Fast-Start Failover
Viewing Fast-Start Failover Information
Performing Role Changes
Reinstating the Database

Implementing Client Failover Procedures
Configuring Your Database to Automate Failover for OCI Clients
Configuring Your Database to Automate Failover for JDBC Clients
Troubleshooting Client Failover

 

Creating and Managing a Snapshot Standby Database
Converting a Physical Standby Database into a Snapshot Standby Database

Using a Snapshot Standby Database
Monitoring a Snapshot Standby Database

Using Oracle Active Data Guard
Opening a Physical Standby Database in Read-only Mode
Enabling Block Change Tracking on a Physical Standby Database for Fast Incremental Backups

Creating a Logical Standby Database
Preparing to Create a Logical Standby Database
Creating a Logical Standby using SQL/RMAN Commands
Creating a Logical Standby using Grid Control

Managing SQL Apply Filtering
Performing Rolling Upgrades
Managing the Data Guard Configuration
Viewing Data Guard Diagnostic Information
Troubleshooting

Guard Configuration
Using RMAN to Back Up and Restore Files in a Data Guard Configuration
Offloading Backups to a Physical Standby Database
Recovering a Corrupted Datafile on the Primary Database
Backing Up a Logical Standby Database

 

Upgrading Databases in a Data Guard Configuration
Using SQL Apply to Upgrade the Oracle Database
Using a Physical Standby for Rolling Upgrades
Upgrading an Oracle Data Guard Release 10.n Configuration to Release 11.1

 

 

Introduction
Course Objectives
Course Schedule
Course Pre-requisites and Suggested Pre-requisites
The sh and dm Sample Schemas and Appendices Used in the Course
Class Account Information
SQL Environments and Data Warehousing Tools Used in this Course
Oracle 11g Data Warehousing and SQL Documentation and Oracle By Examples
Continuing Your Education: Recommended Follow-Up Classes

 

Data Warehousing, Business Intelligence, OLAP, and Data Mining
Data Warehouse Definition and Properties
Data Warehouses, Business Intelligence, Data Marts, and OLTP
Typical Data Warehouse Components
Warehouse Development Approaches
Extraction, Transformation, and Loading (ETL)
The Dimensional Model and Oracle OLAP
Oracle Data Mining

 

Defining Data Warehouse Concepts and Terminology
Data Warehouse Definition and Properties
Data Warehouse Versus OLTP
Data Warehouses Versus Data Marts
Typical Data Warehouse Components
Warehouse Development Approaches
Data Warehousing Process Components
Strategy Phase Deliverables
Introducing the Case Study: Roy Independent School District

(RISD)

Business, Logical, Dimensional, and Physical Modeling
Data Warehouse Modeling Issues
Defining the Business Model
Defining the Logical Model
Defining the Dimensional Model
Defining the Physical Model: Star, Snowflake, and Third Normal Form
Fact and Dimension Tables Characteristics
Translating Business Dimensions into Dimension Tables
Translating Dimensional Model to Physical Model

 

 

Database Sizing, Storage, Performance, and Security Considerations
Database Sizing and Estimating and Validating the Database Size
Oracle Database Architectural Advantages
Data Partitioning
Indexing
Optimizing Star Queries: Tuning Star Queries
Parallelism
Security in Data Warehouses
Oracle’s Strategy for Data Warehouse Security

The ETL Process: Extracting Data
Extraction, Transformation, and Loading (ETL) Process
ETL: Tasks, Importance, and Cost
Extracting Data and Examining Data Sources
Mapping Data
Logical and Physical Extraction Methods
Extraction Techniques and Maintaining Extraction Metadata
Possible ETL Failures and Maintaining ETL Quality
Oracle’s ETL Tools: Oracle Warehouse Builder, SQL*Loader, and Data Pump

The ETL Process: Transforming Data
Transformation
Remote and Onsite Staging Models
Data Anomalies
Transformation Routines
Transforming Data: Problems and Solutions
Quality Data: Importance and Benefits
Transformation Techniques and Tools
Maintaining Transformation Metadata

The ETL Process: Loading Data
Loading Data into the Warehouse
Transportation Using Flat Files, Distributed Systems, and Transportable Tablespaces
Data Refresh Models: Extract Processing Environment
Building the Loading Process
Data Granularity
Loading Techniques Provided by Oracle
Postprocessing of Loaded Data
Indexing and Sorting Data and Verifying Data Integrity
Refreshing the Warehouse Data
Developing a Refresh Strategy for Capturing Changed Data
User Requirements and Assistance
Load Window Requirements
Planning and Scheduling the Load Window
Capturing Changed Data for Refresh
Time- and Date-Stamping, Database triggers, and Database Logs
Applying the Changes to Data
Final Tasks

 

Materialized Views
Using Summaries to Improve Performance
Copyright © 2010, Oracle. Tum Haklari Saklidir. Sayfa 3
Using Materialized Views for Summary Management
Types of Materialized Views
Build Modes and Refresh Modes
Query Rewrite: Overview
Cost-Based Query Rewrite Process
Working With Dimensions and Hierarchies

Leaving a Metadata Trail
Defining Warehouse Metadata
Metadata Users and Types
Examining Metadata: ETL Metadata
Extraction, Transformation, and Loading Metadata
Defining Metadata Goals and Intended Usage
Identifying Target Metadata Users and Choosing Metadata Tools and Techniques
Integrating Multiple Sets of Metadata
Managing Changes to Metadata

Data Warehouse Implementation Considerations
Project Management
Requirements Specification or Definition
Logical, Dimensional, and Physical Data Models
Data Warehouse Architecture
ETL, Reporting, and Security Considerations
Metadata Management
Testing the Implementation and Post Implementation Change Management
Some Useful Resources and White Papers

 

 

 

Review of Linux Basics
Linux Directory Structure
File Permissions and Security
Common Linux Commands and Programs
Working with Linux Shells
Bash Shell Scripting

Preparing Linux for Oracle
Linux Distributions
Verifying the Linux Kernel
Using the /proc System
Setting Kernel Parameters
Managing Packages
Using the oracle-validated RPM
Creating Groups and Users
The nobody User

Installing Oracle on Linux
Setting Environment Variables Pre-Installation
Optimal Flexible Architecture
Installing New Releases
Managing Multiple Oracle Versions
Setting Oracle Environment Variables
Performing a Silent or Suppressed Installation
Oracle Patch Utility
Oracle Relink Utility

Managing Storage on Linux
Oracle Database Storage Options
Supported Linux I/O Modes
Disks and Partitions
Managing Partitions
Logical Volume Manager Concepts
Attached Storage
Linux and File Systems
Monitoring Disk Usage and Free Space

Using Oracle ASM on Linux
Automatic Storaget Management Library Driver
Installing and Initializing ASMLib
Configuring Disks
Marking Disks as Automatic Storage Management Disks
Creating an ASM Instance
ASM Installation Best Practices
Disk Group Configuration Best Practices

Automating Oracle on Linux
Automating Tasks
Linux Startup Sequence
Linux Runlevels
Automating Startup and Shutdown of Oracle Processes
Working with the dbstart and dbshut Scripts
Linux Scheduling Tools
Scheduling a Backup with cron

Optimizing Linux for Oracle
Standard Linux Measurement Tools
Measuring CPU Activity and Reducing CPU Bottlenecks
Monitoring and Tuning Memory
Monitoring and Tuning Disk I/O
Basic Oracle Database Optimizations
Basic Linux Optimizations
Page Address Extensions for 32-bit Systems
Configuring Hugepages

Additional Linux Tips for DBAs
Simple Shell Scripts to Simplify DBA Tasks
Adding Command History to SQL*Plus
Finding Files with SETUID or SETGID Set
Capturing System Data Using Scripts
Finding Background Processes
Finding Server Processes
Killing Server Processes
Query Output in an Environment Variable

Troubleshooting Oracle Issues on Linux
Monitoring alert log with ADRCI
Resolving ORA-600/ORA-7445 Errors
Process Hierarchy
Viewing the Startup Environment for a Process
Viewing the Status of a Process
Recovering from Database Crashes
Debugging a Core Dump
Using strace

Review of Linux Basics
Linux Directory Structure
File Permissions and Security
Common Linux Commands and Programs
Working with Linux Shells
Bash Shell Scripting

Preparing Linux for Oracle
Linux Distributions
Verifying the Linux Kernel
Using the /proc System
Setting Kernel Parameters
Managing Packages
Using the oracle-validated RPM
Creating Groups and Users
The nobody User

Installing Oracle on Linux
Setting Environment Variables Pre-Installation
Optimal Flexible Architecture
Installing New Releases
Managing Multiple Oracle Versions
Setting Oracle Environment Variables
Performing a Silent or Suppressed Installation
Oracle Patch Utility
Oracle Relink Utility

Managing Storage on Linux
Oracle Database Storage Options
Supported Linux I/O Modes
Disks and Partitions
Managing Partitions
Logical Volume Manager Concepts
Attached Storage
Linux and File Systems
Monitoring Disk Usage and Free Space

Using Oracle ASM on Linux
Automatic Storaget Management Library Driver
Installing and Initializing ASMLib
Configuring Disks
Marking Disks as Automatic Storage Management Disks
Creating an ASM Instance
ASM Installation Best Practices
Disk Group Configuration Best Practices

Automating Oracle on Linux
Automating Tasks
Linux Startup Sequence
Linux Runlevels
Automating Startup and Shutdown of Oracle Processes
Working with the dbstart and dbshut Scripts
Linux Scheduling Tools
Scheduling a Backup with cron

Optimizing Linux for Oracle
Standard Linux Measurement Tools
Measuring CPU Activity and Reducing CPU Bottlenecks
Monitoring and Tuning Memory
Monitoring and Tuning Disk I/O
Basic Oracle Database Optimizations
Basic Linux Optimizations
Page Address Extensions for 32-bit Systems
Configuring Hugepages

Additional Linux Tips for DBAs
Simple Shell Scripts to Simplify DBA Tasks
Adding Command History to SQL*Plus
Finding Files with SETUID or SETGID Set
Capturing System Data Using Scripts
Finding Background Processes
Finding Server Processes
Killing Server Processes
Query Output in an Environment Variable

Troubleshooting Oracle Issues on Linux
Monitoring alert log with ADRCI
Resolving ORA-600/ORA-7445 Errors
Process Hierarchy
Viewing the Startup Environment for a Process
Viewing the Status of a Process
Recovering from Database Crashes
Debugging a Core Dump
Using strace

Installation and Upgrade Enhancements
Oracle Database 11g Installation: Changes
Oracle Database 11g: Software Installation
Oracle Database Upgrade Enhancements
Upgrade Performance Enhancement
Upgrade Process
Oracle Database 11g Release 1: Upgrade Paths
Database Upgrade Assistant (DBUA)
Hot Patching: Overview

Storage Enhancements
ASM Fast Mirror Resync: Overview
ASM Preferred Mirror Read: Overview
ASM Scalability and Performance Enhancements
ASM Scalability in Oracle Database 11g
ASM Disk Group Compatibility
ASMCMD Extensions

Change Management Unit Overview
Change Management in Oracle Database 11g
Challenges Faced by DBAs When Performing Changes
Lifecycle of Change Management
Setting Up a Test Environment by Using the Snapshot Standby Database
SQL Performance Analyzer: Overview
Capturing the SQL Workload
SQL Performance Analyzer: Data Dictionary Views

SQL Plan Management
SQL Plan Management: Overview
SQL Plan Baseline: Architecture
Evolving SQL Plan Baselines
SQL Plan Selection
Possible SQL Plan Manageability Scenarios
SQL Performance Analyzer and SQL Plan Baseline Scenario
Loading a SQL Plan Baseline Automatically
Purging SQL Management Base Policy

Database Replay
System Architecture: Capture
System Architecture: Processing the Workload
System Architecture: Replay
Pre-Change Production System
Supported Workloads
Database Replay Workflow in Enterprise Manager
Using the Capture Wizard
Packages and Procedures

Automatic SQL Tuning
Automatic SQL Tuning in Oracle Database 11g
Selecting Potential SQL Statements for Tuning
Maintenance Window Timeline
Automatic Tuning Process
DBA Controls
Automatic SQL Tuning Task
Automatic SQL Tuning: Fine Tune
Automatic SQL Tuning Considerations

Intelligent Infrastructure Enhancements
Automatic Workload Repository Baselines
Performance Monitoring and Baselines
Automated Maintenance Tasks
Resource Manager: New EM Interface

Performance Enhancements
ADDM Enhancements in Oracle Database 11g
Oracle Database 11g: Automatic Database Diagnostic Monitor for RAC
New ADDM Views
Automatic Memory Management: Overview
Oracle Database 11g Memory Parameters
Automatic Memory Parameter Dependency
Monitoring Automatic Memory Management

Partitioning and Storage-Related Enhancements
Oracle Partitioning
Partitioning Enhancements
Interval Partitioning
System Partitioning
Virtual Column–Based Partitioning
Reference Partitioning
Table Compression: Overview
SQL Access Advisor: Overview

Using RMAN Enhancements
RMAN: New Features
Optimized Backups
Parallel Backup and Restore for Very Large Files
Using RMAN Multisection Backups
Duplicating a Database
Managing Recovery Catalogs
Creating and Using Virtual Private Catalogs
Using RMAN Virtual Private Catalogs

Using Flashback and LogMiner
New and Enhanced Features for Flashback and LogMiner
Flashback Data Archive Overview: “Oracle Total Recall”
Flashback Data Archive: Architecture
Flashback Data Archive: Workflow
Guidelines and Usage Tips
Flashback Transaction Backout
Flashback Transaction Wizard
Using LogMiner

Diagnosability Enhancements
Oracle Database 11g R1 Fault Management
Ease Diagnosis: Automatic Diagnostic Workflow
Automatic Diagnostic Repository
Location for Diagnostic Traces
Incident Packaging Service (IPS)
EM Support Workbench: Overview
Health Monitor: Overview
Data Recovery Advisor

Using the Data Recovery Advisor
Repairing Data Failures
Data Recovery Advisor
Data Recovery Advisor RMAN Command-Line Interface
Best Practice: Proactive Checks
Setting Parameters to Detect Corruption
Security: New Features
Secure Password Support
Automatic Secure Configuration
Managing Default Audits
Setting Database Administrator Authentication
Transparent Data Encryption
Hardware Security Module
Using RMAN Security Enhancements

Oracle SecureFiles
Managing Enterprise Information
Problems with Existing LOB Implementation
Oracle SecureFiles
Shared I/O Pool
Altering SecureFiles
Accessing SecureFiles Metadata
Migrating to SecureFiles
SecureFiles Monitoring

Miscellaneous New Features
Foreground Statistics
Online Redefinition Enhancements
Minimizing Dependent Recompilations
Locking Enhancements
Invisible Index: Overview
SQL Query Result Cache: Overview
Adaptive Cursor Sharing: Overview
Temporary Tablespace Shrink

Planning Your Upgrade to Oracle Database 11g
Steps to Upgrade to Oracle Database 11g
Choosing an Upgrade Method
Performing Character Conversion

 

Upgrading the Oracle Database 11g
Installing Oracle Database 11g Software
Applying Oracle Software Patches
Creating a Database

 

Real Application Testing
Why Use Database Replay?
Pre-Change Production System
Capture / Replay Considerations
Performing Workload Capture
Performing Workload Replay
Using SQL Performance Analyzer
Using Database Replay to test system changes
Using SQL Performance Replay to test SQL changes

 

Upgrading Your Database Using other Methods
Using DBUA to Upgrade the Database
Oracle Database 11g: Upgrade Enhancements
Backing Up the Database Before Upgrade
Using the Preupgrade Information Tool
Using New Postupgrade Status Utility

 

Performing Post-Upgrade Steps
Upgrading Server Manager scripts
Performing Additional Upgrade Tasks
Using New Features in Your Upgraded Database
Executing the Postupgrade Script
Upgrading the National Character Set toAL16UTF16
Checking for Unusable Function-based Indexes
Upgrading the Recovery Catalog

Using EM: Database Control and Grid Control
Administering the Database Using Enterprise Manager: Overview
Using Database Control
Granting Database Control Administrative Privileges
Using Grid Control to Manage Your Environment
Setting Preferred Credentials

 

Proactively Maintaining the Database
Oracle Database 11g: Self-Managing Database
Automatic Workload Repository
Job Scheduler Concepts
Server-Generated Alerts
Advisor Framework
Automatic Database Diagnostic Monitor
Configuring Alerts

Managing System Resources
Database Resource Manager: Concepts (auto enabled)
Using New Features of Database Resource Manager
Exploring Allocation methods
Maximum Estimated Execution Time
Automatic Consumer Group Switching
Using Resource Manager Statistics

 

Automating Tasks with the Scheduler
Scheduler Concepts
Using Scheduler Programs
Windows and Resources
Administering the Scheduler

Managing Memory Structures
Enabling Automatic Memory Management
Modifying Parameters for Automatically Managed Components
Modifying Parameters for Manually Sized Components
Monitoring Automatic Memory Management

 

Managing Database Storage Structures
Using the SYSAUX Tablespace
Specifying a Default User Tablespace
Using Oracle Managed Files (OMF
Creating Bigfile Tablespaces
Using Nonstandard Block Sizes
Proactively Monitoring Tablespaces
Using the Redo Logfile Sizing Advisor

 

Implementing Automatic Storage Management (ASM)
ASM: Key Features and Benefits
ASM: General Architecture
Accessing an ASM Instance
ASM Administration
Database Instance Parameter Changes
Migrating Your Database to ASM

 

Managing Database Space
Understanding Automatic Undo Management
Specifying the Undo Management Mode
Setting the Undo Management Initialization Parameters
Using the Undo Advisor

 

Administering Users and Database Security
Unlocking Default User Accounts
Specifying a Common Password for System
Securing access by user
Using Fine-Grained Auditing
Auditing the SYS User
Understanding Changes to the Audit Trail

Managing Schema Objects
Using Automatic Segment-space Management
Estimating Table and Index Size Before Creation
Granting Object Privileges on Behalf of the Object Owner
Performing Online Redefinition of Tables
Enabling Data Segment Compression
Automatically Collecting Statistics
Using the Segment Advisor

 

Accessing and Loading Data
Using Data Pump to Import and Export Data
Managing External Tables
Cross-Platform Transportable Tablespaces

Performing Backup and Recovery Operations
Using RMAN to Back up the SPFILE
Creating Change-aware Incremental Backups
Encrypting backups
Using backup compression
Using RMAN to Perform Recovery: Automatic File Creation During Recovery
Performing Trial Recovery
Enabling and Performing Flashback Database

 

Recovering From User Errors
Using Flashback Query
Auditing and Recovering Transactions Using the Flashback Transaction Query Feature
Retrieving Row History Information Using the Flashback Version Query Feature
Recovering Table Data Using the Flashback Table Feature
Recovering Dropped Tables using Flashback Drop Feature
Flashing back a transaction
Using LogMiner Enhancements

Managing Performance
Using the SQL Tuning Advisor
Using the SQL Access Advisor
Using the trcsess Utility to Consolidate Output from Trace Files
Using DBMS_MONITOR

 

Diagnosability Enhancements
Oracle Database 11g Fault Management
Ease Diagnosis: Automatic Diagnostic Workflow
Automatic Diagnostic Repository •
Incident Packaging Configuration
Running Health Checks
Using the SQL Repair Advisor
Viewing, Disabling, or Removing a SQL Patch

Miscellaneous New Features
Managing & Using SQL Query Result Cache
Adaptive Cursor Sharing: Architecture
Temporary tablespace shrinkage
Using the client result cache

Oracle Streams New Features: Overview
Identifying Oracle Streams Processes
Oracle Streams Performance Advisor
Viewing Oracle Streams Topology
Configuring Oracle Streams with Enterprise Manager
Automated Alerts
Simplified Way to Restore Default Values for Parameters
Restoring default values for parameters
Message Tracking with DBMS_STREAMS_ADM.SET_MESSAGE_TRACKING

Using Manageability Enhancements
Describing and using capture enhancements, such as:
Recording Table Changes and KEEP_COLUMNS Declarative Transformation
Usability Enhancements, such as:
Support for XMLType, SecureFile LOBs, Flashback data archive and virtual columns
Support for Compressed Tables and TDE
Apply Enhancements, such as:
DML Statement Handler Type and SQL Generation
Diagnosability Enhancement

Using Combined Capture and Apply
Combined Capture and Apply (CCA) Requirements
Initiating the CCA Optimization
Verifying the Use of CCA in Enterprise Manager and via Command Line
Monitoring and managing an Oracle Streams environment with CCA

Splitting and Merging of Streams Destination
Automatic Split and Merge
Streams: Hub with Three Spokes
Split Streams: Unavailable Site
The SPLIT_STREAMS Procedure
Merge Streams: Original Configuration
DBMS_STREAMS_ADM Methods
Using MERGE_STREAMS_JOB
New DBA_RECOVERABLE_SCRIPT_HIST View

Comparing and Converging Data
Comparing Table Data
Creating a Comparison
Viewing the Differences in Data
Converging Database Objects
Performing Different Types of Comparisons
Purging Comparison Results
Dropping a Comparison

Implementing Synchronous Capture
Captured Changes
Configuring Synchronous Capture
Queues and Apply for Synchronous Capture
Using the DBMS_STREAMS_ADM and the DBMS_CAPTURE_ADM Package
Copyright © 2010, Oracle. All rights reserved. Page 2
Displaying the Tables and Viewing the Extra Attributes
Displaying the Queue and Rule Set
Dropping a Synchronous Capture
Listing Objects That Are Not Compatible with Synchronous Capture

Using Advanced Queuing Enhancements
Native Support for AQ in JDBC Driver
Viewing Subscriptions and Performance
Using the Scheduler
Scalable Notifications
Enhanced Messaging Gateway Agent
Creating an Agent
Configuring a Messaging Gateway Propagation Job

Configuring and Using XStream
Heterogeneous Data Sharing
XStream Architecture
XStream Out and XStream In
Position Order in an LCR Stream
Configuring an Outbound Server and an Inbound Server
XStream in Action - An Example
Monitoring XStream

Introduction
Tuning Questions
Who tunes
What to tune
How to tune

Monitoring With Basic Tools
Monitoring tools overview
Enterprise Manager
V$ views, Statistics and Metrics
Wait Events
Time Model: Overview

Using Automatic Workload Repository
Automatic Workload Repository: Overview
Automatic Workload Repository Data
Database Control and AWR
Generating AWR Reports in SQL*Plus

Identifying the Problem
Tuning Life Cycle Phases
Identify a Tuning Issue
Remedy one problem

Identifying Problem SQL Statements
Characteristics of a bad SQL statement
Role of the Optimizer
Generate explain plan
Access Paths Choices
Trace the execution

Influencing the Optimizer
Manage Optimizer Statistics
Calibrate I/O
Optimizer Cost
Changing Optimizer Behavior

SQL Plan Management
Automatic Maintenance Tasks
SQL Profiles
SQL Access Advisor
SQL Outlines
SQL Plan Baselines

Change Management
Types of changes
SQL Performance Analyzer
DB Replay
Server-Generated Alerts

Using Metrics and Alerts
Benefits of Metrics
Database Control Usage Model
User-Defined SQL Metrics

Using AWR Based Tools
Automatic Maintenance Tasks
Using ADDM
Using Active Session History
Historical Data View

Monitoring an Application (Using Services)
Service Overview
Managing Service
Service Aggregation and Tracing
Tracing Your Session

Baselines
Working with Metric Baselines
Setting Adaptive Alert Thresholds
Configuring Normalization Metrics

Tuning the Shared Pool
Shared Pool Operation
Mutex
Statspack/AWR Indicators
Library Cache Activity
Diagnostic Tools
UGA and Oracle Shared Server
Large Pool

Tuning the Buffer Cache
Architecture
Tuning Goals and Techniques
Symptoms
Solutions

Monitoring SQL Memory Usage
Temporary Tablespace Management

Automatic Memory Management
Automatic Memory Management Architecture
Dynamic SGA Feature
Managing Automatic Memory Management

Tuning Block Space Usage
Space Management
Extent Management
Anatomy of a Database Block
Block Space Management

Tuning I/O
I/O Architecture
Striping and Mirroring
Using RAID
I/O Diagnostics
Using Automatic Storage Management

: Performance Tuning: Summary
Important Initialization Parameters with Performance Impact
Database High Availability: Best Practices
Tablespace: Best Practices
Statistics Gathering

Using Statspack
Introduction to Statspack
Capturing Statspack Snapshots
Reporting with Statspack
Statspack considerations
Statspack and AWR

Oracle Clustrware Installation
Oracle RAC 11g Installation
Cluster Setup Tasks
Using Raw Partitions
Cluster Configuration

RAC Installation
Installing Automatic Storage Management
Configure ASM Storage
Install the Database Software

Database Creation
Management Agent Installation
Storage Options for Database Files
Create the Database
Single Instance to RAC Conversion

RAC DB Administration
Cluster Database Instance Administration
Topology Viewer
Automatic Undo Management and RAC
Quiescing RAC Databases
Transparent Data Encryption and Wallets in RAC
ASM Instance and Crash Recovery in RAC

Backup and Recover a RAC DB
Protecting Against Media Failure
Oracle Recovery Manager

RAC DB Monitoring and Tuning
CPU and Wait Time Tuning Dimensions
Dynamic Reconfiguration
ADDM Analysis

Services
Workload Dispatching
High Availability of Services in RAC
Services and Resource Manager with EM
Change Service Thresholds
Using Distributed Transactions with RAC

High Availability of Connections
Fast Application Notification
Load Balancing Advisory
Transparent Application Failover

Oracle Clusterware Administration
Controlling the Oracle Clusterware Stack
Back Up and Recover Your Voting Disks
Back Up and Recover Oracle Cluster Registry
Prevent Automatic Instance Restarts

Diagnosing the Oracle Clusterware components
Oracle Clusterware Main Log Files
RAC Diagnostic Infrastructure

Node Addition/Removal
Add and Delete Nodes and Instances
Clone Oracle Clusterware

Design for High Availability
Maximum Availability Architecture
RAC and Data Guard Topologies
Extended RAC Connectivity
Extended RAC disk mirroring
Patch your RAC system in a rolling fashion

Workshop
Extend your cluster using Grid Control
Patch Oracle Clusterware

Security Requirements
Data Security Concerns
Fundamental Data Security Requirements
Components for enforcing security
Security Risks: Internal, External, Sabotage, Recovery
Principle of Least Privilege
Defining a Security policy
Implementing a Security Policy

Choosing Security Solutions
Maintaining data integrity
Controlling data access
Data Protection
Database Vault overview
Audit Vault overview
Combining Optional Security Features
Compliance Scanner
Database Control: Policy Trend

Basic Database Security
Database Security Checklist
Installing only what is required
Applying Security Patches
11g Default security settings
Enforcing Password Management
System and Object Privileges
Restricting the Directories Accessible by the User
Separation of Responsibilities

Database Auditing
Standard Database Auditing
Monitoring for Suspicious Activity
Audit Log Location Options
Viewing Auditing Results
Configure Auditing to syslog
Value-Based Auditing
Triggers and Autonomous Transactions

Auditing DML Statements (Fine-Grained Auditing)
Fine-Grained Auditing (FGA)
Fine-Grained Auditing Policy
Triggering Audit Events
Data Dictionary Views
Enabling and Disabling an FGA Policy
FGA Policy Guidelines
Maintaining the Audit Trail

Basic User Authentication
User Authentication
User Identified by a Password
User Identified Externally
Protecting Passwords
Encrypted Database Link Passwords
Audit with Database Links

Using Strong Authentication
Strong User Authentication
Single Sign-On
How to Use Certificates for Authentication
Configuring SSL
orapki Utility
How to Use Kerberos for Authentication
RADIUS Authentication: Overview
External Secure Password Store

Enterprise User Security
Setting up Enterprise User Security
Oracle Identity Management Infrastructure: Default Deployment
Oracle Database: Enterprise User Security Architecture
Authenticating Enterprise Users
User Migration Utility
Enterprise-User Auditing

Proxy Authentication
Security Challenges of Three-Tier Computing
Common Implementations of Authentication
Restrict the Privileges of the Middle Tier
Using Proxy Authentication for Database Users
Using Proxy Authentication for Enterprise Users
Revoking Proxy Authentication
Data Dictionary Views for Proxy Authentication

Authorization Methods
Authorization
Assigning Privileges
Using Enterprise roles
Implementing a Secure Application Role

Using Application Context
Application Context Overview
Implementing a Local Context
Application Context Accessed Globally
Guidelines

 

Implementing Virtual Private Database
Understanding Fine Grain Access Control
Virtual Private Database
Implementing VPD Policies
Manage VPD Policies
Policy Performance
Checking for Policies Applied to SQL Statements

Oracle Label Security Concepts
Access Control: Overview
Discretionary Access Control
Oracle Label Security
Comparing Oracle Label Security and the VPD

Implementing Oracle Label Security
Policy Enforcement Options
Managing levels, groups, compartments
Administering Labels
Trusted Stored Package Units
Performance tips

Using the Data Masking Pack
Understanding Data Masking
Identifying Sensitive Data for Masking
Implementing Data Masking
Data Masking Impact Report

Encryption Concepts
Understanding encryption
Cost of encryption
Encryption is not Access Control
Data Encryption Challenges
Encryption Key Management
Solutions and examples

Using Application Based Encryption
Overview
The DBMS_CRYPTO Package
Generate Keys Using RANDOMBYTES
Using ENCRYPT and DECRYPT
Enhanced Security Using the Cipher Block Modes
Hash and Message Authentication Code

Applying Transparent Data Encryption
Transparent Data Encryption overview
Components of Data Encryption
Using Data Encryption
Using Hardware Security Modules
Tablespace Encryption

 

Use File Encryption
RMAN Encrypted Backups
Oracle Secure Backup Encryption
Using Transparent Mode Encryption
Using Password Mode Encryption
Using Dual Mode Encryption
Restoring encrypted backups

Oracle Net Services Security Checklist
Security Checklists Overview
Client Checklist
Network Security Checklist
Restricting Network IP Addresses
Restricting Open Ports
Encrypting Network Traffic
Configure Checksumming
Oracle Net Services Log Files

Securing the Listener
Listener Security Checklist
Restricting the Privileges of the Listener
Password Protect the Listener
Administering the Listener Using TCP/IP with SSL
Analyzing Listener Log Files

What's New in Enterprise Manager( An Introduction to New Features in Enterprise Manager)
Discuss Oracle Real User Experience Insight
Discuss Composite Application Performance Management
Discuss Application Configuration Console
Discuss Application Testing Suite
Discuss Oracle Application Diagnostics for Java
Discuss Configuration Change Console

Grid Control Framework: New Features
Use Set Default Templates
Alert management enhancements
Use Action page of Notification System
Privilege Propagating Groups
GC Security framework enhancements
EMCLI enhancements
View Audit reports

Managing Database: High Availability (Maximum Availability Architectures and Configuration Management for Database)
Understand the need to implement high availability
Use High Availability Console
Use Maximum Availability Architecture (MAA) Console
Use MAA workflows
Use Oracle Streams
Migrate Standby Database to Automatic Storage Management (ASM)
Monitor Oracle Secure Backup

Database Management: New Features (Managing, Monitoring and Administering Oracle Database 11g)
Add Active Data Guard
Create Snapshot Standby Database
Flashback Transaction
Use Advisor Central
Use Automatic SQL Tuning feature
Use security features such as : Transparent Data Encryption, Oracle Security Label, Virtual Private Database, Enterprise User Security
Use Database Replay
Change Management

Virtualization Management
Virtualization Management Overview
Provisioning the Virtual Infrastructure
Provisioning Guest Virtual Machines
Virtualization Central Dashboard
Live Migrate Guest Virtual Machines
Enable High Availability and Fault Tolerance
Enable High Availability and Fault Tolerance
EMCLI commands

Managing Monitoring Oracle WebLogic Server (WLS)
Oracle WebLogic Server Overview
Discovery of Oracle WebLogic Server domains (Local and Remote)
Multiple domain monitoring
Tune/Configure WLS to improve performance
Managing Clusters
Monitoring Managed Servers
Configuration Management
Reports

Managing and Monitoring Oracle Service Bus
List the features of Management Pack Plus for Service Oriented Architecture (SOA)
Provide an overview of Oracle Service Bus
Discover Oracle Service Bus targets
Monitor the availability and performance of Oracle Service Bus
View, Compare and Manage configuration information
Provision Oracle Service Bus using ‘Oracle Service Bus Resource Provisioning’ Deployment Procedure

Managing Oracle Coherence Cluster
Define Oracle Coherence
Discovering Coherence target
Monitor Oracle Coherence cluster
Monitor Cache Performance
Monitor Node performance
Discuss Configuration Management features of Oracle Coherence and its components
Deploy Oracle Coherence cluster

Oracle Clusterware Architecture
What is Oracle Clusterware
Complete Integrated Clusterware
Hardware and Software Concepts
Protected Applications with Oracle Clusterware
Process Architecture
File System Structures

 

Oracle Clusterware Installation
Oracle Clusterware Version Compatibility
Oracle RAC 11g Installation
Windows and UNIX Installation Differences
Hardware Requirements
Network Requirements
Package Requirements

Administering Oracle Clusterware
Oracle Clusterware startup and shutdown
Enabling and Disabling Clusterware Daemons
Administering the Voting Disk file
Administering the Oracle Cluster Registry Disk file
Modifying Network Settings

 

Cloning Oracle Clusterware
Preparing the CRS_HOME for cloning
Cloning Oracle Clusterware to create a new cluster
Cloning to Extend and existing cluster
Cloning Script Variables
Examining log files for cloning

Adding and Deleting Oracle Clusterware Homes
Prerequisite Steps for Adding Oracle Clusterware
Adding Oracle Clusterware Homes Using insert active and silent mode
Deleting Oracle Clusterware Homes

 

Making Applications Highly Available Using Oracle Clusterware
Overview of Using Oracle Clusterware to Enable HA
Flow diagram of the HA lifecycle
Application Placement Policies
Optional Resources in Placement Decisions
Creating Application Profiles
Creating an Application VIP
Managing Application Resources

Troubleshooting Oracle Clusterware
Oracle Enterprise Manager monitoring capabilities
Checking the health of the Clusterware
Component Level Debugging
Enabling Tracing for Java Based Tools
Determining Software Versions and Active Versions
Troubleshooting the OCR file
Verifying Event Manager Communications

Patching Oracle Clusterware using Rolling Upgrade Procedure
Metalink for latest patch set
Downloading latest patch set
Shutting down RAC resources
Stopping all Oracle processes
Running OUI to complete patch installation for 1st node
Repeat process on remaining nodes

Introduction to Storage in Oracle Environments
Overview of Disk Management
Overview of OCFS2
Overview of Direct NFS
Overview of Exadata Server
Overview of ASM

Automatic Storage Manager (ASM) Architecture
ASM for Single Instance
ASM Cluster Configuration for Oracle RAC
ASM Components
Utilities for Working with ASM

Installing and Configuring ASM
Install ASM with Oracle Universal Installer
Install 11.1.0.7.0 Patchset to ASM binaries
Installing and Configuring ASMLib
Configuring ASM with dbca

Administering ASM Instances
ASM Initialization Parameters
Connecting to and Authenticating to ASM Instances
Starting/Stopping ASM Instances
Querying Dynamic Performance Views
Administering ASM Disk Groups
Disk group attributes
Creating a new disk group
Disk aliases
Extending an existing disk group
Dropping disk from an existing disk group
Dropping a complete Disk Group
Mounting and Dismounting Disk groups
ASM Fast Mirror Resync

Administering ASM Files, Directories, and Templates
Accessing ASM files
ASM file names
Create ASM files
Managing ASM Directories
Managing Filename aliases
Managing Disk Group Templates

ASM Command-Line utility
Prerequisites to running ASMCMD
Modes of ASMCMD
ASMCMD command overview

Administering ASM with Oracle Enterprise Manager
ASM Homepage
Configuring ASM initialization parameters
Managing ASM users
Managing ASM disk groups
Monitoring ASM Performance
Migrating to ASM

Grid Infrastructure Concepts
What is a Cluster
Grid Foundation Components
Oracle Clusterware Architecture
Oracle Clusterware Software and Storage
Describe ASM Architecture
Creating and Managing ASM Disk Groups
Creating and Managing ASM Cluster Filesystems
Job Role Separation

 

Grid Infrastructure Installation and Configuration
Hardware Requirements
Network Requirements
DNS and DHCP Configuration
Grid Plug and Play Considerations
Single Client Access Names
Post installation tasks

 

Administering Oracle Clusterware
Managing Clusterware with Enterprise Manager
Determining the Location of the Oracle Clusterware Configuration Files
Backing Up and Recovering the Voting Disk
Adding, Deleting, or Migrating Voting Disks
Locating the OCR Automatic Backups
Oracle Local Registry
Migrating OCR Locations to ASM
Managing Network Settings

 

Managing Oracle Clusterware
Prerequisite Steps for Extending a Cluster
Using addNode.sh to Add a Node to a Cluster
Rolling Patches, And Rolling Upgrades
Comparing Software Versions With the Active Version
Installing A Patchset With the OUI Utility
Installing A Patch With The opatch Utility

 

Oracle Clusterware High Availability
Oracle Clusterware high availability components
Contrasting policy-managed and administration managed databases
Server pool functionality
The Generic and Free Server Pools
Application placement policies
Application Virtual IPs
Managing application resources
High availability events

 

Troubleshooting Oracle Clusterware
Oracle Clusterware Log Files
Gathering Log Files Using diagcollection.pl
Resource Debugging
Component-level Debugging
Tracing For Java-based Tools
Troubleshooting the Oracle Cluster Registry

ASM Initialization Parameters
Adjusting ASM Instance Parameters in SPFILEs
Starting and Stopping ASM Instances Using srvctl
Starting and Stopping ASM Instances Using ASMCA and ASMCMD
Starting and Stopping ASM Instances Containing Cluster Files
Starting and Stopping the ASM Listener

 

Administering ASM Disk Groups
Creating And Deleting ASM Disk Groups
ASM Disk Group Attributes
ASM Disk Group Maintenance Tasks
Preferred Read Failure Groups
Viewing ASM Disk Statistics
Performance And Scalability Considerations For ASM Disk Groups

ASM Files, Directories, and Templates
Using Different Client Tools to Access ASM Files
Fully Qualified ASM File Name Format
Creating and Managing ASM files, Directories and Aliases
Managing Disk Group Templates
Managing ASM ACL With Command Line Utilities
Managing ASM ACL with Enterprise Manager

 

Administering ASM Cluster File Systems
ASM Dynamic Volume Manager
Managing ASM Volumes
Implementing ASM Cluster File System
Managing ASM Cluster File System (ACFS)
ACFS Snapshots
Using Command Line Tools To Manage ACFS

Course Introduction
Course objectives
Agenda of lessons

Getting Started with Oracle Secure Enterprise Search
Oracle Secure Enterprise Search 10g (10.1.8.2) product overview – Business drivers
Oracle Secure Enterprise Search 10g (10.1.8.2) product components and architecture
Oracle SES Crawler – An Overview
Oracle SES Administration tool
Oracle SES search features – An Overview

Installing Oracle Secure Enterprise Search 10g
Pre-installation tasks
Install Oracle SES on Linux platform
Install Oracle SES on Windows platform
Oracle SES Infrastructure – An Overview

Oracle Secure Enterprise Search Crawler
Overview of Oracle SES crawler
Crawler URL queue
Access URLs and Display URLs
Crawler Settings
Attributes
Crawling Process
Monitoring the crawling process
Document Pipelines and Services

Searching in Oracle Secure Enterprise Search 10g
Basic search
Query Syntax
Submit URL
Advanced Search and Thesaurus-based Search
Alternate Words
API Support for Thesaurus and Alternate Word Management
Optimizing your search
Customizing the appearance of Search Results

Federated Search
SES-to-SES Federation
Suggested Content

Security in Oracle Secure Enterprise Search
SES Architecture
SES Security Model
Authentication Framework
Configuring Secure Search with OracleAS Single Sign-On
SSL and HTTPS Support in Oracle Secure Enterprise Search

Oracle Secure Enterprise Search APIs
Overview of Oracle Secure Enterprise Search APIs
Oracle Secure Enterprise Search Web Services APIs
Oracle Secure Enterprise Search Java SDK

RSS Connector
Define RSS Connector
Concepts
Steps involved in crawling a repository using RSS connector
Example use cases that uses RSS connector
Configuration parameters in the configuration file that is required to configure a RSS source
Format of the control file used in creating a control feed

Troubleshooting Tips
Tips for using Table sources and Database sources
Tuning crawl performance
Tuning Search Performance
Monitoring Oracle Secure Enterprise Search
Turning On Debug Mode
Accessing Application Server Control Console on Oracle SES
Restarting Oracle Secure Enterprise Search After Rebooting

Additional Sources
Configuring access to Content Management Sources
Configuring access to Collaboration sources
Configuring access to Application Sources

Introduction
Oracle Database: Location Features
Oracle Spatial: Spatial Data Management for Enterprise Applications
Oracle Spatial Development History
Oracle Spatial and Locator: art of the Oracle DBMS Kernel
Oracle Spatial Object-Relational Model
Review: Oracle’s Object-Relational Model
Common Geographical Terms Used in the Course
Oracle Spatial Documentation and Resources

 

Overview of Oracle Spatial Concepts
Define Oracle Spatial
Describe Geometric Primitive Types
Describe the Spatial Data Model
Coordinate Systems: Concepts
Explain Spatial Indexing
Describe the Optimized Query Model
Define Linear Referencing System
Define Geocoding and Routing

 

Creating Spatial Layers
Describe the MDSYS Schema
Spatial Native Data Type: SDO_GEOMETRY
Define different types of geometry elements
Construction of geometries by using the INSERT statement
Manage Spatial metadata

 

Defining Collection Geometries
Define Collection geometries: Multipoint, Multiline string, and Multipolygon
Describe Oriented point
SDO_GEOMETRY constructors and member methods

 

Associating Spatial Layers with Coordinate Systems
Define Coordinate systems and their different types
Geodetic coordinate system concepts
Whole earth geometry model and tolerance
Coordinate system transformations
Units supported by Oracle Spatial

 

Loading Spatial Data
Different ways of loading spatial data
Loading of spatial data by using SQL*Loader
Export and import utilities of the Data Pump technology
Load data using transportable tablespace
Load data using transactional insert
Use the Java shapefile converter

Validating and Debugging Geometries
Validation functions: SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT and SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT
Geometry debugging functions: SDO_UTIL.GETVERTICES, SDO_UTIL.RECTIFY_GEOMETRY, and SDO_UTIL.EXTRACT
Strategy for Geometry Validation

 

Using the Oracle Application Server MapViewer
Introduction to MapViewer
Architecture of Oracle Application Server MapViewer
Installation of Oracle Application Server MapViewer
Use MapViewer demos
Edit MapViewer Configuration File

 

Indexing Spatial Data
Concepts of R-tree indexing
CREATE INDEX and the R-tree parameters
Analyze, drop, and alter operations on the spatial index
Use the Spatial index dictionary views
Estimate R-tree index size and the resources required

 

Querying Spatial Data
Overview of the Spatial query model
Overview of spatial operators, procedures, and functions
Use the SDO_FILTER operator
Define Spatial topological relationships
Use the SDO_RELATE operator
Use the SDO_GEOM.RELATE function

 

Using SDO_WITHIN_DISTANCE, SDO_NN, and SDO_JOIN Operators
Spatial queries and operators
Describe the SDO_WITHIN_DISTANCE operator
Describe the SDO_NN operator
Spatial join by using the SDO_JOIN operator

 

Analyzing Geometries by Using Spatial Operators and Functions
Calculation of the area, length, and distance between geometries
Describe Arc densification and buffering
Use the Spatial Boolean functions
Explicit transformations with spatial functions

 

Using Spatial Analysis, MBR, Utility, and Aggregate Functions
Describe some of the Spatial analysis functions
Describe some of the Spatial MBR functions
Describe some of the Spatial utility functions
Describe some of the Spatial aggregate functions
Conversion between SDO_GEOMETRY and Geography Markup Language (GML)

 

Defining Maps by Using the Map Builder Tool
Introduction to Map Builder
Export and import styles
Use of Map Builder to administer style, theme, and map definitions
Use the Sample mapclient.jsp
Define a Sample XML request with elements
Open Geospatial Consortium (OGC) Web Map Service (WMS) and Oracle Workspace Manager support

Leveraging Oracle Maps: The Map Cache and JavaScript API
Oracle Maps concepts
Oracle Maps demo setup
Maps and Faces demo
More Oracle Maps demos

 

Creating a User-Defined Coordinate System
Coordinate systems concepts: Ellipsoids, Datums, and projections
Geodetic or projected coordinate systems
Define OGC WKT schema and EPSG
Creation of a user-defined coordinate system
Local coordinate system

 

Implementing a Linear Referencing System
Linear Referencing System (LRS) concepts
Define LRS geometries
Overview of LRS functions
Implementation of an LRS

 

Managing Spatial Indexes
Oracle Spatial index partitioning
Partition spatial data based on location
Define function-based indexes
Use transportable tablespaces
Embedded spatial geometry

 

Geocoding Address Data
Geocoding concepts
Oracle Spatial geocoding functions
SDO_KEYWORDARRAY, SDO_GEO_ADDR, and SDO_ADDR_ARRAY types
Examples using geocoding functions
Geocoding service

 

Using the Spatial Routing Engine
Oracle Spatial Routing architecture
Route request and response
Sample RouteServer JSP

Introduction and Course Overview
Describe course objectives
Intended Audience and Course Prerequisites

 

Overview of Oracle TimesTen In-Memory Database
List and describe features of Oracle TimesTen In-Memory Database and its product options
Describe how TimesTen In-Memory Database achieves a very fast response time and a very high transaction throughput rate
Identify the TimesTen processes and files
Describe how updates to a TimesTen data store are processed
Explain the function of the TimesTen Data Manager, Client, and Server components

 

Installing TimesTen
Identify the user and system configuration requirements for installing TimesTen
List the TimesTen installable components
Install the TimesTen software
Configure an environment to run TimesTen
Build and run the demo programs
Explain where to find the TimesTen product documentation

 

Creating Data Source Names and Data Stores
Create a data source name (DSN) and set its attributes
Use the ttIsql command-line interface utility to connect to a data store
Use the ttStatus utility to display connections on data stores
Define a data store RAM policy
Explain the concept of Access Control, and define users and assign privileges

 

Creating Data Store Elements
List and describe the characteristics of a TimesTen table and its columns
Write and execute SQL data definition language (DDL) statements for tables and indexes
Configure an automatic data aging policy on a table
List and describe the different types of TimesTen indexes
Describe the contents of the TimesTen system tables
Use the ttSize utility to calculate the estimated size of a table

Accessing Data Store Elements
Write and execute SQL data manipulation language (DML) statements
Identify the various search conditions, functions, and expressions that can be used in DML statements
Create and access sequences
Create regular and materialized views
Execute prepared and parameterized statements
Call the TimesTen built-in procedures

 

Managing Data Stores
Use the various utilities to import and export data into and out of a data store’s tables, and back up and restore a data store
Explain how data store checkpoint operations are initiated
Change the size of a data store
Configure TimesTen to throw Simple Network Management Protocol (SNMP) traps

Transactions and Concurrency Control
Explain the concept of a transaction and how to complete transactions
Describe the purpose of the transaction log and when log records are created
Explain how locking and isolation levels coordinate concurrent operations on a data store
Use the ttXactAdmin utility to view locks requested and acquired by active transactions

 

Performance Tuning and Query Optimization
Create indexes to improve query performance
Update column and table statistics to improve the query execution plan
Display a query execution plan

Configuring TimesTen Client/Server
Describe the TimesTen client/server architecture
Configure a client/server environment
Access a data store in client/server mode
Manually start and stop the TimesTen Server process
Use the ttStatus utility to display information about the server processes and connections from client applications

 

Overview of Cache Connect to Oracle
List and describe features of the Cache Connect to Oracle product option
Describe various usages of Cache Connect to Oracle

Configuring a TimesTen Cache System
Describe what a cache group contains and how it is defined
Identify the operations that can be performed on a cache group to keep the TimesTen cache and the corresponding Oracle database List the different types of cache groups
Configure a system to run Cache Connect to Oracle
Start the cache agent process and describe its role
Creating Cache Groups
Create read-only, write through, and user-managed cache groups
Describe the functionality of the AUTOREFRESH cache group attribute
Pass through statements from TimesTen to Oracle for execution
Manually load, unload, refresh, and flush a cache group
Configure an automatic data aging policy on a cache group
Automatically load a cache group on demand

Cache Connect to Oracle Administrator
Start the Cache Connect to Oracle Administrator and connect to a data store
Use the Cache Connect to Oracle Administrator to:
- Create a cache group definition
- View the SQL statement for a cache group definition
- Create a cache group from its definition
- Manage the cache agent process

 

Overview of Replication - TimesTen to TimesTen
Describe the benefits of the Replication - TimesTen to TimesTen product option
List and describe the common replication configurations
Describe the differences between asynchronous and synchronous replication

Configuring Replication
List the types of elements that can be replicated between TimesTen data stores
Create an asynchronous replication scheme
Start the replication agent process and describe its role
Create a subscriber data store as a copy of a master data store
List and describe the different replication states
Create a return receipt and a return two-safe synchronous replication scheme

 

Advanced Replication Configuration Topics
Alter a replication scheme to perform operations such as adding elements or adding a subscriber data store
Create a propagation replication scheme
Create an active standby pair replication scheme
Configure replication of cache tables
Configure a system with multiple network interface cards for replication

Replication Recovery and Monitoring
Configure timestamp-based update conflict resolution
Configure a transaction log file threshold
List and describe the system tables that store replication information
Recover a replicated data store after a store or system failure
Use various utility commands and built-in procedures to monitor replication