Migrating a database to a different platform is required when you move an existing database to a system that runs a different operating system than the current system. For example, database migration is required when moving a database from Microsoft Windows to Linux, or from AIX or HP-UX to Oracle Exadata Database Machine running Oracle Linux. Database migration options are highly dependent on the source database platform and source database version. Database migration to a different platform is accomplished with one of the following solutions:

• Data Guard heterogeneous physical standby

• Data Pump full transportable export/import

• Data Pump tablespace transportable export/import

• Recovery Manager cross-platform transport of a PDB using inconsistent backups

• Recovery Manager cross-platform transport of tablespaces in a PDB

The following features can be used in combination with the migration solutions previously described to reduce database migration downtime:

• Recovery Manager cross-platform inconsistent tablespace transportation

• Recovery Manager cross-platform transport of a PDB using inconsistent backups

• Oracle GoldenGate

Topics:

• Migrating a Database to Oracle Exadata Database Machine or Oracle SuperCluster
  
• Platform Migration Solutions
  
• Data Pump Tablespace Transportable Export/Import
  
• Methods to Reduce Database Migration Downtime
  

1. Create the empty target database with the desired character set.
2. Start a change-synchronization Extract group to extract ongoing data changes.
3. Perform a Data Pump full non-transportable export/import. The data is automatically converted to the new character set during the import process.
4. Start the change-synchronization Replicat group to resynchronize rows that were changed while the target database was being created.
5. Stop the application.
6. Start the application, connecting to the target database.

Migrating a non-container database (non-CDB), or an unplugged pluggable database (PDB), to a PDB in a target CDB is accomplished with one of the following solutions:

• CREATE PLUGGABLE DATABASE statement

• Data Pump full transportable export/import

• Data Pump tablespace transportable export/import

The methods described in the following sections can be used in combination with the solutions previously described to reduce migration downtime:

• Recovery Manager Cross-Platform Inconsistent Tablespace Transportation

• Reducing Migration Downtime with Oracle GoldenGate

The use of these features to reduce downtime when migrating to multitenant architecture is the same as the use of these features to reduce downtime for database migration to a different platform.

The following sections describe migration to Oracle ASM storage

• Migrate to Oracle ASM-Managed Storage Using Data Guard

• Migrate to New Storage Using Oracle ASM Rebalance

• Migrate to Oracle ASM by Relocating Online Data Files

Topics:

• Migrate to Oracle ASM-Managed Storage Using Data Guard
  
• Migrate to New Storage Using Oracle ASM Rebalance
  
• Migrate to Oracle ASM by Relocating Online Data Files
  

You can use Data Guard or execute the conversion in place when migrating from a non-clustered system running single-instance Oracle Database to a clustered environment running Oracle RAC. Required downtime depends on the option you use.

Every data file is either online (available) or offline (unavailable). You can alter the availability of individual data files or temporary files by taking them offline or bringing them online. Offline data files cannot be accessed until they are brought back online.

Starting in Oracle Database 12c Release 1 (12.1) you can use SQL to move an online data file from one physical file to another while the database is open and accessing the file.

You can use the ALTER DATABASE MOVE DATAFILE SQL statement to rename or relocate online datafiles. This statement enables you to rename or relocate a datafile while the database is open and users are accessing the data file.

When you rename or relocate online data files, the pointers to the data files, as recorded in the database control file, are changed. The files are also physically renamed or relocated at the operating system level.

You might rename or relocate online data files because you want to allow users to access the data files when you perform one of the following tasks:

• Move the data files from one type of storage to another

• Move data files that are accessed infrequently to lower cost storage

• Make a tablespace read-only and move its data files to write-once storage

• Move a database into Oracle Automatic Storage Management (Oracle ASM)

• Rename a data file to a more descriptive name

Oracle continues to broaden support for dynamic reconfiguration of the database, enabling it to adapt to changes in hardware demands without any service interruptions.

Oracle Clusterware online resource attribute modification allows certain attributes of a resource to be modified without the need to restart the resource for the change to take effect. Online resource attribute modification is available using SRVCTL and CRSCTL commands.

Oracle Database dynamically accommodates various changes to hardware and database configurations by providing the ability to:

• Add and remove processors from a symmetric multiprocessing (SMP) server

• Add and remove nodes and instances in an Oracle RAC environment

• Dynamically increase and decrease its shared memory allocation and automatically tune memory online using automatic shared memory management

• Add and remove database disks online without disturbing database activities using Oracle ASM

• Add and remove storage arrays or Exadata Cells online without disturbing database activities using Oracle ASM

• Change existing Exadata Elastic system by expanding with additional Exadata Database Servers, Exadata Storage Servers, or Exadata Racks without downtime

• Automatically rebalance the I/O load across the database storage using Oracle ASM

• Move data files online when adding or dropping disks using Oracle ASM, which automatically rebalances database storage whenever the storage configuration is changed

• Dynamically control database session resource consumption using Resource Manager consumer groups and plans

• Change almost all initialization parameters without shutting down the instance, by using either of the following SQL*Plus statements:

  • The ALTER SESSION statement changes the value of a parameter during a session.

  • The ALTER SYSTEM statement changes the value of a parameter in all sessions of an instance for the duration of the instance.

These capabilities provide no-cost system changes and capacity on-demand provisioning, both of which are fundamental requirements of enterprise grid computing.

Two memory management initialization parameters, MEMORY_TARGET and MEMORY_MAX_TARGET, enable automatic management of the System Global Area (SGA), Program Global Area (PGA), and other memory required to run Oracle Database.

The MEMORY_MAX_TARGET parameter specifies the maximum value to which the MEMORY_TARGET can grow dynamically.

Oracle Database uses a noncentralized policy to free and acquire memory in each subcomponent of the SGA and the PGA. Oracle Database automatically tunes memory by prompting the operating system to transfer granules of memory from less needy to more needy components. The granularity of the memory transfer is dependent on the current free memory and the amount of memory the operating system requires to maintain a basic level of service.

Oracle ASM automatically distributes data files, control files, and log files across all available disks. Database storage is rebalanced whenever the storage configuration changes, including adding and removing disks, Exadata Cells, or storage arrays. Oracle ASM provides redundancy through the mirroring of database files, and provides optimal performance by automatically striping database files across available disks.

1. Perform the following prerequisite checks:

   • Ensure that the planned maintenance can be performed in a rolling manner from an operating system perspective.

   • Ensure that the database and clusterware versions are certified with the new system and hardware changes.

2. Stop the application service if the application service runs on more than one instance in the cluster. If the application service runs on only the instance being upgraded, then relocate the service to another node in the cluster.

   Stopping the application service implicitly redirects connections off of the destination instance when using fast application notification (FAN).

3. Shut down the destination instance or instances with the IMMEDIATE option.
4. Shut down and disable Oracle Clusterware.

   Disabling Oracle Clusterware prevents it from starting automatically.

5. Perform maintenance.
6. Enable and start Oracle Clusterware.

   This step implicitly starts the database instances.

7. Start the application service.

   This step implicitly redirects connections to the destination instance when using FAN.

8. Repeat all steps on the next node.

Typically, interim and diagnostic patches are applied to one node at a time in a rolling manner. During patch application to a software home, the software (for example, a database instance) running from the home is shut down. If, however, there is an urgent need for the patch to be installed and software cannot be shut down at the current time, then qualified interim and diagnostic patches can be applied online while software remains running.

The only time a patch should be applied in an online manner is when:

• The patch README indicates that it can be applied in an online manner.

• The patch needs to be applied urgently and database instances cannot be shut down to apply the regular (offline) version of the patch.

You can perform online patching with any Oracle database using the OPatch command-line utility.

Use the following considerations when performing online patching:

