Shard Management

About Adding Shards

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 sharded database to increase capacity.

When you add a shard to a sharded database, if the environment is sharded by consistent hash (system-managed), 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.

All of the DDLs that have been processed in the sharded database are applied in the same order to the shard before it becomes accessible to clients.

Work Flow for Adding Shards

Task 1: Create the shard databases

Before you add new shards to the sharded database configuration, you must install the Oracle Database software on the shard host systems and configure new databases for each primary and standby shard. Following these steps, referring to the linked topics for details:

Task 2: Validate the shard databases

Validate the shard database to verify that all of the shard database requirements have been met.

Validate the Shard Database using SQL*Plus
Validating a Shard using Enterprise Manager

Task 3: Add the shard databases to the configuration

Add each primary and standby shard to the sharded database configuration.

Add the Shard CDBs and Add the Shard PDBs using GDSCTL
Adding Primary Shards and Adding Standby Shards using Enterprise Manager

Task 4: Deploy the updated sharded database configuration

The final task is to deploy the updated sharded database configuration with the new shard and its standbys that you added.

Deploy the Sharding Configuration using GDSCTL
Deploying Shards using Enterprise Manager
Note that using Enterprise Manager sharded database management tools, you can optionally add and deploy a shard in a single step. However, if you choose not to deploy when adding the shard to the configuration, then use this procedure to deploy it.

Removing a Shard From the Pool

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.

If a shard is part of a sharded database configured for centralized automatic backup, you can remove backup artifacts from the database before removing the shard from the sharded database configuration. See Removing Backup Configuration from a Shard.

You can remove shards using GDSCTL or Oracle Enterprise Manager Cloud Control:

Oracle Database Global Data Services Concepts and Administration Guide for information about using the GDSCTL REMOVE SHARD command
Removing a Shard with Oracle Enterprise Manager Cloud Control

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.

When a shard database fails and the database can be recovered on the same host (using RMAN backup/restore or other methods), there is no need to replace the shard using the -replace parameter. If the shard cannot be recovered locally, or for some other reason you want to relocate the shard to another host or CDB, it is possible to create its replica on the new host. The sharding configuration can be updated with the new information by specifying the -replace option in GDSCTL command ADD SHARD.

The following are some cases where replacing a shard using ADD SHARD -REPLACE is useful.

The server (machine) where the shard database was running suffered irreparable damage and has to be replaced
You must replace a working server with another (more powerful, for example) server
A shard in a PDB was relocated from one CDB to another

In all of these cases the number of shards and data distribution across shards does not change after ADD SHARD is run; a shard is replaced with another shard that holds the same data. This is different from ADD SHARD used without the -replace option when the number of shards increases and data gets redistributed.

Upon running ADD SHARD -REPLACE, the old shard parameters, such as connect_string, db_unique_name, and so on, are replaced with their new values. A new database can have different db_unique_name than the failed one. When replacing a standby in a Data Guard configuration, the DBID of the new database must match the old one, as Data Guard requires all of the members of the configuration to have same DBID.

Before Using Replace

Before you use ADD SHARD -REPLACE, verify the following:

You have restored the database correctly (for example, using RMAN restore or other method). The new database shard must have the same sharding metadata as the failed one. Perform basic validation to ensure that you do not accidently provide a connect string to the wrong shard.
The shard that failed must have been in a deployed state before failure happened.
The shard that failed must be down when running the ADD SHARD -REPLACE command.
Fast-start failover observer must be running, if fast-start failover is enabled (which it is by default).

Replacing a Shard in a Data Guard Environment

The ADD SHARD -REPLACE command can only be used to replace a standby shard if the primary is still available. In order to replace a primary shard that failed, wait for one of the remaining standbys to switch over to the primary role before trying to replace the failed shard.

When a switchover is not possible (primary and all the standbys are down), you must run ADD SHARD -REPLACE for each member starting with the primary. This creates a new broker configuration from scratch.

In MAXPROTECTION mode with no standbys available, the primary database shuts down to maintain the protection mode. In this case, the primary database cannot be opened if the standby is not available. To handle the replace operation in this scenario, you must first downgrade Data Guard protection mode using DGMGRL (to MAXAVAILABILITY or MAXPERFORMANCE) by starting up the database in mounted mode. After the protection mode is set, open the primary database and perform the replace operation using GDSCTL. After the replace operation finishes you can revert the protection mode back to the previous level using DGMGRL.

When replacing a standby in a Data Guard configuration, the DBID of the new database must match the old one, as Data Guard requires all of the members of the configuration to have same DBID.

Example 10-1 Example 1: Replacing the primary shard with no standbys in the configuration

The initial configuration has two primary shards deployed and no standbys, as shown in the following example. The Availability for shdc is shown as a dash because it has gone down in a disaster scenario.

$ gdsctl config shard

Name    Shard Group    Status    State       Region    Availability
----    -----------    ------    -----       ------    ------------
shdb    dbs1           Ok        Deployed    east      ONLINE      
shdc    dbs1           Ok        Deployed    east      -

