Documentation
Advanced Search


Administering Oracle GoldenGate for Windows and UNIX

20 Performing Administrative Operations

This chapter contains instructions for making changes to applications, systems, and Oracle GoldenGate while the replication environment is active and processing data changes.

This chapter includes the following sections:

20.1 Performing Application Patches

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

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

  2. 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 Section 20.4.2, "Adding Tables to the Oracle GoldenGate Configuration".

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

  1. Stop access to the source database.

  2. 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
    
  3. Stop Extract.

    STOP EXTRACT group
    
  4. Start applying the patch on the source.

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

  7. Apply the patch on the target.

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

  9. Start the Oracle GoldenGate processes whenever you are ready to begin capturing user activity again.

20.2 Initializing the Transaction Logs

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.

  1. Stop the application from accessing the database. This stops more transaction data from being logged.

  2. Run GGSCI and issue the SEND EXTRACT command with the LOGEND option for the primary Extract group. This command queries Extract to determine whether or not Extract is finished processing the records that remain in the transaction log.

    SEND EXTRACT group LOGEND
    
  3. Continue issuing the command until it returns a YES status, indicating that there are no more records to process.

  4. On the target system, run GGSCI and issue the SEND REPLICAT command with the STATUS option. This command queries Replicat to determine whether or not it is finished processing the data that remains in the trail.

    SEND REPLICAT group STATUS
    
  5. Continue issuing the command until it shows 0 records in the current transaction, for example:

    Sending STATUS request to REPLICAT REPSTAB...
    Current status:
      Seqno 0, Rba 9035
      0 records in current transaction.
    
  6. Stop the primary Extract group, the data pump (if used), and the Replicat group.

    STOP EXTRACT group
    STOP EXTRACT pump_group
    STOP REPLICAT group
    
  7. Delete the Extract, data pump, and Replicat groups.

    DELETE EXTRACT group
    DELETE EXTRACT pump_group
    DELETE REPLICAT group
    
  8. Using standard operating system commands, delete the trail files.

  9. Stop the database.

  10. Initialize and restart the database.

  11. Recreate the primary Extract group.

    ADD EXTRACT group TRANLOG, BEGIN NOW
    
  12. Recreate the local trail (if used).

    ADD EXTTRAIL trail, EXTRACT group
    
  13. Recreate the data pump (if used).

    ADD EXTRACT pump_group, EXTTRAILSOURCE trail
    
  14. Recreate the remote trail.

    ADD RMTTRAIL trail, EXTRACT pump_group
    
  15. Recreate the Replicat group.

    ADD REPLICAT group, EXTTRAIL trail
    
  16. Start Extract, the data pump (if used), and Replicat.

    START EXTRACT group 
    START EXTRACT pump_group
    START REPLICAT group
    

20.3 Shutting Down the System

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.

  1. Stop all application and database activity that generates transactions that are processed by Oracle GoldenGate.

  2. Run GGSCI.

  3. In GGSCI, issue the SEND EXTRACT command with the LOGEND option. This command queries the Extract process to determine whether or not it is finished processing the records in the data source.

    SEND EXTRACT group LOGEND
    
  4. Continue issuing the command until it returns a YES status. At that point, all transaction log data has been processed, and you can safely shut down Oracle GoldenGate and the system.

20.4 Changing Database Attributes

This section addresses administrative operations that are performed on database tables and structures.

Section 20.4.1, "Changing Database Metadata"

Section 20.4.2, "Adding Tables to the Oracle GoldenGate Configuration"

Section 20.4.3, "Coordinating Table Attributes between Source and Target"

Section 20.4.4, "Performing an ALTER TABLE to Add a Column on DB2 z/OS Tables"

Section 20.4.5, "Dropping and Recreating a Source Table"

Section 20.4.6, "Changing the Number of Oracle RAC Threads when Using Classic Capture"

Section 20.4.7, "Changing the ORACLE_SID"

Section 20.4.8, "Purging Archive Logs"

Section 20.4.9, "Reorganizing a DB2 Table (z/OS Platform)"

20.4.1 Changing Database Metadata

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/v1000003 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.

  1. Stop transaction activity on the source database. Do not make the metadata change to the database yet.

  2. In GGSCI on the source system, issue the SEND EXTRACT command with the LOGEND option until it shows there is no more redo data to capture.

    SEND EXTRACT group LOGEND
    
  3. Stop Extract.

    STOP EXTRACT group
    
  4. On each target system, issue the SEND REPLICAT command with the STATUS option until it shows a status of "At EOF" to indicate that it finished processing all of the data in the trail. This must be done on all target systems until all Replicat processes return "At EOF."

    SEND REPLICAT group STATUS
    
  5. Stop the data pumps and Replicat.

    STOP EXTRACT group
    STOP REPLICAT group
    
  6. Change the database metadata.

  7. In in GGSCI on the source system, issue the ALTER EXTRACT command with the ETROLLOVER option for the primary Extract to roll over the local trail to the start of a new file.

    ALTER EXTRACT group, ETROLLOVER
    
  8. Issue the ALTER EXTRACT command with the ETROLLOVER option for the data pumps to roll over the remote trail to the start of a new file.

    ALTER EXTRACT pump, ETROLLOVER
    
  9. Start Extract.

    START EXTRACT group
    
  10. In GGSCI, reposition the data pumps and Replicat processes to start at the new trail sequence number.

    ALTER EXTRACT pump, EXTSEQNO seqno, EXTRBA RBA
    ALTER REPLICAT group, EXTSEQNO seqno, EXTRBA RBA
    
  11. Start the data pumps.

    START EXTRACT group
    
  12. Start the Replicat processes.

    START REPLICAT group
    

