C
Cascaded Redo Log Destinations

To reduce the load on your primary system, you can implement cascaded redo log destinations, whereby a standby database receives its redo data from another standby database, instead of directly from the primary database. You can configure:

A physical standby database to retransmit the incoming redo data it receives from the primary database to other remote destinations in the same manner as the primary database
A logical standby database (because it is open in read/write mode) to send the redo data it generates (after filtering and applying the redo data it receives from the primary database) to its own set of standby (physical or logical) databases

Figure C-1 shows cascaded redo log destinations to physical and logical standby databases.

Figure C-1 Cascaded Redo Log Destination Configuration Example

Text description of cascade.gif follows.

Text description of the illustration cascade.gif

A standby database can cascade redo data to up to nine destinations. However, from a practical perspective, only standby databases primarily intended to off-load reporting or backups typically would be configured to receive cascaded redo data. Standby databases that could potentially be involved in role transitions typically are configured to receive redo data directly from the primary database and have LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n parameters defined so that when a switchover or failover operation occurs, redo data continues to be received directly from the new primary database.

This appendix contains the following sections:

C.1 Configuring Cascaded Redo Log Destinations

The following sections describe how to set up a Data Guard configuration to use cascaded redo log destinations:

C.1.1 Configuring Cascaded Redo Log Destinations for Physical Standby Databases

To enable a physical standby database to send the incoming redo data to another set of destinations, you must define the following items:

Define the LOG_ARCHIVE_DEST_n initialization parameter on the primary database to set up a physical standby database that will be the starting point for a cascade to use the LGWR transport method. Use either SYNC or ASYNC network protocols depending on your requirements.
On the receiving physical standby database, define sufficient standby redo log files and ensure archiving is enabled.

At this point, you can begin defining the LOG_ARCHIVE_DEST_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 log files 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 archiver processes on the standby database will now try to send the redo data 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 redo data successfully. If the destination is another standby database and it has not received the log file successfully, then this acts as an active 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 cascaded redo log destinations and the original primary and standby destinations, 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.

C.1.2 Configuring Cascaded Redo Log Destinations for Logical Standby Databases

A logical standby database that receives redo data directly from the primary database can be configured to cascade the redo data it generates (after it has filtered and applied the redo data it receives from the primary database) to other standby databases. Because redo data cascaded from a logical standby database is not identical to the redo data originally generated by the primary database, it cannot be applied to any standby database created directly from the primary database. Instead, any standby databases that receive cascaded redo data from a logical standby database must be created from a copy of the logical standby database, and the following will be true:

Physical standby databases created from a logical standby database will be a block-for-block copy of the logical standby database and a logical copy of the original primary database.
Logical standby databases created from a logical standby database will be logical copies of the parent logical standby database and might bear only a partial resemblance to the original primary database. This is because the original primary database's data is there and so is anything else stored in the parent logical standby database including any other changes such as different indexes or materialized views.

For standby databases that receive cascaded redo data from a logical standby database, you must perform the same setup tasks as for a physical or logical standby database that receives redo data directly from the primary database. You can use any transport mode (LGWR or ARCH) and network protocol (SYNC or ASYNC). If you use the LGWR transport mode, you can optionally use standby redo log files on your standby databases.

C.2 Role Transitions with Cascaded Redo Log Destinations

Most role transitions can be performed involving standby databases that receive redo log files cascaded from another standby database. However, to minimize risk of data loss and ensure the fastest possible role transition, Oracle recommends that any standby databases that are primarily configured for disaster-recovery purposes receive redo data directly from the primary database.

C.2.1 Standby Databases Receiving Redo Data from a Physical Standby Database

The process to perform a switchover or failover is exactly the same in a cascaded redo log configuration, because all physical standby databases that receive retransmitted primary database redo data are identical and valid for role transitions. The only difference is additional time may be required for the end-of-redo data to cascade to the standby database. See Section 7.2 for information about performing role transitions with a physical standby database.

C.2.2 Standby Databases Receiving Redo Data from a Logical Standby Database

Any standby database that receives redo datacascaded from a logical standby database cannot participate in a switchover involving the primary database. (Only logical standby databases that receive redo data directly from the primary database can participate in switchovers.) If you fail over to a database that receives redo data generated by a logical standby database, then only other logical standby databases that receive redo data cascaded from the same logical standby database are able to continue to participate in the Data Guard configuration after the failover. See Section 7.3 for information about performing role transitions with a logical standby database.

C.3 Examples of Cascaded Redo Log Destinations

The following scenarios demonstrate configuration options and uses for cascaded redo log destinations.

C.3.1 Local Physical Standby and Cascaded Remote Physical Standby

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 data 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 data 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 data 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 LGWR and SYNC network transports and standby redo log files. This would protect against losing access to the primary database, and it provides 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 data is stored off site. Nightly backups 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 redo log file of the last standby database 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, when the physical standby database has transitioned to the primary role. However, this configuration would still meet your insurance requirements.

C.3.2 Local Physical Standby and Cascaded Remote Logical Standby

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 data to a logical standby database, because you already have a physical standby database. First, ensure:

The physical standby database is receiving its redo data from the log writer of the primary database.
Standby redo log files are defined and being used.

If standby redo log files are not defined, you can define them dynamically on the standby database. The standby database will begin using the standby redo log files after 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 as usual. After the logical standby database is up and running, define your destination parameters on the physical standby database to send the redo data over the WAN, where it will be applied to the logical standby database.

C.3.3 Local and Remote Physical Standby and Cascaded Local Logical Standby

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 cascaded redo log destinations 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 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 Redo Apply. 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 archived redo log files. You could add another destination to the primary database to send the redo data 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 data needs 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.

Cascaded redo log destinations 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 data 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 opened and in read/write mode, 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.

C.3.4 Consolidated Reporting with Cascaded Logical Standby Destinations

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 LGWR and 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 data, you would configure each of the five standby databases to send the redo data 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.

C.3.5 Temporary Use of Cascaded Destinations During Network Upgrades

You have a primary database that is currently protected only by nightly backups. 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 data 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 smallerlocal system. A distribution repository is contains only the standby control file and the standby database archived redo log files, not the data files. You would configure the primary database to send the redo data 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 onward 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 redo log file 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.

C Cascaded Redo Log Destinations