Author Archives: Nagesh Battula

About Nagesh Battula

Nagesh Battula is a Senior Principal Product Manager and is a member of the Oracle High Availability Product Management team with special focus on distributed database architecture addressing scalability and high availability. He is the product manager for Oracle Sharding and Oracle Global Data Services. Nagesh has 20+ years of combined experience in database software architecture, design, development and product management. He has a BS in Engineering and MS in Computer Science. He is a frequent speaker at OOW and various Oracle user groups and conferences.

How to use Data Pump to import data into the shards of a Sharded Database (SDB)?

Data Pump Import (impdp)  has a new capability starting from Oracle Database 12.2 to filter appropriate records for loading into individual shards of an SDB. It supports all methods of Sharding – namely System Managed, Composite and User-defined Sharding.

If you have a stand-alone database, then you need to export the table data using the data_options=group_partition_table_data. This unloads all table data at once instead of unloading each table partition independently.

Here is an example that walks you through the steps:

On the Source Database:

SQL>grant DATAPUMP_EXP_FULL_DATABASE to  testuser1;

SQL>create or replace directory def_dir1 as ‘/tmp’

SQL>grant read, write on directory def_dir1 to testuser1;

On the non-sharded database, export the table data using “data_options=group_partition_table_data” as shown below. This unloads all table data at once instead of doing each table partition independently. Data Pump exports all data into the dump file.

$ expdp testuser1/testuser1@src_db                   \

directory=def_dir1                                         \

schemas=testuser1                                         \

dumpfile=no_part%u                                     \

logfile=tkdpshrd_exp.log                              \

data_options=group_partition_table_data \

parallel=4

 

Pre-reqs (on the Target Sharded Database)

On the Shard Catalog

Create the Data Pump directory objects (on shardcat and all shards) and grant appropriate role to the user as shown below.

SQL>alter session enable shard ddl

SQL>create or replace directory def_dir1 as ‘/tmp

SQL>grant read, write on directory def_dir1 TO <user>;

SQL>grant DATAPUMP_IMP_FULL_DATABASE  to  <user>;

<create the sharded tables that match the structure of the source tables>

<Create other schema objects>

On each of the shards:

Execute the import specifying CONTENT=DATA_ONLY.  Because the tables already exist in the shard, attempts to recreate them will fail. The Data Pump Import detects that you are importing into a shard and only load rows that belong to that shard.  You need to run the import command on each shard.

impdp testuser1/testuser1@shard1          \

directory=def_dir1                                 \

dumpfile=no_part%u                             \

logfile=tkdpshrd_imp_01.log                \

table_exists_action=append                 \

content=data_only                                \

parallel=4

At the time of import, the Data Pump filters out rows that do not belong in the given shard. Data Pump passes key values for each row to a internal GSM function that tells  whether the row belongs in that shard.

 

Oracle GDS Licensing

Oracle Global Data Services (GDS) is a feature of Oracle Database 12c that provides connect-time and run-time load balancing, region affinity, replication lag tolerance based workload routing, and enables inter-database service failover across a set of replicas.

Here are the details of how Oracle GDS is licensed.

  • Databases in a GDS configuration:
    • Must be Database Enterprise Edition (EE) +  Licensed for Oracle Active Data Guard Option and/or Oracle GoldenGate
  • GSM Software
    • No separate license is required
  • GDS Catalog Database
    • No Database EE license is required, if this is a schema in an existing repository (e.g. Oracle Enterprise Manager) database
    • No Database EE license is required (same as Oracle RMAN/EM repository license), if hosted as a separate single instance database
    • If Oracle RAC / Data Guard is used, Oracle RAC Option and Database EE license (for the extra nodes and/or standby) is required

For more information on GDS, do visit OTN GDS portal – https://www.oracle.com/goto/gds

AskTOM Office Hours: Oracle Sharding Session (April 23rd, 2018)

Exploring Oracle Sharding:
AskTOM Office Hours offers free, open Q&A sessions with Oracle Database experts. Here is the recording of the Oracle Sharding session that I conducted on April 23rd, 2018.
Description:
Oracle Database with Sharding is a globally distributed multi-model (relational & document) cloud-native (and on-premises) DBMS. It is built on shared-nothing architecture where data is horizontally partitioned across databases that share no hardware or software. It provides linear scalability, fault isolation and geographic data distribution for shard-amenable applications. Sharding does all this while rendering strong consistency, full power of SQL, and the Oracle Database ecosystem. Listen to this recorded session to learn how you can deploy a sharded DB and elastically scale your transactions, database capacity and concurrent users.

 

Highlights:

@1.52 – Introduction to Oracle Sharding

