Oracle Administration

Oracle Database Administration

Length: 5 Days
Prerequisites: You must have experience working with Oracle to successfully master the information in this course.
Specifically, you must have solid Oracle SQL experience and familiarity with SQL *Plus and PL/SQL. You can obtain this trianing
in our "Introduction to Oracle," and "Introduction to PL/SQL Programming," classes.

Overview

This course provides you with the skills necessary to create, maintain and manage an Oracle database. In this class you will concentrate on the skills necessary for day-to-day database administration tasks such as security, creating and managing users, creating and managing database objects (tablespaces, tables, indexes, etc.), running utilities, configuring Oracle networking, and simple backup and recovery techniques. A fairly in-depth discussion of database architecture is also provided.

This training course has been completely updated for Oracle10g. This includes training on Data Pump, Server-Generated Alerts, Advisors, Automatic Storage Management (optional lesson), Automatic Statistics Collection and Auditing Enhancements.

Working examples are included throughout the course book. Students have access to electronic versions of all examples, sample scripts and lab solutions.

Approximately 50% of class time is spent on hands-on exercises.

Key Benefits: After successfully completing this course, you will be able to:

Describe the major architectural components of Oracle including the SGA, background processes and files that make up the physical database
Use the Oracle data dictionary to determine the structure and status of the database.
Creative administrative usesr and new Oracle users
Create and Manage a Database, (Start Stop Backup, Mirror and Recover a Database)
Create, index and manage tables
and more....

Audience: Database Systems Administrators

Outline

Chapter 1. A DBA's Introduction to the Oracle Data Dictionary

What is the Dictionary?
Static Views
Common DBA Views
Example: DBA_USERS
Example: DBA_SYS_PRIVS
Dynamic Performance Views
Example: V$PARAMETER
Miscellaneous Views
DICTIONARY View

Chapter 2. Oracle Architecture, Part I

Database and Instance
Database Files
Segments and Extents
Query DBA_DATA_FILES
Query DBA_SEGMENTS
Redo Log Files
Control Files
Temp Files
Parameter Files

Chapter 3. Oracle Architecture, Part II

The Processes
Query V$BGPROCESS
Database Block Writer
Log Writer - LGWR
System Monitor - SMON
Process Monitor - PMON
Checkpoint - CKPT
Archiver - ARCn
Miscellaneous Processes

Chapter 4. Oracle Architecture, Part III

PGA and UGA
The Oracle SGA
Size of SGA Areas
Shared Pool
Impact of Automatic Workload Repository
Library Cache
SQL Statement Processing
Data Dictionary Cache
Buffer Cache
Database Block Size
Non-Standard DB Size
Keep and Recycle Caches
Redo Log Buffer
Large Pool/Java Pool/Streams Pool

Chapter 5. Oracle Administrative Uses

The SYS User
The SYSTEM User
SYSDBA Privilege
DBA Role
Local Connections
Remote Connections
Creating a Password File
Password File Entries
Create New Administrators

Chapter 6. Starting and Stopping an Oracle Instance

STARTUP Procedures
SQL*Plus for Startup
Connecting for STARTUP
Startup/Shutdown Basics

Stages Of STARTUP
STARTUP Command
STARTUP Examples
ALTER DATABASE
Startup MOUNT
Restricted Access
Shutting Down

Chapter 7.Oracle Initialization Parameter Files

Review: Parameter Files
Text Parameter File
Introduction to SPFILE
Benefits of SPFILE
Creating an SPFILE
CREATE SPFILE Notes
Using SPFILE
Parameter Overrides
Am I Using SPFILE?

Export & Backup
Modifying Parameters
Scope Clause
Dynamic Parameters

Chapter 8. Creating Managing Users

Creating New Users
Intro to User Privileges
Default and Temporary Parameters
Default Permanent Tablespace
Querying the Data Dictionary
Altering a User
Dropping a User
Terminating a User Session
User Basics Workshop
Introduction to Quota
Providing Quota
Unlimited and Zero Quota
Query Existing Quotas
User Space Used
Installation Users
Secure Installation Users

