To reduce the load on your primary system, you can implement cascaded 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 E-1 shows cascaded destinations to physical and logical standby databases.
Figure E-1 Cascaded Destination Configuration Example
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 VALID_FOR
attribute on the LOG_ARCHIVE_DEST_
n
parameter 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:
The following sections describe how to set up a Data Guard configuration to use cascaded destinations:
Configuring Cascaded Destinations for Physical Standby Databases
Configuring Cascaded Destinations for Logical 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. Define the destination to use:
The ARCH
or the LGWR
attributes to specify the transport method. With the LGWR
transport method, you can use either SYNC
or ASYNC
network protocols, depending on your requirements.
The VALID_FOR
attribute to handle the cascaded destinations and the original primary and standby destinations. Define destinations for the primary database and other standby databases as well as the cascading standby databases.
On the receiving physical standby database, define more than the usual number of 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.
Table E-1 shows the initialization parameters for the Boston primary database, which sends redo to the Chicago physical standby database, which cascades its archived redo log files to the Denver standby database. In the example, the Denver database is a logical standby database, but note that a physical standby database can cascade redo to either a physical or a logical standby database.
Table E-1 Initialization Parameters for Primary, Physical, and Logical Standby Databases
Boston Database (Primary Role) | Chicago Database (Standby Role) | Denver Database (Standby Role) |
---|---|---|
DB_UNIQUE_NAME=boston LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(chicago,boston,denver)' LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/boston/ VALID_FOR=(ONLINE_ LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston' LOG_ARCHIVE_DEST_2= 'SERVICE=denver VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=denver' LOG_ARCHIVE_DEST_3= 'SERVICE=chicago VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago' STANDBY_ARCHIVE_DEST=/arch1/boston/ REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE |
DB_UNIQUE_NAME=chicago LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(chicago,boston,denver)' LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/chicago/ VALID_FOR=(ONLINE_ LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago' LOG_ARCHIVE_DEST_2= 'SERVICE=denver VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=denver' LOG_ARCHIVE_DEST_3= 'SERVICE=boston VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston' STANDBY_ARCHIVE_DEST=/arch1/chicago/ REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE |
DB_UNIQUE_NAME=denver LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(chicago,boston,denver)' LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/denver/ VALID_FOR=(ONLINE_ LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=denver' LOG_ARCHIVE_DEST_2= 'LOCATION=/arch2/denver/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=denver' STANDBY_ARCHIVE_DEST=/arch2/denver/ REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE |
Both the Boston primary database and the Chicago physical standby database define the LOG_ARCHIVE_DEST_2
initialization parameter as 'SERVICE=denver VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE)
. Hence, even if the Boston and Chicago databases switch roles, the redo data will continue to be cascaded to the Denver database. Remember, as part of the original setup of the physical standby database, you should define a local destination, VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)
, that will be used for local archiving when the physical standby database transitions to the primary role.
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.
As with physical standby databases, you can use any transport mode (LGWR
or ARCH
) and network protocol (SYNC
or ASYNC
). You must configure standby redo log files on your standby databases.
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.
The process to perform a switchover or failover is exactly the same in a cascaded 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.
Any standby database that receives redo data cascaded 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.
The following scenarios demonstrate configuration options and uses for cascaded destinations.
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.
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 standby log files are 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.
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.
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 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 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 to the physical standby database. 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.
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. 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 redo 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.
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 smaller local system. A distribution repository contains only the standby control file, standby redo log files, and the standby database archived redo log files, not the datafiles. 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.