Managing a Logical Standby Database

---

While the log transport or the method of delivering changes to a physical and logical standby are the same, the method for applying changes is very different. Logical standbys applies changes by reading redo from either archivelogs or standby redo logs and converting this redo into SQL statements. These SQL statements are then applied to the database tables while the database is open read/write. Understanding these processes can greatly help in managing the configuration.

First let's look at the processes involved in SQL Apply. The SQL Apply engine is made up of several processes that, as a whole, read the redo, transform the redo, construct transactions, and apply transactions to the database tables. These processes are spawned from a pool of parallel query slaves. The following is a list of the processes, along with a description of their purpose:

• The READER process reads redo as it arrives from the primary and loads it into a memory structure in the shared pool called the LCR cache.

• The PREPARER processes turn the redo in the lcr cache into logical change records (LCR) as well as identifying dependencies between the LCRs.

• The BUILDER process takes individual LCRs and builds complete transactions.

• The Analyzer process takes the transactions completed by the Builder process and computes dependencies between them.

• The COORDINATOR process monitors the dependencies between transactions and correctly schedules the application of those transactions with the apply slaves.

• The APPLY processes accept transactions from the Coordinator process and physically apply the changes to the database tables.

Later, we will discuss how we can view each process and what that process is currently working on.

Stopping and Starting SQL Apply

The first step in starting up logical apply is to bring the database to an open state. Once the standby database is open, you can start logical apply by issuing the following statement as sys:

alter database start logical standby apply;

This statement will spawn all six processes involved in SQL Apply and will read redos from archived redo logs as they are registered with the logical standby. To start SQL Apply and have it immediately apply changes as they arrive (real-time apply) from the primary, issue the following statement:

alter database start logical standby apply immediate;

Before shutting down the standby database, or before changing attributes of the SQL Apply engine, you should first stop logical apply. The following statement stops the apply engine:

alter database stop logical standby apply;

Monitoring SQL Apply Progress

Once you start SQL Apply, you will most likely want to first check the processes to see what action they are performing. This can be done by querying the V$LOGSTDBY view. This view displays one row for each process that is part of the apply engine and gives a description of what that process is doing, as well as what SCN that process has worked on. The following query returns each process name, the highest SCN that it has worked on and its current status:

select type, high_scn, status from v$logstdby;

When querying the V$LOGSTDBY view, pay special attention to the HIGH_SCN column. This is an indicator that progress is being made as long as it is changing each time you query the V$LOGSTDBY view.

Another place to gain information of current activity is the V$LOGSTDBY_STATS view, which provides state and status information. All of the options for the DBMS_LOGSTDBY.APPLY_SET procedure have default values, and those values (default or set) can be seen in the V$LOGSTDBY_STATS view. In addition, a count of the number of transactions applied or transactions ready will tell you if transactions are being applied as fast as they are being read. Other statistics include information on all parts of the system.

The DBA_LOGSTDBY_PROGRESS view describes the progress of SQL apply operations on the logical standby databases. Here's an example:

SELECT APPLIED_SCN, APPLIED_TIME, READ_SCN, READ_TIME,  NEWEST_SCN, NEWEST_TIME  FROM DBA_LOGSTDBY_PROGRESS;

The APPLIED_SCN indicates that any transactions below that SCN have been committed and applied. The NEWEST_SCN column is the highest SCN that the standby has received from the primary database. When the value of NEWEST_SCN and APPLIED_SCN are the same, all available changes have been applied. If your APPLIED_SCN is below NEWEST_SCN and is increasing, SQL apply is currently applying changes. The READ_SCN column reports the SCN that the SQL Apply engine will start at, should it be restarted.

To view a list of archivelogs that are no longer needed on the standby, you must first execute the following procedure:

execute dbms_logstdby.purge_session;

This procedure updates the DBA_LOGMNR_PURGED_LOG view with a list of archivelogs that are no longer needed.

Protecting the Logical Standby from User Modifications

At this point, everyone should be asking the same question: If the logical standby is open and users can connect, what is stopping the users from trashing the tables being maintained by the logical standby? That's a good question. If you refer back to our 'Creating a Logical Standby' section, one command that we left out is the  following:

alter database guard all;

This statement protects the logical standby tables and prevents users from doing updates to the logical standby. The ALTER DATABASE GUARD SQL statement comes with three keywords:

• ALL  All non-sys users cannot modify tables being maintained by SQL Apply, nor can non-sys users create new objects on the logical standby.

• STANDBY  All non-sys users cannot modify tables being maintained by SQL Apply, but they can create new objects on the logical standby.

• NONE  The database guard is disabled and all updates are allowed.

Users with the logstdby_administrator privilege are allowed to modify the database guard at the database level. But changing the guard at the database level can be dangerous. If only temporary corrections need to be made, it is better to temporarily disable the guard for the session only. To do this, issue the following statement:

alter session disable guard;

Once done with the modifications, reenable the guard for your session:

alter session enable guard;

