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 12.2.0.1.0 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.
SQL>
  • Start the Listeners of the Shards
[oracle@ORA-C1 ~]$ lsnrctl start listener_sh1
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-JUL-2017 04:23:59
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.2.0.1/dbhome_2/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0.1/dbhome_2/network/admin/listener.ora
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)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORA-C1.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias listener_sh1
Version TNSLSNR for Linux: Version 12.2.0.1.0 - 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
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.2.0.1/dbhome_2/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ORA-C1/listener_sh1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORA-C1.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
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 12.2.0.1.0 - 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
Alias SHARDDIR_DG
Version 12.2.0.1.0
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:
None
GDS region west
GDSCTL>
  • 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

GDSCTL>

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

Review and Inputs By – Syed Jaffar Hussain