Gavinsoorma.com



Oracle 19c Sharding Oracle Sharding is an architecture in which data is horizontally partitioned across a number of independent physical databases called shards. Think of it as one giant database partitioned into many small databases located on different servers – similar to the concept of one giant table being divided into a number of smaller partitions. Unlike the case of Oracle Partititioing where all the partitions of table are located in the same database, in Oracle Sharding the partitions of the same table are located on different databases.All the shards together make up a single logical database, which is referred to as a sharded database or SDB.Horizontal partitioning involves splitting a database table across the shard databases so that each shard contains the same table with the same set of columns but a different subset of rows. A table split up or partitioned in this manner is also known as a sharded table.As far as the application is concerned a shared database looks like a single database and the number of shards and distribution of data across those shards are completely transparent to the application.Sharding provides advantages like global distribution of data where shards are located in different geographical regions and each such sharded database has data relevant and distinct to the geographical region the shard is located in. It also provides the linear scalability of workloads, data and users as well as fault isolation where the failure of a shard will be transparent to other shards located in maybe different data centres as well as maybe different countries.However, it should be kept in mind that applications that use sharding must have a well-defined data model and data distribution strategy and also primarily access data using a sharding key. Examples of a sharding key could be the CUSTOMER_ID or ORDER_ID columns in a shared table and this is mainly suited for OLTP applications.In addition to the shard databases, we also have a Shard Catalog database which provides the centralized management of the shard database topology as well as performs tasks like automated shard deployment and co-ordinating multi or cross-shard queries.The Shard Director is Global Service Manager (GSM) type network listener which provides high performance routing of application connections based on the sharding key. We typically using the GDSCTL command line utility to manage the shard catalog as well as entire shard environment.Data in the sharded database (SDB) is typically accessed via a global service which is defined via GDSCTLDistribution of partitions across shards is achieved by creating partitions in tablespaces that reside on different shards. Each partition of a sharded table is stored in a separate tablespace on a separate shard based on the sharding or partition key.A tablespace is a logical unit of data distribution in an SDB. Sharded table partitions are stored in different tablespaces.A sharded table family is a set of tables that are sharded in the same manner and are typically tables linked by a parent-child relationship. A chunk is a set of tablespaces that store corresponding partitions of all tables in a sharded table family. So for example take the case of a shared table family consisting of the CUSTOMERS, ORDERS and LINEITEMS. A single chunk (typically a single tablespace) will contain the relevant partitions of all the 3 tables and they will be located in the same tablespace. Having the corresponding partitions of related tables are always stored in the same shard helps minimize the number of multi-shard joins and improves shard performance.In Oracle Sharding, tablespaces are created and managed as a unit called a tablespace set. A tablespace set consists of multiple tablespaces distributed across shards and all tablespaces in a tablespace set would have the same properties.Sharding introduces some changes to the DDL statements like CREATE SHARDED TABLE, CREATE DUPLICATED TABLE, CREATE TABLESPACE SET and such DDL statements with this syntax can only be executed against a sharded database. We need to use the command ALTER SESSION ENABLE SHARD DDL.Oracle Sharding DemoEnvironment host01: Shard Catalog Database (gsmcat) and Shard Director host02: Primary Shard database (sh1)host03: Primary Shard database (sh2)host04: Standby Read-Only Shard database (sh3)host05: Standby Read-Only Shard database (sh4)NoteInstall Oracle 19c database software on all the 5 hosts On host01 also install 19c GSM software and create the GSMCAT 19c database Grant roles and privileges on the Shard Catalog database[oracle@host01 bin]$ . oraenvORACLE_SID = [db1] ? gsmcatThe Oracle base remains unchanged with value /u01/app/oracle[oracle@host01 bin]$ sqlplus sys as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Thu May 30 09:29:51 2019Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Enter password: Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0SQL> alter user gsmcatuser account unlock;User altered.SQL> alter user gsmcatuser identified by oracle;User altered.SQL> create user gsm_admin identified by oracle;User created.SQL> grant connect,create session,gsmadmin_role to gsm_admin;Grant succeeded.SQL> grant inherit privileges on user SYS to gsmadmin_internal;Grant succeeded.SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata' scope=both;System altered.SQL> alter system set open_links=16 scope=spfile;System altered.SQL> alter system set open_links_per_instance=16 scope=spfile;System altered.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup;ORACLE instance started.Total System Global Area 838858176 bytesFixed Size 8902080 bytesVariable Size 247463936 bytesDatabase Buffers 578813952 bytesRedo Buffers 3678208 bytesDatabase mounted.Database opened.Create the Shard Catalog and start GSMConnect to a shard director host (host01) and start GDSCTL.[oracle@host01 bin]$ pwd/u01/app/oracle/product/19.3.0/gsmhome_1/bin[oracle@host01 bin]$ ./gdsctlGDSCTL: Version 19.0.0.0.0 - Production on Sat Jun 01 22:46:28 AWST 2019Copyright (c) 2011, 2019, Oracle. All rights reserved.Welcome to GDSCTL, type "help" for information.Warning: GSM is not set automatically because gsm.ora does not contain GSM entries. Use "set gsm" command to set GSM for the session.Current GSM is set to GSMORAGDSCTL> create shardcatalog -database host01:1521/gsmcat.localdomain -region region1 -user gsm_admin/oracle -agent_port 7777 -agent_password oracle -chunks 12Catalog is createdGDSCTL> add gsm -gsm shard -catalog host01:1521/gsmcat.localdomain -region region1 -pwd oracleGSM successfully addedGDSCTL>start gsm -gsm shardGSM is started successfullyCreate the credentialsNote: These credentials are the operating system username and password on the shard hosts and the credentials are used by the remote scheduler agent to run jobs on the shard hostsGDSCTL>add credential -credential oracred -osaccount oracle -ospassword oracleThe operation completed successfullyConfigure the Shard database host machines (host02, host03, host04 and host05)On every host create directories[oracle@host02 oracle]$ mkdir oradata[oracle@host02 oracle]$ mkdir fast_recovery_areaSet environment variables in .bash_profileORACLE_BASE=/u01/app/oracleexport ORACLE_BASEORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1export ORACLE_HOMERegister the Scheduler Agent (on every shard host) and then start the Scheduler AgentNote: the agent password and port are the one we used in the command to create the shard catalog earlier(create shardcatalog -database host01:1521/gsmcat.localdomain -region region1 -user gsm_admin/oracle -agent_port 7777 -agent_password oracle -chunks 12)host02[oracle@host02 bin]$ pwd/u01/app/oracle/product/19.3.0/dbhome_1/bin [oracle@host02 bin]$ ./schagent -registerdatabase host01 7777Warning:The JKS keystore uses a proprietary format. It is recommended to migrate to PKCS12 which is an industry standard format using "keytool -importkeystore -srckeystore /u01/app/oracle/product/19.3.0/dbhome_1/data/wallet/agent.key -destkeystore /u01/app/oracle/product/19.3.0/dbhome_1/data/wallet/agent.key -deststoretype pkcs12".Agent Registration Password ? *******Oracle Scheduler Agent Registration for 19.3.0.0.0 AgentAgent Registration Successful![oracle@host02 bin]$ ./schagent -startScheduler agent started using port 18614[oracle@host02 bin]$ ./schagent -statusAgent running with PID 3519Agent_version:19.3.0.0.0Running_time:00:00:09Total_jobs_run:0Running_jobs:0Platform:LinuxORACLE_HOME:/u01/app/oracle/product/19.3.0/dbhome_1ORACLE_BASE:/u01/app/oraclePort:18614Host:host02.localdomainhost03[oracle@host03 dbhome_1]$ cd $ORACLE_HOME/bin[oracle@host03 bin]$ ./schagent -registerdatabase host01 7777Warning:The JKS keystore uses a proprietary format. It is recommended to migrate to PKCS12 which is an industry standard format using "keytool -importkeystore -srckeystore /u01/app/oracle/product/19.3.0/dbhome_1/data/wallet/agent.key -destkeystore /u01/app/oracle/product/19.3.0/dbhome_1/data/wallet/agent.key -deststoretype pkcs12".Agent Registration Password ? *******Oracle Scheduler Agent Registration for 19.3.0.0.0 AgentAgent Registration Successful![oracle@host03 bin]$ ./schagent -startScheduler agent started using port 20524[oracle@host03 bin]$ ./schagent -statusAgent running with PID 3593Agent_version:19.3.0.0.0Running_time:00:00:10Total_jobs_run:0Running_jobs:0Platform:LinuxORACLE_HOME:/u01/app/oracle/product/19.3.0/dbhome_1ORACLE_BASE:/u01/app/oraclePort:20524Host:host03.localdomainhost04[oracle@host04 ~]$ cd $ORACLE_HOME/bin[oracle@host04 bin]$ ./schagent -registerdatabase host01 7777Warning:The JKS keystore uses a proprietary format. It is recommended to migrate to PKCS12 which is an industry standard format using "keytool -importkeystore -srckeystore /u01/app/oracle/product/19.3.0/dbhome_1/data/wallet/agent.key -destkeystore /u01/app/oracle/product/19.3.0/dbhome_1/data/wallet/agent.key -deststoretype pkcs12".Agent Registration Password ? *******Oracle Scheduler Agent Registration for 19.3.0.0.0 AgentAgent Registration Successful![oracle@host04 bin]$ ./schagent -startScheduler agent started using port 33172[oracle@host04 bin]$ ./schagent -statusAgent running with PID 3664Agent_version:19.3.0.0.0Running_time:00:00:09Total_jobs_run:0Running_jobs:0Platform:LinuxORACLE_HOME:/u01/app/oracle/product/19.3.0/dbhome_1ORACLE_BASE:/u01/app/oraclePort:33172Host:host04.localdomainhost05[oracle@host05 ~]$ cd $ORACLE_HOME/bin[oracle@host05 bin]$ ./schagent -registerdatabase host01 7777Warning:The JKS keystore uses a proprietary format. It is recommended to migrate to PKCS12 which is an industry standard format using "keytool -importkeystore -srckeystore /u01/app/oracle/product/19.3.0/dbhome_1/data/wallet/agent.key -destkeystore /u01/app/oracle/product/19.3.0/dbhome_1/data/wallet/agent.key -deststoretype pkcs12".Agent Registration Password ? *******Oracle Scheduler Agent Registration for 19.3.0.0.0 AgentAgent Registration Successful![oracle@host05 bin]$ ./schagent -startScheduler agent started using port 27340[oracle@host05 bin]$ ./schagent -statusAgent running with PID 3883Agent_version:19.3.0.0.0Running_time:00:00:14Total_jobs_run:0Running_jobs:0Platform:LinuxORACLE_HOME:/u01/app/oracle/product/19.3.0/dbhome_1ORACLE_BASE:/u01/app/oraclePort:27340Host:host05.localdomain[oracle@host05 bin]$ Create the Shard Groups[oracle@host01 bin]$ ./gdsctlGDSCTL: Version 19.0.0.0.0 - Production on Thu May 30 10:15:29 AWST 2019Copyright (c) 2011, 2019, Oracle. All rights reserved.Welcome to GDSCTL, type "help" for information.Current GSM is set to SHARDGDSCTL> GDSCTL> add region -region region2The operation completed successfullyGDSCTL> add shardgroup -shardgroup primary_shardgroup -deploy_as primary -region region1The operation completed successfullyGDSCTL> add shardgroup -shardgroup standby_shardgroup -deploy_as active_standby -region region2The operation completed successfullyAdd each shard’s host address to the valid node checking for registration (VNCR) list in the shard catalogGDSCTL> add invitednode host02GDSCTL> add invitednode host03GDSCTL> add invitednode host04GDSCTL> add invitednode host05GDSCTL> config vncrName Group ID ---- -------- 192.168.56.80 host02 host03 host04 host05 Create the ShardsNote: two shards are created in the shard group primary_shardgroup and two shards are created in the shard group standby_shardgroup GDSCTL> create shard -shardgroup primary_shardgroup -destination host02 -credential oracred -dbtemplatefile /u01/app/oracle/product/19.3.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbcThe operation completed successfullyDB Unique Name: sh1GDSCTL> create shard -shardgroup primary_shardgroup -destination host03 -credential oracred -dbtemplatefile /u01/app/oracle/product/19.3.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbcThe operation completed successfullyDB Unique Name: sh2GDSCTL> create shard -shardgroup standby_shardgroup -destination host04 -credential oracred -dbtemplatefile /u01/app/oracle/product/19.3.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbcThe operation completed successfullyDB Unique Name: sh3GDSCTL> create shard -shardgroup standby_shardgroup -destination host05 -credential oracred -dbtemplatefile /u01/app/oracle/product/19.3.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbcThe operation completed successfullyDB Unique Name: sh4Review the shard configuration GDSCTL> configRegions------------------------region1 region2 GSMs------------------------shard Sharded Database------------------------orasdb Databases------------------------sh1 sh2 sh3 sh4 Shard Groups------------------------primary_shardgroup standby_shardgroup Shard spaces------------------------shardspaceora Services------------------------GDSCTL pending requests------------------------Command Object Status ------- ------ ------ Global properties------------------------Name: oradbcloudMaster GSM: shardDDL sequence #: 0GDSCTL> GDSCTL> config shardspaceShard space Chunks ----------- ------ shardspaceora 12 GDSCTL> config shardgroupShard Group Chunks Region Shard space ----------- ------ ------ ----------- primary_shardgroup 12 region1 shardspaceora standby_shardgroup 12 region2 shardspaceora GDSCTL> config shardName Shard Group Status State Region Availability ---- ----------- ------ ----- ------ ------------ sh1 primary_shardgroup U none region1 - sh2 primary_shardgroup U none region1 - sh3 standby_shardgroup U none region2 - sh4 standby_shardgroup U none region2 - Run the deployment GDSCTL> deploydeploy: examining configuration...deploy: deploying primary shard 'sh1' ...deploy: network listener configuration successful at destination 'host02'deploy: starting DBCA at destination 'host02' to create primary shard 'sh1' ...deploy: deploying primary shard 'sh2' ...deploy: network listener configuration successful at destination 'host03'deploy: starting DBCA at destination 'host03' to create primary shard 'sh2' ...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: waiting for 2 DBCA primary creation job(s) to complete...deploy: DBCA primary creation job succeeded at destination 'host02' for shard 'sh1'deploy: deploying standby shard 'sh3' ...deploy: network listener configuration successful at destination 'host04'deploy: starting DBCA at destination 'host04' to create standby shard 'sh3' ...deploy: DBCA primary creation job succeeded at destination 'host03' for shard 'sh2'deploy: deploying standby shard 'sh4' ...deploy: network listener configuration successful at destination 'host05'deploy: starting DBCA at destination 'host05' to create standby shard 'sh4' ...Remote job failed with error: EXTERNAL_LOG_ID="job_73230_16",USERNAME="oracle"For more details: select destination, output from all_scheduler_job_run_details where job_name='SHARD_SH3_DBCREATE'deploy: DBCA standby creation job failed at destination 'host04' for shard 'sh3'deploy: waiting for 1 DBCA standby creation job(s) to complete...deploy: waiting for 1 DBCA standby creation job(s) to complete...deploy: waiting for 1 DBCA standby creation job(s) to complete...deploy: waiting for 1 DBCA standby creation job(s) to complete...deploy: waiting for 1 DBCA standby creation job(s) to complete...deploy: waiting for 1 DBCA standby creation job(s) to complete...deploy: DBCA standby creation job succeeded at destination 'host05' for shard 'sh4'Deployment has terminated due to previous errors.The operation completed successfullyGDSCTL> deploydeploy: examining configuration...deploy: deploying standby shard 'sh3' ...deploy: network listener configuration successful at destination 'host04'deploy: starting DBCA at destination 'host04' to create standby shard 'sh3' ...deploy: waiting for 1 DBCA standby creation job(s) to complete...deploy: waiting for 1 DBCA standby creation job(s) to complete...deploy: waiting for 1 DBCA standby creation job(s) to complete...deploy: waiting for 1 DBCA standby creation job(s) to complete...deploy: waiting for 1 DBCA standby creation job(s) to complete...deploy: waiting for 1 DBCA standby creation job(s) to complete...deploy: DBCA standby creation job succeeded at destination 'host04' for shard 'sh3'deploy: requesting Data Guard configuration on shards via GSMdeploy: shards configured successfullyThe operation completed successfullyGDSCTL> databasesDatabase: "sh1" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1Alert: Data Guard observer is not running. Registered instances: orasdb%1Database: "sh2" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1Alert: Data Guard observer is not running. Registered instances: orasdb%11Database: "sh3" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: region2 Registered instances: orasdb%21Database: "sh4" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: region2 Registered instances: orasdb%31GDSCTL> config shardName Shard Group Status State Region Availability ---- ----------- ------ ----- ------ ------------ sh1 primary_shardgroup Ok Deployed region1 ONLINE sh2 primary_shardgroup Ok Deployed region1 ONLINE sh3 standby_shardgroup Ok Deployed region2 READ ONLY sh4 standby_shardgroup Ok Deployed region2 READ ONLY Add a global service that runs on all the primary shardsGDSCTL> add service -service oltp_rw_srvc -role primary The operation completed successfullyGDSCTL> config service Name Network name Pool Started Preferred all ---- ------------ ---- ------- ------------- oltp_rw_srvc oltp_rw_srvc.orasdb.oradbcloud orasdb No Yes GDSCTL> start service -service oltp_rw_srvcThe operation completed successfullyGDSCTL> status serviceService "oltp_rw_srvc.orasdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE Instance "orasdb%1", name: "sh1", db: "sh1", region: "region1", status: ready. Instance "orasdb%11", name: "sh2", db: "sh2", region: "region1", status: ready.Add a global service that runs on all the standby shards for servicing read-only workloadsGDSCTL> add service -service oltp_ro_srvc -role physical_standbyThe operation completed successfullyGDSCTL> config service Name Network name Pool Started Preferred all ---- ------------ ---- ------- ------------- oltp_ro_srvc oltp_ro_srvc.orasdb.oradbclou orasdb No Yes d oltp_rw_srvc oltp_rw_srvc.orasdb.oradbclou orasdb Yes d GDSCTL> start service -service oltp_ro_srvcThe operation completed successfullyGDSCTL> status serviceService "oltp_ro_srvc.orasdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE Instance "orasdb%21", name: "sh3", db: "sh3", region: "region2", status: ready. Instance "orasdb%31", name: "sh4", db: "sh4", region: "region2", status: ready.Service "oltp_rw_srvc.orasdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE Instance "orasdb%1", name: "sh1", db: "sh1", region: "region1", status: ready. Instance "orasdb%11", name: "sh2", db: "sh2", region: "region1", status: ready.Check the Data Guard Broker Configuration(s)Note: In this case the FSFO Observer was manually started host02DGMGRL> show configuration;Configuration - sh1 Protection Mode: MaxPerformance Members: sh1 - Primary database sh3 - (*) Physical standby database Fast-Start Failover: Enabled in Potential Data Loss ModeConfiguration Status:SUCCESS (status updated 54 seconds ago)host03DGMGRL> show configuration;Configuration - sh2 Protection Mode: MaxPerformance Members: sh2 - Primary database sh4 - (*) Physical standby database Fast-Start Failover: Enabled in Potential Data Loss ModeConfiguration Status:SUCCESS (status updated 43 seconds ago)Create the shard database enabled demo APP_SCHEMA schema [oracle@host01 bin]$ . oraenvORACLE_SID = [oracle] ? gsmcatThe Oracle base remains unchanged with value /u01/app/oracle[oracle@host01 bin]$ sqlplus sys as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Thu May 30 12:33:22 2019Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Enter password: Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0SQL> alter session enable shard ddl;Session altered.SQL> create user app_schema identified by app_schema;User created.SQL> grant all privileges to app_schema;Grant succeeded.SQL> grant gsmadmin_role to app_schema;Grant succeeded.SQL> grant select_catalog_role to app_schema;Grant succeeded.SQL> grant connect, resource to app_schema;Grant succeeded.SQL> grant dba to app_schema;Grant succeeded.SQL> grant execute on dbms_crypto to app_schema;Grant succeeded.SQL> CREATE TABLESPACE SET TSP_SET_1 using template (datafile size 100m autoextend on next 10M maxsize unlimited extent management local segment space management auto); Tablespace created.SQL> CREATE TABLESPACE products_tsp datafile size 100m autoextend on next 10M maxsize unlimited extent management local uniform size 1m; Tablespace created.SQL> CONNECT app_schema/app_schemaConnected.SQL> ALTER SESSION ENABLE SHARD DDL;Session altered.SQL> 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; Table created.SQL> 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); Table created.SQL> CREATE SEQUENCE Orders_Seq;Sequence created.SQL> 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); Table created.SQL> 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; Table created.GDSCTL> show ddlid DDL Text Failed shards -- -------- ------------- 5 grant connect, resource to app_schema 6 grant dba to app_schema 7 grant execute on dbms_crypto to app_s... 8 CREATE TABLESPACE SET TSP_SET_1 using... 9 CREATE TABLESPACE products_tsp datafi... 10 CREATE SHARDED TABLE Customers ( ... 11 CREATE SHARDED TABLE Orders ( O... 12 CREATE SEQUENCE Orders_Seq 13 CREATE SHARDED TABLE LineItems ( ... 14 CREATE MATERIALIZED VIEW "APP_SCHEMA"... Verify that there were no DDL errors on each of the shardsGDSCTL> config shard -shard sh2Name: sh2Shard Group: primary_shardgroupStatus: OkState: DeployedRegion: region1Connection string: host03.localdomain:1521/sh2.localdomain:dedicatedSCAN address: ONS remote port: 0Disk Threshold, ms: 20CPU Threshold, %: 75Version: 19.0.0.0Failed DDL: DDL Error: ---Failed DDL id: Availability: ONLINERack: Supported services------------------------Name Preferred Status ---- --------- ------ oltp_ro_srvc Yes Enabled oltp_rw_srvc Yes Enabled GDSCTL> config shard -shard sh3Name: sh3Shard Group: standby_shardgroupStatus: OkState: DeployedRegion: region2Connection string: host04.localdomain:1521/sh3.localdomain:dedicatedSCAN address: ONS remote port: 0Disk Threshold, ms: 20CPU Threshold, %: 75Version: 19.0.0.0Failed DDL: DDL Error: ---Failed DDL id: Availability: READ ONLYRack: Supported services------------------------Name Preferred Status ---- --------- ------ oltp_ro_srvc Yes Enabled oltp_rw_srvc Yes Enabled GDSCTL> config shard -shard sh4Name: sh4Shard Group: standby_shardgroupStatus: OkState: DeployedRegion: region2Connection string: host05.localdomain:1521/sh4.localdomain:dedicatedSCAN address: ONS remote port: 0Disk Threshold, ms: 20CPU Threshold, %: 75Version: 19.0.0.0Failed DDL: DDL Error: ---Failed DDL id: Availability: READ ONLYRack: Supported services------------------------Name Preferred Status ---- --------- ------ oltp_ro_srvc Yes Enabled oltp_rw_srvc Yes Enabled GDSCTL> Verify the number of chunks createdGDSCTL> config chunks Chunks------------------------Database From To -------- ---- -- sh1 1 6 sh2 7 12 sh3 1 6 sh4 7 12 Connect to the Shard Catalog database GSMCATSQL> conn / as sysdbaConnected. SQL> SELECT a.name Shard, COUNT(b.chunk_number) Number_of_Chunks FROM gsmadmin_internal.database a, gsmadmin_internal.chunk_loc b WHERE a.database_num=b.database_num GROUP BY a.name ORDER BY a.name; SHARD NUMBER_OF_CHUNKS---------- ----------------sh1 6sh2 6sh3 6sh4 6Note tablespaces on Shard Catalog databaseSQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name; TABLESPACE_NAME MB------------------------------ ----------PRODUCTS_TSP 100SYSAUX 570SYSTEM 910TSP_SET_1 100UNDOTBS1 340USERS56 rows selected.Connect to a shard database and view the tablespaces, tables and partitions which have been created[oracle@host02 ~]$ sqlplus app_schema/app_schemaSQL*Plus: Release 19.0.0.0.0 - Production on Sun Jun 2 12:04:38 2019Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0SQL> select name from v$database;NAME---------SH1SQL> select table_name from user_tables;TABLE_NAME--------------------------------------------------------------------------------USLOG$_PRODUCTSPRODUCTSCUSTOMERSORDERSLINEITEMSSQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name; TABLESPACE_NAME MB------------------------------ ----------C001TSP_SET_1 100C002TSP_SET_1 100C003TSP_SET_1 100C004TSP_SET_1 100C005TSP_SET_1 100C006TSP_SET_1 100PRODUCTS_TSP 100SYSAUX 560SYSTEM 900TSP_SET_1 100UNDOTBS1 340TABLESPACE_NAME MB------------------------------ ----------USERS512 rows selected.SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions where tablespace_name like 'C%TSP_SET_1' order by tablespace_name; TABLE_NAME PARTITION_NAME TABLESPACE_NAME-------------------- -------------------- ------------------------------ORDERS CUSTOMERS_P1 C001TSP_SET_1CUSTOMERS CUSTOMERS_P1 C001TSP_SET_1LINEITEMS CUSTOMERS_P1 C001TSP_SET_1CUSTOMERS CUSTOMERS_P2 C002TSP_SET_1LINEITEMS CUSTOMERS_P2 C002TSP_SET_1ORDERS CUSTOMERS_P2 C002TSP_SET_1CUSTOMERS CUSTOMERS_P3 C003TSP_SET_1ORDERS CUSTOMERS_P3 C003TSP_SET_1LINEITEMS CUSTOMERS_P3 C003TSP_SET_1ORDERS CUSTOMERS_P4 C004TSP_SET_1CUSTOMERS CUSTOMERS_P4 C004TSP_SET_1LINEITEMS CUSTOMERS_P4 C004TSP_SET_1CUSTOMERS CUSTOMERS_P5 C005TSP_SET_1LINEITEMS CUSTOMERS_P5 C005TSP_SET_1ORDERS CUSTOMERS_P5 C005TSP_SET_1CUSTOMERS CUSTOMERS_P6 C006TSP_SET_1LINEITEMS CUSTOMERS_P6 C006TSP_SET_1ORDERS CUSTOMERS_P6 C006TSP_SET_118 rows selected.[oracle@host03 bin]$ sqlplus app_schema/app_schemaSQL*Plus: Release 19.0.0.0.0 - Production on Sun Jun 2 12:06:13 2019Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0SQL> select name from v$database;NAME---------SH2SQL> select table_name from user_tables;TABLE_NAME--------------------------------------------------------------------------------USLOG$_PRODUCTSPRODUCTSCUSTOMERSORDERSLINEITEMSSQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name; TABLESPACE_NAME MB------------------------------ ----------C007TSP_SET_1 100C008TSP_SET_1 100C009TSP_SET_1 100C00ATSP_SET_1 100C00BTSP_SET_1 100C00CTSP_SET_1 100PRODUCTS_TSP 100SYSAUX 560SYSTEM 900TSP_SET_1 100UNDOTBS1 340TABLESPACE_NAME MB------------------------------ ----------USERS512 rows selected.SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions where tablespace_name like 'C%TSP_SET_1' order by tablespace_name; TABLE_NAME PARTITION_NAME TABLESPACE_NAME-------------------- -------------------- ------------------------------ORDERS CUSTOMERS_P7 C007TSP_SET_1CUSTOMERS CUSTOMERS_P7 C007TSP_SET_1LINEITEMS CUSTOMERS_P7 C007TSP_SET_1CUSTOMERS CUSTOMERS_P8 C008TSP_SET_1LINEITEMS CUSTOMERS_P8 C008TSP_SET_1ORDERS CUSTOMERS_P8 C008TSP_SET_1CUSTOMERS CUSTOMERS_P9 C009TSP_SET_1ORDERS CUSTOMERS_P9 C009TSP_SET_1LINEITEMS CUSTOMERS_P9 C009TSP_SET_1ORDERS CUSTOMERS_P10 C00ATSP_SET_1CUSTOMERS CUSTOMERS_P10 C00ATSP_SET_1LINEITEMS CUSTOMERS_P10 C00ATSP_SET_1CUSTOMERS CUSTOMERS_P11 C00BTSP_SET_1LINEITEMS CUSTOMERS_P11 C00BTSP_SET_1ORDERS CUSTOMERS_P11 C00BTSP_SET_1CUSTOMERS CUSTOMERS_P12 C00CTSP_SET_1LINEITEMS CUSTOMERS_P12 C00CTSP_SET_1ORDERS CUSTOMERS_P12 C00CTSP_SET_118 rows selected.DEMO Setup Download demo application setup zip file sdb_demo_app.zip from MOS note: Master Note for Handling Oracle Sharding - Oracle Database 12.2 Technology (Doc ID 2226341.1)Connect to the Shard Catalog database on host01 and execute the script demo_app_ext.sql located in the sdb_demo_app/sql directory (as sysdba). Note: Check the password used for app_schema user in the script and may need to change that Edit the demo.properties file and change hostname and shard director port – ensure right services are being called and comment out the services which have not been created in our environment name=democonnect_string=(ADDRESS_LIST=(LOAD_BALANCE=off)(FAILOVER=on)(ADDRESS=(HOST=host01)(PORT=1522)(PROTOCOL=tcp)))monitor.user=dbmonusermonitor.pass=TEZiPP4MsLLL#app.service.write=oltp_rw_srvc.cust_sdb.oradbcloudapp.service.write=oltp_rw_srvc.orasdb.oradbcloud#app.service.readonly=oltp_ro_srvc.cust_sdb.oradbcloudapp.service.readonly=oltp_ro_srvc.orasdb.oradbcloudapp.user=app_schemaapp.pass=app_schemaapp.threads=7Start the workload by executing command ./run.sh demo In another session start the monitoring tool via the command ./run.sh monitor Launch browser session and use the URL address localhost:8081When the application launches note the workload being distributed to all the shards and the RO workload and RW workloads being executedNote the CUSTOMERS table being populated at the same time on both SH1 and SH2 shard databases Terminate the run.sh shell script and note the row count in both shard databases SQL> select name from v$database;NAME---------SH1SQL> select count(*) from customers; COUNT(*)---------- 7262SQL> select count(*) from orders; COUNT(*)---------- 11198SQL> select count(*) from products; COUNT(*)---------- 480SQL> select name from v$database;NAME---------SH2SQL> select count(*) from customers; COUNT(*)---------- 6517SQL> select count(*) from orders; COUNT(*)---------- 13248SQL> select count(*) from products; COUNT(*)---------- 480 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download