Managing a Physical Standby

---

Up to this point, much of our discussion has been primarily (no pun intended) centered on the primary database and how to properly configure it. Finally, it's time to start discussing how to manage day-to-day operations on the standby. In this section we will examine how to start the standby, start managed recovery, open the standby in read-only, and perform general maintenance.

Starting a Physical Standby

For those of you who are familiar with a physical standby in a previous version, we have some very good news-and for those starting in this release, it's still good news. In the past, when starting a physical standby, we had to first bring the standby instance up to a nomount state. From there we would issue the command that would mount the standby database on a standby controlfile. After doing this over a thousand times, it can get a little monotonous; however, you will be able to type ALTER DATABASE MOUNT STANDBY DATABASE faster than anyone else in the world. This is where the good news comes in.

To start the standby and place it into the mount state, you simply enter the following command:

startup mount;

The startup process will determine the type of controlfile that is being mounted and implicitly issue the correct command to mount the standby. Similarly, to start the standby in read-only from the down state, you simply need to enter the following command:

startup;

To shut down a standby, you should cancel any managed recovery sessions currently active, disconnect any active sessions if open in read-only mode, and then issue the SHUTDOWN IMMEDIATE command.

Starting Managed Recovery

Once the standby database is up and running, it is constantly receiving changes from the primary in the form of redo. These changes will simply stack up until we instruct the standby to begin taking the redo and applying it to the standby. For a physical standby, the redo application is done via the managed recovery process (MRP). The MRP will, depending on how it's started, automatically take the changes from the primary and apply them without any user intervention.

To start the MRP process as a foreground process, issue the following SQL statement:

 alter database recover managed standby database;

The command will appear to hang because the MRP process is a part of the session in which it was started. If you exit the session, the MRP is also exited. If you wish to start the MRP as a background process, thus giving you your session back, you must use the DISCONNECT keyword. For example:

alter database recover managed standby database disconnect;

When the MRP is started with the above two commands, changes are applied to the standby database when either a) a complete archivelog is received from the primary or b) when one of the standby redo logs are completely archived. So, it's important to note that changes are only applied at an archivelog boundary.

If your physical standby has standby redo logs configured, it is possible to have the MRP begin applying changes as soon as they arrive to the standby instead of waiting for a log switch boundary and for the standby redo log to be archived. This new functionality is called real-time apply. To start real-time apply, you initiate the MRP by issuing the following command:

alter database recover managed standby database using current logfile;

If you want to verify that real-time apply is indeed running, you can examine the RECOVERY_MODE column from the V$ARCHIVE_DEST_STATUS view.

In the ideal world, you would have two physical standby databases running at the same time. One standby would remain in real-time apply so that it would be as close to the primary as possible. The other standby would lag behind the primary a certain distance in time so that we could easily catch user corruptions and logical errors. If someone dropped a table on the primary, we would allow the standby that's running on a time lag to recover to the point just prior to the error, open in read-only mode, export the table, and then import the table into the primary. To achieve this time lag, we start the MRP with a delay interval expressed in minutes. The redo is still shipped immediately from the primary, but the MRP does not apply the changes until the interval has passed. For example, to start the MRP with a delay interval of one hour, run the following command:

recover managed standby database delay 60; 

To cancel delay and have the MRP complete applying all available archivelogs, issue the following command:

alter database recover managed standby database nodelay;

HA Workshop: Monitoring a Physical Standby Progress

Workshop Notes

Often you will want to verify that the physical standby's MRP is able to keep up with the rate at which redo is being sent from the primary. In this workshop we will examine several queries that will allow us to see the status of the Data Guard configuration.

The following procedure can be used to determine the number of archivelogs that have been received by the standby, but not applied, and calculate the number of archivelogs that exist on the primary that have not been received by the standby.

Step 1.  Connect to the standby and get the sequence number of the last applied archivelog. Call the value retrieved laseq:

