Oracle GoldenGate 11g: Configuration for High Availability

 

Oracle GoldenGate 11g Implementer’s guide

Design, install, and configure high-performance data replication solutions using Oracle GoldenGate

• The very first book on GoldenGate, focused on design and performance tuning in enterprise-wide environments
• Exhaustive coverage and analysis of all aspects of the GoldenGate software implementation, including design, installation, and advanced configuration
• Migrate your data replication solution from Oracle Streams to GoldenGate
• Design a GoldenGate solution that meets all the functional and non-functional requirements of your system
• Written in a simple illustrative manner, providing step-by-step guidance with discussion points
• Goes way beyond the manual, appealing to Solution Architects, System Administrators and Database Administrators      

This includes the following discussion points:

• Shared storage options
• Configuring clusterware for GoldenGate
• GoldenGate on Exadata
• Failover

We also touch upon the new features available in Oracle 11g Release 2, including the Database Machine, that provides a “HA solution in a box”.

GoldenGate on RAC

A number of architectural options are available to Oracle RAC, particularly surrounding storage. Since Oracle 11g Release 2, these options have grown, making it possible to configure the whole RAC environment using Oracle software, whereas in earlier versions, third party clusterware and storage solutions had to be used. Let’s start by looking at the importance of shared storage.

Shared storage

The secret to RAC is “share everything” and this also applies to GoldenGate. RAC relies on shared storage in order to support a single database having multiple instances, residing on individual nodes. Therefore, as a minimum the GoldenGate checkpoint and trail files must be on the shared storage so all Oracle instances can “see” them. Should a node fail, a surviving node can “take the reins” and continue the data replication without interruption.

Since Oracle 11g Release 2, in addition to ASM, the shared storage can be an ACFS or a DBFS.

Automatic Storage Management Cluster File System (ACFS)

ACFS is Oracle’s multi-platform, scalable file system, and storage management technology that extends ASM functionality to support files maintained outside of the Oracle Database. This lends itself perfectly to supporting the required GoldenGate files. However, any Oracle files that could be stored in regular ASM diskgroups are not supported by ACFS. This includes the OCR and Voting files that are fundamental to RAC.

Database File System (DBFS)

Another Oracle solution to the shared filesystem is DBFS, which creates a standard file system interface on top of files and directories that are actually stored as SecureFile LOBs in database tables. DBFS is similar to Network File System (NFS) in that it provides a shared network file system that “looks like” a local file system.

On Linux, you need a DBFS client that has a mount interface that utilizes the Filesystem in User Space (FUSE) kernel module, providing a file-system mount point to access the files stored in the database.

This mechanism is also ideal for sharing GoldenGate files among the RAC nodes. It also supports the Oracle Cluster Registry (OCR) and Voting files, plus Oracle homes.

DBFS requires an Oracle Database 11gR2 (or higher) database. You can use DBFS to store GoldenGate recovery related files for lower releases of the Oracle Database, but you will need to create a separate Oracle Database 11gR2 (or higher) database to host the file system.

Configuring Clusterware for GoldenGate

Oracle Clusterware will ensure that GoldenGate can tolerate server failures by moving processing to another available server in the cluster. It can support the management of a third party application in a clustered environment. This capability will be used to register and relocate the GoldenGate Manager process.

Once the GoldenGate software has been installed across the cluster and a script to start, check, and stop GoldenGate has been written and placed on the shared storage (so it is accessible to all nodes), the GoldenGate Manager process can be registered in the cluster. Clusterware commands can then be used to create, register and set privileges on the virtual IP address (VIP) and the GoldenGate application using standard Oracle Clusterware commands.

The Virtual IP

The VIP is a key component of Oracle Clusterware that can dynamically relocate the IP address to another server in the cluster, allowing connections to failover to a surviving node. The VIP provides faster failovers compared to the TCP/IP timeout based failovers on a server’s actual IP address. On Linux this can take up to 30 minutes using the default kernel settings!