• Oracle provides qualified interim and diagnostic patches as combination patches, which contain both an online patch and an offline patch for the same bug fix.

  Thus, you can apply the online patch initially to avoid unplanned downtime. However, because online patches have memory overhead, you should roll back the online patch, and apply the offline patch during scheduled downtime.

• Applying an online patch increases memory consumption on the system because each Oracle process uses more memory from the Program Global Area (PGA) during the patch application. Consider memory requirements before you begin applying an online patch. Each online patch is unique, and the memory requirements are patch-specific. Apply the patch on your test system first so that you can assess the effect of the online patch on your production system and estimate any additional memory usage.

Data Guard and physical standby databases are the recommended solution for performing system and cluster upgrades (including Oracle Grid Infrastructure upgrades) that you cannot upgrade using Oracle RAC rolling upgrades.

Data Guard is also recommended for migrations to Oracle ASM, Oracle RAC, 64-bit systems, Windows to Linux, or Linux to Windows, or the same processor architecture platforms. For example:

• Use Data Guard for system upgrades that cannot be upgraded using Oracle RAC rolling upgrades due to system restrictions.

• Use Data Guard when migrating to Oracle ASM, from a noncluster environment to Oracle RAC, to a different platform with the same endian format, or to a different platform with the same processor architecture. The time required to perform the switchover is the only downtime incurred. For more information, see "Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration" in My Oracle Support Note 413484.1 at

  https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=413484.1

In general, first upgrade the system or cluster where the physical standby database runs, and then perform a Data Guard switchover to the physical standby database. For cases where database software is upgraded, refer to Rolling Patch Installation with Data Guard.

Topics:

• Upgrading the Physical Standby Database
  
• Best Practices for System and Cluster Upgrades
  

Oracle patches to database software are usually applied to implement known fixes for software problems, or to apply diagnostic patches to gather information about a problem. Plan to perform upgrades and apply patches during a scheduled maintenance window. Use the options for rolling or non-rolling patch apply that work best with your business needs.

There are several types of patches including:

• Interim patch

  An interim patch is a bug fix made available to customers who cannot wait until the fix is included in a subsequent patch set release or database patch for Exadata. Installation of an interim patch is done on an as-needed basis, hence it is not a regularly scheduled planned maintenance event.

• Bundle patch

  A collection of patches that is issued between patch sets. A patch bundle is usually cumulative. Microsoft Windows bug fixes for Oracle Database are generally issued in a patch bundle (as opposed to an interim patch).

• Patch Set

  Patch sets contain primarily bug fixes; however, some minor new features and change in functionality may be included.

• Patch Set Update (PSU)

  A quarterly patch that contains the most critical fixes for the applicable product (including security fixes), enabling customers to apply one patch to avoid many problems.

• Critical Patch Update (CPU)

  A collection of high-priority fixes (usually for security issues) once a quarter. CPUs are cumulative with respect to prior security fixes but may contain other fixes in order to address patch conflicts with non-security patches (that is, reduce the need for merge requests).

• Diagnostic patch

  A patch created specifically to diagnose a problem and not to fix a bug.

Topics:

• Rolling Patch Installation with Oracle Real Application Clusters
  
• Rolling Patch Installation with Data Guard
  

Performing rolling upgrades of Oracle Clusterware is the recommended solution for avoiding downtime when upgrading Oracle Clusterware. For single-instance Oracle RAC databases, consider using Oracle RAC One Node.

Rolling upgrades avoid downtime and ensure continuous availability of Oracle Clusterware while the software is upgraded to the new version. When you upgrade to Oracle Clusterware 12c, Oracle Clusterware and Oracle ASM binaries are installed as a single binary called the grid infrastructure. You can upgrade Oracle Clusterware in a rolling manner from Oracle Clusterware 10g and Oracle Clusterware 11g.

You can perform all upgrades to Oracle Clusterware in a rolling manner.

