The Oracle LogMiner API Capture Agent configuration (.cab) file is created and maintained by the SQDCONF utility. While this section focuses primarily on the initial configuration of the Capture Agent, sequences of SQDCONF commands to create and configure the capture agent can/should be stored in shell scripts. See SQDCONF Utility Reference for a full explanation of each command, their respective parameters and the utility's operational considerations.
$ sqdconf create <cab_file_name>
--type=oracle
--database=<database_name>
[--user=<name>]
[--password=<value>]
[--passcmd=<filename>]
[--encryption | --no-encryption]
[--auth-keys-list="<name>"]
[--enable-filter-no-change | --disable-filter-no-change]
[--exclude-user=<value>]
--store=<store_cab_file_name>| Keyword | Description |
|---|---|
| <cab_file_name> |
Path and name of the Capture/Publisher Configuration (.cab) file. The directory must exist and the user-id associated with the agent must have the right to create and delete files in that directory. Precisely recommends including the Capture Agent alias as the third node in the directory structure and first node of the file name, for example, /home/sqdata/oracdc/oracdc.cab. |
| --type=<type> | Agent type, in the case of the Oracle LogMiner API Capture, is "oracle". |
| --database=<database> | The Oracle database name. |
| [--user=<name>] |
User name/id required to connect to the underlying database. This user must have the authority to perform log capture/log mining on the underlying database. If Oracle Wallet is being used for external authentication, this parameter is required but the <name> may be any string of characters. |
| [--password=<value>] |
Plain text passwords are exposed in the cab file. DO NOT use plain text passwords. Precisely highly recommends using Oracle Wallet or The Oracle User Password. If NO --password parameter is specified, the capture will attempt to connect using external authentication which by default will be Oracle Wallet, unless the following Parameter is supplied. |
| [--passcmd='./getpassword.sh'] |
A script that will return the user password. |
| [--encryption | --no-encryption] |
Enables or disables NaCL encryption of the published CDC record payload. |
| [--auth-keys-list="<name>"] |
Required for NaCL encrypted CDC record payload. File name must be enclosed in quotes and must contain public key(s) of only the subscribing Engines requiring encryption of the CDC record payload. See --encryption option. |
| [--exclude-user=<name>] | Exclude transactions associated with the given user name from capture. This parameter can be repeated multiple time. |
| --store=<store_cab_file_name> |
Path and name of the Storage Agent Configuration (.cab) file. - Path and name of the Storage Agent Configuration (.cab) file. For example, /home/sqdata/oracdc/oracdc_store.cab |
While the default for a newly created capture remains, --dict-from-online-catalog to ensure that capture can simply be initiated, when using LogMiner to read the Oracle logs in production, you must specify --dict-from-redo-logs, to ensure that the data in the redo log can be interpreted when the number of archive logs grows and capture for some reason lags and also to ensure that capture can be restarted if it becomes necessary to recapture changes for any reason.
Some parameters used by the Oracle Capture cannot be specified on the "create" action and must be specified using "modify" after the "create".
sqdconf modify <cab_file_name>
[--dict-from-online-catalog | --dict-from-redo-logs]
[--disable-continuous-mining]| Keyword | Description |
|---|---|
| [--dict-from-online-catalog | --dict-from-redo-logs] |
This parameter specifies the source of the Oracle dictionary use by the capture. If this parameter is not specified the default is --dict-from-online-catalog. The method of loading the data dictionary can be toggled via the sqdconf modify command. After the modify, an sqdconf apply is required, and then the capture must be restarted with the new method of loading the dictionary. The sqdconf display command can be used to confirm that the redo logs are being used for the dictionary:
|
| [--disable-continuous-mining] |
Oracle RAC capture only. This option instructs the Capture Agent not to use the Oracle LogMiner CONTINUOUS_MINE option which is not supported by Oracle 11 or 12 on a RAC system. The --disable-continuous-mining option is the default for Oracle version 19 on both RAC and non-RAC systems. |
Next, the configuration file must be updated by adding an entry for each table to be captured using the add command. Onlyonetable andoneassociated datastore (target subscription) can be added at a time. Precisely highly recommends keeping a Recover/Recreate configuration file Job or shell script available should circumstances require recovery.
Add each source table to the list of source tables to be captured in the Capture Configuration (.cab) file. Each table is identified by its name and schema. A datastore representing a single target subscription must be specified for each table added. Additional target subscriptions can be "added" but that is performed with the modify command It is important to develop a standard for how Datastores will be identified, particularly if a large number will be defined. The source is marked inactive by default but remember, even sources marked active will not be captured until changes to the configuration file are applied.
sqdconf add <cab_file_name>
--schema=<name> --table=<name> | --key=<name>
--datastore=<url>
[--active | --inactive]
[--pending]| Keyword | Description |
|---|---|
| <cab_file_name> | Must be specified and must match the name specified in the previous create command. |
| --schema=<name> | Schema name, owner, or qualifier of a table. Different databases use different semantics, but a table is usually uniquely identified as S.T where S is referenced here as schema. This parameter cannot be specified with --key. |
| --table=<name> |
A qualified table name in the form of schema.name that identifies the source. This may be used in place of two parameters, --schema and --table. Both cannot be specified. |
| --key=<name> | Same as --table |
| --datastore=<url> | -d <url> | While most references to the term datastore describe physical entities, a datastore URL represents a target subscription and takes the form: cdc://[localhost]/[<agent_alias>]/<subscriber_name> where:
|
| [--active | --inactive] | Mark a table as active or in-active for capture. The table will remain in the current state until the capture is stopped, applied and re-started. The default is --inactive. |
| [--pending] | This parameter allows a table to be added to the configuration before it exists in the database catalog. |
Example
$ sqdconf create /home/sqdata/oracdc1/oracdc1.cab
--type=oracle --database=<database_name>
--store=oracdc1_store.cab
$ sqdconf add /home/sqdata/oracdc1/oracdc1.cab
--table=SQDATA.DEPT --datastore=cdc:////ORATOORA --active
$ sqdconf add /home/sqdata/oracdc1/oracdc1.cab
--table=SQDATA.EMP --datastore=cdc:////ORATOORA --active
$ sqdconf display /home/sqdata/oracdc/oracdc1.cab- The sqdconf create command defines the location of the Capture agent's configuration CAB file. Once created, this command should never be run again unless you want to destroy and recreate the Capture agent.
- Oracle does not support the LogMiner CONTINUOUS_MINE option on Oracle RAC systems and must be explicitly disabled for Oracle Version 11 and 12. It is the default for Oracle 19 which does not support CONTINUOUS_MINE on either RAC or non-RAC systems.
- Destroying the Capture agent cab file means that the current position in the log and the relative location of each engine's position in the Log will be lost. When the Capture agent is brought back up it will start from the beginning of the oldest active log and will resend everything. After initial configuration, changes in the form of add and modify commands should be used instead of the create command. Note that you cannot delete a cab file if the Capture is mounted and a create on an existing configuration file will fail.
- There must be a separate ADD command executed for every source table to be captured.
- The command will fail if the same table is added more than one time for the same Target Datastore/Engine. See section below "Adding/Removing Output Datastores".
- The <subscriber_name> is case sensitive in that all references should be either upper or lower case. Because references to the "Engine" in z/OS JCL must be upper case, references to the Engine in these examples are all in upper case for consistency.
- A display command when run against an active configuration (.cab) file will output a variety of information including:
- The current status of the table (i.e. active, inactive)
- The starting and current point in the log where data has been captured
- The number of inserts, updates and deletes for the session (i.e. the duration of the capture agent run)
- The number of inserts, updates and deletes since the creation of the configuration file