Note that this software installation is distinct from an Oracle Database installation. If you choose to co-locate the shard director software on the same host as the shard catalog database, it must be installed in a separate Oracle Home.

See Oracle Database Global Data Services Concepts and Administration Guide for information about installing the global service manager software.

The shard catalog database contains a small amount of sharding topology metadata and also contains all the duplicated tables that will be created for use by your sharded application. The catalog database also acts as a query coordinator to run cross-shard queries that select and aggregate data from more than one shard.

From a sharding perspective, the way in which you create or provision the catalog database is irrelevant. The database can be created with the Database Configuration Assistant (DBCA), manually using SQL*Plus, or provisioned from cloud infrastructure tools.

As long as you have a running Oracle Database Enterprise Edition instance on the shard catalog host with the following characteristics, it can used as the shard catalog.

• Create a legacy database or a pluggable database (PDB) for use as the shard catalog database. Using the root container (CDB$ROOT) of a container database (CDB) as the shard catalog database is not supported.

• Your shard catalog database must use a server parameter file (SPFILE). This is required because the sharding infrastructure uses internal database parameters to store configuration metadata, and that data needs to persist across database startup and shutdown operations.

  $ sqlplus / as sysdba
  
  SQL> show parameter spfile
  
  NAME     TYPE      VALUE
  -------- --------- ------------------------------------
  spfile   string    /u01/app/oracle/dbs/spfilecat.ora 

• The database character set and national character set must be the same because it is used for all of the shard databases. This means that the character set chosen must contain all possible characters that will be inserted into the shard catalog or any of the shards.

  This requirement arises from the fact that Oracle Data Pump is used internally to move transportable tablespaces from one shard to another during sharding MOVE CHUNK commands. A requirement of that mechanism is that character sets must match on the source and destination.

  $ sqlplus / as sysdba
  
  SQL> REM run the following command if using a CDB
  SQL> alter session set container=catalog_pdb_name;
  
  SQL> select * from nls_database_parameters 
    2  where parameter like '%CHARACTERSET';
  
  PARAMETER                                VALUE
  ---------------------------------------- --------------------
  NLS_NCHAR_CHARACTERSET                   AL16UTF16
  NLS_CHARACTERSET                         WE8DEC
  

• Because the shard catalog database can run multi-shard queries which connect to shards over database links, the OPEN_LINKS and OPEN_LINKS_PER_INSTANCE database initialization parameter values must be greater than or equal to the number of shards that will be part of the sharded database configuration.

  $ sqlplus / as sysdba	
  
  SQL> REM run the following command if using a CDB
  SQL> alter session set container=catalog_pdb_name;
  
  SQL> show parameter open_links
  
  NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ------------
  open_links                           integer     20
  open_links_per_instance              integer     20
  

• Set the DB_FILES database initialization parameter greater than or equal to the total number of chunks and/or tablespaces in the system.

  Each data chunk in a sharding configuration is implemented as a tablespace partition and resides in its own operating system data file. As a result, the DB_FILES database initialization parameter must be greater than or equal to the total number of chunks (as specified on the CREATE SHARDCATALOG or ADD SHARDSPACE commands) and/or tablespaces in the system.

  $ sqlplus / as sysdba	
  
  SQL> REM run the following command if using a CDB
  SQL> alter session set container=catalog_pdb_name;
  
  SQL> show parameter db_files
  
  NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ------------
  db_files                             integer     1024
  

• If you are planning to use CREATE SHARD to add shards to the sharding configuration, then the SHARED_SERVERS and DISPATCHERS database initialization parameters must be set to allow the Remote Scheduler Agent to connect to the catalog over an XDB connection. This is not necessary if ADD SHARD will be used.

  Specifically, SHARED_SERVERS must be greater than 0 (zero) to allow shared server connections to the shard catalog from the Remote Scheduler Agent processes running on the shard hosts. In addition, the value of DISPATCHERS must contain a service for XDB, based on the Oracle SID value.

  $ sqlplus / as sysdba	
  
  SQL> show parameter shared_servers
  
  NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ------------
  shared_servers                       integer     5
  
  SQL> show parameter dispatchers
  
  NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ----------------------
  Dispatchers                          string      (PROTOCOL=TCP), (PROTO
                                                   COL=TCP)(SERVICE=mysid
                                                   XDB)
  

  After setting the parameter values appropriately, run the ALTER SYSTEM REGISTER command to ensure that the XDB service is available for incoming connection requests.

