DBA Blogs

Oracle 19c Automatic Indexing: How Many Executions Does It Take? (One Shot)

Richard Foote - Wed, 2019-08-28 23:16
One of the first questions I asked when playing with the new Oracle Database 19c Automatic Indexing feature was how many executions of an SQL does it take for a new index to be considered? To find out, I create the following table: I then ran the following query just once and checked to see […]
Categories: DBA Blogs

Cloning of RDS Instance to Another Account

Pakistan's First Oracle Blog - Wed, 2019-08-28 21:01
Frequently, we need to refresh our development RDS based Oracle database from the production which is in another account in AWS. So we take a snapshot from production, share it with another account and then restore it in target from the snapshot.

I will post full process in a later post, but for now just sharing an issue we encountered today. While trying to share a snapshot with another account, I got the following error:

Sharing snapshots encrypted with the default service key for RDS is currently not supported.

Now, this snapshot was using default RDS keys and that is not supported. So in order to share it, we need to have customer managed keys and then copy this snapshot with these news keys and only then we can share it. You don't have to do anything at the target, as these customer managed keys become part of that snapshot. You can create customer managed keys in KMS console and may be assign to IAM user you are using.

I hope it helps.
Categories: DBA Blogs

Speaking at Trivadis Performance Days 2019

Richard Foote - Wed, 2019-08-28 06:36
I’ll again be speaking at the wonderful Trivadis Performance Days 2019 conference in Zurich, Switzerland on 26-27 September. There’s again another fantastic lineup of speakers, including: CHRISTIAN ANTOGNINI IVICA ARSOV MARK ASHDOWN SHASANK CHAVAN EMILIANO FUSAGLIA STEPHAN KÖHLER JONATHAN LEWIS FRANCK PACHOT TANEL PODER DANI SCHNIDER   I’ll be presenting two papers: “Oracle 18c and […]
Categories: DBA Blogs

DevOps for Oracle DBA

Pakistan's First Oracle Blog - Sun, 2019-08-25 00:21
DevOps is natural evolution for Oracle database administrators or sysadmins of any kind. The key to remain relevant in the industry is to embrace DevOps these days and in near future.

The good news is that if you are an Oracle DBA, you already have the solid foundation. You have worked with the enterprise, world class database system and are aware of high availability, disaster recovery, performance optimization, and troubleshooting. Having said that, there is still lots to learn and unlearn to become a DevOps Engineer.

You would need to look outside of Oracle, Linux Shell and the core competency mantra. You would need to learn a proper computer language such as Python. You would need to learn software engineering framework like Agile methodology, and you would need to learn stuff such as Git. Above all you would need to unlearn that you only manage Database. As DevOps Engineer in today's Cloud era, you would be responsible for end to end delivery.

Without Cloud skills, its impossible to transition from Oracle DBA to DevOps role. Regardless of the cloud provider, you must know the networking, compute, storage, and infrastructure as code. You already know the databases side of things, but now learn a decent amount about other databases as you would be expected to migrate and manage them in the cloud.

So any of public cloud like AWS, Azure, or GCP plus a programming language like Python or Go or NodeJS, plus agile concepts, IaC as Terraform or CloudFormation, and plethora of stuff like code repositories and pipelining would be required to be come an acceptable DevOps Engineer.

Becoming obsolete by merely staying Oracle DBA is not an option. So buckle up and start DevOps journey today.
Categories: DBA Blogs

Speaking at Oracle OpenWorld 2019

Richard Foote - Thu, 2019-08-22 03:23
It’s been remarkably 9 years since I’ve been to Oracle OpenWorld, but will finally get the opportunity to present there again this year (with many thanks to the Oracle ACE Director program for making this possible). Details of my presentation are as follows: Conference: Oracle OpenWorld Session Type: Conference Session Session ID: CON1432 Session Title: […]
Categories: DBA Blogs

AUSOUG Connect 2019 Conference Series

