Before configuring backup for a sharded database, make sure the following prerequisites are met.

• Create a recovery catalog in a dedicated database.

  Before you can backup or restore a sharded database using GDSCTL, you must have access to a recovery catalog created in a dedicated database. This recovery catalog serves as a centralized RMAN repository for the shard catalog database and all of the shard databases.

  Note the following:

  • The version of the recovery catalog schema in the recovery catalog database must be compatible with the sharded database version because RMAN has compatibility requirements for the RMAN client, the target databases, and the recovery catalog schema. For more information, see Oracle Database Backup and Recovery Reference, cross-referenced below.

  • The recovery catalog must not share a host database with the shard catalog because the shard catalog database is one of the target databases in the sharded database backup configuration, and RMAN does not allow the recovery catalog to reside in a target database.

  • It is recommended that you back up the recovery catalog backup periodically, following appropriate best practices.

  • The shard catalog database and all of the shard databases must be configured to use the same recovery catalog.

• Configure backup destinations for the shard catalog database and all of the shard databases.

  The backup destination types are either DISK or system backup to tape. The supported DISK destinations are NFS and Oracle ASM file systems.

  System backup to tape destinations require additional software modules to be installed on the database host. They must be properly configured to work with RMAN.

  If the shard catalog database or the shard databases are in Data Guard configurations, you can choose to back up either the primary or standby databases.

• RMAN connects to the target databases as specific internal users to do database backup and restore with the exception of the shard catalog.

  For the shard catalog, a common user in the CDB hosting the shard catalog PDB must be provided at the time when sharded database backup is configured. This user must be granted the SYSDG and SYSBACKUP privileges. If the CDB is configured to use local undo for its PDBs, the SYSBACKUP privilege must also be granted commonly.

  For the shard databases, the internal CDB common user, GSMROOTUSER, is used. This user must be unlocked in the shard CDB root databases and granted the SYSBACKUP privilege in addition to other privileges that the sharded database requires for GSMROOUSER. If the CDB is configured to use local undo for its PDBs, the SYSBACKUP privilege must be granted commonly to GSMROOTUSER, meaning the CONTAINER=ALL clause must be used when granting the SYSBACKUP privilege.

• All of the GDSCTL commands for sharded database backup and restore operations require the shard catalog database to be open. If the shard catalog database itself must be restored, you must manually restore it.
• You are responsible for offloading backups to tape or other long-term storage media and following the appropriate data retention best practices.

Use the GDSCTL CONFIG BACKUP command to configure automated sharded database backups.

GDSCTL> config backup -rccatalog rccatalog_connect_string 
-destination "CATALOG::configure channel device type disk format '/tmp/rman/backups/%d_%U'" 
-destination "dbs1,dbs2:configure device type disk parallelism 2:configure channel 1 device type disk format '/tmp/rman/backups/1/%U';configure channel 2 device type disk format '/tmp/rman/backups/2/%U'" 
-starttime ALL:00:00 -retention 14 -frequency 7,1 -catpwd gsmcatuser_password -cdb catcdb_connect_string;

Configuring backup for database "v1908" ...

Updating wallet ...
The operation completed successfully
 
Configuring RMAN ...
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/tmp/rman/backups/%d_%u';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
 
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
...

Creating RMAN backup scripts ...
replaced global script full_backup 
replaced global script incremental_backup
...
Creating backup scheduler jobs ...
The operation completed successfully
 

Creating restore point creation job ...
The operation completed successfully

Configuring backup for database "v1908b" ...
 
Updating wallet ...
The operation completed successfully
 
Configuring RMAN ...
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
 
new RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/tmp/rman/backups/1/%u';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
...
 
Configuring backup for database "v1908d" ... 
Updating wallet ...
The operation completed successfully
 
Configuring RMAN ...
...

Recovery Manager complete.

You can enable or disable backups on all shards, or specific shards, shardspaces, or shardgroups.

GDSCTL> ENABLE BACKUP

GDSCTL> ENABLE BACKUP -shard dbs1

GDSCTL> DISABLE BACKUP -shard dbs1

Once configured and enabled, backup jobs run on the primary shard catalog database and the primary shards as scheduled.

After a backup job is configured, it is initially disabled. You must enable a backup job for it to run as scheduled. Use the GDSCTL commands ENABLE BACKUP and DISABLE BACKUP to enable or disable the jobs.

