Subscriber Count




ORA-28040: No matching authentication protocol Database driver error


Hope you might have already gone through below, just in case if not.  All of our developer tools/ETL tools datastage/Informatica etc. are failing with ora-28040 once we upgraded to oracle 12c.

This is due to, As per Oracle, In Oracle 12.1 the default value for the SQLNET.ALLOWED_LOGON_VERSION parameter has been updated to 11. This means that database clients using pre-11g JDBC thin drivers cannot authenticate to 12.1 database servers unless the SQLNET.ALLOWED_LOGON_VERSION parameter is set to the old default of 8.

Change/add the SQLNET.ALLOWED_LOGON_VERSION=8 in sqlnet.ora

Note: For the databases on AWS RDS this still works.



ANZ Free Webinar Tour – Second Wave registrations

Hello All,

The Second wave of APACOUC - IAOUG ANZ Webinars registrations open now and you have more exciting presentations to come.

And I am presenting topic Devops for Databases which details about Devops Principles to databases, why they are hard to implement, creating your own framework, strategy, approach, tools that needed for each approach etc.

July 28, 2017 - 19:00 AEST - Suresh Gandhi presenting: DevOps for Databases  - More info or register Here

Registration links:-


  • July 17, 2017 - 17:00 AEST - Gurcan Orhan presenting: Migration steps from OWB to ODI - More info or register Here
  • July 19, 2017 - 10:00 AEST - Dan McGhan presenting: Techniques for Taming the Asynchronous Nature of Node.js - More info or register Here
  • July 19, 2017 - 19:00 AEST - Steven Feuerstein presenting: Change Impact Analysis with 12.2 PL/Scope - More info or register Here
  • July 21, 2017 - 10:00 AEST - Lino Schildenfeld presenting: Thinking of going APEX 5.1? - our experience - More info or register Here
  • July 24, 2017 - 10:00 AEST - Satyendra Pasalapudi presenting: Running Highly Available E Business Suite on Oracle Bare Metal Cloud Service  - More info or register Here
  • July 24, 2017 - 17:00 AEST - Heli Helskyaho  presenting: Top 10 Features of Oracle SQL Developer everybody should use, even in the Cloud  - More info or register Here
  • July 26, 2017 - 10:00 AEST - Tim Gorman presenting: Accelerating Development and Testing Using Data Virtualization  - More info or register Here
  • July 26, 2017 - 19:00 AEST - Biju Thomas presenting: Do I have to learn new tricks to manage Oracle database in the Cloud?  - More info or register Here
  • July 28, 2017 - 10:00 AEST - Kai Yu presenting: Get ready to upgrade your Oracle databases to 12cR2: tools, methods and paths  - More info or register Here
  • July 28, 2017 - 19:00 AEST - Suresh Gandhi presenting: DevOps for Databases  - More info or register Here
  • July 31, 2017 - 10:00 AEST - Charles Kim presenting: Backup to the Cloud and Beyond  - More info or register Here
  • August 2, 2017 - 10:00 AEST - Dan McGhan presenting: Building Real-time Data in Web Applications with Node.js  - More info or register Here
  • August 2, 2017 - 19:00 AEST - Joel Perez presenting: RMAN one of the best tools to move on-premise databases to the Cloud  - More info or register Here
  • August 4, 2017 - 10:00 AEST - Daniel Morgan presenting: Oracle Database 12.2 Application Containers  - More info or register Here
  • August 4, 2017 - 17:00 AEST - Gurcan Orhan presenting: EDQ, OGG and ODI over Exadata in the Cloud for Perfection  - More info or register Here
  • August 7, 2017 - 10:00 AEST - Tim Gorman presenting: Securing Data Using Data Obfuscation  - More info or register Here
  • August 7, 2017 - 17:00 AEST - Heli Helskyaho  presenting: Introduction to the Big Data World  - More info or register Here
  • August 7, 2017 - 19:00 AEST - Francisco M Alvarez presenting: Logging or NoLogging:That's the question  - More info or register Here




Oracle Sharding – Updated Presentation & Webinar Recording

Hello All,

Thanks for joining and attached the updated presentation and webinar recording link.

Note: Please skip the first 10 Mins as the presentation is not shared properly. :).

Find other Recorded Webinars at

Updated Presentation: Click Here


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 18, 2017 - 19:00 AEST - Suresh Gandhi presenting: Building Datalake - Registration Not open yet

And Full List of First wave is here

  • July 3, 2017 - 19:00 AEST - Connor McDonald presenting: 12 things about 12c Release 2 - More info or register Here
  • July 5, 2017 - 10:00 AEST - Tim Gorman presenting: Securing Data Using Data Obfuscation - More info or register Here
  • July 5, 2017 - 19:00 AEST - Suresh Gandhi presenting: Oracle Sharding - More info or register Here
  • July 7, 2017 - 10:00 AEST - Kai Yu presenting: Under the hood of Oracle Database Cloud Service for Oracle DBAs - More info or register Here
  • July 7, 2017 - 19:00 AEST - Lucas Jellema presenting: The Art of Intelligence – A Practical Introduction Machine Learning for Oracle professionals - More info or register Here
  • July 10, 2017 - 10:00 AEST - Charles Kim presenting: Bulletproof Your Data Guard Environment - More info or register Here
  • July 10, 2017 - 17:00 AEST - Gurcan Orhan presenting: Is Data Warehousing dying? - More info or register Here
  • July 12, 2017 - 10:00 AEST - Satyendra Pasalapudi presenting: Experience of being a Oracle Bare Metal Cloud DBA - More info or register Here
  • July 12, 2017 - 19:00 AEST - Joel Perez presenting: What I must know to be expert managing Databases in the Cloud ? - More info or register Here
  • July 14, 2017 - 19:00 AEST - Debra Lilley presenting: EBS to Cloud Applications – A Govt Case Study - More info or register Here
  • July 17, 2017 - 10:00 AEST - Arup Nanda presenting: Docker and Oracle Database in the Cloud - More info or register Here



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;