Oracle Database Administration: The Essential Refe

Oracle Database Administration: The Essential Refe

Released April 1999
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781565925168

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon
Start your free trial

Book description

Oracle database administration requires a vast amount of information and an ability to perform a myriad of tasks--from installation to tuning to network troubleshooting to overall daily administration. Oracle provides many tools for performing these tasks; the trick is knowing what tool is right for the job, what commands you need to issue (and when), and what parameters and privileges you need to set. And, as every DBA knows, you need to know how do all this under pressure, while you face crisis after crisis. This book provides a concise reference to the enormous store of information an Oracle DBA needs every day (as well as what's needed only when disaster strikes). It's crammed full of quick-reference tables, task lists, and other summary material that both novice and expert DBAs will use time and time again. It covers the commands and operations new to Oracle8, but also provides Oracle7 information for sites still running earlier versions. Oracle Database Administration provides two types of material:

  • DBA tasks--chapters summarizing how to perform critical DBA functions: installation, performance tuning, preventing data loss, networking, security and monitoring, auditing, query optimization, and the use of various Oracle tools and utilities

  • DBA reference--chapters providing a quick reference to the Oracle instance and database, the initialization (INIT.ORA) parameters, the SQL statements commonly used by DBAs, the data dictionary tables, the system privileges and roles, and the SQL*Plus, Export, Import, and SQL*Loader syntax

The book also includes a resource summary with references to additional books, Web sites, and other online and offline resources of special use to Oracle DBAs. Oracle Database Administration is the single essential reference you'll turn to again and again. If you must choose only one book to use at the office, keep at home, or carry to a site you're troubleshooting, this will be that book.

Visit the catalog page for Oracle Database Administration: The Essential RefeVisit the errata page for Oracle Database Administration: The Essential RefeDownload the supplemental electronic content for Oracle Database Administration: The Essential Refe

Table of contents

  1. Dedication
  2. Preface
    1. Why We Wrote This Book
    2. Versions of Oracle
    3. How This Book Is Organized
      1. Part I
        1. Part II
    4. Conventions Used in This Book
    5. Comments and Questions
    6. Acknowledgments
  3. I. DBA Tasks
    1. 1. Introduction
      1. Oracle Database Administration
        1. A DBA Job Description
        2. The DBA and This Book
          1. The tasks DBAs do
          2. The information DBAs need
      2. Oracle Architecture
        1. Database Versus Instance
        2. Background Processes
        3. System Global Area (SGA)
        4. Files
        5. Logical Database Structures
      3. Software Options
        1. Base Product
        2. Options
        3. Data Cartridges
        4. Development Tools
        5. Applications
        6. Licensing by the User
        7. Version Numbers
      4. Configuration Planning
        1. Availability
          1. Replication
          2. Hot standby database
          3. Oracle Parallel Server
        2. Backup and Recovery
          1. Export/import
          2. Archivelog mode
          3. Cold backup
          4. Hot backup
          5. Recovery Manager (RMAN)
        3. Performance
          1. Memory
          2. Disk layout
    2. 2. Installation
      1. Media Selection
      2. The Oracle Installer
      3. Installable Components
      4. Dependencies
      5. Pre-Installation Checklist
      6. Critical Decisions
        1. The Starter Database
        2. Oracle Directory Structure
      7. Post-Installation Tasks
        1. Creating a Database
          1. Choosing a blocksize
          2. Choosing the name
          3. Creating the parameter file
        2. Building Database Creation Scripts
          1. A sample script
    3. 3. Maximizing Oracle Performance
      1. Configuration and Tuning—What’s the Difference?
        1. What Can Be Configured?
        2. What Can Be Tuned?
      2. Achieving Maximum Performance
      3. Configuring the Operating System
      4. Configuring Oracle
        1. Configuration Guidelines
          1. Check the documentation
          2. Check resource requirements
          3. Check system privileges
          4. Determine control file locations
        2. SQL*Net Configuration
        3. Multi-Threaded Server Configuration
        4. Parallel Query Configuration
        5. Parallel Server Configuration
      5. Sizing and Configuring Database Objects
        1. Tables
        2. Indexes
        3. Rollback Segments
        4. Sort Areas
        5. Temporary Tablespaces
        6. Redo Logs
        7. Archive Log Destination
      6. Tuning Oracle
        1. A Structured Tuning Approach
        2. Oracle Instance Tuning
          1. Memory utilization
          2. Disk I/O
          3. A note about RAID
          4. Operating system striping
          5. Oracle striping
        3. SQL Tuning
          1. Query processing
        4. Other Useful Tuning Features
          1. Partitioned tables
          2. Index-only tables
          3. Bitmap indexes
          4. Temporary tablespaces
          5. Unrecoverable operations
    4. 4. Preventing Data Loss
      1. Types of Backups
        1. Physical Backups
          1. Benefits
          2. Limitations
        2. Logical Backups
          1. Benefits
          2. Limitations
        3. Archivelog Mode
          1. Benefits
          2. Limitations
        4. Incremental Backups
          1. Benefits
          2. Limitations
      2. Database Backup
        1. Cold Backup
          1. Benefits
          2. Limitations
        2. Hot Backup
          1. Benefits
          2. Limitations
        3. EXPort/IMPort
          1. Benefits
          2. Limitations
          3. Using incremental exports
          4. Direct path versus conventional path
          5. Using the EXP tables
          6. Other uses for Export
      3. Database Recovery
        1. Recovery Scenarios
          1. System failure
          2. Disk failure
          3. Database corruption
          4. Data corruption
        2. Tablespace Point-in-Time Recovery
          1. Overview of the process
          2. Benefits
          3. Limitations
          4. Data dictionary views
      4. Automated Utilities
        1. Oracle Recovery Manager
        2. Oracle Enterprise Backup Utility
        3. Third-Party Utilities
      5. Practice Scenarios
        1. Export/Import
        2. Hot or Cold Backups
        3. Other Techniques
    5. 5. Oracle Networking
      1. Oracle Network Architecture
        1. SQL*Net and Net8
          1. SQL*Net functionality
          2. How SQL*Net works
          3. Oracle services
        2. Advanced Networking Option
          1. Network security
          2. Single sign-on
          3. DCE integration
          4. DCE naming adapter
      2. Oracle Names
      3. MultiProtocol Interchange
      4. Connecting to Non-Oracle Databases
        1. Open DataBase Connectivity
        2. Gateways
      5. Oracle Network Manager
        1. Network Manager Data Storage
        2. Using Network Manager
        3. Files Created by Network Manager
      6. Oracle Net8 Assistant
      7. Manual Network Configuration
      8. Sample SQL*Net Files
        1. listener.ora
        2. tnsnames.ora
        3. sqlnet.ora
      9. SQL*Net Troubleshooting
        1. Some Common ORA Errors, Causes, and Corrective Actions
        2. SQL*Net Troubleshooting Procedures
          1. Determine basic server operation
          2. Determine basic client connectivity
    6. 6. Security and Monitoring
      1. Security
        1. Security Policy
          1. User access to the database
          2. Read sensitivity of the data
          3. Write sensitivity of the data
          4. Audit policy
        2. Creating Users
        3. Profiles
          1. Resource control
          2. Password control
          3. Default profile
          4. Implementing profiles
        4. System Privileges
        5. Object Privileges
          1. The WITH GRANT OPTION clause
        6. Roles
          1. Default roles
          2. Maximum number of enabled roles
          3. Password-enabled roles
          4. The WITH ADMIN OPTION clause
        7. Common Security Holes
          1. Not changing privileged user passwords
          2. Using the same password in all databases
          3. The SCOTT/TIGER account
          4. Shared UTL_FILE access to directories
        8. Auditing
      2. Monitoring
        1. What to Monitor
          1. Memory utilization
          2. Storage utilization
        2. How to Monitor
          1. Getting a baseline
          2. SQL queries to monitor SGA utilization
          3. SQL queries to monitor storage utilization
    7. 7. Auditing
      1. About Auditing
        1. Types of Auditing Supported
        2. Scope of Auditing
        3. Limitations of Auditing
        4. Implementing Auditing
        5. Disabling Auditing
      2. Forms of the AUDIT Statement
        1. Schema Object Auditing
          1. Examples of schema object auditing
        2. Statement Auditing
          1. Examples of statement auditing
        3. System Privilege Auditing
      3. Audit Trail Views and Lookup Tables
        1. Data Dictionary Views
          1. Views containing audit trail information
          2. Views containing auditing specifications
        2. Lookup Tables
      4. Establishing an Audit Policy
      5. Maintaining the Audit Trail
        1. Example Script to Copy and Summarize Session Information
        2. Example Script to Clean Out the Audit Trail
      6. Row-Level Auditing
    8. 8. Query Optimization
      1. Types of Queries
        1. Simple Queries
        2. Joins
        3. Subqueries
        4. Correlated Subqueries
        5. Cartesian Product
      2. Types of Join Access Paths
        1. Nested Loops
        2. Merge Joins
        3. Hash Joins
      3. Determining the Access Plan
        1. The PLAN_TABLE Table
        2. The EXPLAIN PLAN Statement
        3. SQL*Plus AUTOTRACE
        4. SQL Trace and TKPROF
          1. Initialization parameters
          2. Enabling SQL Trace for a specific session
          3. Postprocessing with TKPROF
        5. Oracle Enterprise Manager Top Sessions
      4. Cost-Based Optimizer
        1. Initialization Parameters
        2. Data Dictionary Requirements
          1. ANALYZE TABLE
          2. ANALYZE INDEX
          3. ANALYZE TABLE . . . STATISTICS FOR COLUMNS
          4. ESTIMATE versus COMPUTE
          5. How often to ANALYZE
          6. Script to automate the ANALYZE process
        3. Specifying Hints
          1. How to specify a hint
          2. Scope of a hint
          3. Goal hints
          4. Access method hints
          5. Join order hints
          6. Join operation hints
          7. Parallel operation hints
          8. Additional hints
      5. Rule-Based Optimizer
        1. Rules of Precedence
        2. Outside In
        3. Most Recently Created Index
        4. Examples
    9. 9. Oracle Tools
      1. About the Tools
      2. SQL*Plus
        1. SQL from SQL
        2. Producing Reports
      3. Oracle Server Manager
        1. Graphical Version
        2. Line-Mode Version
        3. The Future of Server Manager
      4. SQLDBA
      5. Oracle Network Manager
      6. SQL*Loader
        1. Conventional and Direct Path Loading
        2. SQL*Loader Files
      7. Oracle Enterprise Manager
      8. Third-Party Tools
  4. II. DBA Reference
    1. 10. The Oracle Instance
      1. Elements of the Instance
        1. Processes
        2. Memory Structures
      2. About Processes
        1. Standard Background Processes
          1. DBWR—the Database Writer
          2. LGWR—the Log Writer
          3. SMON—the System Monitor
          4. PMON—the Process Monitor
        2. Other Background Processes
          1. CKPT—the Checkpoint process
          2. ARCH—the Archiver process
          3. Snnn—the Shared Server process
          4. Dnnn—the Dispatcher process
          5. RECO—the Recoverer process
          6. LCKn—the Lock process
          7. Pnnn—the Parallel Query process
          8. QMNn—the Queue Monitor process
          9. SNPn—the Job Queue process
        3. Server Processes
        4. User Processes
          1. Single task (non-SQL*Net)
          2. Two task (with dedicated servers)
          3. Multi-Threaded Server
      3. About Memory Structures
        1. Software Code Areas
        2. System Global Area (SGA)
          1. Database buffer cache
          2. Redo log buffer
          3. Shared pool
            1. Library cache.
            2. Dictionary cache.
            3. Control structures.
        3. Program Global Area (PGA)
        4. Sort Areas
    2. 11. The Oracle Database
      1. Types of Database Files
        1. Parameter Storage Files
          1. Initialization file
          2. Configuration file
        2. Database Coordination Files
          1. Control files
          2. Redo log files
        3. Data Storage Files
      2. Oracle Tablespaces
        1. Oracle Blocks
          1. Fixed block header
          2. Variable block header
          3. Data storage area
        2. Storage Allocation Parameters
        3. How Oracle Allocates Storage
          1. INITIAL
          2. NEXT
          3. MINEXTENTS
          4. PCTINCREASE
        4. A Space Allocation Example
        5. Object Storage Sizing
          1. Free lists
          2. The highwater mark
      3. Disk Allocation
        1. Disk Layout
        2. About RAID
    3. 12. Initialization Parameters
      1. Dynamically Modifiable Parameters
      2. Platform-Specific Parameters
      3. Summary of Initialization Parameters
      4. Parameters Used Only in Oracle7
      5. Parameters New in Oracle8
    4. 13. SQL Statements for the DBA
      1. SQL Commands by Task
      2. SQL Command Syntax
        1. Reference Section
        2. Reference Section
        3. Reference Section
        4. Reference Section
        5. Reference Section
        6. Reference Section
        7. Reference Section
        8. Reference Section
        9. Reference Section
        10. Reference Section
        11. Reference Section
        12. Reference Section
        13. Reference Section
        14. Reference Section
        15. Reference Section
        16. Reference Section
        17. Reference Section
        18. Reference Section
        19. Reference Section
        20. Reference Section
        21. column_constraint_clause
          1. Note
          2. Examples
          3.  
        22. auto_extend_clause
          1. Notes
          2. Examples
          3.  
        23. for_clause
          1. Notes
          2. Example
          3.  
        24. filespec
        25. autoextend_clause
          1. Notes
          2. Example
          3.  
        26. global_index_clause
        27. local_index_clause
          1. Notes
          2. Example
          3.  
        28. table_constraint_clause
        29. table_ref_clause
        30. column_ref_clause
        31. column_constraint_clause
        32. index_organization_clause
        33. segment_attrib_clause
        34. partition_clause
          1. Notes
          2. Examples
          3.  
        35. autoextend_clause
          1. Notes
          2. Example
          3.  
    5. 14. The Oracle Data Dictionary
      1. Static Data Dictionary Views
        1. Families of Views
        2. Commonly Used Data Dictionary Views
          1. Advanced Queuing
          2. Audit trail
          3. Constraints
          4. Dictionary
          5. Indexes
          6. Jobs
          7. Large objects (LOBs)
          8. Locks
          9. Net8
          10. Objects Option
          11. Partitioning
          12. PL/SQL
          13. Security
          14. Sequences
          15. Server management
          16. Storage
          17. Synonyms
          18. Tables, clusters, and views
          19. Others
        3. Other Static Data Dictionary Views
          1. Advanced replication
          2. Export
          3. Gateways
          4. Oracle Parallel Server
          5. Remote procedure calls
          6. Snapshots
          7. SQL*Loader
          8. Tablespace point-in-time recovery
          9. Trusted Oracle
      2. Dynamic Performance Data Dictionary Views
        1. Availability of the Dynamic Performance Data Dictionary Views
        2. How the Dynamic Performance Data Dictionary Views Are Built
        3. The Global Dynamic Performance Data Dictionary Views (GV$ Views)
        4. New Views with Oracle8
        5. Dynamic Views
          1. Advanced Queuing
          2. Configuration
          3. Data dictionary cache
          4. Database
          5. Instance
          6. Locks and latches
          7. Multi-Threaded Server
          8. Oracle Parallel Server
          9. Parallel Query
          10. Recovery
          11. Security
          12. Session
          13. SGA
          14. SQL
          15. SQL*Loader direct path
          16. System environment
    6. 15. System Privileges and Initial Roles
      1. Actions, Privileges, and Roles
      2. System Privileges
        1. Privileges That Affect the Entire Database
        2. Privileges That Allow You to Create Objects in Your Own Schema
        3. Privileges That Allow You to Manipulate Objects in Any Schema
        4. Summary of System Privileges
        5. New System Privileges in Oracle8
      3. Initial Roles
        1. Automatic Roles
        2. Optional Roles
        3. Cross-Reference: Roles and System Privileges
      4. Initial Users
        1. Summary of Initial Users
        2. Cross-Reference: Roles Assigned to Initial Users
        3. Cross-Reference: System Privileges Assigned to Initial Users
    7. 16. Tools and Utilities
      1. SQL*Plus
        1. Command-Line Syntax
        2. SQL*Plus Editing Commands
        3. Formatting SQL*Plus Output
        4. Other SQL*Plus Commands
        5. SQL Variables and Related Commands
        6. SQL*Plus System Variables
      2. Export
        1. Command-Line Syntax
        2. Parameter File Syntax
        3. Interactive Mode Syntax
        4. Export Parameters
      3. Import
        1. Command-Line Syntax
        2. Parameter File Syntax
        3. Interactive Mode Syntax
        4. Import Parameters
      4. SQL*Loader
        1. Command-Line Syntax
        2. Control File Syntax
          1. delimiter_spec
          2. datatype_spec
  5. A. Appendix:Resources for the DBA
    1. Books
    2. Other Publications
    3. Organizations
    4. Web Sites
    5. Discussion Groups
    6. List Servers
  6. Index
  7. Colophon
  8. Copyright

Product information

  • Title: Oracle Database Administration: The Essential Refe
  • Author(s):
  • Release date: April 1999
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781565925168