Oracle Database Utilities: Full TransportableExport/ImportORACLE WHITE PAPER FEBRUARY 2018

DisclaimerThe following is intended to outline our general product direction. It is intended for informationpurposes only, and may not be incorporated into any contract. It is not a commitment to deliver anymaterial, code, or functionality, and should not be relied upon in making purchasing decisions. Thedevelopment, release, and timing of any features or functionality described for Oracle’s productsremains at the sole discretion of Oracle.ORACLE DATABASE UTILITIES: FULL TRANSPORTABLE EXPORT/IMPORT

Disclaimer1Introduction1Benefits of Using Full Transportable Export/Import2Full Transportable Export/Import Support for Pluggable Databases3Internals of Full Transportable Export/Import3Example: Using Full Transportable to Move a Non-CDB into a CDB5Conclusion8Appendix: Limitations on Full Transportable Export/Import8ORACLE DATABASE UTILITIES: FULL TRANSPORTABLE EXPORT/IMPORT

IntroductionOracle Database includes significant features such as the Oracle Multitenant Option. This multitenantarchitecture includes the ability to create pluggable databases (PDBs), a way to consolidate multipledatabases into a single multitenant container database (CDB). Customers can save on databasemanagement costs, achieve more efficient hardware utilization, and realize economies of scale bymigrating to a consolidated database environment using pluggable databases.Traditional methods used for database migration work seamlessly with pluggable databases. Theoriginal Oracle Export/Import, Oracle Data Pump export/import, and transportable tablespaces can allbe used to migrate from earlier versions of Oracle Database to a pluggable Oracle Database. Inaddition, the feature, full transportable export/import, is available to make migration to OracleDatabase 12c and higher faster, easier, and more efficient than ever before.This white paper describes the full transportable export/import feature in Oracle Database. After givingan overview of the benefits of using full transportable export/import, it explains how the feature worksand provides a detailed example of full transportable export/import to show the syntax and processflow of using this feature. Note that, while this white paper focuses on the use of full transportableexport/import in a pluggable database environment, this feature can be used generally for migrationsto a Oracle Database 12c non-CDB database as well.1 ORACLE DATABASE UTILITIES: FULL TRANSPORTABLE EXPORT/IMPORT

