Oracle Advance DBA Course

Introduction

• Describing the Life Cycle Development Phases
• Defining a Relational Database
• Discussing the Theoretical, Conceptual, and Physical Aspects of a Relational Database
• Describing How a Relational Database Management System (RDBMS) Is Used to Manage a Relational Database
• Describing the Oracle Implementation of Both the RDBMS and the Object Relational Database Management System (ORDBMS)
• Describing How SQL Is Used in the Oracle Product Set

Writing a Basic SQL Statement

• Describing the SQL Select Capabilities
• Executing a Basic Select Statement with the Mandatory Clauses
• Differentiating Between SQL and iSQL*Plus Commands

Restricting and Sorting Data

• Limiting the Rows Retrieved by a Query
• Sorting the Rows Retrieved by a Query

Single Row Functions

• Describing Various Types of Functions Available in SQL
• Using a Variety of Character, Number, and Date Functions in SELECT Statements
• Explaining What the Conversion Functions Are and How They Are Used
• Using Control Statements

Displaying Data from Multiple Tables

• Writing SELECT Statements to Access Data from More Than One Table
• Describing the Cartesian Product
• Describing and Using the Different Types of Joins
• Writing Joins Using the Tips Provided

Aggregating Data by Using Group Functions

• Identifying the Different Group Functions Available
• Explaining the Use of Group Functions
• Grouping Data by Using the GROUP BY Clause

Writing Subqueries

• Describing the Types of Problems That Subqueries Can Solve
• Describing Subqueries
• Listing the Types of Subqueries
• Writing Single-Row and Multi-Row Subqueries
• Describing and Explaining the Behavior of Subqueries When NULL Values Are Retrieved

Producing Readable Output with iSQL*Plus

• Producing Queries That Require an Input Variable
• Customizing the iSQL*Plus Environment
• Producing More Readable Output
• Creating and Executing Script Files

Manipulating Data

• Describing Each Data Manipulation Language (DML) Command
• Inserting Rows into a Table
• Updating Rows in a Table
• Deleting Rows from a Table
• Merging Rows into a Table
• Controlling Transactions
• Describing Transaction Processing
• Describing Read Consistency and Implicit and Explicit Locking

Creating and Managing Tables

• Describing the Main Database Objects
• Creating Tables
• Describing the Oracle Data Types
• Altering Table Definitions
• Dropping, Renaming, and Truncating Tables

Including Constraints

• Describing Constraints
• Creating and Maintaining Constraints

Creating Views

• Describing Views and Their Uses
• Creating a View
• Retrieving Data by Means of a View
• Inserting, Updating, and Deleting Data Through Views
• Dropping Views
• Altering the Definition of a View
• Inline Views
• Top 'N' Analysis

Other Database Objects

• Creating, Maintaining, and Using Sequences
• Creating and Maintaining Indexes
• Creating Private and Public Synonyms

Controlling User Access

• Understanding the Concepts of Users, Roles, and Privileges
• Granting and Revoking Object Privileges
• Creating Roles and Granting Privileges to Roles
• Creating Synonyms for Ease of Table Access

SQL Workshop

• Applying Techniques Learned in This Course
• Preparing for Future Oracle Courses

Using Set Operators

• Describing the Set Operators
• Obeying the Set Operators Rules and Guidelines
• Using a Set Operator to Combine Multiple Queries into a Single Subquery
• Controlling the Order of Rows Returned

Oracle 9i Datetime Functions

• Using DATETIME Functions
• Using the NVL2 Function to Handle NULL Values

Enhancements to the GROUP BY Clause

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

Advanced Subqueries

• Multiple-Column Subqueries
• Writing a Subquery in the FROM Clause
• Writing and describing Correlated Subquery
• Using EXISTS and NOT EXISTS Operators
• Updating and Deleting Rows Using Correlated Subqueries
• Using Scalar Subqueries in SQL

Hierarchical Retrieval

• Discussing the Benefits of the Hierarchical Query
• Ordering the Rows Retrieved by a Query in a Hierarchical Manner
• Formatting Hierarchical Data so That It Is Easy to Read
• Excluding Branches from the Tree Structure

Oracle9i Extensions to DML and DDL Statements

• Discussing Multitable Inserts
• Creating and Using External Tables
• Naming the Index and Using the CREATE INDEX Command at the Time of Creating Primary Key Constraint

Introduction (Database Architecture)

• Describe course objectives
• Explore the Oracle 10g database architecture

Installing the Oracle Database Software

• 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
• Describe the main concepts and functionality of Automatic Storage Management (ASM)

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
• Implement standard password security features
• Control resource usage by users

Managing Schema Objects

• Define schema objects and data types
• Create and modify tables
• Define constraints
• View the columns and contents of a table
• Create indexes, views and sequences
• Explain the use of temporary tables
• Use the Data Dictionary

Managing Data and Concurrency

• Manage data through SQL
• Identify and administer PL/SQL Objects
• Describe triggers and triggering events
• Monitor and resolve locking conflicts

Managing Undo Data

• Explain DML and undo data generation
• Monitor and administer undo
• Describe the difference between undo and redo data
• Configure undo retention
• Guarantee undo retention
• Use the undo advisor

Implementing Oracle Database Security

• Describe DBA responsibilities for security
• Apply the principal of least privilege
• Enable standard database auditing
• Specify audit options
• Review audit information
• Maintain the audit trail

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

Proactive Maintenance

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

Performance Management

• Use Enterprise Manager pages to monitor performance
• Use the SQL Tuning Advisor
• Use the SQL Access Advisor
• Use Automatic Shared Memory Management
• Use the Memory Advisor to size memory buffers
• Use performance related dynamic views
• Troubleshoot invalid or unusable objects

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

