Oracle DBA Training

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

Manage data through SQL

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

Oracle DBA Training Syllabus – Part 2

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

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

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

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 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”

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

DML Error Logging

Interpreting Bigfile ROWIDs

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

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