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
hi,
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