SELECT max(sequence#) from v$archived_log where applied='YES';

Step 2.  Get the sequence number of the last complete archivelog on the standby. This is the last log the standby can apply without receiving additional archivelogs from the primary. Call the value retrieved lrseq:

SELECT min(sequence#) FROM v$archived_log WHERE ( (sequence#+1) NOT IN (SELECT sequence# FROM v$archived_log) ) AND (sequence# > laseq)

If we subtract laseq from lrseq, we have the number of archived logs that have not been applied on the standby, but could be applied if the primary host becomes unavailable.

Next, we will view the potential data loss window for the physical standby.

Step 3.  Connect to the standby and get the sequence number of the last applied log. Call the value retrieved laseq:

select max(sequence#) from v$archived_log where applied='YES';

Step 4.  Get the sequence number of the last complete archivelog on the standby. This is the last log the standby can apply without receiving additional archivelogs from the primary. Call the value retrieved lrseq:

SELECT min(sequence#) FROM v$archived_log WHERE ( (sequence#+1) NOT IN (SELECT sequence# FROM v$archived_log) ) AND (sequence# > laseq);

Step 5.  Connect to the primary database and obtain the sequence number of the current online log. Call the value retrieved curseq.

select sequence# from v$log where status='CURRENT';

If we subtract curseq - lrseq, we have the number of archivelogs that the standby database would not be able to recover should the primary host become unavailable.

Using the Standby in Read-Only Mode

Not only can a standby protect your primary from disasters, it can also relieve some of the user workload by allowing users to perform reporting. We accomplish this by opening the standby in read-only mode. Once the standby is open in read-only mode, reporting applications or user queries that do not perform any type of updates can be performed on the standby, but you should be aware of a few consequences.

Read-Only Considerations

Before deciding to open the standby in read-only mode for use in reporting, you should be aware that while open in read-only, redo changes generated from the primary are not being applied. The managed recovery process mandates that the standby be in the mount state in order for changes to be applied. For this reason, reporting applications that are run against the standby will only see data that has been applied just prior to opening in read-only. While the redo from the primary is not being applied while open, the changes are still being received and stored until such time as managed recovery is once again started. While the standby is out of sync from the primary, you should be aware that switchover and failover times will be greater as the standby must first apply all outstanding redo from the primary.

A good solution to avoid this negative impact on failover times is to implement multiple standbys. One standby would remain in managed recovery and be as close to transactionally consistent with the primary as possible. Another standby could be open read-only and allow reports to run against a snapshot of the primary's databases. In the event of switchover or failover, the standby that is most consistent with the primary would be chosen for the role reversal.

Sorting for Read-Only Queries

When we are open in read-only, no updates to the database can occur. So how do we handle user queries that need sorting space? First the queries will make use of memory sorting using SORT_AREA_SIZE. If the sort space needed exceeds that space, we must go to disk. By making use of tempfiles in locally managed temporary tablespaces, we can provide disk-based sorting space to the users without directly updating the dictionary.

If a locally managed temporary tablespace existed in the primary when you created the standby, the tablespace will also exist in the standby. All that is necessary now is to add the tempfiles to that locally managed temporary tablespace on the standby. To do this, we must first open the standby in read-only and use the following SQL to add the files:

alter tablespace temp add tempfile '/database/10gDR/temp.dbf' size 100m reuse;

If you did not have a locally managed temporary tablespace on the primary prior to creating the standby, we must first create the tablespace on the primary:

create temporary tablespace temp tempfile '/database/10gDR/temp.dbf' size 100m reuse extent management local uniform size 25m;

Once the tablespace has been created, you simply need to switch logs on the primary and allow managed recovery on the standby to create the tablespace in the standby controlfile. Once the CREATE TABLESPACE command has been recovered, you will open the standby in read-only and add the tempfile to the tablespace with the same ALTER TABLESPACE command listed earlier.

Accommodating Physical Changes Made on the Primary

It is important to make sure that any physical database changes performed on the primary, such as adding datafiles or tablespaces, are also performed on the standby. Nobody wants to perform a failover during a disaster and realize that the new tablespace added last week didn't make it across to the standby. It's also important to have as many of these changes as possible performed on the standby without user intervention, so as to reduce undue work on the DBA. To accommodate these needs, the standby parameter STANDBY_FILE_MANAGEMENT was created. When this parameter is set to AUTO, actions from the following types of commands will automatically be performed on the standby:

• CREATE TABLESPACE

• ALTER TABLESPACE commands that add datafiles

• ALTER TABLESPACE commands that change the status of a tablespace

• ALTER DATABASE command that enables or disables threads

• DROP TABLESPACE commands that include the INCLUDING CONTENTS AND DATAFILES clause

  Note 

  You must physically remove the file on the standby if the INCLUDING CONTENTS AND DATAFILES clause was not specified.

HA Workshop: Handling Physical Changes

Workshop Notes

Certain commands performed on the primary will require user intervention on the standby. One such example is renaming a datafile on the primary. In this workshop we will examine how to manually address the actions of a renamed datafile.

Step 1.  Assure that all archivelogs have been applied to the standby by using the V$ARCHIVED_LOG view on the standby.

Step 2.  Stop managed recovery on the standby.

alter database recover managed standby database cancel;

Step 3.  Rename the file to the correct path and name at the operating system level using an operating system command.

Step 4.  Bring the standby to the mount state.

startup mount;

Step 5.  Rename the datafile in the standby controlfile by using the following command on the standby:

alter database rename file  '/database/10gDR/old_name.dbf' to '/database/10gDR/new_name.dbf';

Step 6.  Restart managed recovery with the original options.

alter database recover managed standby database disconnect from session; 

Another example of a change on the primary that requires user intervention on the standby is any command that adds or alters online redo logs. While is it technically not necessary to keep the online redo logs the same between the primary and standby site, it is a good practice. Once you have added or dropped the online redo log on the primary, perform the following on the standby to bring it back into sync with the primary:

Step 1.  Stop managed recovery.

alter database recover managed standby database cancel;

Step 2.  Set the STANDBY_FILE_MANAGEMENT parameter to MANUAL.

alter system set standby_file_management=manual;

Step 3.  Issue the SQL to drop or add the online redo log on the standby database.

Step 4.  Set standby_file_management to AUTO.

alter system set standby_file_management=auto;

Step 5.  Restart managed recovery with the original options.

Be aware that if you add an online redo log on the primary, you should also add a standby redo log on the standby and primary to maintain the correct number to ensure their use.

---