6 Configuring Oracle GoldenGate to Maintain a Live Standby Database

This chapter describes how to configure Oracle GoldenGate to maintain a live standby database.

Topics:

• Overview of a Live Standby Configuration
  
• Considerations for a Live Standby Configuration
  
• Creating a Live Standby Configuration
  
• Configuration from Standby to Active Source
  
• Moving User Activity in a Planned Switchover
  
• Moving User Activity in an Unplanned Failover
  

6.1 Overview of a Live Standby Configuration

Oracle GoldenGate supports an active-passive bi-directional configuration, where Oracle GoldenGate replicates data from an active primary database to a full replica database on a live standby system that is ready for failover during planned and unplanned outages.

Description of the illustration live_standby_overview.jpg

In this configuration, there is an inactive Oracle GoldenGate Extract group and an inactive data pump on the live standby system. Both of those groups remain stopped until just before user applications are switched to the live standby system in a switchover or failover. When user activity moves to the standby, those groups begin capturing transactions to a local trail, where the data is stored on disk until the primary database can be used again.

In the case of a failure of the primary system, the Oracle GoldenGate Manager and Replicat processes work in conjunction with a database instantiation taken from the standby to restore parity between the two systems after the primary system is recovered. At the appropriate time, users are moved back to the primary system, and Oracle GoldenGate is configured in ready mode again, in preparation for future failovers.

6.2 Considerations for a Live Standby Configuration

These sections describe considerations for a live standby configuration.

• Trusted Source
  
• Duplicate Standby
  
• DML on the Standby System
  
• Oracle GoldenGate Processes
  
• Backup Files
  
• Failover Preparedness
  
• Sequential Values that are Generated by the Database
  
• Additional Information
  

6.2.1 Trusted Source

The primary database is the trusted source. This is the database that is the active source during normal operating mode, and it is the one from which the other database is derived in the initial synchronization phase and in any subsequent resynchronizations. Maintain frequent backups of the trusted source data.

6.2.2 Duplicate Standby

In most implementations of a live standby, the source and target databases are identical in content and structure. Data mapping, conversion, and filtering typically are not appropriate practices in this kind of configuration, but Oracle GoldenGate does support such functionality if required by your business model. To support these functions, use the options of the TABLE and MAP parameters.

6.2.3 DML on the Standby System

If your applications permit, you can use the live standby system for reporting and queries, but not DML. If there will be active transactional applications on the live standby system that affect objects in the Oracle GoldenGate configuration, you should configure this as an active-active configuration. See Configuring Oracle GoldenGate for Active-Active Configuration for more information.

6.2.4 Oracle GoldenGate Processes

During normal operating mode, leave the primary Extract and the data pump on the live standby system stopped, and leave the Replicat on the active source stopped. This prevents any DML that occurs accidentally on the standby system from being propagated to the active source. Only the Extract, data pump, and Replicat that move data from the active source to the standby system can be active.

6.2.5 Backup Files

Make regular backups of the Oracle GoldenGate working directories on the primary and standby systems. This backup must include all of the files that are installed at the root level of the Oracle GoldenGate installation directory and all of the sub-directories within that directory. Having a backup of the Oracle GoldenGate environment means that you will not have to recreate your process groups and parameter files.

6.2.6 Failover Preparedness

Make certain that the primary and live standby systems are ready for immediate user access in the event of a planned switchover or an unplanned source failure. The following components of a high-availability plan should be made easily available for use on each system:

• Scripts that grant insert, update, and delete privileges.

• Scripts that enable triggers and cascaded delete constraints on the live standby system. (These may have been disabled during the setup procedures that were outlined in the Oracle GoldenGate installation and configuration document for your database type.)

• Scripts that switch over the application server, start applications, and copy essential files that are not part of the replication environment.

• A failover procedure for moving users to the live standby if the source system fails.

6.2.7 Sequential Values that are Generated by the Database

If database-generated values, such as Oracle sequences, are used as part of a key, the range of values must be different on each system, with no chance of overlap. If the application permits, you can add a location identifier to the value to enforce uniqueness.

For Oracle databases, Oracle GoldenGate can be configured to replicate sequences in a manner that ensures uniqueness on each database. To replicate sequences, use the SEQUENCE and MAP parameters. .

