Explore the Lifecycle Development Phases

• Define a database
• Discuss the theoretical, conceptual and physical aspects of a relational database
• Describe the Oracle implementation of the RDBMS and the ORDBMS
• Describe PL/SQL

Write and Execute a Basic SQL Statement

• Describe the SQL SELECT capabilities
• Differentiate between SQL and SQL*Plus commands
• Limit and sort the rows retrieved by a query
• Customize the SQL*Plus environment
• Save customizations

Examine Single Row Functions

• Use a variety of character, number, and date functions in SELECT statements
• Describe different types of join
• Group functions and their uses
• Insert and update rows in a table
• Delete rows from a table
• Define subqueries
• Write single-row, multi-row, and multi-column subqueries
• Control transactions

Create Tables and Views

• Describe the the main database objects
• Create tables
• Alter table definitions
• Drop, rename and truncate tables
• Including Constraints
• Retrieve data via a view
• Insert, update and delete data through views
• Drop views
• Alter the definition of a view
• Create and maintain sequences and indexes

Control User Access

• Understand the concepts of users, roles, and privileges
• GRANT and REVOKE object privileges
• Using Roles to Manage Database Access

This course is designed to prepare you for the corresponding Oracle Certified Associate exam: Exam #1Z0-031.

• Understanding the physical structure
• Understanding the memory structure
• Understanding the process structure
• Understanding the logical structure
• Outlining the stages of an SQL statement
• Defining an Oracle Database, the Oracle server, and the Oracle Instance
• Establishing a connection and creating a session

Getting Started with the Oracle Server

• Identifying database administrative tools

Identifying the features of the Universal Installer

Defining the Optimal Flexible Architecture

Creating a database during installation

Creating a new database

Identifying database administrative users

• Understanding operating system and password file authentication
• SQL*Plus
• Enterprise Manager

Managing an Oracle Instance

• Creating, managing, and using initialization files: PFILE and SPFILE
• Identifying the various states of starting an instance
• Identifying the various options available to shutdown an instance
• Monitoring Alert and Trace files

Creating a Database

• Using the Database Configuration Assistant to create a database
• Creating a database manually
•  

Using the Data Dictionary

• Identifying the uses and contents of the data dictionary
• Using the data dictionary to retrieve information about the database

Maintaining the Control File

• Explaining the uses of the control file
• Listing the contents of the control file
• Multiplexing the control file
• Managing control file with Oracle Managed Files (OMF)
• Obtaining control file information

Maintaining Redo Log Files

• Explaining the purpose of and how online redo log files work
• Control log switches and checkpoints
• Maintaining online redo log files
• Managing online redo log files using OMF
• Multiplexing and maintaining online redo log files
• Archiving online redo log files
• Obtaining online and archived redo log file information

Managing Tablespaces and Datafiles

Describing storage hierarchy

Establishing the difference between SYSTEM and Non-SYSTEM tablespaces

• Creating and managing tablespaces
• Creating a tablespace when using OMF
• Obtaining tablespace and datafile information

Storage Structures and Relationships

• Describing the logical structure of the database (segments, extents, blocks)
• Listing the segment types and their uses
• Understanding the use of extents and their allocation and deallocation
• Understanding the structure of a database block
• Maintaining storage structures with automatic segment - space management
• Maintaining storage structures manually
• Obtaining storage structure information

Managing Undo Data

• Understanding the difference between automatic undo management and manual undo management
• Creating and maintaining UNDO tablespaces
• Obtaining undo information

Managing Tables

• Creating and maintaining tables using appropriate storage settings
• Obtaining table information

Managing Indexes

• Listing the different types of indexes and their uses
• Creating b-tree and bitmap indexes
• Maintaining Indexes
• Identifying unused indexes
• Obtaining index Information

Maintaining Data Integrity

• Implementing data integrity constraints
• Maintaining integrity constraints
• Obtaining constraint information

Managing Password Security and Resources

• Managing passwords using profiles
• Administering profiles
• Controlling use of resources using profiles
• Managing resources using the Database Resource Manager
• Obtaining information about profiles, password management, and resources

Managing Users

• Creating new database users
• Altering and dropping existing database users
• Obtaining user information

Course Topics:

• Explaining solutions included with Oracle9i for managing complex networks
• Describing Oracle networking add-on solutions