Recovering from Errors

Whenever the SQL Apply engine encounters an error while applying a SQL statement, the Apply engine will stop and give the DBA the opportunity to correct the statement and restart SQL Apply. The error and the statement that stops the Apply engine are logged in a view called DBA_LOGSTDBY_EVENTS. Before we start discussing how to skip transactions, let's first cover some of the dangers. A DBA's number one concern is to keep the data on the logical standby in sync with what exists on the primary. If the SQL Apply engine receives an error during a DDL transaction, it is safe for us to issue a compensating transaction manually and then skip the failed transaction. However, if it is a DML transaction that receives an error, we should proceed very cautiously. For instance, let's suppose that transaction x consists of 500 inserts and 27 updates. If one of the inserts receives an error during apply and we then skip that single insert, we have logically corrupted the entire transaction. In general, how we recover from DDL transaction errors versus DML transactions errors can be very different.

Recovering from a DDL Transaction

One common type of DDL transaction that can fail is one that involves physical modification, such as CREATE TABLESPACE commands. It is important to note that the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters do not function on a logical standby. If the directory structure between the primary and standby are the same, DDL transactions that create files will succeed without error. However, if the directory structure between the primary and standby are different, the DDL transaction will fail because the CREATE FILE will fail.

HA Workshop: Recovering from a Failed DDL Transaction

Workshop Notes

In this workshop we will examine the procedure needed to recover from a failed DDL transaction.

Step 1.  Run the following query to determine the failing SQL statement:

select event_time, commit_scn, event, status  from dba_logstdby_events  order by event_time;

Step 2.  Disable the database guard for our session so we can modify the logical standby.

alter session disable guard;

Step 3.  Issue a compensating transaction on the logical standby. For instance, issue the CREATE TABLESPACE command that failed, but use the correct file specification.

Step 4.  Reenable the database guard for your session:

alter session enable guard;

Step 5.  Restart logical apply with a clause that will cause the failed transaction to be automatically skipped.

alter database start logical standby apply skip failed transaction;

In some rare occasions you might find it necessary to use the above procedure to skip a failed DML statement. You should fully understand how skipping that statement will affect the entire transaction and should validate your data afterward.

Recovering from a Failed DML Transaction

In general, a failed DML statement indicates that the table associated with the DML is not correctly in sync with the one on the primary. The table could have gotten out of sync by a user modifying the table on the logical standby or by defining skips on that object. Your best option for failed DML transactions is to instantiate the table so that it is once again in sync with the primary. You can use the following procedure to accomplish the table instantiation:

1. First we must stop logical standby apply.

   alter database stop logical standby apply;

2. Use the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure to put the table back in sync with the primary. The values passed to the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure are schema name, table name, and database link name.

   exec dbms_logstdby.instantiate_table('MTSMITH','MTS1','Orlando_dblink');

3. Restart logical standby apply.

   alter database start logical standby apply;

Changing the Default Behavior of the SQL Apply Engine

No matter how well intentioned, default values will not cover all possible situations. Depending on your transaction profile, you might find it necessary to fine-tune the default values for several components of the SQL Apply engine. The different parameters are all modified by using the DBMS_LOGSTDBY.APPLY_SET procedure. Below are some of the more common attributes of the apply engine you might find yourselves needing to change.

• MAX_SGA  The number of megabytes that the SQL Apply engine will use to cache change records. This is considered the LCR cache.

• MAX_SERVERS  The number of parallel query slaves that will be used for the SQL Apply engine.  The default is 9, or the number specified by the PARALLEL_MAX_SERVERS PARAMETER, whichever is lower.

• TRANSACTION_CONSISTENCY  The level of transaction consistency maintained during the application of redo from the primary. Valid values are

  • FULL  Transactions are applied to the logical standby database in the exact order in which they were committed on the primary database.

  • READ_ONLY  Transactions can be committed out of order, but user queries on the standby database will return consistent results.

  • NONE  Transactions are applied out of order from how they were committed on the primary database. Because there is no guarantee of transaction order, this mode should not be used during reporting operations.

The DBMS_LOGSTDBY.APPLY_SET procedure takes as the first value the parameter that is being changed, with the second value being what that parameter will be set to. For example, to allocate 70MB of memory to the LCR cache, we would modify the MAX_SGA using DBMS_LOGSTDBY.APPLY_SET as below. Please note that you must always stop SQL Apply prior to making any changes. The changes will take effect once you restart SQL Apply.

exec dbms_logstdby.apply_set('MAX_SGA',70);

To set TRANSACTION_CONSISTENCY to READ_ONLY, issue the following statement:

exec dbms_logstdby.apply_set('TRANSACTION_CONSISTENCY','READ_ONLY');

If you would like to return the parameters to their default values, you simply need to run the DBMS_LOGSTDBY.APPLY_UNSET procedure. For example, to put MAX_SGA back to the default value, you would issue the following statement:

exec dbms_logstdby.apply_unset('MAX_SGA');

---