3 GGSCI Command Line Interface Commands
Learn how to use the Oracle GoldenGate Classic Architecture GoldenGate Software Command Interface (GGSCI) commands, options, and review examples.
Topics:
- Oracle GoldenGate GGSCI Command Groups
- !
- ADD CHECKPOINTTABLE
- ADD CREDENTIALSTORE
- ADD EXTRACT
- ADD EXTTRAIL
- ADD HEARTBEATTABLE
- ADD MASTERKEY
- ADD PROCEDURETRANDATA
- ADD REPLICAT
- ADD RMTTRAIL
- ADD SCHEMATRANDATA
- ADD TRACETABLE
- ADD TRANDATA
- ALLOWNESTED
- ALTER CREDENTIALSTORE
- ALTER DATASTORE
- ALTER EXTRACT
- ALTER EXTTRAIL
- ALTER HEARTBEATTABLE
- ALTER REPLICAT
- ALTER RMTTRAIL
- CLEANUP CHECKPOINTTABLE
- CLEANUP REPLICAT
- CLEANUP EXTRACT
- CLEAR INSTANTIATION CSN
- CREATE DATASTORE
- CREATE SUBDIRS
- CREATE WALLET
- DBLOGIN
- DEFAULTJOURNAL
- DELETE CHECKPOINTTABLE
- DELETE CREDENTIALSTORE
- DELETE EXTRACT
- DELETE EXTTRAIL
- DELETE TRANDATA
- DELETE TRACETABLE
- DELETE SCHEMATRANDATA
- DELETE RMTTRAIL
- DELETE REPLICAT
- DELETE PROCEDURETRANDATA
- DELETE MASTERKEY
- DELETE HEARTBEATTABLE
- DELETE HEARTBEATENTRY
- EDIT PARAMS
- INFO ALL
- HISTORY
- HELP
- FLUSH SEQUENCE
- EXIT
- ENCRYPT PASSWORD
- FC
- FORCEAPPEND
- GETPARAMINFO
- INFO JAGENT
- INFO MANAGER
- INFO MARKER
- INFO PMSRVR
- INFO CHECKPOINTTABLE
- INFO TRANDATA
- INFO TRACETABLE
- INFO SCHEMATRANDATA
- INFO RMTTRAIL
- INFO REPLICAT
- INFO PROCEDURETRANDATA
- INFO PARAM
- INFO MASTERKEY
- INFO HEARTBEATTABLE
- INFO EXTTRAIL
- INFO EXTRACT
- INFO ER
- INFO CREDENTIALSTORE
- KILL ER
- KILL EXTRACT
- KILL REPLICAT
- LAG ER
- LAG EXTRACT
- LAG REPLICAT
- LIST TABLES
- MININGDBLOGIN
- OBEY
- OPEN WALLET
- PURGE WALLET
- RENEW MASTERKEY
- REGISTER EXTRACT
- RENEW MASTERKEY
- REPAIR DATASTORE
- SEND EXTRACT
- SEND MANAGER
- SET EDITOR
- SET NAMECCSID
- SEND REPLICAT
- SET INSTANTIATION CSN
- SHELL
- SHOW
- START ER
- STATS REPLICAT
- STATS EXTRACT
- STATS ER
- START EXTRACT
- START REPLICAT
- START JAGENT
- START MANAGER
- START PMSRVR
- STATUS JAGENT
- STATUS MANAGER
- STATUS PMSRVR
- STOP JAGENT
- STOP MANAGER
- STOP PMSRVR
- SYNCHRONIZE REPLICAT
- UNDELETE MASTERKEY
- UNREGISTER EXTRACT
- UPGRADE CHECKPOINTTABLE
- UPGRADE HEARTBEATTABLE
- VIEW ENCKEYS
- VIEW GGSEVT
Oracle GoldenGate GGSCI Command Groups
This section describes the GGSCI commands and their respective command groups.
- Summary of Manager Commands
- Summary of Extract Commands
- Summary of Replicat Commands
- Summary of the ER Command
- Summary of Wallet Commands
- Summary of Credential Store Commands
- Summary of Trail Commands
- Summary of Database Commands
- Summary of Trandata Commands
- Summary of Checkpoint Table Commands
- Summary of Oracle Trace Table Commands
- Summary of Oracle GoldenGate Data Store Commands
- Summary of Oracle GoldenGate Monitor JAgent Commands
- Summary of Oracle GoldenGate Automatic Heartbeat Commands
- Summary of PMSRVR Commands
- Summary of Procedure Replication Commands
- Summary of Miscellaneous Oracle GoldenGate Commands
Parent topic: GGSCI Command Line Interface Commands
Summary of Manager Commands
Use the Manager commands to control the Manager process. Manager is the parent process of Oracle GoldenGate Classic Architecture and is responsible for the management of its processes and files, resources, user interface, and the reporting of thresholds and errors.
Table 3-1 Manager Commands
Command | Description |
---|---|
|
Returns information about the Manager port and child processes. |
|
Returns information about a running Manager process. |
|
Starts the Manager process. |
|
Returns the state of the Manager process. |
|
Stops the Manager process. |
Parent topic: Oracle GoldenGate GGSCI Command Groups
Summary of Extract Commands
Use the Extract commands to create and manage Extract groups. The Extract process captures either full data records or transactional data changes, depending on configuration parameters, and then sends the data to a trail for further processing by a downstream process, such as a data-pump Extract or the Replicat process.
Table 3-2 Extract Commands
Command | Description |
---|---|
|
Creates an Extract group. |
|
Changes attributes of an Extract group |
|
Deletes run history for an Extract group |
|
Deletes an Extract group. |
|
Returns information about an Extract group. |
|
Forcibly terminates the run of an Extract group. |
|
Returns information about Extract lag. |
|
Registers an Extract group with an Oracle database. |
|
Sends instructions to, or returns information about, a running Extract group. |
|
Starts an Extract group. |
|
Returns processing statistics for an Extract group. |
|
Returns the state of an Extract group. |
|
Stops an Extract group. |
|
Unregisters an Extract group from an Oracle database. |
Parent topic: Oracle GoldenGate GGSCI Command Groups
Summary of Replicat Commands
Use the Replicat commands to create and manage Replicat groups. The Replicat process reads data extracted by the Extract process and applies it to target tables or prepares it for use by another application, such as a load application.
Table 3-3 Replicat Commands
Command | Description |
---|---|
|
Adds a Replicat group. |
|
Changes attributes of a Replicat group. |
|
Deletes run history of a Replicat group. |
|
Deletes a Replicat group. |
|
Returns information about a Replicat group. |
|
Forcibly terminates a Replicat group. |
|
Returns information about Replicat lag. |
|
Registers a Replicat group with an Oracle database. |
|
Sends instructions to, or returns information about, a running Replicat group. |
|
Starts a Replicat group. |
|
Returns processing statistics for a Replicat group. |
|
Returns the state of a Replicat group. |
|
Stops a Replicat group. |
|
Returns all threads of a coordinated Replicat to a uniform start point after an unclean shutdown of the Replicat process. |
|
Unregisters a Replicat group from an Oracle database. |
Parent topic: Oracle GoldenGate GGSCI Command Groups
Summary of the ER Command
Use the ER command to issue standard Extract and Replicat commands to multiple Extract and Replicat groups as a unit.
Table 3-4 ER Commands
Command | Description |
---|---|
|
Returns information about the specified wildcarded groups. |
|
Forcibly terminates the specified wildcarded groups. |
|
Returns lag information about the specified wildcarded groups. |
|
Sends instructions to, or returns information about, the specified wildcarded groups. |
|
Starts the specified wildcarded groups. |
|
Returns processing statistics for the specified wildcarded groups. |
|
Returns the state of the specified wildcarded groups. |
|
Stops the specified wildcarded groups. |
Parent topic: Oracle GoldenGate GGSCI Command Groups
Summary of Wallet Commands
Use the wallet commands to manage the master-key wallet that stores Oracle GoldenGate master encryptions keys, and to add master keys to this wallet.
Table 3-5 Wallet Commands
Command | Description |
---|---|
|
Creates a wallet that stores master encryption keys. |
|
Opens a master-key wallet. |
|
Permanently removes from a wallet the master keys that are marked as deleted. |
|
Adds a master key to a master-key wallet. |
|
Returns information about master keys. |
|
Adds a new version of a master key. |
|
Marks a master key for deletion. |
|
Changes the state of a master key from being marked as deleted to marked as available. |
Parent topic: Oracle GoldenGate GGSCI Command Groups
Summary of Credential Store Commands
Use the credential store commands to manage an Oracle GoldenGate credential store and to add credentials to the credential store.
Table 3-6 Credential Store Commands
Command | Description |
---|---|
|
Creates a credentials store (wallet) that stores encrypted database user credentials. |
|
Changes the contents of a credentials store. |
|
Returns information about a credentials store. |
|
Deletes the wallet that serves as a credentials store. |
Parent topic: Oracle GoldenGate GGSCI Command Groups
Summary of Trail Commands
Use the trail commands to create and manage Oracle GoldenGate trails. A trail is a series of files in which Oracle GoldenGate temporarily stores extracted data on disk until it has been applied to the target location.
Table 3-7 Trail Commands
Command | Description |
---|---|
|
Adds a local trail to the Oracle GoldenGate configuration. |
|
Adds a remote trail to the Oracle GoldenGate configuration. |
|
Changes attributes of a local trail. |
|
Changes attributes of a remote trail. |
|
Removes a local trail from the Oracle GoldenGate configuration. |
|
Removes a remote trail from the Oracle GoldenGate configuration. |
|
Returns information about a local trail. |
|
Returns information about a remote trail. |
Parent topic: Oracle GoldenGate GGSCI Command Groups
Summary of Database Commands
Use the database commands to interact with the database from GGSCI.
Table 3-8 Database Commands
Command | Description |
---|---|
|
Logs the GGSCI session into a database so that other commands that affect the database can be issued. |
|
Shows the data in the Oracle GoldenGate DDL history table. |
|
Encrypts a database login password. |
|
Updates an Oracle sequence so that initial redo records are available at the time that Extract starts capturing transaction data after the instantiation of the replication environment. |
|
Lists the tables in the database with names that match the input specification. |
|
Specifies the credentials of the user that an Oracle GoldenGate process uses to log into an Oracle mining database. |
|
Sets the CCSID of the GGSCI session in a DB2 for i environment. |
Parent topic: Oracle GoldenGate GGSCI Command Groups
Summary of Trandata Commands
Use trandata commands to configure the appropriate database components to provide the transaction information that Oracle GoldenGate needs to replicate source data operations.
Table 3-9 Trandata Commands
Command | Description |
---|---|
|
Enables schema-level supplemental logging. |
|
Enables table-level supplemental logging. |
|
Disables schema-level supplemental logging. |
|
Disables table-level supplemental logging. |
|
Returns information about the state of schema-level supplemental logging. |
|
Returns information about the state of table-level supplemental logging. |
|
Sets whether and how table instantiation CSN filtering is used. |
|
Clears table instantiation CSN filtering. |
Parent topic: Oracle GoldenGate GGSCI Command Groups
Summary of Checkpoint Table Commands
Use the checkpoint table commands to manage the checkpoint table that is used by Oracle GoldenGate to track the current position of Replicat in the trail.
Table 3-10 Checkpoint Table Commands
Command | Description |
---|---|
|
Creates a checkpoint table in a database. |
|
Removes checkpoint records that are no longer needed. |
|
Removes a checkpoint table from a database. |
|
Returns information about a checkpoint table. |
|
Use the |
Parent topic: Oracle GoldenGate GGSCI Command Groups
Summary of Oracle Trace Table Commands
Use the trace table commands to manage the Oracle GoldenGate trace table that is used with bidirectional synchronization of Oracle databases. Replicat generates an operation in the trace table at the start of each transaction. Extract ignores all transactions that begin with an operation to the trace table. Ignoring Replicat's operations prevents data from looping back and forth between the source and target tables.
Table 3-11 Oracle Trace Table Commands
Command | Description |
---|---|
|
Creates a trace table. |
|
Removes a trace table. |
|
Returns information about a trace table. |
Parent topic: Oracle GoldenGate GGSCI Command Groups
Summary of Oracle GoldenGate Data Store Commands
Use the data store commands to control the data store that Oracle GoldenGate uses to store monitoring information for use by Oracle GoldenGate Monitor.
Table 3-12 Oracle GoldenGate Data Store Commands
Command | Description |
---|---|
|
Changes the memory model that is used for interprocess communication by the data store. |
|
Creates the data store. |
|
Removes the data store. |
|
Returns information about the data store. |
|
Repairs the data store after an upgrade or if it is corrupt. |
Parent topic: Oracle GoldenGate GGSCI Command Groups
Summary of Oracle GoldenGate Monitor JAgent Commands
Use the JAgent commands to control the Oracle GoldenGate Monitor JAgent.
Table 3-13 JAgent Commands
Command | Description |
---|---|
|
Returns information about the JAgent. |
|
Starts the JAgent. |
|
Returns the state of the JAgent. |
|
Stops the JAgent. |
Parent topic: Oracle GoldenGate GGSCI Command Groups
Summary of Oracle GoldenGate Automatic Heartbeat Commands
Use the heartbeat table commands to control the Oracle GoldenGate automatic heartbeat functionality.
Table 3-14 Heartbeat Table Commands
Command | Description |
---|---|
|
Creates the objects required for automatic heartbeat functionality. |
|
Alters existing heartbeat objects. |
|
Deletes existing heartbeat objects. |
|
Deletes entries in the heartbeat table. |
|
Displays heartbeat table information. |
Parent topic: Oracle GoldenGate GGSCI Command Groups
Summary of PMSRVR Commands
Use the PMSRVR
commands to control the Performance Metrics Service process. The Performance Metrics Service
uses the metrics service to collect and store instance deployment performance
results.
Table 3-15 PMSRVR Commands
Command | Description |
---|---|
|
Returns information about the Oracle GoldenGate Performance Metrics Service. |
|
Starts the Oracle GoldenGate Performance Metrics Service process. |
|
Returns information about a running Oracle GoldenGate Performance Metrics Service process. |
|
Stops the Oracle GoldenGate Performance Metrics Service process. |
Parent topic: Oracle GoldenGate GGSCI Command Groups
Summary of Procedure Replication Commands
Use the Procedure Replication commands to enable or disable procedural supplemental logging with Oracle GoldenGate. To execute these commands, the Oracle GoldenGate administrator must be connected to the Oracle Database with dblogin
.
Table 3-16 Procedure Replication Commands
Command |
Description |
|
Adds supplemental logging for procedural replication. |
|
Removes supplemental logging for procedural replication. |
|
Displays information about procedural replication. |
Parent topic: Oracle GoldenGate GGSCI Command Groups
Summary of Miscellaneous Oracle GoldenGate Commands
Use the following commands to control various other aspects of Oracle GoldenGate.
Table 3-17 Miscellaneous Commands
Command | Description |
---|---|
|
Executes a previous GGSCI command without modifications. |
|
Enables or disables the use of nested |
|
Sets a default journal for multiple tables or files for the |
|
Allows the modification and re-execution of a previously issued GGSCI command. |
|
Provides assistance with syntax and usage of GGSCI commands. |
|
Shows a list of the most recently issued commands since the startup of the GGSCI session. |
|
Displays status and lag for all Oracle GoldenGate processes on a system. |
|
Processes a file that contains a list of Oracle GoldenGate commands. |
|
Executes shell commands from within the GGSCI interface. |
|
Displays the attributes of the Oracle GoldenGate environment. |
|
Displays information about the operating system and database. |
|
Displays the Oracle GoldenGate error log ( |
|
Displays the process report or the discard file that is generated by Extract or Replicat. |
Parent topic: Oracle GoldenGate GGSCI Command Groups
!
Use the !
command to execute a previous GGSCI command
without modifications. To modify a command before executing it again, use the
FC
command. To display a list of previous commands, use the
HISTORY
command. The !
command without
arguments executes the most recent command. Options enable you to execute any
previous command by specifying its line number or a text substring. Previous
commands can be executed again only if they were issued during the current session,
because command history is not maintained from session to session.
GGSCI Syntax
! [n | -n | string]
Examples
! 9
! -3
! sta
Parent topic: GGSCI Command Line Interface Commands
ADD CHECKPOINTTABLE
Not valid for Replicat for Java, Oracle GoldenGate Applications Adapter, or Oracle GoldenGate for Big Data.
Use ADD CHECKPOINTTABLE
to create a
checkpoint table in the target database. Replicat uses the table to maintain a
record of its read position in the trail for recovery purposes.
The use of a checkpoint table is strongly recommended, because it causes checkpoints to be part of the Replicat transaction. This allows Replicat to recover more easily in certain circumstances than when a checkpoint file alone is used. Parallel and coordinated Replicats require checkpoint tables.
One table can serve as the default checkpoint table for all Replicat
groups in an Oracle GoldenGate instance if you specify it with the
CHECKPOINTTABLE
parameter in a GLOBALS
file.
More than one instance of Oracle GoldenGate (multiple installations) can use the
same checkpoint table. Oracle GoldenGate keeps track of the checkpoints even when
the same Replicat group name exists in different instances.
Use the DBLOGIN
command to establish a database
connection before using this command. Do not change the names or attributes of the
columns in this table. You may, however, change table storage attributes.
See About Checkpoints.
GGSCI Syntax
ADD CHECKPOINTTABLE [[container. | catalog.]owner.table]
-
container.
|
catalog
. -
The Oracle pluggable database, if applicable. If this option is omitted, the pluggable database defaults to the one that is associated with the
SOURCEDB
,USERID
, orUSERIDALIAS
portion of theDBLOGIN
command (depending on the database). -
owner.table
-
The owner and name of the checkpoint table to be created. The name cannot contain any special characters, such as quotes, backslash, dollar sign, and percent symbol.
The name of a MySQL checkpoint table can contain no more than 30 characters.
The owner and name can be omitted if you are using this table as the default checkpoint table and it is listed with
CHECKPOINTTABLE
in theGLOBALS
file.It is recommended, but not required, that the table be created in a schema dedicated to Oracle GoldenGate. If an owner and name are not specified, a default table is created based on the
CHECKPOINTTABLE
parameter in theGLOBALS
parameter file.Record the name of the table, because you will need it to view statistics or delete the table if needed.
Record the name of the checkpoint table as that will be used when you add a Replicat, or delete a Replicat and need to drop the checkpoint table using the
DELETE CHECKPOINTTABLE
command.
Parent topic: GGSCI Command Line Interface Commands
ADD EXTRACT
Use ADD EXTRACT
to create an Extract group. Unless a
SOURCEISTABLE
task or an alias Extract is specified, ADD
EXTRACT
creates an online group that uses checkpoints so that processing
continuity is maintained from run to run.
For Db2 for i, this command establishes a global start point for all journals
and is a required first step. After issuing the ADD EXTRACT
command, you
can then optionally position any given journal at a specific journal sequence number by
using the ALTER EXTRACT
command with an appropriate journal option.
Oracle GoldenGate supports a large number of concurrent Extract and Replicat
groups per instance of Oracle GoldenGate deployment, depending on the resources available
with the operating system. At the supported level, all groups can be controlled and viewed
in full with commands such as the INFO
and STATUS
commands.
Oracle GoldenGate recommends keeping the combined number of Extract and Replicat groups at the default level of 300 or below in order to manage your environment effectively.
This command cannot exceed 500 bytes in size for all keywords and input,
including any text that you enter for the DESC
option.
GGSCI Syntax
ADD EXTRACT group_name
{, SOURCEISTABLE |
, TRANLOG [bsds_name |
, INTEGRATED TRANLOG |
, EXTFILESOURCE file_name |
, EXTTRAILSOURCE trail_name |
[, BEGIN {NOW | yyyy-mm-ddT[ hh:mi:[ss[.cccccc]]]Z}]
[, DESC 'description']
[, EXTSEQNO sequence_number
[, EXTRBA offset_number |
[, EXTRBA relative_byte_address |
[, EOF
[, LOGNUM lognum]
[, LOGPOS logpos
[, LRI value |
[, LSN value |
[, PAGE data_page, ROW row_ID |
[, PASSIVE]
[, PARAMS file_name]
[, REPORT file_name]
[, RMTNAME passive_Extract_name]
[, SEQNO sequence_number
[, SCN value
]
[, SOCKSPROXY {host_name | IP_address}[:port] [PROXYCSALIAS credential_store_alias [PROXYCSDOMAIN credential_store_domain]]]
-
group_name
-
The name of the Extract group. The name of an Extract group can contain up to eight characters, see Choosing Names for Processes and Files.
-
SOURCEISTABLE
-
Creates an Extract task that extracts entire records from the specified tables for an initial load using Oracle GoldenGate. If
SOURCEISTABLE
is not specified,ADD EXTRACT
creates an online change-synchronization process, and one of the other data source options must be specified. When usingSOURCEISTABLE
, do not specify any service options. Task parameters must be specified in the parameter file, see Instantiating Oracle GoldenGate with an Initial Load. -
TRANLOG [bsds_name]
-
Use this option for all databases.
TRANLOG
requires theBEGIN
option.(Db2 z/OS) You can use the
bsds_name
option for Db2 z/OS system to specify the Bootstrap Data Set file name of the transaction log, though it is not required and is not used. You do not need to change existingTRANLOG
parameters.(Oracle) Extract reads the Oracle redo logs directly. See
INTEGRATED TRANLOG
for an alternate configuration. -
INTEGRATED TRANLOG
-
(Oracle) Adds Extract in integrated capture mode. In this mode, Extract integrates with the database logmining server, which passes logical change records (LCRs) directly to Extract. Extract does not read the redo log. Before using
INTEGRATED TRANLOG
, use theREGISTER EXTRACT
command. -
EXTFILESOURCE
file_name
-
Specifies an initial load Extract file as the data source. Use this option with an Extract Pump that sends initial load file data to a remote, target system.
For
file_name
, specify the relative or fully qualified path name of the file. For example./dirdat/initfile
. -
EXTTRAILSOURCE
trail_name
-
Specifies a CDC Extract trail as the data source. Use this option with an Extract Pump that sends change data capture trails to a remote, target system.
For
trail_name
, specify the relative or fully qualified path name of the trail, for example\aa
. -
BEGIN {NOW | yyyy-mm-ddT[ hh:mi:[ss[.cccccc]]]Z}
-
Specifies a timestamp in the data source at which to begin processing.
-
NOW
-
NOW
specifies the time at which theADD EXTRACT
command is issued.For Db2 LUW, only commit and end transaction records contain timestamps, so the Extract starting position can only be calculated relative to those timestamps. This is a limitation of the API that is used by Oracle GoldenGate. It must be noted that positioning by timestamp is not accurate and can also take a long time. It is recommended to use LRI or EOF options wherever possible.
Do not use
NOW
for a data pump Extract except to bypass data that was captured to the trail prior to theADD EXTRACT
statement. -
yyyy-mm-ddthh:mm:ssZ
-
A date and time (timestamp) in the given form. For example, 2017-07-14T14:54:45Z.
-
yyyy-mm-dd[ hh:mi:[ss[.cccccc]]]
-
A date and time (timestamp) in the given form. For an Oracle Extract in integrated mode, the timestamp value must be greater than the timestamp at which the Extract was registered with the database.
Positioning by timestamp in PostgreSQL includes the following scenarios:-
Scenario 1
Iftrack_commit_timestamp
isoff
, the following output will be displayed when the Extract process starts irrespective of what positioning method is used:2020-04-29 02:15:54
-
Scenario 2
If thetrack_commit_timestamp
is enabled before Extract is registered then the correct timestamp will be displayed once the records are pushed in the source database as mentioned in the following example:2020-04-29 02:19:07 INFO OGG-01515 Positioning to begin time Apr 29,2020 2:18:38 AM.
-
Scenario 3
Iftrack_commit_timestamp
is enabled after the Extract is registered, then there may be chances that the older records are available in the log for which the commit timestamp is not built up with the associatedtransaction ID
. In that case, if Extract does not get the timestamp then it will fallback using the default timestamp mentioned in scenario 1. The output will be similar to the following:020-04-29 01:55:07 INFO OGG-01517 Position of first record processed LSN: 0/221D028, Jan 1, 1970 12:00:00 PM.
-
Past timestamp cannot be specified if the replication slot has moved away.
-
-
-
EXTRBA
archive-offset_number
-
Valid for Db2 z/OS. Specifies the relative byte address within a transaction log at which to begin capturing data.
The required format is
0X
nnn
, wherennn
is a 1 to 20 digit hexadecimal number (the first character is the digit zero, and the second character can be upper or lower case letterx
). -
EOF
-
Valid for Db2 for i, Db2 LUW, MySQL, PostgreSQL, and SQL Server.
Configures processing to start at the end of the log files (or journals) that the next record will be written to. Any active transactions will not be captured.
For Db2 LUW, it configures processing to start at the active
LRI
value in the log files. The activeLRI
is the position at the end of the log files that the next record will be written to. Any active transactions will not be captured.For PostgreSQL,
DBLOGIN
is required for position byEOF
.For MySQL, it finds the position corresponding to the end of the file and starts reading transactions from there. The EOF position is not exact, if data is continuously written to the binary log.
-
LSN
value
-
Valid for SQL Server, Db2 z/OS, and PostgreSQL.
Specifies the transactionLSN
at which to start capturing data. An alias for this option isEXTLSN
.Positioning to an LSN is precise.
For PostgreSQL,
LSN
value can behi
orlo
. Set the value ashi
for the entry point of the log file.Lo
is the offset in the log file. TheLSN
position should lie between the replication slot restart position and write ahead log current location. If the position specified itself exists between the mentioned range then Extract will throw an error.(SQL Server) specifies the transaction
LSN
at which to start capturing data. An alias for this option isEXTLSN
.The specified
LSN
should exist as a validtran_begin_lsn
found in thecdc.lsn_time_mapping
system table, otherwise the Extract will attempt to position after the LSN value provided.ValidLSN
specification consists of the following:-
Colon separated hex string (8:8:4) padded with leading zeroes and 0X prefix, as in 0X00000d7e:0000036b:0001
-
Colon separated decimal string (10:10:5) padded with leading zeroes, as in 0000003454:0000000875:00001
-
Colon separated hex string with 0X prefix and without leading zeroes, as in 0Xd7e:36b:1
-
Colon separated decimal string without leading zeroes, as in 3454:875:1
-
Decimal string, as in 3454000000087500001
You can find the minimum LSN available by querying the following:
SELECT min([tran_begin_lsn]) FROM [cdc].[lsn_time_mapping] with (nolock) where tran_id <> 0x00
Example:
ADD EXTRACT extn TRANLOG, LSN 0X00000d7e:0000036b:0001
-
-
LRI
value
-
Valid for DB2 LUW. Specifies a start position in the transaction logs when Extract starts.
You can use the
LRI
option for DB2 LUW systems to specify theLRI
at which extract can start capturing records from the transaction log. You can use the DB2 utilitydb2logsForRfwd
to obtain theLRI
. This utility providesLRI
ranges present in the DB2 logs.Note that, although Extract might position to a given
LRI
, thatLRI
might not necessarily be the first one that Extract will process. There are numerous record types in the log files that Extract ignores, such as DB2 internal log records. Extract will report the actual startingLRI
to the Extract report file. -
LOGNUM lognum
-
Valid for MySQL.
This is the log file number.
ADD EXTRACT
will fail if theLOGNUM
value contains zeroes preceding the value. For example,ADD EXTRACT ext1, TRANLOG, LOGNUM 000001, LOGPOS 0
will fail. Instead, setLOGNUM
to1
for this example to succeed. -
LOGPOS logpos
-
This is is an event offset value within the log file that identifies a specific transaction record. Event offset values are stored in the header section of a log record. To position at the beginning of a binlog file, set the
LOGPOS
as 0. -
SEQNO
sequence_number
-
Valid for DB2 for i. Starts capture at, or just after, a system sequence number, which is a decimal number up to 20 digits in length.
-
SCN
value
-
Valid for Oracle.
Starts Extract at the transaction in the redo log that has the specified Oracle system change number (SCN). For Extract in integrated mode, the SCN value must be greater than the SCN at which the Extract was registered with the database. For more information, see
REGISTER EXTRACT
. -
PARAMS
file_name
-
Specifies the full path name of an Extract parameter file in a location other than the default of
dirprm
within the Oracle GoldenGate directory. -
REPORT
file_name
-
Specifies the full path name of an Extract report file in a location other than the default of
dirrpt
within the Oracle GoldenGate directory. -
PASSIVE
-
(Classic Architecture only) Specifies that this Extract group runs in passive mode and can only be started and stopped by starting or stopping an alias Extract group on the target system. Source-target connections will be established not by this group, but by the alias Extract from the target.
This option can be used for a regular Extract group or a data pump Extract group. It should only be used by whichever Extract on the source system is the one that will be sending the data across the network to a remote trail on the target.
-
DESC '
description
'
-
Specifies a description of the group, such as
'Extracts account_tab on Serv1'
. Enclose the description within single quotes. You may use the abbreviated keywordDESC
or the full wordDESCRIPTION
. -
ENCRYPTIONPROFILE
-
Specifies the name of the Oracle GoldenGate encryption profile associated with the specific client.
-
CRITICAL
-
Indicates if the process is critical for the deployment.
-
PROFILE
-
Name of the auto start profile.
-
AUTOSTART
-
Specifies whether the managed process has to be started automatically when the Administration Service starts. The default value is
YES
. -
RETRIES
-
The maximum number of tries for restarting the task before canceling retry efforts. This is optional.
-
WAITSECONDS
-
The duration (in seconds) in which the retries are counted.
-
RESETSECONDS
-
Resets the duration in which the retries are counted.
-
DISABLEONFAILURE
-
If set to
TRUE
, then the task is disabled when the number of retries is exhausted. -
SOCKSPROXY
{host_name
|IP_address
}[:port
] [PROXYCSALIAS
credential_store_alias
[PROXYCSDOMAIN
credential_store_domain
] -
Use for an alias Extract. Specifies the DNS host name or IP address of the proxy server. You can use either one to define the host though you must use the IP address if your DNS server is unreachable. If you are using an IP address, use either an IPv6 or IPv4 mapped address, depending on the stack of the destination system. You must specify the
PROXYCSALIAS
. In addition, you can specify the port to use, and the credential store domain. -
RMTNAME
passive_extract_name
-
(Classic Architecture only) Use for an alias Extract. Specifies the passive Extract name, if different from that of the alias Extract.
Examples
-
The following example creates an Extract group named
exte
that captures database changes from the transaction logs. Extraction starts with records generated at the time when the Extract group was created and started withADD EXTRACT
.ADD EXTRACT extn, TRANLOG, BEGIN NOW
-
In the following example, from the Admin Client, an Extract group name
extw
is created to get the database changes from the transaction logs beginning from the specified time.ADD EXTRACT extn, TRANLOG, BEGIN 2020-08-02T06:05:30.000Z
-
The following creates a data pump Extract group named
extn
that reads data from the source trailnorth\ea
.ADD EXTRACT extn, EXTTRAILSOURCE north\ea
-
The following creates an initial-load Extract named
extei
.ADD EXTRACT extn, SOURCEISTABLE
-
In Classic Architecture, the following creates a passive Extract group named
extsc
that extracts database changes from the transaction logs.GGSCI> ADD EXTRACT extn, TRANLOG, BEGIN NOW, PASSIVE
-
The following creates an alias Extract group named
extw
. The alias Extract is associated with a passive Extract namedextw
on source systemsys
. The Manager on that system is using port 7800.ADD EXTRACT extn, RMTHOST sys, MGRPORT 7800, RMTNAME extw
-
The following examples create and position Extract at a specific Oracle system change number (SCN) in the redo log.
ADD EXTRACT extn TRANLOG SCN 123456
ADD EXTRACT extn INTEGRATED TRANLOG SCN 123456
-
The following example creates an alias Extract specifying the host to use.
ADD EXTRACT extn DESC 'alias extract' RMTHOST server1.dc1.north.example.com MGRPORT 7813 RMTNAME ppmp SOCKSPROXY server2.dc1.north.example.com PROXYCSALIAS ggnorthproxy
-
The following example creates an Extract on a Db2 LUW system.
ADD EXTRACT extn, TRANLOG LRI 8066.322711
Parent topic: GGSCI Command Line Interface Commands
ADD EXTTRAIL
Use ADD EXTTRAIL
to create a trail for online processing
on the local system and:
-
Associate it with an Extract group.
-
Assign a maximum file size.
See EXTTRAIL
in the Reference for Oracle GoldenGate
Syntax
ADD EXTTRAIL trail_name, EXTRACT group_name
[, MEGABYTES n]
[SEQNO n]
-
trail_name
-
Oracle GoldenGate appends this name with a nine-digit sequence number whenever a new file is created. For example, a trail named
ea
would have trail files namedea000000000
,ea000000001
.(For Classic Architecture) The relative or fully qualified path name of the trail, including a two character (alpha-numeric) maximum trail name. For example
dirdat/ea
.Note:
The trail file name and path (for Classic Architecture) must exactly match the
EXTTRAIL
parameter used in the Extract.
-
group_name
-
The name of the Extract group to which the trail is bound. A trail can only be assigned to one Extract. Multiple Extracts cannot write to the same trail. However, one Extract can write to multiple distinct trails if needed, but this is not normally required.
-
MEGABYTES
n
-
Valid for Extract. The maximum size, in megabytes, of each trail file in the sequence. The default is 2000.
-
SEQNO
n
-
Specifies that the first file in the trail will start with the specified trail sequence number. Do not include any zero padding. For example, to start at sequence 3 of a trail named
tr
, specifySEQNO 3
. The actual file would be named/ea000000003
. This option can be used during troubleshooting when Replicat needs to be repositioned to a certain trail sequence number. It eliminates the need to alter Replicat to read the required sequence number. The default value is 1.
Parent topic: GGSCI Command Line Interface Commands
ADD HEARTBEATTABLE
Valid for Oracle, Db2 z/OS, Db2 LUW, Db2 for i, MySQL, PostgreSQL, SQL Server, Teradata and TimesTen.
This command requires a database login using DBLOGIN
.
Use ADD HEARTBEATTABLE
to create the objects necessary
to use the automatic heartbeat functionality. This command performs the following
tasks
-
Creates a heartbeat seed table, heartbeat table, and heartbeat history table.
-
Creates the
GG_LAG
andGG_LAG_HISTORY
views. -
Creates the
GG_UPDATE_HB_TAB
andGG_PURGE_HB_TAB
procedures that are called by the scheduler jobs. -
Creates the scheduler jobs that periodically update the heartbeat and seed table, and purge the history table. However, it does not create these jobs for PostgreSQL, Teradata, and TimesTen.
-
Populates the seed table.
-
For Oracle multitenant databases:
-
The heartbeat objects and jobs are created in the user’s schema that is connected to the database using the
DBLOGIN
command. Oracle GoldenGate Extract and Replicat look for the heartbeat objects in theUSERID
orUSERIDALIAS
schema. When making the connection usingDBLOGIN
, make sure that it is set to the appropriateUSERID
orUSERIDALIAS
schema that your Extract and Replicat processes will use. -
Extract: Use the schema name in each PDB. Each PDB with which the Extract is registered should have its own heartbeat table. To instruct Extract which schema to find the heartbeat tables in, use the syntax
GGSCHEMA pdb.schemaname
within the GLOBALS file. -
Replicat: Each Replicat must have its own heartbeat table for its PDB.
-
For bidirectional, active/active replication, the heartbeat table should be in the same schema for the outgoing Extracts and incoming Replicats at each site. For example, see the following use case:
Site A Site B
EAB
–------------->RAB
RBA
–------------->EBA
In this example,
EAB
andRBA
heartbeat tables must use the same schema. However,EAB
andRAB
can use different schemas.
-
-
Oracle GoldenGate for Oracle heartbeat table administration has been simplified by eliminating the need for
GGSCHEMA
(orHEARTBEATTABLE
parameter) except for limited circumstances. Heartbeat table administration operations are only done in the schema of theDBLOGIN
user. Except for Oracle CDB root Extract, the Extract and Replicat processes look in the schema of the ER connected user for heartbeat tables. The following table shows the Extract and Replicat behavior for Oracle database:Extract/Replicat Processes Behavior Non-root Extract (non-CDB, and PDB)
If it is the first Extract user, then
GGSCHEMA
is used or in case of Autonomous Database (ADB), user must beggadmin
.If heartbeat is created after Extract starts, then look only in Extract user.
CDB Root Extract
GGSCHEMA
is used.Replicat
If it is the first Replicat user, then
GGSCHEMA
is used. For ADB, user must beggadmin
.If heartbeat is created after Replicat starts, then look only in the Replicat user.
DELETE EXTRACT/REPLICAT
If it is the first Extract or Replicat user, then
GGSCHEMA
is used. For ADB, user must beggadmin
.This feature allows the usage of heartbeat tables in Oracle GoldenGate Hub deployments, where multiple databases are managed with differing Oracle GoldenGate administrator schemas.Note:
The heartbeat table objects should never be created in the root CDB of an Oracle Multitenant Database. -
For heterogeneous or non-Oracle databases, the heartbeat objects and jobs are created in the
GGSCHEMA
value listed in the GLOBALS file.
The default seed, heartbeat, and history table names are
GG_HEARTBEAT_SEED
, GG_HEARTBEAT
, and
GG_HEARTBEAT_HISTORY
respectively.
In Microservices Architecture, the schema is configured using step 12 provided in the How to Create Deployments section of the Step by Step Data Replication Using Oracle GoldenGate Microservices guide.
-
The default names can be overridden by specifying
HEARTBEATTABLE
hbschemaname.hbtablename
in theGLOBALS
file. -
The tables, procedures, and jobs are created in the schema,
hbschemaname
. -
The seed and history table are created by appending a
_SEED
and_HISTORY
to the table,hbtablename
.
For Db2 LUW, you must set the DB2_ATS_ENABLE
property
with the db2set DB2_ATS_ENABLE=yes
command.
For Db2 for i,
to handle upgrade or misconfiguration of heartbeat table functionality, you can run
the ADD HEARTBEATTABLE
command again, which will repair the
functionality of an exisiting heartbeattable setup without deleting the existing
heartbeat data.
For Amazon Aurora MySQL, the global variable
event_scheduler
must be enabled in the parameter group because
Amazon RDS doesn't allow setting global variables. When the database is restarted,
the event_scheduler
returns to being disabled. To avoid this, you
need to enable the event_scheduler
in the
my.cnf/ini
file.
gg_hb_job_run
. For example, a cron job could be created that
runs every minute. The function will check the actual heartbeat record update and
purge frequency settings of the heartbeat configuration and only process operations
within those
boundaries:PGPASSWORD="$passwd" psql -U gguser -d dbname -h dbhostname -p dbport# -c "select <ggschema.gg_hb_job_run();" >/dev/null 2>&1
GGSCI Syntax
ADD HEARTBEATTABLE
[, FREQUENCY number_in_seconds]
[, RETENTION_TIME number_in_days] |
[, PURGE_FREQUENCY number_in_days]
[, PARTITIONED]
[, TARGETONLY]
-
FREQUENCY number_in_seconds
-
Specifies how frequently the heartbeat records are generated. The default is 60 seconds.
Consider the following limits:
-
For Oracle Database, the minimum value is 0 and the maximum is 7999.
-
For Db2 for i Series, the minimum value is 0 and the maximum is 7999.
-
For Db2 LUW and Db2 z/OS, the minimum value is 60 and the maximum is 7999.
-
The frequency for Db2 /zOS and Db2 LUW must be a multiple of 60 for values less than 3600 and multiples for 3600 for values greater or equal to 3600.
-
For MySQL, the minimum value is 0 and the maximum is 7999.
-
For SQL Server, the minimum value is 10 and the maximum is 7999.
-
For PostgreSQL, the minimum value is 60 and the maximum is 7999.
-
Databases that support setting
FREQUENCY
to 0 will pause the heartbeat record scheduler.
-
-
RETENTION_TIME number_in_days
-
Specifies that heartbeat entries older than the retention time in the heartbeat history table are purged. The default is 30 days.
The minimum value for all databases is 1 and the maximum is 2147483646.
-
PURGE_FREQUENCY number_in_days
-
Specifies how often the purge scheduler is run to delete table entries that are older than the retention time from the heartbeat history table. The default is 1 day.
For Db2 LUW and Db2 z/OS, the minimum value is 1 and the maximum is 31.
For all other supported databases, the minimum value is 1 and the maximum value is 199.
-
PARTITIONED
-
Valid for Oracle.
Enables partitioning on the heartbeat history table. The column for the heartbeat time stamp received is used to partition the table with an interval of one day. By default the heartbeat history table is not partitioned.
-
TARGETONLY
- Valid for Oracle Database, Db2 i Series, Db2 LUW, Db2 z/OS, MySQL, PostgreSQL, and SQL Server.
Examples
-
The following command creates default heartbeat tables, procedures and jobs.
ADD HEARTBEATTABLE
-
The following command creates the heartbeat tables, procedures and jobs with custom frequency, retention time, and purge frequency.
ADD HEARTBEATTABLE, FREQUENCY 120, RETENTION_TIME 10, PURGE_FREQUENCY 2
-
The following command creates the heartbeat tables, procedures and jobs with partitioning enabled in the heartbeat history table, and supplemental logging is not enabled in the heartbeat and heartbeat seed tables.
ADD HEARTBEATTABLE, partitioned, TARGETONLY
Parent topic: GGSCI Command Line Interface Commands
ADD MASTERKEY
Use the ADD MASTERKEY
command to add a master key to a
master-key wallet. The master key is used by Extract and Replicat to encrypt the
encryption keys that secure data being sent across the network and in the trail
files, so that those keys can be sent to, and used, by downstream processes. The
master key omits the need to use wallet storage for the keys that actually encrypt
the data.
The master-key wallet must be open to add a key. Use the GGSCI
CREATE WALLET
or the OPEN WALLET
command to
open a wallet. The wallet remains open throughout the same session in which the
command was issued.
The master key is generated as a random sequence of bits. The length is
256 bits by default. The key name is OGG_DEFAULT_MASTERKEY
.
After adding a master key to a wallet that is not maintained centrally on shared storage, the updated wallet must be copied to all of the other systems in the Oracle GoldenGate configuration that use this wallet. Before doing so, Extract must be stopped and then all of the downstream Oracle GoldenGate processes must be allowed to finish processing their trails and then be stopped. After the wallet is copied into place, the processes can be started again. For detailed instructions, see Encrypting Data with the Master Key and Wallet Method.
Syntax
ADD MASTERKEY
Example
ADD MASTERKEY
2019-11-21T19:37:23Z ERROR OGG-06137 Master key 'OGG_DEFAULT_MASTERKEY' does not exist in Oracle Wallet.
2019-11-21T19:37:23Z INFO OGG-06142 Created version 1 of master key 'OGG_DEFAULT_MASTERKEY' in Oracle Wallet.
Parent topic: GGSCI Command Line Interface Commands
ADD PROCEDURETRANDATA
Valid for Oracle.
Use ADD PROCEDURETRANDATA
to add supplemental logging
for Procedural Replication.
Syntax
ADD PROCEDURETRANDATA
Parent topic: GGSCI Command Line Interface Commands
ADD REPLICAT
Use ADD REPLICAT
to create a Replicat group. Unless
SPECIALRUN
is specified, ADD REPLICAT
creates
an online process group that creates checkpoints so that processing continuity is
maintained from run to run.
This command cannot exceed 500 bytes in size for all keywords and input,
including any text that you enter for the DESC
option.
Oracle GoldenGate supports up to 5,000 concurrent Extract and Replicat
groups per instance of Oracle GoldenGate Manager. At the supported level, all groups
can be controlled and viewed in full with GGSCI commands such as the
INFO
and STATUS
commands. Oracle GoldenGate
recommends keeping the number of Extract and Replicat groups (combined) at the
default level of 300 or below in order to manage your environment effectively.
(Oracle) Unless the INTEGRATED
option is used, this
command creates a Replicat group in non-integrated mode.
GGSCI Syntax
ADD REPLICAT group_name
ADD REPLICAT group_name
[, PARALLEL [, INTEGRATED] | INTEGRATED | COORDINATED [MAXTHREADS number]]
{, SPECIALRUN |
, EXTFILE file_name |
, EXTTRAIL trail_name}
[, BEGIN {NOW | yyyy-mm-ddT[ hh:mi:[ss[.cccccc]]]Z}]
[, EXTSEQNO sequence_number, EXTRBA rba]
{, CHECKPOINTTABLE owner.table | NODBCHECKPOINT}
[, PARAMS file_name]
[, REPORT file_name]
-
group_name
-
The name of the Replicat group. If you don't specify any option, then it creates a classic Replicat. The name of a coordinated and parallel Replicat group can contain a maximum of five characters. The name of a regular Replicat group can contain up to eight characters, see Choosing Names for Processes and Files.
-
INTEGRATED
-
(Oracle) Creates the Replicat in integrated mode. Without this option,
ADD REPLICAT
creates the Replicat in non-integrated (classic) mode. This option works for parallel Replicat too. In this mode, the Replicat process leverages the apply processing functionality that is available within the Oracle Database. In this mode, Replicat operates as follows:-
Reads the Oracle GoldenGate trail.
-
Performs data filtering, mapping, and conversion.
-
Constructs logical change records (LCR) that represent source database DML or DDL transactions (in committed order).
-
Attaches to a background process in the target database known as a database inbound server by means of a lightweight streaming interface.
-
Transmits the LCRs to the inbound server, which applies the data to the target database.
-
A Replicat in integrated mode (either normal integrated or parallel integrated) must be used if any of the following features are used:
-
Automatic conflict detection and resolution
-
Procedural replication
-
DML or DDL Handlers
-
Do not use
INTEGRATED
with theSPECIALRUN
orEXTFILE
options.INTEGRATED
must be used for an online change-synchronization Replicat that reads from a localEXTTRAIL
-specified trail.Both integrated Replicat and parallel Replicat in integrated mode maintain the checkpoint table if it exists. Also see
ADD CHECKPOINTTABLE
.TRACETABLE
is not maintained by integrated Replicat or parallel Replicat in integrated mode.When in integrated mode, Replicat does not support the following parameters:
-
BULKLOAD
(Do not use integrated Replicat as an initial-load Replicat.) -
SPECIALRUN
-
GENLOADFILES
-
SHOWSYNTAX
-
MAXTRANSOPS
(is ignored)
-
-
PARALLEL
-
Valid for Oracle, SQL Server, MySQL, Db2, TimesTen, PostgreSQL, and Teradata.
Adds the Replicat in parallel mode. In this mode, Replicat applies transactions in parallel to improve the performance. It takes into account dependencies between transactions.
PARALLEL INTEGRATED
Replicat is valid for Oracle only. It adds the parallel Replicat in integrated mode, which like Integrated Replicat leverages the apply processing functionality that is available within the Oracle Database. -
COORDINATED [MAXTHREADS
number
]
-
Creates the Replicat in coordinated mode. A coordinated Replicat is multithreaded to enable parallel processing. This option adds the coordinator (identified by the group name) and the maximum number of processing threads that are specified by default or with
MAXTHREADS
. Dependencies are computed and coordinated by the coordinator, and the SQL processing is performed by the threads.To create a
COORDINATED
Replicat, a checkpoint table is required.Do not use
COORDINATED
with theSPECIALRUN
orEXTFILE
options.COORDINATED
must be used for an online change-synchronization Replicat that reads from a localEXTTRAIL
-specified trail.Note:
The group name of a coordinated Replicat can contain only five characters.-
MAXTHREADS
number
-
Specifies the maximum number of processing threads that this Replicat group can spawn. These threads are all created on startup, but depending on what is specified in the
MAP
statements in the parameter file, some or all of these threads will process the workload at any given time. As a general rule, specify twice the number of threads that you specify in theMAP
statements when you partition the workload. This allows you to add threads in the event that the workload increases, without having to drop and recreate the Replicat group, see TABLE | MAP for more information about how to partition the workload across threads.The default number of threads is 25 if
MAXTHREADS
is omitted. The maximum number of threads is 500.MAXTHREADS
has a relationship to theMAXGROUPS
parameter.MAXGROUPS
controls the maximum number of process groups (Extract and Replicat) allowed per instance of Oracle GoldenGate. Each Replicat thread is considered a Replicat group in the context ofMAXGROUPS
. Therefore, the number of Extract and Replicat groups in the Oracle GoldenGate instance, plus the value ofMAXTHREADS
, cannot exceed the value ofMAXGROUPS
, see MAXGROUPS
-
-
SPECIALRUN
-
Creates a Replicat special run as a task. Either
SPECIALRUN
, EXTFILE
, orEXTTRAIL
is required. When Extract is inSPECIALRUN
mode, do not start Replicat with theSTART REPLICAT
command. Do not use this option with theINTEGRATED
orCOORDINATED
option.-
EXTFILE
file_name
-
Specifies the relative or fully qualified name of an extract file that is specified with
RMTFILE
in the Extract parameter file. Do not use this option with theINTEGRATED
option. -
EXTTRAIL
trail_name
-
Specifies the relative or fully qualified name of a trail that was created with the
ADD RMTTRAIL
orADD EXTTRAIL
command.
-
-
BEGIN {NOW | yyyy-mm-ddT[ hh:mm[:ss[.cccccc]]]Z}
-
Defines an initial checkpoint in the trail.
-
EXTSEQNO
sequence_number
-
Specifies the sequence number of the file in a trail in which to begin processing data. Specify the sequence number, but not any zeroes used for padding. For example, if the trail file is
\aa000000026
, you would specifyEXTSEQNO 26
. If not specified, the default value is zero.By default, processing begins at the beginning of a trail unless this option is used. To use
EXTSEQNO
, you must also useEXTRBA
. Contact Oracle Support before using this option. -
EXTRBA
rba
-
Specifies the relative byte address within the trail file that is specified by
EXTSEQNO
. Contact Oracle Support before using this option. -
CHECKPOINTTABLE
owner.table
-
Not valid for Oracle GoldenGate Applications Adapter or Oracle GoldenGate Big Data.
Oracle strongly recommends using a checkpoint table. Parallel and coordinated Replicat in integrated and non-integrated mode require a checkpoint table.
Specifies that this Replicat group will write checkpoints to the specified table in the database. Include the owner and table name, as in
ggadmin.ggs_checkpoint
. This argument overrides any defaultCHECKPOINTTABLE
specification in theGLOBALS
file. The table must first be added with theADD CHECKPOINTTABLE
command.When
NODBCHECKPOINT
is specified, this Replicat group will not write checkpoints to a checkpoint table. This argument overrides any defaultCHECKPOINTTABLE
specification in theGLOBALS
file. This argument is required if you do not want to use a checkpoint table with the Replicat group that is being created. -
PARAMS
file_name
-
(Classic Architecture only) Specifies a parameter file in a location other than the default of
dirprm
within the Oracle GoldenGate directory. Specify the fully qualified path name. -
REPORT
file_name
-
Specifies the full path name of a process report file in a location other than the default of
dirrpt
within the Oracle GoldenGate directory. -
DESC '
description
' -
Specifies a description of the group, such as
'Loads account_tab on Serv2
'. Enclose the description within quotes. You can use either the abbreviated keywordDESC
or the full wordDESCRIPTION
. -
ENCRYPTIONPROFILE
- Specifies the name of the encryption profile for the Replicat.
Examples: GGSCI
ADD REPLICAT repe, INTEGRATED, EXTTRAIL north/ea
The following example creates Replicat in coordinated mode. It indicates that up to 100 threads can be employed in parallel at any given point in processing.
ADD REPLICAT repe, COORDINATED MAXTHREADS 100, EXTTRAIL north/ea
Parent topic: GGSCI Command Line Interface Commands
ADD RMTTRAIL
Use ADD RMTTRAIL
to create a trail for online
processing by a Replicat on a remote, target system. The command:
-
Assigns a maximum file size.
-
Associates the trail with an Extract group.
-
Uses remote trails in a Pump Extract.
Syntax
ADD RMTTRAIL trail_name, EXTRACT group_name
[, FORMAT RELEASE major.minor]
[, MEGABYTES n]
[, SEQNO n]
-
trail_name
-
(For Classic Architecture), The relative or fully qualified path name of the remote trail, including a two character (alpha-numeric) maximum trail name. For example:
dirdat/ea
.For Microservices Architecture, a two character (alpha-numeric) maximum trail name, with no path listed, and the first character must not be a number. For example
ea
.Oracle GoldenGate appends this name with a nine-digit sequence number whenever a new file is created. For example, a trail named
ea
would have trail files namedea000000000
,ea000000001
.Note:
Note: The trail file name and path (for Classic Architecture) must exactly match the
RMTTRAIL
parameter used in the Extract. -
group_name
-
The name of the Extract group to which the trail is bound. A remote trail can only be assigned to one Extract. Multiple Extracts cannot write to the same trail. However, one Extract can write to multiple distinct remote trails.
-
MEGABYTES
n
-
The maximum size, in megabytes, of a file in the trail. The default is 500, however, the value can be between 1 MB and 2000 MB (maximum).
-
SEQNO
n
-
Specifies that the first file in the trail will start with the specified trail sequence number. Do not include any zero padding. For example, to start at sequence 3 of a trail named
ea
, specifySEQNO 3
. The actual file would be namedea000000003
. This option can be used during troubleshooting when Replicat needs to be repositioned to a certain trail sequence number. It eliminates the need to alter Replicat to read the required sequence number.
Parent topic: GGSCI Command Line Interface Commands
ADD SCHEMATRANDATA
Valid for Oracle. Use ADD SCHEMATRANDATA
to enable
schema-level supplemental logging for a table. ADD SCHEMATRANDATA
acts on all of the current and future tables in a given schema to automatically log
a superset of available keys that Oracle GoldenGate needs for row
identification.
To perform ADD
SCHEMATRANDATA
against a schema in the PDB of a multitenant database,
you need to login to PDB to issue the command.
ADD SCHEMATRANDATA
is valid for both integrated and
classic Extract and does the following:
-
Enables Oracle supplemental logging for new tables created with a
CREATE TABLE
. -
Updates supplemental logging for tables affected by an
ALTER TABLE
to add or drop columns. -
Updates supplemental logging for tables that are renamed.
-
Updates supplemental logging for tables for which unique or primary keys are added or dropped.
-
Enables a table for auto-capture. The command add schema-level PK, UI, FK, ALLKEYS supplemental logging data.
By default, ADD SCHEMATRANDATA
logs the key columns of a
table in the following order of priority:
-
Primary key
-
In the absence of a primary key, all of the unique keys of the table, including those that are disabled, unusable or invisible. Unique keys that contain ADT member columns are also logged. Only unique keys on virtual columns (function-based indexes) are not logged.
-
If none of the preceding exists, all scalar columns of the table are logged. (System-generated row-OIDs are always logged.)
ADD SCHEMATRANDATA
also supports the conditional or
unconditional logging requirements for using integrated Replicat.
Use ADD SCHEMATRANDATA
in the following cases:
-
For all tables that are part of an Extract group that is to be configured for integrated capture.
ADD SCHEMATRANDATA
ensures that the correct key is logged by logging all of the keys. -
For all source tables that will be processed in an integrated Replicat group. Options are provided that enable the logging of the primary, unique, and foreign keys to support the computation of dependencies among relational tables being processed through different apply servers.
-
When DDL replication is active and DML is concurrent with DDL that creates new tables or alters key columns. It best handles scenarios where DML can be applied to objects very shortly after DDL is issued on them.
ADD SCHEMATRANDATA
causes the appropriate key values to be logged in the redo log atomically with each DDL operation, thus ensuring metadata continuity for the DML when it is captured from the log, despite any lag in Extract processing.
Database-level Logging
Requirements for Using ADD SCHEMATRANDATA
Oracle strongly encourages putting the source database into forced logging mode and enabling minimal supplemental logging at the database level when using Oracle GoldenGate. This adds row chaining information, if any exists, to the redo log for update operations.
Additional Considerations for
Using ADD SCHEMATRANDATA
-
Before using
ADD SCHEMATRANDATA
, issue theDBLOGIN
command. The user who issues the command must be granted the Oracle Streams administrator privilege.exec dbms_streams_auth.grant_admin_privilege('GG-AdminUser')
-
ADD SCHEMATRANDATA
can be used instead of theADD TRANDATA
command when DDL replication is not enabled. Note, however, that if a table has no primary key but has multiple unique keys,ADD SCHEMATRANDATA
causes the database to log all of the unique keys. In such cases,ADD SCHEMATRANDATA
causes the database to log more redo data than doesADD TRANDATA
. To avoid the extra logging, designate one of the unique keys as a primary key, if possible. -
For tables with a primary key, with a single unique key, or without a key,
ADD SCHEMATRANDATA
adds no additional logging overhead, as compared toADD TRANDATA
. -
If adding
SCHEMATRANDATA
on a schema in a PDB, you need to be logged into that PDB inDBLOGIN
. For example, forPDBEAST
and schemaHR
, use the following command:ADD SCHEMATRANDATA pdbeast.hr
-
If you must log additional, non-key columns of a specific table (or tables) for use by Oracle GoldenGate, such as those needed for
FILTER
statements andKEYCOLS
clauses in theTABLE
andMAP
parameters, issue anADD TRANDATA
command for those columns. That command has aCOLS
option to issue table-level supplemental logging for the columns, and it can be used in conjunction withADD SCHEMATRANDATA
.
GGSCI Syntax
ADD SCHEMATRANDATA schema {
[ALLOWNONVALIDATEDKEYS]
[NOSCHEDULINGCOLS | ALLCOLS]}
[NOVALIDATE]
[PREPARECSN {WAIT | LOCK | NOWAIT | NONE}]
-
schema
-
The schema for which you want the supplementary key information to be logged. Do not use a wildcard. To issue
ADD SCHEMATRANDATA
for schemas in more than one pluggable database of a multitenant container database, log in to each pluggable database separately withDBLOGIN
and then issueADD SCHEMATRANDATA
. From the root conatiner, you may add schematrandata with the container prefixADD SCHEMATRANDATA [pdb_name].schema
-
ALLOWNONVALIDATEDKEYS
-
This option is not valid for Oracle 11.2.0.3 or 12.1.0.1. It includes
NON VALIDATED
andNOT VALID
primary keys in the supplemental logging. These keys override the normal key selection criteria that is used by Oracle GoldenGate. If theGLOBALS
parameterALLOWNONVALIDATEDKEYS
is being used,ADD SCHEMATRANDATA
runs withALLOWNONVALIDATEDKEYS
whether or not it is specified. By defaultNON VALIDATED
andNOT VALID
primary keys are not logged, see theGLOBALS
ALLOWNONVALIDATEDKEYS parameter. -
NOSCHEDULINGCOLS | ALLCOLS
-
These options control supplemental logging for an Oracle target database. You can use these options together though the latter option is used. For example, with the
ADD SCHEMATRANDATA oggadm_ext ALLCOL NOSCHEDULINGCOLS
command theNOSCHEDULINGCOLS
option would be used.-
NOSCHEDULINGCOLS
-
Disables the logging of scheduling columns. By default,
ADD SCHEMATRANDATA
enables the unconditional logging of the primary key and the conditional supplemental logging of all unique keys and foreign keys of all current and future tables in the given schema. 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 integrated Replicat primary key, unique keys, and foreign keys must all be available to the inbound server to compute dependencies.If you are enabling
auto_capture
, then do not use this option. This will allow tables in this schema to be auto captured unless the table is explicitly excluded/disabled for replication (such as throughTABLEEXCLUDE
,DELETE TRANDATA
, or alter table disable logical replication DDL). -
ALLCOLS
-
Enables the unconditional supplemental logging of all supported key and non-key columns for all current and future tables in the given schema. This option enables the logging of the keys required to compute dependencies, plus columns that are required for filtering, conflict resolution, or other purposes. Columns like LOB, LONG, and ADT are not included.
-
-
NOVALIDATE
-
Valid for all databases supported by
ADD SCHEMATRANDATA
.Suppresses additional information about the table being handled being processed by
ADD SCHEMATRANDATA
. By default, this option is enabled. The additional information processing creates a lapse time on command response so this option can be used to increase response time. -
PREPARECSN {WAIT | LOCK | NOWAIT | NONE}
-
Valid for Oracle for both DML and DDL. Automatically prepares the tables at the source so the Oracle data pump Export dump file will includes Instantiation CSNs. Replicat uses the per table instantiation CSN set by the Oracle data pump (on import) to filter out trail records. On the target, the data pump import populates the system tables and views with instantiation SCNs using the
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
parameter to enable table-level instantiation filtering.
Example
-
The following enables supplemental logging for the schema
hr
.ADD SCHEMATRANDATA hr
-
The following example logs all supported key and non-key columns for all current and future tables in the schema named
hr
.ADD SCHEMATRANDATA hr ALLCOLS
-
The following example suppress additional table information processing.
ADD SCHEMATRANDATA hr NOVALIDATE
Parent topic: GGSCI Command Line Interface Commands
ADD TRACETABLE
Use ADD TRACETABLE
to create a trace table in the
Oracle Database. The trace table must reside in the schema of the Oracle GoldenGate
Extract user, as configured with the USERID
or
USERIDALIAS
parameter. The trace table prevents Replicat
transactions from being extracted again in a bidirectional synchronization
configuration.
Use the DBLOGIN
command to establish a database
connection before using this command.
Table 3-18 Description of trace table
Name | Null? | Type | Description |
---|---|---|---|
|
|
|
The name of the Replicat group or special run process. |
|
|
|
The user ID of the Replicat group or special run process. |
|
|
|
The timestamp of the transaction. |
Syntax
ADD TRACETABLE [[container
.]owner.table]
-
container
-
The pluggable database, if the database is a multitenant container database (CDB).
-
owner.table
-
Optional, use only to specify a trace table with a name that is different from the default of
GGS_TRACE
. The owner must be the same owner that is specified with theUSERID
orUSERIDALIAS
parameter in the Extract parameter file.To use the default name, omit this argument. Whenever possible, use the default table name. When using a trace table name other than the default of
GGS_TRACE
, specify it with theTRACETABLE
parameter in the Extract and Replicat parameter files. Record the name, because you will need it for the parameter files and to view statistics or delete the table, see TRACETABLE | NOTRACETABLE.
Parent topic: GGSCI Command Line Interface Commands
ADD TRANDATA
Use ADD TRANDATA
to enable Oracle GoldenGate to acquire the
transaction information that it needs from the transaction records.
Before using this command, use the DBLOGIN
command to
establish a database connection.
ADD TRANDATA
is valid for the databases that are listed
here:
-
Db2 for i
-
Db2 LUW
-
Db2 z/OS
-
Oracle
-
PostgreSQL
-
SQL Server
For other supported databases, this functionality may exist already or must be configured through the database interface. See Installing Oracle GoldenGate for any special requirements that apply to making transaction information available.
Db2 for i
Use ADD TRANDATA
to start the journaling of data. The
ADD TRANDATA
command calls STRJRNPF
and is the
recommended method to start journaling for tables, because it ensures that the required
journal image attribute of Record Images (IMAGES): *BOTH
is set on the
STRJRNPF
command.
Db2 LUW
Use ADD TRANDATA
to enable DATA CAPTURE
CHANGES
on specified tables. By default, ADD TRANDATA
issues the following command to the database:
ALTER TABLE name DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS;
You can exclude the LONGVAR
clause by using ADD
TRANDATA
with the EXCLUDELONG
option.
Db2 z/OS
Use ADD TRANDATA
to enable DATA CAPTURE
CHANGES
on specified tables. By default, ADD TRANDATA
issues the following command to the database:
ALTER TABLE name DATA CAPTURE CHANGES;
Oracle Database
From the 21c release onward, this command would also enable a table for auto capture for Oracle database.
By default, ADD TRANDATA
for Oracle enables the
unconditional logging of the primary key and the conditional supplemental logging of all
unique key(s) and foreign key(s) of the specified table, see Ensuring Row Uniqueness in Source and Target
Tables for more information about how Oracle GoldenGate handles supplemental
logging for Oracle Databases.
If possible, use the ADD SCHEMATRANDATA
command rather than
the ADD TRANDATA
command. The ADD SCHEMATRANDATA
command ensures replication continuity should DML occur on an object for which DDL has
just been performed. You can exclude objects from the schema specification by using the
exclusion parameters.
To use the Oracle GoldenGate DDL replication feature, you must use the
ADD SCHEMATRANDATA
command to log the required supplemental
data.
When using ADD SCHEMATRANDATA
, you can use ADD
TRANDATA
with the COLS
option to log any non-key columns,
such as those needed for FILTER
statements and KEYCOLS
clauses in the TABLE
and MAP
parameters.
Note:
It is possible to useADD
TRANDATA
for Oracle when DDL support is enabled, but only if you can stop
DML on all tables before DDL is performed on them or, if that is not possible, you can
guarantee that no users or applications will issue DDL that adds new tables whose names
satisfy an object specification in a TABLE
or MAP
statement. There must be no possibility that users or applications will issue DDL that
changes the key definitions of any tables that are already in the Oracle GoldenGate
configuration.
Oracle recommends putting the source database into forced logging mode and enabling minimal supplemental logging at the database level when using Oracle GoldenGate. This adds row chaining information, if any exists, to the redo log for update operations
Take the following into account when using ADD TRANDATA
for
an Oracle database:
-
If any of the logging details change after Oracle GoldenGate starts extracting data, you must stop and then start the Extract process that is reading from the affected table before any data is changed.
-
When creating a supplemental log group with
ADD TRANDATA
, Oracle GoldenGate appends the object ID to a prefix ofGGS_
, for exampleGGS_18342
.
PostgreSQL
Using ADD TRANDATA
command, the REPLICA
IDENTITY
setting of the table is altered. The REPLICA
IDENTITY
setting controls before images of columns of a table that should be
logged to the transaction log for UPDATE
and DELETE
operations.
SQL Server
Use ADD TRANDATA
to provide the extended logging
information that Oracle GoldenGate needs to reconstruct SQL operations. The SQL Server
transaction log does not provide enough information by default.
By enabling TRANDATA
, Oracle GoldenGate enables the SQL
Server Change Data Capture feature for the database and creates a Change Data Capture
table for each instance enabled with TRANDATA
.
GGSCI Syntax
ADD TRANDATA {[container.]owner.table | schema.table [JOURNAL
library/journal] |
library/file [JOURNAL library/journal]}
[, FILEGROUP filegroup-name]
[, NOSCHEDULINGCOLS | ALLCOLS]
[, COLS (columns)]
[, INCLUDELONG | EXCLUDELONG]
[, NOKEY]
[, NOVALIDATE]
[, PREPARECSN {WAIT | LOCK | NOWAIT | NONE}]
-
[
container
.]
owner
.
table
-
Valid for Db2 LUW, Db2 z/OS, Oracle, PostgreSQL, and SQL Server.
The two-part or three-part name specification. Use a two-part name of
owner.table
for all supported databases except an Oracle multitenant container database. Use a three-part name ofcontainer.owner.table
for an Oracle multitenant container database. A wildcard can be used for any component. Used with a wildcard,ADD TRANDATA
filters out names that match the names of system objects. To useADD TRANDATA
for objects that are not system objects but have names that match those of system objects in a wildcard pattern, issueADD TRANDATA
for those objects without using a wildcard. -
schema.table
[JOURNAL
library/journal
] |
-
library/file
[JOURNAL
library/journal
]
-
Valid for Db2 for i.
Specifies the SQL schema and name of a table or the native library and file name. If a default journal is set with the
DEFAULTJOURNAL
command, you can omit theJOURNAL
option; otherwise it is required. -
FILEGROUP
filegroup-name
-
Valid for SQL Server.
(Optional) You can designate the filegroup in which the SQL Server Change Data Capture staging tables will be placed, by using theFILEGROUP
option with an existing filegroup name.ADD TRANDATA owner.table FILEGROUP cdctables
You can use the
FILEGROUP
option in the GLOBALS file also if you need to use the sameFILEGROUP
for each table when enablingTRANDATA
.The following example shows setting the
FILEGROUP
myFileGroup
inGLOBALS
file:FILEGROUP myFileGroup
The GGSCI command is:ADD TRANDATA dbo.*
The output is:Logging of supplemental log data is enabled for table dbo.test1 in filegroup myFileGroup
In this case,
ADD TRANDATA
command uses themyFileGroup
for all the tables.If you also use the
FILEGROUP
parameter withADD TRANDATA
, the command overrides the filegroup name defined in the GLOBALS file.For example, if you set
FILEGROUP myFileGroup
in the GLOBALS file and then execute the followingADD TRANDATA
command:ADD TRANDATA dbo.* FILEGROUP yourFileGroup
Then the output is:Logging of supplemental log data is enabled for table dbo.test1 in filegroup yourFileGroup
In this case,
ADD TRANDATA
uses theyourFileGroup
instead ofmyFileGroup
for all the tables.If you don't specify the
FILEGROUP
either in GLOBALS or withADD TRANDATA
, then GGSCI considers the defaultFILEGROUP
of the database while addingTRANADATA
of the table. For example, if you runADD TRANDATA dbo.*
, the output is:Logging of supplemental log data is enabled for table dbo.test1 in filegroup PRIMARY
In this case, the default
FILEGROUP
isPrimary
. If you run theINFO TRANDATA
command, theFILEGROUP
name shows asPRIMARY
. -
NOSCHEDULINGCOLS | ALLCOLS
-
Valid for Oracle and PostgreSQL.
From Oracle GoldenGate 21c onward,
NOSCHEDULINGCOLS
is deprecated for Oracle database 21c and higher if the running database supports auto capture capabilities.These options satisfy the logging requirements of an integrated Replicat that will be processing the tables that you are specifying with
ADD TRANDATA
.-
NOSCHEDULINGCOLS
-
Valid for Oracle only.
Disables the logging of scheduling columns. By default,
ADD TRANDATA
enables the unconditional logging of the primary key and the conditional supplemental logging of all unique keys and foreign keys of the specified 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 primary key, unique keys, and foreign keys must all be available to the inbound server to compute dependencies. -
ALLCOLS
-
Enables the unconditional supplemental logging of all of the key and non-key columns of the table. This option enables the logging of the keys required to compute dependencies, plus all other columns for use in filtering, conflict resolution, or other purposes.
-
For PostgreSQL,
ALLCOLS
setsREPLICA IDENTITY
for the table toFULL
.ALLCOLS
is specified as part of theADD TRANDATA
command, to enable logging of all the columns forUPDATE
andDELETE
operations, even if those columns have not been modified.For tables without a Primary Key or Unique Index, the
ALLCOLS
option is redundant. Here's the syntax:ADD TRANDATA table_name ALLCOLS
-
-
COLS (
columns
)
-
Valid for Oracle.
Use the
COLS
option to log specific non-key columns. Can be used to log columns specified in aKEYCOLS
clause and to log columns that will be needed for filtering or manipulation purposes, which might be more efficient than fetching those values with aFETCHCOLS
clause in aTABLE
statement. Separate multiple columns with commas, for exampleNAME
,ID
,DOB
. -
INCLUDELONG | EXCLUDELONG
-
Valid for Db2 LUW.
Controls whether or not the
ALTER TABLE
issued byADD TRANDATA
includes theINCLUDE LONGVAR COLUMNS
attribute.INCLUDELONG
is the default. WhenADD TRANDATA
is issued with this option, Oracle GoldenGate issues the following statement:ALTER TABLE name DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS;
When
EXCLUDELONG
is used, the following is the command:ALTER TABLE name DATA CAPTURE CHANGES;
When
EXCLUDELONG
is used, Oracle GoldenGate does not support functionality that requires before images of tables that includeLONGVAR
columns. For example, theGETUPDATEBEFORES
parameter. To support this functionality, changes toLONGVAR
columns in the transaction logs must include both the before and after images of the column value.
-
NOKEY
-
Valid for Db2 for i, Db2 LUW, Db2 z/OS, Oracle.
From Oracle GoldenGate 21c onward,
NOKEY
is deprecated for Oracle database 21c and higher if the running database supports auto capture capabilities.Suppresses the supplemental logging of primary key columns. If using
NOKEY
, use theCOLS
option to log alternate columns that can serve as keys, and designate those columns as substitute keys by using theKEYCOLS
option of theTABLE
orMAP
parameter. -
NOVALIDATE
-
Valid for Db2 for i, Db2 LUW, Db2 z/OS, Oracle.
Suppresses additional information about the table being handled being processed by
ADD TRANDATA
. By default, this option is enabled. The additional information processing creates a lapse time on command response so this option can be used to increase response time. -
ALLOWNONVALIDATEDKEYS
-
Valid for Db2 for i, Db2 LUW, Db2 z/OS, Oracle.
It includes
NON VALIDATED
andNOT VALID
primary keys in the supplemental logging. These keys override the normal key selection criteria that is used by Oracle GoldenGate. If theGLOBALS
parameterALLOWNONVALIDATEDKEYS
is being used,ADD SCHEMATRANDATA
runs withALLOWNONVALIDATEDKEYS
whether or not it is specified. By default,NON VALIDATED
andNOT VALID
primary keys are not logged. -
PREPARECSN {WAIT | LOCK | NOWAIT | NONE}
-
Valid for Oracle for both DML and DDL. Automatically prepares the tables at the source so the Oracle data pump Export dump file will includes Instantiation CSNs. Replicat uses the per table instantiation CSN set by the Oracle data pump (on import) to filter out trail records. On the target, the data pump import populates the system tables and views with instantiation SCNs using the
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
parameter to enable table-level instantiation filtering.
Examples
-
The following example causes one of the following: the primary key to be logged for an Oracle table; supplemental data to be logged for a SQL Server or DB2 table. This would also enable the table for auto capture.
ADD TRANDATA hr.employees
-
The following example enables the unconditional supplemental logging of all of the key and non-key columns for the table named
acct
. This would also enable the table for auto capture.ADD TRANDATA hr.employees ALLCOLS
-
The following Oracle Database example causes the primary key to be logged plus the non-key columns name and address. This would also enable the table for auto capture.
ADD TRANDATA hr.employees, COLS (name, address)
-
The following Oracle Database example prevents the primary key from being logged, but logs the non-key columns
name
andpid
instead. This would also enable the table for auto capture.ADD TRANDATA hr.employees, NOKEY, COLS (name, pid)
-
The following example adds logging although it does not prepare the table for instantiation. This would also enable the table for auto capture.
ADD TRANDATA hr.employees PREPARECSN NONE
-
The following example suppresses additional table information processing. This would also enable the table for auto capture.
ADD TRANDATA hr.employees.*name NOVALIDATE
Parent topic: GGSCI Command Line Interface Commands
ALLOWNESTED
ALLOWNESTED
and NOALLOWNESTED
commands to enable or disable the use of nested OBEY
files. A nested
OBEY
file is one that contains another OBEY
file,
see OBEY.
Syntax
ALLOWNESTED | NOALLOWNESTED
-
ALLOWNESTED
-
Enables the use of nested
OBEY
files. There is no maximum of the number of nested files. -
NOALLOWNESTED
-
This is the default setting. If you try to run a nested obey file, then it displays the following error
Nested OBEY scripts not allowed. Use ALLOWNESTED to allow nested scripts.
-
The following example illustrates a nested
OBEY
file. Assume anOBEY
file namedaddcmds.txt
. Inside this file, there is anotherOBEY
command that calls theOBEY
file namedstartcmds.txt
, which executes another set of commands.The following example creates an OBEY file (
adder.oby
) to add Extract, Replicat, and trail files and includes another obey file (startcmds.oby).vi ADDER.oby DBLOGIN USERIDALIAS ggeast ADD EXTRACT exte, TRANLOG, BEGIN NOW ADD EXTTRAIL east/ea, EXTRACT exte ADD REPLICAT repe, EXTTRAIL east/ea, BEGIN NOW OBEY startcmds.oby
The
startcmds.oby
file contains the following:START EXTRACT * INFO EXTRACT *, DETAIL START REPLICAT * INFO REPLICAT *, DETAIL
To exectue these obey commands from the command line, you need to use the ALLOWNESTED command:
ALLOWNESTED OBEY adder.oby
Parent topic: GGSCI Command Line Interface Commands
ALTER CREDENTIALSTORE
Use the ALTER CREDENTIALSTORE
command to configure database
credentials for Oracle GoldenGate. You can also use this command to manage user ID and
password pairs in the credential store. This command enables you to add credentials to the
credential store and to specify different aliases for connecting to the database.
The use of a credential store is not supported for the NonStop platforms.
GGSCI Syntax
ALTER CREDENTIALSTORE {
ADD USER userid [@tns_alias] | REPLACE USER userid | DELETE USER userid }
[NOPASSWORD | PASSWORD password]
[ALIAS alias]
[DOMAIN domain]
-
ADD USER userid
-
Adds the specified user and its alias to the credential store. If the
ALIAS
option is not used, the alias defaults to the user name. A credential can only be entered once unless theALIAS
option is used to specify a different alias for each one. Unless thePASSWORD
option is used, the command prompts for the password of the specified user. The user can be an actual user name or a SQL*Net connect string.In GGSCI, for multitenant databases with different users for the CDB and the PDB, you need to specify
@TNS_Service_Name
when adding a user to the credential store. -
REPLACE USER
userid
-
Changes the password of the specified user. If the
ALIAS
option is not used, the alias defaults to the user name. You cannot change the alias or domain of a user with this option, but you can use theADD USER
option to add a new entry for the user under the desiredALIAS
orDOMAIN
. Unless thePASSWORD
option is used, the command prompts for the new password for the specified user. -
DELETE USER
userid
-
Removes the credential for the specified user from the credential store. If the
ALIAS
option is not used, the alias defaults to the user name. If the user ID and alias are not the same, you must specify both user ID and alias. For example:ALTER CREDENTIALSTORE DELETE USER c##ggadmin alias ggadmin
-
NOPASSWORD | PASSWORD
password
-
The
NOPASSWORD
option is the alternative to thePASSWORD
option when using external authentication because password is not required for external authentication such as using Kerberos authentication. After theNOPASSWORD
option is set, theDBLOGIN
command can be used to access the database without a password.Also see USERIDALIAS and USERID | NOUSERID parameters in the Reference for Oracle GoldenGate.
Specify the user's password using the
PASSWORD
option. The password is echoed (not obfuscated) when this option is used. If this option is omitted, the command prompts for the password, which is obfuscated as it is typed (recommended as more secure). -
ALIAS
alias
-
Specifies an alias for the user name. Use this option if you do not want the user name to be in a parameter file or command. If
ALIAS
is not used, the alias defaults to theUSER
name, which then must be used in parameter files and commands where a login is required. You can create multiple entries for a user, each with a different alias, by using theADD USER
option withALIAS
. -
DOMAIN
domain
-
Saves the credential user under the specified domain name. Enables the same alias to be used by multiple Oracle GoldenGate installations that use the same credential store. The default domain is
Oracle GoldenGate
. For example, the administrators of system 1 might not want system 2 to have access to the same credentials that are used on system 1. Those credentials can be stored asALIAS pdbeast
, for example, underDOMAIN system1
, while a different set of credentials can be stored forALIAS pdbwest
underDOMAIN system2
.
Examples
-
The following example (Admin Client) adds a user named
ggadmin
but with external authentication and therefore uses theNOPASSWORD
option.ALTER CREDENTIALSTORE ADD USER /@ggadmin nopassword alias pdbeast
The output shows:
2020-06-22T21:08:33Z INFO OGG-15102 Credential store created.
Running the
INFO CREDENTIALSTORE
command, you can check the add user to the credentialstore:INFO CREDENTIALSTORE Default domain: OracleGoldenGate Alias: pdbeast Userid: /@ggadmin
After you update the credentialstore to use the
NOPASSWORD
option, you can use theDBLOGIN
command with Kerberos authentication for your database.DBLOGIN USERIDALIAS pdbeast Successfully logged into database pdbeast.
-
This example adds a user named
ggadmin
but omits thePASSWORD
specification, so the command prompts for pggeast's password.ALTER CREDENTIALSTORE ADD USER ggadmin Password: ********
-
This example adds the user
ggadmin
with his passwordtiger
and specifies the alias aspdbeast
.ALTER CREDENTIALSTORE ADD USER ggadmin PASSWORD tiger ALIAS pdbeast
-
This example adds the user
ggadmin
under the domain ofOracleGoldenGate
.ALTER CREDENTIALSTORE ADD USER ggadmin ALIAS pdbeast DOMAIN OracleGoldenGate Password: ********
-
This example issues two
ALTER CREDENTIALSTORE
commands, each of which adds aggadmin
entry, but with a different alias.ALTER CREDENTIALSTORE ADD USER ggadmin ALIAS pdbeast Password: ******** ALTER CREDENTIALSTORE ADD USER ggadmin ALIAS pdbwest Password: ********
-
The following shows how the
DELETE USER
option works with and without theALIAS
option.The following command deletes the
user1
entry for which theALIAS
is the same as the user name.ALTER CREDENTIALSTORE DELETE USER ggadmin Alias: pdbeast Userid: ggadmin
The following command deletes the entry for user
ggadmin
that is associated with the aliaspdbeast
.ALTER CREDENTIALSTORE DELETE USER ggadmin ALIAS pdbeast Alias: pdbeast Userid: ggadmin
-
This example uses a SQL*Net connect string as the user value. In this case, the
PASSWORD
option is omitted. The person issuing the command is prompted for the password, which is hidden.ALTER CREDENTIALSTORE ADD USER ggadmin@pdbeast ALIAS pdbeast
Parent topic: GGSCI Command Line Interface Commands
ALTER EXTRACT
Use ALTER EXTRACT
for the following purposes:
-
To change the attributes of an Extract group created with the
ADD EXTRACT
command. -
To increment a trail to the next file in the sequence.
-
To upgrade to an integrated capture configuration.
-
To downgrade from an integrated capture configuration.
-
To position any given Db2 for i journal at a specific journal sequence number.
Before using this command, stop the Extract with the STOP
EXTRACT
group_name
command.
GGSCI Syntax
ALTER EXTRACT group-name
[, BEGIN {NOW | yyyy-mm-ddT[ hh:mi:[ss[.cccccc]]]Z}]
[, START]
[, EXTSEQNO sequence-number]
[, EXTRBA offset-number]
[, TRANLOG LRI LRI_number
]
[, THREAD number]
[, LSN value]
[, SCN value
]
[, ETROLLOVER]
[, INFO EXTRACT group-name]
The following ALTER EXTRACT
options are supported for Db2 for
i to position Extract for a given journal:
ALTER EXTRACT {BEGIN {NOW | yyyy-mm-ddT[ hh:mi:[ss[.cccccc]]]Z}
[JOURNAL journal_library/journal_name [JRNRCV receiver_library/
receiver_name]] |
, EOF [JOURNAL journal_library/journal_name
[JRNRCV receiver_library/receiver_name]] |
, SEQNO sequence_number [JOURNAL journal_library/journal_name
[JRNRCV receiver_library/receiver_name]]}
-
BEGIN {NOW |
yyyy-mm-ddT[ hh:mi:[ss[.cccccc]]]Z
[JOURNAL
journal_library/journal_name
[JRNRCV
receiver_library/ receiver_name
]] |
, EOF [JOURNAL
journal_library/journal_name
[JRNRCV
receiver_library/receiver_name
]] |
, SEQNO
sequence_number
[JOURNAL
journal_library/journal_name
[JRNRCV
receiver_library/receiver_name
]]
-
These IBM for i options allow journal-specific Extract positioning after the global start point is issued with
ADD EXTRACT
. A specific journal position set withALTER EXTRACT
does not affect any global position that was previously set withADD EXTRACT
orALTER EXTRACT
; however a global position set withALTER EXTRACT
overrides any specific journal positions that were previously set in the same Extract configuration.Note:
SEQNO
, when used with a journal inALTER EXTRACT
, is the journal sequence number that is relative to that specific journal, not the system sequence number that is global across journals.
-
group-name
-
The name of the Extract group that is to be altered.
-
{BEGIN {NOW | yyyy-mm-ddT[ hh:mi:[ss[.cccccc]]]Z}
-
-
NOW
-
For all databases except Db2 LUW,
NOW
specifies the time at which theALTER EXTRACT
command is issued.NOW
specifies the time at which theADD EXTRACT
command is issued.For Db2 LUW, only commit and end transaction records contain timestamps, so the Extract starting position can only be calculated relative to those timestamps. This is a limitation of the API that is used by Oracle GoldenGate. It must be noted that positioning by timestamp is not accurate and can also take a long time. It is recommended to use LRI or EOF options wherever possible.
Do not use
NOW
for a data pump Extract except to bypass data that was captured to the trail prior to theADD EXTRACT
statement. -
YYYY-MM-DDThh:mm:ssZ
-
A date and time (timestamp) in the given form. For example,
2017-07-14T14:54:45Z
. -
yyyy-mm-dd[ hh:mi:[ss[.cccccc]]]
-
A date and time (timestamp) in the given form. For an Oracle Extract in integrated mode, the timestamp value must be greater than the timestamp at which the Extract was registered with the database.
Positioning by timestamp in a SQL Server transaction log is affected by the following characteristics of SQL Server:
-
The timestamps recorded in the SQL Server transaction log use a 3.3333 microsecond (ms) granularity. This level of granularity may not allow positioning by time between two transactions, if the transactions began in the same 3.3333 ms time interval.
-
Timestamps are not recorded in every SQL Server log record, but only in the records that begin and commit the transaction, as well as some others that do not contain data.
-
SQL Server timestamps are not from the system clock, but instead are from an internal clock that is specific to the individual processors in use. This clock updates several times a second, but between updates it could get out of sync with the system clock. This further reduces the precision of positioning by time.
-
Timestamps recorded for log backup files may not precisely correspond to times recorded inside the backup (however this imprecision is less than a second).
Positioning to an LSN is precise.
Postitioning by timestamp in PostgreSQL includes the following scenarios:-
Scenario 1
Iftrack_commit_timestamp
isoff
, the following output will be displayed when the Extract process starts irrespective of what positioning method is used:2020-04-29 02:15:54 INFO OGG-01517 Position of first record processed LSN: 0/2222C20, Jan 1, 1970 12:00:00 PM.
-
Scenario 2
If thetrack_commit_timestamp
is enabled before Extract is registered then the correct timestamp will be displayed once the records are pushed in the source database as mentioned in the following example:2020-04-29 02:19:07 INFO OGG-01515 Positioning to begin time Apr 29,2020 2:18:38 AM.
-
Scenario 3
Iftrack_commit_timestamp
is enabled after the Extract is registered, then there may be chances that the older records are available in the log for which the commit timestamp is not built up with the associatedtransaction ID
. In that case, if Extract does not get the timestamp then it will fallback using the default timestamp mentioned in scenario 1. The output will be similar to the following:020-04-29 01:55:07 INFO OGG-01517 Position of first record processed LSN: 0/221D028, Jan 1, 1970 12:00:00 PM.
-
Past timestamp cannot be specified if the replication slot has moved away.
-
-
-
START
-
Valid for PostgreSQL.
Adds an Extract without mentioning
BEGIN NOW
orLSN
. Extract will start from the replication slot restart position automatically. -
ADD_EXTRACT_
attribute
-
You can change any of the attributes specified with the
ADD EXTRACT
command, except for the following:-
Altering an Extract specified with the
EXTTRAILSOURCE
option. -
Altering the number of RAC threads specified with the
THREADS
option.
For these exceptions, delete the Extract group and then add it again.
If using the
BEGIN
option, do not combine other options in the statement. Issue separate statements, for example:ALTER EXTRACT exte, BEGIN 2019-01-01 ALTER EXTRACT exte, ETROLLOVER ALTER EXTRACT exte, SCN 789000
If using the
SCN
orBEGIN
option for Integrated Extract, it requires aDBLOGIN
, and the SCN or timestamp value specified cannot be below the outbound server's first SCN or timestamp. To find the outbound server's first SCN, issue the following command:INFO EXTRACT
group_name
, SHOWCH DETAILThe first SCN value is listed as shown in the following example:
Integrated Extract outbound server first scn: 0.665884 (665884)
-
-
EXTSEQNO
sequence_number
-
Valid for a primary Extract in classic capture mode for Oracle, and a data pump Extract. Not supported for an Extract in integrated mode.
Specifies either of the following:
-
sequence number of an Oracle redo log and RBA within that log at which to begin capturing data.
-
the relative byte address within that file at which to begin capturing data. Together these specify the location in the TMF Master Audit Trail (MAT).
-
the file in a trail in which to begin capturing data (for a data pump). Specify the sequence number, but not any zeroes used for padding. For example, if the trail file is
\aa000026
, you would specifyEXTSEQNO 26
. By default, processing begins at the beginning of a trail unless this option is used.
Contact Oracle Support before using this option. For more information, go to
http://support.oracle.com.
-
-
EXTRBA
offset_number
-
Valid for Oracle Database. Specifies the relative byte address within a transaction log at which to begin capturing data.
The required format is
0X
nnn
, wherennn
is a 1 to 20 digit hexadecimal number (the first character is the digit zero, and the second character can be upper or lower case letterx
). -
EOF
-
Valid for Db2 for i, Db2 LUW, MySQL, PostgreSQL, and SQL Server.
Configures processing to start at the end of the log files (or journals) that the next record will be written to. Any active transactions will not be captured.
For Db2 LUW, it configures processing to start at the active
LRI
value in the log files. The activeLRI
is the position at the end of the log files that the next record will be written to. Any active transactions will not be captured.For PostgreSQL,
DBLOGIN
is required for position byEOF
.For MySQL, it finds the position corresponding to the end of the file and starts reading transactions from there. The EOF position is not exact, if data is continuously written to the binary log.
-
TRANLOG LRI
LRI_number
-
(Db2 LUW) You can use this option for Db2 LUW systems to specify the LRI record value for the checkpoint transaction log.
For PostgreSQL,
DBLOGIN
is required for position byEOF
. -
REPORT
file_name
-
Specifies the full path name of an Extract report file in a location other than the default of
dirrpt
within the Oracle GoldenGate directory. -
SCN
value
-
Valid for Oracle.
Starts Extract at the transaction in the redo log that has the specified Oracle system change number (SCN). This option is valid for Extract both in classic capture and integrated modes. For Extract in integrated mode, the SCN value must be greater than the SCN at which the Extract was registered with the database. If using the SCN or
BEGIN
option for Integrated Extract, it requires aDBLOGIN
, and the SCN or timestamp value specified cannot be below the outbound server's first SCN or timestamp. -
LSN value
- Valid for SQL Server, Db2 z/OS, and PostgreSQL.
-
LOGNUM
-
Valid for MySQL.
This is the log file number. For example, if the required log file name is test.000034, theLOGNUM
value is 34. Extract will search for this log file.Note:
In Microservices Architecture,ADD EXTRACT
will fail if theLOGNUM
value contains zeroes preceding the value. For example,ADD EXTRACT ext1, TRANLOG, LOGNUM 000001, LOGPOS 0
will fail. Instead, setLOGNUM
to 1 for this example to succeed. -
LOGPOS
-
Valid for MySQL.
is an event offset value within the log file that identifies a specific transaction record. Event offset values are stored in the header section of a log record. To position at the beginning of a
binlog
file, set theLOGPOS
as 0. -
ETROLLOVER
-
Use for manual recovery situations that require repositioning and regenerating trail files for a primary Extract and when upgrading Oracle GoldenGate from a previous version.
Causes Extract to create a new incarnation of the trail file and increments to the next file in the trail sequence when restarting, requiring readers such as pump or Replicat, to be manually repositioned to the new trail sequence number.
From 19c onwards, during Distribution Service processing:-
If the source primary Extract is upgraded with target trail file
ETROLLOVER
, then theDistribution Service automatically detects the source trail fileETROLLOVER
and starts reading from the next input trail file. This is the same command as data pumpALTER EXTRACT groupname EXTSEQNO seqno+1 EXTRBA 0
. -
After upgrading the Distribution Service, it automatically performs the ETROLLOVER for the output trail file upon restarting and writes the next trail file properly. This is the same as data pump
ALTER EXTRACT groupname ETROLLOVER
.
-
-
DESC '
description
'
-
Specifies a description of the group, such as
'Extracts account_tab on Serv1'
. Enclose the description within single quotes. You may use the abbreviated keywordDESC
or the full wordDESCRIPTION
. -
ENCRYPTIONPROFILE
- Specifies the name of the encryption profile for the Extract. This name
is case sensitive so you must use the exact name that you entered with
ADD EXTRACT
. -
CRITICAL
-
Indicates if the process is critical for the deployment.
-
PROFILE
- Name of the auto start profile. This name is case sensitive so you must
use the exact name that you entered with
ADD EXTRACT
. -
AUTOSTART
- Specifies whether the managed process has to be started automatically when
the Administration Service starts. The default value is
YES
. -
RETRIES
-
The maximum number of tries for restarting the task before canceling retry efforts. This is optional.
-
WAITSECONDS
-
The duration (in seconds) in which the retries are counted.
-
RESETSECONDS
- Resets the duration in which the retries are counted.
-
DISABLEONFAILURE
-
If set to
TRUE
, then the task is disabled when the number of retries is exhausted. -
INFO EXTRACT group-name, SHOWCH DETAIL
-
The first SCN value is listed as shown in the following example:
Integrated Extract outbound server first scn: 0.665884 (665884)
Examples
-
The following alters Extract to start processing data from January 1, 2011.
ALTER EXTRACT exte, BEGIN 2019-01-01
-
The following alters Extract to start processing at a specific location in the trail.
ALTER EXTRACT exte, EXTSEQNO 26, EXTRBA 338
-
The following alters Extract in a SQL Server environment to start at a specific LSN.
ALTER EXTRACT exte, LSN 3454:875:445
-
The following alters Extract to increment to the next file in the trail sequence.
ALTER EXTRACT exte, ETROLLOVER
-
The following alters Extract to upgrade to integrated capture.
ALTER EXTRACT exte, UPGRADE INTEGRATED TRANLOG
-
The following alters Extract in an Oracle environment to start processing data from source database SCN 778899.
ALTER EXTRACT exte, SCN 778899
-
The following shows
ALTER EXTRACT
for an IBM for i journal start point.ALTER EXTRACT exte, SEQNO 1234 JOURNAL accts/acctsjrn
-
The following shows
ALTER EXTRACT
for an IBM for i journal and receiver start point.ALTER EXTRACT exte, SEQNO 1234 JOURNAL accts/acctsjrn JRNRCV accts/jrnrcv0005
-
The following example alters an Extract on a Db2 LUW system.
ALTER EXTRACT exte, TRANLOG LRI 8066.322711
Parent topic: GGSCI Command Line Interface Commands
ALTER EXTTRAIL
Use ALTER EXTTRAIL
to change the attributes of a trail
that was created with the ADD EXTTRAIL
command (a trail on the
local system). The change takes effect the next time that Extract starts.
Before using this command, stop the Extract using the STOP
EXTRACT
group_name
command.
Syntax
ALTER EXTTRAIL trail_name, EXTRACT group_name
[, MEGABYTES n]
-
trail_name
-
The relative or fully qualified path name of the trail.
-
group_name
-
The name of the Extract group to which the trail is bound.
-
MEGABYTES
n
-
The maximum size of a file, in megabytes. The default is 500. After using this option, issue the
SEND EXTRACT
command with theROLLOVER
option to close the current trail file and open a new one.
Examples
ALTER EXTTRAIL north/ea, EXTRACT exte, MEGABYTES 200
Parent topic: GGSCI Command Line Interface Commands
ALTER HEARTBEATTABLE
Use ALTER HEARTBEATTABLE
to alter existing seed,
heartbeat, and history table options that you set with ADD
HEARTBEATTABLE
.
This command requires a DBLOGIN
. On a CDB database, a
PDB login is required.
Valid for Oracle, Db2 z/OS, Db2 LUW, Db2 for i, MySQL, PostgreSQL, SQL Server. This command is not valid for TimesTen and Teradata.
Syntax
ALTER HEARTBEATTABLE
[, FREQUENCY number_in_seconds]
[, RETENTION_TIME number_in_days] |
[, PURGE_FREQUENCY number_in_days]
[, TARGETONLY | NOTARGETONLY]
-
FREQUENCY number_in_seconds
-
Specifies how frequently heartbeat records are generated. The default is 60 seconds.
Consider the following limits:
-
For Oracle Database, the minimum value is 0 and the maximum is 7999.
-
For DB2 for i Series, the minimum value is 0 and the maximum is 7999.
-
For DB2 LUW and DB2 z/OS, the minimum value is 60 and the maximum is 7999.
-
The frequency for DB2 z/OS and DB2 LUW must be a multiple of 60 for values less than 3600 and multiples for 3600 for values greater or equal to 3600.
-
For MySQL, the minimum value is 0 and the maximum is 7999.
-
For SQL Server, the minimum value is 10 and the maximum is 7999. It supports 0, which disables the SQL Server Agent Heartbeat table
UPDATE
job, but can only be set withALTER HEARTBEATTABLE
. -
For PostgreSQL, the minimum value is 60 and the maximum is 7999.
-
Databases that support setting
FREQUENCY
to 0 will pause the heartbeat record scheduler.
-
-
RETENTION_TIME
-
Specifies that heartbeat entries older than the retention time in the heartbeat history table are purged. The default is 30 days.
The minimum value for all databases is 1 and the maximum is 2147483646.
-
PURGE_FREQUENCY
-
Specifies how often the purge scheduler is run to delete table entries that are older than the retention time from the heartbeat history table. The default is 1 day.
For DB2 LUW and DB2 z/OS, the minimum value is 1 and the maximum is 31.
For all other supported databases, the minimum value is 1 and the maximum value is 199.
-
TARGETONLY | NOTARGETONLY
-
Valid for Oracle Database, DB2 for i Series, DB2 LUW, DB2 z/OS, MySQL, PostgreSQL, and SQL Server.
TARGETONLY
modifies existing heartbeat seed and heartbeat tables by disabling supplemental logging on both tables. It drops the existing scheduler job for updating the heartbeat table.NOTARGETONLY
modifies existing heartbeat seed and heartbeat tables by enabling supplemental logging on both tables. It creates a new scheduler job for updating the heartbeat table.
Examples
ALTER HEARTBEATTABLE FREQUENCY 60
ALTER HEARTBEATTABLE RETENTION_TIME 30
ALTER HEARTBEATTABLE PURGE_FREQUENCY 1
ALTER HEARTBEATTABLE NOTARGETONLY
Parent topic: GGSCI Command Line Interface Commands
ALTER REPLICAT
Use ALTER REPLICAT
to change the attributes of a Replicat
group that was created with the ADD REPLICAT
command. Before using this
command, stop Replicat by issuing the STOP REPLICAT
command. If this is
a coordinated Replicat group, the ALTER
takes effect for all threads
unless the threadID
option is used.
Note:
ALTER REPLICAT
does
not support switching from regular Replicat mode to coordinated mode. You must stop
processes, make certain all of the en route data is applied to the target, roll the
trail to a new trail, drop and recreate the Replicat group in coordinated mode, and then
start the processes again.
Syntax
ALTER REPLICAT
group-name|
[, PARALLEL [, INTEGRATED] | INTEGRATED | COORDINATED [MAXTHREADS number]]
]|
option [, . . .]
{, SPECIALRUN |
EXTFILE file-name |
EXTTRAIL trail-name }
[, PARAMS file_name]
[, REPORT file_name]
[, BEGIN {NOW | yyyy-mm-dd[ hh:mi:[ss[.cccccc]]]
}]
[, EXTSEQNO trail-sequence-number, EXTRBA trail-offset-number ]
[, DESC description ]|
[, ENCRYPTIONPROFILE encryption-profile-name ]|
[, CRITICAL [ YES | NO ] ]|
[, PROFILE profile-name|
[, AUTOSTART [ YES | NO ]|
[, DELAY delay-number ] ]|
[, AUTORESTART [ YES | NO ]|
[, RETRIES retries-number ]|
[, WAITSECONDS wait-number ]|
[, RESETSECONDS reset-number ]|
[, DISABLEONFAILURE [ YES | NO ] ] ] ]
}
-
group_name
[
threadID
]
-
The name of the Replicat group or a thread of a coordinated Replicat that is to be altered. To specify a thread, use the full thread name, such as
ALTER REPLICAT exte, EXTSEQNO 53.
If a thread ID is not specified, the
ALTER
takes effect for all threads of the Replicat group. -
option [, . . .]
-
Use this to change any description or service option that was configured with the
ADD REPLICAT
command, except for theCHECKPOINTTABLE
andNODBCHECKPOINT
options.There is no option to alter a Replicat withCHECKPOINTTABLE
to replicate with or withoutCHECKPOINTTABLE
. However, if you are switching from integrated to nonintegrated Replicat, you can use a nonintegrated Replicat without a checkpoint table, as shown in the following example:ADD REPLICAT repe, INTEGRATED, EXTTRAIL ea, ggadmin.ggs_checkpoint
ALTER REPLICAT repe, NONINTEGRATED, CHECKPOINTTABLE ggadmin.ggs_checkpoint
-
PARALLEL
-
Oracle only.
Adds the Replicat in parallel mode. In this mode, Replicat applies transactions in parallel to improve the performance. It takes into account dependencies between transactions.
PARALLEL INTEGRATED
Replicat is valid for Oracle only. It adds the parallel Replicat in integrated mode, which like Integrated Replicat leverages the apply processing functionality that is available within the Oracle Database. -
INTEGRATED
-
Switches Replicat from non-integrated mode to integrated mode. Transactions currently in process are applied before the switch is made, see Switching Replicat from Integrated Mode to Nonintegrated Mode.
-
NONINTEGRATED, CHECKPOINTTABLE
owner.table
-
(Oracle) Switches Replicat from integrated mode to non-integrated mode.
For
CHECKPOINTTABLE
, specify the owner and name of a checkpoint table. This table must be created with theADD CHECKPOINTTABLE
command before issuingALTER EXTRACT
withNONINTEGRATED
, see Deciding Which Apply Method to Use for more information about integrated Replicat.
-
SPECIALRUN
-
Creates a Replicat special run as a task. Either
SPECIALRUN
, EXTFILE
, orEXTTRAIL
is required. When Extract is inSPECIALRUN
mode, do not start Replicat with theSTART REPLICAT
command. Do not use this option with theINTEGRATED
orCOORDINATED
option.-
EXTFILE
file_name
-
Specifies the relative or fully qualified name of an extract file that is specified with
RMTFILE
in the Extract parameter file. Do not use this option with theINTEGRATED
option. -
EXTTRAIL
trail_name
-
Specifies the relative or fully qualified name of a trail that was created with the
ADD RMTTRAIL
orADD EXTTRAIL
command.
-
-
BEGIN {NOW |
yyyy-mm-dd[ hh:mm
[
:
ss
[.
cccccc
]]]}
-
Defines an initial checkpoint in the trail.
-
EXTSEQNO
sequence_number
-
Specifies the sequence number of the file in a trail in which to begin processing data. Specify the sequence number, but not any zeroes used for padding. For example, if the trail file is
\aa000000026
, you would specifyEXTSEQNO 26
. If not specified, the default value is zero.By default, processing begins at the beginning of a trail unless this option is used. To use
EXTSEQNO
, you must also useEXTRBA
. Contact Oracle Support before using this option. -
EXTRBA
rba
-
Specifies the relative byte address within the trail file that is specified by
EXTSEQNO
. Contact Oracle Support before using this option. -
PARAMS
file_name
-
Specifies a parameter file in a location other than the default of
dirprm
within the Oracle GoldenGate directory. Specify the fully qualified path name. -
REPORT file_name
-
Specifies the full path name of a process report file in a location other than the default of
dirrpt
within the Oracle GoldenGate directory. -
DESC '
description
' -
Specifies a description of the group, such as
'Loads account_tab on Serv2
'. Enclose the description within quotes. You can use either the abbreviated keywordDESC
or the full wordDESCRIPTION
. -
ENCRYPTIONPROFILE
- Specifies the name of the encryption profile for the Replicat.
-
CRITICAL
-
Indicates if the process is critical for the deployment.
-
PROFILE
-
There are options to select the Default or Custom profiles, if you've created one using the Profile page in Distribution Service.
-
AUTOSTART
-
Select this option to start the process when starts.
-
DELAY
-
Time to wait in seconds before starting the process.
-
AUTORESTART
-
Controls how the process will be restarted if it terminates.
-
RETRIES
-
The maximum number of the task should be restarted before canceling retry efforts. This is optional.
-
WAITSECONDS
- Specifies the time to wait before performing the retries.
-
RESETSECONDS
- Resets the time for waiting.for retries.
-
DISABLEONFAILURE
-
If this option is enabled, then the task is disabled when the number of retries is exhausted.
Parent topic: GGSCI Command Line Interface Commands
ALTER RMTTRAIL
Use ALTER RMTTRAIL
to change the attributes of a trail
that was created with the ADD RMTTRAIL
command (a trail on a remote
system). The change takes effect the next time that Extract starts.
Syntax
ALTER RMTTRAIL trail_name, EXTRACT group_name
[, MEGABYTES n]
-
trail_name
-
The relative or fully qualified path name of the trail. For example,
dirdat\ea.
-
group_name
-
The name of the Extract group to which the trail is bound.
-
MEGABYTES
n
-
The maximum size of a file, in megabytes. The default is 500. After using this option, issue the
SEND EXTRACT
command with theROLLOVER
option to close the current trail file and open a new one.
Example
ALTER RMTTRAIL north/ea, EXTRACT exte, MEGABYTES 200
Parent topic: GGSCI Command Line Interface Commands
CLEANUP CHECKPOINTTABLE
Not valid for Replicat for Java, Oracle GoldenGate Applications Adapter, or Oracle GoldenGate Big Data.
Use CLEANUP CHECKPOINTTABLE
to remove
checkpoint records from the checkpoint table when there is no checkpoint file
associated with it in the working Oracle GoldenGate directory (from which GGSCI was
started). This command should only be used on Replicats that have been deleted. The
purpose of this command is to remove checkpoint records that are not needed any
more, either because groups were changed or files were moved.
Use the DBLOGIN
command to establish a database
connection before using this command.
Syntax
CLEANUP CHECKPOINTTABLE [[container.
| catalog
.]owner.table]
-
container
. |
catalog
. -
The Oracle pluggable database, if applicable. If this option is omitted, the catalog or pluggable database defaults to the one that is associated with the
SOURCEDB
,USERID
, orUSERIDALIAS
portion of theDBLOGIN
command (depending on the database). -
owner.table
-
The owner and name of the checkpoint table to be cleaned up. If an owner and name are not specified, the table that is affected is the one specified with the
CHECKPOINTTABLE
parameter in theGLOBALS
parameter file.
Example
CLEANUP CHECKPOINTTABLE ggadmin.ggs_checkpoint
Parent topic: GGSCI Command Line Interface Commands
CLEANUP REPLICAT
Use CLEANUP REPLICAT
to delete run history for a
specified Replicat group. The cleanup keeps the last run record intact so that
Replicat can resume processing from where it left off.
Before using this command, stop Replicat by issuing the STOP
REPLICAT
command.
Syntax
CLEANUP REPLICAT group_name
[threadID
] [, SAVE count]
-
group_name
[
threadID
]
-
One of the following:
-
group_name
: The name of a Replicat group or a wildcard (*) to specify multiple groups. For example,T*
cleans up all Replicat groups whose names begin with T. If the specified group (or groups) is a coordinated Replicat, the cleanup applies to all threads. -
group_name
threadID
: A thread of a coordinated Replicat, identified by its full name (group name plusthreadID
), such asfinance003
.
-
-
SAVE
count
-
Excludes the specified number of the most recent records from the cleanup.
Examples
Parent topic: GGSCI Command Line Interface Commands
CLEANUP EXTRACT
Use CLEANUP EXTRACT
to delete run history for the
specified Extract group. The cleanup keeps the last run record intact so that
Extract can resume processing from where it left off. Before using this command,
stop Extract by issuing the STOP EXTRACT
command.
Syntax
CLEANUP EXTRACT group_name [, SAVE count]
Parent topic: GGSCI Command Line Interface Commands
DELETE CHECKPOINTTABLE
Not valid for Replicat for Java, Oracle GoldenGate Applications Adapter, or Oracle GoldenGate Big Data.
Use DELETE CHECKPOINTTABLE
to drop a checkpoint table
from the database. Use the DBLOGIN
command to establish a database
connection before using this command.
If the checkpoint table is deleted while Replicat is still running and transactions are occurring, Replicat will abend with an error that the checkpoint table could not be found. However, the checkpoints are still maintained on disk in the checkpoint file. To resume processing, add the checkpoint table back under the same name. Data in the trail resumes replicating. Then, you can delete the checkpoint table.
Syntax
DELETE CHECKPOINTTABLE [[container. | catalog.]owner.table] [!]
-
container
. |
catalog
. -
The Oracle pluggable database, if applicable. If this option is omitted, the catalog or pluggable database defaults to the one that is associated with the
SOURCEDB
,USERID
, orUSERIDALIAS
portion (depending on the database) of theDBLOGIN
command. -
owner.table
-
The owner and name of the checkpoint table to be deleted. An owner and name are not required if they are the same as those specified with the
CHECKPOINTTABLE
parameter in theGLOBALS
file. -
!
-
Bypasses the prompt that confirms intent to delete the table.
Example
DELETE CHECKPOINTTABLE ggadmin.ggs_checkpoint
Parent topic: GGSCI Command Line Interface Commands
DELETE CREDENTIALSTORE
Use the DELETE CREDENTIALSTORE
command to remove a
credential store from the system. The credential store and its contents are
permanently deleted.
The use of a credential store is not supported for the NonStop platforms.
Syntax
DELETE CREDENTIALSTORE
Parent topic: GGSCI Command Line Interface Commands
DELETE EXTRACT
Use DELETE EXTRACT
to delete an Extract group. This
command deletes the checkpoint file that belongs to the group, but leaves the
parameter file intact. You can then re-create the group or delete the parameter file
as needed.
Before using DELETE EXTRACT
, stop Extract with the
STOP EXTRACT
command.
Syntax
DELETE EXTRACT group_name [!]
Parent topic: GGSCI Command Line Interface Commands
DELETE EXTTRAIL
Use DELETE EXTTRAIL
to delete the record of checkpoints
associated with a trail on a local system. Checkpoints are maintained in a file
bearing the same name as the group in the dirchk
sub-directory of
the Oracle GoldenGate directory.
This command only deletes references to the specified trail from the checkpoint file. It does not delete the trail files.
Syntax
DELETE EXTTRAIL trail_name
Example
DELETE EXTTRAIL north/ea
Parent topic: GGSCI Command Line Interface Commands
DELETE TRANDATA
Use DELETE TRANDATA
to do one of the
following:
-
Db2 LUW and Db2 z/OS: Alters the table to
DATA CAPTURE NONE.
-
Oracle: Disable supplemental logging.
-
SQL Server: Stops extended logging for a table.
-
PostgreSQL: Alters the table’s
REPLICA IDENTITY
toNOTHING
.
By default,
this command attempts to remove the supplemental logging of the key columns that are
used by Oracle GoldenGate (can be the primary key, a unique key,
KEYCOLS
columns, or all columns) and also the scheduling
columns. The scheduling columns are the primary key, all of the unique keys, and all
of the foreign keys. To delete the logging of the Oracle GoldenGate key columns, but
not the scheduling columns, include the NOSCHEDULINGCOLS
option
with DELETE TRANDATA
. If ADD TRANDATA
was issued
with the ALLCOLS
option, use DELETE TRANDATA
with
the ALLCOLS
option to remove the supplemental logging of all of the
columns, including the Oracle GoldenGate key columns. DELETE
TRANDATA
will disable logical replication for a table after all the
table level supplemental logging has been disabled or removed. This behavior is
supported from Oracle 19c and higher. Also, if a the DELETE
TRANDATA
operation removes the last supplemental log group on a table
then it will also perform an ALTER TABLE owner.table DISABLE LOGICAL
REPLICATION
too.
ADD TRANDATA table_name
command to enable logical replication after running DELETE
TRANDATA
.
Note:
You cannot enable logical replication usingADD
SCHEMATRANDATA
.
Use the DBLOGIN
command to establish a database
connection before using this command. The user specified with this command must have
the same privileges that are required for ADD TRANDATA
.
Syntax
DELETE TRANDATA [container
.]owner.table [NOSCHEDULINGCOLS | ALLCOLS]
-
[
container
.]
owner.table
-
The pluggable database (if this is an Oracle multitenant container database), owner and name of the table or file. A wildcard can be used for any name component.
-
NOSCHEDULINGCOLS
-
Prevents the command from removing the supplemental logging of the scheduling columns of the specified table. The scheduling columns are the primary key, all of the unique keys, and all of the foreign keys of a table.
-
ALLCOLS
-
Removes the supplemental logging of all of the columns of the specified table.
Parent topic: GGSCI Command Line Interface Commands
DELETE TRACETABLE
Use DELETE TRACETABLE
to delete a trace table.
Use the DBLOGIN
command to establish a database
connection before using this command.
Syntax
DELETE TRACETABLE [[container
.]owner.table]
-
container
-
The pluggable database, if the database is a multitenant container database (CDB).
-
owner.table
-
The owner and name of the trace table to be deleted. An owner and name are not required if the owner is the same as that specified with the
USERID
orUSERIDALIAS
parameter and the trace table has the default name ofGGS_TRACE
. The schema is required. -
!
-
Deletes the trace table without prompting.
Example
DELETE TRACETABLE ora_trace
Parent topic: GGSCI Command Line Interface Commands
DELETE SCHEMATRANDATA
Valid for Oracle.
Use DELETE SCHEMATRANDATA
to remove the Oracle
schema-level supplemental logging that was added with the ADD
SCHEMATRANDATA
command. Use the DBLOGIN
command to
establish a database connection before using this command. The user that is
specified with this command must have the privilege to remove supplemental log
groups.
By default, this command attempts to remove the supplemental logging of
the key columns that are used by Oracle GoldenGate (can be the primary key, a unique
key, KEYCOLS
columns, or all columns) and also the scheduling
columns. The scheduling columns are the primary key, all of the unique keys, and all
of the foreign keys. To delete the logging of the Oracle GoldenGate key columns, but
not the scheduling columns, include the NOSCHEDULINGCOLS
option
with DELETE SCHEMATRANDATA
. If ADD SCHEMATRANDATA
was issued with the ALLCOLS
option, use DELETE
SCHEMATRANDATA
with the ALLCOLS
option to remove the
supplemental logging of all of the columns, including the Oracle GoldenGate key
columns.
Syntax
DELETE SCHEMATRANDATA schema [NOSCHEDULINGCOLS | ALLCOLS]
-
schema
-
The schema for which you want supplemental logging to be removed. Do not use a wildcard. If the source is an Oracle multitenant container database, make certain to log into the pluggable database that contains the schema for which you want to remove the logging.
-
NOSCHEDULINGCOLS
-
Prevents the command from removing the supplemental logging of the scheduling columns of the tables in the specified schema. The scheduling columns are the primary key, all of the unique keys, and all of the foreign keys of a table.
-
ALLCOLS
-
Removes the supplemental logging of all of the columns of the tables in the specified schema.
Parent topic: GGSCI Command Line Interface Commands
DELETE RMTTRAIL
Use DELETE RMTTRAIL
to delete the record of checkpoints
associated with a trail on a remote system. Checkpoints are maintained in a file
bearing the same name as the group in a sub-directory of the Oracle GoldenGate
directory.
This command only deletes references to the specified trail from the checkpoint file. It does not delete the trail files.
Syntax
DELETE RMTTRAIL trail_name[,EXTRACT group_name
}
Parent topic: GGSCI Command Line Interface Commands
DELETE REPLICAT
Use DELETE REPLICAT
to delete a Replicat group. This
command deletes the checkpoint file but leaves the parameter file intact. Then you
can re-create the group or delete the parameter file as needed. This command frees
up trail files for purging, because the checkpoints used by the deleted group are
removed (assuming no other processes are reading the file).
Use the DBLOGIN
command before deleting any Replicats so that the
checkpoint data or any internal information stored in the database for that Replicat
can also be cleaned up.
Before using DELETE REPLICAT
, stop Replicat with the
STOP REPLICAT
command.
If this is an integrated Replicat (Oracle only) or a non-integrated Replicat that uses a checkpoint table, do the following after you stop Replicat:
-
Log into the database by using the
DBLOGIN
command.DBLOGIN
enablesDELETE REPLICAT
to delete the checkpoints from the checkpoint table of a non-integrated Replicat or to delete the inbound server that an integrated Replicat uses. -
Issue
DELETE REPLICAT
.
Syntax
DELETE REPLICAT group_name
[!]
-
group_name
-
The name of a Replicat group or a wildcard (*) to specify multiple groups. For example,
T*
deletes all Replicat groups whose names begin with T. -
!
-
Use this option to force the Replicat group to be deleted if the
DBLOGIN
command is not issued before theDELETE REPLICAT
command is issued. If the group is a non-integrated Replicat, this option deletes the group's checkpoints from the checkpoint file on disk, but not from the checkpoint table in the database. If using this option to delete an integrated Replicat group, you must use theUNREGISTER REPLICAT
command to delete the inbound server from the target database. This option can also be used to ignore the prompt that occurs when a wildcard specifies multiple groups.
Note:
The basicDELETE
REPLICAT
command commits an existing Replicat transaction, but the
!
option prevents the commit.
Example
DELETE REPLICAT reps
Parent topic: GGSCI Command Line Interface Commands
DELETE PROCEDURETRANDATA
Valid for Oracle.
Use DELETE PROCEDURETRANDATA
to remove supplemental
logging for Procedural Replication.
Use the DBLOGIN
command to establish a database connection before
using this command.
Syntax
DELETE PROCEDURETRANDATA
Parent topic: GGSCI Command Line Interface Commands
DELETE MASTERKEY
Use the DELETE MASTERKEY
command to mark a version of a
master key for deletion. Routinely deleting older versions of a master key ensures
that they cannot be used maliciously.
The OPEN WALLET
command must be used before using this
command or any of the commands that add or renew the master keys or purge the
wallet.
To view the version of a master key, use the INFO MASTERKEY
command.
This command marks a version for deletion, but does not physically
remove it from the wallet, see PURGE WALLET
to remove the master key version permanently.
Note:
For Oracle GoldenGate deployments using a shared wallet, the older versions of the master key should be retained after the master key is renewed until all processes are using the newest version. The time to wait depends on the topology, latency, and data load of the deployment. A minimum wait of 24 hours is a conservative estimate, but you may need to perform testing to determine how long it takes for all processes to start using a new key. To determine whether all of the processes are using the newest version, view the report file of each Extract immediately after renewing the master key to confirm the last SCN that was mined with the old key. Then, monitor the Replicat report files to verify that this SCN was applied by all Replicat groups. At this point, you can delete the older versions of the master key.See UNDELETE MASTERKEY
to reverse a deletion made by DELETE
MASTERKEY
.
Once a version number is used, the wallet reserves it forever, and no
other key of the same version can be generated. For example, you cannot mark version
2 of a key for deletion, then purge the wallet to remove it, and then issue
RENEW MASTERKEY
to add a version 2 again. Even though only
version 1 of the key remains in the wallet after the purge, the renewal generates
version 3, not version 2.
The use of a wallet and master key is not supported for the NonStop platforms.
Syntax
DELETE MASTERKEY
{VERSION version
| RANGE FROM begin_value
TO end_value
| ALL}
-
VERSION
version
-
Specifies a single version to be marked for deletion.
-
RANGE FROM
begin_value
TO
end_value
-
Specifies a range of versions to be marked for deletion. The versions must be contiguous. For example, specifying
RANGE FROM 3 TO 6
marks versions 3, 4, 5, and 6. -
ALL
-
Marks all versions of the master key for deletion, including the currently active one. When this option is used, it should always be followed by a
RENEW MASTERKEY
command to create a new, current version of the master key.
Examples
-
This command marks one version of the master key for deletion and returns a message similar to the one shown.
DELETE MASTERKEY VERSION 10
Output:
Version 10 of Masterkey 'OGG_DEFAULT_MASTERKEY' deleted from wallet at location './dirwlt'.
-
This command marks versions 3, 4, 5, and 6 for deletion and returns a message similar to the one shown.
DELETE MASTERKEY RANGE FROM 3 TO 6
Example:
DELETE MASTERKEY ALL
Output:
2019-11-21T19:38:08Z INFO OGG-06148 Version 1 of master key 'OGG_DEFAULT_MASTERKEY' in Oracle Wallet was deleted.
Parent topic: GGSCI Command Line Interface Commands
DELETE HEARTBEATTABLE
Use DELETE HEARTBEATTABLE
to delete tables, procedures,
schedulers, and views. This command requires a DBLOGIN
. On a CDB
database, a PDB login is required.
Syntax
For Oracle database, the administration of the heartbeat
table is simiplified by eliminating the need for GGSCHEMA
or
HEARTBEATTABLE
parameter. To implement this, Extracts and
Replicat look in the schema of the ER processes connected user for the heartbeat
tables, except for Oracle CDB root Extract. In case of CDB root Extract,
GGSCHEMA
is used.
DELETE HEARTBEATTABLE group_name
Parent topic: GGSCI Command Line Interface Commands
DELETE HEARTBEATENTRY
Use DELETE HEARTBEATENTRY
to delete the records in the
heartbeat table with the specified process name either in the incoming or outgoing
path columns. This command required a DBLOGIN
. On a CDB database, a
PDB login is required.
Oracle GoldenGate for Oracle database simplifies the administration of the heartbeat table by eliminating the need for GGSCHEMA or HEARTBEATTABLE parameter. To implement this, Extracts and Replicat look in the schema of the ER processes connected user for the heartbeat tables, except for Oracle CDB root Extract. In case of CDB root Extract, GGSCHEMA is used.
Parent topic: GGSCI Command Line Interface Commands
EDIT PARAMS
Use EDIT PARAMS
to create or change a parameter file.
By default, the editor is set with your EDITOR
environment
variable. You can change the default editor with the SET EDITOR
command.
GGSCI Syntax
EDIT PARAMS {MGR | group_name | file_name}
-
MGR
-
Opens a parameter file for the Manager process.
-
group_name
-
Opens a parameter file for the specified Extract or Replicat group.
-
file_name
-
Opens the specified file. When you create a parameter file with
EDIT PARAMS
, it is saved to a sub-directory of the Oracle GoldenGate directory. You can create a parameter file in a directory other than the default by specifying the full path name, but you must also specify the full path name with thePARAMS
option of theADD EXTRACT
orADD REPLICAT
command when you create the process group.
Caution:
Use this command to view or edit an existing parameter file that is in a character set of the local operating system. If you use theCHARSET
option with non-local
operating system character set, then contents may become corrupted.
Parent topic: GGSCI Command Line Interface Commands
INFO ALL
Use INFO ALL
to display the status and lag (where
relevant) for all Manager, Extract, and Replicat processes on a system. When Oracle
Grid Infrastructure Agents (XAG) Clusterware components are in use, the relevant
information is also displayed.
The basic command, without options, displays only online (continuous)
processes .and Microservices Architecture services. To display tasks, use either
INFO ALL TASKS
or INFO ALL ALLPROCESSES
.
The Status
and Lag at Chkpt
(checkpoint) fields display the same process status and lag as the INFO
EXTRACT
and INFO REPLICAT
commands.
If Replicat is in coordinated mode, INFO ALL
shows only
the coordinator thread. To view information about individual threads, use
INFO REPLICAT
.
Syntax
INFO ALL [TASKS | ALLPROCESSES]
Parent topic: GGSCI Command Line Interface Commands
HISTORY
Use HISTORY
to view a list of the most recently issued
commands since the startup of the session. You can use the ! command or the FC to re-execute a command in the list.
Example
HISTORY 7
The result of this command would be similar to:
1: ADD EXTRACT
2: ADD EXTTRAIL
3: INFO CHECKPOINTTABLE
4: EDIT PARAMS
5: START EXTRACT
6: HISTORY
Parent topic: GGSCI Command Line Interface Commands
HELP
Use HELP
to obtain information about an Oracle
GoldenGate command in both GGSCI and Admin Client. The basic command returns a list
of commands. The command
option restricts the output to that
of a specific command.
Example
To display all commands that begin with ADD
.
HELP ADD
Parent topic: GGSCI Command Line Interface Commands
FLUSH SEQUENCE
This command updates an Oracle sequence so that initial redo records are available at the time that Extract starts to capture transaction data. Normally, redo is not generated until the current cache is exhausted. The flush gives Replicat an initial start point with which to synchronize to the correct sequence value on the target system. From then on, Extract can use the redo that is associated with the usual cache reservation of sequence values.
Syntax
FLUSH SEQUENCE owner.sequence
Example
FLUSH SEQUENCE ggadmin.seq
Parent topic: GGSCI Command Line Interface Commands
EXIT
Use EXIT
to exit the Oracle GoldenGate command line
interface.
Syntax
EXIT
Parent topic: GGSCI Command Line Interface Commands
ENCRYPT PASSWORD
Use ENCRYPT PASSWORD
to encrypt a password that is used
in an Oracle GoldenGate parameter file or command.
GGSCI Syntax
ENCRYPT PASSWORD password
[AES128 | AES192 | AES256]
ENCRYPTKEY {key_name
| DEFAULT}
-
password
-
The login password. Do not enclose the password within quotes. Do not use commas in passwords. If the password is case-sensitive, type it that way.
-
AES128 | AES192 | AES256
-
Specifies the encryption algorithm to use.
-
AES128
uses the AES-128 cipher, which has a key size of 128 bits. -
AES192
uses the AES-192 cipher, which has a key size of 192 bits. -
AES256
uses the AES-256 cipher, which has a key size of 256 bits.
If no algorithm is specified, AES128 is the default for all database types where
BLOWFISH
is the default. AES is not supported for those platforms.All of the AES ciphers have a 128-bit block size.
To use AES encryption for any database other than Oracle, the path of the
lib
sub-directory of the Oracle GoldenGate installation directory must be specified as an environment variable before starting any processes:-
UNIX: Specify the path as an entry to the
LD_LIBRARY_PATH
orSHLIB_PATH
variable. For example:SETENV LD_LIBRARY_PATH ./lib:$LD_LIBRARY_PATH
-
Windows: Add the path to the
PATH
variable.
You can use the
SETENV
parameter to set it as a session variable for the process. -
-
ENCRYPTKEY {
key_name
| DEFAULT}
-
Specifies the encryption key.
-
key_name
-
Specifies the logical name of a user-created encryption key in a local
ENCKEYS
lookup file. The key name is used to look up the actual key in theENCKEYS
file. A user-created key and an associatedENCKEYS
file is required when using AES encryption. To usekey_name
, generate the key withKEYGEN
or another utility, then store it in anENCKEYS
file on the source and target systems. The AES ciphers have a 128-bit block size. -
DEFAULT
-
(GGSCI) Directs Oracle GoldenGate to generate a random key that is stored in the trail so that decryption can be performed by the downstream process. This type of key is insecure and should not be used in a production environment.
ENCRYPT PASSWORD
returns an error ifDEFAULT
is used with any AES algorithm.
-
Parent topic: GGSCI Command Line Interface Commands
INFO CHECKPOINTTABLE
Not valid for Replicat for Java, Oracle GoldenGate Applications Adapter, or Oracle GoldenGate Big Data.
Use INFO CHECKPOINTTABLE
to confirm the existence of a
checkpoint table and view the date and time that it was created. It returns a
message similar to the following:
Checkpoint table ggadmin.ggs_checkpoint created 2017-01-06T11:51:53.
Use the DBLOGIN
command to establish a database
connection before using this command.
Syntax
INFO CHECKPOINTTABLE [[container
. | catalog
.]owner.table]
-
container
. |
catalog
. -
The Oracle pluggable database, if applicable. If this option is omitted, the catalog or pluggable database defaults to the one that is associated with the
SOURCEDB
,USERID
, orUSERIDALIAS
portion of theDBLOGIN
command (depending on the database). -
owner.table
-
The owner and name of the checkpoint table. An owner and name are not required if they are the same as those specified with the
CHECKPOINTTABLE
parameter in theGLOBALS
file. You can use a wildcard (*
).
Example
INFO CHECKPOINTTABLE ggadmin.ggs_checkpointtable
Parent topic: GGSCI Command Line Interface Commands
INFO TRANDATA
Use INFO TRANDATA
to get the following information:
-
Db2 LUW and Db2 z/OS: Determine whether
DATA CAPTURE
is enabled or not. -
Oracle: Determine whether supplemental logging is enabled, and to show the names of columns that are being logged supplementary. If all columns are being logged, the notation
ALL
is displayed instead of individual column names. Displays any SCN instantiation information. -
SQL Server: Determine whether or not extended logging is enabled for a table.
-
PostgreSQL: Determine whether supplemental logging is enabled and to show the current
REPLICA IDENTITY
setting.
Use the DBLOGIN
command to establish a database
connection before using this command.
GGSCI Syntax
INFO TRANDATA [container
.]owner.table [NOVALIDATE]
-
[
container
.]
owner.table
-
The pluggable database (if this is an Oracle multitenant container database), owner and name of the table or file for which you want to view trandata information. The owner is not required if it is the same as the login name that was specified by the
DBLOGIN
command. A wildcard can be used for the table name but not the owner name. -
NOVALIDATE
-
Suppresses additional information about the table being handled being processed by
ADD TRANDATA
. By default, this option is enabled. The additional information processing creates a lapse time on command response so this option can be used to increase response time.
Parent topic: GGSCI Command Line Interface Commands
INFO TRACETABLE
Use the INFO TRACETABLE
command to verify the existence
of the specified trace table in the local instance of the database. If the table
exists, Oracle GoldenGate displays the name and the date and time that it was
created; otherwise Oracle GoldenGate displays a message stating that the table does
not exist. Use the DBLOGIN
command to establish a database
connection before using this command.
Syntax
INFO TRACETABLE [[container
.]owner.table]
-
container
-
The pluggable database, if the database is a multitenant container database (CDB).
-
owner.table
-
The owner and name of the trace table to be verified. An owner and name are not required if the owner is the same as that specified with the
USERID
orUSERIDALIAS
parameter and the trace table has the default name ofGGS_TRACE
.
Example
INFO TRACETABLE pdbeast.ggadmin.ora_trace
In this example, pdbeast
is the container name and
ggadmin
is the user id.
Parent topic: GGSCI Command Line Interface Commands
INFO SCHEMATRANDATA
Use INFO SCHEMATRANDATA
to determine whether Oracle
schema-level supplemental logging is enabled for the specified schema or if any
instantiation information is available. Use the DBLOGIN
command to
establish a database connection before using this command.
Syntax
INFO SCHEMATRANDATA schema
Example
INFO SCHEMATRANDATA hr
Parent topic: GGSCI Command Line Interface Commands
INFO RMTTRAIL
Use INFO RMTTRAIL
to retrieve configuration information for a
remote trail. It shows the name of the trail, the Extract that writes to it, the position of
the last data processed, and the assigned maximum file size.
Syntax
INFO RMTTRAIL trail_name
Examples
-
INFO RMTTRAIL north\ea
-
INFO RMTTRAIL *
-
The following is a sample of
INFO RMTTRAIL
output.Extract Trail: /ogg/var/lib/data/ea Seqno Length: 9 Flip Seqno Length: no Extract: exte Seqno: 4 RBA: 78066 File Size: 500M
Extract Trail: /ogg/dirdat/ea Seqno Length: 9 Flip Seqno Length: no Extract: exte Seqno: 4 RBA: 78066 File Size: 500M
Parent topic: GGSCI Command Line Interface Commands
INFO REPLICAT
Use INFO REPLICAT
to retrieve the processing history of a
Replicat group. The output of this command includes:
-
The status of Replicat (
STARTING
,RUNNING
,STOPPED
orABENDED
).STARTING
means that the process has started but has not yet locked the checkpoint file for processing. -
(Oracle Database) The Replicat mode: non-integrated or integrated.
-
Whether or not Replicat is in coordinated mode and, if so, how many threads it currently uses.
-
Approximate Replicat lag.
-
The trail from which Replicat is reading.
-
Replicat run history, including checkpoints in the trail.
-
Information about the Replicat environment.
The basic command displays information only for online (continuous) Replicat groups. Tasks are excluded.
Replicat can be stopped or running when INFO REPLICAT
is
issued. In the case of a running process, the status of RUNNING
can mean
one of the following:
-
Active
: Running and processing (or able to process) data. This is the normal state of a process after it is started. -
Suspended
: The process is running, but suspended due to anEVENTACTIONS SUSPEND
action. In a suspended state, the process is not active, and no data can be processed, but the state of the current run is preserved and can be continued by issuing theRESUME
command. The RBA in theINFO
command reflects the last checkpointed position before the suspend action. To determine whether the state is active or suspended, issue theSEND REPLICAT
command with theSTATUS
option.
About Lag
Checkpoint Lag
is
the lag, in seconds, at the time the last checkpoint was written to the checkpoint file. For
example, consider the following:
-
Current time = 15:00:00
-
Last checkpoint = 14:59:00
-
Timestamp of the last record processed =14:58:00
Assuming these values, the lag is reported as 00:01:00 (one minute, the difference between 14:58 and 14:59).
A lag value of UNKNOWN
indicates that Replicat could be
running but has not yet processed records, or that the source system's clock is ahead of the
target system's clock (due to clock imperfections, not time zone differences). For more
precise lag information, use LAG REPLICAT
(see "LAG REPLICAT
"). For complete end to end lag, use the
heartbeat table functionality. See ADD HEARTBEATTABLE
.
GGSCI Syntax
INFO REPLICAT group_name
[threadID
]
[, SHOWCH [checkpoint_number]]
[, DETAIL]
[, TASKS | ALLPROCESSES]
-
group_name
[
threadID
]
-
The name of:
-
A Replicat group or a wildcard (*) to specify multiple groups. For example,
T*
shows information for all Replicat groups whose names begin with T. -
A thread of a coordinated Replicat, identified by its full name. For example,
fin003
shows information only for thread 3 of thefin
group.
-
-
SHOWCH [
checkpoint_number
]
-
Displays current checkpoint details, including those recorded to the checkpoint file and those recorded to the checkpoint table, if one is being used. The database checkpoint display includes the table name, the hash key (unique identifier), and the create timestamp. Specify a value for checkpoints-number to include the specified number of previous checkpoints as well as the current one.
-
DETAIL
-
Displays detail information. For an Oracle target,
DETAIL
displays the name of the inbound server when Replicat is in integrated mode.If Replicat is in coordinated mode,
DETAIL
will display only the active threads. For example, if a Replicat namedCR
was created with a maximum of 15 threads, but only threads 7-9 are running,INFO REPLICAT
group_name
withDETAIL
will show only the coordinator thread (CR
),CR007
,CR008
, andCR009
. Checkpoints exist for the other threads, but they are not shown in the command output.To view
LOGBSN
information with theDETAIL
output, issue theDBLOGIN
command before you issueINFO REPLICAT
. If the command is issued for a specific thread ID of a coordinated Replicat, only theLOGBSN
for that thread is displayed. Otherwise, theLOGBSN
s for all threads are displayed. For more information about recovering Extract by using theLOGBSN
, see Restarting a Primary Extract after System Failure or Corruption. -
TASKS
-
Displays only Replicat tasks. Tasks that were specified by a wildcard argument are not displayed by
INFO REPLICAT
. -
ALLPROCESSES
-
Displays all Replicat groups, including tasks.
Examples
- The following example run the command from the Admin
Client.
INFO REPLICAT repe
Output:
REPLICAT REPE Initialized 2019-11-20 23:54 Status STOPPED Description demo replicat Checkpoint Lag 00:00:00 (updated 00:00:14 ago) Log Read Checkpoint File ea000000000 First Record RBA 0 Settings Profile default_security Encryption Profile LocalWallet
Parent topic: GGSCI Command Line Interface Commands
INFO PROCEDURETRANDATA
Use INFO PROCEDURETRANDATA
to display information about
procedure-level supplemental database logging (Procedural Replication).
Use the DBLOGIN
command to establish a database connection before
using this command.
Syntax
INFO PROCEDURETRANDATA
Parent topic: GGSCI Command Line Interface Commands
INFO PARAM
Use INFO PARAM
to retrieve the parameter definition
information. If a name matches multiple records, they are all displayed. If the
query parameter has child options, they are not displayed in the output though their
names are listed in the Options tab. To display the full record of an option, the
full name in the form of parameter.option
should be queried
separately.
This parameter infrastructure allows unlimited levels of options. So, the full name of a parameter or option might have numbers of segments, such as A.B.C.D.
Syntax
INFO PARAM name
Example 1
The following example uses GETINSERTS
with INFO
PARAM
along with the output.
INFO PARAM GETINSERTS
param name : getinserts
opposite : ignoreinserts
description : Include insert records.
argument : boolean
default : true
component(s): EXTRACT - All
: REPLICAT - All
platform(s) : All
database(s) : All
status : current
mandatory : false
dynamic : false
relations : none
Example 2
The following example uses the DBOPTIONS.DEFERREFCONST
with
INFO PARAM
to show how to get specific details about a
parameter option.
INFO PARAM DBOPTIONS.DEFERREFCONST
param name : dboptions.deferrefconst
description : Sets constraints to DEFERRABLE to delay the checking and enforcement of cascade delete and cascade update referential integrity constraints by the Oracle target database until the Replicat transaction is committed.
component(s): REPLICAT - All
platform(s) : All
database(s) : Oracle 11g
: Oracle 12c
: Oracle 18c
: Oracle 19c
: Oracle 21c
status : current
mandatory : false
dynamic : false
relations : none
Parent topic: GGSCI Command Line Interface Commands
INFO MASTERKEY
Use the INFO MASTERKEY
command to view the contents of
a currently open master-key wallet. If a wallet store does not exist, a new Wallet
store file is created. This Wallet store file is then used to host different
encrypted keys as they are created.
The default output shows the version history of the master key, with the creation date of a version and the status of the version. The status can be one of the following:
-
Current: Indicates this is the current version of the master key, which is the latest version of the master key as well.
Note:
Changing prior key versions to Current status is not allowed. -
Available: Indicates this version is not the current one but can be made active, if needed.
-
Deleted: Indicates that this version is marked to be deleted when the PURGE WALLET command is issued.
The use of a wallet and master key is not supported for the NonStop platforms.
Syntax
INFO MASTERKEY [VERSION version
]
Example: Admin Client
INFO MASTERKEY
Output:
Masterkey Name: OGG_DEFAULT_MASTERKEY
Version Creation Date Status
1 2019-11-21T19:37:23.000+00:00 Current
Parent topic: GGSCI Command Line Interface Commands
INFO HEARTBEATTABLE
Use INFO HEARTBEATTABLE
to display information about
the heartbeat tables configured in the database.
This command requires a DBLOGIN
. For an Oracle
multitenant database, the DBLOGIN to a PDB is required.
Oracle GoldenGate for Oracle database simplifies the
administration of the heartbeat table by eliminating the need for
GGSCHEMA
or HEARTBEATTABLE
parameter. To
implement this, Extracts and Replicat look in the schema of the ER processes
connected user for the heartbeat tables, except for Oracle CDB root Extract. In case
of CDB root Extract, GGSCHEMA
is used.
Syntax
INFO HEARTBEATTABLE
Parent topic: GGSCI Command Line Interface Commands
INFO EXTTRAIL
Use INFO EXTTRAIL
to retrieve configuration information for a
local trail. It shows the name of the trail, the Extract that writes to it, the position of
the last data processed, and the assigned maximum file size.
Syntax
INFO EXTTRAIL trail_name
Parent topic: GGSCI Command Line Interface Commands
INFO EXTRACT
Use INFO EXTRACT
to view the following information.
-
The status of Extract (
STARTING
,RUNNING
,STOPPED
orABENDED
).STARTING
means that the process has started but has not yet locked the checkpoint file for processing. -
Approximate Extract lag.
-
Checkpoint information.
-
Process run history.
-
The trails to which Extract is writing.
-
Status of upgrade to, or downgrade from, Integrated Extract
Extract can be running or stopped when INFO EXTRACT
is issued.
In the case of a running process, the status of RUNNING
can mean one of the
following:
-
Active: Running and processing (or able to process) data. This is the normal state of a process after it is started.
-
Suspended: The process is running, but suspended due to an
EVENTACTIONS SUSPEND
action. In a suspended state, the process is not active, and no data can be processed, but the state of the current run is preserved and can be continued by issuing theSEND EXTRACT
command with theRESUME
option. The RBA in theINFO
command reflects the last checkpointed position before the suspend action. To determine whether the state is active or suspended, issue theSEND EXTRACT
command with theSTATUS
option.
The basic command displays information only for online (continuous) Extract processes. Tasks are excluded.
About Extract Lag
The Checkpoint Lag
field of the INFO EXTRACT
output reflects the lag, in seconds, at the time that the last checkpoint was written to the
trail. For example, if the following is true:
-
Current time = 15:00:00
-
Last checkpoint = 14:59:00
-
Timestamp of the last record processed = 14:58:00
Then the lag is reported as 00:01:00 (one minute, the difference between 14:58 and 14:59).
A lag value of UNKNOWN
indicates that the process could be
running but has not yet processed records, or that the source system's clock is ahead of the
target system's clock (due to clock imperfections, not time zone differences), see
LAG EXTRACT
. For a more
precise calculation of the lag and for determining the overall lag, use the heartbeat table.
See ADD HEARTBEATTABLE
.
Syntax
INFO EXTRACT group_name
[, SHOWCH [checkpoint_number]]
[, DETAIL]
[, TASKS | ALLPROCESSES]
[, UPGRADE | DOWNGRADE]
[, CONTAINERS]
-
group_name
-
The name of an Extract group or a wildcard (
*
) to specify multiple groups. For example,T*
shows information for all Extract groups whose names start with T. You can list the PDBs registered with a specified Extract group name. -
SHOWCH [
checkpoint_number
]
-
The basic command shows information about the current Extract checkpoints. Extract checkpoint positions are composed of read checkpoints in the data source and write checkpoints in the trail. The trail type
RMTTRAIL
orEXTTRAIL
is also noted.Optionally, specify a value for
checkpoint_number
to include the specified number of previous checkpoints as well as the current one.Note:
You might see irregular indents and spacing in the output. This is normal and does not affect the accuracy of the information.See About Checkpoints, which includes descriptions of the types of checkpoints made by each process and the internal metadata entries that are included in the display.
-
DETAIL
-
Displays the following:
-
Extract run history, including start and stop points in the data source, expressed as a time.
-
Trails to which Extract is writing.
-
-
TASKS
-
Displays only Extract tasks. Tasks that were specified by a wildcard argument are not displayed by
INFO EXTRACT
. -
ALLPROCESSES
-
Displays all Extract groups, including tasks.
-
UPGRADE | DOWNGRADE
-
Valid for an Oracle Database only.
-
UPGRADE
displays whether the Extract can be upgraded from classic capture mode to integrated capture mode. -
DOWNGRADE
displays whether the Extract can be downgraded from integrated capture mode to classic capture mode.
If Extract cannot be upgraded or downgraded, the reason why is displayed.
A wildcarded Extract name is not allowed with this option.
Before using this command, issue the
DBLOGIN
command. -
-
CONTAINERS
-
Lists the PDBs that are registered with the specified Extract group. However, the command errors if it is run in non-CDB mode or the Extract group doesn't exist. Issue the
DBLOGIN
command before using this option.
Examples
- The following example shows the use of the
INFO EXTRACT
command from the Admin Client.INFO EXTRACT exte EXTRACT exte Initialized 2019-11-20 23:22 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:00:09 ago) Log Read Checkpoint Oracle Redo Logs 2019-11-20 23:22:54 Seqno 0, RBA 0 SCN 0.0 (0) Auto Start Delay: 0 Encryption Profile LocalWallet
- The
following example shows the command output with
DBLOGIN
for PostgreSQL:EXTRACT extw Last Started 2020-07-01 01:40 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:09 ago) Process ID 101094 VAM Read Checkpoint 2020-07-01 01:40:15.170802 Replication Slot edf_tc1_2c59ae112747afa1 is active with PID 101120 in database postgres Slot Restart LSN 1/B982ED98 Slot Flush LSN 1/B982ED98 Current Log Position 1/B982EDD0
- The
following example shows the command output without
DBLOGIN
for PostgreSQL:EXTRACT extw Last Started 2020-07-01 01:40 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:04 ago) Process ID 101094 VAM Read Checkpoint 2020-07-01 01:44:16.045693 LSN: 1/B99B7E60 Replication Slot Unavailable(requires DBLOGIN) Slot Restart LSN Unavailable(requires DBLOGIN) Slot Flush LSN Unavailable(requires DBLOGIN) Current Log Position Unavailable(requires DBLOGIN)
Parent topic: GGSCI Command Line Interface Commands
INFO ER
Use the INFO ER
command to get information on multiple
Extract and Replicat groups as a unit. Use it with wildcards to affect every Extract
and Replicat group that satisfies the wildcard. This information is returned:
-
The status of Extract (
STARTING
,RUNNING
,STOPPED
orABENDED
).STARTING
means that the process has started but has not yet locked the checkpoint file for processing. -
Approximate Extract lag.
-
Checkpoint information.
-
Process run history.
-
The trails to which Extract is writing.
-
Status of upgrade to, or downgrade from, integrated capture. The process can be running or stopped when
INFO ER
is issued. With a running process, the status ofRUNNING
can mean one of the following: -
Active: Running and processing (or able to process) data. This is the normal state of a process after it is started.
-
Suspended: The process is running, but suspended due to an
EVENTACTIONS SUSPEND
action. In a suspended state, the process is not active, and no data can be processed, but the state of the current run is preserved and can be continued by issuing theSEND
command with theRESUME
option. The RBA in theINFO
command reflects the last checkpointed position before the suspend action. To determine whether the state is active or suspended, issue theSEND
command with theSTATUS
option.
GGSCI Syntax
INFO ER group_wildcard
-
group_wildcard
-
The wildcard specification for the groups that you want to affect with the command. Oracle GoldenGate automatically increases internal storage to track up to 100,000 wildcard entries.
-
SHOWCH checkpoints-number
-
The basic command shows information about the current Extract checkpoints. Extract checkpoint positions are composed of read checkpoints in the data source and write checkpoints in the trail. The trail type (
RMTTRAIL
orEXTTRAIL
) is also noted.Optionally, specify a value for checkpoints-number to include the specified number of previous checkpoints as well as the current one.
Note:
You might see irregular indents and spacing in the output. This is normal and does not affect the accuracy of the information. -
DETAIL
-
Displays the Extract run history, including start and stop points in the data source expressed as a time and the trails to which Extract is writing.
-
TASKS
-
Displays Extract tasks only. Tasks that were specified by a wildcard argument are not displayed by
INFO EXTRACT
. -
ALLPROCESSES
-
Displays all Extract groups, including tasks.
Example
INFO ER *
Extract EXTE Last Started 2022-06-07 07:33 Status RUNNING
Description east
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Process ID 60782
Log Read Checkpoint Oracle Integrated Redo Logs
2022-09-05 23:17:19
SCN 0.89996333 (89996333)
Settings Profile Default
Encryption Profile LocalWallet
Replicat REPE Last Started 2022-06-15 03:45 Status RUNNING
Description east
INTEGRATED
Parallel
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Process ID 96740
Log Read Checkpoint File east/ea000000009
2022-09-05 23:17:19.808198 RBA 9382
Settings Profile Default
Encryption Profile LocalWallet
Parent topic: GGSCI Command Line Interface Commands
INFO CREDENTIALSTORE
Use the INFO CREDENTIALSTORE
command to get information about
an Oracle GoldenGate credential store. This information includes the aliases that a
credential store contains and the user IDs that correspond to them. The encrypted passwords
in the credential store are not returned.
The credential store location is identified by the
CREDENTIALSTORELOCATION
parameter in the GLOBALS
file.
The use of a credential store is not supported for the NonStop platforms.
Syntax
INFO CREDENTIALSTORE [DOMAIN domain
]
-
DOMAIN
domain
-
Returns the aliases and user IDs for a specific domain. For security purposes, if the
DOMAIN
option is omitted, only the aliases and user IDs under the default domain ofOracleGoldenGate
are shown. It is not possible to seeDOMAIN
credentials unless the person issuing theINFO CREDENTIALSTORE
command knows the name of the domain. SeeALTER CREDENTIALSTORE
for more information about domains.
Examples
Parent topic: GGSCI Command Line Interface Commands
KILL ER
Use the KILL ER
command to forcefully terminate multiple
Extract and Replicat groups as a unit. Use it with wildcards to affect every Extract
and Replicat group that satisfies the wildcard.
Terminating a process leaves the most recent checkpoint in place, and
the current transaction is rolled back by the database, guaranteeing that no data is
lost when the process is restarted. Use this command only if the process cannot be
stopped gracefully with the STOP REPLICAT
command.
Syntax
KILL ER group_name
Example
KILL ER extegrp
Parent topic: GGSCI Command Line Interface Commands
KILL EXTRACT
Use KILL EXTRACT
to end an Extract process running in
regular or PASSIVE
mode. Use this command only if a process cannot
be stopped gracefully with the STOP EXTRACT
command. The Manager
process will not attempt to restart an ended Extract process.
Syntax
KILL EXTRACT group_name
Example
KILL EXTRACT exte
Parent topic: GGSCI Command Line Interface Commands
KILL REPLICAT
Use KILL REPLICAT
to terminate a Replicat process, which
leaves the most recent checkpoint in place and the current transaction is rolled
back by the database. This guarantees that no data is lost when the process is
restarted.
The Manager process will not attempt to restart a terminated Replicat
process. Use this command only if Replicat cannot be stopped gracefully with the
STOP REPLICAT
command.
Syntax
KILL REPLICAT group_name
Example
KILL REPLICAT repe
Parent topic: GGSCI Command Line Interface Commands
LAG ER
Use the LAG ER
to get lag information on multiple
Extract and Replicat groups as a unit. Use it with wildcards to affect every Extract
and Replicat group that satisfies the wildcard. For descriptions and optional
parameters for this command, see LAG EXTRACT
.
Syntax
LAG ER group_name
Example
LAG ER exte
Sending GETLAG request to Extract group EXTE ...
Last record lag 2 seconds.
At EOF, no more records to process
No Replicat groups found, but some coordinated threads may have been excluded.
Parent topic: GGSCI Command Line Interface Commands
LAG EXTRACT
Use LAG EXTRACT
to determine a true lag time between
Extract and the data source. LAG EXTRACT
calculates the lag time
more precisely than INFO EXTRACT
because it communicates with
Extract directly, rather than reading a checkpoint position in the trail.
For Extract, lag is the difference, in seconds, between the time that a record was processed by Extract (based on the system clock) and the timestamp of that record in the data source.
If the heartbeat functionality is enabled, you can view the associated lags.
GGSCI Syntax
LAG EXTRACT
[, group_name
]
[, GLOBAL]
Parent topic: GGSCI Command Line Interface Commands
LAG REPLICAT
Use LAG REPLICAT
to determine a true lag time between
Replicat and the trail. LAG REPLICAT
estimates the lag time more
precisely than INFO REPLICAT
because it communicates with Replicat
directly rather than reading a checkpoint position.
For Replicat, lag is the difference, in seconds, between the time that the last record was processed by Replicat (based on the system clock) and the timestamp of the record in the trail.
If the heartbeat functionality is enabled, you can view the
associated lags. A DBLOGIN
is required to view the heartbeat lag.
From 21c onward, the DB_UNIQUE_NAME
is displayed if it
exists for a remote database, otherwise the DB_NAME
value is
displayed.
GGSCI Syntax
LAG EXTRACT [, group_name
[threadID
]name]
[, GLOBAL]
-
group_name
[
threadID
]
-
The name of:
-
A Replicat group or a wildcard (*) to specify multiple groups. For example,
T*
shows lag for all Replicat groups whose names begin with T. -
A thread of a coordinated Replicat, identified by its full name. For example,
repe003
shows lag for thread 3 of coordinated Replicatrepe
.
-
-
GLOBAL
-
Displays the lags in the
GG_LAGS
view.
Parent topic: GGSCI Command Line Interface Commands
LIST TABLES
Use LIST TABLES
to list all tables in the database that
match the specification provided with the command argument. Use the
DBLOGIN
command to establish a database connection before using
this command. If logging into an Oracle multitenant container database, log in to
the pluggable database that contains the tables that you want to list.
If you want to list all the tables that are enabled for auto capture,
then use the auto_capture
option. This option is only valid for
Oracle Database 21c or higher. You need to establish a database connection (using
DBLOGIN
command) before using this command. If you are unable
to establish a connection or don't have the required privileges, the option will not
work.
Syntax
LIST TABLES table
The following syntax applies when using the auto_capture
option.
LIST TABLES [container.]owner.table [AUTO_CAPTURE]
-
[
container.
]owner.table
-
The command accepts a two-part name in non-CDB mode and a three-part name in CDB mode. Supported wildcards are
?
and*
for single and zero or more character matching respectively. In GGSCI,container
/schema
/table
can have a wildcard specified. Table name can have wildcards. When the command is successful, the output shows the list of tables enabled for auto capture matching the input criteria.
- Example
-
The following example shows a
LIST TABLES
command listing tables enabled for auto capture:LIST TABLES hr.emp* AUTO_CAPTURE
The output is similar to the following:
2022-09-06T06:31:09Z INFO OGG-15189 Default catalog name DBEAST will be used for table specification hr.emp*. "DBEAST"."HR"."EMPLOYEES" "DBEAST"."HR"."EMP_DETAILS_VIEW" Found 2 tables matching list criteria.
Parent topic: GGSCI Command Line Interface Commands
MININGDBLOGIN
Use MININGDBLOGIN
to establish a connection to a downstream
Oracle database logmining server in preparation to issue other Oracle GoldenGate
commands that affect this database, such as REGISTER EXTRACT
.
To log into a source Oracle Database that serves as the database logmining
server, use the DBLOGIN
command. MININGDBLOGIN
is
reserved for login to a downstream mining database.
The user who issues MININGDBLOGIN
must:
-
have privileges granted through the Oracle
dbms_goldengate_auth.grant_admin_privilege
procedure. -
be the user that is specified with the
TRANLOGOPTIONS MININGUSER
parameter for the Extract group that is associated with thisMININGDBLOGIN
.
GGSCI Syntax
MININGDBLOGIN {
USERID {/ | userid}[, PASSWORD password]
[algorithm ENCRYPTKEY {keyname | DEFAULT}] |
USERIDALIAS alias
[DOMAIN domain
] |
[SYSDBA]
}
-
USERID
-
Supplies a database login credential. Can be used if an Oracle GoldenGate credential store is not in use. (See the
USERIDALIAS
option.) Input varies, depending on the database, as follows:-
userid
-
Specifies the name of a database user or a SQL*Net connect string. To log into a pluggable database in an Oracle multitenant container database, specify
userid
as a connect string, such asoggadmin@orcl
. To log into the root container, specifyuserid
as a common user, including the c## prefix, such asc##ggadmin@ggnorth
. -
/
-
The
NOPASSWORD
option is the alternative to thePASSWORD
option when using external authentication because password is not required for external authentication such as using Kerberos authentication. After theNOPASSWORD
option is set, theMININGDBLOGIN
command can be used to access the database without a password.(Oracle) Directs Oracle GoldenGate to use an operating-system login for Oracle, not a database user login. Use this argument only if the database allows authentication at the operating-system level. To use this option, the correct user name must exist in the database, in relation to the value of the Oracle
OS_AUTHENT_PREFIX
initialization parameter, see theUSERID | NOUSERID
parameter. -
NOPASSWORD | PASSWORD
password
-
The
NOPASSWORD
option is the alternative to thePASSWORD
option when using external authentication because password is not required for external authentication such as using Kerberos authentication. After theNOPASSWORD
option is set, theDBLOGIN
command can be used to access the database without a password.Use when authentication is required to specify the password for the database user. If the password was encrypted by means of the
ENCRYPT PASSWORD
command, supply the encrypted password; otherwise, supply the clear-text password. If the password is case-sensitive, type it that way.If the
PASSWORD
clause is omitted, you are prompted for a password, and the password is not echoed. -
algorithm
-
If the password was encrypted with the
ENCRYPT PASSWORD
command, specify the encryption algorithm that was used:AES128
AES192
AES256
-
ENCRYPTKEY {
keyname
| DEFAULT}
-
Specifies the encryption key that was specified with
the ENCRYPT PASSWORD command
. Use one of the following:-
ENCRYPTKEY
keyname
-
Specifies the logical name of a user-created encryption key in the
ENCKEYS
lookup file. Use ifENCRYPT PASSWORD
was used with theKEYNAME
keyname
option. -
ENCRYPTKEY DEFAULT
-
Directs Oracle GoldenGate to generate a Blowfish key. Use if the
ENCRYPT PASSWORD
command was used with theKEYNAME DEFAULT
option.
-
-
-
USERIDALIAS
alias
[DOMAIN
domain
]
-
Supplies the alias of a database login credential. Can be used instead of the
USERID
option if there is a local Oracle GoldenGate credential store that contains a credential with the required privileges for thisMININGDBLOGIN
command.To log into a pluggable database in an Oracle multitenant container database, the user must be stored as a connect string, such as
OGGUSER@FINANCE
. To log into the root container, the user must be stored as a common user, including the c## prefix, such asc##ggadmin@ggnorth
.-
alias
-
Specifies the alias of a database user credential that is stored in the Oracle GoldenGate credential store. The user that is specified with
USERIDALIAS
must be the common database user. -
DOMAIN
domain
-
Specifies the credential store domain for the specified alias. A valid domain entry must exist in the credential store for the specified alias.
-
-
SYSDBA
-
(Oracle) Specifies that the user logs in as
sysdba
. This option can be used forUSERID
andUSERIDALIAS
.
Parent topic: GGSCI Command Line Interface Commands
OBEY
Use OBEY
to process a file that contains a list of
Oracle GoldenGate commands. OBEY
is useful for executing commands
that are frequently used in sequence.
You can call one OBEY
file from another one. This is
called a nested OBEY
file. You can nest up to 16
OBEY
files. To use nested OBEY
files, you must
enable the functionality by first issuing the ALLOWNESTED
command,
see ALLOWNESTED
.
Syntax
OBEY file_name
Examples
-
OBEY ./mycommands.txt
ADD EXTRACT exte, TRANLOG, BEGIN NOW add exttrail east/ea, EXTRACT exte ADD EXTRACT extw, TRANLOG, BEGIN NOW ADD EXTRACT west/ew, EXTRACT extw ADD REPLICAT repe, EXTTRAIL east/ea, BEGIN NOW ADD REPLICAT repw, EXTTRAIL west/ew, BEGIN NOW
The preceding command executes the
mycommands.txt
file and dispalys its content with theADD
commands.The following example displays the content of thestartcmds.txt
file.OBEY ./startcmds.txt
START EXTRACT * INFO EXTRACT *, DETAIL START REPLICAT * INFO REPLICAT *, DETAIL
Parent topic: GGSCI Command Line Interface Commands
PURGE WALLET
Use the PURGE WALLET
command to permanently remove master
key versions from the master-key wallet. Only the versions that are marked for
deletion by the DELETE MASTERKEY
command are removed. The purge is
not reversible.
Note:
For Oracle GoldenGate deployments using a shared wallet, the older versions of the master key should be retained after the master key is renewed until all processes are using the newest version. The time to wait depends on the topology, latency, and data load of the deployment. A minimum wait of 24 hours is a conservative estimate, but you may need to perform testing to determine how long it takes for all processes to start using a new key. To determine whether all of the processes are using the newest version, view the report file of each Extract immediately after renewing the master key to confirm the last SCN that was mined with the old key. Then, monitor the Replicat report files to verify that this SCN was applied by all Replicat groups. At this point, you can delete the older versions of the master key.The OPEN WALLET
command must be used before using this
command or any of the commands that add, renew, or delete the master keys in the
wallet.
After purging a wallet that is not maintained centrally on shared storage, the updated wallet can be copied to all of the other systems in the Oracle GoldenGate configuration that use this wallet, so that no purged keys remain in the configuration. Before doing so, Extract must be stopped and then all of the downstream Oracle GoldenGate processes must be allowed to finish processing their trails and then be stopped. After the wallet is copied into place, the processes can be started again.
Syntax
PURGE WALLET
Parent topic: GGSCI Command Line Interface Commands
RENEW MASTERKEY
Use the RENEW MASTERKEY
command to create a new version of the master encryption key in the master-key wallet. The key name remains the same, but the bit ordering is different. All versions of a master key remain in the wallet until they are marked for deletion with the DELETE MASTERKEY
command and then the wallet is purged with the PURGE WALLET
command.
The OPEN WALLET
command must be used before using this command or any of the commands that add or delete the master keys or purge the wallet.
After renewing a master key in a wallet that is not maintained centrally on shared storage, the updated wallet must be copied to all of the other systems in the Oracle GoldenGate configuration that use this wallet. Before doing so, Extract must be stopped and then all of the downstream Oracle GoldenGate processes must be allowed to finish processing their trails and then be stopped. After the wallet is copied into place, the processes can be started again..
Syntax
RENEW MASTERKEY
Example
RENEW MASTERKEY
Parent topic: GGSCI Command Line Interface Commands
REGISTER EXTRACT
This command applies to Oracle Database and PostgreSQL.
Oracle database
For Oracle database, use REGISTER EXTRACT
to register a
primary Extract group with an Oracle database to:
-
Enable integrated capture mode
-
Specify options for integrated Extract from a multitenant container database
REGISTER EXTRACT
is not valid for a data pump Extract.
To unregister an Extract group from the database, use the UNREGISTER EXTRACT
command.
PostgreSQL
Using this command, a replication slot is created in the connected source
database for PostgreSQL. This command ensures that the PostgreSQL database does not purge
the transaction log until the replication slot is moved or removed. The REGISTER
EXTRACT
command must be run before running the ADD EXTRACT
command. A database connection using DBLOGIN
is required before registering
the Extract.
Syntax
From Oracle GoldenGate 21.3 release onward, it's not
mandatory to enter the database_name
.
Syntax
Oracle:
REGISTER EXTRACT group-name
( | DATABASE
( [ CONTAINER container-list |
ADD CONTAINER container-list |
DROP CONTAINER container-list ]
[ SCN scn ]
[ SHARE ( AUTOMATIC | group-name | NONE ) ]
[ [NO]OPTIMIZED ]
)
Container-list is a comma separated list of PDB names, for example
(pdbeast, pdbwest)
; or wildcarded PDB names, for example (pdb* or
pdb?)
; or both, for example (cdbnorth, pdb*)
. Supported
wildcards are ?
and *
.
The OPTIMIZED
option improves Extract fast startup. The
default value is NOOPTIMIZED
. The OPTIMIZED
option only
impacts an upstream non multitenant configuration.
REGISTER EXTRACT Extract
REGISTER EXTRACT Extract with DATABASE database_name
A replication slot is created in the database for the given Extract group name.
-
DATABASE
[
CONTAINER (
container
[, ...]) |
ADD CONTAINER (
container
[, ...]) |
DROP CONTAINER (
container
[, ...])
]
-
Without options,
DATABASE
enables integrated capture from a non-CDB database for the Extract group. In this mode, Extract integrates with the database logmining server to receive change data in the form of logical change records (LCR). Extract does not read the redo logs. Extract performs capture processing, transformation, and other requirements. The DML filtering is performed by the logmining server.Before using
REGISTER EXTRACT
withDATABASE
, use the DBLOGIN command for all Extracts with the privileges granted using thedbms_goldengate_auth.grant_admin_privilege
procedure. If you have a downstream configuration, then you must also issue the MININGDBLOGIN command. If the source database you are registering is a CDB database and Extract will fetch data, thengrant_admin_privilege
must be called with theCONTAINER=>'ALL
' parameter.After using
REGISTER EXTRACT
, useADD EXTRACT
with theINTEGRATED TRANLOG
option to create an Extract group of the same name.-
CONTAINER (
container
[, ...]
)
-
Applies the registration to a list of one or more pluggable databases (containers) of a multitenant container database (CDB). Specify one or more pluggable databases as a comma-delimited list within parentheses, for example:
CONTAINER (pdbeast, pdbwest)
. If you list the pluggable databases, they must exist in the database. You can also specify the pluggable databases using the wildcards*
and?
. For example,CONTAINER (pdb*)
. -
ADD CONTAINER (
container
[, ...]
)
-
Adds the specified pluggable database to an existing Extract capture configuration. Specify one or more pluggable databases as a comma-delimited list within parentheses or using the wildcards
*
and?
. For example:ADD CONTAINER (pdbeast, pdbwest)
. Before issuingREGISTER EXTRACT
with this option, stop the Extract group.For Oracle, adding containers at particular SCN on an existing Extract is not supported.
-
DROP CONTAINER (
container
[, ...]
)
-
Drops the specified pluggable database from an existing Extract capture configuration. Specify one or more pluggable databases as a comma-delimited list within parentheses or using the wildcards
*
and?
. For example,DROP CONTAINER (pdbeast, pdbwest)
.Registering the Extract after running the
drop container
option, does not fully happen until the Extract has been started and it reads a committed transaction from a dropped pluggable database, which is greater than the Extract checkpoint SCN. Extract then fully drops the containers and shuts down with a message.Before running
REGISTER EXTRACT
with this option, stop the Extract group.
-
-
SCN
system_change_number
-
Registers Extract to begin capture at a specific system change number (SCN) in the past. Without this option, capture begins from the time that
REGISTER EXTRACT
is issued. The specified SCN must correspond to the begin SCN of a dictionary build operation in a log file. You can issue the following query to find all valid SCN values:SELECT first_change# FROM v$archived_log WHERE dictionary_begin = 'YES' AND standby_dest = 'NO' AND name IS NOT NULL AND status = 'A';
When used alone, the SCN value is the beginning SCN of the dictionary build operation in a log file.
When used in conjunction with
SHARE AUTOMATIC
orSHARE
extract_name
, then the specified SCN is thestart_scn
for the capture session and has the following restrictions:-
Should be lesser than or equal to the current SCN.
-
Should be greater than the minimum (first SCN) of the existing captures.
-
-
{SHARE [
AUTOMATIC |
-
extract
| NONE]}
-
Registers the Extract to return to an existing LogMiner data dictionary build with a specified SCN creating a clone. This allows for faster creation of Extracts by leveraging existing dictionary builds.
SHARE
cannot be used on a CDB.The following commands are supported:
REGISTER EXTRACT extract database SCN #### SHARE AUTOMATIC REGISTER EXTRACT extract database SCN #### SHARE extract REGISTER EXTRACT extract database SHARE NONE REGISTER EXTRACT extract database SCN #### SHARE NONE
Or
REGISTER EXTRACT extract DATABASE SHARE NONE REGISTER EXTRACT extract DATABASE SCN #### SHARE NONE
In contrast, the following commands are not supported in a downstream configuration:
REGISTER EXTRACT extract DATABASE SHARE AUTOMATIC REGISTER EXTRACT extract DATABASE SHARE extract
-
AUTOMATIC
-
Clone from the existing closest capture. If no suitable clone candidate is found, then a new build is created.
-
extract
-
Clone from the capture session associated for the specified Extract. If this is not possible, then an error occurs the register does not complete.
-
NONE
-
Does not clone or create a new build; this is the default.
In a downstream configuration, the
SHARE
clause must be used in conjunction with theSCN
clause when registering for Extract. -
Examples
-
REGISTER EXTRACT exte LOGRETENTION
-
REGISTER EXTRACT exte DATABASE
-
REGISTER EXTRACT exte DATABASE CONTAINER (pdbeast, pdbwest, pdbsouth)
-
REGISTER EXTRACT exte DATABASE ADD CONTAINER (pdbnorth)
-
REGISTER EXTRACT exte DATABASE DROP CONTAINER (pdbnorth)
-
REGISTER EXTRACT exte DATABASE SCN 136589
The beginning SCN of the dictionary build is 136589.
-
REGISTER EXTRACT exte DATABASE SCN 67000 SHARE extw
The valid start SCN, 67000 in this case; it is not necessarily the current SCN.
-
REGISTER EXTRACT exte DATABASE CONTAINER (pdbeast, pdbeast, pdbsouth) SCN 136589
Parent topic: GGSCI Command Line Interface Commands
RENEW MASTERKEY
Use the RENEW MASTERKEY
command to create a new version of the master encryption key in the master-key wallet. The key name remains the same, but the bit ordering is different. All versions of a master key remain in the wallet until they are marked for deletion with the DELETE MASTERKEY
command and then the wallet is purged with the PURGE WALLET
command.
The OPEN WALLET
command must be used before using this command or any of the commands that add or delete the master keys or purge the wallet.
After renewing a master key in a wallet that is not maintained centrally on shared storage, the updated wallet must be copied to all of the other systems in the Oracle GoldenGate configuration that use this wallet. Before doing so, Extract must be stopped and then all of the downstream Oracle GoldenGate processes must be allowed to finish processing their trails and then be stopped. After the wallet is copied into place, the processes can be started again..
Syntax
RENEW MASTERKEY
Example
RENEW MASTERKEY
Parent topic: GGSCI Command Line Interface Commands
SEND EXTRACT
Use SEND EXTRACT
to communicate with a running Extract
process. The request is processed as soon as Extract is ready to accept commands
from users.
Syntax
SEND EXTRACT group_name
, {
BR {BRINTERVAL interval |
BRSTART |
BRSTOP |
BRCHECKPOINT {IMMEDIATE | IN n
{M|H} | AT yyyy-mm-dd hh:mm
[:ss
]]}} |
BR BRFSOPTION { MS_SYNC | MS_ASYNC }
BR BRSTATS
BR BRSTATUS
CACHEMGR {CACHESTATS {CACHEALL | CACHEMINIMAL | CACHESUPERPOOL | CACHECURRENTPOOL} | CACHEQUEUES | CACHEPOOL n
} |
CACHEMGR CACHEFSOPTION { MS_SYNC | MS_ASYNC } |
FORCESTOP |
FORCETRANS transaction_ID [FORCE] |
GETLAG |
GETPARAMINFO [parameter_name
] [FILE output_file
] |
GETTCPSTATS |
LOGEND |
LOGSTATS |
REPORT |
RESUME |
ROLLOVER |
SHOWTRANS [transaction_ID] [COUNT n]
[DURATION duration
unit] [TABULAR]
[FILE file_name [DETAIL]] [ALL]|
SKIPTRANS transaction_ID [FORCE] |
STATUS |
STOP |
TRACE[2] file_name |
TRACE[2] OFF |
TRACE OFF file_name |
TRACEINIT |
TRANLOGOPTIONS INTEGRATEDPARAMS(
parameter_specification
)
|
TRANLOGOPTIONS {TRANLOGOPTIONS {PURGEORPHANEDTRANSACTIONS | NOPURGEORPHANEDTRANSACTIONS} |
TRANLOGOPTIONS TRANSCLEANUPFREQUENCY minutes |
VAMMESSAGE 'Teradata_command' |
VAMMESSAGE {'ARSTATS' | 'INCLUDELIST [filter
]' | 'FILELIST [filter
]'| 'EXCLUDELIST [filter
]'} |
VAMMESSAGE 'OPENTRANS'
}
-
group_name
-
The name of the Extract group or a wildcard (*) to specify multiple groups. For example, T* sends the command to all Extract processes whose group names start with T. If an Extract is not running, an error is returned.
-
BR {BRINTERVAL
interval
| BRSTART | BRSTOP |
BRCHECKPOINT {IMMEDIATE | IN
n
{H|M
} |AT
yyyy-mm-dd[ hh:mm[:ss]]
}}-
Sends commands that affect the Bounded Recovery mode of Extract.
-
BRINTERVAL
interval
-
Sets the time between Bounded Recovery checkpoints. Valid values are from 20 minutes to 96 hours specified as
M
for minutes orH
for hours, for example20M
or2H
. The default interval is 4 hours. -
BRSTART
-
Starts Bounded Recovery. This command should only be used under direction of Oracle Support.
-
BRSTOP
-
Stops Bounded Recovery for the run and for recovery. Consult Oracle Support before using this option. In most circumstances, when there is a problem with Bounded Recovery, it turns itself off.
-
BRCHECKPOINT {IMMEDIATE | IN
n
{H
|M
} |AT
yyyy-mm-dd[ hh:mm
[:ss
]]}} -
Sets the point at which a bounded recovery checkpoint is made.
IMMEDIATE
issues the checkpoint immediately whenSEND EXTRACT
is issued.IN
issues the checkpoint in the specified number of hours or minutes from whenSEND EXTRACT
is issued.AT
issues the checkpoint at exactly the specified time.
-
-
BR BRFSOPTION {MS_SYNC | MS_ASYNC}
-
Performs synchronous/asynchronous writes of the mapped data in Bounded Recovery.
-
BR BRSTATS
-
Provides details on each of the object pools, which were persisted by BR. This includes the following details:
-
Object sizes through life of the Extract group
-
Object ages through the life of Extract group
-
Extant object sizes as of the most recent BCP
-
Extant object ages as of the most recent BCP
-
-
BR BRSTATUS
-
Returns status for the Bounded Recovery mode of Extract.
It shows the following:
-
Current settings of the BR parameter.
-
Current status of the Bounded Recovery, if one was performed, with current checkpoint interval, timestamps for the next and last checkpoints, and the total and outstanding number of objects and sizes when BR is in progress.
-
Start and end sequence, RBA, SCN, and timestamp for the Bounded Recovery checkpoint positions (per redo thread).
-
-
CACHEMGR {CACHESTATS | CACHEQUEUES | CACHEPOOL
n
}
-
Returns statistics about the Oracle GoldenGate memory cache manager.
CACHESTATS
should only be used as explicitly directed by Oracle Support.-
CACHESTATS {CACHEALL | CACHEMINIMAL | CACHESUPERPOOL | CACHECURRENTONLY}
-
CACHESTATS
returns statistics for virtual memory usage and file caching.-
CACHEALL
-
Returns all COM statistics and is the default option.
-
CACHESUPERPOOL
-
Super pool statistics. Returns statistics about all object pools.
-
CACHECURRENTONLY
-
Returns statistics only for the run time.
-
CACHEMINIMAL
-
Returns condensed (minimal) version of COM stats whereas CACHEALL returns all statistics.
-
-
CACHEQUEUES
-
Returns statistics for the free queues only.
-
CACHEPOOL
n
-
Returns statistics for the specified object pool only.
-
-
CACHEMGR CACHEFSOPTION {MS_SYNC | MS_ASYNC}
-
Performs synchronous or asynchronous writes of the mapped data in the Oracle GoldenGate memory cache manager.
-
FORCESTOP
-
Forces Extract to stop, bypassing any notifications. This command will stop the process immediately.
-
FORCETRANS
transaction_ID
[THREAD
n
] [FORCE]
-
Valid for DbLUW, Db2 IBM, MySQL, Oracle, and SQL Server.
Forces Extract to write a transaction specified by its transaction ID number to the trail as a committed transaction.
FORCETRANS
does not commit the transaction to the source database. It only forces the existing data to the trail so that it is processed (with an implicit commit) by Replicat. You can repeatFORCETRANS
for other transactions in order of their age. Note that forcing a transaction to commit to the trail (and therefore the target database) may cause data discrepancies if the transaction is rolled back by the source user applications.After using
FORCETRANS
, wait at least five minutes if you intend to issueSEND EXTRACT
withFORCESTOP
. Otherwise, the transaction will still be present.If
FORCETRANS
is used immediately after Extract starts, you might receive an error message that asks you to wait and then try the command again. This means that no other transactions have been processed yet by Extract. Once another transaction is processed, you will be able to force the transaction to trail.-
transaction_ID
-
The ID of the transaction. Get the transaction ID number with
SHOWTRANS
or from an Extract runtime message. Extract ignores any data added to the transaction after this command is issued. A confirmation prompt must be answered unlessFORCE
is used. To useFORCETRANS
, the specified transaction must be the oldest one in the list of transactions shown withSHOWTRANS
. -
FORCE
-
Valid for Oracle and SQL Server. Not valid for MySQL.
Use
FORCE
to bypass the confirmation prompt.
-
-
GETLAG
-
Determines a true lag time between Extract and the data source. Returns the same results as LAG EXTRACT.
-
GETPARAMINFO
[parameter_name
] [FILE
output_file
] -
Use
GETPARAMINFO
to query runtime parameter values of a running instance, including Extract, Replicat, and Manager. You can query for a single parameter or all parameters and send the output to the console or a text file-
parameter_name
-
The default behavior is to display all parameters in use, meaning those parameters that have ever been queried by the application, parameters, and their current values. If you specify a particular parameter, then the output is filtered by that name.
-
FILE
output_file
-
The name of the text file that your output is redirected to.
-
-
GETTCPSTATS
-
Displays statistics about network activity between Extract and the target system. The statistics include:
-
Local and remote IP addresses.
-
Inbound and outbound messages, in bytes and bytes per second.
-
Number of receives (inbound) and sends (outbound). There will be at least two receives per inbound message: one for the length and one or more for the data.
-
Average bytes per send and receive.
-
Send and receive wait time: Send wait time is how long it takes for the write to TCP to complete. The lower the send wait time, the better the performance over the network. Receive wait time is how long it takes for a read to complete. Together, the send and receive wait times provide a rough estimate of network round trip time. These are expressed in microseconds.
-
Status of data compression (enabled or not).
-
Uncompressed bytes and compressed bytes: When compared (uncompressed to compressed), these comprise the compression ratio, meaning how many bytes there were before and after compression. You can compare the compression ratio with the bytes that are being compressed per second to determine if the compression rate is worth the cost in terms of resource and network consumption.
The
TCPBUFSIZE
option ofRMTHOST
andRMTHOSTOPTIONS
controls the size of the TCP buffer for uncompressed data. What actually enters the network will be less than this size if compression is enabled.GETTCPSTATS
shows post-compression throughput. -
-
LOGEND
-
Confirms whether or not Extract has processed all of the records in the data source.
-
LOGSTATS
-
Valid only for Oracle.
Instructs Extract to issue a report about the statistics that are related to the processing of data from the Oracle redo log files. Extract uses an asynchronous log reader that reads ahead of the current record that Extract is processing, so that the data is available without additional I/O on the log files. The processing is done through a series of read/write queues. Data is parsed by a producer thread at the same time that additional data is being read from the log file by a reader thread. Thus, the reason for the term "read-ahead" in the statistics.
The statistics are:
-
AsyncReader.Buffersn: There is a field like this for each buffer queue that contains captured redo data. It shows the size, the number of records in it, and how long the wait time is before the data is processed. These statistics are given for write operations and read operations on the queue.
-
REDO read ahead buffers: The number of buffers that are being used to read ahead asynchronously.
-
REDO read ahead buffer size: The size of each buffer.
-
REDO bytes read ahead for current redo: Whether read-ahead mode is on or off for the current redo log file (value of
ON
orOFF
). -
REDO bytes read: The number of bytes read from all redo log files that are associated with this instance of Extract.
-
REDO bytes read ahead: The number of bytes that were processed by the read-ahead mechanism.
-
REDO bytes unused: The number of read-ahead bytes that were subsequently dropped as the result of Extract position changes or stale reads.
-
REDO bytes parsed: The number of bytes that were processed as valid log data.
-
REDO bytes output: The number of bytes that were written to the trail file (not including internal Oracle GoldenGate overhead).
-
-
REPORT
-
Generates an interim statistical report to the Extract report file. The statistics that are displayed depend upon the configuration of the
STATOPTIONS
parameter when used with theRESETREPORTSTATS
|NORESETREPORTSTATS
option. -
RESUME
-
Resumes (makes active) a process that was suspended by an
EVENTACTIONS SUSPEND
event. The process resumes normal processing from the point at which it was suspended. -
ROLLOVER
-
Causes Extract to increment to the next file in the trail when restarting. For example, if the current file is
ET000002
, the current file will beET000003
after the command executes. A trail can be incremented from000001
through999999
, and then the sequence numbering starts over at000000
. -
SHOWTRANS [
transaction_ID
] [THREAD
n
] [COUNT
n
]
[DURATION
duration unit
] [TABULAR] | [FILE
file_name
[DETAIL]] [ALL]
-
Valid for MySQL, Oracle, and SQL Server.
Displays information about open transactions.
SHOWTRANS
shows any of the following, depending on the database type:-
Process checkpoint (indicating the oldest log needed to continue processing the transaction in case of an Extract restart).
-
Transaction ID
-
Extract group name
-
Redo thread number
-
Timestamp of the first operation that Oracle GoldenGate extracts from a transaction (not the actual start time of the transaction)
-
System change number (SCN)
-
Redo log number and RBA
-
Status (
Pending COMMIT
orRunning
).Pending COMMIT
is displayed while a transaction is being written after aFORCETRANS
was issued.
Without options,
SHOWTRANS
displays all open transactions that will fit into the available buffer. However, it doesn't display the output user name sometimes for an open active transaction because the user name is not provided in the begin record from transaction log.See the examples for sample output of
SHOWTRANS
. To further control output, see the following options.-
transaction_ID
-
Limits the command output to a specific transaction.
-
THREAD
n
-
Valid only for Oracle.
Constrains the output to open transactions against a specific Oracle RAC thread. For
n
, use a RAC thread number that is recognized by Extract. -
COUNT
n
-
Constrains the output to the specified number of open transactions, starting with the oldest one. Valid values are
1
to1000
. -
DURATION
duration unit
-
Restricts the output to transactions that have been open longer than the specified time, where:
duration
is the length of time expressed as a whole number.unit
is one of the following to expressseconds
,minutes
,hours
, ordays
:S|SEC|SECS|SECOND|SECONDS M|MIN|MINS|MINUTE|MINUTES H|HOUR|HOURS D|DAY|DAYS
-
TABULAR
-
Valid only for Oracle.
Generates output in tabular format similar to the default table printout from SQL*Plus. The default is field-per-row.
-
FILE
file_name
[DETAIL]
-
Valid only for Oracle and SQL Server. Not valid for MySQL.
Forces Extract to write the transaction information to the specified file. There is no output to the console.
For Oracle, you can write a hex and plain-character dump of the data by using
FILE
withDETAIL
. This dumps the entire transaction from memory to the file. Viewing the data may help you decide whether to skip the transaction or force it to the trail.Note:
Basic detail information is automatically written to the report file at intervals specified by theWARNLONGTRANS CHECKINTERVAL
parameter. -
[ALL]
-
This option allows showing all the transaction in COM. It is useful when detecting potential issues with committed transactions that are remaining in COM.
-
-
SKIPTRANS
transaction_ID
[THREAD
n
] [FORCE]
-
Valid for Db2 LUW, Db2 IBM, MySQL, Oracle, and SQL Server.
Forces Extract to skip the specified transaction, thereby removing any current data from memory and ignoring any subsequent data. A confirmation prompt must be answered unless
FORCE
is used. After usingSKIPTRANS
, wait at least five minutes if you intend to issueSEND EXTRACT
withFORCESTOP
. Otherwise, the transaction is still present. Note that skipping a transaction may cause data loss in the target database.Note:
To useSKIPTRANS
, the specified transaction must be the oldest one in the list of transactions shown withSHOWTRANS
. You can repeat the command for other transactions in order of their age.-
transaction_ID
-
The transaction ID number. Get the ID number with
SHOWTRANS
or from an Extract runtime message. -
THREAD
n
-
Valid only for Oracle.
Use
THREAD
n
to specify which thread generated the transaction in an Oracle RAC environment if there are duplicate transaction IDs.SKIPTRANS
specifies the checkpoint index number, not the actual thread number. To specify the correct thread, issue theINFO EXTRACT
group_name
SHOWCH
command, and then specify theREAD
checkpoint index number that corresponds to the thread number that you want to skip. -
FORCE
-
Valid for Oracle and SQL Server. Not valid for MySQL.
Use
FORCE
to bypass the prompt that confirms your intent to skip the transaction.
-
-
STATUS
-
Returns a detailed status of the processing state, including current position and activity. Possible processing status messages on the
Current status
line are:-
Delaying
– waiting for more data -
Suspended
– waiting to be resumed -
Processing data
– processing data -
Starting initial load
– starting an initial load task -
Processing source tables
– processing data for initial load task -
Reading from data source
– reading from the data source, such as a source table or transaction log -
Adding record to transaction list
– adding a record to the file memory transaction list -
At EOF (end of file)
– no more records to process
In addition to the preceding statuses, the following status notations appear during an Extract recovery after an abend event. You can follow the progress as Extract continually changes its log read position over the course of the recovery.
-
In recovery[1]
– Extract is recovering to its checkpoint in the transaction log. -
In recovery[2]
– Extract is recovering from its checkpoint to the end of the trail. -
Recovery complete
– The recovery is finished, and normal processing will resume.
-
-
STOP
-
Stops Extract. If there are any long-running transactions (based on the
WARNLONGTRANS
parameter), the following message will be displayed:Sending STOP request to EXTRACT EXTE... There are open, long-running transactions. Before you stop Extract, make the archives containing data for those transactions available for when Extract restarts. To force Extract to stop, use the SEND EXTRACT group, FORCESTOP command. Oldest redo log file necessary to restart Extract is: Redo Thread 1, Redo Log Sequence Number 150, SCN 31248005, RBA 2912272.
-
TRACE[2] {
file_name
| OFF}
-
Turns tracing on and off. Tracing captures information to the specified file to reveal processing bottlenecks. Contact Oracle Support for assistance if the trace reveals significant processing bottlenecks.
-
TRACE
-
Captures step-by-step processing information.
-
TRACE2
-
Identifies code segments rather than specific steps.
-
file_name
-
Specifies the name of the file to which the trace information is written. If a trace is already running when
SEND EXTRACT
is issued withTRACE
, the existing trace file is closed and the trace is resumed to the new file specified withfile_name
. -
OFF
-
Turns off tracing.
-
-
TRACE OFF
file_name
-
Turns tracing off only for the specified trace file.
-
TRACEINIT
-
Resets tracing statistics back to 0 and then starts accumulating statistics again. Use this option to track the current behavior of processing, as opposed to historical.
-
TRANLOGOPTIONS INTEGRATEDPARAMS(
parameter_specification
)
-
(Oracle) Supports an integrated Extract. Sends a parameter specification to the database inbound server while Extract is running in integrated mode. Only one parameter specification can be sent at a time with this command. You can send multiple parameter changes, issue multiple
SEND EXTRACT
commands.To preserve the continuity of processing, the parameter change is made at a transaction boundary.
-
TRANLOGOPTIONS {PURGEORPHANEDTRANSACTIONS | NOPURGEORPHANEDTRANSACTIONS}
-
Valid for Oracle RAC. Enables or disables purging of orphaned transactions that occur when a node fails and Extract cannot capture the rollback.
-
TRANLOGOPTIONS TRANSCLEANUPFREQUENCY
minutes
-
Valid for Oracle RAC. Specifies the interval, in minutes, after which Oracle GoldenGate scans for orphaned transactions and then re-scans to confirm and delete them. Valid values are from 1 to 43200 minutes. Default is 10 minutes.
-
VAMMESSAGE '
Teradata_command
'
VAMMESSAGE { 'ARSTATS' | 'INCLUDELIST [
filter
]' | 'EXCLUDELIST [
filter
]' }
VAMMESSAGE 'OPENTRANS'
-
Sends a command to the capture API that is used by Extract.
A Teradata command can be any of the following:
-
'control:terminate'
-
Stops a replication group. Required before dropping or altering a replication group in Teradata.
-
'control:suspend'
-
Suspends a replication group. Can be used when upgrading Oracle GoldenGate.
-
'control:resume'
-
Resumes a replication group after it has been suspended.
-
'control:copy
database.table'
-
Copies a table from the source database to the target database.
-
'ARSTATS'
-
Displays TMF audit reading statistics.
-
'FILELIST [
filter
]'
-
Displays the list of tables for which Extract has encountered data records in the audit trail that match the selection criteria in the
TABLE
parameters. Thefilter
option allows use of a wildcard pattern to filter the list of tables returned.GETFILELIST
can also be used in the same manner. -
'EXCLUDELIST [
filter
]'
-
Displays the list of tables for which Extract has encountered data records in the audit trail that do not match the selection criteria in the
TABLE
parameters. Thefilter
option allows use of a wildcard pattern to filter the list of tables returned. Certain system tables that are implicitly excluded will always be present in the list of excluded tables.
A SQL Server command can be the following:
-
Examples
-
SEND EXTRACT exte, ROLLOVER
-
SEND EXTRACT exte, STOP
-
SEND EXTRACT exte, VAMMESSAGE 'control:suspend'
-
SEND EXTRACT exte, TRANLOGOPTIONS TRANSCLEANUPFREQUENCY 20
-
This example explains
SKIPTRANS
. Start with the followingSHOWCH
output, which shows that thread 2 is atRead Checkpoint #3
.INFO
exte
SHOWCHRead Checkpoint #3 Oracle RAC Redo Log Startup Checkpoint (starting position in the data source): Thread #: 2 Sequence #: 17560 RBA: 65070096 Timestamp: 2011-07-30 20:04:47.000000 SCN: 1461.3499051750 (6278446271206) Redo File: RAC4REDO/sss11g/onlinelog/group_4.292.716481937
Therefore,
SKIPTRANS
should be:SKIPTRANS
xid
THREAD 3
. -
SEND EXTRACT exte, SHOWTRANS COUNT 2
-
The following shows the default output of
SHOWTRANS.
Oldest redo log file necessary to restart Extract is: Redo Thread 1, Redo Log Sequence Number 148, SCN 30816254, RBA 17319664 ------------------------------------------------------------ XID : 5.15.52582 Items : 30000 Extract : JC108XT Redo Thread : 1 Start Time : 2011-01-18:12:51:27 SCN : 20634955 Redo Seq : 103 Redo RBA : 18616848 Status : Running ------------------------------------------------------------ XID : 7.14.48657 Items : 30000 Extract : JC108XT Redo Thread : 1 Start Time : 2011-01-18:12:52:14 SCN : 20635145 Redo Seq : 103 Redo RBA : 26499088 Status : Running
-
The following example shows
SHOWTRANS
output withTABULAR
in effect (view is truncated on right).
XID Items Extract Redo Thread Start Time
5.15.52582 30000 JC108XT 1 2011-01-18:12:52:14
Dumping transaction memory at 2011-01-21 13:36:54.
Record #1:
Header (140 bytes):
0: 0000 0A4A 0000 FFFF 0000 0000 0057 6C10 ...J.........Wl.
16: 02FF 3F50 FF38 7C40 0303 4141 414E 5A77 ..?P.8|@..AAANZw
32: 4141 4641 4141 4B6F 4941 4144 0041 4141 AAFAAAKoIAAD.AAA
48: 4E5A 7741 4146 4141 414B 6F49 4141 4400 NZwAAFAAAKoIAAD.
64: 4141 414E 5A77 414A 2F41 4142 7A31 7741 AAANZwAJ/AABz1wA
80: 4141 0041 4141 4141 4141 4141 4141 4141 AA.AAAAAAAAAAAAA
96: 4141 4141 4100 0000 0140 FF08 0003 0000 AAAAA....@......
112: 0000 0000 0000 70FF 0108 FFFF 0001 4A53 ......p.......JS
128: 554E 2E54 4355 5354 4D45 5200 UN.TCUSTMER.
Data (93 bytes):
0: 2C00 0400 0400 0000 0100 0200 0300 0000 ,...............
16: 0000 0000 0800 0000 1800 0000 2000 0400 ............ ...
32: 1000 0600 0200 0000 284A 414E 456C 6C6F ........(JANEllo
48: 6352 4F43 4B59 2046 4C59 4552 2049 4E43 cROCKY FLYER INC
64: 2E44 454E 5645 5220 6E43 4F20 7365 7400 .DENVER nCO set.
80: 0000 0000 0000 0C00 0000 0000 00 ..............
When analyzing the summary output of SHOWTRANS
,
understand that it shows all currently running transactions on the database (as many
as will fit into a predefined buffer). Extract must track every open transaction,
not just those that contain operations on tables configured for Oracle
GoldenGate.
The Items
field of the SHOWTRANS
output shows the number of operations in the transaction that have been captured by
Oracle GoldenGate so far, not the total number of operations in the transaction. If
none of the operations are for configured tables, or if only some of them are, then
Items
could be 0 or any value less than the total number of
operations.
The Start Time
field shows the timestamp of the first
operation that Oracle GoldenGate extracts from a transaction, not the actual start
time of the transaction itself.
Note:
Command output may vary somewhat from the examples shown due ongoing enhancements of Oracle GoldenGate.- The following example shows sending BR
request to Extract
exte
SEND exte BR BRSTATUS
Output:
Bounded Recovery Parameter: Options = _BRDEBUG _NOBRCLEANUP _BRFORCE_ASSERT BRINTERVAL = 40SECONDS BRDIR = /home/mpopeang/ogg_test/ Bounded Recovery Status: IN PROGRESS Checkpoint interval = 40SECONDS Next checkpoint = 2020-01-15 21:10:47 Last checkpoint# = 49 Last checkpoint = 2020-01-15 21:10:07 Total objects = 65 Total size = 426 MB Outstanding objects = 58 Outstanding size = 384 MB Object pool 1: p12733_extr:RECOVERY: COMPLETE: start:SeqNo: 1580, RBA: 793460, SCN: 0.664178312(664178312), Timestamp: 2020-01-15 20:35:45.000000, Thread: 1, end=SeqNo:1580, RBA: 793460, SCN: 0.664178312 (664178312), Timestamp: 2020-01-15 20:35:45.000000, Thread: 1, complete=SeqNo: 1580, RBA: 793460, SCN:0.664178312 (664178312), Timestamp: 2020-01-15 20:35:45.000000, Thread: 1 at 2020-01-15 20:38:52.435830 CHECKPOINT: start=SeqNo: 1637, RBA: 10182312, SCN: 0.669567539 (669567539), Timestamp: 2020-01-15 21:09:59.000000, Thread: 1, end=SeqNo: 1637, RBA:10182312, SCN: 0.669567539 (669567539), Timestamp: 2020-01-15 21:09:59.000000, Thread: 1
-
The following example provides details on each of the object pools persisted by BR:
SEND eoradb2 BR BRSTATS
Output:Object pool #0, instance: 1, id: p12733_extr
Object sizes through life of Extract group:Sizes in bytes : POs 512K to 1M-1 : 13 1M to 2M-1 : 37 4M to 8M-1 : 398 8M to 16M-1 : 2 16M to 32M-1 : 10
Object ages through life of Extract group:duration: BCP intervals in the life of the PO duration 0: 230 duration 30 to 39: 230
Extant object sizes as of most recent BCP:Sizes in bytes : POs 512K to 1M-1 : 8 1M to 2M-1 : 17 4M to 8M-1 : 200 16M to 32M-1 : 5
Extant object ages as of most recent BCP:duration: BCP intervals in the life of the PO duration 30 to 39: 230
Parent topic: GGSCI Command Line Interface Commands
SET EDITOR
Use SET EDITOR
to change the default text editor for the current
session of GGSCI. The default editors are Notepad for Windows and vi for UNIX. CLI
input, including to create parameter files, takes the character set of the local
operating system.
Example
The following example changes the default editor to Notepad++.
SET EDITOR notepad++
Parent topic: GGSCI Command Line Interface Commands
SEND REPLICAT
Use SEND REPLICAT
to communicate with a starting or running Replicat process. The request is processed as soon as Replicat is ready to accept commands from users.
Syntax
SEND REPLICAT group_name
[threadID
],
{
CACHEMGR {CACHESTATS | CACHEQUEUES | CACHEVMUSAGE} |
FORCESTOP |
GETLAG |
GETPARAMINFO [parameter_name
] [FILE output_file
] |
HANDLECOLLISIONS | NOHANDLECOLLISIONS [table_spec] |
INTEGRATEDPARAMS(parameter_specification
) |
LOGEND |
REPORT [HANDLECOLLISIONS [table_spec]] |
RESUME |
STATUS |
STOP |
TRACE[2] [DDLINCLUDE | DDL[ONLY]] file_name |
TRACE[2] OFF |
TRACE OFF file_name |
TRACEINIT |
THREADS (threadID
[, threadID
][, ...][, thread_range
[, thread_range
][, ...])
}
-
group_name
[
threadID
]
-
The name of the Replicat group or the name of a specific thread of a coordinated Replicat, for example
fin003
. If the command is issued for a specific thread, then an option that is used applies only to that thread. As an alternative, you can issueSEND REPLICAT
with theTHREADS
option instead of includingthreadID
with the group name. If Replicat is not running, an error is returned. -
CACHEMGR {CACHESTATS | CACHEQUEUES | CACHEVMUSAGE}
-
Returns statistics about the Oracle GoldenGate memory cache manager.
CACHEMGR
should only be used as explicitly directed by Oracle Support. -
FORCESTOP
-
Forces Replicat to stop, bypassing any notifications. This command will roll back any active transaction and stop the process immediately. This command applies to Replicat as a whole and cannot be used for a specific Replicat thread.
-
GETLAG
-
Shows a true lag time between Replicat and the trail. Lag time is the difference, in seconds, between the time that the last record was processed by Replicat and the timestamp of the record in the trail. The results are the same as
LAG REPLICAT
. -
GETPARAMINFO [parameter_name] [FILE output_file]
-
Use
GETPARAMINFO
to query runtime parameter values of a running instance, including Extract, Replicat, and Manager. You can query for a single parameter or all parameters and send the output to the console or a text file-
parameter_name
-
The default behavior is to display all parameters in use, meaning those parameters that have ever been queried by the application, parameters, and their current values. If you specify a particular parameter, then the output is filtered by that name.
-
FILE output_file
-
The name of the text file that your output is redirected to.
-
-
HANDLECOLLISIONS | NOHANDLECOLLISIONS [table_spec]
-
Control
HANDLECOLLISIONS
behavior. Instead of using this option, you can specify theHANDLECOLLISIONS
orNOHANDLECOLLISIONS
parameter in the Replicat parameter file. SeeHANDLECOLLISIONS | NOHANDLECOLLISIONS
in Reference for Oracle GoldenGate. This command can be sent directly to an individual thread by means ofSEND REPLICAT
group_name[threadID]
or you can use theTHREADS
option to send the command through the coordinator thread to affect multiple threads.-
HANDLECOLLISIONS
-
Use
HANDLECOLLISIONS
to enable automatic error handling when performing initial data loads while the source database is active. Make certain to disableHANDLECOLLISIONS
, either by issuingSEND REPLICAT
with theNOHANDLECOLLISIONS
option or by removing the parameter from the parameter file, after the initial load is complete and online data changes have been applied to the target tables.Note:
The message returned by
SEND REPLICAT
withHANDLECOLLISIONS
, when issued for a specific Replicat thread, shows that the command setHANDLECOLLISIONS
for allMAP
statements, not only the one handled by the specified thread. This is a known issue. The command actually affects only theMAP
statement that includes the specified thread. -
NOHANDLECOLLISIONS
-
Turns off the
HANDLECOLLISIONS
parameter but does not remove it from the parameter file. To avoid enablingHANDLECOLLISIONS
the next time Replicat starts, remove it from the parameter file. -
table_spec
-
table_spec
restrictsHANDLECOLLISIONS
orNOHANDLECOLLISIONS
to a specific target table or a group of target tables specified with a standard wildcard (*).
-
-
INTEGRATEDPARAMS(parameter_specification)
-
(Oracle) Supports an integrated Replicat. Sends a parameter specification to the database inbound server while Replicat is running in integrated mode. Only one parameter specification can be sent at a time with this command. To send multiple parameter changes, issue multiple
SEND REPLICAT
commands as in the following example.SEND REPLICAT repe INTEGRATEDPARAMS (parallelism 4) SEND REPLICAT repe INTEGRATEDPARAMS (max_sga_size 250)
To preserve the continuity of processing, the parameter change is made at a transaction boundary.
-
REPORT [HANDLECOLLISIONS [table_spec]]
-
Generates an interim statistical report to the Extract report file. The statistics that are displayed depend upon the configuration of the
STATOPTIONS
parameter when used with theRESETREPORTSTATS
|NORESETREPORTSTATS
option. SeeSTATOPTIONS
. -
RESUME
-
Resumes (makes active) a process that was suspended by an
EVENTACTIONS SUSPEND
event. The process resumes normal processing from the point at which it was suspended. -
STATUS
-
Returns the current location within the trail and information regarding the current transaction. Fields output are:
-
Processing status (per thread, if Replicat is coordinated)
-
Position in the trail file (per thread, if Replicat is coordinated)
-
Trail sequence number (per thread, if Replicat is coordinated)
-
RBA in trail
-
Trail name
Possible processing status messages are:
-
Delaying
– waiting for more data -
Suspended
– waiting to be resumed -
Waiting on deferred apply
– delaying processing based on theDEFERAPPLYINTERVAL
parameter. -
Processing data
– processing data -
Skipping current transaction
–START REPLICAT
withSKIPTRANSACTION
was used. -
Searching for START ATCSN
csn
–START REPLICAT
withATCSN
was used. -
Searching for START AFTERCSN
csn
–START REPLICAT
withAFTERCSN
was used. -
Performing transaction timeout recovery
– Canceling current incomplete transaction and repositioning to start new one (see theTRANSACTIONTIMEOUT
parameter). -
Waiting for data at logical EOF after transaction timeout recovery
– Waiting to receive remainder of incomplete source transaction after aTRANSACTIONTIMEOUT
termination. -
At EOF (end of file)
– no more records to process
Possible thread status messages when
THREADS
is used or the command is issued for a specific thread are:-
Waiting for consensus stop point
: This indicates that the threads are attempting to synchronize for a barrier transaction. -
Waiting for Watermark
: Indicates that all threads are attempting to stop at the same transaction boundary in the trail, known as the global watermark. -
Waiting on all threads to start up
: Indicates that the thread is waiting for all of the threads to start after a successful barrier transaction or a Replicat startup.
Possible coordinator thread status messages are:
-
Waiting for all threads to register
: Indicates that theMAP
statements are all being parsed to determine the thread IDs that are specified in them. -
Processing data
: Indicates that data is being processed normally. -
Suspended, waiting to be resumed
: Indicates that aSEND REPLICAT
command with aSUSPEND
request was sent to Replicat. -
At EOF
: Indicates that there is no more data in the trail to process. -
Waiting to register MAP statistics
: Indicates that Replicat is collecting processing statistics to send to the report file.
-
-
STOP
-
Stops Replicat gracefully. This command applies to Replicat as a whole and cannot be used for a specific Replicat thread.
-
THREADS (threadID[, threadID][, ...][, thread_range[, thread_range][, ...])
-
Issues the command only for the specified thread or threads of a coordinated Replicat. You can use this option or you can use
groupname
withthreadID
. Without either of those options, the command applies to all active threads.-
threadID[, threadID][, ...]
-
Specifies a thread ID or a comma-delimited list of threads in the format of
threadID, threadID, threadID
. -
thread_range[, thread_range][, ...]
-
Specifies a range of threads in the form of
threadIDlow-threadIDhigh
or a comma-delimted list of ranges in the format ofthreadIDlow-threadIDhigh
,threadIDlow-threadIDhigh
.
A combination of these formats is permitted, such as threadID, threadID,
threadIDlow-threadIDhigh
. -
-
TRACE[2] [DDLINCLUDE | DDL[ONLY]] file_name
-
Turns tracing on and off. Tracing captures information to the specified file to reveal processing bottlenecks. Tracing also can be enabled by means of the Replicat parameters
TRACE
andTRACE2
.If the Replicat is in coordinated mode and
TRACE
is used with aTHREADS
list or range, a trace file is created for each currently active thread. Each file name is appended with its associated thread ID. This method of identifying trace files by thread ID does not apply whenSEND REPLICAT
is issued bygroupname
withthreadID
(as inSEND REPLICAT fin003
TRACE
...) or when only one thread is specified withTHREADS
.Contact Oracle Support for assistance if the trace reveals significant processing bottlenecks.
-
TRACE
-
Captures step-by-step processing information.
-
TRACE2
-
Identifies code segments rather than specific steps.
-
DDLINCLUDE | DDLONLY
-
(Replicat only) Enables DDL tracing and specifies how DDL tracing is included in the trace report.
-
DDLINCLUDE
includes DDL tracing in addition to regular tracing of transactional data processing. -
DDL[ONLY]
excludes the tracing of transactional data processing and only traces DDL. This option can be abbreviated toDDL
.
-
-
file_name
-
file_name
specifies the relative or fully qualified name of a file to which Oracle GoldenGate logs the trace information. If a trace is already in progress, the existing trace file is closed and the trace resumes to the file specified withfile_name
. For example:SEND REPLICAT
group_name
TRACE file_name DDLINCLUDEIf no other options will follow the file name, the
FILE
keyword can be omitted, for example:SEND REPLICAT group_name TRACE DDLINCLUDE file_name
-
-
TRACE[2] OFF
-
Turns off tracing.
-
TRACE OFF file_name
-
Turns tracing off only for the specified trace file. This option supports the
EVENTACTIONS
feature, where there can be multiple trace files due to multipleEVENTACTIONS
statements. -
TRACEINIT
-
Resets tracing statistics back to 0 and then starts accumulating statistics again. Use this option to track the current behavior of processing, as opposed to historical.
Examples
-
SEND REPLICAT repe, HANDLECOLLISIONS
-
SEND REPLICAT repe, REPORT HANDLECOLLISIONS rep_*
-
SEND REPLICAT repe, GETLAG
-
SEND REPLICAT repe, INTEGRATEDPARAMS(parallelism 10)
-
The following gets lag for thread 3 of a coordinated Replicat.
SEND REPLICAT repe, GETLAG
-
The following enables tracing for only thread 1 of a coordinated Replicat. In this case, because only one thread is being traced, the trace file will not have a
threadID
extension. The file name istrace.trc
.SEND REPLICAT repe, TRACE THREADS(1) FILE ./dirrpt/trace.trc
-
The following enables tracing for threads 1,2, and 3 of a coordinated Replicat. Assuming all threads are active, the tracing produces files
trace001
,trace002
, andtrace003
.SEND REPLICAT repe TRACE THREADS(1-3) FILE ./dirrpt/trace.trc
-
The following enables tracing only for thread 1 of a coordinated Replicat. Because the command was issued directly for thread 1 without the use of a
THREAD
clause, the trace file is namedtrace
(without a thread ID suffix).SEND REPLICAT repe TRACE FILE ./dirrpt/trace.trc
Parent topic: GGSCI Command Line Interface Commands
SHELL
Use SHELL
to execute shell commands from within the CLI.
Parent topic: GGSCI Command Line Interface Commands
SHOW
Use SHOW
to display the Oracle GoldenGate environment.
Syntax
SHOW
Example
The following are samples of SHOW
output. Additional entries may be
displayed, depending on the database type.
Current directory: /scratch/ogg/sa/bin
DEBUG : OFF
EDITOR : vi
PAGER : more
or
Parameter settings:
SET DEBUG OFF
Current directory: C:\GG_81
Using subdirectories for all process files
Editor: notepad
Reports (.rpt) C:\GG_81\dirrpt
Parameters (.prm) C:\GG_81\dirprm
Replicat Checkpoints (.cpr) C:\GG_81\dirchk
Extract Checkpoints (.cpe) C:\GG_81\dirchk
Process Status (.pcs) C:\GG_81\dirpcs
SQL Scripts (.sql) C:\GG_81\dirsql
Database Definitions (.def) C:\GG_81\dirdef
Parent topic: GGSCI Command Line Interface Commands
START ER
Use the START ER
to start multiple Extract and Replicat
groups as a unit. Use it with wildcards to affect every Extract and Replicat group
that satisfies the wildcard. For descriptions and optional parameters for this
command, see INFO EXTRACT
.
Syntax
START ER group_name
Example
START ER *
Parent topic: GGSCI Command Line Interface Commands
STATS REPLICAT
Use STATS REPLICAT
to display statistics for one or
more Replicat groups. Thread statistics for a coordinated Replicat group are
provided as follows.
- Thread Lag Gap
-
The difference between the maximum lag and the minimum lag among all threads.
- Coordinated Total DDLs
-
The total number of coordinated DDL transactions.
- Coordinated Total PK-Update Transactions
-
The total number of coordinated transactions that involved an update to a primary key.
- Coordinated Total EMI Transactions
-
The total number of coordinated
EVENTACTIONS
events. - Total Transactions with User-Requested Coordination
-
The total number of coordination’s that were explicitly requested in the configuration by means of the
COORDINATED
option of theMAP
parameter. - Average Coordination Time
-
The average time (in seconds) spent in coordination among all threads.
Syntax
STATS REPLICAT group_name
[, statistic]
[, DDLSONLY]
[, TABLE [container
. | catalog
.]schema
.table]
[, TOTALSONLY [container
. | catalog
.]schema
.table]
[, REPORTCDR]
[, REPORTCHARCONV]
[, REPORTDETAIL | NOREPORTDETAIL]
[, REPORTRATE {HR | MIN | SEC}]
-
group_name
-
The name of a Replicat group or a wildcard (*) to specify multiple groups. For example,
T*
shows statistics for all Replicat groups whose names begin with T. -
statistic
-
The statistic to be displayed. More than one statistic can be specified by separating each with a comma, for example
STATS REPLICAT finance, TOTAL, DAILY.
-
DDLSONLY
-
Displays the statistics for DDL statements including number of DDL statements in a readable format.
-
TABLE [container. | catalog.]schema.table_name]
-
Displays statistics only for the specified table or a group of tables specified with a wildcard (*). The table name or wildcard specification must be fully qualified with the two-part or three-part name, for example
hr.emp
or*.*.*
. -
TOTALSONLY [container. | catalog.]schema.table_name]
-
Summarizes the statistics for the specified table or a group of tables specified with a wildcard (*). The table name or wildcard specification must be fully qualified with the two-part or three-part name, for example
hr.emp
or*.*.*
. -
REPORTCDR
-
Shows statistics for Conflict Detection and Resolution. Statistics include:
-
Total CDR conflicts
-
CDR resolutions succeeded
-
CDR resolutions failed
-
CDR
INSERTROWEXISTS
conflicts -
CDR
UPDATEROWEXISTS
conflicts -
CDR
UPDATEROWMISSING
conflicts -
CDR
DELETEROWEXISTS
conflicts -
CDR
DELETEROWMISSING
conflicts
-
-
REPORTCHARCONV
-
Reports statistics for character validation when character-set conversion is performed. The following statistics are added to the
STATS
output:Total column character set conversion failure
: the number of validation or conversion failures in the current Replicat run.Total column data truncation
: the number of times that column data was truncated in the current Replicat run as the result of character set conversion -
REPORTDETAIL | NOREPORTDETAIL
-
Controls whether or not the output includes operations that were not replicated as the result of collision errors. These operations are reported in the regular statistics (inserts, updates, and deletes performed) plus as statistics in the detail display, if enabled. For example, if 10 records were insert operations and they were all ignored due to duplicate keys, the report would indicate that there were 10 inserts and also 10 discards due to collisions. The default is
REPORTDETAIL
. -
REPORTRATE
{HR | MIN | SEC}
-
Displays statistics in terms of processing rate rather than absolute values.
Examples
-
The following example displays total and hourly statistics per minute for a specific table, and it also resets the latest statistics. Statistics for discarded operations are not reported.
STATS REPLICAT finance, TOTAL, HOURLY, TABLE sales.acct, REPORTRATE MIN, RESET, NOREPORTDETAIL
-
The following example displays the same statistics as the previous example, but for thread 3 of a coordinated Replicat group.
STATS REPLICAT fin003, TOTAL, HOURLY, TABLE sales.acct, REPORTRATE MIN, RESET, NOREPORTDETAIL
Parent topic: GGSCI Command Line Interface Commands
STATS EXTRACT
Use STATS EXTRACT
to display statistics for one or more
Extract groups. The output includes DML and DDL operations that are included in the
Oracle GoldenGate configuration.
To get the most accurate number of operations per second that are being processed, do the following.
-
Issue the
STATS EXTRACT
command with theRESET
option. -
Issue the
STATS EXTRACT REPORTRATE
command. TheLATEST STATISTICS
field shows the operations per second.
Note:
The actual number of DML operations executed on a Db2 database might not match the number of extracted DML operations reported by Oracle GoldenGate. Db2 does not log update statements if they do not physically change a row, so Oracle GoldenGate cannot detect them or include them in statistics.Note:
To get accurate statistics on a Teradata source system where Oracle GoldenGate is configured in maximum protection mode, issueSTATS EXTRACT
to the
VAM-sort Extract, not the primary Extract. The primary Extract may contain
statistics for uncommitted transactions that could be rolled back; whereas the
VAM-sort Extract reports statistics only for committed transactions.
Syntax
STATS EXTRACT group_name
[, statistic]
[, DDLSONLY]
[, TABLE [container. | catalog.]schema.table]
[, TOTALSONLY [container. | catalog.]schema.table]
[, REPORTCDR]
[, REPORTCHARCONV]
[, REPORTFETCH | NOREPORTFETCH]
[, REPORTRATE time_units]
-
group_name
-
The name of an Extract group or a wildcard (*) to specify multiple groups. For example,
T*
returns statistics for all Extract groups whose names start with T. -
statistic
-
The statistic to be displayed. More than one statistic can be specified by separating each with a comma, for example
STATS EXTRACT finance, TOTAL, DAILY.
-
DDLSONLY
-
Displays the statistics for DDL statements including number of DDL statements in a readable format.
-
TABLE [container. | catalog.]schema.table_name
-
Displays statistics only for the specified table or a group of tables specified with a wildcard (*). The table name or wildcard specification must be fully qualified with the two-part or three-part name, for example
hr.emp
or*.*.*
. -
TOTALSONLY [container. | catalog.]schema.table_name
-
Summarizes the statistics for the specified table or a group of tables specified with a wildcard (*). The table name or wildcard specification must be fully qualified with the two-part or three-part name, for example
hr.emp
or*.*.*
. -
REPORTCDR
-
Shows statistics for Conflict Detection and Resolution. Statistics include:
-
Total CDR conflicts
-
CDR resolutions succeeded
-
CDR resolutions failed
-
CDR
INSERTROWEXISTS
conflicts -
CDR
UPDATEROWEXISTS
conflicts -
CDR
DELROWEXISTS
conflicts -
CDR
DELROWMISSING
conflicts
-
-
REPORTCHARCONV
-
Use only when
TABLE
parameters have aTARGET
clause and character-set conversion is performed. The following statistics are added to theSTATS
output:Total column character set conversion failure
: the number of validation or conversion failures in the current Extract run.Total column data truncation
: the number of times that column data was truncated in the current Extract run as the result of character set conversion -
REPORTFETCH | NOREPORTFETCH
-
Controls whether or not statistics about fetch operations are included in the output. The default is
NOREPORTFETCH
. The output ofREPORTFETCH
is as follows:-
row fetch attempts
: The number of times Extract attempted to fetch a column value from the database when it could not obtain the value from the transaction log. -
fetch failed
: The number ofrow fetch attempts
that failed. -
row fetch by key: Valid for Oracle. The number of row fetch attempts that were made by using the primary key. The default is to fetch by row ID.
-
-
REPORTRATE
time_units
-
Displays statistics in terms of processing rate rather than absolute values.
HR MIN SEC
Parent topic: GGSCI Command Line Interface Commands
STATS ER
Use the STATS ER
to get statistics on multiple Extract
and Replicat groups as a unit. Use it with wildcards to affect every Extract and
Replicat group that satisfies the wildcard. For descriptions and optional parameters
for this command, see INFO EXTRACT
.
Syntax
STATS ER group_name
Example
STATS ER *T*
Parent topic: GGSCI Command Line Interface Commands
START EXTRACT
Use START EXTRACT
to start the Extract process. To confirm that Extract has started, use the INFO EXTRACT
or STATUS EXTRACT
command. Extract can be started at its normal start point (from initial or current checkpoints) or from an alternate, user-specified position in the data source.
Normal Start Point
Without options, START EXTRACT
directs a primary Extract and a data pump Extract to start processing at one of the following locations in the data source to maintain data integrity:
-
After graceful or abnormal termination: At the first unprocessed transaction in the data source from the previous run, as represented by the current read checkpoint.
-
First-time startup after the group was created: At the start point specified with the
ADD EXTRACT
command.
Alternate Start Point
Before starting Extract with ATCSN
or AFTERCSN
, you must establish a physical starting location with one of the following commands:
-
ADD EXTRACT
with theBEGIN
option set to a timestamp that is earlier than the CSN value specified withATCSN
orAFTERCSN
. The transaction log that contains the timestamp and every log thereafter must be available on the system before Extract is started. -
ALTER EXTRACT
to the sequence number of the log that contains the CSN specified withATCSN
orAFTERCSN
.
Syntax
START EXTRACT group_name
[ATCSN csn
| AFTERCSN csn
]
[BRoptions]
-
group_name
-
The name of an Extract group or a wildcard (*) to specify multiple groups. For example,
T*
starts all Extract groups whose names begin with T. -
ATCSN
csn
| AFTERCSN
csn
-
Specifies an alternate start point.
-
ATCSN
-
Directs Extract to position its start point at the first transaction that has the specified CSN. Any transactions in the data source that have CSN values less than the specified one are skipped.
-
AFTERCSN
-
Directs Extract to position its start point at the beginning of the first transaction after the one that has the specified CSN. Any transactions in the data source that have CSN values that are less than, or equal to, the specified one are skipped.
-
csn
-
Specifies a CSN value. Enter the CSN value in the format that is valid for the database. Extract abends if the format is invalid and writes a message to the report file. To determine the CSN to supply after an initial load is complete, use the serial identifier at which the load utility completed. Otherwise, follow the instructions in the initial load procedure for determining when to start Extract.
The following are additional guidelines to observe when using
ATCSN
andAFTERCSN
:-
The CSN is stored in the file header so that it is available to downstream processes.
-
When a record that is specified with a CSN is found, Extract issues a checkpoint. The checkpoint ensures that subsequent Extract startups begin from the requested location, and not from a point prior to the requested CSN.
-
You must establish a physical start point in the transaction log or trail for Extract with
ADD EXTRACT
orALTER EXTRACT
before usingATCSN
orAFTERCSN
. These options are intended to be an additional filter after Extract is positioned to a physical location in the data source.
-
-
BRoptions
- Extract can be started with BR options. Here are some examples
of the BR options:
-
START EXTRACT BROFF
-
START EXTRACT BRRESET
-
START EXTRACT BRInterval # BRKEEPSTALEFILES
-
START EXTRACT BRKEEPSTALEFILES
-
START EXTRACT BRFSOPTION
See
BR
in Reference for Oracle GoldenGate for details. -
Parent topic: GGSCI Command Line Interface Commands
START REPLICAT
Use START REPLICAT
to start Replicat. To confirm that Replicat has started, use the INFO REPLICAT
or STATUS REPLICAT
command.
When starting an integrated Replicat group for an Oracle target database,
START REPLICAT
automatically registers Replicat with the target
database.
A coordinated Replicat can only be started as a whole. There is no option to start
individual threads. If the prior shutdown of a coordinated Replicat was not clean,
the threads may have stopped at different positions in the trail file. If this
happens, START REPLICAT
writes a warning if the parameter file was
changed since the prior run and raises an error if the number of threads was
changed.
Normal Start Point
Replicat can be started at its normal start point (from initial or current checkpoints) or from an alternate, user-specified position in the trail.
START REPLICAT
, without any options, causes Replicat to start processing at one of the following points to maintain data integrity:
-
After graceful or abnormal termination: At the first unprocessed transaction in the trail from the previous run, as represented by the current read checkpoint.
-
First-time startup after the group was created: From the beginning of the active trail file (
seqno 0
,rba 0
).
Alternate Start Point
The SKIPTRANSACTION
, ATCSN
, and AFTERCSN
options of START REPLICAT
cause Replicat as a whole, or specific threads of a coordinated Replicat, to begin processing at a transaction in the trail other than the normal start point. Use these options to:
-
Specify a logical recovery position when an error prevents Replicat from moving forward in the trail. Replicat can be positioned to skip the offending transaction or transactions, with the understanding that the data will not be applied to the target.
-
Skip replicated transactions that will cause duplicate-record and missing-record errors after a backup is applied to the target during an initial load. These options cause Replicat to discard transactions that occurred earlier than the most recent set of changes that were captured in the backup.You can map the value of the serial identifier that corresponds to the completion of the backup to a CSN value, and then start Replicat to begin applying transactions from the specified CSN onward.
Note:
Skipping a transaction, or starting at or after a CSN, might cause Replicat to start more slowly than normal, depending on how much data in the trail must be read before arriving at the appropriate transaction record. To view the startup progress, use theSEND REPLICAT
command with theSTATUS
option. To omit the need for Replicat to read through transactions that ultimately will be skipped, you can use theATCSN
orAFTERCSN
option when starting Extract and the data pumps, so that those transactions are omitted from the trail, seeSTART EXTRACT
.
Syntax
START REPLICAT group_name
[SKIPTRANSACTION | {ATCSN csn
| AFTERCSN csn}]
[FILTERDUPTRANSACTIONS | NOFILTERDUPTRANSACTIONS
]
[THREADS (threadID
[, threadID
][, ...][, thread_range
[, thread_range
][, ...])
-
group_name
-
The name of a Replicat group or a wildcard (*) to specify multiple groups. For example,
T*
starts all Replicat groups whose names begin with T. -
SKIPTRANSACTION
-
Causes Replicat to skip the first transaction after its expected startup position in the trail. All operations from that first transaction are excluded.
If the
MAXTRANSOPS
parameter is also being used for this Replicat, it is possible that the process will start to read the trail file from somewhere in the middle of a transaction. In that case, the remainder of the partial transaction is skipped, and Replicat resumes normal processing from the next begin-transaction record in the file. The skipped records are written to the discard file if theDISCARDFILE
parameter is being used; otherwise, a message is written to the report file that is similar to:User requested START SKIPTRANSACTION. The current transaction will be skipped. Transaction ID txid, position Seqno seqno, RBA rba
SKIPTRANSACTION
is valid only when the trail that Replicat is reading is part of an online change synchronization configuration (with checkpoints). Not valid for task-type initial loads (whereSPECIALRUN
is used withADD REPLICAT
). -
ATCSN csn | AFTERCSN csn
-
Sets a user-defined start point at a specific CSN. When
ATCSN
orAFTERCSN
is used, a message similar to one of the following is written to the report file:User requested start at commit sequence number (CSN) csn-string
User requested start after commit sequence number (CSN) csn-string
General information about these options:
-
Valid only when the trail that Replicat is reading is part of an online change synchronization configuration (with checkpoints). Not valid for task-type initial loads (where
SPECIALRUN
is used withADD REPLICAT
). -
To support starting at, or after, a CSN, the CSN is stored in the first trail record of each transaction. If Replicat is started with
AFTERCSN
against an earlier trail version, Replicat will abend and write an error to the report stating that the trail format is not supported.-
ATCSN
-
Causes Replicat to start processing at the transaction that has the specified CSN. Any transactions in the trail that have CSN values that are less than the specified one are skipped.
-
AFTERCSN
-
Causes Replicat to start processing at the transaction that occurred after the one with the specified CSN. Any transactions in the trail that have CSN values that are less than, or equal to, the specified one are skipped.
-
csn
-
Specifies a CSN value. Enter the CSN value in the format that is valid for the database. See Administering Oracle GoldenGate for CSN formats and descriptions. Replicat abends if the format is invalid and writes a message to the report file. To determine the CSN to supply after an initial load is complete, use the commit identifier at which the load utility completed the load. Otherwise, follow the instructions in the initial load procedure for determining when to start Replicat.
-
-
-
FILTERDUPTRANSACTIONS | NOFILTERDUPTRANSACTIONS
-
Causes Replicat to ignore transactions that it has already processed. Use when Extract is repositioned to a new start point (see the
ATCSN
orAFTERCSN
option of "START EXTRACT") and you are confident that there are duplicate transactions in the trail that could cause Replicat to abend. This option requires the use of a checkpoint table. The default isFILTERDUPTRANSACTIONS
. However, if you useNOFILTERDUPTRANSACTIONS
, the integrated Replicat default setting is overridden and causes it to not filter the duplicates. So it has the same effect on both classic and integrated Replicat. -
THREADS (threadID[, threadID][, ...][, thread_range[, thread_range][, ...])
-
Valid for
SKIPTRANSACTION
,ATCSN
, andAFTERCSN
when Replicat is in coordinated mode. Not valid forSTART REPLICAT
without those options. Starts the specified Replicat thread or threads at the specified location.-
threadID
[,
threadID
][, ...]
-
Specifies a thread ID or a comma-delimited list of threads in the format of
threadID, threadID, threadID
. -
thread_range[, thread_range][, ...]
-
Specifies a range of threads in the form of
threadIDlow-threadIDhigh
or a comma-delimted list of ranges in the format ofthreadIDlow-threadIDhigh
,threadIDlow-threadIDhigh
.
A combination of these formats is permitted, such as
threadID, threadID
,threadIDlow-threadIDhigh
. -
Examples
-
START REPLICAT repe
-
The following starts Replicat at an Oracle-specific CSN.
START REPLICAT repe, ATCSN 6488359
-
The following starts Replicat at a SQL Server-specific CSN after the one with the specified CSN.
START REPLICAT repe, AFTERCSN 0X000004D2:0000162E:0009
-
The following causes threads 4 and 5 of a coordinated Replicat to skip the first transaction after their last checkpoint when Replicat is started. If this were a 10-thread coordinated Replicat, threads 0-3 and 6-10 would all start at the normal start point, that of their last checkpoint.
START REPLICAT repe SKIPTRANSACTION THREADS(4-5)
-
The following example causes threads 1-3 of a coordinated Replicat to start at CSN 6488359, threads 9-10 to start after CSN 6488360, and threads 7 and 8 to skip the first transaction after its last checkpoint.
START REPLICAT repe ATCSN 6488359 THREADS(1-3), AFTERCSN 6488360 THREADS(9-10), SKIPTRANSACTION THREADS(7,8)
Parent topic: GGSCI Command Line Interface Commands
START MANAGER
Use START MANAGER
to start the Manager process. This
applies to a non-clustered environment. In a Windows cluster, you should stop
Manager from the Cluster Administrator.
Syntax
START MANAGER [, CPU number] [, PRI number] [, HOMETERM device_name] [, PROCESSNAME process_name]
-
CPU
number
-
Valid for SQL/MX. Specifies the number of the CPU to be used for the process. Valid values are numbers
0
-15
and-1
is default, which is assigned 1 higher than the last Manager started. -
PRI
number
-
Valid for SQL/MX. Specifies the Extract process priority. Valid values are numbers are
1
-199
and-1
is the default, and is the same as the manager process priority. -
HOMETERM
device_name
-
Valid for SQL/MX. Specifies the name of the device to be used and must be a terminal or process. It can be entered in either Guardian
$
or OSS/G/
xxxxx
form. The default is$zhome
or the current sessionHOMETERM
when$zhome
is not defined. -
PROCESSNAME
process_name
-
Valid for SQL/MX. Specifies the name of the process as alphanumeric string up to five characters and can be entered in either Guardian
$
or OSS/G/
xxxxx
form. The default is a system generated process name.
Examples
START MANAGER, CPU 2, PRI 148, HOMETERM /G/zhome, PROCESSNAME $ogmgr
Parent topic: GGSCI Command Line Interface Commands
SYNCHRONIZE REPLICAT
Valid for coordinated, integrated, and parallel Replicat. Use
SYNCHRONIZE REPLICAT
to return all of the threads of a Replicat
to the same position in the trail file after an unclean shutdown. This position is
the maximum checkpoint position of all of the threads, in other words, the most
recent trail record processed among all of the threads. When SYNCHRONIZE
REPLICAT
is issued, all threads are started and allowed to process
transactions until they reach the maximum checkpoint position, and then Replicat
stops.
For more information about how to use SYNCHRONIZE
REPLICAT
to recover a coordinated Replicat after an unclean shutdown,
or to enable repartitioning of data among different threads, see Administering a Coordinated Replicat Configuration.
Syntax
SYNCHRONIZE REPLICAT group_name
-
group_name
-
The name of a Replicat group or a wildcard (*) to specify multiple groups. For example,
T*
synchronizes the threads of all Replicat groups whose names begin with T. The threads synchronize to the same position within their group, not to the same position across all Replicat groups being synchronized with this command.
Example
SYNCHRONIZE REPLICAT repe
Parent topic: GGSCI Command Line Interface Commands
UNDELETE MASTERKEY
Use the UNDELETE MASTERKEY
command to remove the
deletion mark from a master key version, thus retaining that version if the
PURGE WALLET
command is used. Only one version can be unmarked
per UNDELETE MASTERKEY
command. See DELETE MASTERKEY to mark a version of a master key for deletion.
The OPEN WALLET
command must be used before using this
command or any of the commands that add, renew, or delete the master keys in the
wallet.
Syntax
UNDELETE MASTERKEY VERSION version
Example
This command unmarks version 3 of the master key and returns a message similar to the one shown.
UNDELETE MASTERKEY VERSION 3
Output:
Version 3 of Masterkey 'OGG_DEFAULT_MASTERKEY' undeleted from wallet at location './ wallet'.
Or
UNDELETE MASTERKEY VERSION 3
Output:
Version 3 of Masterkey 'OGG_DEFAULT_MASTERKEY' undeleted from wallet at location './dirwlt'.
Parent topic: GGSCI Command Line Interface Commands
UNREGISTER EXTRACT
Valid for Oracle and PostgreSQL.
Use UNREGISTER EXTRACT
to remove the registration of an Extract group from an Oracle database. UNREGISTER EXTRACT
is valid only for a primary Extract group. Do not use it for a data pump Extract.
To register an Extract group with the database, use the REGISTER EXTRACT
command.
To upgrade an Extract from classic capture mode to integrated capture mode, use the ALTER EXTRACT
command.
For PostgreSQL, a replication slot is dropped in the connected database for PostgreSQL. This command ensures that the PostgreSQL database overwrites the existing transaction log or may archive the log . After deleting the Extract, the command must be run.
Syntax
UNREGISTER EXTRACT group_name
-
group_name
-
The name of the Extract group that is to be unregistered from the database. Do not use a wildcard. This group must currently be registered with the database.
-
DATABASE
-
(Oracle only) Disables integrated capture mode for the Extract group.
(Oracle only) This command removes the database capture (mining) server that has the same name as the Extract group. For additional information about support for, and configuration of, the Extract capture modes.
Before using
UNREGISTER EXTRACT
withDATABASE
, do the following:-
Stop Extract with the
STOP EXTRACT
command. -
Log in to the mining database with the
DBLOGIN
orMININGDBLOGIN
command with the privileges granted in thedbms_goldengate_auth.grant_admin_privilege
procedure. For local capture,DBLOGIN
is required. For downstream capture,DBLOGIN
andMININGDBLOGIN
are both required. -
Delete the Extract group with
DELETE EXTRACT
.
-
Parent topic: GGSCI Command Line Interface Commands
UPGRADE CHECKPOINTTABLE
Not valid for Replicat for Java, Oracle GoldenGate Applications Adapter, or Oracle GoldenGate Big Data.
Use the UPGRADE CHECKPOINTTABLE
command to add a
supplemental checkpoint table when upgrading Oracle GoldenGate.
Syntax
UPGRADE CHECKPOINTTABLE [[container. | catalog.]owner.table]
-
container
. |
catalog
. -
The Oracle pluggable database. If this option is omitted, the catalog or pluggable database defaults to the one that is associated with the
SOURCEDB
,USERID
, orUSERIDALIAS
portion of theDBLOGIN
command (depending on the database). -
owner.table
-
The owner and name of the checkpoint table. An owner and name are not required if they are the same as those specified with the
CHECKPOINTTABLE
parameter in theGLOBALS
file.
Example
UPGRADE CHECKPOINTTABLE ggadmin.ggs_checkpoint
Parent topic: GGSCI Command Line Interface Commands
UPGRADE HEARTBEATTABLE
Valid for Oracle, Db2 z/OS, Db2 for i, Db2 LUW, MySQL, PostgreSQL, SQL Server, Teradata, and TimesTen.
Use UPGRADE HEARTBEATTABLE
when upgrading Oracle
GoldenGate from a prior release, to enable any new heartbeat functionality available
in the current release.
This command requires a DBLOGIN
. On a CDB database for
Oracle, a PDB login is required.
Oracle GoldenGate for Oracle database simplifies the
administration of the heartbeat table by eliminating the need for
GGSCHEMA
or HEARTBEATTABLE
parameter. In case
of CDB root Extract, GGSCHEMA
is used.
Syntax
UPGRADE HEARTBEATTABLE
Parent topic: GGSCI Command Line Interface Commands
VIEW ENCKEYS
Use VIEW ENCKEYS
to display the contents of the
ENCKEYS
file in read-only mode on-screen.
Syntax
VIEW ENCKEYS
Parent topic: GGSCI Command Line Interface Commands