COURSE OUTLINE

Oracle Database 10g: Database Administration Workshop

What you will learn:

This course is your first step towards success as an Oracle professional, designed to give you a firm foundation in basic database administration. In this class, you'll learn how to install and maintain an Oracle database. You will gain a conceptual understanding of the Oracle database architecture and how its components work and interact with one another. You will also learn how to create an operational database and properly manage the various structures in an effective and efficient manner including performance monitoring, database security, user management, and backup/recovery techniques. The lesson topics are reinforced with structured hands-on practices. This course is designed to prepare you for the corresponding Oracle Certified Associate exam.

Course Topics:

Writing SQL SELECT statements

• Define projection, selection, and join terminology
• Review the basic SQL SELECT statement syntax
• Select all columns using a wildcard notation from a table
• State simple rules and guidelines for writing SQL statements
• Write a query containing the arithmetic operators
• Use aliases to customize column headings
• Create a character expression with the concatenation operator
• Use the quote operator q to control text strings containing the quote character

Restricting Data

• Limit Rows Using a Selection
• Use the WHERE Clause
• List the main Comparison Conditions
• Use the LIKE Condition to Compare Literal Values
• List the Logical Conditions AND, OR, NOT
• Use Multiple Conditions in the WHERE clause
• Describe the Rules of Precedence

Sorting Data

• Sort Rows with the ORDER BY Clause
• Use the && Substitution Variable
• Use the iSQL*Plus DEFINE Command
• Use the VERIFY Command

SQL Functions

• Differentiate between Single Row and Multiple Row SQL Functions
• Categorize the Character Functions into Case Manipulation and Character Manipulation types
• Explain the Numeric Functions ROUND, TRUNC, and MOD
• List the Rules for Applying the Arithmetic Operators on Dates
• Use the Arithmetic Operators with Dates in the SELECT Clause
• Explain the DATE functions MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, and TRUNC
• Explain Implicit and Explicit conversion
• Nest Functions to Perform Multiple Tasks in One Statement

Conditional Expressions

• Use the CASE Expression
• Explain the DECODE Expression

Aggregating Data Using Group Functions

• Categorize the Types of Group Functions
• Use the AVG, SUM, MAX, MIN, and COUNT Functions in a Query
• Utilize the DISTINCT Keyword with the Group Functions
• Describe how Nulls are handled with the Group Functions
• Create Groups of Data with the GROUP BY Clause
• Group Data by more than one column
• Avoid Illegal Queries with the Group Functions
• Exclude Groups of Data with the HAVING Clause

Displaying Data from Multiple Tables

• Identify Types of Joins
• Retrieve Records with Natural Joins
• Use Table Aliases to write shorter code and explicitly identify columns from multiple tables
• Create a Join with the USING clause to identify specific columns between tables
• Use the ON clause to specify arbitrary conditions or specify columns to Join
• Create a Three-way join with the ON clause to retrieve information from 3 tables
• List the Types of Outer Joins LEFT, RIGHT, and FULL
• Generating a Cartesian Product

Using Subqueries

• Use a sub query to solve a problem
• Identify where sub queries can be placed in a SELECT statement
• Describe the types of sub queries (single row, multiple row)
• Show the single row sub query operators
• Use the group functions in a sub query
• Identify illegal statements with sub queries
• Show the multiple row sub query operators
• Explain how null values are handled in sub queries

Using the Set Operators

• Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
• Use the UNION ALL operator to return all rows from multiple tables (with duplicates)
• Describe the INTERSECT operator
• Use the INTERSECT operator
• Explain the MINUS operator
• Use the MINUS operator
• List the SET operator guidelines
• Order results when using the UNION operator

Inserting and Updating Data

• Write INSERT statements to add rows to a table
• Insert Special Values
• Copy Rows from Another Table
• Update Rows in a Table

Deleting Data

• Use DELETE statements to remove rows from a table
• Delete Rows Based on Another Table
• Describe the TRUNCATE Statement

Database Transactions

• Save and Discard Changes to a Table through Transaction Processing (COMMIT, ROLLBACK, and SAVEPOINT)
• Show how Read Consistency works

Using DDL Statements

• List the main database objects
• Identify the Naming Rules
• Display the basic Syntax for Creating a Table
• Show the DEFAULT option
• List the Data Types that are available for Columns

Managing Tables

