Oracle 9i Certification

Oracle Certified Associate / Oracle Certified Professional

(Oracle Certified DBA)

 The Oracle Certified Associate/Professional (OCA): an entry-level Oracle qualification requiring successful completion of two exams.

The Oracle Certified Professionals (OCP/DBA): requires successful completion of all four exams

1. Introduction to Oracle9i: SQL

Description: This course offers students an extensive introduction to data server technology. The class covers the concepts of both relational and object relational databases and the powerful SQL programming language. Students are taught to create and maintain database objects and to store, retrieve, and manipulate data. Students learn to retrieve data by using advanced techniques such as ROLLUP, CUBE, set operators, and hierarchical retrieval. They also learn to write SQL and SQL*Plus script files to generate report-like output. This course is appropriate for students using an Oracle7.3, Oracle8, Oracle8i or Oracle9i database. NASIT instructors are familiar with all versions of Oracle and will point out version specific differences during the course. Demonstrations and hands-on practice reinforce the fundamental concepts.

Topics:

Introduction to SQL

• Describe the Lifecycle Development Phases
• Define a Database
• Discuss the Theoretical, Conceptual, and Physical Aspects of a Relational Database
• Describe how a Relational Database Management System (RDBMS) is used to Manage a Relational Database

Describe the Oracle Implementation of both the RDBMS and the Object Relational Database Management System (ORDBMS)

• Understand how SQL is used in the Oracle Product Set

Write a Basic SQL Statement

• Describe the SQL SELECT capabilities
• Execute a Basic Select Statement with the Mandatory Clauses
• Differentiate between SQL and SQL*Plus commands

Restrict and Sort Data

• Limit the Rows Retrieved by a Query
• Sort the Rows Retrieved by a Query

Use Single Row Functions

• Describe Various Types of Functions Available in SQL
• Use a Variety of Character, Number, and Date Functions in SELECT Statements
• Explain what the Conversion Functions are and how they are used
• Use Control Statements

Display Data from Multiple Tables

• Write SELECT Statements to Access Data from More Than One Table
• Describe the Cartesian Product
• Describe and Use the Four Different Types of Join
• Write Joins Using the Tips Provided

Aggregate Data using Group Functions

• Identify the Different Group Functions Available
• Explain the Use of Group Functions
• Group Data Using the GROUP BY Clause
• Include or Exclude Grouped Rows using the HAVING Clause

Write Subqueries

• Describe the types of Problems That Subqueries Can Solve
• Describing What Subqueries Are
• List the Types of Subqueries
• Write Single-Row and Multi-Row Subqueries
• Explain the behaviour of Subqueries when Null Values are Retrieved

Produce Readable Output with SQL*Plus

• Produce Queries that Require an Input Variable
• Customize the SQL*Plus Environment
• Produce More Readable Output
• Create and Execute Script Files
• Save Customizations

Manipulate Data

• Describe Each Data Manipulation Language (DML) Command
• Insert Rows into a Table
• Update Rows in a Table
• Delete Rows from a Table
• Control Transactions
• Describe Transaction Processing
• Understand Read Consistency
• Explain Implicit and Explicit Locking

Create and Manage Tables

• Describe the Main Database Objects
• Creating Tables
• Describe the Datatypes
• Alter Table Definitions
• Drop, Rename, and Truncate Tables

Include Constraints

• Describe Constraints
• Create and Maintain Constraints

Create Views

• Describe Views and their Uses
• Create a View
• Retrieve Data via a View
• Insert, Update, and Delete Data through Views
• Drop Views
• Alter the Definition of a View
• Create Inline Views
• Perform Top ‘N’ Analysis

Other Database Objects

• Create, Maintain, and Use Sequences
• Create and Maintain Indexes
• Create Private and Public Synonyms

Control User Access

• Understand the Concepts of Users, Roles, and Privileges
• Grant and Revoke Object Privileges
• Create Roles and Grant Privileges to Roles
• Create Synonyms for Ease of Table Access

Complete the SQL Workshop

• Apply Techniques Learned in This Course
• Prepare for Future Oracle Courses

Use Set Operators

• Describe the Set Operators
• Obey the Set Operators Rules and Guidelines
• Use Set Operator to Combine Multiple Queries into a Single Subquery
• Control the Order of Rows Returned

Learn the Oracle9i Single Row Functions

