| Oracle® Streams Replication Administrator's Guide 10g Release 2 (10.2) Part Number B14228-02 |
|
|
View PDF |
| Oracle® Streams Replication Administrator's Guide 10g Release 2 (10.2) Part Number B14228-02 |
|
|
View PDF |
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:
Monitoring an Apply Process in a Streams Replication Environment
Running Flashback Queries in a Streams Replication Environment
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:
Oracle Streams Concepts and Administration for more information about monitoring a Streams environment
Oracle Database Reference for information about the data dictionary views described in this chapter
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.
See Also:
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 notUSER 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.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.
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
Displaying Supplemental Logging Enabled by PREPARE_SCHEMA_INSTANTIATION
Displaying Supplemental Logging Enabled by PREPARE_GLOBAL_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 theWHERE clause in the query to list the information for all of the tables in the database.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.
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.
The following sections contain queries that you can run to monitor an apply process in a Stream replication environment:
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 isNULL if a substitute key column is specified for the local destination database.See Also:
Oracle Streams Concepts and Administration for information about managing apply errors
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 handlersWhen 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 theAPPLY_DATABASE_LINK column is NULL.See Also:
"Managing a DML Handler"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"
See Also:
"Managing a DDL Handler"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 definitionsTo 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.
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
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 theAPPLY_DATABASE_LINK column is NULL.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
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 Default Tag Value for Each Apply Process
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_STREAMS package
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.
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.
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
Determining the Tables for Which an Instantiation SCN Has Been Set
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
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 theAPPLY_DATABASE_LINK column is NULL.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:
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.
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.
Run the Flashback Query at the source database using the source SCN.
Run the Flashback Query at the destination database using the SCN returned in Step 2.
Compare the results of the queries in Steps 3 and 4 and take any necessary action.
See Also:
Oracle Database Concepts and Oracle Database Application Developer's Guide - Fundamentals for more information about Flashback Query
Oracle Database PL/SQL Packages and Types Reference for more information about the GET_SCN_MAPPING procedure
