New Features for DBA’s

Subscribe to Posts by Email


 

Subscriber Count

    699

Disclaimer

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

Pages
«  
  »

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

Environment:-

Node Type Host Details Oracle Home & GSM Listeners Databases Additional 

Shard Catalog Node

192.168.56.150

Host: shardcat

Oracle Home: /u01/app/oracle/12.2.0.1/db_1

GSM home: /u01/app/oracle/12.2.0.1/GSM

GSM Listener 1539

DB Listener 1521

SCAT

  

Shard1

192.168.56.151

Host: shard1

Oracle Home: /u01/app/oracle/12.2.0.1/db_1

DB Listener 1521

sh1

Scheduler Agent 

Shard2

192.168.56.152

Host: shard2

Oracle Home: /u01/app/oracle/12.2.0.1/db_1

DB Listener 1521

sh2

Scheduler Agent 

Steps:-

Step

Nodes/Hosts

Step Details

1

shardcat/Shard1/Shard2

Install Oracle Software Only 12cR2 – Preferably Same Location

2

shardcat

Create Database SCAT using DBCA (NON CDB)

3

shardcat

Install GSM Software as Separate Home

4

shardcat

Create Environment Setup

5

Shardcat

Prepare SCAT database for Sharding - Prerequisities

6

shardcat

Configure Shard Catalog using GSDCTL – Create Shard Catalog in SCAT

7

shard1/shard2

Start the SCH Agent & Register Shard nodes to SCAT database

8

shardcat

Create Shard Group/Director/Add Shards

9

shardcat

Deploy Shards using GSDCTL – This will eventually create databases in shard1/shard2

10

shardcat

Verify Shard Configuration

11

shardcat

Create Global Service using GSDCTL

12

shardcat

Create sample schema and Tablespace set and see that propagate to shard1/shard2

13

shardcat

Connect to sample schema and create shard table

14

Shardcat/shard1/shard2

Verify the sample table is created across shards

 

Step 1 : Install Oracle 12c R2 Software

Install Oracle 12cR2 Software on all three nodes shardcat/shard1/shard2
•Complete Pre-requisities
•Install with Software only option
Sample options to change in <softwarelocation>/response/db_install.rsp

oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=Geek DBA11g
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u02/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oper
oracle.install.db.BACKUPDBA_GROUP=dba
oracle.install.db.DGDBA_GROUP=asmadmin
oracle.install.db.KMDBA_GROUP=dba
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true

Create Oracle Home Directories

mkdir -p /u01/app/oracle/product/12.1.0/db_1

mkdir -p /u02/app/oraInventory
chown -R oracle:oinstall /u02/app/oraInventory

 

Sample Run Installer Command

 

  ./runInstaller -silent -ignoresysprereqs -responsefile /home/oracle/database/response/db_install.rsp

Step 2: Create ShardCatalog Database

On shardcat Node:-

Create Directories

mkdir –p /u01/app/oracle/flash_recover_area/SCAT

mkdir –p /u01/app/oracle/fast_recovery_area/SCAT

mkdir –p /u01/oradata/SCAT

mkdir –p /u01/app/oracle/admin/SCAT/adump

Run DBCA

./dbca -silent \

   -createDatabase \

   -templateName General_Purpose.dbc \

   -gdbName SCAT \

   -sid SCAT     \

   -SysPassword ****\

   -createAsContainerDatabase false\

   -SystemPassword ***\

   -emConfiguration NONE\

   -redoLogFileSize 100   \

   -recoveryAreaDestination /u01/app/oracle/SCAT \

                  -DatafileDestination /u01/app/oracle/SCAT

   -storageType FS       \

   -listeners LISTENER12   \

   -registerWithDirService false \

   -characterSet AL32UTF8 \

   -nationalCharacterSet AL16UTF16 \

   -databaseType MULTIPURPOSE \

   -memoryPercentage 40 \

   -memoryType AUTO

Step 3: Install GSM Software in Shardcat

On Shardcat Node:-

Download from Oracle Downloads page or Edelivery.oracle.com
Unzip the GSM Software

 

Install as separate Home

Step 5 : Prepare SCAT database for Sharding - Prerequisities

On Shardcat : SCAT Database

 

 alter system set db_create_file_dest='/u01/ora12c/app/oracle/oradata' scope=both;

 

 alter system set open_links=16 scope=spfile;

 

 alter system set open_links_per_instance=16 scope=spfile;

 

 startup force

 

 alter user gsmcatuser account unlock;

 

 alter user gsmcatuser identified by oracle;                

 

 CREATE USER mygdsadmin IDENTIFIED BY oracle;

 

 GRANT connect, create session, gsmadmin_role to mygdsadmin;

 

 grant inherit privileges on user SYS to GSMADMIN_INTERNAL; 

 

 execute dbms_xdb.sethttpport(8080);

  

commit;

 

 @?/rdbms/admin/prvtrsch.plb

 exec DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('oracleagent');

Step 6:– Create Shard Catalog in SCAT

 

On Shardcat node, in SCAT Database & Set environment to GSM Home

# gdsctl

GDSCTL> create shardcatalog -database shardcat:1521:SCAT -chunks 12 -usermygdsadmin/oracle -sdb SCAT -region region1

GDSCTL>add gsm -gsm sharddirector1 -listener 1571 -pwd oracle -catalog shardcat:1521:SCAT -region region1

GDSCTL>start gsm -gsm sharddirector1

GDSCTL>add credential -credential oracle_cred -osaccount oracle -ospassword ****

GDSCTL> exit

Step 7: Start the Scheduler Agent & Register Shard Nodes

On Shard1  & 2 :

Scheduler Agent is already installed

if you install oracle database software just start it

#Set Oracle Home

#schagent start

#schagent status

# echo oracleagent | schagent –resiterdatabase shardcat 8080 

Step 8 : Create Shard Group/Director/Add Shards

GDSCTL>set gsm -gsm sharddirector1 

GDSCTL>connect mygdsadmin/oracle

Catalog connection is established

GDSCTL>-- add shard group

GDSCTL>add shardgroup -shardgroupprimary_shardgroup -deploy_as primary -region region1

The operation completed successfully

GDSCTL>-- add shard1

GDSCTL>add invitednode shard1

GDSCTL>create shard -shardgroupprimary_shardgroup -destination shard1 -credential oracle_cred

DB Unique Name: sh1

GDSCTL> --add shard2

GDSCTL>add invitednode shard2

GDSCTL>create shard -shardgroupprimary_shardgroup -destination shard2 -credential oracle_cred

DB Unique Name: sh2

Step 9 : Deploy Shards

In shardcat node, using gsdctl, run

 

gsdctl> deploy

 

Note: This will create the databases in shard1 and shard2

using dbca and create listeners automatically

Step 10: Verify Shard Status

Step 11 : Create Global Service using GSDCTL 

Using GDSCTL on shardcat
gdsctl> add service –service test_srv –role primary

gdsctl> config service

gdsctl> start service

gdsctl> status service

Step 12: Create sample schema and Tablespace set and see that propagate to shard1/shard2

sqlplus / as sysdba

alter session enable shard ddl;

create user app_schema identified by oracle;

grant all privileges to app_schema;

grant gsmadmin_role to app_schema;

grant select_catalog_role to app_schema;

grant connect, resource to app_schema;

grant dba to app_schema;

grant execute on dbms_crypto to app_schema;

conn app_schema/oracle

alter session enable shard ddl;

CREATE TABLESPACE SET TSP_SET_1 using template (datafile size 100m extent management local segment space management auto );

CREATE TABLESPACE products_tsp datafile size 100m extent management local uniform size 1m;

Step 13: Create Shard Tables in SCAT Database

