Powered by Olark

Oracle 11g DBA

Oracle DBA 11 g

The Oracle Database 11g Administrator Certification combines training, experience, and testing to ensure that you have a strong foundation and expertise in Oracle’s database management system.  The following track and training will earn you oracle DBA 11g certification.



Oracle Database 11g: SQL Fundamentals I
Exam Number: 1Z0-051
Duration: 24 Hrs

Course Objectives:

  • Retrieve row and column data from tables with the SELECT statement
  • Create reports of sorted and restricted data
  • Employ SQL functions to generate and retrieve customized data
  • Display data from multiple tables using the ANSI SQL 99 JOIN syntax
  • Create reports of aggregated data
  • Use the SET operators to create subsets of data
  • Run data manipulation statements (DML) to update data in the Oracle Database 11g
  • Run data definition language (DDL) statements to create and manage schema objects
  • Identify the major structural components of the Oracle Database 11g

Course Topic:

  • 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

Oracle Database 11g: Administration Workshop I
Exam Number: 1Z0-052
Duration: 40 Hrs
Course Objectives:

  • Monitor performance
  • Describe Oracle Database Architecture
  • Install Oracle Grid Infrastructure
  • Install and configure Oracle Database 11g
  • Configure Oracle Net services
  • Monitor and administer undo data
  • Manage the database storage structures
  • Create and administer user accounts
  • Perform basic backup and recovery of a database
  • Manage users and schemas
  • Manage data concurrency

Course Topics:
Exploring the Oracle Database Architecture

  • Oracle Database Architecture Overview
  • Oracle ASM Architecture Overview
  • Process Architecture
  • Memory structrues
  • Logical and physical storage structures
  • ASM storage components

Installing your Oracle Software

  • Tasks of an Oracle Database Administrator
  • Tools Used to Administer an Oracle Database
  • Installation: System Requirements
  • Oracle Universal Installer (OUI)
  • Installing Oracle Grid Infrastructure
  • Installing Oracle Database Software
  • Silent Install

Creating an Oracle Database

  • Planning the Database
  • Using the DBCA to Create a Database
  • Password Management
  • Creating a Database Design Template
  • Using the DBCA to Delete a Database

Managing the Oracle Database Instance

  • Start and stop the Oracle database and components
  • Use Oracle Enterprise Manager
  • Access a database with SQLPlus
  • Modify database installation parameters
  • Describe the stages of database startup
  • Describe database shutdown options
  • View the alert log
  • Access dynamic performance views

Manage the ASM Instance

  • Set up initialization parameter files for ASM instance
  • Start up and shut down ASM instances
  • Administer ASM disk groups

Configuring the Oracle Network Environment

  • Use Enterprise Manager to create and configure the Listener
  • Enable Oracle Restart to monitor the listener
  • Use tnsping to test Oracle Net connectivity
  • Identify when to use shared servers and when to use dedicated servers

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)

Administering User Security

  • Database User Accounts
  • Predefined Administrative Accounts
  • Benefits of Roles
  • Predefined Roles
  • Implementing Profiles

Managing Data Concurrency

  • Data Concurrency
  • Enqueue Mechanism
  • Resolving Lock Conflicts
  • Deadlocks

Managing Undo Data

  • Data Manipulation
  • Transactions and Undo Data
  • Undo Data Versus Redo Data
  • Configuring Undo Retention

Implementing Oracle Database Auditing

  • Describe DBA responsibilities for security
  • Enable standard database auditing
  • Specify audit options
  • Review audit information
  • Maintain the audit trail

Database Maintenance

  • Manage optimizer statistics
  • Manage the Automatic Workload Repository (AWR)
  • Use the Automatic Database Diagnostic Monitor (ADDM)
  • Describe and use the advisory framework
  • Set alert thresholds
  • Use server-generated alerts
  • Use automated tasks

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

  • Describe ways to move data
  • Create and use directory objects
  • Use SQL*Loader to move data
  • Use external tables to move data
  • General architecture of Oracle Data Pump
  • Use Data Pump export and import to move data

Working with Support

  • Use the Enterprise Manager Support Workbench
  • Work with Oracle Support
  • Log service requests (SR)
  • Manage patches

Oracle Database 11g: Administration Workshop II
Exam Number: 1Z0-053
Duration: 40 Hrs
Course Objectives:

  • Back up and recover a database
  • Configure Oracle Database for optimal recovery
  • Administer ASM disk groups
  • Use an RMAN backup to duplicate a database
  • Automating Tasks with the Scheduler

Course Topics:

  • 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