Subscriber Count




Contact for Online learnings
* indicates required field
Thanks for the inquiry. You will be receiving a mail shortly with course details. If not please check the spam folder once and mark our mail as not a spam.

ANZ Webinar Tour – 2017 – Registrations Open

Hello All,

IAOUG and APACOUC proudly announces ANZ Webinar Tour - 2017.

The 2017 Webinar Tour will be running from July 3rd until August 25th with the participation of over 20 International Speakers that will be presenting over 50 interesting presentations, making it the largest webinar series ever made in the Oracle world!

Registration Links are open for 1st wave. Please find more information here.

I will be presenting the following topics, register for free , 13- days to go for first one.

  1. July 5, 2017 - 19:00 AEST - Suresh Gandhi presenting: Oracle Sharding - More info or register Here
  2. July 28, 2017 - 19:00 AEST - Suresh Gandhi presenting: DevOps for Databases  - Registration Not open yet
  3. Aug 5, 2017 - 19:00 AEST - Suresh Gandhi presenting: Building Datalake - Registration Not open yet


Geek DBA

Oracle Sharding : Part 2 – Installating & Configuring Shards

In Previous post of the series, I have written about an overview of Oracle Sharding and its features.

In this post, you will be seeing how to configure oracle shard and what steps are need to be performed.

Over all, I felt its very easy to setup and configurewhen compare to RAC setup. All you need to install the oracle home software in all nodes and gsm installation in shard catalog node and rest all are simple commands. One of the excited part is deploy , which creates databases in shards automatically and get shard database ready.

You can also download the presentation from Here

Continue reading Oracle Sharding : Part 2 – Installating & Configuring Shards

Oracle Sharding: Part 1 – Overview

 Oracle is releasing a whistle blowing feature in distributed databases (shared nothing architecture) which has been dominated by many other databases in recent years. In upcoming release Oracle 12.2 , the Oracle Sharding feature provides the exact capability of shared nothing architecture with Leader node and shard nodes to distribute the data to nodes and scale upto 1000 shards and without compromising on high availability and relational database properties.  

For all other distributed databases in the market, consistency is the main issue , none of them  or couple of them provides full consistency due to non implementation of MVCC (undo) in their databases and  users has to compromise on data consistency or infact set the consistency level to full but on the other hand loosing performance.  But Oracle with its sharding it provide full consistency and also relational schema support.

Oracle Sharding features is rich combination of Connection Pools, ONS, Sharding software (GSM), Partitioning, and Powerful Oracle Database. It is fully ACID complaint as like other RDBMS infact this can be major break through.

The following are the supportable features in Oracle Sharding

  • Relational schemas
  • Database partitioning
  • ACID properties and read consistency (very rich feature when compare to other databases)
  • SQL and other programmatic interfaces
  • Complex data types
  • Online schema changes
  • Multi-core scalability
  • Advanced security
  • Compression
  • High Availability features
  • Enterprise-scale backup and recovery

The main components of Oracle Sharding are,

  • Sharded database (SDB) – a single logical Oracle Database that is horizontally partitioned across a pool of physical Oracle Databases (shards) that share no hardware or software, the schema of this database is partitioned in other database (different hosts)
  • Shards - independent physical Oracle databases that host a subset of the sharded databas SDB (schema)
  • Global service  - database services that provide access to data in an SDB , implementation of general service to a distributed service.
  • Shard catalog – an Oracle Database that supports automated shard deployment, centralized management of a sharded database, and multi-shard queries, as like leader node, config instance in mongodb. 
  • Shard directors – network listeners that enable high performance connection routing based on a sharding key, its like mongos instance for instance and holds the key information which stored in shard catalog.
  • Connection pools - at runtime, act as shard directors by routing database requests across pooled connections
  •  Management interfaces - GDSCTL (command-line utility) and Oracle Enterprise Manager (GUI) to manage shards

What you need to consider before implementing sharding

  • Licensing for Sharding/Partitioning

  • Application suitability, in general OLTP applications suits best with regional data distributed to single node and access through that node. 

  • Design of relational schema/table, especially the data distribution key as like other databases.

  • Its not a RAC (shared everything) architecture, its distributed database (shard/partitioning shared nothing)

How the data is distributed?

  • When a table is created with type sharded table one of the column need to specified as distribution key , this is common in any distributed databases

  • The distribution key can be of type consistent,hash,list.

  • The distribution metadata is stored in shard catalog called gsmcatalog

  • The table now created in multiple shards aka databases with partitions evenly distributed to each shard.

Data Access flow

  • A service called GSM global service will be created in shard catalog database (sdb) with type and its region affinity etc using gsdctl

  • This service will be used by user/application

  • When user fire a query, The service will connect to shard catalog and get the metadata of distribution and shard directors reroutes the connection to specific nodes or all nodes.

  • The shard catalog acts as leader node/config node.

General Architecture of Oracle Sharding with two shards and one shard catalog database.

Image title

General Software Requirements of Oracle Sharding

  • Oracle Database 12c Release 2 , Non Container Databases

  • Oracle 12c Global Service Manager (Seperate Oracle Home)

  • Oracle Non container databases for shard catalog (SDB) database

Creating a sample Oracle Shard Table.



( cust_id     NUMBER NOT NULL

, name        VARCHAR2(50)

, address     VARCHAR2(250)

, region      VARCHAR2(20)

, class       VARCHAR2(3)

, signup      DATE







  • When the session has shard ddl enabled,
  • The DDL commands will be fired across the shard nodes through database links created in sdb database
  • A tablespace ts1 will be created in shardcatalog and shard nodes
  • Paritions of table will be created in shard catalog (the first metadata) and shard nodes (exact partitions) distributed evenly.

In next post, I  will be showing how to Install and configure the Oracle Sharding and explore more about this exciting feature.

Geek DBA

Rows in Sort order without order by clause?

I have been asked by one of colleague (Vasu) about a weird issue, that the query returns rows in sort order  in one database not in the other database.

Without having much hassle, by running dbms_xplan.display_cursor('sql_id',NULL,'ADVANCED) mode tell's us the optimizer parameter "_gby_hash_aggregation_enabled" is having different values in each database. Which is mean to perform the sort group by or hash group by operation.

To simulate the issue, I just did the following, a quick check on v$session row_wait_obj# column with parameter false/true.

The result show rows in non sorted for true, (means performed a hash group by operation) where in with false the rows are in sorted order (sort group by has been performed)




Oracle 12.2 New Features : Convert Non Partitioned Table to Partitioned Table using ALTER

In earlier versions of Oracle, when one want to convert a table to partition, one must use move or export/import method and rename it.

Now in 12.2 release we can use ALTER table command to convert the table into Partitioned Table. Here is sample command it is from the documentation excerpt,






-Geek DBA

12.2 New Features : Memory Management at PDB Level

Memory & Guaranteed BufferCache/Shared Pool can be allocated at PDB Level. From the white paper here it is









Well so far I haven't seen any such need to keep a separate memory settings for each PDB as we have a consolidated databases of same size/resource usage sitting in one single container. But a good feature just in case if one PDB requires much SGA than others this can help.


Geek DBA

Oracle 12.2 New Features : Local UNDO and Flashback PDB Database


You can now flashback PDB database exclusively with Local UNDO enabled. In earlier versions, Oracle shares the undo tablespace for CDB and PDB's and creates a common view for transaction and instance recovery done at all CDB and PDB level. With local undo , Oracle has to change this a bit, it has to either maintain records of master cdb and pdb in shared undo and also in Local UNDO for pdb level. I will have to further dig into, but for now this feature is available and flashback is possible.

To do this, Enable Local UNDO on PDB level and restart the database. It will create a undo tablespace automatically and PDB start using it.


SQL> shutdown immediate

SQL> startup upgrade

SQL> alter database local undo on;

SQL> shutdown immediate

SQL> alter pluggable database all open;

SQL> select name,con_id from v$tablespace where name like '%UNDO%' ;

NAME         CON_ID

---------- -------------

UNDOTBS1        1  -- >Root

UNDO_1          2  -- >PDBSEED

UNDO_1          3  -- >PDB1


Do a flashback,

SQL> flashback pluggable database pdb1 to timestamp systimestamp - interval '1' hour;

SQL> alter pluggable database pdb1 open resetlogs;



Oracle 12.2 New Features : SQLPLUS Enhancements

In 12.2 release, one of the coolest things that every DBA would love is to get history in SQLPLUS :).

Now you can have history ON/OFF with SQLPLUS and see history



Some other features like, FAST OPTION  -F flag to set the ARRAYSIZE PAGESIZE STATEMENTCACHE all at once.

Cool one is HISTORY feature.



Geek DBA

Oracle 12.2 New Features : Oracle Sharding on its Way

DBA's , Oracle is coming with Sharding.  The one killer feature that No-SQL databases claiming distributed processing with sharding aka a non-shared database storage.

Now with release 12.2 Oracle releasing Sharding feature, with new command "Create Sharded Table" and with a catalog schema, well hold on second , its basically distributed partitioning relied on partitioning feature and standby and you need a license for it :). Oh No common...

I really want to dirt my hands on this feature hearing of many new databases from past few years, and I want to tell "Hey Oracle supports this too" 🙂

From the internet sources and OOW presentations, what I understood from Oracle Sharding , Looks complex to me not a simple as like other databases.

  •  uses dbca for creating shards intially
  •  use a new catalog called GSM (global service manager a features introduced in 11.2)
  •  and catalog contains sharded nodes and key information(mongo config instance)
  •  can be created by using "Create Sharded Table" command
  •  uses consistent hashing with either Traditional hashing/Linear Hashing (even) 
  •  is based on Distributed Partitioning (licensing)
  •  needs standby databases (active dataguard and its licences)
  •  uses set of tablespaces for each shard
  •  uses dblinks for getting data from each shard
  •  use GSM service and clients need to use this service for their connection (like mongos )
  •  uses listener to redirect the connection to right shard for your data (ex: mongo router instance)

What best with this feature is , Unlike Many NoSQL featurs lack of capability having RDBMS and ACID complaince with distributed processing capabilities , whilst if Oracle can keep this sharding simple then this can be a whistle blower to all other new databases.


Geek DBA

Oracle 12.2 New Features : Long Identifiers – What a relief

Oracle 12.2. is out and in Cloud first. 🙂

Out of all features one future must aware and important thing is long identifiers limitation relief , earlier any table or index cannot exceed more than 30 character length.

Not sure how many of you have face issues with character limit, but I was, and many times encountered this limitation as an obstacle to push Oracle as the database for some projects. And I have suggested alternate approaches always to developers and they used to refer some other database allow it why not Oracle. 🙂

Especially when you use Salesforce as your CRM , it allowed more than 30 characters as name length for an object (ofcourse the underlying database for Salesforce is oracle only but they have their own application layer tables like apex ) and having a Datawarehouse or data lake by pulling the data from salesforce and from your application and creating a BI reports and dimensions is difficult in such cases.

So well, now back to post , now Oracle allows more than 30 characters for a name of table/index etc.

As 12.2. its in beta and only released in cloud so far, Here is the screenshot