4 Oracle Database High Availability Solutions for Planned Downtime

4.1.1 Operating System Upgrades and Hardware Upgrades

Using Oracle RAC is the recommended solution for avoiding downtime during system and hardware upgrades. For a single-instance Oracle RAC database, you can use Oracle RAC One Node.

If you cannot perform the upgrade using Oracle RAC or Oracle RAC One Node, then the recommended solution is to use Oracle Data Guard and physical standby databases as described in Section 4.1.2. Alternatively, you can use cold cluster failover with Oracle Clusterware as described in Section 4.1.4.

The following list provides a high-level overview of the steps when upgrading using Oracle RAC:

1. Perform the following prerequisite checks:

   • Ensure that the planned maintenance can be performed in a rolling fashion 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 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.

Also, see your operating system-specific Oracle Real Application Clusters installation guide.

4.1.2 System and Cluster Upgrades and Migrations Using Oracle Data Guard

Oracle Data Guard and physical standby databases are the recommended solution for performing system and cluster upgrades that you cannot upgrade using Oracle RAC rolling upgrades. Oracle 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 Oracle Data Guard for system upgrades that cannot be upgraded using Oracle RAC rolling upgrades due to system restrictions.

• Use Oracle 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, you first upgrade the physical standby database and then perform an Oracle Data Guard switchover to the physical standby database.

4.1.3 Oracle Database Patches

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 apply patches during a scheduled maintenance outage.

To avoid downtime when applying Oracle database patches, perform rolling patch upgrades using Oracle RAC. You can apply approximately 90% of the new patches using Oracle RAC. Oracle provides the capability to perform rolling patch upgrades with Oracle RAC with little or no database downtime using the OPatch command-line utility. If it is not possible to use Oracle RAC, then use Oracle Data Guard and physical standby databases. See Section 4.1.2 for more information.

There are several types of patches including:

• Interim patch

  A single patch created to provide a specific fix between the release of patch sets.

• Bundle patch

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

• Patch Set Update (PSU)

  A quarterly patch that contains the most critical fixes for the applicable product (including security fixes), allowing 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 (i.e. reduce the need for merge requests).

• Diagnostic patch

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

An Oracle RAC rolling upgrade enables all but one of the instances of the Oracle RAC installation to be available during the scheduled outage, further reducing the impact on the application downtime required for scheduled outages. The Oracle OPatch utility enables you to apply the patch successively to the different instances in an Oracle RAC installation.

Performing a rolling upgrade is possible only for patches that are certified for rolling upgrades.

4.1.4 Upgrading Oracle Clusterware

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.

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

4.1.5 Upgrading Oracle Automatic Storage Management (Oracle ASM)

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 fashion.

For more information, see the Oracle Automatic Storage Management Administrator's Guide.

4.1.6 Storage Migration

Using Oracle ASM is the recommended solution for performing storage migrations.

Oracle ASM enables you to add all disks in one storage array and subsequently drop all disks from another array. Oracle ASM automatically rebalances and migrates data to the new storage while the database remains operational. Before removing the source storage array, ensure that the rebalancing is complete.

For more information, see the MAA white papers "Migration to Automatic Storage Management (ASM)" and "Best Practices for Creating a Low-Cost Storage Grid for Oracle Databases" at http://www.oracle.com/goto/maa.

4.1.7 Migrating Oracle Exadata Storage Server Software

The guidelines in the MAA presentation "Best Practices for Migrating to Exadata Database Machine" define best practices for pre-migration and post migration from legacy storage to Oracle Exadata Storage Server. The best practices help you determine the most appropriate migration strategy given the application service levels and attributes. The MAA presentation is available on the MAA Web site at http://www.oracle.com/goto/maa

4.1.8 Upgrading Oracle Exadata Storage Server Software

See the Oracle Exadata Storage Server Software documentation to learn about the solutions and tools used to perform upgrades.

4.1.9 Patch Set and Database Upgrades

Oracle Data Guard using SQL Apply is the recommended solution for performing patch set and database upgrades with minimal downtime. Section 4.1.9.1 describes this solution. 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 Database Upgrade Assistant (DBUA)^Foot 1 , transportable tablespace, or Oracle GoldenGate:

• 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 one hour.

• Transportable tablespace is the solution if you cannot use SQL Apply but the maintenance window requires downtime to be less than an 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). Section 4.1.9.2 describes the transportable tablespace solution.

• Oracle GoldenGate provides the most flexibility when performing database upgrades and requiring additional data type support. Section 4.1.9.4 describes this solution.

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

See Oracle Database High Availability Best Practices for more information and for help choosing the database upgrade method appropriate for your configuration.

4.1.9.3 Performing Database Upgrades Using Oracle GoldenGate

The configuration in Figure 4-1 shows how to configure Oracle GoldenGate and Oracle Data Guard to minimize downtime and risk for planned outages, such as for any upgrades and migrations that are not supported by an Oracle Data Guard database rolling upgrade. For example, this might include migrating to a different hardware architecture and operating system, or performing application upgrades that modify database objects. In this configuration, the physical standby databases provide disaster protection to prevent downtime or data loss before, during, and after the migration. This configuration also avoids any performance impact or operational risk by isolating the production database from any work required to perform the migration.

Figure 4-1 Oracle GoldenGate Configuration for Minimizing Planned Downtime

