5 Managing Scheduled Outages

This chapter describes scheduled outages and the Oracle operational best practices that can tolerate or manage each outage type and minimize downtime.

This chapter contains these topics:

See Also:

Chapter 4 for information about unscheduled outages

5.1 Overview of Scheduled Outages

Scheduled outages are required for regular maintenance of the technology infrastructure that supports the application, including tasks such as:

  • Hardware maintenance, repair, and upgrades

  • Software upgrades and patching

  • Application (programmatic) changes, patches, and upgrades

  • Changes to improve performance and manageability of systems

You can implement many of these tasks while maintaining continuous application availability.

Table 5-1 describes scheduled outages that affect either the primary or secondary site.

Table 5-1 Scheduled Outages

Outage Scope Description Examples

Site-wide

The entire site where the current primary database resides is unavailable. Usually known well in advance.

Scheduled power outages

Site maintenance

Regular planned switchovers to test infrastructure

Hardware maintenance (node impact)

Hardware maintenance on a database server. Restricted to a node of the database cluster.

Repair of a failed component such as a memory card or CPU board

Addition of memory or CPU to an existing node in the database tier

Hardware maintenance (clusterwide impact)

Hardware maintenance on a database server cluster

Some cases of adding a node to the cluster

Upgrade or repair of the cluster interconnect

Upgrade to the storage tier that requires downtime on the database tier

System software maintenance (node impact)

System software maintenance on a database server. The scope of the downtime is restricted to a node.

Upgrade of a software component such as the operating system

Changes to the configuration parameters for the operating system

System software maintenance (clusterwide impact)

System software maintenance on a database server cluster

Upgrade or patching of the cluster software

Upgrade of the volume management software

Oracle patch upgrade for the database

Scheduled outage for installation of an Oracle patch

Patch Oracle software to fix a specific customer issue

Oracle patch set or software upgrade for the database

Scheduled outage for Oracle patch set or software upgrade

Patching Oracle software with a patch set

Upgrading Oracle software

Database object reorganization or redefinition

Changes to the logical structure or the physical organization of Oracle Database objects, primarily to improve performance or manageability.

Changes to the data or schema.

Using the Oracle Database online redefinition feature enables objects to be available during the reorganization or redefinition.

Moving an object to a different tablespace

Converting a table to a partitioned table

Add, modify, or drop one or more columns in a table or cluster

Storage maintenance

Maintenance of storage where database files reside

Converting to ASM

Adding or removing storage

Platform migration

Changing operating system platform of the primary and standby databases

Moving to the Linux operating system

Location migration

Changing physical location of the primary database

Moving the primary database from one data center to another

Programmatic changes

May include data changes, schema, and other programmatic changes.

Application upgrades

The following sections provide best practice recommendations for reducing scheduled outages on the primary and secondary sites:

5.1.1 Managing Scheduled Outages on the Primary Site

Table 5-2 shows the preferred solutions for performing scheduled outages on the primary site. The table includes links to detailed descriptions in Section 5.2, "Eliminating or Reducing Downtime for Scheduled Outages" beginning.

Table 5-2 Solutions for Scheduled Outages on the Primary Site

Planned Maintenance Preferred Oracle Solution Estimated Downtime

Site maintenance

  1. Site, Hardware, and Software Maintenance Using Database Switchover

  2. Complete Site Failover

  3. Application Failover

< 5 minutes

Hardware maintenance or system software maintenance (clusterwide impact)

  1. Site, Hardware, and Software Maintenance Using Database Switchover

  2. Application Failover

< 5 minutes

Hardware maintenance or system software maintenance (node impact)

Oracle RAC service relocation (see Section 5.2.10, "System Maintenance")

No downtime

Oracle RAC Cluster Ready Service (CRS) Upgrades

Oracle CRS rolling patch upgrade (see your platform-specific Oracle Clusterware Installation Guide for complete details)

No downtime

Oracle patch upgrade for the database, Critical Patch Updates (CPUs)

Oracle RAC rolling patch upgrade using opatch (see Section 5.2.3, "Oracle RAC Database Patches")

No downtime

Oracle diagnostic "one-off" patches

Online Patching

No downtime

ASM upgrades

