12 Monitoring Streams Replication

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

This chapter contains these topics:

Note:

  • The Streams tool in the Oracle Enterprise Manager Console is also an excellent way to monitor a Streams environment. See the online help for the Streams tool for more information.

  • To collect elapsed time statistics in the dynamic performance views discussed in this chapter, set the TIMED_STATISTICS initialization parameter to true.

See Also:

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. The capture process captures this additional information and places it in LCRs. An apply process that applies 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.

Attention:

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 a 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 Streams Tags

The following sections contain queries that you can run to display the 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 in the DBMS_CAPTURE_ADM package:

  • PREPARE_TABLE_INSTANTIATION prepares a single table for instantiation.

  • PREPARE_SCHEMA_INSTANTIATION prepares all of the database objects in a schema for instantiation.

  • PREPARE_GLOBAL_INSTANTIATION prepares all of the database objects in a database for instantiation.

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

  • DBA_CAPTURE_PREPARED_TABLES

  • DBA_CAPTURE_PREPARED_SCHEMAS

  • DBA_CAPTURE_PREPARED_DATABASE

For example, to list all of the tables that have been prepared for instantiation, 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 table 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.

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 A15
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.NET        HR              REGIONS                    196660
DBS1.NET        HR              COUNTRIES                  196660
DBS1.NET        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.

Running Flashback Queries in a 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 a 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 Streams replication environment must have the following characteristics:

  • The replication environment must be a single-source environment, where changes to replicated objects are captured at only one database.

  • No modifications are made to the replicated objects in the Stream. That is, no transformations, subset rules (row migration), or apply handlers modify the LCRs for the replicated objects.

  • No DML or DDL changes are made to the replicated objects at the destination database.

  • Both the source database and the destination database must be configured to use Oracle Flashback, and the Streams administrator at both databases must be able to execute subprograms in the DBMS_FLASHBACK package.

  • The information in the undo tablespace must go back far enough to perform the query at each database. Oracle Flashback features use the Automatic Undo Management system to obtain historical data and metadata for a transaction. The UNDO_RETENTION initialization parameter at each database must be set to a value that is large enough to perform the Flashback Query.

Because 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. While connected as the Streams administrator at the destination database, 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 3.

    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.

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

  4. Run the Flashback Query at the destination database using the SCN returned in Step 2.

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

See Also: