Hey, did you catch that redirect? The Toad World URL is now community.toadworld.com. Don't worry -- you'll still find all the same great content here (and more on the way). We're just in the process of giving our Toad World site some well-deserved love. Stay tuned for some more updates coming this way.
Meanwhile, enjoy community.toadworld.com.
Oracle 10g introduces several new features in Data Guard, including:
Prior to Oracle 10g, redo could only 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. This resulted in a situation where 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 at risk as well. 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 10g introduced solutions to both of these problems. Oracle 10g allows the log apply services to 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. Note that 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
It is possible to 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 will simply ignore the delay attribute if it is used.
The valid_for attribute, introduced in Oracle 10g, can be associated with the log_archive_dest_n parameter. This attribute makes it possible to define when an archive log destination will be used as well as the role of the archived redo at that destination.
The valid_for attribute takes two parameters - archival_source parameter and database_role. The archival_source parameter defines when the destination is used. Valid settings for this parameter are as follows:
The database_role parameter can be used to define the role of the database that the parameter is associated with and, thus, when the archive log destination will be used. Valid settings for this parameter are as follows:
The two parameters above may be specified in any order, but only specific combinations are valid for the attribute. For example, the combination of STANDBY_LOGFILE and PRIMARY_ROLE is invalid and will generate an error at database startup.
Here is an example of an archivelog destination having been set, using the valid_for attribute of the log_archive_dest_1 parameter:
VALID_FOR = (STANDBY_LOGFILE, STANDBY_ROLE)
Data Dictionary Views and valid_for
The VALID_NOW column of the V$ARCHIVE_DEST data dictionary view indicates if the archivelog destination will be used. Valid values include:
The V$ARCHIVE_DEST view provides two additional columns, VALID_TYPE and VALID_ROLE, that allow you to see how the valid_for parameter for each archivelog destination is configured.
Oracle 10g provides two enhancements with regard to redo transmission. First, database authentication must now be set up for all databases. This means that the remote_login_passwordfile parameter must be set to either SHARED or EXCLUSIVE at both the primary and all standby sites. A password file must also be generated, and all SYS passwords must be set the same at all sites. If you change the SYS password, it will be automatically changed at all sites.
Oracle 10g also supports encryption of the redo stream to the standby databases as an optional feature. The Oracle Advanced Security option must be installed at all database sites and Oracle Net for encryption and integrity checksumming must be configured.
Oracle 10g deprecates the lock_name_space parameter in favor of the db_unique_name parameter. Going forward, you should use the db_unique_name parameter to assign unique names to each of your standby databases. The name can be up to 30 characters long, and each Real Application Clusters instance should use the same name.
Note: The lock_name_space parameter is still available for use in Oracle 10g.
The remote_archive_enable parameter is replaced with the log_archive_config parameter (Oracle recommends replacing remote_archive_enable with log_archive_config in Oracle 10g). The log_archive_config parameter allows you to define the standby database configuration currently in use, and update it dynamically. The db_unique_name parameter contains the name of each database in the standby database configuration, and then defines the role in the configuration as one of the following four values:
The log_archive_config parameter should be the same for each Real Application Clusters instance. Also, the log_archive_config parameter has an attribute, db_config, that lists all databases in the standby database configuration. You can dynamically add databases to the configuration by changing this setting dynamically. This eliminates the need to shut down the database when running in maximum availability or maximum protection mode.
The following is an example of the configuration of the log_archive_config parameter, using the db_config parameter:
In earlier versions of Oracle, you would first need to start the database instances (startup nomount) and then either mount it as a standby database and start managed recovery, or open the database in read-only mode.
In Oracle 10g, if you issue the startup mount command, Oracle reads the database control file and, if the database is a standby database, mounts the database as a standby database in preparation for managed recovery to be started. It is still necessary to start managed recovery. In addition, if the startup command has been issued and the database is a standby database, it will open the standby database in read-only mode.
In Oracle 10g, the ARCH process has the ability to write to standby redo logs. This helps with the registration of partially archived redo logs and allows for the configuration of an almost unlimited number of cascaded redo log destinations.
In Oracle 10g, it is now possible to assign standby redo logs to specific redo threads when running a Real Application Clusters configuration. This is supported with the new thread parameter of the alter database add standby logfile command. The assignment of a thread is optional, however, and Oracle will assign the standby redo log to a thread as required.
Oracle 10g introduces a number of improvements to logical standby databases. These include:
Instantiate a Logical Standby Database with Zero Downtime
Prior to Oracle 10g, instantiation of a logical standby database would likely require an outage of the primary database, because the primary database would need to be quiesced, an operation that required that Resource Manager be enabled at database startup. Since many production databases operate without Resource Manager enabled, this would require a cycle of the database. Also, a quiesce of a database could take a considerable amount of time, particularly in databases with a great deal of activity. Oracle 10g removes the requirement to quiesce the database before making the online backup that is the source of the logical standby database. This makes it possible to create the logical standby database without any downtime at all. This change is supported by changes to the standby control file.
The following is a, overview of the steps necessary to create a logical standby database. For more complete information on this procedure, see the Oracle document "Oracle Data Guard Concepts and Administration". The basic steps are as follows:
After the backup is complete, create a logical standby database control file on the primary by using the alter database create standby logical control file command:
Alter database Create standby logical control file AS
Copy the backed-up datafiles, archived redo logs, and the logical standby control file to the location where the logical standby database will be created.
Restore the database at the standby site with the logical standby control file. Do not open the database.
Configure log transport services on the primary database so that it will ship redo to the logical standby database.
Start managed recovery on the standby database with the alter database command:
Alter database recover managed standby database;
Note that this is the same method of recovering a physical standby database.
Activate the standby database with the alter database command:
Alter database activate standby database;
Using the DBNEWID program, change the DBNAME and DBID of the standby database. Follow the instructions in the Oracle 10g Database Utilities guide on how to do this.
Start the logical standby database log application services:
Alter database start logical standby apply;
Once these steps are complete, you have created a logical standby database.
Logical Standby Database Support for Maximum Protection Mode
Previously, logical standby databases did not support maximum protection mode. This implied that there was always some level of data divergence between the primary and the logical standby database, which meant that there was a risk of data loss during an unplanned switchover operation.
In Oracle 10g it is possible to configure a logical standby database in maximum protection mode. Standby redo logs can be created for a logical standby database, which is required for maximum protection mode, and the primary database can be configured to send redo to the logical standby database in maximum protection mode.
New SQL Apply Support for Data Types
In Oracle 10g, SQL Apply supports a number of new data types, including CLOB, NCLOB, LONG, and LONG_RAW. Also, two new 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
Oracle 1og introduces the prepare to switchover to command to expedite switchover operations to logical standby databases. 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 command is executed, it should be followed by the alter database commit to switchover command.
The process to switch over between primary and standby mode is different in Oracle 10g as a result of these new commands. Review the Oracle documentation ("Oracle Data Guard Concepts and Administration") for more detail on this procedure. The following steps provide a brief summary:
Note: After the prepare switchover commands have been issued, the commit to switchover operations should be completed as soon as possible. The longer the delay, the longer the actual switchover will take.
New Data Dictionary Views to Manage Standby Databases
Oracle 10g offers new and updated data dictionary views to assist the DBA in managing standby databases, including:
DBA_LOGSTDBY_LOG - Allows you to determine which archived redo logs have been applied to the standby database.
DBA_LOGSTDBY_PROGRESS - Oracle 10g introduces new columns that provide more detail on the progress of the SQL Apply service. These columns include:
Bypassing the SQL Apply Services to Make Changes to the Logical Standby Database
In order to make changes to the logical standby database (adding indexes, etc.), it is necessary to bypass the Data Guard process. Commands that supercede the use of the Oracle-supplied procedures dbms_logstdby.guard_bypass_on and guard_bypass_off are available in Oracle 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 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.