6.2.8 Additional Information

The following documentation provides additional information of relevance to configuring Oracle GoldenGate.

• For additional system requirements, process configuration, and database setup requirements, see the Oracle GoldenGate installation and configuration document for your database type. These guides are listed in the Preface of this book.

• For detailed instructions on configuring Oracle GoldenGate change capture and delivery groups, see Configuring Online Change Synchronization.

• For additional tuning options for Oracle GoldenGate, see Tuning the Performance of Oracle GoldenGate.

• For complete syntax and descriptions of the Oracle GoldenGate commands and parameters, see Reference for Oracle GoldenGate for Windows and UNIX.

6.3 Creating a Live Standby Configuration

Refer to Figure 6-1 for a visual representation of the objects you will be creating.

Figure 6-1 Oracle GoldenGate configuration elements for live standby

Description of "Figure 6-1 Oracle GoldenGate configuration elements for live standby"

• Prerequisites on Both Systems
  
• Configuration from Active Source to Standby
  

6.3.1 Prerequisites on Both Systems

Perform the following prerequisites on both systems.

1. Create a Replicat checkpoint table (unless using Oracle integrated Replicat). For instructions, see Creating a Checkpoint Table.
2. Configure the Manager process according to the instructions in Configuring Manager and Network Communications.

6.3.2 Configuration from Active Source to Standby

These steps configure Oracle GoldenGate to capture data from the primary database and replicate it to the standby database.

To Configure the Primary Extract Group

Perform these steps on the active source.

1. On the source, use the ADD EXTRACT command to create an Extract group. For documentation purposes, this group is called ext_1.

   ADD EXTRACT ext_1, {TRANLOG | INTEGRATED TRANLOG}, BEGIN time [option[, ...]]
   

   See Reference for Oracle GoldenGate for detailed information about these and other ADD EXTRACT options that may be required for your installation.

2. Use the ADD EXTTRAIL command to add a local trail. For documentation purposes, this trail is called local_trail_1.

   ADD EXTTRAIL local_trail_1, EXTRACT ext_1
   

   For EXTRACT, specify the ext_1 group to write to this trail.

3. Use the EDIT PARAMS command to create a parameter file for the ext_1 group. Include the following parameters plus any others that apply to your database environment. For possible additional required parameters, see the Oracle GoldenGate installation and setup guide for your database.

   -- Identify the Extract group:
   EXTRACT ext_1
   -- Specify database login information as needed for the database:
   [SOURCEDB dsn_1][, USERIDALIAS alias]
   -- Log all scheduling columns if using integrated Replicat
   LOGALLSUPCOLS
   -- Specify the local trail that this Extract writes to
   -- and the encryption algorithm:
   ENCRYPTTRAIL algorithm
   EXTTRAIL local_trail_1
   -- Specify sequences to be captured:
   SEQUENCE [container.]owner.sequence;
   -- Specify tables to be captured:
   TABLE [container.]owner.*;

To Configure the Data Pump

Perform these steps on the active source.

1. Use the ADD EXTRACT command to create a data pump group. For documentation purposes, this group is called pump_1.

   ADD EXTRACT pump_1, EXTTRAILSOURCE local_trail_1, BEGIN time
   

   For EXTTRAILSOURCE, specify local_trail_1 as the data source.

2. Use the ADD RMTTRAIL command to specify a remote trail that will be created on the standby system.

   ADD RMTTRAIL remote_trail_1, EXTRACT pump_1
   

   For EXTRACT, specify the pump_1 data pump to write to this trail.

   See Reference for Oracle GoldenGate for additional ADD RMTTRAIL options.

3. Use the EDIT PARAMS command to create a parameter file for the pump_1 group. Include the following parameters plus any others that apply to your database environment.

   -- Identify the data pump group:
   EXTRACT pump_1
   -- Specify database login information as needed for the database:
   [SOURCEDB dsn_1][, USERIDALIAS alias]
   -- Decrypt the data only if the data pump must process it.
   -- DECRYPTTRAIL
   -- Specify the name or IP address of the standby system
   -- and optional encryption of data over TCP/IP:
   RMTHOSTOPTIONS system_2, MGRPORT port_number, ENCRYPT encryption_options
   -- Specify the remote trail and encryption algorithm on the standby system:
   ENCRYPTTRAIL algorithm
   RMTTRAIL remote_trail_1
   -- Specify sequences to be captured:
   SEQUENCE [container.]owner.sequence;
   -- Specify tables to be captured:
   TABLE [container.]owner.*;

