Shardspace Management

You can add, edit, and remove shardspaces in your deployment with Oracle Enterprise Manager Cloud Control.

Creating a Shardspace

Create shardspaces in your composite sharded database deployment using Oracle Enterprise Manager Cloud Control.

Only databases that are sharded using the composite method can have more than one shardspace. A system-managed sharded database can have only one shardspace.

1. Open the Sharded Database menu, located in the top left corner of the Sharded Database target page, and choose Shardspaces.
2. If prompted, enter the shard catalog credentials, select the shard director to manage under Shard Director Credentials, select the shard director host credentials, and log in.
3. Click Create.

   Note:

   This option is disabled in the Shardspaces page for a system-managed sharded database.
4. Enter the values in the fields in the Add Shardspace dialog, and click OK.

   • Name: enter a unique name for the shardspace (required)

   • Chunks: Enter the number of chunks that should be created in the shardspace (default 120)

   • Protection Mode: select the Data Guard protection mode (default Maximum Performance)

5. Click the link in the Information box at the top of the page to view the provisioning status of the shardspace.

When the shardspace is created successfully it appears in the Shardspaces list. You might need to refresh the page to see the updates.

Adding a Shardspace to a Composite Sharded Database

Learn to create a new shardspace, add shards to the shardspace, create a tablespace set in the new shardspace, and add a partitionset to the sharded table for the added shardspace. Then verify that the partitions in the tables are created in the newly added shards in the corresponding tablespaces.

To add a new shardspace to an existing sharded database, make sure that the composite sharded database is deployed and all DDLs are propagated to the shards.

1. Create a new shardspace, add shards to the shardspace, and deploy the environment.
   1. Connect to the shard catalog database.

      GDSCTL> connect mysdbadmin/mysdbadmin_password

   2. Add a shardspace and add a shardgroup to the shardspace.

      GDSCTL> add shardspace -chunks 8 -shardspace cust_asia
      GDSCTL> add shardgroup -shardspace cust_asia -shardgroup asia_shgrp1 -deploy_as primary -region region3

   3. Add shards

      GDSCTL> add shard -shardgroup asia_shgrp1 –connect shard_host:TNS_listener_port/shard_database_name –pwd GSMUSER_password
      GDSCTL> add shard asia_shgrp1 –connect shard_host:TNS_listener_port/shard_database_name –pwd GSMUSER_password

   4. Deploy the environment.

      GDSCTL> deploy

   Running DEPLOY ensures that all of the previous DDLs are replayed on the new shards and all of the tables are created. The partition is created in the default SYS_SHARD_TS tablespace.
2. On the shard catalog create the tablespace set for the shardspace and add partitionsets to the sharded root table.
   1. Create the tablespace set.

      SQL> CREATE TABLESPACE SET
        TSP_SET_3 in shardspace cust_asia using template
        (datafile size 100m autoextend on next 10M maxsize
         unlimited extent management
         local segment space management auto );

   2. Add the partitionset.

      SQL> ALTER table customers add PARTITIONSET asia VALUES ('ASIA”') TABLESPACE SET TSP_SET_3 ;

   3. When lobs are present, create the tablespace set for lobs and mention the lob storage information in the add partitionset command.

      SQL> alter table customers add partitionset asia VALUES ('ASIA') tablespace set TSP_SET_3 lob(docn) store as (tablespace set LOBTSP_SET_4)) ;

   4. When the root table contains subpartitions, use the store as clause to specify the tablespace set for the subpartitions.

      SQL> alter table customers add partitionset asia VALUES ('ASIA') tablespace set TSP_SET_3 subpartitions store in(SUB_TSP_SET_1, SUB_TSP_SET_2);

   The ADD PARTITIONSET command ensures that the child tables are moved to the appropriate tablespaces.
3. Verify that the partitions in the new shardspace are moved to the new tablespaces.

   Connect to the new shards and verify that the partitions are created in the new tablespace set.

   SQL> select table_name, partition_name, tablespace_name, read_only from dba_tab_partitions;