Upgrade andMigrate toOracleDatabase 19cHow to choose and use the best available method to upgrade ormigrate your current Oracle database to Oracle Database 19c.ORACLE WHITE PAPER / MARCH 6, 2019

PURPOSE STATEMENTThis document provides an overview of upgrade and migration methods available for OracleDatabase 19c. It is intended solely to help you assess the business benefits of upgrading to OracleDatabase 19c and to plan your IT projects.DISCLAIMERThis document in any form, software or printed matter, contains proprietary information that is theexclusive property of Oracle. Your access to and use of this confidential material is subject to theterms and conditions of your Oracle software license and service agreement, which has beenexecuted and with which you agree to comply. This document and information contained herein maynot be disclosed, copied, reproduced or distributed to anyone outside Oracle without prior writtenconsent of Oracle. This document is not part of your license agreement nor can it be incorporatedinto any contractual agreement with Oracle or its subsidiaries or affiliates.This document is for informational purposes only and is intended solely to assist you in planning forthe implementation and upgrade of the product features described. It is not a commitment to deliverany material, code, or functionality, and should not be relied upon in making purchasing decisions.The development, release, and timing of any features or functionality described in this documentremains at the sole discretion of Oracle.Due to the nature of the product architecture, it may not be possible to safely include all featuresdescribed in this document without risking significant destabilization of the code.2WHITE PAPER / Upgrade and Migrate to Oracle Database 19c

TABLE OF CONTENTSPurpose Statement . 2Introduction . 4Comparing upgrade and migration. 4Database Upgrade. 4Database Migration. 4Multitenant Architecture . 5Selecting a Database Upgrade or Migration Method . 5Direct Upgrade to Oracle Database 19c. 6Detailed Upgrade and Migration Method Descriptions. 7Method 1: DBUA or Command-Line Upgrades . 7Method 2: Full Transportable Export/Import or Transportable Tablespaces . 11Method 3: Oracle Data Pump Export/Import. 14Conclusion . 16Appendix A: Upgrade & Migration New Features in Oracle Database 19cError! Bookmark notdefined.3WHITE PAPER / Upgrade and Migrate to Oracle Database 19c

INTRODUCTIONOracle Database 19c includes new features and enhancements, as well as a long-termsupport commitment, that make it an attractive upgrade target for existing Oracle databases.Moving to Oracle Database 19c may be part of an effort that includes moving to newlypurchased server hardware, migrating to different storage architectures, such as OracleAutomatic Storage Management, changing the database character set, or migrating to acompletely different operating system. Increasingly, upgrading to a new version of OracleDatabase may also include migrating to the Oracle Cloud.Because upgrade and migration scenarios can differ in many ways, Oracle provides multiplemethods for you to upgrade and migrate your databases to Oracle Database 19c. Thistechnical white paper outlines these upgrade and migration methods. You will learn aboutdifferent use cases and key factors to consider when choosing the method that best fits yourspecific requirements.COMPARING UPGRADE AND MIGRATIONAlthough the terms are often used as synonyms in other contexts, in the context of Oracle Databasethere is a necessary distinction between database upgrade and database migration. Understanding thisdifference is the first step in choosing the best upgrade or migration method for your project.Note: The term “migration” can also be used when discussing the move of data from a non-Oracledatabase into Oracle. This white paper will cover migrations only when both the source and destinationare Oracle databases.Database UpgradeThe act of upgrading an Oracle Database involves modifying the data dictionary to be compatible with anewer version of Oracle Database software. Typical actions that may be part of a database upgradeinclude:» Adding, dropping, or modifying columns in system tables and views» Creating or modifying system packages or procedures» Creating, modifying, or dropping database types, users, roles, and privileges» Modifying seed data that is used by Oracle Database componentsAll of these actions affect the data dictionary of your database. They do not affect the data stored inyour user or application tablespaces. Therefore, the sheer volume of data stored in your Oracledatabase has little or no bearing on a database upgrade.Database MigrationThe term “migration” applies to several different types of changes that can be applied to an Oracledatabase. In addition to database version, these can include a change to any or all of the following:» Computer server (hardware or virtualized environment)» Storage architecture4WHITE PAPER / Upgrade and Migrate to Oracle Database 19c