To Configure the Replicat Group

Perform these steps on the live standby system.

1. Create a Replicat checkpoint table (unless using Oracle integrated Replicat). See Creating a Checkpoint Table for instructions.
2. Use the ADD REPLICAT command to create a Replicat group. For documentation purposes, this group is called rep_1.

   ADD REPLICAT rep_1
   [, INTEGRATED | COORDINATED [MAXTHREADS number]]
   , EXTTRAIL remote_trail_1, BEGIN time
   

   For EXTTRAIL, specify remote_trail_1 as the trail that this Replicat reads.

   See Reference for Oracle GoldenGate for detailed information about these and other options that may be required for your installation.

3. Use the EDIT PARAMS command to create a parameter file for the rep_1 group. Include the following parameters plus any others that apply to your database environment. For possible additional required parameters, see the Oracle GoldenGate installation and setup guide for your database.

   -- Identify the Replicat group:
   REPLICAT rep_1
   -- State that source and target definitions are identical:
   ASSUMETARGETDEFS
   -- Specify database login information as needed for the database:
   [TARGETDB dsn_2][, USERIDALIAS alias]
   -- Specify error handling rules:
   REPERROR (error, response)
   -- Specify tables for delivery and threads if using coordinated Replicat:
   MAP [container.|catalog.]owner.table, TARGET owner.table[, DEF template]
   [, THREAD (thread_ID)][, THREADRANGE (thread_range[, column_list])]
   ;

6.4 Configuration from Standby to Active Source

These steps configure Oracle GoldenGate in passive mode. In this mode, the Oracle GoldenGate processes are ready, but not started, to capture data from the secondary database and replicate it to the primary database after a switchover of transaction activity to the secondary system.

Note:

This is a reverse image of the configuration that you just created.

To Configure the Primary Extract Group

Perform these steps on the live standby system.

1. On the source, use the ADD EXTRACT command to create an Extract group. For documentation purposes, this group is called ext_2.

   ADD EXTRACT ext_2, {TRANLOG | INTEGRATED TRANLOG}, BEGIN time [option[, ...]]
   

   See Reference for Oracle GoldenGate for detailed information about these and other ADD EXTRACT options that may be required for your installation.

2. Use the ADD EXTTRAIL command to add a local trail. For documentation purposes, this trail is called local_trail_2.

   ADD EXTTRAIL local_trail_2, EXTRACT ext_2
   

   For EXTRACT, specify the ext_2 group to write to this trail.

3. Use the EDIT PARAMS command to create a parameter file for the ext_2 group. Include the following parameters plus any others that apply to your database environment. For possible additional required parameters, see the Oracle GoldenGate installation and setup guide for your database.

   -- Identify the Extract group:
   EXTRACT ext_2
   -- Specify database login information as needed for the database:
   [SOURCEDB dsn_2][, USERIDALIAS alias]
   -- Log all scheduling columns if using integrated Replicat
   LOGALLSUPCOLS
   -- Specify the local trail this Extract writes to and the encryption algorithm:
   ENCRYPTTRAIL algorithm
   EXTTRAIL local_trail_2
   -- Specify sequences to be captured:
   SEQUENCE [container.]owner.sequence;
   -- Specify tables to be captured:
   TABLE [container.]owner.*;

To Configure the Data Pump

Perform these steps on the live standby system.

1. Use the ADD EXTRACT command to create a data pump group. For documentation purposes, this group is called pump_2.

   ADD EXTRACT pump_2, EXTTRAILSOURCE local_trail_2, BEGIN time
   

   For EXTTRAILSOURCE, specify local_trail_2 as the data source.

2. Use the ADD RMTTRAIL command to add a remote trail remote_trail_2 that will be created on the active source system.

   ADD RMTTRAIL remote_trail_2, EXTRACT pump_2
   

   For EXTRACT, specify the pump_2 data pump to write to this trail.

   See Reference for Oracle GoldenGate for additional ADD RMTTRAIL options.

