TA的每日心情 | 开心
2023-8-9 11:05 |
|---|
|
GGSCI Command Summary:
Object: Command:
SUBDIRS CREATE
ER INFO, KILL, LAG, SEND, STATUS, START, STATS, STOP
EXTRACT ADD, ALTER, CLEANUP, DELETE, INFO, KILL,
LAG, REGISTER, SEND, START, STATS, STATUS, STOP
UNREGISTER
EXTTRAIL ADD, ALTER, DELETE, INFO
GGSEVT VIEW
MANAGER INFO, SEND, START, STOP, STATUS
MARKER INFO
PARAMS EDIT, VIEW
REPLICAT ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND,
START, STATS, STATUS, STOP
REPORT VIEW
RMTTRAIL ADD, ALTER, DELETE, INFO
TRACETABLE ADD, DELETE, INFO
TRANDATA ADD, DELETE, INFO
SCHEMATRANDATA ADD, DELETE, INFO
CHECKPOINTTABLE ADD, DELETE, CLEANUP, INFO
Commands without an object:
(Database) DBLOGIN, LIST TABLES, ENCRYPT PASSWORD, FLUSH SEQUENCE
(DDL) DUMPDDL
(Miscellaneous) FC, HELP, HISTORY, INFO ALL, OBEY, SET EDITOR, SHELL,
SHOW, VERSIONS, ! (note: you must type the word
COMMAND after the ! to display the ! help topic.)
i.e.: GGSCI (sys1)> help ! command
---------------------------------------------------------------------
####################################
#
#MANAGER COMMANDS
#
#
####################################
----------------------------------------------------------------------
INFO MANAGER
INFO MANAGER
Use INFO MANAGER to determine whether or not the Manager process is
running. If Manager is running, the port number is displayed. This
command is an alias for STATUS MANAGER.
Syntax:
INFO MANAGER
----------------------------------------------------------------------
SEND MANAGER
SEND MANAGER
Use SEND MANAGER to retrieve the status of the active Manager process
or to retrieve dynamic port information as configured in the Manager
parameter file.
Syntax:
SEND MANAGER
[CHILDSTATUS [DEBUG]]
[GETPORTINFO [DETAIL]
[GETPURGEOLDEXTRACTS]
CHILDSTATUS [DEBUG]
Retrieves status information about processes started by Manager. DEBUG
returns the port numbers that have been allocated by Manager. (Requires
ports to be specified with the DYNAMICPORTLIST parameter.)
GETPORTINFO [DETAIL]
By default, retrieves the current list of ports that have been
allocated by Manager and their corresponding process IDs. (Requires
ports to be specified with the DYNAMICPORTLIST parameter.)
DETAIL provides additional details, including all ports listed with
DYNAMICPORTLIST and which ones are assigned to a process.
GETPURGEOLDEXTRACTS
Displays information about trail maintenance rules that are set with
the PURGEOLDEXTRACTS parameter in the Manager parameter file.
Example 1:
SEND MANAGER CHILDSTATUS DEBUG
Example 2:
SEND MANAGER GETPORTINFO DETAIL
Example 3:
SEND MANAGER GETPURGEOLDEXTRACTS
----------------------------------------------------------------------
START MANAGER
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
----------------------------------------------------------------------
STATUS MANAGER
STATUS MANAGER
Use STATUS MANAGER to determine whether or not the Manager process is
running. If Manager is running, the port number is displayed.
Syntax:
STATUS MANAGER
----------------------------------------------------------------------
STOP MANAGER
STOP MANAGER
Use STOP MANAGER to stop the Manager process. This applies to non-
clustered environments. In a Windows cluster, Manager must be stopped
through the Cluster Administrator.
Syntax:
STOP MANAGER [!]
!
(Exclamation point) Bypasses the prompt that confirms the intent to
shut down Manager.
---------------------------------------------------------------------
####################################
#
#EXTRACT COMMANDS
#
#
####################################
---------------------------------------------------------------------
ADD EXTRACT
ADD EXTRACT
Use ADD EXTRACT to create an Extract group. Unless a SOURCEISTABLE task
or an alias Extract is specified, ADD EXTRACT creates checkpoints so
that processing continuity is maintained from run to run. Review the
Oracle GoldenGate Windows and UNIX Administrator抯 Guide before
creating an Extract group.
The Oracle GoldenGate GGSCI command interface fully supports up to 300
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. Beyond the supported level, group information is not
displayed and errors can occur. Oracle GoldenGate recommends keeping
the number of Extract and Replicat groups (combined) at 300 or below in
order to manage your environment effectively.
Syntax for a regular, passive, or data pump Extract:
ADD EXTRACT <group name>
{, SOURCEISTABLE |
, TRANLOG [<bsds name>] |
, VAM |
, EXTFILESOURCE <file name> |
, EXTTRAILSOURCE <trail name> |
, VAMTRAILSOURCE <VAM trail name>}
{, BEGIN {NOW | yyyy-mm-dd [hh:mi:[ss[.cccccc]]]} |
, EXTSEQNO <seqno>, EXTRBA <relative byte address> |
, LOGNUM <log number>, LOGPOS <byte offset> |
, EOF |
, LSN <value> |
, EXTRBA <relative byte address> |
, EOF | LSN <value> |
, PAGE <data page>, ROW <row> |
}
[, THREADS <n>]
[, PASSIVE]
[, PARAMS <parameter file>]
[, REPORT <report file>]
[, DESC ?description>擼
Syntax for an alias Extract:
ADD EXTRACT <group name>
, RMTHOST {<host name> | <IP address>}
, {MGRPORT <port>} | {PORT <port}
[, RMTNAME <name>]
[, DESC ?description>擼
<group name>
The name of the Extract group. Use the following naming
conventions.
* You can use up to eight ASCII characters, including
non-alphanumeric characters such as the underscore (_). Any ASCII
character can be used, so long as the operating system allows that
character to be in a filename. This is because a group is
identified by its associated checkpoint file.
* The following ASCII characters are not allowed in a file name:
{ \ / : * ? " < > | }
* On HP UX, Linux, and Solaris, it is possible to create a file name
with a colon (:) or an asterisk (*), although it is not
recommended.
* In general, group names are not case-sensitive within Oracle
GoldenGate. For example, finance, Finance, and FINANCE are all
considered to be the same. However, on Linux, the group name (and
its parameter file name if explicitly defined in the ADD command)
must be all uppercase or all lowercase. Mixed case group names and
parameter file names will result in errors when starting the
process.
* Use only one word.
* Do not use the word 損ort?as a group name. However, you can use
the string 損ort?as part of the group name.
* Do not place a numeric value at the end of a group name, such as
fin1, fin10, and so forth. You can place a numeric value at the
beginning of a group name, such as 1_fin, 1fin, and so forth.
Examples of acceptable names:
ext_1
ex+2t
ex!2t
ADD EXTRACT data source options:
SOURCEISTABLE
Creates an Extract task that extracts entire records from the database
for an initial load using the Oracle GoldenGate direct load method or
the direct bulk load to SQL*Loader method. 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 using SOURCEISTABLE, do not specify any service options. Task
parameters must be specified in the parameter file.
For more information about initial load methods, see the Oracle
GoldenGate Windows and UNIX Administrator抯 Guide.
TRANLOG [<bsds name>]
Specifies the transaction log as the data source. Use this option for
all databases except Teradata. TRANLOG requires the BEGIN option.
(DB2 for z/OS) Use the <bsds name> option for DB2 on a z/OS system to
specify the BSDS (Bootstrap Data Set) file name of the transaction log.
Make certain that the BSDS name you provide is the one for the DB2
instance to which the Extract process is connected. Oracle GoldenGate
does not perform any validations of the BSDS specification.
(Oracle) To use TRANLOG, you might need to issue a DBLOGIN command
before you create your Extract groups. This depends on the Oracle
database version:
* Oracle Enterprise Edition version 10.2 or higher: Issue a DBLOGIN
command before you create your Extract groups. One DBLOGIN command is
sufficient, but requires certain database privileges. See the DBLOGIN
command help.
* All other databases: Do not issue DBLOGIN before you create your
Extract groups.
VAM
(Teradata) Specifies that the Extract API known as the Vendor Access
Module (VAM) will interface with the Teradata Access Module (TAM). Use
for Teradata databases.
EXTFILESOURCE <file name>
Specifies an extract file as the data source. Use this option with a
secondary Extract group (data pump) that acts as an intermediary
between a primary Extract group and the target system.
For <file name>, specify the fully qualified path name of the file, for
example c:\ggs\dirdat\extfile.
EXTTRAILSOURCE <trail name>
Specifies a trail as the data source. Use this option with a secondary
Extract group (data pump) that acts as an intermediary between a
primary Extract group and the target system.
For <trail name>, specify the fully qualified path name of the trail,
for example c:\ggs\dirdat\aa.
VAMTRAILSOURCE <VAM trail name>
(Teradata) Specifies a VAM trail. Use this option when using Teradata
maximum protection mode. Specify the fully qualified path name of the
VAM trail to which the primary Extract group is writing. Use a VAM-sort
Extract group to read the VAM trail and send the data to the target
system.
ADD EXTRACT service options:
BEGIN {NOW | yyyy-mm-dd [hh:mi:[ss[.cccccc]]]}
Specifies a timestamp in the data source at which to begin processing.
Valid values:
* NOW
* A date and time in the format of yyyy-mm-dd [hh:mi:[ss[.cccccc]]]
What NOW means:
For all databases except DB2 LUW, NOW specifies the time at which
the ADD EXTRACT command is issued.
For DB2 LUW, NOW specifies the time at which START EXTRACT takes
effect. It positions to the first record that approximately matches
the date and time. This is because the only log records that contain
timestamps are the commit and abort transaction records, so the
starting position can only be calculated relative to those
timestamps. This is a limitation of the API that is used by Oracle
GoldenGate.
Do not use NOW for a data pump Extract except to bypass
data that was captured to the trail prior to the ADD EXTRACT
statement.
Positioning by timestamp in a SQL Server transaction log:
Positioning by time is affected by the following limitations 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).
Note: Positioning to an LSN is precise. See <LSN <value>.
Positioning by timestamp in a Sybase transaction log:
Sybase only records timestamps in BEGIN and COMMIT records. Regardless
of the actual timestamp that is specified, the start position will be
the first record of the transaction that starts closest to, or at, the
specified timestamp. The Extract report will display the following
positions:
* Positioning To: This is the specified begin time, for example:
Positioning to begin time Jun 7, 2010 12:13:33 PM.
* Positioned To: If the specified timestamp is less than, or equal to,
the timestamp of the transaction log that contains the BEGIN or
COMMIT record, "Positioned To Page" is displayed as in this example:
2010-06-07 12:13:39 INFO OGG-01516 Positioned to
Page #: 0004460243
Row #: 00111, Jun 7, 2010 12:13:38 PM.
* First Record Position: This is the position of the first valid record
at, or after, the Positioned To position, as in this example:
010-06-07 12:13:39 INFO OGG-01517 Position of first record
processed
Page #: 0004460243
Row #: 00111, Jun 7, 2010 12:13:38 PM.
EXTSEQNO <seqno>, EXTRBA <relative byte address>
Valid for a primary Extract for Oracle and NonStop SQL/MX, and for a
data pump Extract. Specifies either of the following:
* sequence number of an Oracle redo log and RBA within that log at
which to begin capturing data.
* the NonStop SQL/MX TMF audit trail sequence number and 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 c:\ggs\dirdat\aa000026,
you would specify EXTSEQNO 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 <relative byte address>
Valid for DB2 on z/OS. Specifies the relative byte address within a
transaction log at which to begin capturing data.
LOGNUM <log number>, LOGPOS <byte offset>
Valid for c-tree. Specifies the location in a c-tree transaction log at
which to start capturing data.
* <log number> is the number of the c-tree log file.
* <byte offset> is the relative position from the beginning of the file
(0 based).
EOF
Valid for SQL Server. Configures processing to start at the end of the
log files that the next record will be written to. Any active
transactions will not be captured.
LSN <value>
Valid for SQL Server. Specifies the LSN in a SQL Server transaction log
at which to start capturing data. The specified LSN should exist in a
log backup or the online log. An alias for this option is EXTLSN.
For SQL Server, an LSN is composed of one of these, depending on
how the database returns it:
* Colon separated Hex string (8:8:4) padded with leading zeroes and
0X prefix, as in 0X00000d7e:0000036b:01bd
* Colon separated Decimal string (10:10:5) padded with leading zeroes,
as in 0000003454:0000000875:00445
* Colon separated Hex string with 0X prefix and without leading zeroes,
as in 0Xd7e:36b:1bd
* Colon separated Decimal string without leading zeroes, as in
3454:875:445
* Decimal string, as in 3454000000087500445
In the preceding, the first value is the virtual log file number, the
second is the segment number within the virtual log, and the third is
the entry number.
You can find the LSN for named transactions by using a query like:
select [Current LSN], [Transaction Name], [Begin Time]
from fn_dblog(null, null)
where Operation = 'LOP_BEGIN_XACT'
and [Begin Time] >= <time>
You can determine the time that a particular transaction started, then
find the relevant LSN, and then position between two transactions with
the same begin time.
EOF | LSN <value>
Valid for DB2 LUW. Specifies a start position in the transaction logs
when Extract starts.
* EOF configures processing to start at the active LSN in the log
files. The active LSN 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.
* LSN <value> configures processing to start at an exact LSN if a valid
log record exists there. If one does not exist, Extract will abend.
Note that, although Extract might position to a given LSN, that LSN
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 starting LSN to the Extract report file.
PAGE <data page>, ROW <row>
Valid for Sybase. Specifies a data page and row that together define a
start position in a Sybase transaction log. The start position must be
the first record of the transaction that starts closest to, or at, the
specified PAGE and ROW. The Extract report will display the following
positions:
Positioning To is the position of the record that is specified with
PAGE and ROW.
Positioned To is the position where the first BEGIN record is found at,
or after, the Positioning To position.
First Record Position is the position of the first valid record at, or
after, the Positioned To position.
PARAMS <parameter file>
Specifies an Extract parameter file in a location other than the
default of dirprm within the Oracle GoldenGate directory. Use the fully
qualified path name.
REPORT <report file>
Specifies an Extract report file in a location other than the default
of dirrpt within the Oracle GoldenGate directory. Use the fully
qualified path name.
THREADS <n>
Specifies the number of redo threads when extracting data from an
Oracle RAC clustered database configuration.
PASSIVE
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.
For instructions on how to configure passive and alias Extract groups,
see the Oracle GoldenGate Windows and UNIX Administrator抯 Guide.
DESC ?description>?
Specifies a description of the group, such as 揈xtracts account_tab on
Serv1? The description must be within quotes. You may use the
abbreviated keyword DESC or the full word DESCRIPTION.
RMTHOST {<host name> | <IP address>}
Identifies this group as an alias Extract and specifies either the DNS
name of the remote host or its IP address.
{MGRPORT <port>} | {PORT <port}
Use for an alias Extract. Specify one of the following:
* MGRPORT specifies the port on the remote system where Manager is
running. Use this option when using a dynamic Collector.
* PORT specifies a static Collector port. Use instead of MGRPORT only
if running a static Collector.
RMTNAME <name>
Use for an alias Extract. Specifies the passive Extract name, if
different from that of the alias Extract.
Example 1:
The following creates an Extract group named 揻inance?that extracts
database changes from the transaction logs. Extraction starts with
records generated at the time when the group was created with ADD
EXTRACT.
ADD EXTRACT finance, TRANLOG, BEGIN NOW
Example 2:
The following creates an Extract group named 揻inance?that extracts
database changes from Oracle RAC logs. Extraction starts with records
generated at the time when the group was created. There are four RAC
instances, meaning there will be four Extract threads.
ADD EXTRACT finance, TRANLOG, BEGIN NOW, THREADS 4
Example 3:
The following creates an Extract group named 揻inance?that extracts
database changes from the transaction logs. Extraction starts with
records generated at 8:00 on January 1, 2010.
ADD EXTRACT finance, TRANLOG, BEGIN 2010-01-01 08:00
Example 4:
The following creates an Extract group named 揻inance?that interfaces
with a Teradata TAM in either maximum performance or maximum protection
mode. No BEGIN point is used for Teradata sources.
ADD EXTRACT finance, VAM
Example 5:
The following creates a VAM-sort Extract group named 揻inance.?The
process reads from the VAM trail /ggs/dirdat/vt.
ADD EXTRACT finance, VAMTRAILSOURCE /ggs/dirdat/vt
Example 6:
The following creates a data-pump Extract group named 揻inance.?It
reads from the Oracle GoldenGate trail c:\ggs\dirdat\lt.
ADD EXTRACT finance, EXTTRAILSOURCE c:\ggs\dirdat\lt
Example 7:
The following creates an initial-load Extract named 搇oad.?
ADD EXTRACT load, SOURCEISTABLE
Example 8:
The following creates a passive Extract group named 揻inance?that
extracts database changes from the transaction logs.
ADD EXTRACT finance, TRANLOG, BEGIN NOW, PASSIVE
Example 9:
The following creates an alias Extract group named 揻inanceA.?The
alias Extract is associated with a passive extract named 揻inance?on
source system sysA. The Manager on that system is using port 7800.
ADD EXTRACT financeA, RMTHOST sysA, MGRPORT 7800, RMTNAME finance
---------------------------------------------------------------------
ALTER EXTRACT
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.
Before using this command, stop Extract by issuing the
STOP EXTRACT <group name> command.
Syntax:
ALTER EXTRACT <group name>
[, <ADD EXTRACT attribute>]
[, THREAD <number>]
[, ETROLLOVER]
<group name>
The name of the Extract group that is to be altered.
<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 finance, BEGIN 2011-01-01 08:00
ALTER EXTRACT finance, ETROLLOVER
THREAD <number>
In an Oracle RAC configuration, alters Extract only for the specified
redo thread. Only one thread number can be specified.
ETROLLOVER
Causes Extract to increment to the next file in the trail sequence when
restarting. For example, if the current file is ET000002, the current
file will be ET000003 when Extract restarts. A trail can be incremented
from 000001 through 999999, and then the sequence numbering starts over
at 000000.
Example 1:
The following alters Extract to start processing data from January 1,
2010.
ALTER EXTRACT finance, BEGIN 2010-01-01
Example 2:
The following alters Extract to start processing at a specific location
in the trail.
ALTER EXTRACT finance, EXTSEQNO 26, EXTRBA 338
Example 3
The following alters Extract in an Oracle RAC environment, and applies
the new begin point only for redo thread 4.
ALTER EXTRACT accounts, THREAD 4, BEGIN YYYY-MM-DD
Example 4
The following alters Extract in a SQL Server environment to start at a
specific LSN.
ALTER EXTRACT sales, LSN 3454:875:445
Example 5
The following alters Extract to increment to the next file in the trail
sequence.
ALTER EXTRACT finance, ETROLLOVER
---------------------------------------------------------------------
CLEANUP EXTRACT
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>]
<group name>
The name of an Extract group or a wildcard (*) to specify multiple
groups. For example, T* cleans up all Extract groups whose names start
with T.
SAVE <count>
Excludes the specified number of the most recent records from the
cleanup.
Example 1:
The following deletes all but the last record.
CLEANUP EXTRACT finance
Example 2:
The following deletes all but the most recent five records.
CLEANUP EXTRACT *, SAVE 5
---------------------------------------------------------------------
DELETE EXTRACT
DELETE EXTRACT
Use DELETE EXTRACT to delete an Extract group. This command performs
the following functions:
* 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.
* (Oracle Enterprise Edition version 10.2 and higher) Unregisters the
Extract group from the database. Unregistering the group removes the
associated Oracle Streams Capture process and stops Oracle Recovery
Manager from retaining the logs that the group needs for recovery.
Before using DELETE EXTRACT, stop Extract by issuing the STOP EXTRACT
<group name> command. You might also need to issue a DBLOGIN command,
depending on the Oracle database version:
* Oracle Enterprise Edition version 10.2 or higher: Issue DBLOGIN
before you delete the Extract group. DBLOGIN is required whether or
not Oracle Recovery Manager is retaining logs for this group. One
DBLOGIN command is sufficient to delete any number of groups. The
user that is identified with DBLOGIN must have certain database
privileges. See the DBLOGIN help entry.
If DBLOGIN is omitted, you can unregister the Extract group manually
with the UNREGISTER EXTRACT command.
* All other databases: Do not issue DBLOGIN before you delete Extract
groups.
To delete the trail files that are associated with the Extract group,
delete them manually through the operating system.
Syntax:
DELETE EXTRACT <group name> [!]
<group name>
The name of an Extract group or a wildcard specification (*) to specify
multiple groups. For example, T* deletes all Extract groups whose names
start with T.
!
(Exclamation point) Deletes all Extract groups associated with a
wildcard without prompting.
---------------------------------------------------------------------
INFO EXTRACT
INFO EXTRACT
Use INFO EXTRACT to view the following information.
* Status of Extract (STARTING, RUNNING, STOPPED or ABENDED).
* Approximate Extract lag.
* Checkpoint information.
* Process run history.
* The trail(s) to which Extract is writing.
The basic command, without either the TASKS or ALLPROCESSES argument,
displays information only for online (continuous) Extract processes.
Tasks are excluded.
About time lag:
Time lag 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抯 clock is
ahead of the target system抯 clock (due to clock imperfections, not
time zone differences). For more precise lag information, use LAG
EXTRACT.
About showing checkpoints:
Extract checkpoint positions are composed of read checkpoints in the
data source and write checkpoints in the trail. You can view past
checkpoints by specifying the number of them that you want to view
after the SHOWCH entry.
About read Checkpoints:
The following describes the Extract read checkpoints. Extract places a
read checkpoint in the data source.
Startup Checkpoint: The first checkpoint made in the data source when
the process started.
Recovery Checkpoint: The position in the data source of the record
containing the oldest transaction not yet processed by Extract. The
fields for this statistic are the same as those of the other read
checkpoint types.
Current Checkpoint: The position of the last record read by Extract
in the data source. This should match the Log Read Checkpoint
statistic shown in the summary and in the basic INFO EXTRACT command
without options. The fields for this statistic are the same as those
of the other read checkpoint types.
About the write checkpoint:
Extract places a Write checkpoint in the trail. This is called the
Current Checkpoint and is the position in the trail where Extract is
currently writing.
Other SHOWCH output
The Header, File Information, Configuration, and Status statistics at
the end of the SHOWCH display are for use by Oracle Support analysts.
They contain internal information that is useful when resolving a
support case.
Syntax:
INFO EXTRACT <group name>
[, SHOWCH [<n>]]
[, DETAIL]
[, TASKS | ALLPROCESSES]
<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.
SHOWCH [<n>]
The basic command shows information about the current Extract
checkpoints. Specify a value for <n> 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 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.
Example 1:
INFO EXTRACT fin*, SHOWCH
Example 2:
INFO EXTRACT *, TASKS
---------------------------------------------------------------------
KILL EXTRACT
KILL EXTRACT
Use KILL EXTRACT to kill 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 a killed Extract process.
Syntax:
KILL EXTRACT <group name>
<group name>
The name of an Extract group or a wildcard (*) to specify multiple
groups. For example, T* kills all Extract processes whose group names
start with T.
Example:
KILL EXTRACT finance
---------------------------------------------------------------------
LAG EXTRACT
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.
About Extract lag:
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.
Syntax:
LAG EXTRACT <group name>
<group name>
The name of an Extract group or a wildcard (*) to specify multiple
groups. For example, T* determines lag time for all Extract groups
whose names start with T.
Example 1:
LAG EXTRACT *
Example 2:
LAG EXTRACT *fin*
--------------------------------------------------------------------
REGISTER EXTRACT
REGISTER EXTRACT
Use REGISTER EXTRACT to register an Extract group with an Oracle
database so that Extract can interact with a specified component of the
database. Currently, you can register Extract to retain the archive
logs that Extract needs for recovery.
REGISTER EXTRACT is valid for the following:
* Oracle Enterprise Edition version 10.2 and later.
* A primary Extract group only (the one that reads from the redo logs).
Do not use this command for a data pump Extract.
* An existing Extract group or one that has not yet been created.
Before using REGISTER EXTRACT, do the following:
1. Stop Extract with the STOP EXTRACT <group> command, if necessary.
2. Issue the DBLOGIN command with special privileges to specify the
correct database.
To unregister an Extract group from the database, use DELETE EXTRACT
and UNREGISTER EXTRACT.
Syntax:
REGISTER EXTRACT <group name> LOGRETENTION
<group name>
The name of the Extract group that is to be registered. Do not use a
wildcard.
LOGRETENTION
Registers Extract so that it works with Oracle Recovery Manager (RMAN)
to retain the logs that Extract needs for recovery. REGISTER EXTRACT
with LOGRETENTION provides a workaround for cases where an Extract
group was created with the ADD EXTRACT command without first issuing
the DBLOGIN command.
The LOGRETENTION registration process creates an underlying Oracle
Streams Capture process that is dedicated to the Extract group and has
a similar name. The Capture is only used for the purpose of log
retention through RMAN. The logs will be retained from the time that
you issue the REGISTER EXTRACT command, based on the current database
SCN.
The log-retention feature is controlled with the LOGRETENTION option of
the TRANLOGOPTIONS parameter.
Example:
REGISTER EXTRACT sales LOGRETENTION
---------------------------------------------------------------------
SEND EXTRACT
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>,
{
BRSTATUS |
CACHEMGR {CACHESTATS | CACHEQUEUES | CACHEPOOL} |
FORCESTOP |
FORCETRANS <ID> [THREAD <n>] [FORCE] |
GETLAG |
GETTCPSTATS |
LOGEND |
LOGSTATS |
REPORT |
ROLLOVER |
SHOWTRANS [<ID>] [THREAD <n>] [COUNT <n>]
[DURATION <duration><unit>] [TABULAR]
[FILE <name> [DETAIL]] |
SKIPTRANS <ID> [THREAD <n>] [FORCE] |
STATUS |
STOP |
TRACE[2] <tracefile> |
TRACE[2] OFF |
TRACEINIT |
TRANLOGOPTIONS {PURGEORPHANEDTRANSACTIONS |
NOPURGEORPHANEDTRANSACTIONS} |
TRANLOGOPTIONS TRANSCLEANUPFREQUENCY <minutes> |
VAMMESSAGE ?Teradata command>?|
VAMMESSAGE {揂RSTATS?| 揑NCLUDELIST [filter]?|
揈XCLUDELIST [filter]攠 |
VAMMESSAGE 揙PENTRANS?
}
<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.
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 and timestamps for the next and last
checkpoints.
* Start and end sequence, RBA, SCN, and timestamp for the Bounded
Recovery checkpoint positions (per redo thread).
CACHEMGR {CACHESTATS | CACHEQUEUES | CACHEPOOL}
Retrieves statistics about the Oracle GoldenGate memory cache manager.
* CACHESTATS returns statistics about the overall utilization of
virtual memory, such as that which is being used, that which has been
recycled for possible reassignment, that which is in anonymous
memory, and that which corresponds to paged virtual memory. Also
provided are the size of the cache and the thresholds related to
buffer allocation and paging, and statistics related to the cache
files on disk and their related I/O.
* CACHEQUEUES returns statistics about the queues, which are memory
buffers that have been recycled for future use. Of particular
interest are a statistic that shows the number of times a buffer of
the required size was found, and the number of times that such a
buffer could not be found, resulting in a new request for more
virtual memory.
* CACHEPOOL returns statistics about the cache sub-pools, which consist
of one sub-pool per log reader thread (one or more in Oracle RAC; one
otherwise) for most transaction row data, and another sub-pool for
BLOB data and possibly other large items. The actual allocation of
data to one or the other of these pool types might not always
correspond to the type of data that is being stored.
These statistics can be used as the basis for adjusting the CACHEMGR
parameter. However, there is generally no need to view these statistics
or change CACHEMGR because the cache manager is self-adjusting. Do not
make any adjustments to the memory cache manager unless you open a
support case first. For more information, go to:
http://support.oracle.com.
FORCESTOP
Forces Extract to stop, bypassing any notifications. This command will
stop the process immediately.
FORCETRANS <ID> [THREAD <n>] [FORCE]
(Oracle only) Forces Extract to write a transaction specified by its ID
number to the trail as a committed transaction. Get the ID number with
SHOWTRANS or from an Extract runtime message. Extract will ignore any
data added to the transaction after this command is issued.
Options:
* Use THREAD <n> to specify which thread generated the transaction in
an Oracle RAC environment if there are duplicate transaction Ids
across threads.
* Use FORCE to bypass the confirmation prompt.
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.
In order to use FORCETRANS, the transaction specified must be the
oldest one in the list of transactions shown with SHOWTRANS. You can
repeat the command for other transactions in order of their age.
After using FORCETRANS, wait at least five minutes if you intend to
issue SEND EXTRACT with FORCESTOP. 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.
GETLAG
Determines a true lag time between Extract and the data source. Returns
the same results as LAG EXTRACT.
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).
If compression is enabled, the following statistics are present:
* Compression CPU time: The amount of CPU used to perform the
compression.
* Compress time: total amount of time that compression took, such as
waits for CPU resources.
* 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 of RMTHOST and RMTHOSTOPTIONS 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
(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. For details, see the
Oracle GoldenGate for Windows and UNIX Reference Guide.
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 the RESETREPORTSTATS |
NORESETREPORTSTATS option.
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 be ET000003 after the command executes. A trail can
be incremented from 000001 through 999999, and then the sequence
numbering starts over at 000000.
SHOWTRANS
[<ID>]
[THREAD <n>]
[COUNT <n>]
[DURATION <duration><unit>]
[TABULAR] |
[FILE <name> [DETAIL]]
(Oracle only) Displays information about open transactions.
SHOWTRANS shows:
* Process checkpoint (indicating 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 or Running). Pending COMMIT is displayed while
a transaction is being written after a FORCETRANS was issued.
Without options, SHOWTRANS displays all open transactions that will fit
into the 25K buffer.
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, because it is not known whether
operations on configured tables will be added to a transaction at some
point in the future.
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.
To further control output, see the following options.
SHOWTRANS options:
* <ID> limits the command output to a specific transaction.
* THREAD <n> constrains the output to open transactions against a
specific Oracle RAC thread. For <n>, use a 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 to
100,000.
* 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 seconds, minutes, hours, or days in fully spelled out or
abbreviated form:
S|SEC|SECS|SECOND|SECONDS
M|MIN|MINS|MINUTE|MINUTES
H|HOUR|HOURS
D|DAY|DAYS
* TABULAR generates output in tabular format similar to the default
table printout from SQL*Plus. The default is field-per-row.
* FILE <name> forces Extract to write the transaction information to
the specified file. There is no output to the console.
To write a hex and plain character dump of the data, use FILE with
DETAIL. 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 the WARNLONGTRANS CHECKINTERVAL
parameter.
For additional information about SHOWTRANS, see the Oracle GoldenGate
Windows and UNIX Administrator抯 Guide.
SKIPTRANS <ID>
[THREAD <n>]
[FORCE]
(Oracle only) Forces Extract to skip the specified transaction, thereby
removing any current data from memory and ignoring any subsequent data.
* Get the ID number with SHOWTRANS or from an Extract runtime message.
* Use THREAD <n> to specify which thread generated the transaction in a
RAC environment if there are duplicate transaction IDs.
* Use FORCE to bypass the confirmation prompt.
Note: To use SKIPTRANS, the specified transaction must be the oldest
one in the list of transactions shown with SHOWTRANS. You can repeat
the command for other transactions in order of their age.
After using SKIPTRANS, wait at least five minutes if you intend to
issue SEND EXTRACT with FORCESTOP. Otherwise, the transaction will
still be present.
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
* 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), a message will be displayed.
TRACE[2] {<tracefile> | OFF}
Turns tracing on and off. Tracing captures information to the specified
file to reveal processing bottlenecks.
* TRACE captures step-by-step processing information.
* TRACE2 identifies code segments rather than specific steps.
* OFF turns off tracing.
If a trace is running already, the existing trace file is closed and
the trace is resumed to the new file specified with <tracefile>.
Contact Oracle Support for assistance if a trace reveals significant
processing bottlenecks. For more information, go to
http://support.oracle.com.
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
{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
{揂RSTATS?| 揑NCLUDELIST [filter]?| 揈XCLUDELIST [filter]攠
VAMMESSAGE 揙PENTRANS?
VAMMESSAGE sends a command to the capture API that is used by Extract.
* <Teradata command> can be:
揷ontrol:terminate?
Stops a replication group. Required before dropping or altering
a replication group in Teradata.
揷ontrol:suspend?
Suspends a replication group. Can be used when upgrading Oracle
GoldenGate.
揷ontrol:resume?
Resumes a replication group after it has been suspended.
揷ontrol:copy <database>.<table>?
Copies a table from the source database to the target database.
SQL/MX commands can be:
揂RSTATS?
Displays TMF audit reading statistics
揑NCLUDELIST [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. The [filter] option allows use of a
wildcard pattern to filter the list of tables returned.
揈XCLUDELIST [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. The [filter] 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.
The module returns a response to GGSCI. The response can be either
ERROR or OK along with a response message.
SQL Server command can be:
揙PENTRANS?
Prints a list of open transactions with their transaction ID,
start time, first LSN, and the number of operations they contain.
Example 1:
SEND EXTRACT finance, ROLLOVER
Example 2:
SEND EXTRACT finance, STOP
Example 3:
SEND EXTRACT finance, VAMMESSAGE 揷ontrol:suspend?
Example 4:
SEND EXTRACT finance, TRANLOGOPTIONS TRANSCLEANUPFREQUENCY 20
Example 5:
SEND EXTRACT finance, SKIPTRANS 5.17.27634 THREAD 2
Example 6:
SEND EXTRACT finance, SHOWTRANS COUNT 2
---------------------------------------------------------------------
START EXTRACT
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 also can be started from the operating system抯 command line
for certain synchronization configurations. For more information on the
proper configuration and startup method to use for your purposes, see
the Oracle GoldenGate Windows and UNIX Administrator抯 Guide.
Syntax:
START EXTRACT <group name>
<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.
Example:
START EXTRACT finance
---------------------------------------------------------------------
STATS EXTRACT
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.
1. Issue the STATS EXTRACT command with the RESET option.
2. Issue the STATS EXTRACT REPORTRATE command. The LATEST 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.
Syntax:
STATS EXTRACT <group name>
[, <statistic>]
[, TABLE <table>]
[, TOTALSONLY <table spec>]
[, 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.
Valid values:
TOTAL: Displays totals since process startup.
DAILY: Displays totals since the start of the current day.
HOURLY: Displays totals since the start of the current hour.
LATEST: Displays totals since the last RESET command.
RESET: Resets the counters in the LATEST statistical field.
TABLE <table>
Displays statistics only for the specified table or a group of tables
specified with a wildcard (*).
TOTALSONLY <table spec>
Summarizes the statistics for the specified table or a group of tables
specified with a wildcard (*).
REPORTFETCH | NOREPORTFETCH
Controls whether or not statistics about fetch operations are included
in the output. The default is NOREPORTFETCH.
REPORTRATE <time units>
Displays statistics in terms of processing rate rather than absolute
values.
Valid values:
HR
MIN
SEC
Example:
The following example displays total and hourly statistics per minute
for a specific table, and it also resets the latest statistics and
outputs fetch statistics.
STATS EXTRACT finance, TOTAL, HOURLY, TABLE acct, REPORTRATE MIN,
RESET, REPORTFETCH
---------------------------------------------------------------------
STATUS EXTRACT
STATUS EXTRACT
Use STATUS EXTRACT to determine whether or not Extract is running.
Syntax:
STATUS EXTRACT <group name> [, TASKS | ALLPROCESSES]
<group name>
The name of an Extract group or a wildcard (*) to specify multiple
groups. For example, T* returns status for all Extract groups whose
names begin with T.
TASKS
Displays status only for Extract tasks. By default, tasks are not
displayed unless you specify a single Extract group (without
wildcards).
ALLPROCESSES
Displays status for all Extract groups, including tasks.
Example 1:
STATUS EXTRACT finance
Example 2:
STATUS EXTRACT fin*
---------------------------------------------------------------------
STOP EXTRACT
STOP EXTRACT
Use STOP EXTRACT to stop Extract gracefully. The command preserves the
state of synchronization for the next time Extract starts, and it
ensures that Manager does not automatically start Extract.
If there are open, long-running transactions when you issue STOP
EXTRACT, you might be advised of the oldest transaction log file that
will be needed for that transaction when Extract is restarted. You can
use the SEND EXTRACT option of SHOWTRANS to view details and data of
those transactions and then, if desired, use the SKIPTRANS or
FORCETRANS options to skip the transaction or force it to be written as
a committed transaction to the trail.
Syntax:
STOP EXTRACT <group name>
<group name>
The name of an Extract group or a wildcard (*) to specify multiple
groups. For example, T* stops all Extract processes for groups whose
names begin with T.
Example:
STOP EXTRACT finance
--------------------------------------------------------------------
UNREGISTER EXTRACT
UNREGISTER EXTRACT
Use UNREGISTER EXTRACT to remove the registration of an Extract group
from an Oracle database.
UNREGISTER EXTRACT is valid for the following:
* Oracle Enterprise Edition version 10.2 and later.
* A primary Extract group only (the one that reads from the redo logs).
Do not use this command for a data pump Extract.
Before using UNREGISTER EXTRACT, do the following:
1. Stop Extract with the STOP EXTRACT <group> command, if necessary.
2. Issue the DBLOGIN command with special privileges to specify the
correct database.
To register a new Extract group with the database, use ADD EXTRACT. To
register an existing Extract group that was created without a DBLOGIN
command, use REGISTER EXTRACT.
Syntax:
UNREGISTER EXTRACT <group name> LOGRETENTION
<group name>
The name of the Extract group that is to be un-registered from the
database. Do not use a wildcard. This group must be one that was
previously registered with the REGISTER EXTRACT command.
LOGRETENTION
Provides a workaround in case DELETE EXTRACT is issued without being
preceded by a DBLOGIN command. It removes the underlying Oracle Streams
Capture that was created with the ADD EXTRACT or REGISTER EXTRACT
command. Use UNREGISTER EXTRACT with LOGRETENTION only if you no longer
want to capture changes with this Extract group.
The log-retention feature is controlled with the LOGRETENTION option of
the TRANLOGOPTIONS parameter.
Example:
UNREGISTER EXTRACT sales LOGRETENTION
---------------------------------------------------------------------
####################################
#
#REPLICAT COMMANDS
#
#
####################################
---------------------------------------------------------------------
ADD REPLICAT
ADD REPLICAT
Use ADD REPLICAT to create a Replicat group. Unless SPECIALRUN is
specified, ADD REPLICAT creates checkpoints so that processing
continuity is maintained from run to run. Before creating a Replicat
group, review the Oracle GoldenGate Windows and UNIX Administrator抯
Guide.
This command cannot exceed 500 bytes in size for all keywords and
input, including any text that you enter for the DESC option.
The Oracle GoldenGate GGSCI command interface fully supports up to 300
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. Beyond the supported level, group information is not
displayed and errors can occur. Oracle GoldenGate recommends keeping
the number of Extract and Replicat groups (combined) at 300 or below in
order to manage your environment effectively.
Syntax:
ADD REPLICAT <group name>
{
, SPECIALRUN |
, EXTFILE <full path name> |
, EXTTRAIL <full path name>
}
[, BEGIN {NOW | YYYY-MM-DD HH:MM[:SS[.CCCCCC]]} |
, EXTSEQNO <seqno>, EXTRBA <rba>]
[, CHECKPOINTTABLE <owner.table> | NODBCHECKPOINT]
[, PARAMS <parameter file>]
[, REPORT <report file>]
[, DESC ?description>擼
<group name>
The name of the Replicat group. Use the following naming conventions.
* You can use up to eight ASCII characters, including non-alphanumeric
characters such as the underscore (_). Any ASCII character can be
used, so long as the operating system allows that character to be in
a filename. This is because a group is identified by its associated
checkpoint file.
* The following ASCII characters are not allowed in a file name:
{ \ / : * ? " < > | }
* On HP UX, Linux, and Solaris, it is possible to create a file name
with a colon (:) or an asterisk (*), although it is not recommended.
* In general, group names are not case-sensitive within Oracle
GoldenGate. For example, finance, Finance, and FINANCE are all
considered to be the same. However, on Linux, the group name (and its
parameter file name if explicitly defined in the ADD command) must be
all uppercase or all lowercase. Mixed case group names and parameter
file names will result in errors when starting the process.
* Use only one word.
* Do not use the word 損ort?as a group name. However, you can use
the string 損ort?as part of the group name.
* Do not place a numeric value at the end of a group name, such as
fin1, fin10, and so forth. You can place a numeric value at the
beginning of a group name, such as 1_fin, 1fin, and so forth.
Examples of permissible group names:
ext_1
ex+2t
ex!2t
SPECIALRUN
Creates a Replicat special run as a task. Either SPECIALRUN, EXTFILE,
or EXTTRAIL is required. When Extract is in SPECIALRUN mode, do not
start it with the START REPLICAT command in GGSCI. For more information
on when to use special runs, see the Oracle GoldenGate Windows and UNIX
Administrator抯 Guide.
ADD REPLICAT data source options:
EXTFILE <full path name>
Specifies the fully qualified name of an extract file that is specified
with RMTFILE in the Extract parameter file.
EXTTRAIL <full path name>
Specifies the fully qualified name of a trail that was created with the
ADD RMTTRAIL or ADD EXTTRAIL command.
ADD REPLICAT service options:
BEGIN <start point>
Defines an initial checkpoint in the trail.
* To begin replicating changes from when the group is created with
ADD REPLICAT, use the NOW argument.
* To begin extracting changes from a specific time, use the date-
time format of YYYY-MM-DD HH:MM[:SS[.CCCCCC]].
EXTSEQNO <seqno>
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 c:\ggs\dirdat\aa000026,
you would specify EXTSEQNO 26.
By default, processing begins at the beginning of a trail unless this
option is used. To use EXTSEQNO, you must also use EXTRBA. Contact
Oracle Support before using this option. For more information, go to
http://support.oracle.com.
EXTRBA <rba>
Specifies the relative byte address within the trail file that is
specified by EXTSEQNO. Contact Oracle Support before using this option.
For more information, go to http://support.oracle.com.
CHECKPOINTTABLE <owner.table>
Specifies that this Replicat group will write checkpoints to the
specified table in the database. Include the owner and table name, as
in hr.hr_checkpoint. This argument overrides any default
CHECKPOINTTABLE specification in the GLOBALS file. The table must be
added with the ADD CHECKPOINTTABLE command.
NODBCHECKPOINT
Specifies that this Replicat group will not write checkpoints to a
checkpoint table. This argument overrides any default CHECKPOINTTABLE
specification in the GLOBALS file. This argument is required if you do
not want to use a checkpoint table with the Replicat group that is
being created.
PARAMS <parameter file>
Specifies a parameter file in a location other than the default of
dirprm within the Oracle GoldenGate directory. Use the fully qualified
name.
REPORT <report file>
Specifies a process report file in a location other than the default of
dirrpt within the Oracle GoldenGate directory. Use the fully qualified
name.
DESC ?description>?
Specifies a description of the group, such as 揕oads account_tab on
Serv2? The description must be within quotes. You can use either the
abbreviated keyword DESC or the full word DESCRIPTION.
Example:
ADD REPLICAT sales, EXTTRAIL d:\ggs\dirdat\rt
---------------------------------------------------------------------
ALTER REPLICAT
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 <group name> command.
Syntax:
ALTER REPLICAT <group name> , <option> [, ...]
<group name>
The name of the Replicat group that is to be altered.
<option>
An ADD REPLICAT option. You can change the description or any service
option that was configured with the ADD REPLICAT command, except for
the CHECKPOINT and NODBCHECKPOINT options.
Example 1:
ALTER REPLICAT finance, EXTSEQNO 53
Example 2:
ALTER REPLICAT finance, EXTRBA 0
Example 3:
ALTER REPLICAT finance, BEGIN 2011-01-07 08:00:00
---------------------------------------------------------------------
CLEANUP REPLICAT
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
<group name> command.
Syntax:
CLEANUP REPLICAT <group name> [, SAVE <count>]
<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.
SAVE <count>
Excludes the specified number of the most recent records from the
cleanup.
Example 1:
The following deletes all but the last record.
CLEANUP REPLICAT finance
Example 2:
The following deletes all but the most recent five records.
CLEANUP REPLICAT *, SAVE 5
---------------------------------------------------------------------
DELETE REPLICAT
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 by Manager, because the
checkpoints used by the deleted group are removed (assuming no other
processes are reading the file).
Before using DELETE REPLICAT, do the following:
1. Stop Replicat.
STOP REPLICAT <group name>
2. If this group uses a database checkpoint table, log into the
database by using the DBLOGIN command, so that the checkpoints can
be deleted from the table.
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 delete the group抯 checkpoints from the checkpoint
file on disk, but not from the checkpoint table in the database. This
option can be used to ignore the prompt that occurs when a wildcard
specifies multiple groups.
Example:
DELETE REPLICAT finance
---------------------------------------------------------------------
INFO REPLICAT
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 or ABENDED).
* Approximate replication lag.
* The trail from which Replicat is reading.
* Replicat run history, including checkpoints in the trail.
* Information about the Replicat environment.
The basic command, without the TASKS or ALLPROCESSES argument, displays
information only for online (continuous) Replicat groups. Tasks are
excluded.
About lag values:
Byte lag is the difference, in bytes, between the read position of
Replicat in the trail file at the time of the last checkpoint and the
current end-of-file indicator.
Time lag is the lag, in seconds, at the time 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 Replicat could be running but has
not yet processed records, or that the source system抯 clock is ahead
of the target system抯 clock (due to clock imperfections, not time zone
differences). For more precise lag information, use LAG REPLICAT.
To show detailed information, use the DETAIL option.
About showing checkpoints:
Replicat makes checkpoints in the trail file to mark its last read
position. To view process checkpoints, use the SHOWCH option. The basic
command shows current checkpoints. To view a specific number of
previous checkpoints, type the value after the SHOWCH entry.
About Replicat checkpoints:
Startup Checkpoint: The first checkpoint made in the trail when the
process started. Comprising this statistic are:
Sequence #: The sequence number of the trail file where the
checkpoint was written.
RBA: The relative byte address of the record at which the
checkpoint was made.
Timestamp: The timestamp of the record at which the checkpoint was
made.
Extract Trail: The relative path name of the trail.
Current Checkpoint: The position of the last record read by Replicat
in the trail. This should match the Log Read Checkpoint statistic
shown in the summary and in the basic INFO REPLICAT command without
options. The fields for this statistic are the same as those of the
Startup Checkpoint.
Other SHOWCH output: The Header, File Information, Configuration, and
Status statistics at the end of the SHOWCH display are for use by
Oracle Support analysts. They contain internal information that is
useful when resolving a support case.
Syntax:
INFO REPLICAT <group name>
[, DETAIL]
[, SHOWCH [<n>]]
[, TASKS | ALLPROCESSES]
<group name>
The name of a Replicat group or a wildcard (*) to specify multiple
groups. For example, T* shows all Replicat groups whose names begin
with T.
DETAIL
Displays detail information.
SHOWCH
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 <n> to include the specified number of previous
checkpoints as well as the current one.
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.
Example 1:
INFO REPLICAT *, DETAIL, ALLPROCESSES
Example 2:
INFO REPLICAT *, TASKS
Example 3:
INFO REPLICAT finance, SHOWCH
---------------------------------------------------------------------
KILL REPLICAT
KILL REPLICAT
Use KILL REPLICAT to kill a Replicat process. Killing 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. The Manager process will not attempt to restart a
killed Replicat process. Use this command only if Replicat cannot be
stopped gracefully with the STOP REPLICAT command.
Syntax:
KILL REPLICAT <group name>
<group name>
The name of a Replicat group or a wildcard (*) to specify multiple
groups. For example, T* kills all Replicat processes whose group names
begin with T.
Example:
KILL REPLICAT finance
---------------------------------------------------------------------
LAG REPLICAT
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.
About Replicat lag:
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.
Syntax:
LAG REPLICAT <group name>
<group name>
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.
Example 1:
LAG REPLICAT *
Example 2:
LAG REPLICAT *fin*
---------------------------------------------------------------------
SEND REPLICAT
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>,{
FORCESTOP |
GETLAG |
HANDLECOLLISIONS [<table spec>] |
NOHANDLECOLLISIONS [<table spec>] |
REPORT [HANDLECOLLISIONS [<table spec>]] |
STATUS |
STOP |
TRACE[2] [DDLINCLUDE | DDL[ONLY]] [FILE] <file name> |
TRACE[2] OFF |
TRACEINIT
}
<group name>
The name of the Replicat group. If Replicat is not running, an error is
returned.
FORCESTOP
Forces Replicat to stop, bypassing any notifications. This command will
roll back any active transaction and stop the process immediately.
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.
HANDLECOLLISIONS [<table spec>]
Turns on the HANDLECOLLISIONS parameter. Instead of using this option,
you can specify the HANDLECOLLISIONS parameter in the Replicat
parameter file. HANDLECOLLISIONS is used for automatic error handling
when performing initial data loads while the source database is active.
Make certain to disable HANDLECOLLISIONS (either with SEND REPLICAT 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.
<table spec> restricts HANDLECOLLISIONS to a specific target table or a
group of target tables specified with a standard wildcard (*).
NOHANDLECOLLISIONS [<table spec>]
Turns off the HANDLECOLLISIONS parameter but does not remove it from
the parameter file. To avoid enabling HANDLECOLLISIONS the next time
Replicat starts, remove it from the parameter file.
<table spec> restricts NOHANDLECOLLISIONS to a specific target table or
a group of target tables specified with a standard wildcard (*).
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 the RESETREPORTSTATS |
NORESETREPORTSTATS option.
HANDLECOLLISIONS shows tables for which HANDLECOLLISIONS has been
enabled. <table spec> restricts the output to a specific target table
or a group of target tables specified with a standard wildcard (*).
STATUS
Returns the current location within the trail and information regarding
the current transaction. Fields output are:
* Processing status
* Position in the trail file
* Trail sequence number
* RBA in trail
* Trail name
Possible processing status messages are:
* Delaying ?waiting for more data
* Waiting on deferred apply ?delaying processing based on the
DEFERAPPLYINTERVAL parameter.
* Processing data ?processing data
* Skipping current transaction ?START REPLICAT with SKIPTRANSACTION
was used.
* Searching for START ATCSN <csn> ?START REPLICAT with ATCSN was used.
* Searching for START AFTERCSN <csn> ?START REPLICAT with AFTERCSN was
used.
* Performing transaction timeout recovery ?Aborting current incomplete
transaction and repositioning to start new one (see the
TRANSACTIONTIMEOUT parameter).
* Waiting for data at logical EOF after transaction timeout recovery ?
Waiting to receive remainder of incomplete source transaction after a
TRANSACTIONTIMEOUT termination.
* At EOF (end of file) ?no more records to process
STOP
Stops Replicat gracefully.
TRACE[2] [DDLINCLUDE | DDL[ONLY]] [FILE] <tracefile>
Turns tracing on and off. Tracing captures information to the specified
file to reveal processing bottlenecks.
* TRACE captures step-by-step processing information.
* TRACE2 identifies code segments rather than specific steps.
If a trace is already in progress, the existing trace file is closed
and the trace resumes to the file specified with <tracefile>.
Contact Oracle Support for assistance if the trace reveals significant
processing bottlenecks. For more information, go to
http://support.oracle.com. Tracing also can be enabled by means of the
Replicat parameters TRACE and TRACE2.
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 to DDL.
[FILE] <file name>
The fully qualified name of a file to which Oracle GoldenGate logs the
trace information. The FILE keyword is optional, but must be used if
other parameter options will follow the file name, for example:
SEND REPLICAT <group> TRACE FILE <file name> DDLINCLUDE
If no other options will follow the file name, the FILE keyword can be
omitted, for example:
SEND REPLICAT <group> TRACE DDLINCLUDE <file name>
TRACE[2] OFF
Turns off tracing.
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.
Example 1:
SEND REPLICAT finance, HANDLECOLLISIONS
Example 2:
SEND REPLICAT finance, REPORT HANDLECOLLISIONS fin_*
Example 3:
SEND REPLICAT finance, GETLAG
---------------------------------------------------------------------
START REPLICAT
START REPLICAT
Use START REPLICAT to start Replicat. To confirm that Replicat has
started, use the INFO REPLICAT or STATUS REPLICAT command.
About Replicat start options:
Normal start point:
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 last 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 to begin processing at a transaction in the trail
other than the normal start point. Use them to:
* Specify a logical recovery position after an error that 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.
* Specify a start position at which to begin applying transactional
changes that were replicated during an initial load procedure.
Whenever a transaction changes data in a database, the database
engine assigns a change identifier that represents the state of
the data at that point in time. This type of identifier,
generically known as the commit sequence number (CSN) in Oracle
GoldenGate terminology, helps the database to keep track of
changing data states throughout different transactions. If you know
the CSN that corresponds to the completion of a backup, you can
start Replicat to apply replicated transactions from that point
forward. This allows Replicat to bypass any replicated changes that
represent states that are older than the ones included in the
backup. The purpose of skipping the older data changes is to avoid
duplicate-record and missing-record errors.
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 the SEND REPLICAT command with the STATUS option.
Starting Replicat from the command line:
Replicat also can be started from the operating system抯 command line
for certain synchronization configurations. For more information on the
proper configuration and startup method to use for your purposes, see
the Oracle GoldenGate Windows and UNIX Administrator抯 Guide.
Syntax:
START REPLICAT <group name>
[SKIPTRANSACTION | ATCSN <csn> | AFTERCSN <csn>]
<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 the DISCARDFILE
parameter is being used; otherwise, a message is written to the report
file that is similar to:
揢ser requested START SKIPTRANSACTION. The current transaction will
be skipped. Transaction ID <txid>, position Seqno <seqno>, RBA
<rba>?
Limitations:
* Valid only when the trail that Replicat is reading is part of an
online change synchronization configuration (with checkpoints). Not
valid for batch-type processes, such as initial loads or batch change
synchronization runs (when SPECIALRUN is used in the Replicat
parameter file or with ADD REPLICAT).
ATCSN <csn> | AFTERCSN <csn>
* ATCSN <csn> causes Replicat to skip transactions in the trail until
it finds a begin-transaction indicator that contains the specified
commit sequence number (CSN). This transaction and subsequent ones
are applied to the target. All transactions with a CSN less than the
specified one are skipped.
* AFTERCSN <csn> causes Replicat to skip transactions in the trail
until it finds the first transaction after the one that contains the
specified CSN. All transactions whose begin-transaction record
contains a CSN less than, or equal to, the specified one are skipped.
For CSN descriptions, see the Oracle GoldenGate Windows and UNIX
Reference Guide. The CSN must be in the format that is native to the
database; otherwise, Replicat will abend and write a message to the
report file.
To determine the appropriate CSN to use, view the Replicat report file
with the VIEW REPORT <group> command in GGSCI. If more thorough
investigation is required to determine the correct CSN, an experienced
Oracle GoldenGate user can use the Logdump utility. For more
information about using Logdump, see the Oracle GoldenGate Windows and
UNIX Troubleshooting and Tuning Guide.
When ATCSN or AFTERCSN is used, a message similar to the following is
written to the report file:
"User requested start at commit sequence number (CSN) <csn-string>
or ...User requested start after commit sequence number (CSN)
<csn-string>"
Limitations:
* Valid only when the trail that Replicat is reading is part of an
online change synchronization configuration (with checkpoints). Not
valid for batch-type processes, such as initial loads or batch change
synchronization runs (when SPECIALRUN is used in the Replicat
parameter file or with ADD REPLICAT).
* To support starting at, or after, a CSN, the trail must be of Oracle
GoldenGate version 10.0.0 or later, because the CSN is stored in the
file header. 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.
NOTE: When a record that is specified with a CSN is found, Replicat
issues a checkpoint to ensure that subsequent restarts of the process
that occur before the next checkpoint will start from the requested
location, and not from a point prior to the requested CSN.
For a list of CSN values by database type, see START REPLICAT in the
Oracle GoldenGate Windows and UNIX Reference Guide.
For more information about the CSN, see the Oracle GoldenGate Windows
and UNIX Administrator抯 Guide.
Example 1:
START REPLICAT finance
Example 2:
The following starts Replicat at an Oracle-specific CSN.
START REPLICAT finance, ATCSN 6488359
Example 3:
The following starts Replicat at a SQL Server-specific CSN.
START REPLICAT finance, AFTERCSN 0X000004D2:0000162E:0009
---------------------------------------------------------------------
STATS REPLICAT
STATS REPLICAT
Use STATS REPLICAT to display statistics for one or more Replicat
groups.
Syntax:
STATS REPLICAT <group name>
[, <statistic>]
[, TABLE <table>]
[, TOTALSONLY <table spec>]
[, REPORTDETAIL | NOREPORTDETAIL]
[, REPORTRATE <time units>]
[, ... ]
<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.
Valid values are:
TOTAL: Displays totals since process startup.
DAILY: Displays totals since the start of the current day.
HOURLY: Displays totals since the start of the current hour.
LATEST: Displays totals since the last RESET command.
RESET: Resets the counters in the LATEST statistical field.
TABLE <table>
Displays statistics only for the specified table or a group of tables
specified with a wildcard (*).
TOTALSONLY <table spec>
Summarizes the statistics for the specified table or a group of tables
specified with a wildcard (*).
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. See also the STATOPTIONS parameter.
REPORTRATE <time units>
Displays statistics in terms of processing rate rather than absolute
values.
Valid values:
* HR
* MIN
* SEC
Example:
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 acct, REPORTRATE MIN,
RESET, NOREPORTDETAIL
---------------------------------------------------------------------
STATUS REPLICAT
STATUS REPLICAT
Use STATUS REPLICAT to determine whether or not Replicat is running.
Syntax:
STATUS REPLICAT <group name>
[, TASKS]
[, ALLPROCESSES]
<group name>
The name of a Replicat group or a wildcard (*) to specify multiple
groups. For example, T* shows status for all Replicat groups whose
names begin with T.
TASKS
Displays status only for Replicat tasks. By default, tasks are not
displayed unless you specify a single Replicat group (without
wildcards).
ALLPROCESSES
Displays status for all Replicat groups, including tasks.
Example 1:
STATUS REPLICAT finance
Example 2:
STATUS REPLICAT fin*
---------------------------------------------------------------------
STOP REPLICAT
STOP REPLICAT
Use STOP REPLICAT to stop Replicat gracefully. This command preserves
the state of synchronization for the next time Replicat starts, and it
ensures that Manager does not automatically start Replicat.
Syntax:
STOP REPLICAT <group name> [!]
<group name>
The name of a Replicat group or a wildcard (*) to specify multiple
groups. For example, T* stops all Replicat groups whose names begin
with T.
!
(Exclamation point) Stops Replicat immediately. The transaction is
aborted and the process terminates.
Example:
STOP REPLICAT finance
---------------------------------------------------------------------
####################################
#
#ER COMMANDS
#
#
####################################
---------------------------------------------------------------------
INFO ER
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. For
descriptions and optional parameters for this command, refer to the
INFO EXTRACT or INFO REPLICAT command.
Syntax:
INFO ER <group wildcard specification>
<group wildcard specification>
The wildcard specification for the groups that you want to affect with
the command. Oracle GoldenGate will automatically increase internal
storage to track up to 100,000 wildcard entries.
Example:
INFO ER *X*
---------------------------------------------------------------------
KILL ER
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. For
descriptions and optional parameters for this command, refer to the
KILL EXTRACT or KILL REPLICAT command.
Syntax:
KILL ER <group wildcard specification>
<group wildcard specification>
The wildcard specification for the groups that you want to affect with
the command. Oracle GoldenGate will automatically increase internal
storage to track up to 100,000 wildcard entries.
Example:
KILL ER *X*
---------------------------------------------------------------------
LAG ER
LAG ER
Use the LAG ER command 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, refer to the
LAG EXTRACT or LAG REPLICAT command.
Syntax:
LAG ER <group wildcard specification>
<group wildcard specification>
The wildcard specification for the groups that you want to affect with
the command. Oracle GoldenGate will automatically increase internal
storage to track up to 100,000 wildcard entries.
Example:
LAG ER *X*
---------------------------------------------------------------------
SEND ER
SEND ER
Use the SEND ER command to send instructions to 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, refer to the
SEND EXTRACT or SEND REPLICAT command.
Syntax:
SEND ER <group wildcard specification>
<group wildcard specification>
The wildcard specification for the groups that you want to affect with
the command. Oracle GoldenGate will automatically increase internal
storage to track up to 100,000 wildcard entries.
Example:
SEND ER *X*
---------------------------------------------------------------------
START ER
START ER
Use the START ER command 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, refer to the
START EXTRACT or START REPLICAT command.
Syntax:
START ER <group wildcard specification>
<group wildcard specification>
The wildcard specification for the groups that you want to affect with
the command. Oracle GoldenGate will automatically increase internal
storage to track up to 100,000 wildcard entries.
Example:
START ER *X*
---------------------------------------------------------------------
STATS ER
STATS ER
Use the STATS ER command 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, refer to the
STATS EXTRACT or STATS REPLICAT command.
Syntax:
STATS ER <group wildcard specification>
<group wildcard specification>
The wildcard specification for the groups that you want to affect with
the command. Oracle GoldenGate will automatically increase internal
storage to track up to 100,000 wildcard entries.
Example:
STATS ER *X*
---------------------------------------------------------------------
STATUS ER
STATUS ER
Use the STATUS ER command to check the status of 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, refer to the
STATUS EXTRACT or STATUS REPLICAT command.
Syntax:
STATUS ER <group wildcard specification>
<group wildcard specification>
The wildcard specification for the groups that you want to affect with
the command. Oracle GoldenGate will automatically increase internal
storage to track up to 100,000 wildcard entries.
Example:
STATUS ER *X*
---------------------------------------------------------------------
STOP ER
STOP ER
Use the STOP ER command to stop 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, refer to the
STOP EXTRACT or STOP REPLICAT command.
Syntax:
STOP ER <group wildcard specification>
<group wildcard specification>
The wildcard specification for the groups that you want to affect with
the command. Oracle GoldenGate will automatically increase internal
storage to track up to 100,000 wildcard entries.
Example:
STOP ER *X*
---------------------------------------------------------------------
####################################
#
#TRAIL COMMANDS
#
#
####################################
---------------------------------------------------------------------
ADD EXTTRAIL
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.
Syntax:
ADD EXTTRAIL <trail name>, EXTRACT <group name>
[, MEGABYTES <n>]
[SEQNO <n>]
<trail name>
The fully qualified path name of the trail. The trail name can contain
only two characters. Oracle GoldenGate appends this name with a six-
digit sequence number whenever a new file is created. For example, a
trail named /ggs/dirdat/tr would have files named /ggs/dirdat/tr000001,
/ggs/dirdat/tr000002, and so forth.
<group name>
The name of the Extract group to which the trail is bound. Only one
Extract process can write data to a trail.
MEGABYTES <n>
The maximum size, in megabytes, of a file in the trail. The default is
10.
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 搕r,?specify SEQNO 3.
The actual file would be named /ggs/dirdat/tr000003. 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.
Example:
ADD EXTTRAIL c:\ggs\dirdat\aa, EXTRACT finance, MEGABYTES 20
---------------------------------------------------------------------
ADD RMTTRAIL
ADD RMTTRAIL
Use ADD RMTTRAIL to create a trail for online processing on a remote
system and:
* assign a maximum file size.
* associate the trail with an Extract group.
In the parameter file, specify a RMTHOST entry before any RMTTRAIL
entries to identify the remote system and TCP/IP port for the Manager
process.
Syntax:
ADD RMTTRAIL <trail name>, EXTRACT <group name>
[, MEGABYTES <n>]
[SEQNO <n>]
<trail name>
The fully qualified path name of the trail. The actual trail name can
contain only two characters. Oracle GoldenGate appends this name with a
six-digit sequence number whenever a new file is created. For example,
a trail named /ggs/dirdat/tr would have files named
/ggs/dirdat/tr000001, /ggs/dirdat/tr000002, and so forth.
<group name>
The name of the Extract group to which the trail is bound. Only one
Extract process can write data to a trail.
MEGABYTES <n>
The maximum size, in megabytes, of a file in the trail. The default is
10.
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 搕r,?specify SEQNO 3.
The actual file would be named /ggs/dirdat/tr000003. 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.
Example:
ADD RMTTRAIL c:\ggs\dirdat\aa, EXTRACT finance, MEGABYTES 20
---------------------------------------------------------------------
ALTER EXTTRAIL
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.
You can change the size of trail files with the MEGABYTES option. To
change the file size, follow this procedure.
1. Issue the following command to view the path name of the trail that
you want to alter and the name of the associated Extract group. Use
a wildcard to view all trails.
INFO EXTTRAIL *
2. Issue the following command to change the file size.
ALTER EXTTRAIL <trail name>, EXTRACT <group name>, MEGABYTES <n>
3. Issue the following command to cause Extract to switch to the next
file in the trail.
SEND EXTRACT <group name>, ROLLOVER
Syntax:
ALTER EXTTRAIL <trail name>, EXTRACT < group name> [, MEGABYTES <n>]
<trail name>
The fully qualified path name of the trail, for example
c:\ggs\dirdat\aa.
<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 10.
Example:
ALTER EXTTRAIL c:\ggs\dirdat\aa, EXTRACT finance, MEGABYTES 20
---------------------------------------------------------------------
ALTER RMTTRAIL
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.
You can change the size of trail files with the MEGABYTES option. To
change the file size, follow this procedure.
1. Issue the following command to view the path name of the trail that
you want to alter and the name of the associated Extract group. Use
a wildcard to view all trails.
INFO RMTTRAIL *
2. Issue the following command to change the file size.
ALTER RMTTRAIL <trail name>, EXTRACT <group name>, MEGABYTES <n>
3. Issue the following command to cause Extract to switch to the next
file in the trail.
SEND EXTRACT <group name>, ROLLOVER
Syntax:
ALTER RMTTRAIL <trail name>, EXTRACT <group name> [, MEGABYTES <n>]
<trail name>
The fully qualified path name of the trail, for example
c:\ggs\dirdat\aa.
<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 10.
Example:
ALTER RMTTRAIL c:\ggs\dirdat\aa, EXTRACT finance, MEGABYTES 20
---------------------------------------------------------------------
DELETE EXTTRAIL
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 themselves. To
delete the trail files, use standard operating system commands for
removing files.
Syntax:
DELETE EXTTRAIL <trail name>
<trail name>
The fully qualified path name of the trail, including the two-character
trail prefix.
Example:
DELETE EXTTRAIL /home/ggs/dirdat/et
---------------------------------------------------------------------
DELETE RMTTRAIL
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 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 themselves. To
delete the trail files, use standard operating system commands for
removing files.
Syntax:
DELETE RMTTRAIL <trail name>
<trail name>
The fully qualified path name of the trail, including the two-character
trail prefix.
Example:
DELETE RMTTRAIL /home/ggs/dirdat/et
---------------------------------------------------------------------
INFO EXTTRAIL
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>
<trail name>
The fully qualified path name of the trail or a wildcard designating
multiple trails.
Example 1:
INFO EXTTRAIL c:\ggs\dirdat\aa
Example 2:
INFO EXTTRAIL *
---------------------------------------------------------------------
INFO RMTTRAIL
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>
<trail name>
The fully qualified path name of the trail or a wildcard designating
multiple trails.
Example 1:
INFO RMTTRAIL c:\ggs\dirdat\aa
Example 2:
INFO RMTTRAIL *
---------------------------------------------------------------------
####################################
#
#PARAMETER COMMANDS
#
#
####################################
---------------------------------------------------------------------
EDIT PARAMS
EDIT PARAMS
Use EDIT PARAMS to create or change a parameter file. By default, this
command launches Notepad on Windows systems or the vi editor on UNIX
systems. You can change the editor with the SET EDITOR command.
Syntax:
EDIT PARAMS {MGR | <group> | <file name>}
MGR
Opens a parameter file for the Manager process.
<group>
Opens a parameter file for the specified Extract or Replicat group.
<file name>
Opens the specified file. Use the full path name.
Example 1:
EDIT PARAMS finance
Example 2:
EDIT PARAMS c:\lpparms\replp.prm
---------------------------------------------------------------------
SET EDITOR
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.
Syntax:
SET EDITOR <program name>
<program name>
Any editor that can save text in ASCII format.
Example:
The following example changes the default editor to Wordpad.
SET EDITOR wordpad
---------------------------------------------------------------------
VIEW PARAMS
VIEW PARAMS
Use VIEW PARAMS to view the contents of a parameter file.
Syntax:
VIEW PARAMS {MGR | <group> | <file name>}
MGR
Shows the Manager parameter file.
<group>
Shows the parameter file for the specified Extract or Replicat group.
<file name>
Shows the specified file. Use the full path name.
Example 1:
VIEW PARAMS finance
Example 2:
VIEW PARAMS c:\lpparms\replp.prm
---------------------------------------------------------------------
####################################
#
#DATABASE COMMANDS
#
#
####################################
---------------------------------------------------------------------
DBLOGIN
DBLOGIN
Use DBLOGIN to establish a database connection through GGSCI in
preparation to issue other Oracle GoldenGate commands that affect the
database.
(Oracle Enterprise Edition, version 10.2 and later) DBLOGIN must be
issued with special privileges before using ADD EXTRACT and DELETE
EXTRACT, as well as before using REGISTER EXTRACT and UNREGISTER
EXTRACT. For simplicity, you can log in as the Extract database user if
the correct privileges were granted to that user when Oracle GoldenGate
was installed. Otherwise, log in as a user with the following
privileges:
Oracle 10.2:
exec dbms_streams_auth.grant_admin_privilege('<user>')
grant insert on system.logmnr_restart_ckpt$ to <user>;
grant update on sys.streams$_capture_process to <user>;
grant become user to <user>;
Oracle 11.1 and 11.2.0.1:
exec dbms_streams_auth.grant_admin_privilege('<user>')
grant become user to <user>;
Oracle 11.2.0.2 and later:
exec dbms_goldengate_auth.grant_admin_privilege('<user>')
Syntax:
DBLOGIN {
[SOURCEDB <dsn>] |
[, <database>@<host>:<port>]
[,] [USERID <user>[, PASSWORD <password>] [SYSDBA | SQLID <sqlid>]
}
SOURCEDB <dsn>
A datasource name. Required for all databases that use ODBC.
<database>@<host>:<port>
(MySQL) Specifies a connection string that contains the database name,
host name, and database port number. Can be used to specify a port
other than the default that is specified in the database configuration.
USERID <user>[, PASSWORD <password>]
A database user and that user's password. Use if database
credentials are required. PASSWORD is optional. When used, the
password is echoed. If PASSWORD is omitted, Oracle GoldenGate
prompts for a password, and the password is not echoed. If the
password is case-sensitive, type it that way.
SYSDBA
(Oracle) Specifies that the user logs in as sysdba.
SQLID <sqlid>
(DB2 on z/OS) Issues the SQL command SET CURRENT SQLID = 'sqlid'
after the USERID login (with PASSWORD, if applicable) is completed.
If the SET command fails, the entire DBLOGIN command fails as a
unit.
Example 1:
DBLOGIN USERID ggs, PASSWORD ggs123
Example 2:
DBLOGIN SOURCEDB msqldb@host1:3305, USERID ggs, PASSWORD ggs SYSDBA
---------------------------------------------------------------------
ENCRYPT PASSWORD
ENCRYPT PASSWORD
Use ENCRYPT PASSWORD to encrypt a database login password that is
specified with the USERID and ASMUSERID parameters. Without options,
ENCRYPT PASSWORD generates a random encryption key, but you can specify
a key from a lookup file by using the ENCRYPTKEY option.
ENCRYPT PASSWORD prints the encrypted password to the screen. Copy and
paste it into the PASSWORD argument of the USERID and ASMUSERID
parameters.
Password encryption is not supported for SQL/MX databases.
Syntax:
ENCRYPT PASSWORD <password> [ENCRYPTKEY <keyname>]
<password>
The login password. Do not enclose the password within quotes. If the
password is case-sensitive, type it that way.
ENCRYPTKEY <keyname>
Optional, specifies the logical name of an encryption key contained in
the ENCKEYS lookup file. Oracle GoldenGate uses the key name to look
up the actual key in the file.
To use the <keyname> option, generate the key and store it in an
ENCKEYS lookup file on the local system. For more information, see the
Oracle GoldenGate Windows and UNIX Administrator抯 Guide.
Example:
ENCRYPT PASSWORD ny14072 ENCRYPTKEY superkey2
--------------------------------------------------------------------
FLUSH SEQUENCE
FLUSH SEQUENCE
Use FLUSH SEQUENCE immediately after you start Extract for the first
time during an initial synchronization or a re-synchronization. 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.
The following database procedures are used by this command and normally
are installed by running the sequence.sql script when Oracle GoldenGate
is installed.
Database Procedure User and Privileges
Source updateSequence Grant EXECUTE to the owner of
the Oracle GoldenGate DDL
objects, or
other selected user if not
using DDL support.
Target replicateSequence Grant EXECUTE to the Oracle
GoldenGate Replicat user
Before using FLUSH SEQUENCE, issue the DBLOGIN command as the database
user that has EXECUTE privilege on the updateSequence procedure.
To use this command, the GGSCHEMA parameter must be used in the GLOBALS
file to specify the schema in which the sequence.sql procedure is
installed. This can be the same schema that is used for the Oracle
GoldenGate DDL support and any other database objects that are owned by
Oracle GoldenGate.
Syntax:
FLUSH SEQUENCE <owner.sequence>
<owner.sequence>
The owner and name of an Oracle sequence. The schema name cannot be
null. You can use an asterisk (*) wildcard for the sequence name but
not for the owner name.
Example:
FLUSH SEQUENCE scott.seq*
---------------------------------------------------------------------
LIST TABLES
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.
Syntax:
LIST TABLES <table>
<table>
The name of a table or a group of tables specified with a wildcard (*).
Example:
The following shows a LIST TABLES command and sample output.
GGSCI (sysa) 3> list tables tcust*
TCUSTMER
TCUSTORD
---------------------------------------------------------------------
####################################
#
#TRANDATA COMMANDS
#
#
####################################
--------------------------------------------------------------------
ADD SCHEMATRANDATA
ADD SCHEMATRANDATA
Use ADD SCHEMATRANDATA to enable schema-level supplemental logging for
Oracle tables. 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.
ADD SCHEMATRANDATA 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.
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.)
When to use ADD SCHEMATRANDATA:
ADD SCHEMATRANDATA should be used 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.
Additional requirements for using ADD SCHEMATRANDATA:
Minimal supplemental logging must be enabled at the database level in
order for Oracle GoldenGate to process updates to primary keys and
chained rows. This must be done through the database interface, not
through Oracle GoldenGate.
Before using ADD SCHEMATRANDATA, issue the DBLOGIN command. The user
who issues the command must be granted the Oracle Streams administrator
privilege.
SQL> exec dbms_streams_auth.grant_admin_privilege('<user>')
Additional considerations for using ADD SCHEMATRANDATA:
ADD SCHEMATRANDATA can be used instead of the ADD 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 does ADD
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 to ADD TRANDATA. For more information, see 揂DD TRANDATA?
If you must log other, non-key columns of a specific table (or tables)
for use by Oracle GoldenGate, such as those needed for FILTER
statements and KEYCOLS clauses in the TABLE and MAP parameters, issue
an ADD TRANDATA command for those columns. That command has a COLS
option to issue table-level supplemental logging for the columns, and
it can be used in conjunction with ADD SCHEMATRANDATA.
Syntax:
ADD SCHEMATRANDATA <schema>
<schema> is the schema for which you want the supplementary key
information to be logged. Do not use a wildcard.
Example:
ADD SCHEMATRANDATA fin
---------------------------------------------------------------------
ADD TRANDATA
ADD TRANDATA
Use ADD TRANDATA to enable Oracle GoldenGate to acquire the transaction
information that it needs from the transaction records. Use the DBLOGIN
command to establish a database connection before using this command.
ADD TRANDATA is required only for the databases that are listed here.
For other supported databases, this functionality may exist already or
must be configured through the database interface. See the Oracle
GoldenGate installation guide for your database for any special
requirements that apply to making transaction information available.
c-tree databases
You can use ADD TRANDATA to turn on the REPLICATE attribute of a c-tree
file without bouncing the c-tree server. As an alternative, you can
turn on REPLICATE by setting one or more REPLICATE parameters in the
c-tree server configuration file ctsrvr.cfg before starting the
server.
DB2 databases
Use ADD TRANDATA to enable DATA CAPTURE CHANGES on specified tables.
This command supports DB2 LUW and DB2 z/OS. By default, ADD TRANDATA
issues one of the following commands to the database:
DB2 z/OS:
ALTER TABLE <name> DATA CAPTURE CHANGES;
DB2 LUW:
ALTER TABLE <name> DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS;
For DB2 LUW, you can exclude the LONGVAR clause by using ADD TRANDATA
with the EXCLUDELONG option.
SQL Server databases
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.
Sybase databases
ADD TRANDATA marks a Sybase table for replication by executing the
Sybase sp_setreptable and sp_setrepcol system procedures. ADD TRANDATA
options employ database features to control how the database propagates
LOB data for the specified table.
NOTE: The ADD TRANDATA command will overwrite the LOB setting that is
currently set for the table.
Oracle databases
For Oracle, ADD TRANDATA by default enables table-level
supplemental logging. The supplemental log group includes one of the
following sets of columns, in the listed order of priority, depending
on what is defined on the table:
1. Primary key
2. First unique key alphanumerically with no virtual columns, no
UDTs, no function-based columns, and no nullable columns
3. First unique key alphanumerically with no virtual columns, no
UDTs, or no function-based columns, but can include nullable
columns
4. If none of the preceding key types exist (even though there might
be other types of keys defined on the table) Oracle GoldenGate
constructs a pseudo key of all columns that the database allows to
be used in a unique key, excluding virtual columns, UDTs,
function-based columns, and any columns that are explicitly
excluded from the Oracle GoldenGate configuration.
The command issues an ALTER TABLE command with an ADD SUPPLEMENTAL
LOG DATA clause that is appropriate for the type of unique constraint
(or lack of one) that is defined for the table.
When to use ADD TRANDATA for an Oracle source database:
Use ADD TRANDATA only if you are not using the Oracle GoldenGate DDL
replication feature. If you are using the Oracle GoldenGate DDL
replication feature, use the ADD SCHEMATRANDATA command to log the
required supplemental data. It is possible to use ADD TRANDATA when DDL
support is enabled, but only if you can guarantee one of the following:
* You can stop DML activity on any and all tables before users or
applications perform DDL on them.
* You cannot stop DML activity before the DDL occurs, but you can
guarantee that:
- There is no possibility that users or applications will issue DDL
that adds new tables whose names satisfy an explicit or wildcarded
specification in a TABLE or MAP statement.
- There is 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.
ADD SCHEMATRANDATA is safer to use when DDL replication is enabled,
because it ensures replication continuity should DML ever occur on an
object for which DDL has just been performed.
You can use ADD TRANDATA even when using ADD SCHEMATRANDATA if you need
to use 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.
Additional requirements when using ADD TRANDATA:
Besides table-level logging, minimal supplemental logging must be
enabled at the database level in order for Oracle GoldenGate to
process updates to primary keys and chained rows. This must be done
through the database interface, not through Oracle GoldenGate.
Additional information for ADD TRANDATA for Oracle:
The following are additional options for Oracle supplemental logging:
* Use the COLS option to log non-key columns as needed, such as those
required for a KEYCOLS clause or for filtering and manipulation
requirements. A KEYCOLS clause is checked when processing starts and
prevents ADD TRANDATA from logging all of the columns of the table
when it determines there is no primary or unique key.
* Use the NOKEY option to prevent the logging of key columns when
needed.
Take the following into account when using ADD TRANDATA for an Oracle
database:
* If any of the logging details change after Oracle GoldenGate has
started extracting data, you will need to stop and 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 table name, an underscore, and object ID to
a prefix of GGS_.
Because Oracle limits an object name to 30 characters, Oracle
GoldenGate truncates long table names as needed so the prefix and
object ID can be included.
In Oracle GoldenGate versions prior to 8.0.2, the object ID is not
used in the log group names. If upgrading from one of
those versions, do either of the following so that the correct name
is found when the DELETE TRANDATA or INFO TRANDATA commands
are used:
* The preferred method is to convert existing log groups to the new
name format. First use DELETE TRANDATA, and then use ADD TRANDATA.
* If current names must be retained, use the OLDFORMAT option when
using INFO TRANDATA and DELETE TRANDATA.
Syntax:
ADD TRANDATA <owner.table>
[, COLS (<column list>)]
[, INCLUDELONG | EXCLUDELONG]
[, LOBSNEVER | LOBSALWAYS | LOBSIFCHANGED | LOBSALWAYSNOINDEX]
[, NOKEY]
[, OLDFORMAT]
<owner.table>
The owner and name of the table or file for which to log transaction
data. A wildcard can be used for the table name but not the owner name.
COLS (<column list>)
Adds specific non-key column(s) to the
supplemental logging. Can be used to log columns specified in a KEYCOLS
clause and to log columns that will be needed for filtering or
manipulation purposes, which might be more efficient than fetching
those values with a FETCHCOLS clause in a TABLE statement. Separate
multiple columns with commas, for example NAME, ID, DOB.
INCLUDELONG | EXCLUDELONG
(DB2 LUW) Controls whether or not the ALTER TABLE issued by ADD
TRANDATA includes the "INCLUDE LONGVAR COLUMNS" attribute. INCLUDELONG
is the default. When ADD 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 include
LONGVAR columns. Examples of this functionality are the
GETUPDATEBEFORES, NOCOMPRESSUPDATES, and NOCOMPRESSDELETES parameters.
To support this functionality, changes to LONGVAR columns in the
transaction logs must include both the before and after images of the
column value.
LOBSNEVER | LOBSALWAYS | LOBSIFCHANGED | LOBSALWAYSNOINDEX
(Sybase) Controls how the database propagates LOB data for the
specified table.
Note: The ADD TRANDATA command will overwrite the LOB setting that is
currently set for the table. To change the setting afterwards, you must
use the sp_setrepcol script.
* LOBSNEVER prevents LOB data from being propagated. Note this
exception: If the LOB column is inserted with a NULL value, or if it
is skipped in an INSERT operation, then Extract will write that
column to the trail with NULL data.
* LOBSALWAYS does two things: it uses sp_setrepcol to set LOB
replication to ALWAYS_REPLICATE (always replicate LOB data whether or
not it has changed in a transaction), and it marks the table to use
an index on replication (by means of the USE_INDEX option of
sp_setreptable). Because a LOB is marked for replication in a single
transaction, this can take a long time, and USE_INDEX
reduces that time by creating a global nonclustered index for every
LOB. A shared-table lock is held while the global nonclustered index
is created.
* LOBSIFCHANGED replicates LOB data only if it was changed during a
transaction. This reduces replication overhead but does not protect
against inconsistencies that could occur on the target outside the
replication environment. This is the default.
* LOBSALWAYSNOINDEX sets LOB replication to ALWAYS_REPLICATE (always
replicate LOB data whether or not it has changed in a transaction).
This adds overhead, but protects against inconsistencies that could
occur on the target outside the replication environment.
LOBSALWAYSNOINDEX does not mark the table to use an index on
replication. The benefit is that no lock is held while ADD TRANDATA
is being executed. LOBSALWAYSNOINDEX is the default for Sybase
databases earlier than version 15.
Note: When using the ALWAYS_REPLICATE option, if a LOB column contains
a NULL value, and then another column in the table gets updated (but
not the LOB), that LOB will not be captured even though
ALWAYS_REPLICATE is enabled.
You can check the LOB settings of a table with the INFO TRANDATA
command, after ADD TRANDATA has been used for that table. It shows the
LOB settings for all of the LOB columns. You can use the Sybase system
procedures to change the LOB settings for any given column as needed.
NOKEY
(Oracle 9.x and later) Suppresses the supplemental logging of primary
key columns. If using NOKEY, use the COLS option to log alternate
columns that can serve as keys, and designate those columns as
substitute keys by using the KEYCOLS option of the TABLE or MAP
parameter.
OLDFORMAT
(Oracle) Uses the naming format for log groups that
existed prior to Oracle GoldenGate version 8.0.2. This format appends
GGS_ to log group names that were created
with ADD TRANDATA, but does not include an object ID.
If the name exceeds Oracle抯 30-character maximum, Oracle GoldenGate
truncates the table name. If more than one name gets truncated to the
same characters (when tables have similar names), there will be
duplicate log-groups, with no object ID to differentiate
them. This can cause INFO TRANDATA and DELETE TRANDATA to affect the
wrong object. Do not use this option without guidance from an Oracle
Support analyst. For more information, go to
http://support.oracle.com.
Example 1:
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 table;
* a Sybase table to be marked for replication;
* the enabling of the REPLICATE attribute of a c-tree file.
ADD TRANDATA finance.acct
Example 2:
The following Oracle example causes the primary key to be logged plus
the non-key columns name and address.
ADD TRANDATA finance.acct, COLS (name, address)
Example 3:
The following Oracle example prevents the primary key from being
logged, but logs the non-key columns name and pid instead.
ADD TRANDATA finance.acct, NOKEY, COLS (name, pid)
Example 4:
The following Sybase example marks the acct table for replication and
specifies to log LOB data only if it was changed during a transaction
ADD TRANDATA finance.acct, LOBSIFCHANGED
---------------------------------------------------------------------
DELETE SCHEMATRANDATA
DELETE SCHEMATRANDATA
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.
Syntax:
DELETE SCHEMATRANDATA <schema>
<schema> is the schema for which you want supplemental logging to be
removed. Do not use a wildcard.
Example:
DELETE SCHEMATRANDATA scott
---------------------------------------------------------------------
DELETE TRANDATA
DELETE TRANDATA
Use DELETE TRANDATA to do one of the following:
* c-tree: Disable replication for the specified file.
* DB2 LUW and DB2 on z/OS: Alters the table to DATA CAPTURE NONE.
* Oracle: Disable supplemental logging.
* Sybase: Disable replication.
* SQL Server: Stop extended logging.
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 <owner.table>
[, OLDFORMAT]
<owner.table>
The owner and name of the table or file. A wildcard can be used for the
table name but not the owner name.
OLDFORMAT
(Oracle) Forces the command to search by table name only, and exclude
the object ID. Use this option if the names of log groups are in a
format supported by Oracle GoldenGate versions prior
to 8.0.2, or if ADD TRANDATA was issued with the OLDFORMAT option.
Example 1:
DELETE TRANDATA finance.acct
Example 2:
DELETE TRANDATA finance.ac*
---------------------------------------------------------------------
INFO SCHEMATRANDATA
INFO SCHEMATRANDATA
Use INFO SCHEMATRANDATA to determine whether Oracle schema-level
supplemental logging is enabled for the specified schema. Use the
DBLOGIN command to establish a database connection before using this
command.
Syntax:
INFO SCHEMATRANDATA <schema>
<schema> is the schema for which you want to confirm supplemental
logging. Do not use a wildcard.
Example:
INFO SCHEMATRANDATA scott
---------------------------------------------------------------------
INFO TRANDATA
INFO TRANDATA
Use INFO TRANDATA to get the following information:
* c-tree: Determine whether or not replication is enabled.
* DB2 LUW and DB2 on z/OS: Determine whether DATA CAPTURE is enabled or
not.
* Oracle: Determine whether Oracle supplemental logging is enabled.
* Sybase: Determine whether replication is enabled or not, and whether
all LOB columns have identical logging settings (as specified with
the ADD TRANDATA LOB options.
* SQL Server: Determine whether or not extended logging is enabled.
Use the DBLOGIN command to establish a database connection before using
this command.
Syntax:
INFO TRANDATA <owner.table>
[, OLDFORMAT]
<owner.table>
The 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 user specified by the DBLOGIN command. A wildcard can be used for
the table name but not the owner name.
OLDFORMAT
(Oracle) Forces the command to search by table name only, and exclude
the object ID. Use this option if the names of log groups are in a
format supported by Oracle GoldenGate versions prior
to 8.0.2, or if ADD TRANDATA was issued with the OLDFORMAT option.
Example 1:
INFO TRANDATA finance.acct
Example 2:
INFO TRANDATA finance.ac*
---------------------------------------------------------------------
####################################
#
#CHECKPOINT TABLE COMMANDS
#
#
####################################
---------------------------------------------------------------------
ADD CHECKPOINTTABLE
ADD CHECKPOINTTABLE
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.
A checkpoint table is optional; checkpoints are also maintained in a
file on disk. The use of a checkpoint table causes checkpointing to be
part of the Replicat transaction. This system allows Replicat to
recover better in certain circumstances than when checkpoints alone are
used.
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. For more
information, see the Oracle GoldenGate Windows and UNIX Administrator抯
Guide.
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.
Syntax:
ADD CHECKPOINTTABLE [<owner.table>]
<owner.table>
The owner and name of the checkpoint table to be created. 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 the GLOBALS
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 the GLOBALS parameter file.
Record the name of the table, because you will need it to view
statistics or delete the table if needed.
Example 1:
The following adds a checkpoint table with the default name specified
in the GLOBALS file.
ADD CHECKPOINTTABLE
Example 2:
The following adds a checkpoint table with a user-defined name.
ADD CHECKPOINTTABLE ggs.fin_check
---------------------------------------------------------------------
CLEANUP CHECKPOINTTABLE
CLEANUP CHECKPOINTTABLE
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).
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 [<owner.table>]
<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 the GLOBALS parameter
file.
Example:
CLEANUP CHECKPOINTTABLE ggs.fin_check
---------------------------------------------------------------------
DELETE CHECKPOINTTABLE
DELETE CHECKPOINTTABLE
Use DELETE CHECKPOINTTABLE to drop a checkpoint table from the
database. Use the DBLOGIN command to establish a database connection
before using this command.
To stop using a checkpoint table while the associated Replicat group
remains active, follow these steps:
1. Run GGSCI.
2. Stop Replicat.
STOP REPLICAT <group>
3. Delete the Replicat group and then add it back with the following
commands.
DELETE REPLICAT <group>
ADD REPLICAT <group>, EXTTRAIL <trail>, NODBCHECKPOINT
4. Exit GGSCI, then start it again.
5. Start Replicat again.
START REPLICAT <group>
6. Log into the database with the DBLOGIN command, using the
appropriate authentication options.
DBLOGIN
{SOURCEDB <dsn> |
USERID <user>[, PASSWORD <password>] [SYSDBA] |
SOURCEDB <dsn>, USERID <user>[, PASSWORD <password>][SQLID <sqlid>]
7. Delete the checkpoint table with DELETE CHECKPOINTTABLE.
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, if you still want to delete the checkpoint
table, follow the recommended steps.
Syntax:
DELETE CHECKPOINTTABLE [<owner.table>] [!]
<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 the GLOBALS file.
!
Bypasses the prompt that confirms intent to delete the table.
Example:
DELETE CHECKPOINTTABLE ggs.fin_check
---------------------------------------------------------------------
INFO CHECKPOINTTABLE
INFO CHECKPOINTTABLE
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:
揅heckpoint table HR.CHKPT_TBLE created 2011-01-06 11:51:53.?
Use the DBLOGIN command to establish a database connection before using
this command.
Syntax:
INFO CHECKPOINTTABLE [<owner.table>]
<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 the GLOBALS file.
Example:
INFO CHECKPOINTTABLE ggs.fin_check
---------------------------------------------------------------------
####################################
#
#ORACLE TRACE TABLE COMMANDS
#
#
####################################
---------------------------------------------------------------------
ADD TRACETABLE
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 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.
Syntax:
ADD TRACETABLE [<owner>.<table>]
<owner>.<table>
Optional, required 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 the USERID 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 the TRACETABLE 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. For more
information, see the TRACETABLE | NOTRACETABLE parameter.
Example 1:
The following adds a trace table with the default name of GGS_TRACE.
ADD TRACETABLE
Example 2:
The following adds a trace table with a user-defined name of ora_trace.
ADD TRACETABLE ora_trace
---------------------------------------------------------------------
DELETE TRACETABLE
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 [<owner.table>]
<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 parameter and the trace table has the default name of GGS_TRACE.
Example:
DELETE TRACETABLE ora_trace
---------------------------------------------------------------------
INFO TRACETABLE
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 [<owner.table>]
<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 parameter and the trace table has the default name of GGS_TRACE.
Example:
INFO TRACETABLE ora_trace
---------------------------------------------------------------------
####################################
#
#DDL COMMANDS
#
#
####################################
---------------------------------------------------------------------
DUMPDDL
DUMPDDL
Use the DUMPDDL command to view the data in the Oracle GoldenGate DDL
history table. This information is the same information that is used by
the Extract process. It is stored in proprietary format, but can be
exported in human-readable form to the screen or to a series of SQL
tables that can be queried by using regular SQL.
Because the information is historical data that is provided by the DDL
before trigger, it reflects the state of an object before a DDL change.
Consequently, there will not be any data for CREATE operations.
NOTE: The default name of the before trigger is GGS_DDL_TRIGGER_BEFORE.
Before using DUMPDDL, log into the database as the owner of the history
table by using the DBLOGIN command.
Basic DUMPDDL command:
The basic DUMPDDL command outputs metadata to the Oracle GoldenGate DDL
tables. For more information about these tables, see the Oracle
GoldenGate Windows and UNIX Reference Guide.
DUMPDDL command with SHOW:
DUMPDDL with the SHOW option dumps the information contained in the
history table to the screen in standard output format. No output tables
are produced. All records in the DDL history table are shown.
Additional DUMPDDL guidelines:
DUMPDDL always dumps all of the records in the DDL history table. Use
SQL queries or search redirected standard output to view information
about particular objects and the operations you are interested in.
Because the history table contains large amounts of data, only the
first 4000 bytes (approximately) of a DDL statement are displayed in
order to maintain efficient performance.
The format of the metadata is string based. It is fully escaped and
supports non-standard characters (such as =, ?, *) in table or column
names. The format also supports multi-byte systems (using UTF-8 as the
character set and multi-byte encoding, for example Chinese or German).
Syntax:
DUMPDDL [SHOW]
SHOW
Dumps the DDL information to the screen in standard output format.
---------------------------------------------------------------------
####################################
#
#MISCELLANEOUS COMMANDS
#
#
####################################
---------------------------------------------------------------------
!
!
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 of GGSCI,
because command history is not maintained from session to session.
Syntax:
! [<n> | -<n> | <string>]
<n>
Executes the command from the specified GGSCI line. Each GGSCI command
line is sequenced, beginning with 1 at the start of the session.
-<n>
Executes the command issued <n> lines before the current line.
<string>
Executes the last command that starts with the specified text string.
Example 1:
! 9
Example 2:
! -3
Example 3:
! sta
---------------------------------------------------------------------
ALLOWNESTED
ALLOWNESTED
Use the ALLOWNESTED command to enable the use of nested OBEY files. A
nested OBEY file is one that contains another OBEY file. The maximum
number of nested levels is 16. An attempt to run a nested OBEY file in
the default mode of NOALLOWNESTED will cause an error that is similar
to the following:
揈RROR: Nested OBEY scripts not allowed. Use ALLOWNESTED to allow
nested scripts.?
When you exit your GGSCI session, the next GGSCI session will revert
back to NOALLOWNESTED.
Syntax:
ALLOWNESTED | NOALLOWNESTED
---------------------------------------------------------------------
CREATE SUBDIRS
CREATE SUBDIRS
Use CREATE SUBDIRS when installing Oracle GoldenGate. This command
creates the default directories within the Oracle GoldenGate home
directory. Use CREATE SUBDIRS before any other configuration tasks.
Syntax:
CREATE SUBDIRS
---------------------------------------------------------------------
FC
FC
Use FC to display edit a previously issued GGSCI command and then
execute it again. Previous commands are stored in the memory buffer and
can be displayed by issuing the HISTORY command.
Displaying previous commands:
Issuing FC without arguments displays 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 edited only if
they were issued during the current GGSCI session, because history is
not maintained from one session to another.
Editing commands:
The FC command displays the specified command and then opens an editor
with a prompt containing a blank line starting with two dots. To edit a
command, use the space bar to position the cursor beneath the character
in the displayed command where you want to begin editing, and then use
one of the following arguments. Arguments are not case-sensitive and
can be combined.
FC editor commands:
i <text>
Inserts text. For example:
GGSCI (SysA) 24> fc 9
GGSCI (SysA) 24> send mgr
GGSCI (SysA) 24.. i childstatus
GGSCI (SysA) 24> send mgr childstatus
r <text>
Replaces text. For example:
GGSCI (SysA) 25> fc 9
GGSCI (SysA) 25> info mgr
GGSCI (SysA) 25.. rextract extjd
GGSCI (SysA) 25> info extract extjd
d
Deletes a character. To delete multiple characters, enter a d for each
one. For example:
GGSCI (SysA) 26> fc 10
GGSCI (SysA) 26> info extract extjd, detail
GGSCI (SysA) 26.. dddddddd
GGSCI (SysA) 26> info extract extjd
<replacement text>
Replaces the displayed command with the text that you enter on a
one-for-one basis. For example:
GGSCI (SysA) 26> fc 10
GGSCI (SysA) 26> info mgr
GGSCI (SysA) 26.. extract extjd
GGSCI (SysA) 26> info extract extjd
To execute the command, press Enter twice, once to exit the editor and
once to issue the command. To cancel an edit, type a forward slash (/)
twice.
Syntax:
FC [<n> | -<n> | <string>]
<n>
Displays the command from the specified line. Each GGSCI command line
is sequenced, beginning with 1 at the start of the session.
-<n>
Displays the command that was issued <n> lines before the current line.
<string>
Displays the last command that starts with the specified text string.
Example 1:
FC 9
Example 2:
FC -3
Example 3:
FC sta
---------------------------------------------------------------------
HELP
HELP
Use HELP to obtain information about an Oracle GoldenGate command. The
basic command returns a list of command categories and the associated
commands. The <command> option restricts the output to that of a
specific command.
Syntax:
HELP [<command>]
<command>
The command for which you want help.
Example:
HELP add replicat
---------------------------------------------------------------------
HISTORY
HISTORY
Use HISTORY to view a list of the most recently issued GGSCI commands
since the GGSCI session started. You can use the ! command or the FC
command to re-execute a command in the list.
Syntax:
HISTORY [<n>]
<n>
Returns a specific number of recent commands, where <n> is any positive
number.
Example:
HISTORY 7
The result of this command would be similar to:
1: start manager
2: status manager
3: info manager
4: send manager childstatus
5: start extract extjd
6: info extract extjd
7: history
---------------------------------------------------------------------
INFO ALL
INFO ALL
Use INFO ALL to display the status and lag (where relevant) for all
Manager, Extract, and Replicat processes on a system. The basic
command, without options, displays only online (continuous) processes.
To display tasks, use either INFO ALL TASKS or INFO ALL ALLPROCESSES.
Syntax:
INFO ALL [TASKS | ALLPROCESSES]
TASKS
Displays information only for tasks.
ALLPROCESSES
Displays information for online processes and tasks.
Example 1:
INFO ALL TASKS
Example 2:
INFO ALL ALLPROCESSES
---------------------------------------------------------------------
INFO MARKER
INFO MARKER
Use INFO MARKER to review recently processed markers from a NonStop
system. A record is displayed for each occasion on which GGSCI, Logger,
Extract, or Replicat processed the marker.
Markers can only be added on a NonStop system, using Oracle GoldenGate
for NonStop for HP NonStop software.
Explanation of the output:
* Processed is the local time that a program processed the marker.
* Added is the local time at which the marker was inserted into the
NonStop audit trails or log trails.
* Diff is the time difference between the Processed and Added values.
Diff can serve as an indicator of the lag between the user
application and Extract and Replicat activities.
* Prog shows which process processed the marker, such as GGSCI, Logger,
Extract or Replicat.
* Group shows the Extract or Replicat group or Logger process that
processed the marker. N/A is displayed if GGSCI processed the marker.
* Node shows the node where the marker was inserted into the audit
trails.
* There might be an additional column if user-defined text was included
in the ADD MARKER statement.
Syntax:
INFO MARKER [COUNT <num items>]
COUNT <num items>
Restricts the list to a specified number of the most recent markers.
---------------------------------------------------------------------
OBEY
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.
Syntax:
OBEY <file name>
<file name>
The fully qualified name of the file that contains the list of
commands.
Example 1:
obey ./mycommands.txt
The preceding command executes a file that might look something like
this:
add extract fin, tranlog, begin now
add exttrail ggs/dirdat/aa, extract fin
add extract hr, tranlog, begin now
add exttrail ggs/dirdat/bb, extract hr
start extract *
info extract *, detail
Example 2:
The following illustrates a nested OBEY file. Assume an OBEY file named
addcmds.txt. Inside this file, there is another OBEY command that calls
the OBEY file named startcmds.txt, which executes another set of
commands.
obey ./addcmds.txt
(This executes the following:)
add extract fin, tranlog, begin now
add exttrail ggs/dirdat/aa, extract fin
add extract hr, tranlog, begin now
add exttrail ggs/dirdat/bb, extract hr
add replicat fin2, exttrail ggs/dirdat/aa, begin now
add replicat hr2, exttrail ggs/dirdat/bb, begin now
obey ./startcmds.txt
(The nested startcmds.txt file executes the following:)
start extract *
info extract *, detail
start replicat *
info replicat *, detail
--------------------------------------------------------------------
SHELL
SHELL
Use SHELL to execute shell commands from within the GGSCI interface.
Syntax:
SHELL <command>
<command>
The system command to execute.
Example 1:
SHELL dir dirprm\*
Example 2:
SHELL rm ./dat*
---------------------------------------------------------------------
SHOW
SHOW
Use SHOW to display the Oracle GoldenGate environment.
Syntax:
SHOW
---------------------------------------------------------------------
VERSIONS
VERSIONS
Use VERSIONS to display operating system and database version
information. For odbc connections, driver version information is also
displayed. To display database information, issue a DBLOGIN command
first to establish a database connection.
Syntax:
VERSIONS
---------------------------------------------------------------------
VIEW GGSEVT
VIEW GGSEVT
Use VIEW GGSEVT to view the Oracle GoldenGate error log (ggserr.log
file). This file contains information about Oracle GoldenGate events,
such as process startup, shutdown, and exception conditions. This
information is recorded in the system error log, too, but viewing
the Oracle GoldenGate error log sometimes is more convenient and may
retain events further back in time.
The display can be lengthy. To exit the display before reaching the
end, use the operating system抯 standard methods for terminating screen
output.
Syntax:
VIEW GGSEVT
---------------------------------------------------------------------
VIEW REPORT
VIEW REPORT
Use VIEW REPORT to view the process report that is generated by Extract
or Replicat. The report lists process parameters, run statistics, error
messages, and other diagnostic information.
The command displays only the current report. Reports are aged whenever
a process starts. Old reports are appended with a sequence number, for
example finance0.rpt, finance1.rpt, and so forth. To view old reports,
use the [<n>] option.
Syntax:
VIEW REPORT {<group name>[<n>] | <file name>}
<group name>
The name of the group. The command assumes the report file named
<group>.rpt in the Oracle GoldenGate dirrpt sub-directory.
<n>
The number of an old report. Report files are numbered from 0 (the most
recent) to 9 (the oldest).
<file name>
A fully qualified file name, such as c:\ggs\dirrpt\orders.rpt.
Example 1:
The following displays an old report file (number 3) for the orders
group.
VIEW REPORT orders3
Example 2:
The following displays a specific report identified by file name.
VIEW REPORT c:\ggs\dirrpt\orders.rpt
|
|
发表于 2021-9-24 16:27:04