Online ASM upgrade (see the section "Using ASM Rolling Upgrades" in the Oracle Database Storage Administrator's Guide)

No downtime

Oracle patch set or software upgrade for the database

Oracle Database rolling upgrade with Data Guard SQL Apply (see Section 5.2.5, "Database Upgrades")

< 5 minutes

Database object reorganization or redefinition

Online object reorganization with DBMS_REDEFINITION (see Section 5.2.9, "Data Reorganization and Redefinition")

No downtime

Database storage maintenance

Online storage maintenance using ASM (see Section 5.2.4, "Storage Maintenance")

No downtime

Database platform or location maintenance

Database Platform or Location Migration

< 5 minutes

Application changes

"Oracle Streams for Online Database Upgrade"

< 5 minutes

5.1.2 Managing Scheduled Outages On the Secondary Site

Outages on the secondary site do not affect availability because the clients always access the primary site. Outages on the secondary site might affect the RTO if there are concurrent failures on the primary site. Outages on the secondary site can be managed with no effect on availability:

  • If maximum protection database mode is configured and there is only one standby database protecting the primary database, then you must downgrade the protection mode before scheduled outages on the standby instance or database so that there is no downtime on the primary database.

  • If maximum protection database mode is configured and there are multiple standby databases, there is no need to downgrade the protection mode if at least one standby database that is configured with the LGWR SYNC AFFIRM attributes is available, and to which the primary database can transmit redo data.

When scheduling secondary site maintenance, consider that the duration of a site-wide or clusterwide outage adds to the time that the standby database lags behind the primary database, which in turn lengthens the time to restore fault tolerance. See Section 2.6.2, "Choose the Appropriate Level of Data Protection" for an overview of the Data Guard protection modes.

Table 5-3 describes the steps for performing scheduled outages on the secondary site.

Table 5-3 Managing Scheduled Outages on the Secondary Site

Planned Maintenance Oracle Database 11g with Data Guard Oracle Database 11g - MAA

Site shutdown

Before the outage:

Managing Scheduled Outages On the Secondary Site

After the outage:

Restoring Fault Tolerance After Planned Downtime on Secondary Site or Cluster

Before the outage:

Managing Scheduled Outages On the Secondary Site

After the outage:

Restoring Fault Tolerance After Planned Downtime on Secondary Site or Cluster

Hardware or software maintenance on the node that is running the managed recovery process (MRP)

Before the outage:

Managing Scheduled Outages On the Secondary Site

Before the outage:

Managing Scheduled Outages On the Secondary Site

Hardware or software maintenance on a node that is not running the MRP

Not applicable

No effect because the primary standby node or instance receives redo logs that are applied with the managed recovery process

After the outage: Restart node and instance, when available

Hardware or software maintenance (clusterwide impact)

Not applicable

Before the outage:

Managing Scheduled Outages On the Secondary Site

After the outage:

Restoring Fault Tolerance After Planned Downtime on Secondary Site or Cluster

Oracle patch and software upgrades

Downtime needed for upgrade, but there is no effect on the primary node unless the configuration is in maximum protection database mode

Downtime needed for upgrade, but there is no effect on the primary node unless the configuration is in maximum protection database mode

5.2 Eliminating or Reducing Downtime for Scheduled Outages

This section describes best practices for eliminating or reducing downtime due to scheduled outages. This section contains the following topics:

In general, online patching is the recommended solution for avoiding downtime when applying debug patches and interim patches where the scope of the upgrade is small. If you cannot perform your upgrade using online patching, then Oracle RAC is the next recommended solution before using Oracle Data Guard, next is transportable tablespaces, and then Oracle Streams. Regardless of the method you use, be sure to follow the guidelines and recommendations provided in the Oracle Database Upgrade Guide and its companion document, the Oracle 11g Upgrade Companion that is available in support note 601807.1 at http://support.oracle.com/. Also, before performing any rolling upgrade, Oracle recommends you perform extensive testing.

5.2.1 Site, Hardware, and Software Maintenance Using Database Switchover

A switchover is a planned transition that includes a series of steps to switch database roles between the primary and standby databases. Following a successful switchover operation, the standby database assumes the primary role and the primary database becomes a standby databaseFoot 1 . Data Guard enables you to change these roles dynamically by using Oracle Enterprise Manager and the broker, or manually by issuing SQL*Plus statements.

Switchovers are useful in many situations when performing site maintenance, and hardware or software maintenance such as database upgrades.

5.2.1.1 When to Perform a Data Guard Switchover

Switchover can occur whenever a primary database is started, the target standby database is available, and all the archived redo logs are available.

Switchovers are useful in the following situations:

  • Scheduled maintenance such as hardware maintenance or firmware patches on the primary host

  • Resolution of data failures when the primary database is still opened

  • Testing and validating the secondary resources, as a means to test disaster recovery readiness

  • When using SQL Apply to perform a rolling upgrade (see Section 5.2.5.2, "Data Guard SQL Apply or Transient Logical Standby Database")

Switchover is not possible or practical under the following circumstances:

  • Archived redo log files that are needed for apply are missing

  • A point-in-time recovery is required

  • The primary database is not open and cannot be opened

5.2.1.2 Best Practices for Configuring Data Guard Switchover

Before performing a switchover, employ the configuration best practices in Section 2.6.7.1.1, "Switchover Best Practices".

5.2.1.3 How to Perform Data Guard Switchover

You should perform switchovers dynamically using Oracle Enterprise Manager. If you are not using Oracle Enterprise Manager, then you can perform switchovers manually using the DGMGRL command-line interface or SQL*Plus statements:

5.2.1.3.1 Using SQL*Plus for Data Guard Switchover to a Physical Standby Database

This section describes the switchover steps at a high-level. See Oracle Data Guard Concepts and Administration for detailed steps.

Follow these steps to perform a switchover to a physical standby database:

  1. If possible, disconnect user sessions and disable or stop application processing.

  2. If the primary database is an Oracle RAC, then shut down all primary instances except one. To expedite this operation, issue a SHUTDOWN ABORT statement.

  3. Issue the following SQL statement on the primary database to convert it to the standby database role:

    ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

  4. If the standby database is an Oracle RAC, then shut down all standby instances except one. To expedite this operation, issue a SHUTDOWN ABORT statement.

  5. Check if the standby database has ever been open read-only:

    1. On the standby database, query the V$DATAGUARD_STATS view:

      SELECT VALUE FROM V$DATAGUARD_STATS WHERE NAME='standby has been open';

    2. If the query returns Y, then the standby database was opened in read-only mode and you must shut down and restart the standby database:

      SHUTDOWN IMMEDIATESTARTUP MOUNT

  6. Issue the following SQL statement on the original standby database to perform switchover:

    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

  7. Open the new primary database:

    ALTER DATABASE OPEN;

    Note:

    • Beginning with Oracle Database 10g Release 2, you can open the new primary database directly from the mount state only if the standby database was not opened read-only since the last time the database was started. If the database has been opened read-only, you must restart the database.

    • You may notice an increase in I/O while the new primary database's standby redo logs are cleared.

  8. On the new standby database (original primary database), bring it to the mount state and start Redo Apply. You can issue the following commands at the same time the new primary database is opening:

    SHUTDOWN IMMEDIATESTARTUP MOUNTALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

  9. Restart user sessions and application processing.

  10. If the production and standby databases are configured in an Oracle RAC, then start all instances on the primary and standby databases.

5.2.1.3.2 Using SQL*Plus for Data Guard Switchover to a Logical Standby Database

This section describes the switchover steps at a high-level. See Oracle Data Guard Concepts and Administration for detailed steps.

When performing a switchover using SQL*Plus commands it is possible for the original standby database that is to become the primary database to build and transmit the LogMiner dictionary to the current primary database (the new standby database) before performing the switchover. This reduces the total time needed to perform the switchover. The following steps describe how to perform this optimized method:

  1. Issue the following SQL statement on the primary database to enable receipt of redo from the current standby database:

    ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;

  2. On the current logical standby database, build the LogMiner dictionary and transmit this dictionary to the current primary:

    ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;

  3. If possible, disconnect user sessions and disable or stop application processing.

  4. When the SWITCHOVER_STATUS column of the V$DATABASE view returns TO LOGICAL STANDBY, convert the primary database to a standby by issuing:

    ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY WITH SESSION SHUTDOWN;

  5. Issue the following statement on the original standby database:

    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

  6. Restart user sessions and application processing.

5.2.2 Online Patching

Beginning with Oracle Database 11g there is support for online patching for some qualified interim patches. Online patching provides the ability to patch the processes in an Oracle instance without bringing the instance down. Each process associated with the instance checks for patched code at a safe execution point, and then copies the code into its process space. Thus, the processes being patched may not necessarily pick up the new code at the exact same time.

A key difference between traditional patching and online patching is that traditional patching is implemented at the software level and online patching is implemented at the software or Oracle instance level. In other words, instances using an ORACLE_HOME that receives a traditional patch always use the patched code whereas instances using an ORACLE_HOME that receives an online patch receive the patched code only if the instance is specified when the patch is applied.

Online patching is the preferred solution for debug patches and interim patches where the scope of the fix is small. The same restrictions that apply to rolling patches also apply to online patches, plus some additional restrictions.

The best practices for online patching include the following points:

  • Apply the patch to one instance at a time.

  • When rolling back online patches, ensure all patched instances are included.

  • Avoid the dangerous and confusing situation of having different software across instances using the same $ORACLE_HOME.

  • Assess memory impact on a test system before deploying to production (for example: the pmap command).

  • Never remove the $ORACLE_HOME/hpatch directory.

See Also:

5.2.3 Oracle RAC Database Patches

With Oracle RAC, you can apply certain database patches to one node or instance at a time, which enables continual application and database availability. "One-off" patches, interim patches, and Critical Patch Updates (CPUs) to database software are usually applied to implement known fixes for software problems an installation has encountered or to apply diagnostic patches to gather information regarding a problem. Such patch application is often carried out during a scheduled maintenance outage.

Oracle now provides the capability to do rolling patch upgrades with Oracle RAC with little or no database downtime. The tool used to achieve this is the opatch command-line utility.

The advantage of a Oracle RAC rolling upgrade is that it enables at least some instances of the Oracle RAC installation to be available during the scheduled outage required for patch upgrades. Only the Oracle RAC instance that is currently being patched must be brought down. The other instances can continue to remain available. Thus, the effect on the application downtime required for such scheduled outages is further minimized. Oracle's opatch utility enables the user to apply the patch successively to the different instances of the Oracle RAC installation.

Rolling upgrade is available only for patches that have been certified by Oracle to be eligible 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 that are not related to Oracle RAC internode communication

  • Patches that are not related to client-side tools such as SQL*PLUS, Oracle 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

Rolling upgrade of patches is currently available for one-off patches only. It is not available for patch sets.

Rolling patch upgrades are not available for deployments where the Oracle Database software is shared across the different nodes. This is the case where the Oracle home is on Cluster File System (CFS) or on shared volumes provided by file servers or NFS-mounted drives. The feature is only available where each node has its own copy of the Oracle Database software.

5.2.3.1 Best Practices to Minimize Downtime for All Database Patch Upgrades

Use the following recommended practices for all database patch upgrades:

  • Always confirm with Oracle Support Services that the patch is valid for your problem and for your deployment environment.

  • Have a plan for applying the patch and a plan for backing out the patch.

  • Apply the patch to your test environment first and verify that it fixes the problem.

  • When you plan the elapsed time for applying the patch, include time for starting up and shutting down the other tiers of your technology stack if necessary.

  • If the patch is not a candidate for Oracle RAC rolling upgrade and you can incur the downtime for applying the patch, go to Section 5.2.5, "Database Upgrades" to assess whether other solutions are feasible.

5.2.3.2 Best Practices to Minimize Downtime for Database Rolling Upgrades

The following are additional recommended practices for Oracle RAC rolling upgrades.

  • If multiple instances share an Oracle home, then all of them are affected by application of a patch. Administrators should verify that this does not cause unintentional side effects. Also, you must shut down all such instances on a node during the patch application. You must take this into account when scheduling a planned outage. As a best practice, only similar applications should share an Oracle home on a node. This provides greater flexibility for patching.

  • The Oracle inventory on each node is a repository of the Oracle Database software installed on the node. The inventory is node-specific. It is shared by all Oracle software installed on the node. It is similar across nodes only if all nodes are the same in terms of the Oracle Database software deployed, the deployment configuration, and patch levels. Because the Oracle inventory greatly aids the patch application and patch management process, it is recommended that its integrity be maintained. Oracle inventory should be backed up after each patch installation to any Oracle software on a specific node. This applies to the Oracle inventory on each node of the cluster.

  • Use the Oracle Universal Installer to install all Oracle database software. This creates the relevant repository entries in the Oracle inventory on each node of the cluster. Also, use the Oracle Universal Installer to add nodes to an existing Oracle RAC cluster.

    However, if this was not done or is not feasible for some reason, adding information about an existing Oracle database software installation to the Oracle inventory can be done with the attach option of the opatch utility. Node information can be also added with this option.

  • The nature of the Oracle rolling patch upgrade enables it to be applied to only some nodes of the Oracle RAC cluster. So an instance can be operating with the patch applied, while another instance is operating without the patch. This is not possible for nonrolling patch upgrades. Apply nonrolling patch upgrades to all instances before the Oracle RAC deployment is activated. A mixed environment is useful if a patch must be tested before deploying it to all the instances. Applying the patch with the -local option is the recommended way to do this.

    In the interest of keeping all instances of the Oracle RAC cluster at the same patch level, it is strongly recommended that after a patch has been validated, it should be applied to all nodes of the Oracle RAC installation. When instances of a Oracle RAC cluster have similar patch software, services can be migrated among instances without running into the problem a patch might have fixed.

  • Maintain all patches (including those applied by rolling upgrades) online and do not remove them after they have been applied. Keeping the patches is useful if a patch must be rolled back or applied again.

    Store the patches in a location that is accessible by all nodes of the cluster. Thus all nodes of the cluster are equivalent in their capability to apply or roll back a patch.

  • Perform rolling patch upgrades, just like any other patch upgrade, when no other patch upgrade or Oracle installation is being performed on the node. The application of multiple patches is a sequential process, so plan the scheduled outage accordingly.

  • If you must apply multiple patches at the same time but only some patches are eligible for rolling upgrade, then apply all of the patches in a nonrolling manner. This reduces the overall time required to accomplish the patching process.

  • For patches that are not eligible for rolling upgrade, the next best option for Oracle RAC deployments is the MINIMIZE_DOWNTIME option of the APPLY command.

  • Perform the rolling upgrade when system usage is low to ensure minimal disruption of service for the end users.

See Also:

Oracle Universal Installer and OPatch User's Guide for more information about the opatch utility

5.2.4 Storage Maintenance

Use the following procedure when adding or upgrading storage on the system. The procedures in the following sections assume that you are adding storage to an ASM disk group.

5.2.4.1 Migrating to ASM Storage

If you have an existing Oracle database that stores database files on a file system or on raw devices, you can migrate some or all of these database files to ASM. To minimize downtime, use a physical standby database to migrate data to ASM storage.

See Also:

5.2.4.2 Adding and Removing Storage

Disks can be added to and removed from ASM with no downtime. When disks are added or removed, ASM automatically starts a rebalance operation to evenly spread the disk group contents over all drives in the disk group.The best practices for adding or removing storage include:

  • Make sure your host operating system and storage hardware can support adding and removing storage with no downtime before using ASM to do so.

  • Use a single ALTER DISKGROUP command when adding or removing multiple disk drives.

    For example, if the storage maintenance is to add drives and remove existing drives, use a single ALTER DISKGROUP command with the ADD DISK clause to add the drives, and the DROP DISK clause to remove the existing drives. For example:

    ALTER DISKGROUP data
       DROP DISK diska5
       ADD FAILGROUP failgrp1 DISK '/devices/diska9' NAME diska9;

  • When dropping disks from a disk group, specify the WAIT option in the REBALANCE clause so the ALTER DISKGROUP statement does not return until the contents of the drives being dropped have been moved to other drives. After the statement completes, the drives can be safely removed from the system. For example:

    ALTER DISKGROUP data
DROP DISK diska5
ADD FAILGROUP failgrp1 DISK '/devices/diska9' NAME diska9
REBALANCE WAIT;

  • When dropping disks in a normal or high redundancy disk group, ensure there is enough free disk space in the disk group to reconstruct full redundancy.

  • Monitor the progress of rebalance operations using Enterprise Manager or by querying V$ASM_OPERATION.

  • For long-running rebalance operations that occur during periods of low database activity, increase the rebalance power limit to reduce the rebalance time.

See Also:

Oracle Database Storage Administrator's Guide

5.2.4.3 Upgrading ASM Nodes

Perform an ASM rolling upgrade to independently upgrade or patch clustered ASM nodes without affecting database availability, thus providing greater uptime. You can use ASM rolling upgrades only to upgrade clustered ASM instances for environments running Oracle Database 11g or later releases.

See Also:

The section about "Using ASM Rolling Upgrades" in Oracle Database Storage Administrator's Guide for complete information

5.2.5 Database Upgrades

The following Oracle features are available to perform database upgrades:

The method you choose to perform database upgrades can vary depending on the following considerations:

  • Downtime required to complete the upgrade

  • Setup time and effort required before the downtime

  • Temporary additional resources necessary (for example, disk space or CPU)

  • Complexity of the steps allowed to complete the upgrade

Table 5-4 lists the methods that can be used for database upgrades, and recommends what method to use.

Table 5-4 Database Upgrade Options

Upgrade Method Use This Method When...

Database Upgrade Assistant

Recommended method when the maintenance window is sufficient or when data type constraints prohibit the use of the other methods in this table.

Data Guard SQL Apply or Transient Logical Standby Database

DBUA cannot finish within the maintenance window and the database is not a candidate for Oracle RAC rolling patch upgrade.

Use a transient logical standby when the configuration has only a physical standby database.

Oracle Streams

This is a Streams implementation or when Data Guard SQL Apply rolling upgrade does not support the database versions in use.

Transportable Tablespaces

The database is using data types unsupported by Data Guard SQL Apply or Oracle Streams, and the user schemas are simple.

Regardless of the upgrade method you use, you should follow the guidelines and recommendations provided in the Oracle Database Upgrade Guide and its companion document, the Oracle 11g Upgrade Companion that is available at support note 601807.1. at http://support.oracle.com/

5.2.5.1 Database Upgrade Assistant

Database Upgrade Assistant (DBUA) is used to upgrade a database in place from an earlier software version.

When deciding if DBUA is the proper tool to use when performing a database upgrade with minimal downtime, consider the following:

  • DBUA upgrades the database dictionary and all components (for example: Java, XDB, Streams, and so on) that have been installed while the database is unavailable for normal user activity.

  • Downtime required for a database upgrade when using DBUA is determined by the time needed to:

    • Upgrade all database dictionary objects to the new version

    • Restart the database

    • Upgrade all database dictionary objects

    • Recompile all PL/SQL

    • Reconnect the clients to the upgraded database

  • To reduce the amount of downtime required for a database upgrade when using DBUA:

    • Remove any database options that are not being used.

      DBUA upgrades all of the installed database options, whether they are required by an application. By reducing the number of options that must be upgraded, you can reduce the overall upgrade time.

    • Remove unused user-supplied PL/SQL procedures.

      All PL/SQL routines in the database are invalidated and recompiled as part of the upgrade process. By reducing the amount of recompilation required during the upgrade, you can reduce the overall upgrade time.

Use DBUA for a database upgrade when the time to perform the upgrade with this method fits within the maintenance window.

See Also:

5.2.5.2 Data Guard SQL Apply or Transient Logical Standby Database

Use Data Guard SQL Apply or a transient logical standby database to upgrade a database with minimal downtime using a process called a rolling upgrade. Data Guard currently supports homogeneous environments where the primary and standby databases run on the same platform.

See Also:

Support note 413484.1 at http://support.oracle.com/ for exceptions that are specific to heterogeneous environments, and for other late-breaking information about rolling upgrades with SQL Apply

SQL Apply Rolling Upgrades

Use Data Guard SQL Apply for rolling database upgrade when a conventional upgrade cannot complete the upgrade within the maintenance window and the application does not use user-defined types.

Note the following points when deciding if SQL Apply is the appropriate method for minimizing downtime during a database upgrade:

  • SQL Apply and Oracle Streams share the same log mining infrastructure and therefore both features have the same data type restrictions on user-defined types, such as object types, REF values, varrays, and nested tables. SQL Apply has some data type restrictions (see Oracle Data Guard Concepts and Administration for a list of the restrictions). If there are data type restrictions, consider implementing Extended Datatype Support (EDS).

    EDS enables SQL Apply to replicate changes to tables that contain some data types not natively supported from one database to another. Beginning with Oracle Database 10g Release 2 (10.2.0.4) Patch Set 3, SQL Apply supports the ability for triggers to fire on the logical standby database, which provides the basis of EDS. For an overview of EDS, see the MAA white paper "Extended Datatype Support: with SQL Apply and Oracle Streams" available at

    http://www.oracle.com/technology/deploy/availability/pdf/maa_edtsoverview.pdf

    For examples using EDS to support data types that are not natively supported by SQL Apply, see support note 559353.1 at http://support.oracle.com/.

  • You can perform a SQL Apply rolling upgrade for any upgrade, including a major release upgrade if the source release is Oracle Database 10g release 1 (10.1.0.3) or higher. Before you begin, review the detailed steps for a SQL Apply rolling upgrade and verify the supported data types in Oracle Data Guard Concepts and Administration.

  • Downtime required for a database upgrade (rolling upgrade) when using Data Guard SQL Apply is determined by the time needed to:

    • Perform a Data Guard switchover

    • Reconnect the clients to the new database

See Also:

Transient Logical Standby Database Rolling Upgrades

You can use a transient logical standby database to perform a rolling database upgrade using your current physical standby database by temporarily converting it to a logical standby database. Use a transient logical standby when your configuration only has a physical standby database. Performing a rolling upgrade using a transient logical standby is similar to the standard SQL Apply rolling upgrade with the following differences:

  • A guaranteed restore point is created on the primary database to flash the database back to a physical standby database after the switchover.

  • The conversion of a physical standby database to a logical standby database uses the KEEP IDENTITY clause to retain the same DB_NAME and DBID as that of its primary database.

  • The ALTER DATABASE CONVERT TO PHYSICAL STANDBY statement converts the original primary database from a logical standby to a physical standby database.

  • The original primary database is actually upgraded through Redo Apply after it is converted from the transient logical standby database role to a physical standby database.

Figure 5-1 shows the flow of processing that occurs when you perform a rolling upgrade with a transient logical standby database.

Figure 5-1 Using a Transient Logical Standby Database for Database Rolling Upgrade

Description of Figure 5-1 follows
Description of "Figure 5-1 Using a Transient Logical Standby Database for Database Rolling Upgrade"

This procedure is documented in Oracle Data Guard Concepts and Administration in the section about "Performing a Rolling Upgrade With an Existing Physical Standby Database."

See Also:

The MAA white paper "Rolling Database Upgrades for Physical Standby Databases Using Transient Logical Standby 11g" at

http://www.oracle.com/technology/deploy/availability/pdf/maa_wp_11g_transientlogicalrollingupgrade.pdf

5.2.5.3 Oracle Streams

Use Oracle Streams to upgrade the database software from one version to another with minimal downtime. This is because Oracle Streams supports a configuration in which the primary database and its replica run on different database versions.

Note the following points when deciding if Oracle Streams is an appropriate method for a database upgrade:

  • Oracle Streams does not support user-defined types, such as object types, REF values, varrays, and nested tables. However, if there are data type restrictions, consider implementing Extended Datatype Support (EDS).

    EDS enables Streams to replicate changes to tables that contain some data types not natively supported from one database to another. For an overview of EDS, see the MAA white paper "Extended Datatype Support" available at

    http://www.oracle.com/technology/deploy/availability/pdf/maa_edtsoverview.pdf

    For examples using EDS to support data types that are not natively supported by SQL Apply, see support note To obtain additional information about EDS for Oracle Streams, see support note 556742.1. at http://support.oracle.com/.

  • The source database must be running Oracle9i release 2 or higher.

  •  More administrative effort may be required to set up and maintain the Oracle Streams environment for a database upgrade.

  • For Oracle Streams local capture, there might be a performance effect on the source database while the source and target databases run in parallel as changes are propagated to the target database.

  • Downtime required for a database upgrade when using Oracle Streams is determined by the time needed to reconnect the clients to the new database.

Consider using Oracle Streams when the following situations exist:

  • If the application uses Oracle Streams.

  • The upgraded (target) database can apply changes faster than they are being generated at the source.

See Also:

Oracle Streams Concepts and Administration for more information about database upgrading using Oracle Streams

5.2.5.4 Transportable Tablespaces

Use transportable tablespaces to accomplish a database upgrade by transporting all user data files into a pre-created, prepared target database.

Note the following points when deciding if transportable tablespaces is the appropriate method for performing a database upgrade:

  • The SYSTEM tablespace cannot be moved with transportable tablespaces. The target database SYSTEM tablespace contents, including user definitions and objects necessary for the application, must be built manually. Use Data Pump to move the contents of the SYSTEM tablespace.

  • Downtime required for a database upgrade when using transportable tablespaces is determined by the time needed to:

    • Place the source database tablespaces in read-only mode.

    • Perform a network import of the transportable metadata.

    • If the target database is on a remote system, then include the time to transfer all data files from the source system to the target system. However, note that using transportable tablespaces to perform a database upgrade is useful only if the data files can be used in their current location. Using the transportable tablespace method is not recommended if doing so requires that you copy the data files to the target location.

      The time it takes to transfer the data files can be reduced significantly by using a storage infrastructure that can make the data files available to the target system without physically moving the files, or by using a physical standby database.

Using transportable tablespaces to perform a database upgrade is recommended when:

  • The data files can be used in their current location to avoid copying data files as part of the transport process. If the target database is on a different machine, this requires that the storage is accessible to both the source and target systems.

  • DBUA cannot complete within the maintenance window.

  • Oracle Streams or Data Guard SQL Apply cannot be used due to data type restrictions.

  • The Oracle database has a simple schema.

See Also:

5.2.6 Database Platform or Location Migration

The following Oracle features are available to perform platform migrations and upgrades:

The method you choose to perform these database maintenance tasks depends on the following considerations:

  • Downtime required to complete the maintenance operations

  • Setup time and effort required before the downtime

  • Amount of temporary additional resources necessary, such as disk space or CPU

  • Complexity of the steps allowed to complete maintenance operations

Table 5-5 summarizes the methods you can use for platform migrations and database upgrades, and recommends which method to use for each operation.

Table 5-5 Platform and Location Migration Options

Operation Recommended Method Alternate Methods

Platform migration to same endian platform

Physical Standby Databases for Platform Migration

  1. Use Transportable Database for Platform Migration when a cross-platform physical standby database is not available for the platform combination to be migrated.

  2. Use Oracle Streams for Platform Migration transportable database cannot finish within the maintenance window.

Platform migration to different endian platform

Oracle Data Pump for Platform Migration

  1. Use Oracle Streams for Platform Migration when Data Pump cannot finish within the maintenance window.

  2. Use Transportable Tablespaces for Platform Migration when the database is using data types unsupported by Oracle Streams.

Location Migration Only

Data Guard Redo Apply (Physical Standby Database) for Location Migration

None.

Note:

Query the V$TRANSPORTABLE_PLATFORM view to determine the endian format of all platforms. Query the V$DATABASE view to determine the platform ID and platform name of the current system.

5.2.6.1 Physical Standby Databases for Platform Migration

The recommended approach for platform migration is to create a physical standby and perform a switchover. Physical standby databases support certain heterogeneous platform combinations. See support note 413484.1 for an up-to-date list.Oracle Data Guard and physical standby databases are the recommended solution for performing system and cluster upgrades that are not upgradeable using Oracle RAC rolling upgrades. For example, Data Guard is also recommended for:

  • System upgrades that cannot be upgraded using Oracle RAC rolling upgrades due to system restrictions.

  • Migrations to ASM, to Oracle RAC from a nonclustered environment, to 64-bit systems, to a different platform with the same endian format or to a different platform with the same processor architecture, or to Windows from Linux or to Linux from Windows.

  • When you have a primary database with 32-bit Oracle binaries on Red Hat 32-bit, and a physical standby database with 64-bit Oracle binaries on Red Hat 64-bit. Such configurations must follow additional procedures during Data Guard role transitions (switchover and failover) as described in support note 414043.1.

See Also:

Support notes 413484.1 and 414043.1 at http://support.oracle.com/

5.2.6.2 Transportable Database for Platform Migration

Transportable database, available beginning in Oracle Database 10g Release 2 (10.2), is the recommended solution for migrating an entire database to another platform that has the same endian format, but only when a cross-platform physical standby database is not available for the source/target platform combination to be migrated.

Consider the following points when deciding if transportable database is the appropriate method to use when moving a database to another platform:

  • Transportable database supports moving databases between platforms with the same endian format.

  • Downtime required for a platform migration when using transportable database is determined by the time needed to:

    • Place the source database in read-only mode.

    • Convert all data files with UNDO only. See support note 415884.1 at http://support.oracle.com/ for more details.

    • Transfer all data files from the source system to the target system.

      You can significantly reduce the amount of downtime by using a storage infrastructure that can make the data files available to the target system without physically moving the files.

See Also:

5.2.6.3 Oracle Streams for Platform Migration

You can use Oracle Streams to move a database from one platform to another with minimal downtime. This is because Oracle Streams supports a configuration in which the primary database and its replica run on different platforms.

Consider using Oracle Streams if transportable database cannot perform the migration quickly enough, when the application does not use user-defined types, and you can carry out any extra administrative effort required to perform the migration.

Note the following points when deciding if Oracle Streams is an appropriate method for performing a platform migration:

  • Oracle Streams does not support user-defined types, such as object types, REF values, varrays, and nested tables. If there are data type restrictions, consider implementing Extended Datatype Support (EDS).

    EDS enables Streams to replicate changes to tables that contain some data types not natively supported from one database to another. For an overview of EDS, see the MAA white paper "Extended Datatype Support: SQL Apply and Streams" at

    http://www.otn.oracle.com/goto/maa

    For examples using EDS to support data types that are not natively supported, see support note 556742.1. at http://support.oracle.com/.

  • To perform an upgrade using Oracle Streams, the source database must be running Oracle9i release 2 or higher.

  • Extra administrative effort may be required to set up and maintain the Oracle Streams environment.

  • For Oracle Streams local capture, there might be a performance impact on the source database while the source and target databases run in parallel as changes are propagated to the target database.

  • Downtime required for a platform migration when using Oracle Streams is determined by the time needed to apply the remaining transactions in the queue and to reconnect clients to the new database.

See Also:

Oracle Streams Concepts and Administration

5.2.6.4 Oracle Data Pump for Platform Migration

Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another, across different platforms and different database versions.

Note the following points when deciding if Data Pump is an appropriate method for a platform migration:

  • Oracle Data Pump is available only on Oracle Database 10g Release 1 (10.1) and later releases.

  • Downtime required for a platform migration when using Data Pump is determined by the time needed to perform a full database network import. A network import uses a database link between the target system and the remote source system to retrieve data and write it directly into the target system, without the use of dump files.

Use Data Pump when moving a database to a platform with different endian format when the network import time is acceptable.

See Also:

5.2.6.5 Transportable Tablespaces for Platform Migration

Transportable tablespaces accomplish a platform migration by transporting all user data files into a pre-created, prepared target database. Use transportable tablespaces when the database is using data types unsupported by Oracle Streams and the user schemas are simple.

Note the following points when deciding if transportable tablespaces is the appropriate method for performing a platform migration:

  • The SYSTEM tablespace cannot be moved with transportable tablespaces. the target database SYSTEM tablespace contents, including user definitions and objects necessary for the clients, must be built manually. Use Data Pump to move the necessary contents of the SYSTEM tablespace.

  • Downtime required for a platform migration or database upgrade when using transportable tablespaces is determined by the time needed to:

    • Place the source database tablespaces in read-only mode.

    • Perform a network import of the transportable metadata.

    • Transfer all data files from the source system to the target system.

      This time can be reduced significantly by using a storage infrastructure that can make the data files available to the target system without the physically moving the files.

    • Convert all data files to the new platform format using RMAN.

Use transportable tablespaces to migrate to a platform when Oracle Data Pump cannot complete within the maintenance window, and Oracle Streams or Data Guard SQL Apply cannot be used due to data type restrictions.

See Also:

Oracle Database Administrator's Guide for more information about transportable tablespaces

5.2.6.6 Data Guard Redo Apply (Physical Standby Database) for Location Migration

Data Guard Redo Apply can be used to change the location of a database to a remote site with minimal downtime by setting up a temporary standby database at a remote location and performing a switchover operation.

The downtime required for a location migration when using Data Guard Redo Apply is determined by the time required to perform a switchover operation.

See Also:

Oracle Data Guard Concepts and Administration for more information on Redo Apply and physical standby databases

5.2.7 Oracle Streams for Online Database Upgrade

An Oracle database upgrade is the process of transforming an existing, prior release of an Oracle Database system into the current release of the Oracle Database system and can be a very lengthy process.

If using Data Guard SQL Apply or Transient Logical Standby Database to upgrade your database is not applicable and you require zero-to-minimum downtime while performing the database or application upgrade, then configure Oracle Streams to perform a database upgrade with little or no downtime. To do so, use an Oracle Streams single-source replication environment with the following databases:

  • Source Database: The original database that is being upgraded.

  • Destination Database: The copy of the source database where an apply process applies changes made to the source database during the upgrade process. The apply process can apply changes to the same or different schema and object structure.

The following general steps describe how to perform a database upgrade while the database is online:

  1. Create an empty destination database.

  2. Configure an Oracle Streams single-source replication environment where the original database is the source database and a copy of the database is the destination database for the changes made at the source.

  3. Perform the database upgrade on the destination database. During this time the original source database is available online.

  4. Use Oracle Streams to apply the changes made at the source database to the destination database.

  5. When the destination database has caught up with the changes made at the source database, take the source database offline and make the destination database available for applications and users.

If the schema or object structure is different at the destination database, then you must incorporate Streams transformations to manipulate the change to its new structure.

See Also:

Appendix B "Online Database Upgrade with Oracle Streams" in Oracle Streams Concepts and Administration

5.2.8 Oracle Streams for Online Application Upgrades

An application upgrade may include a database upgrade plus any required application code and schema changes. If you require zero-to-minimum downtime while performing the database or application upgrade, then configure Oracle Streams to perform a database upgrade with little or no downtime. (See also Section 5.2.7, "Oracle Streams for Online Database Upgrade".)

The process for upgrading user-created applications using Oracle Streams can involve modifying and creating the schema objects at the destination database after instantiation. To account for differences between the source database and destination database, you can use one or more declarative rule-based transformations and DML handlers at the destination database to process changes.

In general, declarative rule-based transformations are easier to use than DML handlers. Therefore, when modifications to row LCRs are required, try to configure a declarative rule-based transformation first before using a DML handler. If row LCRs for tables that contain one or more LOB columns must be modified, then you should use a DML handler and LOB assembly.

Before you begin the database maintenance operation, you should follow the instructions in Oracle Streams Concepts and Administration in Appendix C "Online Database Maintenance with Oracle Streams" to:

  1. Prepare your declarative rule-based transformations or DML handlers.

  2. Determine the declarative rule-based transformations and DML handlers you need at your destination database. Your determination depends on the modifications to the schema objects required by your upgraded applications.

  3. Create the PL/SQL procedures necessary for any DML handlers during the database maintenance operation.

  4. Use LOB assembly if row LCRs for tables that contain one or more LOB columns must be modified.

  5. Handle logical dependencies if an apply process requires additional information to detect dependencies in row LCRs that are being applied in parallel (such as if the application rather than the database enforces logical dependencies, or if schema objects have been modified to support the application upgrade and a DML handler modifies row LCRs to account for differences between the source and destination databases).

See Also:

Appendix E "Online Database Maintenance with Oracle Streams" in Oracle Streams Concepts and Administration

5.2.9 Data Reorganization and Redefinition

Many scheduled outages related to the data server involve some reorganization of the database objects. The Online Reorganization and Redefinition feature of Oracle Database enables data reorganization to be performed even while the underlying data is being modified. This feature enhances availability and manageability by allowing users full access to the database during a data reorganization operation.

The ability to modify table physical attributes and transform both data and table structure has been available since Oracle8i. See the Oracle Database High Availability Overview for a comprehensive table of data reorganization capabilities.

In highly available systems, it is occasionally necessary to redefine large tables that are constantly accessed to improve the performance of queries or DML. Using Online Reorganization and Redefinition, administrators have the flexibility to modify table physical attributes and transform both data and table structure at the same time users have full access to the database. This capability improves data availability, query performance, response time, and disk space usage, all of which are important in a mission-critical environment. Plus, Online Reorganization and Redefinition can make the application upgrade process easier, safer and faster.

The recommended best practice is to reorganize tables using the DBMS_REDEFINITION PL/SQL package, because it provides a significant increase in availability compared to traditional methods of redefining tables that require tables to be taken offline. Whether you call DBMS_REDEFINITION manually at the command line or automatically through Oracle Enterprise Manager, the entire reorganization process occurs while users have full access to the table, thus ensuring system availability.

Figure 5-2 shows the Reorganize Objects Wizard in Oracle Enterprise Manager that you can use as an alternative to calling the DBMS_REDEFINITION package at the SQL*Plus command line. After you answer a few questions in the wizard, it automatically generates the script and performs the reorganization.

Figure 5-2 Database Object Reorganization Using Oracle Enterprise Manager

Description of Figure 5-2 follows
Description of "Figure 5-2 Database Object Reorganization Using Oracle Enterprise Manager"

Using the DBMS_REDEFINITION approach, an interim table is created that contains all the desired attributes. The reorganization begins by calling the procedure START_REDEF_TABLE, which is where the column mappings between the current and new version of the table are described. All the dependent objects such as triggers, constraints and indexes are automatically copied to the interim table using the procedure COPY_TABLE_DEPENDENTS. During the reorganization, any changes made to the original table are added to the interim table by calling the procedure SYNC_INTERIM_TABLE. The reorganization is complete when the procedure FINISH_REDEF_TABLE is called and the interim table is renamed as the main table.

You can rename a tablespace beginning in Oracle Database 10g, similar to the ability to rename a column, table and data file. Previously, the only way to change a tablespace name was to drop and re-create the tablespace, but this meant that the contents of the tablespace had to be dropped and rebuilt later. With the ability to rename a tablespace online, there is no interruption to the users.

ALTER TABLESPACE USERS RENAME TO new_tablespace_name;

Tablespace altered.

Additionally, consider the following when performing data reorganization:

  • Minimize concurrent activity on the table during an online operation.

    During an online operation, Oracle recommends users minimize activities on the base table. Database activities should affect less than 10% of the table while an online operation is in progress. Also the database administrator can use the Database Resource Manager to minimize the affect of the data reorganization to users by allocating enough resources to the users.

  • Oracle does not recommend running online operations at peak times or running a batch job that modifies large amount of data during an online data reorganization.

    In fact, parallel DML, direct load and import/export cannot be performed during an online operation.

  • Rebuild indexes online versus dropping an index and then re-creating an index online.

    Rebuilding an index online requires additional disk space for the new index during the operation, whereas dropping an index and then re-creating an index does not require additional disk space.

  • Coalesce an index online versus rebuilding an index online.

    Online index coalesce is an in-place data reorganization operation, hence does not require additional disk space like index rebuild does. Index rebuild requires temporary disk space equal to the size of the index plus sort space during the operation. Index coalesce does not reduce the height of the B-tree. It only tries to reduce the number of leaf blocks. The coalesce operation does not free up space for users but does improve index scan performance.

    If a user must move an index to a new tablespace, use online index rebuild.

  • Perform online maintenance of local and global indexes.

    Oracle Database 11g supports both local and global partitioned indexes with online operations. When tables and indexes are partitioned, this allows administrators to perform maintenance on these objects, one partition at a time, while the other partitions remain online.

See Also:

5.2.10 System Maintenance

For a scheduled outage that requires an instance, node, or other component to be isolated, Oracle RAC provides the ability to relocate, disable, and enable services. Relocation migrates a service to another instance. Services and instances can be selectively disabled while repair, change, or upgrade is performed on hardware or system software and reenabled after the maintenance is complete. This ensures that the service or instance is not started during the maintenance outage. The service and instance is disabled at the beginning of the planned outage. It is then enabled after the maintenance outage.

See Also:

When using Oracle RAC, Oracle Clusterware daemons start automatically at the time the node is started. When performing maintenance that requires one or more system reboots or requires that all non-operating system processes be shut down, use the crsctl command to stop and disable the startup of the Oracle Clusterware daemons. After maintenance is complete, enable and start the Oracle Clusterware daemons with crsctl commands.

See Also:

Oracle Real Application Clusters Administration and Deployment Guide for information about using the crsctl command

Footnote Legend

Footnote 1: At times the term switchback is also used within the scope of database role management. A switchback operation is a subsequent switchover operation to return the standby databases to their original roles.