14 Reducing Downtime for Planned Maintenance

14.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 14.2.6.2, "Upgrading with 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

14.2.1.2 Best Practices for Configuring Data Guard Switchover

Before performing a switchover, employ the Data Guard configuration best practices. For more information, see Section 9.4.1, "Oracle Data Guard Switchovers Best Practices".

14.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:

• Using Oracle Enterprise Manager, as described in Oracle Data Guard Broker

• Using the DGMGRL command-line interface, as described in Oracle Data Guard Broker

• Using SQL*Plus:

  • Role Transitions Involving Physical Standby Databases:

    See Oracle Data Guard Concepts and Administration for detailed steps on Role Transitions Involving Physical Standby Databases.

  • Role Transitions Involving Logical Standby Databases:

    See Oracle Data Guard Concepts and Administration for detailed step on Role Transitions Involving Logical Standby Databases.

After performing the Data Guard Switchover do the following:

• If the database is moved to the secondary site and the application tier is also moved to the secondary site, perform complete site failover. For more information see Section 13.2.1, "Complete Site Failover (Failover to Secondary Site)."

• If only the database is moved to the secondary site, perform application failover. See Section 13.2.4, "Application Failover" for more information.

14.2.4.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 14.2.6, "Database Upgrades" to assess whether other solutions are feasible.

14.2.4.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 the repository that keeps a central inventory of all Oracle software installed. 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 an 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.

14.2.4.3 Out-of-place Software Installation and Patching

The following software installations are performed, by default, out-of-place by Oracle Universal Installer (OUI). Software installations performed by OUI are full software installations:

• Major release

• Maintenance release

• Patch set (beginning with 11g Release 2)

The following software installations are performed in-place by the OPatch utility. OPatch installs the software update into an existing ORACLE_HOME by overwriting existing software with updated software from the patch being installed:

• Interim patch installation

• Bundle patch installation

• Patch Set Update (PSU) installation

• Critical Patch Update (CPU) installation

• Diagnostic patch installation

Advantages of out-of-place patching

• Applications remain available while software is upgraded in the new ORACLE_HOME.

• The configuration inside the ORACLE_HOME is retained because the cloning procedure involves physically copying the software (examples are files such as LISTENER.ORA, TNSNAMES.ORA, and INITSID.ORA).

• It is easier to rollback or test between the original ORACLE_HOME and the patched ORACLE_HOME.

• When consolidating, you could have multiple versions of ORACLE_HOME, so this option should better support consolidation.

Considerations for using out-of-place patching

• When performing out-of-place patch installation with cloning, you must change any ORACLE_HOME environment variable hard coded in application code and Oracle-specific scripts.

• Out-of-place patching requires more disk space than in-place patching.

Out-of-place patching with OPatch

Traditionally, patches installed with OPatch are done in-place, which means that the new code is applied directly over the old code.

The disadvantages of in-place patching are:

• The application cannot connect to the database while new code is being installed.

• If patch rollback is required, the application cannot connect to the database while old code is being reinstalled.

Software installation performed by OPatch to the Oracle Database software home or the Grid Infrastructure software home can be performed out-of-place by using ORACLE_HOME cloning techniques to copy the software to a new home directory before applying a patch to the new ORACLE_HOME with OPatch. The high-level approach to perform out-of-place patching is:

1. Clone the active ORACLE_HOME to a new ORACLE_HOME.

2. Patch the new ORACLE_HOME.

3. Switch to make the new ORACLE_HOME the active software home. This can be done in a rolling manner one node at a time.

14.2.4.4 Using OPlan for Patching

OPlan is a utility that facilitates the patch installation process by providing you with step-by-step patching instructions specific to your environment for both in-place and out-of-place patch installation. Currently, OPlan is supported for Exadata Database Bundle Patches. For the latest information, see "Oracle Software Patching with OPLAN" in My Oracle Support Note 1306814.1 at

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

14.2.5.1 Grid Infrastructure Rolling Upgrade

Grid Infrastructure upgrade means taking the Oracle Clusterware and Oracle ASM to a later major version, maintenance version, or patch set. Grid Infrastructure upgrade is performed in a rolling manner.

14.2.5.2 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 Oracle ASM disk group.

• Migrating to Oracle ASM Storage

• Adding and Removing Storage

• Upgrading Oracle ASM Nodes