20.4.2 Adding Tables to the Oracle GoldenGate Configuration

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 Windows and UNIX for the proper usage of these commands for your database.

To add a table to the Oracle GoldenGate configuration

  1. Stop user access to the new tables.

  2. (If new tables do not satisfy a wildcard) If you are adding numerous tables that do not satisfy a wildcard, make a copy of the Extract and Replicat parameter files, and then add the new tables with TABLE and MAP statements. If you do not want to work with a copy, then edit the original parameter files after you are prompted to stop each process.

  3. (If new tables satisfy wildcards) In the Extract and Replicat parameter files, make certain the WILDCARDRESOLVE parameter is not being used, unless it is set to the default of DYNAMIC.

  4. (If new tables do not satisfy a wildcard) If the new tables do not satisfy a wildcard definition, stop Extract.

    STOP EXTRACT group
    
  5. Add the new tables to the source and target databases.

  6. If required for the source database, issue the ADD TRANDATA command in GGSCI for the new tables. Before using ADD TRANDATA, issue the DBLOGIN command.

  7. Depending on whether the source and target definitins are identical or different, use either ASSUMETARGETDEFS or SOURCEDEFS in the Replicat parameter file. If SOURCEDEFS is needed, you can do either of the following:

    • Run DEFGEN, then copy the new definitions to the source definitions file on the target.

    • If the new tables match a definitions template, specify the template with the DEF option of the MAP parameter. (DEFGEN not needed.)

  8. To register the new source definitions or new MAP statements, stop and then start Replicat.

    STOP REPLICAT group
    START REPLICAT group
    
  9. Start Extract, if applicable.

    START EXTRACT group
    
  10. Permit user access to the new tables.

20.4.3 Coordinating Table Attributes between Source and Target

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:

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.

  1. On the source and target systems, create a table, to be known as the marker table, that can be used for the purpose of generating a marker that denotes a stopping point in the transaction log. Just create two simple columns: one as a primary key and the other as a regular column. For example:

    CREATE TABLE marker
    (
    id int NOT NULL,
    column varchar(25) NOT NULL,
    PRIMARY KEY (id)
    );
    
  2. Insert a row into the marker table on both the source and target systems.

    INSERT INTO marker VALUES (1, 1);
    COMMIT;
    
  3. On the source system, run GGSCI.

  4. Open the Extract parameter file for editing.

    Caution:

    Do not use the VIEW PARAMS or EDIT PARAMS command to view or edit an existing parameter file that is in a character set other than that of the local operating system (such as one where the CHARSET option was used to specify a different character set). View the parameter file from outside GGSCI if this is the case; otherwise, the contents may become corrupted..

  5. Add the marker table to the Extract parameter file in a TABLE statement.

    TABLE marker;
    
  6. Save and close the parameter file.

  7. Add the marker table to the TABLE statement of the data pump, if one is being used.

  8. Stop the Extract and data pump processes, and then restart them immediately to prevent capture lag.

    STOP EXTRACT group
    START EXTRACT group
    STOP EXTRACT pump_group
    START EXTRACT pump_group
    
  9. On the target system, run GGSCI.

  10. Open the Replicat parameter file for editing.

    Caution:

    Do not use the VIEW PARAMS or EDIT PARAMS command to view or edit an existing parameter file that is in a character set other than that of the local operating system (such as one where the CHARSET option was used to specify a different character set). View the parameter file from outside GGSCI if this is the case; otherwise, the contents may become corrupted.

  11. Add the marker table to the Replicat parameter file in a MAP statement, and use the EVENTACTIONS parameter as shown to stop Replicat and ignore operations on the marker table.

    MAP marker, TARGET marker, EVENTACTIONS (STOP, IGNORE);
    
  12. Save and close the parameter file.

  13. Stop, and then immediately restart, the Replicat process.

    STOP REPLICAT group
    START REPLICAT group
    
  14. When you are ready to change the table attributes for both source and target tables, stop all user activity on them.

  15. On the source system, perform an UPDATE operation to the marker table as the only operation in the transaction.

    UPDATE marker
    SET column=2,
    WHERE id=1;
    COMMIT;
    
  16. On the target system, issue the following command until it shows that Replicat is stopped as a result of the EVENTACTIONS rule.

    STATUS REPLICAT group
    
  17. Perform the DDL on the source and target tables, but do not yet allow user activity.

  18. Start Replicat.

    START REPLICAT group
    
  19. Allow user activity on the source and target tables.

