Introduction to SQL
o Listing the features of Oracle Database 11g
o Discussing the basic design, theoretical and physical aspects of a relational database
o Describing the development environments for SQL
o Describing Oracle SQL Developer
o Describing the data set used by the Package
Retrieving Data Using the SQL SELECT Statement
o Listing the capabilities of SQL SELECT statements.
o Generating a report of data from the output of a basic SELECT statement
o Using arithmetic expressions and NULL values in the SELECT statement
o Using Column aliases
o Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
o Displaying the table structure using the DESCRIBE command
Restricting and Sorting Data
o Writing queries with a WHERE clause to limit the output retrieved
o Using the comparison operators and logical operators
o Describing the rules of precedence for comparison and logical operators
o Using character string literals in the WHERE clause
o Writing queries with an ORDER BY clause to sort the output
o Sorting output in descending and ascending order
o Using the Substitution Variables
Using Single-Row Functions to Customize Output
o Differentiating between single row and multiple row functions
o Manipulating strings using character functions
o Manipulating numbers with the ROUND, TRUNC and MOD functions
o Performing arithmetic with date data
o Manipulating dates with the date functions
Using Conversion Functions and Conditional Expressions
o Describing implicit and explicit data type conversion
o Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
o Nesting multiple functions
o Applying the NVL, NULLIF, and COALESCE functions to data
o Using conditional IF THEN ELSE logic in a SELECT statement
Reporting Aggregated Data Using the Group Functions
o Using the aggregation functions in SELECT statements to produce meaningful reports
o Using AVG, SUM, MIN, and MAX function
o Handling Null Values in a group function
o Creating queries that divide the data in groups by using the GROUP BY clause
o Creating queries that exclude groups of date by using the HAVING clause
Displaying Data From Multiple Tables
o Writing SELECT statements to access data from more than one table
o Joining Tables Using SQL:1999 Syntax
o Viewing data that does not meet a join condition by using outer joins
o Joining a table by using a self join
o Creating Cross Joins
Using Sub-queries to Solve Queries
o Using a Subquery to Solve a Problem
o Executing Single-Row Sub-queries
o Using Group Functions in a Sub-query
o Using Multiple-Row Subqueries
o Using the ANY and ALL Operator in Multiple-Row Sub-queries
Using the SET Operators
o Describing the SET operators
o Using a SET operator to combine multiple queries into a single query
o Using UNION, UNION ALL, INTERSECT, and MINUS Operator
o Using the ORDER BY Clause in Set Operations
Manipulating Data
o Adding New Rows to a Table Using the INSERT statement
o Changing Data in a Table Using the UPDATE Statement
o Using DELETE and TRUNCATE Statements
o Saving and discarding changes with the COMMIT and ROLLBACK statements
o Implementing Read Consistency
o Using the FOR UPDATE Clause
Using DDL Statements to Create and Manage Tables
o Categorizing Database Objects
o Creating Tables using the CREATE TABLE Statement
o Describing the data types
o Describing Constraints
o Creating a table using a subquery
o Altering and Dropping a table
Creating Other Schema Objects
o Creating, modifying, and retrieving data from a view
o Performing Data manipulation language (DML) operations on a view
o Dropping a view
o Creating, using, and modifying a sequence
o Creating and dropping indexes
o Creating and dropping synonyms
Exploring the Oracle Database Architecture
o Oracle Database Architecture Overview
o Interacting with an Oracle Database
o Process Architecture
o Database Storage Architecture
o Logical and Physical Database Structures
o Tablespaces and Data Files
o SYSTEM and SYSAUX Tablespaces
o Segments, Extents, and Blocks
Preparing the Database Environment
o Tasks of an Oracle Database Administrator
o Tools Used to Administer an Oracle Database
o Installation: System Requirements
o Optimal Flexible Architecture (OFA)
o Setting Environment Variables
o Oracle Universal Installer (OUI)
o Database Configuration Options
o Advanced Installation Options
Creating an Oracle Database
o Planning the Database
o Configuring the Listener
o Using the DBCA to Create a Database
o Password Management
o Creating a Database Design Template
o Using the DBCA to Delete a Database
o Using DBCA For Additional Tasks
Managing the Oracle Instance
o Management Framework
o Oracle Enterprise Manager
o Initialization Parameter
o Database Startup and Shutdown
o Shutdown Modes
o Viewing the Alert History
o Using Trace Files
o Data Dictionary: Overview
Configuring the Oracle Network Environment
o Oracle Net Services & Listener
o Establishing Net Connections
o Tools for Configuring and Managing the Oracle Network
o Net Services Administration Pages
o Naming Methods
o Directory Naming
o SGA and PGA
o When Not to Use a Shared Server
Managing Database Storage Structures
o Storage Structures
o How Table Data Is Stored
o Anatomy of a Database Block
o Space Management in Tablespaces
o Tablespaces in the Preconfigured Database
o Actions with Tablespaces
o Oracle Managed Files (OMF)
o ASM: Concepts
Administering User Security
o Database User Accounts
o Predefined Accounts: SYS and SYSTEM
o Administrator Authentication
o Benefits of Roles
o Predefined Roles
o Implementing Password Security Features
o Assigning Quota to Users
Managing Schema Objects
o What Is a Schema?
o Accessing Schema Objects
o Specifying Data Types in Tables
o Creating and Modifying Tables
o Understanding Data Integrity
o Indexes & Views
o Sequences
o Temporary Tables
Managing Data and Concurrency
o Manipulating Data Through SQL
o PL/SQL
o Administering PL/SQL Objects
o Functions, Procedures, Packages & Triggers
o Data Concurrency
o Enqueue Mechanism
o Resolving Lock Conflicts Using SQL
o Deadlocks
Managing Undo Data
o Data Manipulation
o Transactions and Undo Data
o Undo Data Versus Redo Data
o Configuring Undo Retention
o Guaranteeing Undo Retention
o Using the Undo Advisor
o Viewing System Activity
Implementing Oracle Database Security
o Industry Security Requirements
o Principle of Least Privilege
o Monitoring for Compliance
o Value-Based Auditing
o Fine-Grained Auditing
o DBA Auditing
o Security Updates
o Applying Security Patches
Database Maintenance
o Introducing Terminology
o Optimizer Statistics
o Automatic Workload Repository (AWR)
o Automatic Database Diagnostic Monitor (ADDM)
o Advisory Framework
o Automated Maintenance Tasks
o Server-Generated Alerts
o Reacting to Alerts
Performance Management
o Performance Monitoring
o Managing Memory Components
o Enabling Automatic Memory Management (AMM)
o Automatic Shared Memory Advisor
o Using Memory Advisors
o Dynamic Performance Statistics
o Troubleshooting and Tuning Views
o Invalid and Unusable Objects
Backup and Recovery Concepts
o Part of Your Job
o Statement Failure
o User Error
o Understanding Instance Recovery
o Phases of Instance Recovery
o Using the MTTR Advisor
o Media Failure
o Archive Log Files
Performing Database Backups
o Backup Solutions: Overview
o Oracle Secure Backup
o User-Managed Backup
o Terminology
o Recovery Manager (RMAN)
o Configuring Backup Settings
o Backing Up the Control File to a Trace File
o Monitoring the Flash Recovery Area
Performing Database Recovery
o Opening a Database
o Data Recovery Advisor
o Loss of a Control File
o Loss of a Redo Log File
o Data Recovery Advisor
o Data Failures
o Listing Data Failures
o Data Recovery Advisor Views
Moving Data
o Moving Data: General Architecture
o Directory Object: Overview
o SQL*Loader: Overview
o Data Pump: Overview
o Performance Initialization Parameters
o Using Enterprise Manager to Monitor Data Pump Jobs
o Data Dictionary
Enhancing Database Capabilities
o Using EM Support Workbench
o Create a Service Request
o Package and upload diagnostic data to Oracle Support
o Track the SR and Implement Repairs
o Incident Packaging Configuration
o Working with Oracle Support
o MetaLink Integration
o Managing Patches
Database Architecture and ASM
o Oracle Database Architecture Overview
o ASM General Architecture
o Creating an ASM Instance
o Using Enterprise Manager to Manage ASM Users
o ASM Storage Concepts
o The ASMCMD Utility
o ASM Scalability and Performance
Configuring for Recoverability
o Purpose of Backup and Recovery Functionality
o Typical Backup and Recovery Tasks
o Oracle Backup and Recovery Solutions
o Using Recovery Manager (RMAN)
o Using a Flash Recovery Area
o Flash Recovery Area Space Management
Using the RMAN Recovery Catalog
o RMAN Repository Data Storage: Comparison of Options
o Storing Information in the Recovery Catalog
o Registering a Database in the Recovery Catalog
o Recovery Catalog Resynchronization: Concepts
o Using RMAN Stored Scripts
o Backing Up and Recovering the Recovery Catalog
o Using a Virtual Private Catalog
Configuring Backup Specifications
o Using RMAN to Create Backups
o Configuring Persistent Settings for RMAN
o Using Enterprise Manager to Configure RMAN Settings
o Control File Auto-backups
o Managing Persistent Settings
o Configuring Devices for Backup
o Configuring and Allocating Channels for Use in Backups
o Configuring Backup Optimization
Creating Backups
o Creating an Oracle-Suggested Backup
o Creating Backup Sets & Image Copies
o Creating a Whole Database Backup
o RMAN Backup Types
o Monitoring Block Change Tracking
o Creating Duplex Backup Sets
o Archival Backups: Concepts
o Encrypting Backups
Performing User-Managed Backup and Recovery
o Restoring and Recovering
o Causes of File Loss
o Critical Versus Non-critical
o Recovering from a TEMPFILE Loss
o Re-creating Indexes
o Recovering from a Lost Index Tablespace
o Authentication Methods for Database Administrators
o Recovering a Read-Only Tablespace
Using RMAN to Perform Recovery
o Using RMAN RESTORE and RECOVER Commands
o Performing Recovery Using Enterprise Manager
o Recovery Using Incrementally Updated Backups
o Perform a Fast Switch to Image Copies
o Restoring and Recovering the Database on a New Host
o Performing Disaster Recovery
Using RMAN to Duplicate a Database
o Using RMAN to Create a Duplicate Database
o Creating a Duplicate Database
o Creating an Initialization Parameter File for the Auxiliary Instance
o Allocating Auxiliary Channels
o Understanding the RMAN Duplication Operation
o Using EM to Clone a Database
o Cloning a Running Database
Performing Tablespace Point-in-time Recovery
o Tablespace Point-in-Time Recovery (TSPITR) Concepts
o Tablespace Point-in-Time Recovery: Architecture
o Understanding When to Use TSPITR
o Determining the Tablespaces for the Recovery Set
o Using Enterprise Manager to Perform TSPITR
o Understanding TSPITR Processing
o Troubleshooting RMAN TSPITR
Monitoring and Tuning RMAN
o Parallelization of Backup Sets
o Monitoring RMAN Sessions
o Interpreting RMAN Message Output
o Using the DEBUG Option
o Tuning RMAN
o RMAN Multiplexing
o Comparing Synchronous and Asynchronous I/O
o Tape Subsystem Performance Rules
Using Flashback Technology
o Flashback Technology
o Transactions and Undo
o Guaranteeing Undo Retention
o Preparing Your Database for Flashback
o Flashback Drop and the Recycle Bin
o Using Flashback Technology to Query Data
o Possible Workflow
o Flashback Transaction Wizard
Using Flashback Database
o Flashback Table
o Enabling Row Movement on a Table
o Enabling Row Movement on a Table
o Flashback Database Architecture
o Configuring Flashback Database
o Performing Flashback Database Using EM
o Flashback Database Considerations
o Flashback Data Archive
Diagnosing the Database
o Automatic Diagnostic Workflow & Repository
o Location for Diagnostic Traces
o Viewing the Alert Log Using Enterprise Manager
o The Support Workbench and Oracle Configuration Manager
o Create a Service Request
o Package and upload diagnostic data to Oracle Support
o Incident Packaging Configuration
o Health Monitor Overview & Data Recovery Advisor
Managing Memory
o Oracle Memory Structures
o Automatic Memory Management Overview
o Oracle Database Memory Parameters
o Auto Memory Parameter Dependency
o Automatic Shared Memory Management: Overview
o Automatic PGA Memory Management
o Using the Memory Advisor to Size the SGA
o Memory Tuning Guidelines for the Library Cache
Managing Database Performance
o Tuning Activities
o Performance Planning & Performance Tuning Methodology
o Instance Tuning
o Troubleshooting and Tuning Views
o Automatic Workload Repository
o SQL Tuning & SQL Advisors
o Using the SQL Tuning Advisor
o SQL Access Advisor: Overview
Using the Segment Advisor
o Space Management: Overview
o Proactive Tablespace Monitoring
o Thresholds and Resolving Space Problems
o Monitoring Tablespace Space Usage
o Shrinking Segments
o Space Reclamation with ASSM
o Automatic Segment Advisor
o Minimum Compatibility Level
Managing Resources
o Database Resource Manager: Overview
o Database Resource Manager Concepts
o Accessing Resource Plans
o Default Maintenance Resource Manager Plan
o Creating Consumer Groups
o Resource Allocation Methods for Resource Plans
o Resource Allocation Methods for Resource Plans
o Monitoring the Resource Manager
Automating Tasks with the Scheduler
o Simplifying Management Tasks
o Key Components and Steps
o Creating an Event-Based Schedule
o Creating Complex Schedules
o Creating Job Chains
o Creating Job Chains
Administering the Scheduler
o Advanced Scheduler Concepts
o Job Classes
o Windows
o Prioritizing Jobs Within a Window
o Remote Jobs
o Installing the Scheduler Agent
o Dictionary Views Supporting Remote Jobs
Globalization
o Globalization Support Features
o What Every DBA Needs to Know
o Understanding Unicode
o Database Character Sets and National Character Sets
o Language- and Territory-Dependent Parameters
o Linguistic Searching and Sorting
o Case- and Accent-Insensitive Search and Sort
o NLS Data Conversion with Oracle Utilities
