Como instalar o Goldengate em Cluster usando agentes XAG

An Implementation of GoldenGate Monitoring configured in a Real Application Cluster Environment

Essa postagem não é minha, todos os créditos de desse post são para : http://maazanjum.com/2014/06/19/an-implementation-of-goldengate-monitoring-configured-in-a-real-application-cluster-environment/

Nearly a year ago I wrote about GoldenGate monitoring using a Metric Extension, and then later on expanded upon the creation of a Metric Extension, which I’ve installed and configured at several customer sites where GoldenGate was running on a stand alone server.

Over the last few months, several people had approached me on the process, as well as improved on it. One such improvement is accredited to Bobby Curtis (@dbasolved) who taught me how to buffer in Perl. Bobby also has a neat collection of monitoring scripts for GoldenGate, you can find them here.

The latest implementation, is a collaboration led by my tenacious and talented colleague Tucker Thompson (LinkedIn). He is responsible to maintain and manage the Enterprise Manager environment from an operational perspective for (among many other) a rather large retail corporation – let’s call them Furry Feet (FF). Their environment contains multiple Exadata machines including several dozen non-Exadata environments.

A few weeks ago he approached me with a question on GoldenGate monitoring with Enterprise Manager that does not involve the GoldenGate Plugin. In his own words, Tucker described the problem below:

“The client was previously using [custom built] crontab scripts to monitor multiple items (including GoldenGate) in their large Exadata environment, despite having an OEM 12c implementation. Our desire was to move all of their crontab elements into a centralized strategy utilizing OEM 12c.

The current GoldenGate plugin for OEM 12c was tested, but seemed very buggy and the client was not ready to use it yet.

The client has AGCTL configured to assist in running multiple highly available GoldenGate instances in the same Exadata DBM.”

As per Oracle’s documentation; Agent Control (AGCTL) is the agent command line utility to manage bundled agents (XAG) for application High Availability (HA) using Oracle Grid Infrastructure.

Tucker explains why this solution wasn’t always reliable by stating:

“The crontab scripts operated per compute node to check the logs for errors, send a lag status to the elements, and check the AGCTL status to determine where the instance was running. However, we found that any alert in the alert.log triggered a critical alert through their ticketing system, as it gripped for any ORA-XXXXX error.

For instance, if there were any long running queries, we would get a ticket created. Another major issue was that we would encounter multiple issues where the status of GoldenGate in AGCTL did not accurately reflect is actual status. For example, an instance could be showing as down through AGCTL, but through GGSCI, the status was RUNNING.”

We seem to find a pattern with issues in monitoring of GoldenGate, don’t we? This doesn’t necessarily mean that the tool itself is at fault but rather the available options. Since I had already come up with an adequate way to monitor GoldenGate using Metric Extensions in EM12c, but it was designed to run against a host target specifically where GoldenGate runs in a stand alone mode. In FF’s environment, there were several GoldenGate instances running across the various Exadata compute nodes that were configured to fail-over and restart GoldenGate seamlessly. This made for an interesting problem to resolve because my initial script assumes a static GoldenGate home.

As an example, three Nodes in a cluster each with a different GoldenGate instance that is managed by XAG.

NewImage

Tucker’s innovative solution was to retrieve the information from clusterware via AGCTL to run the GoldenGate check against the nodes where the instance is currently running.

“What this script does is execute against the Exadata Database Machine as a target. This means that it will first find a compute node available, run AGCTL to determine the names of the GoldenGate instances, and respective nodes they are configured to run on. This information is always available from any node, and the script does not take into account where AGCTL thinks GoldenGate is actually currently running.

Next, with that information registered, the script runs olsnodes to grab the host names of all compute nodes registered in the DBM. It then uses information pulled from the AGCTL configuration per GoldenGate instance to ssh to each compute node and grep for the manager process for that specific GG instance. With the manager found running on a certain node, the script then runs ggsci from that node against that GG instance, and parses the results to tell us if the different components are running, stopped, or abended. It will also take the lag into consideration and set warning thresholds, rather than a critical alert for any amount of lag. The script even goes as far to add the agctl status as an informational column, so it can be seen if agctl is showing as down, but GGSCI shows all processes running fine.

NewImage

If the manager is not found anywhere in the Exadata environment, it extracts the first node that the instance is configured to run on from the AGCTL configuration, and runs ggsci from that node. This allows the script to still show all of the components as stopped or abended, and their respective lags.”

Another thing to note is that if a GoldenGate instance relocates to a different node for some reason, instead of just getting an alert that the instance went down we would get that alert, followed by a clear alert once the GoldenGate objects (manager, extract, replicat, pump) are back up and running on a different node.

Once tested via the Metric Extension setup screens, the output looks like:

NewImage

This strategy allows for one script to monitor multiple diagnostics for across GoldenGate instances configured to run in a large, highly available environment.”

