Skip Headers
Oracle® Streams Replication Administrator's Guide
11g Release 1 (11.1)

B28322-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

13 Monitoring Oracle Streams Replication

This chapter provides information about the static data dictionary views and dynamic performance views related to Oracle Streams replication. You can use these views to monitor your Oracle Streams replication environment. This chapter also illustrates example queries that you can use to monitor your Oracle Streams replication environment.

This chapter contains these topics:

Note:

See Also:

Monitoring the Oracle Streams Topology and Oracle Streams Performance

You can use the DBMS_STREAMS_ADVISOR_ADM package to gather information about the Oracle Streams topology and performance. After this information is gathered, you can view it by querying the following data dictionary views:

When you gather information using the DBMS_STREAMS_ADVISOR_ADM package, the Oracle Streams Performance Advisor places information about the Oracle Streams topology and performance in these views. You can query these views to determine how Oracle Streams components are performing currently and for information about ways to make them perform better.

See Also:

Oracle Streams Concepts and Administration for more information about using the DBMS_STREAMS_ADVISOR_ADM package, the topology data dictionary views, and Oracle Streams Performance Advisor

Monitoring Supplemental Logging

The following sections contain queries that you can run to monitor supplemental logging at a source database:

The total supplemental logging at a database is determined by the results shown in all three of the queries in these sections combined. For example, supplemental logging can be enabled for columns in a table even if no results for the table are returned by the query in the "Displaying Supplemental Log Groups at a Source Database" section. That is, supplemental logging can be enabled for the table if database supplemental logging is enabled or if the table is in a schema for which supplemental logging was enabled during preparation for instantiation.

Supplemental logging places additional column data into a redo log when an operation is performed. A capture process captures this additional information and places it in LCRs. An apply process that applies these captured LCRs might need this additional information to schedule or apply changes correctly.

Displaying Supplemental Log Groups at a Source Database

To check whether one or more log groups are specified for the table at the source database, run the following query:

COLUMN LOG_GROUP_NAME HEADING 'Log Group' FORMAT A20
COLUMN TABLE_NAME HEADING 'Table' FORMAT A15
COLUMN ALWAYS HEADING 'Conditional or|Unconditional' FORMAT A14
COLUMN LOG_GROUP_TYPE HEADING 'Type of Log Group' FORMAT A20

SELECT 
    LOG_GROUP_NAME, 
    TABLE_NAME, 
    DECODE(ALWAYS,
             'ALWAYS', 'Unconditional',
             'CONDITIONAL', 'Conditional') ALWAYS,
    LOG_GROUP_TYPE
  FROM DBA_LOG_GROUPS;

Your output looks similar to the following:

                                     Conditional or
Log Group            Table           Unconditional  Type of Log Group
-------------------- --------------- -------------- --------------------
LOG_GROUP_DEP_PK     DEPARTMENTS     Unconditional  USER LOG GROUP
SYS_C002105          REGIONS         Unconditional  PRIMARY KEY LOGGING
SYS_C002106          REGIONS         Conditional    FOREIGN KEY LOGGING
SYS_C002110          LOCATIONS       Unonditional   ALL COLUMN LOGGING
SYS_C002111          COUNTRIES       Conditional    ALL COLUMN LOGGING
LOG_GROUP_JOBS_CR    JOBS            Conditional    USER LOG GROUP

If the output for the type of log group shows how the log group was created:

  • If the output is USER LOG GROUP, then the log group was created using the ADD SUPPLEMENTAL LOG GROUP clause of the ALTER TABLE statement.

  • Otherwise, the log group was created using the ADD SUPPLEMENTAL LOG DATA clause of the ALTER TABLE statement.

If the type of log group is USER LOG GROUP, then you can list the columns in the log group by querying the DBA_LOG_GROUP_COLUMNS data dictionary view.

Note:

If the type of log group is not USER LOG GROUP, then the DBA_LOG_GROUP_COLUMNS data dictionary view does not contain information about the columns in the log group. Instead, Oracle supplementally logs the correct columns when an operation is performed on the table. For example, if the type of log group is PRIMARY KEY LOGGING, then Oracle logs the current primary key column(s) when a change is performed on the table.

Displaying Database Supplemental Logging Specifications

To display the database supplemental logging specifications, query the V$DATABASE dynamic performance view, as in the following example:

COLUMN log_min HEADING 'Minimum|Supplemental|Logging?' FORMAT A12
COLUMN log_pk HEADING 'Primary Key|Supplemental|Logging?' FORMAT A12
COLUMN log_fk HEADING 'Foreign Key|Supplemental|Logging?' FORMAT A12
COLUMN log_ui HEADING 'Unique|Supplemental|Logging?' FORMAT A12
COLUMN log_all HEADING 'All Columns|Supplemental|Logging?' FORMAT A12

SELECT SUPPLEMENTAL_LOG_DATA_MIN log_min, 
       SUPPLEMENTAL_LOG_DATA_PK log_pk, 
       SUPPLEMENTAL_LOG_DATA_FK log_fk,
       SUPPLEMENTAL_LOG_DATA_UI log_ui,
       SUPPLEMENTAL_LOG_DATA_ALL log_all
  FROM V$DATABASE;  
  