Performing rolling upgrades is the recommended solution for upgrading Oracle ASM. You can perform all upgrades starting with Oracle Database 11g (and later releases) in a rolling manner.

When you upgrade to Oracle Clusterware 12c, Oracle Clusterware and Oracle ASM binaries are installed as a single binary called the grid infrastructure. You can only upgrade Oracle ASM in a rolling manner from Oracle Database 11g release 1 (11.1).

During a rolling Exadata Storage Server Software upgrade, storage servers are patched one at a time until all of the servers are updated. Rolling patching takes advantage of Oracle ASM redundancy and automatic disk resynchronization to allow databases to continue to operate during patching. Rolling Exadata Storage Server Software upgrade orchestration is managed by the PatchMgr utility provided with the Exadata Storage Server Software.

Data Guard using SQL Apply is a recommended solution for performing patch set and database upgrades with minimal downtime. If the source database is using data types not natively supported by SQL Apply, you can use Extended Datatype Support (EDS) to accommodate several more advanced data types.

If the source database is using a software version not supported by SQL Apply rolling upgrade (earlier than Oracle Database release 10.1.0.3), or using EDS cannot sufficiently resolve SQL Apply data type conflicts, then consider using Rolling Upgrades using Oracle Active Data Guard, Database Upgrade Assistant (DBUA), transportable tablespace, or Oracle GoldenGate.

• Rolling Upgrades using Oracle Active Data Guard use a Data Guard physical standby database and the SQL Apply process.

• DBUA provides a graphical user interface (GUI) utility that guides you through the upgrade process and is the simplest and recommended method of upgrading a database. However, if the time it takes DBUA to upgrade a database does not fit in the defined maintenance window, then consider using the transportable tablespace feature to perform a database upgrade in less than 1 hour.

• Transportable tablespace is the solution if you cannot use SQL Apply but the maintenance window requires downtime to be less than 1 hour in duration, and the database being upgraded has a small number of simple schemas and data files that do not need to be transferred as part of the transport process (such as when the data files will be used in place).

• Oracle GoldenGate provides the most flexibility when performing database upgrades and requiring additional data type support.

DBUA incurs downtime. The amount of downtime is dependent on a number of factors.

Do not use Oracle RAC to perform rolling upgrades of patch sets. See your operating system-specific Oracle Real Application Clusters installation guide.

Oracle Data Guard broker supports Oracle Active Data Guard rolling upgrade in Oracle Database 12c Release 2. Oracle Active Data Guard rolling upgrade was introduced in Oracle Database 12c Release 1. It simplifies the execution of the transient logical database rolling upgrade process by automating many manual steps in a simple PL/SQL package, DBMS_ROLLING. In addition to making database rolling upgrades simpler, the automated process is much more reliable. Oracle Data Guard broker can now direct Oracle Active Data Guard rolling upgrades from the DGMGRL command-line interface. Data Guard Broker support also adds substantial simplification to the rolling upgrade process by transparently handling redo transport destination settings and other tasks.

Topics:

• Performing Database Upgrades Using Data Guard and Physical Standby Databases
  
• Performing Database Upgrades Using Data Pump Full Transportable Export/Import
  
• Performing Database Upgrades Using Transportable Tablespace
  
• Performing Database Upgrades Using Oracle GoldenGate
  
• Performing Database Upgrades Using Oracle GoldenGate and Data Guard
  

Edition-based redefinition (EBR) lets you upgrade the database component of an application while it is in use, thereby minimizing or eliminating downtime.

To upgrade an application while it is in use, you copy the database objects that comprise the application and redefine the copied objects in isolation. Your changes do not affect users of the application—they continue to run the unchanged application. When you are sure that your changes are correct, you make the upgraded application available to all users.

The following sections describe the Editions, Editioning Views, and Crossedition Triggers features of edition-based redefinition.

For more information, see Oracle Database Development Guide.

Topics:

• Editions
  
• Editioning Views
  
• Crossedition Triggers
  

