39,59 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
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.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 451
Veröffentlichungsjahr: 2009
Ähnliche
Table of Contents
Oracle 10g/11g Data and Database Management Utilities
Hector R. Madrid
Oracle 10g/11g Data and Database Management Utilities
Copyright © 2009 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, Packt Publishing, nor its dealers or distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: June 2009
Production Reference: 1220609
Published by Packt Publishing Ltd.
32 Lincoln Road
Olton Birmingham, B27 6PA, UK.
ISBN 978-1-847196-28-6
www.packtpub.com
Cover Image by Vinayak Chittar (<[email protected]>)
Credits
Author
Hector R. Madrid
Reviewers
Hans Forbrich
Peter McLarty
Ulises Lazarini
Acquisition Editor
James Lumsden
Development Editor
Dhiraj Chandiramani
Technical Editor
John Antony
Indexer
Rekha Nair
Editorial Team Leader
Gagandeep Singh
Project Team Leader
Priya Mukherji
Project Coordinator
Leena Purkait
Proofreader
Lesley Harrison
Production Coordinator
Dolly Dasilva
Cover Work
Dolly Dasilva
About the Author
Hector R. Madrid is a highly respected Oracle professional with 20 years of experience as a full time DBA. He has been working with Oracle databases from version 5.0 up to the latest 11g release. He was the first Oracle Certified Master in Latin America and he holds the Oracle Certified Professional certificate for all Oracle versions starting with 7.3 up to 11g.
He obtained a bachelor's degree in Electronic Engineering from the Metropolitan Autonomous University in 1992, with a major in Digital Systems and Computers. He obtained a Master's degree in Computer Science from the same University. He has collaborated with Oracle Corp. as an instructor teaching the database track since 1996.
Hector works as a database consultant for several major firms, dealing with a wide range of DBA requirements, ranging from daily DBA tasks to defining and maintaining mission critical and high availability systems.
He has presented different technical papers at several Oracle conferences. He is the author of the Blog 'Oracle by Madrid' a Blog specializing in Oracle database topics.
To my parents Higinio Rivera and Teresa Madrid who taught me the basis of who I am now.
My wife Claudia and my daughter Alexandra for their extraordinary patience and support during the development of this book
Hans Forbrich, a respected Oracle ACE Director, who has forged each letter of this title with knowledge and experience, for his valuable technical feedback and all his comments both in this book and in general in the Oracle community.
About the Reviewer
Hans Forbrich has been around computers for 40 years. Indeed, while studying for his BSc EE in the 1970s he worked as a contract programmer to help pay for school. Hans has been working with Oracle products since 1984. In the field service group at Nortel he was introduced to Oracle Database version 4. He joined Oracle Canada to work in the Communications vertical from 1996 to 2002. In 2003 Hans started Forbrich Computer Consulting Ltd., which has become a successful international Oracle consultancy and Oracle training partner based in St. Albert, near Edmonton, Alberta, Canada.
As an Oracle ACE Director and OCP, Hans frequently responds in various Oracle Forums, teaches for Oracle University, consults with Oracle customers on maximizing value from Oracle licenses, and speaks at Oracle User Group conferences around the world. He holds a strong belief that Oracle products provide significant value and the key to extracting that value—and reducing the effective cost of the product—is in understanding the product and using the right tool for the job.
I thank my wife of 27 years for her patience, especially while I experiment in the lab. And also, I thank my two sons for their patience, their assistance at computer setups, and help with those same experiments. (I am proud to note that Son #1, aka Employee #2, aka Chief Network & Systems Administrator, has achieved his MSc EE this past year!) Finally I thank Edmonton Opera and my colleagues there for allowing me to break away from computers and unwind on stage with the Edmonton Opera Chorus.
Peter McLarty has worked with technology for over 25 years. He has been working with Unix and databases for over 10 years with 8 years experience as an Oracle DBA. Peter has worked with Oracle 7.3 through to Oracle 11. Peter has a number of years experience supporting Oracle Application Server. He has experience with RAC and Oracle Maximum Availability Architecture. Peter maintains his own web site with articles about many topics of interest to him and not always about databases or Oracle. Peter has a diverse background in IT supporting his DBA skills and is now involved in Architecture and System Assurance. Peter works for Pacific DBMS, whose office is in Margate, Queensland. Peter is married with 2 children, and several pets to support. When he is not doing things with computers he likes to follow his football team or study things about Asia and learn Thai.
I would like to thank my family for giving me peace to review this book.
Ulises Lazarini is the president of Consultoria Informatica Lazarini, and a partner of Oracle with more than 10 years experience of working with Oracle databases. He has also been an OCP member since Oracle 7.3.4, 8, 8i, 9i, 10g, and so on.
He has been an Oracle instructor in the kernel field for more than 12 years. Ulises has been a speaker at Oracle Open World (September 2008, "Migration from Siebel 7.8 running on SQL Server to Oracle 10g RAC") and a DBA Consultant on two successful Oracle database cases. He has been very active in installing and monitoring RAC environments for OLTP and data warehouse databases.
He has been responsible for high availability on global databases.
Preface
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.
What this book covers
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.
What you need for this book
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.
Who this book is for
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.
Reader feedback
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.
Customer support
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.
Downloading the example code for the book
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.
Errata
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
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.
Questions
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.
Chapter 1. Data Pump
Storage technology is improving day by day, and the more storage that becomes available at a low cost, the more data appears to fill it up. Managing high volumes of data becomes impractical if we take the traditional export/import approach, as this tool is very limited. Let's remember that export/import has been available in Oracle for a very long time, it dates back to Oracle Release 5, and it has been adapted to incorporate some meaningful new features. When 10g first appeared, a complete re-engineering took place and a new product was conceived to meet today's data management requirements. It was the Data Pump.
Data Pump allows better manageability, and performance; it can be parameterized to meet particular data management requirements, such as direct export/import operations between different databases (or even different versions, starting with 10g Release 1). It can remap data object definitions, and access them by means of either a Command Line Interface(CLI) batch or interactive interface. In turn, the data pump Application Programming Interface(API) allows a programmer to embed data pump code inside a regular PL/SQL application so that it manages its own data without requiring a direct Database Administrator (DBA) or Database Owner(DBO) intervention.
Data Pump features
Data Pump provides these features:
Oracle Data Pump is a facility available since Oracle 10g Release 1. It first appeared back in 2003. It enables high speed data and metadata transfers. It is an efficient, manageable, adaptive tool that can be used in more than one mode; namely, the regular command line interface, the suspended mode, the network mode, and the PL/SQL API mode. Besides the CLI interface, it is used by Enterprise Manager, SQL*Loader (by means of the external data pump table driver), the PL/SQL API, and other clients.
Data Pump is a productive tool designed to make the DBA's life easier. It can be easily set to a suspended mode and brought back to work wherever it was stopped. A session does not need an interactive connection to perform data management, so it can leave an unattended job and it can be resumed any time. This tool doesn't need to generate a file to transfer data in a database-to-database mode; it is the so called network mode, which is very useful when a single load is performed. When this data transfer mode is used, data does not have to be erased afterwards as there is no intermediate file created to move the data. The network mode is similar to the conventional named pipes which are used to perform data transfers on the fly; however, this traditional approach is not available on all Operating Systems(OSes) (Windows does not support named pipes). If a task is launched, even if a degree of parallelism hasn't been specified, it can be modified at run time, so resource consumption can be increased or decreased at will.
Data Pump allows high speed data movement from one database to another. The expdp command exports data and metadata to a set of OS files known as a dump file set. Compared with the traditional export/import tool set, Data Pump allows a DBA to easily clone accounts, move objects between tablespaces and change other object features at load time without being required to generate an SQL script to have the object modified, rebuilt and loaded. This kind of on-the-fly object redefinition is known as the remap feature. Data Pump performance is significantly better than that of the old export/import tools.
Data Pump architecture
Data Pump is a server side tool; even if it is remotely invoked, all the command actions and file generation will take place on the host where the database resides, and all directory objects refer to paths in the server. Oracle Data Pump requires a Master Table which is created in the user's schema when a Data Pump session is open. This table records the Data Pump's session status and if the job has to be stopped (either on purpose or due to an unexpected failure), the Data Pump knows where it was when it is brought back to work. This table is automatically purged once the job is finished. The master table will match the job name given, by means of the command line parameter job_name, or Oracle can choose to generate a name for it, in case this parameter hasn't been defined.
Oracle Data Pump has a master process that is responsible for orchestrating the data pump work. This master process is automatically created when either an impdp or expdp is started. Among other things, this process is responsible for populating the master table and spawning several worker processes (in case Data Pump has been directed to work in parallel mode).
Setting up the practical scenarios
Data Pump is a server side tool. In order for it to work with the remote file system it requires an access to the file by means of Oracle directory objects. On the database you must create directory objects and make sure the physical paths at the OS level are readable and writable by the oracle user. The examples provided assume a default database was created with the default oracle demo schemas; we'll be using the SCOTT, HR, SH, and OE demo schemas; when the database is created make sure the default demo accounts are selected.
Let's connect with the SYS administrative account by means of a regular SQL command line interface session, in this example the SYS user is used only for demonstration purposes, and the goal of SYS is to create the directory objects and grant privileges on these directories to the demo users. You can use any user who has been granted privileges to read and write on a directory object.
Let's create two directories, one for the default dump files and the other for the default log dest:
Some privileges are required for the users to have access to these oracle directories:
In this example, the exp_full_database privilege is granted to the demo accounts. This is done to allow the users to work on the database, but you can restrict them to only manage the data that belongs to their schemas.
Data Pump export
Data Pump export (expdp) is the database utility used to export data, it generates a file in a proprietary format. The generated file format is not compatible with the one generated by the old export (exp) utility.
Data Pump export modes
Data Pump export modes define the different operations that are performed with Data Pump. The mode is specified on the command line using the appropriate parameters. Data Pump has the following modes:
Data Pump Export provides different working interfaces such as:
A simple Data Pump export session
Now let's start with our first simple Data Pump export session. This will show us some initial and important features of this tool.
Here we will start with a basic Data Pump session to perform a simple logical backup. The command expdp has the following arguments:
Initially we define a Dumpfile(A) . As it will be managed by means of a database directory object(B) it is not necessary to define the path where the dump file will be stored. Remember, the directory objects were previously defined at the database level. This session will export a user's Schema(C) . No other parameters are defined at the command prompt and the session begins.
It can be seen from the command output that an estimation(D) takes place; this estimates the size of the file at the file system, and as no other option for the estimation was defined at the command line it is assumed the BLOCKS method will be used. The estimation by means of the BLOCKS method isn't always accurate, as it depends on the blocks sampled. Block density is a meaningful error factor for this estimation, it is better to use STATISTICS as the estimation method.
At the output log, the Master table(F) where the job running information is temporarily stored can be seen. The job name takes a default name(E) . It is a good practice to define the job name and not let Oracle define it at execution time, if a DBA names the Job, it will be easier to reference it at a later time.
Data Pump export filtering operations
At the command line, filtering options can be specified. In this example, it is used to define the tables to export, but we can also specify whether the dump file will (or will not) include all other dependent objects.
The include (A) and exclude options are mutually exclusive, and in this case as include was declared at the command line and it requires special characters, those must be escaped so the OS doesn't try to interpret them. When a longer include or exclude option is required, it is better to use a parameter file, where the escape characters are not required.
All the filtered objects (C) to be exported were saved in the dump file along with their dependent objects (B). If you change the command line with the following, it will prevent all the indexes being exported:
As can be seen, the exclude or include clause is actually a where predicate.
Use of parameter file
Using a parameter file simplifies an otherwise complex to write command line, it also allows the user to define a library of repeatable operations, even for simple exports. As previously seen, if a filtering (object or row) clause is used—some extra operating system escape characters are required. By writing the filtering clauses inside a parameter file, the command line can be greatly simplified.
Comparing this command line (A) against the previously exposed command lines, it can be seen that it is more readable and manageable. The SHParFile.dpp file from the example contains these command options:
The parameter file is a plain text format file. You may use your own naming conventions. Oracle regularly uses .par for the parameter files, in this case it used .dpp to denote a Data Pump parameter file. The file name can be dynamically defined using environment variables, but this file name formatting is beyond the scope of Oracle and it exclusively depends on the OS variable management.
JOBNAME (C) is the option to specify a non-default job name, otherwise oracle will use a name for it. It is good practice to have the job name explicitly defined so the user can ATTACH to it at a later time, and related objects such as the Master table (B) can be more easily identified.
Retrieve original data
In some circumstances, it may be useful to export the image of a table the way it existed before a change was committed. If the database is properly configured, the database flashback query facility—also integrated with dpexp—may be used. It is useful for obtaining a consistent exported table image.
In this example a copy of the original HR.EMPLOYEES table is made (HR.BAK_EMPLOYEES), and all the tasks will update the BAK_EMPLOYEES table contents. A Restore Point is created so that you can easily find out the exact time stamp when this change took place:
This is the way data was, at the referred SCN. This number will be used later, to perform the expdp operation and retrieve data as it was, at this point in time.
Next a non-reversible update on the data takes place.
Here we have a time reference and the goal is to restore data as it was.
Below are the contents of the data pump parameter file used to retrieve data.
The parameter FLASHBACK_SCN states the point in time from when the table is to be retrieved.
Once the backup is taken, the current table is dropped. When the import takes place it rebuilds the table with the data, as it was before. The import parameter file has been temporarily modified so it defines the log file name, and it includes only the minimum required parameters for the impdp task (C).
Once the import job is finished, a query to the current table shows the data 'as it was', prior to the update command.
Data Pump export space estimation
Proactively estimating the amount of space required by an export file prevents physical disk space shortages. Data Pump has two methods to estimate the space requirements: Estimation by block sampling (BLOCKS) or estimation by object statistics (STATISTICS).
The second method leads to more accurate results and can be performed in a more efficient way than the BLOCKS method; this method requires reliable table statistics.
It can be seen from an export execution, that space estimation is always carried out, and the default estimation method is BLOCKS. The BLOCKS method is used by default as data blocks will always be present at the table, while the presence of reliable statistics cannot be taken for granted. From performance and accuracy perspectives it is not the best choice. It takes longer to read through the whole table, scanning the data block to estimate the space required by the dump file. This method may not be accurate as it depends on the block data distribution. This means that it assumes all block data is evenly distributed throughout all the blocks, which may not be true in every case, leading to inaccurate results. If the STATISTICS keyword is used, it is faster; it only has to estimate the file size from the information already gathered by the statistics analysis processes.
Taking the export of the SH schema with the ESTIMATE_ONLY option and the option BLOCKS, the estimation may not be as accurate as the STATISTICS method. As these test results shows:
ESTIMATE_ONLY
Reported Estimated Dump File Size
BLOCKS
15.12 MB
STATISTICS
25.52 MB
ACTUAL FILE SIZE
29.98 MB
From the above results, it can be seen how important it is to have reliable statistics at the database tables, so any estimation performed by data pump can be as accurate as possible.
Dump file multiplexing
Data Pump export is an exporting method that is faster than the old exp utility. Export speed can between 15 and 45 times faster than the conventional export utility. This is because the original export utility uses only conventional mode inserts, whereas Data Pump export uses the direct path method of loading, but in order for it to reach the maximum possible speed it is important to perform the parallel operations on spindles other than those where the database is located. There should be enough I/O bandwidth for the export operation to take advantage of the dump file multiplexing feature.
The options used to generate an export dump in parallel with multiplexed dump files are:
Notice the %u flag, which will append a two digit suffix to the Data Pump file. These options will direct export data pump to generate four dump files which will be accessed in a round robin fashion, so they get uniformly filled.
The resulting export dump files are:
Transporting data among different versions
Data Pump allows data transfers among different Oracle versions that support the feature. (Note the feature was introduced in Oracle Database 10g Release. 1). The database must be configured for compatibility of 9.2.0 or higher. This feature simplifies data transfer tasks. In order for this to work it is important to consider the source version versus the destination version. It works in an ascending compatible mode, so a Data Pump export taken from a lower release can always be read by the higher release, but an export taken from a higher release must be taken with the VERSION parameter declaring the compatibility mode. This parameter can either take the value of COMPATIBLE (default) which equals the compatible instance parameter value, LATEST, which equals the metadata version or any valid database version greater than 9.2.0. This last statement doesn't mean Data Pump can be imported on a 9.2.0 database. Rather, it stands for the recently migrated 10g databases which still hold the compatible instance parameter value set to 9.2.0.
If the COMPATIBLE parameter is not declared an export taken from a higher release won't be read by a lower release and a run time error will be displayed.
When performing data transfers among different database versions, you should be aware of the Data Pump compatibility matrix:
Data Pump client and server compatibility:
expdp and impdp client version
10.1.0.X
10.2.0.X
11.1.0.X
10.1.0.X
Supported
Supported
Supported
10.2.0.X
NO
Supported
Supported
11.1.0.X
NO
NO
Supported
Each Oracle version produces a different Data Pump file version, when performing expdp/impdp operations using different Data Pump file versions you should be aware of the file version compatibility.
Version Data Pump Dumpfile Set
Written by database with compatibility
Can be imported into Target
10.1.0.X
10.2.0.X
11.1.0.X
0.1
10.1.X
Supported
Supported
Supported
1.1
10.2.X
No
Supported
Supported
2.1
11.1.X
No
No
Supported
Data Pump export interactive mode
Data Pump is meant to work as a batch utility, but it also has a prompt mode, which is known as the interactive mode. It should be emphasized that the data pump interactive mode is conceptually different from the old interactive export/import mode. In this release, the interactive mode doesn't interfere with the currently running job, it is used to control some parameter of the running job, such as the degree of parallelism, kill the running job, or resume job execution in case of a temporary stop due to lack of disk space.
In order for the user to ATTACH to a running job in interactive mode, the user must issue the Ctrl-C keystroke sequence from an attached client. If the user is running on a terminal different from the one where the job is running, it is still possible to attach to the running job by means of the explicit ATTACH parameter. It is because of this feature that it is useful to not let Oracle define the job name.
Once attached there are several commands that can be issued from the open Data Pump prompt:
Command
Description (Default)
CONTINUE_CLIENT
Return to logging mode. Job will be re-started if idle
EXIT_CLIENT
Quit client session and leave the job running
HELP
Summarize interactive commands
KILL_JOB
Detach and delete job
PARALLEL
Change the number of active workers for current job PARALLEL=
START_JOB
Start/resume current job. START_JOB=SKIP_CURRENT will start the job after skipping any action which was in progress when job was stopped
STATUS
Frequency (seconds) job status is to be monitored where the default (0) will show new status when available STATUS=[interval]
STOP_JOB
Orderly shutdown of job execution and exits the client. STOP_JOB=IMMEDIATE performs an immediate shutdown of the Data Pump job
In this scenario the expdp Command Line Interface (CLI) is accessed to manage a running job. First a simple session is started using the command:
The JOB_NAME parameter provides a means to quickly identify the running job.
Once the job is running on a second OS session a new expdp command instance is started, this time using the ATTACH command. This will open a prompt that will allow the user to manage the running job.
After showing the job status it enters the prompt mode where the user can issue the previously listed commands.
In this case a STOP_JOB command has been issued. This notifies the running session that the command execution has been stopped, the job output is stopped and the OS prompt is displayed. After a while the user reattaches to the running job, this time the START_JOB command is issued, this resumes the job activity, but as the expdp session was exited no more command output is displayed. The only way the user can realize the job is running is by querying the DBA_DATAPUMP_JOBS view or by browsing the log file contents.
Tip
The ATTACH command does not require the job name if there is only a single JOB running. If there is more than one concurrent job running then the user must specify the job name.
Data Pump restart capability
In case of failure or any other circumstances that prevent the Data Pump job from successfully ending its work, an implicit recommencing feature is activated. The job enters a suspended mode that allows the DBA to attach this feature to the job. It is important to emphasize that the master job table must positively identify the interrupted job, otherwise it won't be possible to restart the job once the circumstance behind the failure has been properly corrected.
In order for the user to attach to the job, it must be connected with the ATTACH command line option properly set. At this point, it becomes evident why it is a good practice to have a name for the data pump job, other than the default system generated name.
Getting information about the export job
When a Data Pump task takes place, it can be monitored to find out if everything is running fine with it. A view named DBA_DATAPUMP_JOBS can be queried to check the task status.
In this query it can be seen that a FULL(C) EXPORT(B) data pump job named SYS_EXPORT_FULL_01(A) is in Executing State(D) . It is executing with a default parallel degree of 1(E) . In case of trouble, the status changes and it would be time to work with the CLI mode to ATTACH to the job and take corrective action.
Data Pump import
Data Pump import (impdp) is the tool used to perform the data import operation, it reads the data from a file created by Data Pump export. This tool can work in different modes such as:
The Data Pump import tool provides three different interfaces:
