9
Managing a Logical Standby Database

This chapter describes how to manage logical standby databases. This chapter contains the following topics:

The topics in this chapter describe how to use SQL statements, initialization parameters, views, and the DBMS_LOGSTDBY PL/SQL package to manage logical standby databases.

See Also:

Oracle9i Data Guard Broker, which describes how the Data Guard broker automates the management tasks described in this chapter through the Data Guard Manager graphical user interface and the Data Guard command-line interface

9.1 Configuring and Managing Logical Standby Databases

Chapter 6 described how you use the DBMS_LOGSTDBY PL/SQL package to control SQL apply operations to the logical standby database. The DBMS_LOGSTDBY PL/SQL package also provides procedures to help you configure and manage logical standby databases. You can use the DBMS_LOGSTDBY PL/SQL package to perform management tasks such as the following on logical standby databases:

In addition, see Section 10.2 for scenarios that show how to skip a transaction, instantiate a table, and perform switchover and failover operations in a logical standby environment.

9.1.1 Controlling User Access to Tables in a Logical Standby Database

The ALTER DATABASE GUARD SQL statement controls user access to tables in logical standby databases. Until you start SQL apply operations, users can modify the logical standby database. However, once you start SQL apply operations to the logical standby database, the database guard is set to ALL by default, which prevents nonprivileged users from executing DML or DDL statements on the logical standby database.

This command allows the following keywords:

ALL

Specify ALL to prevent all users other than SYS from making changes to any data in the database.
STANDBY

Specify STANDBY to prevent all users other than SYS from making changes to any database object being maintained by logical standby databases. This setting is useful if you want report operations to be able to modify data as long as it is not being replicated by the logical standby database.
NONE

Specify NONE if you want typical security for all data in the database.

Users with DBMS_LOGSTDBY access may be able to perform DML transactions after executing the DBMS_LOGSTDBY.GUARD_BYPASS_ON PL/SQL procedure. For example, this GUARD_BYPASS_ON PL/SQL procedure allows users to create additional indexes and materialized views.

Also, it is possible to set the database guard to the STANDBY keyword, which allows users to submit DML transactions except for tables maintained in SQL apply mode. In other words, a user can create new tables and modify them in SQL apply mode.

Finally, you can skip certain operations affecting tables that are maintained by the SQL apply operation of log apply services. In this case, users can execute DML statements against the skipped objects if the database guard is set to the STANDBY keyword.

9.1.2 Skipping Tables in a Logical Standby Database

By default, all SQL statements executed on a primary database are applied to a logical standby database. If only a subset of activity on a primary database is of interest for replication, use the DBMS_LOGSTDBY.SKIP procedure to define filters that prevent the application of SQL statements on the logical standby database.

9.1.3 Adding Tables to a Logical Standby Database

Initially, you may decide to leave tables out of the logical standby database that you later want to add.

This following list and Example 9-1 show how to add a table to a logical standby database:

Stop log apply services to stop applying SQL statements to the database.

Stopping log apply services on a logical standby database does not compromise the standby database. Log transport services on the primary database continue to transmit archived redo logs to the logical standby database. However, stopping log apply services affects the time it takes to fail over to the logical standby database because of the remaining log data that must be applied before failing over.
Remove the skip table entry using the DBMS_LOGSTDBY.UNSKIP procedure.
Re-create the table in the logical standby database using the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure.
Restart log apply services.

Example 9-1 demonstrates how to add the emp table to a logical standby database.

Example 9-1 Adding a Table to a Logical Standby Database

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP('table','scott','emp',null);
SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE('emp','scott','dbone');
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

See Also:

Oracle9i Supplied PL/SQL Packages and Types Reference for information about the DBMS_LOGSTDBY.UNSKIP and the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedures

Prior to running reports that use this table, you should archive the current redo log on the primary database and ensure that it is applied to the logical standby database.

9.1.4 Finding Unsupported Database Objects

