Contents

List of Figures

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documentation
• Conventions

1 Introduction

• About This Guide
  • What This Guide Is Not
  • How to Use This Guide with Related Material
• About Oracle Database
• Common Oracle DBA Tasks
• Tools for Administering the Database

2 Installing Oracle Database and Creating a Database

• Overview of Installing Oracle Database Software and Creating a Database
  • Checking Prerequisites
  • Installation Choices
    • Basic Installation
    • Advanced Installation
• Installing Oracle Database Software
• Using DBCA to Create and Configure a Database
  • Starting DBCA
  • Creating a Database Using DBCA
    • Step 2 - Database Templates
    • Step 3 - Database Identification
    • Step 4 - Management Options
    • Step 5 - Database Credentials
    • Step 6 - Storage Options
    • Step 7 - Database File Locations
    • Step 8 - Recovery Configuration
    • Step 9 - Database Content
    • Step 10 - Initialization Parameters
      • Memory
      • Sizing
      • Character Sets
      • Connection Mode
    • Step 11 - Security Settings
    • Step 12 - Automatic Maintenance Tasks
    • Step 13 - Database Storage
    • Step 14 - Creation Options
  • Changing the Configuration of a Database Using DBCA
  • Deleting a Database Using DBCA
  • Managing Templates with DBCA
    • Advantages of Using Templates
    • Types of Templates
    • DBCA Templates Provided by Oracle Database
    • Creating Templates Using DBCA
    • Deleting DBCA Templates
  • Configuring Automatic Storage Management Using DBCA
• Installation: Oracle By Example Series

3 Getting Started with Database Administration

• Managing Your Database: A Roadmap
• Introduction to Oracle Enterprise Manager Database Control
  • Database Management Features
  • Database Home Page
  • Online Help
  • Navigational Features
• Starting and Stopping the Database Control Console Process
• Accessing the Database Home Page
• Access to Database Control for Nonadministrative Users
• Creating Database Control Administrative Users
• Setting Database Control Preferences
  • About Database Control Preferences
  • Defining Blackout Periods
  • Setting Preferred Credentials
• About SQL and SQL-Based Management Tools
  • Starting SQL*Plus and Connecting to the Database
  • About SQL Developer
• Getting Started with Oracle Enterprise Manager: Oracle By Example Series

4 Configuring the Network Environment

• Understanding Network Configuration
  • Oracle Net Listener Configuration
  • Connect Descriptors
  • Connection Requests
  • Naming Methods
    • Easy Connect Naming
    • Local Naming
    • Directory Naming
  • Tools for Network Configuration
    • Net Configuration Assistant
    • Oracle Enterprise Manager
    • Oracle Net Manager
• Viewing Listener Configuration
• Starting and Stopping the Listener
• Connecting to an Oracle Database from a Client Computer
• Networking: Oracle By Example Series

5 Managing the Oracle Instance

• Overview of the Oracle Instance and Instance Management
  • About Initialization Parameters
  • About Background Processes
  • About Server and Client Processes
  • About Instance Memory Structure
    • System Global Area
    • Program Global Area
  • About Instance Startup and Shutdown
    • About Administration Privileges for Startup and Shutdown
    • About Instance Startup
    • About Instance Shutdown
• Shutting Down and Starting Up the Oracle Instance
  • Shutting Down and Starting Up Using Oracle Enterprise Manager Database Control
  • Shutting Down and Starting Up Using the Windows Services Program
• Viewing and Modifying Initialization Parameters
• Managing Memory
  • About Memory Management
  • Enabling Automatic Memory Management
  • Modifying Memory Settings – Automatic Memory Management
  • Modifying Memory Settings – Automatic Shared Memory Management
  • Modifying Memory Settings – Automatic PGA Memory Management
• Instances: Oracle By Example Series

6 Managing Database Storage Structures

• About Database Storage Structures
  • About Control Files
  • About Online Redo Log Files
  • About Archived Redo Log Files
  • About Rollback Segments
  • About Datafiles
  • About Tablespaces
    • Locally Managed Tablespaces Compared to Dictionary-Managed Tablespaces
    • Tablespace Type
    • Tablespace Status
    • Autoextend Tablespace
  • About Other Storage Structures
    • Initialization Parameter File
    • Password File
    • Backup Files
• Viewing Database Storage Structure Information
  • Viewing Control File Information
  • Viewing Online Redo Log File Information
  • Viewing Archive Log Information
  • Viewing Datafile Information
  • Viewing Tablespace Information
