DBA Blogs

Oracle 19c Automatic Indexing: Configuration (All I Need)

Richard Foote - Mon, 2019-07-29 00:12
In this post, I’ll discuss how to configure the new Automatic Indexing capability introduced in Oracle Database 19c. The intent of Oracle here is to make the configuration of Automatic Indexing as simplistic as possible, with as few levers for DBAs to potentially stuff up as possible. The ultimate goal would be to have a […]
Categories: DBA Blogs

Python Script To Backup Linux Directory To Windows

Bobby Durrett's DBA Blog - Thu, 2019-07-25 18:32

I found out that my blog backup script was failing so I had to rewrite it to handle dropped connections to my remote sftp server. In the process I broke out as much of the code as I could into a module that I could share. The module is backupremote.py in my miscpython repository. Might be helpful to someone else. It copies the directory tree on a remote Linux server down to a directory on a Windows machine (i.e. a laptop). Uses sftp.

The earlier version of this script was in this blog post: https://www.bobbydurrettdba.com/2018/05/30/python-script-to-backup-remote-directory-using-sftp/


Categories: DBA Blogs

Certs and AdminClient … How to login?

DBASolved - 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 – 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 OGGCORE_19.</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 OGGCORE_19.

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.


Categories: DBA Blogs

Simple way to get rid of OGG-01525 for AdminClient

DBASolved - 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

export OGG_VAR_HOME=/tmp


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 OGGCORE_19.

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!


Categories: DBA Blogs

Oracle 19c Automatic Indexing: Methodology Introduction (After Today)

Richard Foote - Tue, 2019-07-23 23:27
For the past month or so I’ve been playing around extensively with the new Oracle 19c “Automatic Indexing” feature, so I thought it was time to start blogging about it. Considering it’s only in “Version 1” status, my initial impression is very positive in that it works extremely well doing at what it’s initially designed […]
Categories: DBA Blogs

What is a Blog and What does it Stand for?

VitalSoftTech - Tue, 2019-07-23 09:49

All of us who use the internet daily are familiar with what a blog is. A blog is a web page or a website that one person or a small group regularly updates, written in a simple conversational style. But how many of us know what the word blog means or what does it stand […]

The post What is a Blog and What does it Stand for? appeared first on VitalSoftTech.

Categories: DBA Blogs

Age Range of Generations & Customer Demographics

VitalSoftTech - Tue, 2019-07-16 10:09

Being an online content producer and marketer, one of your primary concerns should be is your content and ad on your website, reaching the correct customer demographics and age ranges of generations?  To think of it, the question of who is your target audience is a challenging question. You must answer this question to make […]

The post Age Range of Generations & Customer Demographics appeared first on VitalSoftTech.

Categories: DBA Blogs

Witty Screen Names and Why You Should Use Them

VitalSoftTech - Tue, 2019-07-09 10:08

There are several reasons why someone would require a screen name for social media. Everyone manages their privacy in their unique ways. Some are more comfortable letting on about themselves to the oldest and most trusted friends. Similarly, others tell their grave dark tales to strangers on trains or in these days, social media. Considering […]

The post Witty Screen Names and Why You Should Use Them appeared first on VitalSoftTech.

Categories: DBA Blogs

Snapchat Usernames that are Interesting and More You

VitalSoftTech - Tue, 2019-07-02 09:52

Snapchat is an application for Android and Apple by Eva Spiegel and Bobby Murphy. It is a social media messenger to allow users to share their photos and videos with their friends. It is essential to have a cool Snapchat username that will help you portray your personality and entertain your friends and family. One […]

The post Snapchat Usernames that are Interesting and More You appeared first on VitalSoftTech.

Categories: DBA Blogs

Oracle Transparent Data Encryption and the world of Multitenant Database (Orace 12c)

VitalSoftTech - Tue, 2019-07-02 09:45

Step-by-step instructions on how to secure the Oracle Database Datafiles and the Operating System Data Files using Oracle 12c Transparent Data Encryption. Learn more ..

The post Oracle Transparent Data Encryption and the world of Multitenant Database (Orace 12c) appeared first on VitalSoftTech.

Categories: DBA Blogs

Using DbVisualizer to work with #Oracle, #PostgreSQL and #Exasol

The Oracle Instructor - Tue, 2019-07-02 09:01

As a Database Developer or Database Administrator, it becomes increasingly unlikely that you will work with only one platform.

It’s quite useful to have one single tool to handle multiple different database platforms. And that’s exactly the ambition of DbVisualizer.

As a hypothecial scenario, let’s assume you are a database admin who works on a project to migrate from Oracle to EDB Postgres and Exasol.

The goal might be to replace the corporate Oracle database landscape, moving the OLTP part to EDB Postgres and the DWH / Analytics part to Exasol.

Instead of having to switch constantly between say SQL Developer, psql and EXAplus, a more efficient approach would be using DbVisualizer for all three.