Your output looks similar to the following:

Minimum      Primary Key  Foreign Key  Unique        All Columns
Supplemental Supplemental Supplemental Supplemental  Supplemental
Logging?     Logging?     Logging?     Logging?      Logging?
------------ ------------ ------------ ------------- ------------
YES          YES          YES          YES           NO

These results show that minimum, primary key, foreign key, and unique key columns are being supplementally logged for all of the tables in the database. Because unique key columns are supplementally logged, bitmap index columns also are supplementally logged. However, all columns are not being supplementally logged.

Displaying Supplemental Logging Specified During Preparation for Instantiation

Supplemental logging can be enabled when database objects are prepared for instantiation using one of the three procedures in the DBMS_CAPTURE_ADM package. A data dictionary view displays the supplemental logging enabled by each of these procedures: PREPARE_TABLE_INSTANTIATION, PREPARE_SCHEMA_INSTANTIATION, and PREPARE_GLOBAL_INSTANTIATION.

  • The DBA_CAPTURE_PREPARED_TABLES view displays the supplemental logging enabled by the PREPARE_TABLE_INSTANTIATION procedure.

  • The DBA_CAPTURE_PREPARED_SCHEMAS view displays the supplemental logging enabled by the PREPARE_SCHEMA_INSTANTIATION procedure.

  • The DBA_CAPTURE_PREPARED_DATABASE view displays the supplemental logging enabled by the PREPARE_GLOBAL_INSTANTIATION procedure.

Each of these views has the following columns:

  • SUPPLEMENTAL_LOG_DATA_PK shows whether primary key supplemental logging was enabled by a procedure.

  • SUPPLEMENTAL_LOG_DATA_UI shows whether unique key and bitmap index supplemental logging was enabled by a procedure.

  • SUPPLEMENTAL_LOG_DATA_FK shows whether foreign key supplemental logging was enabled by a procedure.

  • SUPPLEMENTAL_LOG_DATA_ALL shows whether supplemental logging for all columns was enabled by a procedure.

Each of these columns can display one of the following values:

  • IMPLICIT means that the relevant procedure enabled supplemental logging for the columns.

  • EXPLICIT means that supplemental logging was enabled for the columns manually using an ALTER TABLE or ALTER DATABASE statement with an ADD SUPPLEMENTAL LOG DATA clause.

  • NO means that supplemental logging was not enabled for the columns using a prepare procedure or an ALTER TABLE or ALTER DATABASE statement with an ADD SUPPLEMENTAL LOG DATA clause. Supplemental logging might not be enabled for the columns. However, supplemental logging might be enabled for the columns at another level (table, schema, or database), or it might have been enabled using an ALTER TABLE statement with an ADD SUPPLEMENTAL LOG GROUP clause.

The following sections contain queries that display the supplemental logging enabled by these procedures:

Displaying Supplemental Logging Enabled by PREPARE_TABLE_INSTANTIATION

The following query displays the supplemental logging enabled by the PREPARE_TABLE_INSTANTIATION procedure for the tables in the hr schema:

COLUMN TABLE_NAME HEADING 'Table Name' FORMAT A15
COLUMN log_pk HEADING 'Primary Key|Supplemental|Logging' FORMAT A12
COLUMN log_fk HEADING 'Foreign Key|Supplemental|Logging' FORMAT A12
COLUMN log_ui HEADING 'Unique|Supplemental|Logging' FORMAT A12
COLUMN log_all HEADING 'All Columns|Supplemental|Logging' FORMAT A12

SELECT TABLE_NAME,
       SUPPLEMENTAL_LOG_DATA_PK log_pk, 
       SUPPLEMENTAL_LOG_DATA_FK log_fk,
       SUPPLEMENTAL_LOG_DATA_UI log_ui,
       SUPPLEMENTAL_LOG_DATA_ALL log_all
  FROM DBA_CAPTURE_PREPARED_TABLES
  WHERE TABLE_OWNER = 'HR';  
  

Your output looks similar to the following:

                Primary Key  Foreign Key  Unique         All Columns
                Supplemental Supplemental Supplemental   Supplemental
Table Name      Logging      Logging      Logging        Logging
--------------- ------------ ------------ -------------- ------------
COUNTRIES       NO           NO           NO             NO
REGIONS         IMPLICIT     IMPLICIT     IMPLICIT       NO
DEPARTMENTS     IMPLICIT     IMPLICIT     IMPLICIT       NO
LOCATIONS       EXPLICIT     NO           NO             NO
EMPLOYEES       NO           NO           NO             IMPLICIT
JOB_HISTORY     NO           NO           NO             NO
JOBS            NO           NO           NO             NO

These results show the following:

  • The PREPARE_TABLE_INSTANTIATION procedure enabled supplemental logging for the primary key, unique key, bitmap index, and foreign key columns in the hr.regions and hr.departments tables.

  • The PREPARE_TABLE_INSTANTIATION procedure enabled supplemental logging for all columns in the hr.employees table.

  • An ALTER TABLE statement with an ADD SUPPLEMENTAL LOG DATA clause enabled primary key supplemental logging for the hr.locations table.

Note:

Omit the WHERE clause in the query to list the information for all of the tables in the database.

Displaying Supplemental Logging Enabled by PREPARE_SCHEMA_INSTANTIATION

The following query displays the supplemental logging enabled by the PREPARE_SCHEMA_INSTANTIATION procedure:

COLUMN SCHEMA_NAME HEADING 'Schema Name' FORMAT A20
COLUMN log_pk HEADING 'Primary Key|Supplemental|Logging' FORMAT A12
COLUMN log_fk HEADING 'Foreign Key|Supplemental|Logging' FORMAT A12
COLUMN log_ui HEADING 'Unique|Supplemental|Logging' FORMAT A12
COLUMN log_all HEADING 'All Columns|Supplemental|Logging' FORMAT A12

SELECT SCHEMA_NAME,
       SUPPLEMENTAL_LOG_DATA_PK log_pk, 
       SUPPLEMENTAL_LOG_DATA_FK log_fk,
       SUPPLEMENTAL_LOG_DATA_UI log_ui,
       SUPPLEMENTAL_LOG_DATA_ALL log_all
  FROM DBA_CAPTURE_PREPARED_SCHEMAS;
  

Your output looks similar to the following:

                     Primary Key  Foreign Key  Unique         All Columns
                     Supplemental Supplemental Supplemental   Supplemental
Schema Name          Logging      Logging      Logging        Logging
-------------------- ------------ ------------ -------------- ------------
OUTLN                NO           NO           NO             NO
DIP                  NO           NO           NO             NO
TSMSYS               NO           NO           NO             NO
DBSNMP               NO           NO           NO             NO
WMSYS                NO           NO           NO             NO
CTXSYS               NO           NO           NO             NO
SCOTT                NO           NO           NO             NO
ADAMS                NO           NO           NO             NO
JONES                NO           NO           NO             NO
CLARK                NO           NO           NO             NO
BLAKE                NO           NO           NO             NO
HR                   NO           NO           NO             IMPLICIT
OE                   IMPLICIT     IMPLICIT     IMPLICIT       NO
IX                   NO           NO           NO             NO
ORDSYS               NO           NO           NO             NO
ORDPLUGINS           NO           NO           NO             NO
SI_INFORMTN_SCHEMA   NO           NO           NO             NO
MDSYS                NO           NO           NO             NO
PM                   NO           NO           NO             NO
SH                   NO           NO           NO             NO

These results show the following:

  • The PREPARE_SCHEMA_INSTANTIATION procedure enabled supplemental logging for all columns in tables in the hr schema.

  • The PREPARE_SCHEMA_INSTANTIATION procedure enabled supplemental logging for the primary key, unique key, bitmap index, and foreign key columns in the tables in the oe schema.

Displaying Supplemental Logging Enabled by PREPARE_GLOBAL_INSTANTIATION

The following query displays the supplemental logging enabled by the PREPARE_GLOBAL_INSTANTIATION procedure:

COLUMN log_pk HEADING 'Primary Key|Supplemental|Logging' FORMAT A12
COLUMN log_fk HEADING 'Foreign Key|Supplemental|Logging' FORMAT A12
COLUMN log_ui HEADING 'Unique|Supplemental|Logging' FORMAT A12
COLUMN log_all HEADING 'All Columns|Supplemental|Logging' FORMAT A12

SELECT SUPPLEMENTAL_LOG_DATA_PK log_pk,
       SUPPLEMENTAL_LOG_DATA_FK log_fk,
       SUPPLEMENTAL_LOG_DATA_UI log_ui,
       SUPPLEMENTAL_LOG_DATA_ALL log_all
  FROM DBA_CAPTURE_PREPARED_DATABASE;
  

Your output looks similar to the following:

Primary Key  Foreign Key  Unique         All Columns
Supplemental Supplemental Supplemental   Supplemental
Logging      Logging      Logging        Logging
------------ ------------ -------------- ------------
IMPLICIT     IMPLICIT     IMPLICIT       NO

These results show that the PREPARE_GLOBAL_INSTANTIATION procedure enabled supplemental logging for the primary key, unique key, bitmap index, and foreign key columns in all of the tables in the database.

Monitoring an Apply Process in an Oracle Streams Replication Environment

The following sections contain queries that you can run to monitor an apply process in a Stream replication environment:

Displaying the Substitute Key Columns Specified at a Destination Database

You can designate a substitute key at a destination database, which is a column or set of columns that Oracle can use to identify rows in the table during apply. Substitute key columns can be used to specify key columns for a table that has no primary key, or they can be used instead of a table's primary key when the table is processed by any apply process at a destination database.

To display all of the substitute key columns specified at a destination database, run the following query:

COLUMN OBJECT_OWNER HEADING 'Table Owner' FORMAT A20
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A20
COLUMN COLUMN_NAME HEADING 'Substitute Key Name' FORMAT A20
COLUMN APPLY_DATABASE_LINK HEADING 'Database Link|for Remote|Apply' FORMAT A15

SELECT OBJECT_OWNER, OBJECT_NAME, COLUMN_NAME, APPLY_DATABASE_LINK 
  FROM DBA_APPLY_KEY_COLUMNS
  ORDER BY APPLY_DATABASE_LINK, OBJECT_OWNER, OBJECT_NAME;

Your output looks similar to the following:

                                                               Database Link
                                                               for Remote
Table Owner          Table Name           Substitute Key Name  Apply
-------------------- -------------------- -------------------- ---------------
HR                   DEPARTMENTS          DEPARTMENT_NAME
HR                   DEPARTMENTS          LOCATION_ID
HR                   EMPLOYEES            FIRST_NAME
HR                   EMPLOYEES            LAST_NAME
HR                   EMPLOYEES            HIRE_DATE

Note:

This query shows the database link in the last column if the substitute key columns are for a remote non-Oracle database. The last column is NULL if a substitute key column is specified for the local destination database.

Displaying Information About DML and DDL Handlers

This section contains queries that display information about apply process DML handlers and DDL handlers.

See Also:

Oracle Streams Concepts and Administration for more information about DML and DDL handlers

Displaying All of the DML Handlers for Local Apply

When you specify a local DML handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package at a destination database, you can either specify that the handler runs for a specific apply process or that the handler is a general handler that runs for all apply processes in the database that apply changes locally, when appropriate. A specific DML handler takes precedence over a generic DML handler. A DML is run for a specified operation on a specific table.

To display the DML handler for each apply process that applies changes locally in a database, run the following query:

COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A11
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A10
COLUMN OPERATION_NAME HEADING 'Operation' FORMAT A9
COLUMN USER_PROCEDURE HEADING 'Handler Procedure' FORMAT A25
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15

SELECT OBJECT_OWNER, 
       OBJECT_NAME, 
       OPERATION_NAME, 
       USER_PROCEDURE,
       APPLY_NAME
  FROM DBA_APPLY_DML_HANDLERS
  WHERE ERROR_HANDLER = 'N' AND
        APPLY_DATABASE_LINK IS NULL
  ORDER BY OBJECT_OWNER, OBJECT_NAME;

Your output looks similar to the following:

Table                                                      Apply Process
Owner       Table Name Operation Handler Procedure         Name
----------- ---------- --------- ------------------------- --------------
HR          LOCATIONS  UPDATE    "STRMADMIN"."HISTORY_DML"

Because Apply Process Name is NULL for the strmadmin.history_dml DML handler, this handler is a general handler that runs for all of the local apply processes.

Note:

You can also specify DML handlers to process changes for remote non-Oracle databases. This query does not display such DML handlers because it lists a DML handler only if the APPLY_DATABASE_LINK column is NULL.

Displaying the DDL Handler for Each Apply Process

To display the DDL handler for each apply process in a database, run the following query:

COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20
COLUMN DDL_HANDLER HEADING 'DDL Handler' FORMAT A40

SELECT APPLY_NAME, DDL_HANDLER FROM DBA_APPLY;

Your output looks similar to the following:

Apply Process Name   DDL Handler
-------------------- ----------------------------------------
STREP01_APPLY        "STRMADMIN"."HISTORY_DDL"

Monitoring Virtual Dependency Definitions

The following sections contain queries that display information about virtual dependency definitions in a database:

See Also:

"Apply Processes and Dependencies" for more information about virtual dependency definitions

Displaying Value Dependencies

To display the value dependencies in a database, run the following query:

COLUMN DEPENDENCY_NAME HEADING 'Dependency Name' FORMAT A25
COLUMN OBJECT_OWNER HEADING 'Object Owner' FORMAT A15
COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A20
COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A15

SELECT DEPENDENCY_NAME, 
       OBJECT_OWNER, 
       OBJECT_NAME, 
       COLUMN_NAME 
  FROM DBA_APPLY_VALUE_DEPENDENCIES;

Your output should look similar to the following:

Dependency Name           Object Owner    Object Name          Column Name
------------------------- --------------- -------------------- ---------------
ORDER_ID_FOREIGN_KEY      OE              ORDERS               ORDER_ID
ORDER_ID_FOREIGN_KEY      OE              ORDER_ITEMS          ORDER_ID
KEY_53_FOREIGN_KEY        US_DESIGNS      ALL_DESIGNS_SUMMARY  KEY_53
KEY_53_FOREIGN_KEY        US_DESIGNS      DESIGN_53            KEY_53

This output shows the following value dependencies:

  • The order_id_foreign_key value dependency describes a dependency between the order_id column in the oe.orders table and the order_id column in the oe.order_items table.

  • The key_53_foreign_key value dependency describes a dependency between the key_53 column in the us_designs.all_designs_summary table and the key_53 column in the us_designs.design_53 table.

Displaying Object Dependencies

To display the object dependencies in a database, run the following query:

COLUMN OBJECT_OWNER HEADING 'Object Owner' FORMAT A15
COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A15
COLUMN PARENT_OBJECT_OWNER HEADING 'Parent Object Owner' FORMAT A20
COLUMN PARENT_OBJECT_NAME HEADING 'Parent Object Name' FORMAT A20

SELECT OBJECT_OWNER, 
       OBJECT_NAME, 
       PARENT_OBJECT_OWNER, 
       PARENT_OBJECT_NAME 
  FROM DBA_APPLY_OBJECT_DEPENDENCIES;