• Performing Common Database Storage Tasks
  • Creating a Tablespace
  • Modifying a Tablespace
    • Setting a Tablespace to Automatically Extend
    • Changing Space Usage Alert Thresholds for a Tablespace
    • Taking a Tablespace Offline
  • Dropping a Tablespace
  • Reclaiming Unused Space
    • About Reclaimable Unused Space
    • About the Segment Advisor
      • Segment Advisor Recommendations
    • Viewing Segment Advisor Recommendations and Reclaiming Space
    • Running the Segment Advisor Manually
• Managing the Redo Log
  • Multiplexing the Redo Log
  • Switching a Log File
• Managing Undo Data
  • About Undo Data
  • About Managing Undo Data
  • Viewing Undo Information
  • Computing the Minimum Undo Tablespace Size Using the Undo Advisor
  • Changing the Undo Tablespace to a Fixed Size
• Storage: Oracle By Example Series

7 Administering User Accounts and Security

• About User Accounts
• About User Privileges and Roles
• About Administrative Accounts and Privileges
  • SYS and SYSTEM Users
  • SYSDBA and SYSOPER System Privileges
• Administering Roles
  • Viewing Roles
  • Example: Creating a Role
  • Example: Modifying a Role
  • Deleting a Role
• Administering Database User Accounts
  • Viewing User Accounts
  • Example: Creating a User Account
  • Creating a New User Account by Duplicating an Existing User Account
  • Example: Granting Privileges and Roles to a User Account
  • Example: Assigning a Tablespace Quota to a User Account
  • Example: Modifying a User Account
  • Locking and Unlocking User Accounts
  • Expiring a User Password
  • Example: Deleting a User Account
• Setting the Database Password Policy
  • About Password Policies
  • Modifying the Default Password Policy
• Users: Oracle By Example Series

8 Managing Schema Objects

• About Schema Objects
• About Schema Object Management Privileges
• Managing Tables
  • About Tables
    • About Table Types
    • About Table Column Attributes
      • Data Type
      • NOT NULL Column Constraint
      • Default Value
      • Encryption
    • About Table-Level Constraints
    • About Table Storage Attributes
    • Other Table Creation Considerations
      • User-Defined Types and Large Objects (LOBs)
      • Partitioned Tables and Indexes
      • Compressed Tables
  • Viewing Tables
  • Viewing Table Data
  • Example: Creating a Table
  • Modifying Table Attributes
    • Example: Adding Table Columns
    • Example: Deleting a Table Column
    • Example: Adding a New Table Constraint
    • Example: Modifying an Existing Table Constraint
    • Example: Deleting a Table Constraint
  • Example: Loading Data into a Table
  • Deleting a Table
• Managing Indexes
  • About Indexes
    • Indexes and Performance
    • Index Attributes
      • Standard (B-tree) and Bitmap
      • Ascending and Descending
      • Column and Functional
      • Single-Column and Concatenated
      • Nonpartitioned and Partitioned
  • Viewing Indexes
  • Example: Creating an Index
  • Example: Deleting an Index
• Managing Views
  • About Views
  • Displaying Views
  • Example: Creating a View
  • Example: Deleting a View
• Managing Program Code Stored in the Database
  • About Program Code Stored in the Database
  • Validating (Compiling) Invalid Schema Objects
• Working with Other Schema Objects
• Schemas: Oracle By Example Series

9 Performing Backup and Recovery

• Overview of Database Backup and Recovery
• Database Backup and Recovery Concepts
  • Consistent and Inconsistent Backups
  • Media Recovery
  • Flash Recovery Area
  • RMAN Repository
• Configuring Your Database for Basic Backup and Recovery
  • Specifying Credentials for Backup and Recovery with Database Control
  • Planning Space Usage and Location for the Flash Recovery Area
    • About the Backup Retention Policy and the Flash Recovery Area
    • About the Flash Recovery Area Size
  • Configuring Recovery Settings
  • Monitoring Flash Recovery Area Usage
  • Configuring Backup Settings
    • About RMAN Backups
    • Configuring Backup Device Settings
    • Configuring Backup Policy Settings
