Using the Physical Standby for Rolling Upgrades - Oracle 11g

Oracle 10.1.0.3 introduced the feature to be able to perform rolling upgrades of the database software for the logical standby database. For a short interim, the databases can run different releases of Oracle software on the primary and logical standby databases. With careful planning, you can upgrade the disaster recovery site and then switch over the database. The amount of downtime can be as little as the amount of downtime to perform a switchover.

Once the applications are switched over, the original primary database can be upgraded. This approach of a rolling upgrade can be achieved with minimal downtime on the production database. The ability to perform this kind of rolling upgrade is now possible with the physical standby database. The physical standby database goes through a transient conversion to a logical standby and is converted to a physical standby.

Prepare the Primary Database

On the primary database, you need to confirm that flashback is enabled, you need to create a restore point, and you need to back up the standby controlfile, as shown here:

Pay particular attention that you don’t delete this file. You will use this controlfile to switch over your primary to standby. The database must be in Maximum Availability or Maximum Performance mode to qualify for a rolling upgrade. You must downgrade databases that are in Maximum Protection mode to either one of the other modes.

Convert to Logical: Keep Your Identity

In Oracle Database 11g, you can temporarily convert the physical standby database to a logical standby to perform a rolling upgrade. The conversion from physical to logical standby is identical as in the previous release except for one step. Instead of using the command alter database recover to logical standby database_name, you will use the keep identity clause in lieu of database_name. You can temporarily convert a physical standby to a logical standby database using the new keep identity clause option:

SQL> alter database recover to logical standby keep identity;

Database altered.

The keep identity clause tells Oracle Data Guard to behave as a logical standby database even though it is really still a physical standby database. The DBID and DB_NAME of the standby database will remain the same. The keep identity clause should be used only in context of rolling upgrades of the physical standby database.

Please be aware that as you are converting into the logical database, you have to be concerned about the unsupported datatypes. You can query the DBA_LOGSTDBY_UNSUPPORTED view to find all the unsupported datatypes on the database, as shown here:

SQL> select distinct owner, table_namefrom dba_logstdby_unsupported;

At this point, you need to be extra cautious to keep all your archivelogs. You will need the archivelogs later during the rolling upgrade process. You should make sure to disable the automatic archivelog deletion process while the database is engaged in keep-identity mode. In addition, you need to start the SQL Apply process. You can achieve both of these steps in the manner specified here:

SQL> execute dbms_logstdby.apply_set ('LOG_AUTO_DELETE', 'FALSE');

SQL> alter database start logical standby apply immediate;

Now, you can exercise the same steps you would usually apply to perform a rolling upgrade to a logical standby database. Once the logical standby upgrade process is complete on the transient logical standby database server, your disaster recovery site will be running the new Oracle software and become the primary database, and the primary database will be switched over to become the logical standby database.

Next, you will have to perform the following steps to turn the original database (at the primary data center) into a physical standby for the new primary database (at the disaster recovery site):

1. Shut down the database.
2. Start up in mount mode.
3. Flash back the database to the guaranteed restore point created earlier (pre_upgrade_11g).
4. Shut down again and start up in nomount mode.
5. Restore the controlfile from the backup taken earlier(/tmp/control01.standby.ctl).
6. Shut down again.
7. Switch the binary to the new Oracle software version. At this point, both sites will be running
the new version of Oracle software.
8. Start up in mount mode.
9. Start MRP, and make sure that both databases are in sync.

Your database will be synchronized again running the new version of Oracle software. You can perform a switchover again to bring the primary database back to the original data center. At this point, both sites are running the new version of Oracle software with the original Data Guard configuration.

Improvements in Redo Transport

One of the changes made to the redo log transport mechanism in Oracle now defaults to asynchronous mode. In Oracle Database 11g, the default behavior of the log transport mechanism is set to asynchronous mode. One way to confirm the new default asynchronous redo transport mode is to invoke the Data Guard command-line interface (DGMGRL) and review the verbose output from the SHOW command. You will notice that the last line of the following output (logxptmode=async) reflects the asynchronous mode of redo transport:

DGMGRL> show database verbose dba11gdr

Database

The redo transport mechanism in Oracle Database 11g can also take advantage of authentication using SSL. The redo transport network transmission sessions can now use the remote login password file to perform encrypted SSL connections.

Block Change Tracking Support

Oracle Database 10g introduced the block change tracking feature to improve incremental backup performance. Block change tracking enables fast incremental RMAN backups by reading and backing up only the changed data blocks during incremental backups. Oracle Database 11g allows for block change tracking on the Data Guard configuration. To enable block change tracking, you can submit a SQL statement similar to the following example on the standby database:

SQL> alter database enable block change tracking using file '+DATA';

Database altered.

This example enables block change tracking and places the file in the DATA diskgroup.

Note Use of real-time query or RMAN block-change tracking on a Data Guard standby database requires a license for Oracle Active Data Guard.

BCT enabled on the standby database provides greater ammunition to leverage backups at the disaster recovery site. Performing backups on the disaster recovery site can offload resources on the primary data center.

RMAN Understands Data Guard Configurations

