Oracle 10g/11g Data and Database Management Utilities

Oracle 10g/11g Data and Database Management Utilities
eBook: $29.99
Formats: PDF, PacktLib, ePub and Mobi formats
save 15%!
Print + free eBook + free PacktLib access to the book: $79.98    Print cover: $49.99
save 37%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Table of Contents
Sample Chapters
  • 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

Book Details

Language : English
Paperback : 432 pages [ 235mm x 191mm ]
Release Date : June 2009
ISBN : 1847196284
ISBN 13 : 9781847196286
Author(s) : Hector R. Madrid
Topics and Technologies : All Books, Enterprise Products and Platforms, Oracle Database, Enterprise, Oracle

Table of Contents

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

Hector R. Madrid

Hector Madrid is currently working as a freelance consultant. He is an Oracle ACE, collaborates with Oracle University as a certified instructor for the DBA and Java curriculum tracks. He is a highly respected Oracle professional with 20 years of experience as a full time DBA. He works with a wide range of DBA requirements from the daily DBA routine duties to tasks related to mission-critical and high availability systems. He was the first Oracle Certified Master in Latin America and he holds the certificate for all Oracle Version starting with 7.0 and up to 11g.

He obtained a Masters Degree in Computer Sciences from the Metropolitan Autonomous University (UAM) and he has presented different technical papers at several Oracle conferences.

Code Downloads

Download the code and support files for this book.

Submit Errata

Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.

Sample chapters

You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

Frequently bought together

Oracle 10g/11g Data and Database Management Utilities +    Programming Microsoft Dynamics® NAV 2013 =
50% Off
the second eBook
Price for both: £30.75

Buy both these recommended eBooks together and get 50% off the cheapest eBook.

What you will learn from this book

  • Improve performance and manageability using the advanced features of direct export/import utilities among different databases
  • Optimize your maintenance windows related to data management tasks such as importing data from one database to another using Data Pump and SQL*Loader
  • Perform more than just the ETL processes by taking advantage of the external tables feature
  • Use Oracle Scheduler to specify maintenance windows, assign priorities, configure job classes and many more features, and take decisions based on the task outcome
  • Get acquainted with all the possibilities the Oracle Universal Installer tool offers to make the installation task more efficient
  • Execute effective database creations: not just default creations, but comprehensive database creations
  • Configure and manage an ASM environment using DBCA
  • Improve performance and reduce the impact of recovery manager database backups in production environments
  • Increase the security in an Oracle environment, protect the backups, and manage certificates using Oracle Wallet Manager
  • Perform installations in batch environments and manage your software updates related to Critical Patch Updates (CPU) or individual patches using OPatch

Chapter 1: Data Pump – Data Pump is a versatile data management tool. This is much more than just an exp/imp upgrade, it allows remapping, dump file size estimation, restartable tasks, network transfers, advanced filtering operations, recovering data after a commit has been issued, and transferring data files among different oracle versions. It includes a PL/SQL API so it can be used as a base to develop data pump-based systems.

Chapter 2: SQL*Loader – SQL*Loader the tool to upload plain text format files to the database; if it is properly tuned you can boost the upload performance. Loading data taking care of the character set will avoid unnecessary headaches and you can optimize your loading window. There are several tips and tricks to load different character sets to the database and load binary data to BLOB fields. This tool can be used to load data on the fly and you will learn how to proactively configure it to get a smooth load.

Chapter 3: External Tables – The external table is a concept oracle introduced in 9i to ease the ETL (Extraction Transformation and Loading) DWH process. An external table can be created to map an external file to the database so you can seamlessly read it as if it was a regular table. You can extend the use of the external tables concept to analyze log files such as the alert.log or the network log files inside the database. The external table concept can be implemented with the data pump drivers; this way you can easily and selectively perform data transfers among databases spanning different Oracle versions.

Chapter 4: Recovery Manager Advanced Techniques -  Recovery manager can be optimized to minimize the impact in production environments, it can run faster using parallel techniques.  It can be used to clone a database on the same O.S. or  transport it over different platforms, or even change the storage method between ASM and conventional file system storage and viceversa.

Chapter 5: Recovery Manager – Recovery manager first appeared back in 8.0, but it was until 9i when it began to gain popularity among DBA's as the default backup/recover tool.  It is simple and elegant and the most frequently used commands are pretty simple and intuitive.  This chapter presents several practical database backup and recovery scenarios.