Backup jobs are scheduled based on the backup repeat intervals you configure for the level 0 and level 1 incremental backups, and the backup start time for the shard catalog database and the shards.

Two separate jobs are created for level 0 and level 1 incremental backups. The names of the jobs are AUTOMATED_SDB_LEVEL0_BACKUP_JOB and AUTOMATED_SDB_LEVEL1_BACKUP_JOB. Full logging is enabled for both jobs.

When running, the backup jobs find the configured backup target type (PRIMARY or STANDBY), figure out the correct target databases based on the backup target type, and then launch RMAN to back up the target databases. RMAN uses the shard wallets updated during the backup configuration for database connection authentication.

Note that sharded database chunk moves do not delay automated backups.

There are a few different ways to monitor the status of automated and on-demand backup jobs.

When GDSCTL CONFIG BACKUP is not provided with any parameters, it shows the current backup configuration.

GDSCTL> CONFIG BACKUP;

GDSCTL> config backup
Recovery catalog database user: rcadmin
Recovery catalog database connect descriptor: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=den02qxr)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cdb6_pdb1.example.com)))
Catalog database root container user: gsm_admin
Catalog database root container connect descriptor: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=den02qxr)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=v1908.example.com)))
Backup retention policy in days: 14
Level 0 incremental backup repeat interval in minutes: 10080
Level 1 incremental backup repeat interval in minutes: 1440
Level 1 incremental backup type : DIFFERENTIAL
Backup target type: STANDBY
Backup destinations:
catalog::channel device type disk format '/tmp/rman/backups/%d_%u'
dbs1,dbs2:device type disk parallelism 2:channel 1 device type disk format '/tmp/rman/backups/1/%u';channel 2 device type disk format '/tmp/rman/backups/2/%u'
catalog::configure channel device type disk format '/tmp/rman/backups/%d_%u'
dbs1,dbs2:configure device type disk parallelism 2:configure channel 1 device type disk format '/tmp/rman/backups/1/%u';configure channel 2 device type disk format '/tmp/rman/backups/2/%u'
Backup start times:
all:00:00

The GDSCTL RUN BACKUP command lets you start backups for the shard catalog database and a list of shards.

GDSCTL> RUN BACKUP -shard dbs1

Use GDSCTL command STATUS BACKUP to view the detailed state on the scheduled backup jobs in the specified shards. Command output includes the job state (enabled or disabled) and the job run details.

GDSCTL> status backup -catpwd -shard catalog,rdbmsb_cdb2_pdb1;
"GSMCATUSER" password:***
 
Retrieving scheduler backup job status for database "rdbms" ...
Jobs:
  Incremental Level 0 backup job is enabled
    Job schedule start time: 2020-07-27 00:00:00.000 -0400
    Job repeat interval: freq=daily;interval=1
  Incremental Level 1 backup job is enabled
    Job schedule start time: 2020-07-27 00:00:00.000 -0400
    Job repeat interval: freq=minutely;interval=60
  Global restore point create job is enabled
    Job schedule start time: 2020-07-27 23:59:55.960 -0400
    Job repeat interval: freq=hourly
 
Run Details:
  Incremental Level 1 backup job status: SUCCEEDED
    Job run actual start time: 2020-07-26 14:00:00.177 -0400
    Job run slave process ID: 9023
  Incremental Level 1 backup job status: SUCCEEDED
    Job run actual start time: 2020-07-26 22:00:01.305 -0400
Job run slave process ID: 59526
…
Global restore point create job status: SUCCEEDED
    Job run actual start time: 2020-07-27 15:28:37.603 -0400
    Job run slave process ID: 44227
  …
  Global restore point create job status: SUCCEEDED
    Job run actual start time: 2020-07-27 17:28:38.251 -0400
    Job run slave process ID: 57611
  
Retrieving scheduler backup job status for database "rdbmsb_cdb2_pdb1" ...
Jobs:
  Incremental Level 0 backup job is enabled
    Job schedule start time: 2020-07-28 00:00:00.000 -0400
    Job repeat interval: freq=daily;interval=1
  Incremental Level 1 backup job is enabled
    Job schedule start time: 2020-07-28 00:00:00.000 -0400
    Job repeat interval: freq=minutely;interval=60
 
