All about Database Administration, Tips & Tricks

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

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.

SQL> ALTER SESSION ENABLE SHARD DDL;

SQL> CREATE SHARDED TABLE customers

( cust_id NUMBER NOT NULL

, name VARCHAR2(50)

, address VARCHAR2(250)

, region VARCHAR2(20)

, class VARCHAR2(3)

, signup DATE

CONSTRAINT cust_pk PRIMARY KEY(cust_id)

)

PARTITION BY CONSISTENT HASH (cust_id)

TABLESPACE SET ts1

PARTITIONS AUTO

;

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.

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

-Thanks

Suresh

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,

ALTER TABLE test_table MODIFY

PARTITION BY RANGE (amount) INTERVAL (100)

(PARTITION P1 VALUES LESS THAN (500), PARTITION P2 VALUES LESS THAN (1000),

ONLINE

UPDATE INDEXES (IDX01_AMOUNT LOCAL (PARTITION IDX_P1 VALUES LESS THAN (MAXVALUE)));

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

-Thanks

Geek DBA

Oracle 12.2 New Features : Local UNDO and Flashback PDB Database

Hi,

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.

Steps

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;

-Thanks

GEEK DBA

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

SET HISTORY ON|OFF

SHOW HISTORY

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

Cool one is HISTORY feature.

-Thanks

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.

-Thanks

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

MongoDB for Oracle DBA’s Part 11 : Enabling Preferred Read on Standby (Secondary) Instances

As we know in ASM we can specificy the preferred read fail group to scatter the reads across the failgroups to reduce the read contention on single disk group. Similarly in MongoDB you can set to read the data from secondary servers i.e standby, In other words take this as active dataguard feature where you can run queries and backups on standby.

Here is how to do it.

### Check the Lag of the Standby or secondaries

MongoDB Enterprise rs0:SECONDARY> rs.printSlaveReplicationInfo()

source: localhost:47018

syncedTo: Mon Dec 12 2016 09:15:35 GMT+1100 (AEDT)

0 secs (0 hrs) behind the primary

source: localhost:47020

syncedTo: Mon Dec 12 2016 09:15:35 GMT+1100 (AEDT)

0 secs (0 hrs) behind the primary

MongoDB Enterprise rs0:SECONDARY>

### Connect to any secondary host and run the query, eventually it throw errors

mongo localhost:47018

MongoDB Enterprise rs0:SECONDARY> use foo

switched to db foo

MongoDB Enterprise rs0:SECONDARY> db.testData.find()

Error: error: { "$err" : "not master and slaveOk=false", "code" : 13435 }

MongoDB Enterprise rs0:SECONDARY>

Error out as its not master

### Enable the reads on slave (remember you need to do every time when you connect

MongoDB Enterprise rs0:SECONDARY> rs.slaveOk()

MongoDB Enterprise rs0:SECONDARY> use foo

switched to db foo

MongoDB Enterprise rs0:SECONDARY> db.testData.find()

{ "_id" : ObjectId("5835286da372386306caeee2"), "x" : 1 }

{ "_id" : ObjectId("5835286da372386306caeee3"), "x" : 2 }

{ "_id" : ObjectId("5835286da372386306caeee4"), "x" : 3 }

{ "_id" : ObjectId("5835286da372386306caeee5"), "x" : 4 }

{ "_id" : ObjectId("5835286da372386306caeee6"), "x" : 5 }

{ "_id" : ObjectId("5835286da372386306caeee7"), "x" : 6 }

{ "_id" : ObjectId("5835286da372386306caeee8"), "x" : 7 }

{ "_id" : ObjectId("5835286da372386306caeee9"), "x" : 8 }

{ "_id" : ObjectId("5835286da372386306caeeea"), "x" : 9 }

{ "_id" : ObjectId("5835286da372386306caeeeb"), "x" : 10 }

{ "_id" : ObjectId("5835286da372386306caeeec"), "x" : 11 }

{ "_id" : ObjectId("5835286da372386306caeeed"), "x" : 12 }

{ "_id" : ObjectId("5835286da372386306caeeee"), "x" : 13 }

{ "_id" : ObjectId("5835286da372386306caeeef"), "x" : 14 }

{ "_id" : ObjectId("5835286da372386306caeef0"), "x" : 15 }

{ "_id" : ObjectId("5835286da372386306caeef1"), "x" : 16 }

{ "_id" : ObjectId("5835286da372386306caeef2"), "x" : 17 }

{ "_id" : ObjectId("5835286da372386306caeef3"), "x" : 18 }

{ "_id" : ObjectId("5835286da372386306caeef4"), "x" : 19 }

{ "_id" : ObjectId("5835286da372386306caeef6"), "x" : 21 }

Type "it" for more

MongoDB Enterprise rs0:SECONDARY>

-Thanks

Geek DBA

MongoDB for Oracle DBA’s Part 10 : Switchover & Role Transition between Primary and Secondary

In MongoDB as like Oracle Dataguard switchover is possible to but in different way.

You can ask Primary to stepdown and let nodes elect a new primary in replicaset. This can be done using rs.stepDown() method.

Before stepping down primary, one must know the below

The procedure blocks all writes to primary while it runs
Terminates all sessions and running jobs, index rebuilts etc
Disconnects all connections
If the wait period to elect a new primary exceeds then primary will not step down.

Let's take a look of the same,

I have opened three sessions and in one sessions querying a table which is running (the right top most one in screenshot)
In another session tail the log file so can understand what happens when a step down is initiated
In the session that left side , initiated the stepdown of primary , immediately disconnects the current session and also the session that running a query on the database (top left side)
In the log it shows the primary is transitioning to secondary
as per rs.status() the 47018 (original primary) becomes secondary.

Screenshot

All about Database Administration, Tips & Tricks

New Features for DBA’s

Subscribe to Posts by Email

Subscriber Count

Disclaimer

Recent Posts

Categories

Archives

Pages

Oracle Sharding: Part 1 – Overview

Rows in Sort order without order by clause?

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

12.2 New Features : Memory Management at PDB Level

Oracle 12.2 New Features : Local UNDO and Flashback PDB Database

Oracle 12.2 New Features : SQLPLUS Enhancements

Oracle 12.2 New Features : Oracle Sharding on its Way

Oracle 12.2 New Features : Long Identifiers – What a relief

MongoDB for Oracle DBA’s Part 11 : Enabling Preferred Read on Standby (Secondary) Instances

MongoDB for Oracle DBA’s Part 10 : Switchover & Role Transition between Primary and Secondary

New Features for DBA’s

Follow Me!!!

Subscribe to Posts by Email

Subscriber Count

Disclaimer

Recent Posts

Categories

Archives

Tags

Pages