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.

If you cannot perform the upgrade using Oracle RAC, 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.5.

Oracle RAC Solution Description

To perform upgrades using Oracle RAC:

1. 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).

2. Shut down destination instance or instances with the IMMEDIATE option.

3. Shut down and disable Oracle Clusterware.

   Disabling Oracle Clusterware prevents it from starting automatically.

4. Perform maintenance.

5. Enable and start Oracle Clusterware.

   This step implicitly starts the database instances.

6. Start the application service.

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

7. Repeat all steps on the next node.

Additional Considerations

Verify the following:

• Ensure that the planned maintenance can be done 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.

  See Also:

  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.

In general, you first upgrade the physical standby database and then perform an Oracle Data Guard switchover to the physical standby database.

To upgrade the physical standby database and perform a switchover:

1. Upgrade the system or change the physical standby database system to your destination environment.

   For example, you can convert the standby database from a single-instance database to an Oracle RAC database by using Oracle ASM, without any impact on the primary database. Then, restart the standby database, ensure that it matches your destination environment, and wait for Redo Apply to finish applying all redo data to the standby database.

2. Perform an Oracle Data Guard switchover. Optimally, the switchover should take only seconds to minutes.

3. Shut down the original primary database (now the standby database).

4. Upgrade or make system changes to the original primary database.

5. Restart the upgraded database as a standby database and allow recovery to automatically synchronize the databases.

6. Optionally, perform an Oracle Data Guard switchover to return the standby database to the primary database role.

Additional Considerations

• For fastest switchover, configure the standby database to use real-time apply and, if possible, ensure that the databases are synchronized before the switchover operation.

• Use Oracle Data Guard and physical standby databases to perform system and cluster upgrades if Oracle RAC rolling upgrade or online patching is not possible. See Oracle Data Guard Concepts and Administration for more information.

• The conversion from 32-bit to 64-bit is automatic if you are applying an Oracle Database patch set or doing an Oracle Database upgrade at the same time. If you are upgrading only the operating system, then you may need to perform additional post-upgrade steps that are described in support note 414043.1 at http://metalink.oracle.com/. Also, see the Oracle Database Upgrade Guide for more information about upgrades.

4.1.3 Oracle Interim Database Patches

Use Oracle RAC to avoid downtime when applying Oracle interim database patches. You can apply approximately 90% of the new patches using Oracle RAC.

If you cannot apply patches using Oracle RAC, then use Oracle Data Guard and physical standby databases. See Section 4.1.2 for more information.

Solution Description

Oracle interim (one-off) 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.

Oracle provides the capability to do rolling patch upgrades with Oracle RAC with little or no database downtime using the OPatch command-line utility.

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.

Additional Considerations

Performing a rolling upgrade is possible only for patches that are certified for rolling upgrades. Typically, patches that can be installed in a rolling upgrade include:

• Patches that do not affect the contents of the database, such as the data dictionary

• Patches not related to Oracle RAC internode communication

• Patches related to client-side tools such as SQL*Plus, Oracle Database utilities, development libraries, and Oracle Net

• Patches that do not change shared database resources, such as data file headers, control files, and common header definitions of kernel modules

Do not use Oracle RAC to perform rolling upgrades of patch sets.

4.1.4 Online Patching

Online patching is the recommended solution for avoiding downtime when an online patch is available for debug patches and interim patches.

Solution Description

Online patches are a special type of interim patch that you can apply while the instance remains online.

Oracle provides the capability to perform online patching with any Oracle database using the OPatch command-line utility.

Additional Considerations

• Oracle provides combo patches, which are online patches that can also be applied when the instance is offline.

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

• Oracle provides certified online patches for diagnostic patches or various bug fixes.

• Oracle provides online patches when the patch does not change shared memory structures in the System Global Area (SGA), or other critical internal code structures.

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

4.1.5 Upgrading Oracle Clusterware

Performing rolling upgrades of Oracle Clusterware is the recommended solution for avoiding downtime when upgrading Oracle Clusterware.

Solution Description

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

4.1.6 Upgrading Oracle Automatic Storage Management (Oracle ASM)

Performing rolling upgrades is the recommended solution for upgrading Oracle ASM.

Solution Description