• To support Oracle Managed Files, which is used by the sharding chunk management infrastructure, the DB_CREATE_FILE_DEST database parameter must be set to a valid value.

  This location is used during chunk movement operations (for example MOVE CHUNK or automatic rebalancing) to store the transportable tablespaces holding the chunk data. In addition, files described in Oracle Database Administrator’s Guide, "Using Oracle Managed Files," are also stored in this location as is customary for any Oracle database using Oracle Managed Files.

  $ sqlplus / as sysdba	
  
  SQL> REM run the following command if using a CDB
  SQL> alter session set container=catalog_pdb_name;
  
  SQL> show parameter db_create_file_dest
  
  NAME                  TYPE      VALUE
  --------------------- --------- -----------------------------
  db_create_file_dest   string    /u01/app/oracle/oradata 

• An Oracle-provided user account named GSMCATUSER must be unlocked and assigned a password inside the legacy database or PDB designated for the shard catalog. This account is used by the shard director processes to connect to the shard catalog database and perform administrative tasks in response to sharding commands.

  If you are using a PDB as the shard catalog, note that GSMCATUSER is a common user in the container database. As a result, its password is the same for CDB$ROOT and all PDBs in the CDB. If multiple PDBs in a single CDB are to be used as catalog databases for different sharding configurations, they will all share the same GSMCATUSER password which can be a security concern. To avoid this, host only one shard catalog PDB per CDB, and do not unlock the GSMCATUSER account in any other PDBs.

  The password you specify is used later during sharding topology creation in any ADD GSM commands that are issued. It never needs to be specified again because the shard director stores it securely in an Oracle Wallet and decrypts it only when necessary.

  The MODIFY GSM command can be used to update the stored password if it is later changed on the shard catalog database.

  $ sqlplus / as sysdba
  
  SQL> alter user gsmcatuser account unlock;
  
  User altered.
  
  SQL> alter user gsmcatuser identified by gsmcatuser_password;
  
  User altered.

  If you are using a PDB as the shard catalog, also run the following commands.

  SQL> alter session set container=catalog_pdb_name;
  SQL> alter user gsmcatuser account unlock;
  
  User altered.

• A shard catalog administrator account must be created, assigned a password, and granted privileges inside the legacy database or PDB designated as the shard catalog.

  This account is the administrator account for the sharding metadata in the shard catalog database. It is used to access the shard catalog using the GDSCTL utility when an administrator needs to makes changes to the sharded database topology or perform other administrative tasks.

  GDSCTL connects as this user to the shard catalog database when GDSCTL commands are run. The user name and password specified are used later in the CREATE SHARDCATALOG command. As with the GSMCATUSER account above, the user name and password are stored securely in an Oracle Wallet for later use. The stored credentials can be updated by issuing an explicit CONNECT command from GDSCTL to reset the values in the wallet.

  $ sqlplus / as sysdba
  
  SQL> REM run the following command if using a CDB
  SQL> alter session set container=catalog_pdb_name;
  
  SQL> create user mysdbadmin identified by mysdbadmin_password;
  
  User created.
  
  SQL> grant gsmadmin_role to mysdbadmin;
  
  Grant succeeded.
  

• Set up and run an Oracle Net TNS Listener at your chosen port (default is 1521) that can service incoming connection requests for the shard catalog legacy database or PDB.

  The TNS Listener can be created and configured in whatever way you wish. If the shard catalog is a PDB, depending on how the database was created, it may be necessary to explicitly create a database service that can allow for direct connection requests to the PDB without the need to use ALTER SESSION SET CONTAINER.

  To validate that the listener is configured correctly when using a PDB for the shard catalog, do the following using your newly created mysdbadmin account above and an appropriate connect string. Running LSNRCTL SERVICES lists all services currently available using the listener.

  $ sqlplus mysdbadmin/mysdbadmin_password@catalog_connect_string
  
  SQL> show con_name
  
  CON_NAME
  -----------------------
  catalog_pdb_name
  

  Once you confirm connectivity, make note of the catalog_connect_string above. It is used later in the configuration process in the GDSCTL CREATE SHARDCATALOG command. Typically, it will be of the form host:port/service_name (for example, cathost.example.com:1521/catalog_pdb.example.com).

After all of the above requirements have been met, the newly created database can now be the target of a GDSCTL CREATE SHARDCATALOG command.

For high availability and disaster recovery purposes, it is highly recommended that you also create one or more standby shard catalog databases. From a sharding perspective, as long as the above requirements are also met on the standby databases, and all changes to the primary shard catalog database are consistently applied to the standbys, there are no further sharding-specific configuration steps required.