Your output should look similar to the following:

Object Owner    Object Name     Parent Object Owner  Parent Object Name
--------------- --------------- -------------------- --------------------
ORD             CUSTOMERS       ORD                  SHIP_ORDERS
ORD             ORDERS          ORD                  SHIP_ORDERS
ORD             ORDER_ITEMS     ORD                  SHIP_ORDERS

This output shows an object dependency in which the ord.ship_orders table is a parent table to the following child tables:

  • ord.customers

  • ord.orders

  • ord.order_items

Displaying Information About Conflict Detection

You can stop conflict detection for nonkey columns using the COMPARE_OLD_VALUES procedure in the DBMS_APPLY_ADM package. When you use this procedure, conflict detection is stopped for the specified columns for all apply processes at a destination database. To display each column for which conflict detection has been stopped, run the following query:

COLUMN OBJECT_OWNER HEADING 'Table Owner' FORMAT A15
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A20
COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A20
COLUMN COMPARE_OLD_ON_DELETE HEADING 'Compare|Old On|Delete' FORMAT A7
COLUMN COMPARE_OLD_ON_UPDATE HEADING 'Compare|Old On|Update' FORMAT A7

SELECT OBJECT_OWNER, 
       OBJECT_NAME, 
       COLUMN_NAME, 
       COMPARE_OLD_ON_DELETE, 
       COMPARE_OLD_ON_UPDATE 
  FROM DBA_APPLY_TABLE_COLUMNS
  WHERE APPLY_DATABASE_LINK IS NULL;

Your output should look similar to the following:

                                                          Compare Compare
                                                          Old On  Old On
Table Owner     Table Name           Column Name          Delete  Update
--------------- -------------------- -------------------- ------- -------
HR              EMPLOYEES            COMMISSION_PCT       NO      NO
HR              EMPLOYEES            EMAIL                NO      NO
HR              EMPLOYEES            FIRST_NAME           NO      NO
HR              EMPLOYEES            HIRE_DATE            NO      NO
HR              EMPLOYEES            JOB_ID               NO      NO
HR              EMPLOYEES            LAST_NAME            NO      NO
HR              EMPLOYEES            PHONE_NUMBER         NO      NO
HR              EMPLOYEES            SALARY               NO      NO

Note:

You can also stop conflict detection for changes that are applied to remote non-Oracle databases. This query does not display such specifications because it lists a specification only if the APPLY_DATABASE_LINK column is NULL.

Displaying Information About Update Conflict Handlers

When you specify an update conflict handler using the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package, the update conflict handler is run for all apply processes in the database, when a relevant conflict occurs.

The query in this section displays all of the columns for which conflict resolution has been specified using a prebuilt update conflict handler. That is, it shows the columns in all of the column lists specified in the database. This query also shows the type of prebuilt conflict handler specified and the resolution column specified for the column list.

To display information about all of the update conflict handlers in a database, run the following query:

COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A5
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A12
COLUMN METHOD_NAME HEADING 'Method' FORMAT A12
COLUMN RESOLUTION_COLUMN HEADING 'Resolution|Column' FORMAT A13
COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A30

SELECT OBJECT_OWNER, 
       OBJECT_NAME, 
       METHOD_NAME, 
       RESOLUTION_COLUMN, 
       COLUMN_NAME
  FROM DBA_APPLY_CONFLICT_COLUMNS
  ORDER BY OBJECT_OWNER, OBJECT_NAME, RESOLUTION_COLUMN;

Your output looks similar to the following:

Table                           Resolution
Owner Table Name   Method       Column        Column Name
----- ------------ ------------ ------------- ------------------------------
HR    COUNTRIES    MAXIMUM      TIME          COUNTRY_NAME
HR    COUNTRIES    MAXIMUM      TIME          REGION_ID
HR    COUNTRIES    MAXIMUM      TIME          TIME
HR    DEPARTMENTS  MAXIMUM      TIME          DEPARTMENT_NAME
HR    DEPARTMENTS  MAXIMUM      TIME          LOCATION_ID
HR    DEPARTMENTS  MAXIMUM      TIME          MANAGER_ID
HR    DEPARTMENTS  MAXIMUM      TIME          TIME

Monitoring Oracle Streams Tags

The following sections contain queries that you can run to display the Oracle Streams tag for the current session and the default tag for each apply process:

Displaying the Tag Value for the Current Session

You can display the tag value generated in all redo entries for the current session by querying the DUAL view:

SELECT DBMS_STREAMS.GET_TAG FROM DUAL;

Your output looks similar to the following:

GET_TAG
--------------------------------------------------------------------------------
1D

You can also determine the tag for a session by calling the DBMS_STREAMS.GET_TAG function.

Displaying the Default Tag Value for Each Apply Process

You can get the default tag for all redo entries generated by each apply process by querying for the APPLY_TAG value in the DBA_APPLY data dictionary view. For example, to get the hexadecimal value of the default tag generated in the redo entries by each apply process, run the following query:

COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A30
COLUMN APPLY_TAG HEADING 'Tag Value' FORMAT A30

SELECT APPLY_NAME, APPLY_TAG FROM DBA_APPLY;