@4:08 – Benefits of Sharding

@6:28 – Architecture and Key Features

@21:02 – Data Modeling and Application Considerations for a Sharded Database (SDB)

@29.51 –  Sharded Schema Creation

@36:14 – Direct Routing against an SDB

@41:23 – Proxy Routing for reporting workload

@48:35 – Results of Oracle Sharding Scalability Characterization study

AskTOM Office Hours: Oracle GDS Session 1 (April 4th, 2018)

Exploring Oracle Global Data Services (GDS):
AskTOM Office Hours offers free, open Q&A sessions with Oracle Database experts. Here is the recording of the GDS session that I conducted on April 4th, 2018.
Description:
Oracle GoldenGate and Oracle Active Data Guard allow for distribution of application workloads across replicated databases, but when applications are spread across geodistributed databases, it is a challenge to efficiently use all the databases for best performance and availability. In this AskTom Office hours session, hear from Oracle Product Development experts about how the Global Data Services feature of Oracle Database provides region-based workload routing, load balancing, and service failover across replicas while enabling a seamless distributed private data cloud for your IT. In this session, you will learn how to load-balance read/write workloads in active/active Oracle GoldenGate and to load-balance read-only workloads across Oracle Active Data Guard reader farms, among numerous other use cases.
Highlights: At 2:03, GDS is introduced. At 4:25, GDS architecture is discussed. At 8:15, Deployment steps are covered. For use cases, please go to 15:58.
For the upcoming GDS AskTOM Office Hours sessions, please visit https://asktom.oracle.com/pls/apex/f?p=100:551:::NO:551:P551_CLASS_ID:3785:

AskTOM Office Hours: Oracle Sharding (Mar 26th, 2018 : 8 AM PST)

Exploring Oracle Sharding
AskTOM Office Hours offers free, open Q&A sessions with Oracle Database experts. Join Srinagesh Battula and other members of the Oracle Sharding team to get your questions about sharding in Oracle Database answered.
March 26, 2018 15:00 – 16:00 UTC
UTC 15:00 March 26 2018
US/Pacific 08:00 March 26 2018
US/Eastern 11:00 March 26 2018
Europe/London 16:00 March 26 2018
Asia/Calcutta 20:30 March 26 2018
Asia/Hong_Kong 23:00 March 26 2018
Australia/Sydney 02:00 March 27 2018
It’s the only one you’ll ever need for this Office Hours, taking you back to this page to join the next Q&A session, review upcoming sessions, and check out past sessions.

Visit the Zoom in advance of the session to make sure your browser is properly configured. But note that we will not be sending you the Zoom URL. Instead, you will come back to the AskTOM Office Hours page (URL above) to join the session.

We will take questions via chat (all audio lines will be muted). We will record sessions for later study.

For an audio-only connection, follow these instructions:

Dial (for higher quality, dial a number based on your current location):
US: +1 669 900 6833 or +1 646 558 8656
Meeting ID: 175942660
International numbers available: https://oracle.zoom.us/zoomconference?m=9yJ1LnAX3LsKA__-cuc3TFhsGBHdaMym
Click here for more information on joining a Zoom session by phone.

Oracle Sharding Product Overview

Oracle Sharding is a scale-out relational database architecture where data is horizontally partitioned across multiple discrete databases that share no hardware or software. It provides linear scalability, fault isolation and geographic data distribution for applications designed for a sharded architecture. Checkout this video to learn how Oracle Sharding automates the deployment of sharded databases, supports elastic scaling and automatic rebalancing, direct routing, proxy routing for multi-shard queries. It does all this while rendering strict consistency, full power of SQL, and the proven enterprise qualities of Oracle Database.

AskTOM Office Hours: Oracle Sharding

AskTOM Office Hours offers free, open Q&A sessions with Oracle Database experts. Join me, and other members of the Oracle Sharding team to get your questions about sharding in Oracle Database answered.

URL: https://devgym.oracle.com/pls/apex/dg/office_hours/3242

When: 2018-02-26   22:00 UTC  – Oracle Sharding Office Hours

Start Times around the world:

UTC 10:00 PM February 26 2018
US/Pacific 02:00 PM February 26 2018
US/Eastern 05:00 PM February 26 2018
Europe/London 10:00 PM February 26 2018
Asia/Calcutta 03:30 AM February 27 2018
Asia/Hong_Kong 06:00 AM February 27 2018
Australia/Sydney 09:00 AM February 27 2018

And here are some general resources for the Office Hours program:

Landing page https://asktom.oracle.com/pls/apex/f?p=100:500
Promotional Video https://www.youtube.com/watch?v=7_-46aL0xU0

Hope you can join us.