As with the shard catalog database, the way in which you create or provision the shard databases is irrelevant from a sharding perspective. The database can be created with the Database Configuration Assistant (DBCA), manually using SQL*Plus, or provisioned from cloud infrastructure tools.

As long as you have a running Oracle Database Enterprise Edition instance on each shard host, with the following characteristics, it can be used as a shard.

• If the shard will be a PDB in a CDB, then an Oracle-provided user account named GSMROOTUSER must be unlocked and assigned a password inside CDB$ROOT of the database designated for a shard. In addition, this user must be granted the SYSDG and SYSBACKUP system privileges.

  The GSMROOTUSER account is used by GDSCTL and the shard director processes to connect to the shard database to perform administrative tasks in response to sharding commands. The password specified is used by GDSCTL during sharding topology creation in any ADD CDB commands that are issued. It is also be used by the shard director during the DEPLOY command to configure Oracle Data Guard (as necessary) on the shard databases. It never needs to be specified again by the user, because GDSCTL and the shard director store it securely in an Oracle Wallet and decrypt it only when necessary. The MODIFY CDB command can be used to update the stored password if it is later changed on the shard database.

  $ sqlplus / as sysdba
  
  SQL> alter user gsmrootuser account unlock;
  
  User altered.
  
  SQL> alter user gsmrootuser identified by gsmrootuser_password;
  
  User altered.
  
  SQL> grant SYSDG, SYSBACKUP to gsmrootuser;
  
  Grant succeeded.
  

• If the shard will be a PDB, then create a PDB for use as the shard database. Using the root container (CDB$ROOT) of a CDB as a shard is not supported.

• Your shard database must use a server parameter file (SPFILE). The SPFILE is required because the sharding infrastructure uses internal database parameters to store configuration metadata, and that data must persist through database startup and shutdown operations.

  $ sqlplus / as sysdba
  
  SQL> REM run the following command if using a CDB
  SQL> alter session set container=shard_pdb_name;
  
  SQL> show parameter spfile
  
  NAME     TYPE      VALUE
  -------- --------- ------------------------------------
  spfile   string    /u01/app/oracle/dbs/spfileshard.ora
  

• The database character set and national character set of the shard database must be the same as that used for the shard catalog database and all other shard databases. This means that the character set you choose must contain all possible characters that will be inserted into the shard catalog or any of the shards.

  This requirement arises from the fact that Oracle Data Pump is used internally to move transportable tablespaces from one shard to another during sharding MOVE CHUNK commands. A requirement of that mechanism is that character sets must match on the source and destination.

  $ sqlplus / as sysdba
  
  SQL> REM run the following command if using a CDB
  SQL> alter session set container=shard_pdb_name;
  
  SQL> select * from nls_database_parameters 
    2  where parameter like '%CHARACTERSET';
  
  PARAMETER                                VALUE
  ---------------------------------------- --------------------
  NLS_NCHAR_CHARACTERSET                   AL16UTF16
  NLS_CHARACTERSET                         WE8DEC
  

• The COMPATIBLE initialization parameter must be set to at least 12.2.0.

  $ sqlplus / as sysdba
  
  SQL> REM run the following command if using a CDB
  SQL> alter session set container=shard_pdb_name;
  
  SQL> show parameter compatible
  
  NAME                   TYPE        VALUE
  ---------------------- ----------- -----------------
  compatible             string      20.0.0
  

• Enable Flashback Database if your sharded database will use standby shard databases.

  $ sqlplus / as sysdba
  
  SQL> REM run the following command if using a CDB
  SQL> alter session set container=shard_pdb_name;
  
  SQL> select flashback_on from v$database;
  
  FLASHBACK_ON
  ------------------
  YES
  

• FORCE LOGGING mode must be enabled if your shard database will use standby shard databases.

  $ sqlplus / as sysdba
  
  SQL> REM run the following command if using a CDB
  SQL> alter session set container=shard_pdb_name;
  
  SQL> select force_logging from v$database;
  
  FORCE_LOGGING
  ---------------------------------------
  YES
  

• Set the DB_FILES database initialization parameter greater than or equal to the total number of chunks and/or tablespaces in the system.

  Each data chunk in a sharding configuration is implemented as a tablespace partition and resides in its own operating system datafile. As a result, the DB_FILES database initialization parameter must be greater than or equal to the total number of chunks (as specified in the CREATE SHARDCATALOG or ADD SHARDSPACE commands) and/or tablespaces in the system.

  $ sqlplus / as sysdba	
  
  SQL> REM run the following command if using a CDB
  SQL> alter session set container=shard_pdb_name;
  
  SQL> show parameter db_files
  
  NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ------------
  db_files                             integer     1024
  

