Subscribe to  DBASolved feed  DBASolved
Helping Oracle DBAs solve problems
Updated: 7 hours 34 min ago

#GoldenGate Microservices (3 of 5) … Distribution Service

7 hours 35 min ago

This is post 3 of a 5 part post related to Oracle GoldenGate 12.3 Microservices. This series of posts will provide some details over the new graphical user interface (GUI) that has been built into Oracle GoldenGate 12.3.

The third part of setting up the replication enviornment with Microservices is to understand what the Distributuion Server is. The component is the replacement for the Data Pump Extract in classic architecture. Distribution Server does all the same things as the Data Pump Extract except with from transformations. If you use transformations in the Data Pump, you really should relook where this is being done.

Note: Transformations should be done at the source, target, or with Oracle Data Integrator.

Distribution Server is your path way for shipping trail files from source to target in the Microservices architecture. Let’s take a closer look at this service.

Upon accessing the Distribution Server page, you see a blank page (Figure 1). This is the overview page and where you will configure you distribution paths needed for replication.

Figure 1:

To begin adding a distribution path, click the plus ( + ) sign on the right hand side of the page. This will take you to the Add Path page (Figure 2). On the Add Path page, you will provide the details for the path. These details include extract/trail file to read from, protocol to use for transmissions, log positioning and many other options. A lot of the items you could do in the data pump extract, can be configured on this page as well.

Figure 2:

After adding a distribution path, you will be able to see clearly where the transactions are coming from and being shipped to (Figure 3). You will also see what database, extract, and trail if being used for the transactions.

Figure 3:

If you click on the “Action” button, you are taken to a menu where you can perform operations on the Distribution Path. There is an option for “Details”. By looking at the details, you can quickly see, in more detail, what is happening with the Distrubtuion Path. You can see the source and target, database, extract, trail name, source trail file, as well as many other item (Figure 4). Additonally, on the details page, you can see the statistics for what is running through the Distribution path.

Figure 4:

Lastly, if you return to the context menu on the left hand side, you will see an option for “Diagnosis & Reports”. Here is where you will be able to take a look at the report file for the Distribution Server (Figure 5). This page provides you with a date sorted view of the log file for easy reading. You will also be able to quickly find items that are wrong or warnings by the color indicator used with the log file.

Figure 5:


Filed under: Golden Gate
Categories: DBA Blogs

#GoldenGate sessions at #OOW17

Thu, 2017-09-21 14:52

We are a little over a week out from Oracle Open World 2017; hopefully you are filling out your schedules! This year there is a lot of items going on; so I figured I would help you out and point out where you can find the Oracle GoldenGate sessions this year! With the release of Oracle GoldenGate 12.3, there is a lot of new features and updates. So check them out (in no particular order)!


  • Oracle GoldenGate Product Update and Strategy
    • Tuesday, Oct 03, 5:45 p.m. – 6:30 p.m. | Moscone West – Room 3003
  • Maximizing Availability for Oracle GoldenGate Microservices
    • Tuesday, Oct 03, 4:45 p.m. – 5:30 p.m. | Moscone West – Room 3014
  • Oracle GoldenGate for Big Data
    • Wednesday, Oct 04, 4:30 p.m. – 5:15 p.m. | Moscone West – Room 3005
  • Deep Dive into Automating Oracle GoldenGate Using the New Microservices
    • Monday, Oct 02, 1:15 p.m. – 2:00 p.m. | Moscone West – Room 3010
  • Accelerate Cloud Onboarding Using Oracle GoldenGate Cloud Service
    • Tuesday, Oct 03, 3:45 p.m. – 4:30 p.m. | Moscone West – Room 3024
  • Oracle Data Integrator and Oracle GoldenGate for Big Data
    • Wednesday, Oct 04, 11:30 a.m. – 12:30 p.m. | Hilton San Francisco Union Square (Ballroom Level) – Continental 4
  • Replicate Data Across Data Centers with Equinix and Oracle GoldenGate
    • Wednesday, Oct 04, 10:30 a.m. – 10:50 a.m. | The Exchange @ Moscone South – Showcase Theater 1
  • An Enterprise Databus: Oracle GoldenGate in the Cloud Working with Kafka and Spark
    • Wednesday, Oct 04, 3:30 p.m. – 4:15 p.m. | Moscone West – Room 3003
  • Oracle GoldenGate Cloud Service: Real-Time Data Replication in the Cloud
    • Monday, Oct 02, 6:00 p.m. – 7:00 p.m. | Hilton San Francisco Union Square (Ballroom Level) – Continental 4
  • Best Practices and Deep Dive on Oracle GoldenGate 12.3 Microservices at Cloud
    • Wednesday, Oct 04, 3:30 p.m. – 4:15 p.m. | Moscone West – Room 3011
  • How to Replicate Data in the Cloud in Real Time
    • Wednesday, Oct 04, 11:00 a.m. – 1:00 p.m. | Hilton San Francisco Union Square (Lobby Level) – Golden Gate 2/3
  • Oracle Data Integration Platform Cloud Strategy and Roadmap
    • Monday, Oct 02, 12:15 p.m. – 1:00 p.m. | Moscone West – Room 3024
  • Oracle Data Integration Platform Empowers Enterprise-Grade Big Data Solutions
    • Monday, Oct 02, 4:45 p.m. – 5:30 p.m. | Marriott Marquis (Golden Gate Level) – Golden Gate C1/C2
  • Kafka’s Role in Implementing Oracle’s Big Data Reference Architecture  
    • Sunday, Oct 01, 1:45 p.m. – 2:30 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 12
  • Oracle Maximum Availability Architecture Best Practices: Oracle Database 12 c
    • Tuesday, Oct 03, 5:45 p.m. – 6:30 p.m. | Moscone West – Room 3006

As you can tell, there is a lot of information that is going to be helpful for your Oracle GoldenGate, BigData and various other areas. Make sure you check all these out.


Filed under: Golden Gate
Categories: DBA Blogs

#GoldenGate and #Docker!

Wed, 2017-09-13 22:55

Check this out … Oracle GoldenGate is now avaliable on Docker! All the details you need to get up and running on an image is included in Oracle’s public github.



Filed under: Docker, Golden Gate
Categories: DBA Blogs

#GoldenGate Microservices (2 of 5) … Administration Service

Tue, 2017-09-12 14:30

This is post 2 of a 5 part post related to Oracle GoldenGate 12.3 Microservices. This series of posts will provide some details over the new graphical user interface (GUI) that has been built into Oracle GoldenGate 12.3.

The second interface of the Microservices Architecture that you need to understand is the Administration Service. This service is the interface where you will setup the capture (extract) process and the apply (replicat) process. On the left hand side of the page, in the context menu, you will see a few options … overview, configuration, diagnosis and administrator. These are the primary areas that you interact with the administrator server.

On the overview page, as previously mentioned you can setup your capture (extract) and apply (replicat) processes. You also get a view of the critical events that is are happening within the environment (Figure 1).

Figure 1:

On the configuration page, you will be able to configure credentials that are needed for your replication environment. The addtions of the credential page allows you to create your standard logins for the goldengate user within the database, plus what I’m calling “protocol” users that can be used within the replication environment. Next is the maintenance tab; this tab you can setup tasks like auto start, auto restart, purging trail files, and purge tasks. Additionally, you can add a master key that is used with encryption. Lastly the parameter files tab, will list all the parameter files that is configured in the environment. From here you can edit all the parameters files (Figure 2).

Figure 2:

Next is the diagnosis page. This page is where you can see what is happening in the log file for the Administraton Service. Items on this page will be color coded, green, yellow and red. This give you an indication of what is happening on what you should be looking at (Figure 3).

Figure 3:

Lastly, you have the Administrator page. This page is where you can create users that can access the Oracle GoldenGate environment. When you create a user for access purposes, you get four roles to choose from … Security, Administrator, Operator, and User. Each one of these roles provide a varying level of access (Figure 4).

Note: Do not delete the security user that is created during the intital install of the software.

Figure 4:


Filed under: Golden Gate
Categories: DBA Blogs

#GoldenGate Microservices (1 of 5) … ServiceManager

Mon, 2017-09-11 14:30

This is post 1 of a 5 part post related to Oracle GoldenGate 12.3 Microservices. This series of posts will provide some details over the new graphical user interface (GUI) that has been built into Oracle GoldenGate 12.3.