» Character set» Operating system» Schema topology (changing the partitioning scheme)» Encryption» Compression» Database architecture (moving into the multitenant database architecture)Database migration differs from database upgrade in two important ways. First, database migrationgenerally involves moving or modifying the user and application data in the database. This means thatthe size of your database has a dramatic impact on your database migration project. Second, any of theabove migrations can be performed on a database without upgrading it to a new version. This whitepaper will discuss techniques that can be used to perform both an upgrade to Oracle Database 19c anda migration simultaneously.Multitenant ArchitectureOracle Database 12c introduced a new multitenant architecture more than six years ago. It enables anOracle database to contain a portable collection of schemas, schema objects, and nonschema objectsthat appears to an Oracle Net client as a separate database. This self-contained collection is called apluggable database (PDB). A multitenant containerdatabase (CDB) is an Oracle database that includeszero, one, or many user-created PDBs. This newarchitecture enables customers to easily consolidatemultiple databases and introduces another veryimportant type of database migration for customersto consider: migration to a PDB.In some cases, migrating from the traditionaldatabase architecture (also called a non-CDB) to aPDB can be combined with other database upgradeor migration tasks into a single operation. In othercases, migration into a PDB may involve anadditional step. The steps for migrating an existing ornon-CDB database to a PDB will be described undereach upgrade or migration technique in this whitepaper.SELECTING A DATABASE UPGRADE OR MIGRATION METHODWith several upgrade and migration approaches available, choosing the best upgrade or migrationmethod for a particular project requires an analysis of several important project characteristics. Each ofthese characteristics can influence the suitability of a given method when you upgrade or migrate toOracle Database 19c:» The version from which you are upgrading or migrating, including the patch set level» The source and destination operating system and version» The source and destination hardware platforms and their endian characteristics» Any plans to change the actual data layout or format, such as changing the character set, partitioning,encryption, or compression5WHITE PAPER / Upgrade and Migrate to Oracle Database 19c

» Availability requirements including the amount of downtime allowed for the upgrade or migrationproject, fallback possibilities and disaster recovery» The size of the database to be migrated» The source and target database architecture, whether non-CDB or PDBThere is no single upgrade or migration method that is the best option for all possible upgrade andmigration scenarios. However, there is a method that is best for any particular scenario based on thepreviously mentioned factors.Direct Upgrade to Oracle Database 19cA direct upgrade is one where either the Database Upgrade Assistant (DBUA) or command-lineupgrade script is used to upgrade your database to Oracle Database 19c. Direct upgrade is supportedwhen the source database is running one of the releases shown in the following table.TABLE 1. DIRECT UPGRADE PATHS FOR ORACLE DATABASE 19CSOURCE RELEASESOURCE PATCH SET ORRELEASEOracle Database 12c Release 2Oracle Database 12cOracle Database 11g Release 2Oracle Database 11g Release 1 andearlierDirect 0.1No. Use another method11.2.0.4Yes11.2.0.1,, Use another methodAllNo. Use another methodWhen direct upgrade is not supported, other methods of moving to Oracle Database 19c will apply.These methods are described in the following section.UPGRADE AND MIGRATION METHODSThere are four upgrade and migration methods described in this white paper, and three of thesemethods have variants that can be used in particular situations.The methods are:1. Database Upgrade, using either the command-line upgrade with dbupgrade or the newAutoupgrade, or DBUA2. Transportable tablespaces (TTS) export and import, using the Oracle Database feature fulltransportable export/import, or the traditional TTS mode3. Oracle Data Pump Export/Import, using either dump files or network mode6WHITE PAPER / Upgrade and Migrate to Oracle Database 19c