Chapter 9. Oracle Password Management

Password Aging
Manual Password Expiration
Account Locking
Password History
Password Complexity

Chapter 10. Introduction Oracle Auditing

Auditing Technique Overview
Enabling the Audit Trail
Auditing SYSDBA
Auditing SYSDBA Tips
AUDIT Statement
Auditing Logons
Monitoring Logon Attempts
Audit System Grants
Monitor System Grants
Object-Level Auditing
Monitoring Object Activity
The SYSAUD$ Table
Auditing with Triggers
Fine Grained Auditing
Create FGA Policy
Display FGA Activity
Fine Grained Auditing of DML

Chapter 11.Managing Security in Oracle

Security Basics
Privilege Types
System Privileges
ANY-Type Privileges
PUBLIC Keyword
ADMIN OPTION
Revoking System Privileges
SESSION_PRIVS
DBA_SYS_PRIVS

Object Privileges
Grant Object-Level Privileges
Insert / Update Privileges
WITH GRANT OPTION
Revoking Object Privileges
DBA_TAB_PRIVS

Role Concepts
Predefined Roles
Giving Privileges To Roles
WITH ADMIN OPTION
Granting Role to Role
Role Privileges
PL/SQL and Roles
Revoking Roles
Dropping Roles

Chapter 12. Creating and Managing Oracle Tablespaces

What is a Tablespace?
Data Dictionary
Why Tablespaces?
Locally Managed Tablespaces

Tablespace Parameter
System-Managed LMT
Uniform LMT's
Bigfile Tablespaces

Automatic Segment Space Management
Oracle Managed Files
OMF Key Points
Configuration and File Location
Striping Overview
Non-Standard Blocksize
Benefits of Non-Standard Blocksize
Monitoring Free Space
Adding Space
Offline Tablespace
Read-Only Tablespace
Dropping Tablespaces
Managing the SYSTEM Tablespace
Managing the SYSAUX Tablespace
Temporary Tablespace and Temporary Tablespace Groups

Chapter 13. Proactive Maintenance with Server Generated Alerts

Concepts and Alert Architecture
Threshold-Based Alerts
Default Thresholds
Setting Custom Thresholds
Enterprise Manager Notifications
Display Alert Data in the Data Dictionary

Chapter 14. Creating and Managing Oracle Heap Tables

Types of Tables
CREATE TABLE Statement
Column Definition
Character and Numeric Datatypes
Date and Time Datatypes
Binary Datatypes
ANSI and Miscellaneous Datatypes

Create Table As Subquery
CTAS Options

Collecting Statistics
Oracle Automatic Statistics Collection
GATHER_SCHEMA_STATS
Gather Database Stats
Table MONITORING
Additional STATS Features

Managing Table Storage
INITRANS & MAXTRANS
NOLOGGING

Table Compression
Table Compression Workshop
ALTER TABLE MOVE
CTAS Nologging
DROP Column
Renaming Tables
Renaming Columns

Chapter 15. B-Tree Indexes

Indexes: What and Why
Index Structures
B-Tree Organization
Unique Index Scan
AUTOTRACE
Important Points
CREATE INDEX
NOLOGGING
Statistics Collection
Indexes Workshop
Composite Indexes
Which Column First?
Index Key Compression
Descending Keys
Index Coalesce
Choosing Index Columns
Calculating Selectivity
Where to Look Next

Chapter 16. Creating and Managing Constraints

Introduction to Constraints
Inline Syntax
Out-of-Line Syntax
Constraint Name
Violating Constraint

Referential Integrity
Alter to Add Constraint
Identifying Exceptions

NOVALIDATE Option
Primary Key Index
USING INDEX Option
Modify State
Drop Constraint
Rename Constraint

Chapter 17. Managing Undo in Oracle