You can perform all upgrades starting with Oracle Database 11g (and later releases) in a rolling fashion.

4.1.7 Storage Migration

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

Solution Description

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.

Additional Considerations

Before removing the source storage array, ensure that the rebalancing is complete.

4.1.8 Migrating Oracle Exadata Storage Server Software

The guidelines in the MAA white paper "Best Practices for Migrating to Oracle Exadata Storage Server" define best practices for pre-migration, 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 white paper is available at http://www.oracle.com/technology/products/bi/db/exadata/pdf/migration-to-exadata-whitepaper.pdf.

Solution Description

This section summarizes the steps described in the "Best Practices for Migrating to Oracle Exadata Storage Server" MAA white paper.

To migrate Oracle Exadata Cell deployments:

Pre migration:

1. Perform capacity planning

2. Employ Exadata configuration automation

3. Ensure the Infiniband network is used for Oracle Clusterware and Oracle RAC communication

4. Ensure proper Oracle ASM software and disk group attributes

5. Ensure proper database software and compatibility

6. Enable the DB_BLOCK_CHECKSUM initialization parameter

Migration:

1. Configure the Oracle ASM allocation unit size to 4 MB

2. Configure optimal database extent sizes

3. Choose to migrate to Oracle Exadata Storage Server either logically or physically:

   • Logical migrations: database extent size changes

   • Physical migrations: no database extent size change

4. Create new tablespaces on Oracle Exadata Storage Server

Post-Migration:

1. Check disk groups for rebalance

2. Assess index requirements

4.1.9 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.10 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.10.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 Streams:

• 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 transportable tablespaces to perform a database upgrade in less than one hour.

• Transportable tablespaces 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.10.2 describes the transportable tablespace solution.

• Oracle Streams is the solution that provides the most flexibility when performing database upgrades and additional data type support. Section 4.1.10.3 describes this solution.

4.1.11 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.11.1 describes this solution.

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

4.1.12 Platform Migration Across Different Endian Format Platforms

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

• Transportable tablespace is the recommended solution for performing platform migration across different endian format platforms and reduces downtime significantly. See the "Solution Description for Transportable Tablespace" section for more details.

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

• For planned downtime of potentially seconds, consider using Oracle Streams as described in Section 4.1.11.2, "Solution Description for Platform Migration Using Oracle Streams".

Solution Description for Transportable Tablespace

Migrating a database to a new platform using a different endian format with transportable tablespaces requires the following high level steps.

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 destination system's endian format.

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.

If the destination database is being moved to a new location (for example, to a new data center) during the migration, then create a physical standby database from the original primary database co-located with the destination database. After an Oracle Data Guard switchover, transport the tablespaces from the source to the destination without incurring the file transfer time as part of the downtime.

Additional Considerations

Transportable tablespace has limitations and restrictions in regard to character sets, opaque types, and system tablespace objects. Unlike previous solutions, the steps are not automated.

Perform a platform migration using transportable tablespaces 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.

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.

4.5.2 Oracle Streams for Rolling Upgrades

Consider using Oracle Streams for fast rolling upgrades. However, while Oracle Streams upgrades can achieve little or no database down time, your ability to configure this solution will require some operational investment. See Section 3.6, "Oracle Streams" and Oracle Streams Concepts and Administration for more information.

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 could have possibly succeeded subseconds later. Specifying DDL 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.

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.

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 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 subseconds and works independently of the existing data volume, but it also consumes no space.

4.5.6 Finer-Grained Dependencies

Prior to Oracle Database 11g, metadata would record 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 cases such as these, the dependent objects were sometimes invalidated when there was no logical requirement to do so. 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, application availability is increased. 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 and, therefore does not cause consequential invalidations.

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 have to be modified without being able to bring the complete application 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.

4.5.8 Materialized View Logging Control

Oracle Database includes session-level control for materialized view logs. You can disable the capture of changes for materialized views (materialized view logs) for an individual session while logging continues for changes made by other sessions. This feature reduces application patching downtime.

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

This feature reduces the time and effort to manually recompile dependent PL/SQL after an online table redefinition. This also includes views, synonyms, and other table dependent objects (with the exception of triggers) that are not logically affected by the redefinition.