Richard Foote - Wed, 2019-08-21 03:26
  AUSOUG will again be running their excellent CONNECT 2019 conference series this year at the following great venues: Monday 14th October – Rendezvous Hotel In Melbourne Wednesday 16th October –  Mercure Hotel in Perth As usual, there’s a wonderful lineup of speakers from both Australia and overseas including: Connor McDonald Scott Wesley Guy Harrison […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: My First Auto Index (Absolute Beginners)

Richard Foote - Mon, 2019-08-19 04:21
I am SOOOO struggling with this nightmare block editor but here goes. Please excuse any formatting issues below: I thought it was time to show the new Oracle 19c Automatic Indexing feature in action and what better way than to go through how I created my first ever Automatic Index. To start, I create a […]
Categories: DBA Blogs

Basic Replication -- 1 : Introduction

Hemant K Chitale - Thu, 2019-08-15 23:24
Basic Replication, starting with Read Only Snapshots has been available in Oracle since  V7.   This was doable with the "CREATE SNAPSHOT" command.

In 8i, the term was changed from "Snapshot" to "Materialized View"  and the "CREATE MATERIALIZED VIEW" command was introduced, while "CREATE SNAPSHOT" was still supported.

Just as CREATE SNAPSHOT is still available in 19c,  DBMS_SNAPSHOT.REFRESH is also available.

Not that I recommend that you use CREATE SNAPSHOT and DBMS_SNAPSHOT anymore.  DBAs and Developers should have been using CREATE MATERIALIZED VIEW and DBMS_REFRESH since 8i.

In the next few blog posts (this will be a very short series) I will explore Basic Replication.  Let me know if you want to see it in 11.2 and 12c as well.

Categories: DBA Blogs

2019 Public Appearances (What In The World)

Richard Foote - Wed, 2019-08-14 21:29
I’ll be presenting at a number of Oracle events over the remainder of the year. Details as follows: Oracle Open World – San Francisco (16-19 September 2019)
Categories: DBA Blogs

Find Docker Container IP Address?

DBASolved - Wed, 2019-08-14 11:38

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

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

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

The syntax is as follows:

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




Categories: DBA Blogs

Identifying the TNS_ADMIN for an deployment in GoldenGate Microservices

DBASolved - Wed, 2019-08-14 10:30

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

export TNS_ADMIN=${ORACLE_HOME}/network/admin


export TNS_ADMIN=$(ORA_CLIENT_HOME}/network/admin


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

As a illistration, it would look something like this:

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


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

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

Changing TNS_ADMIN

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

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

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

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


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

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


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

curl -X PATCH \
  <a href="https://<ip_address>/services/v2/deployments/alpha" target="_blank" rel="noopener">https://<ip_address>/services/v2/deployments/alpha</a> \
  -H 'cache-control: no-cache' \
  -d '{


Categories: DBA Blogs

Rolling out patched 19c home using gold image

Bobby Durrett's DBA Blog - Thu, 2019-08-08 18:49

For Oracle versions 11.2 through 18 I have been applying quarterly patches to a test database and then gathering the Oracle home into a tar file. When we want to roll out a new database server with the latest patches we untar the file and clone the Oracle home with a command like this:

$ORACLE_HOME/oui/bin/runInstaller -clone -silent ...

This command no longer works for 19c so now I am using a new command like this:

$ORACLE_HOME/runInstaller -silent -responseFile ...

Before running this command I had to unzip my gold image zip file into $ORACLE_HOME. I created the gold image zip file using a command like this:

./runInstaller -createGoldImage -destinationLocation ...

I ran this through MobaXterm to use their X server. I created the response file when I initially installed 19c on this test server. Then I patched the Oracle home with the July 2019 PSU and finally ran the above command to create the gold image.

Some useful links that I ran into:

Franck Pachot’s post about doing a silent 18c install using the new runInstaller

Oracle support document that says the old Oracle home cloning does not work in 19c:

19.x:Clone.pl script is deprecated and how to clone using gold-image (Doc ID 2565006.1)

Oracle documentation about installing silent with response file

DBA Stackexchange post about how you have to use a response file because the command line options don’t work

This is kind of terse but it has some of the key information. I may update it later if I can.


Categories: DBA Blogs

2million PageViews

Hemant K Chitale - Wed, 2019-08-07 23:53

This blog has now achieved 2million PageViews :

(The "drop" at the end is the count for only the first week of August 2019).

Although this blog began in December 2006, the PageViews counts start with 8,176 in July 2010.  So, effectively, this blog has had 2million PageViews in 9years.

The first 1million PageViews were achieved in March 2015.

Unfortunately, the rate at which I have been publishing has declined since 2017 (36 posts in 2017, 30 in 2018 and only 8 so far this year).  I apologise for this.  Hopefully, I should be able to add more posts in the coming months.

Categories: DBA Blogs

Cool Roblox Usernames

VitalSoftTech - Tue, 2019-08-06 02:02

Are you about to make a new Roblox account to play with your friends and are in a bind for a cool username? We’ll help you there. Roblox is an interactive, imaginative, online game that you can play with your friends. It is one of the largest online gaming platforms, with over 15 million games […]

The post Cool Roblox Usernames appeared first on VitalSoftTech.

Categories: DBA Blogs

AdminClient and Set Commands

DBASolved - Fri, 2019-08-02 13:32

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

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

Features that make this tool so nice:

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

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

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

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

Oracle GoldenGate Administration Client for Oracle
Version 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> show

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

OGG (not connected) 2>


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

OGG (not connected) 2> set color on

OGG (not connected) 3> show

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

OGG (not connected) 4>


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

export ADMINCLIENT_COLOR=<value>

Note: The <value> is case sensitive.

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


export OGG_VAR_HOME=/tmp



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

[oracle@ogg19c scripts]$ sh ./adminclient.sh
Oracle GoldenGate Administration Client for Oracle
Version 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> show

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

OGG (not connected) 2>



Categories: DBA Blogs

ServiceManager … Manually start/stop

DBASolved - Tue, 2019-07-30 09:58

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

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

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

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

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

  • startSM.sh
  • stopSM.sh

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


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

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


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

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

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

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

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

In order to stop the ServiceManager manually:

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


Categories: DBA Blogs

How to add cells in a Column on Excel

VitalSoftTech - Tue, 2019-07-30 09:53

Are you just starting to learn Excel? Are all these boxes confusing you? Adding is the most primary and straightforward function of Excel. Here is a simple and easy guide to help you with different methods of finding the sum of cells in columns. So, here we go! How to add in a Column on […]

The post How to add cells in a Column on Excel appeared first on VitalSoftTech.

Categories: DBA Blogs

Announcement: “Oracle Performance Diagnostics and Tuning” Webinar – 20-23 August 2019.

Richard Foote - Tue, 2019-07-30 06:05
I have just scheduled a new “Oracle Performance Diagnostics and Tuning” webinar to run between 20-23 August 2019, specifically for my friends in New Zealand, but is open to anyone. It will run between 7am-11am AEST each day, which is perfect for those in NZ, but also other parts of the world such as Western […]
Categories: 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


Subscribe to Oracle FAQ aggregator - DBA Blogs