Run Details:
  Incremental Level 1 backup job status: SUCCEEDED
    Job run actual start time: 2020-07-26 14:00:00.485 -0400
    Job run slave process ID: 9056
  …
  Incremental Level 1 backup job status: SUCCEEDED
    Job run actual start time: 2020-07-27 14:33:42.702 -0400
    Job run slave process ID: 9056
  Incremental Level 0 backup job status: SUCCEEDED
    Job run actual start time: 2020-07-27 00:00:01.469 -0400
    Job run slave process ID: 75176

GDSCTL> status backup -start_time "2020-07-26 12:00:00" -end_time "2020-07-27 00:00:00" -catpwd -shard catalog,rdbmsb_cdb2_pdb1;
"GSMCATUSER" password:***

Retrieving scheduler backup job status for database "rdbms" ...
Jobs:
  Incremental Level 0 backup job is enabled
    Job schedule start time: 2020-07-27 00:00:00.000 -0400
    Job repeat interval: freq=daily;interval=1
  Incremental Level 1 backup job is enabled
    Job schedule start time: 2020-07-27 00:00:00.000 -0400
    Job repeat interval: freq=minutely;interval=60
  Globa1 restore point create job is enabled
    Job schedule start time: 2020-07-27 23:59:55.960 -0400
    Job repeat interval: freq=hourly
 
Run Details:
  Incremental Level 1 backup job status: SUCCEEDED
    Job run actual start time: 2020-07-26 14:00:00.177 -0400
    Job run slave process ID: 9023
  …
  Incremental Level 1 backup job status: SUCCEEDED
    Job run actual start time: 2020-07-26 23:50:00.293 -0400
    Job run slave process ID: 74171
  Globa1 restore point create job status: SUCCEEDED
    Job run actual start time: 2020-07-26 14:28:38.263 -0400
    Job run slave process ID: 11987
  …
  Globa1 restore point create job status: SUCCEEDED
    Job run actual start time: 2020-07-26 23:28:37.577 -0400
    Job run slave process ID: 69451
 
Retrieving scheduler backup job status for database "rdbmsb_cdb2_pdb1" ...
Jobs:
  Incremental Level 0 backup job is enabled
    Job schedule start time: 2020-07-28 00:00:00.000 -0400
    Job repeat interval: freq=daily;interval=1
  Incremental Level 1 backup job is enabled
    Job schedule start time: 2020-07-28 00:00:00.000 -0400
    Job repeat interval: freq=minutely;interval=60
 
Run Details:
  Incremental Level 1 backup job status: SUCCEEDED
    Job run actual start time: 2020-07-26 14:00:00.485 -0400
    Job run slave process ID: 9056
  Incremental Level 1 backup job status: SUCCEEDED
    Job run actual start time: 2020-07-26 22:11:50.931 -0400
    Job run slave process ID: 9056

Use GDSCTL LIST BACKUP to list backups usable to restore a sharded database or a list of shards to a specific global restore point.

GDSCTL> LIST BACKUP -shard cdb2_pdb1 -restorepoint BACKUP_BEFORE_DB_MAINTENANCE

GDSCTL> list backup -shard cat1, cat2 -controlfile -summary 

Run the GDSCTL VALIDATE BACKUP command to validate sharded database backups against a specific global restore point for a list of shards. The validation confirms that the backups to restore the databases to the specified restore point are available and not corrupted.

GDSCTL> VALIDATE BACKUP -shard cat1,cat2 -controlfile -restorepoint BACKUP_BEFORE_DB_MAINTENANCE

Use the GDSCTL DELETE BACKUP command to delete backups from the recovery repository.

GDSCTL> DELETE BACKUP -shard cdb2_pdb1 -tag ODB_200414205057124_0400
"GSMCATUSER" password:

This will delete identified backups, would you like to continue [No]?y

Deleting backups for database "cdb2_pdb1" ...

A restore point for a sharded database that we call a global restore point, actually maps to a set of normal restore points in the individual primary databases in a sharded database.

GDSCTL> CREATE RESTOREPOINT 

GDSCTL> CREATE RESTOREPOINT -name CUSTOM_SDB_RESTOREPOINT_JOB

GDSCTL> LIST RESTOREPOINT

GDSCTL> LIST RESTOREPOINT -start_scn 2600000 -end_scn 2700000