3. Use the EDIT PARAMS command to create a parameter file for the pump_2 group. Include the following parameters plus any others that apply to your database environment.

   -- Identify the data pump group:
   EXTRACT pump_2
   -- Specify database login information as needed for the database:
   [SOURCEDB dsn_2][, USERIDALIAS alias]
   -- Decrypt the data only if the data pump must process it.
   -- DECRYPTTRAIL
   -- Specify the name or IP address of the active source system
   -- and optional encryption of data over TCP/IP:
   RMTHOSTOPTIONS system_1, MGRPORT port_number, ENCRYPT encryption_options
   -- Specify remote trail and encryption algorithm on active source system:
   ENCRYPTTRAIL algorithm
   RMTTRAIL remote_trail_2
   -- Specify sequences to be captured:
   SEQUENCE [container.]owner.sequence;
   -- Specify tables to be captured:
   TABLE [container.]owner.*;

To Configure the Replicat Group

Perform these steps on the active source.

1. Use the ADD REPLICAT command to create a Replicat group. For documentation purposes, this group is called rep_2.

   ADD REPLICAT rep_2
   [, INTEGRATED | COORDINATED [MAXTHREADS number]]
   , EXTTRAIL remote_trail_1, BEGIN time
   

   For EXTTRAIL, specify remote_trail_2 as the trail that this Replicat reads.

   See Reference for Oracle GoldenGate for detailed information about these and other options that may be required for your installation.

2. Use the EDIT PARAMS command to create a parameter file for the rep_2 group. Include the following parameters plus any others that apply to your database environment. For possible additional required parameters, see the Oracle GoldenGate installation and setup guide for your database.

   -- Identify the Replicat group:
   REPLICAT rep_2
   -- State that source and target definitions are identical:
   ASSUMETARGETDEFS
   -- Specify database login information as needed for the database:
   [TARGETDB dsn_1][, USERIDALIAS alias]
   -- Handle collisions between failback data copy and replication:
   HANDLECOLLISIONS
   -- Specify error handling rules:
   REPERROR (error, response)
   -- Specify tables for delivery and threads if using coordinated Replicat:
   MAP [container.|catalog.]owner.table, TARGET owner.table[, DEF template]
   [, THREAD (thread_ID)][, THREADRANGE (thread_range[, column_list])]
   ;

6.5 Moving User Activity in a Planned Switchover

This procedure moves user application activity from a primary database to a live standby system in a planned, graceful manner so that system maintenance and other procedures that do not affect the databases can be performed on the primary system.

• Moving User Activity to the Live Standby
  
• Moving User Activity Back to the Primary System
  

6.5.1 Moving User Activity to the Live Standby

To move user activity to the live standby:

1. (Optional) If you need to perform system maintenance on the secondary system, you can do so now or at the specified time later in these procedures, after moving users from the secondary system back to the primary system. In either case, be aware of the following risks if you must shut down the secondary system for any length of time:

   • The local trail on the primary system could run out of disk space as data accumulates while the standby is offline. This will cause the primary Extract to abend.

   • If the primary system fails while the standby is offline, the data changes will not be available to be applied to the live standby when it is functional again, thereby breaking the synchronized state and requiring a full re-instantiation of the live standby.

2. On the primary system, stop the user applications, but leave the primary Extract and the data pump on that system running so that they capture any backlogged transaction data.
3. On the primary system, issue the following command for the primary Extract until it returns "At EOF, no more records to process." This indicates that all transactions are now captured.

   LAG EXTRACT ext_1
   

   Note:

   Since capture continues to read REDO, the non-production workload continues to work. In this case, there is possibility that At EOF is never returned even though the production workload has already stopped8.5.1..

4. On the primary system, stop the primary Extract process

   STOP EXTRACT ext_1
   

5. On the primary system, issue the following command for the data pump until it returns "At EOF, no more records to process." This indicates that the pump sent all of the captured data to the live standby.

   LAG EXTRACT pump_1
   

6. On the primary system, stop the data pump.

   STOP EXTRACT pump_1
   

7. On the live standby system, issue the STATUS REPLICAT command until it returns "At EOF (end of file)." This confirms that Replicat applied all of the data from the trail to the database.

   STATUS REPLICAT rep_1
   