The first of the Oracle GoldenGate Microservices that you need to understand is that ServiceManager. This service is main interface into the Oracle GoldenGate environment. From the HTML5 page that is provided with ServiceManager, you can quickly get an overview of what is running on the server that Oracle GoldenGate is running on. Additonally the ServiceManager acts as the watch dog process for the environment. This means that if a service was to go down, it can be restarted via the ServiceManager.

The ServiceManager can be configured in three different modes. These modes range from manual to Real Application Cluster (RAC) aware. These modes are:

  1. Manual
  2. Deamon
  3. XAG

These configuration options are selectable during the inital run of Oracel GoldenGate Configuration Assistant (oggca.sh) (Figure 1). If you wanted to use it manually, you woudn’t select anything on this screen. If you want to run it on a single server, than the deamon option is a good choice. For the XAG option, you need to make sure you have the current XAG agent (here).

Figure 1:

After ServiceManage is installed, then you will be able to access the interface via a URL (Figure 2). In the example, you will see that I’m usign port 16000. This is not the default port, but a port that I had configured during the installation using Oracle GoldenGate Configuration Assistant.

Figure 2:

Once the ServerManager page is up, then you just need to login with the administrator account you created during installation of ServiceManager. Keep in mind, if you do not know the userid or password, the consult the people who initally configured the ServiceManager.

After logging in, you are taken directly to the overview page. This page has a few items of importance for the Oracle GoldenGate environment. First you will notice on the left hand side of the page, is a context menu that provides a link to Overview and Diagnostics. This is basically a navigation menu that will be in every microservice (different per service). Then notice at the top of the page, a summary of processes that are running, stopped, or other Lastly, at the middle and bottom of the page, you see the services and deployments that are associated with the installation (Figure 3).

Figure 3:

If you want to know details of the ServiceManager and/or deployments, you can click on the associated deployment under Deployments. This will take you to a summary page, where you can identify items releated to the deployment and change the $OGG_HOME (more on this later).

As you can tell, we have made some great improvments to the Oracle GoldenGate product. Hopefully, this post has you excited to see what else is hiding in the product and my next couple of posts.


Filed under: Golden Gate
Categories: DBA Blogs

Events to round out 2017 … where I’ll be!

Mon, 2017-09-11 08:23

The first half of 2017 started off with me joining Oracle and getting the first looks at the new Microservices Architecture for Oracle GoldenGate. So where does this leave me for the second half of 2017?

As some of you may know, I’m currently working on a Oracle GoldenGate 12.3 roadshow; where I’m explaining the new features in 12.3 and giving attendees hands-on to the new product. After two stops on the roadshow, so far, we have seen so much excitement for this new architecture. It is going to change the way we, as a community, replicate data both on-premise, in the cloud, and in hybrid architecture.

Check out the new Microservice Architecture by downloading it from OTN (here).

As we continue the roadshow, we will be hitting a few more cities in the US before moving on to EMEA in November. Once we get to EMEA, there will be some cool places we plan on holding events and/or meeting customers. The excitement for Oracle GoldenGate 12.3 continues to grow as we attend more cities.

If you want to know the cities in the US, please see this blog post (here).

On top of the US and EMEA legs of the Oracle GoldenGate 12.3 roadshow, I will be attending two conferences in EMEA. I’m sure you can guess at which two … DOAG and UKOUG. This will my first time at both events and I hope I get to see a lot of great friends and make many new ones.

At DOAG, I will be speaking, on November 22, 2017 @ 08:00, about Oracle GoldenGate Peformance Tuning. Additionally, I will have another product manager in tow (hopefully) so attendees will have direct access to the PMs who are responsible for the core Oracle GoldenGate product.

At UKOUG, I will be speaking, need to confirm date, about the Conflict, Detection and Resolution (CDR) in Oracle GoldenGate 12.3 Both the manual process and the new Automatic CDR. Then if needed, by the UKOUG board, I may have another Oracle GoldenGate 12.3 presentation.

All in all, 2017 is rounding out to be a great first year at Oracle along with many first on top of that. Looking forward to seeing everyone on the roadshow!


Filed under: Golden Gate
Categories: DBA Blogs

#GoldenGate Classic vs Microservices Architecture

Fri, 2017-08-25 23:30