The following table summarizes the applicability of these upgrade and migration methods for yourscenario, based on the project characteristics listed in the previous section.TABLE 2. DATABASE UPGRADE AND VersionMoveto NewServerChangeO/SChange DataLayout, characterset, encryption,compressionUnplug, Plug radeMedFastest11.2.0.4YesNoNoDatabase UpgradeAssistantLowFastest11.2.0.4NoNoNoFull oTransportableTablespacesHighFaster8.1.5YesYes, startingwith 10.1NoData Pumpexpdp/impdpMedFast10.1YesYesYesDETAILED UPGRADE AND MIGRATION METHOD DESCRIPTIONSMethod 1: DBUA or Command-Line UpgradesThe easiest way to move up to Oracle Database 19c is to upgrade your existing database using eitherDBUA or the command-line upgrade scripts. These are two variants of the same method, differingprimarily in their user interfaces. Because these methods act on your database without creating a copyor a new instance, this is sometimes referred to as upgrading “in place.”DATABASE UPGRADE ASSISTANTDatabase Upgrade Assistant is a graphical user interface (GUI) that guides you through the databaseupgrade process and presents a series of screens that allow you to specify options for your databaseupgrade. During the upgrade process, DBUA invokes the same scripts used for the command-lineupgrade. It also performs pre-upgrade validation steps and can automate post-upgrade tasks. UsingDBUA can significantly reduce the amount of manual effort required for a database upgrade but offersless flexibility than the command-line upgrade. DBUA is fully described in Oracle Database UpgradeGuide.Example of Using Database Upgrade AssistantAs a GUI-based utility, DBUA is very easy to use because it allows you to select options for yourdatabase upgrade using dialog boxes, such as this example from the DBUA workflow:7WHITE PAPER / Upgrade and Migrate to Oracle Database 19c

Figure 1. Screenshot of DBUA Prerequisite Checks PageIn this screenshot, the steps in the workflow are listed in the left-hand pane of the window, while theprerequisite checks run by DBUA are in the right-hand pane.Considerations for Using Database Upgrade AssistantIn general, DBUA can only be used when upgrading your Oracle database on its current hardwaresystem. This is because DBUA performs the pre-upgrade validation steps using the source Oraclehome, and then switches to the destination Oracle home to perform the upgrade and post-upgradesteps.Note: If you would like to use DBUA when migrating to new hardware, then you will first need to installthe source version Oracle Home on the new system, in addition to the target version Oracle Home.DBUA does not give the user any control over the degree of parallelism used to upgrade the database.While DBUA does automate many of the pre-upgrade tasks identified in the database pre-upgradescripts, there are some actions which may still require manual intervention. One such example is shownin the screenshot above.COMMAND-LINE UPGRADEOracle Database 19c uses the command-line upgrade utility (dbupgrade) The command-line upgradeutility enables parallel processing during the database upgrade, resulting in better upgrade performanceand reduced database downtime compared to previous releases.8WHITE PAPER / Upgrade and Migrate to Oracle Database 19c

Command-line upgrades follow the same steps and take the same amount of time as upgrading withDBUA. They are most commonly used by database administrators (DBAs) who desire more directcontrol, or in situations where a database is being moved to a new hardware server in conjunction withtheir database upgrade.Starting with Oracle Database 19c, the Pre-Upgrade Information Tool (preupgrd.jar) automaticallygenerates fixup scripts to address common issues that may prevent an upgrade from being successful.The post-upgrade phase has also been enhanced to automate the running of many post-upgrade steps.Example of Using Command-line UpgradeThe process of upgrading your Oracle database using command-line upgrade can be broken into threephases, each of which has a small number of steps to follow for a successful upgrade. Starting withOracle Database 19c, you can then plug your database into a CDB as a PDB:» Pre-upgrade Phase1.2.3.Run the new Pre-Upgrade Information Tool (preupgrade.jar), which validates the readinessof your database to be upgradedRun the preupgrade fixups.sql script to automatically address issues found by the PreUpgrade Information ToolPerform any manual fixup steps identified by the Pre-Upgrade Information Tool» Upgrade Phase1.Run the Parallel Upgrade Utility (dbupgrade)» Post-upgrade Phase1. Run the postupgrade fixups.sql script to automatically fix any issues identified by the PreUpgrade Information Tool, which need to be addressed after the upgrade.2.3.Review the log files generated by the Parallel Upgrade UtilityRecompile invalid objects by running utlrp.sql» Plug into a CDB1.2.3.Start the database READ ONLY and use the DBMS PDB.DESCRIBE() procedure to generatean XML descriptor file for your databaseConnect to the CDB and issue the CREATE PLUGGABLE DATABASE command to migrate yournon-CDB database into a PDBRun the noncdb to pdb.sql script to convert the non-CDB into a fully usable PDBIf your source database contains either encrypted tablespaces or tables with encrypted columns, thenthe keys associated with those tablespaces or tables must be moved from the source database into thenew PDB using the export and import functions of the Oracle Database ADMINISTER KEYMANAGEMENT SQL statement.These steps are an outline of the command-line upgrade process. Full details of command-line upgradeare explained in Oracle Database Upgrade Guide.Considerations for Using Command-line UpgradeMany DBAs prefer the level of control that they get from typing commands at the command line,compared to using a GUI such as DBUA. Beyond personal preference, a common use case forcommand-line upgrade is when you are migrating to new hardware but staying on the same operatingsystem architecture.Unlike DBUA, command-line upgrade allows the user to specify the number of parallel processes usedfor the database upgrade, thereby giving you the ability to balance upgrade speed and system resourceutilization.9WHITE PAPER / Upgrade and Migrate to Oracle Database 19c