The prerequisites are as follows:

1. The VIP must be a fixed IP address on the public subnet.
2. The interconnect must use a private non-routable IP address, ideally over Gigabit Ethernet.

Use a VIP to access the GoldenGate Manager process to isolate access to the Manager process from the physical server. Remote data pump processes must also be configured to use the VIP to contact the GoldenGate Manager.

The following diagram illustrates the RAC architecture for 2 nodes (rac1 and rac2) supporting 2 Oracle instances (oltp1 and oltp2). The VIPs are 11.12.1.6 and 11.12.1.8 respectively, in this example:

The user community or application servers connect to either instance via the VIP and a load balancing database service, that has been configured on the database and in the client’s SQL*Net tnsnames.ora file or JDBC connect string.

The following example shows a typical tnsnames entry for a load balancing service. Load balancing is the default and does not need to be explicitly configured. Hostnames can replace the IP addresses in the tnsnames.ora file as long as they are mapped to the relevant VIP in the client’s system hosts file.

OLTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.12.1.6)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.12.1.8)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oltp)
)
)

This is the recommended approach for scalability and performance and is known as active-active. Another HA solution is the active-passive configuration, where users connect to one instance only leaving the passive instance available for node failover.

The term active-active or active-passive in this context relates to 2-node RAC environments and is not to be confused with the GoldenGate topology of the same name.

On Linux systems, the database server hostname will typically have the following format in the /etc/hosts file.

For Public VIP: <hostname>-vip

For Private Interconnect: <hostname>-pri

The following is an example hosts file for a RAC node:

127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6

#Virtual IP Public Address
11.12.1.6 rac1-vip rac1-vip
11.12.1.8 rac2-vip rac2-vip

#Private Address
192.168.1.33 rac1-pri rac1-pri
192.168.1.34 rac2-pri rac2-pri

Creating a GoldenGate application

The following steps guide you through the process of configuring GoldenGate on RAC. This example is for an Oracle 11g Release 1 RAC environment:

1. Install GoldenGate as the Oracle user on each node in the cluster or on a shared mount point that is visible from all nodes. If installing the GoldenGate home on each node, ensure the checkpoint and trails files are on the shared filesystem.
2. Ensure the GoldenGate Manager process is configured to use the AUTOSTART and AUTORESTART parameters, allowing GoldenGate to start the Extract and Replicat processes as soon as the Manager starts.
3. Configure a VIP for the GoldenGate application as the Oracle user from 1 node.

   <CLUSTERWARE_HOME>/bin/crs_profile -create ggsvip
   -t application
   -a <CLUSTERWARE_HOME>/bin/usrvip
   -o oi=bond1,ov=11.12.1.6,on=255.255.255.0

   CLUSTERWARE_HOME is the oracle home in which Oracle Clusterware is installed. E.g. /u01/app/oracle/product/11.1.0/crs
   ggsvip is the name of the application VIP that you will create.
   oi=bond1 is the public interface in this example.
   ov=11.12.1.6 is the virtual IP address in this example.
   on=255.255.255.0 is the subnet mask. This should be the same subnet mask for the public IP address.

4. Next, register the VIP in the Oracle Cluster Registry (OCR) as the Oracle user.

   <CLUSTERWARE_HOME>/bin/crs_register ggsvip

5. Set the ownership of the VIP to the root user who assigns the IP address. Execute the following command as the root user:

   <CLUSTERWARE_HOME>/bin/crs_setperm ggsvip -o root

6. Set read and execute permissions for the Oracle user. Execute the following command as the root user:

   <CLUSTERWARE_HOME>/bin/crs_setperm ggsvip -u user:oracle:r-x

7. As the Oracle user, start the VIP.

   <CLUSTERWARE_HOME>/bin/crs_start ggsvip

