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.
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 |
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:
See Also:
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.
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.
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.
DBMS_LOGSTDBY.UNSKIP
procedure.DBMS_LOGSTDBY.INSTANTIATE_TABLE
procedure.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;
See Also:
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 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.
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.
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:
READER
process reads redo records from the archived redo logs.PREPARER
processes do the heavy computing required to convert the block changes into table changes.BUILDER
process assembles completed transactions.ANALYZER
process examines the records, possibly dumping transactions and performing some dependency computation.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.
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;
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_ON
procedure to bypass the database guard so you can make modifications to the logical standby database:
SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_ON;
SQL> ALTER TABLESPACE t_table ADD DATAFILE 'dbs/t_db.f' SIZE 100M REUSE; SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_OFF;
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*/);
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.
Take the following actions to increase system performance:
DBMS_LOGSTDBY.APPLY_SET
procedure to affect SGA and process usage.RELY
constraints.
See Also:
Section 4.1 and Oracle9i SQL Reference for more information about |
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.
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.
|
Copyright © 1999, 2002 Oracle Corporation. All Rights Reserved. |
|