Oracle Database 10g DBA Part I — A 5-Day Course

 Open Public Course Closed In-House Course

Synopsis

This course is designed to give database administrators the knowledge and skills needed to maintain successful and efficient day-to-day operations of an Oracle database.

This course is designed to give the Oracle database administrator practical experience in setting up, administering and troubleshooting an Oracle10g database. Database administrators will gain an understanding of the architecture and processes of the Oracle database server. They will be able to ensure the integrity and availability of a company's data within the Oracle environment. They will be able to monitor the performance of the database and perform basic backup and recovery of the database.

This course covers the main examination topics of the Oracle exam #1Z1-042 Oracle Database 10g: Administration I. Exams are not included as part of the course.

Course Objectives

On completion of this course, delegates will be able to:

• Install and Configure Oracle
• Create and Manage an Oracle Database
• Administer an Oracle Database
• Configure the Oracle Network Environment
• Configure Shared Servers
• Create and Manage Database Storage Structures
• Manage Redo Log Files and Control Files
• Manage Schema Objects
• Manage Users, Profiles, Privileges and Roles
• Manage Undo Data and Temporary Segments
• Monitor and Resolving Lock Conflicts
• Maintain Database Security
• Monitor the Performance of the Database
• Load and Unload Data
• Back Up and Recover the Database

Suitable For

This course is intended for:

• Anyone who needs to administer, monitor and support an Oracle 10g database.

Prerequisites

• Delegates should have an understanding of relational database concepts and basic operating system knowledge
• A working knowledge of SQL*Plus is required. This can be gained by attendance on the Oracle SQL course
• In addition knowledge of PL/SQL would be beneficial.

Publicly scheduled dates, locations, and prices

Outline Course Contents

Introduction to Oracle Database 10g

• Relational database concepts
• Database administration tasks
• DBA responsibilities

Oracle Database 10g Architecture

• Oracle architecture
• The Oracle instance
• Memory structures
• Background processes
• Server and client processes
• The Oracle database

Installation & Configuration of an Oracle Database

• Installation Prerequisites
• Understand system requirements
• Use optimal flexible architecture
• Authentication methods for a database
• Oracle environment variables
• Install Oracle software using the Oracle Universal Installer (OUI)
• Use the Database Configuration Assistant (DBCA) to create a database
• Use DBCA to manage templates
• Upgrade a database using Database Upgrade Assistant (DBUA)

Administer a Database

• Administrative tools available to a DBA
• Using SQL*Plus in Oracle to manage a database
• Administer the database using Enterprise Manager (EM)
• Start and stop the agent
• Start and stop the EM Database Console
• Access EM
• EM navigation
• Grant EM administrative privileges to a user

Configure the Oracle Network Environment

• Overview of network configuration
• Oracle Net Listener configuration and management
• Oracle Net Naming methods
• Tools for configuring and managing the Oracle network
• Configure client connections with Net Manager
• View listener configuration
• Start and stop the Oracle listener
• Use TNSPING to test Oracle Net connectivity
• Connect to the database

Manage an Oracle Instance

• Access an Oracle database
• Database authentication methods
• Initialization parameters
• Password file
• SHUTDOWN the database
• STARTUP the database
• View alert log and trace files

Tablespace and Datafile Management

• Overview of tablespaces and datafiles
• Use EM to view the storage structure of the database
• Create tablespace commands
• Locally managed tablespaces
• Dictionary managed tablespaces
• Bigfile tablespaces
• Temporary tablespaces
• Multiple temporary tablespaces
• Create tablespaces
• Manage tablespaces
• Use standard and non-standard block sizes
• Create and manage tablespaces using OMF
• Manage tablespaces

Manage Redo Logs

• Redo log file structure
• Maintaining redo log files
• Log switches and checkpoints
• Multiplex redo log files
• Manage online redo log files with OMF
• List redo log information

Manage Control Files

• The usage of the control file
• The contents of a control file
• Manage control files
• Standby control file
• Manage control files with OMF
• List control file information

Manage Schema Objects

• Create a new table
• View the attributes and contents of a table
• Understand data integrity
• Define constraints
• reate indexes
• View index attributes
• Create views
• Create and use sequences

Manage Users

• Database administration accounts
• User accounts
• Create a user account
• Change a user's password
• Alter and drop a user account
• Monitor user information
• Terminate user sessions

Mange Profiles and Resources

• Create profile command
• Manage passwords with profiles
• Control resource usage with profiles
• Alter and drop profile command

Managing Privileges

• Database access
• System level roles and privileges
• Object level privileges
• The grant and revoke commands
• Create roles
• Modify and drop roles
• Control the availability of roles
• Use predefined roles
• Synonyms
• Listing valid grants
• Product user profiles

Database Auditing

• Auditing SYS statements
• Auditing end user sessions
• Auditing the use of privileges
• Auditing modifications made to database objects
• Audit table relocation
• Fine grained auditing

Undo Management

• Undo data overview
• Monitor and administer undo
• Configure undo retention
• Guarantee undo retention
• Use the undo advisor
• Size the undo tablespace

Manage Data

• Use Data Pump to export data
• Use Data Pump to import data
• Use SQL Loader
• Create directory objects

Oracle Shared Server

• When shared servers should be used
• Configure shared servers
• Monitor shared servers
• Shared server architecture

Monitoring and Resolving Lock Conflicts

• Detect and resolve lock conflicts
• Manage deadlocks

Proactive Database Maintenance

• Automatic Workload Repository
• Automatic Database Diagnostic Monitoring
• Monitor an Oracle database
• Use the Advisors
• Set up notification rules

Performance Monitoring

• Troubleshoot invalid and unusable objects
• Gather optimizer statistics
• View performance metrics
• React to performance issues

Backup and Recovery Considerations

• Oracle Suggested Backup Strategy
• Database backup, restore and recover
• Types of failure
• Tune instance recovery
• Checkpoints, redo log files and archive log files
• Configure ARCHIVELOG mode
• Configure a database for recoverability

Database Backups

• Types of RMAN backup
• Backup sets and image copies
• Create consistent database backups
• Perform online backups
• Perform incremental backups
• Automate database backups
• Monitor the flash recovery area
• Manage backups

Database Recovery

• Recover from the loss of a system critical data file
• Recover from the loss of a non system critical data file
• Recover from the loss of a control file
• Recover from the loss of a redo log file

Flashback

• Flashback Query
• Flashback Version Query
• Flashback Transaction Query
• Flashback Table
• Flashback Drop