A capture process captures changes in a redo log, reformats the captured changes into logical change records (LCRs), and enqueues the LCRs into an ANYDATA
queue.
This chapter contains these topics:
Managing the Checkpoint Retention Time for a Capture Process
Adding an Archived Redo Log File to a Capture Process Explicitly
Each task described in this chapter should be completed by a Streams administrator that has been granted the appropriate privileges, unless specified otherwise.
You can create a capture process that captures changes either locally at the source database or remotely at a downstream database. If a capture process runs on a downstream database, then redo data from the source database is copied to the downstream database, and the capture process captures changes in redo data at the downstream database.
You can use any of the following procedures to create a local capture process:
Each of the procedures in the DBMS_STREAMS_ADM
package creates a capture process with the specified name if it does not already exist, creates either a positive or negative rule set for the capture process if the capture process does not have such a rule set, and can add table rules, schema rules, or global rules to the rule set.
The CREATE_CAPTURE
procedure creates a capture process, but does not create a rule set or rules for the capture process. However, the CREATE_CAPTURE
procedure enables you to specify an existing rule set to associate with the capture process, either as a positive or a negative rule set, a first SCN, and a start SCN for the capture process. To create a capture process that performs downstream capture, you must use the CREATE_CAPTURE
procedure.
Attention:
When a capture process is started or restarted, it might need to scan redo log files with aFIRST_CHANGE#
value that is lower than start SCN. Removing required redo log files before they are scanned by a capture process causes the capture process to abort. You can query the DBA_CAPTURE
data dictionary view to determine the first SCN, start SCN, and required checkpoint SCN for a capture process. A capture process needs the redo log file that includes the required checkpoint SCN, and all subsequent redo log files. See "Capture Process Creation" for more information about the first SCN and start SCN for a capture process.Note:
To configure downstream capture, the source database must be an Oracle Database 10g Release 1 database or later.The following sections describe:
After creating a capture process, avoid changing the DBID
or global name of the source database for the capture process. If you change either the DBID
or global name of the source database, then the capture process must be dropped and re-created.
To create a capture process, a user must be granted DBA
role.
See Also:
Oracle Streams Replication Administrator's Guide for information about changing the DBID or global name of a source database
The following tasks must be completed before you create a capture process:
Configure any source database that generates redo data that will be captured by a capture process to run in ARCHIVELOG
mode. See "ARCHIVELOG Mode and a Capture Process" and Oracle Database Administrator's Guide. For downstream capture processes, the downstream database also must run in ARCHIVELOG
mode if you plan to configure a real-time downstream capture process. The downstream database does not need to run in ARCHIVELOG
mode if you plan to run only an archived-log downstream capture process on it.
Make sure the initialization parameters are set properly on any database that will run a capture process. See "Setting Initialization Parameters Relevant to Streams".
Create a Streams administrator on each database involved in the Streams configuration. See "Configuring a Streams Administrator". The examples in this chapter assume that the Streams administrator is strmadmin
.
Create an ANYDATA
queue to associate with the capture process, if one does not exist. See "Creating an ANYDATA Queue" for instructions. The examples in this chapter assume that the queue used by the capture process is strmadmin.streams_queue
. Create the queue on the same database that will run the capture process.
The following sections describe using the DBMS_STREAMS_ADM
package and the DBMS_CAPTURE_ADM
package to create a local capture process. Make sure you complete the tasks in "Preparing to Create a Capture Process" before you proceed.
This section contains the following examples:
Example of Creating a Local Capture Process Using DBMS_STREAMS_ADM
Example of Creating a Local Capture Process Using DBMS_CAPTURE_ADM
Example of Creating a Local Capture Process with Non-NULL Start SCN
The following example runs the ADD_TABLE_RULES
procedure in the DBMS_STREAMS_ADM
package to create a local capture process:
BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.employees', streams_type => 'capture', streams_name => 'strm01_capture', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, include_tagged_lcr => false, source_database => NULL, inclusion_rule => true); END; /
Running this procedure performs the following actions:
Creates a capture process named strm01_capture
. The capture process is created only if it does not already exist. If a new capture process is created, then this procedure also sets the start SCN to the point in time of creation.
Associates the capture process with an existing queue named streams_queue
.
Creates a positive rule set and associates it with the capture process, if the capture process does not have a positive rule set, because the inclusion_rule
parameter is set to true
. The rule set uses the SYS.STREAMS$_EVALUATION_CONTEXT
evaluation context. The rule set name is system generated.
Creates two rules. One rule evaluates to TRUE
for DML changes to the hr.employees
table, and the other rule evaluates to TRUE
for DDL changes to the hr.employees
table. The rule names are system generated.
Adds the two rules to the positive rule set associated with the capture process. The rules are added to the positive rule set because the inclusion_rule
parameter is set to true
.
Specifies that the capture process captures a change in the redo log only if the change has a NULL
tag, because the include_tagged_lcr
parameter is set to false
. This behavior is accomplished through the system-created rules for the capture process.
Creates a capture process that captures local changes to the source database because the source_database
parameter is set to NULL
. For a local capture process, you can also specify the global name of the local database for this parameter.
Prepares the hr.employees
table for instantiation.
See Also:
Oracle Streams Replication Administrator's Guide for more information about Streams tags
The following example runs the CREATE_CAPTURE
procedure in the DBMS_CAPTURE_ADM
package to create a local capture process:
BEGIN DBMS_CAPTURE_ADM.CREATE_CAPTURE( queue_name => 'strmadmin.streams_queue', capture_name => 'strm02_capture', rule_set_name => 'strmadmin.strm01_rule_set', start_scn => NULL, source_database => NULL, use_database_link => false, first_scn => NULL); END; /
Running this procedure performs the following actions:
Creates a capture process named strm02_capture
. A capture process with the same name must not exist.
Associates the capture process with an existing queue named streams_queue
.
Associates the capture process with an existing rule set named strm01_rule_set
. This rule set is the positive rule set for the capture process.
Creates a capture process that captures local changes to the source database because the source_database
parameter is set to NULL
. For a local capture process, you can also specify the global name of the local database for this parameter.
Specifies that the Oracle database determines the start SCN and first SCN for the capture process because both the start_scn
parameter and the first_scn
parameter are set to NULL
.
If no other capture processes that capture local changes are running on the local database, then the BUILD
procedure in the DBMS_CAPTURE_ADM
package is run automatically. Running this procedure extracts the data dictionary to the redo log, and a LogMiner data dictionary is created when the capture process is started for the first time.
This example runs the CREATE_CAPTURE
procedure in the DBMS_CAPTURE_ADM
package to create a local capture process with a start SCN set to 223525
. This example assumes that there is at least one local capture process at the database, and that this capture process has taken at least one checkpoint. You can always specify a start SCN for a new capture process that is equal to or greater than the current SCN of the source database. If you want to specify a start SCN that is lower than the current SCN of the database, then the specified start SCN must be higher than the lowest first SCN for an existing local capture process that has been started successfully at least once and has taken at least one checkpoint.
You can determine the first SCN for existing capture processes, and whether these capture processes have taken a checkpoint, by running the following query:
SELECT CAPTURE_NAME, FIRST_SCN, MAX_CHECKPOINT_SCN FROM DBA_CAPTURE;
Your output looks similar to the following:
CAPTURE_NAME FIRST_SCN MAX_CHECKPOINT_SCN ------------------------------ ---------- ------------------ CAPTURE_SIMP 223522 230825
These results show that the capture_simp
capture process has a first SCN of 223522
. Also, this capture process has taken a checkpoint because the MAX_CHECKPOINT_SCN
value is non-NULL
. Therefore, the start SCN for the new capture process can be set to 223522
or higher.
Before you proceed, complete the tasks in "Preparing to Create a Capture Process". Next, run the following procedure to create the capture process:
BEGIN DBMS_CAPTURE_ADM.CREATE_CAPTURE( queue_name => 'strmadmin.streams_queue', capture_name => 'strm05_capture', rule_set_name => 'strmadmin.strm01_rule_set', start_scn => 223525, source_database => NULL, use_database_link => false, first_scn => NULL); END; /
Running this procedure performs the following actions:
Creates a capture process named strm05_capture
. A capture process with the same name must not exist.
Associates the capture process with an existing queue named streams_queue
.
Associates the capture process with an existing rule set named strm01_rule_set
. This rule set is the positive rule set for the capture process.
Specifies 223525
as the start SCN for the capture process. The new capture process uses the same LogMiner data dictionary as one of the existing capture processes. Streams automatically chooses which LogMiner data dictionary to share with the new capture process. Because the first_scn
parameter was set to NULL
, the first SCN for the new capture process is the same as the first SCN of the existing capture process whose LogMiner data dictionary was shared. In this example, the existing capture process is capture_simp
.
Creates a capture process that captures local changes to the source database because the source_database
parameter is set to NULL
. For a local capture process, you can also specify the global name of the local database for this parameter.
Note:
If no local capture process exists when the procedure in this example is run, then theDBMS_CAPTURE_ADM.BUILD
procedure is run automatically during capture process creation to extract the data dictionary into the redo log. The first time the new capture process is started, it uses this redo data to create a LogMiner data dictionary. In this case, a specified start_scn
parameter value must be equal to or higher than the current database SCN.This section describes preparing for a downstream capture process and configuring a real-time or archived-log downstream capture process.
This section contains these topics:
Creating an Archived-Log Downstream Capture Process that Assigns Logs Implicitly
Creating an Archived-Log Downstream Capture Process that Assigns Logs Explicitly
See Also:
"Downstream Capture" for conceptual information about downstream captureComplete the following steps to prepare the source database to transmit its redo data to the downstream database, and to prepare the downstream database to accept the redo data:
Complete the tasks in "Preparing to Create a Capture Process".
Configure Oracle Net so that the source database can communicate with the downstream database.
Set the following initialization parameters to configure redo transport services to transmit redo data from the source database to the downstream database:
At the source database, configure at least one LOG_ARCHIVE_DEST_
n
initialization parameter to transmit redo data to the downstream database. To do this, set the following attributes of this parameter:
SERVICE
- Specify the network service name of the downstream database.
ARCH
, LGWR
ASYNC
, or LGWR
SYNC
- Specify a redo transport mode.
If you specify ARCH
(the default), then the archiver process (ARC
n
) will archive the redo log files to the downstream database. You can specify ARCH
for an archived-log downstream capture process only.
If you specify LGWR
ASYNC
, then the log writer process (LGWR) will archive the redo log files to the downstream database. The advantage of specifying LGWR
ASYNC
is that it results in little or no effect on the performance of the source database. If the source database is running Oracle Database 10g Release 1 or later, then LGWR
ASYNC
is recommended to avoid affecting source database performance if the downstream database or network is performing poorly. You can specify LGWR
ASYNC
for an archived-log downstream capture process or a real-time downstream capture process.
The advantage of specifying LGWR
SYNC
is that redo data is sent to the downstream database faster then when LGWR
ASYNC
is specified. You can specify LGWR
SYNC
for a real-time downstream capture process only.
NOREGISTER
- Specify this attribute so that the location of the archived redo log files is not recorded in the downstream database control file.
VALID_FOR
- Specify either (ONLINE_LOGFILE,PRIMARY_ROLE
) or (ONLINE_LOGFILE,ALL_ROLES
).
TEMPLATE
- If you are configuring an archived-log downstream capture process, then specify a directory and format template for archived redo logs at the downstream database. The TEMPLATE
attribute overrides the LOG_ARCHIVE_FORMAT
initialization parameter settings at the downstream database. The TEMPLATE
attribute is valid only with remote destinations. Ensure that the format uses all of the following variables at each source database: %t
, %s
, and %r
.
Do not specify the TEMPLATE
attribute if you are configuring a real-time downstream capture process.
DB_UNIQUE_NAME
- The unique name of the downstream database. Use the name specified for the DB_UNIQUE_NAME
initialization parameter at the downstream database.
The following example is a LOG_ARCHIVE_DEST_
n
setting that specifies a downstream database for a real-time downstream capture process:
LOG_ARCHIVE_DEST_2='SERVICE=DBS2.NET LGWR ASYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dbs2'
The following example is a LOG_ARCHIVE_DEST_
n
setting that specifies a downstream database for an archived-log downstream capture process:
LOG_ARCHIVE_DEST_2='SERVICE=DBS2.NET LGWR ASYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) TEMPLATE=/usr/oracle/log_for_dbs1/dbs1_arch_%t_%s_%r.log DB_UNIQUE_NAME=dbs2'
Tip:
Specify a value for theTEMPLATE
attribute that keeps log files from a remote source database separate from local database log files. In addition, if the downstream database contains log files from multiple source databases, then the log files from each source database should be kept separate from each other.LOG_ARCHIVE_DEST_STATE_
n
- At the source database, set this initialization parameter that corresponds with the LOG_ARCHIVE_DEST_
n
parameter for the downstream database to ENABLE
.
For example, if the LOG_ARCHIVE_DEST_2
initialization parameter is set for the downstream database, then set the LOG_ARCHIVE_DEST_STATE_2
parameter in the following way:
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_CONFIG
- At both the source database and the downstream database, set the DB_CONFIG
attribute in this initialization parameter to include the DB_UNIQUE_NAME
of the source database and the downstream database.
For example, if the DB_UNIQUE_NAME
of the source database is dbs1
, and the DB_UNIQUE_NAME
of the downstream database is dbs2
, then specify the following parameter:
LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbs1,dbs2)'
By default, the LOG_ARCHIVE_CONFIG
parameter enables a database to both send and receive redo.
See Also:
Oracle Database Reference and Oracle Data Guard Concepts and Administration for more information about these initialization parametersIf you reset any initialization parameters while an instance was running at a database in Step 3, then you might want to reset them in the relevant initialization parameter file as well, so that the new values are retained when the database is restarted.
If you did not reset the initialization parameters while an instance was running, but instead reset them in the initialization parameter file in Step 3, then restart the database. The source database must be open when it sends redo data to the downstream database, because the global name of the source database is sent to the downstream database only if the source database is open.
To create a capture process that performs downstream capture, you must use the CREATE_CAPTURE
procedure. The example in this section describes creating a real-time downstream capture process that uses a database link to the source database. However, a real-time downstream capture process might not use a database link.
This example assumes the following:
The source database is dbs1.net
and the downstream database is dbs2.net
.
The capture process that will be created at dbs2.net
uses the streams_queue
.
The capture process will capture DML changes to the hr.departments
table.
This section contains an example that runs the CREATE_CAPTURE
procedure in the DBMS_CAPTURE_ADM
package to create a real-time downstream capture process at the dbs2.net
downstream database that captures changes made to the dbs1.net
source database. The capture process in this example uses a database link to dbs1.net
for administrative purposes.
Complete the following steps:
Complete the tasks in "Preparing to Create a Capture Process".
Complete the steps in "Preparing to Transmit Redo Data to a Downstream Database".
At the downstream database, set the following initialization parameters to configure the downstream database to receive redo data from the source database and write the redo data to the standby redo log at the downstream database:
Set at least one archive log destination in the LOG_ARCHIVE_DEST_
n
initialization parameter to a directory on the computer system running the downstream database. To do this, set the following attributes of this parameter:
LOCATION
- Specify a valid path name for a disk directory on the system that hosts the downstream database. Each destination that specifies the LOCATION
attribute must identify a unique directory path name. This is the local destination for archived redo log files written from the standby redo logs. Log files from a remote source database should be kept separate from local database log files. In addition, if the downstream database contains log files from multiple source databases, then the log files from each source database should be kept separate from each other.
VALID
FOR
- Specify either (STANDBY_LOGFILE,PRIMARY_ROLE)
or (STANDBY_LOGFILE,ALL_ROLES)
.
The following example is a LOG_ARCHIVE_DEST_
n
setting at the real-time capture downstream database:
LOG_ARCHIVE_DEST_2='LOCATION=/home/arc_dest/srl_dbs1 VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)'
You can specify other attributes in the LOG_ARCHIVE_DEST_
n
initialization parameter if necessary.
Set the LOG_ARCHIVE_DEST_STATE_
n
initialization parameter that corresponds with the LOG_ARCHIVE_DEST_
n
parameter for the downstream database to ENABLE
.
For example, if the LOG_ARCHIVE_DEST_2
initialization parameter is set for the downstream database, then set one LOG_ARCHIVE_DEST_STATE_2
parameter in the following way:
LOG_ARCHIVE_DEST_STATE_2=ENABLE
Optionally set the LOG_ARCHIVE_FORMAT
initialization parameter to generate the filenames in a specified format for the archived redo log files. The following example is a valid LOG_ARCHIVE_FORMAT
setting:
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
If you set other archive destinations at the downstream database, then, to keep archived standby redo log files separate from archived online redo log files from the downstream database, explicitly specify ONLINE_LOGFILE
or STANDBY_LOGFILE
, instead of ALL_LOGFILES
, in the VALID_FOR
attribute. For example, if the LOG_ARCHIVE_DEST_1
parameter specifies the archive destination for the online redo log files at the downstream database, then avoid the ALL_LOGFILES
keyword in the VALID_FOR
attribute when you set the LOG_ARCHIVE_DEST_1
parameter.
See Also:
Oracle Database Reference and Oracle Data Guard Concepts and Administration for more information about these initialization parametersIf you reset any initialization parameters while an instance was running at a database in Step 3, then you might want to reset them in the relevant initialization parameter file as well, so that the new values are retained when the database is restarted.
If you did not reset the initialization parameters while an instance was running, but instead reset them in the initialization parameter file in Step 3, then restart the database. The source database must be open when it sends redo data to the downstream database, because the global name of the source database is sent to the downstream database only if the source database is open.
Create standby redo log files.
Note:
The following steps outline the general procedure for adding standby redo log files to the downstream database. The specific steps and SQL statements used to add standby redo log files depend on your environment. For example, in a Real Application Clusters environment, the steps are different. See Oracle Data Guard Concepts and Administration for detailed instructions about adding standby redo log files to a database.In SQL*Plus, connect to the source database dbs1.net
as an administrative user.
Determine the log file size used on the source database. The standby log file size must exactly match (or be larger than) the source database log file size. For example, if the source database log file size is 500 MB, then the standby log file size must be 500 MB or larger. You can determine the size of the redo log files at the source database (in bytes) by querying the V$LOG
view at the source database.
For example, query the V$LOG
view:
SELECT BYTES FROM V$LOG;
Determine the number of standby log file groups required on the downstream database. The number of standby log file groups must be at least one more than the number of online log file groups on the source database. For example, if the source database has two online log file groups, then the downstream database must have at least three standby log file groups. You can determine the number of source database online log file groups by querying the V$LOG
view at the source database.
For example, query the V$LOG
view:
SELECT COUNT(GROUP#) FROM V$LOG;
In SQL*Plus, connect to the downstream database dbs2.net
as an administrative user.
Use the SQL statement ALTER
DATABASE
ADD
STANDBY
LOGFILE
to add the standby log file groups to the downstream database.
For example, assume that the source database has two online redo log file groups and is using a log file size of 500 MB. In this case, use the following statements to create the appropriate standby log file groups:
ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 ('/oracle/dbs/slog3a.rdo', '/oracle/dbs/slog3b.rdo') SIZE 500M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/oracle/dbs/slog4.rdo', '/oracle/dbs/slog4b.rdo') SIZE 500M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/oracle/dbs/slog5.rdo', '/oracle/dbs/slog5b.rdo') SIZE 500M;
Ensure that the standby log file groups were added successfully by running the following query:
SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$STANDBY_LOG;
You output should be similar to the following:
GROUP# THREAD# SEQUENCE# ARC STATUS ---------- ---------- ---------- --- ---------- 3 0 0 YES UNASSIGNED 4 0 0 YES UNASSIGNED 5 0 0 YES UNASSIGNED
Connect to the downstream database dbs2.net
as the Streams administrator.
CONNECT strmadmin/strmadminpw@dbs2.net
Create a database link from dbs2.net
to dbs1.net
. For example, if the user strmadmin
is the Streams administrator on both databases, then create the following database link:
CREATE DATABASE LINK dbs1.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'dbs1.net';
This example assumes that a Streams administrator exists at the source database dbs1.net
. If no Streams administrator exists at the source database, then the Streams administrator at the downstream database should connect to a user who allows remote access by a Streams administrator. You can enable remote access for a user by specifying the user as the grantee when you run the GRANT_REMOTE_ADMIN_ACCESS
procedure in the DBMS_STREAMS_AUTH
package at the source database.
Run the CREATE_CAPTURE
procedure to create the capture process:
BEGIN DBMS_CAPTURE_ADM.CREATE_CAPTURE( queue_name => 'strmadmin.streams_queue', capture_name => 'real_time_capture', rule_set_name => NULL, start_scn => NULL, source_database => 'dbs1.net', use_database_link => true, first_scn => NULL, logfile_assignment => 'implicit'); END; /
Running this procedure performs the following actions:
Creates a capture process named real_time_capture
at the downstream database dbs2.net
. A capture process with the same name must not exist.
Associates the capture process with an existing queue on dbs2.net
named streams_queue
.
Specifies that the source database of the changes that the capture process will capture is dbs1.net
.
Specifies that the capture process uses a database link with the same name as the source database global name to perform administrative actions at the source database.
Specifies that the capture process accepts redo data implicitly from dbs1.net
. Therefore, the capture process scans the standby redo log at dbs2.net
for changes that it must capture. If the capture process falls behind, then it scans the archived redo log files written from the standby redo log.
This step does not associate the capture process real_time_capture
with any rule set. A rule set will be created and associated with the capture process in the next step.
If no other capture process at dbs2.net
is capturing changes from the dbs1.net
source database, then the DBMS_CAPTURE_ADM.BUILD
procedure is run automatically at dbs1.net
using the database link. Running this procedure extracts the data dictionary at dbs1.net
to the redo log, and a LogMiner data dictionary for dbs1.net
is created at dbs2.net
when the capture process real_time_capture
is started for the first time at dbs2.net
.
If multiple capture processes at dbs2.net
are capturing changes from the dbs1.net
source database, then the new capture process real_time_capture
uses the same LogMiner data dictionary for dbs1.net
as one of the existing archived-log capture process. Streams automatically chooses which LogMiner data dictionary to share with the new capture process.
Note:
Only one real-time downstream capture process is allowed at a single downstream database.Set the downstream_real_time_mine
capture process parameter to y
:
BEGIN DBMS_CAPTURE_ADM.SET_PARAMETER( capture_name => 'real_time_capture', parameter => 'downstream_real_time_mine', value => 'y'); END; /
Create the positive rule set for the capture process and add a rule to it:
BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.departments', streams_type => 'capture', streams_name => 'real_time_capture', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => false, include_tagged_lcr => false, source_database => 'dbs1.net', inclusion_rule => true); END; /
Running this procedure performs the following actions:
Creates a rule set at dbs2.net
for capture process real_time_capture
. The rule set has a system-generated name. The rule set is the positive rule set for the capture process because the inclusion_rule
parameter is set to true
.
Creates a rule that captures DML changes to the hr.departments
table, and adds the rule to the positive rule set for the capture process. The rule has a system-generated name. The rule is added to the positive rule set for the capture process because the inclusion_rule
parameter is set to true
.
Prepares the hr.departments
table at dbs1.net
for instantiation using the database link created in Step 7.
Enables supplemental logging for any primary key, unique key, bitmap index, and foreign key columns in the hr.departments
table. Primary key supplemental logging is required for the hr.departments
table because this example creates a capture processes that captures changes to this table.
Connect to the source database dbs1.net
as an administrative user with the necessary privileges to switch log files.
Archive the current log file at the source database:
ALTER SYSTEM ARCHIVE LOG CURRENT;
Archiving the current log file at the source database starts real time mining of the source database redo log.
Now you can configure propagation or apply, or both, of the LCRs captured by the capture process.
In this example, if you want to use an apply process to apply the LCRs at the downstream database dbs2.net
, then set the instantiation SCN for the hr.departments
table at dbs2.net
. If this table does not exist at dbs2.net
, then instantiate it at dbs2.net
.
For example, if the hr.departments
table exists at dbs2.net
, then set the instantiation SCN for the hr.departments
table at dbs2.net
by running the following procedure at the destination database dbs2.net
:
DECLARE iscn NUMBER; -- Variable to hold instantiation SCN value BEGIN iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@DBS1.NET; DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN( source_object_name => 'hr.departments', source_database_name => 'dbs1.net', instantiation_scn => iscn); END; /
After the instantiation SCN has been set, you can configure an apply process to apply LCRs for the hr.departments
table from the streams_queue
queue. Setting the instantiation SCN for an object at a database is required only if an apply process applies LCRs for the object. When all of the necessary propagations and apply processes are configured, start the capture process using the START_CAPTURE
procedure in DBMS_CAPTURE_ADM
.
Note:
If you want the database objects to be synchronized at the source database and the destination database, then make sure the database objects are consistent when you set the instantiation SCN at the destination database. In the previous example, thehr.departments
table should be consistent at the source and destination databases when the instantiation SCN is set.See Also:
Oracle Streams Replication Administrator's Guide for more information about instantiation
This section describes configuring an archived-log downstream capture process that either assigns log files implicitly or explicitly.
This section contains these topics:
Creating an Archived-Log Downstream Capture Process that Assigns Logs Implicitly
Creating an Archived-Log Downstream Capture Process that Assigns Logs Explicitly
This section contains an example that runs the CREATE_CAPTURE
procedure in the DBMS_CAPTURE_ADM
package to create an archived-log downstream capture process at the dbs2.net
downstream database that captures changes made to the dbs1.net
source database. The capture process in this example uses a database link to dbs1.net
for administrative purposes.
This example assumes the following:
The source database is dbs1.net
and the downstream database is dbs2.net
.
The capture process that will be created at dbs2.net
uses the streams_queue
.
The capture process will capture DML changes to the hr.departments
table.
The capture process assigns log files implicitly. That is, the downstream capture process automatically scans all redo log files added by redo transport services or manually from the source database to the downstream database.
Complete the following steps:
Complete the tasks in "Preparing to Create a Capture Process".
Complete the steps in "Preparing to Transmit Redo Data to a Downstream Database".
Connect to the downstream database dbs2.net
as the Streams administrator.
CONNECT strmadmin/strmadminpw@dbs2.net
Create the database link from dbs2.net
to dbs1.net
. For example, if the user strmadmin
is the Streams administrator on both databases, then create the following database link:
CREATE DATABASE LINK dbs1.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'dbs1.net';
This example assumes that a Streams administrator exists at the source database dbs1.net
. If no Streams administrator exists at the source database, then the Streams administrator at the downstream database should connect to a user who allows remote access by a Streams administrator. You can enable remote access for a user by specifying the user as the grantee when you run the GRANT_REMOTE_ADMIN_ACCESS
procedure in the DBMS_STREAMS_AUTH
package at the source database.
While connected to the downstream database as the Streams administrator, run the CREATE_CAPTURE
procedure to create the capture process:
BEGIN DBMS_CAPTURE_ADM.CREATE_CAPTURE( queue_name => 'strmadmin.streams_queue', capture_name => 'strm04_capture', rule_set_name => NULL, start_scn => NULL, source_database => 'dbs1.net', use_database_link => true, first_scn => NULL, logfile_assignment => 'implicit'); END; /
Running this procedure performs the following actions:
Creates a capture process named strm04_capture
at the downstream database dbs2.net
. A capture process with the same name must not exist.
Associates the capture process with an existing queue on dbs2.net
named streams_queue
.
Specifies that the source database of the changes that the capture process will capture is dbs1.net
.
Specifies that the capture process accepts new redo log files implicitly from dbs1.net
. Therefore, the capture process scans any new log files copied from dbs1.net
to dbs2.net
for changes that it must capture. These log files must be added to the capture process automatically using redo transport services or manually using the following DDL statement:
ALTER DATABASE REGISTER LOGICAL LOGFILE file_name FOR capture_process;
Here, file_name
is the name of the redo log file and capture_process
is the name of the capture process that will use the redo log file at the downstream database. You must add redo log files manually only if the logfile_assignment
parameter is set to explicit
.
This step does not associate the capture process strm04_capture
with any rule set. A rule set will be created and associated with the capture process in the next step.
If no other capture process at dbs2.net
is capturing changes from the dbs1.net
source database, then the DBMS_CAPTURE_ADM.BUILD
procedure is run automatically at dbs1.net
using the database link. Running this procedure extracts the data dictionary at dbs1.net
to the redo log, and a LogMiner data dictionary for dbs1.net
is created at dbs2.net
when the capture process is started for the first time at dbs2.net
.
If multiple capture processes at dbs2.net
are capturing changes from the dbs1.net
source database, then the new capture process uses the same LogMiner data dictionary for dbs1.net
as one of the existing capture process. Streams automatically chooses which LogMiner data dictionary to share with the new capture process.
See Also:
Oracle Database SQL Reference for more information about the ALTER
DATABASE
statement
Oracle Data Guard Concepts and Administration for more information registering redo log files
While connected to the downstream database as the Streams administrator, create the positive rule set for the capture process and add a rule to it:
BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.departments', streams_type => 'capture', streams_name => 'strm04_capture', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => false, include_tagged_lcr => false, source_database => 'dbs1.net', inclusion_rule => true); END; /
Running this procedure performs the following actions:
Creates a rule set at dbs2.net
for capture process strm04_capture
. The rule set has a system-generated name. The rule set is a positive rule set for the capture process because the inclusion_rule
parameter is set to true
.
Creates a rule that captures DML changes to the hr.departments
table, and adds the rule to the rule set for the capture process. The rule has a system-generated name. The rule is added to the positive rule set for the capture process because the inclusion_rule
parameter is set to true
.
Now you can configure propagation or apply, or both, of the LCRs captured by the strm04_capture
capture process.
In this example, if you want to use an apply process to apply the LCRs at the downstream database dbs2.net
, then set the instantiation SCN for the hr.departments
table at dbs2.net
. If this table does not exist at dbs2.net
, then instantiate it at dbs2.net
.
For example, if the hr.departments
table exists at dbs2.net
, then connect to the source database as the Streams administrator, and create a database link to dbs2.net
:
CONNECT strmadmin/strmadminpw@dbs1.net CREATE DATABASE LINK dbs2.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'dbs2.net';
Set the instantiation SCN for the hr.departments
table at dbs2.net
by running the following procedure at the source database dbs1.net
:
DECLARE iscn NUMBER; -- Variable to hold instantiation SCN value BEGIN iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DBS2.NET( source_object_name => 'hr.departments', source_database_name => 'dbs1.net', instantiation_scn => iscn); END; /
After the instantiation SCN has been set, you can configure an apply process to apply LCRs for the hr.departments
table from the streams_queue
queue. Setting the instantiation SCN for an object at a database is required only if an apply process applies LCRs for the object. When all of the necessary propagations and apply processes are configured, start the capture process using the START_CAPTURE
procedure in DBMS_CAPTURE_ADM
.
Note:
If you want the database objects to be synchronized at the source database and the destination database, then make sure the database objects are consistent when you set the instantiation SCN at the destination database. In the previous example, thehr.departments
table should be consistent at the source and destination databases when the instantiation SCN is set.See Also:
Oracle Streams Replication Administrator's Guide for more information about instantiation
To create a capture process that performs downstream capture, you must use the CREATE_CAPTURE
procedure. This section describes creating an archived-log downstream capture process that assigns redo log files explicitly. That is, you must use the DBMS_FILE_TRANSFER
package, FTP, or some other method to transfer redo log files from the source database to the downstream database, and then you must register these redo log files with the downstream capture process manually.
In this example, assume the following:
The source database is dbs1.net
and the downstream database is dbs2.net
.
The capture process that will be created at dbs2.net
uses the streams_queue
.
The capture process will capture DML changes to the hr.departments
table.
The capture process does not use a database link to the source database for administrative actions.
Complete the following steps:
Complete the tasks in "Preparing to Create a Capture Process".
Complete the steps in "Preparing to Transmit Redo Data to a Downstream Database".
Connect to the source database dbs1.net
as the Streams administrator. For example, if the Streams administrator is strmadmin
, then issue the following statement:
CONNECT strmadmin/strmadminpw@dbs1.net
If you do not use a database link from the downstream database to the source database, then a Streams administrator must exist at the source database.
If there is no capture process at dbs2.net
that captures changes from dbs1.net
, then perform a build of the dbs1.net
data dictionary in the redo log. This step is optional if a capture process at dbs2.net
is already configured to capture changes from the dbs1.net
source database.
SET SERVEROUTPUT ON DECLARE scn NUMBER; BEGIN DBMS_CAPTURE_ADM.BUILD( first_scn => scn); DBMS_OUTPUT.PUT_LINE('First SCN Value = ' || scn); END; / First SCN Value = 409391
This procedure displays the valid first SCN value for the capture process that will be created at dbs2.net
. Make a note of the SCN value returned because you will use it when you create the capture process at dbs2.net
.
If you run this procedure to build the data dictionary in the redo log, then when the capture process is first started at dbs2.net
, it will create a LogMiner data dictionary using the data dictionary information in the redo log.
Prepare the hr.departments
table for instantiation:
BEGIN DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION( table_name => 'hr.departments', supplemental_logging => 'keys'); END; /
Primary key supplemental logging is required for the hr.departments
table because this example creates a capture processes that captures changes to this table. Specifying keys
for the supplemental_logging
parameter in the PREPARE_TABLE_INSTANTIATION
procedure enables supplemental logging for any primary key, unique key, bitmap index, and foreign key columns in the table.
Determine the current SCN of the source database:
SET SERVEROUTPUT ON SIZE 1000000 DECLARE iscn NUMBER; -- Variable to hold instantiation SCN value BEGIN iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_OUTPUT.PUT_LINE('Current SCN: ' || iscn); END; /
You can use the returned SCN as the instantiation SCN for destination databases that will apply changes to the hr.departments
table that were captured by the capture process being created. In this example, assume the returned SCN is 1001656
.
Connect to the downstream database dbs2.net
as the Streams administrator. For example, if the Streams administrator is strmadmin
, then issue the following statement:
CONNECT strmadmin/strmadminpw@dbs2.net
Run the CREATE_CAPTURE
procedure to create the capture process and specify the value obtained in Step 4 for the first_scn
parameter:
BEGIN DBMS_CAPTURE_ADM.CREATE_CAPTURE( queue_name => 'strmadmin.streams_queue', capture_name => 'strm05_capture', rule_set_name => NULL, start_scn => NULL, source_database => 'dbs1.net', use_database_link => false, first_scn => 409391, -- Use value from Step 4 logfile_assignment => 'explicit'); END; /
Running this procedure performs the following actions:
Creates a capture process named strm05_capture
at the downstream database dbs2.net
. A capture process with the same name must not exist.
Associates the capture process with an existing queue on dbs2.net
named streams_queue
.
Specifies that the source database of the changes that the capture process will capture is dbs1.net
.
Specifies that the first SCN for the capture process is 409391
. This value was obtained in Step 4. The first SCN is the lowest SCN for which a capture process can capture changes. Because a first SCN is specified, the capture process creates a new LogMiner data dictionary when it is first started, regardless of whether there are existing LogMiner data dictionaries for the same source database.
Specifies new redo log files from dbs1.net
must be assigned to the capture process explicitly. After a redo log file has been transferred to the computer running the downstream database, you assign the log file to the capture process explicitly using the following DDL statement:
ALTER DATABASE REGISTER LOGICAL LOGFILE file_name FOR capture_process;
Here, file_name
is the name of the redo log file and capture_process
is the name of the capture process that will use the redo log file at the downstream database. You must add redo log files manually if the logfile_assignment
parameter is set to explicit
.
This step does not associate the capture process strm05_capture
with any rule set. A rule set will be created and associated with the capture process in the next step.
See Also:
Oracle Database SQL Reference for more information about the ALTER
DATABASE
statement
Oracle Data Guard Concepts and Administration for more information registering redo log files
Create the positive rule set for the capture process and add a rule to it:
BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.departments', streams_type => 'capture', streams_name => 'strm05_capture', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => false, include_tagged_lcr => false, source_database => 'dbs1.net', inclusion_rule => true); END; /
Running this procedure performs the following actions:
Creates a rule set at dbs2.net
for capture process strm05_capture
. The rule set has a system-generated name. The rule set is a positive rule set for the capture process because the inclusion_rule
parameter is set to true
.
Creates a rule that captures DML changes to the hr.departments
table, and adds the rule to the rule set for the capture process. The rule has a system-generated name. The rule is added to the positive rule set for the capture process because the inclusion_rule
parameter is set to true
.
After the redo log file at the source database dbs1.net
that contains the first SCN for the downstream capture process is archived, transfer the archived redo log file to the computer running the downstream database. The BUILD
procedure in Step 4 determined the first SCN for the downstream capture process. If the redo log file is not yet archived, you can run the ALTER
SYSTEM
SWITCH
LOGFILE
statement on the database to archive it.
You can run the following query at dbs1.net
to identify the archived redo log file that contains the first SCN for the downstream capture process:
COLUMN NAME HEADING 'Archived Redo Log|File Name' FORMAT A50 COLUMN FIRST_CHANGE# HEADING 'First SCN' FORMAT 999999999 SELECT NAME, FIRST_CHANGE# FROM V$ARCHIVED_LOG WHERE FIRST_CHANGE# IS NOT NULL AND DICTIONARY_BEGIN = 'YES';
Transfer the archived redo log file with a FIRST_CHANGE#
that matches the first SCN returned in Step 4 to the computer running the downstream capture process.
At the downstream database dbs2.net
, connect as an administrative user and assign the transferred redo log file to the capture process. For example, if the redo log file is /oracle/logs_from_dbs1/1_10_486574859.dbf
, then issue the following statement:
ALTER DATABASE REGISTER LOGICAL LOGFILE '/oracle/logs_from_dbs1/1_10_486574859.dbf' FOR 'strm05_capture';
Now you can configure propagation or apply, or both, of the LCRs captured by the strm05_capture
capture process.
In this example, if you want to use an apply process to apply the LCRs at the downstream database dbs2.net
, then set the instantiation SCN for the hr.departments
table at dbs2.net
. If this table does not exist at dbs2.net
, then instantiate it at dbs2.net
.
For example, if the hr.departments
table exists at dbs2.net
, then set the instantiation SCN for the hr.departments
table at dbs2.net
to the value determined in Step 6. Run the following procedure at dbs2.net
to set the instantiation SCN for the hr.departments
table:
CONNECT strmadmin/strmadminpw@dbs2.net BEGIN DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN( source_object_name => 'hr.departments', source_database_name => 'dbs1.net', instantiation_scn => 1001656); END; /
After the instantiation SCN has been set, you can configure an apply process to apply LCRs for the hr.departments
table from the streams_queue
queue. Setting the instantiation SCN for an object at a database is required only if an apply process applies LCRs for the object. When all of the necessary propagations and apply processes are configured, start the capture process using the START_CAPTURE
procedure in DBMS_CAPTURE_ADM
.
Note:
If you want the database objects to be synchronized at the source database and the destination database, then make sure the database objects are consistent when you set the instantiation SCN at the destination database. In the previous example, thehr.departments
table should be consistent at the source and destination databases when the instantiation SCN is set.See Also:
Oracle Streams Replication Administrator's Guide for more information about instantiation
If you plan to configure propagations and apply processes that process LCRs captured by the new capture process, then perform the configuration steps in the following order:
Create all of the propagations that will propagate LCRs captured by the new capture process. See "Creating a Propagation Between Two ANYDATA Queues".
If you created a downstream capture process, and the captured changes will be applied at the downstream database by an apply process, then the capture process and apply process can use the same queue at the downstream database. Using the same queue for the downstream capture process and the apply process at a downstream database is more efficient than propagating the changes between two queues, and it eliminates the need for a propagation.
Create all of the apply processes that will dequeue LCRs captured by the new capture process. See "Creating an Apply Process". Configure each apply process to apply captured LCRs.
Instantiate the tables for which the new capture process captures changes at all destination databases. See Oracle Streams Replication Administrator's Guide for detailed information about instantiation.
Start the apply processes that will process LCRs captured by the new capture process. See "Starting an Apply Process".
Start the new capture process. See "Starting a Capture Process".
Note:
Other configuration steps might be required for your Oracle Streams environment. For example, some Oracle Streams environments include transformations, apply handlers, and conflict resolution.You run the START_CAPTURE
procedure in the DBMS_CAPTURE_ADM
package to start an existing capture process. For example, the following procedure starts a capture process named strm01_capture
:
BEGIN DBMS_CAPTURE_ADM.START_CAPTURE( capture_name => 'strm01_capture'); END; /
Note:
If a new capture process will use a new LogMiner data dictionary, then, when you first start the new capture process, some time might be required to populate the new LogMiner data dictionary. A new LogMiner data dictionary is created if a non-NULL
first SCN value was specified when the capture process was created.You run the STOP_CAPTURE
procedure in the DBMS_CAPTURE_ADM
package to stop an existing capture process. For example, the following procedure stops a capture process named strm01_capture
:
BEGIN DBMS_CAPTURE_ADM.STOP_CAPTURE( capture_name => 'strm01_capture'); END; /
This section contains instructions for completing the following tasks:
You can specify one positive rule set and one negative rule set for a capture process. The capture process captures a change if it evaluates to TRUE
for at least one rule in the positive rule set and evaluates to FALSE
for all the rules in the negative rule set. The negative rule set is evaluated before the positive rule set.
You specify an existing rule set as the positive rule set for an existing capture process using the rule_set_name
parameter in the ALTER_CAPTURE
procedure. This procedure is in the DBMS_CAPTURE_ADM
package.
For example, the following procedure sets the positive rule set for a capture process named strm01_capture
to strm02_rule_set
.
BEGIN DBMS_CAPTURE_ADM.ALTER_CAPTURE( capture_name => 'strm01_capture', rule_set_name => 'strmadmin.strm02_rule_set'); END; /
You specify an existing rule set as the negative rule set for an existing capture process using the negative_rule_set_name
parameter in the ALTER_CAPTURE
procedure. This procedure is in the DBMS_CAPTURE_ADM
package.
For example, the following procedure sets the negative rule set for a capture process named strm01_capture
to strm03_rule_set
.
BEGIN DBMS_CAPTURE_ADM.ALTER_CAPTURE( capture_name => 'strm01_capture', negative_rule_set_name => 'strmadmin.strm03_rule_set'); END; /
To add rules to a rule set for an existing capture process, you can run one of the following procedures in the DBMS_STREAMS_ADM
package and specify the existing capture process:
Excluding the ADD_SUBSET_RULES
procedure, these procedures can add rules to the positive rule set or negative rule set for a capture process. The ADD_SUBSET_RULES
procedure can add rules only to the positive rule set for a capture process.
See Also:
"System-Created Rules"The following example runs the ADD_TABLE_RULES
procedure in the DBMS_STREAMS_ADM
package to add rules to the positive rule set of a capture process named strm01_capture
:
BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.departments', streams_type => 'capture', streams_name => 'strm01_capture', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, inclusion_rule => true); END; /
Running this procedure performs the following actions:
Creates two rules. One rule evaluates to TRUE
for DML changes to the hr.departments
table, and the other rule evaluates to TRUE
for DDL changes to the hr.departments
table. The rule names are system generated.
Adds the two rules to the positive rule set associated with the capture process because the inclusion_rule
parameter is set to true
.
Prepares the hr.departments
table for instantiation by running the PREPARE_TABLE_INSTANTIATION
procedure in the DBMS_CAPTURE_ADM
package.
Enables supplemental logging for any primary key, unique key, bitmap index, and foreign key columns in the hr.departments
table. When the PREPARE_TABLE_INSTANTIATION
procedure is run, the default value (keys
) is specified for the supplemental_logging
parameter.
If the capture process is performing downstream capture, then the table is prepared for instantiation and supplemental logging is enabled for key columns only if the downstream capture process uses a database link to the source database. If a downstream capture process does not use a database link to the source database, then the table must be prepared for instantiation manually and supplemental logging must be enabled manually.
The following example runs the ADD_TABLE_RULES
procedure in the DBMS_STREAMS_ADM
package to add rules to the negative rule set of a capture process named strm01_capture
:
BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.job_history', streams_type => 'capture', streams_name => 'strm01_capture', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, inclusion_rule => false); END; /
Running this procedure performs the following actions:
Creates two rules. One rule evaluates to TRUE
for DML changes to the hr.job_history
table, and the other rule evaluates to TRUE
for DDL changes to the hr.job_history
table. The rule names are system generated.
Adds the two rules to the negative rule set associated with the capture process, because the inclusion_rule
parameter is set to false
.
You specify that you want to remove a rule from a rule set for an existing capture process by running the REMOVE_RULE
procedure in the DBMS_STREAMS_ADM
package. For example, the following procedure removes a rule named departments3
from the positive rule set of a capture process named strm01_capture
.
BEGIN DBMS_STREAMS_ADM.REMOVE_RULE( rule_name => 'departments3', streams_type => 'capture', streams_name => 'strm01_capture', drop_unused_rule => true, inclusion_rule => true); END; /
In this example, the drop_unused_rule
parameter in the REMOVE_RULE
procedure is set to true
, which is the default setting. Therefore, if the rule being removed is not in any other rule set, then it will be dropped from the database. If the drop_unused_rule
parameter is set to false
, then the rule is removed from the rule set, but it is not dropped from the database.
If the inclusion_rule
parameter is set to false
, then the REMOVE_RULE
procedure removes the rule from the negative rule set for the capture process, not the positive rule set.
If you want to remove all of the rules in a rule set for the capture process, then specify NULL
for the rule_name
parameter when you run the REMOVE_RULE
procedure.
You specify that you want to remove a rule set from an existing capture process using the ALTER_CAPTURE
procedure in the DBMS_CAPTURE_ADM
package. This procedure can remove the positive rule set, negative rule set, or both. Specify true
for the remove_rule_set
parameter to remove the positive rule set for the capture process. Specify true
for the remove_negative_rule_set
parameter to remove the negative rule set for the capture process.
For example, the following procedure removes both the positive and negative rule set from a capture process named strm01_capture
.
BEGIN DBMS_CAPTURE_ADM.ALTER_CAPTURE( capture_name => 'strm01_capture', remove_rule_set => true, remove_negative_rule_set => true); END; /
Note:
If a capture process does not have a positive or negative rule set, then the capture process captures all supported changes to all objects in the database, excluding database objects in theSYS
, SYSTEM
, and CTXSYS
schemas.Set a capture process parameter using the SET_PARAMETER
procedure in the DBMS_CAPTURE_ADM
package. Capture process parameters control the way a capture process operates.
For example, the following procedure sets the parallelism
parameter for a capture process named strm01_capture
to 3
.
BEGIN DBMS_CAPTURE_ADM.SET_PARAMETER( capture_name => 'strm01_capture', parameter => 'parallelism', value => '3'); END; /
Note:
Setting the parallelism
parameter automatically stops and restarts a capture process.
The value
parameter is always entered as a VARCHAR2
value, even if the parameter value is a number.
See Also:
The DBMS_CAPTURE_ADM.SET_PARAMETER
procedure in the Oracle Database PL/SQL Packages and Types Reference for detailed information about the capture process parameters
The capture user is the user who captures all DML changes and DDL changes that satisfy the capture process rule sets. Set the capture user for a capture process using the capture_user
parameter in the ALTER_CAPTURE
procedure in the DBMS_CAPTURE_ADM
package.
To change the capture user, the user who invokes the ALTER_CAPTURE
procedure must be granted DBA
role. Only the SYS
user can set the capture_user
to SYS
.
For example, the following procedure sets the capture user for a capture process named strm01_capture
to hr
.
BEGIN DBMS_CAPTURE_ADM.ALTER_CAPTURE( capture_name => 'strm01_capture', capture_user => 'hr'); END; /
Running 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, make sure the capture user has the following privileges:
EXECUTE
privilege on the rule sets used by the capture process
EXECUTE
privilege on all custom rule-based transformation functions used in the rule set
These privileges must be granted directly to the capture user. They cannot be granted through roles.
The checkpoint retention time is the amount of time that a capture process retains checkpoints before purging them automatically. Set the checkpoint retention time for a capture process using checkpoint_retention_time
parameter in the ALTER_CAPTURE
procedure of the DBMS_CAPTURE_ADM
package.
See Also:
"Capture Process Checkpoints"When you set the checkpoint retention time, you can specify partial days with decimal values. For example, run the following procedure to specify that a capture process named strm01_capture
should purge checkpoints automatically every ten days and twelve hours:
BEGIN DBMS_CAPTURE_ADM.ALTER_CAPTURE( capture_name => 'strm01_capture', checkpoint_retention_time => 10.5); END; /
To specify that a capture process should not purge checkpoints automatically, set the checkpoint retention time to DBMS_CAPTURE_ADM.INFINITE
. For example, the following procedure sets the checkpoint retention time for a name strm01_capture
to infinite:
BEGIN DBMS_CAPTURE_ADM.ALTER_CAPTURE( capture_name => 'strm01_capture', checkpoint_retention_time => DBMS_CAPTURE_ADM.INFINITE); END; /
Supplemental logging must be specified for some columns at a source database for changes to the columns to be applied successfully at a destination database. Typically, supplemental logging is required in Streams replication environments, but it might be required in any environment that processes captured messages with an apply process. You use the ALTER
DATABASE
statement to specify supplemental logging for all tables in a database, and you use the ALTER
TABLE
statement to specify supplemental logging for a particular table.
See Also:
Oracle Streams Replication Administrator's Guide for more information about specifying supplemental loggingYou can add an archived redo log file to a capture process manually using the following statement:
ALTER DATABASE REGISTER LOGICAL LOGFILE file_name FOR capture_process;
Here, file_name
is the name of the archived 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 capture process.
For example, to add the /usr/log_files/1_3_486574859.dbf
archived redo log file to a capture process named strm03_capture
, issue the following statement:
ALTER DATABASE REGISTER LOGICAL LOGFILE '/usr/log_files/1_3_486574859.dbf' FOR 'strm03_capture';
See Also:
Oracle Database SQL Reference for more information about theALTER
DATABASE
statement and Oracle Data Guard Concepts and Administration for more information registering redo log filesYou can set the first SCN for an existing capture process using the ALTER_CAPTURE
procedure in the DBMS_CAPTURE_ADM
package.
The specified first SCN must meet the following requirements:
It must be greater than the current first SCN for the capture process.
It must be less than or equal to the current applied SCN for the capture process. However, this requirement does not apply if the current applied SCN for the capture process is zero.
It must be less than or equal to the required checkpoint SCN for the capture process.
You can determine the current first SCN, applied SCN, and required checkpoint SCN for each capture process in a database using the following query:
SELECT CAPTURE_NAME, FIRST_SCN, APPLIED_SCN, REQUIRED_CHECKPOINT_SCN FROM DBA_CAPTURE;
When you reset a first SCN for a capture process, information below the new first SCN setting is purged from the LogMiner data dictionary for the capture process automatically. Therefore, after the first SCN is reset for a capture process, the start SCN for the capture process cannot be set lower than the new first SCN. Also, redo log files that contain information prior to the new first SCN setting will never be needed by the capture process.
For example, the following procedure sets the first SCN for a capture process named strm01_capture
to 351232
.
BEGIN DBMS_CAPTURE_ADM.ALTER_CAPTURE( capture_name => 'strm01_capture', first_scn => 351232); END; /
Note:
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.
If you need to capture changes in the redo log from a point in time in the past, then you can create a new capture process and specify a first SCN that corresponds to a previous data dictionary build in the redo log. The BUILD
procedure in the DBMS_CAPTURE_ADM
package performs a data dictionary build in the redo log.
You can query the DBA_LOGMNR_PURGED_LOG
data dictionary view to determine which redo log files will never be needed by any capture process.
See Also:
"First SCN and Start SCN Specifications During Capture Process Creation"
"Displaying SCN Values for Each Redo Log File Used by Each Capture Process" for a query that determines which redo log files are no longer needed
You can set the start SCN for an existing capture process using the ALTER_CAPTURE
procedure in the DBMS_CAPTURE_ADM
package. Typically, you reset the start SCN for a capture process if point-in-time recovery must be performed on one of the destination databases that receive changes from the capture process.
The specified start SCN must be greater than or equal to the first SCN for the capture process. When you reset a start SCN for a capture process, make sure the required redo log files are available to the capture process.
You can determine the first SCN for each capture process in a database using the following query:
SELECT CAPTURE_NAME, FIRST_SCN FROM DBA_CAPTURE;
For example, the following procedure sets the start SCN for a capture process named strm01_capture
to 750338
.
BEGIN DBMS_CAPTURE_ADM.ALTER_CAPTURE( capture_name => 'strm01_capture', start_scn => 750338); END; /
See Also:
Oracle Streams Replication Administrator's Guide for information about performing database point-in-time recovery on a destination database in a Streams environment
You specify whether an existing downstream capture process uses a database link to the source database for administrative purposes using the ALTER_CAPTURE
procedure in the DBMS_CAPTURE_ADM
package. Set the use_database_link
parameter to true
to specify that the downstream capture process uses a database link, or you set the use_database_link
parameter to false
to specify that the downstream capture process does not use a database link.
If you want a capture process that is not using a database link currently to begin using a database link, then specify true
for the use_database_link
parameter. In this case, a database link with the same name as the global name as the source database must exist at the downstream database.
If you want a capture process that is using a database link currently to stop using a database link, then specify false
for the use_database_link
parameter. In this case, some administration must be performed manually after you alter the capture process. For example, if you add new capture process rules using the DBMS_STREAMS_ADM
package, then you must prepare the objects relating to the rules for instantiation manually at the source database.
If you specify NULL
for the use_database_link
parameter, then the current value of this parameter for the capture process is not changed.
The example in "Creating an Archived-Log Downstream Capture Process that Assigns Logs Explicitly" created the capture process strm05_capture
and specified that this capture process does not use a database link. To create a database link to the source database dbs1.net
and specify that this capture process uses the database link, complete the following actions:
CREATE DATABASE LINK dbs1.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'dbs1.net'; BEGIN DBMS_CAPTURE_ADM.ALTER_CAPTURE( capture_name => 'strm05_capture', use_database_link => true); END; /
See Also:
"Local Capture and Downstream Capture"You can use the INCLUDE_EXTRA_ATTRIBUTE
procedure in the DBMS_CAPTURE_ADM
package to instruct a capture process to capture one or more extra attributes. You can also use this procedure to instruct a capture process to exclude an extra attribute that it is capturing currently.
The extra attributes are the following:
row_id
(row LCRs only)
serial#
session#
thread#
tx_name
username
This section contains instructions for completing the following tasks:
See Also:
"Viewing the Extra Attributes Captured by Each Capture Process"
Oracle Database PL/SQL Packages and Types Reference for more information about the INCLUDE_EXTRA_ATTRIBUTE
procedure
To instruct a capture process named strm01_capture
to include the transaction name in each captured message, run the following procedure:
BEGIN DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE( capture_name => 'strm01_capture', attribute_name => 'tx_name', include => true); END; /
To instruct a capture process named strm01_capture
to exclude the transaction name from each captured message, run the following procedure:
BEGIN DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE( capture_name => 'strm01_capture', attribute_name => 'tx_name', include => false); END; /
You run the DROP_CAPTURE
procedure in the DBMS_CAPTURE_ADM
package to drop an existing capture process. For example, the following procedure drops a capture process named strm02_capture
:
BEGIN DBMS_CAPTURE_ADM.DROP_CAPTURE( capture_name => 'strm02_capture', drop_unused_rule_sets => true); END; /
Because the drop_unused_rule_sets
parameter is set to true
, this procedure also drops any rule sets used by the strm02_capture
capture process, unless a rule set is used by another Streams client. If the drop_unused_rule_sets
parameter is set to true
, then both the positive rule set and negative rule set for the capture process might be dropped. If this procedure drops a rule set, then it also drops any rules in the rule set that are not in another rule set.
Note:
The status of a capture process must beDISABLED
or ABORTED
before it can be dropped. You cannot drop an ENABLED
capture process.