8. To verify the the VIP is running, execute the following command then ping the IP address from a different node in the cluster.

   <CLUSTERWARE_HOME>/bin/crs_stat ggsvip -t
   Name Type Target State Host
   —— ——- —— ——- ——
   ggsvip application ONLINE ONLINE rac1

   ping -c3 11.12.1.6
   64 bytes from 11.12.1.6: icmp_seq=1 ttl=64 time=0.096 ms
   64 bytes from 11.12.1.6: icmp_seq=2 ttl=64 time=0.122 ms
   64 bytes from 11.12.1.6: icmp_seq=3 ttl=64 time=0.141 ms
   — 11.12.1.6 ping statistics —
   3 packets transmitted, 3 received, 0% packet loss, time 2000ms
   rtt min/avg/max/mdev = 0.082/0.114/0.144/0.025 ms

9. Oracle Clusterware supports the use of “Action” scripts within its configuration, allowing bespoke scripts to be executed automatically during failover. Create a Linux shell script named ggs_action.sh that accepts 3 arguments: start, stop or check. Place the script in the <CLUSTERWARE_HOME>/crs/public directory on each node or if you have installed GoldenGate on a shared mount point, copy it there.
   • Ensure that start and stop: returns 0 if successful, 1 if unsuccessful.
   • check: returns 0 if GoldenGate is running, 1 if it is not running.
10. As the Oracle user, make sure the script is executable.

    chmod 754 ggs_action.sh

11. To check the GoldenGate processes are running, ensure the action script has the following commands. The following example can be expanded to include checks for Extract and Replicat processes:
    • First check the Linux process ID (PID) the GoldenGate Manager process is configured to use.

      GGS_HOME=/mnt/oracle/ggs # Oracle GoldenGate
      home
      pid=`cut -f8 ${GGS_HOME}/dirpcs/MGR.pcm`

    • Then, compare this value (in variable $pid) with the actual PID the Manager process is using. The following example will return the correct PID of the Manager process if it is running.

      ps -e |grep ${pid} |grep mgr |cut -d " " -f2

12. The code to start and stop a GoldenGate process is simply a call to ggsci.

    ggsci_command=$1
    ggsci_output=`${GGS_HOME}/ggsci << EOF
    ${ggsci_command}
    exit
    EOF`

13. Create a profile for the GoldenGate application as the Oracle user from 1 node.

    <CLUSTERWARE_HOME>/bin/crs_profile
    -create goldengate_app
    -t application
    -r ggsvip
    -a <CLUSTERWARE_HOME>/crs/public/ggs_action.sh
    -o ci=10

    CLUSTERWARE_HOME is the Oracle home in which Oracle Clusterware is installed. For example: /u01/app/oracle/product/11.1.0/crs
    -create goldengate_app the application name is goldengate_app.
    -r specifies the required resources that must be running for the application to start. In this example, the dependency is the VIP ggsvip must be running before Oracle GoldenGate starts.
    -a specifies the action script. For example: <CLUSTERWARE_HOME>/crs/public/ggs_action.sh
    -o specifies options. In this example the only option is the Check Interval which is set to 10 seconds.

14. Next, register the application in the Oracle Cluster Registry (OCR) as the oracle user.

    <CLUSTERWARE_HOME>/bin/crs_register goldengate_app

15. Now start the Goldengate application as the Oracle user.

    <CLUSTERWARE_HOME>/bin/crs_start goldengate_app

16. Check that the application is running.

    <CLUSTERWARE_HOME>/bin/crs_stat goldengate_app -t
    Name Type Target State Host
    —— —— ——– —– —-
    goldengate_app application ONLINE ONLINE rac1

17. You can also stop GoldenGate from Oracle Clusterware by executing the following command as the oracle user:

    CLUSTERWARE_HOME/bin/crs_stop goldengate_app

Oracle has published a White Paper on “Oracle GoldenGate high availability with Oracle Clusterware”. To view the Action script mentioned in this article, refer to the document, which can be downloaded in PDF format from the Oracle Website at the following URL:
http://www.oracle.com/technetwork/middleware/goldengate/overview/ha-goldengate-whitepaper-128197.pdf