20.4.4 Performing an ALTER TABLE to Add a Column on DB2 z/OS Tables

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

  1. Allow Extract to finish capturing transactions that happened prior to the quiesce.

  2. Alter the table to add the column.

  3. Reorganize the tablespace.

  4. Restart Extract.

  5. Allow table activity to resume.

If the table cannot be quiesced

  1. Stop Extract.

  2. Remove the table from the TABLE statement in the parameter file.

  3. Restart Extract.

  4. Alter the table to add the column.

  5. Reorganize the tablespace.

  6. Stop Extract.

  7. Add the table back to the TABLE statement.

  8. Resynchronize the source and target tables.

  9. Start Extract.

  10. Allow table activity to resume.

20.4.5 Dropping and Recreating a Source Table

Dropping and recreating a source table requires caution when performed while Oracle GoldenGate is running.

  1. Stop access to the table.

  2. Allow Extract to process any remaining changes to that table from the transaction logs. To determine when Extract is finished, use the INFO EXTRACT command in GGSCI.

    INFO EXTRACT group
    
  3. Stop Extract.

    STOP EXTRACT group
    
  4. Drop and recreate the table.

  5. If supported for this database, run the ADD TRANDATA command in GGSCI for the table.

  6. If the recreate action changed the source table's definitions so that they are different from those of the target, run the DEFGEN utility for the source table to generate source definitions, and then replace the old definitions with the new definitions in the existing source definitions file on the target system.

  7. Permit user access to the table.

20.4.6 Changing the Number of Oracle RAC Threads when Using Classic Capture

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:

  1. On the source and target systems, run GGSCI.

  2. Stop Extract and Replicat.

    STOP EXTRACT group
    STOP REPLICAT group
    
  3. On the source system, issue the following command to delete the primary Extract group and the data pump.

    DELETE EXTRACT group
    DELETE EXTRACT pump_group
    
  4. On the target system, issue the following command to delete the Replicat groups.

    DELETE REPLICAT group
    
  5. Using standard operating system commands, remove the local and remote trail files.

  6. Add the primary Extract group again with the same name as before, specifying the new number of RAC threads.

    ADD EXTRACT group TRANLOG, THREADS n, BEGIN NOW
    
  7. Add the local trail again with the same name as before.

    ADD EXTTRAIL trail, EXTRACT group
    
  8. Add the data pump Extract again, with the same name as before.

    ADD EXTRACT group EXTTRAILSOURCE trail, BEGIN NOW
    
  9. Add the remote trail again with the same name as before.

    ADD RMTTRAIL trail, EXTRACT group
    
  10. Add the Replicat group with the same name as before. Leave off any BEGIN options so that processing begins at the start of the trail.

    ADD REPLICAT group EXTTRAIL trail
    
  11. Start all processes, using wildcards as appropriate. If the re-created processes are the only ones in the source and target Oracle GoldenGate instances, you can use START ER * instead of the following commands.

    START EXTRACT group
    START REPLICAT group
    

20.4.7 Changing the ORACLE_SID

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')

20.4.8 Purging Archive Logs

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.

20.4.9 Reorganizing a DB2 Table (z/OS Platform)

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.

20.5 Adding Process Groups to an Active Configuration

This section describes how to add process groups.

20.5.1 Before You Start

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:

20.5.2 Adding Another Extract Group to an Active Configuration

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.

  1. Make certain the archived transaction logs are available in case the online logs recycle before you complete this procedure.

  2. Choose a name for the new Extract group.

  3. Decide whether or not to use a data pump.

  4. On the source system, run GGSCI.

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

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

  7. Save and close the file.

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

  9. (Oracle) If you are using Extract in integrated mode, register the new Extract group with the source database.

    REGISTER EXTRACT group DATABASE [CONTAINER (container[, ...])]
    
  10. 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;
    
  11. Unlock the table(s) if you did not use the script in the previous step.

  12. Stop the old Extract group(s) and any existing data pumps.

    STOP EXTRACT group
    
  13. 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
    
  14. Add a trail for the new Extract group.

    ADD {EXTTRAIL | RMTTRAIL} trail, EXTRACT group
    

    Where:

    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

  1. 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
    
  2. Create a parameter file for the data pump.

    EDIT PARAMS pump
    
  3. 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.

      Note:

      If the data pump will be pumping data, but not performing filtering, mapping, or conversion, then you can include the PASSTHRU parameter to bypass the overhead of database lookups. You also can omit database authentication parameters.

  4. 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
    
  5. Follow the steps in To link a remote Replicat to the new data pump.

To link a remote Replicat to the new data pump

  1. In GGSCI on the target system, add a Replicat group to read the remote trail. For EXTTRAIL, specify the same trail as in the RMTTRAIL Extract parameter and the ADD RMTTRAIL command.

    ADD REPLICAT group, EXTTRAIL trail
    

    For example:

    ADD REPLICAT rep2, EXTTRAIL /home/ggs/dirdat/rt
    
  2. Create a parameter file for this Replicat group. Use MAP statement(s) to specify the same tables that you specified for the new primary Extract and the data pump (if used).

  3. On the source system, start the Extract groups and data pumps.

    START EXTRACT group
    START EXTRACT pump
    
  4. On the target system, start the new Replicat group.

    START REPLICAT group
    