Your output looks similar to the following:

Apply Process Name             Tag Value
------------------------------ ------------------------------
APPLY_FROM_MULT2               00
APPLY_FROM_MULT3               00

A handler or custom rule-based transformation function associated with an apply process can get the tag by calling the DBMS_STREAMS.GET_TAG function.

Monitoring Instantiation

The following sections contain queries that you can run to determine which database objects are prepared for instantiation at a source database and the instantiation SCN for database objects at a destination database:

Determining Which Database Objects Are Prepared for Instantiation

You prepare a database object for instantiation using one of the following procedures or function in the DBMS_CAPTURE_ADM package:

  • The PREPARE_TABLE_INSTANTIATION procedure prepares a single table for instantiation when changes to the table will be captured by a capture process.

  • The PREPARE_SYNC_INSTANTIATION function prepares a single table or multiple tables for instantiation when changes to the table will be captured by a synchronous capture.

  • The PREPARE_SCHEMA_INSTANTIATION procedure prepares for instantiation all of the database objects in a schema and all database objects added to the schema in the future. This procedure should only be used when changes will be captured by a capture process.

  • The PREPARE_GLOBAL_INSTANTIATIONprocedure prepares for instantiation all of the database objects in a database and all database objects added to the database in the future. This procedure should only be used when changes will be captured by a capture process.

To determine which database objects have been prepared for instantiation, query the following corresponding data dictionary views:

  • DBA_CAPTURE_PREPARED_TABLES

  • DBA_SYNC_CAPTURE_PREPARED_TABS

  • DBA_CAPTURE_PREPARED_SCHEMAS

  • DBA_CAPTURE_PREPARED_DATABASE

For example, to list all of the tables that have been prepared for instantiation by the PREPARE_TABLE_INSTANTIATION procedure, the SCN for the time when each table was prepared, and the time when each table was prepared, run the following query:

COLUMN TABLE_OWNER HEADING 'Table Owner' FORMAT A15
COLUMN TABLE_NAME HEADING 'Table Name' FORMAT A15
COLUMN SCN HEADING 'Prepare SCN' FORMAT 99999999999
COLUMN TIMESTAMP HEADING 'Time Ready for|Instantiation'

SELECT TABLE_OWNER, 
       TABLE_NAME, 
       SCN, 
       TO_CHAR(TIMESTAMP, 'HH24:MI:SS MM/DD/YY') TIMESTAMP
  FROM DBA_CAPTURE_PREPARED_TABLES;

Your output looks similar to the following:

                                                  Time Ready for
Table Owner     Table Name            Prepare SCN Instantiation
--------------- --------------- ----------------- -----------------
HR              COUNTRIES                  196655 12:59:30 02/28/02
HR              DEPARTMENTS                196658 12:59:30 02/28/02
HR              EMPLOYEES                  196659 12:59:30 02/28/02
HR              JOBS                       196660 12:59:30 02/28/02
HR              JOB_HISTORY                196661 12:59:30 02/28/02
HR              LOCATIONS                  196662 12:59:30 02/28/02
HR              REGIONS                    196664 12:59:30 02/28/02

Determining the Tables for Which an Instantiation SCN Has Been Set

An instantiation SCN is set at a destination database. It controls which captured LCRs for a database object are ignored by an apply process and which captured LCRs for a database object are applied by an apply process. If the commit SCN of an LCR for a table from a source database is less than or equal to the instantiation SCN for that table at a destination database, then the apply process at the destination database discards the LCR. Otherwise, the apply process applies the LCR. The LCRs can be captured by a capture process or a synchronous capture.

You can set an instantiation SCN using one of the following procedures in the DBMS_APPLY_ADM package:

  • SET_TABLE_INSTANTIATION_SCN sets the instantiation SCN for a single table.

  • SET_SCHEMA_INSTANTIATION_SCN sets the instantiation SCN for a schema, and, optionally, for all of the database objects in the schema.

  • SET_GLOBAL_INSTANTIATION_SCN sets the instantiation SCN for a database, and, optionally, for all of the database objects in the database.

To determine which database objects have a set instantiation SCN, query the following corresponding data dictionary views:

  • DBA_APPLY_INSTANTIATED_OBJECTS

  • DBA_APPLY_INSTANTIATED_SCHEMAS

  • DBA_APPLY_INSTANTIATED_GLOBAL

The following query lists each table for which an instantiation SCN has been set at a destination database and the instantiation SCN for each table:

COLUMN SOURCE_DATABASE HEADING 'Source Database' FORMAT A20
COLUMN SOURCE_OBJECT_OWNER HEADING 'Object Owner' FORMAT A15
COLUMN SOURCE_OBJECT_NAME HEADING 'Object Name' FORMAT A15
COLUMN INSTANTIATION_SCN HEADING 'Instantiation SCN' FORMAT 99999999999

SELECT SOURCE_DATABASE, 
       SOURCE_OBJECT_OWNER, 
       SOURCE_OBJECT_NAME, 
       INSTANTIATION_SCN 
  FROM DBA_APPLY_INSTANTIATED_OBJECTS
  WHERE APPLY_DATABASE_LINK IS NULL;

Your output looks similar to the following:

