 |
|
Apache.RU - Webboard Âåðíóòüñÿ XML-èíòåðïðèòàòîð (Couwecs) 09/03 - 17:16:17 Re: XML-èíòåðïðèòàòîð (iPod) 10/03 - 01:49:34 Re: XML-èíòåðïðèòàòîð (Couwecs) 10/03 - 21:40:47 Re: XML-èíòåðïðèòàòîð (iPod) 12/03 - 11:28:57 Re: XML-èíòåðïðèòàòîð (vladev) 24/02 - 12:19:06> Original message text: > From: Couwecs - 09/03 - 17:16:17 > Subject:XML-èíòåðïðèòàòîð > ----------------- > > Âîïðîñ: êòî ìîæåò ïîäñêàçàòü êàêîé-íèáóòü XML-èíòåðïðèòàòîð, êîé ìîæíî ïðèêðóòèòü ê àïà÷ó? > From: vladev - 24/02 - 12:19:06 Subject:XML-èíòåðïðèòàòîð ----------------- The script content on this page is for navigation purposes only and does not alter the content in any way. 16 Change Data Capture Change Data Capture efficiently identifies and captures 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 chapter describes Change Data Capture in the following sections: • Overview of Change Data Capture • Change Sources and Modes of Data Capture • Change Sets • Change Tables • Getting Information About the Change Data Capture Environment • Preparing to Publish Change Data • Publishing Change Data • Subscribing to Change Data • Considerations for Asynchronous Change Data Capture • Managing Published Data • Implementation and System Configuration See PL/SQL Packages and Types Reference for reference information about the Change Data Capture publish and subscribe PL/SQL packages. Overview of Change Data Capture Often, data warehousing involves the extraction and transportation of relational data from one or more production databases into a data warehouse for analysis. Change Data Capture quickly identifies and processes only the data that has changed and makes the change data available for further use. Capturing Change Data Without Change Data Capture Prior to the introduction of Change Data Capture, there were 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 (production) database to the staging database (where the change data is captured), where an older version of the table already exists. Using 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 transport costs. • The computational cost of performing the two MINUS operations on the staging database 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 fluctuated. Moreover, any intermediate price values between the old and new versions of the product's table cannot be captured using table differencing. • There is no way to determine which changes were made as part of the same transaction. For example, suppose a sales manager creates a special discount to close a deal. The fact that the creation of the discount and the creation 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 on the source database by selecting the new and changed data from the source tables based on the value of a specific column. For example, 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 expensive queries against the source table on the source database. The need for these queries may force you to add indexes that would otherwise be unneeded. There is no way to offload this overhead to the staging database. • This method is no better at capturing intermediate values than the table differencing method. If the price in the product's table fluctuates, you will not 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 the last time that you captured change data. • This method is also no better than the table differencing method 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 rows. For example, suppose the following: o You capture data changes using change-value selection on a date column such as LAST_UPDATE_DATE. o The capture happens at a particular instant in time, 14-FEB-2003 17:10:00. o Additional updates occur to the table during the same second that you performed your capture. When you next capture data changes, you will select rows with a LAST_UPDATE_DATE strictly after 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 which you wish to capture change data must have a change-value column. If you want to build a data warehouse with data sources from legacy systems, those legacy systems may not supply the necessary change-value columns you need. Change Data Capture does not depend on expensive and cumbersome table differencing or change-value selection mechanisms. Instead, it captures the change data 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 controlled way. Capturing Change Data with Change Data Capture Change Data Capture can capture and publish committed change data in either of the following modes: • Synchronous Change 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 captured as part of the transaction modifying the source table. Synchronous Change Data Capture is available with Oracle Standard Edition and Enterprise Edition. • Asynchronous Change data is captured after a SQL statement that performs a DML operation is committed, by taking advantage of the data sent to the redo log files. In this mode, change data is not captured as part of the transaction that is modifying the source table, and therefore has no effect on that transaction. Asynchronous 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 Oracle Streams Concepts and Administration for information on Oracle Streams. The following list describes the advantages of capturing change data with Change Data Capture: • Completeness Change Data Capture can capture all effects of INSERT, UPDATE, and DELETE operations, including data values before and after UPDATE operations. • Performance Asynchronous 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 overhead 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. Publish and Subscribe Model Most Change Data Capture 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 subscribers. Change Data Capture provides PL/SQL packages to accomplish the publish and subscribe tasks. The following sections describe the roles of the publisher and subscriber in detail. Subsequent sections describe change sources, more about modes of Change Data Capture, and change tables. Publisher The publisher is usually a database administrator (DBA) who creates and maintains the schema objects that make up the Change Data Capture system. Typically, a publisher deals with two databases: • Source database This is the production database that contains the data of interest. Its associated tables are referred to as the source tables. • Staging database This is the database where the change data capture takes place. Depending on the capture mode that the publisher uses, the staging database can be the same as, or different from, the source database. The following Change Data Capture objects reside on the staging database: o 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. o Change set A change set is a set of change data that is guaranteed to be transactionally consistent. It contains one or more change tables. o Change source The change source is a logical representation of the source database. It contains one or more change sets. The publisher performs these tasks: • Determines the source databases and tables from which the subscribers are interested in viewing change data, and the mode (synchronous or asynchronous) in which to capture the change data. • Uses the Oracle-supplied package, DBMS_CDC_PUBLISH, to set up the system to capture change data from the source tables of interest. • Allows subscribers to have controlled access to the change data in the change tables by using the SQL GRANT and REVOKE statements to grant and revoke the SELECT privilege on change tables for users 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 interested 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 DW staging database, a change set, SH_SET, and two change tables: sales_ct and promo_ct. The 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 Description of the illustration pub_components.gif Subscribers The subscribers are consumers of the published change data. A subscriber performs the following tasks: • Uses the Oracle supplied package, DBMS_CDC_SUBSCRIBE, to: o 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 subscriber view is a view that specifies the change data from a specific publication in a subscription. The subscriber is restricted to seeing change data that the publisher has published and has granted the subscriber access to use. See "Subscribing to Change Data" for more information on choosing a method for specifying a subscriber view. o Notify Change Data Capture when ready to receive a set of change data A subscription window defines the time range 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 subscription has its own subscription window that applies to all of its subscriber views. o Notify Change Data Capture when finished with a set of change data • Uses SELECT statements to retrieve change data from the subscriber views. A subscriber has the privileges of the user account under which the subscriber is running, plus any additional privileges that have been granted to the subscriber. In Figure 16-2, the subscriber is interested in a subset of columns that the publisher (in Figure 16-1) has published. Note that the publications shown in Figure 16-2, are represented as change tables in Figure 16-1; this reflects the different terminology used by subscribers and publishers, respectively. The subscriber creates a subscription, 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 that 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 Subscriber Components in a Change Data Capture System Description of the illustration sub_components.gif Change Data Capture provides the following benefits for subscribers: • Guarantees that each subscriber sees all the changes • 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 Sources and Modes of Data Capture Change Data Capture provides synchronous and asynchronous 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. Synchronous This mode uses triggers on the source database to capture change data. It has no latency because the change data is captured continuously and in real time on the source database. The change tables are populated when DML operations on the source table are committed. While the synchronous mode of Change Data Capture adds overhead to the source database at capture 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 only 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 executed 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 Description of the illustration synch.gif Asynchronous This mode captures change data after the changes have been committed to the source database by using 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 adds 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: HotLog Change data is captured from the online redo log file on the source database. There is a brief latency between the act of committing source table transactions and the arrival of change data. There is a single, predefined HotLog change source, HOTLOG_SOURCE, that represents the current redo log files of the source database. 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 configuration. The Logwriter Process (LGWR) records committed transactions in the online redo log files on the source database. Change Data Capture 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. Figure 16-4 Asynchronous HotLog Configuration Description of the illustration hotlog.gif AutoLog 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. Change 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 provides information about the source database to create an AutoLog change source. See "Performing Asynchronous AutoLog Publishing" for details. Change sets for this mode of Change Data Capture can be remote from or local to the source database. Typically, they are remote. Figure 16-5 shows a typical Change Data Capture asynchronous AutoLog configuration in which, when the log switches on the source database, archiver processes archive the redo log file on the source database to the destination specified by the LOG_ARCHIVE_DEST_1 parameter and copy the redo log file to the staging database 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.) Note that the archiver processes use Oracle Net to send redo data over the network to the remote file server (RFS) process. Transmitting redo log files to a remote destination requires uninterrupted connectivity through Oracle Net. On the staging database, 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, Change 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 Description of the illustration autolog.gif Change Sets A change set is a logical grouping of change data that is guaranteed to be transactionally consistent and that 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 change 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 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-transaction basis from the current online redo log file. Publishers define change sets in the predefined HOTLOG_SOURCE change source. • Asynchronous AutoLog 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 tables 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. Valid Combinations of Change Sources and Change Sets 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 represents a local or remote source database, and whether the change source is used for synchronous or asynchronous Change Data Capture. 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 HOTLOG_SOURCE Local Publisher-defined Asynchronous AutoLog Publisher-defined Remote or local Publisher-defined Note: Although the AutoLog source database and 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. Change Tables 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 columns. The publisher specifies the source columns that are to be included in the change table. Typically, for a change table to contain 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 UNIT_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 determine for which products these changes have occurred. There are optional and required control columns. The required control columns are always included in a change table; the optional ones are included if specified by the publisher when creating the change table. 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. Getting Information About the Change Data Capture Environment 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 lists the views that are intended for use by publishers; the user must have the SELECT_CATALOG_ROLE privilege to access the views listed in this table. Table 16-3 lists the views that are intended 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 information 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 user issuing the query without the use of additional special privileges or roles. Note: To look at all the views (those intended for both the publisher and the subscriber), a user must have the SELECT_CATALOG_ROLE privilege. Table 16-2 Views Intended for Use by Change Data Capture Publishers View Name Description CHANGE_SOURCES Describes existing change sources. 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 Describes 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. Table 16-3 Views Intended for Use by Change Data Capture Subscribers View Name Description ALL_SOURCE_TABLES Describes all existing source tables accessible to the current user. USER_SOURCE_TABLES Describes all existing source tables owned by the current user. ALL_PUBLISHED_COLUMNS 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 all the subscriptions owned by the current user. ALL_SUBSCRIBED_TABLES Describes the source tables to which any subscription accessible to the current user has subscribed. USER_SUBSCRIBED_TABLES Describes the source tables to which the current user has subscribed. ALL_SUBSCRIBED_COLUMNS Describes the columns of source tables to which any subscription 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 information about these views. Preparing to Publish Change Data This section describes the tasks the publisher should perform before starting to publish, information 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. • Determine which source database contains the relevant source tables. • Choose the capture mode: synchronous, asynchronous HotLog, or asynchronous AutoLog, as described in "Determining the Mode in Which to Capture Data". • Ensure that the source and staging database DBAs have set database initialization parameters, as described in "Setting Initialization Parameters for Change Data Capture Publishing" and "Publishing Change Data". Creating a User to Serve As a Publisher Typically, a DBA creates a user to serve as a publisher for Change Data Capture. The following sections describe the tasks involved. Granting Privileges and Roles to the Publisher On the staging database, the publisher must be granted the privileges and roles in the following list: • EXECUTE_CATALOG_ROLE privilege • SELECT_CATALOG_ROLE privilege • CREATE TABLE and CREATE SESSION privileges • CONNECT and RESOURCE roles In addition, for asynchronous HotLog and AutoLog publishing, the publisher must: • Be granted the CREATE SEQUENCE privilege • Be granted the DBA role • Be the GRANTEE specified in a DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE() subprogram issued by the staging database DBA In other words, for asynchronous publishing, the publisher should be configured as an Oracle Streams administrator. See Oracle Streams Concepts and Administration for information on configuring an Oracle Streams administrator. Creating a Default Tablespace for the Publisher When creating the publisher account, the DBA should also consider specifying a default tablespace for the publisher in which he or she can create change tables. Otherwise, Oracle recommends that the publisher always specify a tablespace that he or she owns when creating a change table. To create a tablespace, the publisher will need the following privileges: • CREATE TABLESPACE • UNLIMITED TABLESPACE Password Files and Setting the REMOTE_LOGIN_PASSWORDFILE Parameter For asynchronous AutoLog publishing only, the REMOTE_LOGIN_PASSWORDFILE database initialization parameter must be set to SHARED on both the source and staging databases, 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. Determining the Mode in Which to Capture Data Three factors influence the decision on the mode in which to capture change data: • Whether or not the staging database is remote from the source database • Tolerance for latency between changes made on the source database and changes captured by Change Data 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 Mode Location of Staging Database Latency Source Database Performance Impact Synchronous Must be the same as the source database. None - change data is automatically committed as part of the same transaction it reflects. Adds overhead to source database transactions to perform change data capture. Asynchronous HotLog Must be the same as the source database. Change data is captured from the current online redo log file. Change sets are populated automatically as new committed transactions arrive. Minimal impact on source database transactions to perform supplemental logging. Additional source database overhead to perform change data capture. Asynchronous AutoLog Typically remote from the source database. Depends on the frequency of redo log switches on the source database. Change sets are populated automatically as new log files arrive. Minimal impact on source database transactions to perform supplemental logging. Minimal source database overhead for log transport services. When the source database is remote from the staging database, then this mode has the least impact on the source database. Setting Initialization Parameters for Change Data Capture Publishing 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 parameter 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 that parameter is not explicitly specified in the initialization parameter file. See Oracle Database Reference and Oracle Data Guard Concepts and Administration for more information about these database initialization parameters. Initialization Parameters for Synchronous Publishing Set the JAVA_POOL_SIZE parameter as follows: JAVA_POOL_SIZE = 50000000 Initialization Parameters for Asynchronous HotLog Publishing 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 Asynchronous HotLog Publishing Parameter Recommended 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 * (the number of change sets planned)) STREAMS_POOL_SIZE • If the current value of the STREAMS_POOL_SIZE parameter is 50 MB or greater, then set this parameter to: (current value) + ((the number of change sets planned) * (21 MB)) • If the current value of the STREAMS_POOL_SIZE parameter is less than 50 MB, then set the value of this parameter to: 50 MB + ((the number of change sets planned) * (21 MB)) See Oracle Streams Concepts and Administration for information on how the STREAMS_POOL_SIZE parameter is applied when changed dynamically. UNDO_RETENTION 3600 Initialization Parameters for Asynchronous AutoLog Publishing Table 16-6 lists the database initialization parameters and their recommended settings for the asynchronous AutoLog publishing source database and Table 16-7 lists the database initialization parameters and their recommended settings for the asynchronous AutoLog publishing staging database. Table 16-6 Source Database Initialization Parameters for Asynchronous AutoLog Publishing Parameter Recommended Value COMPATIBLE 10.1.0 JAVA_POOL_SIZE 50000000 LOG_ARCHIVE_DEST_1Foot 1 The directory specification 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 SERVICE, ARCH or LGWR ASYNC, OPTIONAL, NOREGISTER, and REOPEN attributes so that log transport services are configured to copy the redo log files from the source database to the staging database. These attributes are set as follows: • SERVICE specifies the network name of the staging database. • ARCH or LGWR ASYNC ARCH specifies that the archiver process (ARCn) copy the redo log files to the staging database after a source database log switch occurs. LGWR ASYNC specifies that the log writer process (LGWR) copy redo data to the staging database as the redo is generated on the source database. Note that, the copied redo data becomes available to Change Data Capture only after a source database log switch occurs. • OPTIONAL specifies that the copying of a redo log file to the staging database need not succeed before the corresponding online redo log at the source database can be overwritten. This is needed to avoid stalling operations on the source database due to a transmission failure to the staging database. The original redo log file remains available to the source database in either archived or backed up form, if it is needed. • NOREGISTER specifies that the staging database location is not recorded in the staging database control file. • REOPEN specifies the minimum number of seconds the archiver process (ARCn) should wait before trying to access the staging database if a previous attempt to access this location failed. • TEMPLATE defines a directory specification and a format template for the file name used for the redo log files that are copied to the staging database.Foot 2 LOG_ARCHIVE_DEST_STATE_1Footref 1 ENABLE Indicates that log transport services can 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. LOG_ARCHIVE_FORMAT "arch1_%s_%t_%r.dbf" Specifies a format template for the default file name when archiving redo log files.Footref 2 The string value (arch1) and the file name extension (.dbf) do not have to be exactly as specified here. REMOTE_LOGIN_PASSWORDFILE SHARED Footnote 1 The integer value in this parameter can be any value between 1 and 10. In this manual, the values 1 and 2 are used. For each LOG_ARCHIVE_DEST_n parameter, there must be a corresponding LOG_ARCHIVE_DEST_STATE_n parameter that specifies the same value for n. Footnote 2 In the format template, %t corresponds to the thread number, %s corresponds to the sequence number, and %r corresponds to the resetlogs ID. Together, these ensure that unique names are constructed for the copied redo log files. Table 16-7 Staging Database Initialization Parameters for Asynchronous AutoLog Publishing Parameter Recommended Value COMPATIBLE 10.1.0 GLOBAL_NAMES TRUE 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)) REMOTE_LOGIN_PASSWORDFILE SHARED SESSIONS (current value)+ (2 * (the number of change sets planned)) STREAMS_POOL_SIZE • If the current value of the STREAMS_POOL_SIZE parameter is 50 MB or greater, then set this parameter to: (current value) + ((the number of change sets planned) * (21 MB)) • If the current value of the STREAMS_POOL_SIZE parameter is less than 50 MB, then set the value of this parameter to: 50 MB + ((the number of change sets planned) * (21 MB)) See Oracle Streams Concepts and Administration for information on how the STREAMS_POOL_SIZE parameter is applied when changed dynamically. UNDO_RETENTION 3600 Note: If Oracle Streams capture parallelism and apply parallelism values are increased after the change sets are created, the source or staging database DBA (depending on the mode of capture) must adjust initialization parameter values as described in "Adjusting Initialization Parameter Values When Oracle Streams Values Change". Determining the Current Setting of an Initialization Parameter 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 current value. The current settings of all initialization parameters for the database are displayed when the following SQL statement is issued: SQL> SHOW PARAMETERS The current setting of a particular initialization parameter can be displayed, in this case, STREAMS_POOL_SIZE, using a SQL statement such as the following: SQL> SHOW PARAMETER STREAMS_POOL_SIZE Retaining Initialization Parameter Values When a Database Is Restarted To ensure that changed initialization 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 automatically 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 REMOTE_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_POOL_SIZE dynamically, he or she should place the parameter in the database initialization parameter file and restart the database. 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 database is restarted. Adjusting Initialization Parameter Values When Oracle Streams Values Change 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 processes, parallel execution servers, and memory. Oracle Streams capture and apply processes each have a parallelism parameter that is 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 capture parallelism and apply parallelism values are increased after the change sets are created, the staging database DBA must adjust initialization 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 parallelism value is 1, so the amount by which a given parallelism value has been increased is the returned value minus 1. Example 16-1 Obtaining the Oracle Streams Capture Parallelism Value SELECT cp.value FROM DBA_CAPTURE_PARAMETERS cp, CHANGE_SETS 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 = 'CHICAGO_DAILY' and cset.APPLY_NAME = ap.APPLY_NAME and ap.parameter = 'PARALLELISM'; The staging database DBA must adjust the staging database initialization parameters as described in the following list to accommodate the 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 increased, increase the value of this parameter by the sum of increased Streams parallelism values. 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 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 change sets, increases for those change sets must also be made. • PROCESSES For each change 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 parallelism 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 STREAMS_POOL_SIZE parameter by (10 MB * (2-1) + 1MB * (3-1)), or 12MB for the CHICAGO_DAILY change set. If the Oracle Streams capture or apply parallelism values have increased for other change sets, increases for those change sets must also be made. 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. Publishing Change Data The following sections provide step-by-step instructions on performing the various types of publishing: • Performing Synchronous Publishing • Performing Asynchronous HotLog Publishing • Performing Asynchronous AutoLog Publishing Performing Synchronous Publishing For synchronous Change Data Capture, the publisher must use the predefined change source, SYNC_SOURCE. The publisher can define new change sets or can use the predefined change set, SYNC_SET. The publisher should not create change tables on source tables owned by SYS or 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 and 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, as described in "Setting Initialization Parameters for Change Data Capture Publishing". java_pool_size = 50000000 Step 2 Source Database DBA: Create and 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 "Creating 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 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 CONNECT, RESOURCE TO cdcpub; Step 3 Staging Database Publisher: Create a change set. The publisher uses the DBMS_CDC_PUBLISH.CREATE_CHANGE_SET procedure 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 job history info', change_source_name => 'SYNC_SOURCE'); END; / The change set captures changes from the predefined change source SYNC_SOURCE. Because begin_date and end_date parameters cannot be specified for synchronous change sets, capture begins at the earliest available change data and continues capturing change data indefinitely. Step 4 Staging Database Publisher: Create a change table. The publisher uses the DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE procedure to create change tables. The publisher can set the options_string field of the DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE procedure to have more control over the physical properties and tablespace properties of the change table. The options_string field can contain any option, except partitioning, that is available in the CREATE TABLE statement. 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_DAILY tablespace. 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,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 => 'y', target_colmap => 'y', options_string => 'TABLESPACE TS_CHICAGO_DAILY'); END; / This statement creates a change table named jobhist_ct within the change set CHICAGO_DAILY. The column_type_list parameter identifies the columns captured by the change table. The source_schema and source_table parameters identify the schema and source table that reside in the source 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 occurred. Step 5 Staging Database Publisher: Grant access to subscribers. The publisher controls subscriber access to change data by granting and revoking 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 any change data. This example assumes that user subscriber1 already exists. GRANT SELECT ON cdcpub.jobhist_ct TO subscriber1; The Change Data Capture synchronous system is now ready for subscriber1 to create subscriptions. Performing Asynchronous HotLog Publishing 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 asynchronous HotLog Change Data Capture, the publisher must use the predefined change source, HOTLOG_SOURCE, and must create the change sets and the change tables that will contain the changes. The staging database is always the source database. This example 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 the 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 parameter 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 sessions = <current value> + 2 streams_pool_size = <current value> + 21 MB undo_retention = 3600 Step 2 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 recommended. It is assumed that the database is currently running in ARCHIVELOG mode. 1. Place the database into FORCE LOGGING logging mode to protect against unlogged direct write operations in the source database that cannot be captured by asynchronous Change Data Capture: 2. ALTER DATABASE FORCE LOGGING; 3. 4. Enable supplemental logging. Supplemental logging places additional column data into a redo log file whenever an UPDATE operation is performed. Minimally, database-level minimal supplemental logging must be enabled for any Change Data Capture source database: 5. ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 6. 7. 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 created.) 8. ALTER TABLE HR.JOB_HISTORY 9. ADD SUPPLEMENTAL LOG GROUP log_group_jobhist 10. (EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID) ALWAYS; 11. 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_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 supplemental logging. Step 3 Source Database DBA: Create and 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 underlying Oracle Streams 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 been created. For example: CREATE USER cdcpub IDENTIFIED BY cdcpub DEFAULT TABLESPACE ts_cdcpub QUOTA UNLIMITED 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 CREATE 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. Step 4 Source Database DBA: Prepare the source tables. The source database DBA must prepare the source tables on the source database for asynchronous Change Data Capture by instantiating each source 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_PUBLISH.CREATE_CHANGE_SET procedure on the staging database to create change sets. Note that when Change Data Capture creates a change set, its associated Oracle Streams capture and apply processes are also created (but not started). The following example creates a change set called CHICAGO_DAILY that captures changes starting today, and stops capturing change data 5 days from 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 predefined 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 creates 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 staging database that captures changes made to a source table on the source database. The example uses the sample table HR.JOB_HISTORY 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 schema and source table that reside on the source database. The capture_values setting in this statement 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 occurred. The options_string parameter in this statement specifies a tablespace for the change table. (This example assumes that the publisher previously created the TS_CHICAGO_DAILY tablespace.) Step 7 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 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 Publisher: Grant access to subscribers. The publisher controls subscriber access to change data by granting and revoking 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 HotLog system is now ready for subscriber1 to create subscriptions. Performing Asynchronous AutoLog Publishing Change Data Capture uses Oracle Streams downstream capture to perform asynchronous AutoLog publishing. The Change Data Capture staging database is considered a downstream database in the Streams environment. See Oracle Streams Concepts and Administration for information on Streams downstream capture. For asynchronous AutoLog Change Data Capture, the publisher creates new change sources, as well as the change sets and the change tables that will contain the changes that are made to individual source tables. For AutoLog Change Data Capture, the staging database is usually remote from the source database. Steps must be performed on both the source database and the staging database to set up redo logging, Streams, 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 copy redo log files from the source database to the staging database and to prepare the staging database to receive these redo log files, as follows: 1. The source database DBA configures Oracle Net so that the source database can communicate with the staging database. (See Oracle Net Services Administrator's Guide for information about Oracle Net). 2. The source database DBA sets the database initialization 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: 3. compatible = 10.1.0 4. java_pool_size = 50000000 5. log_archive_dest_1="location=/oracle/dbs mandatory reopen=5" 6. log_archive_dest_2 = "service=stagingdb arch optional noregister reopen=5 7. template = /usr/oracle/dbs/arch1_%s_%t_%r.dbf" 8. log_archive_dest_state_1 = enable 9. log_archive_dest_state_2 = enable 10. log_archive_format="arch1_%s_%t_%r.dbf" 11. remote_login_passwordfile=shared 12. See Oracle Data Guard Concepts and Administration for information on log transport services. Step 2 Staging Database DBA: Set the database initialization parameters. The staging database DBA sets the database initialization parameters on the staging database, as described in "Setting Initialization Parameters for Change Data Capture Publishing". In this example, one change set will be defined and the current value for the STREAMS_POOL_SIZE parameter is 50 MB or greater: compatible = 10.1.0 global_names = true java_pool_size = 50000000 job_queue_processes = 2 parallel_max_servers = <current_value> + 5 processes = <current_value> + 7 remote_login_passwordfile = 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 recommended. It is assumed that the database is currently running in ARCHIVELOG mode. 1. Place the database into FORCE LOGGING logging mode to protect against unlogged direct writes in the source database that cannot be captured by asynchronous Change Data Capture: 2. ALTER DATABASE FORCE LOGGING; 3. 4. Enable supplemental logging. Supplemental logging places additional column data into a redo log file whenever an update operation is performed. 5. ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 6. 7. 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 created). 8. ALTER TABLE HR.JOB_HISTORY 9. ADD SUPPLEMENTAL LOG GROUP log_group_job_hist 10. (EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID) ALWAYS; 11. 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_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 supplemental 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 the underlying Oracle Streams operations needed to create Change Data Capture change sources, change sets, and change tables on the staging 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 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 CONNECT, RESOURCE, DBA TO cdcpub; GRANT CREATE 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 transport this data dictionary to the staging database. This LogMiner data dictionary build provides the table definitions as they were 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 always 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('Th [Ýòî ñîîáùåíèå - ñïàì!] Ïîñëåäíèå ñîîáùåíèÿ èç ôîðóìà 28868 |
| Copyright Apache.ru © 1999-2017, All Rights Reserved | Ðàçðàáîòêà ñàéòà: Inside.ru | ||
| ÐÅÊËÀÌÀ ÍÀ ÑÀÉÒÅ: | | |||