Basic Oracle Net Architecture

• Explaining the key components of Oracle Net architecture
• Explaining Oracle Net Services role in client-server connections
• Describing how web client connections are established through Oracle networking products

Basic Oracle Net Services Server-Side Configuration

• Identifying How the Listener Responds to Incoming Connections
• Configuring the Listener using Oracle Net Manager
• Controlling the Oracle Net Listener by Using the Listener Control Utility
• Describing Dynamic Service Registration
• Configuring the Listener for IIOP and HTTP Connections

Basic Oracle Net Services Client-Side Configuration

• Describing the Difference Between Host Naming and Local Service Name Resolution
• Using Oracle Net Configuration Assistant to Configure Host Naming Method, Local Naming Method, and Net Service Names
• Performing Simple Connection Troubleshooting

Usage and Configuration of the Oracle Shared Server

• Identifying the Components of the Oracle Shared Server
• Describing the Oracle Shared Server Architecture
• Configuring Shared Servers by Using Initialization Parameters
• Configuring Dispatchers by Using Initialization Parameters
• Identifying Useful Data Dictionary Views

Backup and Recovery Overview

• Describing the Basics of Database Backup, Restore, and Recovery
• Listing the Types of Failures that May Occur in an Oracle Environment
• Defining a Backup and Recovery Strategy

Instance and Media Recovery Structures

• Describing the Oracle Processes, Memory Structures, and Files Relating to Recovery
• Identifying the Importance of Checkpoints, Redo Log Files, and Archived Log Files
• Describing Ways to Tune Instance Recovery

Configuration of the Database Archiving Mode

• Noarchivelog and Archivelog Mode Configuration
• Automatic and Manual Archiving of Redo Log Files
• Multiple Archival Destinations and Processes

Oracle Recovery Manager (RMAN) Overview and Configuration

• Identifying the Features and Components of RMAN
• Describing the RMAN Repository and Control File Usage
• Describing Channel Allocation
• Describing Media Management Library Interface
• Connecting to Recovery Manager
• Configuring the RMAN Environment

User-Managed Backups

• Describing User-Managed Backup and Recovery Operations
• Performing Closed Database Backups
• Performing Open Database Backups
• Cleaning Up After a Failed Online Backup
• Backing Up the Control File
• Discussing Backup Issues Associated with Read-Only Tablespaces
• Identifying the Backup Implications of Operations with NOLOGGING Options
• Using DBVerify to Check for Block Corruption

RMAN Backups

• Identifying RMAN Specific Backups
• Using the RMAN BACKUP Command to Create Backup Sets
• Backing Up the Control File
• Backing Up the Archived Redo Log Files
• Using the RMAN COPY Command to Create Image Copies

User-Managed Complete Recovery

• Describing Media Recovery
• Performing Recovery in Noarchivelog Mode
• Performing Recovery in Archivelog Mode
• Read-only Tablespace Recovery
• Relocating and Recovering a Tablespace

RMAN Complete Recovery

• Describing the Use of RMAN for Restoration and Recovery
• Performing Recovery in Noarchivelog Mode
• Performing Completing Recovery in Archivelog Mode
• Restoring Data Files to a Different Location
• Relocating and Recovering a Tablespace

User-Managed Incomplete Recovery

• Describing the Steps of Incomplete Recovery
• Identifying the Situations Which Require Incomplete Recovery
• Performing an Incomplete Recovery
• Recovering from the Loss of Current Online Redo Logs

RMAN Incomplete Recovery

• Performing Incomplete Recovery Using UNTIL TIME
• Performing Incomplete Recovery Using UNTIL SEQUENCE

RMAN Repository Maintenance

• Performing Crosschecking of Backups and Copies
• Updating the Repository When Backups Have Been Deleted
• Changing the Availability Status of Backups and Copies
• Making a Backup or Copy Exempt from the Retention Policy
• Cataloging Backups Made with Operating System Commands

RMAN Catalog Creation and Usage

• Describing the Contents of the Recovery Catalog
• Listing the RMAN Features Which Require the Recovery Catalog
• Creating the Recovery Catalog
• Maintaining the Recovery Catalog by Using RMAN Commands
• Using RMAN to Register, Resynchronize, and Reset a Database
• Querying the Recovery Catalog to Generate Reports and Lists
• Creating, Storing and Executing Scripts
• Describing Methods for Backing Up and Recovering the Recovery Catalog

