36 DBMS_CAPTURE_ADM
The DBMS_CAPTURE_ADM
package, one of a set of Oracle Replication packages, provides subprograms for starting, stopping, and configuring a capture process. The source of the captured changes is the redo logs, and the repository for the captured changes is a queue.
36.1 DBMS_CAPTURE_ADM Overview
This package provides interfaces to start, stop, and configure a capture process or a synchronous capture. This package includes subprograms for preparing database objects for instantiation.
Capture processes can be used in an XStream configuration in a multitenant container database (CDB). A CDB is an Oracle database that includes zero, one, or many user-created pluggable databases (PDBs).
See Also:
Oracle Database Concepts for more information about CDBs and PDBs
36.2 DBMS_CAPTURE_ADM Security Model
The DBMS_CAPTURE_ADM security can be controlled in one of two ways.
-
Granting
EXECUTE
on this package to selected users or roles. -
Granting
EXECUTE_CATALOG_ROLE
to selected users or roles.
If subprograms in the package are run from within a stored procedure, then the user who runs the subprograms must be granted EXECUTE
privilege on the package directly. It cannot be granted through a role.
When the DBMS_CAPTURE_ADM
package is used to manage an Oracle Replication configuration, it requires that the user is granted the privileges of an Oracle Replication administrator.
When the DBMS_CAPTURE_ADM
package is used to manage an XStream configuration, it requires that the user is granted the privileges of an XStream administrator.
Note:
-
The user must be granted additional privileges to perform some administrative tasks using the subprograms in this package, such as setting a capture user. If additional privileges are required for a subprogram, then the privileges are documented in the section that describes the subprogram.
-
Using XStream requires purchasing a license for the Oracle GoldenGate product. See Oracle Database XStream Guide.
See Also:
Oracle Database XStream Guide for information about configuring an XStream administrator
36.3 Summary of DBMS_CAPTURE_ADM Subprograms
This table lists the DBMS_CAPTURE_ADM
subprograms and briefly describes them.
Table 36-1 DBMS_CAPTURE_ADM Package Subprograms
Subprogram | Description |
---|---|
Reverses the effects of running the |
|
Reverses the effects of running the |
|
Reverses the effects of running the |
|
Reverses the effects of running the |
|
Alters a capture process |
|
Alters a synchronous capture |
|
Extracts the data dictionary of the current database to the redo logs and automatically specifies database supplemental logging for all primary key and unique key columns |
|
Creates a capture process |
|
Creates a synchronous capture |
|
Drops a capture process |
|
Includes or excludes an extra attribute in logical change records (LCRs) captured by the specified capture process or synchronous capture |
|
Performs the synchronization necessary for instantiating all the tables in the database at another database and can enable supplemental logging for key columns or all columns in these tables |
|
Performs the synchronization necessary for instantiating all tables in the schema at another database and can enable supplemental logging for key columns or all columns in these tables |
|
Performs the synchronization necessary for instantiating one or more tables at another database and returns the prepare SCN |
|
Performs the synchronization necessary for instantiating the table at another database and can enable supplemental logging for key columns or all columns in the table |
|
Sets a capture process parameter to the specified value |
|
Starts the capture process, which mines redo logs and enqueues the mined redo information into the associated queue |
|
Stops the capture process from mining redo logs |
Note:
All subprograms commit unless specified otherwise.
36.3.1 ABORT_GLOBAL_INSTANTIATION Procedure
This procedure reverses the effects of running the PREPARE_GLOBAL_INSTANTIATION
, PREPARE_SCHEMA_INSTANTIATION
, and PREPARE_TABLE_INSTANTIATION
procedures.
Specifically, this procedure performs the following actions:
-
Removes data dictionary information related to the database, schema, and table instantiations
-
Removes any supplemental logging enabled by the
PREPARE_GLOBAL_INSTANTIATION
,PREPARE_SCHEMA_INSTANTIATION
, andPREPARE_TABLE_INSTANTIATION
procedures
Syntax
DBMS_CAPTURE_ADM.ABORT_GLOBAL_INSTANTIATION( container IN VARCHAR2 DEFAULT 'CURRENT');
Parameter
Table 36-2 ABORT_GLOBAL_INSTANTIATION Procedure Parameter
Parameter | Description |
---|---|
|
Either If If If
|
36.3.2 ABORT_SCHEMA_INSTANTIATION Procedure
This procedure reverses the effects of running the PREPARE_SCHEMA_INSTANTIATION
procedure. It also reverses the effects of running the PREPARE_TABLE_INSTANTIATION
procedure on tables in the specified schema.
Specifically, this procedure performs the following actions:
-
Removes data dictionary information related to schema instantiations and table instantiations of tables in the schema
-
Removes any supplemental logging enabled by the
PREPARE_SCHEMA_INSTANTIATION
procedure -
Removes any supplemental logging enabled by the
PREPARE_TABLE_INSTANTIATION
procedure for tables in the specified schema
Syntax
DBMS_CAPTURE_ADM.ABORT_SCHEMA_INSTANTIATION( schema_name IN VARCHAR2, container IN VARCHAR2 DEFAULT 'CURRENT');
Parameter
Table 36-3 ABORT_SCHEMA_INSTANTIATION Procedure Parameter
Parameter | Description |
---|---|
|
The name of the schema for which to abort the effects of preparing instantiation |
|
Either If If If
|
36.3.3 ABORT_SYNC_INSTANTIATION Procedure
This procedure reverses the effects of running the PREPARE_SYNC_INSTANTIATION
procedure. Specifically, this procedure removes data dictionary information related to the table instantiation.
This procedure is overloaded. The table_names
parameter is VARCHAR2
datatype in one version and DBMS_UTILITY.UNCL_ARRAY
datatype in the other version.
Syntax
DBMS_CAPTURE_ADM.ABORT_SYNC_INSTANTIATION( table_names IN VARCHAR2); DBMS_CAPTURE_ADM.ABORT_SYNC_INSTANTIATION( table_names IN DBMS_UTILITY.UNCL_ARRAY);
Parameters
Table 36-4 ABORT_SYNC_INSTANTIATION Procedure Parameter
Parameter | Description |
---|---|
|
When the When the In either version of the procedure, specify the name of each table in the form |
36.3.4 ABORT_TABLE_INSTANTIATION Procedure
This procedure reverses the effects of running the PREPARE_TABLE_INSTANTIATION
procedure.
Specifically, this procedure performs the following actions:
-
Removes data dictionary information related to the table instantiation
-
Removes any supplemental logging enabled by the
PREPARE_TABLE_INSTANTIATION
procedure
Syntax
DBMS_CAPTURE_ADM.ABORT_TABLE_INSTANTIATION( table_name IN VARCHAR2); container IN VARCHAR2 DEFAULT 'CURRENT');
Parameter
Table 36-5 ABORT_TABLE_INSTANTIATION Procedure Parameter
Parameter | Description |
---|---|
|
The name of the table for which to abort the effects of preparing instantiation, specified as |
|
Either If If If
|
36.3.5 ALTER_CAPTURE Procedure
This procedure alters a capture process.
Syntax
DBMS_CAPTURE_ADM.ALTER_CAPTURE( capture_name IN VARCHAR2, rule_set_name IN VARCHAR2 DEFAULT NULL, remove_rule_set IN BOOLEAN DEFAULT FALSE, start_scn IN NUMBER DEFAULT NULL, use_database_link IN BOOLEAN DEFAULT NULL, first_scn IN NUMBER DEFAULT NULL, negative_rule_set_name IN VARCHAR2 DEFAULT NULL, remove_negative_rule_set IN BOOLEAN DEFAULT FALSE, capture_user IN VARCHAR2 DEFAULT NULL, checkpoint_retention_time IN NUMBER DEFAULT NULL, start_time IN TIMESTAMP DEFAULT NULL, oldest_scn IN NUMBER DEFAULT NULL);
Parameters
Table 36-6 ALTER_CAPTURE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the capture process being altered. You must specify an existing capture process name. Do not specify an owner. |
|
The name of the positive rule set for the capture process. The positive rule set contains the rules that instruct the capture process to capture changes. To change the positive rule set for the capture process, specify an existing rule set in the form An error is returned if the specified rule set does not exist. You can create a rule set and add rules to it using the If you specify |
|
If If you remove a positive rule set for a capture process, and the capture process has a negative rule set, then the capture process captures all supported changes that are not discarded by the negative rule set. If If the |
|
A valid SCN for the database from which the capture process starts capturing changes. The SCN value must be greater than or equal to the first SCN for the capture process. Also, the capture process must be stopped before resetting its start SCN. An error is returned if an invalid SCN is specified or if the capture process is enabled. |
|
If If If |
|
The lowest SCN in the redo log from which a capture process can capture changes. If you specify a new first SCN for the capture process, then the specified first SCN must meet the following requirements:
An error is returned if the specified SCN does not meet the first three requirements. See "Usage Notes" for information about determining an SCN value that meets all of these conditions. When the first SCN is modified, the capture process purges information from its LogMiner data dictionary that is required to restart it at an earlier SCN. See BUILD Procedure for more information about a LogMiner data dictionary. If the specified first SCN is higher than the current start SCN for the capture process, then the start SCN is set automatically to the new value of the first SCN. |
|
The name of the negative rule set for the capture process. The negative rule set contains the rules that instruct the capture process to discard changes. To change the negative rule set for the capture process, specify an existing rule set in the form An error is returned if the specified rule set does not exist. You can create a rule set and add rules to it using the If you specify If you specify both a positive and a negative rule set for a capture process, then the negative rule set is always evaluated first. |
|
If If you remove a negative rule set for a capture process, and a positive rule set exists for the capture process, then the capture process captures all changes that are not discarded by the positive rule set. If If the |
|
The user in whose security domain a capture process captures changes that satisfy its rule sets and runs custom rule-based transformations configured for capture process rules. If To change the capture user, the user who invokes the If you change the capture user, then this procedure grants the new capture user enqueue privilege on the queue used by the capture process and configures the user as a secure queue user of the queue. In addition, ensure that the capture user has the following privileges:
These privileges can be granted directly to the capture user, or they can be granted through roles. In addition, the capture user must be granted The capture process is stopped and restarted automatically when you change the value of this parameter. Note: If the capture user for a capture process is dropped using |
|
Either the number of days that a capture process retains checkpoints before purging them automatically, or If a number is specified, then a capture process purges a checkpoint the specified number of days after the checkpoint was taken. Partial days can be specified using decimal values. For example, When a checkpoint is purged, LogMiner data dictionary information for the archived redo log file that corresponds to the checkpoint is purged, and the |
|
A valid time from which the capture process starts capturing changes. The capture process must be stopped before resetting its start time. An error is returned if an invalid time is specified or if the capture process is enabled. The |
|
The oldest SCN of the transactions currently being processed. |
Usage Notes
If you want to alter the first SCN for a capture process, then the value specified must meet the conditions in the description for the first_scn
parameter.
Examples
The following query determines the current first SCN, applied SCN, and required checkpoint SCN for each capture process in a database:
SELECT CAPTURE_NAME, FIRST_SCN, APPLIED_SCN, REQUIRED_CHECKPOINT_SCN FROM DBA_CAPTURE;
36.3.6 ALTER_SYNC_CAPTURE Procedure
This procedure alters a synchronous capture.
Syntax
DBMS_CAPTURE_ADM.ALTER_SYNC_CAPTURE( capture_name IN VARCHAR2, rule_set_name IN VARCHAR2 DEFAULT NULL, capture_user IN VARCHAR2 DEFAULT NULL);
Parameters
Table 36-7 ALTER_SYNC_CAPTURE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the synchronous capture being altered. You must specify an existing synchronous capture name. Do not specify an owner. |
|
The name of the positive rule set for the synchronous capture. The positive rule set contains the rules that instruct the synchronous capture to capture changes. To change the rule set for the synchronous capture, specify an existing rule set in the form An error is returned if the specified rule set does not exist. If |
|
The user in whose security domain a synchronous capture captures changes that satisfy its rule set and runs custom rule-based transformations configured for synchronous capture rules. If To change the capture user, the user who invokes the If you change the capture user, then this procedure grants the new capture user enqueue privilege on the queue used by the synchronous capture and configures the user as a secure queue user of the queue. In addition, ensure that capture user has the following privileges:
These privileges can be granted directly to the capture user, or they can be granted through roles. In addition, the capture user must be granted |
Usage Notes
If the capture user for a synchronous capture is dropped using DROP
USER . . .
CASCADE
, then the synchronous capture is also dropped automatically.
36.3.7 BUILD Procedure
This procedure extracts the data dictionary of the current database to the redo log and automatically specifies database supplemental logging by running the SQL statement ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
This procedure is overloaded. One version of this procedure contains the OUT
parameter first_scn
, and the other does not.
Syntax
DBMS_CAPTURE_ADM.BUILD( first_scn OUT NUMBER); DBMS_CAPTURE_ADM.BUILD;
Parameters
Table 36-8 BUILD Procedure Parameter
Parameter | Description |
---|---|
|
Contains the lowest SCN value corresponding to the data dictionary extracted to the redo log that can be specified as a first SCN for a capture process |
Usage Notes
The following usage notes apply to this procedure:
-
You can run this procedure multiple times at a source database.
-
If you plan to capture changes originating at a source database with a capture process, then this procedure must be executed at the source database at least once. When the capture process is started, either at a local source database or at a downstream database, the capture process uses the extracted information in the redo log to create a LogMiner data dictionary.
-
A LogMiner data dictionary is a separate data dictionary used by a capture process to determine the details of a change that it is capturing. The LogMiner data dictionary is necessary because the primary data dictionary of the source database might not be synchronized with the redo data being scanned by a capture process.
-
After executing this procedure, you can query the
FIRST_CHANGE#
column of theV$ARCHIVED_LOG
dynamic performance view where theDICTIONARY_BEGIN
column isYES
to determine the lowest SCN value for the database that can be specified as a first SCN for a capture process. The first SCN for a capture process is the lowest SCN in the redo log from which the capture process can capture changes.You can specify the first SCN for a capture process when you run theCREATE_CAPTURE
orALTER_CAPTURE
procedure in theDBMS_CAPTURE_ADM
package. -
In a CDB, the
BUILD
procedure must be executed from the root.
36.3.8 CREATE_CAPTURE Procedure
This procedure creates a capture process.
See Also:
DBMS_RULE_ADM for more information about rules and rule sets
Syntax
DBMS_CAPTURE_ADM.CREATE_CAPTURE( queue_name IN VARCHAR2, capture_name IN VARCHAR2, rule_set_name IN VARCHAR2 DEFAULT NULL, start_scn IN NUMBER DEFAULT NULL, source_database IN VARCHAR2 DEFAULT NULL, use_database_link IN BOOLEAN DEFAULT FALSE, first_scn IN NUMBER DEFAULT NULL, logfile_assignment IN VARCHAR2 DEFAULT 'implicit', negative_rule_set_name IN VARCHAR2 DEFAULT NULL, capture_user IN VARCHAR2 DEFAULT NULL, checkpoint_retention_time IN NUMBER DEFAULT 60, start_time IN TIMESTAMP DEFAULT NULL, source_root_name IN VARCHAR2 DEFAULT NULL, capture_class IN VARCHAR2 DEFAULT 'Streams');
Parameters
Table 36-9 CREATE_CAPTURE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the queue into which the capture process enqueues changes. You must specify an existing queue in the form Note: The |
|
The name of the capture process being created. A Note: The |
|
The name of the positive rule set for the capture process. The positive rule set contains the rules that instruct the capture process to capture changes. If you want to use a positive rule set for the capture process, then you must specify an existing rule set in the form An error is returned if the specified rule set does not exist. You can create a rule set and add rules to it using the If you specify If you specify |
|
A valid SCN for the database from which the capture process starts capturing changes. An error is returned if an invalid SCN is specified. The See Also: "Usage Notes" for more information setting the |
|
The global name of the source database. The source database is where the changes to be captured originated. If you do not include the domain name, then the procedure appends it to the database name automatically. For example, if you specify If |
|
If The capture process uses the database link to prepare database objects for instantiation at the source database and run the During the creation of a downstream capture process, if the If
|
|
The lowest SCN in the redo log from which a capture process can capture changes. A non- You can query the See Also: "Usage Notes" for more information setting the |
|
If If If you specify See Also: "Usage Notes" for information about adding redo log files manually |
|
The name of the negative rule set for the capture process. The negative rule set contains the rules that instruct the capture process to discard changes. If you want to use a negative rule set for the capture process, then you must specify an existing rule set in the form If you specify If you specify An error is returned if the specified rule set does not exist. You can create a rule set and add rules to it using the If you specify both a positive and a negative rule set for a capture process, then the negative rule set is always evaluated first. |
|
The user in whose security domain a capture process captures changes that satisfy its rule sets and runs custom rule-based transformations configured for capture process rules. If Note: If the capture user for a capture process is dropped using See Also: "Usage Notes" for more information about this parameter. |
|
Either specify the number of days that a capture process retains checkpoints before purging them automatically, or specify If a number is specified, then a capture process purges a checkpoint the specified number of days after the checkpoint was taken. Partial days can be specified using decimal values. For example, When a checkpoint is purged, LogMiner data dictionary information for the archived redo log file that corresponds to the checkpoint is purged, and the |
|
A valid time from which the capture process starts capturing changes. An error is returned if an invalid time is specified. The See Also: "Usage Notes" for more information setting the |
|
The global name of the root in the source CDB. If you specify If not Note: In a downstream capture configuration, if the capture database is CDB and the source database is a non-CDB, then specify the same value for |
|
The valid values are If Note: The |
Usage Notes
Consider the following usage notes when you run this procedure:
DBA Role Requirement
If the user who invokes this procedure is different from the user specified in the capture_user
parameter, then the invoking user must be granted the DBA
role. If the user who invokes this procedure is the same as the user specified in the capture_user
parameter, then the DBA
role is not required for the invoking user. Only the SYS
user can set the capture_user
to SYS
.
Capture User Requirements
The capture_user
parameter specifies the user who captures changes that satisfy the capture process rule sets. This user must have the necessary privileges to capture changes. This procedure grants the capture user enqueue privilege on the queue used by the capture process and configures the user as a secure queue user of the queue.
In addition, ensure that the capture user has the following privileges:
-
EXECUTE
privilege on the rule sets used by the capture process -
EXECUTE
privilege on all rule-based transformation functions used in the positive rule set
These privileges can be granted directly to the capture user, or they can be granted through roles.
In addition, the capture user must be granted EXECUTE
privilege on all packages, including Oracle-supplied packages, that are invoked in rule-based transformations run by the capture process. These privileges must be granted directly to the capture user. They cannot be granted through roles.
Note:
-
A capture user does not require privileges on a database object to capture changes to the database object. The capture process can pass these changes to a rule-based transformation function. Therefore, ensure that you consider security implications when you configure a capture process.
-
Creation of the first capture process in a database might take some time because the data dictionary is duplicated during this creation.
First SCN and Start SCN Settings
When you create a capture process using this procedure, you can specify the first SCN and start SCN for the capture process. A capture process scans the redo data from the first SCN or an existing capture process checkpoint forward, even if the start SCN is higher than the first SCN or the checkpoint SCN. In this case, the capture process does not capture any changes in the redo data before the start SCN. Oracle recommends that, at capture process creation time, the difference between the first SCN and start SCN be as small as possible to keep the amount of redo scanned by the capture process to a minimum.
Note:
When you specify the start_time
parameter instead of the start_scn
parameter, the start_time
corresponds with a specific SCN. In this case, the information in this section also applies to the SCN that corresponds with the specified start_time
.
In some cases, the behavior of the capture process is different depending on the settings of these SCN values and on whether the capture process is local or downstream.
The following table describes capture process behavior for SCN value settings:
first_scn Setting | start_scn Setting | Capture Process Type | Description |
---|---|---|---|
Non- |
|
Local or Downstream |
The new capture process is created at the local database with a new LogMiner session starting from the value specified for the The Capture process behavior is the same for a local capture process and a downstream capture process created with these SCN settings, except that a local capture process is created at the source database and a downstream capture process is created at the downstream database. |
Non- |
Non- |
Local or Downstream |
If the specified value for the The Capture process behavior is the same for a local capture process and a downstream capture process created with these SCN settings, except that a local capture process is created at the source database and a downstream capture process is created at the downstream database. |
|
Non- |
Local |
The new capture process creates a new LogMiner data dictionary if either one of the following conditions is true:
In either of these cases, the However, if there is at least one existing local capture process for the local source database that has taken a checkpoint, then the new capture process shares an existing LogMiner data dictionary with one or more of the existing capture processes. In this case, a capture process with a first SCN that is lower than or equal to the specified start SCN must have been started successfully at least once. Also, if there are any in-flight transactions, then the capture process is created after these transactions commit. If there is no existing capture process for the local source database (or if no existing capture processes have taken a checkpoint yet), and the specified start SCN is less than the current SCN for the database, then an error is raised. |
|
Non- |
Downstream |
When the The new capture process creates a new LogMiner data dictionary if either one of the following conditions is true:
In either of these cases, the However, if at least one existing capture process has taken a checkpoint and captures changes to the source database at the downstream database, then the new capture process shares an existing LogMiner data dictionary with one or more of these existing capture processes. In this case, one of these existing capture processes with a first SCN that is lower than or equal to the specified start SCN must have been started successfully at least once. Also, if there are any in-flight transactions, then the capture process is created after these transactions commit. If there is no existing capture process that captures changes to the source database at the downstream database (or no existing capture process has taken a checkpoint), and the specified |
|
|
Local or Downstream |
The behavior is the same as setting the |
See Also:
BUILD Procedure for more information about the BUILD
procedure and the LogMiner data dictionary
Explicit Log File Assignment
If you specify explicit
for the logfile_assignment
parameter, then you add a redo log file manually to a downstream database using the following statement:
ALTER DATABASE REGISTER LOGICAL LOGFILE file_name FOR capture_process;
Here, file_name
is the name of the redo log file being added and capture_process
is the name of the capture process that will use the redo log file at the downstream database. The capture_process
is equivalent to the logminer_session_name
and must be specified. The redo log file must be present at the site running the downstream database. You must transfer this file manually to the site running the downstream database using the DBMS_FILE_TRANSFER
package, FTP, or some other transfer method.
See Also:
Oracle Database SQL Language Reference for more information about the ALTER
DATABASE
statement and Oracle Data Guard Concepts and Administration for more information registering redo log files
36.3.9 CREATE_SYNC_CAPTURE Procedure
This procedure creates a synchronous capture.
Syntax
DBMS_CAPTURE_ADM.CREATE_SYNC_CAPTURE( queue_name IN VARCHAR2, capture_name IN VARCHAR2, rule_set_name IN VARCHAR2, capture_user IN VARCHAR2 DEFAULT NULL);
Parameters
Table 36-10 CREATE_SYNC_CAPTURE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the queue into which the synchronous capture enqueues changes. You must specify an existing queue in the form Note: The |
|
The name of the synchronous capture being created. A Note: The |
|
The name of the positive rule set for the synchronous capture. The positive rule set contains the rules that instruct the synchronous capture to capture changes. Specify an existing rule set in the form An error is returned if the specified rule set does not exist. If |
|
The user in whose security domain the synchronous capture captures changes that satisfy its rule set and runs custom rule-based transformations configured for synchronous capture rules. If Only a user who is granted the Note: If the capture user for a synchronous capture is dropped using See Also: "Usage Notes" for more information about this parameter. |
Usage Notes
When the CREATE_SYNC_CAPTURE
procedure creates a synchronous capture, the procedure must obtain an exclusive lock on each table for which it will capture changes. The rules in the specified rule set for the synchronous capture determine these tables. If there are outstanding transactions on a table for which the synchronous capture will capture changes, then the procedure waits until it can obtain a lock.
The capture_user
parameter specifies the user who captures changes that satisfy the synchronous capture rule set. This user must have the necessary privileges to capture changes.
In addition, ensure that the capture user has the following privileges:
-
ENQUEUE
privilege on the queue specified in thequeue_name
parameter -
EXECUTE
privilege on the rule set used by the synchronous capture -
EXECUTE
privilege on all rule-based transformation functions used in the rule set
These privileges can be granted directly to the capture user, or they can be granted through roles.
In addition, the capture user must be granted EXECUTE
privilege on all packages, including Oracle-supplied packages, that are invoked in rule-based transformations run by the synchronous capture. These privileges must be granted directly to the capture user. These privileges cannot be granted through roles.
Note:
A capture user does not require privileges on a database object to capture changes to the database object. The synchronous capture can pass these changes to a rule-based transformation function. Therefore, ensure that you consider security implications when you configure a synchronous capture.
36.3.10 DROP_CAPTURE Procedure
This procedure drops a capture process.
Syntax
DBMS_CAPTURE_ADM.DROP_CAPTURE( capture_name IN VARCHAR2, drop_unused_rule_sets IN BOOLEAN DEFAULT FALSE);
Parameters
Table 36-11 DROP_CAPTURE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the capture process being dropped. Specify an existing capture process name. Do not specify an owner. |
|
If If |
Usage Notes
The following usage notes apply to this procedure:
The Capture Process Must Be Stopped Before It Is Dropped
A capture process must be stopped before it can be dropped.
See Also:
The DROP_CAPTURE Procedure and Rules-related Information
When you use this procedure to drop a capture process, rules-related information for the capture process is removed from the data dictionary views for Oracle Replication rules. Information about such a rule is removed even if the rule is not in either rule set for the capture process.
The following are the data dictionary views for Oracle Replication rules:
-
ALL_STREAMS_GLOBAL_RULES
-
DBA_STREAMS_GLOBAL_RULES
-
ALL_STREAMS_SCHEMA_RULES
-
DBA_STREAMS_SCHEMA_RULES
-
ALL_STREAMS_TABLE_RULES
-
DBA_STREAMS_TABLE_RULES
-
DBA_STREAMS_RULES
36.3.11 INCLUDE_EXTRA_ATTRIBUTE Procedure
This procedure includes or excludes an extra attribute in logical change records (LCRs) captured by the specified capture process or synchronous capture.
Syntax
DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE( capture_name IN VARCHAR2, attribute_name IN VARCHAR2, include IN BOOLEAN DEFAULT TRUE);
Parameters
Table 36-12 INCLUDE_EXTRA_ATTRIBUTE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the capture process or synchronous capture. Specify an existing capture process name or synchronous capture name. Do not specify an owner. |
|
The name of the attribute to be included in or excluded from LCRs captured by the capture process or synchronous capture. The following names are valid settings:
|
|
If If |
Usage Notes
Some information is not captured by a capture process or synchronous capture unless you use this procedure to specify that the information should be captured. If you want to exclude an extra attribute that is being captured by a capture process or synchronous capture, then specify the attribute and specify FALSE
for the include
parameter.
36.3.12 PREPARE_GLOBAL_INSTANTIATION Procedure
This procedure performs the synchronization necessary for instantiating all the tables in the database at another database and can enable supplemental logging for key columns or all columns in these tables.
This procedure prepares the tables in the database for instantiation when a capture process will be used to capture changes to the tables in the database.
This procedure records the lowest SCN of each object in the database for instantiation. SCNs after the lowest SCN for an object can be used for instantiating the object. Running this procedure prepares all current and future objects in the database for instantiation.
Syntax
DBMS_CAPTURE_ADM.PREPARE_GLOBAL_INSTANTIATION supplemental_logging IN VARCHAR2 DEFAULT 'KEYS', container IN VARCHAR2 DEFAULT 'CURRENT');
Parameter
Table 36-13 PREPARE_GLOBAL_INSTANTIATION Procedure Parameter
Parameter | Description |
---|---|
|
Either If If If |
|
Either If If If
|
Usage Notes
Run this procedure at the source database.
If you use a capture process to capture all of the changes to a database, then use this procedure to prepare the tables in the database for instantiation after the capture process has been configured.
36.3.13 PREPARE_SCHEMA_INSTANTIATION Procedure
This procedure performs the synchronization necessary for instantiating all tables in the schema at another database and can enable supplemental logging for key columns or all columns in these tables.
This procedure prepares the tables in the schema for instantiation when a capture process will be used to capture changes to the tables in the schema.
This procedure records the lowest SCN of each object in the schema for instantiation. SCNs after the lowest SCN for an object can be used for instantiating the object. Running this procedure prepares all current and future objects in the schema for instantiation.
Syntax
DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION( schema_name IN VARCHAR2, supplemental_logging IN VARCHAR2 DEFAULT 'KEYS', container IN VARCHAR2 DEFAULT 'CURRENT');
Parameters
Table 36-14 PREPARE_SCHEMA_INSTANTIATION Procedure Parameters
Parameter | Description |
---|---|
|
The name of the schema. For example, |
|
Either If If If |
|
Either If If If
|
Usage Notes
Run this procedure at the source database. If you use a capture process to capture all of the changes to a schema, then use this procedure to prepare the tables in the schema for instantiation after the capture process has been configured.
36.3.14 PREPARE_SYNC_INSTANTIATION Function
This function performs the synchronization necessary for instantiating one or more tables at another database. This function returns the prepare system change number (SCN) for the table or tables being prepared for instantiation.
This function prepares one or more tables for instantiation when a synchronous capture will be used to capture changes to the tables.
This function records the lowest SCN of each table for instantiation (prepare SCN). SCNs after the lowest SCN for an object can be used for instantiating the object.
This function is overloaded. The table_names
parameter is VARCHAR2
datatype in one version and DBMS_UTILITY.UNCL_ARRAY
datatype in the other version.
Syntax
DBMS_CAPTURE_ADM.PREPARE_SYNC_INSTANTIATION( table_names IN VARCHAR2) RETURN NUMBER; DBMS_CAPTURE_ADM.PREPARE_SYNC_INSTANTIATION( table_names IN DBMS_UTILITY.UNCL_ARRAY) RETURN NUMBER;
Parameters
Table 36-15 PREPARE_SYNC_INSTANTIATION Function Parameter
Parameter | Description |
---|---|
|
When the When the In either version of the function, specify the name of each table in the form |
36.3.15 PREPARE_TABLE_INSTANTIATION Procedure
This procedure performs the synchronization necessary for instantiating the table at another database and can enable supplemental logging for key columns or all columns in the table
This procedure prepares the table for instantiation when a capture process will be used to capture changes to the table.
This procedure records the lowest SCN of the table for instantiation. SCNs after the lowest SCN for an object can be used for instantiating the object.
Syntax
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION( table_name IN VARCHAR2, supplemental_logging IN VARCHAR2 DEFAULT 'KEYS', container IN VARCHAR2 DEFAULT 'CURRENT');
Parameters
Table 36-16 PREPARE_TABLE_INSTANTIATION Procedure Parameters
Parameter | Description |
---|---|
|
The name of the table specified as |
|
Either If If If |
|
Either If If If
|
Usage Notes
Run this procedure at the source database. If you use a capture process to capture all of the changes to a table, then use this procedure to prepare the table for instantiation after the capture process has been configured.
36.3.16 SET_PARAMETER Procedure
This procedure sets a capture process parameter to the specified value.
Syntax
DBMS_CAPTURE_ADM.SET_PARAMETER( capture_name IN VARCHAR2, parameter IN VARCHAR2, value IN VARCHAR2 DEFAULT NULL);
Parameters
Table 36-17 SET_PARAMETER Procedure Parameters
Parameter | Description |
---|---|
|
The name of the capture process. Do not specify an owner. |
|
The name of the parameter you are setting. |
|
The value to which the parameter is set. If |
Capture Process Parameters
The following table lists the parameters for the capture process.
Table 36-18 Capture Process Parameters
Parameter Name | Possible Values | Default | Description |
---|---|---|---|
|
|
|
The maximum length of the DDL text, in bytes, up to which the DDL can be chosen for annotation, if applicable for the DDL. Any DDL text whose size is greater than the specified size is not annotated. The default, Annotating DDL can simplify filtering and transformation of the DDL within Oracle GoldenGate. Note: This parameter is intended for Oracle GoldenGate. Do not use this parameter in an Oracle Replication environment or in an XStream environment. |
|
|
|
If If Capture processes do not fully support capturing changes to some data types from the redo log. ID key LCRs enable an XStream configuration to capture these changes and process them with an XStream client application. Note: This parameter is intended for XStream. Do not use this parameter in an Oracle Replication environment unless XStream optimizations are enabled by the See Also: "Usage Notes" for more information about this parameter and Oracle Database XStream Guide for more information about ID key LCRs |
|
|
|
If If An apply process or XStream inbound server can use sequence LCRs to ensure that the sequence values at a destination database use the appropriate values. For increasing sequences, the sequence values at the destination are equal to or greater than the sequence values at the source database. For decreasing sequences, the sequence values at the destination are less than or equal to the sequence values at the source database. Note: This parameter is intended for XStream. Do not use this parameter in an Oracle Replication environment unless XStream optimizations are enabled by the See Also: "Usage Notes" for more information about this parameter and "SET_PARAMETER Procedure" for information about the |
|
|
|
If If When a capture process is restarted, it starts to capture changes at the point where it last stopped. A restarted capture process gets a new session identifier, and the processes associated with the capture process also get new session identifiers. However, the capture process number ( |
|
|
|
If If An error is raised if an attempt is made to set this parameter for a local capture process. |
|
Comma-delimited list of Oracle Replication tags |
|
Controls whether the capture process captures DML changes that are tagged with one of the specified Oracle Replication tags. Whether the capture process captures these changes depends on the settings for the If Note: This parameter is intended for XStream. Do not use this parameter in an Oracle Replication environment unless XStream optimizations are enabled by the See Also: "Usage Notes" for more information about this parameter |
|
Comma-delimited list of transaction names |
|
Controls whether the capture process captures DML changes in the specified transaction names. Whether the capture process captures these changes depends on the settings for the If Note: This parameter is intended for XStream. Do not use this parameter in an Oracle Replication environment unless XStream optimizations are enabled by the See Also: "Usage Notes" for more information about this parameter |
|
Comma-delimited list of user names |
|
Controls whether the capture process captures DML changes made by the specified users. Whether the capture process captures these changes depends on the settings for the Specify an exact pattern match for each user name. The pattern match is case sensitive. For example, specify If Note: This parameter is intended for XStream. Do not use this parameter in an Oracle Replication environment unless XStream optimizations are enabled by the See Also: "Usage Notes" for more information about this parameter |
|
Comma-delimited list of user ID values |
|
Controls whether the capture process captures data manipulation language (DML) changes made by the specified users. Whether the capture process captures these changes depends on the settings for the To view the user ID for a user, query the If Note: This parameter is intended for XStream. Do not use this parameter in an Oracle Replication environment unless XStream optimizations are enabled by the See Also: "Usage Notes" for more information about this parameter |
|
|
|
If If In either case, the capture process captures a DML change only if it satisfies the capture process's rule sets. Note: This parameter is intended for XStream. Do not use this parameter in an Oracle Replication environment unless XStream optimizations are enabled by the See Also: "Usage Notes" for more information about this parameter |
|
|
|
If If In either case, the capture process captures a DML change only if it satisfies the capture process's rule sets. Note: This parameter is intended for XStream. Do not use this parameter in an Oracle Replication environment unless XStream optimizations are enabled by the See Also: "Usage Notes" for more information about this parameter |
|
A valid transaction ID or |
|
Instructs the capture process to ignore the specified transaction from the source database, effective immediately. The capture process eliminates all subsequent LCRs for the transaction. If the specified transaction is committed successfully at the source database, the destination database will receive a If Use caution when setting this parameter because ignoring a transaction might lead to data divergence between the source database and destination database. To ignore multiple transactions, specify each transaction in a separate call to the |
|
A fully qualified table name, |
|
Controls the behavior of the capture process when it tries to capture changes to a specified table or to an unsupported table. A capture process tries to capture changes to an unsupported table when its rule sets instruct it to do so. If you do not want the capture process to try to capture changes to unsupported tables, then ensure that the capture process's rule sets exclude unsupported tables. When a table name is specified, the capture process does not capture changes to the specified table. The table name must be entered in the form When When |
|
A list of tables or schema names separated by commas |
|
Directs capture to include changes from the specified tables or schemas. An LCR that is selected by Note: This parameter is intended for XStream. Do not use or attempt to set this parameter in an Oracle Replication environment unless XStream optimizations are enabled by the See Also: "Usage Notes" for more information about this parameter |
|
|
|
If If Note: This parameter is intended for XStream. Do not use or attempt to set this parameter in an Oracle Replication environment unless XStream optimizations are enabled by the See Also: "Usage Notes" for more information about this parameter |
|
A valid SCN or |
|
The capture process is disabled before capturing a change record with an SCN greater than or equal to the value specified. If |
|
A positive integer |
|
Controls the amount of system global area (SGA) memory allocated specifically to the capture process, in megabytes. The capture process attempts to allocate memory up to this limit. A capture process uses Oracle LogMiner to scan for changes in the redo log. The memory is allocated for the duration of the capture process session and is released when the capture process becomes disabled. Note: The sum of system global area (SGA) memory allocated for all components on a database must be less than the value set for the If Note: This parameter is intended for XStream. Do not use or attempt to set this parameter in an Oracle Replication environment unless XStream optimizations are enabled by the See Also: "Usage Notes" for more information about this parameter |
|
A negative integer, |
|
The amount of time, in seconds, between the message creation time of the original capture process and the message creation time of the cloned capture process. Specifically, if the difference, in seconds, between the This parameter is relevant only when changes captured by the capture process are applied by two or more apply processes and the If a negative value is specified, then automatic merge is disabled. If If |
|
A positive integer or |
|
The capture process stops after capturing the specified number of messages. If |
|
|
|
The frequency at which messages captured by the capture process are tracked automatically. For example, if this parameter is set to the default value of The tracking label used for automatic message tracking is If |
|
A positive integer |
|
The number of preparer servers that can concurrently mine the redo log for the capture process. A capture process consists of one reader server, one or more preparer servers, and one builder server. The preparer servers concurrently format changes found in the redo log into logical change records (LCRs). Each reader server, preparer server, and builder server is a process, and the number of preparer servers equals the number specified for the Setting the Note: When you change the value of this parameter, the capture process is stopped and restarted automatically. |
|
|
|
If If |
|
|
|
The amount of time, in seconds, that a stream is broken before the stream is automatically split from other streams that flow from the capture process. When a stream is split, the capture process, queue, and propagation are cloned. In this case, a stream is a flow of logical change records (LCRs) that flows from a capture process to an apply. A stream is broken when LCRs captured by the capture process cannot reach the apply process. For example, a stream is broken when the relevant propagation or apply process is disabled. This parameter is relevant only when changes captured by the capture process are applied by two or more apply processes. If If This parameters is designed to be used with the |
|
|
|
The maximum number of seconds to wait for another instantiation of the same capture process to finish. If the other instantiation of the same capture process does not finish within this time, then the capture process does not start. This parameter is useful only if you are starting the capture process manually. If |
|
A positive integer or |
|
The capture process stops as soon as possible after the specified number of seconds since it started. If |
|
|
|
Set this parameter only under the guidance of Oracle Support Services. |
|
|
|
If If Note: This parameter is intended for XStream. Do not use or attempt to set this parameter in an Oracle Replication environment unless XStream optimizations are enabled by the See Also: "Usage Notes" for more information about this parameter |
|
|
|
If If The message specifies the reason the capture process stopped. |
|
|
|
A single capture process cannot send LCRs to both outbound servers and apply processes. In an XStream configuration where an outbound server runs on a different database than its capture process, set this parameter to Note: Using XStream requires purchasing a license for the Oracle GoldenGate product. See Oracle Database XStream Guide. |
Usage Notes
The following usage notes apply to the SET_PARAMETER
procedure:
-
Delays Are Possible Before New Parameter Settings Take Effect
-
XStream or Oracle GoldenGate Integrated Capture Configurations
Delays Are Possible Before New Parameter Settings Take Effect
When you alter a parameter value, a short amount of time might pass before the new value for the parameter takes effect.
Parameters Interpreted as Positive Integers
For all parameters that are interpreted as positive integers, the maximum possible value is 4,294,967,295
. Where applicable, specify INFINITE
for larger values.
Parameters with a System Change Number (SCN) Setting
For parameters that require an SCN setting, any valid SCN value can be specified.
Parameters that Require XStream Optimizations
A capture process uses the following parameters only when the capture process is sending logical change records (LCRs) to an XStream outbound server or when XStream optimizations are enabled for Oracle Replication components:
-
capture_idkey_objects
-
capture_sequence_nextval
-
excludetag
-
excludetrans
-
excludeuser
-
excludeuserid
-
getapplops
-
getreplicates
-
include_objects
-
inline_lob_optimization
-
max_sga_size
-
use_rac_services
The DBMS_XSTREAM_ADM.ENABLE_GG_XSTREAM_FOR_STREAMS
procedure enables XStream optimizations for Oracle Replication. When XStream optimizations are not enabled by the DBMS_XSTREAM_ADM.ENABLE_GG_XSTREAM_FOR_STREAMS
procedure, a capture process raises an error if one of these parameters is set to any value other than its default value.
When XStream optimizations are enabled for Oracle Replication and the capture_idkey_objects
parameter is set to Y
, a capture process can capture ID key LCRs. ID key LCRs do not contain all of the columns for a row change. Instead, they contain the rowid of the changed row, a group of key columns to identify the row in the table, and the data for the scalar columns of the table that are supported by capture processes. An apply process can apply these changes using the information available the ID key LCRs.
To determine the database objects for which a capture process will capture ID key LCRs, run the following query on the source database:
SELECT OWNER, OBJECT_NAME FROM DBA_XSTREAM_OUT_SUPPORT_MODE WHERE SUPPORT_MODE='ID KEY';
Note:
Using XStream requires purchasing a license for the Oracle GoldenGate product. See Oracle Database XStream Guide.
See Also:
Oracle Database XStream Guide for more information about ID key LCRs
XStream or Oracle GoldenGate Integrated Capture Configurations
In an XStream or Oracle GoldenGate integrated capture configuration, the following parameters control which changes are captured by a capture process:
-
capture_idkey_objects
-
capture_sequence_nextval
-
excludetag
-
excludetrans
-
excludeuser
-
excludeuserid
-
getapplops
-
getreplicates
-
include_objects
-
inline_lob_optimization
-
max_sga_size
-
use_rac_services
You can set these parameters to avoid change cycling. Change cycling sends a change back to the database where it originated. Typically, change cycling should be avoided in a replication environment so that the same change is not made to a database more than once.
In an XStream or Oracle GoldenGate integrated capture configuration that performs bi-directional replication, a GoldenGate Replicat process runs on the source database for a capture process. Therefore, the changes made by the GoldenGate Replicat are recorded in the redo log.
If an integrated configuration performs bi-directional replication, then, to avoid change cycling, the capture process should not capture the changes made by the Oracle GoldenGate Replicat process. To accomplish this goal, use the default settings for the getapplops
and getreplicates
parameters and exclude changes made by the user running the Replicat process. To exclude these changes, specify this user in the excludeuserid
or excludeuser
parameter. Typically, the user running the Oracle GoldenGate Replicat process is the XStream administrator.
In some configurations, the goal might be to capture or exclude changes made by applications or by the Replicat process. For example, an intermediate database in a replication environment might capture all of the changes made to the database, including both application changes and Replicat process changes, and send these changes to a different destination database.
Table 36-19 describes the capture process behavior when at least one of the exclude parameters is non-NULL
.
Table 36-19 Behavior When at Least One exclude Parameter Is Non-NULL
getapplops Setting | getreplicates Setting | Description |
---|---|---|
|
|
The capture process captures all DML changes. |
|
|
The capture process captures the DML changes made by the users that are not in the The capture process captures the DML changes that are not in the transactions in the The capture process captures only the DML changes that do not have a tag that is in the |
|
|
The capture process captures only the DML changes made by the users that are in the The capture process captures only the DML changes that are in the transactions in the The capture process captures only the DML changes that have a tag that is in the |
|
|
The capture process does not capture any DML changes. |
Table 36-19 describes the capture process behavior when all of the exclude parameters are set to NULL
.
Table 36-20 Behavior When All exclude Parameters Are Set to NULL
getapplops Setting | getreplicates Setting | Description |
---|---|---|
|
|
The capture process captures all DML changes. |
|
|
The capture process captures all DML changes. |
|
|
The capture process does not capture any DML changes. |
|
|
The capture process does not capture any DML changes. |
See the documentation for the Oracle GoldenGate product for more information:
http://docs.oracle.com/cd/E15881_01/index.htm
Note:
A capture process evaluates a change using these parameters before it evaluates a change using its rule sets. Therefore, a capture process can discard a change before the change is evaluated against the capture process's rule sets. Also, regardless of the settings for these parameters, a capture process captures a change only if the change satisfies the capture process's rule sets.
See Also:
36.3.17 START_CAPTURE Procedure
This procedure starts the capture process, which mines redo logs and enqueues the mined redo information into the associated queue.
The start status is persistently recorded. Hence, if the status is ENABLED
, then the capture process is started upon database instance startup.
The capture process is a background Oracle process and is prefixed by c
.
The enqueue and dequeue state of DBMS_AQADM.START_QUEUE
and DBMS_AQADM.STOP_QUEUE
have no effect on the start status of a capture process.
Syntax
DBMS_CAPTURE_ADM.START_CAPTURE( capture_name IN VARCHAR2);
Parameters
Table 36-21 START_CAPTURE Procedure Parameter
Parameter | Description |
---|---|
|
The name of the capture process. Do not specify an owner. The capture process uses LogMiner to capture changes in the redo information. A |
Usage Notes
The capture process status is persistently recorded. Hence, if the status is ENABLED
, then the capture process is started upon database instance startup. A capture process (c
nnn
) is an Oracle background process.
36.3.18 STOP_CAPTURE Procedure
This procedure stops the capture process from mining redo logs.
Syntax
DBMS_CAPTURE_ADM.STOP_CAPTURE( capture_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
Parameters
Table 36-22 STOP_CAPTURE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the capture process. A |
|
If If |
Usage Notes
The following usage notes apply to this procedure:
-
The capture process status is persistently recorded. Hence, if the status is
DISABLED
orABORTED
, then the capture process is not started upon database instance startup. -
A capture process is an Oracle background process with a name in the form
CP
nn
, wherenn
can include letters and numbers. -
The enqueue and dequeue state of
DBMS_AQADM.START_QUEUE
andDBMS_AQADM.STOP_QUEUE
have no effect on the stop status of a capture process.