Oracle 10g/11g Data and Database Management Utilities

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


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.

  • 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
Page Count 432
Course Length 12 hours 57 minutes
ISBN 9781847196286
Date Of Publication 25 Jun 2009


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.