Also unlike DBUA, the command-line upgrade can automatically detect the point at which it wasinterrupted, and restart the upgrade, using the ‘-R’ parameter. If command-line upgrade is interruptedfor any reason, you can rerun or restart the Parallel Upgrade Utility as described in Oracle DatabaseUpgrade Guide.PLUGGING A NON-CDB DATABASE INTO A CDBAfter upgrading to Oracle Database 19c, you can plug the database into a CDB as a PDB. Note that thesource database and destination CDB must meet the following requirements:» The hardware and operating system platforms must have the same endianness.» They must have compatible database options installed.» They must have compatible character sets and national character sets. The means that either:» The character set in the CDB is AL32UTF8, or.» The character set in the non-CDB is the same as the character set in the CDB, or» The character set in the non-CDB is a binary subset of the character set in the CDB.The procedure for creating a PDB from a non-CDB is described in Oracle Database Administrator’sGuide. Oracle recommends using the AL32UTF8 character set for CDBs.UPGRADING A CDB WITH MULTIPLE PDBSIf you are upgrading an Oracle Database 19c container database, you can take advantage of theflexibility of the CDB architecture to approach the upgrade in one of two ways:» Upgrade the CDB and all PDBs with a single command» Upgrade one PDB, or a subset of the PDBs, at a time1.Create a new CDB using the new version of Oracle Database software2.Unplug one or more PDBs from the source CDB and plug them into the new CDB3.Upgrade the older PDB(s) using either DBUA or the command-line upgradeThere are tradeoffs with each technique. Some considerations for choosing an upgrade method for aCDB are as follows.“All At Once” UpgradeUnplug/Plug/Upgrade» Less effort: upgrade up to 252 PDBs with asingle command» More flexible: allows you to plan migrationwindows based on user needs» Upgrade automation reduces overalldowntime for the upgrade process» Retain original database SID for the CDB» Easier migration to a new server» Downtime for individual PDB will beshorterThe choice of an upgrade method for a CDB thus depends on your use case and upgrade or migrationscenario, and methods can be combined. For example, a single PDB could be unplugged, pluggedinto a new CDB and upgraded while the rest of the PDBs are kept at the source version. Then theoriginal CDB could be upgraded all at once, and the first (upgraded) PDB moved back to the originalCDB using unplug and plug.10WHITE PAPER / Upgrade and Migrate to Oracle Database 19c

Method 2: Full Transportable Export/Import or Transportable TablespacesTransportable tablespaces allows you to copy a set of tablespaces from one database to another. Thiscan be much faster than exporting and importing data from those tablespaces because the tablespacesare copied as physical files without the need to interpret the logical entities, such as rows or indexes,contained within those files. However, in addition to copying the tablespaces, metadata describing theobjects within the source database must be moved to the new database by Data Pump export/import.Transported tablespaces can be copied to another database that may be on a different operatingsystem platform or running a different version of Oracle Database software. This makes transportabletablespaces a relatively fast way to migrate and upgrade a database in a single operation. In the past,the tradeoff for this speed is that transportable tablespaces could be fairly complicated, because theuser was responsible for moving metadata such as procedures, packages, constraints, and so on.Starting with Oracle Database 19c, the full transportable export/import feature combines the speed oftransportable tablespaces with a much easier process for transporting metadata and data from installeddatabase options.FULL TRANSPORTABLE EXPORT/IMPORTFull transportable export/import is a feature of Oracle Database that makes it easy to move an entiredatabase using the transportable tablespaces feature. It automates the process of moving metadataand can move user data that resides in non-transportable tablespaces such as SYSAUX and SYSTEM.In addition, full transportable export/import can transport encrypted tablespaces.Full transportable export/import moves metadata using either dump files or a database link. Thiscombination of speed and simplicity makes full transportable export/import a good choice for manymigration scenarios. The detailed steps for using full transportable export/import are described in theOracle white paper, “Oracle Database 12c: Full Transportable Export/Import.” The overall process forfull transportable export/import has not changed for Oracle Database 19c.Migrating to a Pluggable Database Using Full Transportable Export/ImportBecause full transportable export/import allows you to migrate an entire database to both a newoperating system platform and a new release of Oracle Database in a single operation, it is quite usefulfor migrating to a PDB. The procedure for using full transportable export/import to migrate to a PDB isthe same as for migrating into a non-CDB.Examples of Using Full Transportable Export/ImportThe complete implementation of the full transportable export/import feature is included in OracleDatabase 12c and later releases, including Oracle Database 19c. In addition, export-side support forthis feature is available starting in Oracle Database 11g Release 2 ( these examples, assume that you start with an source database containing the followinguser tablespaces:TABLE 3. SOURCE DATABASE TABLESPACES11TABLESPACE NAMEENCRYPTED?Datafile NameHRYes/data3/oracle/dbs/hr 1.fENGTABLESNo/data4/oracle/dbs/eng 1.fWHITE PAPER / Upgrade and Migrate to Oracle Database 19c

ENGINDEXESNo/data4/oracle/dbs/eng 2.fAll three of these tablespaces, as well as all of the metadata such as procedures, packages,constraints, triggers, and so on, will be moved from the source to the destination in a single fulltransportable export and import sequence.The first example is a dumpfile-based full transportable export/import operation. In this case themetadata from the source database is exported to a dump file, and both the dump file and thetablespace data files are transferred to a new system. The steps would be as follows:1.Set user tablespaces in the source database to READ ONLY.2.From the Oracle Database 11g Release 2 ( environment, export the metadata and anydata residing in administrative tablespaces from the source database using the FULL Y andTRANSPORTABLE ALWAYS parameters. Note that the VERSION 12 parameter is requiredonly when exporting from an Oracle Database 11g Release 2 database:expdp src112admin/ passwd @src112 DIRECTORY src112 dirDUMPFILE src112fulltts.dmp VERSION 12 FULL YTRANSPORTABLE ALWAYSEXCLUDE TABLE STATISTICS,INDEX STATISTICSENCRYPTION PASSWORD enc passwd METRICS YLOGFILE src112fullttsexp.logNote: Oracle recommends the use of a parameter file to specify data pump parameters.3.Copy the tablespace data files from the source system to the destination system using operatingsystem commands. Note that the log file from the export operation will list the data files requiredto be moved.4.Create a CDB on the destination system, including a PDB into which you will import the sourcedatabase. Administration of PDBs is described in Oracle Database Administrator’s Guide.5.In the Oracle Database 12c environment, connect to the PDB and import the dump file. The actof importing the dump file will plug the tablespace data files into the destination PDB:impdp pdbadmin/ passwd @tgtpdb DIRECTORY src112 dirDUMPFILE src112fulltts.dmpENCRYPTION PASSWORD enc passwd METRICS YLOGFILE src112fullttsimp.logTRANSPORT DATAFILES '/recovery1/data/hr 1.f 'TRANSPORT DATAFILES '/recovery1/data/eng 1.f 'TRANSPORT DATAFILES '/recovery1/data/eng 2.f 'The second example of using full transportable export/import employs the network mode of Data Pumpto eliminate the need for a dumpfile. In this case, we will assume that the tablespace data files are in alocation, such as a Storage Area Network (SAN) device, which is accessible to both the source anddestination systems. This enables you to migrate from a non-CDB into a PDB with one Data Pumpcommand:121.Create a CDB on the destination system, including a PDB into which you will import the sourcedatabase.2.Create a database link in the destination PDB, pointing to the source database.WHITE PAPER / Upgrade and Migrate to Oracle Database 19c

3.Set user tablespaces in the source database to READ ONLY.4.In the Oracle Database 19c environment, import directly from the source database into thedestination PDB using full transportable export/import in network mode:impdp pdbadmin/ passwd @tgtpdb NETWORK LINK src112 VERSION 12FULL Y TRANSPORTABLE ALWAYSEXCLUDE TABLE STATISTICS,INDEX STATISTICSENCRYPTION PASSWORD enc passwd METRICS Y LOGFILE tgtpdb dir:src112fullimp.logTRANSPORT DATAFILES '/recovery1/data/hr 1.f 'TRANSPORT DATAFILES '/recovery1/data/eng 1.f 'TRANSPORT DATAFILES '/recovery1/data/eng 2.f 'Whether you use conventional dump files or network mode, full transportable export/import is aconvenient way to upgrade a database to a new version, migrate to a different operating system orhardware platform, migrate into a PDB – or even to perform all three of these upgrades and migrationsin a single operation!Considerations for Using Full Transportable Export/ImportPlease be aware of the following considerations of using full transportable export/import: Full transportable export/import is subject to the limitations detailed in Oracle DatabaseAdministrator’s Guide. Full transportable export/import can be used to migrate source databases starting with OracleDatabase 11g Release 2 ( Full transportable export/import jobs cannot be restarted. If the operation is interrupted, thenthe entire job must start over from the beginning. For complete documentation of full transportable export/import, including details about networkmode migration into a PDB, see Oracle Database Utilities Guide. If hardware and operating system platforms of your source and destination databases havedifferent endian characteristics, you will need to use the RMAN CONVERT command toconvert each tablespace to the new platform. See Oracle Database Backup and RecoveryReference for a description of the RMAN CONVERT command.TRANSPORTABLE TABLESPACESThe transportable tablespaces feature can be used to copy a single tablespace or a set of tablespacesto a new database. While this method of migrating data is both fast and reliable, it requires a number ofmanual steps that can be more complicated than some DBAs would prefer.Migrating to a Pluggable Database Using Transportable TablespacesLike full transportable export/import, transportable tablespaces can be used to migrate to a PDB. Theprocedure for using transportable tablespaces to migrate a database to a PDB is the same as atransportable tablespaces migration to a non-CDB.Example of Using Transportable TablespacesOracle recommends using full transportable export/import to migrate databases starting with OracleDatabase 11g Release 2 ( However, you can still use the transportable tablespaces feature tomigrate databases from earlier releases. The use of the transportable tablespaces feature is describedin detail by the following white papers:» “Database Upgrade Using Transportable Tablespaces”13WHITE PAPER / Upgrade and Migrate to Oracle Database 19c

» “Platform Migration Using Transportable Tablespaces”The steps for using this feature remain unchanged for Oracle Database 19c.Considerations for Using Transportable TablespacesPlease be aware of the following considerations of using transportable tablespaces: Transporting tablespaces between databases is subject to the limitations detailed in OracleDatabase Administrator’s Guide. Transportable tablespace export/import jobs cannot be restarted. If the operation isinterrupted, then the entire job must start over from the beginning. Transportable tablespaces can be used on databases starting with Oracle8i Database. Thisfeature gained the ability to migrate cross-platform starting with Oracle Database 10g( If hardware and operating system platforms of your source and destination databases havedifferent endian characteristics, you will need to use the RMAN CONVERT command toconvert each tablespace to the new platform. See Oracle Database Backup and RecoveryReference for a description of the RMAN CONVERT command.Method 3: Oracle Data Pump Export/ImportOracle Data Pump provides high-speed movement of data and metadata within and between Oracledatabases. Because they are extremely flexible and easy to use, the Oracle Data Pump export (expdp)and import (impdp) utilities are commonly used to migrate tables, schemas, and databases to newhardware servers, to different operating system platforms, and to new releases of Oracle Databasesoftware.Oracle Data Pump can write data to dump files on disk

» Character set » Operating system » Schema topology (changing the partitioning scheme) » Encryption » Compression » Database architecture (moving into the multitenant database architecture) Database migration differs from database upgrade in two important ways. First, database migration generally involves moving