8. On the live standby system, stop Replicat.

   STOP REPLICAT rep_1
   

9. On the live standby system, do the following:

   • Run the script that grants insert, update, and delete permissions to the users of the business applications.

   • Run the script that enables triggers and cascade delete constraints.

   • Run the scripts that switch over the application server, start applications, and copy essential files that are not part of the replication environment.

10. On the live standby system, alter the primary Extract to begin capturing data based on the current timestamp. Otherwise, Extract will spend unnecessary time looking for operations that date back to the time that the group was created with the ADD EXTRACT command.

    ALTER EXTRACT ext_2, BEGIN NOW
    

11. On the live standby system, start the primary Extract so that it is ready to capture transactional changes.

    START EXTRACT ext_2
    

    Note:

    Do not start the data pump on the live standby system, and do not start the Replicat on the primary system. Data must be stored in the local trail on the live standby until the primary database is ready for user activity again.

12. Switch user activity to the live standby system.
13. On the primary system, perform the system maintenance.

6.5.2 Moving User Activity Back to the Primary System

To move user activity back to the primary system:

1. On the live standby system, stop the user applications, but leave the primary Extract running so that it captures any backlogged transaction data.
2. On the primary system, start Replicat in preparation to receive changes from the live standby system.

   START REPLICAT rep_2
   

3. On the live standby system, start the data pump to begin moving the data that is stored in the local trail across TCP/IP to the primary system.

   START EXTRACT pump_2
   

4. On the live standby system, issue the following command for the primary Extract until it returns "At EOF, no more records to process." This indicates that all transactions are now captured.

   LAG EXTRACT ext_2
   

5. On the live standby system, stop the primary Extract.

   STOP EXTRACT ext_2
   

6. On the live standby system, issue the following command for the data pump until it returns "At EOF, no more records to process." This indicates that the pump sent all of the captured data to the primary system.

   LAG EXTRACT pump_2
   

7. On the live standby system, stop the data pump.

   STOP EXTRACT pump_2
   

8. On the primary system, issue the STATUS REPLICAT command until it returns "At EOF (end of file)." This confirms that Replicat applied all of the data from the trail to the database.

   STATUS REPLICAT rep_2
   

9. On the primary system, stop Replicat.

   STOP REPLICAT rep_2
   

10. On the primary system, do the following:

    • Run the script that grants insert, update, and delete permissions to the users of the business applications.

    • Run the script that enables triggers and cascade delete constraints.

    • Run the scripts that switch over the application server, start applications, and copy essential files that are not part of the replication environment.

11. On the primary system, alter the primary Extract to begin capturing data based on the current timestamp. Otherwise, Extract will spend unnecessary time looking for operations that were already captured and replicated while users were working on the standby system.

    ALTER EXTRACT ext_1, BEGIN NOW
    

12. On the primary system, start the primary Extract so that it is ready to capture transactional changes.

    START EXTRACT ext_1
    

13. Switch user activity to the primary system.
14. (Optional) If system maintenance must be done on the live standby system, you can do it now, before starting the data pump on the primary system. Note that captured data will be accumulating on the primary system while the standby is offline.
15. On the primary system, start the data pump.

    START EXTRACT pump_1
    

16. On the live standby system, start Replicat.

    START REPLICAT rep_1

6.6 Moving User Activity in an Unplanned Failover

These sections describe how to move user activity in an unplanned failover.

• Moving User Activity to the Live Standby
  
• Moving User Activity Back to the Primary System
  

6.6.1 Moving User Activity to the Live Standby

This procedure does the following:

• Prepares the live standby for user activity.

• Ensures that all transactions from the primary system are applied to the live standby.

• Activates Oracle GoldenGate to capture transactional changes on the live standby.

• Moves users to the live standby system.

Perform these steps on the live standby system

To move users to the live standby

1. Issue the STATUS REPLICAT command until it returns "At EOF (end of file)" to confirm that Replicat applied all of the data from the trail to the database.

   STATUS REPLICAT rep_1
   

2. Stop the Replicat process.

   STOP REPLICAT rep_1
   