To recover, you create a replica of the primary from the backup, using RMAN for example. For this example, a new shard is created with db_unique_name shdd and connect string inst4. Now, the old shard, shdc, can be replaced with the new shard, shdd, as follows:

$ gdsctl add shard -replace shdc -connect inst4 -pwd password

DB Unique Name: SHDD

You can verify the configuration as follows:

$ gdsctl config shard

Name     Shard Group      Status    State       Region    Availability
----     -----------      ------    -----       ------    ------------
shdb     dbs1             Ok        Deployed    east      ONLINE
shdd     dbs1             Ok        Deployed    east      ONLINE

Example 10-2 Example 2: Replacing a standby shard

Note that you cannot replace a primary shard when the configuration contains a standby shard. In such cases, if the primary fails, the replace operation must be performed after one of the standbys becomes the new primary by automatic switchover.

The initial configuration has two shardgroups: one primary and one standby, each containing two shards, when the standby, shdd goes down.

$ gdsctl config shard

Name    Shard Group      Status    State       Region    Availability
----    -----------      ------    -----       ------    ------------
shdb    dbs1             Ok        Deployed    east      ONLINE
shdc    dbs1             Ok        Deployed    east      ONLINE
shdd    dbs2             Ok        Deployed    east      -
shde    dbs2             Ok        Deployed    east      READ ONLY

Create a new standby. Because the primary is running, this should be done using the RMAN DUPLICATE command with the FOR STANDBY option. Once the new standby, shdf, is ready, replace the old shard, shdd, as follows:

$ gdsctl add shard -replace shdd -connect inst6 -pwd password

DB Unique Name: shdf

You can verify the configuration as follows:

$ gdsctl config shard

Name    Shard Group      Status    State       Region    Availability
----    -----------      ------    -----       ------    ------------
shdb    dbs1             Ok        Deployed    east      ONLINE
shdc    dbs1             Ok        Deployed    east      ONLINE
shde    dbs2             Ok        Deployed    east      READ ONLY
shdf    dbs2             Ok        Deployed    east      READ ONLY

Common Errors

ORA-03770: incorrect shard is given for replace

This error is thrown when the shard given for the replace operation is not the replica of the original shard. Specifically, the sharding metadata does not match the metadata stored in the shard catalog for this shard. Make sure that the database was copied correctly, preferably using RMAN. Note that this is not an exhaustive check. It is assumed that you created the replica correctly.

ORA-03768: The database to be replaced is still up: shardc

The database to be replaced must not be running when running the add shard -replace command. Verify this by looking at the output of GDSCTL command config shard. If the shard failed but still shows ONLINE in the output, wait for some time (about 2 minutes) and retry.

Converting a Physical Standby to a Snapshot Standby

When using Oracle Data Guard as the replication method for a sharded database, Oracle Globally Distributed Database supports only the addition of a primary or physical standby shard; other types of Data Guard standby databases are not supported when adding a new standby to the sharded database.

However, a shard that is already part of the sharded database can be converted from a physical standby to a snapshot standby.

Stop all global services on the shard using the GDSCTL command STOP SERVICE.
Disable all global services on the shard using the GDSCTL command DISABLE SERVICE.
Convert the shard to a snapshot standby using the procedure described in the Oracle Data Guard documentation Converting a Physical Standby Database into a Snapshot Standby Database.

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

If the database is converted back to a physical standby, the global services can be enabled and started again, and the shard becomes an active member of the sharded database.

Migrating a Non-PDB Shard to a PDB

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.

Back up each existing non-PDB shard, and then create a new CDB, and a PDB inside it.
Restore each shard to the PDB inside the CDB.

Run the GDSCTL ADD CDB command to add the new CDB.