CREATE SHARDED TABLE Customers

(

CustId VARCHAR2(60) NOT NULL,

FirstName VARCHAR2(60),

LastName VARCHAR2(60),

Class VARCHAR2(10),

Geo VARCHAR2(8),

CustProfile VARCHAR2(4000),

Passwd RAW(60),

CONSTRAINT pk_customers PRIMARY KEY (CustId),

CONSTRAINT json_customers CHECK (CustProfile IS JSON)

) TABLESPACE SET TSP_SET_1

PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;

CREATE SHARDED TABLE Orders

(

OrderId INTEGER NOT NULL,

CustId VARCHAR2(60) NOT NULL,

OrderDate TIMESTAMP NOT NULL,

SumTotal NUMBER(19,4),

Status CHAR(4),

constraint pk_orders primary key (CustId, OrderId),

constraint fk_orders_parent foreign key (CustId)

references Customers on delete cascade

) partition by reference (fk_orders_parent);

 

CREATE SEQUENCE Orders_Seq;

 

CREATE SHARDED TABLE LineItems

(

OrderId INTEGER NOT NULL,

CustId VARCHAR2(60) NOT NULL,

ProductId INTEGER NOT NULL,

Price NUMBER(19,4),

Qty NUMBER,

constraint pk_items primary key (CustId, OrderId, ProductId),

constraint fk_items_parent foreign key (CustId, OrderId)

references Orders on delete cascade

) partition by reference (fk_items_parent);

 

 

SQL> -- duplicated table

CREATE DUPLICATED TABLE Products

(

ProductId INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

Name VARCHAR2(128),

DescrUri VARCHAR2(128),

LastPrice NUMBER(19,4)

) TABLESPACE products_tsp;

Step 14: Verify Distribution of Tables to shards

On Shard 1:- The table and tablespace are partitioned and distributed some partitions

On Shard2: The tables and tablespace are partitioned and distributed some partitions

In the next post of series, I will be writing how to manage shards and more about operational restrictions.

-Thanks

Geek DBA

February 20th, 2017 | Tags: , , , , | Category: 12c Database