Previously, DBAs had to create a standby controlfile from the primary database and push the controlfile using sftp/scp to the standby database server to create a physical standby database. Oracle Database 11g eliminates this step. Using the RMAN repository, you are able to register the physical standby database. Once you register the standby database with RMAN, RMAN has the ability to resynchronize file names and update the information back in the controlfile by performing a reverse resynchronization. This is an automatic process on physical standby databases.

Improved Integration with RMAN

There are numerous improvements made to RMAN integration with Data Guard. The majority of these improvements revolve around the db_unique_name parameter and the ability to maintain site persistence. Improvements relative to RMAN and Data Guard integration include the ability to do the following:

• Restore a standby controlfile from an existing controlfile backup.
• Set up persistent configuration at the site level instead of connecting to a database as a target using the new db_unique_name option.
• Back up, restore, and recover works transparently with any database configuration.
• Back up the spfile based on db_unique_name.
• Define a backup accessibility group.

Compressed Redo Traffic

Oracle Database 11g introduces the capability to compress redo log data as it transports over the network to the standby databases. This is an important feature for Data Guard implementers who have high-latency and low-bandwidth WAN networks. This compressed redo feature can be turned on by enabling the log_archive_dest_1 parameter with the compression parameter. Compression becomes enabled only when a GAP exists and the standby database needs to catch up to the primary database. When a GAP resolution is detected, the redo transport can be enabled to ship logs in compressed mode. This will reduce the network bandwidth. By default, the compression attribute is disabled and is optional. To enable compression of redo transport, you can use the alter system syntax:

SQL> alter system set log_archive_dest_1 = 'SERVICE=DBA11GDR COMPRESSION=ENABLE';

System altered.

Alternatively, you can enable compression using Data Guard Broker by setting the RedoCompression property to enable. In the following example, the RedoCompression property will be set on both the primary and physical standby databases:

DGMGRL> edit database 'dba11g' set property redocompression=enable;

Property "redocompression" updated

On the standby database, you can enable redo compression also:

DGMGRL> edit database 'dba11gdr' set property redocompression=enable;

Property "redocompression" updated

Once you set compression, either through Data Guard Broker or by modifying the log_archive_dest_1 parameter, you can query the V$ARCHIVE_DEST view to confirm that redo log compression is enabled, as shown here: SQL> select dest_name, compression from v$archive_dest;

Redo log compression can also be disabled by using the disable option with Data Guard Broker or by setting the log_archive_dest_1 initialization parameter. When adding a database to the Data Guard configuration, Data Guard Broker will detect the archive destination compression setting and adjust the RedoCompression property.

Note Using network compression with Data Guard redo transport services requires a license for Oracle

Usage of Histograms for the NET_TIMEOUT Attributes

The optional net_timeout parameter to the log_archive_dest_n initialization parameter allows the DBAs to specify the number of seconds the log writer process (LGWR) waits for a response from the logwriter network server (LNS) process before terminating the connection. This parameter by default is set to 30 seconds.

Note Oracle recommends that you set the net_timeout parameter for the Maximum Protection and

Maximum Availability databases.

The net_timeout parameter populates a histogram of response time values for theV$REDO_DEST_RESP_ HISTOGRAM dynamic view. There is one entry for every synchronous redo transport destination. The maximum allowable value for the net_timeout parameter is 1,200 seconds. You can query V$REDO_DEST_RESP_HISTOGRAM to determine the response time for each transport destination. The DEST_ID column correlates to the log_archive_dest_n parameter. The DURATION column can have the following values:

• 1–300(shows actual seconds rounded up)
• 600(301–600 seconds show the value of 600)
• 1,200(601–1,200 seconds show the value of 1,200)
• 2,400(1,201–2,400 seconds show the value of 2,400)
• 4,800(2,401–4,800 seconds show the value of 4,800)
• 9,600(anything greater than 4,801 seconds shows the value of 9,600)

For ease of management, the numbers in this column are reported in seconds only for the first 300 seconds. For all values less than 300 seconds, the DURATION column reports the time rounded to the nearest second. For values larger than 300 seconds, Oracle rounds up to the next increment of 600, 1,200, 2,400, 4,800, or 9,600 seconds.

This is the makeup of the histogram view:

You should carefully observe the FREQUENCY column to review the bucket counts to determine an appropriate value for net_timeout. A high bucket count suggests a good value for the net_timeout attribute.

Here’s a sample query to display a response time histogram for the second archive destination:

You can also determine the fastest response time by using the max function on the DURATION column. Likewise, to determine the slowest response time, you can use the min function on the DURATION column.

Fast-Start Failover for Maximum Performance Mode

In the previous release, fast-start failover was available only when the redo transport was set to synchronous mode. This meant that unless your Data Guard was set up for Maximum Availability, you could not take advantage of fast-start failover. Oracle Data Guard 11g now enables fast-start failover to function for databases that are in Maximum Performance mode using the asynchronous transport mode. Setting up the flashback recovery area is a requirement for Data Guard with Maximum Performance implementations. DBAs can configure the tolerable data loss for fast-start failover in Maximum Performance mode depending on the company’s RPO/RTO.