GDSCTL> add cdb -connect cdb_connect_string -pwd gsmrootuser_password

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
```

Managing Shards with Oracle Enterprise Manager Cloud Control

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.

Shards are managed from within their respective shardgroups. To manage a shard you must first navigate to the shardgroup which contains the shard you wish to manage. This can be done from the All Targets page or the Sharded Database page.

In the Shardgroup page, open the Shardgroup menu, located in the top left corner of the shardgroup target page, and choose Manage Shards.

Figure 10-6 Shardgroup Menu

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.

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 Globally Distributed Database deployment.

You can use Oracle Enterprise Manager Cloud Control to validate shards before adding them to your Oracle Globally Distributed Database deployment. You can also validate a shard after deployment to confirm that the settings are still valid later in the shard life cycle. For example, after a software upgrade you can validate existing shards to confirm correctness of their parameters and configuration.

To validate shards with Cloud Control, they should be existing targets that are being monitored by Cloud Control.

In the Shardgroup page, open the Shardgroup menu, located in the top left corner of the shardgroup target page, and choose Manage Shards.
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.
In the Manage Shards page, select a shard from the list and click Validate.
Click OK to confirm you want to validate the shard.
Click the link in the Information box at the top of the page to view the provisioning status of the shard.

When the shard validation script runs successfully check for errors reported in the output.

Adding Primary Shards

You can use Oracle Enterprise Manager Cloud Control to add a primary shards to your Oracle Globally Distributed Database deployment.

To add a primary shard using Cloud Control it must be an existing target being monitored by Cloud Control.

Note:

It is highly recommended that you validate a shard before adding it to your configuration. You can either use Cloud Control to validate the shard (see Validating a Shard), or run the DBMS_GSM_FIX.validateShard procedure against the shard using SQL*Plus (see Validate the Shard Database).

Open the Sharded Database menu, located in the top left corner of the Sharded Database target page, and choose Add Primary Shards.
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.
To add and deploy the shards in the same operation, select Deploy All Shards in the sharded database to deploy all shards added to the sharded database configuration.

The deployment operation validates the configuration of the shards and performs final configuration steps. Shards can be used only after they are deployed.
Click Add.
In the Database field of the Shard Details dialog, select the shard database target and click Select.
In composite sharding you can select also the shardspace to which to add the shard.
Configure any Advanced Settings:

Connect Descriptor (you can use the default Enterprise Manager connect descriptor or specify another connect descriptor in the Connect Descriptor box)

CPU Utilization Threshold (%)

Disk Threshold (ms)
Click OK.
Enter the GSMUSER credentials if necessary, then click Next.
Indicate when the ADD SHARD operation should occur, then click Next.
- Immediately: the shard is provisioned upon confirmation
- Later: schedule the timing of the shard addition using the calendar tool in the adjacent field
Review the configuration of the shard to be added and click Submit.
Click the link in the Information box at the top of the page to view the provisioning status of the shard.

If you did not select Deploy All Shards in the sharded database in the procedure above, deploy the shard in your Oracle Globally Distributed Database deployment as described in Deploying Shards.

Adding Standby Shards

Use Oracle Enterprise Manager Cloud Control to add a standby shards to your deployment.

To add a standby shard using Cloud Control the database must be an existing target being monitored by Cloud Control.

Note:

It is highly recommended that you validate a shard before adding it to your deployment. You can either use Cloud Control to validate the shard (see Validating a Shard), or run the DBMS_GSM_FIX.validateShard procedure against the shard using SQL*Plus (see Validate the Shard Database).

Open the Sharded Database menu, located in the top left corner of the Sharded Database target page, and choose Add Standby Shards.
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.
To add and deploy the shards in the same operation, select Deploy All Shards in the sharded database to deploy all shards added to the sharded database configuration.

The deployment operation validates the configuration of the shards and performs final configuration steps. Shards can be used only after they are deployed.
In the Primary Shards list, select a primary shard for which the new shard database will act as a standby.
At the top of the Standby Shards list, click Add.
In the Database field of the Shard Details dialog, select the standby shard.
Select the shardgroup to which to add the shard.

Only shardgroups that do not already contain a standby for the selected primary are shown.
Click OK.
Enter the GSMUSER credentials if necessary, then click Next.
Indicate when the ADD SHARD operation should occur, then click Next.
- Immediately: the shard is provisioned upon confirmation
- Later: schedule the timing of the shard addition using the calendar tool in the adjacent field
Review the configuration of the shard to be added and click Submit.
Click the link in the Information box at the top of the page to view the provisioning status of the shard.

If you did not select Deploy All Shards in the sharded database in the procedure above, deploy the shard in your deployment as described in Deploying Shards.

Deploying Shards

Use Oracle Enterprise Manager Cloud Control to deploy shards that have been added to your deployment.

Open the Sharded Database menu, located in the top left corner of the Sharded Database target page, and choose Deploy Shards.
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.
Select the Perform Rebalance check box to redistribute data between shards automatically after the shard is deployed.

If you want to move chunks to the shard manually, uncheck this box.
Click Submit.
Click the link in the Information box at the top of the page to view the provisioning status of the shard.

Editing a Shard

You can update a shard's CPU Utilization Threshold (%), Disk Threshold (ms), ONS Port, SCAN Address, Connect Descriptor, and GSMUSER Password in the Manage Shards page in Oracle Enterprise Management Cloud Control.

In the Shardgroup page, open the Shardgroup menu, located in the top left corner of the shardgroup target page, and choose Manage Shards.
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.
In the Manage Shards page, select a shard from the list and click Edit.
Click OK to save any changes made in the Edit Shard dialog.

Removing a Shard

You can remove a shard from your sharded database configuration in the Manage Shards page in Oracle Enterprise Management Cloud Control.

In the Shardgroup page, open the Shardgroup menu, located in the top left corner of the shardgroup target page, and choose Manage Shards.
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.
In the Manage Shards page, select a shard from the list and click Remove.

Use the Force option to remove the specified shard even if it is inaccessible and/or contains chunks. Using this option might result in a lower number of replicas or total unavailability for a certain range of data.
Click OK to confirm that you want to remove the shard.