OGh Tech Experience 2017 : The Journey Home

I woke up really early so I decided to head straight for the airport. It was a 20 minute bus ride to the station, then a train to the airport, or so I thought. It turned out there wasn’t a straight through train, so I had to go via Utrecht. Once at Utrecht I got on a train the the airport, but it stopped early due to work on the lines, so I had to (more...)

OGh Tech Experience 2017 : Day 2

I woke up on day 2 feeling pretty dreadful, but I lay in the bath for a while, waiting for a dose of Lemsip to kick in. By the time I got out of the bath I was feeling OK-ish.

By the time I finished the 20 minute bus ride and the short walk to the venue I wasn’t feeling so great, but the adrenalin started to kick in, which always helps. I sat for (more...)

12c DataGuard Validate and More

12c Dataguard Switchover Best Practices using DGMGRL(Dataguard Broker Command Prompt) (Doc ID 1582837.1)	

db_unique_name=hawka (primary)
db_unique_name=hawkb (standby)

Configuration - hawkdg
  Protection Mode: MaxPerformance
    hawka - Primary database
      hawkb - Physical standby database 

Validate DataGuard Configurations.

Monitorable (Read-Only) Properties

show configuration verbose

show database verbose hawka
show database verbose hawkb

validate database verbose hawka
validate database verbose hawkb

There is no need to use on database if the instance names are unique (more...)

JDBC, Linux and Entropy

Some troubles — especially those happening only sporadically — are not so easy to shoot and call for a deeper understanding of the matter. In the following real-world example this means: SQL*Net Tracing and some knowledge about the inner workings of the server’s operating system, particularly random number generation. This case was suited well to demonstrate an approach to trouble-shoot […]

Log Buffer #514: A Carnival of the Vanities for DBAs

This Log Buffer Edition encompasses few of the nifty blog posts from Oracle, SQL Server and MySQL.


Introduction to Oracle Big Data Cloud Service

Interpreting wpad.dat using jrunscript from JDK 8 to work out the right proxy.

Nice Trick to Get ADF LOV Description Text

Presentation: Dynamic Actions, Javascript, & CSS for APEX Beginners

E4 Session Developing Agnostic Data Services

SQL Server:

Setting up R in SQL 2016 – Avoiding Pitfalls

Setting (more...)

Introduction to Oracle Big Data Cloud Service (Part IV) – Zeppelin

This is my forth blog post about Oracle Big Data Cloud Service. In my previous blog posts, I showed how we can create a big data cloud service on Oracle Cloud, which services are installed by default, ambari management service and now it’s time to write about how we can work with data using Apache Zeppelin. Apache Zeppelin is a web-based notebook that enables interactive data analytics. Zeppelin is not the only way to work (more...)

Introduction to Oracle Big Data Cloud Service (Part III) – Ambari

This is the third blog post about Oracle Big Data Cloud Service. I continue to guide you about the Big Data Cloud Service and its components. In this blog post, I will introduce Ambari – the management service of our hadoop cluster.

The Apache Ambari simplifies provisioning, managing, and monitoring Apache Hadoop clusters. It’s the default management tool of Hortonworks Data Platform but it can be used independently from Hortonworks. After you create your big (more...)

Notes From Orlando

I thought y'all would appreciate some notes from last week's OHUG conference in Orlando Florida.  So, in no particular order, my observations...
  • The sessions were pretty evenly divided between Oracle E-Business, PeopleSoft and HCM Cloud.  Right around 1/3 of total sessions for each track.
  • The mix of attendees, from what I could tell, were about half HCM Cloud users and half on either EBS or PeopleSoft.  And out of those on EBS or Peoplesoft, about (more...)


An interesting observation appeared recently as a side-channel on a question on the OTN database forum – how does Oracle execute an unpivot() operation. Here’s an example of such a query:

rem     Script:         unpivot_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2017

create table t1( id, col1, col2, col3, col4, col5, padding )
pctfree 95 pctused 5
-- compress for query low
        1, 100 , 200 , 300 ,  (more...)

Introduction to Oracle Big Data Cloud Service – Compute Edition (Part II)

In my previous post, I gave a list of installed services on a Oracle Big Data Cloud Service when you select “full” as deployment profile. In this post, I’ll explain these services and software.

HDFS: HDFS is a distributed, scalable, and portable file system written in Java for Hadoop. It stores data so it is the main component of the our cluster. A Hadoop (big data) cluster has nominally a single namenode plus a cluster (more...)

Introduction to Oracle Big Data Cloud Service – Compute Edition (Part I)

Over the last few years, Oracle has dedicated to cloud computing and they are in a very tough race with its competitors. In order to stand out in this race, Oracle provides more services day by day. One of the services Oracle offers to the end user is “Oracle Big Data Cloud Service”. I examined this service by creating a trial account, and I decided to write a series of blog posts for those who (more...)

ETL using Oracle DBMS_HS_PASSTHROUGH and SQL Server

While I prefer a “loosely coupled architecture” for replication between Oracle and SQL Server, sometimes a direct (database) link cannot be avoided. By using DBMS_HS_PASSTHROUGH for data extraction the 2 other ETL processes (transformation and load) can be configured and administered with more flexibility, providing an almost acceptable level of “loosely coupled processing“.
Consider this as a really simple ETL config:

    Extract: Select SQL Server data with native sql, using (more...)

Log Buffer #513: A Carnival of the Vanities for DBAs

This Log Buffer Edition includes blog posts from Oracle, SQL Server and MySQL.


In 12.2 you can: Convert a simple table to partitioned with multi-column automatic list partitions, partially indexed, with read only segments, filtering out unwanted data, online in one operation.

12c How to UN Expire Password for CDB Users

rman auxiliary (for duplicate)

How long will Oracle APEX remain an included feature of the Oracle Database?

How to easily delete files (more...)

SNMP unresponsive on Storage Cell after Exadata Patching

After a round of Exadata patching, the Storage Cells become unresponsive to SNMP for monitoring tools like OpsViews:

check_snmp_sysinfo CRITICAL - Agent not responding, tried SNMP v1 and v2c

This is because the Storage Cells get re-imaged as part of the Exadata patching, confirmed by imagehistory:

[root@v1ex1celadm01 ~]# imagehistory
Version :
Image activation date : 2015-05-01 16:10:10 -0700
Imaging mode : fresh
Imaging status : success

Version :  (more...)


If you’re familiar with SQL Profiles and SQL Baselines you may also know about SQL Patches – a feature that allows you to construct hints that you can attach to SQL statements at run-time without changing the code. Oracle 12c Release 2 introduces a couple of important changes to this feature:

  • It’s now official – the feature had been moved from package dbms_sqldiag_internal to package dbms_sqldiag.
  • The limitation of 500 characters has been removed from (more...)


Time Since Chkpt and Lag at Chkpt from replicat keep increasing

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
REPLICAT    RUNNING     R_NEW12C    03:49:45      06:37:47    

This occurs for due to the following reasons:

Delivering a long running transaction
Waiting on a full table scan
Blocked by another sessions
Primary extract lag or pump lag keeps increasing

Is There a Way to Make Long-running Transactions Checkpoint? (Doc ID 969684.1)

The tradeoff with GROUPTRANSOPS is with  (more...)

12.2 Partitions

At the end of my presentation to the UKOUG Database SIG yesterday I summed up (most) of points I’d made with a slide making the claim:

In 12.2 you can: Convert a simple table to partitioned with multi-column automatic list partitions, partially indexed, with read only segments, filtering out unwanted data, online in one operation.


Last night I decided I ought to demonstrate the claim – so here’s a little code, first (more...)

How to easily delete files in the Oracle Cloud using CloudBerry Explorer

So you have some Oracle Cloud storage, which was probably throw in as a freebie initially by Oracle 🙂  Now your freebie is expiring and you decide you want to retain the service but now as metered, so you want to delete all the unnecessary files, which in my case are old Oracle database backups.

As you can see here I have 50k plus files using 897Gb:

Oracle Cloud Web Console Details

Here are the files listed in the Web Console:

Oracle Cloud Web Console List Objects


Announcing My Employer-Related Twitter Account

When I tweet anything about Amazon Web Services it will be on the following twitter handle:  https://twitter.com/ClossonAtWork (@ClossonAtWork).

If you’re interested in following my opinions on that twitter feed, please click and follow. Thanks.

Filed under: oracle

Log Buffer #512: A Carnival of the Vanities for DBAs

This Log Buffer Edition covers Oracle, SQL Server and MySQL.


Upgrade Existing TDE to Use New Unified Key Management in 12c Upgraded Database (non-cdb)

Instrumentation … not just for debugging

12.2 Index Deferred Invalidation (Atomica)

Collation in 12cR2 – when AA equals Å (or not?)

Configuring Oracle Traffic Director 12c with WebGate

SQL Server:

PowerShell Scripting to Replicate Cloud & SaaS Data to SQL Server

Comparing and Synchronizing Two Folders with Azure