Logical Standby Improvements

10g introduces many new enhancements to logical standby databases. New features discussed in this chapter include the following:

• Zero downtime for instantiation

• Support for redo log files

• The PREPARE TO SWITCHOVER command

• Support for SQL Apply services

• Rolling database upgrades using SQL Apply

• Enhanced commands and views that support logical standby database management

Zero Outage for Instantiation

In 10g Release 1 RMAN does not support the new logical standby control file.

Prior to Oracle 10g, the major limitation with logical standby was that DBAs had to accept some type of outage in order to complete the build. The creation of the logical standby required the primary database to be shut down completely or required a quiesce of the database via Resource Manager. A clean shutdown was necessary to ensure that there were no in-flight transactions running during the time of the dictionary build on the primary; however, any downtime on your primary is not the best case. The alternative, a quiesce of the database via Resource Manager, required your instance to have an active Resource Manager in place, and then required the database to be placed in quiesce mode. Due to the load on some systems, quiescing a primary database could run for days without completion, and could cause errors. Now, with 10g, DBAs are not required to shut down the primary or use the quiesce command. DBAs can now use the new logical standby control file without having to experience any downtime to build the logical standby.

Following are the steps DBAs should take in Oracle 10g to build a logical standby:

1.	Create an online (hot) backup of your primary database and ensure you have all archive logs from all threads through the end of the backup process. In previous versions, you were required to do a quiesce of the database at the end of a hot backup to record the SCN (system change number). This was necessary because you needed to have the recovery portion end during a time when the database was not active. Then you would start the SQL Apply service. With the new 10g logical standby control file, however, the quiesce and SCN snapshot are no longer needed.
2.	Create a logical standby control file on your primary database. (A logical standby control file is like a physical standby control file, with the key difference that database guard is set to ALL by default. This prevents anyone from making changes that might corrupt the logical standby.) SQL> alter database create logical standby control file as 'LOGICAL_STDBY.ctl';
3.	Copy all necessary filesincluding the backup, archive log files, and logical standby control fileto the standby host.
4.	Restore your backup database to mount stage using the logical standby control file. Do not open the database.
5.	On the primary database, set up Log Transport services.
6.	Place the standby database in recover managed mode. The recovery is the same as it would be for a physical standby. The recovery end point is governed by the information in the logical standby control file. SQL> alter database recover managed standby database
7.	When the recovery is complete, activate the logical standby database. When you activate the database, it is a new database. SQL> alter database activate standby database
8.	On the logical standby, use DBNEWID to change the DBNAME and DBID. To begin, shut down the database using the IMMEDIATE option; then, start up and mount the database.
9.	Run NID (New Database ID utility introduced in Oracle 9.2) to change your DBNAME and DBID settings. When you're finished, shut down the database, restart it, and open the database with reset logs again. $> nid target=sys/password@standby_db When using the NID utility in a RAC environment, the database must be mounted in NOPARALLEL modethat is, set the initialization parameter CLUSTER_DATABASE=FALSE.
10.	On the logical standby, add the necessary temporary tablespaces, and start the SQL Apply services: SQL> alter database start logical standby apply immediate

You now have a fully functioning logical standby database. By default, the logical standby is in maximum performance mode.

As a review, the Oracle Data Guard configuration has three specific data-protection modes to meet your business needs: Maximum Protection, Maximum Availability, or Maximum Performance. Maximum Protection mode guarantees that no data loss will occur if the primary database fails by committing data on the primary only after it is committed on the standby. Maximum Availability mode provides the same protection as Maximum Protection, but the primary will not shut down if communications with the standby are lost. Instead, the communications will operate in a Resync mode until all gaps are corrected. Maximum Performance mode uses the online or archive redo log files to propagate changes from the primary to the standby asynchronously. For more information about the Data Guard protection modes, please review the Data Guard Concepts and Administration documentation.

Support for Redo Log Files

Oracle 10g introduces new standby redo log support for logical standby databases. With this support, it is possible for a logical standby database to support the Maximum Protection and Maximum Availability modes. Because standby redo logs survive failures, it is no longer necessary to register partial archive log files if a failure occurs. Logical standby redo logs use the RFS process for redo shipment.

The PREPARE TO SWITCHOVER Command

Starting with 10g, you now have the ability to build the LogMiner dictionary before you need to perform a switchover operation. The new PREPARE TO SWITCHOVER TO [PRIMARY | LOGICAL STANDBY] command optimizes the switchover operations to enable this new behavior. After the LogMiner dictionary build has completed, you can use the COMMIT TO SWITCHOVER statement to switch the roles of the primary and logical standby databases. Here's how it's done:

