|Oracle9i Data Guard Concepts and Administration
Release 2 (9.2)
Part Number A96653-01
To reduce the load on your primary system, you can implement cascading standby databases. A cascading standby database is a standby database that receives its redo logs from another standby database, not from the original primary database. You can configure:
This appendix contains the following sections:
To help you understand cascading standby databases and how to configure them, this section provides background information about configuring standby databases in the Data Guard environment.
A Data Guard configuration typically contains a primary database and a remote standby database. As a part of setting up the Data Guard configuration, you must define and enable the
n (where n is a number from 1 to 10) initialization parameter on the primary database. Once this destination is enabled, log transport services on the primary database send the redo logs to be applied on the standby database. You configure the destination attributes the same way for physical or logical standby databases, and you can continue to set up additional standby databases until you have the maximum of nine remote standby databases.
To create a seamless environment in which standby databases continue to operate through a switchover or failover operation, define the destination parameters on the various standby databases to point back to the primary database. In addition, you can configure the
n initialization parameter:
On the primary database, you have three standby databases that are configured as follows:
LOG_ARCHIVE_DEST_2='SERVICE=PHYSICAL1' LOG_ARCHIVE_DEST_3='SERVICE=PHYSICAL2' LOG_ARCHIVE_DEST_4='SERVICE=LOGICAL3'
On the physical standby database called
PHYSICAL1, you need to define the following:
LOG_ARCHIVE_DEST_2='SERVICE=PRIMARY' LOG_ARCHIVE_DEST_3='SERVICE=PHYSICAL2' LOG_ARCHIVE_DEST_4='SERVICE=LOGICAL3'
On the physical standby database called
PHYSICAL2, you need to define the following:
LOG_ARCHIVE_DEST_2='SERVICE=PRIMARY' LOG_ARCHIVE_DEST_3='SERVICE=PHYSICAL1' LOG_ARCHIVE_DEST_4='SERVICE=LOGICAL3'
On the logical standby database called
LOGICAL3, you need to define the following:
With the configuration defined in this way, a switchover operation from the primary database to a physical standby database will allow that standby database, as the new primary database, to send new redo log information to all of the other standby databases. Performing a switchover operation to a logical standby database renders the other physical standby databases unable to accept redo logs from the new primary database in this case. However, other logical standby databases can accept the redo logs from the new primary database.
During a failover operation, these parameters are even more important as they enable the system to automatically send the final redo log (which contains the end-of-redo marker) to the other standby databases, allowing them to accept new redo information from the new primary database. For example, if you fail over to
PHYSICAL1 when it finishes applying redo logs,
PHYSICAL1 can send the redo log containing the end-of-redo marker to
LOGICAL3 to signal that a new primary database will soon begin sending redo logs.
Although you can define up to nine remote standby databases per primary database, each standby destination that must be sent redo logs puts additional load on the primary system. You can reduce this load by using the archiving process (
ARCH) transport method or the log writer (
LGWR) with asynchronous (
ASYNC) network protocol, but these methods increase the possibility of missing data in a failure situation. You could also define the
DEPENDENCY attribute for some destinations, but that requires one of the following:
The following sections describe how to set up the Data Guard configuration to use cascading standby databases:
To enable a physical standby database to send the incoming redo logs to another set of destinations, you must define the following items:
ninitialization parameter on the primary database to set up a physical standby database that will be the starting point for a cascade to use the
LGWRtransport method. Use either
ASYNCnetwork protocols depending on your requirements.
At this point, you can begin defining the
n initialization parameter on the physical standby database that will define the end points of the cascade. Remember, as part of the original setup of the physical standby database, you should have defined a local archive destination that will be used for local archiving when the physical standby database transitions to the primary role. For example, you might define the
LOG_ARCHIVE_DEST_1 initialization parameter to be the
`LOCATION=/physical1/arch' location. When the physical standby database switches roles, any archived redo logs will be put into that directory with the same format that you defined with the
LOG_ARCHIVE_FORMAT initialization parameter. This local archiving destination can be the same as the one defined in the parameter
STANDBY_ARCHIVE_DEST, but this is not required.
A side effect of this configuration is that the archive process on the standby database will now try to send the log files not only to the cascading end points but also to the other standby databases and the primary database if they are defined and enabled. This is not a problem, because the receiving database will either reject it if it is the primary database or a standby database that has already received the same log successfully. If the destination is another standby database and it has not received the log successfully, then this acts as a proactive gap resolution. You can avoid this by setting the state to
DEFER for any destinations not involved in the cascade. However, you will have to remember to enable them again if you do a switchover or failover operation.
If you want to have one initialization parameter file to handle both the cascading standby databases and the original primary and standby configuration, define the destinations for the primary database and other standby databases as well as the cascading standby databases. However, the total remote destinations still cannot exceed 10, including the local archiving destination.
Because it is the archiver process and not the log writer process that (during the archiving of the standby online redo logs) is sending the redo information to the cascading standby databases, you are limited to one set of cascading standby databases per standby database that is connected directly to the primary database.
A logical standby database is similar to a primary database in that you can define and create standby databases for it in the same manner as for the original configuration. However, unlike cascading physical standby databases, logical standby databases will not be exact copies of the original primary database.
Cascading standby databases from a logical standby database require that you perform the same setup tasks as for a normal physical or logical standby database. You can use any transport mode (
ARCH) and network protocol (
ASYNC). If you use the
LGWR network protocol, you can optionally use standby online redo logs on your physical standby databases.
The following scenarios demonstrate set up and uses for cascading standby databases.
You have a primary database in your corporate offices and you want to create a standby database in another building on your local area network (LAN). In addition, you have a legal insurance requirement to keep the redo information and backup copies off-site at a geographically distant location outside of your LAN but on your wide area network (WAN).
You could define two destinations on your primary database so that redo logs could be transmitted to both of these sites, but this would put an extra workload on your primary database throughput due to the network latency of sending the redo logs over the WAN.
To solve this problem, you could define a tight connection between your primary and physical standby databases in your LAN using the
SYNC network transports and standby online redo logs. This would protect against losing access to the primary database and provide an alternate site for production when maintenance is required on the primary database. The secondary location on the WAN could be serviced by the physical standby database, ensuring that the redo information is stored off-site. Nightly backup operations on the production database could then be moved to the WAN remote standby database, which removes the requirement to ship tapes to the off-site storage area.
Finally, in a worst case scenario where you lose access to both the primary database and the physical standby database on the LAN, you could fail over to the remote standby database with minimal data loss. If you can gain access to the last standby database's online redo log from the original standby database, you could recover it on the remote standby database, incurring no data loss.
The only time you would incur problems by sending the information over the WAN is during a switchover or failover operation, when the physical standby database has transitioned to the primary role. However, this configuration would still meet your insurance requirements.
You have a primary database in a remote city and you would like to have access to its data locally for reporting purposes. The primary database already has a standby database set up for failure protection in an off-site location on the LAN. Putting a destination on the primary database to send the information to your site would adversely affect the performance of the primary database.
Solving this problem is similar to the solution that is described in scenario 1, except that you are sending the redo logs to a logical standby database, because you already have a physical standby database. First, ensure that:
If standby redo logs are not defined, you can define them dynamically on the standby database. The standby database will begin using the standby redo logs at the next log switch on the primary database. If the
LGWR network transport is not being used, you can dynamically set log transport services on the primary database, and the primary database will start using the log writer at the next log switch.
Next, perform the normal setup tasks for a logical standby database. Any of the steps required to prepare to use a logical standby database must be done at the primary location, but once complete, you can copy the primary database to the standby location. After the logical standby database is up and running, define your destination parameter on the physical standby database to send the redo logs over the WAN, where they will be applied to the logical standby database.
A primary database located in a manufacturing site already is configured with two physical standby databases. One standby database is located on the LAN in another building, and the second standby database is more remotely located on a WAN in the corporate offices. You cannot use cascading standby databases for the standby database on the WAN, because there is a requirement to have two standby databases in no-data-loss mode. Also, the marketing department has requested access to the manufacturing data for sales predictions. The marketing department needs access to the data on a daily basis, and they want to combine sales data with manufacturing data to better understand sales versus the actual manufacturing times.
One solution would be to allow marketing to access a physical standby database in the corporate offices using read-only mode. However, putting the standby database in read-only mode requires stopping managed recovery operations. This means that the physical standby database can only catch up with the primary database at night, while it is still receiving data from the second and third shifts at the manufacturing plant. In addition, the standby database would always be at least 12 hours behind in applying redo logs. You could add another destination to the primary database to send the redo logs to a new logical standby database in the corporate offices. Because the systems used in the corporate office are different for the physical standby database and the proposed logical standby database, you cannot use the
DEPENDENCY attribute when defining the standby destinations. Because redo logs need to be transmitted over a WAN, it would degrade performance on the primary database to send the redo data twice, which has been deemed to be unacceptable.
Cascading standby databases can solve this problem. To set this up, you would create a logical standby database following the instructions in Chapter 4, but you would also set up the corporate physical standby database to transmit the redo logs over the corporate LAN to the new logical standby database. In this way, the primary database is only sending the data once over the WAN. The logical standby database could then be modified with new materialized views so that the marketing group can manage the data more efficiently. Because the logical standby database is open for read/write operations, the marketing group can add new schemas and load in sales data without affecting performance on the primary database, or the viability and current state of the physical standby database.
You have five Sales offices around the world, each with its own primary database. You would like to implement a failure protection strategy for all of them, as well as a way to get timely access to all data with minimal effect on each primary database.
To solve this problem, you would first implement a no-data-loss environment for each of the five offices by creating a physical standby database (with
SYNC attributes) local to each office. The physical standby databases could be on a LAN or a WAN. Then, create a logical standby database from each of the five primary databases and locate the logical standby databases in your corporate office. However, instead of having log transport services on each of the five primary databases send the redo logs, you would configure each of the five standby databases to send the redo logs to its logical standby database over the WAN. At one logical standby database (or all of them), you would define database links to each of the other logical standby databases and use them to access all of the sales data. If you decide that you do not need all of the information from each of the five primary databases, but only certain tables, you can use the
SKIP routines to stop applying data that you do not need on each of the logical standby databases.
You have a primary database that is currently protected only by nightly backup operations. You have been told that you must implement a major failure recovery strategy immediately. You have another system of the same hardware type in-house, but it does not have enough power to serve as a standby database for failover purposes, and it does not have enough disks for the entire database. The only other system available to you that is large enough to hold the entire database is too far away to be put on the LAN, and the WAN that connects to it is extremely slow. The deadline for implementing the strategy is well before any network upgrades can occur. Adding a destination (on the primary database) to send the redo logs to the remote location would severely affect performance.
The interim solution to this problem would be to create a physical standby database on the remote system and create a distribution repository on the local smaller system. A distribution repository comprises only the standby control file and the standby database online redo logs, not the data files. You would configure the primary database to send the redo information to the repository locally using the log writer process (
LGWR) in synchronous mode (
SYNC). Because the connection is over the LAN, the effect on performance would be minimal. The repository would then be configured to send the data onwards over the WAN to the real standby database.
The risk with this configuration is that while the primary database has transmitted all of its data to a standby database, it is possible that the repository has not completed sending the data to the remote standby database at the time of a failure at the primary database. In this environment, as long as both systems do not fail at the same time, the remote standby database should receive all the data sent up to the last log switch. You would have to send the current online redo log manually.
Once the WAN is upgraded to permit a direct connection to the remote standby database, you can either redirect the destination to the repository to point to the remote standby database directly or create a new destination to the remote standby database and continue transmitting to the repository as an archive log repository.