GDSCTL> LIST RESTOREPOINT -start_time "2020-07-27 00:00:00" -end_time "2020-07-28 00:00:00"

The GDSCTL RESTORE BACKUP command lets you restore sharded database shards to a specific global restore point.

GDSCTL> RESTORE BACKUP -shard cdb2_pdb1 -restorepoint BACKUP_BEFORE_DB_MAINTENANCE –controlfile

There are numerous GDSCTL CONFIG commands that you can use to obtain the health status of individual shards, shardgroups, shardspaces, and shard directors.

Monitoring a shard is just like monitoring a normal database, and standard Oracle best practices should be used to monitor the individual health of a single shard. However, it is also important to monitor the overall health of the entire sharded environment. The GDSCTL commands can also be scripted and through the use of a scheduler and can be done at regular intervals to help ensure that everything is running smoothly. 

Oracle Enterprise Manager Cloud Control lets you discover, monitor, and manage the components of a sharded database.

Sharded database targets are found in the All Targets page.

The target home page for a sharded database shows you a summary of the sharded database components and their statuses.

To monitor sharded database components you must first discover them. See Discovering Sharded Database Components for more information.

• Discovering Sharded Database Components
  In Enterprise Manager Cloud Control, you can discover the shard catalog and shard databases, then add the shard directors, sharded databases, shardspaces, and shardgroups using guided discovery.

Use the SHARDS() clause to query Oracle-supplied tables to gather performance, diagnostic, and audit data from V$ views and DBA_* views.

The shard catalog database can be used as the entry point for centralized diagnostic operations using the SQL SHARDS() clause. The SHARDS() clause allows you to query the same Oracle supplied objects, such as V$, DBA/USER/ALL views and dictionary objects and tables, on all of the shards and return the aggregated results.

As shown in the examples below, an object in the FROM part of the SELECT statement is wrapped in the SHARDS() clause to specify that this is not a query to local object, but to objects on all shards in the sharded database configuration. A virtual column called SHARD_ID is automatically added to a SHARDS()-wrapped object during execution of a multi-shard query to indicate the source of every row in the result. The same column can be used in predicate for pruning the query.

A query with the SHARDS() clause can only be run on the shard catalog database.

SQL> SELECT shard_id, callspersec FROM SHARDS(v$servicemetric)
 WHERE service_name LIKE 'oltp%' AND group_id = 10;

SQL> SELECT table_name, partition_name, blocks, num_rows
 FROM SHARDS(dba_tab_partition) p
 WHERE p.table_owner= :1;

SQL> select ORA_SHARD_ID, INSTANCE_NAME from SHARDS(sys.v_$instance);

    ORA_SHARD_ID INSTANCE_NAME
    ------------ ----------------
               1 sh1
              11 sh2
              21 sh3
              31 sh4

SQL> select ORA_SHARD_ID, INSTANCE_NAME
 from SHARDS(sys.v_$instance)
 where ORA_SHARD_ID=21;

    ORA_SHARD_ID INSTANCE_NAME
    ------------ ----------------
              21 sh3

New shards can be added to an existing sharded database environment to scale out and to improve fault tolerance.

For fault tolerance, it is beneficial to have many smaller shards than a few very large ones. As an application matures and the amount of data increases, you can add an entire shard or multiple shards to the SDB to increase capacity. 

When you add a shard to a sharded database, if the environment is sharded by consistent hash, then chunks from existing shards are automatically moved to the new shard to rebalance the sharded environment.

When using user-defined sharding, populating a new shard with data may require manually moving chunks from existing shards to the new shard using the GDSCTL split chunk and move chunk commands.

Oracle Enterprise Manager Cloud Control can be used to help identify chunks that would be good candidates to move, or split and move to the new shard.

When you add a shard to the environment, verify that the standby server is ready, and after the new shard is in place take backups of any shards that have been involved in a move chunk operation.

The process of redistributing data between shards, triggered by a change in the number of shards, is called resharding. Automatic resharding is a feature of the system-managed sharding method that provides elastic scalability of a sharded database.

Sometimes data in a sharded database needs to be migrated from one shard to another. Data migration across shards is required in the following cases:

• When one or multiple shards are added to or removed from a sharded database

• When there is skew in the data or workload distribution across shards

