E Cascaded Destinations

E.1.1 Configuring Cascaded 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. 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.

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.

E.1.2 Configuring Cascaded 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.

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.

E.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 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.

E.2.2 Standby Databases Receiving Redo Data from a Logical 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.

E.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.

E.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 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.

E.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 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.

E.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. 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.

E.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 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 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.