manage logical standby database
1,The DBA_LOGSTDBY_EVENTS view record interesting events that occurred during the operation of SQL Apply.
By default, the view records the most recent 100 events. However, you can change the number of recorded events
by calling DBMS_LOGSTDBY.APPLY_SET() PL/SQL procedure. If SQL Apply should stop unexpectedly,
the reason for the problem is also recorded in this view
DBA_LOGSTDBY_EVENTS
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
Session altered.
SQL> COLUMN STATUS FORMAT A60
SQL> SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS
2 ORDER BY EVENT_TIMESTAMP, COMMIT_SCN;
2,
DBA_LOGSTDBY_LOG View
The DBA_LOGSTDBY_LOG view provides dynamic information about archived logs being processed by SQL Apply.
For example:
SQL> COLUMN DICT_BEGIN FORMAT A10;
SQL> SET NUMF 9999999
SQL> SELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS FCHANGE#, -
NEXT_CHANGE# AS NCHANGE#, TIMESTAMP, -
DICT_BEGIN AS BEG, DICT_END AS END, -
THREAD# AS THR# FROM DBA_LOGSTDBY_LOG -
ORDER BY SEQUENCE#;
FILE_NAME SEQ# F_SCN N_SCN TIMESTAM BEG END THR# APPLIED
------------------------- ---- ------- ------- -------- --- --- --- ---------
/oracle/dbs/hq_nyc_2.log 2 101579 101588 11:02:58 NO NO 1 YES
/oracle/dbs/hq_nyc_3.log 3 101588 142065 11:02:02 NO NO 1 YES
/oracle/dbs/hq_nyc_4.log 4 142065 142307 11:02:10 NO NO 1 YES
/oracle/dbs/hq_nyc_5.log 5 142307 142739 11:02:48 YES YES 1 YES
/oracle/dbs/hq_nyc_6.log 6 142739 143973 12:02:10 NO NO 1 YES
/oracle/dbs/hq_nyc_7.log 7 143973 144042 01:02:11 NO NO 1 YES
/oracle/dbs/hq_nyc_8.log 8 144042 144051 01:02:01 NO NO 1 YES
/oracle/dbs/hq_nyc_9.log 9 144051 144054 01:02:16 NO NO 1 YES
/oracle/dbs/hq_nyc_10.log 10 144054 144057 01:02:21 NO NO 1 YES
/oracle/dbs/hq_nyc_11.log 11 144057 144060 01:02:26 NO NO 1 CURRENT
/oracle/dbs/hq_nyc_12.log 12 144060 144089 01:02:30 NO NO 1 CURRENT
/oracle/dbs/hq_nyc_13.log 13 144089 144147 01:02:41 NO NO 1 NO
The YES entries in the BEG and END columns indicate that a LogMiner dictionary
build starts at log file sequence number 5. The most recent archived redo log file is sequence number 13,
and it was received at the logical standby database at 01:02:41.The APPLIED column indicates that SQL Apply has applied all redo before SCN 144057.
Since transactions can span multiple archived log files,
multiple archived log files may show the value CURRENT in the APPLIED column.
3
V$LOGSTDBY_STATS View
This view provides information related to the failover characteristics of the logical standby database, including:
*
The time to failover (apply finish time)
*
How current is the committed data in the logical standby database (lag time)
*
What the potential data loss will be in the event of a disaster (potential data loss).
For example:
SQL> SELECT NAME, VALUE, TIME_COMPUTED FROM V$LOGSTDBY_STATS;
NAME VALUE TIME_COMPUTED
------------------ -------------- ---------------------
apply finish time +00 00:00:00.1 07-APR-2005 08:29:23
lag time +00 00:00:00.1 07-APR-2005 08:29:23
potential data loss +00 00:00:00 07-APR-2005 08:29:23
The unit (metric) of each of the columns displayed is in day (2) to second (1) interval.
The output identifies a logical standby database that is caught up within 0.1 second of the primary database,
and no data loss will occur in the event of a primary failure.
4
V$LOGSTDBY_PROCESS View
This view provides information about the current state of the various processes involved with SQL Apply, including;
*
Identifying information (sid | serial# | spid)
*
SQL Apply process: COORDINATOR, READER, BUILDER, PREPARER, ANALYZER, or APPLIER (type)
*
Status of the process's current activity (status_code | status)
*
Highest redo record processed by this process (high_scn)
For example:
SQL> COLUMN LID FORMAT 9999
SQL> COLUMN SERIAL# FORMAT 9999
SQL> COLUMN SID FORMAT 9999
SQL> SELECT SID, SERIAL#, LOGSTDBY_ID AS LID, SPID, TYPE, HIGH_SCN FROM V$LOGSTDBY_PROCESS;
SID SERIAL# LID SPID TYPE HIGH_SCN
----- ------- ----- ------------ ---------------- ----------
48 6 -1 11074 COORDINATOR 7178242899
56 56 0 10858 READER 7178243497
46 1 1 10860 BUILDER 7178242901
45 1 2 10862 PREPARER 7178243295
37 1 3 10864 ANALYZER 7178241034
36 1 4 10866 APPLIER 7178239467
35 3 5 10868 APPLIER 7178239463
34 7 6 10870 APPLIER 7178239461
33 1 7 10872 APPLIER 7178239472
9 rows selected.
The HIGH_SCN column shows that the reader process is ahead of all other processes, and the PREPARER and BUILDER process ahead of the rest.
SQL> COLUMN STATUS FORMAT A40
SQL> SELECT TYPE, STATUS_CODE, STATUS FROM V$LOGSTDBY_PROCESS;
TYPE STATUS_CODE STATUS
---------------- ----------- -----------------------------------------
COORDINATOR 16117 ORA-16117: processing
READER 16127 ORA-16127: stalled waiting for additional
transactions to be applied
BUILDER 16116 ORA-16116: no work available
PREPARER 16116 ORA-16117: processing
ANALYZER 16120 ORA-16120: dependencies being computed for
transaction at SCN 0x0001.abdb440a
APPLIER 16124 ORA-16124: transaction 1 13 1427 is waiting
on another transaction
APPLIER 16121 ORA-16121: applying transaction with commit
SCN 0x0001.abdb4390
APPLIER 16123 ORA-16123: transaction 1 23 1231 is waiting
for commit approval
APPLIER 16116 ORA-16116: no work available
The output shows a snapshot of SQL Apply running. On the mining side, the READER process is waiting for additional memory to become available before it can read more,
the PREPARER process is processing redo records, and the BUILDER process has no work available. On the apply side, the COORDINATOR is assigning more transactions to APPLIER processes, the ANALYZER is computing dependencies at SCN 7178241034,
one APPLIER has no work available, while two have outstanding dependencies that are not yet satisfied.
10
V$LOGSTDBY_PROCESS View
This view provides information about the current state of the various processes involved with SQL Apply, including;
*
Identifying information (sid | serial# | spid)
*
SQL Apply process: COORDINATOR, READER, BUILDER, PREPARER, ANALYZER, or APPLIER (type)
*
Status of the process's current activity (status_code | status)
*
Highest redo record processed by this process (high_scn)
For example:
SQL> COLUMN LID FORMAT 9999
SQL> COLUMN SERIAL# FORMAT 9999
SQL> COLUMN SID FORMAT 9999
SQL> SELECT SID, SERIAL#, LOGSTDBY_ID AS LID, SPID, TYPE, HIGH_SCN FROM V$LOGSTDBY_PROCESS;
SID SERIAL# LID SPID TYPE HIGH_SCN
----- ------- ----- ------------ ---------------- ----------
48 6 -1 11074 COORDINATOR 7178242899
56 56 0 10858 READER 7178243497
46 1 1 10860 BUILDER 7178242901
45 1 2 10862 PREPARER 7178243295
37 1 3 10864 ANALYZER 7178241034
36 1 4 10866 APPLIER 7178239467
35 3 5 10868 APPLIER 7178239463
34 7 6 10870 APPLIER 7178239461
33 1 7 10872 APPLIER 7178239472
9 rows selected.
The HIGH_SCN column shows that the reader process is ahead of all other processes, and the PREPARER and BUILDER process ahead of the rest.
SQL> COLUMN STATUS FORMAT A40
SQL> SELECT TYPE, STATUS_CODE, STATUS FROM V$LOGSTDBY_PROCESS;
TYPE STATUS_CODE STATUS
---------------- ----------- -----------------------------------------
COORDINATOR 16117 ORA-16117: processing
READER 16127 ORA-16127: stalled waiting for additional
transactions to be applied
BUILDER 16116 ORA-16116: no work available
PREPARER 16116 ORA-16117: processing
ANALYZER 16120 ORA-16120: dependencies being computed for
transaction at SCN 0x0001.abdb440a
APPLIER 16124 ORA-16124: transaction 1 13 1427 is waiting
on another transaction
APPLIER 16121 ORA-16121: applying transaction with commit
SCN 0x0001.abdb4390
APPLIER 16123 ORA-16123: transaction 1 23 1231 is waiting
for commit approval
APPLIER 16116 ORA-16116: no work available
The output shows a snapshot of SQL Apply running. On the mining side, the READER process is waiting for additional memory to become available before it can read more,
the PREPARER process is processing redo records, and the BUILDER process has no work available. On the apply side,
the COORDINATOR is assigning more transactions to APPLIER processes, the ANALYZER is computing dependencies at SCN 7178241034,
one APPLIER has no work available, while two have outstanding dependencies that are not yet satisfied.
11
V$LOGSTDBY_STATE View
This view provides a synopsis of the current state of SQL Apply, including:
*
The DBID of the primary database (primary_dbid).
*
The LogMiner session ID allocated to SQL Apply (session_id).
*
Whether or not SQL Apply is applying in real time (realtime_apply).
*
Where SQL Apply is currently with regard to loading the LogMiner Multiversioned Data Dictionary (described in Section 4.2.3.2, "Build a Dictionary in the Redo Data"), receiving redo from the primary database, and applying redo data (STATE)
For example:
SQL> COLUMN REALTIME_APPLY FORMAT a15
SQL> COLUMN STATE FORMAT a16
SQL> SELECT * FROM V$LOGSTDBY_STATE;
PRIMARY_DBID SESSION_ID REALTIME_APPLY STATE
------------ ---------- --------------- ----------------
1562626987 1 Y APPLYING
The output shows that SQL Apply is running in the real-time apply mode and is currently applying redo data received from the primary database, the primary database's DBID is 1562626987 and the LogMiner session identifier associated the SQL Apply session is 1.
12，在邏輯備庫如何清除已經sql apply的歸檔日誌
Although SQL Apply automatically deletes archived redo log files when they are no longer needed on the logical standby database, there may be times when you want to manually remove them (for example, to reclaim disk space).
If you are overriding the default automatic log deletion capability, perform. the following steps to identify and delete archived redo log files that are no longer needed by SQL Apply:
1.
To purge the logical standby session of metadata that is no longer needed, enter the following PL/SQL statement:
SQL> EXECUTE DBMS_LOGSTDBY.PURGE_SESSION;
This statement also updates the DBA_LOGMNR_PURGED_LOG view that displays the archived redo log files that are no longer needed.
2.
Query the DBA_LOGMNR_PURGED_LOG view to list the archived redo log files that can be removed:
SQL> SELECT * FROM DBA_LOGMNR_PURGED_LOG;
FILE_NAME
------------------------------------
/boston/arc_dest/arc_1_40_509538672.log
/boston/arc_dest/arc_1_41_509538672.log
/boston/arc_dest/arc_1_42_509538672.log
/boston/arc_dest/arc_1_43_509538672.log
/boston/arc_dest/arc_1_44_509538672.log
/boston/arc_dest/arc_1_45_509538672.log
/boston/arc_dest/arc_1_46_509538672.log
/boston/arc_dest/arc_1_47_509538672.log
3.
Use an operating system-specific command to delete the archived redo log files listed by the query.
9.4 Customizing a Logical Standby Database
This section contains the following topics:
*
Using Real-Time Apply On the Logical Standby Database
*
Customizing Logging of Events in the DBA_LOGSTDBY_EVENTS View
*
Using DBMS_LOGSTDBY.SKIP to Prevent Changes to Specific Schema Objects
*
Setting up a Skip Handler for a DDL Statement
*
Modifying a Logical Standby Database
*
Adding or Re-Creating Tables On a Logical Standby Database
13
Using DBMS_LOGSTDBY.SKIP to Prevent Changes to Specific Schema Objects
By default, all supported tables in the primary database are replicated in the logical standby database. You can change the default behavior. by specifying rules to skip applying modifications to specific tables. For example, to omit changes to the HR.EMPLOYEES table, you can specify rules to prevent application of DML and DDL changes to the specific table. For example:
1.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
2.
Register the SKIP rules:
SQL> EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'DML', schema_name => 'HR', -
object_name => 'EMPLOYEES', proc_name => null);
SQL> EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'SCHEMA_DDL', schema_name => 'HR', -
object_name => 'EMPLOYEES', proc_name => null);
3.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
14
Setting up a Skip Handler for a DDL Statement
You can create a procedure to intercept certain DDL statements and replace the original DDL statement with a different one. For example, if the file system organization in the logical standby database is different than that in the primary database, you can write a DBMS_LOGSTDBY.SKIP procedure to transparently handle DDL transactions with file specifications.
The following procedure can handle different file system organization between the primary database and standby database, as long as you use a specific naming convention for your file-specification string.
1.
Create the skip procedure to handle tablespace DDL transactions:
CREATE OR REPLACE PROCEDURE SYS.HANDLE_TBS_DDL (
OLD_STMT IN VARCHAR2,
STMT_TYP IN VARCHAR2,
SCHEMA IN VARCHAR2,
NAME IN VARCHAR2,
XIDUSN IN NUMBER,
XIDSLT IN NUMBER,
XIDSQN IN NUMBER,
ACTION OUT NUMBER,
NEW_STMT OUT VARCHAR2
) AS
BEGIN
-- All primary file specification that contains a directory
-- /usr/orcl/primary/dbs
-- should go to /usr/orcl/stdby directory specification
NEW_STMT = REPLACE(OLD_STMT,
'/usr/orcl/primary/dbs',
'/usr/orcl/stdby');
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;
EXCEPTION
WHEN OTHERS THEN
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;
NEW_STMT := NULL;
END HANDLE_TBS_DDL;
2.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
3.
Register the skip procedure with SQL Apply:
SQL> EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'TABLESPACE', -
proc_name => 'sys.handle_tbs_ddl');
4.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
15
Performing DDL on a Logical Standby Database
This section describes how to add a constraint to a table maintained through SQL Apply.
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 SQL Apply, 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> ALTER SESSION DISABLE GUARD;
PL/SQL procedure successfully completed.
SQL> ALTER TABLE SCOTT.EMP ADD CONSTRAINT EMPID UNIQUE (EMPNO);
Table altered.
SQL> ALTER SESSION ENABLE GUARD;
PL/SQL procedure successfully completed.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
Database altered.
Oracle recommends that you do not perform. DML operations on tables maintained by SQL Apply 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.
16
Modifying Tables That Are Not Maintained by SQL Apply
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 the 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. To do this, you must:
*
Specify the set of tables on the logical standby database to which an application can write data by executing the DBMS_LOGSTDBY.SKIP procedure. Skipped tables are not maintained through SQL Apply.
*
Set the database guard to protect only standby tables.
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, skips the tables, and then restarts SQL Apply 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.
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered.
SQL> EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL',-
schema_name => 'HR', -
object_name => 'TESTEMP%');
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGSTDBY.SKIP('DML','HR','TESTEMP%');
PL/SQL procedure successfully completed.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.
Once SQL Apply starts, it needs to update metadata on the standby database for the newly specified tables added in the skip rules. Attempts to modify the newly skipped table until SQL Apply has had a chance to update the metadata will fail. You can find out if SQL Apply has successfully taken into account the SKIP rule you just added by issuing the following query:
SQL> SELECT VALUE FROM DBA_LOGSDTBY_PARAMETERS
WHERE NAME = 'GUARD_STANDBY';
VALUE
---------------
Ready
Once the VALUE column displays "Ready" SQL Apply has successfully updated all relevant metadata for the skipped table, and it is safe to modify the table.
17
Adding or Re-Creating Tables On a Logical Standby Database
Typically, you use table instantiation to re-create a table after an unrecoverable operation. You can also use the procedure to enable SQL Apply on a table that was formerly skipped.
Before you can create a table, it must meet the requirements described in Section 4.1.2, "Ensure Table Rows in the Primary Database Can Be Uniquely Identified". Then, you can use the following steps to re-create a table named HR.EMPLOYEES and resume SQL Apply. The directions assume that there is already a database link BOSTON defined to access the primary database.
The following list shows how to re-create a table and restart SQL Apply on that table:
1.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
2.
Ensure no operations are being skipped for the table in question by querying the DBA_LOGSTDBY_SKIP view:
SQL> SELECT * FROM DBA_LOGSTDBY_SKIP;
ERROR STATEMENT_OPT OWNER NAME PROC
----- ------------------- ------------- ---------------- -----
N SCHEMA_DDL HR EMPLOYEES
N DML HR EMPLOYEES
N SCHEMA_DDL OE TEST_ORDER
N DML OE TEST_ORDER
Because you already have skip rules associated with the table that you want to re-create on the logical standby database, you must first delete those rules. You can accomplish that by calling the DBMS_LOGSTDBY.UNSKIP procedure. For example:
SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP(stmt => 'DML', -
schema_name => 'HR', - object_name => 'EMPLOYEES');SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP(stmt => 'SCHEMA_DDL', -
schema_name => 'HR', - object_name => 'EMPLOYEES');
3.
Re-create the table HR.EMPLOYEES with all its data in the logical standby database by using the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure. For example:
SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE(shema_name => 'HR', -
object-+_name => 'EMPLOYEES', -
dblink => 'BOSTON');
4.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
To ensure a consistent view across the newly instantiated table and the rest of the database, wait for SQL Apply to catch up with the primary database before querying this table. You can do this by performing the following steps:
1.
On the primary database, determine the current SCN by querying the V$DATABASE view:
SQL> SELECT CURRENT_SCN FROM V$DATABASE@BOSTON;
CURRENT_SCN
---------------------
345162788
2.
Make sure SQL Apply has applied all transactions committed before the CURRENT_SCN returned in the previous query:
SQL> SELECT APPLIED_SCN FROM V$LOGSTDBY_PROGRESS;
APPLIED_SCN
--------------------------
345161345
When the APPLIED_SCN returned in this query is greater than the CURRENT_SCN returned in the first query, it is safe to query the newly re-created table.
18
Importing a Transportable Tablespace to the Primary Database
Perform. the following steps to import a tablespace to the primary database.
1.
Disable the guard setting so that you can modify the logical standby database:
SQL> ALTER SESSION DISABLE GUARD;
2.
Import the tablespace at the logical standby database.
3.
Enable the database guard setting (or disconnect from the session):
SQL> ALTER SESSION ENABLE GUARD;
4.
Import the tablespace at the primary database.
19
Using Materialized Views
SQL Apply does not support these DDL statements related to materialized views:
*
CREATE, ALTER, or DROP MATERIALIZED VIEW
*
CREATE, ALTER, or DROP MATERIALIZED VIEW LOG
Thus, new materialized views that have been created, altered, or dropped on the primary database after the logical standby database has been created are not reflected on the logical standby database. However, materialized views created on the primary database before the logical standby database has been created are also present on the logical standby database.
*
For materialized views that exist on both the primary and logical standby databases, an ON-COMMIT materialized view is refreshed on the logical standby database when the transaction commit occurs.
An ON-DEMAND materialized view is not automatically refreshed by SQL Apply. You must execute the DBMS_MVIEW.REFRESH procedure to refresh it. For example, to refresh an ON-DEMAND materialized view named HR.DEPARTMENTS_MV on a logical standby database using the fast refresh method, issue the following command:
SQL> EXECUTE DBMS_MVIEW.REFRESH (-
LIST => 'HR.DEPARTMENTS_MV', -
METHOD => 'F');
*
Additional ON-COMMIT materialized views created on the logical standby database are automatically maintained.
*
Additional ON-DEMAND materialized views created on the logical standby database are not maintained by SQL Apply, and you must refresh these using the DBMS_MVIEW.REFRESH procedure.
20
Create a Primary Key RELY Constraint
On the primary database, if a table does not have a primary key or a unique index and you are certain the rows are unique, then create a primary key 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;
You can add a rely primary key constraint to a table on the primary database, as follows:
1.
Add the primary key rely constraint at the primary database
SQL> ALTER TABLE HR.TEST_EMPLOYEES ADD PRIMARY KEY (EMPNO) RELY DISABLE;
SQL> ALTER SESSION DISABLE GUARD;
This will ensure that the EMPNO column, which can be used to uniquely identify the rows in HR.TEST_EMPLOYEES table, will be supplementally logged as part of any updates done on that table.
Note that the HR.TEST_EMPLOYEES table still does not have any unique index specified on the logical standby database. This may cause UPDATE statements to do full table scans on the logical standby database. You can remedy that by adding a unique index on the EMPNO column on the logical standby database.See Section 4.1.2, "Ensure Table Rows in the Primary Database Can Be Uniquely Identified" and Oracle Database SQL Reference for more information about RELY constraints.
2.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
3.
Disable the guard so that you can modify a maintained table on the logical standby database:
SQL> ALTER SESSION DISABLE GUARD;
4.
Add a unique index on EMPNO column:
SQL> CREATE UNIQUE INDEX UI_TEST_EMP ON HR.TEST_EMPLOYEES (EMPNO);
5.
Enable the guard:
SQL> ALTER SESSION ENABLE GUARD;
6.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
21
Adjusting the Number of APPLIER Processes
Perform. the following steps to find out whether adjusting the number of APPLIER processes will help you achieve greater throughput:
1.
Determine if APPLIER processes are busy by issuing the following query:
SQL> SELECT COUNT(*) AS IDLE_APPLIER
FROM V$LOGSTDBY_PROCESS
WHERE TYPE = 'APPLIER' and status_code = 16166;
IDLE_APPLIER
-------------------------
0
2.
Once you are sure there are no idle APPLIER processes, issue the following query to ensure there is enough work available for additional APPLIER processes if you choose to adjust the number of APPLIERS:
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS
WHERE NAME LIKE 'TRANSACTIONS%';
NAME VALUE
--------------------- -------
transactions ready 27896
transactions applied 25671
These two statistics keep a cumulative total of transactions that are ready to be applied by the APPLIER processes and the number of transactions that have already been applied.
If the number (transactions ready - transactions applied) is higher than twice the number of APPLIER processes available, an improvement in throughput is possible if you increase the number of APPLIER processes.
Note:
The number is a rough measure of ready work. The workload may be such that an interdependency between ready transactions will prevent additional available APPLIER processes from applying them. For instance, if the majority of the transactions that are ready to be applied are DDL transactions, adding more APPLIER processes will not result in a higher throughput.
To adjust the number of APPLIER processes to 20 from the default value of 5, perform. the following steps:
1.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered
2.
Set the number of APPLY_SERVERS to 20:
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS', 20);
PL/SQL procedure successfully completed
3.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered
9.6.3.2 Adjusting the Number of PREPARER Processes
In only rare cases do you need to adjust the number of PREPARER processes. Before you decide to increase the number of PREPARER processes, ensure the following conditions are true:
*
All PREPARER processes are busy
*
The number of transactions ready to be applied is less than the number of APPLIER processes available
*
There are idle APPLIER processes
The following steps show how to determine these conditions are true:
1.
Ensure all PREPARER processes are busy:
SQL> SELECT COUNT(*) AS IDLE_PREPARER
FROM V$LOGSTDBY_PROCESS
WHERE TYPE = 'PREPARER' and status_code = 16166;
IDLE_PREPARER
-------------
0
2.
Ensure the number of transactions ready to be applied is less than the number of APPLIER processes:
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS
WHERE NAME LIKE 'transactions%';
NAME VALUE
--------------------- -------transactions ready 27896
transactions applied 27892
SQL> SELECT COUNT(*) AS APPLIER_COUNT
FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER';
APPLIER_COUNT
-------------
20
Note: Issue this query several times to ensure this is not a transient event.
3.
Ensure there are idle APPLIER processes:
SQL> SELECT COUNT(*) AS IDLE_APPLIER
FROM V$LOGSTDBY_PROCESS
WHERE TYPE = 'APPLIER' and status_code = 16166;
IDLE_APPLIER
-------------------------
19
In the example, all conditions have been satisfied. Therefore, you can now increase the number of PREPARER processes to 4 (from the default value of 1), by performing the following steps:
1.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered
2.
Set the number of PREPARE_SERVERS to 4:
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('PREPARE_SERVERS', 4);
PL/SQL procedure successfully completed
3.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered
9.6.4 Adjust the Memory Used for LCR Cache
For some workloads, SQL Apply may use a large number of pageout operations, thereby reducing the overall throughput of the system. To find out whether increasing memory allocated to LCR cache will be beneficial, perform. the following steps:
1.
Issue the following query to obtain a snapshot of pageout activity:
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS
WHERE NAME LIKE '%PAGE%' OR
NAME LIKE '%UPTIME%' OR NAME LIKE '%idle%';
NAME VALUE
-------------------------- ---------------
coordinator uptime in secs 894856
bytes paged out 20000
seconds spent in pageout 2
system idle time in secs 1000
2.
Issue the query again in 5 minutes:
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS
WHERE NAME LIKE '%PAGE%' OR
NAME LIKE '%UPTIME%' OR NAME LIKE '%idle%';
NAME VALUE
-------------------------- ---------------
coordinator uptime in secs 895156
bytes paged out 1020000
seconds spent in pageout 100
system idle time in secs 1000
3.
Compute the normalized pageout activity. For example:
Change in coordinator uptime (C)= (895156 – 894856) = 300 secs
Amount of additional idle time (I)= (1000 – 1000) = 0
Change in time spent in pageout (P) = (100 – 2) = 98 secs
Pageout time in comparison to uptime = P/(C-I) = 98/300 ~ 32.67%
Ideally, the pageout activity should not consume more than 5 percent of the total uptime. If you continue to take snapshots over an extended interval and you find the pageout activities continue to consume a significant portion of the apply time, increasing the memory size may provide some benefits. You can increase the memory allocated to SQL Apply by performing the following steps:
1.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered
2.
Set the memory allocated to LCR cache (for this example, the SGA is set to 1 GB):
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_SGA', 1024);
PL/SQL procedure successfully completed
Because the MAX_SGA is specified in megabytes (MB), increasing the memory to 1 GB is specified as 1024 (MB) in the example.
3.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered
9.6.5 Adjust How Transactions are Applied On the Logical Standby Database
By default transactions are applied on the logical standby database in the exact order in which they were committed on the primary database. The default order of committing transactions allow any reporting application to run transparently on the logical standby database. However, there are times (such as after a prolonged outage of the logical standby database due to hardware failure or upgrade) when you want the logical standby database to catch up with the primary database, and can tolerate not running the reporting applications for a while. In this case, you can change the default apply mode by performing the following steps:
1.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered
2.
Issue the following to allow transactions to be applied out of order from how they were committed on the primary databases:
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('PRESERVE_COMMIT_ORDER', 'FALSE');
PL/SQL procedure successfully completed
3.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered
Once you have caught up with the primary database (verify this by querying the V$LOGSTDBY_STATS view), and you are ready to open the logical standby database for reporting applications, you can change the apply mode as follows:
1.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered
2.
Restore the default value for the PRESERVE_COMMIT_ORDER parameter:
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_UNSET('PRESERVE_COMMIT_ORDER');
PL/SQL procedure successfully completed
3.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered
For a typical online transaction processing (OLTP) workload, the nondefault mode can provide a 50 percent or better throughput improvement over the default apply mode.
22
Examples of the CONFIGURE ARCHIVELOG DELETION POLICY Command
When backups of archived redo log files are taken on the standby database:
1.
Issue the following command on the primary database:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
2.
Issue the following command on the standby database:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
When backups of archived redo log files are taken on the primary database:
1.
Issue the following command on the standby database:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
2.
Issue the following command on the primary database:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
10.3.4.1 Reconfiguring the Deletion Policy After a Role Transition
After a switchover or failover, you may need to reissue the RMAN CONFIGURE ARCHIVELOG DELETION POLICY command on each database. If the backup site for archived redo log files remains the same, then do nothing. Otherwise, you must switch the archivelog deletion policy by issuing the CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY statement on the database where backups are not taken, and issuing the CONFIGURE ARCHIVELOG DELETION POLICY TO NONE statement on the database where backups are taken.
10.3.4.2 Viewing the Current Deletion Policy
To see the current setting (APPLIED ON STANDBY, CLEAR, NONE) for a database, issue the following query:
SELECT NAME, VALUE FROM V$RMAN_CONFIGURATION WHERE
NAME LIKE '%ARCHIVELOG DELETION POLICY%';
NAME VALUE
----------------------------- --------------
ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY
You can also find the existing configuration using the RMAN SHOW ARCHIVELOG DELETION POLICY command:
RMAN> SHOW ARCHIVELOG DELETION POLICY
RMAN configuration parameters are:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
By default, the view records the most recent 100 events. However, you can change the number of recorded events
by calling DBMS_LOGSTDBY.APPLY_SET() PL/SQL procedure. If SQL Apply should stop unexpectedly,
the reason for the problem is also recorded in this view
DBA_LOGSTDBY_EVENTS
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
Session altered.
SQL> COLUMN STATUS FORMAT A60
SQL> SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS
2 ORDER BY EVENT_TIMESTAMP, COMMIT_SCN;
2,
DBA_LOGSTDBY_LOG View
The DBA_LOGSTDBY_LOG view provides dynamic information about archived logs being processed by SQL Apply.
For example:
SQL> COLUMN DICT_BEGIN FORMAT A10;
SQL> SET NUMF 9999999
SQL> SELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS FCHANGE#, -
NEXT_CHANGE# AS NCHANGE#, TIMESTAMP, -
DICT_BEGIN AS BEG, DICT_END AS END, -
THREAD# AS THR# FROM DBA_LOGSTDBY_LOG -
ORDER BY SEQUENCE#;
FILE_NAME SEQ# F_SCN N_SCN TIMESTAM BEG END THR# APPLIED
------------------------- ---- ------- ------- -------- --- --- --- ---------
/oracle/dbs/hq_nyc_2.log 2 101579 101588 11:02:58 NO NO 1 YES
/oracle/dbs/hq_nyc_3.log 3 101588 142065 11:02:02 NO NO 1 YES
/oracle/dbs/hq_nyc_4.log 4 142065 142307 11:02:10 NO NO 1 YES
/oracle/dbs/hq_nyc_5.log 5 142307 142739 11:02:48 YES YES 1 YES
/oracle/dbs/hq_nyc_6.log 6 142739 143973 12:02:10 NO NO 1 YES
/oracle/dbs/hq_nyc_7.log 7 143973 144042 01:02:11 NO NO 1 YES
/oracle/dbs/hq_nyc_8.log 8 144042 144051 01:02:01 NO NO 1 YES
/oracle/dbs/hq_nyc_9.log 9 144051 144054 01:02:16 NO NO 1 YES
/oracle/dbs/hq_nyc_10.log 10 144054 144057 01:02:21 NO NO 1 YES
/oracle/dbs/hq_nyc_11.log 11 144057 144060 01:02:26 NO NO 1 CURRENT
/oracle/dbs/hq_nyc_12.log 12 144060 144089 01:02:30 NO NO 1 CURRENT
/oracle/dbs/hq_nyc_13.log 13 144089 144147 01:02:41 NO NO 1 NO
The YES entries in the BEG and END columns indicate that a LogMiner dictionary
build starts at log file sequence number 5. The most recent archived redo log file is sequence number 13,
and it was received at the logical standby database at 01:02:41.The APPLIED column indicates that SQL Apply has applied all redo before SCN 144057.
Since transactions can span multiple archived log files,
multiple archived log files may show the value CURRENT in the APPLIED column.
3
V$LOGSTDBY_STATS View
This view provides information related to the failover characteristics of the logical standby database, including:
*
The time to failover (apply finish time)
*
How current is the committed data in the logical standby database (lag time)
*
What the potential data loss will be in the event of a disaster (potential data loss).
For example:
SQL> SELECT NAME, VALUE, TIME_COMPUTED FROM V$LOGSTDBY_STATS;
NAME VALUE TIME_COMPUTED
------------------ -------------- ---------------------
apply finish time +00 00:00:00.1 07-APR-2005 08:29:23
lag time +00 00:00:00.1 07-APR-2005 08:29:23
potential data loss +00 00:00:00 07-APR-2005 08:29:23
The unit (metric) of each of the columns displayed is in day (2) to second (1) interval.
The output identifies a logical standby database that is caught up within 0.1 second of the primary database,
and no data loss will occur in the event of a primary failure.
4
V$LOGSTDBY_PROCESS View
This view provides information about the current state of the various processes involved with SQL Apply, including;
*
Identifying information (sid | serial# | spid)
*
SQL Apply process: COORDINATOR, READER, BUILDER, PREPARER, ANALYZER, or APPLIER (type)
*
Status of the process's current activity (status_code | status)
*
Highest redo record processed by this process (high_scn)
For example:
SQL> COLUMN LID FORMAT 9999
SQL> COLUMN SERIAL# FORMAT 9999
SQL> COLUMN SID FORMAT 9999
SQL> SELECT SID, SERIAL#, LOGSTDBY_ID AS LID, SPID, TYPE, HIGH_SCN FROM V$LOGSTDBY_PROCESS;
SID SERIAL# LID SPID TYPE HIGH_SCN
----- ------- ----- ------------ ---------------- ----------
48 6 -1 11074 COORDINATOR 7178242899
56 56 0 10858 READER 7178243497
46 1 1 10860 BUILDER 7178242901
45 1 2 10862 PREPARER 7178243295
37 1 3 10864 ANALYZER 7178241034
36 1 4 10866 APPLIER 7178239467
35 3 5 10868 APPLIER 7178239463
34 7 6 10870 APPLIER 7178239461
33 1 7 10872 APPLIER 7178239472
9 rows selected.
The HIGH_SCN column shows that the reader process is ahead of all other processes, and the PREPARER and BUILDER process ahead of the rest.
SQL> COLUMN STATUS FORMAT A40
SQL> SELECT TYPE, STATUS_CODE, STATUS FROM V$LOGSTDBY_PROCESS;
TYPE STATUS_CODE STATUS
---------------- ----------- -----------------------------------------
COORDINATOR 16117 ORA-16117: processing
READER 16127 ORA-16127: stalled waiting for additional
transactions to be applied
BUILDER 16116 ORA-16116: no work available
PREPARER 16116 ORA-16117: processing
ANALYZER 16120 ORA-16120: dependencies being computed for
transaction at SCN 0x0001.abdb440a
APPLIER 16124 ORA-16124: transaction 1 13 1427 is waiting
on another transaction
APPLIER 16121 ORA-16121: applying transaction with commit
SCN 0x0001.abdb4390
APPLIER 16123 ORA-16123: transaction 1 23 1231 is waiting
for commit approval
APPLIER 16116 ORA-16116: no work available
The output shows a snapshot of SQL Apply running. On the mining side, the READER process is waiting for additional memory to become available before it can read more,
the PREPARER process is processing redo records, and the BUILDER process has no work available. On the apply side, the COORDINATOR is assigning more transactions to APPLIER processes, the ANALYZER is computing dependencies at SCN 7178241034,
one APPLIER has no work available, while two have outstanding dependencies that are not yet satisfied.
10
V$LOGSTDBY_PROCESS View
This view provides information about the current state of the various processes involved with SQL Apply, including;
*
Identifying information (sid | serial# | spid)
*
SQL Apply process: COORDINATOR, READER, BUILDER, PREPARER, ANALYZER, or APPLIER (type)
*
Status of the process's current activity (status_code | status)
*
Highest redo record processed by this process (high_scn)
For example:
SQL> COLUMN LID FORMAT 9999
SQL> COLUMN SERIAL# FORMAT 9999
SQL> COLUMN SID FORMAT 9999
SQL> SELECT SID, SERIAL#, LOGSTDBY_ID AS LID, SPID, TYPE, HIGH_SCN FROM V$LOGSTDBY_PROCESS;
SID SERIAL# LID SPID TYPE HIGH_SCN
----- ------- ----- ------------ ---------------- ----------
48 6 -1 11074 COORDINATOR 7178242899
56 56 0 10858 READER 7178243497
46 1 1 10860 BUILDER 7178242901
45 1 2 10862 PREPARER 7178243295
37 1 3 10864 ANALYZER 7178241034
36 1 4 10866 APPLIER 7178239467
35 3 5 10868 APPLIER 7178239463
34 7 6 10870 APPLIER 7178239461
33 1 7 10872 APPLIER 7178239472
9 rows selected.
The HIGH_SCN column shows that the reader process is ahead of all other processes, and the PREPARER and BUILDER process ahead of the rest.
SQL> COLUMN STATUS FORMAT A40
SQL> SELECT TYPE, STATUS_CODE, STATUS FROM V$LOGSTDBY_PROCESS;
TYPE STATUS_CODE STATUS
---------------- ----------- -----------------------------------------
COORDINATOR 16117 ORA-16117: processing
READER 16127 ORA-16127: stalled waiting for additional
transactions to be applied
BUILDER 16116 ORA-16116: no work available
PREPARER 16116 ORA-16117: processing
ANALYZER 16120 ORA-16120: dependencies being computed for
transaction at SCN 0x0001.abdb440a
APPLIER 16124 ORA-16124: transaction 1 13 1427 is waiting
on another transaction
APPLIER 16121 ORA-16121: applying transaction with commit
SCN 0x0001.abdb4390
APPLIER 16123 ORA-16123: transaction 1 23 1231 is waiting
for commit approval
APPLIER 16116 ORA-16116: no work available
The output shows a snapshot of SQL Apply running. On the mining side, the READER process is waiting for additional memory to become available before it can read more,
the PREPARER process is processing redo records, and the BUILDER process has no work available. On the apply side,
the COORDINATOR is assigning more transactions to APPLIER processes, the ANALYZER is computing dependencies at SCN 7178241034,
one APPLIER has no work available, while two have outstanding dependencies that are not yet satisfied.
11
V$LOGSTDBY_STATE View
This view provides a synopsis of the current state of SQL Apply, including:
*
The DBID of the primary database (primary_dbid).
*
The LogMiner session ID allocated to SQL Apply (session_id).
*
Whether or not SQL Apply is applying in real time (realtime_apply).
*
Where SQL Apply is currently with regard to loading the LogMiner Multiversioned Data Dictionary (described in Section 4.2.3.2, "Build a Dictionary in the Redo Data"), receiving redo from the primary database, and applying redo data (STATE)
For example:
SQL> COLUMN REALTIME_APPLY FORMAT a15
SQL> COLUMN STATE FORMAT a16
SQL> SELECT * FROM V$LOGSTDBY_STATE;
PRIMARY_DBID SESSION_ID REALTIME_APPLY STATE
------------ ---------- --------------- ----------------
1562626987 1 Y APPLYING
The output shows that SQL Apply is running in the real-time apply mode and is currently applying redo data received from the primary database, the primary database's DBID is 1562626987 and the LogMiner session identifier associated the SQL Apply session is 1.
12，在邏輯備庫如何清除已經sql apply的歸檔日誌
Although SQL Apply automatically deletes archived redo log files when they are no longer needed on the logical standby database, there may be times when you want to manually remove them (for example, to reclaim disk space).
If you are overriding the default automatic log deletion capability, perform. the following steps to identify and delete archived redo log files that are no longer needed by SQL Apply:
1.
To purge the logical standby session of metadata that is no longer needed, enter the following PL/SQL statement:
SQL> EXECUTE DBMS_LOGSTDBY.PURGE_SESSION;
This statement also updates the DBA_LOGMNR_PURGED_LOG view that displays the archived redo log files that are no longer needed.
2.
Query the DBA_LOGMNR_PURGED_LOG view to list the archived redo log files that can be removed:
SQL> SELECT * FROM DBA_LOGMNR_PURGED_LOG;
FILE_NAME
------------------------------------
/boston/arc_dest/arc_1_40_509538672.log
/boston/arc_dest/arc_1_41_509538672.log
/boston/arc_dest/arc_1_42_509538672.log
/boston/arc_dest/arc_1_43_509538672.log
/boston/arc_dest/arc_1_44_509538672.log
/boston/arc_dest/arc_1_45_509538672.log
/boston/arc_dest/arc_1_46_509538672.log
/boston/arc_dest/arc_1_47_509538672.log
3.
Use an operating system-specific command to delete the archived redo log files listed by the query.
9.4 Customizing a Logical Standby Database
This section contains the following topics:
*
Using Real-Time Apply On the Logical Standby Database
*
Customizing Logging of Events in the DBA_LOGSTDBY_EVENTS View
*
Using DBMS_LOGSTDBY.SKIP to Prevent Changes to Specific Schema Objects
*
Setting up a Skip Handler for a DDL Statement
*
Modifying a Logical Standby Database
*
Adding or Re-Creating Tables On a Logical Standby Database
13
Using DBMS_LOGSTDBY.SKIP to Prevent Changes to Specific Schema Objects
By default, all supported tables in the primary database are replicated in the logical standby database. You can change the default behavior. by specifying rules to skip applying modifications to specific tables. For example, to omit changes to the HR.EMPLOYEES table, you can specify rules to prevent application of DML and DDL changes to the specific table. For example:
1.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
2.
Register the SKIP rules:
SQL> EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'DML', schema_name => 'HR', -
object_name => 'EMPLOYEES', proc_name => null);
SQL> EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'SCHEMA_DDL', schema_name => 'HR', -
object_name => 'EMPLOYEES', proc_name => null);
3.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
14
Setting up a Skip Handler for a DDL Statement
You can create a procedure to intercept certain DDL statements and replace the original DDL statement with a different one. For example, if the file system organization in the logical standby database is different than that in the primary database, you can write a DBMS_LOGSTDBY.SKIP procedure to transparently handle DDL transactions with file specifications.
The following procedure can handle different file system organization between the primary database and standby database, as long as you use a specific naming convention for your file-specification string.
1.
Create the skip procedure to handle tablespace DDL transactions:
CREATE OR REPLACE PROCEDURE SYS.HANDLE_TBS_DDL (
OLD_STMT IN VARCHAR2,
STMT_TYP IN VARCHAR2,
SCHEMA IN VARCHAR2,
NAME IN VARCHAR2,
XIDUSN IN NUMBER,
XIDSLT IN NUMBER,
XIDSQN IN NUMBER,
ACTION OUT NUMBER,
NEW_STMT OUT VARCHAR2
) AS
BEGIN
-- All primary file specification that contains a directory
-- /usr/orcl/primary/dbs
-- should go to /usr/orcl/stdby directory specification
NEW_STMT = REPLACE(OLD_STMT,
'/usr/orcl/primary/dbs',
'/usr/orcl/stdby');
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;
EXCEPTION
WHEN OTHERS THEN
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;
NEW_STMT := NULL;
END HANDLE_TBS_DDL;
2.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
3.
Register the skip procedure with SQL Apply:
SQL> EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'TABLESPACE', -
proc_name => 'sys.handle_tbs_ddl');
4.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
15
Performing DDL on a Logical Standby Database
This section describes how to add a constraint to a table maintained through SQL Apply.
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 SQL Apply, 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> ALTER SESSION DISABLE GUARD;
PL/SQL procedure successfully completed.
SQL> ALTER TABLE SCOTT.EMP ADD CONSTRAINT EMPID UNIQUE (EMPNO);
Table altered.
SQL> ALTER SESSION ENABLE GUARD;
PL/SQL procedure successfully completed.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
Database altered.
Oracle recommends that you do not perform. DML operations on tables maintained by SQL Apply 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.
16
Modifying Tables That Are Not Maintained by SQL Apply
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 the 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. To do this, you must:
*
Specify the set of tables on the logical standby database to which an application can write data by executing the DBMS_LOGSTDBY.SKIP procedure. Skipped tables are not maintained through SQL Apply.
*
Set the database guard to protect only standby tables.
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, skips the tables, and then restarts SQL Apply 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.
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered.
SQL> EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL',-
schema_name => 'HR', -
object_name => 'TESTEMP%');
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGSTDBY.SKIP('DML','HR','TESTEMP%');
PL/SQL procedure successfully completed.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.
Once SQL Apply starts, it needs to update metadata on the standby database for the newly specified tables added in the skip rules. Attempts to modify the newly skipped table until SQL Apply has had a chance to update the metadata will fail. You can find out if SQL Apply has successfully taken into account the SKIP rule you just added by issuing the following query:
SQL> SELECT VALUE FROM DBA_LOGSDTBY_PARAMETERS
WHERE NAME = 'GUARD_STANDBY';
VALUE
---------------
Ready
Once the VALUE column displays "Ready" SQL Apply has successfully updated all relevant metadata for the skipped table, and it is safe to modify the table.
17
Adding or Re-Creating Tables On a Logical Standby Database
Typically, you use table instantiation to re-create a table after an unrecoverable operation. You can also use the procedure to enable SQL Apply on a table that was formerly skipped.
Before you can create a table, it must meet the requirements described in Section 4.1.2, "Ensure Table Rows in the Primary Database Can Be Uniquely Identified". Then, you can use the following steps to re-create a table named HR.EMPLOYEES and resume SQL Apply. The directions assume that there is already a database link BOSTON defined to access the primary database.
The following list shows how to re-create a table and restart SQL Apply on that table:
1.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
2.
Ensure no operations are being skipped for the table in question by querying the DBA_LOGSTDBY_SKIP view:
SQL> SELECT * FROM DBA_LOGSTDBY_SKIP;
ERROR STATEMENT_OPT OWNER NAME PROC
----- ------------------- ------------- ---------------- -----
N SCHEMA_DDL HR EMPLOYEES
N DML HR EMPLOYEES
N SCHEMA_DDL OE TEST_ORDER
N DML OE TEST_ORDER
Because you already have skip rules associated with the table that you want to re-create on the logical standby database, you must first delete those rules. You can accomplish that by calling the DBMS_LOGSTDBY.UNSKIP procedure. For example:
SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP(stmt => 'DML', -
schema_name => 'HR', - object_name => 'EMPLOYEES');SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP(stmt => 'SCHEMA_DDL', -
schema_name => 'HR', - object_name => 'EMPLOYEES');
3.
Re-create the table HR.EMPLOYEES with all its data in the logical standby database by using the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure. For example:
SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE(shema_name => 'HR', -
object-+_name => 'EMPLOYEES', -
dblink => 'BOSTON');
4.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
To ensure a consistent view across the newly instantiated table and the rest of the database, wait for SQL Apply to catch up with the primary database before querying this table. You can do this by performing the following steps:
1.
On the primary database, determine the current SCN by querying the V$DATABASE view:
SQL> SELECT CURRENT_SCN FROM V$DATABASE@BOSTON;
CURRENT_SCN
---------------------
345162788
2.
Make sure SQL Apply has applied all transactions committed before the CURRENT_SCN returned in the previous query:
SQL> SELECT APPLIED_SCN FROM V$LOGSTDBY_PROGRESS;
APPLIED_SCN
--------------------------
345161345
When the APPLIED_SCN returned in this query is greater than the CURRENT_SCN returned in the first query, it is safe to query the newly re-created table.
18
Importing a Transportable Tablespace to the Primary Database
Perform. the following steps to import a tablespace to the primary database.
1.
Disable the guard setting so that you can modify the logical standby database:
SQL> ALTER SESSION DISABLE GUARD;
2.
Import the tablespace at the logical standby database.
3.
Enable the database guard setting (or disconnect from the session):
SQL> ALTER SESSION ENABLE GUARD;
4.
Import the tablespace at the primary database.
19
Using Materialized Views
SQL Apply does not support these DDL statements related to materialized views:
*
CREATE, ALTER, or DROP MATERIALIZED VIEW
*
CREATE, ALTER, or DROP MATERIALIZED VIEW LOG
Thus, new materialized views that have been created, altered, or dropped on the primary database after the logical standby database has been created are not reflected on the logical standby database. However, materialized views created on the primary database before the logical standby database has been created are also present on the logical standby database.
*
For materialized views that exist on both the primary and logical standby databases, an ON-COMMIT materialized view is refreshed on the logical standby database when the transaction commit occurs.
An ON-DEMAND materialized view is not automatically refreshed by SQL Apply. You must execute the DBMS_MVIEW.REFRESH procedure to refresh it. For example, to refresh an ON-DEMAND materialized view named HR.DEPARTMENTS_MV on a logical standby database using the fast refresh method, issue the following command:
SQL> EXECUTE DBMS_MVIEW.REFRESH (-
LIST => 'HR.DEPARTMENTS_MV', -
METHOD => 'F');
*
Additional ON-COMMIT materialized views created on the logical standby database are automatically maintained.
*
Additional ON-DEMAND materialized views created on the logical standby database are not maintained by SQL Apply, and you must refresh these using the DBMS_MVIEW.REFRESH procedure.
20
Create a Primary Key RELY Constraint
On the primary database, if a table does not have a primary key or a unique index and you are certain the rows are unique, then create a primary key 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;
You can add a rely primary key constraint to a table on the primary database, as follows:
1.
Add the primary key rely constraint at the primary database
SQL> ALTER TABLE HR.TEST_EMPLOYEES ADD PRIMARY KEY (EMPNO) RELY DISABLE;
SQL> ALTER SESSION DISABLE GUARD;
This will ensure that the EMPNO column, which can be used to uniquely identify the rows in HR.TEST_EMPLOYEES table, will be supplementally logged as part of any updates done on that table.
Note that the HR.TEST_EMPLOYEES table still does not have any unique index specified on the logical standby database. This may cause UPDATE statements to do full table scans on the logical standby database. You can remedy that by adding a unique index on the EMPNO column on the logical standby database.See Section 4.1.2, "Ensure Table Rows in the Primary Database Can Be Uniquely Identified" and Oracle Database SQL Reference for more information about RELY constraints.
2.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
3.
Disable the guard so that you can modify a maintained table on the logical standby database:
SQL> ALTER SESSION DISABLE GUARD;
4.
Add a unique index on EMPNO column:
SQL> CREATE UNIQUE INDEX UI_TEST_EMP ON HR.TEST_EMPLOYEES (EMPNO);
5.
Enable the guard:
SQL> ALTER SESSION ENABLE GUARD;
6.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
21
Adjusting the Number of APPLIER Processes
Perform. the following steps to find out whether adjusting the number of APPLIER processes will help you achieve greater throughput:
1.
Determine if APPLIER processes are busy by issuing the following query:
SQL> SELECT COUNT(*) AS IDLE_APPLIER
FROM V$LOGSTDBY_PROCESS
WHERE TYPE = 'APPLIER' and status_code = 16166;
IDLE_APPLIER
-------------------------
0
2.
Once you are sure there are no idle APPLIER processes, issue the following query to ensure there is enough work available for additional APPLIER processes if you choose to adjust the number of APPLIERS:
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS
WHERE NAME LIKE 'TRANSACTIONS%';
NAME VALUE
--------------------- -------
transactions ready 27896
transactions applied 25671
These two statistics keep a cumulative total of transactions that are ready to be applied by the APPLIER processes and the number of transactions that have already been applied.
If the number (transactions ready - transactions applied) is higher than twice the number of APPLIER processes available, an improvement in throughput is possible if you increase the number of APPLIER processes.
Note:
The number is a rough measure of ready work. The workload may be such that an interdependency between ready transactions will prevent additional available APPLIER processes from applying them. For instance, if the majority of the transactions that are ready to be applied are DDL transactions, adding more APPLIER processes will not result in a higher throughput.
To adjust the number of APPLIER processes to 20 from the default value of 5, perform. the following steps:
1.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered
2.
Set the number of APPLY_SERVERS to 20:
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS', 20);
PL/SQL procedure successfully completed
3.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered
9.6.3.2 Adjusting the Number of PREPARER Processes
In only rare cases do you need to adjust the number of PREPARER processes. Before you decide to increase the number of PREPARER processes, ensure the following conditions are true:
*
All PREPARER processes are busy
*
The number of transactions ready to be applied is less than the number of APPLIER processes available
*
There are idle APPLIER processes
The following steps show how to determine these conditions are true:
1.
Ensure all PREPARER processes are busy:
SQL> SELECT COUNT(*) AS IDLE_PREPARER
FROM V$LOGSTDBY_PROCESS
WHERE TYPE = 'PREPARER' and status_code = 16166;
IDLE_PREPARER
-------------
0
2.
Ensure the number of transactions ready to be applied is less than the number of APPLIER processes:
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS
WHERE NAME LIKE 'transactions%';
NAME VALUE
--------------------- -------transactions ready 27896
transactions applied 27892
SQL> SELECT COUNT(*) AS APPLIER_COUNT
FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER';
APPLIER_COUNT
-------------
20
Note: Issue this query several times to ensure this is not a transient event.
3.
Ensure there are idle APPLIER processes:
SQL> SELECT COUNT(*) AS IDLE_APPLIER
FROM V$LOGSTDBY_PROCESS
WHERE TYPE = 'APPLIER' and status_code = 16166;
IDLE_APPLIER
-------------------------
19
In the example, all conditions have been satisfied. Therefore, you can now increase the number of PREPARER processes to 4 (from the default value of 1), by performing the following steps:
1.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered
2.
Set the number of PREPARE_SERVERS to 4:
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('PREPARE_SERVERS', 4);
PL/SQL procedure successfully completed
3.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered
9.6.4 Adjust the Memory Used for LCR Cache
For some workloads, SQL Apply may use a large number of pageout operations, thereby reducing the overall throughput of the system. To find out whether increasing memory allocated to LCR cache will be beneficial, perform. the following steps:
1.
Issue the following query to obtain a snapshot of pageout activity:
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS
WHERE NAME LIKE '%PAGE%' OR
NAME LIKE '%UPTIME%' OR NAME LIKE '%idle%';
NAME VALUE
-------------------------- ---------------
coordinator uptime in secs 894856
bytes paged out 20000
seconds spent in pageout 2
system idle time in secs 1000
2.
Issue the query again in 5 minutes:
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS
WHERE NAME LIKE '%PAGE%' OR
NAME LIKE '%UPTIME%' OR NAME LIKE '%idle%';
NAME VALUE
-------------------------- ---------------
coordinator uptime in secs 895156
bytes paged out 1020000
seconds spent in pageout 100
system idle time in secs 1000
3.
Compute the normalized pageout activity. For example:
Change in coordinator uptime (C)= (895156 – 894856) = 300 secs
Amount of additional idle time (I)= (1000 – 1000) = 0
Change in time spent in pageout (P) = (100 – 2) = 98 secs
Pageout time in comparison to uptime = P/(C-I) = 98/300 ~ 32.67%
Ideally, the pageout activity should not consume more than 5 percent of the total uptime. If you continue to take snapshots over an extended interval and you find the pageout activities continue to consume a significant portion of the apply time, increasing the memory size may provide some benefits. You can increase the memory allocated to SQL Apply by performing the following steps:
1.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered
2.
Set the memory allocated to LCR cache (for this example, the SGA is set to 1 GB):
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_SGA', 1024);
PL/SQL procedure successfully completed
Because the MAX_SGA is specified in megabytes (MB), increasing the memory to 1 GB is specified as 1024 (MB) in the example.
3.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered
9.6.5 Adjust How Transactions are Applied On the Logical Standby Database
By default transactions are applied on the logical standby database in the exact order in which they were committed on the primary database. The default order of committing transactions allow any reporting application to run transparently on the logical standby database. However, there are times (such as after a prolonged outage of the logical standby database due to hardware failure or upgrade) when you want the logical standby database to catch up with the primary database, and can tolerate not running the reporting applications for a while. In this case, you can change the default apply mode by performing the following steps:
1.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered
2.
Issue the following to allow transactions to be applied out of order from how they were committed on the primary databases:
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('PRESERVE_COMMIT_ORDER', 'FALSE');
PL/SQL procedure successfully completed
3.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered
Once you have caught up with the primary database (verify this by querying the V$LOGSTDBY_STATS view), and you are ready to open the logical standby database for reporting applications, you can change the apply mode as follows:
1.
Stop SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered
2.
Restore the default value for the PRESERVE_COMMIT_ORDER parameter:
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_UNSET('PRESERVE_COMMIT_ORDER');
PL/SQL procedure successfully completed
3.
Start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered
For a typical online transaction processing (OLTP) workload, the nondefault mode can provide a 50 percent or better throughput improvement over the default apply mode.
22
Examples of the CONFIGURE ARCHIVELOG DELETION POLICY Command
When backups of archived redo log files are taken on the standby database:
1.
Issue the following command on the primary database:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
2.
Issue the following command on the standby database:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
When backups of archived redo log files are taken on the primary database:
1.
Issue the following command on the standby database:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
2.
Issue the following command on the primary database:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
10.3.4.1 Reconfiguring the Deletion Policy After a Role Transition
After a switchover or failover, you may need to reissue the RMAN CONFIGURE ARCHIVELOG DELETION POLICY command on each database. If the backup site for archived redo log files remains the same, then do nothing. Otherwise, you must switch the archivelog deletion policy by issuing the CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY statement on the database where backups are not taken, and issuing the CONFIGURE ARCHIVELOG DELETION POLICY TO NONE statement on the database where backups are taken.
10.3.4.2 Viewing the Current Deletion Policy
To see the current setting (APPLIED ON STANDBY, CLEAR, NONE) for a database, issue the following query:
SELECT NAME, VALUE FROM V$RMAN_CONFIGURATION WHERE
NAME LIKE '%ARCHIVELOG DELETION POLICY%';
NAME VALUE
----------------------------- --------------
ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY
You can also find the existing configuration using the RMAN SHOW ARCHIVELOG DELETION POLICY command:
RMAN> SHOW ARCHIVELOG DELETION POLICY
RMAN configuration parameters are:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-626198/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 建立 Logical Standby DatabaseDatabase
- 監控Logical standby databaseDatabase
- Oracle DG建立Logical Standby DatabaseOracleDatabase
- Logical Standby Database的配置步驟.Database
- 建立Data guard logical standby database須知Database
- [江楓]In Memory Undo與logical standby databaseDatabase
- 4 Creating a Logical Standby Database 建立邏輯備庫Database
- DataGuard:Logical Standby Switchover
- DataGuard:Logical Standby FailoverAI
- alter database recover to logical standby xxx 很長時間，為什麼Database
- standby databaseDatabase
- Convert a Physical Standby Database into a Snapshot Standby DatabaseDatabase
- standby database to primary database.Database
- Physical Standby Database 切換到 Snapshot Standby DatabaseDatabase
- Standby Database ---09Database
- 物理Standby資料庫及邏輯Standby資料庫（Physical Standby & Logical Standby）資料庫
- 在Logical Standby上處理DDL及DML , ORA-16224: Database Guard is enabledDatabase
- 1.1 Logical Structure of Database ClusterStructDatabase
- 建立Oracle 11g logical standbyOracle
- Oracle10g logical standby 建立Oracle
- Create Logical Standby For Oracle 10GOracle 10g
- Logical Standby的維護操作_SKIP
- Logical Standby中Job的處理
- Create RAC Standby Database for RAC Primary DatabaseDatabase
- standby database No RFS 程式Database
- Rman backup standby databaseDatabase
- Standby Database for reportDatabase
- Oracle10gR2 Logical Standby (zt)Oracle
- [zt] Logical standby維護命令手冊
- Standby Database的工作原理Database
- How a Standby Database Is Mounted (295)Database
- Oracle Logical Standby 維護常用命令Oracle
- Logical Standby常見問題解決方式
- oracle 10g logical standby db creationOracle 10g
- RAC環境LOGICAL STANDBY的SWITCHOVER切換
- Oracle 9i Logical Standby與Physical standby歸檔恢復區別Oracle
- Using srvctl to Manage your 10g RAC DatabaseDatabase
- Oracle 10g Limits - Logical Database LimitsOracle 10gMITDatabase