I created one connection for each of the three databases here for my demo:Now let’s see if statements I do in Oracle also work in EDB Postgres and in Exasol:




Works the same for all three! The convenient thing here is that I just had to select the Database Connection from the pull down menu while leaving the statement as it is. No need to copy & paste even.

What about schemas and tables?


In EDB, I need to create a schema accordingly:



In Exasol, schema and table can be created in the same way:


Notice that the data types got silently translated into the proper Exasol data types:


There is no DBA_TABLES in Exasol, though:


Of course, there’s much more to check and test upon migration, but I think you got an idea how a universal SQL Client like DbVisualizer might help for such purposes.


Categories: DBA Blogs

Build an Oracle GoldenGate Compute Node on OCI Marketplace

DBASolved - 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/


Categories: DBA Blogs

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

DBASolved - 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:


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>


Categories: DBA Blogs

During Extract Upgrade “extract not ready to be upgraded because recovery SCN” returned

VitalSoftTech - Mon, 2019-06-24 23:59

During the upgrade of a Classical extract process to Integrated extract I get the "extract not ready to be upgraded because recovery SCN". How do I work around this?

The post During Extract Upgrade “extract not ready to be upgraded because recovery SCN” returned appeared first on VitalSoftTech.

Categories: DBA Blogs

Contextual Targeting vs Behavioral Targeting

VitalSoftTech - Tue, 2019-06-18 12:19

Let’s suppose these are the olden times and you have to advertise for a new circus in town. Do you paste the posters on the walls of places of entertainment like a movie theater, a bar, horse racing tracks, or a casino? Or do you spend a little time about town and look around for […]

The post Contextual Targeting vs Behavioral Targeting appeared first on VitalSoftTech.

Categories: DBA Blogs

Looking for errors in the Clusterware and RAC logs? Dash through using the TFA Collector

VitalSoftTech - Mon, 2019-06-17 09:49

The Oracle Trace File analyzer utility has been originally developed by Oracle to help collect and bundle up all the pertinent diagnostic data in the log files, tracefiles, os statistics, etc.. This is a very common task when Oracle Support engineers request this information to help troubleshoot issues and bugs.

The post Looking for errors in the Clusterware and RAC logs? Dash through using the TFA Collector appeared first on VitalSoftTech.

Categories: DBA Blogs

Batch Query Reduced from 12 hours to 45 Minutes

Bobby Durrett's DBA Blog - Thu, 2019-06-13 16:38

I was asked to look at a batch SQL query that ran for 12 hours on June 4th. I messed around with some other ideas and then went back to my best method for tuning long running batch queries. I ripped it apart. My code ran in 45 minutes.

This only works if you have the code and can change it. I work with a lot of PL/SQL code in Unix shell scripts running SQL*Plus. If someone comes to me with a SQL statement in this kind of code, I do not tear my hair out trying to tune it in some clever way. Also, this assumes that I cannot use a SQL Profile. SQL Profiles are my first choice for production performance problems. But for long running SQL that I have full control over and cannot use a SQL Profile I tear it apart.

Ripping or tearing a SQL statement apart means that I become the optimizer. In this case the problem query joined 5 tables. It summarized 3 months of data from a large fact table and the other 4 tables were joined together with the fact table. I replaced the one query with 5 queries each of which saved their results in a table. This first query summarized the fact table and the remaining four joined one more table to the current results. Something like this:

  • Summarize 3 months of fact table data – table 1
  • Join table 2 on surrogate key
  • Join table 3 on surrogate key
  • Join table 4 on natural keys
  • Join table 5 on natural keys

So, I created 5 tables each of which held the results of the previous joins. I dropped the tables as I was done with them to save space.

I have a feeling that I could use some clever hint to force the join order and access methods to match my 5 queries. But my time is short and this works, so I did not bother trying. When you have a query that runs for 12 hours it’s not very motivating to try various hints to get it to run faster. How long do you wait for each try before you give up? Working on one table at a time is nice. I have had this approach work for me time and time again. It is almost a mechanical process without a lot of painful thinking.

Anyway, I pass this on to the internet. People may think that breaking up a 5 table join into 5 queries is ugly, but it works.


Categories: DBA Blogs

Another On Call Week, Another SQL Profile (or two)

Bobby Durrett's DBA Blog - Thu, 2019-06-13 16:14

I was on call again last week and Sunday night I used a SQL Profile to fix a performance problem. We rolled out some updates Saturday night and the modified query ran fine in some cases, but others ran for hours. When I got on there were 60 sessions running the bad plan and the load on the system was very high. It took me a while to identify the good plan, but then I found it, put in a SQL Profile, killed the 60 bad sessions, and the problem jobs ran in 2 minutes each. A similar situation came up Monday morning after I went off on call and a coworker took over and he ended up applying another SQL Profile on a similar query.

