OGh Tech Experience 2017 – recap

On June 15th and 16th 2017 the very first OGh Tech Experience was held. This 2-day conference was a new combination of the DBA Days and Fusion Middleware Tech Experience that were held in previous years. To summarize: OGh hit bullseye. It was two days packed with excellent in-depth technical sessions, good customer experiences and great networking opportunities.

The venue was well chosen. De Rijtuigenloods in Amersfoort is a former maintenance building of the Dutch (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...)


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

#GoldenGate Manager Access Rules

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 (more...)

12c How to UN Expire Password for CDB Users

Use dbms_metadata.get_ddl to extract user and replace create with alter.

oracle@medintdbl01 ~ $ sqlplus / as sysdba

SQL*Plus: Release Production on Sat Jun 10 15:31:13 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select username, expiry_date, account_status from  (more...)

GoldenGate Debugging

I was working on automating debug information to submit to Oracle Support and thought I share implementation for what was requested.

OGG_GROUP_NAME is from ggsci info all (case sensitive)
./debug_gg.sh: line 3: 1: —> USAGE: /debug_gg.sh OGG_GROUP_NAME
./debug_gg.sh E_LAX

#!/bin/sh -e
DN=`dirname $0`
BN=`basename $0`
set -x
ps -o pid,uname,cmd `pgrep -f "extract.*${OGG_GROUP_NAME}"`
pstack `pgrep -f "extract.*${OGG_GROUP_NAME}"`
pstack `pgrep -f "extract.*${OGG_GROUP_NAME}"`
pstack `pgrep (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...)

Oracle SOA Suite: Want performance? Don’t log so much and clean up your database!

The Oracle SOA Suite infrastructure, especially composites, use the database intensively. Not only are the process definitions stored in the database, also a lot of audit information gets written there. The SOA infrastructure database, if not well managed, will grow and will eventually have detrimental effects on performance. In this blog post I will give some quick suggestions that will help you increase performance of your SOA Suite infrastructure on the database side by executing (more...)

Index compression–quick tip

If you’re appropriately licensed and want to use advanced index compression, you can take advantage of the “db_index_compression_inheritance” parameter which will let you establish compression as a default at a nominated level in the database.  From the docs:



Indexes will be created with ADVANCED LOW compression if the tablespace they reside in has OLTP compression enabled as the default.


If the underlying table is OLTP compressed, the indexes on this table (more...)

Histogram Upgrade – 2

While reading a blog post by Maria Colgan a couple of week ago I came across an observation about histograms that I’d not noticed before; worse still, it was a feature that seemed to make some “damage-limitation” advice I’d been giving for years a really bad idea! The threat appeared in these paragraphs:

Setting SIZE REPEAT ensures a histogram will only be created for any column that already has one. If the table is a (more...)

How to install Oracle Database Cloud Backup Module

I had the joy of using the Oracle Database Cloud backup module a month ago. Needless to say that things changed since last year and some of the blogs posts I found were not relative anymore.

I used the database cloud backup module to take a backup of a local database and restore it in the cloud.

A good start would be this link to the documentation.

The installation is really simple, download the installation (more...)


Headline – if you don’t want to read the note – the /*+ parallel(N) */ hint doesn’t mean a query will use parallel execution, even if there are enough parallel execution server processes to make it possible. The parallel(N) hint tells the optimizer to consider the cost of using parallel execution for each path that it examines, but ultimately the optimizer will still take the lowest cost path (bar the odd few special cases) and (more...)

255 Again!

There’s so many things that can go wrong when you start using tables with more than 255 columns – here’s one I discovered partly because I was thinking about a client requirement, partly because I had a vague memory of a change in behaviour in 12c and Stefan Koehler pointed me to a blog note by Sayan Malakshinov when I asked the Oak Table if anyone remembered seeing the relevant note. Enough of the roundabout (more...)

NOLOGGING Bad For Recovery

What doesn’t kill you makes you stronger.
I have gone through NOLOGGING hell and not sure if I am out yet.
Disk was lost from server and database was recovered.

Oracle Standard Edition (SE2) for Microsoft Windows x64 (64-bit).

RMAN> restore database validate preview;

  List of Archived Logs in backup set 4098
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    15209   134553557  26-APR-17 134574622  26-APR-17
  1     (more...)

DBFS and XAG for Goldengate P5

$ $GRID_HOME/crs/script/mount-dbfs.sh version

$ grep ^VERSION $GRID_HOME/crs/script/mount-dbfs.sh 

The following is an option for customers that have removed the dos2unix tool:
1. vi /tmp/mount-dbfs.sh
2. :set ff=unix
3. :wq
4. repeat for /tmp/mount-dbfs.conf

DBFS and XAG for Goldengate P4

I encountered a situation where I was not able to start dbfs_mount to save my life.

2 people spent over 5 hours and was not able to resolve the issue.

1 person suggested to to restart clusterware because it looks like fuse group was added to oracle after oracle clusterware was up.

During configuration:
kernel-devel was missing, /etc/rc.modules did not exists, oracle was not part of fuse group

# rpm -qa |egrep 'fuse|kernel-devel'
#  (more...)

DBFS and XAG for Goldengate P3

Start Pump Extract at source failed as shown below.

INFO OGG-00993 Oracle GoldenGate Capture for Oracle, p_test.prm: EXTRACT P_TEST started.
ERROR OGG-01224 Oracle GoldenGate Capture for Oracle, p_test.prm: TCP/IP error 111 (Connection refused), endpoint: ggvip_hawk.local:7809.
ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, p_test.prm: PROCESS ABENDING.

Hypothesis is ports are not opened from source to target and let’s verify configuration.

VIP name and address from DNS.

Name:    ggvip_hawk.local
Address: 10.10. (more...)

Setting up Oracle Database on Docker

A couple of days ago it was announced that several Oracle images were available on the Docker Store.

This is by far the easiest Oracle Database install I have every done !

You simply have no excuse now for not installing and using an Oracle Database. Just go and do it now!

The following steps outlines what I did you get an Oracle 12.1c Database.

1. Download and Install Docker

There isn't much to say (more...)

Intra-block row chaining optimization in 12.2

I’ve wrote in previous post

4. Next row piece can be stored in the same block only with inserts. When you run update, oracle will place new row piece into another block.

But it’s not valid anymore 🙂 Since 12.2 Oracle optimizes updates too.
You can check it on 12.2 and previous version using example 4 from previous post:

Test 4

drop table test purge;
set serverout on
alter session set tracefile_identifier='test4';