Benefits of Using Full Transportable Export/ImportFull transportable export/import combines the ease of use familiar to users of original Export/Import and Data Pumpexport/import, with the speed of migration possible with transportable tablespaces. A recap of these three migrationtechniques, and a comparison and contrast with full transportable export/import, helps show why full transportableexport/import is faster, easier, and more efficient than previously available migration methods.Full transportable export is available starting with Oracle Database 11g Release 2 ( Full transportableimport is available starting with Oracle Database 12c.Original Export/Import is generally the slowest method for performing database migrations. While it has beenavailable for many years, and has been a mainstay utility for many DBAs, original Export/Import was not designedwith large (100 GB) databases in mind. Its performance does not scale to the data volume of modern IT systems.Further, original Export/Import has not been enhanced to support new database functionality added in OracleDatabase 10g and later releases. Therefore, while original Export/Import is a known and reliable migrationtechnique, it should be used only for Oracle 9i and earlier databases.Oracle Data Pump was introduced in Oracle Database 10g, and is designed to handle large volumes of data.Employing techniques such as parallel worker processes, choosing the best access method for the data beingmoved, and offering flexible and powerful exclude/include capabilities, Oracle Data Pump has effectively replacedoriginal Export/Import as the most common way to move data between Oracle databases. The command line forOracle Data Pump, while not identical to that of original Export/Import, offers a familiar feel to DBAs and is generallyconsidered very easy to use. Even with all these benefits, however, there can be cases where Data Pump isovertaken in terms of performance by transportable tablespaces. When the data being moved is very large (morethan a few hundred gigabytes), or when there are large volumes of indexes to be moved, Data Pump is fast butother techniques may be even faster.Transportable tablespaces are usually the fastest fast way to move user and application data between databases,because tablespace data files are moved en masse from the source database to the target. Moving an entire datafile is generally much faster than exporting and importing individual rows or even blocks of data. However, traditionaltransportable tablespaces can require a fairly complicated set of steps to move user and application metadataneeded to effectively use these tablespace data files in the destination database. A migration using transportabletablespaces can therefore be characterized as being very fast but more complex.As of Oracle Database 12c, the best features of speed and usability are combined in full transportable export/import.The command line for full transportable export/import is that of Oracle Data Pump. Full transportable export/importcan take advantage of Data Pump options such as the ability to move metadata over a database link, and is able toaccomplish a full database migration with a single import command.At the same time, full transportable export/import uses the transportable tablespaces mechanism to move user andapplication data. This results in a migration that is very fast, even for very large volumes of data. Most important, fulltransportable export/import moves all of the system, user, and application metadata needed for a databasemigration, without the complex set of steps required for a traditional transportable tablespaces operation.Thus, full transportable export/import combines the ease of use of Oracle Data Pump with the performance oftransportable tablespaces, resulting in a feature that makes database migration faster and easier.2 ORACLE DATABASE UTILITIES: FULL TRANSPORTABLE EXPORT/IMPORT

Full Transportable Export/Import Support for Pluggable DatabasesFull transportable export/import was designed with pluggable databases as a migration destination. You can use fulltransportable export/import to migrate from a non-CDB database into a PDB, from one PDB to another PDB, or froma PDB to a non-CDB. Pluggable databases act exactly like non-CDBs when importing and exporting both data andmetadata.The steps for migrating from a non-CDB into a pluggable database are as follows:» Create a new PDB in the destination CDB using the create pluggable database command» Set the user and application tablespaces in the source database to be READ ONLY» Copy the tablespace data files to the destination» Using an account that has the DATAPUMP IMP FULL DATABASE privilege, either» Export from the source database using expdp with the FULL Y TRANSPORTABLE ALWAYS options, andimport into the target database using impdp, or» Import over a database link from the source to the target using impdp» Perform post-migration validation or testing according your normal practiceInternals of Full Transportable Export/ImportWhile not intended to be an in depth technological treatise, this section describes the mechanisms behind fulltransportable export/import. The intent of this section is to give you a better understanding of what full transportableexport/import does, and how it achieves the optimal combination of usability and performance.Full Transportable Export/Import ConceptsTo understand the internals of full transportable export/import, you need to know the difference between movingdata in a conventional manner versus a transportable approach, and the distinction between administrative and usertablespaces.When using conventional methods to move data, Oracle Data Pump uses either external tables or direct path unloadto extract data. While the choice between these two access methods is based on the structure and types of the databeing unloaded, both methods efficiently extract logical subsets of data from an Oracle database.In contrast, a transportable move of data and indexes involves the physical movement of one or more tablespacedata files. The data segments inside the tablespace data files are not read individually. Instead, the export operationextracts the metadata that describes the objects containing storage within each data file, and each file is moved as asingle entity. Moving large volumes of data using transportable tablespaces can be faster than conventional datamovement because there is no need to interpret and extract individual rows of data or index entries. It is possible tomove individual tables or partitions in a transportable manner, but the entire tablespace data file is moved in thesecases as well.Understanding the difference between conventional and transportable data movement is helpful when consideringthe distinction between administrative and user tablespaces. For the purposes of a full transportable export,administrative tablespaces are the tablespaces provided by Oracle, such as SYSTEM, SYSAUX, TEMP, andUNDO. These tablespaces contain the procedures, packages, and seed data for the core Oracle databasefunctionality and Oracle-provided database components such as Oracle Spatial, Oracle Text, OLAP, JAVAVM, andXML Database. In contrast, user tablespaces are those tablespaces defined by database users or applications.These may store user data, application data, and any other information defined by users of the database.3 ORACLE DATABASE UTILITIES: FULL TRANSPORTABLE EXPORT/IMPORT

The first step of a full transportable export is to create the destination database. This newly created databaseincludes a set of administrative tablespaces appropriate to the target environment, complete with Oracle-suppliedcomponents and packages. Starting with Oracle Database 12c, Oracle-supplied objects are neither exported norimported by Oracle Data Pump. Thus, when migrating data from the administrative tablespaces of the sourcedatabase, full transportable export uses conventional data movement to extract only the data and metadata for userdefined objects that will be need to be added to the destination database.The user tablespaces, on the other hand, are moved to the destination database as full tablespace data files, underthe assumption that a full export migrates all user and application data and metadata from the source to thedestination system. User tablespaces are thus moved transportably, resulting in maximum performance whenmigrating user data.One consideration specific to full transportable export/import arises when there exists a database object (e.g., apartitioned table) that is stored across both user and administrative tablespaces. Storing an object in this way isgenerally not good practice, but it is possible. If there is an object with storage in both administrative and usertablespaces, then you can either redefine that object before transporting your data, or use conventional Data Pumpexport/import. The example later in this white paper shows how to detect this condition prior to starting a fulltransportable export.Full Transportable ExportThe key to the improved usability of full transportable export/import is that the export is able to extract the metadatafor all user and administrative objects. A callout mechanism and API is provided for internal use by Oraclecomponents, allowing for a complete extract of the metadata needed to create a full copy of the database uponimport.All user and application objects that reside in administrative tablespaces are unloaded conventionally, including boththeir metadata (e.g. a table definition) and their data (e.g. the rows in that table). In contrast, objects stored in usertablespaces have only their metadata unloaded by Data Pump; the data for those objects is moved transportably inthe tablespace data files.Invoking Full Transportable ExportOracle Data Pump export (expdp) is the command line interface for full transportable export. You can initiate a fulltransportable export by specifying two parameters in the parameter file or on the command line:TRANSPORTABLE ALWAYS and FULL Y. These parameter values tell Data Pump to use full transportable ratherthan conventional export methods.In addition, there is a special consideration if the COMPATIBLE database initialization parameter of the sourcedatabase is not set to a value of at least 12.0. This would be true, for example, if you perform a full transportableexport from Oracle Database 11g Release 2 ( In this case, you must also specify the Data Pumpparameter VERSION 12 to denote the fact that the result of the export will be imported into an Oracle Database 12cRelease 1 (12.1) or later database.Full transportable ImportLike a conventional Data Pump import, full transportable import can be used to import a dump file or to importdirectly from a source database into a destination database over a database link. The ability to import without usinga dump file makes full transportable import an indispensable tool for database migrations.4 ORACLE DATABASE UTILITIES: FULL TRANSPORTABLE EXPORT/IMPORT

Invoking Full Transportable ImportIf you are importing a dump file, Oracle Data Pump is able to determine whether a dump file was produced by aconventional or full transportable export. A file-based full transportable import thus requires only that you specify thedumpfile name and the list of user tablespace data files to be transported using theTRANSPORT DAT