• Explain the different types of constraints
• Show resulting exceptions when constraints are violated with DML statements
• Create a table with a sub query
• Describe the ALTER TABLE functionality
• Remove a table with the DROP statement

Creating Other Schema Objects

• Categorize simple and complex views and compare them
• Create a view
• Retrieve data from a view
• Explain a read-only view
• List the rules for performing DML on complex views
• Create a sequence
• List the basic rules for when to create and not create an index
• Create a synonym

Managing Objects with Data Dictionary Views

• Describe the structure of each of the dictionary views
• List the purpose of each of the dictionary views
• Write queries that retrieve information from the dictionary views on the schema objects
• Use the COMMENT command to document objects

Introduction (Database Architecture)
Describe course objectives
Explore the Oracle 10g database architecture

Installing the Oracle Database Software on UNIX
Explain core DBA tasks and tools
Plan an Oracle installation
Use optimal flexible architecture
Install software with the Oracle Universal Installer (OUI)

Creating an Oracle Database
Create a database with the Database Configuration Assistant (DBCA)
Create a database design template with the DBCA
Generate database creation scripts with the DBCA

Managing the Oracle Instance
Start and stop the Oracle database and components
Use Enterprise Manager (EM)
Access a database with SQL*Plus and iSQL*Plus
Modify database initialization parameters
Understand the stages of database startup
View the Alert log
Use the Data Dictionary

Managing Database Storage Structures
Describe table data storage (in blocks)
Define the purpose of tablespaces and data files
Understand and utilize Oracle Managed Files (OMF)
Create and manage tablespaces
Obtain tablespace information

Administering User Security
Create and manage database user accounts
Authenticate users
Assign default storage areas (tablespaces)
Grant and revoke privileges
Create and manage roles
Create and manage profiles

Configuring the Oracle Network Environment
Use Enterprise Manager for configuring the Oracle network environment
Create additional listeners
Create Net Service aliases
Configure connect-time failover
Control the Oracle Net Listener
Test Oracle Net connectivity
Identify when to use shared versus dedicated servers

Automatic Performance Management
The Use of 10g Database Advisors
Identifying Tunable Components
Advisory Framework
Using the SQL Tuning and SQL Access Advisor
Manage the Automatic Workload Repository (AWR)
Use the Automatic Database Diagnostic Monitor (ADDM)
Describe advisory framework

Using AWR
Create and manage AWR snapshots
Generate AWR reports
Create snapshot sets and compare periods
Generate ADDM reports
Generate ASH reports

Backup and Recovery Concepts
Identify the types of failure that may occur in an Oracle Database
Describe ways to tune instance recovery
Identify the importance of checkpoints, redo log files, and archived log files
Configure ARCHIVELOG mode

Performing Database Backups
Create consistent database backups
Back your database up without shutting it down
Create incremental backups
Automate database backups
Monitor the flash recovery area

Performing Database Recovery
Recover from loss of a control file
Recover from loss of a redo log file
Perform complete recovery following the loss of a data file

Configuring Recovery Manager
Recovery Manager Features and Components
Using a Flash Recovery Area with RMAN
Configuring RMAN
Control File Autobackups
Retention Policies and Channel Allocation
Using Recovery Manager to connect to a target database in default NOCATALOG mode
Displaying the current RMAN configuration settings
Altering the backup retention policy for a database

Using Recovery Manager
RMAN Command Overview
Parallelization of Backup Sets
Compressed Backups
Image Copy
Whole Database and Incremental Backups
LIST and REPORT commands
Enable ARCHIVELOG mode for the database
Use Recovery Manager

Recovering from Non-critical Losses
Recovery of Non-Critical Files
Creating New Temporary Tablespace
Recreating Redo Log Files, Index Tablespaces, and Indexes
Read-Only Tablespace Recovery
Authentication Methods for Database Administrators
Loss of Password Authentication File
Creating a new temporary tablespace
Altering the default temporary tablespace for a database

Incomplete Recovery
Recovery Steps
Server and User Managed Recovery commands
Recovering a Control File Autobackup
Creating a New Control File
Incomplete Recovery Overview
Incomplete Recovery Best Practices
Simplified Recovery Through RESETLOGS
Point-in-time recovery using RMAN

Flashback
Flashback Database Architecture
Configuring and Monitoring Flashback Database
Backing Up the Flash Recovery Area
Flashback Database Considerations
Using Flashback Database EM Interface
Managing and monitoring Flashback Database operations