Does your database seem complicated? Are you finding it difficult to work with it efficiently? Database administration is part of a daily routine for all database professionals. Using Oracle Utilities, administrators can benefit from improved maintenance windows, optimized backups, faster data transfers, and more reliable security, and can in general do more with the same time and resources.
You don't have to reinvent the wheel, just learn how to use Oracle Utilities properly to achieve your goals. That is what this book is about; it covers topics which are oriented towards data management, session management, batch processing, massive deployment, troubleshooting, and how to make the most out of 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 ever increasing amount of data and need for business availability, that problems with any particular approach get identified. People tend to get used to using the old export/import utilities to perform data management and if it works, they probably will consider the problem solved and 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 the reader how to adopt more optimized data management techniques focusing on the tools and requirements most frequently seen in modern production environments.
Chapter 1 deals with Data Pump. Data Pump is a versatile data management tool. It 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 involves a description of the SQL*Loader. It describes how SQL* Loader is the tool to upload plain text format files to the database. If SQL* Loader properly configured, you can greatly increase the speed with which uploads are completed. Loading data to take 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 is all about 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 specializes in advanced techniques involved in optimizing the Recovery Manager. Recovery Manager can be optimized to minimize the impact in production environments; or it can run faster using parallel techniques. It can be used to clone a database on the same OS or transport it over different platforms, or even change the storage method between ASM and conventional file system storage and vice versa.
Chapter 5 talks about the Recovery Manager. Recovery manager first appeared back in 8.0, but it was not until 9i that it began to gain popularity among DBAs 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 recovery scenarios.
Chapter 6 is about Session Management. The users are the main reason why the 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 diagnose and solve this problem? What does it mean to kill a user session? Managing sessions means you can regulate them by means of Oracle profiles; this may sooner or later lead to snipped sessions; what are those snipped sessions? How do you get rid of them? This chapter discusses several user session management issues.
Chapter 7 talks about 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 will teach you about Oracle Wallet Manager. Oracle Wallet Manager is the cornerstone and entry point for advanced security management. You can use it to 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 using OS Authentication mechanisms by storing the user password inside the wallet.
Chapter 9 deals with security of the system. 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 tool which can be used 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 talks about 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 provides details about the 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 backups, as well as configuration file backups, but what about the Oracle installer repository? This set of files is most often underestimated unless a hardware failure makes 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 is about the 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 to complete through character console mode. But what happens when it is not available, either because of a change in the network topology or a firewall that restricts access to the managing port? Then the user needs to have the console reconfigured to bring it back into operation. EMCA is the character mode tool used to perform this task.
Chapter 13 talks about OPatch. Patching the RDBMS is required to keep 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.
This book requires the reader to know the basics of SQL, and have some experience with Oracle 10g and 11g databases.
This book covers an Oracle database installation on Linux, although the techniques detailed are equally applicable to other operating systems.
This book is aimed at all Oracle professionals who wish to employ must-use data and database utilities, and optimize their database interactions.
Entry-level users can acquaint themselves with the best practices needed to get jobs 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 process.
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text are shown as follows: "We can include other contexts through the use of the include directive."
A block of code will be set as follows:
BEGIN dbms_resource_manager_privs.grant_switch_consumer_group( grantee_name => 'SCOTT', consumer_group => 'OLTP', grant_option => FALSE ); END;
When we wish to draw your attention to a particular part of a code block, the relevant lines or items will be shown in bold:
ALPHA = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = alpha)(PORT = 1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = alpha)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = alpha) ) )
Any command-line input or output is written as follows:
orapki wallet create -wallet <Path to Wallet>
New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in our text like this: "From the main menu choose the Operations menu and then select the Add Certificate Request submenu, a form as shown in the following screenshot will be displayed where you can capture specific information.".
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply drop an email to <[email protected]>, and mention the book title in the subject of your message.
If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or email <[email protected]>.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Visit http://www.packtpub.com/files/code/6286_Code.zip to directly download the example code.
The downloadable files contain instructions on how to use them.
Although we have taken every care to ensure the accuracy of our contents, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in text or code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration, and help us to improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the let us know link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata added to any list of existing errata. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.