Free Sample
+ Collection

Oracle 10g/11g Data and Database Management Utilities

Hector R. Madrid

Master 12 must-use Oracle Database Utilities with this Oracle book and eBook
RRP $29.99
RRP $49.99
Print + eBook

Want this title & more?

$12.99 p/month

Subscribe to PacktLib

Enjoy full and instant access to over 2000 books and videos – you’ll find everything you need to stay ahead of the curve and make sure you can always get the job done.

Book Details

ISBN 139781847196286
Paperback432 pages

About This Book

  • 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

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.

Table of Contents

Chapter 1: Data Pump
Data Pump features
Data Pump architecture
Setting up the practical scenarios
Data Pump export
Data Pump restart capability
Getting information about the export job
Data Pump import
Improving performance with Data Pump
Working with the Data Pump API
Data Pump 11g new features
Chapter 2: SQL*Loader
SQL*Loader basics
Preparing the demo environment
Specifying a particular character set
Load on the fly
Direct path versus Conventional path load
Loading Large Objects (LOBs)
Loading multimedia files
Resumable load
Parallel load
General performance booster tips
Chapter 3: External Tables
The External Table basics
Let's setup the environment
Inter-version compatibility
Data transformation with External Tables
Mapping XML files as External Tables
Dynamically changing the external reference
Oracle 11g External Table enhancements
Chapter 4: Recovery Manager Advanced Techniques
Recovery Manager basics
Backup compression
Faster backups through intra-file parallel backup and restore operations (11g only)
Block media recovery
Backup duration and throttling
Database cloning
Inter-platform database migration
Migrate to and from an ASM environment
General backup advices
Chapter 5: Recovery Manager Restore and Recovery Techniques
Oracle database recovery
Loss of data files
Loss of redo log files
Test restore
Crosscheck command
Nologging considerations
Chapter 6: Session Management
User sessions in a dedicated server architecture
Blocking sessions
Resource Manager
Active Session History (ASH)
Session monitoring, the traditional way
Chapter 7: Oracle Scheduler
Oracle Scheduler concepts
Getting started with the Oracle Scheduler
Time expression syntax
Jobs and Job Classes
Managing the Scheduler
Data dictionary related views
Chapter 8: Oracle Wallet Manager
The Oracle Wallet Manager
Oracle Wallet Manager CSR generation
Storing the Oracle Wallet in the Windows registry
Storing the Wallet in an LDAP server
Using certificates for authentication
Using the Oracle Wallet to store database credentials
Chapter 9: Security Management
Using the Oracle Wallet to encrypt backups
The enterprise user
Chapter 10: Database Configuration Assistant
Database creation
Database edition
Database template management
Automatic Storage Management configuration
DBCA, Batch mode
Chapter 11: Oracle Universal Installer
OUI basics
OUI basic and advanced installation modes
Modes of installation
OUI command line parameters
Silent installation mode
The oraparam.ini file
OUI return codes
Installing Oracle from the Web
Recovering a lost Inventory
Cloning Oracle Home using OUI
Chapter 12: Enterprise Manager Configuration Assistant
Enterprise Manager Components
Enterprise Manager configuration
EMCA Command Line Interface
EMCA 10g Release 1
EM directory structure
Environment changes
Securing Enterprise Manager
Chapter 13: OPatch
Downloading the latest OPatch version
OPatch requirements
OPatch syntax
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
Hot patching (11g only)
Troubleshooting OPatch
Using Enterprise Manager for software maintenance
Managing Patches in EM 11g

What You Will Learn

  • 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.


Read More

Recommended for You

Oracle Database 11g – Underground Advice for Database Administrators
$ 21.00