New SQL Apply Support for Data Types - Oracle

SQL Apply now supports a number of new data types. These include CLOB, NCLOB, LONG, and LONG_RAW. Also, two new Oracle Database 10g data types, BINARY_FLOAT and BINARY_DOUBLE, are supported by SQL Apply. SQL Apply also supports tables with columns set to UNUSED. Note that index organized tables with overflow segments or BLOB columns are still not supported.

Optimized Switchover Operations on Logical Standby Databases

Switchover operations to logical standby databases are optimized with the new prepare to switchover to command. With the alter database prepare to switchover to primary command, the logical standby database will proceed to build the Log Miner dictionary before the actual switchover operation occurs from a standby to a primary configuration. The alter database prepare to switchover to standby command notifies the primary database that it will soon find itself converted to a standby role. After the alter database prepare to switchover command is executed, it should be followed by the alter database commit to switchover command.

As a result of these new commands, the process to switch over between primary and standby mode has changed. I recommend that you look at the Oracle documentation (“Oracle Data Guard Concepts and Administration”) for more detail on this procedure (as it may change in interim releases of the database). However, in summary, the new steps are as follows:

1. Issue the alter database prepare switchover to standby command on the primary database.

2. Issue the alter database prepare to switchover to primary command on the logical standby database. Watch for any errors that might be raised by Oracle Database 10g during the execution of this command.

3. On the primary database, start the switchover operation via the alter database commit to switchover to logical standby command.

4. On the logical standby database, after the apply commits, finish the switchover operation with the alter database commit to switchover command.

5. On the new logical standby database, start the SQL Apply service using the alter database start logical standby apply command.

NOTE -- After you have used the prepare switchover commands, you should complete the commit to switchover operations as soon as possible. The longer you wait, the longer the actual switchover will take.

New Data Dictionary Views to Manage Standby Databases

Oracle Database 10g offers new and updated data dictionary views to assist you in managing your standby database. These include

• DBA_LOGSTDBY_UNSUPPORTED This view defines unsupported object storage attributes.

• DBA_LOGSTDBY_LOG This view allows you to determine which archived redo logs have been applied to the standby database.

• DBA_LOGSTDBY_PROGRESS This view has new columns that provide more detail on the progress of the SQL Apply service. These columns include:
  • APPLIED_SEQUENCE# Sequence number of the log that contains the APPLIED_SCN column, which existed prior to Oracle Database 10g

  • APPLIED_THREAD# Thread number for the log associated with the APPLIED_SCN column

  • READ_SEQUENCE# Sequence number of the log associated with the READ_SCN column

  • READ_THREAD# Thread number of the log that is associated with the READ_SCN column

  • NEWEST_SEQUENCE# Sequence number of the log associated with the NEWEST_SCN column

  • NEWEST_THREAD# Thread number of the log that is associated with the NEWEST_SCN column

Bypassing the SQL Apply Services to Make Changes to the Logical Standby Database

If you need to make changes to your logical standby database (adding indexes, etc.), you need to bypass the Data Guard process. New commands that supercede the use of the Oracle-supplied procedures dbms_logstdby.guard_bypass_on and guard_ bypass_off are available in Oracle Database 10g. The new commands are alter session enable guard and alter session disable guard.

Skipping a Failed Transaction

In many cases, if the SQL Apply operation halts, all you really want to do is skip the transaction that failed and continue on with the next transaction. Oracle Database 10g makes this easy with the skip failed transaction option of the alter database start standby apply command. This command is identical to the dbms_logstby.skip_ transaction stored procedure, but will find the transaction automatically and then restart the SQL Apply operation.