1.	Execute the PREPARE TO SWITCHOVER command on the primary database. This command enables the primary database to accept redo information from one of its logical standby members; that said, the primary will not yet apply the redo information. When this command is executed, you can observe the value of PREPARING SWITCHOVER in the SWITCHOVER_STATUS column in V$DATABASE view: SQL> alter database prepare to switchover to logical standby;
2.	Execute the PREPARE TO SWITCHOVER command on the logical standby database. This will build and send the LogMiner dictionary to the primary. SQL> alter database prepare to switchover to primary;
3.	Execute the COMMIT TO SWITCHOVER command on the primary database: SQL> alter database commit to switchover to logical standby;
4.	Execute the COMMIT TO SWITCHOVER command on the logical standby database. SQL> alter database commit to switchover to primary;
5.	When the commit is complete, start the SQL Apply services on the new logical standby (the original primary is the new logical standby): SQL> alter system archivelog current; SQL> alter database start logical standby apply;

Support for SQL Apply Services

Oracle 10g expands the benefits of using SQL Apply services on your logical standby database. For one, SQL Apply now supports multibyte CLOB, NCLOB, LONG, LONG_RAW, BINARY_FLOAT, and BINARY_DOUBLE data types. This new datatype support allows the logical standby database to process a wider variety of data. SQL Apply also supports indexed organized tables (IOTs), although these IOTs cannot contain overflow segments of LOB columns. Also, tables with unused columns are now supported with the new 10g SQL Apply.

Rolling Database Upgrades with SQL Apply

Oracle 10g introduces the foundation for rolling upgrades within the SQL Apply services to eliminate any downtime previously associated with this maintenance operation. For example, using SQL Apply and logical standby databases, you can upgrade the Oracle Database software from patch set release 10.1.0.n to the next database 10.1.0.(n+1) patch set release. With 10.1.0.2, this process cannot be invoked, but you can still apply patches with a logical or physical standby in place following the steps outlined later in this chapter in the section titled "Applying Patch Sets with Data Guard in Place." Only specific patch sets can be used with this online patching method, so you will need to check the patch README file to see whether your patch is a valid candidate.

Enhanced Logical Standby Commands and Views

In prior versions of Data Guard, you needed to explicitly skip the transaction and then restart the SQL Apply service. Now starting with Oracle 10g, the updated ALTER DATABASE START LOGICAL STANDBY APPLY command offers the SKIP FAILED TRANSACTION clause to automatically skip any failed transactions to start the SQL Apply service.

 SQL> alter database start logical standby apply skip failed transaction; 

DBAs should use the new SKIP FAILED TRANSACTION clause with caution. Skipping DDL operations is fine as long as you are able to reproduce it manually and all dependencies are not affected. If you ever skip a DML operation, however, you may make your logical standby unusable.

If you do have to make any change on the logical standby, such as adding indexes or modifying tables that are not maintained by SQL Apply, 10g allows you to enable or disable the Data Guard process at the session level.

To disable the Data Guard process, issue this command:

 SQL> alter session disable guard; 

To enable the process, issue this command:

 SQL> alter session enable guard; 

10g offers several new updates to its Data Dictionary tables and views to support logical standby processing. Following is a summary of a few of the changes:

• You can now view all unsupported storage attributes with the new column ATTRIBUTES in DBA_LOGSTDBY_UNSUPPORTED:

  [View full width]
   SQL> select distinct table_name, attributes from dba_logstdby_unsupported where owner =  'TSDATA'; TABLE_NAME       ATTRIBUTES ---------------- ------------ USERS            Segment Compression DOCUEMNTS        Segment Compression DOC_COMPONENTS   Index Organized Table 

  
  

• Several new columns in DBA_LOGSTDBY_PROGRESS reveal details on the progress of the SQL Apply service in your standby database:

  [View full width]
   SQL> select applied_scn, applied_thread#, newest_scn, newest_thread# from  dba_logstdby_progress; APPLIED_SCN       APPLIED_THREAD#  NEWEST_SCN     NEWEST_THREAD# ----------------  ------------     ----------     -------------- 34872                   2             34872             2 

  
  

• Using the new APPLIED column in the DBA_LOGSTDBY_LOG view, you can now easily see which logs have been applied to your logical standby database:

   SQL> select thread#, sequence#, applied from dba_logstdby_log order by sequence; THREAD#  SEQUENCE#     APPLIED -------  --------      -------       1         51     YES       1         52     YES       1         53     YES       1         54     CURRENT       1         55     CURRENT