10 Managing and Monitoring

10.2.1.1 To estimate the required trail space

1. Estimate the longest time that you think the network can be unavailable.

2. Estimate how much transaction log volume you generate in one hour.

3. 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:

4. 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.

5. 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.

10.2.2.1 Setting the Size of the Trail

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.

GGSCI> INFO EXTTRAIL GGSDAT.ET
Extract file: \NY.$DATA04.GGSDAT.ET
       Extract group: EXTSQL
               Owner: 150,110
            Security: NUNU
       Current seqno: 0
         Current rba: 2280
      Primary extent: 64
    Secondary extent: 128
         Max extents: 512
           Max files: 100

10.2.2.2 Setting the PURGEOLDEXTRACTS rules

You can set PURGEOLDEXTRACT in the Manager, Extract, or Replicat, but only Manager has options.

In the Manager

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.

In Extract or Replicat

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.

10.2.2.3 Manager Purge Trail Processing

If PURGEOLDEXTRACTS is set in the Manager parameter file, when the Manager reaches CHECKMINUTES the purge rules are evaluated as explained below.

1. 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.

2. 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.

3. 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.

4. 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.

10.2.2.4 Recommendations for Managing Trail Purges

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
  

10.5.3.1 Altering TCP/IP Error Handling Parameters

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.

10.5.4.1 Using the Discard File

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.

Generating Discard Files

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)

To specify a non-default location or characteristic use the DISCARDFILE parameter in the Replicat parameter file.

10.5.4.2 Using the SQL Formatted Discard 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.

SQL DIscard File Example

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

Editing the SQL Discard File

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.

10.5.5.1 A SQLEXEC Example

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.

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:

ERROR REPORT

ERROR RAISE

ERROR FINAL

ERROR FATAL

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.