1 Preparing the Database for Oracle GoldenGate
Learn how to prepare your database for Oracle GoldenGate, including how to configure connections and logging, how to enable Oracle GoldenGate in your database, how to set the flashback query, and how to manage server resources.
Topics:
- Enabling Oracle GoldenGate in the Database
The database services required to support Oracle GoldenGate capture and apply must be enabled explicitly for all Oracle database versions. This is required for Extract and all Replicat modes. - Configuring Connections for Extract and Replicat Processes
Extract and Replicat require a dedicated server connection in thetnsnames.ora
file. - Setting Flashback Query
To process certain update records, Extract fetches additional row data from the source database. - Managing Server Resources
Extract interacts with an underlying logmining server in the source database and Replicat interacts with an inbound server in the target database. This section provides guidelines for managing the shared memory consumed by the these servers. - Ensuring Row Uniqueness in Source and Target Tables
Oracle GoldenGate requires a unique row identifier on the source and target tables to locate the correct target rows for replicated updates and deletes. - Configuring Logging Properties
Oracle GoldenGate relies on the redo logs to capture the data that it needs to replicate source transactions. - Using the Extract Automated Capture Mode
The automated capture (auto capture) mode allows automatically capturing the tables that have been enabled for Oracle GoldenGate auto capture. - Additional Oracle GoldenGate Configuration for Your Database
This chapter contains additional configuration considerations that may apply to your database environment.
Enabling Oracle GoldenGate in the Database
The database services required to support Oracle GoldenGate capture and apply must be enabled explicitly for all Oracle database versions. This is required for Extract and all Replicat modes.
To enable Oracle GoldenGate, set the following database initialization parameter. All instances in Oracle RAC must have the same setting.
ENABLE_GOLDENGATE_REPLICATION=true
This parameter alters the DBA_FEATURE_USAGE_STATISTICS
view. For
more information about this parameter, see Initialization Parameters.
Parent topic: Preparing the Database for Oracle GoldenGate
Configuring Connections for Extract and Replicat Processes
Extract and Replicat require a dedicated server connection in the
tnsnames.ora
file.
Before you begin, make sure that there is a dedicated user on the Oracle database side, with the required privileges. See Granting User Privileges for Oracle Database 21c and Lower.
On the Oracle GoldenGate side, you direct the Extract and Replicat processes to use
these connections by specifying the values for USERID
or
USERIDALIAS
parameter in the Extract and Replicat parameter files.
The following are the security options for specifying the connection string in the Extract or Replicat parameter file.
Credential store method:
USERIDALIAS ggeast
In the case of USERIDALIAS
, the alias ggeast
is
stored in the Oracle GoldenGate credential store with the actual connection string. The
following example uses the INFO CREDENTIALSTORE
command to display the
details of the credentials configured in Oracle GoldenGate:
INFO CREDENTIALSTORE DOMAIN OracleGoldenGate
Output:
Domain: OracleGoldenGate
Alias: ggeast
Userid: ggadmin@dc1.example.com:1521/DBEAST.example.com
Setting up a Bequeath connection
Oracle GoldenGate can connect to a database instance without using
the network listener if a Bequeath connect descriptor is added in the
tnsnames.ora
.
The following example shows the configuration for connecting to a database using Bequeath connect descriptor:
dbbeq = (DESCRIPTION=
(ADDRESS=(PROTOCOL=beq)
(ENVS='ORACLE_SID=sales,ORACLE_HOME=/app/db_home/oracle,LD_LIBRARY_PATH=/app/db_home/oracle/lib')
(PROGRAM=/app/db_home/oracle/bin/oracle)
(ARGV0=oraclesales)
(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))
(CONNECT_DATA=(SID=sales)))
In this example:
/app/db_home
is the target Oracle database installation directory
sales
is the database service name
ORACLE_SID
, ORACLE_HOME
, and
LD_LIBRARY_PATH
in the ENVS
parameter refers to the
target.
Note:
Make sure that there is no white space between these environment variable settings.Parent topic: Preparing the Database for Oracle GoldenGate
Granting User Privileges for Oracle Database 21c and Lower
The user privileges that are required for connecting to Oracle database from Oracle GoldenGate depend on the type of user.
The user privileges that are required for connecting to Oracle database from Oracle GoldenGate depend on the type of user.
Privileges should be granted depending on the actions that the user
needs to perform as the GoldenGate Administrator User on the source and target
databases. For example, to grant DML operation privileges to insert, update, and
delete transactions to a user, use the GRANT ANY
INSERT
/UPDATE
/DELETE
privileges and
to further allow users to work with tables and indexes as part of DML operations,
use the GRANT CREATE
/DROP
/ALTER ANY
TABLE
/INDEX
privileges.
If the GoldenGate Administrator user has the DBA role, additional object privileges are not needed. However, there might be security constraints granting the DBA role to the GoldenGate Administration user. The DBA role is not necessarily required for Oracle GoldenGate.
If there are many objects being replicated, you might consider using the ANY privilege for DML and DDL operations. This simplifies the provision of privileges to the GoldenGate Administrator users, as you only need to grant a few privileges depending on the database operations.
The following table describes some of the essential privileges for
GoldenGate Administrator user for Oracle database. For explanation purposes, the
table uses c##ggadmin
as an example of a common user for a
multitenant container database and ggadmin
as the pluggable
database (PDB) user. PDBEAST
and PDBWEST
are used
as examples of PDB names.
Privilege | Extract | Replicat All Modes | Purpose |
---|---|---|---|
|
Yes |
Yes |
Required to create objects In Oracle Database 12cR1 and later, instead of
|
|
Yes |
Yes |
Common user See an example of Example: Grant privileges using the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE package. |
|
Yes |
Yes |
Required to connect to the database. |
|
Yes |
Yes |
Required to add the heartbeat table view. If you want to be specific to each object, you can also provide the privileges for each object individually. You may consider creating a specific database role to maintain such privileges. |
|
Yes |
Yes |
Perform administrative changes, such as enabling logging. |
|
Yes |
Yes |
Required for multitenant architecture and
|
|
Yes | Yes |
|
Grant |
Yes |
Yes |
Capture from Data Vault. See Privileges for Capturing from Oracle Data Vault. |
Grant Replicat privileges in
|
NA | Yes |
Capture from Data Vault. See Privileges for Capturing from Oracle Data Vault. |
|
NA |
Yes |
Apply replicated DML to target objects. See Details of Support for Objects and Operations in Oracle DML |
|
NA |
Yes |
Grant these privileges to the Replicat user,
instead of granting |
If DDL replication is performed, grant the following as Database Vault owner:
|
No | No |
Capture from Data Vault. See Privileges for Capturing from Oracle Data Vault. |
DDL privileges on target objects (if using DDL support) |
NA |
Yes |
Issue replicated DDL on target objects. See Details of Support for Objects and Operations in Oracle DDL. |
|
Yes | Yes | Grants privileges for DDL Replication for tables. |
|
Yes |
Yes |
Grants privileges for creating table in any
schema. To allow creating tables only in a specific schema,
use the |
CREATE ANY
VIEW |
Yes |
Yes | Grants privilges to create view in
any database schema. To allow creating views in a specific
schema, use the CREATE VIEW privilege.
|
|
Yes |
Yes |
Allow all privileges to work properly on dictionary tables. |
Example: Permissions granted for the Oracle database common user
c##ggadmin
in the following
example:CREATE USER c##ggadmin IDENTIFIED BY passw0rd CONTAINER=all DEFAULT
TABLESPACE GG_DATA TEMPORARY TABLESPACE temp;
GRANT RESOURCE to c##ggadmin;
GRANT CREATE SESSION to c##ggadmin;
GRANT CREATE VIEW to c##ggadmin;
GRANT CREATE TABLE to c##ggadmin;
GRANT CONNECT to c##ggadmin CONTAINER=all;
GRANT DV_GOLDENGATE_ADMIN; –-- for data vault user
GRANT DV_GOLDENGATE_REDO_ACCESS; –-- for data vault user
GRANT ALTER SYSTEM to c##ggadmin;
GRANT ALTER USER to c##ggadmin;
ALTER USER c##ggadmin SET CONTAINER_DATA=all CONTAINER=current;
ALTER USER c##ggadmin QUOTA unlimited ON GG_DATA;
GRANT SELECT ANY DICTIONARY to c##ggadmin;
GRANT SELECT ANY TRANSACTION to c##ggadmin;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('c##ggadmin');
In this example, DBA privilege is not provided but the user will be able
to access the DBA_SYS_PRIVS
package, if required.
ggadmin
are provided in
the following example:
Example: Grant privileges using the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE package
This procedure grants the privileges needed by a user to be an Oracle GoldenGate administrator The following example grants explicit privileges for Extract on Oracle multitenant database:
BEGIN
DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE
(GRANTEE => 'c##ggadmin', PRIVILEGE_TYPE => 'CAPTURE', GRANT_SELECT_PRIVILEGES => TRUE, DO_GRANTS => TRUE, CONTAINER => 'ALL' );
END;
See DBMS_GOLDENGATE_AUTH
in Oracle Database PL/SQL
Packages and Types Reference for more information.
Privileges for Capturing from Oracle Data Vault
Grant the following privileges connected as SYS
user in
Oracle database. These privileges are set for Extract and Replicat user credentials:
-
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE ('userID','*', GRANT_OPTIONAL_PRIVILEGES=>'*'); GRANT DV_GOLDENGATE_ADMIN, DV_GOLDENGATE_REDO_ACCESS to userID;
-
Grant Replicat the privileges in
DBMS_MACADM.ADD_AUTH_TO_REALM
if applying to a realm.Connect as Database Vault owner and execute the following scripts:BEGIN DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM( REALM_NAME => 'Oracle Default Component Protection Realm',GRANTEE => 'userID',AUTH_OPTIONS => 1) ; END ; / EXECUTE_DBMS_MACADM.AUTHORIZE_DDL('SYS', 'SYSTEM');
-
For DDL replication, grant the following as the Database Vault owner:
EXECUTE DBMS_MACADM.AUTHORIZE_DDL (‘userID', ‘SCHEMA FOR DDL’);
Setting Flashback Query
To process certain update records, Extract fetches additional row data from the source database.
Oracle GoldenGate fetches data for the following:
-
User-defined types
-
Nested tables
-
XMLType objects
By default, Oracle GoldenGate uses Flashback Query to fetch the values from the undo (rollback) tablespaces. That way, Oracle GoldenGate can reconstruct a read-consistent row image as of a specific time or SCN to match the redo record.
For best fetch results, configure the source database as follows:
Oracle GoldenGate provides the following parameters to manage fetching.
Parameter or Command | Description |
---|---|
|
Shows Extract fetch statistics on demand. |
|
Sets the |
|
Controls the number of open cursors for prepared queries that Extract maintains in the source database, and also for |
|
Controls the default fetch behavior of Extract: whether Extract performs a flashback query or fetches the current image from the table. |
|
Handles the failure of an Extract flashback query, such as if the undo retention expired or the structure of a table changed. Extract can fetch the current image from the table or ignore the failure. |
|
Controls the response by Replicat when it processes trail records that include fetched data or column-missing conditions. |
Parent topic: Preparing the Database for Oracle GoldenGate
Managing Server Resources
Extract interacts with an underlying logmining server in the source database and Replicat interacts with an inbound server in the target database. This section provides guidelines for managing the shared memory consumed by the these servers.
The shared memory that is used by the servers comes from the Streams pool portion of the System Global Area (SGA) in the database. Therefore, you must set the database initialization parameter STREAMS_POOL_SIZE
high enough to keep enough memory available for the number of Extract and Replicat processes that you expect to run in integrated mode. Note that Streams pool is also used by other components of the database (like Oracle Streams, Advanced Queuing, and Datapump export/import), so make certain to take them into account while sizing the Streams pool for Oracle GoldenGate.
By default, one Extract requests the logmining server to run with
MAX_SGA_SIZE
of 1GB. Thus, if you are running three Extracts in
the same database instance, you need at least 3 GB of memory allocated to the
Streams pool. As a best practice, keep 25 percent of the Streams pool available. For
example, if there are 3 Extracts, set STREAMS_POOL_SIZE
for the
database to the following value:
3 GB * 1.25 = 3.75 GB
Parent topic: Preparing the Database for Oracle GoldenGate
Ensuring Row Uniqueness in Source and Target Tables
Oracle GoldenGate requires a unique row identifier on the source and target tables to locate the correct target rows for replicated updates and deletes.
Unless a KEYCOLS
clause is used in the TABLE
or
MAP
statement, Oracle GoldenGate selects a row identifier to
use in the following order of priority, depending on the number and type of
constraints that were logged (see Configuring Logging Properties).
-
Primary key if it does not contain any extended (32K)
VARCHAR2/NVARCHAR2
columns. Primary key without invisible columns. -
Unique key: Unique key without invisible columns.
In the case of a non-integrated Replicat, the selection of the unique key is as follows:
-
First unique key alphanumerically with no virtual columns, no UDTs, no function-based columns, no nullable columns, and no extended (32K)
VARCHAR2/NVARCHAR2
columns. To support a key that contains columns that are part of an invisible index, you must use theALLOWINVISIBLEINDEXKEYS
parameter in the Oracle GoldenGateGLOBALS
file. -
First unique key alphanumerically with no virtual columns, no UDTs, no extended (32K)
VARCHAR2/NVARCHAR2
columns, or no function-based columns, but can include nullable columns. To support a key that contains columns that are part of an invisible index, you must use theALLOWINVISIBLEINDEXKEYS
parameter in the Oracle GoldenGateGLOBALS
file.
-
-
Not Nullable Unique keys: At least one column from one of the unique keys must be not nullable. This is because
NOALLOWNULLABLEKEYS
is the default.Note:
ALLOWNULLABLEKEYS
is not valid for integrated Replicat. -
If none of the preceding key types exist (even though there might be other types of keys defined on the table) Oracle GoldenGate constructs a pseudo key of all columns that the database allows to be used in a unique key, excluding virtual columns, UDTs, function-based columns, extended (32K)
VARCHAR2/NVARCHAR2
columns, and any columns that are explicitly excluded from the Oracle GoldenGate configuration by an Oracle GoldenGate user.Unless otherwise excluded due to the preceding restrictions, invisible columns are allowed in the pseudo key.
Note:
If there are other, non-usable keys on a table or if there are no keys at all on the table, Oracle GoldenGate logs an appropriate message to the report file. Constructing a key from all of the columns impedes the performance of Oracle GoldenGate on the source system. On the target, this key causes Replicat to use a larger, less efficient WHERE
clause.
If a table does not have an appropriate key, or if you prefer the existing key(s) not to be used, you can define a substitute key if the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS
clause within the Extract TABLE
parameter and the Replicat MAP
parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. For more information, see Reference for Oracle GoldenGate.
Parent topic: Preparing the Database for Oracle GoldenGate
Configuring Logging Properties
Oracle GoldenGate relies on the redo logs to capture the data that it needs to replicate source transactions.
The Oracle redo logs on the source system must be configured properly before you start Oracle GoldenGate processing.
This section addresses the following logging levels that apply to Oracle GoldenGate. The logging level that you use depends on Oracle GoldenGate features that you are using.
Note:
Redo volume is increased as the result of this required logging. You can wait until you are ready to start Oracle GoldenGate processing to enable the logging.
This table shows the Oracle GoldenGate use cases for the different logging properties.
Logging option | Command | What it does | Use case |
---|---|---|---|
Forced logging mode |
|
Forces the logging of all transactions and loads. |
Strongly recommended for all Oracle GoldenGate use cases. |
Minimum database-level supplemental logging |
|
Enables minimal supplemental logging to add row-chaining information to the redo log. |
Required for all Oracle GoldenGate use cases |
Schema-level supplemental logging, default setting |
|
Enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of all tables in a schema. All of these keys together are known as the scheduling columns. |
Enables the logging for all current and future tables in the schema. If the primary key, unique key, and foreign key columns are not identical at both source and target, use |
Schema-level supplemental logging with unconditional logging for all supported columns. (See Enabling Schema-level Supplemental Logging for non-supported column types.) |
|
Enables unconditional supplemental logging of all of the columns in a table, for all of the tables in a schema. |
Used for bidirectional and active-active configurations where all column values are checked, not just the changed columns, when attempting to perform an update or delete. This takes more resources though allows for the highest level of real-time data validation and thus conflict detection. This method should also be used if they are going to be using the
|
Schema-level supplemental logging, minimal setting |
|
Enables unconditional supplemental logging of the primary key and all valid unique indexes of all tables in a schema. |
Use only for nonintegrated Replicat. This is the minimum required schema-level logging. |
Table-level supplemental logging with built-in support for integrated Replicat |
|
Enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of a table. All of these keys together are known as the scheduling columns. |
Required for all Oracle GoldenGate use cases unless schema-level supplemental logging is used. If the primary key, unique key, and foreign key columns are not identical at both source and target, use |
Table-level supplemental logging with unconditional logging for all supported columns. (See Enabling Table-level Supplemental Logging for non-supported column types.) |
|
Enables unconditional supplemental logging of all of the columns of the table. |
Used for bidirectional and active-active configurations where all column values are checked, not just the changed columns, when attempting to perform an update or delete. This takes more resources though allows for the highest level of real-time data validation and thus conflict detection. It can also be used when the source and target primary, unique, and foreign keys are not the same or are constantly changing between source and target. |
Table-level supplemental logging, minimal setting |
|
Enables unconditional supplemental logging of the primary key and all valid unique indexes of a table. |
Use for nonintegrated Replicat and non-parallel Replicat. This is the minimum required table-level logging. |
Note:
Oracle Databases must be in ARCHIVELOG
mode so that Extract can process the log files.
Topics:
- Enabling Subset Database Replication Logging
- Enabling Schema-level Supplemental Logging
- Enabling Table-level Supplemental Logging
Parent topic: Preparing the Database for Oracle GoldenGate
Enabling Subset Database Replication Logging
Oracle strongly recommends putting the Oracle source database into forced logging mode. Forced logging mode forces the logging of all transactions and loads, overriding any user or storage settings to the contrary. This ensures that no source data in the Extract configuration gets missed.
There is a fine-granular database supplemental logging mode called Subset Database Replication available in LogMiner, which is the basic recommended mode for all Oracle GoldenGate and XStream clients. It replaces the previously used Minimum Supplemental Logging mode.
To know more, see ALTER
DATABASE
in the Oracle Database SQL
Language Reference.
The subset database replication logging is enabled at
CDB$ROOT
(and all user-PDBs
inherit it) currently.
Note:
Database-level primary key (PK) and unique index (UI) logging is only discouraged if you are replicating a subset of tables. You can use it with Live Standby, or if Oracle GoldenGate is going to replicate all tables, like to reduce the downtime for a migration or upgrade.
Perform the following steps to verify and enable, if necessary, subset database replication logging and forced logging.
SELECT con_id, MINIMAL, SUBSET_REP, PRIMARY_KEY, UNIQUE_INDEX, FOREIGN_KEY, ALL_COLUMN FROM CDB_SUPPLEMENTAL_LOGGING;
YES
.SUBSET_REP = YES
SELECT NAME, LOG_MODE, FORCE_LOGGING, SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK PK, SUPPLEMENTAL_LOG_DATA_UI UI,
SUPPLEMENTAL_LOG_DATA_FK FK,
SUPPLEMENTAL_LOG_DATA_ALL,
SUPPLEMENTAL_LOG_DATA_SR FROM V$DATABASE;
For the query for SUPPLEMENTAL_LOG_DATA_SR
the
output should be YES
and for
SUPPLEMENTAL_LOG_DATA_MIN
the
output should be IMPLICIT
.
-
Drop the earlier higher levels on
CDB$ROOT
.ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
-
Add only
subset database replication
mode:ALTER PLUGGABLE DATABASE pdbname ADD SUPPLEMENTAL LOG DATA SUBSET DATABASE REPLICATION;
-
Ensure that all PDBs inherit this
subset database replication
mode.
Parent topic: Configuring Logging Properties
Enabling Schema-level Supplemental Logging
Oracle GoldenGate supports schema-level supplemental logging. Schema-level logging is required for an Oracle source database when using the Oracle GoldenGate DDL replication feature. In all other use cases, it is optional, but then you must use table-level logging instead (see Enabling Table-level Supplemental Logging).
By default, schema-level logging automatically enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of all tables in a schema. Options enable you to alter the logging as needed.
Note:
Oracle strongly recommends using schema-level logging rather than table-level
logging, because it ensures that any new tables added to a schema are captured
if they satisfy wildcard specifications. This method is also recommended because
any changes to key columns are automatically reflected in the supplemental log
data too. For example, if a key changes, there is no need to issue ADD
TRANDATA
.
Perform the following steps on the source system to enable schema-level supplemental logging.
Parent topic: Configuring Logging Properties
Enabling Table-level Supplemental Logging
Enable table-level supplemental logging on the source system in the following cases:
-
To enable the required level of logging when not using schema-level logging (see Enabling Schema-level Supplemental Logging). Either schema-level or table-level logging must be used. By default, table-level logging automatically enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of a table. Options enable you to alter the logging as needed.
-
To prevent the logging of the primary key for any given table.
-
To log non-key column values at the table level to support specific Oracle GoldenGate features, such as filtering and conflict detection and resolution logic.
-
If the key columns change on a table that only has table-level supplemental logging, you must perform
ADD TRANDATA
on the table prior to allowing any DML activity on the table.
Perform the following steps on the source system to enable table-level supplemental logging or use the optional features of the command.
-
Run the command line on the source system.
-
Issue the
DBLOGIN
command using the alias of a user in the credential store who has privilege to enable table-level supplemental logging.DBLOGIN USERIDALIAS alias
See
USERIDALIAS
in Reference for Oracle GoldenGatefor more information aboutDBLOGIN
and additional options. -
Issue the
ADD TRANDATA
command.ADD TRANDATA [PDB.]schema.table [, COLS (columns)] [, NOKEY] [, ALLCOLS | NOSCHEDULINGCOLS]
Where:
-
PDB
is the name of the root container or pluggable database if the table is in a multitenant container database. -
schema
is the source schema that contains the table. -
table
is the name of the table. See Specifying Object Names in Oracle GoldenGate Input in Administering Oracle GoldenGate for instructions for specifying object names. -
ADD TRANDATA
without other options automatically enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of the table. Unconditional logging forces the primary key values to the log whether or not the key was changed in the current operation. Conditional logging logs all of the column values of a foreign or unique key if at least one of them was changed in the current operation. The default is optional to support nonintegrated Replicat (see alsoNOSCHEDULINGCOLS
) but is required to support integrated Replicat because primary key, unique keys, and foreign keys must all be available to the inbound server to compute dependencies. For more information about integrated Replicat, see Deciding Which Apply Method to Use. -
ALLCOLS
enables the unconditional supplemental logging of all of the columns of the table. Use to support integrated Replicat when the source and target tables have different scheduling columns. (Scheduling columns are the primary key, the unique key, and the foreign key.) -
NOSCHEDULINGCOLS
is valid for Replicat in nonintegrated mode only. It issues anALTER TABLE
command with anADD SUPPLEMENTAL LOG DATA ALWAYS
clause that is appropriate for the type of unique constraint that is defined for the table, or all columns in the absence of a unique constraint. This command satisfies the basic table-level logging requirements of Oracle GoldenGate when schema-level logging will not be used. See Ensuring Row Uniqueness in Source and Target Tables for how Oracle GoldenGate selects a key or index. -
COLS
columns
logs non-key columns that are required for aKEYCOLS
clause or for filtering and manipulation. The parentheses are required. These columns will be logged in addition to the primary key unless theNOKEY
option is also present. -
NOKEY
prevents the logging of the primary key or unique key. Requires aKEYCOLS
clause in theTABLE
andMAP
parameters and aCOLS
clause in theADD TRANDATA
command to log the alternateKEYCOLS
columns.
-
-
If using
ADD TRANDATA
with theCOLS
option, create a unique index for those columns on the target to optimize row retrieval. If you are logging those columns as a substitute key for aKEYCOLS
clause, make a note to add theKEYCOLS
clause to theTABLE
andMAP
statements when you configure the Oracle GoldenGate processes.
Parent topic: Configuring Logging Properties
Using the Extract Automated Capture Mode
The automated capture (auto capture) mode allows automatically capturing the tables that have been enabled for Oracle GoldenGate auto capture.
See How to Capture Supplemental Logging for Oracle GoldenGate in the Oracle Database Utilities guide.
-
Easy to configure captured table set
-
No requirement to update
TABLE
/TABLEEXCLUDE
parameter -
No need to stop or restart Extract when captured table set changes
Enabling Extract Auto Capture Mode
TRANLOGOPTIONS
:
TRANLOGOPTIONS ENABLE_AUTO_CAPTURE | DISABLE_AUTO_CAPTURE
When Extract is running in the auto capture mode, don't filter an LCR if
the object is not part of exclusion list set by TABLE EXCLUDE
parameter or any inclusion list set by TABLE
parameter.
The LIST TABLES
command shows the
list of tables enabled for AUTO_CAPTURE
.
See DML Auto Capture and Supported Objects and Operations in Oracle DDL to know about the DML and DDL considerations.
Also see this article Oracle GoldenGate 21c: Auto Capture of Tables to learn more.
Parent topic: Preparing the Database for Oracle GoldenGate
Additional Oracle GoldenGate Configuration for Your Database
Topics:
- Installing Support for Oracle Sequences
To support Oracle sequences, you must install some database procedures. - Handling Other Database Properties
This topic describes the database properties that may affect Oracle GoldenGate and the parameters that you can use to resolve or work around the condition.
Parent topic: Preparing the Database for Oracle GoldenGate
Installing Support for Oracle Sequences
To support Oracle sequences, you must install some database procedures.
To Install Oracle Sequence Objects
-
In SQL*Plus, connect to the source and target Oracle systems as
SYSDBA
. -
If you already assigned a database user to support the Oracle GoldenGate DDL replication feature, you can skip this step. Otherwise, in SQL*Plus on both systems create a database user that can also be the DDL user.
CREATE USER
DDLuser
IDENTIFIED BYpassword
; GRANT CONNECT, RESOURCE, DBA TODDLuser
; -
From the Oracle GoldenGate installation directory on each system, run GGSCI.
-
In GGSCI, issue the following command on each system.
EDIT PARAMS ./GLOBALS
-
In each
GLOBALS
file, enter theGGSCHEMA
parameter and specify the schema of the DDL user that you created earlier in this procedure.GGSCHEMA
schema
-
Save and close the files.
-
In SQL*Plus on the source system, issue the following statement in SQL*Plus.
ALTER TABLE sys.seq$ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
To capture the sequence from a multitenant database
-
Create an Oracle GoldenGate user in each PDB that you need to capture sequences from.
-
Add the user to the GLOBALS parameter file. It is easier if you use the same user for each PDB, if you don't then you need to change the GLOBALS file each time you do step 3.
-
Log into Admin Client or GGSCI.
-
Connect to the root container on the source using
DBLOGIN
. -
Issue the
FLUSH SEQUENCE
command for each PDB.
If you don't want to keep these database accounts, you can drop the user or deactivate the account.
Here is an example of the entire process:
Environment information
OGG 19.1 Oracle 12c to Oracle 12c Replication, Integrated
Extract, Parallel Replicat
Source: CDB GOLD, PDB CERTMISSN
Target: CDB PLAT, PDB CERTDSQ
Source Oracle GoldenGate Configuration
Container User: C##GGADMIN
PDB User for Sequences: GGATE
When prompted, enter GGATE
GLOBALS
GGSCHEMA GGATE
Flush Sequence
GGSCI> DBLOGIN USERIDALIAS GGADMIN DOMAIN GOLD_QC_CDB$ROOT
GGSCI> FLUSH SEQUENCE CERTMISSN.SRCSCHEMA1.
Target OGG Configuration
PDB User: GGATE
Run @sequence
sqlplus / as sysdba
SQL> alter session set container=CERTDSQ;
SQL> @sequence
When prompted enter GGATE
.
-
For migrations, you need rebuild the sequences on the target during the switchover, or increase them to a higher value just prior to the switchover.
-
Make sure you place the sequences into their own Replicat.
Handling Other Database Properties
This topic describes the database properties that may affect Oracle GoldenGate and the parameters that you can use to resolve or work around the condition.
The following table lists the database properties and the associated concern/resolution.
Database Property | Concern/Resolution |
---|---|
Table with interval partitioning |
To support tables with interval partitioning, make certain that the |
Table with virtual columns |
Virtual columns are not logged, and Oracle does not permit DML on virtual columns. You can, however, capture this data and map it to a target column that is not a virtual column by doing the following: Include the table in the Extract In the Replicat |
Table with inherently updateable view |
To replicate to an inherently updateable view, define a key on the unique columns in the updateable view by using a |
Redo logs or archives in different locations |
The |
|
To replicate
|
Sequences |
To replicate DDL for sequences ( To replicate just sequence values, use the |