Source Database     Object Owner    Object Name     Instantiation SCN
-------------------- --------------- --------------- -----------------
DBS1.EXAMPLE.COM     HR              REGIONS                    196660
DBS1.EXAMPLE.COM     HR              COUNTRIES                  196660
DBS1.EXAMPLE.COM     HR              LOCATIONS                  196660

Note:

You can also display instantiation SCNs for changes that are applied to remote non-Oracle databases. This query does not display these instantiation SCNs because it lists an instantiation SCN only if the APPLY_DATABASE_LINK column is NULL.

Tracking LCRs Through a Stream

A logical change record (LCR) typically flows through a stream in the following way:

  1. A database change is captured, formatted into an LCR, and enqueued. A capture process or a synchronous capture can capture database changes implicitly. An application or user can construct and enqueue LCRs to capture database changes explicitly.

  2. One or more propagations send the LCR to other databases in the Oracle Streams environment.

  3. One or more apply processes dequeue the LCR and processes it.

You can use the SET_MESSAGE_TRACKING procedure in the DBMS_STREAMS_ADM package to track an LCR as it flows through a stream. This procedure enables you to specify a tracking label that becomes part of each LCR generated by the current session. Using this tracking label, you can query the V$STREAMS_MESSAGE_TRACKING view to track the LCRs through the stream and see how they were processed by each Oracle Streams client.

LCR tracking is useful if LCRs are not being applied as expected by one or more apply processes. When this happens, you can use LCR tracking to determine where the LCRs are stopping in the stream and address the problem at that location.

When a capture process or a synchronous capture captures an LCR, and a tracking label is set for the session that made the captured database change, the tracking label is included in the LCR automatically. When a user or application constructs an LCR and a tracking label is set for the session that constructs the LCR, the tracking label is included in the LCR automatically.

To track LCRs through a stream, complete the following steps:

  1. In SQL*Plus, start a session. If you want to use a tracking label for database changes captured by a capture process or synchronous capture, then connect to the source database for the capture process or synchronous capture.

    See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

  2. Begin message tracking:

    BEGIN
      DBMS_STREAMS_ADM.SET_MESSAGE_TRACKING(
        tracking_label => 'TRACK_LCRS',
        actions        => DBMS_STREAMS_ADM.ACTION_MEMORY);
    END;
    /
    

    You can use any label you choose to track LCRs. This example uses the TRACK_LCRS label.

    Also, this example specifies DBMS_STREAMS_ADM.ACTION_MEMORY for the actions parameter. This value specifies that information about the LCRs is tracked in memory and that the V$STREAMS_MESSAGE_TRACKING dynamic performance view is populated with information about the LCRs. Currently, ACTION_MEMORY is the only value allowed for the actions parameter.

  3. Optionally, to ensure that message tracking is set in the session, get the tracking label:

    SET SERVEROUTPUT ON SIZE 4000
    DECLARE
      tracking_label  VARCHAR2(4000);
    BEGIN
      tracking_label := DBMS_STREAMS_ADM.GET_MESSAGE_TRACKING();
      DBMS_OUTPUT.PUT_LINE('Tracking Label: ' || tracking_label);
    END;
    /
    

    The GET_MESSAGE_TRACKING function should return the tracking label you specified in Step 2.

  4. Make changes to the source database that will be captured by the capture process or synchronous capture that starts the stream, or construct and enqueue the LCRs you want to track. Typically, these LCRs are for testing purposes only. For example, you can insert a number of dummy rows into a table and then modify these rows. When the testing is complete, you can delete the rows.

  5. Monitor the entire Oracle Streams environment to track the LCRs. To do so, query the V$STREAMS_MESSAGE_TRACKING view at each database that processes the LCRs.

    For example, to track LCRs with the TRACK_LCRS label specified in Step 2, run the following query at each database:

    COLUMN COMPONENT_NAME HEADING 'Component|Name' FORMAT A10
    COLUMN COMPONENT_TYPE HEADING 'Component|Type' FORMAT A12
    COLUMN ACTION HEADING 'Action' FORMAT A11
    COLUMN SOURCE_DATABASE_NAME HEADING 'Source|Database' FORMAT A10
    COLUMN OBJECT_OWNER HEADING 'Object|Owner' FORMAT A6
    COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A10
    COLUMN COMMAND_TYPE HEADING 'Command|Type' FORMAT A7
     
    SELECT COMPONENT_NAME,
           COMPONENT_TYPE,
           ACTION,
           SOURCE_DATABASE_NAME,
           OBJECT_OWNER,
           OBJECT_NAME,
           COMMAND_TYPE
       FROM V$STREAMS_MESSAGE_TRACKING
       WHERE TRACKING_LABEL='TRACK_LCRS';
    

    These queries will show how the LCRs were processed at each database. If the LCRs are not being applied at destination databases, then these queries will show where in the stream the LCRs are stopping.

    For example, the output at a source database with a synchronous capture is similar to the following:

    Component  Component                Source     Object Object     Command
    Name       Type         Action      Database   Owner  Name       Type
    ---------- ------------ ----------- ---------- ------ ---------- -------
    CAPTURE    SYNCHRONOUS  Create      HUB.EXAMPL HR     EMPLOYEES  UPDATE
               CAPTURE                  E.COM
    CAPTURE    SYNCHRONOUS  Rule evalua HUB.EXAMPL HR     EMPLOYEES  UPDATE
               CAPTURE      tion        E.COM
    CAPTURE    SYNCHRONOUS  Enqueue     HUB.EXAMPL HR     EMPLOYEES  UPDATE
               CAPTURE                  E.COM
    

    The output at a destination database with an apply process is similar to the following:

    Component  Component                Source     Object Object     Command
    Name       Type         Action      Database   Owner  Name       Type
    ---------- ------------ ----------- ---------- ------ ---------- -------
    APPLY_SYNC APPLY READER Dequeue     HUB.EXAMPL HR     EMPLOYEES  UPDATE
    _CAP                                E.COM
    APPLY_SYNC APPLY READER Dequeue     HUB.EXAMPL HR     EMPLOYEES  UPDATE
    _CAP                                E.COM
    APPLY_SYNC APPLY READER Dequeue     HUB.EXAMPL HR     EMPLOYEES  UPDATE
    _CAP                                E.COM
    

    You can query additional columns in the V$STREAMS_MESSAGE_TRACKING view to display more information. For example, the ACTION_DETAILS column provides detailed information about each action.

  6. To stop message tracking in the current session, set the tracking_label parameter to NULL in the SET_MESSAGE_TRACKING procedure:

    BEGIN
      DBMS_STREAMS_ADM.SET_MESSAGE_TRACKING(
        tracking_label => NULL,
        actions        => DBMS_STREAMS_ADM.ACTION_MEMORY);
    END;
    /
    

Note:

You can use the message_tracking_frequency capture process parameter to track LCRs automatically.

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the message_tracking_frequency capture process parameter

Running Flashback Queries in an Oracle Streams Replication Environment

Oracle Flashback Query enables you to view and repair historical data. You can perform queries on a database as of a certain clock time or system change number (SCN). In an Oracle Streams single-source replication environment, you can use Flashback Query at the source database and a destination database at a past time when the replicated database objects should be identical.

Running the queries at corresponding SCNS at the source and destination databases can be used to determine whether all of the changes to the replicated objects performed at the source database have been applied at the destination database. If there are apply errors at the destination database, then such a Flashback Query can show how the replicated objects looked at the time when the error was raised. This information could be useful in determining the cause of the error and the best way to correct the error.

Running a Flashback Query at each database can also check whether tables have certain rows at the corresponding SCNs. If the table data does not match at the corresponding SCNs, then there is a problem with the replication environment.

To run queries, the Oracle Streams replication environment must have the following characteristics:

Because Oracle Streams replication is asynchronous, you cannot use a past time in the Flashback Query. However, you can use the GET_SCN_MAPPING procedure in the DBMS_STREAMS_ADM package to determine the SCN at the destination database that corresponds to an SCN at the source database.

These instructions assume that you know the SCN for the Flashback Query at the source database. Using this SCN, you can determine the corresponding SCN for the Flashback Query at the destination database. To run these queries, complete the following steps:

  1. At the destination database, ensure that the archived redo log file for the approximate time of the Flashback Query is available to the database. The GET_SCN_MAPPING procedure requires that this redo log file be available.

  2. In SQL*Plus, connect to the destination database as the Oracle Streams administrator.

    See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

  3. Run the GET_SCN_MAPPING procedure. In this example, assume that the SCN for the source database is 52073983 and that the name of the apply process that applies changes from the source database is strm01_apply:

    SET SERVEROUTPUT ON
    DECLARE
      dest_scn   NUMBER;
      start_scn  NUMBER;
      dest_skip  DBMS_UTILITY.NAME_ARRAY;
    BEGIN
      DBMS_STREAMS_ADM.GET_SCN_MAPPING(
        apply_name             => 'strm01_apply',
        src_pit_scn            => '52073983',
        dest_instantiation_scn => dest_scn,
        dest_start_scn         => start_scn,
        dest_skip_txn_ids      => dest_skip);
      IF dest_skip.count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No Skipped Transactions');
        DBMS_OUTPUT.PUT_LINE('Destination SCN: ' || dest_scn);
      ELSE
        DBMS_OUTPUT.PUT_LINE('Destination SCN invalid for Flashback Query.');
        DBMS_OUTPUT.PUT_LINE('At least one transaction was skipped.');
      END IF;
    END;
    /
    

    If a valid destination SCN is returned, then proceed to Step 4.

    If the destination SCN was not valid for Flashback Query because one or more transactions were skipped by the apply process, then the apply process parameter commit_serialization was set to none, and nondependent transactions have been applied out of order. There is at least one transaction with a source commit SCN less than src_pit_scn that was committed at the destination database after the returned dest_instantiation_scn. Therefore, tables might not be the same at the source and destination databases for the specified source SCN. You can choose a different source SCN and restart at Step 1.

  4. Run the Flashback Query at the source database using the source SCN.

  5. Run the Flashback Query at the destination database using the SCN returned in Step 3.

  6. Compare the results of the queries in Steps 4 and 5 and take any necessary action.

See Also: