mopdf.com

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 (11.2.0.3).In these examples, assume that you start with an 11.2.0.3 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 (11.2.0.3) 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 (11.2.0.3). 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 (11.2.0.3). 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(10.1.0.3). 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