There are almost as many ways to upgrade your Oracle database to 12c as there are reasons to do so. For a survey of database experts on why you should upgrade to 12c, see our blog post, Why Should You Upgrade to Oracle 12c? For detailed instructions on how to upgrade to 12c — including a sample script from an actual upgrade that took less than an hour — download the new Datavail whitepaper, Upgrading to Oracle 12c. For a quick summary of the tools you can use to execute the upgrade, read on.
Oracle 12c has attractive new features such as Multitenant, Active Data Guard Far Sync, information lifecycle management, among hundreds of other improvements. To take advantage of these features, you must upgrade your Oracle database to 12c — a process that is often combined with new hardware and storage architecture. Oracle provides multiple methods for you to upgrade and migrate your databases to 12c. We’ll try to provide enough information for you to determine which method best fits your organizations needs.
Oracle provides a comprehensive set of tools for upgrading to 12c with minimal downtime and for migrating your applications to the new release. Here are four methods recommended by Oracle for upgrading and migrating your databases to 12c.
Method 1. Upgrading with Oracle Database Update Assistant (DBUA)
Database Upgrade Assistant (DBUA) provides a graphical user interface (GUI) to guide you through the upgrade. DBUA works for CDB and non-CDB database systems.
Oracle recommends that you run the Pre-Upgrade Information Tool before starting an upgrade with DBUA. Although DBUA runs the Pre-Upgrade Information Tool as part of the pre-requisite checks, it’s a good idea to run the tool ahead of time in order to analyze the database and take actions that can decrease downtime for upgrading.
If you installed the software for Oracle 12c and specified you are upgrading an existing database, DBUA starts automatically. While the upgrade is in process, DBUA shows the progress for each component. DBUA writes trace and log files and generates an HTML tracking report.
Caution: If you stop the upgrade in process, but do not restore the database, you cannot restart DBUA until you start the existing database in UPGRADE mode using the Oracle 12c server. You cannot go back to the original server unless you restore your database. Instead, you must continue with a manual (command line) upgrade.
Method 2. Command-Line Upgrade to Oracle 12c
Oracle Database 12c introduces a new command-line upgrade utility (catctl.pl). This utility replaces the catupgrd.sqlscript used to upgrade to previous releases. The new command-line upgrade utility enables parallel processing during the upgrade, resulting in smoother upgrade performance and reduced database downtime.
Command-line upgrades follow the same steps and take about the same amount of time as upgrading with DBUA. They’re most commonly used by database administrators who want more control over the process, or when a database is being moved to a new server in conjunction with the upgrade.
Starting with version 12c, the Pre-Upgrade Information Tool (preupgrd.sql) automatically generates fixup scripts to address common issues that may prevent an upgrade from being successful. The post-upgrade has also been enhanced to automate the running of many post-upgrade steps.
If command-line upgrade is interrupted for any reason, you can rerun or restart the Parallel Upgrade Utility.
Method 3: Full Transportable Export/Import or Transportable Tablespaces
Transportable tablespaces allow you to copy tablespaces from one database to another. This can be faster than exporting/importing data from those tablespaces because the tablespaces are copied as files without the need to interpret logical entities such as rows or indexes in those files.
Along with the tablespaces, metadata describing the objects in the source database must be transported to the new database using Oracle Data Pump. Transported tablespaces can be copied to a database on a different operating system platform or running a different release of Oracle. This feature makes transportable tablespaces a faster way to migrate and upgrade a database in one single operation.
Transportable tablespaces can be fairly complicated because the DBA is responsible for moving metadata including procedures, packages, constraints, etc. Full transportable export/import is a new feature in Oracle 12c that makes transporting this metadata much more straightforward. Oracle claims full transportable export/import is able to transport encrypted tablespaces.
Caution: You can’t restart full transportable export/import jobs. If the operation is interrupted for any reason, the entire job must be started over from the beginning.
Method 4: Oracle Data Pump Export/Import
Oracle Data Pump facilitates high-speed movement of data and metadata between Oracle databases. The Oracle Data Pump export (expdp) and import (impdp) utilities are frequently used to migrate tables, schemas, and databases to new hardware servers, to different operating system platforms, and to new releases of Oracle software.
Oracle Data Pump can write data to dump files on disk or it can transfer data directly over the network. When data is imported, it can be altered to match the characteristics of the destination database.
According to an Oracle whitepaper on the subject, “Some interesting ways in which a database can be transformed upon import include migrating to a new character set, implementing encryption or compression, changing BasicFiles LOBs to SecureFiles LOBs, or changing the partitioning of tables in the database.”
Data Pump Export/Import with Dump Files: Data Pump Export (expdp) chooses the best method to extract data from the source database. Data Pump Import (impdp) chooses the best method for reading dump files and inserts the data into the destination database.
Oracle Data Pump Network Mode: Instead of exporting to dump files and then importing into the destination database, you can migrate your database using Data Pump Import over a network link. According to Oracle, “this eliminates the need to store, manage, and transfer dump files. Instead, data is extracted from the source database and inserted directly into the destination database over a database link.” Pata Pump in network mode simplifies migration by allowing you migrate cross-platform or to a new release of Oracle in one step. Data Pump network mode jobs are fully restartable.
Migrating to a Pluggable Database: Oracle Data Pump export/import is a a fast and flexible way to migrate into a PDB. This is a particularly useful method when the source database resides on a different operating system, uses a different character set, or needs modification or reconfiguration during the migration.
If you’d like assistance with your Oracle databases, let Datavail help. We have more than 400 database administrators serving clients worldwide. With 24×7 managed database services, including database design, architecture and staffing, Datavail can support your organization, regardless of the challenges you may have. Contact Datavail today to learn more about our Oracle database services and how our experts can help with your ongoing operations.
The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.
This blog reviews how you can generate scripts for SQL server logins, role assignments, and server permissions for a smooth migration.
Learn how to fix common Log Shipping Failure errors in SQL Server. Includes step-by-step instructions, screenshots, and software script.