Description of "Figure 4-1 Oracle GoldenGate Configuration for Minimizing Planned Downtime"

Oracle GoldenGate replication from the standby database (in the top right of Figure 4-1), to the new production database (bottom right), requires Oracle GoldenGate Archive Log Mode. If the requirements for Archive Log Mode cannot be met, then replicate directly from the original production database (represented by the database in the top left corner).

These requirements are achieved by creating a parallel environment on the new platform. Depending upon the type of migration planned, instantiating the new primary database may be as simple as restoring a backup of the existing standby database. For more complex migrations it may be necessary to use other Oracle technologies to instantiate the new primary database, such as Oracle Transportable Technologies or Oracle Data Pump. Once instantiated, any additional changes are then implemented on what will become the new production system. When all changes have been implemented, a new physical standby database is created to provide continuos data protection after cutover. Oracle GoldenGate heterogeneous replication (previously configured), is then used to synchronize the new production system with all transactions that had occurred on the old system while the new environment was being implemented. When synchronization is complete, production is ready for cutover to the new environment. There is also the option of using Oracle GoldenGate heterogeneous replication after the cutover to keep the old environment synchronized with the new production system for a period of time - to provide a fast fall back option should any unanticipated problems arise.

4.1.10 Platform Migration Across the Same Endian Format Platforms

Consider the following approaches when you perform platform migrations across the same endian format platforms:

• Oracle Data Guard (physical standby database) is the recommended solution for performing platform migration across Linux and Windows platforms. Section 4.1.2 describes this solution.

• If cross-platform physical standby database is not available for the platform combination to be migrated, then use the transportable database feature. Section 4.1.10.1 describes this solution.

• If the transportable database feature cannot perform the migration quickly enough, then use Oracle GoldenGate. Section 4.1.10.2 describes this solution.

4.1.11 Platform Migration Across Different Endian Format Platforms

Consider the following approaches when performing platform migrations on different endian format platforms:

• For significantly reduced downtime, the transportable tablespace feature is the recommended solution for performing platform migration across different endian format platforms. Migration using transportable tablespace is described after this list.

  The transportable tablespace solution has limitations and restrictions regarding character sets, opaque types, and system tablespace objects. Unlike previous solutions, the steps are not automated.

  Perform a platform migration using transportable tablespace if all of the following are true:

  • The source and destination platforms have different endian formats.

  • The time required to perform a full Data Pump Export and Import does not fit in the maintenance window.

• For the simplest of all the approaches, consider using Oracle Data Pump. See Oracle Database Utilities for complete information about using Oracle Data Pump.

• For planned downtime that potentially requires only seconds, consider using Oracle GoldenGate, as described in Section 4.1.10.2.

The following high-level steps describe how to migrate a database to a new platform using transportable tablespace:

1. Create a new, empty database on the destination platform.

2. Import objects required for transport operations from the source database into the destination database.

3. Export transportable metadata for all user tablespaces from the source database.

4. Transfer data files for user tablespaces to the destination system.

5. Use RMAN to convert the data files to the endian format of the destination system.

6. Import transportable metadata for all user tablespaces into the destination database.

7. Import the remaining database objects and metadata (that were not moved by the transport operation) from the source database into the destination database.

4.2.1 Dynamic Reconfiguration of the Database

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 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 grow and shrink 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^Foot 3 

• 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

• 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.

4.2.2 Automatic Tuning of Memory Management

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.

MEMORY_MAX_TARGET specifies the maximum value to which 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.

4.2.3 Automated Distribution of Data Files, Control Files, and Log Files

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.

4.5.1 Edition-Based Redefinition

Edition-based redefinition allows you to upgrade the database component of an application while the application is in use, thereby minimizing or eliminating down time. Your changes do not affect users of the application who continue to run the unchanged application until you make the upgraded application available to all users.

In favorable cases, rollover is possible. The pre-upgrade and the post-upgrade editions can be used concurrently so that sessions that were started before the post-upgrade edition was published can continue to use the pre-upgrade edition until they are terminated naturally while new sessions use the post-upgrade edition. In less favorable cases, all pre-upgrade sessions must be terminated before new sessions can be allowed to use the post-upgrade edition. In such cases, the application suffers a small amount of downtime.

The following sections describe the Editions, Editioning Views, and Crossedition Triggers features of edition-based redefinition. For more information, see the Oracle Database Development Guide.

4.5.2 Oracle GoldenGate for Rolling Upgrades

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 will require some operational investment. See Section 3.7 and the Oracle GoldenGate documentation, as appropriate.

4.5.3 DDL with the WAIT Option

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. For more information, see the Oracle Database Administrator's Guide.

4.5.4 ENABLE, DISABLE, and FOLLOWS Clauses for CREATE TRIGGER

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. For more information, see the Oracle Database Development Guide.

4.5.5 Enhanced ADD COLUMN Functionality

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. For more information, see the Oracle Database Administrator's Guide.

4.5.6 Finer-Grained Dependencies

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 a live application is parsed or upgraded. The benefit occurs when an Oracle Database patch set is applied because changes to schema objects must be compatible. For more information, see the Oracle Database Development Guide.

4.5.7 Invisible Indexes

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. For more information, see the Oracle Database Administrator's Guide.

4.5.8 Dependent PL/SQL Recompilation After Online Table Redefinition

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 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. For more information about redefining tables online, see the Oracle Database Administrator's Guide.