Consider using Oracle GoldenGate for fast rolling upgrades. However, although Oracle GoldenGate upgrades might incur little or no database downtime, your ability to configure this solution requires some operational investment.

Data definition language (DDL) commands require exclusive locks on internal structures. If DDL commands are issued, then these locks may not be available causing the statement to immediately fail even though the DDL might have succeeded less than a second later. Specifying DDL commands with the WAIT option (the new default) resolves this issue. You specify the wait time instance-wide (in the initialization parameter file) and modify the wait time on a session level.

Specifying DDL commands with the WAIT option provides more flexibility to define grace periods for such commands to succeed instead of raising an error right away, thus requiring additional application logic to handle such errors.

The states (ENABLE and DISABLE) and ordering (FOLLOWS) are triggers to control the firing of triggers. These additional states allow greater administrative control for triggers. You can use the CREATE TRIGGER statement in a disabled state to validate successful compilation before enabling. In addition, the trigger order can be controlled with the FOLLOWS clause.

Default values of columns are maintained in the data dictionary for columns specified as NOT NULL.

Adding new columns with DEFAULT values and the NOT NULL constraint no longer requires the default value to be stored in all existing records. This enhancement not only enables a schema modification in less than a second and works independently of the existing data volume, but it also consumes no space.

Prior to Oracle Database 11g, metadata recorded mutual dependencies between objects with the granularity of the whole object. (For example, PL/SQL unit P depends on PL/SQL unit Q, or view V depends on table T.) In such cases, the dependent objects were sometimes needlessly invalidated. For example, if view V depends only on columns C1, C2, and C3 in table T and a new column, C99, is added, the validity of view V is not logically affected. Nevertheless, in earlier releases, V was invalidated by the addition of column C99.

Beginning with Oracle Database 11g release 1 (11.1), dependency metadata is recorded at a finer level of granularity, so that the addition of C99 does not invalidate view V. Similarly, if procedure P depends only on elements E1 and E2 in package PKG, then if element E99 is added to PKG, procedure P is not invalidated. (In Oracle Database 10g, this change to PKG would invalidate procedure P.)

By reducing the consequential invalidation of dependent objects in response to changes in the objects they depend upon, you can increase application availability. The benefit occurs both in the development environment and when an active application is parsed or upgraded. The benefit occurs when an Oracle Database patch set is applied because changes to schema objects must be compatible.

An invisible index provides an alternative to making an index unusable or even to dropping the index. An invisible index is maintained for any DML operation but is not used by the optimizer unless you explicitly specify the index with a hint.

Applications often require modification even when the complete application cannot be taken offline. Invisible indexes enable you to use temporary index structures for certain operations or modules of an application without affecting the overall application. Furthermore, you can use invisible indexes to test the removal of an index without dropping it right away, thus enabling a grace period for testing in production environments.

An invisible column is a user-specified column whose values are only visible when the column is explicitly specified by name. You can add an invisible column to a table without affecting existing applications, and make the column visible if necessary.

You might use invisible columns if you want to make changes to a table without disrupting applications that use the table. After you add an invisible column to a table, queries and other operations that must access the invisible column must refer to the column explicitly by name. When you migrate the application to account for the invisible columns, you can make the invisible columns visible.

In Oracle Database 12c, both B-tree and bitmap indexes can be created on the same set of columns. This feature enables an index to be created on the same set of columns as an existing index as long as some characteristic is different. This enables the type of an index to be changed in a patch edition while not disrupting an application. Only one of the multiple indexes can be a visible index at any time.

This feature minimizes the need to recompile dependent PL/SQL packages after an online table redefinition. If the redefinition does not logically affect the PL/SQL packages, recompilation is not needed. This optimization is turned on by default.

If recompilation is needed, this feature reduces the time and effort to manually recompile a dependent PL/SQL package after an online table redefinition. The recompilation also includes views, synonyms, and other table-dependent objects (with the exception of triggers) that are not logically affected by the redefinition.