| Oracle Data Guard Concepts and Administration Release 2 (9.2) Part Number A96653-02 |
|
|
View PDF |
| Oracle Data Guard Concepts and Administration Release 2 (9.2) Part Number A96653-02 |
|
|
View PDF |
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 to use the Data Guard broker to automate the management tasks described in this chapter |
The DBMS_LOGSTDBY PL/SQL package 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:
The DBMS_LOGSTDBY PL/SQL package includes procedures to help you manage SQL apply operations on logical standby databases. Using it you can do the following:
CREATE, ALTER, or DROP INDEX operations| See Also:
Oracle9i Supplied PL/SQL Packages and Types Reference for complete information about the |
Table 9-1 summarizes the procedures of the DBMS_LOGSTDBY PL/SQL package.
| See Also:
Oracle9i Supplied PL/SQL Packages and Types Reference for complete information about the |
The SQL ALTER DATABASE GUARD statement controls user access to tables in logical standby databases. Until you start log apply services on the logical standby database, users can modify the logical standby database. However, once you start log apply services, the database guard is set to ALL by default.
The ALTER DATABASE GUARD statement allows the following keywords:
ALL
Specify ALL to prevent all users other than SYS from making changes to any data in the logical standby database.
STANDBY
Specify STANDBY to prevent all users other than SYS from making DML and DDL changes to any table or sequence being maintained through SQL apply operations.
NONE
Specify NONE if you want typical security for all data in the database.
For example, use the following statement to enable the database guard and prevent user access to tables in the logical standby database:
SQL> ALTER DATABASE GUARD ALL;
You can temporarily override the database guard to allow changes to the logical standby database by executing the DBMS_LOGSTDBY.GUARD_BYPASS_ON procedure. The following sections describe two examples that show when it might be useful to bypass the database guard temporarily to make changes to the logical standby database:
The discussions in these sections assume that the database guard is set to ALL or STANDBY.
This section describes how to add an index to a table maintained through SQL apply operations.
By default, only accounts with SYS privileges can modify the database while the database guard is set to ALL or STANDBY. If you are logged in as SYSTEM or another privileged account, you will not be able to issue DDL statements on the logical standby database without first bypassing the database guard for the session.
The following example shows how to stop log apply services, bypass the database guard, execute SQL statements on the logical standby database, and then reenable the guard:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY; Database altered. SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_ON; PL/SQL procedure successfully completed. SQL> ALTER TABLE SCOTT.EMP ADD CONSTRAINT EMPID UNIQUE (EMPNO); Table altered. SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_OFF; PL/SQL procedure successfully completed. SQL> ALTER DATABASE START LOGICAL STANDBY APPLY; Database altered.
This sample procedure could be used to execute other DDL statements. Oracle Corporation recommends that you do not perform DML operations while the database guard bypass is enabled. This will introduce deviations between the primary and standby databases that will make it impossible for the logical standby database to be maintained. It is unlikely that you will be able to modify rows in a table in such a way that the logical standby database can incrementally maintain the rows.
Sometimes, a reporting application must collect summary results and store them temporarily or track the number of times a report was run. Although the main purpose of an application is to perform reporting activities, the application might need to issue DML (insert, update, and delete) operations on a logical standby database. It might even need to create or drop tables.
You can set up the database guard to allow reporting operations to modify data as long as the data is not being maintained through SQL apply operations. To do this, you must:
DBMS_LOGSTDBY.SKIP procedure. Skipped tables are not maintained through SQL apply operations.In the following example, it is assumed that the tables to which the report is writing are also on the primary database.
The example stops SQL apply operations, skips the tables, and then restarts SQL apply operations so that changes can be applied to the logical standby database. The reporting application will be able to write to MYTABLES% in MYSCHEMA. They will no longer be maintained through SQL apply operations.
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY; Database altered. SQL> EXECUTE DBMS_LOGSTDBY.SKIP('SCHEMA_DDL','MYSCHEMA','MYTABLES%'); PL/SQL procedure successfully completed. SQL> EXECUTE DBMS_LOGSTDBY.SKIP('DML','MYSCHEMA','MYTABLES%'); PL/SQL procedure successfully completed. SQL> ALTER DATABASE START LOGICAL STANDBY APPLY; Database altered.
The example then queries the DBA_LOGSTDBY_PARAMETERS view to verify the logical standby database is updated. Verification can take a while so you might need to repeat the query until no rows are returned, as shown in the following example:
SQL> SELECT NAME FROM DBA_LOGSTDBY_PARAMETERS WHERE NAME = 'EVALUATE_SKIP'; no rows selected
Finally, the example sets the database guard to allow updates to the tables.
SQL> ALTER DATABASE GUARD STANDBY; Database altered.
Triggers and constraints are enabled on the standby database but they are not executed. For triggers and constraints on tables maintained through SQL apply operations, constraints are evaluated on the primary database and do not need to be re-evaluated on the logical standby database. The effects of the triggers executed on the primary database are logged and applied on the standby database. Triggers will be fired and constraints will be evaluated on tables not maintained through SQL apply operations.
If only a subset of activity on a primary database is of interest on the standby database, use the DBMS_LOGSTDBY.SKIP procedure to define filters that prevent log apply services from issuing the SQL statements on the logical standby database. (See Section 4.1.4 for information about SQL statements that are skipped automatically.)
Tables continue applying SQL statements after filtering out unsupported datatypes or statements automatically. However, you must use the DBMS_LOGSTDBY.SKIP procedure to skip tables that you do not want to apply to the logical standby database. The following list shows typical examples of the types of SQL statements that can be filtered or skipped so that they are not applied on the logical standby database:
CREATE, ALTER, or DROP INDEX DDL statementsCREATE, ALTER, DROP, or TRUNCATE TABLE statementsCREATE, ALTER, or DROP TABLESPACE statementsCREATE or DROP VIEW statementsExample 9-1 demonstrates how to skip all SQL apply operations that reference the EMP table in a logical standby database.
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY; SQL> EXECUTE DBMS_LOGSTDBY.SKIP('SCHEMA_DDL', 'SCOTT', 'EMP', NULL); SQL> EXECUTE DBMS_LOGSTDBY.SKIP('DML', 'SCOTT', 'EMP', NULL); SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
In addition to skipping DML and DDL statements for schema and non-schema operations, you can also skip specific DML and DDL operations as well. Example 9-2 shows how to skip ALTER TABLESPACE and CREATE TABLESPACE for non-schema DDL operations.
SQL> EXEC DBMS_LOGSTDBY.SKIP(`CREATE TABLESPACE', NULL, NULL, NULL); SQL> EXEC DBMS_LOGSTDBY.SKIP(`ALTER TABLESPACE', NULL, NULL, NULL); SQL> COLUMN ERROR FORMAT a5; SQL> COLUMN STATEMENT_OPT FORMAT a20; SQL> COLUMN OWNER FORMAT a10 SQL> COLUMN NAME FORMAT a15; SQL> COLUMN PROC FORMAT a20; SQL> SELECT * FROM DA_LOGSTDBY_SKIP; ERROR STATEMENT_OPT OWNER NAME PROC ----- ----------------- ---------- --------------- -------------------- N CREATE TABLESPACE N ALTER TABLESPACE
Use the SKIP procedure with caution, particularly when skipping DDL statements. If a CREATE TABLE statement is skipped, for example, you must also skip any other DDL statements that refer to that table. Otherwise, these statements will fail and cause an exception. When this happens, the SQL apply services stop running and will need to be manually restarted.
Typically, you use table instantiation to re-create a table after an unrecoverable operation. You can also use the procedure to enable SQL apply operations on a table that was formerly skipped.
Before you can create a table, it must meet the requirements described in Section 4.1.4 and Section 4.1.5 that explain:
|
Note: The |
The following list and Example 9-3 show how to re-create a table and resume SQL apply operations on that table:
DBA_LOGSTDBY_SKIP view.
If any operations are being skipped, resume application of each operation that is currently being skipped by using the DBMS_LOGSTDBY.UNSKIP procedure. If multiple filters were created on the table, you will need to execute the procedure multiple times.
DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure. In addition to creating a table, this procedure also imports the data from the primary table using a database link. The link supplied to this procedure must have LOGSTDBY_ADMINISTRATOR role granted on the primary database.Before accessing data in the newly added table, you should archive the current redo log on the primary database, and ensure that it is applied to the logical standby database.
Example 9-3 demonstrates how to add the EMP table to a logical standby database.
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY; SQL> SELECT * FROM DBA_LOGSTDBY_SKIP; ERROR STATEMENT_OPT OWNER NAME PROC --------------------------------------------------------------------- N SCHEMA_DDL SCOTT EMP N DML SCOTT EMP SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP('SCHEMA_DDL','SCOTT','EMP'); SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE('SCOTT','EMP','DBLINK'); SQL> ALTER DATABASE START LOGICAL STANDBY APPLY; SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP('DML','SCOTT','EMP');
Log on to the primary database and issue 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 the value returned by theDBA_LOGSTDBY_PROGRESS.APPLIED_SCNprocedure is greater than the value selected from the query of theV$LOGview, the database is consistent and you can safely run reports again.
When you query the DBA_LOGSTDBY_EVENTS view, it displays a table of events that contains 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 it. For example:
SQL> 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 FALSE to avoid recording these events.
Errors that cause SQL apply operations to stop are always recorded in the events table (unless there is insufficient space in the system tablespace). These events are always put into the ALERT.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:
COORDINATOR process (LSP) is the background process that starts the other processes and schedules transactions.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 eliminating transactions and performing some dependency computation.APPLIER processes generate and execute the completed SQL 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. For example:
SQL> COLUMN NAME FORMAT A30 SQL> COLUMN VALUE FORMAT A30 SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME = 'coordinator state'; NAME VALUE ------------------------------ ------------------------------ coordinator state APPLYING SQL> COLUMN STATUS FORMAT A50 SQL> COLUMN TYPE FORMAT A12 SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY; TYPE HIGH_SCN STATUS ------------ ---------- -------------------------------------------------- COORDINATOR ORA-16117: processing READER ORA-16127: stalled waiting for additional transact ions to be applied BUILDER 191896 ORA-16116: no work available PREPARER 191902 ORA-16117: processing ANALYZER 191820 ORA-16120: dependencies being computed for transac tion at SCN 0x0000.0002ed4e APPLIER 191209 ORA-16124: transaction 1 16 1598 is waiting on ano ther transaction . . .
Another place to get information about 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. For example:
SQL> COLUMN NAME FORMAT A35 SQL> COLUMN VALUE FORMAT A35 SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS 2> WHERE NAME LIKE 'coordinator%' or NAME LIKE 'transactions%'; NAME VALUE ----------------------------------- ----------------------------------- coordinator state APPLYING transactions ready 7821 transactions applied 7802 coordinator uptime 73
This query shows how long SQL apply operations have been running and how many transactions have been applied in that time. It also shows how many transactions are available to be applied, indicating that more work is necessary.
Specifying an apply delay interval (in minutes) on the primary database is the same for both logical and physical standby databases (as described in Section 5.3.2.3, "Specifying a Time Lag for the Application of Redo Logs"). However, on a logical standby database, if the primary database is no longer available, you can cancel the apply delay interval by specifying the following PL/SQL command:
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_UNSET('APPLY_DELAY');
Transaction data in the redo stream can span multiple redo logs. For this reason, logical standby databases use an SCN range of redo data, rather than individual archived redo logs to report the progress of SQL apply operations.
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 were 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.
Logs with a NEXT_CHANGE# below READ_SCN are no longer needed. The information in those logs was 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.
You can see which logs were applied or were not applied by using the following query:
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS'; Session altered. SQL> SELECT L.SEQUENCE#, L.FIRST_TIME, 2 (CASE WHEN L.NEXT_CHANGE# < P.READ_SCN THEN 'YES' 3 WHEN L.FIRST_CHANGE# < P.APPLIED_SCN THEN 'CURRENT' 4 ELSE 'NO' END) APPLIED 5 FROM DBA_LOGSTDBY_LOG L, DBA_LOGSTDBY_PROGRESS P 6 ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIME APPLIED ---------- ------------------ ------- 24 23-JUL-02 18:19:05 YES 25 23-JUL-02 18:19:48 YES 26 23-JUL-02 18:19:51 YES 27 23-JUL-02 18:19:54 YES 28 23-JUL-02 18:19:59 YES 29 23-JUL-02 18:20:03 YES 30 23-JUL-02 18:20:13 YES 31 23-JUL-02 18:20:18 YES 32 23-JUL-02 18:20:21 YES 33 23-JUL-02 18:32:11 YES 34 23-JUL-02 18:32:19 CURRENT 35 23-JUL-02 19:13:20 CURRENT 36 23-JUL-02 19:13:43 CURRENT 37 23-JUL-02 19:13:46 CURRENT 38 23-JUL-02 19:13:50 CURRENT 39 23-JUL-02 19:13:54 CURRENT 40 23-JUL-02 19:14:01 CURRENT 41 23-JUL-02 19:15:11 NO 42 23-JUL-02 19:15:54 NO 19 rows selected.
Logical standby databases maintain user tables, sequences, and jobs. To maintain other objects, you must reissue the DDL statements seen in the redo data stream. Tables in the SYS schema are never maintained, because only Oracle metadata is maintained in the SYS schema.
If a SQL apply operation fails, an error is recorded in the DBA_LOGSTDBY_EVENTS table. The following sections demonstrate how to recover from two such errors.
DDL statements are executed the same way on both the primary database and logical standby databases. If the underlying file structure is the same on both databases, the DDL will execute on the standby database as expected. However, if the structure of the file system on the standby system differs from the file system on the primary system, it is likely that an error might result because the DB_FILE_NAME_CONVERT will not convert the filenames of one or more sets of datafiles on the primary database to filenames on the standby database for a logical standby database.
If an 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 procedure. The failed DDL statement will always be the last transaction. For example:
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 log apply services restart, they will attempt to re-execute the transaction that failed. If you do not want to re-execute it, provide the values to the DBMS_LOGSTDBY.SKIP_TRANSACTION procedure (see step 3 for an example) to skip the transaction.
In some situations, the problem that caused the transaction to fail can be corrected and log apply services restarted without skipping the transaction. An example of this might be when available space is exhausted. The example shows log apply services stopping, how to correct the error, and then restart log apply services. For example:
SQL> SELECT * FROM DBA_LOGSTDBY_EVENTS; EVENT_TIM CURRENT_SCN COMMIT_SCN XIDUSN XIDSLT XIDSQN --------- ----------- ---------- ---------- ---------- ---------- EVENT -------------------------------------------------------------------------------- STATUS_CODE ----------- STATUS -------------------------------------------------------------------------------- 30-JUL-02 16111 ORA-16111: log mining and apply setting up 30-JUL-02 200240 200243 1 2 2213 create table bar (x number, y number) tablespace foo 16204 ORA-16204: DDL successfully applied 30-JUL-02 200695 200735 1 11 2215 SCOTT.BAR (Oper=INSERT) 1653 ORA-01653: unable to extend table SCOTT.BAR by %d in tablespace 30-JUL-02 200812 200864 1 11 2215 SCOTT.BAR (Oper=INSERT) 1653 ORA-01653: unable to extend table SCOTT.BAR by %d in tablespace
In the example, the ORA-01653 message indicates that the tablespace was full and unable to extend itself. To correct the problem, add a new datafile to the tablespace. For example:
SQL> ALTER TABLESPACE t_table ADD DATAFILE 'dbs/t_db.f' SIZE 60M; Tablespace altered.
Then, restart log apply services:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY; Database altered.
When log apply services restart, the transaction that failed will be re-executed and applied to the logical standby database.
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 might be damaged by such an action.
DML failures usually indicate a problem with a specific table. For example, assume the failure is an out-of-storage error that you cannot resolve immediately. The following steps demonstrate one way to respond to this problem.
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 correct the storage problem, you can fix the table, provided that you set up a database link to the primary database that has administrator privileges to run procedures in the DBMS_LOGSTDBY package.
SCOTT.EMP table and then re-create it, and pull the data over to the standby database. The link supplied to this procedure must have LOGSTDBY_ADMINISTRATOR role granted on the primary 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 will contain records as of when the INSTANTIATE_TABLE procedure was performed (in step 2), it is possible for the SCOTT.EMP table to contain records for a department not in the SCOTT.DEPT table.Materialized views refreshed on the primary database are not automatically refreshed separately on a logical standby database. To refresh materialized views on the logical standby database, use the GUARD_BYPASS_ON and GUARD_BYPASS_OFF procedures of the DBMS_LOGSTDBY package. For example:
EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_ON; EXECUTE DBMS_MVIEW.REFRESH ( 'BMVIEW', 'F', '',TRUE,FALSE,0,0,0,FALSE); EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_OFF;
| See Also:
Oracle9i Supplied PL/SQL Packages and Types Reference for more information about the |
If you are using the DBMS_LOGSTDBY.APPLY_SET procedure but you are not using the default value of FULL for the TRANSACTION_CONSISTENCY parameter, you should stop SQL apply operations before refreshing materialized views on the logical standby database.
Take the following actions to increase system performance:
RELY constraint. On the logical standby database, create an index on the columns that make up the primary key. The following query generates a list of tables with no index information that can be used by a logical standby database to apply to uniquely identify rows. By creating an index on the following tables, performance can be improved significantly.
SQL> SELECT OWNER, TABLE_NAME FROM DBA_TABLES 2> WHERE OWNER NOT IN('SYS','SYSTEM','OUTLN','DBSNMP') 3> MINUS 3> SELECT DISTINCT TABLE_OWNER, TABLE_NAME FROM DBA_INDEXES 4> WHERE INDEX_TYPE NOT LIKE ('FUNCTION-BASED%') 5> MINUS 6> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED;
The following example shows the creation of an index for the table EMP. This should be done for all the tables returned by the previous query:
SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_ON; SQL> CREATE INDEX EMPI ON EMP (EMPNO); SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_OFF;
| See Also:
Section 4.1.2 and Oracle9i SQL Reference for more information about |
Statistics should be gathered on the standby database because DML/DDL operations on the primary are executed as a function of the workload. While the standby database is logically equivalent to the primary, SQL apply operations might execute the workload in a different way. This is why using the DBMS_STATS package on the logical standby database and the V$SYSSTAT view can be useful in determining which tables are consuming the most resources and table scan operations.
Use the TRANSACTION_CONSISTENCY parameter of the DBMS_LOGSTDBY.APPLY_SET procedure to control how transactions are applied to the logical standby database. The default setting is FULL, which applies transactions to the logical standby database in the same order in which they were committed on the primary database.
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.
READ_ONLY
Transactions are applied out of order from how they were committed on the primary database. The READ_ONLY option 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 applied 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.
Use the PARALLEL_MAX_SERVERS initialization parameter to adjust 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.
You can use the MAX_SERVERS parameter of the DBMS_LOGSTDBY.APPLY_SET procedure 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 initialization parameter. If you set this parameter explicitly, do not set it to a value less than 5 or greater than the value of the PARALLEL_MAX_SERVERS initialization parameter.
Increasing the number of parallel execution processes and parallel recovery processes for an instance can speed up execution and recovery operations, but this improvement must be balanced against the consumption of additional system resources by the processes.
You can use the MAX_SGA parameter of the DBMS_LOGSTDBY.APPLY_SET procedure to set the maximum amount of shared pool space used by log apply services for redo cache. 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 © 1998, 2002 Oracle Corporation. All Rights Reserved. |
|