Performing Flashback

• Describe Flashback database
• Restore the table content to a specific point in the past with Flashback Table
• Recover from a dropped table
• View the contents of the database as of any single point in time with Flashback Query
• See versions of a row over time with Flashback Versions Query
• View the transaction history of a row with Flashback Transaction Query

Moving Data

• Describe available ways for moving data
• Create and use directory objects
• Use SQL*Loader to load data from a non-Oracle database (or user files)
• Explain the general architecture of Data Pump
• Use Data Pump Export and Import to move data between Oracle databases
• Use external tables to move data via platform-independent files

Introduction

• Grid Computing
• Oracle Enterprise Manager 10g Product Controls
• Database Architecture Review

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

Oracle Secure Backup

• Installation and Configuration
• Implement the Oracle suggested strategy
• RMAN and Oracle Secure Backup
• Database and File-system files backup/restore to tape
• Using obtool and web interface to configure Oracle Secure Backup devices (CLI/GUI)
• Configuring EM for Oracle Secure Backup and test backup to tape (EM)
• Using RMAN to backup your database to tape (CLI)
• Using the OB Web tool to backup file system files

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
• Using V$FLASH_RECOVERY_AREA_USAGE
• Flashback Database Considerations
• Using the Flashback Database RMAN interface
• Using Flashback Database EM Interface
• Managing and monitoring Flashback Database operations

Dealing with Database Corruption

• Block Corruption Symptoms: ORA-1578
• DBVERIFY Utility and the ANALYZE command
• Initialization parameter DB_BLOCK_CHECKING
• Segment Metadata Dump and Verification
• Using Flashback for Logical Corruption and using DBMS_REPAIR
• Block Media Recovery
• RMAN BMR Interface
• Dumping and Verifying Segment Metadata

Monitoring and Managing Memory

• Oracle Memory Structures
• Automatic Shared Memory Management
• SGA Tuning Principles
• Database Control and Automatic Shared Memory Management
• Behavior of Auto-Tuned and Manual SGA Parameters
• Resizing SGA_TARGET
• PGA Management Resources
• Using the Memory Advisor

Automatic Performance Management

• Identifying Tunable Components
• Oracle Wait Events and System Statistics
• Troubleshooting and Tuning Views
• Direct Attach to SGA for Statistic Collection
• Workload Repository
• Advisory Framework
• ADDM Scenarios and Usage Tips
• Using the SQL Tuning and SQL Access Advisor

Monitoring and Managing Storage I

• Database Storage Structures
• Space Management Overview
• Oracle-Managed Files (OMF)
• Row Chaining and Migrating
• Proactive Tablespace Monitoring
• Managing Resumable Space Allocation
• SYSAUX Tablespace
• Monitoring table and index space usage

Monitoring and Managing Storage II

• Automatic Undo Management
• Redo Log Files
• Table Types
• Partitioned Tables
• Index-Organized Tables (IOT)
• Managing index space with SQL
• Configure optimal redo log file size
• View “Automatic Tuning of Undo Retention”

Automatic Storage Management

• ASM General Architecture and Functionalities
• Dynamic Performance View Additions
• Managing an ASM Instance
• ASM Disk Groups
• Using asmcmd Command Line
• Migrating Your Database to ASM Storage
• Creating an ASM instance in a separate Oracle Home
• Migrating a tablespace to use ASM storage

VLDB Support

• Creating Bigfile Tablespaces
• Packages and data dictionary changes to support VLDB
• Creating and maintaining temporary tablespace groups (TTG)
• Partitioning and Partitioned Indexes
• Skipping unusable indexes
• Creating and using hash-partitioned global indexes
• DML Error Logging
• Interpreting Bigfile ROWIDs

Managing Resources

• Database Resource Manager Concepts and Configuration
• Creating a New Resource Plan
• Active Session Pool Mechanism
• Maximum Estimated Execution Time
• Creating a Complex Plan
• Administering and Monitoring Resource Manager
• Resource Plan Directives
• Creating Resource Consumer Groups

Automating Tasks with the Scheduler

• Scheduler Concepts
• Creating a Job Class and a Window
• Managing Jobs, Programs, Chains, Events, Schedules, priority
• Viewing and Purging Job Logs
• Creating a program and a schedule
• Creating a job that uses a program and a schedule
• Altering the program and schedule for the job and observing the behavior change of the job
• Monitoring job runs

Database Security

• Virtual Private Database: Overview
• Creating a Column-Level Policy
• Writing a Policy Function
• Policy Types
• Column level VPD with column masking
• Transparent Data Encryption
• Setting the listener password
• Implement VPD

Data Movement

• External Tables Concepts
• Creating a Directory object and External Table
• Data Pump
• Transport Database
• RMAN CONVERT DATABASE Command
• Transport Tablespace
• Create a Directory Object
• Create a Temporary Table

Using Globalization Support

• Globalization Support Features
• Encoding Schemes
• Database Character Sets and National Character Sets
• Specifying Language-Dependent Behavior
• Locale Variants
• Using Linguistic Comparison and Sorting
• Data Conversion Between Client and Server Character Sets
• Determining the Default NLS Settings

Workshop

• Workshop Methodology, requirements, and setup
• Scenario 1: Database performance
• Scenario 2: Finding and Tuning Inefficient SQL
• Scenario 3: SGA Management - REDO
• Scenario 4: Running out of Undo Space
• Scenario 5: Missing datafile
• Scenario 6: Managing space in a tablespace - REDO
• Scenario 7: Missing TEMP data file