The unit of data migration between shards is the chunk. Migrating data in chunks guaranties that related data from different sharded tables are moved together.

When a shard is added to or removed from a sharded database, multiple chunks are migrated to maintain a balanced distribution of chunks and workload across shards.

Depending on the sharding method, resharding happens automatically (system-managed) or is directed by the user (composite). The following figure shows the stages of automatic resharding when a shard is added to a sharded database with three shards.

Figure 12-1 Resharding a Sharded Database

Description of "Figure 12-1 Resharding a Sharded Database"

A particular chunk can also be moved from one shard to another, when data or workload skew occurs, without any change in the number of shards. In this case, chunk migration can be initiated by the database administrator to eliminate the hot spot.

RMAN Incremental Backup, Transportable Tablespace, and Oracle Notification Service technologies are used to minimize impact of chunk migration on application availability. A chunk is kept online during chunk migration. There is a short period of time (a few seconds) when data stored in the chunk is available for read-only access only.

FAN-enabled clients receive a notification when a chunk is about to become read-only in the source shard, and again when the chunk is fully available in the destination shard on completion of chunk migration. When clients receive the chunk read-only event, they can either repeat connection attempts until the chunk migration is completed, or access the read-only chunk in the source chunk. In the latter case, an attempt to write to the chunk will result in a run-time error.

It may become necessary to remove a shard from the sharded database environment, either temporarily or permanently, without losing any data that resides on that shard.

For example, removing a shard might become necessary if a sharded environment is scaled down after a busy holiday, or to replace a server or infrastructure within the data center. Prior to decommissioning the shard, you must move all of the chunks from the shard to other shards that will remain online. As you move them, try to maintain a balance of data and activity across all of the shards.

If the shard is only temporarily removed, keep track of the chunks moved to each shard so that they can be easily identified and moved back once the maintenance is complete.

You can add Data Guard standby shards to an Oracle Sharding environment; however there are some limitations.

1. Stop all global services on the shard using the GDSCTL command STOP SERVICE.
2. Disable all global services on the shard using the GDSCTL command DISABLE SERVICE.
3. Convert the shard to a snapshot standby using the procedure described in the Data Guard documentation.

   At this point, the shard remains part of the sharded database, but will not accept connections which use the sharding key.

You can manage database shards using Oracle Enterprise Manager Cloud Control

To manage shards using Cloud Control, they must first be discovered. Because each database shard is a database itself, you can use standard Cloud Control database discovery procedures.

The following topics describe shard management using Oracle Enterprise Manager Cloud Control:

• Validating a Shard
  Validate a shard prior to adding it to your Oracle Sharding deployment.
• Adding Primary Shards
  Use Oracle Enterprise Manager Cloud Control to add a primary shards to your Oracle Sharding deployment.
• Adding Standby Shards
  Use Oracle Enterprise Manager Cloud Control to add a standby shards to your Oracle Sharding deployment.
• Deploying Shards
  Use Oracle Enterprise Manager Cloud Control to deploy shards that have been added to your Oracle Sharding environment.

You can manage shards in your Oracle Sharding deployment using the GDSCTL command-line utility.

The following topics describe shard management using GDSCTL:

• Validating a Shard
  Before adding a newly created shard to a sharding configuration, you must validate that the shard has been configured correctly for the sharding environment.
• Adding Shards to a System-Managed Sharded Database
  Adding shards to a system-managed sharded database elastically scales the sharded database. In a system-managed sharded database chunks are automatically rebalanced after the new shards are added.
• Replacing a Shard
  If a shard fails, or if you just want to move a shard to a new host for other reasons, you can replace it using the ADD SHARD -REPLACE command in GDSCTL.

Do the following steps if you want to migrate shards from a traditional single-instance database to Oracle multitenant architecture. Also, you must migrate to a multitenant architecture before upgrading to Oracle Database 21c or later releases.

1. Back up each existing non-PDB shard, and then create a new CDB, and a PDB inside it.
2. Restore each shard to the PDB inside the CDB.
3. Run the GDSCTL ADD CDB command to add the new CDB.

   GDSCTL> add cdb -connect cdb_connect_string -pwd gsmrootuser_password