• Use DATETIME functions
• Use the NVL2 Function to Handle NULL Values

Understand the Enhancements to the GROUP BY Clause

• Use ROLLUP as an Extension to the GROUP BY Clause to Produce Subtotal Values
• Use CUBE as an Extension to the GROUP BY Clause to Produce Cross-Tabulation Values
• Use the GROUPING Function to Identify the Row Values Created by ROLLUP or CUBE Operators
• Create GROUPING SETS to Produce a Single Result Set that is Equivalent to a UNION ALL Approach
• Include the WITH Clause

Produce Advanced Subqueries

• Update and Delete Rows by Using Correlated Subqueries
• Write a Multicolumn Subquery
• Write a Subquery in a FROM Clause
• Describe the Types of Problems that can be Solved with a Correlated Subquery
• Write Correlated Subqueries
• Use the EXISTS and NOT EXISTS Operators

Write Hierarchical Retrieval Queries

• Discuss the Benefits of the Hierarchical Query
• Order the Rows Retrieved by a Query in a Hierarchical Manner
• Format Hierarchical Data so that it is Easy to Read
• Exclude Branches from the Tree Structure

Understand the Extensions to DDL and DML Statements

• Discuss Multitable Inserts
• Create and Use External Tables
• Name the Index and Use the CREATE INDEX Command at the Time of Creating a Primary Key Constraint

Write Scripts to Generate Scripts

• Describe the Types of Problems that are Solved by Writing SQL Scripts that Generate Other SQL Scripts
• Write and Execute Scripts that Generate Scripts with Commands to Create and Drop Tables

• Write and Execute a Script that Generates a Script of INSERT INTO Commands

2. Oracle9i DBA Fundamentals I: Architecture and Administration

Description: This course is designed to give the Oracle database administrator (DBA) a firm foundation in basic administrative tasks and provide the necessary knowledge and skills to set up, maintain, and troubleshoot an Oracle7.3, Oracle8, Oracle8i or Oracle9i database. Students will gain a conceptual understanding of the Oracledatabase architecture and how the architectural structures work and interact with one another. Students will also learn how to create a database and properly manage the various structures in an effective and efficient manner in order to have a well-designed and operational database. This course is appropriate for students using an Oracle7.3, Oracle8, Oracle8i or Oracle9i database. NASIT instructors are familiar with all versions of Oracle and will point out version specific differences during the course. The instructor-led lesson topics are reinforced with structured hands-on practices.

Topics:

Discuss the Oracle Architectural Components

• Understand the Physical Structure
• Understand the Memory Structure
• Understand the Process Structure
• Understand the Logical Structure
• Describe the Stages used to Process an SQL Statement
• Define an Oracle Database, the Oracle Server, and the Oracle Instance

Get Started with the Oracle Server

• Identify the Features of the Universal Installer
• Identify Database Administration Tools
• Set Up Operating System and Password File Authentication
• Understand the Main Components of Oracle Enterprise Manager

Manage an Oracle Instance

• Create, Manage, and Use Initialization Files: PFILE and SPFILE
• Identify the Various States of Starting an Instance
• Learn to Start Up an Instance and Open the Database
• Learn to Close a Database and Shut Down the Instance
• Manage Sessions
• Monitor the ALERT File and Trace Files

Create a Database

• Prepare the Operating System
• Prepare the Parameter File
• Create a Database Manually
• Use the Database Configuration Assistant to Create a Database

Use the Data Dictionary

• Identify the Uses and Contents of the Data Dictionary
• Use the Data Dictionary to Obtain Information about the Database
• Query the Dynamic Performance Views

Maintain the Control File

• Explain the Use of the Control File
• List the Contents of the Control File
• Multiplex the Control File
• Obtain Control File Information

Maintain Redo Log Files

• Explain the Use of Online Redo Log Files
• Obtain Redo Log Information
• Control Log Switches and Checkpoints
• Multiplex and Maintain Online Redo Log Files
• Manage Online Redo Log Files with Oracle Managed Files (OMF)

Manage Tablespaces and Data Files

• Outline the Database Storage Hierarchy
• Identify the Types of Tablespaces
• Distinguish the Different Types of Temporary Segments
• Create Tablespaces
• Change the Size of Tablespaces
• Allocate Space for Temporary Segments
• Change the Status of Tablespaces
• Change the Storage Settings of Tablespaces
• Relocate Tablespaces

Understand Storage Structure and Relationships

• Describe the Logical Structure of the Database
• List the Segment Types and their Uses
• Outline the Storage Clause Precedence
• List the Keywords that Control Block Space Usage
• Obtain Information About Storage Structures from the Data Dictionary
• List the Criteria for Separating Segments

Manage Undo Data

• Describe the Purpose of Undo Data
• Manage Undo Data
• Implement Automatic Undo Management
• Create and Configure Undo Segments
• Obtain Undo Segment Information from the Data Dictionary

Manage Tables

• Outline the various Oracle Datatypes
• Explain the two types of ROWID Formats
• Create Regular and Temporary Tables
• Manage Storage Structure within a Table
• Understand the Structure of a Row
• Reorganize, Truncate and Drop Tables
• Retrieve Information About Tables from the Data Dictionary

Manage Indexes

• List the Different Types of Indexes and their Uses
• Create B*-Tree and Bitmap Indexes
• Reorganize Indexes
• Drop Indexes
• Obtain Index Information from the Data Dictionary
• Monitor the Usage of an Index

Maintain Data Integrity

• Identify the various Types and States of Constraints
• Implement Data Integrity Constraints
• Maintain Integrity Constraints
• Create an Exceptions Table
• Obtain Constraint Information from the Data Dictionary

Manage Password Security and Resources

• Manage Passwords Using Profiles
• Administer Profiles
• Control Use of Resources Using Profiles
• Obtain Information About Profiles, Password Management and Resources

Manage Users

• Define Database Schema and Objects
• Create New Database Users
• Alter and Drop Existing Database Users
• Monitor Information about Existing Users

Manage Privileges

• Identify System and Object Privileges
• Grant and Revoke Privileges
• Identify Auditing Capabilities

Manage Roles

• Create and Modify Roles
• Control Availability of Roles
• Remove Roles
• Use Predefined Roles
• Display Role Information from the Data Dictionary

Use Globalization Support

• Choose Character Set and National Language Character Set for a Database
• Specify the Language-Dependent Behavior Using Initialization Parameters, Environment Variables, and the ALTER SESSION Command
• Use Different Types of National Language Support (NLS) Parameters
• Obtain Information about Globalization Support Usage

3. Oracle9i DBA Fundamentals II: Backup and Recovery

Description: This course is designed to give the Oracle database administrator (DBA) a firm foundation in fundamental database administrative tasks. Loading and transporting for databases, and the utilities used to perform these activities are described. Then networking concepts and configuration parameters, as well as how to solve some common network problems, are covered. In hands-on exercises, participants configure network parameters so database clients and tools can communicate with the Oracle database server. This course also addresses backup and recovery techniques and examines various backup, failure, restore and recovery scenarios. Participants also examine backup methodologies based on business requirements in a mission critical enterprise. Participants will utilize multiple strategies and Oracle Recovery Manager to perform backups, and restore and recovery operations. This course is appropriate for students using an Oracle7.3, Oracle8, Oracle8i or Oracle9i database. NASIT instructors are familiar with all versions of Oracle and will point out version specific differences during the course. This class includes two interactive workshops that will provide participants with the opportunity to walk through numerous real-world case studies.

Topics:

Networking Overview

• Understand the Basic Oracle Net Architecture
• Explain Solutions for Managing Complex Networks
• Describe Oracle Networking Add-on Solutions

Explain the Oracle Net Architecture

• Define the Key Components of Oracle Net Layered Architecture
• Understand Oracle Net Services Role in Client-Server Connections
• Describe how Web Client Connections are Established through Oracle Networking Products

Explain Basic Net Services Server-Side Configuration

• Identify how the Listener Responds to Incoming Connections
• Configure the Listener using Oracle Net Manager
• Control the Oracle Net Listener using the Listener Control Utility (LSNRCTL)
• Describe Dynamic Service Registration
• Configure the Listener for IIOP and HTTP Connections

Explain Basic Net Services Client-Side Configuration

• Describe the Difference between Host Naming and Local Service Name Resolution
• Use Oracle Net Configuration Assistant to Configure Host Naming Method, Local Naming Method, and Net Service Names
• Perform Simple Connection Troubleshooting

Describe Usage and Configuration of the Oracle Shared Server

• Identify the Components of the Oracle Shared Server
• Describe the Oracle Shared Server Architecture
• Configure Shared Servers Using Initialization Parameters
• Identify the Related Data Dictionary Views

Backup and Recovery Overview

• Describe the Basics of Database Backup, Restore, and Recovery
• List the Types of Failures that May Occur in an Oracle Environment
• Define a Backup and Recovery Strategy

Understand Instance and Media Recovery Structures

• Describe the Oracle Processes, Memory Structures and Files Relating to Recovery
• Observe the Importance of Checkpoints, Redo Log Files, and Archive Log Files
• Describe Ways to Tune Instance Recovery
• Multiplex Control Files and Redo Logs

Configure the Database Archiving Mode

• Describe the Differences between Archivelog and Noarchivelog Modes
• Configure a Database in Noarchivelog Mode
• Configure a Database in Archivelog Mode
• Configure Remote Archival of Redo Logs
• Perform Manual Archive of Log Files
• Enable/Disable Automatic Archiving
• Configuring Multiple Archival Destinations and Processes

Describe Oracle Recovery Manager (RMAN) Architecture and Configuration

• Identify the Features and Components of RMAN
• Describe the Advantages of RMAN
• Describe the Channel Allocation
• Describe the Media Management Library Interface
• Connect to Recovery Manager without the Recovery Catalog
• Configure the RMAN Environment

Perform User-Managed Backups (without RMAN)

• Describe User-Managed Backup and Recovery Operations
• Perform Backup of a Closed Database
• Perform Backup of an Opened Database
• Backup the Control Files
• Discuss Backup Issues Associated with "Read-Only" Tablespaces
• Perform Cleanup after a Failed Online Backup
• Use the DBVERIFY Utility to Detect Corruption
• List the Data Dictionary Views useful for Backup Operations

Perform Physical Backups Using RMAN

• Identify types of RMAN Specific Backups
• Describe Backup Concepts Using RMAN
• Perform Incremental and Cumulative Backups
• Backup the Control File
• Backup the Archived Redo Log Files
• View Information from the Data Dictionary

Perform Complete Recovery of an Oracle Database

• Describe Media Recovery
• Note the Implications of Media Failure with a Database in Noarchivelog Mode
• Recover (Restore) a database in Noarchivelog Mode after Media Failure
• Note Implications of Instance Failure with an Archivelog Mode Database
• Perform Recovery of a Database in Archivelog Mode after Media Failure
• Restore Datafiles to Different Locations
• Relocate and Recover a Tablespace by Using Archived Redo Log Files
• Describe Read-only Tablespace Recovery

Restore and Recover Using RMAN

• Describe the Use of RMAN for Restoration and Recovery
• Restore a Database in Noarchivelog Mode
• Perform a Complete Recovery in Noarchivelog Mode
• Restore Datafiles to Different Locations
• Restore and Recover a Tablespace Using Archived Redo Log Files

Perform a User-Managed Incomplete Recovery

• Understand the Reasons for Performing an Incomplete Recovery
• Describe the Steps of an Incomplete Recovery
• Perform an Incomplete Database Recovery
• Identify the Loss of Online Current Redo Log Files

Perform a RMAN Incomplete Recovery

• Describe the Steps of an Incomplete Recovery Using RMAN
• Perform an Incomplete Recovery Using UNTIL TIME
• Perform an Incomplete Recovery Using UNTIL SEQUENCE

Maintain RMAN

• Perform Cross Checking of Backups and Copies
• Update the Repository when Backups have Been Deleted
• Change the Availability Status of Backups and Copies
• Make a Backup or Copy Exempt from the Retention Policy
• Catalog Backups Made with Operating System Commands

Create and Maintain Oracle Recovery Catalog

• Describe the Contents of the Recovery Catalog
• List the RMAN features that Require the Recovery Catalog
• Create the Recovery Catalog
• Maintain the Recovery Catalog using RMAN Commands
• Use RMAN to Register, Resynchronize, and Reset a Database
• Query the Recovery Catalog to Generate Reports and Lists

Transport Data Between Databases

• Describe the Uses of the Export and Import Utilities
• Describe Export and Import Concepts and Structures
• Perform Simple Export and Import Operations
• Invoke Direct-Path Method Export
• Use the Import Utility to Recover a Database Object

Load Data into a Database

• Demonstrate the Usage of Direct-Load Insert Operations
• Describe the Usage of SQL*Loader
• Perform basic SQL*Loader Operations
• List Guidelines for Using SQL*Loader and Direct-Load Insert

