Topics:
Application patches and application upgrades typically perform DDL such as adding new objects or changing existing objects. To apply applications patches or upgrades in an Oracle GoldenGate environment, you can do one of the following:
If Oracle GoldenGate supports DDL replication for your database type, you can use it to replicate the DDL without stopping replication processes. To use this method, the source and target table structures must be identical.
You can apply the patch or upgrade manually on both source and target after taking the appropriate steps to ensure replication continuity.
To Use Oracle GoldenGate to Replicate Patch DDL
If you have not already done so, dedicate some time to learn, install, and configure the Oracle GoldenGate DDL support. See the instructions for your database in this documentation. Once the DDL environment is in place, future patches and upgrades will be easier to apply.
If the application patch or upgrade adds new objects that you want to include in data replication, make certain that you include them in the DDL
parameter statement. To add new objects to your TABLE
and MAP
statements, see the procedure on Adding Tables to the Oracle GoldenGate Configuration.
If the application patch or upgrade installs triggers or cascade constraints, disable those objects on the target to prevent collisions between DML that they execute on the target and the same DDL that is replicated from the source trigger or cascaded operation.
To Apply a Patch Manually on the Source and Target
Stop access to the source database.
Allow Extract to finish capturing the transaction data that remains in the transaction log. To determine when Extract is finished, issue the following command in GGSCI until it returns At EOF.
SEND EXTRACT group GETLAG
Stop Extract.
STOP EXTRACT group
Start applying the patch on the source.
Wait until the data pump (if used) and Replicat are finished processing the data in their respective trails. To determine when they are finished, use the following commands until they return At EOF
.
SEND EXTRACT group GETLAG SEND REPLICAT group GETLAG
Stop the data pump and Replicat.
STOP EXTRACT group STOP REPLICAT group
At this point, the data in the source and target should be identical, because all of the replicated transactional changes from the source have been applied to the target.
Apply the patch on the target.
If the patches changed table definitions, run DEFGEN for the source tables to generate updated source definitions, and then replace the old definitions with the new ones in the existing source definitions file on the target system.
Start the Oracle GoldenGate processes whenever you are ready to begin capturing user activity again.
When you initialize a transaction log, you must ensure that all of the data is processed by Oracle GoldenGate first, and then you must delete and re-add the Extract group and its associated trail.
When shutting down a system for maintenance and other procedures that affect Oracle GoldenGate, follow these steps to make certain that Extract has processed all of the transaction log records. Otherwise, you might lose synchronization data.
This section addresses administrative operations that are performed on database tables and structures.
Adding Tables to the Oracle GoldenGate Configuration
Coordinating Table Attributes between Source and Target
Performing an ALTER TABLE to Add a Column on DB2 z/OS Tables
Dropping and Recreating a Source Table
Changing the Number of Oracle RAC Threads when Using Classic Capture
This procedure is required to prevent Replicat errors when changing the following metadata of the source database:
Database character set
National character set
Locale
Timezone
Object name case-sensitivity
If these changes are made without performing this procedure, the following error occurs:
2013-05-26 20:10:09 ERROR OGG-05500 Detected database metadata mismatch between current trail file ./dirdat/_p/v1000000003 and the previous sequence. *DBTIMEZONE: [GMT]/[UTC].
This procedure stops Extract, and then creates a new trail file. The new database metadata is included in this new file with the transactions that started after the change.
This procedure assumes that the Oracle GoldenGate DDL support feature is not in use, or is not supported for, your database.
Note:
For Oracle and Teradata databases, you can enable the DDL support feature of Oracle GoldenGate to automatically capture and apply the DDL that adds new tables, instead of using this procedure. See the appropriate instructions for your database in this documentation.
Review these steps before starting. The process varies slightly, depending on whether or not the new tables satisfy wildcards in the TABLE
parameter, and whether or not names or data definitions must be mapped on the target.
Prerequisites for Adding Tables to the Oracle GoldenGate Configuration
This procedure assumes that the source and target tables are either empty or contain identical (already synchronized) data.
You may be using the DBLOGIN
and ADD TRANDATA
commands. Before starting this procedure, see Reference for Oracle GoldenGate for the proper usage of these commands for your database.
To Add a Table to the Oracle GoldenGate Configuration
Follow this procedure if you are changing an attribute of a source table that is in the Oracle GoldenGate configuration, such as adding or changing columns or partitions, or changing supplemental logging details (Oracle). It directs you how to make the same change to the target table without incurring replication latency.
Note:
See also Performing an ALTER TABLE to Add a Column on DB2 z/OS Tables.
Note:
This procedure assumes that the Oracle GoldenGate DDL support feature is not in use, or is not supported for your database. For Oracle and Teradata databases, you can enable the DDL support feature of Oracle GoldenGate to propagate the DDL changes to the target, instead of using this procedure.
To add a fixed length column to a table that is in reordered row format and contains one or more variable length columns, one of the following will be required, depending on whether the table can be quiesced or not.
If the Table can be Quiesced
Allow Extract to finish capturing transactions that happened prior to the quiesce.
Alter the table to add the column.
Reorganize the tablespace.
Restart Extract.
Allow table activity to resume.
If the Table cannot be Quiesced
TABLE
statement in the parameter file.TABLE
statement.Dropping and recreating a source table requires caution when performed while Oracle GoldenGate is running.
Valid for Extract in classic capture mode for Oracle. When Extract operates in classic capture mode, the Extract group must be dropped and re-added any time the number of redo threads in an Oracle RAC cluster changes. To drop and add an Extract group, perform the following steps:
You can change the ORACLE_SID
and ORACLE_HOME
without having to change environment variables at the operating-system level. Depending on whether the change is for the source or target database, set the following parameters in the Extract or Replicat parameter files. Then, stop and restart Extract or Replicat for the parameters to take effect.
SETENV (ORACLE_HOME=location) SETENV (ORACLE_SID='SID')
An Oracle archive log can be purged safely once Extract's read and write checkpoints are past the end of that log. Extract does not write a transaction to a trail until it has been committed, so Extract must keep track of all open transactions. To do so, Extract requires access to the archive log where each open transaction started and all archive logs thereafter.
Extract reads the current archive log (the read checkpoint) for new transactions and also has a checkpoint (the recovery checkpoint) in the oldest archive log for which there is an uncommitted transaction.
Use the following command in GGSCI to determine Extract's checkpoint positions.
INFO EXTRACT group, SHOWCH
The Input Checkpoint
field shows where Extract began processing when it was started.
The Recovery Checkpoint
field shows the location of the oldest uncommitted transaction.
The Next Checkpoint
field shows the position in the redo log that Extract is reading.
The Output Checkpoint
field shows the position where Extract is writing.
You can write a shell script that purges all archive logs no longer needed by Extract by capturing the sequence number listed under the Recovery Checkpoint
field. All archive logs prior to that one can be safely deleted.
When using IBM's REORG utility to reorganize a DB2 table that has compressed tablespaces, specify the KEEPDICTIONARY
option if the table is being processed by Oracle GoldenGate. This prevents the REORG utility from recreating the compression dictionary, which would cause log data that was written prior to the change not to be decompressed and cause Extract to terminate abnormally. As an alternative, ensure that all of the changes for the table have been extracted by Oracle GoldenGate before doing the reorganization, or else truncate the table.
This section describes how to add process groups.
These instructions are for adding process groups to a configuration that is already active. The procedures should be performed by someone who has experience with Oracle GoldenGate. They involve stopping processes for a short period of time and reconfiguring parameter files. The person performing them must:
Know the basic components of an Oracle GoldenGate configuration
Understand Oracle GoldenGate parameters and commands
Have access to GGSCI to create groups and parameter files
Know which parameters to use in specific situations
Instructions are provided for:
This procedure splits the workload of an existing Extract group into multiple Extract groups. It also provides instructions for including a data pump group (if applicable) and a Replicat group to propagate data that is captured by the new Extract group.
Steps are performed on the source and target systems.
Make certain the archived transaction logs are available in case the online logs recycle before you complete this procedure.
Choose a name for the new Extract group.
Decide whether or not to use a data pump.
On the source system, run GGSCI.
Create a parameter file for the new Extract group.
EDIT PARAMS group
Note:
You can copy the original parameter file to use for this group, but make certain to change the Extract group name and any other relevant parameters that apply to this new group.
In the parameter file, include:
EXTRACT
parameter that specifies the new group.
Appropriate database login parameters.
Other appropriate Extract parameters for your configuration.
EXTTRAIL
parameter that points to a local trail (if you will be adding a data pump) or a RMTTRAIL
parameter (if you are not adding a data pump).
RMTHOST
parameter if this Extract will write directly to a remote trail.
TABLE
statement(s) (and TABLEEXCLUDE
, if appropriate) for the tables that are to be processed by the new group.
Save and close the file.
Edit the original Extract parameter file(s) to remove the TABLE
statements for the tables that are being moved to the new group or, if using wildcards, add the TABLEEXCLUDE
parameter to exclude them from the wildcard specification.
(Oracle) If you are using Extract in integrated mode, register the new Extract group with the source database.
REGISTER EXTRACT group DATABASE [CONTAINER (container[, ...])]
Lock the tables that were moved to the new group, and record the timestamp for the point when the locks were applied. For Oracle tables, you can run the following script, which also releases the lock after it is finished.
-- temp_lock.sql -- use this script to temporary lock a table in order to -- get a timestamp lock table &schema . &table_name in EXCLUSIVE mode; SELECT TO_CHAR(sysdate,'MM/DD/YYYY HH24:MI:SS') "Date" FROM dual; commit;
Unlock the table(s) if you did not use the script in the previous step.
Stop the old Extract group(s) and any existing data pumps.
STOP EXTRACT group
Add the new Extract group and configure it to start at the timestamp that you recorded.
ADD EXTRACT group, TRANLOG, BEGIN YYYY/MM/DD HH:MI:SS:CCCCCC
Add a trail for the new Extract group.
ADD {EXTTRAIL | RMTTRAIL} trail, EXTRACT group
Where:
EXTTRAIL
creates a local trail. Use this option if you will be creating a data pump for use with the new Extract group. Specify the trail that is specified with EXTTRAIL
in the parameter file. After creating the trail, go To Link a Local Data Pump to the New Extract Group.
RMTTRAIL
creates a remote trail. Use this option if a data pump will not be used. Specify the trail that is specified with RMTTRAIL
in the parameter file. After creating the trail, go To Link a Remote Replicat to the New Data Pump
You can specify a relative or full path name. Examples:
ADD RMTTRAIL dirdat/rt, EXTRACT primary ADD EXTTRAIL c:\ogg\dirdat\lt, EXTRACT primary
To Link a Local Data Pump to the New Extract Group
On the source system, add the data-pump Extract group using the EXTTRAIL
trail as the data source.
ADD EXTRACT pump, EXTTRAILSOURCE trail
For example:
ADD EXTRACT pump2, EXTTRAILSOURCE dirdat\lt
Create a parameter file for the data pump.
EDIT PARAMS pump
In the parameter file, include the appropriate Extract parameters for your configuration, plus:
RMTHOST
parameter to point to the target system.
RMTTRAIL
parameter to point to a new remote trail (to be specified later).
TABLE
parameter(s) for the tables that are to be processed by this data pump.
In GGSCI on the source system, add a remote trail for the data-pump. Use the trail name that you specified with RMTTRAIL
in the parameter file.
ADD RMTTRAIL trail, EXTRACT pump
For example:
ADD RMTTRAIL dirdat/rt, EXTRACT pump2
Follow the steps in To Link a Remote Replicat to the New Data Pump.
To Link a Remote Replicat to the New Data Pump
This procedure adds a data-pump Extract group to an active primary Extract group on the source system. It makes these changes:
The primary Extract will write to a local trail.
The data pump will write to a new remote trail after the data in the old trail is applied to the target.
The old Replicat group will be replaced by a new one.
Steps are performed on the source and target systems.
This procedure adds a new Replicat group to an existing Replicat group. The new Replicat reads from the same trail as the original Replicat.
Multiple Replicat groups may be required when Replicat is configured in classic mode, for the purpose of isolating transactions on certain tables or improving performance. Multiple Replicat groups usually are not required if using coordinated Replicat, because you can divide the workload among multiple processing threads within the same Replicat group. See Creating an Online Replicat Group for more information about Replicat modes.
Steps are performed on the source and target systems.
You can change the size of trail files with the MEGABYTES
option of either the ALTER EXTTRAIL
or ALTER RMTTRAIL
command, depending on whether the trail is local or remote. To change the file size, follow this procedure.
Valid for Oracle only.
This procedure switches an existing Extract group from classic mode to integrated mode. For more information about Extract modes for an Oracle database, see Choosing Capture and Apply Modes in Using Oracle GoldenGate for Oracle Database.
To support the transition to integrated mode, the transaction log that contains the start of the oldest open transaction must be available on the source or downstream mining system, depending on where Extract will be running.
To determine the oldest open transaction, issue the SEND EXTRACT
command with the SHOWTRANS
option. You can use the FORCETRANS
or SKIPTRANS
options of this command to manage specific open transactions, with the understanding that skipping a transaction may cause data loss and forcing a transaction to commit to the trail may add unwanted data if the transaction is rolled back by the user applications. Review these options in SEND EXTRACT
Reference for Oracle GoldenGatebefore using them.
GGSCI> SEND EXTRACT group, SHOWTRANS� GGSCI> SEND EXTRACT group, { SKIPTRANS ID [THREAD n] [FORCE] |� FORCETRANS ID [THREAD n] [FORCE] }�
To Switch Extract Modes
Valid for Oracle only.
This procedure switches an existing Extract group from integrated mode to classic mode. For more information about Extract modes for an Oracle database, see Choosing Capture and Apply Modesin Using Oracle GoldenGate for Oracle Database.
To support the transition to classic mode, the transaction log that contains the start of the oldest open transaction must be available on the source or downstream mining system. To determine the oldest open transaction, issue the SEND EXTRACT
command with the SHOWTRANS
option. You can use the FORCETRANS
or SKIPTRANS
options of this command to manage specific open transactions, with the understanding that skipping a transaction may cause data loss and forcing a transaction to commit to the trail may add unwanted data if the transaction is rolled back by the user applications. Review these options in Oracle GoldenGate Parametersin Reference for Oracle GoldenGatebefore using them.
GGSCI> SEND EXTRACT group, SHOWTRANS� GGSCI> SEND EXTRACT group, { SKIPTRANS ID [THREAD n] [FORCE] |� FORCETRANS ID [THREAD n] [FORCE] }�
To Switch Extract Modes
Valid for Oracle only. For more information about Replicat modes for an Oracle database, see Choosing Capture and Apply Modes in Using Oracle GoldenGate for Oracle Database.
This procedure switches an existing Replicat group from nonintegrated to integrated mode.
Note:
Do not configure the switch between Replicat modes to occur immediately after Extract recovers from a failure or is repositioned to a different location in the transaction log.
When you start Replicat in integrated mode for the first time, the START
command registers the Replicat group with the database and starts an inbound server to which Replicat attaches. When you convert a Replicat group to integrated mode, the use of the Oracle GoldenGate checkpoint table is discontinued and recovery information is maintained internally by the inbound server and by the checkpoint file going forward. You can retain the checkpoint table in the event that you decide to switch back to nonintegrated mode.
Valid for Oracle only. For more information about Replicat modes for an Oracle database, see About Integrated Replicat in Using Oracle GoldenGate for Oracle Database.
You can, at any time, switch Replicat from integrated mode to nonintegrated mode. This switch automatically unregisters the Replicat group from the target database, which removes the inbound server.
Note:
Do not configure the switch between Replicat modes to occur immediately after Extract recovers from a failure or is repositioned to a different location in the transaction log.
Bug 17079228
Valid for all database types supported by Oracle GoldenGate.
This procedure upgrades a regular Replicat configuration (non-coordinated) to a coordinated configuration. This procedure assumes you are replacing a configuration that partitions data across multiple Extract and Replicat processes with a configuration that uses one Extract and one coordinated Replicat. The coordinated Replicat replaces the need for using multiple Replicat processes. A coordinated Replicat requires only one trail, so there is no need for multiple Extract processes or data pumps.
See Configuring Online Change Synchronization for more information about coordinated Replicat.
This procedure makes use of the EVENTACTIONS
parameter with a STOP
action, which enables all of the Replicat processes to stop at the same point in the trail. The EVENTACTIONS
action is triggered by a transaction that contains an INSERT
to a dummy table. The INSERT
causes each process to finish processing everything up to, and including, the event transaction and then stop cleanly. An additional event action of IGNORE
is specified for Replicat to prevent the multiple Replicat processes from attempting to insert the same record to the target. The result of this procedure is that all processes stop at the same point in the data stream: after completing the INSERT
transaction to the dummy table.
After the processes stop, you move all of the TABLE
statements to one primary Extract group. You move the same TABLE
statements to the data pump that reads the trail of the Extract group that you retained. You move all of the MAP
statements to a new coordinated Replicat group that reads the remote trail that is associated with the retained data pump. Once all of the MAP
statements are in one parameter file, you edit them to add the thread specifications to support a coordinated Replicat. (This can be done ahead of time.) Then you drop the Replicat group and add it back in coordinated mode with the same name.
This section contains instructions for coordinating threads and re-partitioning the workload among new or different threads. A coordinated Replicat should be stopped cleanly with the STOP REPLICAT
command before making modifications to the partition specifications in THREAD
or THREADRANGE
clauses of the MAP
statements. A clean stop ensures that all of the threads, which may be at different locations in the trail at any given point, all finish their work and arrive at a common trail location.
At startup, Replicat issues an error and abends if it detects that the last shutdown was not clean and the partitioning in the MAP
statements was changed to contain a different number of threads (threads were added or removed). However, if the same threads are kept in the parameter file but simply rearranged among different MAP
statements, Replicat issues a warning but does not abend. This can result in missing or duplicate records, because there is no way to ensure continuity of the thread-to-workload allocations from the previous run.
The following is an example of this condition.
Following is the original partitioning scheme:
MAP source, target, THREADRANGE(1-5); MAP source1, target1, THREADRANGE(6-10);
The following re-partitioning of the original scheme produces only a warning:
MAP source, target, THREADRANGE(1-4); MAP source1, target1, THREADRANGE(5-10);
This section provides instructions for cleanly shutting down Replicat before performing a re-partitioning, as well as instructions for attempting to recover Replicat continuity when a re-partitioning is performed after an unclean shutdown.
The following tasks can be performed for a Replicat group in coordinated mode.
Performing a Planned Re-partitioning of the Workload
A planned re-partitioning is when Replicat is allowed to shut down cleanly before it is started again with a new parameter file that contains updated thread partitioning. A clean shutdown enables all of the threads to arrive at a common checkpoint position in the trail. At that point, the new partitioning scheme can be applied in the next run. If Replicat does not shut down cleanly in this procedure, for example if there is an apply error, use the procedure in Synchronizing Threads After an Unclean Stop to re-synchronize the threads before you re-partition them.
An unplanned re-partitioning is when Replicat is not allowed to shut down cleanly before it is started again with a new parameter file that contains updated thread partitioning. In this scenario, some or all of the old threads were not able to finish their work and arrive at a common checkpoint. Upon restart, the coordinator thread attempts to apply the old partitioning scheme, and Replicat abends with an error. You can recover the coordinated Replicat group from this condition in one of the following ways:
Use the auto-saved copy of the parameter file
Reprocess from the low watermark with HANDLECOLLISIONS
In this procedure, you reposition all of the threads to the low watermark position. This is the earliest checkpoint position performed among all of the threads. To state it another way, the low watermark position is the last record processed by the slowest thread before the unclean stop. When you start Replicat, the threads reprocess the operations that they were processing before Replicat stopped, and the HANDLECOLLISIONS
parameter handles any duplicate-record and missing-record errors that occur as the faster threads reprocess operations that they applied before the unclean stop.
A copy of the original parameter file is saved whenever the parameter file is edited before shutting down Replicat cleanly. You can revert to this parameter file and then resynchronize the threads so that they all catch up to the thread that had the most recent checkpoint position. Once the threads are synchronized, you can switch to the new parameter file and then start Replicat.
When a Replicat group stops in an unclean manner, not all of the threads will reach a common checkpoint position in the trail. Unclean stops can be caused by issuing STOP REPLICAT
with the !
option, issuing the KILL REPLICAT
command, or by transient errors related to Replicat, the database, or other local processes. You can restore the threads to the same position in the trail after an unclean stop and then start Replicat again from the correct checkpoint position.
In this procedure, the restore position is the high watermark. This is the most recent checkpoint position performed among all of the threads (the last record processed by the fastest thread before the unclean stop). Before starting Replicat, you can make changes to the parameter file, such as to repartition the workload among different or new threads. The repartitioning takes effect in a seamless manner after you start Replicat, because the threads can start from a synchronized state.
This procedure enables Oracle GoldenGate to recover from certain conditions, such as a file system corruption or a system failure, that corrupt the Extract checkpoint file, trail, or both, and which prevent Extract from being able to start. It enables you to establish a safe starting point in the transaction log for the primary Extract after the system has been restored. It also shows you how to reposition downstream data pumps and Replicat to read from the correct Extract write position in the trails, and to filter out any transactions that Replicat already applied to the target.
Extract passes a log begin sequence number, or LOGBSN, to the trail files. The BSN is the native database sequence number that identifies the oldest uncommitted transaction that is held in Extract memory. For example, the BSN in an Oracle installation would be the Oracle system change number (SCN). Each trail file contains the lowest LOGBSN
value for all of the transactions in that trail file. Once you know the LOGBSN
value, you can reposition Extract at the correct read position to ensure that the appropriate transactions are re-generated to the trail and propagated to Replicat.
Note:
In an Oracle RAC environment, the lowest SCN of all of the threads is transmitted to Replicat. Transactions that may already have been committed by Replicat are handled as duplicates at startup. However, any thread that has been idle past a certain threshold will not be considered for the BSN value, to avoid Extract having to read too far back in the log stream when restarted.
The bounded recovery checkpoint is not taken into account when calculating the LOGBSN
. The failure that affected the Extract checkpoint file may also involve a loss of the persisted bounded recovery data files and bounded recovery checkpoint information.
Follow these steps in the order shown to recover the Oracle GoldenGate processes.
Note:
The LOGBSN
gives you the information needed to set Extract back in time to reprocess transactions. Some filtering by Replicat is necessary because Extract will likely re-generate a small amount of data that was already applied by Replicat. FILTERDUPTRANSACTIONS
directs Replicat to find and filter duplicates at the beginning of the run.