4. Run the GDSCTL ADD SHARD -REPLACE command, specifying the connect string of the PDB, shard_connect_string, which tells the sharding infrastructure to replace the old location of the shard with new PDB location.

   For system-managed or composite sharding, run ADD SHARD with the parameters shown here.

   
   GDSCTL> add shard -replace db_unique_name_of_non_PDB -connect shard_connect_string -pwd gsmuser_password 
   -shardgroup shardgroup_name -cdb cdb_name
   

   For user-defined sharding, the command usage is slightly different.

   GDSCTL> add shard -replace db_unique_name_of_non_PDB -connect shard_connect_string -pwd gsmuser_password 
   -shardspace shardspace_name -deploy_as db_mode -cdb cdb_name
   

Sometimes it becomes necessary to move a chunk from one shard to another. To maintain scalability of the sharded environment, it is important to attempt to maintain an equal distribution of the load and activity across all shards.

As the environment matures in a composite SDB, some shards may become more active and have more data than other shards. In order to keep a balance within the environment you must move chunks from more active servers to less active servers. There are other reasons for moving chunks:

• When a shard becomes more active than other shards, you can move a chunk to a less active shard to help redistribute the load evenly across the environment.

• When using range, list, or composite sharding, and you are adding a shard to a shardgroup.

• When using range, list, or composite sharding, and you a removing a shard from a shardgroup.

• After splitting a chunk it is often advisable to move one of the resulting chunks to a new shard.

When moving shards to maintain scalability, the ideal targets of the chunks are shards that are less active, or have a smaller portion of data. Oracle Enterprise Manager and AWR reports can help you identify the distribution of activity across the shards, and help identify shards that are good candidates for chunk movement.

You can move chunks from one shard to another in your Oracle Sharding deployment using Oracle Enterprise Manager Cloud Control.

1. From a shardspace management page, open the Shardspace menu, located in the top left corner of the Sharded Database target page, and choose Manage Shardgroups.
2. Select a shardgroup in the list and click Move Chunks.
3. In the Move Chunks dialog, select the source and destination shards between which to move the chunks.
4. Select the chunks that you want to move by choosing one of the options.

   • Enter ID List: enter a comma separates list of chunk ID numbers

   • Select IDs From Table: click the chunk IDs in the table

5. Indicate when the chunk move should occur.

   • Immediately: the chunk move is provisioned upon confirmation

   • Later: schedule the timing of the chunk move using the calendar tool in the adjacent field

6. Click OK.
7. Click the link in the Information box at the top of the page to view the provisioning status of the chunk move.

Splitting a chunk in a sharded database is required when chunks become too big, or only part of a chunk must be migrated to another shard.

Oracle Sharding supports the online split of a chunk. Theoretically it is possible to have a single chunk for each shard and split it every time data migration is required. However, even though a chunk split does not affect data availability, the split is a time-consuming and CPU-intensive operation because it scans all of the rows of the partition being split, and then inserts them one by one into the new partitions. For composite sharding, it is time consuming and may require downtime to redefine new values for the shard key or super shard key.

Therefore, it is recommended that you pre-create multiple chunks on each shard and split them either when the number of chunks is not big enough for balanced redistribution of data during re-sharding, or a particular chunk has become a hot spot.

Even with system-managed sharding, a single chunk may grow larger than other chunks or may become more active. In this case, splitting that chunk and allowing automatic resharding to move one of the resulting chunks to another shard maintains a more equal balanced distribution of data and activity across the environment.

Oracle Enterprise Manager heat maps show which chunks are more active than other chunks. Using this feature will help identify which chunks could be split, and one of the resulting chunks could then be moved to another shard to help rebalance the environment.

You can split chunks in your Oracle Sharding deployment using Oracle Enterprise Manager Cloud Control.

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. Select a shardspace in the list and click Split Chunks.
4. Select the chunks that you want to split by choosing one of the options.

   • Enter ID List: enter a comma separate list of chunk ID numbers

   • Select IDs From Table: click the chunk IDs in the table

5. Indicate when the chunk split should occur.

   • Immediately: the chunk split is provisioned upon confirmation

   • Later: schedule the timing of the chunk split using the calendar tool in the adjacent field

6. Click OK.
7. Click the link in the Information box at the top of the page to view the provisioning status of the chunk split.

Use Oracle Enterprise Manager Cloud Control to create and add a shard director to your Oracle Sharding deployment.