Enterprise DBA Part 2: Performance Tuning

Description: This course will introduce participants to a series of tuning steps which can be used to improve the performance of the Oracle8i Server. The focus is on database rather than specific operating system performance issues. Participants will gain practical experience tuning an Oracle database. Using a variety of tools, participants also learn how to recognize, troubleshoot and resolve common performance related problems in administering an Oracle database. The class includes a one-day interactive workshop that provides participants with the opportunity to walk through ‘real-world’ performance tuning scenarios including problem diagnosis and performance enhancement.

Topics:

Oracle Performance Tuning Methodology

• Listing the different roles associated with the tuning process
• Defining the steps associated with the tuning process
• Identifying different tuning goals

Oracle Alert and Trace Files

• Identifying the location and usefulness of the alert log file
• Identifying the location and usefulness of the background and user process trace files

Utilities and Dynamic Performance Views

• Collecting statistics using the dynamic troubleshooting and performance views
• Diagnosing statistics using the UTBSTAT/UTLESTAT output report
• Identifying the different types of latches
• Retrieving and displaying wait events
• Setting events through OEM to be alerted about predefined situations
• Using appropriate OEM tuning tools

Tuning the Shared Pool

• Tuning the library cache and data dictionary cache
• Measuring the shared pool hit percentage
• Sizing the shared pool appropriately
• Pinning objects in the shared pool
• Tuning the shared pool reserved space
• Listing the UGA and session memory considerations

Tuning the Buffer Cache

• Describing how the buffer cache is managed
• Calculating the buffer cache hit ratio
• Examining the impact of adding or removing buffers
• Creating multiple buffer pools
• Sizing multiple buffer pools
• Monitoring buffer cache usage
• Making appropriate use of table caching
• Identifying performance issues associated with buffer cache latches

Tuning the Redo Log Buffer

• Determining if processes are waiting for space in the redo log buffer
• Sizing the redo log buffer appropriately
• Reducing redo operations
• Identifying performance issues associated with the redo buffer latches

Database Configuration and I/O Issues

• Diagnosing inappropriate use of SYSTEM, RBS, TEMP, DATA and INDEX tablespaces
• Detecting I/O problems
• Ensuring that files are distributed to minimize I/O contention
• Using striping where appropriate
• Tuning checkpoints
• Tuning background process I/O

Using Oracle Blocks Efficiently

• Determining an appropriate block size
• Optimizing space usage within blocks
• Detecting and resolving row migration
• Monitoring and tuning indexes
• Appropriately sizing extents

Optimizing Sort Operations

• Identifying the SQL operations that require sorts
• Ensuring that sorting is done in memory where possible
• Allocating temporary space appropriately

Rollback Segment Tuning

• Using the dynamic performance views to check rollback segment performance
• Reconfiguring and monitoring rollback segments
• Defining the number and sizes of rollback segments
• Allocating rollback segments to specific transactions

Monitoring and Detecting Lock Contention

• Defining the levels of Oracle locking
• Listing possible causes of lock contention
• Using Oracle utilities to diagnose lock contention
• Resolving contention in an emergency
• Preventing locking problems
• Recognizing Oracle errors arising from deadlocks

SQL Issues and Tuning Considerations for Different Applications

• Identifying the role of the DBA in application tuning
• Using optimizer modes to enhance SQL statement performance
• Managing stored outlines to store execution paths as a series of hints
• Using the available data access methods to tune the physical design of the database. Identify the demands of online transaction processing systems (OLTP).
• Identify the demands of decision support system

Managing Mixed Workload

• Resource Management Concepts
• Steps in Database Resource Management
• Dictionary View

Tuning with Oracle Expert

• List the features of Oracle Expert
• Creating a tuning session
• Gathering, viewing, and editing the input data
• Analyzing the collected data using rules
• Reviewing tuning recommendations
• Implementing tuning recommendations

Multithreaded Server Tuning Issues (Optional)

• Identifying issues associated with managing users in a multithreaded server environment
• Diagnosing and resolving performance issues with multithreaded server processes
• Configuring the multithreaded server environment to optimize performance

Use a Tuning Methodology to Diagnose and Resolve Performance Tuning Issues

• Using Oracle tools to diagnose performance problems
• Tuning memory structures, file I/O, and contention