| Oracle®
Database Data Warehousing Guide 10g Release 1 (10.1) Part Number B10736-01 |
|
|
View PDF |
| Oracle®
Database Data Warehousing Guide 10g Release 1 (10.1) Part Number B10736-01 |
|
|
View PDF |
Change Data Capture efficiently identifies and captur es data that has been added to, updated in, or removed from, Oracle relational tables and makes this change data available for use by applications or individuals. Change Data Capture is provided as a database component beginning with Oracle9i.
This c hapter describes Change Data Capture in the following sections:
See PL/SQL Packages and Types Reference for reference information about the Change Data Capture publish and subscribe PL/SQL packages.
Often, data wa rehousing involves the extraction and transportation of relational data from one or more production databases into a data warehouse f or analysis. Change Data Capture quickly identifies and processes only the data that has changed and makes the change data available for further use.
Prior to the introduction of Change Data Capture, there we re a number of ways that users could capture change data, including table differencing and change-value selection.
Table differencing involves transporting a copy of an entire table from the source (prod
uction) database to the staging database (where the change data is captured), where an older version of the table already exists. Usi
ng the SQL MINUS operator, you can obtain the inserted and new versions of updated rows with the following query:
SELECT * FROM new_version MINUS SELECT * FROM old_version;
Moreover, you can obtain the deleted rows and old versions of updated rows with the following query:
SELECT * FROM old_version MINUS SELECT * FROM new_version;
However, there are several problems with this method:
It requires that the new version of the entire table be transported to the staging database, not just the change data, thereby greatly increasing tran sport costs.
The computational cost of performing the two MINUS operations on the staging d
atabase can be very high.
Table differencing cannot capture data changes that have reverted to their old values. For example, suppose the price of a product changes several times between the old version and the new version of the product 's table. If the price in the new version ends up being the same as the old, table differencing cannot detect that the price has fluc tuated. Moreover, any intermediate price values between the old and new versions of the product's table cannot be captured using tabl e differencing.
There is no way to determine which changes were made as part of the same transaction. Fo r example, suppose a sales manager creates a special discount to close a deal. The fact that the creation of the discount and the cre ation of the sale occurred as part of the same transaction cannot be captured, unless the source database is specifically designed to do so.
Change-value selection involves capturing the data o
n the source database by selecting the new and changed data from the source tables based on the value of a specific column. For examp
le, suppose the source table has a LAST_UPDATE_DATE column. To capture changes, you base your selection from the source
table on the LAST_UPDATE_DATE column value.
However, there are also several problems with this method:
The overhead of capturing the change data must be borne on the source database, and you must run potentially expen sive queries against the source table on the source database. The need for these queries may force you to add indexes that would othe rwise be unneeded. There is no way to offload this overhead to the staging database.
This method is no b etter at capturing intermediate values than the table differencing method. If the price in the product's table fluctuates, you will n ot be able to capture all the intermediate values, or even tell if the price had changed, if the ending value is the same as it was t he last time that you captured change data.
This method is also no better than the table differencing me thod at capturing which data changes were made together in the same transaction. If you need to capture information concerning which changes occurred together in the same transaction, you must include specific designs for this purpose in your source database.
The granularity of the change-value column may not be fine enough to uniquely identify the new and changed row s. For example, suppose the following:
You capture data changes using change-value selection on a date
column such as LAST_UPDATE_DATE.
The capture happens at a particular instant in time, 14 -FEB-2003 17:10:00.
Additional updates occur to the table during the same second that you performed yo ur capture.
When you next capture data changes, you will select rows with a LAST_UPDATE_DATE strictly a
fter 14-FEB-2003 17:10:00, and thereby miss the changes that occurred during the remainder of that second.
To use change-value selection, you either have to accept that anomaly, add an artificial change-value column with the granularity you need, or lock out changes to the source table during the capture process, thereby further burdening the performance of the source database.
You have to design your source database in advance with this capture mechanism in mind – all tables from whi ch you wish to capture change data must have a change-value column. If you want to build a data warehouse with data sources from lega cy systems, those legacy systems may not supply the necessary change-value columns you need.
Change Data Capture doe
s not depend on expensive and cumbersome table differencing or change-value selection mechanisms. Instead, it captures the change dat
a resulting from INSERT, UPDATE, and DELETE operations made to user tables. The change data is
then stored in a relational table called a change table, and the change data is made available to applications or individuals in a c
ontrolled way.
Change Data Capture can capture an d publish committed change data in either of the following modes:
Synchronous
Ch
ange data is captured immediately, as each SQL statement that performs a data manipulation language (DML) operation (INSERT, UPDATE, or DELETE) is made, by using triggers on the source database. In this mode, change data is cap
tured as part of the transaction modifying the source table. Synchronous Change Data Capture is available with Oracle Standard Editio
n and Enterprise Edition.
Asynchronous
Change data is captured after a SQL state ment that performs a DML operation is committed, by taking advantage of the data sent to the redo log files. In this mode, change dat a is not captured as part of the transaction that is modifying the source table, and therefore has no effect on that transaction. Asy nchronous Change Data Capture is available with Oracle Enterprise Edition only.
Asynchronous Change Data Capture is built on, and provides a relational interface to, Oracle Streams. See Orac le Streams Concepts and Administration for information on Oracle Streams.
The following list describes the advantages of capturing change data with Change Data Capture:
Com pleteness
Change Data Capture can capture all effects of INSERT, UPDATE, and DELETE ope
rations, including data values before and after UPDATE operations.
Performance
Async hronous Change Data Capture can be configured to have minimal performance impact on the source database.
Interface
Change Data Capture includes the PL/SQL DBMS_CDC_PUBLISH and DBMS_CDC_SUBSCRIBE packages,
which provide easy-to-use publish and subscribe interfaces.
Cost
Change Data Capture reduces ove rhead cost because it simplifies the extraction of change data from the database and is part of Oracle9i and later databases .
A Change Data Capture system is based on the interaction of a publisher and subscribers to capture and distribute change data, as described in the next section.
Most Change Data Capt ure systems have one person who captures and publishes change data; this person is the publisher. There can be multiple applications or individuals that access the change data ; these applications and individuals are the subscriber s. Change Data Capture provides PL/SQL packages to accomplish the publish and subscribe tasks.
The following sect ions describe the roles of the publisher and subscriber in detail. Subsequent sections describe change sources, more about modes of C hange Data Capture, and change tables.
This is the production database that contains the data of interest. Its associated tables are referred to as the source tables.
This is the database where the c hange data capture takes place. Depending on the capture mode that the publisher uses, the staging database can be the same as, or di fferent from, the source database. The following Change Data Capture objects reside on the staging database:
Change table
A change table is a relational table that contains change data for a single source table. To subscribers, a change table is known as a publication.
Change set< /p>
A change set is a set of change data that is guaranteed to be transactiona lly consistent. It contains one or more change tables.
Change source
The change source is a logical representation of the source database. It contains one or more change se ts.
The publisher performs these tasks:
Uses the Oracle-supplied package, DBMS_CDC_PUBLISH, to set up the system to capture change data from the source tables of inter
est.
Allows subscribers to have controlled access to the change data in the change tables by using the S
QL GRANT and REVOKE statements to grant and revoke the SELECT privilege on change tables for u
sers and roles. (Keep in mind, however, that subscribers use views, not change tables directly, to access change data.)
In Figure 16-1, the publisher determines that subscribers are inte
rested in viewing change data from the HQ source database. In particular, subscribers are interested in change data from
the SH.SALES and SH.PROMOTIONS source tables.
The publisher creates a change source HQ_SRC on the . The DW staging database, a change set, SH_SET, and two change tables: sales_ct and sales_ct change table contains all the columns from the source table, SH.SALES. For
the promo_ct change table, however, the publisher has decided to exclude the PROMO_COST column.
Figure 16-1 Publisher Components in a Change Data Capture System
The subscribers are consumers of the publis hed change data. A subscriber performs the following tasks:
Uses the Oracle supplied package, DBMS_CDC_SUBSCRIBE, to:
Create subscriptions
A subscription controls access to the change data from one or more source tables of interest within a single change set. A subscription contains one or more subscriber views.
< a id="sthref852" name="sthref852">A subscriber view is a view that specifies the change data from a specific pub lication in a subscription. The subscriber is restricted to seeing change data that the publisher has published and has granted the s ubscriber access to use. See "Subscribing to Change Data" for more information on choosing a method for speci fying a subscriber view.
Notify Change Data Capture when ready to receive a set of change data
A subscription window defines the time ran ge of rows in a publication that the subscriber can currently see in subscriber views. The oldest row in the window is called the low boundary; the newest row in the window is called the high boundary. Each subscripti on has its own subscription window that applies to all of its subscriber views.
Notify Change Data Cap ture when finished with a set of change data
Uses SELECT statements to retrieve change data from the subscriber views.
In Figure 16-2, the subscriber i s interested in a subset of columns that the publisher (in Figure 16-1) has published. Note that the publ ications shown in Figure 16-2, are represented as change tables in Figure 1 6-1; this reflects the different terminology used by subscribers and publishers, respectively.
The subscriber creates a su
bscription, sales_promos_list and two subscriber views (spl_sales and spl_promos) on the SH_SET change set on the DW staging database. Within each subscriber view, the subscriber includes a subset of the columns th
at were made available by the publisher. Note that because the publisher did not create a change table that includes the PROMO_
COST column, there is no way for the subscriber to view change data for that column.
Figure 16-2 Sub scriber Components in a Change Data Capture System
Change Data Capture pr ovides the following benefits for subscribers:
Guarantees that each subscriber sees all the changes
< /li>Keeps track of multiple subscribers and gives each subscriber shared access to change data
Handles all the storage management by automatically removing data from change tables when it is no longer required by any of the subscribers
|
Note: Oracle provides the previously listed benefits only when the subscriber accesses change data through a subscriber view. |
Change Data Capture provides synchronous and asyn chronous modes for capturing change data. The following sections summarize how each mode of Change Data Capture is performed, and the change source associated with each mode of Change Data Capture.
This mode uses triggers on the source database to capture change data. It has no latency because the ch ange data is captured continuously and in real time on the source database. The change tables are populated when DML operations on th e source table are committed.
While the synchronous mode of Change Data Capture adds overhead to the source database at captur e time, this mode can reduce costs (as compared to attempting to extract change data using table differencing or change-value section ) by simplifying the extraction of change data.
There is a single, predefined synchronous change source, SYNC_SOURCE, that represents the source database. This is the o
nly synchronous change source. It cannot be altered or dropped.
Change tables for this mode of Change Data Capture must reside locally in the source database.
Figure 16-3 illustrates the synchronous configuration. Triggers execu
ted after DML operations occur on the source tables populate the change tables in the change sets within the SYNC_SOURCE
change source.
Figure 16-3 Synchronous Change Data Capture Configuration
This mode captures change data after the changes have been committed to the source database by usi ng the database redo log files.
The asynchronous mode of Change Data Capture is dependent on the level of supplemental logging enabled at the source database. Supplemental logging a dds redo logging overhead at the source database, so it must be carefully balanced with the needs of the applications or individuals using Change Data Capture. See "Asynchronous Change Data Capture and Supplemental Logging" for information on supplemental logging.
There are two methods of capturing change data asynchronously, HotLog and AutoLog, as described in the following sections:
Change data is captu red from the online redo log file on the source database. There is a brief latency between the act of committing source table transac tions and the arrival of change data.
There is a single, predefined HotLog change source, <
/a>HOTLOG_SOURCE, that represents the current redo log files of the source databa
se. This is the only HotLog change source. It cannot be altered or dropped.
Change tables for this mode of Change Data Capture must reside locally in the source database.
Figure 16-4, illustrates the asynchronous HotLog configur
ation. The Logwriter Process (LGWR) records committed transactions in the online redo log files on the source database. Change Data C
apture uses Oracle Streams processes to automatically populate the change tables in the change sets within the HOTLOG_SOURCE change source as newly committed transactions arrive.
Change data is captured from a set of redo log files managed by log transport services. Log transport services control the automated transfer of redo log files from the source database to the staging database. Using database initialization parameters (described in "Initialization Parameters for Asynchronous AutoLog Publishing"), the publisher configures log transport services to copy the redo log files from the source database system to the staging database system and to automatically register the redo log files. C hange sets are populated automatically as new redo log files arrive. The degree of latency depends on frequency of redo log switches on the source database.
There is no predefined AutoLog change source. The publisher pro vides information about the source database to create an AutoLog change source. See "Performing Asynchronous Auto Log Publishing" for details.
Change sets for this mode of Change Data Capture can be remote from or local to the source da tabase. Typically, they are remote.
Figure 16-5 shows a typical Change Data Capture asynchronous AutoL
og configuration in which, when the log switches on the source database, archiver processes archive the redo log file on the source d
atabase to the destination specified by the LOG_ARCHIVE_DEST_1 parameter and copy the redo log file to the staging datab
ase as specified by the LOG_ARCHIVE_DEST_2 parameter. (Although the image presents these parameters as LOG_ARCHIVE
_DEST_1 and LOG_ARCHIVE_DEST_2, the integer value in these parameter strings can be any value between 1 and 10.)<
/p>
Note that the archiver processes use Oracle Net to send redo data over the network to the remote file server (RFS) process. Tr ansmitting redo log files to a remote destination requires uninterrupted connectivity through Oracle Net.
On the staging datab
ase, the RFS process writes the redo data to the copied log files in the location specified by the value of the TEMPLATE
attribute in the LOG_ARCHIVE_DEST_2 parameter (specified in the source database initialization parameter file). Then, C
hange Data Capture uses Oracle Streams downstream capture to populate the change tables in the change sets within the AutoLog change
source.
Figure 16-5 Asynchronous AutoLog Change Data Capture Configuration
A change set is a logical grouping of change data that is guaranteed to be transactionally consistent and t hat can be managed as a unit. A change set is a member of one (and only one) change source. A change source can contain one or more c hange sets. Conceptually, a change set shares the same mode as its change source. For example, an AutoLog change set is a change set contained in an AutoLog change source.
When a publisher includes two or more change tables in the same change set, subscribers can perform join operations across the tables represented within the change set and be assured of transactional consistency.
There are three modes of change sets, as follow:
Synchronous
<
/a>New change data arrives automatically as DML operations on the source tables are committed.
Publishers can define new change sets in the predefined SYNC_SOURCE change source or use the predefined change set, SYNC_SET. The SYNC_SET change set cannot be altered or dropped.
Asynchronous HotLog
New change data arrives automatically, on a transaction-by-tr
ansaction basis from the current online redo log file. Publishers define change sets in the predefined HOTLOG_SOURCE change source.
Asynchronous A utoLog
New change data arrives automatically, on a log-by-log basis, as log transport services makes redo log files available. Publishers define change sets in publisher-defined AutoLog change sources.
Publishers can purge unneeded change data from change ta bles at the change set level to keep the change tables in the change set from growing larger indefinitely.
See "Purging Change Tables of Unneeded Data" for more information on purging change data.
Table 16-1 summarizes the valid combinations of change sources and change sets and indicates whether each is predefined or publisher-defined. In addition, Table 16-1 indicates whether the change source represent s a local or remote source database, and whether the change source is used for synchronous or asynchronous Change Data Capture.
< div class="tblformal">Table 16-1 Summary of Change Sources and Change Sets
| Mode | Change Source | Source Database Represented | Associated Change Sets |
|---|---|---|---|
| Synchronous | Predefined SYNC_SOURCE |
Local | Predefined SYNC_SET and publisher-defined |
| Asynchronous HotLog | Predefined |
Local | Publish er-defined |
| Asynchronous AutoLog | Publisher-defined | Remote or local | Publisher-defined |
|
Note: Although the AutoLog source database an d AutoLog staging database can be the same, this arrangement is rarely used. AutoLog examples in this chapter assume that the source and staging databases are different. |
A given change table contains the change data resulting from DML operations performed on a given source table. A change table consists of two things: the change data itself, which is stored in a database table, ; and the system metadata necessary to maintain the change table, which includes control column s.
The publisher specifies the source columns that are to be included in the change table. Typically, for a change table to co
ntain useful data, the publisher needs to include the primary key column in the change table along with any other columns of interest
to subscribers. For example, suppose subscribers are interested in changes that occur to the UNIT_COST and the UN
IT_PRICE columns in the SH.COSTS table. If the publisher does not include the PROD_ID column in the
change table, subscribers will know only that the unit cost and unit price of some products have changed, but will be unable to deter
mine for which products these changes have occurred.
There are optional and required control columns. The required control col umns are always included in a change table; the optional ones are included if specified by the publisher when creating the change tab le. Control columns are managed by Change Data Capture. See "Understanding Change Table Control Columns" and "Understanding TARGET_COLMAP$ and SOURCE_COLMAP$ Values" for detailed information on control columns.
Information about the Change Data Capture environment is provided in the static data dictionary
views described in Table 16-2 and Table 16-3. Table 16-2 list
s the views that are intended for use by publishers; the user must have the SELECT_CATAL
OG_ROLE privilege to access the views listed in this table. Table 16-3 lists the views that are intend
ed for use by subscribers. Table 16-3 includes views with the prefixes ALL and USER
. These prefixes have the following general meanings:
A view with the ALL prefix allows the
user to display all the information accessible to the user, including information from the current user's schema as well as informat
ion from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.
A view with the USER prefix allows the user to display all the information from the schema of the us
er issuing the query without the use of additional special privileges or roles.
Table 16-2 Views Intended for Use by Change Data Capture Publishers< /font>
| View Name | Description |
|---|---|
CHANGE_SOURCES |
Describes existing change sourc es. |
CHANGE_SETS |
Describes existing change sets. |
CHANGE_TABLES |
Describes existing change tables. |
DBA_SOURCE_TABLES |
Describes all existing source tables in the database. |
DBA_PUBLISHED_COLUMNS |
Describe s all published columns of source tables in the database. |
DBA_SUBSCRIPTIONS |
Describes all subscriptions. | DBA_SUBSCRIBED_TABLES |
Describes all source tables to which any subscriber has subscribed. |
DBA_SUBSCRIBED_COLUMNS |
Describes the columns of source tables to which any subscriber has subscribed. |
| View Name | Description |
|---|---|
ALL_SOURCE_TABLES |
Describes all ex isting source tables accessible to the current user. |
USER_SOURCE_TABLES |
Describes all existing source tables owned b y the current user. |
ALL_PUBLISHED_COLU
MNS |
Describes all published columns of source tables accessible to the current user. |
USER_PUBLISHED_COLUMNS
| Describes all published columns of source tables owned by the current user. |
ALL_SUBSCRIPTIONS |
Describes all subscriptions accessible to the current user. |
USER_SUBSCRIPTIONS |
Describes al l the subscriptions owned by the current user. |
ALL_SUBSCRIBED_TABLES |
Describes the source tables to which any subscr iption accessible to the current user has subscribed. |
USER_SUBSCRIBED_TABLES |
Describes the source tables to which th e current user has subscribed. |
ALL_SU
BSCRIBED_COLUMNS |
Describes the columns of source tables to which any subscrip tion accessible to the current user has subscribed. |
USER_SUBSCRIBED_COLUMNS |
Describes the columns of source tables to which the current user has subscribed. |
See Oracle Database Reference for complete informatio n about these views.
This section describes the tasks the publisher should perform before starting to publish, informa tion on creating the publisher, information on selecting a mode in which to capture change data, and instructions on setting database initialization parameters required by Change Data Capture.
The publisher should do the following before performing the actual steps for publishing:
Gather requirements from the subscribers.< /p>
Determine which source database contains the relevant source tables.
Choose the capture mode: synchronous, asynchronous HotLog, or asynchronous AutoLog, as described in "Determining the Mo de in Which to Capture Data".
Ensure that the source and staging database DBAs have set database ini tialization parameters, as described in "Setting Initialization Parameters for Change Data Capture Publishing" and "Publishing Change Data".
Typically, a DBA creates a user to serve as a publisher for Change Data Ca pture. The following sections describe the tasks involved.
On the staging database, the publisher must be granted the privileges and roles in the following list:
In addition, for asynchronous HotLog and AutoLog publishing, the publisher must:
Be granted the CREATE SEQUENCE privilege
Be th
e GRANTEE specified in a DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE() subprogram issued by the staging databas
e DBA
In other words, for asynchronous publishing, the publisher should be configured as an Oracle Streams administr ator. See Oracle Streams Concepts and Administration for informa tion on configuring an Oracle Streams administrator.
When creati ng the publisher account, the DBA should also consider specifying a default tablespace for the publisher in which he or she can creat e change tables. Otherwise, Oracle recommends that the publisher always specify a tablespace that he or she owns when creating a chan ge table. To create a tablespace, the publisher will need the following privileges:
For asynchronous AutoLog publishing only, the REM
OTE_LOGIN_PASSWORDFILE database initialization parameter must be set to SHARED on both the source and staging dat
abases, and a password file must exist on both the machines hosting the source and staging databases. The DBA uses the orapwd utility to create password files. For example:
ORAPWD FILE=orapw PASSWORD=mypassword ENTRIES=10
This example creates a password file with 10 entries, where the password for SYS is mypassword. For redo log file transmission to succeed, the password for the SYS user account must be identical for the
source and staging databases.
Three factors influence the decision on the m ode in which to capture change data:
Whether or not the staging database is remote from the source datab ase
Tolerance for latency between changes made on the source database and changes captured by Change Dat a Capture
Performance impact on the source database transactions and overall database performance
Table 16-4 summarizes the factors that might influence the mode decision.
Table 16-4 Factors Influencing Choice of Change Data Capture Mode
Initialization parameters must be set on the source or staging database, or both, for Change Data Capture to succeed. Which parameters to set depend on the mode in which Change Data Capture is publishing change data, and on whether the parameters are being set on the source or staging database.
The following sections describe the database initialization p arameter settings for each mode of Change Data Capture. Sometimes you are directed to add a value to a current setting. See "Determining the Current Setting of an Initialization Parameter" to determine the current setting for a parameter if t hat parameter is not explicitly specified in the initialization parameter file. See Oracle Database Reference and Oracl e Data Guard Concepts and Administration for more information about these database initialization parameters.
Set the
JAVA_POOL_SIZE parameter as follows:
JAVA_POOL_SIZE = 50000000
Table 16-5 lists the source database initialization parameters and their recommended settings for Asychronous HotLog publishing.
Table 16-5 Source Database Initialization Parameters for Asy nchronous HotLog Publishing
| Rec ommended Value | |
|---|---|
COMPATIBLE |
10.1.0 |
JAVA_POOL_SIZE |
50000000 |
JOB_QUEUE_PROCESSES |
2 |
PARALLEL_
MAX_SERVERS |
(current value) + (5 * (the number of change sets planned)) |
PROCESSES |
(current value) + (7 * (the number of change sets planned)) |
SESSIONS |
(current value) + (2 * (th e number of change sets planned)) |
STRE
AMS_POOL_SIZE |
See Oracle Streams Concepts and Administration for information on how
the |
UNDO_RETENTION |
3600 |
Table 16-6< /a> lists the database initialization parameters and their recommended settings for the asynch ronous AutoLog publishing source database and Table 16-7 lists the database initialization parameters and the ir recommended settings for the asynchronous AutoLog pu blishing staging database.
Table 16-6 Source Database Initialization Parameters for Asynchronous Aut oLog Publishing
| Parame ter | Recommended Value |
|---|---|
| 10.1.0 | |
JAVA_POOL_SIZE |
50000000 |
LOG_ARCHIVE_DEST_1Foot 1 |
The directory speci fication on the system hosting the source database where the archived redo log files are to be kept. |
LOG_ARCHIVE_DEST_2Footref 1 |
This parameter must include the SER
VICE, ARCH or LGWR ASYNC, OPTIONAL, NOREGISTER, and REOPEN at
tributes so that log transport services are configured to copy the redo log files from the source database to the staging database. T
hese attributes are set as follows:
|
LOG_ARCHIVE_DEST_STATE_1Footref
;1 |
ENABLE
Indicates that log transport services c an transmit archived redo log files to this destination. |
LOG_ARCHIVE_DEST_STATE_2Footref 1 |
ENABLE
Indicates that log transport services can transmit redo log files to this destination. |
| "arch1_%s_%t_%r.dbf"
Specifies a format template for the default file name when archiving redo log files.Footref < /font>2 The string value (arch1) and the file name extension (.dbf) do not have to be exactly as specified here. |
|
REMOTE_LOGIN_PASSWORDFILE |
SHARED |
LOG_ARCHIVE_DEST_n parameter, there mu
st be a corresponding LOG_ARCHIVE_DEST_STATE_n parameter that specifies the same value for n.Table 16-7 Staging Database Initi alization Parameters for Asynchronous AutoLog Publishing
| Recommended Value | |
|---|---|
COMPATIBLE |
10.1.0 |
GLOBAL_NAMES |
TRUE
|
JAVA_POOL_SIZE |
<
td align="left" headers="r4c1-t11 r1c2-t11">50000000|
JOB_QUEUE_PROCESSES |
2 |
PARALLEL_MAX_SERVERS |
(current value) + (5 * (the number of change sets planned)) |
PROCESSES |
(current value) + (7 * (the number of change sets planned)) |
REMOTE_LOGIN_
PASSWORDFILE |
SHARED |
| (current valu e)+ (2 * (the number of change sets planned)) | |
STREAMS_POOL_SIZE |
See Oracle Streams Concepts and Administration for
information on how the |
UNDO_RETENTION |
3600 |
Sometimes the DBA needs to make adjustments to the initialization parameters by adding a value to the current setting. If a specific parameter is not in the initialization parameter file, the default value (if one exists) is the curren t value. The current settings of all initialization parameters for the database are displayed when the following SQL statement is iss ued:
SQL> SHOW PARAMETERS
The current setting of a particular initialization parameter c
an be displayed, in this case, STREAMS_POOL_SIZE, using a SQL statement such as the following:
SQL> SHOW PARAMETER STREAMS_POOL_SIZE
To ensure that changed initial ization parameter values are retained when the database is restarted:
If the database is using a pfile,
manually update the pfile with any parameter values you change with the SQL ALTER SYSTEM statement.
If the database is using an spfile, then parameter values you change with the SQL ALTER SYSTEM statement are au
tomatically changed in the parameter file.
If the database is using an spfile and a given initialization
parameter is not or cannot be changed dynamically (such as the PROCESS, LOG_ARCHIVE_FORMAT, and REMO
TE_LOGIN_ENABLE parameters), you must change the value with the SQL ALTER SYSTEM statement, and then restart the
database.
If the ORA-04031 error is returned when the DBA attempts to set the JAVA_PO
OL_SIZE dynamically, he or she should place the parameter in the database initialization parameter file and restart the databa
se.
See Oracle Database Administrator's Guide for information on managing initialization parameters using a pfile or an spfile.
In any case, if a parameter is reset dynamically, the new value should also be placed in the initialization parameter file, so that the new value is retained if the data base is restarted.
Asynchronous Change Data Capture uses an Oracle Streams configuration for each HotLog and AutoLog change set. This Streams configuration consists of a Streams capture process and a Streams apply process, with an accompanying queue and queue table. Each Streams configuration uses additional processe s, parallel execution servers, and memory.
Oracle Streams capture and apply processes each have a parallelism parameter that i s used to improve performance. When a publisher first creates a change set, its capture parallelism value and apply parallelism value are each 1. If desired, a publisher can increase one or both of these values using Streams interfaces.
If Oracle Streams capt
ure parallelism and apply parallelism values are increased after the change sets are created, the staging database DBA must adjust in
itialization parameter values accordingly. Example 16-1 and Example 16-2 demonstrate
how to obtain the capture parallelism and apply parallelism values for change set CHICAGO_DAILY. By default, each parall
elism value is 1, so the amount by which a given parall
elism value has been increased is the returned value minus 1.
Example 16-1 Obtaining the Oracle Streams Ca pture Parallelism Value
SELECT cp.value FROM DBA_CAPTURE_PARAMETERS cp, CHANGE_SET S cset WHERE cset.SET_NAME = 'CHICAGO_DAILY' and cset.CAPTURE_NAME = cp.CAPTURE_NAME and cp.PARAMETER = 'PARALLELISM';
Example 16-2 Obtaining the Oracle Streams Apply Parallelism Value
SELECT ap.value FROM DBA_APPLY_PARAMETERS ap, CHANGE_SETS cset WHERE cset.SET_NAME = 'CHI CAGO_DAILY' and cset.APPLY_NAME = ap.APPLY_NAME and ap.parameter = 'PARALLELISM';
Th e staging database DBA must adjust the staging database initialization parameters as described in the following list to accommodate t he parallel execution servers and other processes and memory required for asynchronous Change Data Capture:
PARALLEL_MAX_SERVERS
For each change set for which Oracle Streams capture or apply parallelism values were in creased, increase the value of this parameter by the sum of increased Streams parallelism values.
For example, if the statemen
t in Example 16-1 returns a value of 2, and the statement in Example 16-2 returns a v
alue of 3, then the staging database DBA should increase the value of the PARALLEL_MAX_SERVERS parameter by (2-1) + (3-1
), or 3 for the CHICAGO_DAILY change set. If the Streams capture or apply parallelism values have increased for other ch
ange sets, increases for those change sets must also be made.
PROCESSES
For each cha
nge set for which Oracle Streams capture or apply parallelism values were changed, increase the value of this parameter by the sum of
increased Streams parallelism values. See the previous list item, PARALLEL_MAX_SERVERS, for an example.
STREAMS_POOL_SIZE
For each change set for which Oracle Streams capture or apply parallelism values were changed, increase the value of this parameter by (10MB * (the increased capture parallelism value)) + (1MB * increased apply par allelism value).
For example, if the statement in Example 16-1 returns a value of 2, and the statement
in Example 16-2 returns a value of 3, then the staging database DBA should increase the value of the S
TREAMS_POOL_SIZE parameter by (10 MB * (2-1) + 1MB * (3-1)), or 12MB for the CHICAGO_DAILY change set. If the Ora
cle Streams capture or apply parallelism values have increased for other change sets, increases for those change sets must also be ma
de.
See Oracle Streams Concepts and Administration for more information on Streams capture parallelism and apply parallelism values. See Oracle Database Reference for more information about database initialization parameters.
The following sections provide step-by-step i nstructions on performing the various types of publishing:
For synchronous Change Data Capture, the publisher must use the p
redefined change source, SYNC_SOURCE. The publisher can define new change sets or can use the predefined change set, SYSTEM because triggers will not fire and therefore changes will not be captured.
This example
shows how to create a change set. If the publisher wants to use the predefined SYNC_SET, he or she should skip Step 3 an
d specify SYNC_SET as the change set name in the remaining steps.
This example assumes that the publisher and the source database DBA are two different people.
Step 1 Source Database DBA: Set the JAVA_POOL_SIZE parameter.
The source database DBA sets the database initialization parameters, a s described in "Setting Initialization Parameters for Change Data Capture Publishing".
java_pool_size = 50000000
Step 2 Source Database DBA: Create an d grant privileges to the publisher.
The source database DBA creates a user (for example, cdcpub), to serve as
the Change Data Capture publisher and grants the necessary privileges to the publisher so that he or she can perform the operations
needed to create Change Data Capture change sets and change tables on the source database, as described in "Creat
ing a User to Serve As a Publisher". This example assumes that the tablespace ts_cdcpub has already been created.
CREATE USER cdcpub IDENTIFIED BY cdcpub DEFAULT TABLESPACE ts_cdcpub QUOTA UNLIMITED ON SYSTEM QUOTA UNL IMITED ON SYSAUX; GRANT CREATE SESSION TO cdcpub; GRANT CREATE TABLE TO cdcpub; GRANT CREATE TABLESPACE TO cdcpub; GRANT UNLIMITED TA BLESPACE TO cdcpub; GRANT SELECT_CATALOG_ROLE TO cdcpub; GRANT EXECUTE_CATALOG_ROLE TO cdcpub; GRANT CONNECT, RESOURCE TO cdcpub;Step 3 Staging Database Publisher: Create a change set.
The publisher uses the
DBMS_CDC_PUBLISH.CREATE_CHANGE_SETprocedure on the staging database to create change sets.The following example shows how to create a change set called
CHICAGO_DAILY:BEGIN DBMS_CDC_PUBLISH.CREATE_CHANGE_SET( change_set_name => 'CHICAGO_DAILY', description => 'Change set for j ob history info', change_source_name => 'SYNC_SOURCE'); END; /The change set captures changes from the predefine d change source
SYNC_SOURCE. Becausebegin_dateandend_dateparameters cannot be specified fo r synchronous change sets, capture begins at the earliest available change data and continues capturing change data indefinitely.Step 4 Staging Database Publisher: Creat e a change table.
The publisher uses the
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLEprocedure to create change tables.The publisher can set the
options_stringfield of theDBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE< /code> procedure to have more control over the physical properties and tablespace properties of the change table. Theoptions_s tringfield can contain any option, except partitioning, that is available in theCREATE TABLEstatement.The following example creates a change table that captures changes that occur on a source table. The example uses the sample table
HR.JOB_HISTORY as the source table. It assumes that the publisher has already created the TS_CHICAGO_DAILYta blespace.BEGIN DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(owner => 'cdcpub', change_table_name => 'jobhist_ct', change_set_name => 'CHICAGO_DAILY', source_schema => 'HR', source_ table => 'JOB_HISTORY', column_type_list => 'EMPLOYEE_ID NUMBER(6),START_DATE DATE, END_DATE DATE,J OB_ID VARCHAR2(10), DEPARTMENT_ID NUMBER(4)', capture_values => 'both', rs_id => 'y', row_id => 'n', user_id => 'n', timestamp => 'n', object_id => 'n', source_colmap => 'y', target_colmap => 'y', options_string => 'TABLESPACE TS_CHICAGO_DAILY'); END; /This statement creates a change table named
job hist_ctwithin the change setCHICAGO_DAILY. Thecolumn_type_listparameter identifies the columns c aptured by the change table. Thesource_schemaandsource_tableparameters identify the schema and source t able that reside in the source database.The
capture_valuessetting in the example indicates that for update oper ations, the change data will contain two separate rows for each row that changed: one row will contain the row values before the upda te occurred, and the other row will contain the row values after the update occurred.Step 5 Staging Database Publisher: Grant access to subscribers.
The publisher controls subscriber ac cess to change data by granting and revoking the
SELECTprivilege on change tables for users and roles. The publisher gr ants access to specific change tables. Without this step, a subscriber cannot access any change data. This example assumes that usersubscriber1already exists.GRANT SELECT ON cdcpub.jobhist_ct TO subscriber1;The Change Data Capture synchronous system is now ready for subscriber1 to create subscriptions.
Change Data Capture uses Oracle Streams local capture to perform asynchronous HotLog publishing. (See Oracle Streams Concepts and Administration for information on Streams local capture.)
For a
synchronous HotLog Change Data Capture, the publisher must use the predefined change source, HOTLOG_SOURCE, and must cre
ate the change sets and the change tables that will contain the changes. The staging database is always the source database. This exa
mple assumes that the publisher and the source database DBA are two different people.
The following steps set up redo logging, Oracle Streams, and Change Data Capture for asynchronous HotLog publishing:
Step 1 Source Database DBA: Set the database initialization parameters.
The source database DBA sets t
he database initialization parameters, as described in "Setting Initialization Parameters for Change Data Capture
Publishing". In this example, one change set will be defined and the current value of the STREAMS_POOL_SIZE paramet
er is 50 MB or greater.
compatible = 10.1.0 java_pool_size = 50000000; job_queue_processes = 2
parallel_max_servers = <current value> + 5 processes = <current value> + 7 se ssions = <current value> + 2 streams_pool_size = <current value> + 21 MB undo_retention = 3600
The source
database DBA performs the following three tasks. The second is required. The first and third are optional, but recommended. It is ass
umed that the database is currently running in ARCHIVELOG mode.
Place the database i
nto FORCE LOGGING logging mode to protect against unlogged direct write operations in the source database t
hat cannot be captured by asynchronous Change Data Capture:
ALTER DATABASE FORCE LOGGING;
Enable supplemental logging. Supplemental logging places additional column data into a redo log file whenever an UPDAT
E operation is performed. Minimally, database-level minimal supplemental logging must be enabled for any Change Data Capture s
ource database:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Create an unconditional log group on all columns to be captured in the source table. Source table columns that are unchanged and are not in an unconditional log group, will be null in the change table, instead of reflecting their actual source table values. (This example captures rows in the HR.JOB_HISTORY table only. The source database DBA would repeat this step for each source table for which change tables will be c reated.)
ALTER TABLE HR.JOB_HISTORY ADD SUPPLEMENTAL LOG GROUP log_group_jobhist (EMPLOYEE_ID, START_ DATE, END_DATE, JOB_ID, DEPARTMENT_ID) ALWAYS;
If you intend to capture all the column values in a row whenever a column
in that row is updated, you can use the following statement instead of listing each column one-by-one in the ALTER TABLE statement. However, do not use this form of the ALTER TABLE statement if all columns are not
needed. Logging all columns incurs more overhead than logging selected columns.
ALTER TABLE HR.JOB_HIST ORY ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
See Ora
cle Database Administrator's Guide for information about running a database in ARCHIVELOG mode. See "Asynchronous Change Data Capture and Supplemental Logging" for more information on enabling supplemental logging.
Step 3 Source Database DBA: Create and grant privileges to the p ublisher.
The source database DBA creates a user, (for example, cdcpub), to serve as the Change D
ata Capture publisher and grants the necessary privileges to the publisher so that he or she can perform the underlying Oracle Stream
s operations needed to create Change Data Capture change sets, and change tables on the source database, as described in "Creating a User to Serve As a Publisher". This example assumes that the ts_cdcpub tablespace has already be
en created. For example:
CREATE USER cdcpub IDENTIFIED BY cdcpub DEFAULT TABLESPACE ts_cdcpub QUOTA UNL IMITED ON SYSTEM QUOTA UNLIMITED ON SYSAUX; GRANT CREATE SESSION TO cdcpub; GRANT CREATE TABLE TO cdcpub; GRANT CREATE TABLESPACE TO cdcpub; GRANT UNLIMITED TABLESPACE TO cdcpub; GRANT SELECT_CATALOG_ROLE TO cdcpub; GRANT EXECUTE_CATALOG_ROLE TO cdcpub; GRANT CREAT E SEQUENCE TO cdcpub; GRANT CONNECT, RESOURCE, DBA TO cdcpub; EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE => 'cdcpub' );
Note that for HotLog Change Data Capture, the source database and the staging database are the same database.
< strong>Step 4 Source Database DBA: Prepare the source tables.
The so urce database DBA must prepare the source tables on the source database for asynchronous Change Data Capture by instantiating each so urce table so that the underlying Oracle Streams environment records the information it needs to capture each source table's changes. The source table structure and the column datatypes must be supported by Change Data Capture. See "Datatypes and Table Structures Supported for Asynchronous Change Data Capture" for more information.
BEGIN DBMS_ CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME => 'hr.job_history'); END; /
Step 5 Staging Database Publisher: Create change sets.
The publisher uses the DBMS_CDC_PUBLIS
H.CREATE_CHANGE_SET procedure on the staging database to create change sets. Note that when Change Data Capture creates a chan
ge set, its associated Oracle Streams capture and apply processes are also created (but not started).
The following example cr
eates a change set called CHICAGO_DAILY that captures changes starting today, and stops capturing change data 5 days fro
m now.
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
change_set_name => 'CHICAGO_DAILY',
description => 'Change set for job history info',
change_source_name => 'HOTLOG_SOURCE',
stop_on_ddl =>
'y',
begin_date => sysdate,
end_date => sysdate+5);
END;
/
The change set captures changes from the p
redefined HOTLOG_SOURCE change source.
Step 6 Staging Database Publisher: Create the change tables that will contain the changes to the source tables.
The publisher uses the
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE procedure on the staging database to create change tables.
The publisher cr eates one or more change tables for each source table to be published, specifies which columns should be included, and specifies the combination of before and after images of the change data to capture.
The following example creates a change table on the stag
ing database that captures changes made to a source table on the source database. The example uses the sample table HR.JOB_HIST
ORY as the source table.
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
owner => '
cdcpub',
change_table_name => 'job_hist_ct',
change_set_name => 'CHICAGO_DAILY',
source_schema => 'HR',
source_table => 'JOB_HISTORY',
column_type_list => 'EMPLOYEE_ID NUMBER(6),START_DATE DATE,END_DATE DATE,
JOB_ID VARCHAR(10), DEPARTMENT_ID NUMBER(4)',
capture_values => 'both',
rs_id => 'y',
row_id => 'n',
user_id => 'n',
timestamp => 'n',
object_id => 'n',
source_colmap => 'n',
target_colmap
=> 'y',
options_string => 'TABLESPACE TS_CHICAGO_DAILY');
END;
/
This statement creates a change table named
job_history_ct within change set CHICAGO_DAILY. The column_type_list parameter identifies the
columns to be captured by the change table. The source_schema and source_table parameters identify the sch
ema and source table that reside on the source database.
The capture_values setting in this statement indicates t
hat for update operations, the change data will contain two separate rows for each row that changed: one row will contain the row val
ues before the update occurred and the other row will contain the row values after the update occurred.
The options_stri
ng parameter in this statement specifies a tablespace for the change table. (This example assumes that the publisher previousl
y created the TS_CHICAGO_DAILY tablespace.)
Step 7 Staging Data base Publisher: Enable the change set.
Because asynchronous change sets are always disabled when they are crea ted, the publisher must alter the change set to enable it. The Oracle Streams capture and apply processes are started when the change set is enabled.
BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'CHICAGO_DAILY
',
enable_capture => 'y');
END;
/
Step 8 Staging Database Publi sher: Grant access to subscribers.
The publisher controls subscriber access to change data by granting and rev
oking the SELECT privilege on change tables for users and roles. The publisher grants access to specific change tables.
Without this step, a subscriber cannot access change data. This example assumes that user subscriber1 already exists.
GRANT SELECT ON cdcpub.job_hist_ct TO subscriber1;
The Change Data Capture Asynchronous HotLo g system is now ready for subscriber1 to create subscriptions.
Change Data Capture uses Oracle Streams downstream capture to perform asynchronous AutoLog publishing. The Change Data Capture staging data base is considered a downstream database in the Streams environment. See Oracle Streams Concepts and Administration for information on Streams downstream capture.
For asynchronous A utoLog Change Data Capture, the publisher creates new change sources, as well as the change sets and the change tables that will cont ain the changes that are made to individual source tables. For AutoLog Change Data Capture, the staging database is usually remote fr om the source database.
Steps must be performed on both the source database and the staging database to set up redo logging, S treams, and Change Data Capture for asynchronous AutoLog publishing. Because the source database and staging database are usually on separate systems, this example assumes that the source database DBA, the staging database DBA, and the publisher are different people .
Step 1 Source Database DBA: Prepare to copy redo log files from the source database.
The source database DBA and the staging database DBA must set up log transport services to c opy redo log files from the source database to the staging database and to prepare the staging database to receive these redo log fil es, as follows:
The source database DBA configures Oracle Net so that the source database can com municate with the staging database. (See Oracle Net Services Ad ministrator's Guide for information about Oracle Net).
The source database DBA sets the database initializa
tion parameters on the source database as described in "Setting Initialization Parameters for Change Data Capture
Publishing". In the following code example stagingdb is the network name of the staging database:
compatible = 10.1.0
java_pool_size = 50000000
log_archive_dest_1="location=/oracle/dbs mandatory reopen=5"
log_archive_d
est_2 = "service=stagingdb arch optional noregister reopen=5
template = /usr/oracle/dbs/arch1_%s_%t_%r.dbf"
lo
g_archive_dest_state_1 = enable
log_archive_dest_state_2 = enable
log_archive_format="arch1_%s_%t_%r.dbf"
remote_login_passwordfile=s
hared
See Oracle Data Guard Concepts and Administrat ion for information on log transport services.
Ste p 2 Staging Database DBA: Set the database initialization parameters.
The staging database DBA sets the databa
se initialization parameters on the staging database, as described in "Setting Initialization Parameters for Chan
ge Data Capture Publishing". In this example, one change set will be defined and the current value for the STREAMS_POOL_SIZ
E parameter is 50 MB or greater:
compatible = 10.1.0 global_names = true java_pool_size = 500000 00 job_queue_processes = 2 parallel_max_servers = <current_value> + 5 processes = <current_value> + 7 remote_login_passwo rdfile = shared sessions = <current value> + 2 streams_pool_size = <current_value> + 21 MB undo_retention = 3600
Step 3 Source Database DBA: Alter the source database.
The source database DBA performs the following three tasks. The second is required. The first and third are optional, but recomm
ended. It is assumed that the database is currently running in ARCHIVELOG mode.
Plac
e the database into FORCE LOGGING logging mode to protect against unlogged direct writes in the source data
base that cannot be captured by asynchronous Change Data Capture:
ALTER DATABASE FORCE LOGGING;
Enable supplemental logging. Supplemental logging places additional column data into a redo log file whenever an updat e operation is performed.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Create an unc
onditional log group on all columns to be captured in the source table. Source table columns that are unchanged and are not in an unc
onditional log group, will be null in the change table, instead of reflecting their actual source table values. (This example capture
s rows in the HR.JOB_HISTORY table only. The source database DBA would repeat this step for each source table for which
change tables will be created).
ALTER TABLE HR.JOB_HISTORY ADD SUPPLEMENTAL LOG GROUP log_group_job_his t (EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID) ALWAYS;
If you intend to capture all the column values in
a row whenever a column in that row is updated, you can use the following statement instead of listing each column one-by-one in the
ALTER TABLE statement. However, do not use this form of the ALTER TABLE statemen
t if all columns are not needed. Logging all columns incurs more overhead than logging selected columns.
ALTER TABLE HR.JOB_HISTORY ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
See Oracle Database Administrator's Guide for information about running a database in ARCHIVELOG mode. See "Asynchronous Change Data Capture and Supplemental Logging" for more information on enabling su
pplemental logging.
Step 4 Staging Database DBA: Create and grant privileges to the publisher.
The staging database DBA creates a user, (for example, cdcpub
), to serve as the Change Data Capture publisher and grants the necessary privileges to the publisher so that he or she can perform t
he underlying Oracle Streams operations needed to create Change Data Capture change sources, change sets, and change tables on the st
aging database, as described in "Creating a User to Serve As a Publisher". For example:.
CREATE USER cdcpub IDENTIFIED BY cdcpub DEFAULT TABLESPACE ts_cdcpub QUOTA UNLIMITED ON SYSTEM QUOTA UNLIMITED ON SYS AUX; GRANT CREATE SESSION TO cdcpub; GRANT CREATE TABLE TO cdcpub; GRANT CREATE TABLESPACE TO cdcpub; GRANT UNLIMITED TABLESPACE TO c dcpub; GRANT SELECT_CATALOG_ROLE TO cdcpub; GRANT EXECUTE_CATALOG_ROLE TO cdcpub; GRANT CONNECT, RESOURCE, DBA TO cdcpub; GRANT CREAT E SEQUENCE TO cdcpub; EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(grantee => 'cdcpub');
Step 5 Source Database DBA: Build the LogMiner data dictionary.
The source database DBA builds a LogMiner data dictionary at the source database so that log transport services can t ransport this data dictionary to the staging database. This LogMiner data dictionary build provides the table definitions as they wer e just prior to beginning to capture change data. Change Data Capture automatically updates the data dictionary with any source table data definition language (DDL) operations that are made during the course of change data capture to ensure that the dictionary is al ways synchronized with the source database tables.
When building the LogMiner data dictionary, the source database DBA should
get the SCN value of the data dictionary build. In Step 8, when the publisher creates a change source, he or she will need to provide
this value as the first_scn parameter.
SET SERVEROUTPUT ON
VARIABLE f_scn NUMBER;
BEGIN
:f_scn := 0;
DBMS_CAPTURE_ADM.BUILD(:f_scn);
DBMS_OUTPUT.PUT_LINE('The first_scn value is ' || :f_scn);
END;
/
The first_s
cn value is 207722
For asynchronous AutoLog publishing to work, it is critical that the source database DBA build the dat a dictionary before the source tables are prepared. The source database DBA must be careful to follow Step 5 and Step 6 in the order they are presented here.
See Oracle Streams Concepts and Administration for more information on the LogMiner data dictionary.
Step 6 Source Database DBA: Prepare the source tables.
The source database DBA must prepare the source table s on the source database for asynchronous Change Data Capture by instantiating each source table so that the underlying Oracle Stream s environment records the information it needs to capture each source table's changes. The source table structure and the column data types must be supported by Change Data Capture. See "Datatypes and Table Structures Supported for Asynchronous Ch ange Data Capture" for more information.
BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
TABLE_NAME => 'hr.job_history');
END;
/
Step 7 Source Database DB A: Get the global name of the source database.
In Step 8, the publisher will need to reference the global name
of the source database. The source database DBA can query the GLOBAL_NAME column in the GLOBAL_NAME view o
n the source database to retrieve this information for the publisher:
SELECT GLOBAL_NAME FROM GLOBAL_NA ME; GLOBAL_NAME ------------------------------------------------ ---------------------------- HQDB
Step 8 Staging Database Publisher: I dentify each change source database and create the change sources.
The publisher uses the DBMS_CDC_PUBLI
SH.CREATE_AUTOLOG_CHANGE_SOURCE procedure on the staging database to create change sources.
The process of managing the
capture system begins with the creation of a change source. A change source describes the source database from which the data will b
e captured, and manages the relationship between the source database and the staging database. A change source always specifies the S
CN of a data dictionary build from the source database as its first_scn parameter.
The publisher gets the SCN of
the data dictionary build and the global database name from the source database DBA (as shown in Step 5 and Step 7, respectively). If
the publisher cannot get the value to use for the first_scn parameter value from the source database DBA, then, with th
e appropriate privileges, he or she can query the V$ARCHIVED_LOG view on the source database to determine the value. Thi
s is described in the DBMS_CDC_PUBLISH chapter of the <
em>PL/SQL Packages and Types Reference.
On the staging database, the publisher creates the AutoLog change source and
specifies the global name as the source_database parameter value and the SCN of the data dictionary build as the f
irst_scn parameter value:
BEGIN
DBMS_CDC_PUBLISH.CREATE_AUTOLOG_CHANGE_SOURCE(
change_
source_name => 'CHICAGO',
description => 'test source',
source_database => 'HQDB',
first_scn => 2077
22);
END;
/
Step 9 Staging Database Publisher: Create change sets.
The publisher uses the DBMS_CDC_PUBLISH.CREATE_CHANGE_SET procedure on the staging database to create
change sets. The publisher can optionally provide beginning and ending dates to indicate where to begin and end the data capture.
Note that when Change Data Capture creates a change set, its associated Oracle Streams capture and apply processes are also crea ted (but not started).
The following example shows how to create a change set called CHICAGO_DAILY that captures
changes starting today, and continues capturing change data indefinitely. (If, at some time in the future, the publisher decides that
he or she wants to stop capturing change data for this change set, he or she should disable the change set and then drop it.)
BEGIN DBMS_CDC_PUBLISH.CREATE_CHANGE_SET( change_set_name => 'CHICAGO_DAILY', description => 'change set for job history info', change_source_name => 'CHICAGO', stop_on_ddl => 'y'); END; /
Step 10 Staging Database Publisher: Create the change tables.
The publisher uses the DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE procedure on the staging database to create change tables
.
The publisher creates one or more change tables for each source table to be published, specifies which columns should be inc luded, and specifies the combination of before and after images of the change data to capture.
The publisher can set the options_string field of the DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE procedure to have more control over the physica
l properties and tablespace properties of the change tables. The options_string field can contain any option available (
except partitioning) on the CREATE TABLE statement. In this example, it specifies a tablespace for the chan
ge set. (This example assumes that the publisher previously created the TS_CHICAGO_DAILY tablespace.)
The followi
ng example creates a change table on the staging database that captures changes made to a source table in the source database. The ex
ample uses the sample table HR.JOB_HISTORY.
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
owner => 'cdcpub',
change_table_name => 'JOB_HIST_CT',
change_set_name => 'CHICAGO_DAILY',
sour
ce_schema => 'HR',
source_table => 'JOB_HISTORY',
column_type_list => 'EMPLOYEE_ID NUMBER(6),START_DATE DATE,E
ND_DATE DATE,
JOB_ID VARCHAR2(10), DEPARTMENT_ID NUMBER(4)',
capture_values => 'both',
rs_id => 'y',
row_id => 'n',
user_id => 'n',
timestamp => 'n',
object_id => 'n',
source_colmap => 'n',
target_colmap => 'y',
options_string => 'TABLESPACE TS_CHICAGO_DAILY');
END;
/
This example creates
a change table named job_hist_ct within change set CHICAGO_DAILY. The column_type_list paramet
er identifies the columns captured by the change table. The source_schema and source_table parameters ident
ify the schema and source table that reside in the source database, not the staging database.
The capture_values
setting in the example indicates that for update operations, the change data will contain two separate rows for each row that changed
: one row will contain the row values before the update occurred and the other row will contain the row values after the update occur
red.
Step 11 Staging Database Publisher: Enable the change set.
Because asynchronous change sets are always disabled when they are created, the publisher must alter the change set to ena ble it. The Oracle Streams capture and apply processes are started when the change set is enabled.
BEGI
N
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'CHICAGO_DAILY',
enable_capture => 'y');
END;
/
Step 12 Source Database DBA: Switch the redo log files at the source database.
To begin capturing data, a log file must be archived. The source database DBA can initiate the process by swi tching the current redo log file:
ALTER SYSTEM ARCHIVE LOGFILE;
Step 13 Staging Database Publisher: Grant access to subscribers.
The publisher controls
subscriber access to change data by granting and revoking the SQL SELECT privilege on change tables for users and roles
on the staging database. The publisher grants access to specific change tables. Without this step, a subscriber cannot access any cha
nge data. This example assumes that user subscriber1 already exists.
GRANT SELECT ON cdcpu b.job_hist_ct TO subscriber1;
The Change Data Capture asynchronous AutoLog system is now ready for subscriber1 to create subscriptions.
When a publisher creates a change table, Change Data Capture assigns it a publi
cation ID and maintains a list of all the publication IDs in the ALL_PUBLISHED_COLUMNS view. A publication ID is a numeric value that Change Data Capture assigns to each change table defined by the publisher.
The subscribers regi ster their interest in one or more source tables, and obtain subscriptions to these tables. Assuming sufficient access privileges, th e subscribers may subscribe to any source tables for which the publisher has created one or more change tables by doing one of the fo llowing:
Specifying the source tables and columns of interest.
When there are multiple publicatio ns that contain the columns of interest, then Change Data Capture selects one on behalf of the user.
Spe cifying the publication IDs and columns of interest.
When there are multiple publications on a single source table and these p ublications share some columns, the subscriber should specify publication IDs (rather than source tables) if any of the shared column s will be used in a single subscription.
The following steps provide an example to demonstrate the second scenario:<
/p>
Step 1 Find the source tables for which the subscriber has access pri vileges.
The subscriber queries the ALL_SOURCE_TABLES view to see all the published source tables
for which the subscriber has access privileges:
SELECT * FROM ALL_SOURCE_TABLES;
SOURCE_SCHEMA_NAME
SOURCE_TABLE_NAME
------------------------------ ------------------------------
HR JOB_HISTORY
Step 2 Find the change set names and columns for which the subscriber h as access privileges.
The subscriber queries the ALL_PUBLISHED_COLUMNS view to see all the change
sets, columns, and publication IDs for the HR.JOB_HISTORY table for which the subscriber has access privileges:
Step 3 Create a subscription.
The subscriber calls the DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION procedure to create a subscription.
The following example shows how the subscriber identi
fies the change set of interest (CHICAGO_DAILY), and then specifies a unique subscription name that will be used through
out the life of the subscription:
BEGIN
DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION(
change_se
t_name => 'CHICAGO_DAILY',
description => 'Change data for JOB_HISTORY',
subscription_name => 'JOBHIST_SUB');
END;
/
Step 4 Subscribe to a source table and the columns in the source table.
The subscriber calls the DBMS_CDC_SUBSCRIBE.SUBSCRIBE procedure to specify wh
ich columns of the source tables are of interest to the subscriber.
A subscription can contain one or more source tables refer enced by the same change set.
In the following example, the subscriber wants to see the EMPLOYEE_ID, START_
DATE, and END_DATE columns from the JOB_HISTORY table. Because all these columns are contained in th
e same publication (and the subscriber has privileges to access that publication) as shown in the query in Step 2, the following call
can be used:
BEGIN
DBMS_CDC_SUBSCRIBE.SUBSCRIBE(
subscription_name => 'JOBHIST_SUB',
source_schema => 'HR',
source_table => 'JOB_HISTORY',
column_list => 'EMPLOYEE_ID, START_DATE, END_DATE
, JOB_ID',
subscriber_view => 'JOBHIST_VIEW');
END;
/
However, assume that for security reasons the publisher h
as not created a single change table that includes all these columns. Suppose that instead of the results shown in Step 2, the query
of the ALL_PUBLISHED_COLUMNS view shows that the columns of interest are included in multiple publications as shown in t
he following example:
SELECT UNIQUE CHANGE_SET_NAME, SOURCE_TABLE_NAME, COLUMN_NAME, PUB_ID
FROM ALL_P
UBLISHED_COLUMNS
WHERE SOURCE_SCHEMA_NAME ='HR' AND SOURCE_TABLE_NAME = 'JOB_HISTORY';
CHANGE_SET_NAME COLUMN_NAME PUB_I
D
---------------- ------------------ ------------
CHICAGO_DAILY DEPARTMENT_ID 34883
CHICAGO_DAILY EMPLOYEE_ID
34883
CHICAGO_DAILY END_DATE 34885
CHICAGO_DAILY JOB_ID 34883
CHICAGO_DAILY START_DATE
34885
CHICAGO_DAILY EMPLOYEE_ID 34885
This returned data shows that the EMPLOYEE_ID col
umn is included in both publication 34883 and publication 34885. A single subscribe call must specify columns available in a single p
ublication. Therefore, if the subscriber wants to subscribe to columns in both publications, using EMPLOYEE_ID to join a
cross the subscriber views, then the subscriber must use two calls, each specifying a different publication ID:
BEGIN
DBMS_CDC_SUBSCRIBE.SUBSCRIBE(
subscription_name => 'MULTI_PUB',
publication_id => 34885,
column_list => 'EMPLOYEE_ID, START_DATE, END_DATE',
subscriber_view => 'job_dates');
DBMS_CDC_SUBSCRIBE.SUB
SCRIBE(
subscription_name => 'MULTI_PUB',
publication_id => 34883,
column_list => 'EMPLOYEE_ID, JOB_ID
',
subscriber_view => 'job_type');
END;
/
Note that each DBMS_CDC_SUBSCRIBE.SUBSCRIBE call specif
ies a unique subscriber view.
Step 5 Activate the subscription.
The subscriber calls the DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION procedure to activate the subs
cription.
A subscriber calls this procedure when finished subscribing to source tables (or publications), and ready to receive
change data. Whether subscribing to one or multiple source tables, the subscriber needs to call the ACTIVATE_SUBSCRIPTION procedure only once.
The ACTIVATE_SUBSCRIPTION procedure creates empty subscriber views. At this point, no add
itional source tables can be added to the subscription.
BEGIN
DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTI
ON(
subscription_name => 'JOBHIST_SUB');
END;
/
Step 6 Get the next set of change data.
The subscriber calls the DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW procedure to
get the next available set of change data. This sets the high boundary of the subscription window.
For example:
BEGIN
DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
subscription_name => 'JOBHIST_SUB');
END;
/
If this
is the subscriber's first call to the EXTEND_WINDOW procedure, then the subscription window contains all the change data
in the publication. Otherwise, the subscription window contains all the new change data that was created since the last call to the
EXTEND_WINDOW procedure.
If no new change data has been added, then the subscription window remains unchanged.
Step 7 Read and query the contents of the subscriber views.
The subscriber uses the SQL SELECT statement on the subscriber view to query the change data (within the current bo
undaries of the subscription window). The subscriber can do this for each subscriber view in the subscription. For example:
SELECT EMPLOYEE_ID, START_DATE, END_DATE FROM JOBHIST_VIEW; EMPLOYEE_ID START_DAT END_DATE ----------- ------- -- --------- 176 24-MAR-98 31-DEC-98 180 24-MAR-98 31-DEC-98 190 01-JAN-99 31-DEC-99 200 01-JAN-9 9 31-DEC-99
The subscriber view name, JOBHIST_VIEW, was specified when the subscriber called the DBMS
_CDC_SUBSCRIBE.SUBSCRIBE procedure in Step 4.
Step 8 Indicate that th e current set of change data is no longer needed.
The subscriber uses the DBMS_CDC_SUBSCRIBE.DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure causes the subscription window to be empty.
Fo r example:
BEGIN
DBMS_CDC_SUBSCRIBE.PURGE_WINDOW(
subscription_name => 'JOBHIST_SUB');
END
;
/
Step 9 Repeat Steps 6 through 8.
The subscrib er repeats Steps 6 through 8 as long as the subscriber is interested in additional change data.
Step 10 End the subscription.
The subscriber uses the DBMS_CDC_SUBSCRIBE.
BEGIN
DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIPTION(
subs
cription_name => 'JOBHIST_SUB');
END;
/
The following sections provide information that the publisher and the source and staging da tabase DBAs should be aware of when using the asynchronous mode of Change Data Capture.
T he asynchronous mode of Change Data Capture uses redo log files, as follows:
HotLog
Asynchronous HotLog Change Data Capture reads online redo log files whenever possible and archived redo log files otherwise.
AutoLog
Asynchronous AutoLog Change Data Capture reads redo log files that have been copied from the source database t o the staging database by log transport services.
In ARCH mode, log transport services copies archived redo log files to the s taging database after a log switch occurs on the source database. In LGWR mode, log transport services copies redo data to the stagin g database while it is being written to the online redo log file on the source database, and then makes it available to Change Data C apture when a log switch occurs on the source database.
For log files to be a
rchived, the source databases for asynchronous Change Data Capture must run in ARCHIVELOG mode, as specified with the fo
llowing SQL statement:
ALTER DATABASE ARCHIVELOG;
See Oracle Database Administrator's Guide for information about running a database in ARCH
IVELOG mode.
A redo log file used by Change Data Capture must remain available o n the staging database until Change Data Capture has captured it. However, it is not necessary that the redo log file remain availabl e until the Change Data Capture subscriber is done with the change data.
To determine which redo log files are no longer neede
d by Change Data Capture for a given change set, the publisher alters the change set's Streams capture process, which causes Streams
to perform some internal cleanup and populates the DBA_LOGMNR_PURGED_LOG view. The publisher follows these steps:
Uses the following query on the staging database to get the three SCN values needed to determine an app
ropriate new first_scn value for the change set, CHICAGO_DAILY:
SELECT cap.CA
PTURE_NAME, cap.FIRST_SCN, cap.APPLIED_SCN,
cap.SAFE_PURGE_SCN
FROM DBA_CAPTURE cap, CHANGE_SETS cset
WHERE cset.SET_NAME =
'CHICAGO_DAILY' AND
cap.CAPTURE_NAME = cset.CAPTURE_NAME;
CAPTURE_NAME FIRST_SCN APPLIED_SCN SAFE_PURGE_SCN
------------------------------ ---------- ----------- --------------
CDC$C_CHICAGO_DAILY 778059 778293 7
78293
Determines a new first_scn value that is greater than the original first_scn va
lue and less than or equal to the applied_scn and safe_purge_scn values returned by the query in step 1. In
this example, this value is 778293, and the capture process name is CDC$C_CHICAGO_DAILY, therefore the publisher can al
ter the first_scn value for the capture process as follows:
BEGIN DBMS_CAPTURE_ADM.ALTER_C APTURE( capture_name => 'CDC$C_CHICAGO_DAILY', first_scn => 778293); END; /
If there if not an SCN value tha t meets these criteria, then the change set needs all of its redo log files.
Queries the DBA_LOGMNR_PURGED_LOG
view to see any log files that are no longer needed by Change Data Capture:
SELECT FILE_NAME FR OM DBA_LOGMNR_PURGED_LOG;
|
Note: Redo log files may be required on the staging database for purposes other than Change D ata Capture. Before deleting a redo log file, the publisher should be sure that no other users need it. |
See the information on setting the first SCN for an existing capture pro cess and on capture process checkpoints in Oracle Streams Concep ts and Administration for more information.
The first_scn value can be updated for all change sets in an
AutoLog change source by using the DBMS_CDC_PUBLISH.ALTER_AUTOLOG_CHANGE_SOURCE first_scn parameter. Note
that the new first_scn value must meet the criteria stated in step 2 of the preceding list for all change sets in the Au
toLog change source.
Both the size of the redo log files and the frequency with which a log switch occurs can affect the gener ation of the archived log files at the source database. For Change Data Capture, the most important factor in deciding what size to m ake a redo log file is the tolerance for latency between when a change is made and when that change data is available to subscribers. However, because the Oracle Database software attempts a check point at each log switch, if the redo log file is too small, frequent log switches will lead to frequent checkpointing and negatively impact the performance of the source database.
See Oracle Data Guard Concepts and Administration for step-by-step in structions on monitoring log file archival information. Substitute the terms source and staging database for the Oracle Data Guard te rms primary database and archiving destinations, respectively.
When using log transport services to supply redo log files to a
n AutoLog change source, gaps in the sequence of redo log files are automatically detected and resolved. If a situation arises where
it is necessary to manually add a log file to an AutoLog change set, the publisher can use instructions on explicitly assigning log f
iles to a downstream capture process described in the Oracle Str
eams Concepts and Administration. These instructions require the name of the capture process for the AutoLog change set. The
publisher can obtain the name of the capture process for an AutoLog change set from the CHANGE_SETS data dictionary vie
w.
The asynchronous mode of Change Data Capture works best when supplemental logging is enabled on the source database. (Supplemental logging is not used by synchronous Change Data Captur e.)
Oracle recommends that the source database DBA enable minimal supplemental logging at the database level:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
In addition, Oracle recommends that the source database DBA:
Supplementally log all source table columns that are part of a primary key or function to uniquely identify a row. This can be done using database-level or table-level identification key logging, or through a table-level unconditional log gr oup.
Create an unconditional log group for all source table columns that are captured by any asynchronou s change table. This should be done before any change tables are created on a source table.
ALTER TABLE SH.PROMOTIONS ADD SUPPLEMENTAL LOG GROUP log_group_cust (PROMO_NAME, PROMO_SUBCATEGORY, PROMO_CATEGORY) ALWAYS;
If an un conditional log group is not created for all source table columns to be captured, then when an update DML operation occurs, some unch anged user column values in change tables will be null instead of reflecting the actual source table value.
For example, suppo
se a source table contains two columns, X and Y, and that the source database DBA has defined an unconditio
nal log group for that table that includes only column Y. Furthermore, assume that a user updates only column Y in that table row. When the subscriber views the change data for that row, the value of the unchanged column X will
be null. However, because the actual column value for X is excluded from the redo log file and therefore cannot be incl
uded in the change table, the subscriber cannot assume that the actual source table value for column X is null. The subs
criber must rely on the contents of the TARGET_COLMAP$ control column to determine whether the actual source table value
for column X is null or it is unchanged.
See O racle Database Utilities for more information on the various types of supplemental logging.
Asynchronous Change Data Capture supports columns of all built-in Oracle datatypes except the following :
BFILE
BLOB
C
LOB
LONG
NCLOB
ROWIDUROWID
object types (for example, XMLType )
Asynchronous Change Data Capture does not support the following table structures:
Sou rce tables that are temporary tables
Source tables that are object tables
I
ndex-organized tables with columns of unsupported datatypes (including LOB columns) or with overflow segments
T his section provides information about the management tasks involved in managing change sets and change tables. For the most part, th ese tasks are the responsibility of the publisher. However, to purge unneeded data from the change tables, both the publisher and the subscribers have responsibilities as described in "Purging Change Tables of Unneeded Data".
The follo wing topics are covered in this section:
< a id="sthref1003" name="sthref1003">This section provides information about tasks that t he publisher can perform to manage asynchronous change sets. The following topics are covered:
Change sets associated with asynchronous change sources (Au toLog and HotLog) can optionally specify starting and ending dates to limit the change data that they capture. A change set with no s tarting date begins capture with the earliest available change data. A change set with no ending date continues capturing change data indefinitely.
The following example creates a change set, JOBHIST_SET, in the AutoLog change source, HQ_SO
URCE, that starts capture two days from now and continues indefinitely:
BEGIN DBMS_CDC_PUBLISH.C REATE_CHANGE_SET(
change_set_name => 'JOBHIST_SET', description => 'Job History Application C hange Set', change_source_name => 'HQ_SOURCE', stop_on_ddl => 'Y', begin_date => sysdate+2);
END; /
The publisher can enable and disable asynchronous change sets. When a change set is disabled, i t cannot process new change data until the change set is enabled.
Synchronous change sets are always created enabled and canno t be disabled. Asynchronous change sets are always created disabled.
The publisher can enable the JOBHIST_SET asy
nchronous change set with the following call:
BEGIN DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'JOBHIST_SET', enable_capture => 'y');
END; /
The Oracle Streams capture and apply processes for the change set are started when the change set is enabled.
The publisher
can disable the JOBHIST_SET asynchronous change set with the following call:
BEGIN DBMS_C DC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'JOBHIST_SET', enable_capture => 'n');
END; /
The Oracle Streams capture and apply processes for the change set are stopped when th e change set is disabled.
Although a disabled change set cannot process new change da ta, it does not lose any change data provided that the necessary archived redo log files remain available until the change set is ena bled and processes them. Oracle recommends that change sets be enabled as much as possible to avoid accumulating archived redo log fi les. See "Asynchronous Change Data Capture and Redo Log Files" for more information.
Change Data Captu
re can automatically disable an asynchronous change set if DDL is encountered during capture and the stop_on_ddl parameter is set to 'Y', or if there is an internal capture error. The publisher must check the alert log for more information, take any nec
essary actions to adjust to the DDL or recover from the internal error, and explicitly enable the change set. See "Recovering from Errors Returned on Asynchronous Change Sets" for more information.
The publisher can specify that a change set be automatically disabled by Change Data Capture if D DL is encountered. Some DDL commands can adversely affect capture, such as dropping a source table column that is being captured. If the change set stops on DDL, the publisher has a chance to analyze and fix the problem before capture proceeds. If the change set doe s not stop on DDL, internal capture errors are possible after DDL occurs.
The publisher can specify whether a change set stops
on DDL when creating or altering the change set. The publisher can alter the JOBHIST_SET change set to stop on DDL with
the following call:
BEGIN DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_ set_name => 'JOBHIST_SET', stop_on_ddl => 'y');
END; /
The publisher can al
ter the JOBHIST_SET change set so that it does not stop on DDL by using the following call:
BEGIN DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'JOBHIST_SET', stop_on_ddl =& gt; 'n');
END; /
If a DDL statement causes processing to stop, a message is written to
the alert log indicating the DDL statement and change set involved. For example, if a TRUNCATE TABLE DDL statement cause
s the JOB_HIST change set to stop processing, the alert log contains lines such as the following:
Change Data Capture received DDL for change set JOB_HIST Change Data Capture received DDL and stopping: truncate table job_hi story
Because they do not affect the column data itself, the following DDL state
ments do not cause Change Data Capture to stop capturing change data when the stop_on_ddl parameter is set to 'Y':
ANALYZE TABLE
LOCK TABLE
GRANT privileges to access a table
REVOKE privileges to access a table
COMMENT on a table
COMMENT on a column
These statements can be issued on the source database without concern for their impact on Change Data Capture processing. For example
, when an ANALYZE TABLE command is issued on the JOB_HISTORY source table, the alert log on th
e staging database will contain a line similar to the following when the stop_on_ddl parameter is set to 'Y':
Change Data Capture received DDL and ignoring: analyze table job_history compute statistics
Errors during asynchronous Change Data Capture are possible due to a variety of circumstances. If a change set stop s on DDL, that DDL must be removed before capture can continue. If a change set does not stop on DDL, but a DDL change occurs that af fects capture, it can result in an internal error. There are also system conditions that can cause internal errors, such as being out of disk space.
In all these cases, the change set is disabled and marked as having an error. Subscriber procedures detect whe n a change set has an error and return the following message:
ORA-31514: change set disabled due to cap ture error
The publisher must check the alert log for more information and attempt to fix the underlying problem. The pub
lisher can then attempt to recover from the error by calling ALTER_CHANGE_SET with the recover_after_error
and remove_ddl parameters set appropriately. The publisher can retry this procedure as many times as necessary to resolv
e the problem. When recovery succeeds, the error is removed from the change set and the publisher can enable the asynchronous change
set (as described in "Enabling and Disabling Asynchronous Change Sets").
If more information is needed
to resolve capture errors, the publisher can query the DBA_APPLY_ERROR view to see information about Streams apply erro
rs; capture errors correspond to Streams apply errors. The publisher must always use the DBMS_CDC_PUBLISH.ALTER_CHANGE_SET procedure to recover from capture errors because both Streams and Change Data Capture actions are needed for recovery and only the
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET procedure performs both sets of actions. See Oracle Streams Concepts and Administration for information about the error queue and apply erro
rs.
The following two scenarios demonstrate how a publisher might investigate and then recover from two different types of err ors returned to Change Data Capture:
The publisher can view the contents of the alert log to determine which error is being returned for a given change set and which SCN is not being processed. For example, the alert log may contain lines such as the following (where LCR refers to a logical change record) :
Change Data Capture has encountered error number: 1688 for change set: CHICAGO_DAILY Change Data Capt ure did not process LCR with scn 219337
The publisher can determine the message associated with the error number specifie
d in the alert log by querying the DBA_APPLY_ERROR view for the error message text, where the APPLY_NAME in
the DBA_APPLY_ERROR view equals the APPLY_NAME of the change set specified in the alert log. For example:<
/p>
SQL> SELECT ERROR_MESSAGE FROM DBA_APPLY_ERROR
WHERE APPLY_NAME =
(SELECT APPLY_NAME FR
OM CHANGE_SETS WHERE SET_NAME ='CHICAGO_DAILY');
ERROR_MESSAGE
--------------------------------------------------------------------
------------
ORA-01688: unable to extend table LOGADMIN.CT1 partition P1 by 32 in tablespace
TS_CHICAGO_DAILY
Afte
r taking action to fix the problem that is causing the error, the publisher can attempt to recover from the error. For example, the p
ublisher can attempt to recover the CHICAGO_DAILY change set after an error with the following call:
BEGIN DBMS_CDC_PUBLISH.ALTER_CHANGE_SET( change_set_name => 'CHICAGO_DAILY', recover_after_error => 'y'); EN D; /
If the recovery does not succeed, then an error is returned and the publisher can take further action to attempt to r esolve the problem. The publisher can retry the recovery procedure as many times as necessary to resolve the problem.
|
Note: When recovery succeeds, the publisher must remember to enable the change set. |
Suppose a SQL TRUNCATE TABLE statement is issued against the JOB_
HISTORY source table and the stop_on_ddl parameter is set to 'Y', then an error such as the following is returned
from an attempt to enable the change set:
ERROR at line 1: ORA-31468: cannot process DDL change record ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 79 ORA-06512: at line 2
The alert log will contain lines similar to the follo wing:
Mon Jun 9 16:13:44 2003 Change Data Capture received DDL for change set JOB_HIST Change Data Cap ture received DDL and stopping: truncate table job_history Mon Jun 9 16:13:50 2003 Change Data Capture did not process LCR with scn 219777 Streams Apply Server P001 pid=19 OS id=11730 stopped Streams Apply Reader P000 pid=17 OS id=11726 stopped Streams Apply Server P000 pid=17 OS id=11726 stopped Streams Apply Server P001 pid=19 OS id=11730 stopped Streams AP01 with pid=15, OS id=11722 stopped
Because the TRUNCATE TABLE statement removes all rows from a table, the publisher will want to n
otify subscribers before taking action to reenable Change Data Capture processing. He or she might suggest to subscribers that they p
urge and extend their subscription windows. The publisher can then attempt to restore Change Data Capture processing by altering the
change set and specifying the remove_ddl => 'Y' pa
rameter along with the recover_after_error => 'Y' parameter, as follows:
BEGIN DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'JOB_HIST', recove r_after_error => 'y', remove_ddl => 'y');
END; /
After this procedure completes, the alert log will contain lines similar to the following:
Mon Jun 9 16:20:17 2003 Change D ata Capture received DDL and ignoring: truncate table JOB_HISTORY The scn for the truncate statement is 202998
Now, the p ublisher must enable the change set.
All change table management tasks are the responsibility of the publisher with one exception: purging change tables of unneeded data. This task requires action from both the publisher and the subscriber to work most effectively.
The following topics are discussed in this section:
< ul>When creating change tables, the publisher should be aware that Oracle recommends the following:
For all modes of Change Data Capture, publishers should not create change tables in system tablespaces.
Either of the following methods can be used to ensure that change tables are created in tablespaces managed by the publisher. The fi rst method creates all the change tables created by the publisher in a single tablespace, while the second method allows the publishe r to specify a different tablespace for each change table.
When the database administrator creates the account for the publisher, he or she can specify a default tablespace. For example:
CREATE USER cdcpub DEFAULT TABLESPACE ts_cdcpub;
When the publisher creates a change table, he or she can use the < code>options_string parameter to specify a tablespace for the change table being created. See Step 4 in "P erforming Synchronous Publishing" for an example.
If both methods are used, the tablespace specified by the publ
isher in the options_string parameter takes precedence over the default tablespace specified in the SQL CREATE USER statement.
For asynchronous Change Data Capture, the publisher should be certain t
hat the source table that will be referenced in a DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE procedure has been created prior
to calling this procedure, particularly if the change set that will be specified in the procedure has the stop_on_ddl pa
rameter set to 'Y'.
Suppose the publisher created a change set with the stop_on_ddl parameter set to 'Y', then cr
eated the change table, and then the source table was created. In this scenario, the DDL that creates the source table would trigger
the stop_on_ddl condition and cause Change Data Capture processing to stop.
For asynchronous Change Data Capture, the source database DBA should create an uncondi
tional log group for all source table columns that will be captured in a change table. This should be done before any change tables a
re created on a source table. If an unconditional log group is not created for source table columns to be captured, then when an upda
te DML operation occurs, some unchanged user column values in change tables will be null instead of reflecting the actual source tabl
e value. This will require the publisher to evaluate the TARGET_COLMAP$ control column to distinguish unchanged column v
alues from column values that are actually null. See "Asynchronous Change Data Capture and Supplemental Logging"<
/a> for information on creating unconditional log groups and see "Understanding Change Table Control Columns"
for information on control columns.
A change table con sists of two things: the change data itself, which is stored in a database table, and the system metadata necessary to maintain the c hange table, which includes control columns.
Table 16-8 describes the control columns for a change tab le, including the column name, datatype, mode, whether the column is optional or not, and a description.
The mode indicates th
e type of Change Data Capture associated with the column. A value of All indicates that the column is associated with the sy
nchronous mode and both modes of asynchronous Change Data Capture. Note that for both synchronous and asynchronous Change Data Captur
e, if the subscriber wants a query of a subscriber view to return DML changes in the order in which they occurred, the query should order data by CSCN$ and then RSID
$.
A column is considered optional if the publisher can choose to exclude it from a change table using the operat
ion parameter and the parameters that indicate specific control columns in the DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE procedure. The syntax for the DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE procedure is documented in PL/SQL Packages and Types Reference.
Table 16-8 Control Columns for a Change Table
| Column | Datatype | Mode | Optional Column | Description | OPERATION$ |
CHAR(2) |
All | No | The value in this column can be any one of the following
< code>UO: Indicates this row represents the before-image of an updated source table row for the following cases:
|
|---|---|---|---|---|
CS
CN$ |
NUMBER |
Al l | No | Commit SCN of this transacti on. |
RSID$ |
NUMBER<
/td>
| All | Yes | Unique row sequence ID within this transaction.Foot 2&
nbsp; The RSID$ column reflects an operation's capture order within a transaction, but not across transactions
. The publisher cannot use the RSID$ column value by itself to order committed operations across transactions; it must b
e used in conjunction with the CSCN$ column value. |
SOURCE_COLMAP$
| RAW(128) |
Synchronous< /td> | Yes | Bit maskFoot 3 of updated columns in the source table. |
TARGET_COLMAP$ |
RAW(128) |
All | Yes | Bit maskFootref 3 of updated c olumns in the change table. |
COMMIT_TIMESTAMP$ |
DATE |
All | No | Commit time of this transaction. |
DATE |
All | Yes | Time when the oper ation occurred in the source table. | |
USERNAME$ |
VARCHAR2(30) |
All | Yes | Name of the user who caused the operation. |
ROW_ID$ |
ROW_ID |
All | Yes | Row ID of affected row in source table. |
XIDUSN$ |
NUMBER |
Asynchronous | No | Transaction ID undo segment number. |
XIDSLT$ |
NUMBER |
Asynchronous | No | Transaction ID slot number. |
XIDSEQ$ |
NUMBER |
Asynchronous | No | Transaction ID sequence number. |
SYS_NC_OID$ |
RAW(16) |
Synchronous | Yes | Object ID. |
I or D c
olumn values as "I " or "D ", respectively. The OPERATION$ column is a 2-character column; values are left-justified and
space-filled. A query that specifies a value of "I" or "D" will return no values.RSID$ column to associate the after-image with the before-image of a gi
ven operation. The value of the after-image RSID$ column always matches the value of the before-image RSID$
column value for a given update operation.The TARGET_COLMAP$ and SOURCE_COLMAP$ columns are used to indicate whi
ch columns in a row have changed. The TARGET_COLMAP$ column indicates which columns in the change table row have changed
. The SOURCE_COLMAP$ column (which is included for synchronous change tables only) indicates which columns in a source t
able row have changed.
Because the datatype of the TARGET_COLMAP$ and the SOURCE_COLMAP$ columns is
RAW(128), each column can hold 128 bytes of binary information. This binary information consists of words strung togethe
r with the low order byte on the left and the high order byte on the right. By default, when the field is displayed, the system inter
prets each byte and displays a value for each. Example 16-3 provides a sample TARGET_COLMAP$ val
ue. The text following the example explains how to interpret this data.
Example 16-3 Sample TARGET_COLMA P$ VALUE
FE11000000000000000000000000000000000000000000000000000000000000000000000 0000000 00000000000000000000000000000000000000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000 000000000000000000000000000000000000 000000000000
In Example 16-3, the first 'FE' is the low orde r byte and the last '00' is the high order byte. To correctly interpret the meaning of the values, you must consider which bits are s et in each byte. The bits in the bitmap are counted starting at zero. The first bit is bit 0, the second bit is bit 1, and so on. Bit 0 is always ignored. For the other bits, if a particular bit is set to 1, it means that the value for that column has been changed.< /p>
To interpret the string of bytes as presented in the Example 16-3, you read from left to right. The fi rst byte is the string 'FE'. Broken down into bits (again from left to right) this string is "1111 1110", which maps to columns " 7,6 ,5,4 3,2,1,-" in the change table (where the hyphen represents the ignored bit). The first bit tells you if column 7 in the change ta ble has changed. The right-most bit is ignored. The values in Example 16-3 indicate that the first 7 columns have a value present. This is typical - the first several columns in a change table are control columns.
The next byte in Example 16-3 is the string '11'. Broken down into bits, this string is "0001 0001", which maps to columns "15,14, 13,12 11,10,9,8" in the change table. These bits indicate that columns 8 and 12 are changed. Columns 9, 10, 11, 13, 14, 15, are not c hanged. The rest of the string is all '00', indicating that none of the other columns has been changed.
A publisher can issue the following query to determine the mapping of column numbers to column names:
SELECT COLUMN_NAME, COL
UMN_ID FROM ALL_TAB_COLUMNS WHERE OWNER='PUBLISHER_STEWART' AND TABLE_NAME='MY_CT';
COLUMN_NAME COLUMN_ID
-------
----------------------- ----------
OPERATION$ 1
CSCN$ 2
COMMIT_TIMESTAMP$
3
XIDUSN$ 4
XIDSLT$ 5
XIDSEQ$
6
RSID$ 7
TARGET_COLMAP$ 8
C_ID 9
C_KEY
10
C_ZIP 11
COLUMN_NAME COLUMN_ID
-----------------------
------- ----------
C_DATE 12
C_1 13
C_3
14
C_5 15
C_7 16
C_9 17
Using Example 16-3, the publisher can conclude that following columns were changed in the particular cha
nge row in the change table represented by this TARGET_COLMAP$ value: OPERATION$, CSCN$, COMMIT_TIMESTAMP$, XIDUSN$, XIDSLT$, XIDSEQ$, RSID$, TARGET_COLMAP$
, and C_DATE.
Note that Change Data Capture generates values for all control columns in all change rows, s
o the bits corresponding to control columns are always set to 1 in every TARGET_COLMAP$ column. Bits that correspond to
user columns that have changed are set to 1 for the OPERATION$ column values UN and I, as appr
opriate. (See Table 16-8 for information about the OPERATION$ column values.)
A common us
e for the values in the TARGET_COLMAP$ column is for determining the meaning of a null value in a change table. A column
value in a change table can be null for two reasons: the value was changed to null by a user or application, or Change Data Capture
inserted a null value into the column because a value was not present in the redo data from the source table. If a user changed the v
alue to null, the bit for that column will be set to 1; if Change Data Capture set the value to null, then the column will be set to
0.
Values in the SOURCE_COLMAP$ column are interpreted in a similar manner, with the following exceptions:
The SOURCE_COLMAP$ column refers to columns of source tables, not
columns of change tables.
The SOURCE_COLMAP$ column does not reference control columns beca
use these columns are not present in the source table.
Changed source columns are set to 1 in the
SOURCE_COLMAP$ column for OPERATION$ column values UO, UU, UN, and I
, as appropriate. (See Table 16-8 for information about the OPERATION$ column values.)
The SOURCE_COLMAP$ column is valid only for synchronous change tables.
The publisher grants privileges to subscribers to allow them access to change tables. Because privileges on sour
ce tables are not propagated to change tables, a subscriber might have privileges to perform a SELECT operation on a sou
rce table, but might not have privileges to perform a SELECT operation on a change table.
The publisher controls subscriber access to change data by using the SQL GRANT and REVOKE statement
s to grant and revoke the SELECT privilege on change tables for users and roles. The publisher must grant the SELE
CT privilege before a subscriber can subscribe to the change table.
The publisher must not grant any DML access (use of INSERT, UPDATE, or DELETE statements) to the subscribers on the change tables because of the risk that a subscriber might inadvertently change t
he data in the change table, making it inconsistent with its source. Furthermore, the publisher should avoid creating change tables i
n schemas to which subscribers have DML access.
This section describes purge operations. For optimum results , purge operations require action from the subscribers. Each subscriber indicates when he or she is done using change data, and then Change Data Capture or the publisher actually removes (purges) data that is no longer being used by any subscriber from the change ta ble, as follows:
Subscriber
When finished using change d
ata, a subscriber must call the DBMS_CDC_SUBS
CRIBE.PURGE_WINDOW procedure. This indicates to Change Data Capture and the publisher that the change data is no longer needed
by this subscriber. The DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure does not physically remove rows from the change table
s.
In addition, as shown in "Subscribing to Change Data" beginning, the subscriber should call the
See PL/SQL Packages and Types Reference for information about the DBM
S_CDC_SUBSCRIBE.DROP_SUBSCRIPTION and the DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedures.
Change Data Capture creates a purge job using the DBMS_JOB PL/SQL package (which runs under the accou
nt of the publisher who created the first change table). This purge job automatically calls the
DBMS_CDC_PUBLISH.PURGE procedure to remove data that subscribers are no lon
ger using from the change tables. This ensures that the size of the change tables does not grow without limit. The automatic call to
the DBMS_CDC_PUBLISH.PURGE procedure evaluates all active subscription windows to determine which change data is still n
eeded. It will not purge any data that could be referenced by one or more subscribers with active subscription windows.
By def
ault, this purge job runs every 24 hours. The publisher who created the first change table can adjust this interval using the PL/SQL
DBMS_JOB.CHANGE procedure. The values for the JOB parameter for this procedure can be found by querying the
USER_JOBS view for the job number that corresponds to the WHAT column containing the string 'SYS.DBM
S_CDC_PUBLISH.PURGE();'.
See PL/SQL Packages and T
ypes Reference for information about the DBMS_JOB package and the Oracle Database Reference for information about the USER_JOBS view.
Publisher
The publisher can manually execute a purge operation at any time. The publisher has the ability to perform purge operations at a finer granularity than the automatic purge operation performed by Cha nge Data Capture. There are three purge operations available to the publisher:
Thus, calls to the DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure by subscribers and calls to the PURGE procedure by Change Data Capture (or one of the PURGE procedures by the publisher) work together: when each subscrib
er purges a subscription window, it indicates change data that is no longer needed; the PURGE procedure evaluates the su
m of the input from all the subscribers before actually purging data.
Note that it is possible that a subscriber could fail to
call PURGE_WINDOW, with the result being that unneeded rows would not be deleted by the purge job. The publisher can qu
ery the DBA_SUBSCRIPTIONS view to determine if this is happening. In extreme circumstances, a publisher may decide to ma
nually drop an active subscription so that space can be reclaimed. One such circumstance is a subscriber that is an applications prog
ram that fails to call the PURGE_WINDOW procedure when appropriate. The DBMS_CDC_PUBLISH.DROP_SUBSCRIPTION
procedure lets the publisher drop active subscriptions if circumstances require it; however, the publisher should first consider that
subscribers may still be using the change data.
To drop a change table, the publis
her must call the DBMS_CDC_PUBLISH.DROP_CHANG
E_TABLE procedure. This procedure ensures that both the change table itself and the Change Data Capture metadata for the table
are dropped. If the publisher tries to use a SQL DROP TABLE statement on a change table, it will fail with
the following error:
ORA-31496 must use DBMS_CDC_PUBLISH.DROP_CHANGE_TABLE to drop change tables
The DBMS_CDC_PUBLISH.DROP_CHANGE_TABLE
procedure also safeguards the publisher from inadvertently dropping a change table w
hile there are active subscribers using the change table. If DBMS_CDC_PUBLISH.DROP_CHANGE_TABLE is called while subscrip
tions are active, the procedure will fail with the following error:
ORA-31424 change table has active subscriptions
If the publisher still wan
ts to drop the change table, in spite of active subscriptions, he or she must call the DROP_CHANGE_TABLE procedure using
the force_flag => 'Y' parameter. This tells Change Data Capture to override its normal safeguards and allow the chan
ge table to be dropped despite active subscriptions. The subscriptions will no longer be valid, and subscribers will lose access to t
he change data.
Starting in Oracle Databse 10g, Oracle Data Pump is the supported export and import utility for Change Data Capture. Change Data Capture change sources,
change sets, change tables, and subscriptions are exported and imported by the Oracle Data Pump expdp and impdp commands with the following restrictions:
Change Data Capture objects are exported and imported on
ly as part of full database export and import operations (those in which the expdp and impdb commands speci
fy the FULL=y parameter). Schema-level
import and export operations include some underlying objects (for example, the table underlying a change table), but not the Change
Data Capture metadata needed for change data capture to occur.
AutoLog change sources, change sets, and change tables are not supported.
You should export asynchronous change se ts and change tables at a time when users are not making DDL and DML changes to the database being exported.
When
importing asynchronous change sets and change tables, you must also import the underlying Or
acle Streams configuration; set the Oracle Data Pump import parameter STREAMS_CONFIGURATION to y explicitly
(or implicitly by accepting the default), so that the necessary Streams objects are imported. If you perform an import operation and
specify STREAMS_CONFIGURATION=n, then imported asynchronous change sets and change tables wil
l not be able to continue capturing change data.
Change Data Capture objects never overwrite existing ob
jects when they are imported (similar to the effect of the import command TABLE_EXISTS_ACTION=skip parameter for tables)
. Change Data Capture generates warnings in the import log for these cases.
Change Data Capture objects are validated at the end of an import operation to determine if all expected underlying objects are present in the correct form. Chan ge Data Capture generates validation warnings in the import log if it detects validation problems. Imported Change Data Capture objec ts with validation warnings usually cannot continue capturing change data.
The following are examples of Data Pump e xport and import commands that support Change Data Capture objects:
> expdp system/manager DIRECTORY =dpump_dir FULL=y > impdp system/manager DIRECTORY=dpump_dir FULL=y STREAMS_CONFIGURATION=y
After a Data Pump full dat abase import operation completes for a database containing AutoLog Change Data Capture objects, the following steps must be performed to restore these objects:
The publisher must manually drop the change tables with the SQL
DROP TABLE command. This is needed because the tables are imported without the accompanying Change Data Capture metadata.
The publisher must re-create the AutoLog change sources, change sets, and change tables using the appropriate DBMS
_CDC_PUBLISH procedures.
Subscribers must re-create their subscriptions to the AutoLog change sets.
Change data may be lost in the interval between a Data Pump full database export operation involving AutoLog Change Data Capt ure objects and their re-creation after a Data Pump full database import operation in the preceding step. This can be minimized by pr eventing changes to the source tables during this interval, if possible.
See Oracle Database Utilities for information on Oracle Data Pump.
The following are publisher consid erations for exporting and importing change tables:
When change tables are imported, the job queue is ch
ecked for a Change Data Capture purge job. If no purge job is found, then one is submitted automatically (using the DBMS_CDC_PU
BLISH.PURGE procedure). If a change table is imported, but no subscriptions are taken out before the purge job runs (24 hours
later, by default), then all rows in the table will be purged.
The publisher can use one of the following methods to prevent t he purging of data from a change table:
Suspend the purge job using the DBMS_JOB package
to either disable the job (using the BROKEN procedure) or execute the job sometime in the future when there are subscrip
tions (using the NEXT_DATE procedure).
|
Note: If you disable the purge job by marking it as broken, you need to remember to reset it once subscriptions have been activ ated. This prevents the change table from growing indefinitely. |
Create a temporary subscription to preserve the change table data until real subscriptions appear. Then, dro p the temporary subscription.
When importing data into a source table for which a change table already exists, the imported data is also recorded in any associated change tables.
Assume that the publisher has a source table SALES that has an associated chang
e table ct_sales. When the publisher imports data into SALES, that data is also recorded in ct_sales<
/code>.
When importing
a change table having the optional control ROW_ID column, the ROW_ID columns stored in the change table ha
ve meaning only if the associated source table has not been imported. If a source table is re-created or imported, each row will have
a new ROW_ID that is unrelated to the ROW_ID that was previously recorded in a change table.
The original level of export and import support available in Oracle9i is retained for backward compatibility. Synchronou
s change tables that reside in the SYNC_SET change set can be exported as part of a full database, schema, or individual
table export operation and can be imported as needed. The following Change Data Capture objects are not included in the original exp
ort and import support: change sources, change sets, change tables that do not reside in the SYNC_SET change set, and su
bscriptions.
The Change Data Capture environment is dynamic. The publisher can add and drop change tables at any time. The publisher can also add columns to and drop columns from exist ing change tables at any time. The following list describes how changes to the Change Data Capture environment affect subscriptions:< /p>
Subscribers do not get explicit notification if the publisher add
s a new change table or adds columns to an existing change table. A subscriber can check the ALL_PUBLISHED_COLUMNS view to see if new columns have been added, and whether
or not the subscriber has access to them.
Tabl e 16-9 describes what happens when the publisher adds a column to a change table.
Table 16-9 Effects of Publisher Adding a Column to a Change Table
| If the publisher adds | And . . . | Then . . . |
|---|---|---|
| A user column | A new subscription includes this column | The subscription window for this subscription starts at the point the column was added. |
| A user column | A new subscription does not include this newly added column | The subscripti on window for this subscription starts at the earliest available change data. The new column will not be seen. |
| A user column | A subscription exists | The subscription window for this subscription remains unchang ed. |
| A control column | A new subscription is created | The subscription window for this subscription starts at the earliest available change data. The subscription can see the control column immediately. All chan ge table rows that existed prior to adding the control column will have the null value for the newly added control column. |
| A control column | A subscription exists | This subscription can see the new control colu
mn when the subscription window is extended (DBMS_CDC_PUBLISH.EXTEND_WINDOW procedure) such that the low boundary for th
e window crosses over the point when the control column was added. |
Change Data Capture comes packaged with the appropriate Oracle drivers already installed with which you can implement eit her asynchronous or synchronous data capture. Starting with Oracle Database 10g, the synchronous mode of Change Data Capture is included with the Standard Edition; the synchronous and asynchronous modes of Change Data Capture are included with the Enterpris e Edition.
In addition, note that Change Data Capture uses Java. Therefore, when you install Oracle Database, ensure that Java is enabled.
Change Data Capture places systemwide triggers on the SQL CREATE TABLE, ALTER
TABLE, and DROP TABLE statements. If system triggers are disabled on the source database, Change Dat
a Capture will not function correctly. Therefore, you should never disable system triggers.
To remove Change Data Capture from
the database, the SQL script rmcdc.sql is provided in the admin directory.
This will remove the system triggers that Change Data Capture places on the SQL CREATE TABLE, ALTER TABLE, and DROP TABLE statements. In addition, rmcdc.sql removes all Java c
lasses used by Change Data Capture. Note that after rmcdc.sql is called, Change Data Capture will no longer operate on t
he system. If the system administrator decides to remove the Java Virtual Machine from a database, rmcdc.sql must be cal
led before rmjvm is called.
To reinstall Change Data Capture, the SQL script initcdc.sql is provided in the admin directory. It creates th
e Change Data Capture system triggers and Java classes that are required by Change Data Capture.
Synchronous Change Data Capture does not support the direct-path INSERT statement (and, by
association, the MERGE statement and the multi_table_insert clause of the INSERT statement) i
n parallel DML mode.
When the publisher creates a change table in synchronous mode, Change Data Capture creates triggers on th
e source table. Because a direct-path INSERT statement disables all database triggers, any rows inserted into the source
table using the SQL statement for direct-path INSERT in parallel DML mode will not be captured in the change table.
Similarly, Change Data Capture cannot capture the inserted rows from multitable insert and merge operations because these stateme
nts use a direct-path INSERT statement. The direct-path INSERT statement does not return an error message t
o indicate that the triggers used by Change Data Capture did not fire.
See Oracle Database SQL Reference for more information regarding the direct-path INSERT statement
and triggers.
|
 Copyright © 2001, 2003 Oracle Corporation All Rights Reserved. |
|