• To support Oracle Managed Files, used by the sharding chunk management infrastructure, the DB_CREATE_FILE_DEST database parameter must be set to a valid value.

  This location is used during chunk movement operations (for example MOVE CHUNK or automatic rebalancing) to store the transportable tablespaces holding the chunk data. In addition, files described in Oracle Database Administrator’s Guide, "Using Oracle Managed Files," are also stored in this location as is customary for any Oracle database using Oracle Managed Files.

  $ sqlplus / as sysdba	
  
  SQL> REM run the following command if using a CDB
  SQL> alter session set container=shard_pdb_name;
  
  SQL> show parameter db_create_file_dest
  
  NAME                  TYPE      VALUE
  --------------------- --------- -----------------------------
  db_create_file_dest   string    /u01/app/oracle/oradata
  

• A directory object named DATA_PUMP_DIR must be created and accessible in the shard database from the GSMADMIN_INTERNAL account.

  GSMADMIN_INTERNAL is an Oracle-supplied account that owns all of the sharding metadata tables and PL/SQL packages. It should remain locked and is never used to login interactively. It’s only purpose is to own and control access to the sharding metadata and PL/SQL.

  $ sqlplus / as sysdba	
  
  SQL> REM run the following command if using a CDB
  SQL> alter session set container=shard_pdb_name;
  
  SQL> create or replace directory DATA_PUMP_DIR as ‘/u01/app/oracle/oradata’;
  
  Directory created.
  
  SQL> grant read, write on directory DATA_PUMP_DIR to gsmadmin_internal;
  
  Grant succeeded.
  

• To support file movement from shard to shard, the DB_FILE_NAME_CONVERT database parameter must be set to a valid value. This location is used when standby databases are in use, as is typical with non-sharded databases, and the location can also be used during chunk movement operations. For regular file system locations, it is recommended that this parameter end with a trailing slash (/).

  $ sqlplus / as sysdba
  
  SQL> REM run the following command if using a CDB
  SQL> alter session set container=shard_pdb_name;
  
  SQL> show parameter db_file_name_convert
  
  NAME TYPE VALUE
  ---------------------- --------- -----------------------------
  db_file_name_convert   string    /dbs/SHARD1/, /dbs/SHARD1S/

• An Oracle-provided user account named GSMUSER must be unlocked and assigned a password inside the PDB or legacy database designated as the shard database. In addition, this user must be granted the SYSDG and SYSBACKUP system privileges.

  If the shards are PDBs, note that GSMUSER is a common user in the CDB. As a result, its password is the same for CDB$ROOT and all PDBs in the CDB, which can be a security concern. To avoid this, host only one shard PDB per CDB, and do not unlock the GSMUSER account in any other PDBs.

  This account is used by the shard director processes to connect to the shard database and perform administrative tasks in response to sharding commands. The password specified is used later during sharding topology creation in any ADD SHARD commands that are issued. The password never needs to be specified again because the shard director stores it securely in an Oracle Wallet and only decrypts it when necessary. You can update the stored password using the MODIFY SHARD command if the password is later changed on the shard database.

  $ sqlplus / as sysdba
  
  SQL> alter user gsmuser account unlock;
  
  User altered.
  
  SQL> alter user gsmuser identified by gsmuser_password;
  
  User altered.
  
  SQL> REM run the following commands if using a CDB
  SQL> alter session set container=shard_pdb_name;
  
  SQL> alter user gsmuser account unlock;
  
  User altered.
  
  SQL> REM all cases run the following command
  
  SQL> grant SYSDG, SYSBACKUP to gsmuser;
  
  Grant succeeded.
  

• Set up and run an Oracle Net TNS Listener at your chosen port (default is 1521) that can service incoming connection requests for the shard PDB.

  The TNS Listener can be created and configured in whatever way you wish. If the shard is a PDB, depending on how the database was created, it may be necessary to explicitly create a database service that can allow for direct connection requests to the PDB without the need to use ALTER SESSION SET CONTAINER.

  To validate that the listener is configured correctly when using a PDB for the shard, run the following command using your newly unlocked GSMUSER account and an appropriate connect string. Running LSNRCTL SERVICES lists all services currently available using the listener.

  $ sqlplus gsmuser/gsmuser_password@shard_connect_string
  
  SQL> show con_name
  
  CON_NAME
  -----------------------
  shard_pdb_name
  

  Once you confirm connectivity, make note of the shard_connect_string above. It is used later in the configuration process in the GDSCTL ADD SHARD command. Typically, the connect string is in the form host:port/service_name (for example, shardhost.example.com:1521/shard_pdb.example.com).