Transporting Data Between Databases

• Export
• Import
• Guidelines for Using Export and Import

This course is designed to prepare you for the corresponding Oracle Certified Professional exam: Exam #1Z0-003.

• Job Roles in Tuning
• Tuning phases
• Tuning goals and Service Level Agreements
• Common performance problems
• Tuning Methodology

Diagnostic and Tuning Tools

• Alert log file
• Background process trace files
• User trace files
• Dictionary views providing statistics
• Dynamic performance views
• TIMED_STATISTICS parameter to collect statistics
• Statistics Package
• STATSPACK procedures

Sizing the Shared Pool

• Overview of the shared pool
• Library cache tuning
• Reuse statements
• Using Reserved Space
• Keeping Large Objects
• Related tuning issues
• Data Dictionary Cache (DDC) Tuning

Sizing the Buffer Cache

• Overview of tuning the buffer cache
• Buffer Cache Sizing Parameters in Oracle9i
• Depreciated Buffer Cache Parameters
• Buffer Cache Advisory Parameter
• Dynamically resizing SGA components
• Granules of Allocation
• Increase the size of a SGA component
• Resolving Techniques

Sizing other SGA Structures

• Sizing the redo log buffer
• Detecting contention
• Resolving contention
• Sizing the Java Pool
• Monitoring Java Pool Memory
• Sizing the SGA for Java
• Sizing Java Pool Memory
• Limiting Java Session Memory Usage

Database Configuration and I/O Issues

• Distributing files across devices
• Tablespace usage
• Detecting improper tablespace usage
• Moving datafiles
• Oracle File Striping
• Tuning Full Table Scans
• Tuning checkpoints
• Redo log file configuration

Optimize Sort Operations

• What is the sorting process
• Sort Area Parameters (New & Old)
• Tuning sort operations (DB Configuration)
• Tuning Temporary segments
• Reducing sort operations (application tuning)
• Identify sort operations
• Avoiding sort operations
• Diagnostic Tools

Diagnosing Contention For Latches

• Oracle¿s Implementation of latches
• Latch request types - willing-to-wait, immediate
• Reducing Latch Contention
• Significant Latches
• Shared Pool and Library Cache Latches

Tuning Rollback (or UNDO) Segments

• Describing Rollback segment usage
• Usage and configuration
• Detecting Problems with Rollback Segments
• Resolving Problems with Rollback Segments
• Creating Rollback Segments and bringing them online
• Allocating transactions to a rollback segment
• Resolving RBS problems
• Describing System Managed Undo

Monitoring and Detecting Lock Contention

• Concepts of Locking
• Overview of Locking issues
• Types of DML locks
• Levels of locks
• Types of DDL lock modes
• Detecting Blocking Locks
• Monitoring locking activity
• Deadlocks

Tuning Oracle Shared Server

• Introducing Oracle Shared Server
• When are Shared Servers required in Oracle9i
• Monitoring dispatcher processes
• Monitoring shared server processes
• Increasing or decreasing the number of Dispatchers
• Increasing or decreasing the number of Shared Servers
• Monitoring Process Usage
• Monitoring Memory Usage

Application Tuning

• Role of the DBA
• Oracle Data Structures
• Selecting the physical structure
• Data storage structures
• Clusters
• Indexes
• B-Tree
• Compressed

Using Oracle Blocks Efficiently

• Database Storage hierarchy
• Allocating extents
• Monitoring Space Usage in Data Segments
• Recovering space from sparsely populated segments
• Database Blocks
• Chaining and migration
• Detecting row chaining and migration
• Resolving row chaining and migration

SQL Statement Tuning

• Cost-based, Rule-based Optimiser Modes
• New Cost Based Optimiser option
• Plan Stability
• Plan Equivalence
• Stored Outlines
• Creating Stored Outlines
• Maintaining Stored Outlines
• Hints

Tuning the Operating System and Using Resource Manager

• System Architecture
• Virtual and Physical Memory
• Paging and Swapping
• Paging and Swapping
• CPU Tuning Guidelines
• Process Vs Thread
• Resource Manager Concepts