14.2.6.1 Upgrading with Database Upgrade Assistant (DBUA)

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, 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

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

  • Update data dictionary statistics immediately before the upgrade.

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

14.2.6.2 Upgrading with 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.

14.2.6.2.2 Transient Logical Standby Database Rolling Upgrade

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 14-1 shows the flow of processing that occurs when you perform a rolling upgrade with a transient logical standby database.

Note:

To simplify the operation shown in Figure 14-1, a Bourne shell script is available that automates the database rolling upgrade procedure (starting with Oracle Database 11g Release 1). The database rolling upgrade is performed using an existing Data Guard physical standby database and the transient logical standby rolling upgrade process. The Bourne shell script, named physru, is available for download with details in "Oracle 11g Data Guard: Database Rolling Upgrade Shell Script" in My Oracle Support Note 949322.1 at

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

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

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

See Also:

• Oracle Data Guard Concepts and Administration for more information about "Performing a Rolling Upgrade With an Existing Physical Standby Database"

• The MAA white paper, "Database Rolling Upgrades Made Easy by Using a Data Guard Physical Standby Database", which describes the process of automating many of the tasks associated with a database rolling upgrade, available from the MAA Best Practices area for Oracle Database at

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

14.2.6.3 Upgrading with Oracle GoldenGate

Consider using Oracle GoldenGate as an alternative to Data Guard database rolling upgrades for upgrading the database software from one version to another with minimal downtime, for requirements that Oracle Data Guard is not designed to address. Oracle GoldenGate offers the following advantages over Oracle Data Guard for this purpose:

• Oracle GoldenGate can upgrade an Oracle Database in rolling fashion from an Oracle Database release before Oracle Database 10g (Data Guard Database Rolling Upgrades are supported beginning with Oracle Database 10g).

• Oracle GoldenGate can be configured for one-way replication from a later Oracle Database release to a previous Oracle Database release to enable a fast fall-back option (Oracle Data Guard can only replicate from a earlier database release to a later release). This is useful in cases where you want to operate at the new release for a period and have the option to quickly revert to the previous release should unanticipated issues arise days after production cut-over. By configuring one-way replication from the new release to the previous release, production can be switched to the prior release quickly, without losing data or incurring the time of a downgrade, while the problems are resolved.

• Oracle GoldenGate can be configured for multi-master replication between different Oracle Database releases to facilitate a zero downtime upgrade (Oracle Data Guard is a one-way replication solution). When the new Oracle release is deployed and ready for user connections, new user connections can be directed to the new release while existing user connections at the old release continue to process transactions. As existing user connections terminate, utilization of the Oracle Database operating at the previous release diminishes naturally without users perceiving any downtime. Multi-master replication keeps both databases synchronized during this transitional phase. Once all users have migrated to the new release, simpler one-way replication can maintain synchronization of the previous database release to provide a fast fall-back option as described in the previous bullet item. Note that multi-master replication is not suitable for all applications - conflict detection and resolution is required.

• If you cannot use the procedure described in Section 14.2.6.2, "Upgrading with Data Guard SQL Apply or Transient Logical Standby Database" to upgrade your database and you require zero-to-minimum downtime while performing the database or application upgrade, then configure Oracle GoldenGate to perform a database upgrade with little or no downtime. For more information, see the White Paper, "Zero-Downtime Database Upgrades Using Oracle GoldenGate" at

  http://www.oracle.com/technetwork/middleware/goldengate/overview/ggzerodowntimedatabaseupgrades-174928.pdf

14.2.6.4 Upgrading with 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 you can use the data files 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:

• You can use the data files 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 GoldenGate or Data Guard SQL Apply cannot be used due to data type restrictions.

• The Oracle database has a simple schema.

14.2.7.1 Consider Your Options and Your Migration Strategy

When developing your migration strategy the first step will be to learn about your new target environment and determine how your data is going to physically move from your source system to the target system. At the heart of the target environment is the Oracle Database. As with any Oracle database upgrade or migration, you should follow the guidelines and recommendations provided in the Oracle Database Upgrade Guide and its companion document, "Oracle 11gR2 Upgrade Companion" in My Oracle Support Note 785351.1 at

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