Validate the Shard Database

To validate that all of the above requirements have been met, you can run an Oracle-supplied procedure, validateShard, that inspects the shard database and reports any issues encountered. This procedure is read-only and makes no changes to the database configuration.

The validateShard procedure can and should be run against primary, mounted (unopened) standby, and Active Data Guard standby databases that are part of the sharded database configuration. You can run validateShard multiple times and at any time during the sharded database life cycle, including after upgrades and patching.

To run the validateShard package, do the following:

$ sqlplus / as sysdba

SQL> REM run the following command if using a CDB
SQL> alter session set container=shard_pdb_name;

SQL> set serveroutput on
SQL> execute dbms_gsm_fix.validateShard

This procedure will produce output similar to the following:

INFO: Data Guard shard validation requested.
INFO: Database role is PRIMARY.
INFO: Database name is SHARD1.
INFO: Database unique name is shard1.
INFO: Database ID is 4183411430.
INFO: Database open mode is READ WRITE.
INFO: Database in archivelog mode.
INFO: Flashback is on.
INFO: Force logging is on.
INFO: Database platform is Linux x86 64-bit.
INFO: Database character set is WE8DEC. This value must match the character set of the catalog database.
INFO: 'compatible' initialization parameter validated successfully.
INFO: Database is a multitenant container database.
INFO: Current container is SHARD1_PDB1.
INFO: Database is using a server parameter file (spfile).
INFO: db_create_file_dest set to: '/u01/app/oracle/dbs'
INFO: db_recovery_file_dest set to: '/u01/app/oracle/dbs'
INFO: db_files=1000. Must be greater than the number of chunks and/or
tablespaces to be created in the shard.
INFO: dg_broker_start set to TRUE.
INFO: remote_login_passwordfile set to EXCLUSIVE.
INFO: db_file_name_convert set to: '/dbs/SHARD1/, /dbs/SHARD1S/'
INFO: GSMUSER account validated successfully.
INFO: DATA_PUMP_DIR is '/u01/app/oracle/dbs/9830571348DFEBA8E0537517C40AF64B'.

All output lines marked INFO are for informational purposes and should be validated as correct for your configuration.

All output lines marked ERROR must be fixed before moving on to the next deployment steps. These issues will cause errors for certain sharding operations if they are not resolved.

All output lines marked WARNING may or may not be applicable for your configuration. For example, if standby databases will not be used for this particular deployment, then any warnings related to standby databases or recovery can be ignored. This is especially true for non-production, proof-of-concept, or application development deployments. Review all warnings and resolve as necessary.

Once all of the above steps have been completed, the newly created database can now be the target of a GDSCTL ADD SHARD command.

For high availability and disaster recovery purposes, it is highly recommended that you also create one or more standby shard databases. From a sharding perspective, as long as the above requirements are also met on the standby databases, and all changes to the primary shard database are applied to the standbys, the standby database only needs to be added to the sharding configuration with an ADD SHARD command.

The sharded database topology consists of the sharding method, replication (high availability) technology, the default number of chunks to be present in the sharded database, the location and number of shard directors, the numbers of shardgroups, shardspaces, regions, and shards in the sharded database, and the global services that will be used to connect to the sharded database.

Keep the Global Data Services Control Utility (GDSCTL) Command Reference in the Oracle Database Global Data Services Concepts and Administration Guide on hand for information about usage and options for the GDSCTL commands used in the configuration procedures.

Run GDSCTL CONFIG SHARD to see the detailed status of each shard.

GDSCTL> config shard -shard cdb1_pdb1
Name: cdb1_pdb1
Shard Group: shardgroup_primary
Status: Ok
State: Deployed
Region: region1
Connection string:shard_connect_string
SCAN address:
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 20.0.0.0
Failed DDL:
DDL Error: ---
Management error:
Failed DDL id:
Availability: ONLINE
Rack:


Supported services
------------------------
Name Preferred Status
---- --------- ------
oltp_ro_srvc Yes Enabled
oltp_rw_srvc Yes Enabled 

Limitations

The following limitations apply to using TDE with Oracle Sharding.

• For MOVE CHUNK to work, all shard database hosts must be on the same platform.

• MOVE CHUNK cannot use compression during data transfer, which may impact performance.

• Only encryption on the tablespace level is supported. Encryption on specific columns is not supported.