You are previewing Oracle 10g/11g Data and Database Management Utilities.
O'Reilly logo
Oracle 10g/11g Data and Database Management Utilities

Book Description

Master twelve must-use utilities to optimize the efficiency, management, and performance of your daily database tasks

  • Optimize time-consuming tasks efficiently using the Oracle database utilities

  • Perform data loads on the fly and replace the functionality of the old export and import utilities using Data Pump or SQL*Loader

  • Boost database defenses with Oracle Wallet Manager and Security

  • A handbook with lots of practical content with real-life scenarios

In Detail

Does your database look complicated? Are you finding it difficult to interact with it? Database interaction is a part of the daily routine for all database professionals. Using Oracle Utilities the user can benefit from improved maintenance windows, optimized backups, faster data transfers, and more reliable security and in general can do more with the same time and resources.

You don't have to reinvent the wheel, just learn how to properly use the Oracle Utilities to achieve your goals. This is what this book is about; it covers topics oriented towards data management, session management, batch processing, massive deployment, troubleshooting, and how to make the most out of the frequently used DBA tools to improve your daily work.

Data management is one of the most frequently required tasks; doing a backup is a must-do task for any company. Data management includes several tasks such as data transfers, data uploading and downloading, reorganizing data, and data cloning, among many others. If people learn to use a tool and things appear to go well, few will question if their approach is optimal. Often it is only when maintenance windows start shrinking, due to the amount of data and business availability, that problems with any particular approach get identified. If people get used to the old export/import utilities to perform data management and if it works, they probably will consider the problem solved and will continue to use an obsolete tool. This book explores further possibilities and new tools. It makes the user question if his/her current environment is optimized and teaches how to adopt more optimized data management techniques focusing to the tools and the requirements most frequently seen in modern production environments.