• Update init.ora during migration.

  Take your existing init.ora file and remove parameters you consider no longer important. For changes that take parameters away from their default setting, justify the changes. For example, you might be able to remove underscore parameters that are set to work around issues found in previous releases (for example to handle an optimizer problem you resolved in a previous release).

• Update SQL during migration.

  Remove SQL hints added in a previous Oracle Database version that were put in place to force the optimizer to generate the desired plan. The optimizer generally creates a good execution plan without the need for hints when provided good statistics.

• Simplify or change schema objects during migration.

  You should consider if there are changes to the schema layout that you can make during a migration. For example, consider the following:

  • Changes in the partitioning scheme for large tables

  • Adoption of newly available compression capabilities, such as Hybrid Columnar Compression (HCC) if migrating to Oracle Exadata Database Machine (see Oracle Database Concepts for more information)

  • Adoption of Transparent Data Encryption (TDE), especially if migrating to a system that provides cryptographic hardware acceleration

  Also, determine if there are objects that should not be migrated, such as excessive use of indexes. If you are going to have altered or fewer schema objects in the database you must consider whether it is better to migrate the database in its current form, then perform the changes after migration, or be more selective during the migration.

• Remove unused tablespaces and data files during migration.

  You should consider if you can remove unused or unnecessary tablespaces and data files during a migration. Using fewer tablespaces and data files leads to better manageability and performance.

14.2.7.2 Plan Your Migration

As you plan the migration consider the following points:

• Consider upgrading the source database to Oracle Database 11g Release 2 as this may improve the migration (in some cases significantly). For example, the parallel capabilities of Data Pump are significantly better in Oracle Database 11g Release 2 than in Oracle Database Release 10.2, so a database export from the source system could be improved and completed faster if the source database is upgraded to Oracle Database 11g Release 2.

• Consider dropping schema objects that are not needed in the source database before the migration. This can reduce the amount of data that has to be migrated.

• Determine and consider the business needs and downtime requirements. Review the Oracle features for platform migration in Section 14.2.7.3, "Oracle Features for Platform Migration and Upgrades," for the factors that influence the amount of downtime required.

• Consider whether there is a requirement or an opportunity to perform the migration in stages. For example, if there is a large amount of read only data in the source database, it might be migrated well before the live data migration to reduce downtime.

• Any platform migration exercise should include a significant amount of testing.

14.2.7.3 Oracle Features for Platform Migration and Upgrades

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

• Physical Standby Databases for Platform Migration

• Transportable Database for Platform Migration

• Oracle GoldenGate for Platform Migration

• Oracle Data Pump for Platform Migration

• Transportable Tablespaces for Platform Migration

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

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 14-4 summarizes the methods you can use for platform migrations and database upgrades, and recommends which method to use for each operation.

14.2.7.4 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. For an up-to-date list of platform combinations, 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

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 Oracle 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 Linux 32-bit, and a physical standby database with 64-bit Oracle binaries on Linux 64-bit. Such configurations must follow additional procedures during Data Guard role transitions (switchover and failover) as described in Support Note 414043.1.

14.2.7.5 Transportable Database for Platform Migration

Transportable database 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 data files. Only files that contain undo segments, or files that contain automatic segment-space management (ASSM) segment headers if converting from or to HP Tru64, require conversion.

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

14.2.7.6 Oracle GoldenGate for Platform Migration

You can use Oracle GoldenGate to move a database from one platform to another with minimal downtime. Consider using Oracle GoldenGate if transportable database cannot perform the migration quickly enough, when the application does not use user-defined types, and you can perform any extra administrative effort required to perform the migration.

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

• Oracle GoldenGate does not support user-defined types, such as object types, REF values, varrays, and nested tables.

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

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

14.2.7.7 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 when deciding if Data Pump is an appropriate method for a platform migration:

• Downtime required for a platform migration when using Data Pump is determined by the time needed to perform a full database export, transfer the export dump files to the target system, then perform a full database import.

• Downtime may be reduced by performing the export to storage that is shared between the source and target systems, thus eliminating the need to transfer the export dump files.

• Data Pump supports the ability to load the target database directly from the source database over database links, known as network import. In some cases a network import may be faster than the multi-step approach of export database, transfer dump files, and import database.

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

14.2.7.8 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 GoldenGate 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 GoldenGate or Data Guard SQL Apply cannot be used due to data type restrictions.

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

You can use Data Guard Redo Apply 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.