• Backing Up Your Database
  • RMAN Backup Concepts
    • Full Backups of Datafiles
    • Incremental Backups of Datafiles
    • Incrementally Updated Backups: Rolling Forward Image Copies of Datafiles
    • Backup Tags
  • Performing and Scheduling Backups with Database Control
    • Performing a Whole Database Backup
    • Using the Oracle Suggested Backup Strategy
      • About the Oracle Suggested Backup Strategy
      • Backing Up Your Database with the Oracle Suggested Disk Backup Strategy
    • Scheduling Miscellaneous Backup Tasks
  • Validating Backups and Testing Your Backup Strategy
    • Validating Selected Backups
    • Validating Backups for Restore Operations
• Displaying Backup Reports
• Managing Backups
  • About Backup Management
  • Cross-Checking Backups
  • Deleting Expired Backups
  • Marking Backups as Available or Unavailable
  • Deleting Obsolete Backups
• Performing Oracle Advised Recovery
  • About the Data Recovery Advisor
  • Using the Data Recovery Advisor
• Performing User-Directed Recovery
  • Rewinding a Table with Oracle Flashback Table
    • Enabling Row Movement on a Table
    • Performing a Flashback Table Operation
  • Recovering a Dropped Table with Oracle Flashback Drop
    • Dropping a Table
    • Retrieving a Dropped Table
  • Rewinding a Database with Oracle Flashback Database
  • Restoring and Recovering the Database
• Backup and Recovery: Oracle By Example Series

10 Monitoring and Tuning the Database

• Proactive Database Monitoring
  • Alerts
  • Performance Self-Diagnostics: Automatic Database Diagnostic Monitor
  • Monitoring General Database State and Workload
  • Managing Alerts
    • Viewing Metrics and Thresholds
    • Setting Metric Thresholds
    • About Responding to Alerts
    • Clearing Alerts
    • Setting Up Direct Alert Notification
• Diagnosing Performance Problems with ADDM
  • Viewing a Summary of ADDM Performance Findings
  • Responding to ADDM Performance Findings
  • Running ADDM Manually
  • Modifying AWR Snapshot Frequency and Retention
• Using Advisors to Optimize Database Performance
  • About Advisors
  • About the SQL Advisors
  • About the Automatic SQL Tuning Advisor
  • Configuring the Automatic SQL Tuning Advisor
  • Viewing Automatic SQL Tuning Results
  • Running the SQL Tuning Advisor
  • Running the SQL Access Advisor
  • Using the Memory Advisors
    • Obtaining Memory Sizing Advice
• Monitoring and Tuning: Oracle By Example Series

11 Investigating, Reporting, and Resolving Problems

• About Database Problems and Incidents
• Investigating, Reporting, and Resolving a Problem
  • Roadmap: Investigating, Reporting, and Resolving a Problem
  • Task 1 – View Critical Error Alerts in Enterprise Manager
  • Task 2 – View Problem Details
  • Task 3 – (Optional) Create a Service Request
  • Task 4 – Package and Upload Diagnostic Data to Oracle Support Services
  • Task 5 – Track the Service Request and Implement Any Repairs
  • Task 6 – Closing Incidents
• Viewing Problems Using the Enterprise Manager Support Workbench

12 Managing Oracle Database Software

• About Software Management and Patch Releases
• Patching the Oracle Software
  • Determining Your Oracle Database Software Environment
    • Determining Your Oracle Database Version
    • Determining the Location of Your Oracle Home
    • Determining Your Hardware Configuration
  • Setting Your OracleMetaLink Credentials
  • Staging and Applying Patches and Patch Sets
• Viewing Critical Patch Advisories
• Upgrading a Database
  • Overview of Database Upgrade Assistant
    • Pre-Upgrade Checks
    • Automated Upgrade Tasks
    • Support for Oracle Real Application Clusters
    • Support for Automatic Storage Management
    • Support for Silent Mode
  • Database Versions Supported by DBUA
  • Starting DBUA
  • Upgrading a Database Using DBUA
• Managing Oracle Software: Oracle By Example Series

A Administering Automatic Storage Management

• About Automatic Storage Management
• Overview of Disks, Disk Groups, and Failure Groups
• Installing Automatic Storage Management
• Accessing the Automatic Storage Management Home Page
  • Accessing the ASM Home Page on a Single-Instance System
  • Accessing the ASM Home Page on an Oracle RAC System
• Monitoring Disk Group Space Usage
• Creating a Disk Group
• Dropping a Disk Group
• Adding Disks to a Disk Group
• Dropping Disks from a Disk Group
• Backing Up ASM-Managed Files
• Automatic Storage Management: Oracle By Example Series

Index