Table of Contents

  1. Oracle 10g/11g Data and Database Management Utilities
  2. Credits
  3. About the Author
  4. About the Reviewer
  5. Preface
    1. What this book covers
    2. What you need for this book
    3. Who this book is for
    4. Conventions
    5. Reader feedback
    6. Customer support
      1. Downloading the example code for the book
      2. Errata
      3. Piracy
      4. Questions
  6. 1. Data Pump
    1. Data Pump features
    2. Data Pump architecture
    3. Setting up the practical scenarios
    4. Data Pump export
      1. Data Pump export modes
      2. A simple Data Pump export session
      3. Data Pump export filtering operations
      4. Use of parameter file
      5. Retrieve original data
      6. Data Pump export space estimation
      7. Dump file multiplexing
      8. Transporting data among different versions
      9. Data Pump export interactive mode
    5. Data Pump restart capability
    6. Getting information about the export job
    7. Data Pump import
      1. Remap function
      2. Data Pump import network mode
    8. Improving performance with Data Pump
    9. Working with the Data Pump API
    10. Data Pump 11g new features
      1. Compression
      2. Encrypted dump file sets
      3. Enhancements for Data Pump External Tables
      4. Single partition transportable for Oracle Data Pump
      5. Overwrite dump files
      6. XML Datatypes
    11. Summary
  7. 2. SQL*Loader
    1. SQL*Loader basics
    2. Preparing the demo environment
      1. Our first simple load
      2. The SQL*Loader log file
      3. Fixed record size format load
      4. Variable record size format load
      5. Stream record format load
    3. Specifying a particular character set
    4. Load on the fly
    5. Direct path versus Conventional path load
      1. Direct path load pros
      2. Direct path load cons
    6. Loading Large Objects (LOBs)
    7. Loading multimedia files
    8. Resumable load
    9. Parallel load
    10. General performance booster tips
    11. Summary
  8. 3. External Tables
    1. The External Table basics
    2. Let's setup the environment
      1. A basic External Table
      2. Creating External Table metadata, the easy way
      3. Unloading data to External Tables
    3. Inter-version compatibility
    4. Data transformation with External Tables
      1. Extending the alert.log analysis with External Tables
      2. Reading the listener.log from the database
            1. Connect String
            2. Protocol
    5. Mapping XML files as External Tables
    6. Dynamically changing the external reference
    7. Oracle 11g External Table enhancements
    8. Summary
  9. 4. Recovery Manager Advanced Techniques
    1. Recovery Manager basics
      1. Getting started with a Recovery Manager session
      2. Format masks used by recovery manager
      3. What happens in a user-managed online backup?
        1. Myths related to the online backup method
      4. Configuring a multiplexed backup
      5. Configuring the RMAN recovery catalog
      6. A simple backup session
    2. Backup compression
      1. Fast backup compression
      2. Improving data set compression with the ZLIB algorithm (11g only)
    3. Faster backups through intra-file parallel backup and restore operations (11g only)
    4. Block media recovery
    5. Backup duration and throttling
    6. Database cloning
      1. Database cloning procedure
      2. Database cloning on the fly (11g only)
    7. Inter-platform database migration
    8. Migrate to and from an ASM environment
    9. General backup advices
    10. Summary
  10. 5. Recovery Manager Restore and Recovery Techniques
    1. Oracle database recovery
      1. Instance failure
      2. Media failure
      3. Complete recovery
      4. Incomplete recovery
    2. Loss of data files
      1. Queries used to diagnose data files
      2. Loss of a non-critical datafile
      3. Loss of a temporary datafile
        1. Managing temporary datafiles
      4. Loss of a critical datafile
    3. Loss of redo log files
      1. Loss of the inactive redo log group
      2. Loss of the current redo log group
    4. Test restore
    5. Crosscheck command
    6. Nologging considerations
    7. Summary
  11. 6. Session Management
    1. User sessions in a dedicated server architecture
      1. Instance self registration process
    2. Blocking sessions
      1. Optimistic versus pessimistic locking
      2. Row lock contention monitoring
      3. Killing sessions
      4. Deadlock handling
      5. Sniped sessions
      6. Orakill
    3. Services
    4. Resource Manager
      1. Resource Manager Elements
      2. Configuring resources assigned to users
      3. Configuring resources assigned to services
        1. Creating the database user
        2. Service names definition
        3. Listener verification
        4. TNS entry configuration
        5. Resource consumer group creation
        6. Service mapping
        7. Resource plan definition
        8. Resource manager plan activation
        9. Testing and monitoring
    5. Active Session History (ASH)
    6. Session monitoring, the traditional way
    7. Summary
  12. 7. Oracle Scheduler
    1. Oracle Scheduler concepts
    2. Getting started with the Oracle Scheduler
      1. Required privileges
      2. Scheduling our first job
        1. Creating the job
        2. Specifying procedure arguments
        3. Enabling the job schedule
        4. Using Enterprise Manager
    3. Time expression syntax
      1. The repeat interval
      2. Regular schedule
      3. Combined schedule
        1. Exclude scenario
        2. Include scenario
        3. Intersect scenario
      4. Time expression examples
    4. Programs
      1. Creating programs manually
      2. Defining a program using Enterprise Manager
    5. Schedules
    6. Jobs and Job Classes
    7. Managing the Scheduler
      1. Enable or disable components
      2. Managing job logs
        1. Monitor a Job Execution
        2. Purging the job log
    8. Data dictionary related views
    9. Summary
  13. 8. Oracle Wallet Manager
    1. The Oracle Wallet Manager
      1. Creating the Oracle Wallet
      2. Enabling Auto Login
      3. mkwallet, the CLI OWM version
      4. Managing Wallets with orapki
    2. Oracle Wallet Manager CSR generation
    3. Storing the Oracle Wallet in the Windows registry
      1. Save Wallet to the registry
      2. Open the Wallet from the registry
      3. Save as to a different registry location
      4. Open the Wallet from the registry, save it to the file system and vice versa
      5. Delete the Wallet from the registry
      6. Configuring the Wallet location
    4. Storing the Wallet in an LDAP server
      1. Uploading the Wallet to an LDAP server
      2. Downloading the Wallet from LDAP
    5. Using certificates for authentication
      1. Public Key Infrastructure tools
    6. Using the Oracle Wallet to store database credentials
    7. Summary
  14. 9. Security Management
    1. Using the Oracle Wallet to encrypt backups
      1. Recovery Manager encryption
        1. Using the transparent mode
        2. Using the password mode
        3. Using the dual mode
      2. RMAN backup shredding (11g only)
      3. Data pump encryption
    2. The enterprise user
      1. Configuring the environment
        1. How Oracle SSO works
      2. Configure access to the LDAP directory
      3. Registering the database against the OID
      4. Shared schema
    3. Summary
  15. 10. Database Configuration Assistant
    1. DBCA
    2. Database creation
      1. Database templates
      2. Database identification
      3. Management options
      4. Database credentials
      5. Storage options
      6. Database file locations
      7. Database content
      8. Initialization parameters
        1. Memory
        2. Character sets
        3. Connection mode
      9. Database storage
      10. Creation options
    3. Database edition
    4. Database template management
      1. Template management operations
      2. Creating a seed database out of a current database
      3. Database related file location
      4. Migrating a single instance database to RAC
    5. Automatic Storage Management configuration
      1. ASM
      2. How to setup ASM using DBCA
        1. Select the Configure ASM option
        2. Run the localconfig shell script as root
        3. Set the SYS password and the ASM instance parameters
        4. Setup disk groups
        5. ASM disk group's validation
      3. Setting up ASM in a Windows environment
        1. ASM setup
        2. Disk layout
        3. Logical partitions
        4. Setup ASM
    6. DBCA, Batch mode
      1. DBCA response file example
      2. Where can you get a DBCA response file
    7. Summary
  16. 11. Oracle Universal Installer
    1. OUI basics
      1. OUI components
      2. Setting up a stage area
        1. DVD distribution
      3. Troubleshooting an installation session
      4. Oracle Universal Installer JRE
      5. OUI system requirements
    2. OUI basic and advanced installation modes
      1. OUI Basic Installation
      2. Licensed installed options
      3. OUI Advanced Installation
    3. Modes of installation
    4. OUI command line parameters
      1. Command line variables usage
    5. Silent installation mode
      1. The response file structure and syntax
      2. Customizing a response file
      3. Creating a response file out from an actual installation
      4. The Batch installation, step by step
      5. Creating a response file to perform a batch deinstallation
    6. The oraparam.ini file
    7. OUI return codes
    8. Installing Oracle from the Web
    9. Recovering a lost Inventory
    10. Cloning Oracle Home using OUI
    11. Summary
  17. 12. Enterprise Manager Configuration Assistant
    1. Enterprise Manager Components
      1. Differences between EM DB Control and EM Grid Control
    2. Enterprise Manager configuration
      1. How to find out if the console components are currently installed
      2. Console setup prerequisites
      3. Configuring EM using DBCA
      4. Manually configuring Enterprise Manager with EMCA
      5. Manually assigning EM managing ports
    3. EMCA Command Line Interface
      1. EMCA commands
      2. EMCA flags
      3. EMCA general Command-Line Parameters
      4. EMCA backup parameters
      5. EMCA ASM parameters
      6. EMCA Cluster (RAC) parameters
    4. EMCA 10g Release 1
      1. EMCA 10gR1 syntax
      2. EMCA 10gR1 options
      3. EMCA 10gR1 parameters
      4. EMCA 10gR1 RAC parameters
      5. EMCA silent mode
    5. EM directory structure
      1. EMCA log files
      2. The SYSMAN configuration files
      3. The SYSMAN log files
    6. Environment changes
      1. Changing the IP address or host name
      2. Changing administrative passwords
        1. Changing SYSMAN password
        2. Changing DBSNMP password
    7. Securing Enterprise Manager
    8. Summary
  18. 13. OPatch
    1. OPatch
    2. Downloading the latest OPatch version
    3. OPatch requirements
    4. OPatch syntax
      1. OPatch options
    5. Oracle maintenance using OPatch
    6. Applying a single patch using OPatch
    7. Querying the Oracle inventory
    8. Rolling back a failed OPatch session
    9. Considerations after applying a patch
    10. OPatch in Oracle 11g
    11. Oracle Configuration Manager Registration
    12. Critical Patch Updates
      1. Find out the installed patches
      2. Critical Patch Advisory
    13. Hot patching (11g only)
    14. Troubleshooting OPatch
      1. PATH environment variable
      2. OPatch log files
    15. Using Enterprise Manager for software maintenance
      1. Enterprise Manager Metalink configuration
      2. Refresh from Metalink Job
      3. Downloading and staging patches
      4. The Patch Cache
    16. Managing Patches in EM 11g
      1. Patch Advisor
        1. Critical Security Patches
        2. Feature based patching
      2. View Patch Cache
      3. Patch prerequisites
      4. Stage patch
      5. Apply patch
    17. Summary