Information Technology Certifications | School of Continuing Studies | Brooklyn Campus

Information Technology Certifications at the Brooklyn Campus of Long Island University

ORACLE 11G DATABASE ADMINISTRATION (128 hours)

The Oracle 11g Database Administration program delivers knowledge of database installation, management and administration tasks and an understanding of the Oracle database architecture. By earning an Oracle Certified Professional credential, database professionals can accelerate their careers and significantly enhance their professional growth.
An Oracle Certified Professional (OCP) database administrator with the 11g credential has mastered the skill sets needed to address critical database functions such as manageability, performance, reliability, security, and availability using the latest Oracle technology.

AUDIENCE AND PREREQUISITES
This course is designed for individuals who have some computer and database experience and wish to prepare for advanced skills in the installation, configuration and administration of Oracle Enterprise Databases. Related experience might include familiarity with Microsoft Windows, Microsoft Office, programming and/or other databases.
Total Hours: 128
CEUs: 12.8

Tuition: $5,900.

Fall 2008 Schedule

Schedule to be announced.

OVERVIEW
This hands-on course will teach you to effectively plan, install, configure, administer, query, troubleshoot and manage Oracle Databases. All books and materials are included. Through four modules, including hands-on exercises and professional guidance, students will gain a broad and deep foundation in Oracle Database planning, design, installation and administration. The course includes modules on SQL query languages and both levels of Oracle 11g certification: Oracle Certified Associate (OCA) and Oracle Certified Professional (OCP). The course will prepare students for the Oracle exams, as well as deliver a solid foundation in the real world application of their database skills.
Learn how to:

Install, configure, and administer Oracle Database systems
Manage and maintain an Oracle Database
Plan, design and create tables
Create and update reports from Oracle Databases
Query Oracle databases using SQL and iSQL*Plus to retrieve critical business data
Backup and recovery of database systems
Tune and optimize a Oracle Database for most efficient use of computer resources

COURSE OUTLINE
Module 1: Oracle Database 11g: SQL Fundamentals I
Introduction

Listing the features of Oracle Database 11g
Discussing the basic design, theoretical and physical aspects of a relational database
Describing the development environments for SQL
Describing Oracle SQL Developer
Describing the data set used by the course

Retrieving Data Using the SQL SELECT Statement

Listing the capabilities of SQL SELECT statements.
Generating a report of data from the output of a basic SELECT statement
Using arithmetic expressions and NULL values in the SELECT statement
Using Column aliases
Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
Displaying the table structure using the DESCRIBE command

Restricting and Sorting Data

Writing queries with a WHERE clause to limit the output retrieved
Using the comparison operators and logical operators
Describing the rules of precedence for comparison and logical operators
Using character string literals in the WHERE clause
Writing queries with an ORDER BY clause to sort the output
Sorting output in descending and ascending order
Using the Substitution Variables

Using Single-Row Functions to Customize Output

Differentiating between single row and multiple row functions
Manipulating strings using character functions
Manipulating numbers with the ROUND, TRUNC and MOD functions
Performing arithmetic with date data
Manipulating dates with the date functions

Using Conversion Functions and Conditional Expressions

Describing implicit and explicit data type conversion
Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
Nesting multiple functions
Applying the NVL, NULLIF, and COALESCE functions to data
Using conditional IF THEN ELSE logic in a SELECT statement

Reporting Aggregated Data Using the Group Functions

Using the aggregation functions in SELECT statements to produce meaningful reports
Using AVG, SUM, MIN, and MAX function
Handling Null Values in a group function
Creating queries that divide the data in groups by using the GROUP BY clause
Creating queries that exclude groups of date by using the HAVING clause

Displaying Data From Multiple Tables

Writing SELECT statements to access data from more than one table
Joining Tables Using SQL:1999 Syntax
Viewing data that does not meet a join condition by using outer joins
Joining a table by using a self join
Creating Cross Joins

Using Sub-queries to Solve Queries

Using a Subquery to Solve a Problem
Executing Single-Row Sub-queries
Using Group Functions in a Sub-query
Using Multiple-Row Subqueries
Using the ANY and ALL Operator in Multiple-Row Sub-queries

Using the SET Operators

Describing the SET operators
Using a SET operator to combine multiple queries into a single query
Using UNION, UNION ALL, INTERSECT, and MINUS Operator
Using the ORDER BY Clause in Set Operations

Manipulating Data

Adding New Rows to a Table Using the INSERT statement
Changing Data in a Table Using the UPDATE Statement
Using DELETE and TRUNCATE Statements
Saving and discarding changes with the COMMIT and ROLLBACK statements
Implementing Read Consistency
Using the FOR UPDATE Clause

Using DDL Statements to Create and Manage Tables

Categorizing Database Objects
Creating Tables using the CREATE TABLE Statement
Describing the data types
Describing Constraints
Creating a table using a subquery
Altering and Dropping a table

Creating Other Schema Objects

Creating, modifying, and retrieving data from a view
Performing Data manipulation language (DML) operations on a view
Dropping a view
Creating, using, and modifying a sequence
Creating and dropping indexes
Creating and dropping synonyms

Module 2: PL/SQL
What is PL/SQL

PL/SQL Environment
Benefits of PL/SQL
Stored Procedures, Functions, and Triggers
Stored Procedures and SQL Scripts

Structure of a PL/SQL Block

Header Section
Declaration Section
Execution Section
Exception Section

Creating a PL/SQL Procedure
Creating a PL/SQL Function
PL/SQL Variables and Constants

Declaring PL/SQL Variables
Declaring PL/SQL Constants
Assigning Values to Variables
Using Variables

Control Structures in PL/SQL

IF Statement
Loops
Cursors
Nested Loops

Error Handling

Exceptions
System Defined Exceptions
Programmer Defined Exceptions

Other PL/SQL Tools

PL/SQL Packages
Triggers
Views used with PL/SQL

Module 3: Oracle 11g Certified Associate
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

Module 4: Oracle 11g Certified Professional
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

School of Continuing Studies - Long Island University - Brooklyn Campus -1 University Plaza -Brooklyn, NY 11201-5372 - 718 488-1364

Long Island University

Brooklyn Campus

School of Continuing Studies