Satya's DBA Blog
February 6, 2021
ggsci Oracle GoldenGate (OGG) Command Interpreter
GGSCI -- (Oracle) GoldenGate Software Command Interpreter (19c/21c)- Stores
- Mp4 Download
- Databases
- Alias
- Approximate
- Checkpoint
- Classic
- Cleanups
OGG files
Reports (.rpt) /u02/goldengate/ogg/dirrpt
Parameters (.prm) /u02/goldengate/ogg/dirprm
Replicat Checkpoints (.cpr) /u02/goldengate/ogg/dirchk
Extract Checkpoints (.cpe) /u02/goldengate/ogg/dirchk
Process Status (.pcs) /u02/goldengate/ogg/dirpcs
SQL Scripts (.sql) /u02/goldengate/ogg/dirsql
Database Definitions (.def) /u02/goldengate/ogg/dirdef
Dump files (.dmp) /u02/goldengate/ogg/dirdmp
Masterkey wallet files (.wlt) /u02/goldengate/ogg/dirwlt
Credential store files (.crd) /u02/goldengate/ogg/dircrd
/u02/goldengate/ogg/dirdat
How to Invoke OGG command line interface?
C:\GG> ggsci.exe (in Windows) [oracle@prod gg]$ ./ggsci
Commands ggsci > HELP [command] [object] ggsci > help
GGSCI/OGG Command Summary: Object: Command: SUBDIRS CREATE
DATASTORE ALTER, CREATE, DELETE, INFO, REPAIR
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
JAGENT INFO, START, STATUS, STOP
MANAGER INFO, SEND, START, STOP, STATUS
MARKER INFO
PARAMETERS EDIT, VIEW, SET EDITOR, INFO, GETPARAMINFO
REPLICAT ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, REGISTER, SEND,
START, STATS, STATUS, STOP, SYNCHRONIZE, UNREGISTER
REPORT VIEW
RMTTRAIL ADD, ALTER, DELETE, INFO
TRACETABLE ADD, DELETE, INFO
TRANDATA ADD, DELETE, INFO
SCHEMATRANDATA ADD, DELETE, INFO
CHECKPOINTTABLE ADD, DELETE, CLEANUP, INFO, UPGRADE
WALLET CREATE, OPEN, PURGE
MASTERKEY ADD, INFO, RENEW, DELETE, UNDELETE
CREDENTIALSTORE ADD, ALTER, INFO, DELETE
HEARTBEATTABLE ADD, DELETE, ALTER, INFO, UPGRADE
HEARTBEATENTRY DELETE
Commands without an object:
(Database) DBLOGIN, LIST TABLES, ENCRYPT PASSWORD, FLUSH SEQUENCE, MININGDBLOGIN, SET NAMECCSID
(DDL) DUMPDDL
(Miscellaneous) !, ALLOWNESTED | NOALLOWNESTED, CREATE SUBDIRS, DEFAULTJOURNAL, FC, HELP,
HISTORY, INFO ALL, OBEY, SHELL, SHOW, VERSIONS, VIEW GGSEVT, VIEW REPORT
(note: type the word COMMAND after the ! to display the
! help topic, for example: GGSCI (sys1)> help ! command
For help on a specific command, type HELP command object.
Example: HELP ADD REPLICAT
ggsci > help add rmttrail =========================================================================
ggsci > CREATE SUBDIRS -- To create default directories within Oracle GoldenGate (OGG) home directory
=========================================================================
DATASTORE ALTER, CREATE, DELETE, INFO, REPAIR
ggsci > info datastore -- to display information about the Oracle GoldenGate Monitor data store
ggsci > info datastore
ggsci > create datastore { MMAP | SHM [ID ] } -- to create an Oracle GoldenGate Monitor data store in the Oracle GoldenGate installation directory
ggsci > CREATE DATASTORE MMAP
ggsci > CREATE DATASTORE SHM
ggsci > CREATE DATASTORE SHM ID 999
ggsci > alter datastore { MMAP | SHM [ID ] } -- to change the memory model that is used for
interprocess communication by the Oracle GoldenGate Monitor data store.
ggsci > ALTER DATASTORE MMAP
ggsci > ALTER DATASTORE SHM
ggsci > ALTER DATASTORE SHM ID 999
ggsci > DELETE DATASTORE [!] -- to remove the Oracle GoldenGate Monitor data store from the Oracle GoldenGate installation directory.
ggsci > DELETE DATASTORE
ggsci > DELETE DATASTORE !
ggsci > REPAIR DATASTORE -- to repair the Oracle GoldenGate Monitor data store if it is corrupt or after an upgrade.
ggsci > REPAIR DATASTORE
=========================================================================
ER INFO, KILL, LAG, SEND, STATUS, START, STATS, STOP
ggsci > INFO ER group_wildcard_specification -- To get information on multiple Extract and Replicat groups as a unit
ggsci > info er *ext*
ggsci > info ER *
ggsci > START ER group_wildcard_specification -- To start multiple Extract and Replicat groups as a unit
ggsci > start er *rep*
ggsci > STOP ER group_wildcard_specification -- To stop multiple Extract and Replicat groups as a unit
ggsci > stop er *ext*
ggsci > STATUS ER group_wildcard_specification -- To check the status of multiple Extract and Replicat groups as a unit
ggsci > status er *EX*
ggsci > LAG ER group_wildcard_specification -- To get lag information on multiple Extract and Replicat groups as a unit
ggsci > lag er *ext*
ggsci > KILL ER group_wildcard_specification -- To forcefully terminate multiple Extract and Replicat groups as a unit
ggsci > kill er *x*
ggsci > SEND ER group_wildcard_specification -- To send instructions to multiple Extract and Replicat groups as a unit
ggsci > send er *ext
ggsci > STATS ER group_wildcard_specification -- To get statistics on multiple Extract and Replicat groups as a unit
ggsci > stats er ext*
stats ER *
=========================================================================
EXTRACT ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, REGISTER, SEND, START, STATS, STATUS, STOP, UNREGISTER
ggsci > INFO EXTRACT group_name [, SHOWCH [n]] [, DETAIL] [, TASKS | ALLPROCESSES] -- To display status of Extract, approximate Extract lag, checkpoint information, process run history
ggsci > info extract emp_ext
ggsci > info extract cust_ext, detail
ggsci > info extract ext*, showch
ggsci > INFO EXTRACT E*, SHOWCH 10
ggsci > info extract *, detail
ggsci > info extract hr, tasks
ggsci > info extract extp, allprocesses
ggsci > ADD EXTRACT group_name
{, SOURCEISTABLE |
, TRANLOG |
, INTEGRATED TRANLOG |
, VAM |
, EXTFILESOURCE file_name |
, EXTTRAILSOURCE trail_name |
, VAMTRAILSOURCE VAM_trail_name}
BEGIN {NOW | yyyy-mm-dd[ hh:mi:[ss[.cccccc]]]} |
EXTSEQNO sequence_number, EXTRBA relative_byte_address |
EOF |
LSN [value] |
bsds_name |
LRI_NUMBER |
EXTRBA relative_byte_address |
PAGE data_page, ROW row_ID |
SEQNO sequence_number
SCN value
THREADS [n]
PASSIVE
PARAMS file_name
REPORT file_name
DESC 'description'
SOCKSPROXY {host_name | IP_address}[:port] [PROXYCSALIAS credential_store_alias
[PROXYCSDOMAIN credential_store_domain]]]
RMTNAME passive_Extract_name]
DESC [description]
} -- To create an Extract group
Syntax for an alias Extract:
ADD EXTRACT group_name, RMTHOST {host_name | IP_address}, {MGRPORT port} | {PORT port} [, RMTNAME name] [, DESC "description"]
ggsci > add extract s_extr, tranlog, begin now
ggsci > add extract finext, tranlog, begin now, threads 4
ggsci > add extract fin, tranlog, begin now, passive
ggsci > ADD EXTRACT ESOURCE02, INTEGRATED TRANLOG, BEGIN NOW
ggsci > add extract ext_ms, extseqno 111, begin now
ggsci > add extract hr_ext, extrba 567890, begin 2018-04-02 12:00:00
ggsci > add extract initload, sourceistable
ggsci > add extract pump, exttrailsource /oracle/ogg_21c/dirdat/hr
ggsci > add extract fin, vam -- VAM - Vendor Access Module
ggsci > add extract fin, vamtrailsource /ogg/dirdat/vt
ggsci > add extract finA, rmthost host123, mgrport 7810, rmtname fin
ggsci > ADD EXTRACT finance TRANLOG SCN 123456
ggsci > ADD EXTRACT finance INTEGRATED TRANLOG SCN 123456
ggsci > REGISTER EXTRACT extract_group_name LOGRETENTION -- For classic Extract
REGISTER EXTRACT group-name
( LOGRETENTION | DATABASE
( [ CONTAINER container-list |
ADD CONTANER container-list |
DROP CONTAINER container-list ]
[ SCN scn ]
[ SHARE ( AUTOMATIC | group-name | NONE ) ]
[ [NO]OPTIMIZED ]
)
) -- For Integrated Extract
ggsci > register extract extr logretention
ggsci > REGISTER EXTRACT 1ext DATABASE
ggsci > REGISTER EXTRACT ext9 database SCN 136589 SHARE AUTOMATIC
ggsci > REGISTER EXTRACT ext2 DATABASE SHARE NONE
ggsci > REGISTER EXTRACT sales DATABASE CONTAINER (sales, finance, hr)
ggsci > UNREGISTER EXTRACT extract_group_name {DATABASE | LOGRETENTION}
ggsci > unregister extract extr logretention
ggsci > UNREGISTER EXTRACT sales DATABASE
ggsci > ALTER EXTRACT group_name
[ADD_EXTRACT_attribute]
[TRANLOG LRI_number]
[UPGRADE INTEGRATED TRANLOG]
[DOWNGRADE INTEGRATED TRANLOG [THREADS number]]
[THREAD number]
[LSN value]
[SCN value]
[ETROLLOVER] -- To change the attributes of an Extract group, To increment a trail to the next file in the sequence
ggsci > alter extract PA_AD, begin now
ggsci > alter extract EA_AD, begin 2018-08-23
ggsci > alter extract PA_AD, etrollover
ggsci > alter extract PA_AD, extseqno 5470, extrba 209184
ggsci > ALTER EXTRACT EA01RA THREAD 1
ggsci > alter extract accounts, thread 4, begin 2018-03-09
ggsci > alter extract sales, lsn 1234:123:1
ggsci > ALTER EXTRACT finance, SCN 778899
ggsci > ALTER EXTRACT finance, UPGRADE INTEGRATED TRANLOG
ggsci > ALTER EXTRACT efin8, DOWNGRADE INTEGRATED TRANLOG THREADS 2
ggsci > ALTER EXTRACT finance, SEQNO 1234 JOURNAL accts/acctsjrn JRNRCV accts/jrnrcv0005
ggsci > LAG EXTRACT group_name [GLOBAL] -- To determine a true lag time between Extract and the datasource
ggsci > lag extract ext*
ggsci > lag extract *
ggsci > KILL EXTRACT group_name -- To kill an Extract process running in regular or PASSIVE mode
ggsci > kill extract fin
ggsci > DELETE EXTRACT group_name [!] -- To delete an Extract group
ggsci > delete extract emp_ext
ggsci > delete extract emp_ext ! -- will not ask for confirmation
ggsci > CLEANUP EXTRACT group_name [, SAVE count] -- To delete run history for the specified Extract group
ggsci > cleanup extract fin
ggsci > cleanup extract e*, save 6
ggsci > START EXTRACT extract_name [ATCSN csn | AFTERCSN csn] -- To start Extract process
ggsci > start extract extr
ggsci > start *
ggsci > START EXTRACT finance ATCSN 684993
ggsci > START EXTRACT finance AFTERCSN 684993
ggsci > START EXT1PMP FORCEAPPEND
ggsci > STOP EXTRACT extract_name -- To stop Extract gracefully
ggsci > stop extract extr ggsci > stop *
ggsci > STATUS EXTRACT group_name [, TASKS | ALLPROCESSES] [UPGRADE | DOWNGRADE] -- To determine whether or not Extract is running
ggsci > status extract extr_hr
ggsci > status extract ext*, tasks
ggsci > status extract *ext*, allprocesses
ggsci > STATS EXTRACT group_name [, statistic] [, TABLE table] [, TOTALSONLY table_specification] [, REPORTFETCH | NOREPORTFETCH] [, REPORTRATE HR|MIN|SEC] [, ... ] -- To display statistics for one or more Extract group
ggsci > stats ext_hr
ggsci > stats ecm*
ggsci > stats extract ext
ggsci > stats extract ext2 reportrate sec
ggsci > stats extract fin, total, daily
ggsci > stats extract fin, total, hourly, table acct, reportrate min, reset, reportfetch
ggsci > stats extract ext_1a, reset
ggsci > STATS EXTRACT ext, LATEST, REPORTFETCH
ggsci > SEND EXTRACT group_name, {
BR {BRINTERVAL interval |
BRSTART |
BRSTOP | BRCHECKPOINT {IMMEDIATE | IN n{M|H} |
AT yyyy-mm-dd hh:mm[:ss]]}} |
BR BRFSOPTION { MS_SYNC | MS_ASYNC }
CACHEMGR {CACHESTATS | CACHEQUEUES | CACHEVMUSAGE} |
FORCESTOP |
FORCETRANS transaction_ID [THREAD n] [FORCE] |
GETLAG |
GETPARAMINFO [parameter_name] [FILE output_file] |
GETTCPSTATS |
LOGEND |
LOGSTATS |
REPORT |
RESUME |
ROLLOVER |
SHOWTRANS [transaction_ID] [THREAD n] [COUNT n]
[DURATION duration unit] [TABULAR]
[FILE file_name [DETAIL]] |
SKIPTRANS transaction_ID [THREAD n] [FORCE] |
STATUS |
STOP |
TRACE[2] file_name |
TRACE[2] OFF |
TRACE OFF file_name |
TRACEINIT |
TRANSLOGOPTIONS INTEGRATEDPARAMS(parameter_specification)|
TRANLOGOPTIONS {PREPAREFORUPGRADETOIE | NOPREPAREFORUPGRADETOIE}
|TRANLOGOPTIONS
{PURGEORPHANEDTRANSACTIONS | NOPURGEORPHANEDTRANSACTIONS} |
TRANLOGOPTIONS TRANSCLEANUPFREQUENCY minutes |
VAMMESSAGE 'Teradata_command' |
VAMMESSAGE {'ARSTATS' | 'INCLUDELIST [filter]' | 'FILELIST [filter]'
| 'EXCLUDELIST
[filter]'} |
VAMMESSAGE 'OPENTRANS'
} -- To communicate with a running Extract process
Teradata_command = {"control:terminate" | "control:suspend" | "control:resume" | "control:copy database.table"
ggsci > send extract exthr status
ggsci > send extract extr, getlag
ggsci > send extract group_name tltrace file file_name ddlinclude
ggsci > send extract fin, rollover
ggsci > send extract fin stop
ggsci > send extract fin, vammessage control:suspend
ggsci > send extract fin, tranlogoptions transcleanupfrequency 15
ggsci > send extract fin, showtrans
ggsci > send extract fin, showtrans count 10
ggsci > send extract fin, skiptrans 15.7.42734 thread 4
ggsci > SEND EXTRACT EXTORD, ROLLREPORT
=========================================================================
EXTTRAIL ADD, ALTER, DELETE, INFO
ggsci > INFO EXTTRAIL trail_name -- To retrieve configuration information for a local trail
ggsci > info exttrail *
ggsci > info exttrail e:\ogg\dirdat\ex
ggsci > ADD EXTTRAIL trail_name, EXTRACT group_name [, MEGABYTES n] [, SEQNO n] -- To create a trail for online processing on local system
ggsci > add exttrail /oracle/ogg_21c/dirdat/lt, extract s_extr
ggsci > add exttrail C:\OGG\dirdat\et, extract emp_ext
ggsci > add exttrail e:\ogg\dirdat\fi, extract fin, megabytes 50
ggsci > ALTER EXTTRAIL trail_name, EXTRACT group_name [, MEGABYTES n] -- To change the attributes of a trail (on the local system)
ggsci > alter exttrail c:\ogg\dirdat\aa, extract fin, megabytes 30
ggsci > DELETE EXTTRAIL trail_name -- To delete the record of checkpoints associated with a trail on a local system
ggsci > delete exttrail /home/ogg/dirdat/et
=========================================================================
JAGENT INFO, START, STATUS, STOP
INFO JAGENT Returns information about the Oracle GoldenGate Monitor JAgent.
ggsci > INFO JAGENT
START JAGENT Starts the JAgent.
ggsci > START JAGENT
STATUS JAGENT Returns the state of the Oracle GoldenGate Monitor JAgent. ggsci > STATUS JAGENT
STOP JAGENT Stops the JAgent.
ggsci > STOP JAGENT
ggsci > STOP JAGENT !
=========================================================================
MANAGER INFO, SEND, START, STOP, STATUS
ggsci > INFO MANAGER -- To determine whether or not the Manager process is running
ggsci > INFO MGR
ggsci > INFO MARKER [COUNT number_of_items] -- To review recently processed markers from a nonstop system
ggsci > info marker
ggsci > info marker count 6
ggsci > EDIT PARAMS {MGR | ./GLOBALS | group_name | file_name} -- To create or change a parameter file
ggsci > EDIT PARAMS DEFGEN
ggsci > edit params ./GLOBALS
GGSCHEMA ogg_own
CHECKPOINTTABLE ogg_own.checkpoint
ALLOWOUTPUTDIR /u02/ogg_trails
HEARTBEATTABLE table_name
ENABLEMONITORING
ggsci > edit params mgr
ggsci > VIEW PARAMS {MGR | group_name | file_name} -- To view the contents of a parameter file
ggsci > view params mgr
ggsci > view params s_extr
ggsci > view params e:\ogg_parameters\replp.prm
ggsci > START MANAGER -- To start Manager process
ggsci > start MGR
ggsci > start manager
ggsci > STOP MANAGER [!] -- To stop Manager process
ggsci > stop manager
ggsci > stop mgr
ggsci > stop manager ! -- will not ask for confirmation
ggsci > SEND MANAGER [CHILDSTATUS [DEBUG]] [GETPORTINFO [DETAIL]] [GETPURGEOLDEXTRACTS] -- To retrieve the status of the active Manager process or to retrieve dynamic port information as configured in the Manager parameter file
ggsci > send manager childstatus
ggsci > send manager childstatus debug
ggsci > send manager getportinfo
ggsci > send manager getportinfo detail
ggsci > send manager getpurgeoldextracts
ggsci > STATUS MANAGER -- To determine whether or not the Manager process is running
ggsci > status manager
=========================================================================
PARAMETERS EDIT, VIEW, SET EDITOR, INFO, GETPARAMINFO
ggsci > SET EDITOR program_name -- To change the default text editor for the current session of GGSCI, default editors are Notepad for Windows and vi for UNIX
ggsci > set editor wordpad
=========================================================================
REPLICAT ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, REGISTER, SEND,
START, STATS, STATUS, STOP, SYNCHRONIZE, UNREGISTER
ggsci > INFO REPLICAT group_name [, SHOWCH [n]] [, DETAIL] [, TASKS | ALLPROCESSES] -- To display status of Replicat, approximate replication lag, the trail from which Replicat is reading, Replicat run history, including checkpoints in the trail, information about the Replicat environment.
ggsci > info replicat emp_rep
ggsci > info replicat emp_rep, detail
ggsci > info replicat prd*, detail, allprocesses
ggsci > info replicat *, tasks
ggsci > info replicat fin, showch
ggsci > info ra002 detail
INFO command will read the checkpoint file. LAG command get the lag time more precisely, because it directly communicates with ER to obtain the lag time. SEND command communicates directly with the process and gets the current/latest status of it.
ggsci > edit params e:\ogg\dirprm\replp.prm
ggsci > ADD REPLICAT group_name
INTEGRATED | COORDINATED [MAXTHREADS number]]
{, SPECIALRUN |
, EXTFILE file_name |
, EXTTRAIL trail_name}
BEGIN {NOW | yyyy-mm-dd[ hh:mm[:ss[.cccccc]]]} |
, EXTSEQNO sequence_number, EXTRBA rba]
{PARALLEL | PARALLEL INTEGRATED EXTTRAIL trail_name ,
CHECKPOINTTABLE owner.table | NODBCHECKPOINT}
PARAMS file_name]
REPORT file_name]
DESC 'description'] -- To create a Replicat group
ggsci > add replicat repl, exttrail C:\OGG\dirdat\lt
ggsci > add replicat R3CT, exttrail /u01/ogg/dirdat/lt, checkpointtable ogg_own.checkpoint
ggsci > add replicat initload, specialrun
ggsci > add replicat sales, exttrail /oracle/ogg/dirdat/lt, nodbcheckpoint
ggsci > ADD REPLICAT financer, INTEGRATED, EXTTRAIL c:\ggs\dirdat\rt
ggsci > add replicat sales, EXTTRAIL /ogg/dirdat/ex, FORMAT RELEASE 11.1
ggsci > ADD REPLICAT sales, COORDINATED MAXTHREADS 100, EXTTRAIL dirdat/rt
ggsci > ADD REPLICAT sales, PARALLEL INTEGRATED, EXTTRAIL dirdat/rt, checkpointtable ggadmin.checkpoint
ggsci > ADD REPLICAT R01AD INTEGRATED EXTTRAIL /ogg/trail/ra CHECKPOINTTABLE OGGOWN.CHKPTAB BEGIN 2018-10-31 20:04:25.293534
ggsci > ALTER REPLICAT group_name[threadID], {
ADD REPLICAT option ...] |
INTEGRATED | NONINTEGRATED, CHECKPOINTTABLE owner.table } -- To change the attributes of a Replicat group
ggsci > alter replicat fin, begin now
ggsci > alter replicat rfin, begin 2018-08-08 08:08:08
ggsci > alter replicat RA_AD, extseqno 3804
ggsci > alter replicat RA_AD, extrba 766338576
ggsci > alter replicat RBBP01 extseqno 13, extrba 11995
ggsci > ALTER REPLICAT finance, INTEGRATED
ggsci > ALTER REPLICAT finance, NONINTEGRATED, CHECKPOINTTABLE ogg.checkpt
ggsci > CLEANUP REPLICAT group_name [, SAVE count] -- To delete run history for a specified Replicat group
ggsci > cleanup replicat fin
ggsci > cleanup replicat *, save 10
ggsci > DELETE REPLICAT group_name [!] -- To delete a Replicat group
ggsci > delete replicat emp_ext
ggsci > delete replicat emp_ext ! -- will not ask for confirmation
ggsci > KILL REPLICAT group_name -- To kill a Replicat process
ggsci > kill replicat fin
ggsci > LAG REPLICAT group_name [GLOBAL] -- To determine a true lag time between Replicat and the trail
ggsci > lag replicat myrepl
ggsci > lag replicat *
ggsci > SEND REPLICAT group_name[threadID],
{
CACHEMGR {CACHESTATS | CACHEQUEUES | CACHEVMUSAGE} |
DEPENDENCYINFO|DEPINFO [TXNCOUNT num] |
FORCESTOP |
GETLAG |
GETPARAMINFO [parameter_name] [FILE output_file] |
HANDLECOLLISIONS | NOHANDLECOLLISIONS [table_spec] |
INTEGRATEDPARAMS(parameter_specification) |
REPORT [HANDLECOLLISIONS [table_spec]] |
RESUME |
STATUS |
STOP |
TRACE[2] [DDLINCLUDE | DDL[ONLY]] file_name |
TRACE[2] OFF |
TRACE OFF file_name |
TRACEINIT |
THREADS (threadID threadID] ...] thread_range thread_range]
...])
} -- To communicate with a starting or running Replicat process
ggsci > send replicat fin, handlecollisions
ggsci > send replicat fin, report handlecollisions fin_*
ggsci > send replicat fin, getlag
ggsci > SEND REPLICAT finance, INTEGRATEDPARAMS (parallelism 10)
ggsci > SEND REPLICAT fin, TRACE THREADS(1) FILE ./dirrpt/trace.trc
ggsci > SEND REPLICAT R01IX TRACE FILE ./dirrpt/R01IX_trace.trc
ggsci > send Rep2 DEPENDENCYINFO TXNCOUNT 5
ggsci > send replicat R01IX status
ggsci > START REPLICAT group_name [SKIPTRANSACTION | ATCSN csn | AFTERCSN csn] [FILTERDUPTRANSACTIONS | NOFILTERDUPTRANSACTIONS]
[THREADS (threadID threadID] ...] thread_range thread_range] ...]) -- To start Replicat process
ggsci > start *
ggsci > start replicat RCT
ggsci > start replicat fin, atcsn 5238306 -- commit sequence number (CSN)
ggsci > start replicat fin, aftercsn 0X000006B1:0002334D:0004
ggsci > start replicat MYREP skiptransaction
ggsci > start RAU8 NOFILTERDUPTRANSACTIONS
ggsci > START REPLICAT fin ATCSN 6488359 THREADS(1-3), AFTERCSN 6488360 THREADS(9-10), SKIPTRANSACTION THREADS(7,8)
ggsci > STOP REPLICAT replicat_name [!] -- To stop Replicat gracefully
ggsci > stop replicat 2RCT
ggsci > stop replicat rep ! ggsci > stop *
ggsci > STATUS REPLICAT group_name [, TASKS | ALLPROCESSES] -- To determine whether or not Replicat is running
ggsci > status replicat emp_rep
ggsci > status replicat cust_rep, tasks
ggsci > status replicat rep, allprocesses
ggsci > STATS REPLICAT group_name
[statistic]
[TABLE [container. | catalog.]schema.table]
[TOTALSONLY [container. | catalog.]schema.table]
[REPORTCDR]
[REPORTCHARCONV]
[REPORTDETAIL | NOREPORTDETAIL] REPORTRATE {HR | MIN | SEC}]
... ] -- To display statistics for one or more Replicat groups
ggsci > stats rep_hr
ggsci > stats rep_name -total
ggsci > STATS REPLICAT r_trg REPORTDETAIL SEC
ggsci > STATS REPLICAT r_trg, TOTAL, DAILY
ggsci > stats replicat fin, total, table acct, reportrate hr, reset, noreportdetail
ggsci > STATS REPLICAT group, REPORTCDR
ggsci > STATS REPLICAT RA_TH table OWNER.USERS REPORTCDR hourly REPORTRATE MIN
ggsci > SYNCHRONIZE REPLICAT group_name
ggsci > SYNCHRONIZE REPLICAT rep_fin
ggsci > UNREGISTER REPLICAT group_name DATABASE
ggsci > UNREGISTER REPLICAT sales DATABASE
=========================================================================
RMTTRAIL ADD, ALTER, DELETE, INFO
ggsci > INFO RMTTRAIL trail_name -- To retrieve configuration information for a remote trail
ggsci > info rmttrail *
ggsci > info rmttrail D:\ogg19c\dirdat\ex
ggsci > ADD RMTTRAIL trail_name, EXTRACT group_name [, MEGABYTES n] [, SEQNO n] -- To create a trail for online processing on remote system
ggsci > add rmttrail C:\OGG\dirdat_21c\hr, extract extr
ggsci > add rmttrail /u01/app/oracle/ogg21c/dirdat/ms, extract msextr
ggsci > add rmttrail /u02/app/ogg_19c/dirdat/my, extract mysql, megabytes 50
ggsci > ALTER RMTTRAIL trail_name, EXTRACT group_name [, MEGABYTES n] -- To change the attributes of a trail (on a remote system)
ggsci > alter rmttrail c:\ogg\dirdat\et, extract fin, megabytes 25
ggsci > DELETE RMTTRAIL trail_name -- To delete the record of checkpoints associated with a trail on a remote system
ggsci > delete rmttrail /home/ogg_21c/dirdat/et
=========================================================================
TRACETABLE ADD, DELETE, INFO
ggsci > INFO TRACETABLE [owner.table] -- To verify the existence of the specified trace table
ggsci > info tracetable
ggsci > info tracetable ora_trace
ggsci > ADD TRACETABLE [owner.table] -- To create a trace table in the Oracle database
ggsci > add tracetable
ggsci > add tracetable ora_trace
ggsci > DELETE TRACETABLE [owner.table] -- To delete a trace table
ggsci > delete tracetable
ggsci > delete tracetable ora_trace
=========================================================================
TRANDATA ADD, DELETE, INFO
ggsci > INFO TRANDATA [container.]owner.table [NOVALIDATE] -- To determine whether logging/replication enabled or not
ggsci > info trandata hr.*
ggsci > info trandata fin.acct
ggsci > INFO TRANDATA finance.ac* NOVALIDATE
ggsci > ADD TRANDATA {[container.]owner.table | schema.table [JOURNAL
library/journal] | library/file [JOURNAL library/journal]}
[NOSCHEDULINGCOLS | ALLCOLS]
[COLS (columns)]
[INCLUDELONG | EXCLUDELONG]
[LOBSNEVER | LOBSALWAYS | LOBSIFCHANGED | LOBSALWAYSNOINDEX]
[NOKEY]
[NOVALIDATE]
[PREPARECSN {WAIT | LOCK | NOWAIT | NONE}] -- To enable Oracle GoldenGate to acquire the transaction information it needs from the transaction records
ggsci > add trandata hr.*
ggsci > add trandata emp.employees
ggsci > add trandata fin.acct, cols (name, address)
ggsci > add trandata fin.acct, nokey, cols (name, pid)
ggsci > add trandata fin.acct, lobsalwaysnoindex
ggsci > ADD TRANDATA \u20AC1
ggsci > ADD TRANDATA SCHEMA_NAME.TABLE_NAME ALLCOLS
ggsci > DELETE TRANDATA user_name.table_names [, OLDFORMAT] [, USETRIGGER] -- To delete logging/replication data
ggsci > delete trandata fin.acct
ggsci > delete trandata fin.cust*
ggsci > delete trandata emp.hr, usetrigger
=========================================================================
SCHEMATRANDATA ADD, DELETE, INFO
ggsci > ADD SCHEMATRANDATA schema {
[ALLOWNONVALIDATEDKEYS]
[NOSCHEDULINGCOLS | ALLCOLS]}
[NOVALIDATE]
[PREPARECSN {WAIT | LOCK | NOWAIT | NONE}]
ggsci > ADD SCHEMATRANDATA OWNER ALLCOLS
ggsci > ADD SCHEMATRANDATA scott
ggsci > INFO SCHEMATRANDATA schema_name
ggsci > INFO SCHEMATRANDATA scott
ggsci > DELETE SCHEMATRANDATA schema_name
ggsci > DELETE SCHEMATRANDATA scott
ggsci > DELETE SCHEMATRANDATA scott ALLCOLS
ggsci > SET_INSTANTIATION_CSN csn FOR [schema.] table FROM source_database_name # Sets whether and how table instantiation CSN filtering is used
ggsci > SET_INSTANTIATION_CSN 12345678 FOR hr.employees FROM DBS6
ggsci > CLEAR_INSTANTIATION_CSN FOR [schema.]table FROM source_database_name # Clears table instantiation CSN filtering
ggsci > CLEAR_INSTANTIATION_CSN FOR hr.employees FROM orcl
=========================================================================
CHECKPOINTTABLE ADD, DELETE, CLEANUP, INFO, UPGRADE
ggsci > INFO CHECKPOINTTABLE [user_name.table_name] -- To confirm the existence of a checkpoint table and view the date and time that it was created
ggsci > info checkpointtable
ggsci > info checkpointtable ogg_own.chkpt_table
ggsci > ADD CHECKPOINTTABLE [user_name.table_name] -- To create a checkpoint table in the target database
ggsci > add checkpointtable
ggsci > add checkpointtable ogg_own.checkpoint
ggsci > CLEANUP CHECKPOINTTABLE [user_name.table_name] -- To remove checkpoint records from the checkpoint table
ggsci > cleanup checkpointtable ggs.fin_check
ggsci > DELETE CHECKPOINTTABLE [user_name.table_name] [!] -- To drop a checkpoint table from the database
ggsci > delete checkpointtable ggs.fin_check
ggsci > UPGRADE CHECKPOINTTABLE [[container. | catalog.]owner.table]
ggsci > UPGRADE CHECKPOINTTABLE
ggsci > UPGRADE CHECKPOINTTABLE ggs.fin_check
=========================================================================
WALLET CREATE, OPEN, PURGE
create wallet -- to encrypt the encryption keys that secure data over the network and in trail files and other Oracle GoldenGate files that store sensitive data.
ggsci > create wallet
open wallet -- to open a master-key wallet
ggsci > open wallet
purge wallet -- to permanently remove master key versions from the master-key wallet.
ggsci > purge wallet
=========================================================================
MASTERKEY ADD, INFO, RENEW, DELETE, UNDELETE
ADD MASTERKEY
ggsci > add masterkey ogg_mk
ggsci > add masterkey renew
INFO MASTERKEY [VERSION version]
ggsci > INFO MASTERKEY
ggsci > INFO MASTERKEY VERSION 7
ggsci > info masterkey ogg_mk version 1
DELETE MASTERKEY {VERSION version | RANGE FROM begin_value TO end_value | ALL}
ggsci > DELETE MASTERKEY VERSION 10
ggsci > DELETE MASTERKEY RANGE FROM 3 TO 6
UNDELETE MASTERKEY VERSION version
ggsci > UNDELETE MASTERKEY VERSION 3
RENEW masterkey
ggsci > renew masterkey ogg
=========================================================================
CREDENTIALSTORE ADD, ALTER, INFO, DELETE
ggsci > ADD CREDENTIALSTORE
ggsci > ADD CREDENTIALSTORE
ggsci > ALTER CREDENTIALSTORE delete user gg_root
ALTER CREDENTIALSTORE {
ADD USER userid |
REPLACE USER userid |
DELETE USER userid }
[PASSWORD password]
[ALIAS alias]
[DOMAIN domain]
ggsci > ALTER CREDENTIALSTORE ADD USER oggadmin password password
ggsci > ALTER CREDENTIALSTORE ADD USER oggadmin@link alias gg_root
ggsci > ALTER CREDENTIALSTORE ADD USER oggown PASSWORD oggown ALIAS gg_root
ggsci > ALTER CREDENTIALSTORE ADD USER ggadmin PASSWORD oracle ALIAS gg_root DOMAIN gg
ggsci > ALTER CREDENTIALSTORE REPLACE USER oggadmin password password
ggsci > ALTER CREDENTIALSTORE REPLACE USER oggown@testdb PASSWORD oggown ALIAS gg_root
ggsci > ALTER CREDENTIALSTORE delete user gg_root
ggsci > INFO CREDENTIALSTORE [DOMAIN domain-name]
ggsci > INFO CREDENTIALSTORE DOMAIN support
ggsci > DELETE CREDENTIALSTORE
ggsci > DELETE CREDENTIALSTORE
=========================================================================
HEARTBEATTABLE ADD, DELETE, ALTER, INFO,UPGRADE
ADD HEARTBEATTABLE
[, FREQUENCY number in seconds]
[, RETENTION_TIME number in days] |
[, PURGE_FREQUENCY number in days]
[, PARTITIONED]
[, NOADDTRANDATA ]
[, TARGETONLY]
ggsci > ADD HEARTBEATTABLE # from OGG 12c R2 (12.2.0)
ggsci > add heartbeattable, frequency 5
ggsci > ADD HEARTBEATTABLE, partitioned ggsci > add heartbeattable, RETENTION_TIME 60 ggsci > add heartbeattable, PURGE_FREQUENCY 5
ALTER HEARTBEATTABLE
[FREQUENCY number in seconds]
[RETENTION_TIME number in days] |
[PURGE_FREQUENCY number in days]
[TARGETONLY | NOTARGETONLY]
ggsci > ALTER HEARTBEATTABLE
ggsci > ALTER HEARTBEATTABLE FREQUENCY 60
ggsci > ALTER HEARTBEATTABLE RETENTION_TIME 30
ggsci > ALTER HEARTBEATTABLE PURGE_FREQUENCY 1
ggsci > INFO heartbeattable
ggsci > INFO heartbeattable
DELETE HEARTBEATTABLE group_name
ggsci > DELETE HEARTBEATTABLE ggsci > UPGRADE heartbeattable
ggsci > UPGRADE heartbeattable
=========================================================================
HEARTBEATENTRY DELETE
ggsci > DELETE HEARTBEATENTRY group_name Deletes entries in the heartbeat table.
ggsci > DELETE HEARTBEATENTRY
=========================================================================
Commands without an object:
(Database) DBLOGIN, LIST TABLES, ENCRYPT PASSWORD, FLUSH SEQUENCE, MININGDBLOGIN, SET NAMECCSID
ggsci > DBLOGIN {SOURCEDB|TARGETDB dsn | USERID user_name[, PASSWORD password] [SYSDBA] |
SOURCEDB|TARGETDB dsn, USERID user_name[, PASSWORD password] [SYSDBA] [SQLID sqlid] -- To establish a database connection
ggsci > dblogin sourcedb testdb
ggsci > dblogin targetdb repldb
ggsci > dblogin userid gg
ggsci > dblogin userid gg, password oracle
ggsci > dblogin userid system@localhost:1521/prod, password 12345678
ggsci > dblogin sourcedb ctdb@host1, userid ogg_own, password ggs sysdba
ggsci > dblogin USERIDALIAS gg_login
ggsci > ENCRYPT PASSWORD password [AES128 | AES192 | AES256 | BLOWFISH] ENCRYPTKEY {key_name | DEFAULT} -- To encrypt a database login password
ggsci > encrypt password oracle143 encryptkey spiderman
ggsci > ENCRYPT PASSWORD oracle123 BLOWFISH ENCRYPTKEY DEFAULT
ggsci > ENCRYPT PASSWORD oracle890 AES192 ENCRYPTKEY superkey2
FLUSH SEQUENCE owner.sequence -- Updates an Oracle sequence
ggsci > FLUSH SEQUENCE scott.seq*
ggsci > LIST TABLES table_name -- To list all tables in the database that match the specification
ggsci > list tables cust*
MININGDBLOGIN {
USERID {/ | userid} PASSWORD password] [algorithm ENCRYPTKEY {keyname |
DEFAULT}] | USERIDALIAS alias [DOMAIN domain] |
[SYSDBA]
} -- Specifies the credentials of the user that an Oracle GoldenGate process uses to log into an Oracle mining database
MININGDBLOGIN USERIDALIAS oggalias SESSIONCHARSET ISO-8859-11
MININGDBLOGIN USERID ogg@ora1.ora, PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC AES128, ENCRYPTKEY securekey1
SET NAMECCSID {CCSID | DEFAULT}
ggsci > SET NAMECCSID 1141
=========================================================================
(DDL) DUMPDDL
ggsci > DUMPDDL [SHOW] -- To view the data in the Oracle GoldenGate DDL history table
ggsci > dumpddl
=========================================================================
(Miscellaneous) !, ALLOWNESTED | NOALLOWNESTED, CREATE SUBDIRS, DEFAULTJOURNAL, FC, HELP,
HISTORY, INFO ALL, OBEY, SHELL, SHOW, VERSIONS, VIEW GGSEVT, VIEW REPORT
ggsci > SHOW -- To display the Oracle GoldenGate (OGG) environment
ggsci > SHOW ALL
ggsci > INFO ALL [TASKS | ALLPROCESSES] -- To display the status of all Manager, Extract, Datapump and Replicat processes
ggsci > info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXTR 00:01:15 00:00:07
REPLICAT ABENDED REP 00:00:00 00:00:04
ggsci > info all tasks
ggsci > info all allprocesses
ggsci > VIEW GGSEVT -- To view GoldenGate error log (ggserr.log file)
ggsci > VIEW REPORT {group_name[n] | file_name|mgr} -- To view the process report that is generated by Extract or Replicat
ggsci > view report ext6
ggsci > view report rep
ggsci > view report mgr
ggsci > view report c:\ogg\dirrpt\orders.rpt
ggsci > HISTORY [n] -- To view the most recently issued GGSCI commands since the session started
ggsci > history -- by default, shows last 10 commands
ggsci > history 30
pause sleep_interval
ggsci > pause 900
ggsci > VERSIONS -- To display operating system and database version information
ggsci > versions
Operating System:
Linux
Version #1 SMP Thu Jul 8 02:53:40 UTC 2021, Release 3.10.0-1160.36.2.el7.x86_64
Node: linux03.calldba.com
Machine: Linux
Database:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ggsci > FC [n | -n | string] -- To display edit a previously issued GGSCI command and then execute it again
ggsci > fc
ggsci > fc 9
ggsci > fc -3
ggsci > fc sta
FC editor commands
i text -- Inserts text
r text -- Replaces text
d -- Deletes a character
replacement_text -- Replaces the displayed command with the text that we enter on a one-for-one basis.
ggsci > SHELL shell_command -- To execute shell commands from within GGSCI interface
ggsci > shell ls -l /u01/app/oracle/19c/dirdat
ggsci > sh dir dirprm\*
ggsci > shell rm ./dat*
GGSCI > shell pmap -x replicate_process_id
ggsci > ! [n | -n | string] -- To execute previous GGSCI command
ggsci > !
ggsci > ! 6 -- To run the command 6 listed in the history
ggsci > ! -3
ggsci > ! sta
ggsci > help ! command
ggsci > OBEY ggsci_script -- To process a file that contains a list of Golden Gate commands.
ggsci > shell more /u01/app/oracle/ogg21c/startup.txt
START MANAGER
START EXTRACT EXT3
START EXTRACT DPUMP
INFO ALL
ggsci > obey /u01/app/oracle/ogg19c/startup.txt
ggsci > obey ./mycommands.txt
INFO PARAM name
ggsci > INFO PARAM RMTHOST
ggsci > INFO PARAM RETRYDELAY
ALLOWNESTED
NOALLOWNESTED
DEFAULTJOURNAL [library/journal] [CLEAR]
ggsci > EXIT
New Commands in OGG 12c R3 (Oracle GoldenGate 12c (12.3.0.1))
=========================================================================
PMSRVR - Performance Metrics Server process uses the metrics service to collect and store instance deployment performance results. INFO PMSRVR Returns information about the PMSRVR.
ggsci > INFO PMSRVR
START PMSRVR Starts the PMSRVR.
ggsci > START PMSRVR
STATUS PMSRVR Returns the state of the PMSRVR.
ggsci > STATUS PMSRVR
STOP PMSRVR Stops the PMSRVR.
ggsci > STOP PMSRVR
STOP PMSRVR !
=========================================================================
ADD PROCEDURETRANDATA Adding supplemental logging for Procedural Replication.
ggsci > ADD PROCEDURETRANDATA
DELETE PROCEDURETRANDATA Remove supplemental logging for Procedural Replication.
ggsci > DELETE PROCEDURETRANDATA
INFO PROCEDURETRANDATA Display display supplemental logging information about Procedural Replication.
ggsci > INFO PROCEDURETRANDATA
=========================================================================
Oracle GoldenGate 18c provides support for some new features which were introduced in Oracle Database 12c –
- Support for Identity/AUTO_INCREMENT Columns and
- Support for In-Database Row Archival (works like TTL, hidden column called ORA_ARCHIVE_STATE column is added to the table).