New Transaction Recovery Monitoring Features - Oracle

Oracle Database 10g offers enhancements to monitoring the rollback of transactions by SMON, and offers historical information about transaction recovery and rollback operations. This allows you to determine how much work remains during recovery operations. This monitoring is supported via the V$FAST_START_TRANSACTIONS view, which provides information about transactions that Oracle Database 10g is recovering as well as transactions that have been recovered. Also, the V$FAST_ START_SERVERS view provides information about recovery operations that the database server is performing.

The following SQL provides a report that allows you to track transaction recovery during instance recovery:

Select state, undoblocksdone, undoblockstotal, cputime
FROM v$fast_start_transactions;

Transactions that are recovered will have a state of RECOVERED, thus these views provide historical information on rollbacks and recoveries. This information is available until the next time the instance is cycled.

NOTE -- Smaller recovery operations do not show up in the views V$FAST_START_TRANSACTIONS and V$FAST_START_SERVERS.

The V$SESSION_LONGOPS view can now be used to monitor transaction rollback operations. This can assist you in determining how long a given rollback operation will take to complete.

New Data Guard Features

In Oracle Database 10g, Data Guard comes with a number of new features that you will want to be aware of. These include

• The ability to apply redo in real time on both physical and logical standby databases

• A new attribute, valid_for

• New features related to transmission of database generated redo

• New parameters associated with Oracle Database 10g standby databases

• Changes to the way that Oracle Database 10g will start a standby database

• The ability of the ARCH process to write directly to the standby redo logs

• The ability to assign threads to standby redo log groups

• Enhancements to Oracle Database 10g logical standby database

Apply Redo in Real Time

Prior to Oracle Database 10g, at best, redo could be shipped to a physical standby database, in real time, and it would be stored in standby redo logs. Unfortunately, that redo would still not be applied to the physical standby database until a log switch occurred on the primary database. Thus, the data in the primary and standby databases was always physically divergent. Logical standby databases were even worse, since they only supported application of redo from archived redo logs on the remote database after a log switch. As a result, not only was the logical standby database data divergent from that of the primary, but the redo data was really at risk too. This lag in applying the redo also has implications with mean time to recover, since the standby has to apply the remaining redo in the standby redo log before it can come up. This can delay the opening of the standby database as the new primary database.

Oracle Database 10g solves both of these problems! Now the log apply services can be configured to apply redo data almost in real time from the standby redo logs. When configured for real-time application, redo will be written to the standby database itself at nearly the same time (by the Managed Recovery Process [MRP] or Logical Standby Process [LSP]) that the primary database LGWR and Remote File System (RFS) processes write the redo to the standby redo log files. Let’s look in a bit more detail at the use of real-time log apply and how it works on both physical and logical standby databases.

NOTE -- The Dataguard Broker also supports real-time apply.

Real-Time Log Apply: Physical Standby Database

If the standby database is a physical standby database, real-time log apply can only occur while the physical database is in log apply mode. If the physical standby database is in read-only mode, then no application of redo data will occur. Once the physical standby database is put back into application mode, the log apply service will catch up and return to real-time apply as quickly as possible.

For a physical standby database, the MRP is responsible for application of the redo log files. The application of the redo log file data occurs once the RFS process has actually written the redo. To start the real-time application process on the standby database, use the alter database recover managed standby database command along with the new using current logfile parameter, as shown in this example:

Alter database recover managed standby database using current logfile;

Real-Time Log Apply: Logical Standby Database

The LSP is responsible for the application of redo on the standby database. Again, once the RFS process has finished writing the redo from the primary database, the LSP will pick up that redo and apply it. To start the real-time application of redo, use the alter database start logical standby command with the new apply immediate clause, as shown in this example:

Alter database start logical standby apply immediate;

Monitoring Real-Time Log Apply

You can determine if the logical standby database is running in real-time apply mode by querying the V$ARCHIVE_DEST_STATUS view on the standby database. The column RECOVERY_MODE will indicate MANAGED REAL TIME APPLY if the standby database is in real-time apply mode.

NOTE -- It is not possible to configure a delay if you are using real-time apply mode. Oracle Database 10g will simply ignore the delay attribute if it is used.