|Oracle9i Data Guard Concepts and Administration
Release 2 (9.2)
Part Number A96653-01
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.
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
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:
Table 6-1 and Oracle9i Supplied PL/SQL Packages and Types Reference for complete information about the
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.
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 to prevent all users other than
SYS from making changes to any data in the database.
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 if you want typical security for all data in the database.
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
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.
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:
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.
Example 9-1 demonstrates how to add the
emp 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;
Oracle9i Supplied PL/SQL Packages and Types Reference for information about the
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.
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
To see other tables that are not being maintained, query the following views:
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.
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.
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.
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:
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_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
CURRENT_SCN. This ordering ensures that a shutdown failure appears last in the view.
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:
READERprocess reads redo records from the archived redo logs.
PREPARERprocesses do the heavy computing required to convert the block changes into table changes.
BUILDERprocess assembles completed transactions.
ANALYZERprocess examines the records, possibly dumping transactions and performing some dependency computation.
APPLIERprocesses 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.
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.
DBA_LOGSTDBY_PROGRESS view displays
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
DBA_LOGSTDBY_LOG when there are no gaps in the list.
Log files with a
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.
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;
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:
DBMS_LOGSTDBY.GUARD_BYPASS_ONprocedure to bypass the database guard so you can make modifications to the logical standby database:
DBA_LOGSTDBY_EVENTSview to find the
XIDSQNvalues for the failed DDL, and provide the values to the
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.
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;
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;
Take the following actions to increase system performance:
DBMS_LOGSTDBY.APPLY_SETprocedure to affect SGA and process usage.
TRANSACTION_CONSISTENCY parameter of the
DBMS_LOGSTDBY.APPLY_SET procedure to set the level of transaction consistency.
Specify one of the following values:
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.
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.
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 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
PARALLEL_ADAPTIVE_MULTI_USER initialization parameters. This parameter must not be set to a value less than 5 on a logical standby database.
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
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.
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.