3. Run the script that grants insert, update, and delete permissions to the users of the business applications.
4. Run the script that enables triggers and cascade delete constraints.
5. Run the scripts that fail over the application server, start applications, and copy essential files that are not part of the replication environment.
6. Start the primary Extract process on the live standby.

   START EXTRACT ext_2
   

7. Move the users to the standby system and let them start working.

   Note:

   Do not start the data pump group on the standby. The user transactions must accumulate there until just before user activity is moved back to the primary system.

6.6.2 Moving User Activity Back to the Primary System

This procedure does the following:

• Recovers the Oracle GoldenGate environment.

• Makes a copy of the live standby data to the restored primary system.

• Propagates user transactions that occurred while the copy was being made.

• Reconciles the results of the copy with the propagated changes.

• Moves users from the standby system to the restored primary system.

• Prepares replication to maintain the live standby again.

Perform these steps after the recovery of the primary system is complete.

To Recover the Source Oracle GoldenGate Environment

1. On the primary system, recover the Oracle GoldenGate directory from your backups.

2. On the primary system, run GGSCI.

3. On the primary system, delete the primary Extract group.

   DELETE EXTRACT ext_1
   

4. On the primary system, delete the local trail.

   DELETE EXTTRAIL local_trail_1
   

5. On the primary system, add the primary Extract group again, using the same name so that it matches the parameter file that you restored from backup. For documentation purposes, this group is called ext_1. This step initializes the Extract checkpoint from its state before the failure to a clean state.

   ADD EXTRACT ext_1, {TRANLOG | INTEGRATED TRANLOG}, BEGIN time
   [, THREADS n]
   

   • For TRANLOG and INTEGRATED TRANLOG, see Reference for Oracle GoldenGate. INTEGRATED TRANLOG enables integrated capture for an Oracle database.

6. On the primary system, add the local trail again, using the same name as before. For documentation purposes, this trail is called local_trail_1.

   ADD EXTTRAIL local_trail_1, EXTRACT ext_1
   

   • For EXTRACT, specify the ext_1 group to write to this trail.

7. On the primary system, start the Manager process.

   START MANAGER

To Copy the Database from Standby to Primary System

1. On the primary system, run scripts to disable triggers and cascade delete constraints.

2. On the standby system, start making a hot copy of the database.

3. On the standby system, record the time at which the copy finishes.

4. On the standby system, stop user access to the applications. Allow all open transactions to be completed.

To Propagate Data Changes Made During the Copy

1. On the primary system, start Replicat.

   START REPLICAT rep_2
   

2. On the live standby system, start the data pump. This begins transmission of the accumulated user transactions from the standby to the trail on the primary system.

   START EXTRACT pump_2
   

3. On the primary system, issue the INFO REPLICAT command until you see that it posted all of the data changes that users generated on the standby system during the initial load. Refer to the time that you recorded previously. For example, if the copy stopped at 12:05, make sure that change replication has posted data up to that point.

   INFO REPLICAT rep_2
   

4. On the primary system, issue the following command to turn off the HANDLECOLLISIONS parameter and disable the initial-load error handling.

   SEND REPLICAT rep_2, NOHANDLECOLLISIONS
   

5. On the primary system, issue the STATUS REPLICAT command until it returns "At EOF (end of file)" to confirm that Replicat applied all of the data from the trail to the database.

   STATUS REPLICAT rep_2
   

6. On the live standby system, stop the data pump. This stops transmission of any user transactions from the standby to the trail on the primary system.

   STOP EXTRACT pump_2
   

7. On the primary system, stop the Replicat process.

   STOP REPLICAT rep_2
   

At this point in time, the primary and standby databases should be in a state of synchronization again.

(Optional) To Verify Synchronization

1. Use a compare tool, such as Oracle GoldenGate Veridata, to compare the source and standby databases for parity.

2. Use a repair tool, such as Oracle GoldenGate Veridata, to repair any out-of-sync conditions.

To Switch Users to the Primary System

1. On the primary system, run the script that grants insert, update, and delete permissions to the users of the business applications.
2. On the primary system, run the script that enables triggers and cascade delete constraints.
3. On the primary system, run the scripts that fail over the application server, start applications, and copy essential files that are not part of the replication environment.
4. On the primary system, start the primary Extract process.

   START EXTRACT ext_1
   

5. On the primary system, allow users to access the applications.