Chapter 6: Session Management – The users are the main reason why a DBA exists. If it were not for the users, there would be no database activity and there would be no problems to be solved. How can you easily spot a row lock contention problem? What should be done to have this problem diagnosed and solved? What does it imply to kill a user session? Managing sessions means you can regulate them by means of the Oracle profiles; this may leave sooner or later snipped sessions; what are those snipped sessions? And what does it imply getting rid of them? This chapter discusses several user session management issues.

Chapter 7: The Oracle Scheduler – The Oracle scheduler is a powerful tool used to schedule tasks in Oracle. This tool can perform simple schedules as well as complex schedules; you need to understand time expressions and the Oracle scheduler architecture to take advantage of this utility.

Chapter 8: Oracle Wallet Manager – Oracle Wallet Manager is the cornerstone and entry point for advanced security management. You can manage certificates and certificate requests, you can store identity certificates and retrieve them from a central location, or you can use the registry in a Windows environment. You can hide passwords without OS Authentication mechanisms by storing the user password inside the wallet.

Chapter 9: Security – Most people worry about having a valid backup that can be used to effectively recover data, but not all of them are concerned about the backup security; if a backup can be used to recover data, this doesn't actually mean the data will be recovered at the same site where it was taken from. OWM is a key tool to have the backup encrypted, so sensitive data can be secured not only from the availability point of view, but also from the confidentiality point of view. Security has to do also with identifying who the real user is; this can be achieved with the enterprise user. This chapter explains step by step how to set up an environment with enterprise identity management using the Enterprise Security Manager.

Chapter 10: Database Configuration Assistant – Creating a database is one of the first tasks the user performs when installing Oracle, but this tool goes far beyond the simple task of creating the database; it can be used to manage templates, create a database in silent mode, and configure services in an RAC environment. Configuring database options and enabling the Enterprise Manager DB Control can be done here. DBCA is also the easy way to start up and configure an Automatic Storage Management (ASM) environment.

Chapter 11: Oracle Universal Installer – Installing Oracle is more than just a next → next button pressing activity, OUI is a tool to manage software. Most people care about database backup, as well as configuration file backup, but what about the Oracle installer repository? This set of files is most often underestimated unless a hardware failure make the DBA understand what Oracle software maintenance is. OUI can perform silent and batch installations; it can also perform installations from a central software depot accessible through the Web.

Chapter 12: Enterprise Manager Configuration Assistant – Most DBAs use EM as the basic DBA administration tool; it is a very intuitive database management console. Most people depend on it to easily perform most of the administration and operation tasks that otherwise would be time consuming through character console mode. But what happens when it, somehow is not available, either by a change in the network topology or a firewall that restricts access to the managing port? Then the user requires to have the console reconfigured to bring it back into operation. EMCA is the character mode tool used to perform this task.

Chapter 13: OPatch – Patching the RDBMS is a required task to have the software up to date. When a patchset is to be applied OUI is used, but when a single patch or a CPU is to be applied OPatch must be used. You will learn how to perform a basic patch application task, list the patch inventory, find out if a patch has already been applied, maintain the software and the software inventory, and learn how and when to perform a patch application while the database is up and running.

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.

Book Reviews
I have being reading the "Oracle 10g/11g Data and Database Management Utilities" eBook. I appreciate the chapters on
SQL Loader,
External Tables,
Session Management (locking and waiting for locks, killing sessions, resource manager, ASH, even Service Registration) {never before have I seen all these topics put together in a Chapter....................} - Hemant Chitale

Ask Anantha:
For a long time, there has been a demand for technical details of what a database administrator deals in his day-to-day life and this book addresses to this need. The book explains in detail the various tools the DBAs would use and then discusses the feature enhancements done in 11g. Also examples constitute a sizable portion of the book which are nice to have in a book of this magnitude.
In short this book deserves its place in your custody and will prove to be best in times of database administration activities -


This book is written using a practical approach that guides you through different practical scenarios. It provides a brief introduction to the topics; this way you can quickly get to know the main features, start being productive with the tool, and grow with it at a fast pace. You won't have to spend too much time getting to the basics of the tool and can immediately progress towards the advanced tips.

Using this practical approach you don't have to spend valuable time trying to decipher arid reference manuals; you can easily set up the practical scenarios, and try to follow up the presentations. The images used in the book come from real scenarios, and the output is always explained so you can easily interpret what is being displayed on the screen after issuing the commands.

Who this book is for

This book is aimed at all Oracle professionals who interact with the database through the data and database utilities and are willing to optimize their interaction with it.

Entry-level users will get acquainted with the best practices to get their job done in a timely and efficient manner. Advanced users will find useful tips and How-Tos that will help them focus on getting the most out of the database utilities and fine-tune batch processing.

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software