What is Undo?
Where is Undo Kept?
Why Not Rollback Segments?
Why AUM?
Implementing AUM
Find Undo Tablespace
Create Undo Tablespace
Guaranteed Retention
Initialization Parameters
Switching Undo Tablespace
Dropping Undo Tablespace
Data Dictionary Views
Tuning AUM
Adding Space
Using the Oracle Undo Advisor
Resources

Chapter 18. Managing Redo Logs

Review: Concepts
DBAs Responsibilities
Archiving Status
Enabling Archiving
Archiving Workshop
Multiplexing Archive Logs
Multiplex Archive Logs Workshop
Log Switch Delays
Adding Log Files
Using the Redo Log Advisor
Review of OMF
Adding Log Files - OMF
Multiplexing Online Logs

Chapter 19. Manage Control Files

Control File Review
DBA Responsibilities
Adding Control Files

Backing Up Control File
Recreating Control File

Chapter 20. Oracle Backup and Recovery Basics - Introduction to RMAN

What is RMAN?
RMAN Components
Starting and Connecting
Common RMAN Commands
Backup Concepts
Full Backup
Full Tablespace Backup

Datafile Backup
Incremental Backup
Change Tracking
Backups - Where to go from here
RESTORE Command
RECOVER Command
Complete Recovery - Database
Recover Tablespace
Summary: Restore and Recovery
Reporting
Report Need Backup
Show Stored Settings
What Else is There?
Getting More Information

Chapter 21. Importing and Exporting with DataPump

Data Pump Concepts
Data Pump Modes
Data Pump Access Methods
Parallel Processing
Export Example
Import Example
Data Pump Features
Network Export / Import
Network Import Example
DBMS_DATAPUMP
Data Pump Dictionary
Datapump Resources
Affect on External Tables

Chapter 22. SQL *Loader

Executing SQL*Loader
The Control File
Variable Format Data

Fixed Format Data
LOG File
Conditional Loads

Chapter 23. Introduction to Oracle Networking

Introduction to Oracle Net
Configuration Files
TNSNAMESORA
Introduction to the Listener
Listener Control Utility
Listener Started?
Stopping the Listener
Listener Password

LISTENER.ORA
Resources

Chapter 24. Creating a New Oracle Database

What We Need to Know
The Oracle SID
Windows Services
Seeing the Active Threads
Listing Active Instances
Stopping Active Instances
Database Configuration Assistant
Step 1: Select Operation
Step 2: Select Template
Step 3: Database ID
Step 4: Database Features
Step 5: Database Connection Options
Step 6:Init Parameters
Step 7:Database Storage
Step 8: Create Options
The BAT File
ORADIM
CREATEDB Script

Chapter 25. Optional: Automatic Storage Management

Introducing ASM
The ASM Instance
Starting the ASM Instance
ASM Disks
ASM Diskgroups
Creating Diskgroup
Altering Diskgroup
Working in the Database Instance
Tablespace Management in ASM

Chapter 26. Optional: Introduction to Oracle Partitioning

Partitioning History
Range Partitioning
MAXVALUE and NULLs
Partition Storage Attributes
Multiple-Column Partitioning
Hash Partitioning
Composite Partitioning
List Partitioning
Composite Range-List Partitioning
Subpartition Template
IOT and Hash Partitioning
Global Indexes and Partitioning Maintenance
Row Movement
Maintenance: Rolling In

Maintenance: Rolling Out
Merge Partitions
Other Maintenance Operations
Data Dictionary Implications
Finding the Partitioning Key
Finding the Partitioning Bounds
Explicit Referencing
Table Partitioning Restrictions

Chapter 27. Optional: Introduction to Other Table Types

Index Organized Tables
Temporary Tables Concepts
Temporary Tables Management
Creating Temporary Tables
Index Clustered Tables
Hash Clustered Tables
Sorted Hash Clusters
Nested Tables
Nested Table Examples
Object Tables

Chapter 28. Optional: Logical Backups with Original Export/Import Utilities

Export Modes
User-Mode Export
Export Features
Import
Using Import
Import Tips
Resources

Back to Course Listing