I spent the past couple of hours doing my typical SQL tuning exercise to see if I could figure out why Sunday’s query sometimes chose the bad plan.

The typical scenario includes these elements:

  1. Partitioned table with some near empty partitions and a lot of full partitions
  2. Bind variables used to determine partition choice

In our case we have certain tables that partition data by a code number. Certain numbers were used in the past or for other reasons are not active now. My guess is that the bad plans that we see come from bind variable values that point to the empty partitions. The bad plan works fine with empty partitions but then the optimizer uses it on full partitions and the query spins for hours.

I started to research this further to see if I could come up with a better fix than putting in SQL Profiles but did not get very far. I thought I would just pass this post along as is.


P.S. I originally wrote this June 4th, but decided to just publish as is today.

Categories: DBA Blogs

Slides and My Impressions from May 17th AZORA Meetup

Bobby Durrett's DBA Blog - Tue, 2019-06-11 18:44

We have the slides from the two talks at our May 17th AZORA Meetup.

Here are Stephen Andert’s slides: Networking is NOT just cables and fiber!

Here are Doug Hood’s slides in three parts:

  1. Using SQL and PLSQL for Mid-Tier Database Caching
  2. Oracle TimesTen Scaleout – World’s Fastest OLTP DB
  3. Oracle In-Memory Data Processing

I wanted to share my impressions of the two talks and the meeting as well as share the slides. Of course, these are my opinions shaped by my own experiences and not universally true of everyone!

This meetup had two very different types of talks. I thought of Stephen’s talk as a career development or soft skills sort of talk. I have been to a number of talks like that at national Oracle user group conferences such as Collaborate. They help balance out the purely technical Oracle database content. Once Stephen got into his talk, I really started to appreciate the quality of the content. To me he was talking about keeping in touch with people in an intentional but sincere way. I like the idea of planning on contacting people a certain number of times per year for example.

Years ago, in my first job I worked for Campus Crusade for Christ (now Cru) and I raised money to support myself and my family. I networked with people that I met through churches and friends and family members. It was different than networking as part of a DBA career because I was directly asking for money instead of making career-oriented connections. But the core idea that I remember from Stephen’s talk applied then. Stephen’s idea was to genuinely seek to help the folks in your network without focusing on what they could do for you. In my CCC days the support raising training told us that we were not “fundraising” but instead “friend raising”. I had some great experiences meeting people and getting to know them and I think it was best when my focus was on how to inspire and encourage the people I met rather than to anxiously think about whether they could give money to support what I did.

The other less serious connection I saw between Stephen’s presentation and my Cru days is that Stephen has a hand-written database setup to keep track of his people. Back in the day I had a Paradox database from Borland running on MS-DOS to do a lot of the same things. So, hearing Stephen talk about his contact database was a blast from the past for me.

I am not really doing much in the way of networking myself these days. I write this blog. I speak at conferences every couple of years or so. I help with the local Oracle user group AZORA. But I am not intentionally making and maintaining relationships with other technical people in the way Stephen described so his talk gave me something to think about.

Doug Hood’s talk was at the other end of the spectrum with some cool technology. Doug spoke on several things and with a lot of detail so I cannot do a good job of summarizing what he said. Check the slides for more details. But I do want to write down my impressions. Listening to Doug’s talk reminded me of some of the computer science study that I have been doing on my own recently. I have sort of gone back to school as an independent learner. When Doug talked about the memory hierarchy and caching it tied right back to the assembly language and algorithms study I have been doing.

Doug presented some cool hardware that puts persistent memory close enough to the CPU that it changes the way we think about memory hierarchy. What if you replace your RAM with persistent RAM that did not get cleared when you power off your computer? Evidently in some architectures (maybe all the modern ones these days I don’t know) the RAM is closely connected to the CPU and does not have to be accessed over the bus in the way I/O is. So, persistent RAM would be much faster than some solid-state disk being read over the bus no matter how fast the SSD is. Anyway, see Doug’s slides. I am sure that I am butchering the details, but I am giving my impression and my memory so keep that in mind.

In general database work and database performance has a lot to do with caching. I have heard a little bit about how algorithms can be designed to work well with CPU caches. I recently read a chapter about the B-Tree data structure that is used in databases and it was a big contrast to the other data structures I had studied because it took disk reads and memory accesses into consideration. Anyway, at a high level I took away from Doug’s talk notions about memory and caching and different ways people can tweak the memory hierarchy to get better database performance.

I had to leave a little early to head for the mountains for the weekend but as always, I valued the time I spent at AZORA, my local Oracle user group. I appreciate Stephen and Doug stepping up and giving their presentations. I hope that my links and the way I characterized their talks is accurate enough. I am sure that I made mistakes, but I got something out of my time and appreciate their efforts.

AZORA is taking a break for the hot Arizona summer but coming back strong with our next meeting on September 27th. The details are being finalized so keep an eye on our Meetup page.


Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs