DBASolved

Subscribe to  DBASolved feed  DBASolved
Updated: 3 days 10 hours ago

Find Docker Container IP Address?

Wed, 2019-08-14 11:38

This is just simple post for later reference, if I need it …

In setting up some docker containers for testing Oracle GoldenGate, I needed to find the IP address of the container where my database was running (I keep my database in a seperate container in order not to rebuild it every time).

To find the address of my database container, I had to use the docker “inspect” command. This command returns low level infomation on Docker objects.

The syntax is as follows:

docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}'  

 

 

Enjoy!!!!

Categories: DBA Blogs

Identifying the TNS_ADMIN for an deployment in GoldenGate Microservices

Wed, 2019-08-14 10:30

Setting up network routing/access with Oracle GoldenGate Microservices (12.3 and later) can be an interesting experience. As many in the Oracle space knows, you simply have to setup TNS_ADMIN to point to the location where your sqlnet.ora and tnsnames.ora files are located. This would normally look like this:


export TNS_ADMIN=${ORACLE_HOME}/network/admin


or


export TNS_ADMIN=$(ORA_CLIENT_HOME}/network/admin

 

These examples still working for Oracle GoldenGate Classic, however, when we start looking at this through the lens of Microservices; it changes a bit. Within the Oracle GoldenGate Microservices architecture the TNS_ADMIN enviroment variable has to be set “per deployment”. Depending on the number of deployments that are deployed with in the architecture, it is possible to have 1+N TNS_ADMIN variables.

As a illistration, it would look something like this:

As you can see this is specific to the Microservices architecture and how to setup network routing for individual deployments.

Setting TNS_ADMIN

How do you set the TNS_ADMIN environment variable for each deployment? It is quite simple, when you are building a deployment using the Oracle GoldenGate Configuration Assistant (OGGCA). Priort to running OGGCA, you can set the TNS_ADMIN variable at the OS level and the OGGCA will pick it up for that run and configuration of that specific deployment.

Optionally, you don’t have to set it at the OS level. During the OGGCA walkthrough, you will be able to set the variable manually. The OGGCA will not move past the enviornment variables step until it is provided.

Changing TNS_ADMIN

After building a deployment, you many want to chang the location of your network related files. This can be done from either the HTML5 web page for the deployment or from REST API.

To change TNS_ADMIN from the HTML5 pages within Oracle GoldenGate Microservices, you need to start at the ServiceManager Overview page. At the bottom on this page, there is a section called “Deployments”

The select the deployment you want to work with. After clicking on the deployment name, you should now be on the “Deployment Information” page. This page has two tabs at the top. The first tab is related to details of the deployment. The second table is related to configurations for the deployment.

Within the second tab – Configurations, is where you can set/change the environment variables for the deployment. In this case, we want to to modify the TNS_ADMIN enviornment variable.

 

To the right of the variable in the “Actions” column, click on the pencil icon. This will allow you to edit the environment variable. Change to the new location and save it. You may need to restart the deployment (hint, that step is on the ServiceManager Overview page).

At this point, you should now be able to change the location of your TNS_ADMIN variable. This is also handy for Oracle GoldenGate Microserivces on Marketplace as well … just saying.

Using REST API

This same process can be done quickly using the REST API. The below sample code, is only and sample and has not been tested. Use at your own risk!

curl -X PATCH \
  <a href="https://<ip_address>/services/v2/deployments/alpha" target="_blank" rel="noopener">https://<ip_address>/services/v2/deployments/alpha</a> \
  -H 'cache-control: no-cache' \
  -d '{
    "oggHome":"/opt/app/oracle/product/19.1.0/oggcore_1",
    "oggEtcHome":"/opt/app/oracle/gg_deployments/Atlanta/etc",
    "oggVarHome":"/opt/app/oracle/gg_deployments/Atlanta/var",
    "environment"{
    	"tns_admin":"/opt/app/oracle/product/18.1.0/network/admin"
    }
    "status":"restart"
}'

Enjoy!!!

Categories: DBA Blogs

AdminClient and Set Commands

Fri, 2019-08-02 13:32

AdminClient is the “new” command line utility that is used with Oracle GoldenGate Microservices. Initally, AdminClient was released with Oracle GoldenGate 12c (12.3.0.0.1) and enhanced in each release there after. With this new command line tool, there are a few things you can do with it that makes it a powerful tool for administering Oracle GoldenGate.

Reminder: This is only avaliable in Oracle GoldenGate Microservices Editions.

Features that make this tool so nice:

  • Default command line tool for Microservices
  • Can be installed on a remote linux machine or Windows Workstations/Laptops
  • Can “Set” advanced setting that provide a few nice features

The third bullet is what will be the focus of this post.

The “Set” command within AdminClient provide you with options that allow you to extend the command line for Oracle GoldenGate. These features are:

After starting the AdminClient, it is possible to see the current settings of these values by using the SHOW command:

Oracle GoldenGate Administration Client for Oracle
Version 19.1.0.0.1 OGGCORE_19.1.0.0.0_PLATFORMS_190524.2201


Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.


Linux, x64, 64bit (optimized) on May 25 2019 02:00:23
Operating system character set identified as US-ASCII.


OGG (not connected) 1> show


Current directory: /home/oracle/software/scripts
COLOR            : OFF
DEBUG            : OFF
EDITOR           : vi
PAGER            : more
VERBOSE          : OFF


OGG (not connected) 2>

 

If you want to change any of these settings, you can simply run the “set <option> <value>” at the command prompt. For example, I want to turn on the color option.

OGG (not connected) 2> set color on


OGG (not connected) 3> show


Current directory: /home/oracle/software/scripts
COLOR            : ON
DEBUG            : OFF
EDITOR           : vi
PAGER            : more
VERBOSE          : OFF


OGG (not connected) 4>

 

Now, that we can set these values and change how AdminClient responds; how can these settings be automated (to a degree)? In order to do this, you can write a wrapper around the execution of the AdminClient executable (similar to my post on resolving OGG-01525 error). Within this wrapper, the setting you want to change has to be prefixed with ADMINCLIENT_. This would like this:

export ADMINCLIENT_COLOR=<value>

Note: The <value> is case sensitive.

My shell script for AdminClient with the settings I like to have turned on is setup as follows:

#/bin/bash


export OGG_VAR_HOME=/tmp
export ADMINCLIENT_COLOR=ON
export ADMINCLIENT_DEBUG=OFF


${OGG_HOME}/bin/adminclient

 

Now, when I start AdminClient, I have all the settings I want for my environment. Plus, the ones I do not set will take the default settings.

[oracle@ogg19c scripts]$ sh ./adminclient.sh
Oracle GoldenGate Administration Client for Oracle
Version 19.1.0.0.1 OGGCORE_19.1.0.0.0_PLATFORMS_190524.2201


Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.


Linux, x64, 64bit (optimized) on May 25 2019 02:00:23
Operating system character set identified as US-ASCII.


OGG (not connected) 1> show


Current directory: /home/oracle/software/scripts
COLOR            : ON
DEBUG            : OFF
EDITOR           : vi
PAGER            : more
VERBOSE          : OFF


OGG (not connected) 2>

 

Enjoy!!!

Categories: DBA Blogs

ServiceManager … Manually start/stop

Tue, 2019-07-30 09:58

Oracle GoldenGate Microservices, starting in 12c (12.3.0.0.1) through 19c (19.1.0.0.1), provide a set of services that you can interact with via a webpage, command line, REST API, and PL/SQL. All of which is great; however, for any of these items to work the ServiceManager has to be up and running.

There are three ways configure ServiceManager when an environment is initally setup. These three ways are:

  • Manually
  • As a daemon
  • Integration with XAG agent (9.1 or later)

For this post, I’ll just show you how to start or stop ServiceManager manually. Manually starting or stopping the ServiceManager is the default setting if you do not select either of the other two options while running Oracle GoldenGate Configuration Assistant (OGGCA.sh).

In order to start or stop the ServiceManager manually, you have to make sure you have two files. These files are:

  • startSM.sh
  • stopSM.sh

Both of these files will be in the $DEPLOYMENT_HOME/bin directory for the ServiceManager. On my system this location is:

/opt/app/oracle/gg_deployments/ServiceManager/bin

Note: If you are running ServiceManager as a daemon, you will not have these files. In the bin directory you will find a file that is used to register ServiceManager as a daemon.

Before you can start or stop the ServiceManager manually, there are two (2) environment variables that need to be set. These environment variables are:

  • OGG_ETC_HOME
  • OGG_VAR_HOME

These environment variables are set to the etc and var directory locations for the ServiceManager deployment. On my system these are set to:

export OGG_ETC_HOME=/opt/app/oracle/gg_deployments/ServiceManager/etc
export OGG_VAR_HOME=/opt/app/oracle/gg_deployments/ServiceManager/var

Now with all these requirements met, I can now go back to the $DEPLOYMENT_HOME/bin directory and start or stop the ServiceManager.

[oracle@ogg19c bin]$ cd /opt/app/oracle/gg_deployments/ServiceManager/bin
[oracle@ogg19c bin]$ sh ./startSM.sh

[oracle@ogg19c bin]$ sh ./startSM.sh
Starting Service Manager process…
Service Manager process started (PID: 376)

In order to stop the ServiceManager manually:

[oracle@ogg19c bin]$ cd /opt/app/oracle/gg_deployments/ServiceManager/bin
[oracle@ogg19c bin]$ sh ./stopSM.sh
Stopping Service Manager process (PID: 376)…
Service Manager stopped

Enjoy!!!

Categories: DBA Blogs

Certs and AdminClient … How to login?

Thu, 2019-07-25 15:26

I’ve been building a test environment using Docker for sometime (over and over), to validate some items within Oracle GoldenGate Microservices (current release as of writing – 19.1.0.0.1). Part of setting Oracle GoldenGate Microservices is to make the environment secure by using certificates. Per Oracle documentation, you can use Self-Signed Certificates for testing purposes (more on that in this post).

In my testing, I have built an Oracle GoldenGate 19c Microservices configuraiton with two deployments (Atlanta and New York). I can access the ServiceManager and login to the associated HTML5 pages with no problem. When I went to run items from the command line (adminclient), I wouldn’t login to the ServiceManager/Deployment due to a Network Error.

[oracle@ogg19c scripts]$ sh ./adminclient.sh
Oracle GoldenGate Administration Client for Oracle
Version 19.1.0.0.1 OGGCORE_19.1.0.0.0_PLATFORMS_190524.2201</p>
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

Linux, x64, 64bit (optimized) on May 25 2019 02:00:23
Operating system character set identified as US-ASCII.

OGG (not connected) 1> connect https://ogg19c:16000 deployment NewYork as oggadmin password ********

ERROR: Network error - Certificate validation error

OGG (not connected) 2> exit

This got me thinking and started to ask some questions internally. Which lead me to a new envionrment parameter. This enviornment variable is OGG_CLIENT_TLS_CAPATH. The OGG_CLIENT_TLS_CAPATH variable is used to specify the root certificate athority needed to login to the ServiceManager/Deployment that has been secured using the certificate … in my case, my Self-Signed Certs.

After setting the enviornment variable OGG_CLIENT_TLS_CAPATH, I can now login to the AdminClient as expected.

[oracle@ogg19c scripts]$ export OGG_CLIENT_TLS_CAPATH=/home/oracle/wallet/Root_CA.pem
[oracle@ogg19c scripts]$ sh ./adminclient.sh
Oracle GoldenGate Administration Client for Oracle
Version 19.1.0.0.1 OGGCORE_19.1.0.0.0_PLATFORMS_190524.2201


Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.


Linux, x64, 64bit (optimized) on May 25 2019 02:00:23
Operating system character set identified as US-ASCII.


OGG (not connected) 1> connect https://ogg19c:16000 deployment NewYork as oggadmin password ********


OGG (https://ogg19c:16000 NewYork) 2>

 

I found this quite helpful.

Enjoy!!!!

Categories: DBA Blogs

Simple way to get rid of OGG-01525 for AdminClient

Wed, 2019-07-24 22:22

While installing Oracle GoldenGate 19c tonight, I was wanting to work from the command line and play around with AdminClient. For those who are not up to speed, AdminClient is the replacement for GGSCI when using Microservices.

AdminClient in Microservices provide the same functionalty as GGSCI, but it is a thin, lightweight tool that can be installed on a remote linux box or windows desktop/laptop. It allows you to make simple GGSCI commands and convert them into RESTP API calls on the backend. All the while, providing the same command line interface as GGSCI provided.

It is great that there is still a command line option for Oracle GoldenGate within the Microservices Architecture, however, when you start it you get presented with a Warning. The Warning is an OGG-01525 message that states there is no place to produce a trace file for the session.

WARNING OGG-01525 Failed to open trace output file, ‘/opt/app/oracle/product/19.1.0/oggcore_1/var/log/adminclient.log’, error 2 (No such file or directory).

So how do you fix this issue?

Note: This is not a bug! AdminClient was designed this way.

In order to fix this issue and get rid of the warning, you need to set a new enviornment variable. Since Oracle GoldenGate Microservices has been out for a bit over 2 year, I guess the environment variable isn’t that new. Any ways, the environment variable that needs to be set is OGG_VAR_HOME.

export OGG_VAR_HOME=/tmp

The OGG_VAR_HOME variable is used to tell AdminClient where to keep the adminclient.log file. In my example above, I’m using the temp ( /tmp ) directory for the log file.

Now, there is only one problem with the OGG_VAR_HOME environment variable. This environment variable along with OGG_ETC_HOME have to be set per Deployment Home environment. Meaning, when you have more than one Deployment Home, these environment variables are specific to that deployment.

The question that is begging to asked is -> How do I assign an environment variable for AdminClient and Deployments at the same time?

To solve this problem, I just wrote a simple shell wrapper and placed it in my home directory. The script looks like this:

[oracle@ogg19c scripts]$ cat adminclient.sh
#/bin/bash

export OGG_VAR_HOME=/tmp

${OGG_HOME}/bin/adminclient

Now, I can run the shell script and execute the AdminClient without getting the OGG-01525 warning.

[oracle@ogg19c scripts]$ sh ./adminclient.sh
Oracle GoldenGate Administration Client for Oracle
Version 19.1.0.0.1 OGGCORE_19.1.0.0.0_PLATFORMS_190524.2201

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

Linux, x64, 64bit (optimized) on May 25 2019 02:00:23
Operating system character set identified as US-ASCII.

 

OGG (not connected) 1>

For everyone is likes the Oracle GoldenGate command line, you still have access there!

Enjoy!!!

Categories: DBA Blogs

Build an Oracle GoldenGate Compute Node on OCI Marketplace

Wed, 2019-06-26 11:03

Over the last few weeks, I have been working with Oracle GoldenGate Development on a solution to bring Oracle GoldenGate to the OCI framework. Well, last nigh it has been released – Oracle GoldenGate 19c Microservices on OCI Marketplace.

You can now provision Oracle GoldenGate 19c into an OCI compute node in under 11 minutes (assumption is 4 core box). This is huge and will allow you to quickly build Oracle GoldenGate Microservices environments that allow you to conect on-premise resources to cloud resources as well as cloud-to-cloud solutions.

To show you how easy it is to build an Oracle GoldenGate 19c Microservices Compute Node, I have put together this short video (don’t shoot the messenger – this is my first attempt at using videos on my blog).

For more details on how to build Oracle GoldenGate on the OCI Marketplace, you can reference the Oracle Documentation as well. The link to the Marketplace doc is: https://docs.oracle.com/en/middleware/goldengate/core/19.1/oggmp/

Enjoy!!

Categories: DBA Blogs

EZConnect Oracle GoldenGate to Oracle Database – On-Premise or Cloud (DBaaS)

Tue, 2019-06-25 12:53

With the release of Oracle GoldenGate 19c, it has been made easier to connect Oracle GoldenGate to the Oracle Database – source or target. Gone are the days that you need to update your local tnsnames.ora file to point to the desired database. The only thing you have to do now, is ensure that your database is reachable via an easy connect (ezconnect) string.

Within Oracle GoldenGate 19c Microservices, you would simply setup the connection in the Credential Store within the Administration Service of the deployment where your GoldenGate processes will be running. In order to do this, you simply do the following:

1. Login to the Administration Service as an authorized user
2. Navigate to the Configuration option

3. Click on the plus ( + ) sign to add a new Credential. Fill in all the needed information. When filling in the information for User Id make sure you use the format for EZConnect:

<username>@<host>:<port>/<service_name>

4. Then click Submit

5. This will result in a useridalias connection being created and stored for the database. At this point, you can test the connection by clicking on the database icon for the connection.

If you provided the correct password for the user id, you should easily login to the Oracle Database you are pointed to.

This connection feature has been carried over to Oracle GoldenGate 19c Classic as well; afterall connections are part of the core product and enables both archiectures to connect in the same manner. The only difference is you would be workign from the GGSCI command line building the credential store information with some typing. To use EZConnect from GGSCI, follow these steps:

1. Login to GGSCI

$ cd $OGG_HOME
$ ./ggsci

2. Add the credential store (if you don’t already have one)

GGSCI (ogg19cca) 1> add credentialstore

3. Add the user with ezconnect string to the credential store

GGSCI (ogg19cca) 2> alter credentialstore add user c##ggate@oggdbaas:1521/orclogg password ************ alias SGGATE domain OracleGoldenGate

4. Test the connection using the DBLOGIN option

GGSCI (ogg19cca) 3> dblogin useridalias SGGATE domain OracleGoldenGate
GGSCI (ogg19cca as c##ggate@orclogg/CDB$ROOT) 4>

Enjoy!!!

Categories: DBA Blogs

ServiceManager Daemon fails to start on reboot?

Thu, 2019-04-11 21:23

If you have been working with Oracle GoldenGate 12c (12.3.0.1.x) or 18c (18.1.0) recently, you may have setup the ServiceManager as a daemon process. This is a great option for setting up the ServiceManager. The benefit is provides is when you host is rebooted it comes back online and allows you to see all of your managers; however, there is a small issue with the process … not really the process but the environment where it runs.

If you are like me, you like to setup your environment variables for quick reference; I do this with $OGG_HOME a lot, especially setting up new enviornments. Having this enviornment variable set actually causes a problem with the ServiceManager. If the $OGG_HOME variable is set for the Oracle user environment; the ServiceManager will not restart upon reboot. This leads you to try to execute ServiceManager from the $OGG_HOME/bin directory, like so:

$ cd $OGG_HOME/bin
$ ./ServiceManager &

When you do this, you will be met with a message similar to this:

$ Service Manager is terminating because it cannot load the inventory from ‘/opt/app/oracle/product/18.1.0/oggcore_1/etc/conf/deploymentRegistry.dat

So what does this message mean? The ServiceManager is trying to find the deploymentRegistry.dat file. This file has all of the configurations that ServiceManager needs to start (BTW – DO NOT EDIT THIS FILE DIRECTLY). You will also notice that the directory structure after $OGG_HOME is not correct. That is because the deploymentRegistry.dat file is located in the $DEPLOYMENT_HOME for the ServiceManager.

So how do you fix this issue? The answer is quite simple. Just unset $OGG_HOME within the envrionment. Then ServiceManager can be started from $OGG_HOME/bin. If you remove/rename $OGG_HOME from your oracle profile (.bash_profile, .bashrc, etc…), the ServiceManager will restart on reboot as well.

When starting the ServiceManager from $OGG_HOME/bin, the output should look something similar to this:

[oracle@db18c_ogg18c bin]$ ./ServiceManager &
[1] 464
[oracle@db18c_ogg18c bin]$ Oracle GoldenGate Service Manager for Oracle
Version 18.1.0.0.0 OGGCORE_18.1.0.0.0_PLATFORMS_180928.0432

Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.

 

Linux, x64, 64bit (optimized) on Sep 28 2018 17:31:51
Operating system character set identified as US-ASCII.

 

[1]+ Done ./ServiceManager

With the ServiceManager stared, you can now access the HTML5 web page or the associated REST APIs.

Enjoy!!!

Categories: DBA Blogs

Running GoldenGate Installers within Docker containers on MacOS

Fri, 2019-04-05 21:56

Over the last few days, I’ve been trying to improve the speed at which I can setup and configure Oracle GoldenGate for testing purposes. What I settled on what setting up a Docker container to be a VM subsistute. Besides I’ve been playing with Docker, off and on, for over a year now; yet I’m finally investing the time to drive this forward.

One of the items I’ve been trying to solve is “how do I test the installers for Oracle GoldenGate”? In order to do this, I’ve had to figure out how to run an X11 interface from the Docker container. In order to do this, I’ve had to use the product XQuartz (download here).

Configure XQuartz

After you download the XQuartz software, you will need to enable the security setting “Allow connections from network clients”. This setting enables connections from remote applications, i.e. within the Docker container.

XQuartz -> Preferences

After setting the settings in XQuartz, restart the application. The restart will enable the settings. Then start XQuartz and minimize the window.

Prepare Docker

With te XQuartz running, now I can start using Docker to run the installers. The steps that I need to this are:

Start a Docker container:
When you start the Docker container, you will need to specify the DISPLAY port in the run command. Also notice that I’m using “docker.host.internal”.

docker run -dit –privileged –name oraogg -e DISPLAY=host.docker.internal:0 oraogg:18.1.0

Enable XHost from the OS:
Before you can bring the installer GUI up on my host display, I need to enable the host to allow the connection. This is simply enabling xhost for the localhost.

xhost + 127.0.0.1

Set DISPLAY within the Docker Container:
With the Docker container running, I can now access the container and specify the DISPLAY variable. Also notice that I’m using “docker.host.internal” inside of the Docker container.

docker exec -it oraogg /bin/bash
su – oracle

export DISPLAY=host.docker.internal:0

Run the Installer:
Now I’m ready to start the Oracle GoldenGate Installer(s). I navigate to the $OGG_HOME/bin and run the Oracle GoldenGate Configuration Assistant (OGGCA).

 

At this point, I can use xQuartz to provide the GUI interface for items I want to install within a Docker container.

Enjoy!!

Categories: DBA Blogs

Quickly build self-signed certificates for Oracle GoldenGate 18c

Wed, 2019-03-27 13:30

With Oracle GoldenGate Microservices 12c and 18c, the architecture can be configured to use SSL certificates for securing and replicating between sites. This is a huge improvement for securing the replication framework and makes it a lot simpler to replicate data over standard HTTPS ports.

In order to configure Oracle GoldenGate 12c/18c Microserivces with SSL certificates, you either have to generate a self-signed certificate or bring your own certificate (BYOC) to use. This also requires you to build an Oracle Wallet to store the certificates in. Additionally, to secure both sides of the replication environment within a Microservices environment; the wallet is required to be copied to any other environment where you will be replicating to or from.

In this post, I’m providing you with a simple python script that will build the Self-Signed Certificates needed for testing purposes. By using this overly-simplified, script you assume responsibility of using the generated certificates.

For more details on configuring Oracle GoldenGate Microservices 12c/18c using Self-Signed Certificates, refer to the Oracle documentation for Securing Oracle GoldenGate Microservices (here).

My overly-simple python script is located here.

Happy Securing and Enjoy!!!

Categories: DBA Blogs

Installing Oracle GoldenGate 18c Microservices on Windows…. I know, why?

Fri, 2019-03-01 12:00

I’m not a huge fan of the Windows platform although I’ve spent the early part of my career as a Systems Admin working on Windows. As I’ve gotten a bit older, Linux/Unix seem to be more my way of thinking; however, there are still many customers out there who run Oracle Databases and Microsoft SQL Server databases on the Windows platform. They still have a need to move data, right?

In this post, you’ll walk through how to install (yes a boring installation post) Oracle GoldenGate 18c Microservices on the Windows platform. This will include building the ServiceManager and first deployment. First there are a few prerequisites that need to be installed on the Microsoft Windows Platform. These items are:

  • Microsoft Visual C++ 2010 SP1 (64-bit)
  • Microsoft Visual C++ 2012 (64-bit)
  • Microsoft Visual C++ 2017 (64-bit)

You wll also need to confirm with the certification matrix on what platforms are supported. You can find the certification matrix here.

Installation:

Just like previous releases starting with Oracle GoldenGate 12c (12.1.x), where the Oracle Universal Installer (OUI) was added; Oracle GoldenGate 18c is installed the same way on Windows as on *nix platforms. For this post, you will look at the OUI and the Oracle GoldenGate Configuration Assistance (OGGCA) on Windows.

1. Unzip the downloaded zip file to a directory of your choice. The zip file can either be pulled from Oracle EDelivery or from the Oracle Technology Network. The command below will unzip the file from a Linux prompt, but you can use the Windows Extractor or Winzip to unzip the file.

unzip V980818-01.zip -d ./ogg18c_win

2. Navigate to the directory where the you unzipped the file. You will find a directory called fbo_gg_Windows_x64_services_shiphome. Inside of this directory you will find another called Disk1. You will need to be inside of the Disk1 directory.

3. From the Disk1 directory, you will run the Setup application. You can double-click this application. This will start the Oracle Universal Installer.

4. On the first step of the Oracle GoldenGate 18c installation, you need to specify what version of the Oracle Database you will run Oracle GoldenGate against. Since there is an Oracle Database 18c installed, select the Oracle GoldenGate for Oracle Database 18c.

5. The Software Location is the same at the Oracle GoldenGate Home (OGG_HOME) location. Specify where the software should be installed.

6. Finally, confirm everything you have selected during the installation wizard and click “Install”. At this time, you can also option to save the install steps into a response file by click the “Save Response File” button.

7. At this point, the installation will begin and should complete fairly quickly. Once the installation is done, the Oracle Universal Installer can be closed.

With the Oracle GoldenGate 18c (18.1.0) software installed, you now want to to build your ServiceManager and first deployment. In order to do this, you will need to go to the $OGG_HOME\bin directory and run the batch file called OGGCA.

To run the Oracle GoldenGate Configuration Assistant (OGGCA (batch file)):

1. Navigate to the Oracle GoldenGate Home and enter the $OGG_HOME\bin directory

2. Double-Click on the batch file for the Oracle GoldenGate Configuration Assistanct (oggca). This will start the configuration assistant.

3. Being that this is a new installation, you will provide all the following information:

  • Select the radio button for “Create New Service Manager”
  • Provide the ServiceManager Deployment Home location
  • Leave “Listening hostname/address” as the default
  • Provide a “Listening Port”
  • Select the checkbox for “Registering Service Manager as a system service/daemon”

After you have provide all the needed details, select Next to move through the wizard.

4. Next step, you will add a new GoldenGate Deployment. On this step, the only thing you need to do is click “Next”. The radio button should already be selected for you.

5. Now you will need to provide the deployment details. This consists of the Deployment Name and the Software Home. Keep in mind that the Software Home is the Oracle GoldenGate Home (OGG_HOME). Should be automatically populated as well.

6. After defining the name of the Deployment, you are given the opportunity to provide the deployment home location. On this screen, you can also choose to customize the locations of various deployment files. For now, just provide a deployment home.

7. Next step is to make sure your environment variables are correct.

8. Provide an administrator account. This account is defined at the “Security Role” within the Microservices Deployment framework. This is the same account that will be used to login to the ServiceManager and associated Deployments. If this account is lost, there is no way to recreate the account to date. This same account will need to be use when creating additional deployments under the same ServiceManager.

9. At this point, you have to choose if you want your deployment to be secure or un-secure. To simplify the understanding of the install, lets go with un-secure; however, we will not be able to change the decision unless we recreate the deployment. Use the checkbox for SSL/TLS security very wisely.

10. Now you will assign port numbers to all the services within the deployment. These port numbers will allow you to access each service individually of each other. There is a port number for the following services:

  • Administration Server
  • Distribution Server
  • Receiver Server
  • Performance Metric Server – TCP
  • Performance Metric Server – UDP

Additionally, you need to select the type of NoSQL database that will be used for storing performance metrics information. In the example, you selected Berkely database (BDB). You can also select Light Memory Database (LMDB). Lastly, you will provide a location where the database information will be stored.

11. Now, you will provide the default GoldenGate schema. This is the GoldenGate user that will be inside of the Oracle Database/Pluggable Database. Items like Automatic Heartbeat and Checkpoint tables will go into this schema. In this example, lets call it GGATE.

12. Before creating the deployment, you now have the opportunity to review your selections on the Summary page. At the same time, you can select the “Save Response File” and edit the resulting file for silent installs of the deployments as well. If you are happy with everything you are selecting, then you can click “Finish” to begin building the ServiceManager and associated first deployment.

After clicking “Finish”, the Oracle GoldenGate Configuration Assistant will build the ServiceManaager and the first deployment. At this point, you should be able to access the ServiceManager using the hostname and port number you specified during the configuration.

Categories: DBA Blogs

Performance Metric Service – Classic Configuration

Thu, 2018-06-28 16:37

Almost a year ago, Oracle released Oracle GoldenGate 12c (12.3.0.1.x). At that time, there were two architectures released; Microservices and Classic. Both architectures provided the same enterprise level replication. The only difference was that one enabled a RESTful API interface with HTML5 page and the other was still command line driven.

The biggest change though was with the addition of the Performance Metric Service/Server that come bundled with the core product. This is a huge addition to the core product and allows end-users to monitor their Oracle GoldenGate environment in near-realtime. On the Microservices architecture this service is enabled automatically and can be used on a per deployment basis. With the Classic architecture, it is there but requires a small configuration to get it to work.

In this post, I’ll show you how to get the Performance Metric Service (PMSRVR) in Classic Architecture configured and access the RESTful API endpoints. The context of this post actually builds upon a post I did almost 3 years ago (here), where I talked about how to pull XML information via a browser for Oracle GoldenGate.

After installing Oracle GoldenGate 12c (12.3.0.1.4) Classic Architecture, open GGSCI and evaluate the environment. You should notice that you have a Manager, JAgent, and Performance Metric Service (PMSRVR) all as defaults (Figure 1).

Figure 1:

Next start the Manager (MGR) process. This is done the same way as has been done in in the past – START MGR. Once the MGR process is started, your GGSCI should look like Figure 2.

Figure 2:

Now to get the PMSRVR to work. This requires the editing of the GLOBALS file. The GLOBALS files can be edited either from the command line (vi GLOBALS). Within the GLOBALS file, turn on the ENABLEMONITORING parameter. At this point, you need to understand that there has been a few changes to Oracle GoldenGate with the ENABLEMONTIORING parameter. Without getting into to much details of the changes, you now have to specify the option for UDP.

A simple GLOBALS file would look like this:

ENABLEMONITORING UDP

At this point, you can start the PMSRVR within GGSCI (start pmsrvr) (Figure 3). What this does is provide you with a default port of 9004 to access PMSRVR pages via HTTP. If you want to get more detail and have a bit more control over the port numbers, you can modify the GLOBALS file to specify the HTTP port you want to use.

An example would look like this:

ENABLEMONITORING UDP HTTPPORT 12000

Note: https://docs.oracle.com/goldengate/c1230/gg-winux/GWURF/Chunk1486599197.htm#GWURF474

Then restart the PMSRVR. After the restart, you will be able to access the PMSRVR via the HTTP port specified.

Figure 3:

Now to access the PMSRVR page, just navigate to http://hostname:port/groups (Figure 4). This is the starting point for checking the status.

Figure 4:

Notice that in Figure 4, you see a list of all services that are avaliable within the product. The services like AdminSrvr, Recvsrvr, Distsrvr, and Adminclnt are never executed. This is normal since this is not the Microservice Architecture. These services will not work.

At this point, you can use the web pages to drill into the PMSRVR, MGR and any capture/apply processes that are being monitored by the PMSRVR.

Enjoy!!

Categories: DBA Blogs

Loading Tables with Oracle GoldenGate and REST APIs

Mon, 2018-05-14 12:11

With Oracle GoldenGate 12c (12.3.0.1.x), you can now quickly load empty target tables with data from your source database. You could always do this in previous releases of Oracle GoldenGate, but the process has now been simplified using REST APIs and some scripting know-how. In this post, I’m going to show you, high level, how you can use the REST APIs and a bit of scripting to do an initial load of a two table with a single command.

In previous releases of Oracle GoldenGate, a similar task could be done, but it required you to include the Oracle Database Export/Import data pumps or some other drawn out process. With this new process, you can effectively get around that and only need to use trail files to perform the initial load.

In this scenario, I have two table with a total of 14,000 records in them. This will be a small example of an initial load, but you should get the idea behind how this will work. This approach will also work for adding tables into an existing replication scheme.

The below architcture diagram illistrates how the architecture would look with an existing GoldenGate capture running and incorprating an File-Based Initial Load process to load a few tables.

Image 1:

This may look a bit confusing, but this is quite simple to understand. The red items are the GoldenGate extract, trails (local and remote), and the GoldenGate replicat. This is an existing replication stream. The GoldenGate extract is capturing from the source database, moving transactions to the local trail file (aa). Then the DistroService picks up/reads the local trail and ships the transactions across the GoldenGate Path to the ReceiverService. The Receiver Service then writes to the remote trail (ab) where the GoldenGate replicat processes the transactions into the target database. Pretty simple and this is doing a continuous replication of transactions.

Now, you want to just setup a few new tables, but do not want to take the day or two it would take to configure, export, import, apply and then catch up. Along the bottom, is the initial load path (green) using a File-Based approach to initially load tables. This process is what I’ve scripted out to using cURL and Shell scripts. Normally, you would spend time doing an export/import for the table(s) that you want to move to the target system after setting up the initial load extract.

Using Oracle GoldenGate Microservices architecture, this initial load process can be simplied and done very quickly. Below is a link to a script which I wrote to perform an File-Based Initial Load within Oracle GoldenGate Microservices.

FB_InitialLoad.sh <— Use at your own risk! This is only an example script of how this can be done.

What this script does, is creates the File-Based Initial Load process and populates the two tables I’ve identified in the target system.

As you run this script, everything I needed to build has been reduced down to functions that I can call when needed within the script. Granted this script if very simple but it orchatrates the whole initial load process for the tables I wanted. After the tables have been loaded, then they can be merged into the existing replication stream.

Enjoy!!!

Categories: DBA Blogs

Build a Integrated Replicat using JSON

Mon, 2018-05-07 13:15

In a previous post, I showed you how to build an Integrated Extract (IE) and Distribution Path by using JSON and cURL. In this post, let’s look at how you can build an Integrated Replicat (IR) in the same manner.

To build a replicat using JSON, the JSON document is made up of the following 8:

Config – Details for the associated parameter file
Source – Where the replicat should read transactions from
Credentials – What credentials in the credential stores should be used
Checkpoint – What checkpoint table is used by the replicat
Mode – What type of replicat will be built
Registration – Register the replicat with the database
Begin – At what timeframe the replicat should start
Status – If the extract should be started or not

The resulting JSON document would look like the following:

{
“config”:[
“Replicat REPTS”,
“UseridAlias TGGATE”,
“Map SOE.*, Target SOE.*;”
],
“source”:{
“name”:”bc”
},
“credentials”:{
“alias”:”TGGATE”
},
“checkpoint”:{
“table”:”ggate.checkpoints”
},
“mode”:{
“type”:”integrated”,
“parallel”: true
},
“registration”: “standard”,
“begin”:”now”,
“status”:”stopped”
}

Now that you have a valid JSON document, a cURL command for building the integrated replicat can be done as follows:

curl -X POST \
http://localhost:17001/services/v2/replicats/REPTS\
-H ‘Cache-Control: no-cache’ \
-d ‘{
“config”:[
“Replicat REPTS”,
“UseridAlias TGGATE”,
“Map SOE.*, Target SOE.*;”
],
“source”:{
“name”:”bc”
},
“credentials”:{
“alias”:”TGGATE”
},
“checkpoint”:{
“table”:”ggate.checkpoints”
},
“mode”:{
“type”:”integrated”,
“parallel”: true
},
“registration”: “standard”,
“begin”:”now”,
“status”:”stopped”
}’

Just like the Integrated Extract (IE) and Distribution Service, the Integrated Replicat (IR) is created in a stopped state. At this point, you can start the IR and validate whatchanges need to be made to ensure replication happens.

Enjoy!!!

Categories: DBA Blogs

Build a Distribution Path using JSON

Fri, 2018-05-04 13:15

In a previous post, I showed you how to build an Integrated Extract (IE) using JSON and simple cURL command. In this post, let’s take a look at how to build a Distribution Service Path.

First think to understand is that the Distribution Service is the replace for the Extract Data Pump in the traditional Oracle GoldenGate architecture. The Distribution Service does the same thing as the Extract Data Pump with the exception of transformations. If you have a need to do transformations with Oracle GoldenGate Microservices; the transformations have to be pushed to either the extract or the replicat to be done.

The purpose of the Distribution Service is to ensure that the local trail files are shipped across the network and reach the Reciever Service which will create the remote trail files.

Note: The Receiver Service, on the target side, will be started automatically when the Distribution Service connects to the port number for it.

Within the Distribution Service, you will create Distribution Paths between the source and target hosts. The way you do this with JSON quite simple. There are 4 main items the JSON should contain.

1. Name – This is what the Distribution Path will be named
2. Status – Should the Distribution Path be running or stopped
3. Source – This specifies the local trail file that should be read for transactions
4. Target – This specifies the Login and URL to write to the remote trail files.

Note: For the Target setting, there are 4 protocols that can be used:
Secure Websockets (wss) – default
Websockets (ws)
UDP-based Data Transfer Protocol (udt)
Oracle GoldenGate (ogg)

An example of a JSON document that would be used to build a Distribution Path is as follows:

{
“name”: “TSTPATH”,
“status”: “stopped”,
“source”: {
“uri”: “trail://localhost:16002/services/v2/sources?trail=bb”
},
“target”: {
“uri”: “ws://OracleGoldenGate+WSTARGET@localhost:17003/services/v2/targets?trail=bc”
}
}

To build this Distirbution Path (TSTPATH), a cURL command as such can be used to build it:

curl -X POST \
http://localhost:16002/services/v2/sources/TSTPATH \
-H ‘Cache-Control: no-cache’ \
-d ‘{
“name”: “TSTPATH”,
“status”: “stopped”,
“source”: {
“uri”: “trail://localhost:16002/services/v2/sources?trail=bb”
},
“target”: {
“uri”: “ws://OracleGoldenGate+WSTARGET@localhost:17003/services/v2/targets?trail=bc”
}
}’

Once the Distribution Path is created, you can start it. Upon starting the path, you can check the Receiver Service on the target side. It should have been started as well.

Enjoy!!!

Categories: DBA Blogs

Build an Integrated Extract using JSON

Thu, 2018-05-03 13:15

Now that Oracle GoldenGate 12.3 Microservices have been out for about 9 month; there seems to be more and more discussions around how microservices can be used. The mircoservices architecture provides a faster way for users to build extract, replicats, distribution paths and many other items by using a JSON document and simply calling a REST end-point.

In this post, I’ll show you how to build an integrated extract using JSON and REST APIs. First think you need to understand, is the steps that it takes to build an extract currently in GGSCI/AdminClient.

Note: AdminClient can be used, with debug on, to see how these commands translate back into JSON and REST calls.

To build an Integrated Extract via GGSCI/AdminClient:

1. add extract exttst, integrated, begin now
2. register extract exttst, database container pdb1
3. add exttrail aa, extract exttst, megabytes 250
4. start extract exttst

As you can tell, it takes 4 steps to add and start the extract to an Oracle GoldenGate configuration.

If your a command line geek or a developer who wants to do more with Oracle GoldenGate, the mircroservices architecture provides you a way to build an extract via JSON files. A simple JSON file for building an integrated extract looks as follows:

{
“description”:”Integrated Extract”,
“config”:[
“Extract EXTTST”,
“ExtTrail bb”,
“UseridAlias SGGATE”,
“Table SOE.*;”
],
“source”:{
“tranlogs”:”integrated”
},
“credentials”:{
“alias”:”SGGATE”
},
“registration”:{
“containers”: [ “pdb1” ],
“optimized”:false
},
“begin”:”now”,
“targets”:[
{
“name”:”bb”,
“sizeMB”:250
}
],
“status”:”stopped”
}

This JSON example, describes all the attributes needed to build an integrated extract. The main items in this JSON are:

Description – Provide a description for the parameter file
Config – Details for the associated parameter file
Source – Where the extract should read transactions from
Credentials – What credentials in the credential stores should be used
Registration – Register the extract with the database and against associated pdbs
Begin – At what timeframe the extract should start
Targets – What trail files the extract should write to
Status – If the extract should be started or not

These 8 categories cover what we traditioanlly did in the classic architecture in 3 steps. With all these items in the JSON file, you can now quickly build the extract by calling a simple curl command.

In order to build the extract, you need to know the REST API end-point that is needed. All extracts are built against the Administration Server (AdminService) within the microservices architecture. In my configuration, my AdminService is running on port 16000; so the REST API end-point would be:

{{Source_AdminServer}}/services/v2/extracts/{{extract_name}}

http://localhost:16000/services/v2/extracts/EXTTST

The REST API end-point, requires you to specify the extract name in the URL. Now with the URL and associated JSON, you can create an extract with a simple cURL command or embed the call into an application. An example of a cURL command that would be used is:

curl -X POST \
http://localhost:16001/services/v2/extracts/EXTTST\
-H ‘Cache-Control: no-cache’ \
-d ‘{
“description”:””,
“config”:[
“Extract EXTTST”,
“ExtTrail bb”,
“UseridAlias SGGATE”,
“Table SOE.*;”
],
“source”:{
“tranlogs”:”integrated”
},
“credentials”:{
“alias”:”SGGATE”
},
“registration”:{
“containers”: [ “pdb1” ],
“optimized”:false
},
“begin”:”now”,
“targets”:[
{
“name”:”bb”,
“sizeMB”:250
}
],
“status”:”stopped”
}’

Once the extract is created, you will notice that the extract is stopped. This is due to the “status” that was feed through the JSON document. You should be able to start the extract and start seeing transactions being extracted.

Enjoy!!!

Categories: DBA Blogs

How to write to SYSLOG in OGG 12.3 Classic and Microservices Architectures?

Sun, 2018-03-25 17:25

Oracle GoldenGate 12.3 Microservices introduced a lot of changes into how replication is to be managed moving forward. The Microservices Architecture (MA), provides a lot of opportunity for people to learn a lot about microservices and how they can be leveraged to to bring real-time replication to their organizations.

One feature that was used in the Classic Architecture (CA) was having the GGSERR.log file write items to the messages file at the system level. Starting in Oracle GoldenGate 12.3, both MA and CA, this can still be done; however, how it is done is different.

When you first look at the release notes for Oracle GoldenGate 12.3, you will notice that the SYSLOG parameter is listed under the section 2.3.2 Deprecated Features (here). It clearly states that the SYSLOG parameter has been removed and should not be used for the GLOBALS or the MGR.prm files. So, how do we enable writing to the messages file then?

After digging around a bit, you will notice that this is covered in the docs under 17.6 Using the Error Log in the Administration Guide (here). There is a lot of information here for how to enable the SYSLOG functionality and tells you what file to look at (ogg-ggserr.xml).

Now, that you know what file controls the SYSLOG option, where is this file located?

In CA, the file is located in a new directory called “diretc” and under another directroy called “logging”.

$OGG_HOME/diretc/loggin

In MA, the file is buried in the $OGG_HOME a bit, but easy to find.

$OGG_HOME/lib/utl/logging

Once you are in the file, you will notice that the file is configured by default to write to the GGSERR.log and rollover after every 10Mb in size. Just below that, you will notice that there are three commented out items. Each one of these control what you can do with the GGSERR.log. The first option will write to the GGSERR.log without rolling over. The second will allow you to write GGSERR.log info to the SYSLOG! The third will turn off all event logging. Since you will want to write to the SYSLOG, you will go with the second option. Uncomment that section (remove <!— && ! —> ).

This section will look like this:

!- The ggserr.Syslog adapter will write all messages to the local
!- system log.
!-
<appender name=”ggserr.Syslog” class=”SyslogAppender”>
<layout class=”PatternLayout”>
<param name=”Pattern” value=”%-19.19d{%Y-%m-%d %H:%M:%S} %m%n”/>
</layout>
</appender>

After uncommiting the section for writing to SYSLOG, you will need to restart the ServiceManager by using the Action button in the Deployment section of the ServiceManager HTML5 page. After the ServiceManager restarts, you will notice Oracle GoldenGate commands being written to the /var/lib/messages file.

Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-01892 Distribution path network is configured as
Mar 20 11:12:17 ogg123 ServiceManager: OS DEFAULT SPECIFIED ACTUAL VALUE
Mar 20 11:12:17 ogg123 ServiceManager: APP_FLUSHBYTES 27985 N/A 27985
Mar 20 11:12:17 ogg123 ServiceManager: APP_FLUSHSECS 1 N/A 1
Mar 20 11:12:17 ogg123 ServiceManager: IP_DSCP DEFAULT N/A DEFAULT
Mar 20 11:12:17 ogg123 ServiceManager: IP_TOS DEFAULT N/A DEFAULT
Mar 20 11:12:17 ogg123 ServiceManager: TCP_NODELAY 0 N/A 0
Mar 20 11:12:17 ogg123 ServiceManager: TCP_QUICKACK 1 1 1
Mar 20 11:12:17 ogg123 ServiceManager: TCP_CORK 0 N/A 0
Mar 20 11:12:17 ogg123 ServiceManager: SO_SNDBUF 16384 N/A 16384
Mar 20 11:12:17 ogg123 ServiceManager: TCP_CORK 0 N/A 0
Mar 20 11:12:17 ogg123 ServiceManager: SO_SNDBUF 16384 N/A 16384
Mar 20 11:12:17 ogg123 ServiceManager: SO_RCVBUF 87380 N/A 87380.
Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-01052 No recovery is required for target file CB000000000, at RBA 0 (file not opened).
Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-01669 Opening /opt/app/oracle/gg_deployments/pdb2/var/lib/data/CB000000000 (byte -1, current EOF 0).
Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-02243 Opened trail file /opt/app/oracle/gg_deployments/pdb1/var/lib/data/CA000000 at 2018-03-20 11:12:17.126288.
Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-01478 Output file CB is using format RELEASE 12.3.
Mar 20 11:12:17 ogg123 ServiceManager: 2018-03-20 11:12:17 INFO OGG-02756 The definition for table PDB1.TEST_USER.LRG_TABLE is obtained from the trail file.

Enjoy!!!

Categories: DBA Blogs

Changing Security Role Password in Oracle GoldenGate 12.3 Microservices

Thu, 2018-03-08 12:02

One of the nice things about Oracle GoldenGate 12.3 Microserivces, is the flexibility we now have to interact with Oracle GoldenGate. Additionally, what comes with Oracle GoldenGate 12.3 Microservices is a new role based security frame work.

When you first setup Oracle GoldenGate 12.3 Microservices, you setup your ServiceManager and initial deployment. Upon setting this up, you will assign a new administrator to the frame work. This is a different user than the Oracle GoldenGate user within the database. This user is the user assigned to the security role (highest role). This user is the equivalent of the SYS user in the Oracle Database and has access to everything within the security framework of Oracle GoldenGate 12.3 Microservices. It should be a no-brainer to keep the password for this user secure and to as few people as possible.

Now, when you look at the user from inside of the Administration Server (AdminServer); notice there is not an update button under the Action category (Figure 1).

Figure 1:

So how do we update this password for a user in the security role, much less any other role? Remember, when I said Oracle GoldenGate 12.3 Microservices was “flexible”? We have to use the RESTful API to update the password.

The exact end-point that you have to use is: /services/v2/authorizations/{role}/{user}. More info can be found here.

The way that I update the password is by using a JSON document and a simple cURL command. Below is a simple JSON document example:

{
“credential”:”welcome1″
}

Then the cURL command I use is:

curl -u oggadmin:********* -H “Content-Type:application/json” -H “Accept:application/json” -X PATCH http://localhost:16001/services/v2/authorizations/security/oggadmin -d @update_security_password.json| python -mjson.tool

After running the cURL command, the password for the oggadmin user is updated. You should see some similiar output to this output.

{
“$schema”: “api:standardResponse”,
“links”: [
{
“href”: “http://localhost:16001/services/v2/authorizations/security/oggadmin&#8221;,
“mediaType”: “application/json”,
“rel”: “canonical”
},
{
“href”: “http://localhost:16001/services/v2/authorizations/security/oggadmin&#8221;,
“mediaType”: “application/json”,
“rel”: “self”
}
],
“messages”: [],
“response”: {
“links”: [
{
“href”: “http://localhost:16001/services/v2/authorizations/security/oggadmin&#8221;,
“mediaType”: “application/json”,
“rel”: “self”
}
]
}
}

Hope this helps you understand how to change a user password from REST within the Oracle GoldenGate 12.3 Microservices Architecture.

Enjoy!!!

Categories: DBA Blogs

Identifying Integrated Replicat transactional progress

Fri, 2018-02-23 09:50

From time-to-time, you may want to find out what transactions have been applied or still in flight while Oracle GoldenGate is running while using the Integrated Replicat (IR) products. This can be done easily by using the ALL_GG_INBOUND_PROGRESS/DBA_GG_INBOUND_PROCESS views. There are a few columns of interest in this view, they are:

  • APPLIED_LOW_POSITION
  • APPLIED_HIGH_POSITION
  • OLDEST_POSITION
  • APPLIED_LOW_SCN (not applicable for GoldenGate)

Note: For more information on this view, reference Oracle Docs – here

The APPLIED_LOW_POSITION represents the commit positions less than this SCN have been applied.
The APPLIED_HIGH_POSITION is the commit position of a transaction that has been applied.
The OLDEST_POSITION is the earliest position of transactions currently being applied.
The APPLIED_LOW_SCN is the marker that represents all SCN below or equal to this number have been successfully applied; however, this column is not applicable for GoldenGate replication since the source database may be non-Oracle in nature.

To identify these columns during replication, you can use a simple query like:

select server_name, applied_low_position, applied_high_position, oldest_position, applied_low_scn
from DBA_GG_INBOUND_PROGRESS
where server_name = ‘OGG$IREPSOE’;

In the above query, I’m looking at a specific replicat, called OGG$IREPSOE. This an indicator that I’m looking at an Integrated Replicat (IR). When I run the query, I get the following output:

SERVER_NAME APPLIED_LO APPLIED_HI OLDEST_POS APPLIED_LO
————— ———- ———- ———- ———-
OGG$IREPSOE 6232708 6232710 6232505 0

Using the definitions above, the APPLIED_LOW_SCN column can be thrown out, since we do not use it for identifying what has been applied. Taking the next three columns into account, I can see that SCN 6232505 (OLDEST_POSITION) is the last SCN applied. Any transactions with SCN lower or equal to this SCN has been applied to the database.

The next column we need to look at is the APPLIED_LOW_POSITION. This column represents transactions that have been applied to the database as well. Any SCN below or equal to this SCN has been applied. In this case the SCN is 6232708. This SCN looks really close to the OLDEST_POSITION SCN that was just discussed; within 203 value (6232708 – 6232505). This change represents just a 3 seconds in changes. You can see this by running this query:

select server_name, scn_to_timestamp(applied_low_position), scn_to_timestamp(oldest_position)
from DBA_GG_INBOUND_PROGRESS
where server_name = ‘OGG$IREPSOE’;

Which produces the following output:

SERVER_NAME SCN_TO_TIMESTAMP(APPLIED_LOW_PO SCN_TO_TIMESTAMP(OLDEST_POSITIO
————— ——————————- ——————————-
OGG$IREPSOE 23-FEB-18 09.49.45.000000000 AM 23-FEB-18 09.49.42.000000000 AM

Lastly, when looking at APPLIED_HIGH_POSITION. This column represents transactions that have been applied as well; however, any SCN/transaction that is higher than this SCN has not been applied yet.

Hopefully, this helps explain how you can identify what SCNs have been applied and help identify what to look for transactions that have not been applied.

Enjoy!!

Categories: DBA Blogs

Pages