Logical standby databases maintain user tables, sequences, and jobs. To maintain other objects, you must reissue the DDL statements seen in the redo stream. Tables in the SYS schema are never maintained, because only Oracle metadata is maintained in the SYS schema.

To see other tables that are not being maintained, query the following views:

DBA_LOGSTDBY_UNSUPPORTED view

Lists all of the tables that contain datatypes not supported by logical standby databases in the current release. These tables are not maintained (will not have DML applied) by the logical standby database. Query this view on the primary database to ensure that those tables necessary for critical applications are not in this list. If the primary database includes unsupported tables that are critical, consider using a physical standby database.
DBA_LOGSTDBY_SKIP view

Lists the DML and DDL that is to be skipped. This view reflects the options chosen by the DBMS_LOGSTDBY.SKIP and the DBMS_LOGSTDBY.SKIP_ERROR procedures. The DBA_LOGSTDBY_SKIP view does not list tables.

You select which DML and DDL to skip using the DBMS_LOGSTDBY.SKIP procedure. If there are schemas or tables with contents that are not important during a failover operation, you should avoid applying changes for those tables. Doing so may improve the overall performance of the system because it avoids performing unnecessary work. The same applies to sequences. You can skip DML for a sequence in the same way that you skip DML for a table.
```
EXECUTE DBMS_LOGSTDBY.SKIP('dml','scott','scottseq');
```
Another important skip tool is DBMS_LOGSTDBY.SKIP_ERROR. Depending on how important a table is, you may want to ignore failures for a table or specific DDL. Doing so prevents the SQL apply operations from stopping. Later you can view the DBA_LOGSTDBY_EVENTS view to find and correct any problems that exist.

9.1.5 Viewing and Controlling Logical Standby Events

When you query the DBA_LOGSTDBY_EVENTS view, it displays a table of events that contains interesting activity from SQL apply operations. In particular, DDL execution or anything that generates an error is recorded in the events table. You can control what and how much activity is recorded in the events table. By default, 100 records are stored in this table, but you can increase that to 200 by specifying the following:

DBMS_LOGSTDBY.APPLY_SET('max_events_recorded', 200);

Additionally, you can indicate what type of events you want recorded. By default, everything is recorded in the table. However, you can set the RECORD_SKIP_DDL, RECORD_SKIP_ERRORS, and RECORD_APPLIED_DDL parameters to the APPLY_SET procedure to false to avoid recording these events.

Errors that cause SQL apply operations to stop are always recorded in the events table (unless access to the system tablespace is an issue). These events are always put into the ALTER.LOG file as well, with the phrase 'LOGSTDBY event' included in the text. When querying the view, select the columns in order by EVENT_TIME, COMMIT_SCN, and CURRENT_SCN. This ordering ensures that a shutdown failure appears last in the view.

9.1.6 Viewing SQL Apply Operations Activity

SQL apply operations for logical standby databases use a collection of parallel execution servers and background processes to perform a number of different tasks. The V$LOGSTDBY view shows what each process is currently doing; the TYPE column describes the task being performed:

The READER process reads redo records from the archived redo logs.
The PREPARER processes do the heavy computing required to convert the block changes into table changes.
The BUILDER process assembles completed transactions.
The ANALYZER process examines the records, possibly dumping transactions and performing some dependency computation.
The APPLIER processes apply the completed transactions.

When querying the V$LOGSTDBY view, pay special attention to the HIGH_SCN column. This is an activity indicator. As long as it is changing each time you query the V$LOGSTDBY view, progress is being made. The STATUS column gives a text description of the current activity.

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.

9.1.7 Determining How Much Redo Log Data Has Been Applied

An important difference between a logical standby database and a physical standby database is that a logical standby database applies transactions while a physical standby database applies blocks of redo. This means that when a physical standby database has applied an archived redo log, that log will not be read again. However, for a logical standby database, all committed transactions from a given redo log may be applied, but there is always data in a redo log for transactions that commit in the next log. For this reason, logical standby databases use an SCN range of redo data, rather than individual archived redo logs.