20.5.3 Adding Another Data Pump to an Active Configuration

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.

  1. On the source system, run GGSCI.

  2. Add a local trail, using the name of the primary Extract group for group.

    ADD EXTTRAIL trail, EXTRACT group
    

    For example:

    ADD EXTTRAIL dirdat\lt, EXTRACT primary
    
  3. Open the parameter file of the primary Extract group, and replace the RMTTRAIL parameter with an EXTTRAIL parameter that points to the local trail that you created.

    Caution:

    Do not use the VIEW PARAMS or EDIT PARAMS command to view or edit an existing parameter file that is in a character set other than that of the local operating system (such as one where the CHARSET option was used to specify a different character set). View the parameter file from outside GGSCI if this is the case; otherwise, the contents may become corrupted..

    Example EXTTRAIL parameter:

    EXTTRAIL dirdat\lt
    
  4. Remove the RMTHOST parameter.

  5. Save and close the file.

  6. Add a new data-pump Extract group, using the trail that you specified in step 2 as the data source.

    ADD EXTRACT group, EXTTRAILSOURCE trail
    

    For example:

    ADD EXTRACT pump, EXTTRAILSOURCE dirdat\lt
    
  7. Create a parameter file for the new data pump.

    EDIT PARAMS group
    
  8. In the parameter file, include the appropriate Extract parameters for your configuration, plus:

    • TABLE parameter(s) for the tables that are to be processed by this data pump.

    • RMTHOST parameter to point to the target system.

    • RMTTRAIL parameter to point to a new remote trail (to be created later).

      Note:

      If the data pump will be pumping data, but not performing filtering, mapping, or conversion, you can include the PASSTHRU parameter to bypass the overhead of database lookups. You also can omit database authentication parameters.

  9. In GGSCI on the source system, add a remote trail for the data-pump. Use the trail name that is specified with RMTTRAIL in the data pump's parameter file, and specify the group name of the data pump for EXTRACT.

    ADD RMTTRAIL trail, EXTRACT group
    

    For example:

    ADD RMTTRAIL dirdat/rt, EXTRACT pump
    

    Note:

    This command binds a trail name to an Extract group but does not actually create the trail. A trail file is created when processing starts.

  10. On the target system, run GGSCI.

  11. Add a new Replicat group and link it with the remote trail.

    ADD REPLICAT group, EXTTRAIL trail
    

    For example:

    ADD REPLICAT rep, EXTTRAIL dirdat/rt
    
  12. Create a parameter file for this Replicat group. You can copy the parameter file from the original Replicat group, but make certain to change the REPLICAT parameter to the new group name.

  13. On the source system, stop the primary Extract group, then start it again so that the parameter changes you made take effect.

    STOP EXTRACT group
    START EXTRACT group
    
  14. On the source system, start the data pump.

    START EXTRACT group
    
  15. On the target system, issue the LAG REPLICAT command for the old Replicat, and continue issuing it until it reports At EOF, no more records to process.

    LAG REPLICAT group
    
  16. Stop the old Replicat group.

    STOP REPLICAT group
    
  17. If using a checkpoint table for the old Replicat group, log into the database from GGSCI.

    DBLOGIN [SOURCEDB datasource] [{, USERIDALIAS alias | USERID user [,options]]
    
  18. Delete the old Replicat group.

    DELETE REPLICAT group
    
  19. Start the new Replicat group.

    START REPLICAT group
    

    Note:

    Do not delete the old remote trail, just in case it is needed later on for a support case or some other reason. You can move it to another location, if desired.

20.5.4 Adding Another Replicat Group to an Active Configuration

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 Section 14.7, "Creating an Online Replicat Group" for more information about Replicat modes.

Steps are performed on the source and target systems.

  1. Choose a name for the new group.

  2. On the target system, run GGSCI.

  3. Create a parameter file for the new Replicat group.

    EDIT PARAMS group
    

    Note:

    You can copy the original parameter file to use for this group, but make certain to change the Replicat group name and any other relevant parameters that apply to this new group.

  4. Add MAP statements (or edit copied ones) to specify the tables that you are adding or moving to this group. If this group will be a coordinated Replicat group, include the appropriate thread specifications.

  5. Save and close the parameter file.

  6. On the source system, run GGSCI.

  7. Stop the Extract group.

    STOP EXTRACT group
    
  8. On the target system, edit the old Replicat parameter file to remove MAP statements that specified the tables that you moved to the new Replicat group. Keep only the MAP statements that this Replicat will continue to process.

  9. Save and close the file.

  10. Issue the INFO REPLICAT command for the old Replicat group, and continue issuing it until it reports At EOF, no more records to process.

    INFO REPLICAT group
    
  11. Stop the old Replicat group. If you are stopping a coordinated Replicat, make certain the stop is clean so that all threads stop at the same trail record.

    STOP REPLICAT group
    
  12. Add the new Replicat group. For EXTTRAIL, specify the trail that this Replicat group is to read.

    ADD REPLICAT group, EXTTRAIL trail
    

    For example:

    ADD REPLICAT rep, EXTTRAIL dirdat/rt
    
  13. On the source system, start the Extract group.

    START EXTRACT group
    
  14. On the target system, start the old Replicat group.

    START REPLICAT group
    
  15. Start the new Replicat group.

    START REPLICAT group
    

20.6 Changing the Size of Trail Files

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.

  1. Issue one of the following commands, depending on the location of the trail, to view the path name of the trail you want to alter and the name of the associated Extract group. Use a wildcard to view all trails.

    (Remote trail)

    INFO RMTTRAIL *
    

    (Local trail)

    INFO EXTTRAIL *
    
  2. Issue one of the following commands, depending on the location of the trail, to change the file size.

    (Remote trail)

    ALTER RMTTRAIL trail, EXTRACT group, MEGABYTES n
    

    (Local trail)

    ALTER EXTTRAIL trail, EXTRACT group, MEGABYTES n
    
  3. Issue the following command to cause Extract to switch to the next file in the trail.

    SEND EXTRACT group, ROLLOVER
    

20.7 Switching Extract from Classic Mode to Integrated Mode

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 Installing and Configuring 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 Reference for Oracle GoldenGate for Windows and UNIX before 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

  1. Back up the current Oracle GoldenGate working directories.

  2. While the Oracle GoldenGate processes continue to run in their current configuration, so that they keep up with current change activity, copy the Extract parameter file to a new name.

  3. Grant the appropriate privileges to the Extract user and perform the required configuration steps to support your business applications in integrated capture mode. See Installing and Configuring Oracle GoldenGate for Oracle Database for information about configuring and running Extract in integrated mode.

  4. Log into the mining database with one of the following commands, depending on where the mining database is located.

    DBLOGIN USERIDALIAS alias
    
    MININGDBLOGIN USERIDALIAS alias
    

    Where: alias specifies the alias of a user in the credential store who has the privileges granted through the Oracle dbms_goldengate_auth.grant_admin_privilege procedure.

  5. Register the Extract group with the mining database. Among other things, this creates the logmining server.

    REGISTER EXTRACT group DATABASE
    
  6. Stop the Extract group.

    STOP EXTRACT group
    
  7. Issue the following command to determine whether the upgrade command can be issued. Transactions that started before the registration command must be written to the trail before you can proceed with the upgrade. You may have to issue this command more than once until it returns a message stating that Extract can be upgraded.

    INFO EXTRACT group UPGRADE
    
  8. Switch the Extract group to integrated mode. See Oracle RAC options for this command in Reference for Oracle GoldenGate for Windows and UNIX, if applicable.

    ALTER EXTRACT group UPGRADE INTEGRATED TRANLOG
    
  9. Replace the old parameter file with the new one, keeping the same name.

  10. Start the Extract group.

    START EXTRACT group
    

20.8 Switching Extract from Integrated Mode to Classic Mode

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 Installing and Configuring 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 Reference for Oracle GoldenGate for Windows and UNIX before 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

  1. Back up the current Oracle GoldenGate working directories.

  2. While the Oracle GoldenGate processes continue to run in their current configuration, so that they keep up with current change activity, copy the Extract parameter file to a new name.

  3. Grant the appropriate privileges to the Extract user and perform the required configuration steps to support your business applications in classic capture mode. See Installing and Configuring Oracle GoldenGate for Oracle Database for information about configuring and running Extract in classic mode.

  4. Stop the Extract group.

    STOP EXTRACT group
    
  5. Issue the following command to determine whether the downgrade command can be issued. Transactions that started before the downgrade command is issued must be written to the trail before you can proceed. You may have to issue this command more than once until it returns a message stating that Extract can be downgraded.

    INFO EXTRACT group DOWNGRADE
    
  6. Log into the mining database with one of the following commands, depending on where the mining database is located.

    DBLOGIN USERIDALIAS alias
    
    MININGDBLOGIN USERIDALIAS alias
    

    Where: alias is the alias of a user in the credential store who has the privileges granted through the Oracle dbms_goldengate_auth.grant_admin_privilege procedure.

  7. Switch the Extract group to classic mode.

    ALTER EXTRACT group DOWNGRADE INTEGRATED TRANLOG
    

    If on a RAC system, then the THREADS option has to be used with the downgrade command to specify the number of RAC threads.

  8. Unregister the Extract group from the mining database. Among other things, this removes the logmining server.

    UNREGISTER EXTRACT group DATABASE
    
  9. Replace the old parameter file with the new one, keeping the same name.

  10. Start the Extract group.

    START EXTRACT group
    

20.9 Switching Replicat from Nonintegrated Mode to Integrated Mode

Valid for Oracle only. For more information about Replicat modes for an Oracle database, see Installing and Configuring 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.

  1. Back up the Oracle GoldenGate working directories.

  2. While the Oracle GoldenGate processes continue to run in their current configuration, so that they keep up with current change activity, copy the Replicat parameter file to a new name.

  3. Grant the appropriate privileges to the Replicat user and perform the required configuration steps to support your business applications in integrated Replicat mode. See Installing and Configuring Oracle GoldenGate for Oracle Database for information about configuring and running Replicat in integrated mode.

  4. Run GGSCI.

  5. Stop Replicat.

    STOP REPLICAT group
    
  6. Log into the target database from GGSCI.

    DBLOGIN USERIDALIAS alias
    

    Where: alias is the alias of a user in the credential store who has the privileges granted through the Oracle dbms_goldengate_auth.grant_admin_privilege procedure.

  7. Alter Replicat to integrated mode.

    ALTER REPLICAT group, INTEGRATED
    
  8. Replace the old parameter file with the new one, keeping the same name.

  9. Start Replicat.

    START REPLICAT group
    
  10. Verify that Replicat is in integrated mode.

    INFO REPLICAT group
    

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.

20.10 Switching Replicat from Integrated Mode to Nonintegrated Mode

Valid for Oracle only. For more information about Replicat modes for an Oracle database, see Installing and Configuring 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.

  1. Back up the Oracle GoldenGate working directories.

  2. While the Oracle GoldenGate processes continue to run in their current configuration, so that they keep up with current change activity, copy the Replicat parameter file to a new name.

  3. Grant the appropriate privileges to the Replicat user and perform the required configuration steps to support your business applications in nonintegrated Replicat mode. See Installing and Configuring Oracle GoldenGate for Oracle Database for information about configuring and running Replicat in integrated mode.

  4. Run GGSCI.

  5. Log into the target database from GGSCI.

    DBLOGIN USERIDALIAS alias
    

    Where: alias is the alias of a user in the credential store who has the privileges granted through the Oracle dbms_goldengate_auth.grant_admin_privilege procedure.

  6. Create a checkpoint table in the target database for the nonintegrated Replicat to use to store its recovery checkpoints. If a checkpoint table was previously associated with this Replicat group and still exists, you can omit this step. See Section 14.3, "Creating a Checkpoint Table" for more information about options for using a checkpoint table.

    ADD CHECKPOINTTABLE [container.]table
    
  7. Stop Replicat.

    STOP REPLICAT group
    
  8. Alter Replicat to nonintegrated mode. For the CHECKPOINTTABLE argument, specify the checkpoint table that you created for this Replicat group.

    ALTER REPLICAT group, NONINTEGRATED, CHECKPOINTTABLE [container.]table
    
  9. Replace the old parameter file with the new one, keeping the same name.

  10. Start Replicat.

    START REPLICAT group
    

    After issuing this command, wait until there is some activity on the source database so that the switchover can be completed. (Replicat waits until its internal high-water mark is exceeded before removing the status of "switching from integrated mode.")

  11. Verify that Replicat switched to nonintegrated mode.

    INFO REPLICAT group
    

20.11 Switching Replicat to Coordinated Mode

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 Chapter 14, "Configuring Online Change Synchronization" for more information about coordinated Replicat.

20.11.1 Procedure overview

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.

20.11.2 Performing the switch to coordinated Replicat

Note:

Do not create the Replicat group until prompted by these instructions.

  1. Back up the current parameter files of all of the Extract groups, data pumps, and Replicat groups. You will be editing them.

  2. Create a working directory outside the Oracle GoldenGate directory. You will use this directory to create and stage new versions of the parameter files. If needed, you can create a working directory on the source and target systems.

  3. In the working directory, create a parameter file for a coordinated Replicat. Copy the MAP parameters from the active parameter files of all of the Replicat groups to this parameter file, and then add the thread specifications and any other parameters that support your required coordinated Replicat configuration

  4. If using multiple primary Extract groups, select one to keep, and then save a copy of its current parameter file to the working directory.

  5. Copy all of the TABLE statements from the other Extract groups to the new parameter file of the primary Extract that you are keeping.

  6. In the working directory, save a copy of the parameter file of the data pump that is linked to the primary Extract that you are keeping.

  7. Copy all of the TABLE statements from the other data pumps to the copied parameter file of the kept data pump.

  8. In the source database, create a simple dummy table on which a simple INSERT statement can be performed. For this procedure, the name schema.event is used.

  9. Create the same table on the target system, to avoid the need for additional configuration parameters.

  10. Edit the active parameter files (not the copies) of all primary and data-pump Extract groups to add the following EVENTACTIONS parameter to each one. If the data pump is configured with the PASSTHRU parameter for the data tables, add NOPASSTHRU before the TABLE statement that captures the dummy table.

    NOPASSTHRU
    TABLE schema.event, EVENTACTIONS(STOP);
    
  11. Edit the active parameter files (not the copies) of all of the Replicat groups to add the following EVENTACTIONS parameter to each one.

    MAP schema.event, TARGET schema.event, EVENTACTIONS(IGNORE, STOP);
    
  12. Stop the Oracle GoldenGate processes gracefully in the following order:

    • Stop all Replicat processes.

    • Stop all data pumps.

    • Stop all Extract processes.

  13. Restart the Oracle GoldenGate processes in the following order so that the EVENTACTIONS parameters take effect:

    • Start all Extract processes.

    • Start all data pumps.

    • Start all Replicat processes.

  14. On the source system, issue a transaction on the schema.event table that contains one INSERT statement. Make certain to commit the transaction.

  15. In GGSCI, issue the STATUS command for all of the primary Extract and data pump processes on the source system, and issue the same command for all of the Replicat processes on the target system, until the commands show that all of the processes are STOPPED.

    STATUS EXTRACT *
    STATUS REPLICAT *
    
  16. Replace the active parameter files of the primary Extract and data pump that you kept with the new parameter files from the working directory.

  17. Delete the unneeded Extract and data pump groups and their parameter files.

  18. Log into the target database by using the DBLOGIN command.

  19. Delete all of the Replicat groups and their active parameter files.

  20. Copy or move the new coordinated Replicat parameter file from the working directory to the Oracle GoldenGate directory.

  21. In GGSCI, issue the INFO EXTRACT command for the data pump and make note of its write checkpoint position in the output (remote) trail.

    INFO EXTRACT pump, DETAIL
    
  22. Add a new coordinated Replicat group with the following parameters.

    ADD REPLICAT group, EXTTRAIL trail, EXTSEQNO sequence_number, EXTRBA rba, COORDINATED MAXTHREADS number
    

    Where:

    • group is the name of the coordinated Replicat group. The name must match that of the new parameter file created for this group.

    • EXTTRAIL trail is the name of the trail that the data pump writes to.

    • EXTSEQNO sequence_number is the sequence number of the trail as shown in the write checkpoint returned by the INFO EXTRACT that you issued for the data pump.

    • EXTRBA rba is the relative byte address in the trail as shown in the write checkpoint returned by INFO EXTRACT. Together, these position Replicat to resume processing at the correct point in the trial.

    • MAXTHREADS number specifies the maximum number of threads allowed for this group. This value should be appropriate for the number of threads that are specified in the parameter file.

  23. Start the primary Extract group.

  24. Start the data pump group.

  25. Start the coordinated Replicat group.

20.12 Administering a Coordinated Replicat Configuration

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.

Section 20.12.1, "Performing a Planned Re-partitioning of the Workload"

Section 20.12.2, "Recovering Replicat after an Unplanned Re-partitioning"

Section 20.12.3, "Synchronizing threads after an Unclean Stop"

20.12.1 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 Section 20.12.3 to re-synchronize the threads before you re-partition them.

  1. Run GGSCI.

  2. Stop Replicat.

    STOP REPLICAT group
    
  3. Open the parameter file for editing.

    EDIT PARAMS group
    
  4. Make the required changes to the THREAD or THREADRANGE specifications in the MAP statements.

  5. Save and close the parameter file.

  6. Start Replicat.

    START REPLICAT group
    

20.12.2 Recovering Replicat after an Unplanned Re-partitioning

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

20.12.2.1 Reprocessing 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.

  1. Add the HANDLECOLLISIONS parameter to the Replicat parameter file. It is not necessary to use any THREADS options.

  2. Issue the INFO REPLICAT command for the Replicat group as a whole (the coordinator thread). Make a record of the RBA of the checkpoint. This is the low watermark value. This output also shows you the active thread IDs under the Group Name column. Make a record of these, as well.

    INFO REPLICAT group
    
    GGSCI (slc03jgo) 3> info ra detailREPLICAT   RA       Last Started 2013-05-01 14:15   Status ABENDEDCOORDINATED          Coordinator                      MAXTHREADS 15Checkpoint Lag       00:00:00 (updated 00:00:07 ago)Process ID           11445Log Read Checkpoint  File ./dirdat/withMaxTransOp/bg000001                     2013-05-02 07:49:45.975662  RBA 44704Lowest Log BSN value: (requires database login)Active Threads: ID  Group Name PID   Status   Lag at Chkpt  Time Since Chkpt1   RA001     11454 ABENDED  00:00:00      00:00:01    2   RA002     11455 ABENDED  00:00:00      00:00:04    3   RA003     11456 ABENDED  00:00:00      00:00:01    5   RA005     11457 ABENDED  00:00:00      00:00:02    6   RA006     11458 ABENDED  00:00:00      00:00:04    7   RA007     11459 ABENDED  00:00:00      00:00:04  
    
  3. Issue the INFO REPLICAT command for each processing thread ID and record the RBA position of each thread. Make a note of the highest RBA. This is the high watermark of the Replicat group.

    INFO REPLICAT threadID
    
     info ra002
    REPLICAT   RA002    Last Started 2013-05-01 14:15   Status ABENDEDCOORDINATED          Replicat Thread                  Thread 2Checkpoint Lag       00:00:00 (updated 00:00:06 ago)Process ID           11455
    Log Read Checkpoint  File ./dirdat/withMaxTransOp/bg000001                     2013-05-02 07:49:15.837271  RBA 45603
    
  4. Issue the ALTER REPLICAT command for the coordinator thread (Replicat as a whole, without any thread ID) and position to the low watermark RBA that you recorded.

    ALTER REPLICAT group EXTRBA low_watermark_rba
    
  5. Start Replicat.

    START REPLICAT group
    
  6. Issue the basic INFO REPLICAT command until it shows an RBA that is higher than the high watermark that you recorded. HANDLECOLLISIONS handles any collisions that occur due to previously applied transactions.

    INFO REPLICAT group
    
  7. Stop Replicat.

    STOP REPLICAT group
    
  8. Remove or comment out the HANDLECOLLISIONS parameter.

  9. Start Replicat.

    START REPLICAT group
    

20.12.2.2 Using the auto-saved parameter file

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.

  1. Save the new parameter file to a different name, and then rename the saved original parameter file to the correct name (same as the group name). The saved parameter file has a .backup suffix and is stored in the dirprm subdirectory of the Oracle GoldenGate installation directory.

  2. Issue the following command to synchronize the Replicat threads to the maximum checkpoint position. This command automatically starts Replicat and executes the threads until they reach the maximum checkpoint position.

    SYNCHRONIZE REPLICAT group
    
  3. Issue the STATUS REPLICAT command until it shows that Replicat stopped cleanly.

    STATUS REPLICAT group
    
  4. Save the original parameter file to a different name, and then rename the new parameter file to the group name.

  5. Start Replicat.

    START REPLICAT group
    

20.12.3 Synchronizing threads after an Unclean Stop

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.

  1. Run GGSCI.

  2. Synchronize the Replicat threads to the maximum checkpoint position. Replicat performs the synchronization and then stops.

    SYNCHRONIZE REPLICAT group
    
  3. (Optional) To re-partition the workload among different or new threads, open the parameter file for editing and then make the required changes to the THREAD or THREADRANGE specifications in the MAP statements.

    EDIT PARAMS group
    
  4. Save and close the parameter file.

  5. Start Replicat.

    START REPLICAT group
    

20.13 Restarting a Primary Extract after System Failure or Corruption

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.

20.13.1 Details of this procedure

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.

20.13.2 Performing the Recovery

Follow these steps in the order shown to recover the Oracle GoldenGate processes.

  1. In GGSCI on the target system, issue the DBLOGIN command.

    DBLOGIN {USERID Replicat_user | USERIDALIAS alias_of_Replicat_user} 
    
  2. On the target, obtain the LOGBSN value by issuing the INFO REPLICAT command with the DETAIL option.

    INFO REPLICAT group, DETAIL
    

    The BSN is included in the output as a line similar to the following:

    Current Log BSN value: 1151679
    
  3. (Classic capture mode only. Skip if using integrated capture mode.) Query the source database to find the sequence number of the transaction log file that contains the value of the LOGBSN that you identified in the previous step. This example assumes 1855798 is the LOGBSN value and shows that the sequence number of the transaction log that contains that LOGBSN value is 163.

    SQL> select name, thread#, sequence# from v$archived_log 
    where 1855798 between first_change# and next_change#; 
    
    NAME                                  THREAD#    SEQUENCE# 
    ------------------------------------- ---------- ----------/oracle/dbs/arch1_163_800262442.dbf   1          163 
    
  4. Issue the following commands in GGSCI to reposition the primary Extract to the LOGBSN start position.

    • (Classic capture mode)

      ALTER EXTRACT group EXTSEQNO 163
      ALTER EXTRACT group EXTRBA 0 
      ALTER EXTRACT group ETROLLOVER
      
    • (Integrated capture mode)

      ALTER EXTRACT group SCN 1151679
      ALTER EXTRACT group ETROLLOVER
      

    Note:

    There is a limit on how far back Extract can go in the transaction stream, when in integrated mode. If the required SCN is no longer available, the ALTER EXTRACT command fails.

  5. Issue the following command in GGSCI to the primary Extract to view the new sequence number of the Extract Write Checkpoint. This command shows the trail and RBA where Extract will begin to write new data. Because a rollover was issued, the start point is at the beginning (RBA 0) of the new trail file, in this example file number 7.

    INFO EXTRACT group SHOWCH
    Sequence #: 7
    RBA: 0 
    
  6. Issue the following command in GGSCI to reposition the downstream data pump and start a new output trail file.

    ALTER EXTRACT pump EXTSEQNO 7
    ALTER EXTRACT pump EXTRBA 0
    ALTER EXTRACT pump ETROLLOVER
    
  7. Issue the following command in GGSCI to the data pump Extract to view the new sequence number of the data pump Write Checkpoint, in this example trail number 9.

    INFO EXTRACT pump SHOWCH
    Sequence #: 9
    RBA: 0 
    
  8. Reposition Replicat to start reading the trail at the new Write Checkpoint of the data pump.

    ALTER REPLICAT group EXTSEQNO 9
    ALTER REPLICAT group EXTRBA 0
    
  9. Start the primary Extract and the data pump.

    START EXTRACT group
    START REPLICAT group
    
  10. Issue the following command in GGSCI to start Replicat. If Replicat is operating in integrated mode (Oracle targets only), omit the FILTERDUPTRANSACTIONS option. Integrated Replicat handles duplicate transactions transparently.

    START REPLICAT group[, FILTERDUPTRANSACTIONS]
    

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.

Close Window

Table of Contents

Administering Oracle GoldenGate for Windows and UNIX

Expand | Collapse