With the release of Oracle GoldenGate 12c ( came the introduction of a new architecture that can be used to replicat your business information. This architecture had many different names over the years as it was being developed; which we (Oracle) finally settled on the name of “Microservices” Architecture as we got closer to general release. There are many benefits to the Microservices Architecture and these benefits should give you pause to look closely at this new feature for Oracle GoldenGate 12c.

Before we get into the Microservices Architecture, let’s review the Classic Architecture. In the below image, you see a pretty standard Oracle GoldenGate implementation.

In this architecture, the primary access into the Oracle GoldenGate enviornmentis is through the GoldenGate Service Command Interface (GGSCI). After logging into GGSCI, you can interact and administrate the associated processes, i.e. Manager, Extract (Capture), Data Pump, and Replicat (Apply). The Collectors are pretty much hidden on the target systems, but they are there; just not seen through GGSCI. In this architecture, you data is replicated over TCP/IP between the Data Pump process and Collectors using local and remote trail files.

The downside to this architecture is that, in order to administer the environment, you have to physically login to the server where Oracle GoldenGate is running. With this requirement, many organizations were restrictive on who had access to the server and often caused a debate over who were truely the owners of Oracle GoldenGate.

Althought the Classic Architecture of Oracle GoldenGate has been a bedrock of replication for nearly 20 years, we (Oracle) wanted to leverage that bedrock to transform the way we (industries) replicate data today. This lead to the more flexible and super scalable Microservices Architecture. As you may have guessed, “Microservices” is the mechanism that we are suing to provide access to the “RESTful API” end points. By using RESTful APIs, we (Oracle) have taken a huge leap forward in the replication space. We have broken down the limitations we had around administration and access, while at the same time remaining true to the bedrock that is the foundation of Oracle GoldenGate.

The below image is a view of a simple Oracle GoldenGate Microservices Architecture, for you to review.

As you will notice there are some traditional components of Oracle GoldenGate missing, while there are still extracts (capture), trail files, and replicats (apply). This is due to being a completely new replication architecture which had many benefits. A few of these benefits are:

  • Remote Administration
  • SSL Support
  • HTML 5 webpages for each service/server
  • Additional replication protocols (WSS, WS, UDT, OGG)
  • Real-time Performance Metrics

In order to understand the Microservices Architecture, you have to understand what each of the servers (or services) provide within the architecture. So, let’s take a moment and talk about these items starting with the ServiceManager.

The ServiceManager is the watchdog process for the architecture on each server in the replication enviornment. Ideally, you should only have one of these processes running. This process can be configured to run in one of 3 ways. These ways are:

  • Manually
  • As a daemon
  • Integrated with XAG

While the ServiceManager is running, this process will be the main entry point into the Oracle GoldenGate environment. During the configuration process, you will be asked to assign ports for each the servers to run on. The ServiceManager will be the first port you assign. From the HTML5 page of the ServiceManager, you will be able to see all of your deployment homes and associated servers.

The AdminServer is the service that will take the place GGSCI (don’t worry, we still have a command line in this architecture) and Manager in the Classic Architecture. From here, you will be able to setup your credential store, extract and replicats. Most Oracle GoldenGate Administrators will spend their time here. Additionally, from this service you can drill into the running process and review current status, statistics, parameter files, and report file. Making your administration in general much simpler.

The DistributionServer is the replacement for the Data Pump Extract. The service performs all the same functionality as the Data Pump Extract with the exception of transformations. Besides providing all the same functionality, you also get a visual representation of where your trail file is being read from and shipped to. It is very clear to see from the overview page of this services. As you dig into the details of the DistributionServer, you can see the statistics on what is being read and written to trail files and adjust TCP/IP items within the distribution path.

The ReceiverServer is the replacement for the Collectors. The whole job of the ReceiverServer is to accept transmissions from the DistributionServer and write out the remote trail files. From the overview page of this service, you can clearly see where the information is coming from and what trail it is writing to. Just like the DistributionServer, if you look at the details of this service you can see alot of useful information.

Lastly, is the most interesting of the services with the Microservices Architecture. This would be:

Performance Metrics Server:
Finally, we (Oracle) have provide a real-time performance monitoring services with Oracle GoldenGate. Before you get all happy about having a new way to monitor performance, you must have a license for the Oracle Managment Pack for GoldenGate before you can use the GUI or associated metric APIs. If you have that in place, there is so much performance metric information you can retrieve and use in both the GUI and APIs. I would encourage you to take a look.

With that my friends, hopefully, you are a bit excited about using the new Microservices Architecture. There is so much you can do with this architecture and it is going to change how we replicat data, both on-primese, in the cloud, and in hybrid environments.


Filed under: Golden Gate
Categories: DBA Blogs

5 City #GoldenGate Tour

Fri, 2017-08-18 19:59

Oracle GoldenGate is the best replication platform on the market, so much so, the product management team is hitting the road to show customers!  Over the next two months (August 2017 /September 2017) we will be taking to the road to provide customers with hands-on experience of the latest release.  These events will be limited in space and many have filled up already; but if you have the opportunity to attend these will be great events!

The cites and dates that we will be attending (US Leg):

August 23, 2017 – San Francisco, Oracle Headquarters, Redwood Shores
August 29, 2017 – Atlanta, Ga, Oracle Offices
September 12, 2017 – Chicago, Il, Willis Tower (Oracle Offices)
September 13, 2017 – Chicago, Il, Deerfield (Oracle Office)
September 19, 2017 – Dallas, TX
September 26, 2017 – Boston, MA 

If you are interested in attending one of these events, please contact Patrick Cassidy (patrick.cassidy@oracle.com) to register.  Hurry seats are filling up, if not already filled up!!!

Look forward to seeing you at one of the shows.

Note: for the international people reading, we have not forgotten about you.  We are planning on seeing you soon!


Filed under: Golden Gate
Categories: DBA Blogs

Oracle #GoldenGate has arrived!

Fri, 2017-08-18 18:46

As of today, August 18, 2017, the latest release of Oracle GoldenGate 12c ( is avaliable for download! You should checkout the new Microservices Architecture!

You can find the links to download Oracle GoldenGate 12c ( at this link: http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

This release bring many new features to Oracle Goldengate:

GoldenGate 12.3 Platform Features – All Platforms For the Oracle Database

Support for Oracle Database
Oracle Database provides many exciting features for organizations to use. Oracle GoldenGate 12.3 is designed to leverage many of these features in Oracle Database as well. Organizations will have a fully supported and integrated replication framework that provides organizations with performance and throughput enhancements within the Integrated Capture, Integrated Apply and many others processes.

-Microservices Architecture

A new services based architecture that simplifies configuration, administration and monitoring for large scale and cloud deployments. The RESTFul services enable secure remote access using role based authorization over HTTPS and Websocket (streaming) protocols. Each service also has an embedded HTML5 browser based UI for better user experience in addition to traditional command line access for ggsci style scripting/automation. It enables Applications to embed, automate, and orchestrate GoldenGate across the enterprise.

Parallel Replicat
Highly scalable apply engine for the Oracle database that can automatically parallelize the apply workload taking into account dependencies between transactions. Parallel Replicat provides all the benefits of Integrated Replicat performing the dependency computation and parallelism outside the database. It parallelizes the reading and mapping of trail files and provides the ability to apply large transactions quickly in Oracle Database 11g ( and later.

Automatic Conflict-Detection-Resolution (CDR) without application changes
Quickly enable active/active replication while ensuring consistent conflict-detection-resolution (CDR) without modifying application or database structure. With automatic CDR you can now configure and manage Oracle GoldenGate to automate conflict detection and resolution when it is configured in Oracle Database 12c Release 2 (12.2) and later.

Procedural Replication to enable simpler application migrations and upgrades
Procedural Replication in Oracle GoldenGate allows you to replicate Oracle-supplied PL/SQL procedures, avoiding the shipping and applying of high volume records usually generated by these operations.

Database Sharding
Oracle Sharding is a scalability and availability feature designed OLTP applications that enables distribution and replication of data across a pool of Oracle databases that share no hardware or software. The pool of databases is presented to the application as a single logical database. Data redundancy is managed by Oracle GoldenGate via Active-Active replication that is automatically configured and orchestrated through the database engine invoking the RestFul API’s.

Fast Start Capture
Fast Start Capture is a new feature for Integrated Capture that will improve overall performance and enable you to quickly start capturing and replicating transactions.

For SQL Server

Introducing a new, CDC based Capture

Oracle GoldenGate 12.3 will introduce a new Change Data Capture based Extract, which offers new functional advantages over our existing transaction log based capture method.Benefits include:

  •   Capture from SQL Server 2016
  •   Remote Capture
  •   Transparent Data Encryption (TDE) support

Certification to capture, from an AlwaysOn primary and/or synchronous secondary database
With an increase in uptake of our customers running their application critical databases in an AlwaysOn environment, Oracle GoldenGate 12.3 is the first version to certify capture from either the Primary database, or a read-only Synchronous Secondary database.

-Delivery to SQL Server 2016 Enterprise Edition

For DB2 z/OS

Remote Execution
The new remote execution includes both remote capture and delivery for DB2 z/OS. Running Oracle GoldenGate off the z/OS server significantly reduces the MIPS consumption and allows the support of AES encryption and credential store management.

For DB2 i

Support for IBM i 7.3
Oracle GoldenGate supports the latest DB2 for i platform.


– DDL replication between MySQL Databases
With the DDL replication between MySQL databases, there is no need to stop Oracle GoldenGate replication when there are DDL changes on the source database.


Filed under: Golden Gate
Categories: DBA Blogs

#GoldenGate Manager Access Rules

Sat, 2017-06-10 22:21

Starting in Oracle GoldenGate, the manager process is more secure than in previous releases of Oracle GoldenGate. After the inital install and running of the Manager process, you may run into the following warning message upon a restart of the Oracle GoldenGate environment:

WARNING OGG-01877 Missing explicit accessrule for server collector.

The inital description of the error message doesn’t make much sense. Using OGGERR, you can find out more about this error message:

$ oggerr ogg-01877
01877, 00000, "Missing explicit accessrule for server collector."
// *Cause: There was no explict ACCESSRULE specified for SERVER.
// *Action: Only allow accessing SERVER from hosts where data pump(s) might

// send trail files to this host.

As you can tell, the error message is pointing you to the ACCESSRULE parameter.

Now that you have more detail, you know that there is a new parameter called ACCESSRULE that needs to be specificed in the manager parameter file. To find out more information on the ACCESSRULE parameter look at this link.

The defnition of this parameter is:

“Use ACCESSRULE to control connection access to the Manager process and the processes under its control. You can establish multiple rules by specifying multiple ACCESSRULE statements in the parameter file and control their priority. To establish priority, you can either list the rules in order from most important to least important,or you can explicitly set the priority of each rule with the PRI option.” 

This parameter is not a default configured parameter for the manager process. This is something that you will need to decided on and how to control access to your environments. The syntax for this parameter is as follows:

ACCESSRULE[, PROG program_name][, IPADDR address][, PRI rule][, login_ID]{,ALLOW | DENY}

There are multiple example of how to define an access rule in the Oracle GoldenGate documentation. The example that I’m providing here is how I resolved the warning message in my Vbox test environment.


With this access rule, you can now start the manager process without issue. You would be allowing access to all processes in the Oracle GoldenGate environment.



Filed under: Golden Gate
Categories: DBA Blogs

Oracle GoldenGate 101 at IOUG 17 and GaOUG TechDays 17 within two month

Fri, 2017-03-31 08:00

Losts have changed for me since my last blog post. A new job and new responsibilities, which will include presenting more at conferences on the conference circuit and a few roadshows that are planned in the near future.

Back in the fall of 2016, when most of the Call for Papers open, I had a great idea for introducing people to Oracle GoldenGate. This idea is walk everyone through the progression of replication technology from CDC through Oracle GoldenGate, including some future road map items; that will ensure Oracle GoldenGate stays the number 1 replication tool on the market.

Over the next two months, I will be presenting this great idea/topic in a presentation called Oracle GoldenGate 101 – Replication from the beginning starting at IOUG Collaborate next week (April 2-6, 2017) and then at the best keep secret in the south – Georgia Oracle User Group (GaOUG) Tech Days 17 on May 9th-1th, 2017 (register here). This is going to be a session you will not want to miss, espeically if your organization uses Oracle GoldenGate for any architecture.

Additionally, the great guys who run the Georgia Oracle User Group (GaOUG) have done some amazing things since the planning of Tech Days 17 started. Just getting the key note speaker was a great addition to the already packed schedule of talks. This year, the keynote speaker is Maria Colgan, Master Product Manager for Oracle. She will be sharing some new and exciting items related to the Oracle Database and the direction Oracle is taking with new and exciting products.

GaOUG will also have dozens of Oracle ACE’s and ACE Director’s talking about topics accross multiple track (Database Development, Big Data, Applications, Middleteir). As an added bouns this year, GaOUG has added a half day Cloud track where you can learn all the items needed for your future Cloud deployments.

Hope to see everyone at both of these events, especially GaOUG in the coming months!

If you want to learn more about GaOUG Tech Days and the other impressive speakers, check out the blog hop posts below! What is a blog hop? It’s a group of bloggers who all get together to blog on a similar topic, giving you a lot of useful information from different perspectives, but in one stream. Enjoy your “hop” between blog posts and register for GaOUG Tech Days today!

Stewart Bryson
, RedPill Analytics
Jim Czuprynski, OnX Enterprise Solutions
Chris Lawless, Dbvisit Software
Danny Bryant, Accenture Enkitec Group
Eric Helmer, Mercury Technology Group

Filed under: GaOUG
Categories: DBA Blogs

Next Chapter … Time for a Change

Sat, 2017-01-21 20:06

It has been about three months since I’ve published a post. Well this time it has been a mix of work and decision making on my next chapter in my career that has kept me from posting. Hopefully, in the near future I will have more time to publish articles on topics I really want to discuss.

For the people close to me, many of you already know what this change is. I appreciate that you have kept it quite while all the details were worked out.

Before I get into what the next chapter of my career is, I have to say thanks to the management and team members at Accenture Enkitec Group (Enkitec/AEG). It has been a pleasure to work with everyone at Enkitec/AEG over the last couple of years. You truely are a great group of individuals and I will miss working daily with many of you. AEG is such a brain trust of people, it is almost hard to leave! I know that this will continue to be the trend at AEG.

Now what is the next chapter?

As of late January 2017, I have decided to leave Enkitec/AEG in pursuit of an opportunity that I feel is a good mix of my technical skills and allow me to grow in a challenging direction. Come Feburary 6th, 2017, I will be starting my new role as Senior Principal Product Manager on the GoldenGate team at Oracle. This position will allow me to work with a technology I’m very interested in and help shape the path forward for it. After all we know “cloud” is the next best thing right? How are you getting your data into or out of that “cloud”? Well, I will be part of the team that will help provide a path to do that. I’ll have to keep you posted on the technology as it evolves.

Now that you know where I’m going, just a few house keeping items to take care of. I’ve already said thanks to my friends at Enkitec/AEG. Without your support on many fronts, I wouldn’t be where I’m at as I proceed on this new chapter. Again, I greatly appreciate it.

For the community as a whole, many of you know that I’m an ACE Director (ACED) or at least was an ACED … LOL. Well, just like many of former ACEDs, when you go to Oracle you have to give it up. So, as of January 20, 2017, I resigned my ACED status and now listed as an ACE Alumni.

Blog site! I plan on keeping dbasolved.com up and running. I will be posting more article on GoldenGate here after starting with Oracle. So keep checking back to see if I’ve posted anything new. After all, GoldenGate is starting to become a hot topic!

I hope you will join me in my excitement on this new chapter of my career with Oracle and the GoldenGate team! I look forward to seeing many of you out and about within the community as I take on this new chapter.



Filed under: Golden Gate
Categories: DBA Blogs

Fabric … Simple #GoldenGate Administration over SSH

Wed, 2016-10-05 10:45


For awhile now, I’ve been looking at different approaches for automating some of my GoldenGate monitor tasks. Yes, there are tools out there that provide a wide variety of ways for interaction and monitoring of Oracle GoldenGate. However, the problem with Oracle GoldenGate is that you have at least two parts of the equation when interacting or monitoring an environment, i.e. source and target. If you are so lucky to have a multi-master configuration, then you have to deal with multiple target sites. In the end, making administration and knowing what is going on a bit of a time-consuming process.

I’ve looked at different tools to try and solve this problem; mostly in Perl since I write a lot of that from time to time. Trying to get Perl to do network commands like SSH was time consuming and cumbersome when scripting it. This is when I started to look at Python. Initially I wasn’t sold on Python, but after spending some time to get familiar with the language, I actually like it; but it still was missing the SSH functionality I wanted, until recently.

As I was browsing the inter-web over the last week or so, I came across a blog post that compared deployment tools such as Chef, Puppet, Ansible, Fabric and a few others. The website provided pros and cons for each of the deployment options they were reviewing. Take a look and see what you may like to use for deployment options in your environments.

Out of the tools the website talked about, Fabric peaked my attention because it was a Python-based tool for streamlining SSH application deployments. I was thinking, if I could deploy applications with Fabric, then I should be able to do some basic monitoring with it as well; sending me down the path of solving basic Oracle GoldenGate monitoring from a single command line. Yea sounds like a good plan!

After spending some time, figuring out how to install Fabric and what exactly is a “fabfile”, I wrote my first monitoring script for Fabric!

What exactly does this monitoring script look like? Below is the monitoring script, also known as a “fabfile”. This script is written in Python syntax and sets up the environment and what functions should be called.

from fabric.api import *

env.hosts = [

env.user = "oracle"
env.password = [ do not place in clear text ]

def info_all():
        with cd("$OGG_HOME"):
                run("echo info all | ./ggsci")

def ggstatus():

The environment that is going to be monitored are my test servers that I use for many of my tests (Fred and Wilma). Then I’m telling Fabric to use the “oracle” userid and password to login to each server. In this environment I have all my oracle passwords set the same so I only have to provide it once. After the environment is set, I define the functions that I want to perform. The first function is the info_all() function which is logging into GGSCI and providing me the output of the info all command. The second function is just a generic function that calls the first function.

With the “fabfile” created, I can now run a single command to access both servers (Fred & Wilma) and check on the status of the Oracle GoldenGate processes running on these servers. The command that I run is:

$ fab ggstatus -f ./fab_gg.py

This command is executing the Fabric executable “fab” followed by the function in the “fabfile” I want to execute. Then the “-f ./fab_gg.py” is the “fabfile” that I want to use during the execution. Upon execution, I will spool output on my STDOUT providing me the status of each Oracle GoldenGate environment for each server I’ve requested.

AMAC02P37LYG3QC:python bobby.l.curtis$ fab ggstatus -f ./fab_gg.py
[fred.acme.com] Executing task 'ggstatus'
[fred.acme.com] run: echo info all | ./ggsci
[fred.acme.com] out: The Oracle base has been set to /u01/app/oracle
[fred.acme.com] out: ====================================
[fred.acme.com] out: ORACLE_SID=src12c
[fred.acme.com] out: ORACLE_BASE=/u01/app/oracle
[fred.acme.com] out: ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
[fred.acme.com] out: OGG_HOME=/u01/app/oracle/product/12.2.0/oggcore_1
[fred.acme.com] out: JAVA_HOME=/home/oracle/Downloads/jdk1.8.0_71
[fred.acme.com] out: OGGSTUDIO_HOME=/u01/app/oracle/product/oggstudio/oggstudio
[fred.acme.com] out: OGGSTUDIO_HOME1=/u01/app/oracle/product/oggstudio/
[fred.acme.com] out: ====================================
[fred.acme.com] out:
[fred.acme.com] out: Oracle GoldenGate Command Interpreter for Oracle
[fred.acme.com] out: Version OGGCORE_12.
[fred.acme.com] out: Linux, x64, 64bit (optimized), Oracle 12c on Nov 11 2015 03:53:23
[fred.acme.com] out: Operating system character set identified as UTF-8.
[fred.acme.com] out:
[fred.acme.com] out: Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
[fred.acme.com] out:
[fred.acme.com] out:
[fred.acme.com] out:
[fred.acme.com] out: GGSCI (fred.acme.com) 1>
[fred.acme.com] out: Program     Status      Group       Lag at Chkpt  Time Since Chkpt
[fred.acme.com] out:
[fred.acme.com] out: MANAGER     RUNNING
[fred.acme.com] out: JAGENT      RUNNING
[fred.acme.com] out: EXTRACT     RUNNING     EGG12C      00:00:09      00:00:02
[fred.acme.com] out: Description 'Integrated Extract'
[fred.acme.com] out: EXTRACT     RUNNING     PGG12C      00:00:00      00:00:02
[fred.acme.com] out: Description 'Data Pump'
[fred.acme.com] out:
[fred.acme.com] out:
[fred.acme.com] out: GGSCI (fred.acme.com) 2>
[wilma.acme.com] Executing task 'ggstatus'
[wilma.acme.com] run: echo info all | ./ggsci
[wilma.acme.com] out: The Oracle base has been set to /opt/app/oracle
[wilma.acme.com] out: ====================================
[wilma.acme.com] out: ORACLE_SID=rmt12c
[wilma.acme.com] out: ORACLE_BASE=/opt/app/oracle
[wilma.acme.com] out: ORACLE_HOME=/opt/app/oracle/product/
[wilma.acme.com] out: OGG_HOME=/opt/app/oracle/product/
[wilma.acme.com] out: JAVA_HOME=/home/oracle/Downloads/jdk1.8.0_71
[wilma.acme.com] out: ODI_HOME=/opt/app/oracle/product/
[wilma.acme.com] out: ====================================
[wilma.acme.com] out:
[wilma.acme.com] out: Oracle GoldenGate Command Interpreter for Oracle
[wilma.acme.com] out: Version OGGCORE_12.
[wilma.acme.com] out: Linux, x64, 64bit (optimized), Oracle 12c on Nov 11 2015 03:53:23
[wilma.acme.com] out: Operating system character set identified as UTF-8.
[wilma.acme.com] out:
[wilma.acme.com] out: Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
[wilma.acme.com] out:
[wilma.acme.com] out:
[wilma.acme.com] out:
[wilma.acme.com] out: GGSCI (wilma.acme.com) 1>
[wilma.acme.com] out: Program     Status      Group       Lag at Chkpt  Time Since Chkpt
[wilma.acme.com] out:
[wilma.acme.com] out: MANAGER     RUNNING
[wilma.acme.com] out: JAGENT      RUNNING
[wilma.acme.com] out: REPLICAT    RUNNING     RGG12C      00:00:00      00:00:03
[wilma.acme.com] out:
[wilma.acme.com] out:
[wilma.acme.com] out: GGSCI (wilma.acme.com) 2>

Disconnecting from wilma.acme.com... done.
Disconnecting from fred.acme.com... done.

As you can tell, I get a scrolling output of both servers showing that the servers were logged into and changed directories to the $OGG_HOME. Then executed and “info all” command against GGSCI with the returning output. This makes for a quick and easy way to get the current status of all Oracle GoldenGate processes in an environment.

With this being an introduction to Fabric, I’m looking forward to seeing what else I can do with it. I’ll keep everyone posted on additional things I do may do with it. But for now, I encourage you to take a look at it and see if you can simplify some of your administration tasks with it.


about.me: http://about.me/dbasolved

Filed under: Dev Ops, Golden Gate
Categories: DBA Blogs

Oracle Open World 2016 – What GoldenGate sessions are there?

Wed, 2016-08-31 23:00

Well, it is that time of year again; the streets of San Francisco will be crowded with members of the Oracle community! As everyone in the Oracle community descends onto the bay area, there will be excitement about the things that will be announced this year. Sure a lot of it is going to be about “cloud” and what direction Oracle is taking with their “cloud” strategy. Besides, “cloud” there will be a lot of good things to take in as well. As I look through the online session catalog for 2016, I’m interested in the topics related to Oracle GoldenGate.

This year there appears to be a good number of Oracle GoldenGate sessions at Oracle Open World, to be specific there are 21 scheduled during the event. I have listed a few of the ones I think will be interesting and will make an attempt to attend; always check the session catalog because what I think is good you may not.

  • CON6632 – Oracle GoldenGate for Big Data
  • CON7318 – Getting Started with Oracle GoldenGate
  • CON6551 – New Oracle GoldenGate 12.3 Services Architecture (beta stuff)
  • CON6633 – Accelerate Cloud Onboarding with Oracle GoldenGate Cloud Service
  • CON6634 – Faster Design, Development and Deployment with Oracle GoldenGate Studio (should be updated from last year session)
  • CON6558 – Best Practice for High Availability and Performance Tuning for Oracle GoldenGate
  • UGF616 – Oracle GoldenGate and Apache Kafka: A Deep Dive into Real-Time Data Streaming
  • THT7817 – Real-Time and Batch Data Ingestion into Data Lake with Oracle GoldenGate Cloud Service
  • UGF5120 – Oracle GoldenGate and Baseball: Five Fundamentals Before Jumping to the Cloud

As we are within two weeks of Oracle Open World, I hope everyone is ready to go and looking forward to seeing others from the community.



Filed under: General
Categories: DBA Blogs

Digging into ADD SCHEMATRANDATA … what is #GoldenGate doing?

Fri, 2016-08-19 10:30

In my post on the differences between ADD TRANDATA and ADD SCHEMATRANDATA, I highlighted the differences between the two ways of adding supplemental logging to tables. One of the things I pointed out was that ADD SCHEMATRANDATA doesn’t add any supplemental log groups (SLG). Without SLGs then how does ADD SCHEMATRANDATA work? That was the question I was left with. So I started digging around to find the answer and I think you may be interested in this as well.


In order to figure out what is actually going on within the database when ADD SCHEMATRANDATA is run, I had to trace the GoldenGate session. In order to do this, I first had to login to the database from GGSCI using “dblogin useridalias ggate”. After logging in, I had to identify the session and then setup tracing. This was done with the following scripts:

—SQL to identify the GGate session
select 'exec dbms_monitor.session_trace_enable(session_id=>'||sid||', serial_num=>'||serial#||', binds=>true, waits=true)' 
from v$session where username = 'GGATE';

—Start tracing of the session
exec dbms_monitor.session_trace_enable(session_id=>156, serial_num=>15799, binds=>true, waits=>true);
—Disable tracing after done
exec dbms_monitor.session_trace_disable(session_id=>156, serial_num=>15799);

Now with tracing enabled for the session, I’m able to trace the GoldenGate session from within GGSCI. At this time, I’m able to run DELETE SCHEMATRANDATA and ADD SCHEMATRANDATA against the schema I want to add the logging to.

All the last step of tracing, I need to disable the tracing (script above) of the GoldenGate user and identify the trace file. By default the trace file should be in $ORACLE_BASE/diag/rdbms/<db>/<db>/trace. In this directory, you need to identify the trace files for the session that was traced.

oracle >ls -ltr src12c_ora_23267.*
-rw-r-----. 1 oracle oracle   2788 Aug 15 16:09 src12c_ora_23267.trm
-rw-r-----. 1 oracle oracle 300531 Aug 15 16:09 src12c_ora_23267.trc

After you know the trace file, you can perform a TKPROF on the trace file.

oracle > tkprof src12c_ora_23267.trc src12c_ora_23267.tkprofs

This is generate a tkprofs file that will show specifics for the sessions, such as the SQL that is ran. This is what I’m more interested in, what SQL is ran with ADD SCHEMATRANDATA is called.

Quick Review of TKProf file

I’m not going to go into all the details that are in a tkprof generated file, but I took a look through the file trying to figure out what is going on when running the ADD SCHEMATRANDATA. As I was searching the file, I found a reference to LOGMNR$ALWAYS_SUPLOG_COLUMNS.

If you have kept up with the versions of Oracle GoldenGate, you will know that this is a reference to LogMiner and that Oracle is integrating the Oracle GoldenGate processes with it. This is also a hint to where to look, a.k.a Oracle Streams. As I continued to look through the tkprof file, I found a few references to a streams package – DBMS_CAPTURE_ADM; along with sql statements making calls to views like STREAMS$_PREPARE_DDL.

BEGIN sys.dbms_capture_adm.ABORT_SCHEMA_INSTANTIATION('soe'); END;

At this point, it is safe to say, that Oracle is merging Oracle GoldenGate into Oracle Streams.

Review of Packages

Note: These are Oracle packages that are encrypted in the database. You can use UnwrapIt to view if needed. Output here is only for teaching purposes.

Now that I’m armed with what package the ADD SCHEMATRANDATA is calling, I can drill into what exactly is happening.

In taking a look at the DBMS_CAPTURE_ADM package, I wanted to look at the PREPARE_SCHEMA_INSTANTIATION procedure. I can quickly see that the procedure takes the schema_name and that defaults will be used for supplemental logging and container.

 SCHEMA_NAME               IN VARCHAR2, 

After the schema is passed to the procedure, this procedure passes all three of the parameters to another package and procedure for execution. This package is DBMS_CAPTURE_ADM_IVK.PREPARE_SCHEMA_INST_IVK.

SCHEMA_NAME               IN VARCHAR2,
  DBMS_LOGREP_UTIL.WRITE_TRACE('prepare_schema_inst_ivk()+ container: ' ||


           SYNCHRONIZATION != 'WAIT') 

     DBMS_LOGREP_UTIL.WRITE_TRACE('prepare schema objects for current' ||
            ' container: ', DBMS_LOGREP_UTIL.TRACE_FLAG_PUBLISH);


In this procedure, you will notice that the input is all three of the parameters that are passed from the PREPARE_SCHEMA_INSTANTIATION procedure. Then this procedure precedes to check the supplemental logging and attempts to sync the tables with the DBMS_XSTREAM_GG_ADM package. After the tables have been synced, then the procedure calls another package and procedure to prepare (DBMS_CAPTURE_ADM_INTERNAL.PREPARE_SCHEMA_INSTANTIATION) the tables with the required supplemental logging level.

When looking at this procedure, it takes in five different parameters; two of which are defaults. After the procedure sets up tracing, it checks to see if the calling procedure is specifying GoldenGate. Once everything is confirmed and synchronization is complete, then the procedure grabs the CURRENT_SCN, waits for any inflight transactions and prepares the schema tables before exiting the package.

SCHEMA_NAME               IN VARCHAR2,
IDX           NUMBER :=0;
 'dbms_capture_adm_internal.prepare_schema_instantiation()+', DBMS_LOGREP_UTIL.TRACE_FLAG_PUBLISH);

    DBMS_LOGREP_UTIL.WRITE_TRACE('dbms_capture_adm_internal.prepare_schema_instantiation()+ before read_ev', DBMS_LOGREP_UTIL.TRACE_FLAG_PUBLISH);

     SUPPLOG := 0;
    END IF;
   DBMS_LOGREP_UTIL.WRITE_TRACE('dbms_capture_adm_internal.prepare_schema_instantiation()+ before prepare_ddl',DBMS_LOGREP_UTIL.TRACE_FLAG_PUBLISH);
    DBMS_LOGREP_UTIL.WRITE_TRACE('dbms_capture_adm_internal.prepare_schema_instantiation()+ before prepare_schema_tables',DBMS_LOGREP_UTIL.TRACE_FLAG_PUBLISH);

    END IF;

Up to this point, the last three packages have been preparing the tables. Seems like a lot of preparing to get tables set for supplemental logging. Well the last call in the previous package called the procedure to prepare the tables. When I look at this procedure, I see that there is a call to a PREPARE_TABLE_INST procedure (getting a bit lazy on copying the procedure – don’t wan to put to much since this is Oracle’s code).

   DBMS_LOGREP_UTIL.DUMP_TRACE('error:dbms_capture_adm_internal.prepare_table_inst('||'owner='||CANON_OWNER_ARRAY(I)||' name='||CANON_NAME_ARRAY(I));

Now, when I go an look at the PREPARE_TABLE_INST procedure, I see that the procedure is calling a C package called “knlcpreptabinst” to set the supplemental logging on the tables associated with the schema.

CANON_OWNER                 IN VARCHAR2,
LOCKING                     IN BOOLEAN,
CONTAINER                   IN VARCHAR2) 
 NAME "knlcpreptabinst"
      LOCKING           UB2,       LOCKING           INDICATOR SB2,

Without drilling down into the C package, I cannot see exactly how the supplemental logging is added or where it is exactly stored in the database. I can only assume (we know what that means … lol), that it is in the data dictionary some where.

Looking for tables or views that may shed some light on this as well, I’ve found LOGMNR$SCHEMA_ALLKEY_SUPLOG that will show you the schema, if all keys are in supplemental log mode and if no validated pks are allowed. The following query is what I used to extract information about the SOE schema:

where allkey_suplog = 'YES';


Output is as follows:

--------------- --- ---
SOE             YES NO

In drilling down further, after have a friend of mine pointed out a function to me (follow him on twitter -> @resetlogs). You can get down to the table level on supplemental logging when using ADD SCHEMATRANDATA. There is a log miner function that has to be called when using SQL to pull the correct information. This function is similar named to the table I referenced above … LOGMNR$ALWAYS_SUPLOG_COLUMNS.

This function takes two parameters. The first is the schema that holds the objects and the second is the table name. So in the following example, I can see that the ORDERS table of the SOE schema has supplemental logging added.

select * from table(logmnr$always_suplog_columns('SOE','ORDERS'));

OWNER           TABLE_NAME                     COLUMN_NAME                        INTCOL     SEGCOL    USERCOL
--------------- ------------------------------ ------------------------------ ---------- ---------- ----------
SOE             ORDERS                         ORDER_ID                                1          1          1
SOE             ORDERS                         ORDER_TOTAL                             6          6          6
SOE             ORDERS                         COST_OF_DELIVERY                       11         11         11
SOE             ORDERS                         DELIVERY_ADDRESS_ID                    13         13         13
SOE             ORDERS                         ORDER_DATE                              2          2          2
SOE             ORDERS                         CUSTOMER_CLASS                         14         14         14
SOE             ORDERS                         CUSTOMER_ID                             4          4          4
SOE             ORDERS                         ORDER_STATUS                            5          5          5
SOE             ORDERS                         PROMOTION_ID                            8          8          8
SOE             ORDERS                         ORDER_MODE                              3          3          3
SOE             ORDERS                         SALES_REP_ID                            7          7          7
SOE             ORDERS                         WAREHOUSE_ID                            9          9          9
SOE             ORDERS                         DELIVERY_TYPE                          10         10         10
SOE             ORDERS                         WAIT_TILL_ALL_AVAILABLE                12         12         12
SOE             ORDERS                         CARD_ID                                15         15         15
SOE             ORDERS                         INVOICE_ADDRESS_ID                     16         16         16


I know this has been a long post, but hopefully, I’ve been able to somewhat show how the ADD SCHEMATRANDATA command within GGSCI works and where you can see if supplemental logging is turned on for the selected schema. As I was trying to dig to the root of this issue, I found it interesting that so many packages are involved with setting the supplemental logging on a schema/tables; while identifying if it is enabled is not as easy as ADD TRANDATA. Where when you use ADD TRANDATA the tables are easily identified and can quickly see that the supplemental log groups have been added.

As Oracle GoldenGate for Oracle moves to a more integrated approach to replication, I think more items will be tied to the log miner and streams architecture.



Filed under: Golden Gate
Categories: DBA Blogs

rlwrap with #GoldenGate GGSCI

Tue, 2016-08-16 10:45

Since I published posts on how to retrieve command history within GGSCI using the Oracle provided commands of “history” and “fc”, I was pinged a couple of times by some friends asking about “rlwrap” usage with GoldenGate. The answer is a simple yes, rlwrap can be used with Oracle GoldenGate.

What exactly is “rlwrap”?

According to the readme file at http://utopia.knoware.nl/~hlub/uck/rlwrap/#rlwrap, rlwrap is a ‘read-one wrapper’. Basically, it allows for the editing of keyboard input for any command.

How to install?

There are two ways to install “rlwrap”. The first way is manually, which requires you to run the configure and make commands; however, the easiest way I’ve found is by using a yum repository. Tim Hall (a.k.a Oracle-Base) documents this process quite effortlessly over at his blog. Thanks Tim!


After you have “rlwrap” installed, you simply have to create an alias in your profile to use it. You can use it from the command line as well; however, the alias approach ensure that it is executed every time you run ggsci. The below example is what I have running in my .bash_profile for the Oracle user.

alias ggsci='rlwrap $OGG_HOME/ggsci'

The next time I login to use ggsci, rlwrap will automatically be ran and then I will be able to scroll through the commands I typed while in ggsci. Another nice thing about “rlwrap” is that when I logout of ggsci, and then back in, my command history is available still.

Although the “history” and “fc” commands are handy, it would be nice to see Oracle include “rlwrap” into the core product of Oracle GoldenGate …



Filed under: Golden Gate
Categories: DBA Blogs

Edit #GoldenGate commands from GGSCI

Mon, 2016-08-15 10:45

Ever get tired of typing the same command over and over again in Oracle GoldenGate GGSCI? Well, Oracle has provided a history recall function into GGSCI, I talked about his in an earlier post. This post on history recall can be found here.

In this post, lets take the command recall and how these commands can be edited. In Oracle GoldenGate 12c, Oracle has provided an GGSCI command called “FC”. The “FC” command is used to display/edit a previously issued GGSCI command and the execute it again. This command leverages the memory buffer the same was as the history command does within GGSCI.

Now the syntax for using this command is as follows:

FC [ n | -n | string ]

n – Displays the command from the line number provided

-n – Displays the command that was issued n lines before the current line

string – Displays the last command that starts with the specified string

Let’s take a look at an example of using “FC” within GGSCI.

In the below output, I have issued a series of commands and then listed these commands using this history function.

GGSCI (fred.acme.com) 6> history

GGSCI Command History

    1: info all
    2: stats extract egg12c
    3: info mgr
    4: info pgg12c
    5: info all
    6: history

Using the “FC” command, I can edit and execute the edited command. If you take a look at command 4 (info pgg12c), I want to replace “pgg12c” with “egg12c”. this is done by using the “r” edit command as follows.

GGSCI 7> fc 4
GGSCI 7> info pgg12c
GGSCI 7..     regg12c
GGSCI 7> info egg12c

EXTRACT    EGG12C    Last Started 2016-08-12 17:06   Status RUNNING
Description          'Integrated Extract'
Checkpoint Lag       00:00:08 (updated 00:00:05 ago)
Process ID           24082
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2016-08-15 10:14:45
                     SCN 0.51017268 (51017268)

By executing “fc 4” (fc n), I’m telling GGSCI to pull the fourth command from the memory buffer for editing. Then I use the “r” command to provide the text that I want to replace in the command. Notice the position of the “r”, it is lined up directly under the string I want to replace. Then I provide the replacement string. Before execution GGSCI provides you with an output of the changed command. After verifying the command, the command can be executed providing the output for the correct process.

Note: For more editing options with FC, you can find these here.

If you want to know what command was executed n commands before the previous command, you can use the -n option to “FC”. This makes the “FC” command act like the history command but only displays the command at the -n position. This can be seen if you do a history command prior to a fc -n command.

GGSCI (fred.acme.com) 11> history

GGSCI Command History

    2: stats extract egg12c
    3: info mgr
    4: info pgg12c
    5: info all
    6: history
    7: info egg12c
    8: info mgr
    9: history
   10: info egg12c
   11: history

GGSCI (fred.acme.com) 12> fc -4
GGSCI (fred.acme.com) 12> info mgr
GGSCI (fred.acme.com) 12..

Manager is running (IP port fred.acme.com.15000, Process ID 12377).

You will notice, that I have eleven commands in the command history. By using “fc -4”, I’m able to retrieve the info mgr command and then execute it by simply by hitting return. Before hitting return, I could still edit the command as was shown previously.

Lastly, you can retrieve a previous command by searching for a specific string. The string has to be at the beginning of the command. In the below example, I’m searching for the last stats command that was ran.

GGSCI (fred.acme.com) 16> history

GGSCI Command History

    7: info egg12c
    8: info mgr
    9: history
   10: info egg12c
   11: history
   12: info mgr
   13: info all
   14: history
   15: stats extract egg12c
   16: history

GGSCI (fred.acme.com) 17> fc sta
GGSCI (fred.acme.com) 17> stats extract egg12c
GGSCI (fred.acme.com) 17..

Sending STATS request to EXTRACT EGG12C ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
	Operations                		           2.00
	Mapped operations         		           2.00
	Unmapped operations         		           0.00
	Other operations         		           0.00
	Excluded operations         		           0.00

As you can see, there is only one stats command in the command history. The “FC” command pulls this command and provides an opportunity to edit it. After hitting return, the command is executed.

Now that you know how to recall and edit Oracle GoldenGate commands from the GGSCI command prompt, hopefully, this will make your life with Oracle GoldenGate a bit easier.



Filed under: Golden Gate
Categories: DBA Blogs

To TRANDATA or To SCHEMATRANDATA? … That is the #GoldenGate questions of the day!

Fri, 2016-08-12 19:30

If you are familiar with using Oracle GoldenGate, you know that on the source side of the equation you have to enable supplemental logging and sometimes force logging on the database. I traditionally do both just to make sure that I capture as much as I can into the redo stream from the transactions on the database. For Oracle GoldenGate purposes, this is not the only thing you need to turn on to ensure all needed information is captured to the trail files.

There are two Oracle GoldenGate GGSCI commands that can be ran to enable supplemental logging at the schema or table level. These commands are ADD TRANDATA and ADD SCHEMATRANDATA. What is the difference between the two, you may ask?

ADD TRANDATA – is used to enable supplemental logging at the table level
ADD SCHEMATRANDATA – is used to enable supplemental logging at the schema level

That is such a high-level view of the concept. What is the difference between the two trandata approaches, really?


ADD TRANDATA command is used to enable Oracle GoldenGate to acquire the transaction information that it needs from the transaction records. This version of the command can be used on the following databases:

  • DB2 for i Database
  • DB2 LUW Database
  • DB2 z/OS Database
  • Oracle Database
  • MS SQL Server
  • Sybase Database

For an Oracle Database, ADD TRANDATA enables the unconditional logging of the primary key and conditional supplemental logging of all unique key(s) and foreign key(s) of the specified table. Additionally, you can use ADD TRANDATA with the COLS option to log any non-key columns that can be used with the FILTER statements and KEYCOLS clauses in the TABLE and MAP parameters.

An example of adding trandata to a schema would be:

GGSCI> dblogin useridalias gate
GGSCI> add trandata soe.*


Once transdata has been added to the schema/tables, you can verify the existence of trandata from GGSCI using the INFO TRANDATA command as demonstrated in the below command set.

GGSCI> dblogin useridalias gate
GGSCI> info trandata soe.addresses
2016-08-12 15:07:23  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema SOE.
2016-08-12 15:07:23  INFO    OGG-01980  Schema level supplemental logging is enabled on schema SOE for all scheduling columns.
Logging of supplemental redo log data is enabled for table SOE.ADDRESSES.

Now that ADD TRANDATA has been ran, what exactly does ADD TRANDATA do to the database it is ran against? For an Oracle Database, ADD TRANDATA adds a Supplemental Log Group (SLG) on to the table. This can be seen from the DBA_LOG_GROUP view under SYS. The SLGs that are corrected are all labeled with a prefix of “GGS”. The following output shows what this looks like after running it for a whole schema.

select owner, log_group_name, table_name, log_group_type, always, generated 
from dba_log_groups
where owner = 'SOE'
and log_group_name like 'GGS%';

OWNER           LOG_GROUP_NAME       TABLE_NAME                     LOG_GROUP_TYPE                 ALWAYS                         GENERATED                    
--------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
SOE             GGS_105669           CUSTOMERS                      USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105702           ADDRESSES                      USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105735           CARD_DETAILS                   USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105768           WAREHOUSES                     USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105769           ORDER_ITEMS                    USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105802           ORDERS                         USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105835           INVENTORIES                    USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105836           PRODUCT_INFORMATION            USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105837           LOGON                          USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105870           PRODUCT_DESCRIPTIONS           USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_105871           ORDERENTRY_METADATA            USER LOG GROUP                 ALWAYS                         USER NAME                     
SOE             GGS_254721           TEST1                          USER LOG GROUP                 ALWAYS                         USER NAME

Now, there are some who will argue that the same effect can be done by just asking a SLG to a table manually. Although this is true, Oracle GoldenGate uses the GGS_ prefix to keep track of the tables that are in the replication process. Also, easier to clean up when you issue DROP TRANDATA, which will remove all the associated SLG items from the tables.

The ADD TRANDATA approach should be used with 11g or older versions of Oracle GoldenGate. As you move towards new version of Oracle GoldenGate, Oracle is pushing that everyone pick up and use the ADD SCHEMATRANDATA method. So let’s take a look at that now.


The ADD SCHEMATRANDATA is used on all the current and future tables in a given schema to automatically log a superset of available keys that Oracle GoldenGate needs for row identification. Using this version of TRANDATA, it can be used with both the integrated and classic capture processes.

There are four key reasons why you should use ADD SCHEMATRANDATA:

  • Enables supplemental logging for new tables created with a CREATE TABLE DDL command.
  • Updates supplemental logging for tables affected by an ALTER TABLE DDL command that adds or drops columns
  • Updates supplemental logging for tables affected by RENAME TABLE command
  • Updates supplemental logging for tables affected by adding or dropping of unique or primary key constraints


Although ADD SCHEMATRANDATA can be used with both integrated and classic capture processes, it is mostly geared towards the integrated process. There are three primary reasons to use ADD SCHEMATRANDATA with the integrated capture:

Ensures that the correct key is logged by logging all the keys
Options are provided that enable the logging of the primary, unique, and foreign keys to support the computation of dependences among the tables being processed by the integrated replicats (think apply servers)
Ensures the appropriate key values are logged in the redo to allow DML to be mapped to object that have DDL issued against them.

Earlier in this post, I mentioned that I often enable “force logging” on the database when I do the minimal supplemental logging. Force logging is encouraged by Oracle, especially when using ADD SCHEMATRANDATA.

Now to add issue ADD SCHEMATRANDATA against an Oracle database, it is similar the same way as ADD TRANDATA, with the difference that you don’t have to provide any wildcards. In the examples below, I show you how this can be done:

GGSCI> dblogin useridalias ggate
GGSCI> add schematrandata soe
2016-08-12 15:47:40  INFO    OGG-01788  SCHEMATRANDATA has been added on schema soe.
2016-08-12 15:47:40  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema soe.

After running ADD SCHEMATRANDATA, you can perform an INFO SCHEMATRANDATA on the schema to see what has been modified.

GGSCI (fred.acme.com as ggate@src12c) 9> info schematrandata soe

2016-08-12 15:51:52  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema SOE.

2016-08-12 15:51:52  INFO    OGG-01980  Schema level supplemental logging is enabled on schema SOE for all scheduling columns.

2016-08-12 15:51:52  INFO    OGG-10462  Schema SOE have 12 prepared tables for instantiation.

Digging around in the database, to see if ADD SCHEMATRANDATA does the same as ADD TRANDATA with SLG; well, it doesn’t. ADD SCHEMATRANDATA does not create any SLGs. The only place that I have found that has any record of supplemental logging turned on with ADD SCHEMATRANDATA is in the V_$GOLDENGATE_CAPABILITIES view. Here, you can see that supplemental logging has been enabled, the number of times it has been acted upon and when it was last executed.

NAME                        COUNT TO_CHAR(LAST_USED     CON_ID
---------------------- ---------- ----------------- ----------
DBENCRYPTION                    0 12-JUN-2016 21:20          0
DBLOGREADER                     0 12-JUN-2016 21:20          0
TRIGGERSUPPRESSION              0 12-JUN-2016 21:20          0
TRANSIENTDUPLICATE              0 12-JUN-2016 21:20          0
DDLTRIGGEROPTIMIZATION          0 12-JUN-2016 21:20          0
GGSESSION                       0 12-JUN-2016 21:20          0
DELETECASCADEHINT               0 12-JUN-2016 21:20          0
SUPPLEMENTALLOG                 5 12-AUG-2016 16:02          0

Now, being that the integrated items of Oracle GoldenGate are closely related to Oracle Streams, there may be a table or view related to Streams that has this information. Once I find it, I’ll provide an update to this post.

In the mean time, I hope this post has provided some insight into the differences between ADD TRANDATA and ADD SCHEMATRANDATA.

If you are moving to or using the integrated products of Oracle GoldenGate, then ADD SCHEMATRANDATA is the method that you should be using.






Filed under: Golden Gate
Categories: DBA Blogs

#GoldenGate #Cloud Service (#GGCS) … what to expect?

Mon, 2016-05-02 07:30

As I sit here working on performing some GoldenGate migrations to AWS for a client, I’ve been thinking about the glimpse of GoldenGate Cloud Service (GGCS) that was provided to me earlier this week. That glimpse has helped me define what and how GGCS is going to work within the Oracle Cloud space. Ever since this service was announced back at Oracle Open World 2015, I’ve been wanting to get my hands on this cloud product from Oracle to just better understand it. Hopefully, what I’m about to share with you will provide some insight into what to expect.

First, you will need a cloud account. If you do not have a cloud account; visit http://cloud.oracle.com and sign up for an account. This will typically be the same account you use to login to My Oracle Support (MOS).

Once you have an account and are in the cloud interface, subscribe to some services. You will need a Database Cloud Service or an Compute Cloud Service. These services will be the end points for the GGCS to point to. As part of setting up the compute node, you will need to setup SSH access with a public/private key. Once you create the GGCS instance, the same public/private key should be use to keep everything simple.

Once GGCS is made available for trial, currently it is only available through the sales team, many of us will have the opportunity to play with this. The following screen captures and comments were taken from the interface I had access to while discussing GGCS with Oracle Product Management.

Like any of the other cloud services from Oracle, once you have access to GGCS it will appear in your dashboard as available cloud services. In the figure below, GGCS is listed at the top of the services that I had access to. You will notice over on the right, there is a link called “service console”.

When you click on the service console link, you are taken to the console that is specific to GGCS. On the left hand side of the console, you will see three primary areas. The “overview” area is the important one; it provides you with all the information needed about your GGCS environment. You will see the host and port number, what version of GGCS you are running and the status of your environment.

With the environment up and running, you will want to create a new GGCS instance. This instance is created under your cloud service console. On this screen you are given information that tells you how many instances you have running with the number of OCPUs, Memory and storage for the configuration along with the public IP address. Notice the button to the right, just below Public IPs, this is the button that allows you to create a new GGCS instance. In the figure below, the instance has already been created.

Drilling down into the instance, you are taken to a page that illustrates your application nodes for GGCS. Notice that the GGCS instance actually created a compute node VM to run GoldenGate from.

With everything configured from the Oracle Cloud interface, you can now access the cloud server using the details provided (do not have current screen shots of this). Once you access the cloud server, you will find that Oracle GoldenGate has been configured for you along with a TNS entry that points to a “target” location. These items are standard template items for you to build your GoldenGate environment from. The interesting thing about this configuration is that Oracle is providing a single virtual machine (compute node) that will handle all the apply process to a database (compute node).

With the GGCS service running, you are then ready to build out your GoldenGate environment.

Like many other GoldenGate architectures, you build out the source side of the architecture like anything else. You install the GoldenGate software, build an extract, trail files and a data pump. The data pump process is then pointed to the GoldenGate Cloud Service (GGCS) instance instead of the target instance. The local trail files will be shipped to the GGCS machine. Once on the GGCS instance, the replicat would need to be configured. Part of the configuration of the replicat at this point is updating the TNSNames.ora file to point to the correct “target” compute node/database instance. The below picture illustrates this concept.

You will notice that the GGCS is setup to be an intermediary point in the cloud. This allows you to be flexible with your GoldenGate architecture in the cloud. From a single GGCS service you can run multiple replicats that can point to multiple difference cloud compute nodes; turning your GGCS into a hub that can send data to multiple cloud resources.

In talking with the Oracle Product team about GGCS, the only downside to GGCS right now is that it cannot be used for bi-directional setup or pulling data from the cloud. In essence, this is a uni-direction setup that can help you move from on-premise to cloud with minimal configuration setup needed.

Well, this is my take on GGCS as of right now. Once GGCS trials are available, I’ll try to update this post or add more posts on this topic. Until then, hope you have gain a bit of information this topic and looking forward to using GGCS.


about.me: http://about.me/dbasolved

Filed under: Cloud, Golden Gate
Categories: DBA Blogs

Veridata and boot.properties file

Mon, 2016-04-04 12:30

I’m spending some time building out an Oracle WebLogic/Veridata system at home. This is mostly for testing and general understanding purposes. One of the things I’ve noticed with the WebLogic server requires the username and password before the WebLogic server will start. After providing the name and password, the terminal window has to stay open because, closing it will kill the WebLogic server that is running. This leads to two interesting question and simple resolutions.

1. How can I automatically pass the username and password to WebLogic?
2. How to I keep WebLogic running upon closing my terminal window?

The answers to the questions are quite simple. Let’s take a look at the first question.

How can I automatically pass the username and password to WebLogic?

This is done by using a “boot.properties” file. This file needs to be placed in the security directory under the server that you want to automatically log in. In my case, I had to create a security directory and the boot.properties file under the AdminServer directory. The below steps are what I used:

$ /opt/app/oracle/product/
$ cd ./servers
$ cd ./AdminServer
$ mkdir security
$ touch boot.properties
$ vi boot.properties


Once the boot.properties file is open for editing, simply put in the username and password you want the WebLogic server to use. In this case, I’m using a standard password setup.

After creating the boot.properties, file, you can go back and start the WebLogic Server and should not be prompted for a userid and password. You can see this during the startup of the Weblogic server as seen in the below image. Additionally, if you go and look at the boot.properties file, the username and password will be encrypted as well.

This same approach can be done with the Veridata components of the configuration. You just have to remember to create a security directory under the Veridata server directory as you did for the Weblogic server.

Now for the second question:

How to keep WebLogic Server running upon closing my terminal window?

With the boot.properties file in place, you can start the WebLogic/Veridata server(s) by using the “nohup” option. “Nohup” will allow you to keep the server processes running in the background.

Using these two options, you can very easily automate the starting/restarting of the WebLogic/Veridata server(s).



Filed under: Golden Gate, WebLogic
Categories: DBA Blogs