The DBA_LOGSTDBY_PROGRESS view displays APPLIED_SCN, NEWEST_SCN, and READ_SCN information. The APPLIED_SCN indicates that committed transactions at or below that SCN have been applied. The NEWEST_SCN is the maximum SCN to which data could be applied if no more logs were received. This is usually the MAX(NEXT_CHANGE#)-1 from DBA_LOGSTDBY_LOG when there are no gaps in the list.

Log files with a NEXT_CHANGE# below READ_SCN are no longer needed. The information in those logs has been applied or persistently stored in the database. The time values associated with these SCN values are only estimates based on log times. They are not meant to be accurate times of when those SCN values were written on the primary database.

The DBA_LOGSTDBY_LOG view contains a list of all the log files that have been registered. If you prefer using sequence numbers, rather than SCNs, you can combine the DBA_LOGSTDBY_LOG view and the DBA_LOGSTDBY_PROGRESS view to get that information. For example:

SELECT SEQUENCE# FROM DBA_LOGSTDBY_LOG l, DBA_LOGSTDBY_PROGRESS p
WHERE l.FIRST_CHANGE# <= p.APPLIED_SCN AND l.NEXT_CHANGE# > p.APPLIED_SCN;

SELECT SEQUENCE# FROM DBA_LOGSTDBY_LOG l, DBA_LOGSTDBY_PROGRESS p
WHERE l.FIRST_CHANGE# <= p.NEWEST_SCN AND l.NEXT_CHANGE# > p.NEWEST_SCN;

SELECT SEQUENCE# FROM DBA_LOGSTDBY_LOG l, DBA_LOGSTDBY_PROGRESS p
WHERE l.FIRST_CHANGE# <= p.READ_SCN AND l.NEXT_CHANGE# > p.READ_SCN;

9.1.8 Recovering from Errors

If the SQL apply operation fails, an error is recorded in the DBA_LOGSTDBY_EVENTS table. If the error was caused by a DDL transaction that contained a file specification that does not match in the logical standby database environment, perform the following steps to fix the problem:

Use the DBMS_LOGSTDBY.GUARD_BYPASS_ON procedure to bypass the database guard so you can make modifications to the logical standby database:
```
SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_ON;
```

Execute the DDL command, using the correct file specification, and then reenable the database guard. For example:

SQL> ALTER TABLESPACE t_table ADD DATAFILE 'dbs/t_db.f' SIZE 100M REUSE;
SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_OFF;

Query the DBA_LOGSTDBY_EVENTS view to find the XIDUSN, XIDSLT, and XIDSQN values for the failed DDL, and provide the values to the DBMS_LOGSTDBY.SKIP_TRANSACTION.

SQL> SELECT XIDUSN, XIDSLT, XIDSQN FROM DBA_LOGSTDBY_EVENTS 
  2> WHERE EVENT_TIME = (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS);
SQL> EXECUTE DBMS_LOGSTDBY.SKIP_TRANSACTION( /*xidusn*/, /*xidslt*/, 
/*xidsqn*/);

Start SQL apply operations on the logical standby database.
```
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
```

When the logical standby database restarts, it will attempt to reexecute the transaction, but the transaction will be skipped. Because you have compensated for the failure by executing the DDL properly, anything else that depends on the DDL should execute without problems.

Although the SKIP_TRANSACTION procedure can be very helpful, you should be cautious when using it to filter DML failures. Not only is the DML that is seen in the events table skipped, but so is all the DML associated with the transaction. Thus, multiple tables may be damaged by such an action.

DML failures usually indicate a problem with a specific table. Assume the failure is an out-of-storage error that you cannot resolve immediately. One way to bypass the table but not the transaction is to add the table to the skip list.

SQL> EXECUTE DBMS_LOGSTDBY.SKIP('dml','scott','emp');
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

From this point on, DML activity for the scott.emp table will not be applied. After you have corrected the storage problem, you can fix the table provided you have set up a database link to the primary database that has administrator privileges to run procedures in the DBMS_LOGSTDBY package. With that database link, you can re-create the table and pull the data over to the standby database.

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE('scott','emp','primarydb');
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

The local scott.emp table is dropped, then re-created using the database link. Table scott.emp will contain records as of when the INSTANTIATE_TABLE procedure was performed. That means it is possible for the scott.emp table to contain records for a department not in the scott.dept table. To remedy this, log on to the primary database and execute the following statements:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> SELECT FIRST_CHANGE# FROM V$LOG WHERE STATUS = 'CURRENT';
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

When DBA_LOGSTDBY_PROGRESS.APPLIED_SCN is greater than the value selected from the query of the V$LOG view, the database is consistent, and you can safely run reports again.

9.2 Tuning Logical Standby Databases

Take the following actions to increase system performance:

On the primary database, provide additional identification key information, indicating which columns uniquely define the rows for tables without primary keys. This makes logging more efficient. You also have some control over tuning the system through your ability to affect what DDL is logged. Use the DBMS_LOGSTDBY.APPLY_SET procedure to affect SGA and process usage.
Application programmers should provide primary key information to aid in efficient logging of the application data. Also, programmers can provide primary keys with RELY constraints.

See Also:
Section 4.1 and Oracle9i SQL Reference for more information about RELY constraints
Transaction consistency

Use the TRANSACTION_CONSISTENCY parameter of the DBMS_LOGSTDBY.APPLY_SET procedure to set the level of transaction consistency.

Specify one of the following values:
- FULL
  
  Transactions are applied to the logical standby database in the exact order in which they were committed on the primary database. This option results in the lowest performance. This is the default parameter setting.
  
  Reporting applications can run trouble-free in this mode, even if they use temporary tables to summarize results.
- READ_ONLY
  
  Transactions are committed out of order from how they were committed on the primary database. The READ_ONLY value provides better performance than the FULL value, and SQL SELECT statements return read-consistent results. This is particularly beneficial when you are using the logical standby database to generate reports.
  
  Note:
  DML statements involving standby tables are not allowed in this mode.
- NONE
  
  Transactions are committed out of order from how they were committed on the primary database, and no attempt is made to provide read-consistent results. This results in the best performance of the three values. If applications that are reading the logical standby database make no assumptions about transaction order, this option works well.
PARALLEL_MAX_SERVERS

Use the PARALLEL_MAX_SERVERS initialization parameter to specify the maximum number of parallel execution processes and parallel recovery processes for an instance. The default value for this parameter is derived from the values of the CPU_COUNT, PARALLEL_AUTOMATIC_TUNING, and PARALLEL_ADAPTIVE_MULTI_USER initialization parameters. This parameter must not be set to a value less than 5 on a logical standby database.

The MAX_SERVERS parameter of the DBMS_LOGSTDBY.APPLY_SET procedure can be used to limit the number of parallel servers used by log apply services. The default value of this parameter is set equal to the value of the PARALLEL_MAX_SERVERS parameter. If explicitly set, this parameter should not be set to a value less than 5 or greater than the value of the PARALLEL_MAX_SERVER parameter.
SHARED_POOL_SIZE

Use the SHARED_POOL_SIZE initialization parameter to specify the size (in bytes) of the shared pool. The shared pool contains shared cursors, stored procedures, and control structures. The shared pool is also used to hold redo data while it is processed by log apply services.

The MAX_SGA parameter of the DBMS_LOGSTDBY.APPLY_SET procedure can be used to set the maximum amount of shared pool space used by log apply services. By default, log apply services will use up to one quarter of the shared pool. Generally speaking, increasing the size of the shared pool or the amount of shared pool space used by log apply services will improve the performance of a logical standby database.

9 Managing a Logical Standby Database