Once you have initialized your source and target and started change synchronization you are ready to manage your Oracle GoldenGate for NonStop environment. This chapter introduces a variety of tools and techniques for these administrative tasks.
This chapter includes the following sections:
Tasks are processes that are special runs, such as a one-time data synchronization, or direct file extraction. Tasks are useful in managing Oracle GoldenGate, because they allow you to load data that may have been missed due to a variety of system errors. You can define a task with the GGSCI commands:
GGSCI> ADD EXTRACT group_name, SOURCEISTABLE GGSCI> ADD REPLICAT group_name, SPECIALRUN
When you define a task, you must include the task type parameter in the parameter file. For the Extract parameter file, include SOURCEISTABLE
or SOURCEISFILE
. For the Replicat parameter file include SPECIALRUN
.
Manager can purge tasks. To purge tasks enter parameters such as:
PURGEOLDTASKS EXTRACT wildcard_spec, AFTER number HOURS, USESTOPSTATUS PURGEOLDTASKS REPLICAT wildcard_spec, AFTER number DAYS, USESTOPSTATUS PURGEOLDTASKS ER wildcard_spec, AFTER number HOURS, USESTOPSTATUS
You can retrieve information about a task using the INFO
and STATUS
commands with the TASKS
or ALLPROCESSES
options:
GGSCI> INFO EXTRACT *, TASKS GGSCI> INFO REPLICAT *, ALLPROCESSES GGSCI> STATUS ER *, ALLPROCESSES
TASKS
reports on SPECIALRUN
or SOURCEISFILE
tasks. ALLPROCESSES
reports on all processes.
Tasks defined with SPECIALRUN
, SOURCEISFILE
, or SOURCEISTABLE
do not require a group name. Even without this name, it is possible to communicate with these running tasks by using the SEND
PROCESS
command. The syntax for this uses the process name instead of a group name as shown below.
GGSCI> SEND PROCESS process_name {text | WAKE | BREAK}
The text
option can be any one of the subset of GGSCI commands that are recognized by the receiving process.
See Reference for Oracle GoldenGate on HP NonStop Guardian for details about the SEND
PROCESS
options.
Oracle GoldenGate trails can be managed by allocating optimal storage for the trail files and setting parameters for cleaning up trail files that are no longer needed.
To prevent trail activity from interfering with business applications, use a separate disk managed by a disk process different than that of the application.
To ensure there is enough disk space for the trail files, follow these guidelines:
For trails on the source system, there should be enough space to handle data accumulation if the network connection fails. In a failure, reading from a trail terminates but the primary Extract group reading from logs or audit file continues extracting data. It is not good practice to stop the primary Extract group to prevent further accumulation. The logs could recycle or the audit files could be off-loaded.
For trails on the target system, data will accumulate because data is extracted and transferred across the network faster than it can be applied to the target database.
Estimate the longest time that you think the network can be unavailable.
Estimate how much transaction log volume you generate in one hour.
Use the following formula:
trail disk space = transaction log volume in 1 hour x number of hours down x .4
Note:
The equation uses a multiplier of 40 percent because Oracle GoldenGate estimates that only 40 percent of the data in the transaction logs is written to the trail.
A more exact estimate can be derived by either:
Configuring Extract and allowing it to run for a set time period, such as an hour, to determine the growth. This growth factor can then be applied to the maximum down time.
Using MEASFLS and MEASRPT to collect and report on statistics over a full business cycle and using this data to determine the volume over the maximum down time.
Plan to store enough data to withstand the longest anticipated outage possible because you will need to re-synchronize the source and target data should the outage outlast the disk capacity.
Oracle GoldenGate provides options that let you manage your trails in two ways.
Based on the number and size of the files.
The MEGABYTES, MAXFILES
, and EXTENTS
options specify how large each trail file may become, and how many files may exist before Extract stops with an error.
With the PURGEOLDEXTRACTS
parameter.
This lets you purge old extracted data you no longer need. This can be based on rules you set up.
The MINKEEPHOURS
, MINKEEPDAYS
options set the time to keep files. MINKEEPFILES
sets the minimum number of files to keep.
In the Manager only, the USECHECKPOINTS
option uses checkpoints to determine whether processing is complete. You can also set the CHECKMINUTES
parameter to control how often the process checks the parameters to determine if anything must be purged.
Two options for managing trail size are MEGABYES
and MAXFILES
. MEGABYTES
lets you specify how large your trail file gets before your data rolls to another trail file. It is useful if you want to equally distribute data between your files. The default size is 134
megabytes and the largest size supported is two gigabytes. MAXFILES
lets you specify the number of trail files Oracle GoldenGate creates. The default is 100 files. Allowing multiple files lets data roll over when one file is full, which prevents errors. The syntax for using MEGABYTES
and MAXFILES
is:
GGSCI> ADD EXTTRAIL trail_name, EXTRACT group_name, MEGABYTES num, MAXFILES num
Trails that either reside on the local node or on a node that is connected by Expand are considered local for NonStop. For these trails, you can also control size by setting the files' primary, secondary and maximum number of extents. The syntax for this is:
GGSCI> ADD EXTTRAIL trail_name, EXTRACT group_name [, EXTENTS (primary, secondary, max)]
The defaults for EXTENTS
are (64, 128, 512).
From GGSCI, an INFO
of the trail will show the current trail settings.
You can set PURGEOLDEXTRACT
in the Manager, Extract, or Replicat, but only Manager has options.
You can set options for purging trails with the PURGEOLDEXTRACTS
in the Manager's parameter file.
Use USECHECKPOINTS
to purge when all processes are finished with a file as indicated by checkpoints. This is the default, but it can be turned off with the NOUSECHECKPOINTS
option.
MINKEEPHOURS
or MINKEEPDAYS
to keep files n
hours or days. MINKEEPFILES
to keep at least n
files including the active file. The default is 1
.
Only one of the three MINKEEP
options should be set. If more than one is entered the system will select one based on the following:
If both MINKEEPHOURS
and MINKEEPDAYS
are specified, only the last setting will be used and the other will be ignored.
If both MINKEEP
{
HOURS
|DAYS
} and MINKEEPFILES
are specified MINKEEP
{HOURS|DAYS}
will be used and MINKEEPFILES
will be ignored.
You cannot set any options for the Extract and Replicat PURGEOLDEXTRACTS
parameter. In this case the trail is purged as soon as the process moves to the next trail.
If PURGEOLDEXTRACTS
is set in the Manager parameter file, when the Manager reaches CHECKMINUTES
the purge rules are evaluated as explained below.
USECHECKPOINTS
only
If there are no minimum rules set with the USECHECKPOINTS
option, MINKEEPFILES
defaults to 1
. If checkpoints indicate that a trail file has been processed, it will be purged unless it would fall below this one file minimum.
USECHECKPOINTS
with MINKEEP
rules
If checkpoints indicate that a trail file has been processed, it will be purged unless doing so would violate the applicable MINKEEP
{HOURS|DAYS}
or MINKEEPFILES
rules. These PURGEOLDEXTRACTS
minimum rules are set as explained in Section 10.2.2.2.
NOUSECHECKPOINTS
only
If there are no minimum rules and checkpoints are not to be considered, the file will be purged, unless doing so will violate the default MINKEEPFILES
of 1
.
NOUSECHECKPOINTS
with MINKEEP
rules
The file will be purged unless doing so will violate applicable MINKEEP{HOURS|DAYS}
or MINKEEPFILES
rules. Refer to Section 10.2.2.2 for information on setting these PURGEOLDEXTRACTS
minimum rules.
Purge Processing Examples
Trail files AA000000
, AA000001
, and AA000002
exist. The Replicat has been down for four hours and has not completed processing any of the files
The Manager parameters include:
PURGEOLDEXTRACTS $DATA1.DB.AA*, USECHECKPOINTS, MINKEEPHOURS 2
Result: The time files that are not accessed must be retained has been exceeded. No files will be purged, however, because checkpoints indicate that the files have not been fully processed by Replicat.
Trail files AA000000
, AA000001
, and AA000002
exist. The Replicat has been down for four hours and has not completed processing.
The Manager parameters include:
PURGEOLDEXTRACTS $DATA1.DB.AA*, NOUSECHECKPOINTS, MINKEEPHOURS 2
Result: All trail files will be purged since the minimums have been met.
The following is an example of why only one of the MINKEEP
options should be set
Replicat and Extract have completed processing. There has been no access to the trail files for the last five hours. Trail files AA000000
, AA000001
, and AA000002
exist.
The Manager parameters include:
PURGEOLDEXTRACTS $DATA1.DB.AA*, USECHECKPOINTS, MINKEEPHOURS 4, MINKEEPFILES 4
Result: USECHECKPOINTS
requirements have been met so the minimum rules will be considered when deciding whether to purge AA000002
.
There will only be two files if AA000002
is purged, which will violate the MINKEEPFILES
parameter. Since both MINKEEPFILES
and MINKEEPHOURS
have been entered, however, MINKEEPFILES
is ignored. The file will be purged because it has not been modified for 5 hours, which meets the MINKEEPHOURS
requirement of 4 hours.
The Manager process determines which files to purge based on the Extract processes configured on the local system. If at least one Extract process reads the trail file, Manager applies the specified rules.
For further information regarding PURGEOLDEXTRACTS
and ADD
EXTTRAIL
options, see Reference for Oracle GoldenGate on HP NonStop Guardian.
Consider the following recommendations for managing Oracle GoldenGate trails.
For setting the purge rules, it is recommended that: you
Specify PURGEOLDEXTRACTS
in the Manager parameter file so you manage your trails from a single location.
Purge trail files through Extract or Replicat only when one process is processing the trail, such as a data pump. Use Manager to purge trail files that are being processed by both Extract and Replicat.
Use USECHECKPOINTS
to ensure that the checkpoints of both Extract and Replicat are considered and reduce the chance of data loss.
Be aware that PURGEOLDEXTRACTS
in Extract or Replicat can remove trails still needed by the Coordinator. If you use the Coordinator, specify PURGEOLDEXTRACTS
in the Manager to manage the Coordinator checkpoints.
The rules should be assigned to the process that resides where the trail must be cleaned.
For example, if there are three nodes: \A
where the Extract is running and extracting the data; \B
where a subset of the data is replicated and \C
where another part of the data is replicated, it is the Manager on \A
that should be assigned the parameters that define how to manage the trails.
For USECHECKPOINTS
, this Manager will need to know the location of the checkpoint files on \B
and \C
, but this can be accomplished with REMOTECHKPT
as shown below.
GGSCI> ADD REMOTECHKPT \node.$volume.subvolume.REPCTXT
Unlike trails that are created externally, if a trail created by Logger runs out of space there are no audit records to be reprocessed once the problem is fixed. This can cause data loss, so it is important to have adequate space available for log trails. The following steps help do this.
Include adequate trail space when the Logger process is added.
Log trail files are pre-allocated during ADD
LOGGER
so this ensures that the space is available before the logging process begins.
Monitor and adjust the trail space as needed.
During processing, adjust the number and size of each log trail as needed by editing the Logger parameter file with the command EDIT
PARAM
LOGPARM
. Then the number of files can be increased or decreased by changing the NUMFILES
option, and the size of each trail file can be adjusted by changing the MEGABYTES
or EXTENTS
. The changes will be activated when an ALTER
LOGGER
command is issued.
Monitor the impact of the trail space on your system.
Manage creates the next log trail file if it is not available when it is time to rollover. This helps ensure that data will not be lost, but it also means that you may have more log trail files than specified in the NUMFILES
of LOGPARM
.
You can monitor the state of Oracle GoldenGate processing with the following tools.
Tool | Description |
---|---|
The event log |
The event log, named |
Process reports |
Oracle GoldenGate generates a report for Extract, Replicat, and Manager at the end of each run. The report provides information about run-time parameters and statistics, including process events and operations that were performed. |
N/A |
The name of a process report is either |
Record counts |
You can produce record counts at designated times during processing by using the |
Discard files |
The discard file logs information about operations that failed. To generate discard files, use the The SQL formatted discard file logs information and SQLCI formatted statements for operations that failed replication to SQL/MP. To generate, use the To control how discard files are generated, use the |
GGSCI |
Using the GGSCI commands |
Each of these tools is discussed in greater detail below.
There are several error handling parameters available in Oracle GoldenGate. In addition, Oracle GoldenGate provides error-handling options for Replicat and TCP/IP processing.
Error handling parameters let you insert missing records, prevent duplicate records from being loaded. For a complete list, see Reference for Oracle GoldenGate on HP NonStop Guardian.
To control the way that Replicat responds to errors, use the REPERROR
parameter in the Replicat parameter file. This parameter handles most errors in a default fashion (for example, to cease processing), and you can specify REPERROR
options to handle other errors in a specific manner, or to ignore them altogether.
REPERROR
provides the following options:
Option | Description |
---|---|
ABEND |
Roll back the Replicat transaction and terminate processing. This is the default. |
DISCARD |
Log the error to the discard file but continue processing the transaction and subsequent transactions. |
EXCEPTION |
Treat the error as an exception. To handle an exception, create an entry with the |
FILEOP |
Set the error handling for a particular I/O type. |
IGNORE |
Ignore the error. |
RESET |
Remove all |
RETRYOP |
Retry the operation. Use the |
RETRYOPEN |
Retry a file open error. |
TRANSABORT |
Abort the current target transaction and then retry it. |
The TCPERRS
file in the Oracle GoldenGate installation location contains preset TCP/IP errors and instructions for how Oracle GoldenGate generally reacts to them. If a response is not explicitly defined in this file, Oracle GoldenGate responds to TCP/IP errors by exiting.
Note:
The Manager process is an exception. When Manager has an IP error it retries every 60 seconds and does not abend. It does not use the TCPERRS
file to determine the number of retries or the delay.
Example 10-2 Sample of the TCPERRS File
# # TCP/IP error handling parameters # Default error response is abend # # error Response Delay (csecs) Max Retries ECONNABORTED RETRY 1000 10 ECONNREFUSED RETRY 1000 12 ECONNRESET RETRY 500 10 ENETDOWN RETRY 3000 50 ENETRESET RETRY 1000 10 ENOBUFS RETRY 100 60 ENOTCONN RETRY 100 10 EPIPE RETRY 500 10 ESHUTDOWN RETRY 1000 10 ETIMEDOUT RETRY 1000 10 NODYNPORTS RETRY 100 10
To alter the instructions or add instructions for new errors, open the file in a text editor and change any of the values in the following columns:
Error column: Specifies a TCP/IP error for which you are defining a response.
Response column: Controls whether Oracle GoldenGate tries to connect again after the defined error.
Delay column: Controls how long Oracle GoldenGate waits before attempting to connect again.
Max Retries column: Controls the number of times that Oracle GoldenGate attempts to connect again before aborting.
See Reference for Oracle GoldenGate on HP NonStop Guardian for details about the TCP/IP error messages, their causes, effects, and recovery.
Two types of discard files are available with Oracle GoldenGate for HP NonStop:
Discard files identify a record and operation that failed., providing information to help troubleshoot the error.
SQL discard files provide SQLCI formatted input to process the operation to an SQL/MP database once the error is corrected.
The discard file logs information about operations that failed. Extract discard records have a header and a data portion; the discard file is entry-sequenced. Replicat produces discard records in an external, easy-to-understand format
Full record images are provided when IO-TYPE
is Delete
, Insert
or Update
. Each record has the same format as if retrieved from a program reading the original file or table directly. For SQL tables, datetime fields, nulls and other fields are output exactly as a program would SELECT
them into an application buffer. Even though datetime fields are represented internally as an eight byte timestamp, their external form can be up to 26 bytes expressed as a string. Enscribe records are retrieved as they exist in the original file.
Full record images are output unless the original file has the AUDITCOMPRESS
attribute set to ON
. When AUDITCOMPRESS
is ON
, compressed update records are generated whenever the original file receives an update operation. (A full image can be retrieved by Extract using the FETCHCOMPS
parameter.)
When the operation type is Insert
or Update
, the image is the contents of the record after the change is made. When the operation type is Delete
, the image is the contents of the record before the change.
To control how discard files are generated, use the DISCARDROLLOVER
parameter in the Extract or Replicat parameter file. The parameter has provisions for specifying when new files are created.
To generate a discard file, use the DISCARDFILE
parameter in the Extract or Replicat parameter file.
If there is no DISCARDFILE
parameter for Replicat, a discard file is created by default when Replicat is started from GGSCI (as opposed to when it is run from the TACL prompt.) The default discard file will have the following characteristics:
The file name is derived by appending a D to up to 7 characters of the Replicat group name. For example, the Replicat GROUPA
will create discard file GROUPAD
and Replicat GROUPAB
will create discard file GROUPABD
. If the Replicat group name is more than 7 characters, the process name is used instead. Replicat GROUPABC
with process name $ABR00
will create discard file ABR00D
.
The file is created in the report file location.
The extents are set to (4, 4, 100)
Note:
Discard files that have been created by default cannot be rolled over.
To specify a non-default location or characteristic use the DISCARDFILE
parameter in the Replicat parameter file.
SQL discard files provide SQLCI formatted input for failed replication operations to a target SQL/MP database. Specify SQLFORMATDISCARDFILE
in the Replicat parameter file to generate the SQL discard file.
To use the SQL formatted discard file the target must be SQL/MP. The source of the operation can be any database and platform that Oracle GoldenGate supports.
The SQLCI formatted input will only process DML records once the original problem has been corrected.
To control how SQL formatted discard files are generated, use the parameter SQLFORMATDISCARDROLLOVER
. This parameter specifies when new files are created.
The following is an example of a section of a SQL formatted discard file record:
SET SESSION ERROR ABORT ON; BEGIN WORK; -- Problem with Insert Record, Seqno 0, RBA 1234 Error -8227 INSERT INTO \NY.$DATA2.SALES11.CUSTOMER & (CUST_CODE, NAME, CITY, STATE) VALUES & (”A543”, ”Advantage Software ”,& ”SEATTLE ”, ”WA”); COMMIT WORK;
The following is an example of the corresponding section from the discard file record:
*** ERROR from SQL [-8227]: The row being inserted or updated in underlying *** table \NY.$DATA2.SALES11.CUSTOMER contains a key value that must be *** unique but is already present in a row. Error -82227, guardian 10 occurred with insert record (target format). . . *--- 2013-08-28 09:46:13.775946 ----* 0, 4:CUST_CODE = ’A543' 1, 12:NAME = ’Advantage Software' 2, 46:CITY = ’SEATTLE' 3, 70:STATE = ’WA' *--- End Discard Record --- * Process Abending: 2013-08-28 09:46:14
The SQL discard file is an edit file that can be changed if needed. To change the file, first do a rollover on the file to trigger Replicat to close it and begin to use a new one. Then edit the file.
When you edit the SQL formatted discard file input, you must make sure your statements are valid for SQLCI:
Commands greater than 132 bytes must force a line break.
Strings must be enclosed in quotation marks. If embedded quotes are part of the data, the outermost set of quotation marks must be of the opposite type (single or double.)
Quoted strings longer than the maximum size must be broken into the 132 byte segments surrounded by quotation marks and terminated by the (&) continuation symbol. Note that the quotation marks and & symbol are all counted in the 132 byte limit.
The requirements for date-time and timestamp syntax are specific to the metadata.
SQLEXEC
works on SQL for NonStop databases to call queries you specify. This lets you leverage application rules to resolve conflicts between incoming source records. To do this, add a SQLEXEC
parameter in a MAP
statement in the Replicat parameter file.
Some applications that support distributed processing provide their own methods for handling conflicts. Such methods include IP persistent routers or application privileges that prevent multiple users from modifying the same data. These rules can be combined with the use of SQLEXEC
procedures.
The following is an example of basic conflict detection based on a timestamp. It raises an exception based on a user-defined error number passed to Replicat and a SQL query to check the value of the TIMESTAMP
column. If the timestamp is newer than the one on the existing record, the update is performed. Otherwise, the operation is ignored (because the newer timestamp is considered more valid) and a message is generated to the Replicat process report.
Example 10-3 REPLICAT Parameter File for Conflict Detection
REPERROR (9999, EXCEPTION) MAP $DATA.MASTER.SRC, TARGET $DATA.MASTER.TAR, SQLEXEC (ID check, QUERY " SELECT TIMESTAMP FROM TARGTAB" WHERE PKCOL =?P1 ", ERROR IGNORE); PARAMS (P1 = PKCOL)), FILTER (CREATED_BY <> "DBA"), FILTER (ON UPDATE, BEFORE.TIMESTAMP < CHECK.TIMESTAMP, RAISEERROR 9999); INSERTALLRECORDS MAP $DATA.MASTER.SRC, TARGET $DATA.MASTER.TAREXEC, EXCEPTIONSONLY, COLMAP (USEDEFAULTS, ERRTYPE = "UPDATE FILTER FAILED.");
In the example, the query is run under the logical name of check
. Values retrieved from this query can be utilized anywhere in the MAP
statement by referencing check.column name
.
The FILTER
statements in the example parameter file are processed in the order that they are written. If, in the first FILTER
statement, the value of the CREATED_BY
column in the record being applied by Replicat is equal to the DBA
account, the operation is accepted for processing by the second FILTER
statement. Otherwise, it is ignored.
In this example, SQLEXEC
also detects database errors, but ignores them and continues processing. This is the default action for ERROR
.
SQLEXEC (ID check, QUERY " SELECT TIMESTAMP FROM TARGTAB" WHERE PKCOL =?P1 ',ERROR IGNORE);
However, SQLEXEC
could perform any of the following:
Syntax | Description |
---|---|
ERROR REPORT |
Write the database error to a report. |
ERROR RAISE |
Enable the same error handling capabilities available for table replication errors. |
ERROR FINAL |
Enable the same error handling capabilities as |
ERROR FATAL |
Abend the process immediately. |
In the second FILTER
statement, the ON UPDATE
clause directs the filter to run only for update statements. It compares the value of BEFORE.TIMESTAMP
(the timestamp of the row that Replicat is attempting to apply) to CHECK.TIMESTAMP
(the timestamp of the row already in the database). If the row in the database is newer than the row being applied, then the filter raises an error and the update is ignored.
In the example, the error correction was implemented with RAISEERROR
in the SQLEXEC
clause in the first MAP
statement, but it could have been implemented in the second MAP
statement by replacing the COLMAP
clause with a SQLEXEC
clause.
To handle specific issues, additional SQLEXEC
statements could be performed after the filter or even between the filter statements for increased control.
The Oracle GoldenGate event log shows processing events, messages, errors, and warnings generated by Oracle GoldenGate. Although this information is also recorded in the NonStop Event Management System (EMS), viewing the Oracle GoldenGate log is sometimes more convenient. Use GGSCI VIEW GGSEVT
command to view the event log.
Oracle GoldenGate generates a report about Manager, Logger, Extract, Replicat, and Syncfile at the end of each run. The report provides information about run-time parameters and statistics, including process events, and operations that were performed. The name of a process report is either MANAGER
for the Manager process, or it is the same as the group name for Extract and Replicat. By default, reports are created in the subvolume GGSRPT
. For example, a report for an Extract group EXTORD
would have a report named GGSRPT.EXTORD
.
Generate process reports with the SEND EXTRACT
group_name
command and the following options:
Report | Option | Description |
---|---|---|
The end of an audit trail |
|
Queries Extract to determine whether all records in the TMF audit trails have been processed. This command indicates whether more Extract or Replicat activity must occur before a scheduled switch between databases. Until |
Processing status |
|
Returns a detailed status of the processing state, including current position and activity. |
Processing statistics |
|
Generates an interim statistical report to the report file, including the number of inserts, updates, and deletes since the last report (default) or according to report options that can be entered. See Reference for Oracle GoldenGate on HP NonStop Guardian for details on the |
TCP/IP statistics |
|
Retrieves TCP/IP statistics, such as the quantity and byte length of inbound and outbound messages, the number of messages received and sent, wait times, process CPU time, and byte transmit averages. Time accumulates when Extract is waiting on a socket send or receive and all times are reported in microseconds. Resets the TCP/IP statistics so the next report displays fresh statistics |
RMTTRAIL $DATA10.LOGGER.R1000038, RBA 5348453 Session Index 1 Stats started 2011/01/10 11:46:18.804165 0:00:41.522086 Local address 192.0.2.2:1000 Remote address 192.0.2.2:1000 Inbound Msgs 199 Bytes 2337, 57 bytes/second Outbound Msgs 200 Bytes 5389492, 131451 bytes/second Recvs 199 Sends 200 Avg bytes per recv 11, per msg 11 Avg bytes per send 26947, per msg 26947 Recv Wait Time 17592208, per msg 88403, per recv 88403 Send Wait Time 774603, per msg 3873, per send 3873 Process CPU Time 0:00:07.715372
Note:
In Oracle GoldenGate for NonStop, several additional reporting options are available. For specifics, see Reference for Oracle GoldenGate on HP NonStop Guardian.
To view a process report, view the file directly from the operating system's command shell, or use the VIEW REPORT
command in GGSCI. You also can view process reports from the Activity Console by clicking More Info
beneath the name of the process.
By default, process reports are stored in the GGSRPT
subvolume of the Oracle GoldenGate installation volume. You can designate an alternative file name by using the REPORT
option of the ADD EXTRACT
and ADD REPLICAT
commands when you create the group from the GGSCI interface. Specify the fully qualified file name.
Once paired with Extract or Replicat, the report file may remain in its original location, or you may change its location with the ALTER
command, such as:
ALTER EXTRACT group_name REPORT filename
Whenever a process starts, a new report file is created, and a sequence number is appended to the name of the old file. The naming sequence goes from no sequence number (current), to 0 (the most recent) to 9 (the oldest), for example: $DATA.GGSRPT.EXTORD, $DATA.GGSRPT.EXTORD0, $DATA.GGSRPT.EXTORD1
and so forth. When the file number reaches nine, the oldest file is deleted to make room for a new file, so there are never more than 11 files on the system at one time (the current report plus the ten aged reports).
To prevent the size of the report file from becoming too large, use the REPORTROLLOVER
parameter in the Extract and Replicat parameter files. This parameter forces the report files to age on a regular schedule. Options are available to age the current file on a specific day and/or a specific time.
To minimize the impact of errors on the size of the Replicat report file, use the WARNRATE
parameter in the Replicat parameter file. This parameter conserves the size of the report file and the event log by issuing a warning only after a specific number of errors have been generated, instead of after each one. This parameter is useful if you expect a certain number of errors and can tolerate them. The default for this parameter is to warn after 100 errors.
Run-time statistics show the current state of processing. By default, run-time statistics are written to the existing process report at the end of each run. To control when run-time statistics are generated, use the REPORT
parameter. This parameter has options for controlling the day and time that statistics are generated.
To generate interim run-time statistics, use the SEND EXTRACT
or SEND REPLICAT
GGSCI command with the REPORT
option syntax as shown below.
GGSCI> SEND {EXTRACT|REPLICAT} group_name REPORT [time_option [RESET | FILE filename | TABLE name]]
The time_option controls the time span covered by the report, such as since the start of Extract or since the last report request. RESET
sets the counters for that time_option to zero. FILE
or TABLE
limits the report to counts for name. For detail on these options see SEND
REPORT
in the Reference for Oracle GoldenGate on HP NonStop Guardian.
To generate run-time statistics and also cause the report file to roll over to a new one, add the ROLLREPORT
option to the command, for example:
GGSCI> SEND EXTRACT EXTORD, REPORT GGSCI> SEND EXTRACT EXTORD, ROLLREPORT
You can produce record counts at designated times during processing by using the REPORTCOUNT
parameter in the Extract and Replicat parameter files. Results are printed to the process report file and to screen.
The record count shows the number of records extracted and replicated since the Extract or Replicat process started. Counts can be obtained at regular intervals or each time a specific number of records is processed.
To generate a statistical report for Extract or Replicat, specify the LAGSTATS
parameter. Oracle GoldenGate measures lag in bytes and time:
Lag in bytes is the difference between the position of the Extract program in the source at the time of the last checkpoint, and the current end-of-file. A lag value of UNKNOWN
indicates that the process may have recently started and hasn't yet processed records, or that the source system's clock may be ahead of the target system's clock due to a reason other than time zone differences.
Time lag reflects the lag in seconds at the time the last checkpoint was written. For example, if it is now 15:00:00, the last checkpoint was at 14:59:00 and the timestamp of the last record processed by the Replicat program was 14:58:00, the lag is reported as 00:01:00 (one minute, the difference between 14:58 and 14:59).
The report includes the following general performance categories:
General statistics
Lag statistics
Extract's processing in the Oracle GoldenGate trail
Audit trail reading statistics for Extract (when applicable)
Output statistics for Extract only
The following table describes each item in the lag statistics report.
Item | Description |
---|---|
Last Record Timestamp |
The timestamp of the source record (when the source record was input or committed to the target database). |
Configured Interval |
Determined by the |
Actual Duration |
The duration of time measured. |
Records Processed |
Number of records output or replicated during the period. |
Records per Second |
Records processed per second during the interval. |
Source Records per Second |
The estimated rate of records read for either the TMF audit trails or logger processes. |
Last lag |
The time lag of the last record measured between (1) the records update into the source database and (2) the actual processing of the record by Extract or Replicat. |
Min lag |
Smallest value of Last lag during the interval. |
Average lag |
Average time lag calculated by adding the maximum lag during the interval to the minimum lag and dividing the result by 2. |
Peak lag |
Peak time lag during the interval for all records processed and the timestamp of the peak. |
Last est. record lag |
An estimate of the number of records the component is behind the current record. |
Pct Below Lag of mi:ss:mmm |
The percentage of times lag was below the time threshold you specified. This is an optional statistic, which can occur up to five times. To generate the statistic, specify the |
Pct CPU Busy |
The amount of time the CPU in which Extract or Replicat was running was busy during the interval. |
PCT Process Busy |
The amount of time Extract or Replicat was busy during the interval. |
At EOF? |
Whether more data was available to process the last time more data was requested by Extract or Replicat from the audit or Oracle GoldenGate trails. |
Trail Reads per Second |
When reading Oracle GoldenGate trails, the number of attempted block reads per second. |
Bytes per Trail Read |
When reading Oracle GoldenGate trails, the number of bytes read per successful read. |
Records per Block Read |
When reading Oracle GoldenGate trails, the number of records read per successful read. This indicates the blocking factor on input. |
Wait per Block Read |
When reading Oracle GoldenGate trails, the amount of time Extract or Replicat waits, on average, to complete the read. |
Audit Bytes per Second |
The number of bytes of audit processed per second (TMF Extract only). |
Pct EOF Trail Reads |
For TMF Extract, the percentage of times Extract reached the end of file, compared with the number of records processed. For Replicat or Extract reading Oracle GoldenGate trails, the number of times the process read at the end of file, compared with the total number of blocks it attempted to read. |
Transactions per Second |
For TMF Extract, the number of transactions processed per second. |
Transactions Aborted |
For TMF Extract, the number of transactions aborted during the interval. |
Audit positions |
The number of times during the interval that Extract requested Audserv to position for read. |
Audit position seconds |
The elapsed time in seconds required for Audserv to position for read. |
Audserv requests |
The number of data requests to Audserv during the interval. |
Audserv request wait seconds |
The elapsed time for the Audserv to fulfill data requests during the interval. |
Long transactions |
The number of long transactions during the interval. |
Long transaction seconds |
The elapsed time for the long transactions that occurred during the interval. |
Output Bytes per Second |
For Extract, the bytes of data output to the extract trails per second. |
Output Blocks per Second |
For Extract, the number of blocks of data written to the Oracle GoldenGate trails per second. |
Records per Block Written |
For Extract, the average number of records in each block written. |
Bytes per Block Written |
For Extract, the average number of bytes in each block written. |
Wait per Block Written |
For Extract, the amount of time waiting for the last write or TCP/IP send to complete before sending the next block of data. This statistic can indicate whether the network might be introducing a delay to Extract. |
Average Record Flush Delay |
For Extract, the estimated average amount of time a record was held in its buffers before flushing. |
Pct Output/Input |
For Extract, the ratio of bytes output compared with input bytes processed. Useful for estimating potential bandwidth required by Extract based on amount of TMF audit generated. |
Event messages created by the Collector and Replicat on Windows and UNIX systems can be captured and sent back to EMS on NonStop systems. This feature enables centralized viewing of Oracle GoldenGate messages across platforms.
To collect events from other systems:
Run Collector on NonStop to collect and distribute EMS messages. For each EMSCLNT process, run one Collector process. The following example runs Collector and outputs its messages to $0
.
TACL> ASSIGN STDERR, $0 TACL> RUN SERVER /NOWAIT/ –p 7880
Run the EMSCLNT utility on the remote target. EMSCLNT reads a designated error log and runs indefinitely, waiting for more messages to send. When EMSCLNT receives a message, it sends the message to a TCP/IP collector process on NonStop. See the examples for running EMSCLNT on other operating systems for syntax information.
This Unix example reads the file ggslog.err
for error messages. Error messages are sent to the collector to the NonStop at IP address 192.0.2.2
listening on port 7850
. The Collector on NonStop writes formatted messages to EMS Collector $0
.
> $emsclnt –h 192.0.2.2 –p 7850 –f ggserr.log –c $0
This Windows example (from the DOS prompt) reads the file d:\ggserrs\log.txt
for error messages. Error messages are sent to the Collector on host ggs2
listening on port 9876
. The Collector on NonStop writes formatted messages to EMS Collector $P0
.
> emsclnt –h ggs2 –p 9876 –f c:\ggs\ggserr.log –c $P0
Argument | Description |
---|---|
–h ggs2 |
The node on which the collector is being run. Can be a name or IP address. This is a required parameter. |
–p 9876 |
The port at which the collector is listening for messages. This is a required parameter. |
–f c:\ggs\ggserr.log |
The error file from which EMSCLNT retrieves error messages. This is a required parameter. |
–c $P0 |
The collector to which EMS messages should be written on the NonStop (default is $0). |