How to start the full stack of Sharding Database

Sharding configuration is a mix of various Oracle products and various configurations are involved. Hence there is dependency and a process to start the sharding environment completely.

  • Start the Shard Databases sh1, sh2
[oracle@ORA-C1 ~]$ sqlplus / as sysdba
SQL*Plus: Release Production on Fri Jul 14 04:20:58 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1828716544 bytes
Fixed Size 8621856 bytes
Variable Size 1157628128 bytes
Database Buffers 654311424 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
  • Start the Listeners of the Shards
[oracle@ORA-C1 ~]$ lsnrctl start listener_sh1
LSNRCTL for Linux: Version - Production on 14-JUL-2017 04:23:59
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Starting /u01/app/oracle/product/ please wait...
TNSLSNR for Linux: Version - Production
System parameter file is /u01/app/oracle/product/
Log messages written to /u01/app/oracle/diag/tnslsnr/ORA-C1/listener_sh1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORA-C1.localdomain)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORA-C1.localdomain)(PORT=1521)))
Alias listener_sh1
Version TNSLSNR for Linux: Version - Production
Start Date 14-JUL-2017 04:24:00
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
Listener Parameter File /u01/app/oracle/product/
Listener Log File /u01/app/oracle/diag/tnslsnr/ORA-C1/listener_sh1/alert/log.xml
Listening Endpoints Summary...
Services Summary...
Service "sh1_DGMGRL.localdomain" has 1 instance(s).
Instance "sh1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ORA-C1 ~]$
  • Start the Catalog Database
  • Start the Catalog Listener
  • Start the GSM
[oracle@ORA-C2 ~]$ gdsctl
GDSCTL: Version - Production on Fri Jul 14 04:26:53 NZST 2017
Copyright (c) 2011, 2016, Oracle. All rights reserved.
Welcome to GDSCTL, type "help" for information.
Current GSM is set to SHARDDIR_DG
GDSCTL>status gsm
GSM-45075: No response from GSM
GDSCTL>start gsm
GSM is started successfully
GDSCTL>status gsm
Start Date 14-JUL-2017 04:27:07
Trace Level support
Listener Log File /u01/app/oracle/diag/gsm/ORA-C2/sharddir_dg/alert/log.xml
Listener Trace File /u01/app/oracle/diag/gsm/ORA-C2/sharddir_dg/trace/ora_6082_140168269836608.trc
Endpoint summary (ADDRESS=(HOST=ORA-C2.localdomain)(PORT=12121)(PROTOCOL=tcp))
GSMOCI Version 2.2.1
Mastership Y
Connected to GDS catalog Y
Process Id 6092
Number of reconnections 0
Pending tasks. Total 0
Tasks in process. Total 0
Regional Mastership TRUE
Total messages published 0
Time Zone +12:00
Orphaned Buddy Regions:
GDS region west
  • Check the configuration
GDSCTL>config shard
Catalog connection is established
Name Shard Group Status State Region Availability
---- ----------- ------ ----- ------ ------------
sh1 primary_east_shg Ok Deployed east ONLINE
sh2 standby_west_shg Ok Deployed west READ ONLY


In a similar way, the descending order can be used to stop the complete Sharding environment.

Review and Inputs By – Syed Jaffar Hussain

Website Comments

  1. m rehman kahloon

    18c user manged sharding with range partition, need help in following issues
    1 catalog db,3shards , each shard has 5 tablespace and different date data.
    1– how can i split my maxvalue partition into new partition, when i try recursive sql errors show
    2– can’t load bulk load, as we are used to in simple tables , array dml not suported on sharded table
    3– how can i offload data from sharded table , alter table exchange partition

    please guide me what i do now, thanks

Leave a Reply to m rehman kahloon Cancel reply