PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
The DBMS_LOGSTDBY
package provides procedures for configuring and managing the logical standby database environment.
See Also:
Oracle Data Guard Concepts and Administration for more information about logical standby databases |
This chapter contains the following topics:
The DBMS_LOGSTDBY
package helps you manage the SQL Apply (logical standby database) environment. The procedures in the DBMS_LOGSTDBY
package help you to accomplish the following main objectives:
Ensure you use the correct case when supplying schema and table names to the DBMS_LOGSTDBY
package. For example, the following statements show incorrect and correct syntax for a SKIP
procedure that skips changes to OE.TEST
.
Incorrect statement:
EXECUTE DBMS_LOGSTDBY.SKIP ('DML', 'oe', 'test', null);
Because the names are specified with lowercase characters, the transactions that update these columns will still be applied to the logical standby database.
Correct statement:
EXECUTE DBMS_LOGSTDBY.SKIP ('DML', 'OE', 'TEST', null);
Use this procedure to set and modify the values of initialization parameters that configure and manage log apply services in a logical standby database environment. SQL apply cannot be running when you use this procedure.
DBMS_LOGSTDBY.APPLY_SET ( parameter IN VARCHAR, value IN VARCHAR);
Parameter | Description |
---|---|
|
Number of megabytes for the system global area (SGA) that SQL Apply will use to cache change records. The default value is 30 megabytes or less if the Note: In Oracle9i, the parameter default was one quarter of the value set for the |
|
Number of parallel query servers that SQL Apply uses to read and apply redo. It defaults to the value of the Note: In Oracle9i, the |
|
Number of events that will be stored in the |
|
Level of transaction consistency maintained between the primary and standby databases. Specify one of the following values:
Regardless of the level chosen, modifications done to the same row are always applied in the same order as they happened in the primary database. See the Usage Notes for details and recommendations. |
|
Controls whether skipped errors (as described by the
|
|
Controls whether skipped DDL statements are recorded in the
|
|
Controls whether DDL statements that have been applied to the logical standby database are recorded in the
|
|
Controls the number of parallel execution servers used to apply changes. See Oracle Data Guard Concepts and Administration for an explanation of the logical standby processes. |
|
Controls the number of parallel execution servers used to prepare changes. See Oracle Data Guard Concepts and Administration for an explanation of the logical standby processes. |
APPLY_SET
procedure when you need to perform tuning and maintenance tasks. For example, use the APPLY_SET
procedure when you want to override default initialization parameter values to tune log apply services.APPLY_SET
procedure. The initialization parameter values that you set using this procedure do not become active until you start log apply services.APPLY_UNSET Procedure
to reverse (undo) the actions of the APPLY_SET
procedure.TRANSACTION_CONSISTENCY
parameter:
FULL
option (the default) is recommended when the logical standby database is used for generic reporting applications. This option results in the lowest performance.READ_ONLY
option is recommended when the logical standby database is used for read-only reporting.
SQL Apply periodically refreshes an SCN maintained in SGA that represents a consistent state. Queries executed on the standby database, automatically use Oracle Flashback to the maintained SCN. This is beneficial when the logical standby database is being used to generate reports. Any Oracle Flashback restrictions apply to this option.
NONE
option works well as long as applications that are reading the logical standby database make no assumptions about transaction order. The NONE
option is also useful when the logical standby database is in catch-up mode.
The NONE
option results in the best performance of the three options for the TRANSACTION_CONSISTENCY
parameter. However, this setting might give you inconsistent results on the standby database. If applications that are reading the logical standby database make no assumptions about transaction order, this option works well. For example:
If parallel queries are routinely being performed by applications, a certain number of parallel query servers should be reserved for those queries. To allocate 30 parallel query servers for logical standby log apply services, enter the following statement:
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_SERVERS', 30);
Thus, if the PARALLEL_MAX_SERVERS
parameter is set to 50, 30 servers will be available for logical standby processing and 20 parallel query servers will be allocated for parallel query processing.
Use the APPLY_UNSET
procedure to reverse or undo the settings that you made with the APPLY_SET
procedure. The APPLY_UNSET
procedure resets the specified initialization parameter value to the system default value. The initialization parameter default value does not become active until log apply services are started.
DBMS_LOGSTDBY.APPLY_UNSET ( parameter IN VARCHAR);
Parameter | Description |
---|---|
|
Number of megabytes for the system global area (SGA) allocation for log apply services cache. The default value is one quarter of the value set for the |
|
Number of parallel query servers specifically reserved for log apply services. By default, log apply services use all available parallel query servers to read the log files and apply changes. See Oracle Database Reference for more information about parallel query servers. |
|
Number of events that will be stored in the |
|
Level of transaction consistency maintained between the primary and standby databases. Specify one of the following values:
Regardless of the level chosen, modifications done to the same row are always applied in the same order as they happened in the primary database. See the Usage Notes for details and recommendations. |
|
Controls whether skipped errors (as described by the
|
|
Controls whether skipped DDL statements are recorded in the
|
|
Controls whether DDL statements that have been applied to the logical standby database are recorded in the
|
|
Controls the number of parallel execution servers used to apply changes. See Oracle Data Guard Concepts and Administration for an explanation of the logical standby processes. |
|
Controls the number of parallel execution servers used to prepare changes. See Oracle Data Guard Concepts and Administration for an explanation of the logical standby processes. |
APPLY_UNSET
procedure.APPLY_SET
procedure to set the values of initialization parameters.To unset the number of parallel query servers for log apply services, enter the following statement:
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_UNSET('MAX_SERVERS');
Assuming that the PARALLEL_MAX_SERVERS
initialization parameter is set to 50, this statement will result in 50 parallel query servers being available for parallel query processing. This is because, by default, log apply services use all available parallel query servers to read the log files and apply changes.
Use this procedure on the primary database to preserve important metadata (LogMiner dictionary) information in the redo logs. If supplemental logging has not been set correctly, this procedure sets it up and enables it automatically.
DBMS_LOGSTDBY.BUILD;
ALTER DATABASE CREATE LOGICAL STANDBY CONTROLFILE
statement also performs this functionality.This procedure creates and populates a table in the standby database from a corresponding table in the primary database. The table requires the name of the database link (dblink)
as an input parameter.
Use the INSTANTIATE_TABLE
procedure to:
DBMS_LOGSTDBY.INSTANTIATE_TABLE ( schema_name IN VARCHAR2, table_name IN VARCHAR2, dblink IN VARCHAR2);
Enter this statement to create and populate a new table on the standby database.
SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE ( 'myschema', 'mytable', 'mydblink');
Use the SKIP
procedure to define filters that prevent the application of SQL statements on the 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 application to the standby database, you can use the SKIP
procedure to define filters that prevent the application of SQL statements on the logical standby database. While skipping (ignoring) SQL statements is the primary goal of filters, it is also possible to associate a stored procedure with a DDL filter so that runtime determinations can be made whether to skip the statement, execute this statement, or execute a replacement statement.
DBMS_LOGSTDBY.SKIP ( stmt IN VARCHAR2, schema_name IN VARCHAR2, object_name IN VARCHAR2, proc_name IN VARCHAR2, use_like IN BOOLEAN, esc IN CHAR1);
Parameter | Description |
---|---|
|
Either a keyword that identifies a set of SQL statements or a specific SQL statement. The use of keywords simplifies configuration since keywords, generally defined by the database object, identify all SQL statements that operate on the specified object. Table 49-6 shows a list of keywords and the equivalent SQL statements, either of which is a valid value for this parameter. |
|
The name of one or more schemas (wildcards are permitted) associated with the SQL statements identified by the |
|
The name of one or more objects (wildcards are permitted) associated with the SQL statements identified by the |
|
Name of a stored procedure to call when log apply services determines that a particular statement matches the filter defined by the
This procedure returns a value that directs log apply services to perform one of the following: execute the statement, skip the statement, or execute a replacement statement. Log apply services calls the stored procedure with the following call signature:
|
|
Allows pattern matching to isolate the tables that you want to skip on the logical standby database. The |
|
Identifies an escape character (such as the character "/") that you can use for pattern matching. If the escape character appears in the pattern before the character "%" or "_" then Oracle interprets this character literally in the pattern, rather than as a special pattern matching character. See Oracle Database SQL Reference for more information about pattern matching. |
SKIP
procedure with caution, particularly when skipping DDL statements. If a CREATE
TABLE
statement is skipped, for example, you must also specify other DDL statements that refer to that table in the SKIP
procedure. Otherwise, the statements will fail and cause an exception. When this happens, log apply services stop running.SKIP
procedure, log apply services must be halted. Do this by issuing an ALTER DATABASE STOP LOGICAL STANDBY APPLY
statement. Once all desired filters have been specified, issue an ALTER DATABASE START LOGICAL STANDBY APPLY
statement to start log apply services using the new filter settings.UNSKIP Procedure
for information about reversing (undoing) the settings of the SKIP
procedure.Table 49-6 lists the supported values for the stmt
parameter of the SKIP
procedure. The left column of the table lists the keywords that may be used to identify the set of SQL statements to the right of the keyword. Any of the SQL statements in the right column, however, are also valid values. Note that keywords are generally defined by database object.
Keyword | Associated SQL Statements |
---|---|
|
All DDL that does not pertain to a particular schema |
|
All DDL that pertains to a particular schema |
|
Sequence operations such as |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Log-ons |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 Java schema objects (sources, classes, and resources) are considered the same as procedure for purposes of skipping (ignoring) SQL statements. |
The following example shows how to use the SKIP
procedure to skip (ignore) a schema on the logical standby database.
To skip changes for a given schema, you must prevent log apply services from creating new objects in the schema and from modifying existing objects in the schema. In addition, the tablespace that supports the schema must not change. The following example demonstrates this using the SKIP
procedure in a situation where schema smith has some number of tables defined in tablespace bones that we wish to ignore.
BEGIN DBMS_LOGSTDBY.SKIP('SCHEMA_DDL', 'SMITH', '%', null); DBMS_LOGSTDBY.SKIP('DML', 'SMITH', '%', null); DBMS_LOGSTDBY.SKIP('TABLESPACE', null, null, 'SMITH.PROTECT_BONES'); END;
In the previous example, wildcards were used for the object_name
parameter to indicate that the filter applies to all objects. In the last call to the SKIP
procedure, the PROTECT_BONES
procedure was supplied so that TABLESPACE
could prevent tablespace operations on BONES
. The following example is the definition for the PROTECT_BONES
procedure:
CREATE OR REPLACE PROCEDURE PROTECT_BONES (statement IN VARCHAR2, statement_type IN VARCHAR2, schema IN VARCHAR2, name IN VARCHAR2, xidusn IN NUMBER, xidslt IN NUMBER, xidsqn IN NUMBER, skip_action OUT NUMBER, new_statement OUT VARCHAR2) AS BEGIN -- Init new_statement := NULL; -- Guaranteed to be either CREATE, DROP, or TRUNCATE TABLESPACE IF statement LIKE '%TABLESPACE BONES%' THEN -- Skip the statement skip_action := DBMS_LOGSTDBY.SKIP_ACTION_SKIP; ELSE -- Apply the statement skip_action := DBMS_LOGSTDBY.SKIP_ACTION_APPLY; END IF; END protect_bones;
Upon encountering an error, the logical standby feature uses the criteria contained in this procedure to determine if the error should cause log apply services to stop. All errors to be skipped are stored in system tables that describe how exceptions should be handled.
DBMS_LOGSTDBY.SKIP_ERROR ( stmt IN VARCHAR2, schema_name IN VARCHAR2, object_name IN VARCHAR2, proc_name IN VARCHAR2, use_like IN BOOLEAN, esc IN CHAR1);
Parameter | Description |
---|---|
|
Either a keyword that identifies a set of SQL statements or a specific SQL statement. The use of keywords simplifies configuration since keywords, generally defined by the database object, identify all SQL statements that operate on the specified object. Table 49-6 shows a list of keywords and the equivalent SQL statements, either of which is a valid value for this parameter. |
|
The name of one or more schemas (wildcards are permitted) associated with the SQL statements identified by the |
|
The name of one or more objects (wildcards are permitted) associated with the SQL statements identified by the |
|
Name of a stored procedure to call when log apply services determines a particular statement matches the filter defined by the
This procedure returns a value that directs log apply services to perform one of the following: execute the statement, skip the statement, or execute a replacement statement. Log apply services call the stored procedure with the following call signature:
|
|
Allows pattern matching to isolate the tables that you want to skip on the logical standby database. The |
|
Identifies an escape character (such as the characters "%" or "_") that you can use for pattern matching. If the escape character appears in the pattern before the character "%" or "_" then Oracle interprets this character literally in the pattern, rather than as a special pattern matching character. See Oracle Database SQL Reference for more information about pattern matching. |
SKIP_ERROR
procedure is called when log apply services encounter an error that could shut down the application of redo logs to the standby database.
Running this stored procedure affects the error being written in the STATUS
column of the DBA_LOGSTDBY_EVENTS
table. The STATUS_CODE
column remains unchanged. If the stored procedure is to have no effect, that is, apply will be stopped, then the NEW_ERROR
is written to the events table. To truly have no effect, set NEW_ERROR
to ERROR
in the procedure.
If the stored procedure requires that a shutdown be avoided, then you must set NEW_ERROR
to NULL
.
DBMS_LOGSTDBY.SKIP_ERROR('DDL', 'joe', 'apptemp', null);
This procedure provides a way to skip (ignore) applying transactions to the logical standby database. You can skip specific transactions by specifying transaction identification information.
You may want to use the SKIP_TRANSACTION
procedure to:
DBMS_LOGSTDBY.SKIP_TRANSACTION ( XIDUSN IN NUMBER, XIDSLT NUMBER IN NUMBER, XIDSQN NUMBER IN NUMBER);
Table 49-9 describes the parameters for the SKIP_TRANSACTION
procedure.
If log apply services stop due to a particular transaction (for example, a DDL transaction), you can specify that transaction ID and then continue to apply. You can call this procedure multiple times for as many transactions as you want log apply services to ignore.
Note: Do not let the primary and logical standby databases diverge when skipping transactions. If possible, you should manually execute a compensating transaction in place of the skipped transaction. |
DBA_LOGSTDBY_EVENTS
to determine the reason that log apply services stopped processing transactions to the logical standby database. Examine the statement and error condition provided.DBA_LOGSTDBY_SKIP_TRANSACTION
view to list the transactions that are going to be skipped by log apply services.ALTER DATABASE START LOGICAL STANDBY SKIP FAILED TRANSACTION
statement in Oracle Database SQL Reference.This procedure reverses the actions of the SKIP
procedure by finding the record, matching all the parameters, and removing the record from the system table. The match must be exact, and multiple skip actions can be undone only by a matching number of unskip actions. You cannot undo multiple skip actions using wildcard characters.
DBMS_LOGSTDBY.UNSKIP ( stmt IN VARCHAR2, schema_name IN VARCHAR2, object_name IN VARCHAR2);
Parameter | Description |
---|---|
|
Either a keyword that identifies a set of SQL statements or a specific SQL statement. The use of keywords simplifies configuration since keywords, generally defined by the database object, identify all SQL statements that operate on the specified object. Table 49-6 shows a list of keywords and the equivalent SQL statements, either of which is a valid value for this parameter. |
|
The name of one or more schemas (wildcards are permitted) associated with the SQL statements identified by the |
|
The name of one or more objects (wildcards are permitted) associated with the SQL statements identified by the |
|
Name of a stored procedure to call when log apply services determines that a particular statement matches the filter defined by the
This procedure returns a value that directs log apply services to perform one of the following: execute the statement, skip the statement, or execute a replacement statement. Log apply services calls the stored procedure with the following call signature:
|
|
Allows pattern matching to isolate the tables that you want to skip on the logical standby database. The |
|
Identifies an escape character (such as the character "/") that you can use for pattern matching. If the escape character appears in the pattern before the character "%" or "_" then Oracle interprets this character literally in the pattern, rather than as a special pattern matching character. See Oracle Database SQL Reference for more information about pattern matching. |
CAUTION: If DML changes for a table have been skipped, you must follow the call to the |
This procedure reverses or undoes the actions of the SKIP_ERROR
procedure by finding the record, matching all the parameters, and removing the record from the system table. The match must be exact, and multiple skip actions can be undone only by a matching number of unskip actions. You cannot undo multiple skip actions with just one unskip procedure call.
DBMS_LOGSTDBY.UNSKIP_ERROR ( stmt IN VARCHAR2, schema_name IN VARCHAR2, object_name IN VARCHAR2);
The parameter information for the UNSKIP_ERROR
procedure is the same as that described for the SKIP_ERROR
procedure. See Table 49-8 for complete parameter information.
DBMS_LOGSTDBY.UNSKIP_ERROR;
This procedure reverses the actions of the SKIP_TRANSACTION
procedure. The match must be exact, and multiple skip transaction actions can be undone only by a matching number of unskip transaction actions. You cannot undo multiple skip transaction actions using wildcard characters.
DBMS_LOGSTDBY.UNSKIP_TRANSACTION ( XIDUSN NUMBER STRING, XIDSLT NUMBER STRING, XIDSQN NUMBER STRING);
Table 49-11 describes the parameters for the UNSKIP_TRANSACTION
procedure.