1. Open the Sharded Database menu, located in the top left corner of the Sharded Database target page, and choose Shard Directors.
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, or select a shard director from the list and click Create Like.

   Choosing Create opens the Add Shard Director dialog with default configuration values in the fields.

   Choosing Create Like opens the Add Shard Director dialog with configuration values from the selected shard director in the fields. You must select a shard director from the list to enable the Create Like option.

4. Enter the required information in the Add Shard Director dialog, and click OK.

   Note:

   If you do not want the shard director to start running immediately upon creation, you must uncheck the Start Shard Director After Creation checkbox.

5. Click OK on the confirmation dialog.
6. Click the link in the Information box at the top of the page to view the provisioning status of the shard director.

Use Oracle Enterprise Manager Cloud Control to edit a shard director configuration in your Oracle Sharding deployment.

1. Open the Sharded Database menu, located in the top left corner of the Sharded Database target page, and choose Shard Directors.
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. Select a shard director from the list and click Edit.

   Note that you cannot edit the shard director name, host, or Oracle home.

4. Edit the fields, enter the GSMCATUSER password, and click OK.
5. Click the link in the Information box at the top of the page to view the provisioning status of the shard director configuration changes.

Use Oracle Enterprise Manager Cloud Control to remove shard directors from your Oracle Sharding deployment.

1. Open the Sharded Database menu, located in the top left corner of the Sharded Database target page, and choose Shard Directors.
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. Select a shard director from the list and click Delete.
4. Click the link in the Information box at the top of the page to view the provisioning status of the shard director removal.

Create sharded database regions in your Oracle Sharding deployment using Oracle Enterprise Manager Cloud Control.

1. Open the Sharded Database menu, located in the top left corner of the Sharded Database target page, and choose Regions.
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.
4. Enter a unique name for the region in the Create Region dialog.
5. Optionally, select a buddy region from among the existing regions.
6. Click OK.
7. Click the link in the Information box at the top of the page to view the provisioning status of the region.

Edit sharded database region configurations in your Oracle Sharding deployment using Oracle Enterprise Manager Cloud Control.

1. Open the Sharded Database menu, located in the top left corner of the Sharded Database target page, and choose Regions.
2. If prompted, enter the shard catalog credentials, select the shard director under Shard Director Credentials, select the shard director host credentials, and log in.
3. Select a region from the list and click Edit.
4. Select or remove a buddy region, and click OK.
5. Click the link in the Information box at the top of the page to view the provisioning status of the region configuration changes.

Remove sharded database regions in your Oracle Sharding deployment using Oracle Enterprise Manager Cloud Control.

1. Open the Sharded Database menu, located in the top left corner of the Sharded Database target page, and choose Regions.
2. If prompted, enter the shard catalog credentials, select the shard director under Shard Director Credentials, select the shard director host credentials, and log in.
3. Select a region from the list and click Delete.
4. Click the link in the Information box at the top of the page to view the provisioning status of the region removal.

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

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.

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.

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;

Create shardgroups in your Oracle Sharding deployment using Oracle Enterprise Manager Cloud Control.

1. Select a shardspace to which to add the shardgroup.
2. Open the Shardspace menu, located in the top left corner of the shardspace target page, and choose Manage Shardgroups.
3. Click Create.
4. Enter values in the Create Shardgroup dialog, and click OK.
5. Click the link in the Information box at the top of the page to view the provisioning status of the shardgroup.

   For example, with the values entered in the screenshots above, the following command is run:

   GDSCTL Command: ADD SHARDGROUP -SHARDGROUP 'north' -SHARDSPACE 'shardspaceora'
    -REGION 'north' -DEPLOY_AS 'STANDBY'

Create services in your Oracle Sharding deployment using Oracle Enterprise Manager Cloud Control.

1. Open the Sharded Database menu, located in the top left corner of the Sharded Database target page, and choose Services.
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, or select a service from the list and click Create Like.

   Choosing Create opens the Create Service dialog with default configuration values in the fields.

   Choosing Create Like opens the Create Like Service dialog with configuration values from the selected service in the fields. You must select a service from the list to enable the Create Like option.

4. Enter the required information in the dialog, and click OK.

   Note:

   If you do not want the service to start running immediately upon creation, you must uncheck the Start service on all shards after creation checkbox.

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