12 comments to Oracle Sharding : Part 2 – Installating & Configuring Shards

  • Venkat
    February 20, 2017 at 7:38 am

    Great documentation..
    Is it 12cR2 enterprise edition for production database released. I have checked in oracle but I couldn’t locate it.

    • Geek DBA
      February 20, 2017 at 10:57 am

      Hi Venkat

      Yes its 12c R2 and its now available in edelivery.oracle.com and in oracle public cloud. I believe it will be out soon by Mar 2017. The post contains all relevant practical stuff as well.

      -Thanks

  • dbaontap
    March 3, 2017 at 12:10 am

    12cR2 is now available. Check the Oracle downloads site.

  • GAVIN
    May 15, 2017 at 10:21 am

    Good work! – thanks

  • Venkatesh
    June 1, 2017 at 12:36 am

    Hi ,

    I am following same steps to install in AWS EC2 instance.
    its failing register the database with below command.

    echo welcome | schagent –resiterdatabase shardcat 8080

    my shard catalog database up and running also i have checked listner status.

    STATUS of the LISTENER
    ————————
    Alias LISTENER
    Version TNSLSNR for Linux: Version 12.2.0.1.0 – Production
    Start Date 31-MAY-2017 11:49:24
    Uptime 0 days 0 hr. 26 min. 55 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
    Listener Log File /u01/app/oracle/diag/tnslsnr/ip-10-74-22-249/listener/alert/log.xml
    Listening Endpoints Summary…
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-10-74-22-249)(PORT=1539)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-10-74-22-249)(PORT=8080))(Presentation=HTTP)(Session=RAW))
    Services Summary…
    Service “GDS$CATALOG.oradbcloud” has 1 instance(s).
    Instance “orcl”, status READY, has 1 handler(s) for this service…
    Service “orcl” has 1 instance(s).
    Instance “orcl”, status READY, has 1 handler(s) for this service…
    Service “orclXDB” has 1 instance(s).
    Instance “orcl”, status READY, has 1 handler(s) for this service…
    The command completed successfully

  • Chandra A
    April 11, 2018 at 11:50 pm

    I am getting the ORA-02577 error when I tried to create a sharded table. I need to shard my data by a business, but I could not. I could not get any help online. Can you please help me?

    create SHARDED table MyData
    (
    Data_id NUMBER not null,
    member_Type VARCHAR2(20),
    business_id NUMBER,
    constraint MyDATA_PK primary key (Data_id)
    )
    PARTITIONSET BY LIST (business_id)
    PARTITION BY CONSISTENT HASH (Data_id)
    PARTITIONS AUTO
    (
    PARTITIONSET Biz1 VALUES (1) TABLESPACE SET TSP_SET_1,
    PARTITIONSET Biz2 VALUES (2) TABLESPACE SET TSP_SET_2
    )
    ;

    Error report –
    ORA-02577: cannot specify PARTITIONSET in a non-composite sharding environment

  • Chandra A
    April 11, 2018 at 11:58 pm

    I tried the example from Oracle web site (https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/sharding-schema-design.html#GUID-B69FD5E1-7DDC-4867-9C26-9849C507E8C4) with same error. Any help will be greatly appreciated.

    CREATE SHARDED TABLE customers
    ( cust_id NUMBER NOT NULL
    , name VARCHAR2(50)
    , address VARCHAR2(250)
    , location_id VARCHAR2(20)
    , class VARCHAR2(3)
    , signup_date DATE
    ,
    CONSTRAINT cust_pk PRIMARY KEY(cust_id)
    )
    PARTITIONSET BY LIST (class)
    PARTITION BY CONSISTENT HASH (cust_id)
    PARTITIONS AUTO
    (PARTITIONSET gold VALUES (‘gld’) TABLESPACE SET ts2,
    PARTITIONSET silver VALUES (‘slv’) TABLESPACE SET ts1)
    ;

  • m rehman
    September 7, 2018 at 1:24 pm

    hi very informative topic.

    Now i want to add new shard in existing sdb, what the steps i have to follow, pls shared.

    thanks
    regards

  • m rehman
    September 13, 2018 at 8:53 pm

    hi i am using user managed sharding, i have to spplit my table partition into another tablespace as i am used to in simple table.
    so what about sharded table ,, table 1st partition was created with maxvalue and diferenct tablespace

    now using following command and giving me error

    SQL> ALTER TABLE TRY SPLIT PARTITION TRY_P1 AT (TIMESTAMP’ 2011-10-01 00:00:00′) INTO (PARTITION “TRY_P1” , PARTITION “TRY_P2” TABLESPACE “Node2_TS_2” );
    ALTER TABLE TRY SPLIT PARTITION TRY_P1 AT (TIMESTAMP’ 2011-10-01 00:00:00′) INTO (PARTITION “TRY_P1” , PARTITION “TRY_P2” TABLESPACE “Node2_TS_2” )
    *
    ERROR at line 1:
    ORA-02553: TABLESPACE is required

    SQL> !oerr ora 2553
    02553, 00000, “TABLESPACE is required”
    // *Cause: The TABLESPACE clause was not supplied when specifying a partition
    // of a user-defined sharded table.
    // *Action: Add the TABLESPACE clause.

    SQL>

    pls help me what i do now . tks
    mrehman_kahloon@yahoo.com

    • Geek DBA
      September 18, 2018 at 10:33 pm

      Hi

      Thanks for writing to me.

      I can see only one tablespace is mentioned for two partitions, secondly do you need to really mention TRY_P1? since you want to split the data at

      I think this may not be an issue with sharding .

      Please let me know if you have resolved it.

      -Suresh