|Oracle® Data Guard Concepts and Administration
10g Release 2 (10.2)
|PDF · Mobi · ePub|
A standby database is a transactionally consistent copy of an Oracle production database that is initially created from a backup copy of the primary database. Once the standby database is created and configured, Data Guard automatically maintains the standby database by transmitting primary database redo data to the standby system, where the redo data is applied to the standby database.
A standby database can be one of two types: a physical standby database or a logical standby database. If needed, either type of standby database can assume the role of the primary database and take over production processing. A Data Guard configuration can include physical standby databases, logical standby databases, or a combination of both types.
A physical standby database is physically identical to the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are identical.
Data Guard maintains a physical standby database by performing Redo Apply. When it is not performing recovery, a physical standby database can be open in read-only mode, or it can be opened temporarily in read/write mode if Flashback Database is enabled.
The physical standby database is maintained by applying redo data from the archived redo log files or directly from standby redo log files on the standby system using the Oracle recovery mechanism. The recovery operation applies changes in redo blocks to data block using the data-block address. The database cannot be opened while redo is being applied.
A physical standby database can be open in read-only mode so that you can execute queries on the database. While opened in read-only mode, the standby database can continue to receive redo data, but application of the redo data from the log files is deferred until the database resumes Redo Apply.
Although the physical standby database cannot perform both Redo Apply and be opened in read-only mode at the same time, you can switch between them. For example, you can perform Redo Apply, then open it in read-only mode for applications to run reports, and then change it back to perform Redo Apply to apply any outstanding archived redo log files. You can repeat this cycle, alternating between Redo Apply and read-only, as necessary.
The physical standby database is available to perform backups. Furthermore, the physical standby database will continue to receive redo data even if archived redo log files or standby redo log files are not being applied at that moment.
A physical standby database can also be opened for read/write access for purposes such as creating a clone database or for read/write reporting. While opened in read/write mode, the standby database does not receive redo data from the primary database and cannot provide disaster protection.
The physical standby database can be opened temporarily in read/write mode for development, reporting, or testing purposes, and then flashed back to a point in the past to be reverted back to a physical standby database. When the database is flashed back, Data Guard automatically synchronizes the standby database with the primary database, without the need to re-create the physical standby database from a backup copy of the primary database.
See Also:Section 12.6 for usage examples
A physical standby database provides the following benefits:
Disaster recovery and high availability
A physical standby database enables a robust and efficient disaster recovery and high availability solution. Easy-to-manage switchover and failover capabilities allow easy role reversals between primary and physical standby databases, minimizing the downtime of the primary database for planned and unplanned outages.
Using a physical standby database, Data Guard can ensure no data loss, even in the face of unforeseen disasters. A physical standby database supports all datatypes, and all DDL and DML operations that the primary database can support. It also provides a safeguard against data corruptions and user errors. Storage level physical corruptions on the primary database do not propagate to the standby database. Similarly, logical corruptions or user errors that cause the primary database to be permanently damaged can be resolved. Finally, the redo data is validated when it is applied to the standby database.
Reduction in primary database workload
Oracle Recovery Manager (RMAN) can use physical standby databases to off-load backups from the primary database saving valuable CPU and I/O cycles. The physical standby database can also be opened in read-only mode for reporting and queries.
The Redo Apply technology used by the physical standby database applies changes using low-level recovery mechanisms, which bypass all SQL level code layers; therefore, it is the most efficient mechanism for applying high volumes of redo data.
A logical standby database is initially created as an identical copy of the primary database, but it later can be altered to have a different structure. The logical standby database is updated by executing SQL statements. This allows users to access the standby database for queries and reporting at any time. Thus, the logical standby database can be used concurrently for data protection and reporting operations.
Data Guard automatically applies information from the archived redo log file or standby redo log file to the logical standby database by transforming the data in the log files into SQL statements and then executing the SQL statements on the logical standby database. Because the logical standby database is updated using SQL statements, it must remain open. Although the logical standby database is opened in read/write mode, its target tables for the regenerated SQL are available only for read-only operations. While those tables are being updated, they can be used simultaneously for other tasks such as reporting, summations, and queries. Moreover, these tasks can be optimized by creating additional indexes and materialized views on the maintained tables.
A logical standby database has some restrictions on datatypes, types of tables, and types of DDL and DML operations. Section 4.1.1 describes the unsupported datatypes and storage attributes for tables.
Efficient use of standby hardware resources
A logical standby database can be used for other business purposes in addition to disaster recovery requirements. It can host additional database schemas beyond the ones that are protected in a Data Guard configuration, and users can perform normal DDL or DML operations on those schemas any time. Because the logical standby tables that are protected by Data Guard can be stored in a different physical layout than on the primary database, additional indexes and materialized views can be created to improve query performance and suit specific business requirements.
Reduction in primary database workload
A logical standby database can remain open at the same time its tables are updated from the primary database, and those tables are simultaneously available for read access. This makes a logical standby database an excellent choice to do queries, summations, and reporting activities, thereby off-loading the primary database from those tasks and saving valuable CPU and I/O cycles.
You can use the following interfaces to configure, implement, and manage a Data Guard configuration:
Oracle Enterprise Manager
Enterprise Manager provides a GUI interface for the Data Guard broker that automates many of the tasks involved in creating, configuring, and monitoring a Data Guard environment. See Oracle Data Guard Broker and the Oracle Enterprise Manager online Help for information about the GUI and its wizards.
SQL*Plus Command-line interface
Several SQL*Plus statements use the
STANDBY keyword to specify operations on a standby database. Other SQL statements do not include standby-specific syntax, but they are useful for performing operations on a standby database. See Chapter 15 for a list of the relevant statements.
Several initialization parameters are used to define the Data Guard environment. See Chapter 13 for a list of the relevant initialization parameters.
Data Guard broker command-line interface (DGMGRL)
The DGMGRL command-line interface is an alternative to using Oracle Enterprise Manager. The DGMGRL command-line interface is useful if you want to use the broker to manage a Data Guard configuration from batch programs or scripts. See Oracle Data Guard Broker for complete information.
All members of a Data Guard configuration must run an Oracle image that is built for the same platform.
For example, this means a Data Guard configuration with a primary database on a 32-bit Linux on Intel system can have a standby database that is configured on a 32-bit Linux on Intel system. However, a primary database on a 64-bit HP-UX system can also be configured with a standby database on a 32-bit HP-UX system, as long as both servers are running 32-bit images.
The hardware (for example, the number of CPUs, memory size, storage configuration) can be different between the primary and standby systems.
If the standby system is smaller than the primary system, you may have to restrict the work that can be done on the standby system after a switchover or failover. The standby system must have enough resources available to receive and apply all redo data from the primary database. The logical standby database requires additional resources to translate the redo data into SQL statements and then execute the SQL on the logical standby database.
The operating system running on the primary and standby locations must be the same, but the operating system release does not need to be the same. In addition, the standby database can use a different directory structure from the primary database.
Oracle Data Guard is available only as a feature of Oracle Database Enterprise Edition. It is not available with Oracle Database Standard Edition. This means the same release of Oracle Database Enterprise Edition must be installed on the primary database and all standby databases in a Data Guard configuration.
Note:It is possible to simulate a standby database environment with databases running Oracle Database Standard Edition. You can do this by manually transferring archived redo log files using an operating system copy utility or using custom scripts that periodically send archived redo log files from one database to the other. The consequence is that this configuration does not provide the ease-of-use, manageability, performance, and disaster-recovery capabilities available with Data Guard.
Using Data Guard SQL Apply, you will be able to perform a rolling upgrade of the Oracle database software from patch set release n (minimally, this must be release 10.1.0.3) to the next database 10.1.0.(n+1) patch set release. During a rolling upgrade, you can run different releases of the Oracle database on the primary and logical standby databases while you upgrade them, one at a time. For complete information, see Chapter 11, "Using SQL Apply to Upgrade the Oracle Database" and the ReadMe file for the applicable Oracle Database 10g patch set release.
COMPATIBLE initialization parameter must be set to the same value on all databases in a Data Guard configuration.
If you are currently running Oracle Data Guard on Oracle8i database software, see Oracle Database Upgrade Guide for complete information about upgrading to Oracle Data Guard.
The primary database must run in ARCHIVELOG mode. See Oracle Database Administrator's Guide for more information.
The primary database can be a single instance database or a multi-instance Real Application Clusters database. The standby databases can be single instance databases or multi-instance Real Application Clusters (RAC) databases, and these standby databases can be a mix of both physical and logical types. See Oracle Database High Availability Overview for more information about configuring and using Oracle Data Guard with RAC.
Each primary database and standby database must have its own control file.
If a standby database is located on the same system as the primary database, the archival directories for the standby database must use a different directory structure than the primary database. Otherwise, the standby database may overwrite the primary database files.
To protect against unlogged direct writes in the primary database that cannot be propagated to the standby database, turn on
FORCE LOGGING at the primary database before performing datafile backups for standby creation. Keep the database in
FORCE LOGGING mode as long as the standby database is required.
The user accounts you use to manage the primary and standby database instances must have
SYSDBA system privileges.
Oracle recommends that when you set up Oracle Automatic Storage Management (ASM) and Oracle Managed Files (OMF) in a Data Guard configuration, set it up symmetrically on the primary and standby database. That is, if any database in the Data Guard configuration uses ASM, OMF, or both, then every database in the configuration should use ASM, OMF, or both, respectively. See the scenario in Section 12.12 for more information.
Note:Because some applications that perform updates involving time-based data cannot handle data entered from multiple time zones, consider setting the time zone for the primary and remote standby systems to be the same to ensure the chronological ordering of records is maintained after a role transition.
The directory structure of the various standby databases is important because it determines the path names for the standby datafiles, archived redo log files, and standby redo log files. If possible, the datafiles, log files, and control files on the primary and standby systems should have the same names and path names and use Optimal Flexible Architecture (OFA) naming conventions. The archival directories on the standby database should also be identical between sites, including size and structure. This strategy allows other operations such as backups, switchovers, and failovers to execute the same set of steps, reducing the maintenance complexity.
Otherwise, you must set the filename conversion parameters (as shown in Table 2-1) or rename the datafile. Nevertheless, if you need to use a system with a different directory structure or place the standby and primary databases on the same system, you can do so with a minimum of extra administration.
The three basic configuration options are illustrated in Figure 2-1. These include:
A standby database on the same system as the primary database that uses a different directory structure than the primary system. This is illustrated in Figure 2-1 as
If you have a standby database on the same system as the primary database, you must use a different directory structure. Otherwise, the standby database attempts to overwrite the primary database files.
A standby database on a separate system that uses the same directory structure as the primary system. This is illustrated in Figure 2-1 as
Standby2. This is the recommended method.
A standby database on a separate system that uses a different directory structure than the primary system. This is illustrated in Figure 2-1 as
Note:if any database in the Data Guard configuration uses ASM, OMF, or both, then every database in the configuration should use ASM, OMF, or both, respectively. See Chapter 12 for a scenario describing how to set up OMF in a Data Guard configuration.
Table 2-1 describes possible configurations of primary and standby databases and the consequences of each. In the table, note that the service name defaults to the concatenation of the
DB_DOMAIN initialization parameters. You must specify a unique value for the
DB_UNIQUE_NAME initialization parameter when more than one member of a Data Guard configuration resides on the same system. Oracle recommends that the value of the
DB_UNIQUE_NAME initialization parameter always be unique, even if each database is located on a separate system.
|Standby System||Directory Structure||Consequences|
Same as primary system
Different than primary system (required)
Same as primary system
Different than primary system
The most crucial structures for Data Guard recovery operations are online redo logs, archived redo logs, and standby redo logs. Redo data transmitted from the primary database is received by the remote file server (RFS) process on the standby system where the RFS process writes the redo data to archived log files or standby redo log files. Redo data can be applied either after the redo is written to the archived redo log file or standby redo log file, or, if real-time apply is enabled, directly from the standby redo log file as it is being filled.
This documentation assumes that you already understand the concepts behind online redo logs and archived redo logs. Section 2.5.1 supplements the basic concepts by providing information that is specific to Data Guard configurations. Section 2.5.2 provides detailed information about using standby redo log files.
The transmission of redo is integral to maintaining the transactional consistency of the primary and standby databases. Both online redo logs and archived redo logs are required in a Data Guard environment:
Every instance of an Oracle primary database and logical standby database has an online redo log to protect the database in case of an instance failure. Physical standby databases do not use an online redo log, because physical standby databases are not opened for read/write I/O. Changes are not made to the physical standby database and new redo data is not generated.
An archived redo log is required because archiving is the method used to keep standby databases transactionally consistent with the primary database. Primary databases, and both physical and logical standby databases all use an archived redo log. Oracle databases are set up, by default, to run in ARCHIVELOG mode so that the archiver (ARCn) process automatically copies each filled online redo log file to one or more archived redo log files.
Unlike physical standby databases, logical standby databases are open databases that generate redo data and have multiple log files, including online redo log files, archived redo log files, and standby redo log files (if configured).
Both the size of the online redo log files and the frequency with which a log switch occurs can affect the generation of the archived redo log files at the primary site. The Oracle Database High Availability Overview provides recommendations for log group sizing.
An Oracle database will attempt a checkpoint at each log switch. Therefore, if the size of the online redo log file is too small, frequent log switches lead to frequent checkpointing and negatively affect system performance on the standby database.
See Also:Oracle Database Administrator's Guide for more details about configuring redo logs, archive logs, and log groups
A standby redo log is similar to an online redo log, except that a standby redo log is used to store redo data received from another database.
A standby redo log is required if you want to implement:
Real-time apply (described in Section 6.2)
Cascaded destinations (described in Appendix E)
A standby redo log provides a number of advantages:
Standby redo log files can be multiplexed using multiple members, improving reliability over archived log files.
During a failover, Data Guard can recover and apply more redo data from standby redo log files than from the archived log files alone.
The archiver (ARCn) process or the log writer (LGWR) process on the primary database can transmit redo data directly to remote standby redo log files, potentially eliminating the need to register a partial archived log file (for example, to recover after a standby database crashes). See Chapter 5 for more information.
Section 3.1.3 describes how to configure standby redo log files.