It outputs the following information when run from a prompt.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
ggate_baby077|MANAGER|MANAGER|RUNNING|0|0|0|exababy01db02|Goldengate  instance 'ggate_baby077' is running on exababy01db02|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby077
ggate_baby077|CODS_E01|EXTRACT|RUNNING|22940|25|0|exababy01db02|Goldengate  instance 'ggate_baby077' is running on exababy01db02|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby077
ggate_baby077|CODS_R01|REPLICAT|RUNNING|0|3|0|exababy01db02|Goldengate  instance 'ggate_baby077' is running on exababy01db02|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby077
ggate_baby077|CODS_R02|REPLICAT|RUNNING|0|3|0|exababy01db02|Goldengate  instance 'ggate_baby077' is running on exababy01db02|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby077
ggate_baby077|CODS_R03|REPLICAT|RUNNING|0|3|0|exababy01db02|Goldengate  instance 'ggate_baby077' is running on exababy01db02|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby077
ggate_baby077|CODS_R04|REPLICAT|RUNNING|0|3|0|exababy01db02|Goldengate  instance 'ggate_baby077' is running on exababy01db02|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby077
ggate_baby077|CODS_R05|REPLICAT|RUNNING|0|3|0|exababy01db02|Goldengate  instance 'ggate_baby077' is running on exababy01db02|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby077
ggate_baby077|CODS_R06|REPLICAT|RUNNING|0|3|0|exababy01db02|Goldengate  instance 'ggate_baby077' is running on exababy01db02|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby077
ggate_baby077|CODS_R08|REPLICAT|RUNNING|0|3|0|exababy01db02|Goldengate  instance 'ggate_baby077' is running on exababy01db02|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby077
ggate_baby077|CODS_R09|REPLICAT|RUNNING|0|3|0|exababy01db02|Goldengate  instance 'ggate_baby077' is running on exababy01db02|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby077
ggate_baby077|CODS_R10|REPLICAT|RUNNING|0|3|0|exababy01db02|Goldengate  instance 'ggate_baby077' is running on exababy01db02|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby077
ggate_baby077|CODS_R11|REPLICAT|RUNNING|0|3|0|exababy01db02|Goldengate  instance 'ggate_baby077' is running on exababy01db02|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby077
ggate_baby077|CODS_R12|REPLICAT|RUNNING|0|3|0|exababy01db02|Goldengate  instance 'ggate_baby077' is running on exababy01db02|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby077
ggate_baby19sss|MANAGER|MANAGER|RUNNING|0|0|0|exababy01db04|Goldengate  instance 'ggate_baby19sss' is running on exababy01db04|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby19ssa
ggate_baby19sss|MKCN_E01|EXTRACT|RUNNING|7|6|0|exababy01db04|Goldengate  instance 'ggate_baby19sss' is running on exababy01db04|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby19ssa
ggate_baby19sss|POMS_E01|EXTRACT|RUNNING|9|5|0|exababy01db04|Goldengate  instance 'ggate_baby19sss' is running on exababy01db04|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby19ssa
ggate_baby19sss|POMS_E02|EXTRACT|RUNNING|8|9|0|exababy01db04|Goldengate  instance 'ggate_baby19sss' is running on exababy01db04|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby19ssa
ggate_baby19sss|POMS_P02|EXTRACT|RUNNING|0|6|0|exababy01db04|Goldengate  instance 'ggate_baby19sss' is running on exababy01db04|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby19ssa
ggate_baby19sss|XFER_E01|EXTRACT|RUNNING|8|0|0|exababy01db04|Goldengate  instance 'ggate_baby19sss' is running on exababy01db04|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby19ssa
ggate_baby19sss|XFER_EM1|EXTRACT|RUNNING|7|1|0|exababy01db04|Goldengate  instance 'ggate_baby19sss' is running on exababy01db04|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby19ssa
ggate_baby19sss|XFER_P01|EXTRACT|RUNNING|0|8|0|exababy01db04|Goldengate  instance 'ggate_baby19sss' is running on exababy01db04|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby19ssa
ggate_baby19sss|XFER_PM1|EXTRACT|RUNNING|0|6|0|exababy01db04|Goldengate  instance 'ggate_baby19sss' is running on exababy01db04|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby19ssa
ggate_baby19sss|BASE_R01|REPLICAT|RUNNING|0|7|0|exababy01db04|Goldengate  instance 'ggate_baby19sss' is running on exababy01db04|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby19ssa
ggate_baby19sss|MKCN_R01|REPLICAT|RUNNING|0|7|0|exababy01db04|Goldengate  instance 'ggate_baby19sss' is running on exababy01db04|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby19ssa
ggate_baby19sss|POMS_R01|REPLICAT|RUNNING|0|5|0|exababy01db04|Goldengate  instance 'ggate_baby19sss' is running on exababy01db04|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_baby19ssa
ggate_test|MANAGER|MANAGER|STOPPED|0|0|1|exababy01db01|Goldengate  instance 'ggate_test' is not running|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_test
ggate_test|TEST_E01|EXTRACT|ABENDED|6|7|2|exababy01db01|Goldengate  instance 'ggate_test' is not running|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_test
ggate_test|TEST_R01|REPLICAT|ABENDED|0|0|2|exababy01db01|Goldengate  instance 'ggate_test' is not running|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_test

image002

The perl script itself is fairly simple, and can be plugged into the Metric Extension example from earlier. The only caveat is, that your GoldenGate environment must be an XAG resource.

Once the Metric Extension is deployed, its information is accessible in the “All Metrics” section for the Exadata Database Machine.

image001

The point of this exercise was to solve a particular use-case where GoldenGate instances are configured as clusterware resources which can be restarted on different nodes each time. What I would like to see is, an implementation of this GoldenGate monitoring script in an implementation that doesn’t necessarily use XAG in a clustered environment.

Thanks again to Tucker for coming up with the idea to retrieve the information and it was fun to incorporate my original script into his version.

Cheers!

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em GOLDENGATE, GRID CONTROL, RAC e marcado . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s