1. Oracle Globally Distributed Database
  2. Oracle Globally Distributed Database Deployment
  3. Create the Shard Databases

Create the Shard Databases

The databases that will be used as shards should be created on their respective hosts.

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

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

Unlock GSMROOTUSER

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 distributed database commands. The password specified is used by GDSCTL during distributed database 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.

Unlock GSMUSER

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

Note that GSMUSER 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, 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 distributed database commands. The password specified is used later during distributed database 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> alter session set container=shard_pdb_name;
SQL> alter user gsmuser account unlock;

User altered.

SQL> grant SYSDG, SYSBACKUP to gsmuser;

Grant succeeded.

Create a PDB

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

Verify SPFILE Exists

Your shard database must use a server parameter file (SPFILE).

The SPFILE is required because the distributed database infrastructure uses internal database parameters to store configuration metadata, and that data must persist through database startup and shutdown operations. 

$ sqlplus / as sysdba

SQL> alter session set container=shard_pdb_name;
SQL> show parameter spfile

NAME     TYPE      VALUE
-------- --------- ------------------------------------
spfile   string    /u01/app/oracle/dbs/spfileshard.ora

Calculate and Set DB_FILES Appropriately

Set the DB_FILES database initialization parameter greater than or equal to the total number of chunks and/or tablespace sets required in the distributed database.

Each data chunk in a distributed database 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 in the CREATE SHARDCATALOG or ADD SHARDSPACE commands) and/or tablespace sets in the system.

Note that the number of chunks present on a shard in a Raft replication scenario is the total of all chunks that the shard is either leader or follower for.

To calculate the number of database files created for distributed database objects on a given shard:

Database files required = (Number of CREATE TABLESPACE SET SQL statements executed using SHARD DDL) * (Number of chunks present on the shard + 1)

DB_FILES must be set to at least the number of files used by the distributed database (above) PLUS non-distributed database files (system, sysaux, and so on) PLUS any extra needed by generic RDBMS code (5); therefore:

DB_FILES required in each shard = (Number of database files required, as calculated above) + Number of default database files(6) + 5

Check Character Sets

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 MOVE CHUNK commands. A requirement of that mechanism is that character sets must match on the source and destination. 

$ sqlplus / as sysdba

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

Set COMPATIBLE to 12.2.0 or Higher

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

$ sqlplus / as sysdba

SQL> alter session set container=shard_pdb_name;
SQL> show parameter compatible

NAME                   TYPE        VALUE
---------------------- ----------- -----------------
compatible             string      21.0.0

Set DB_CREATE_FILE_DEST

To support Oracle Managed Files, used by the 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> 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

Create DATA_PUMP_DIR

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

GSMADMIN_INTERNAL is an Oracle-supplied account that owns all of the distributed database 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 distributed database metadata and PL/SQL. 

$ sqlplus / as sysdba	

SQL> create or replace directory DATA_PUMP_DIR as ‘/u01/app/oracle/oradata’;

Directory created.

SQL> alter session set container=shard_pdb_name;
SQL> grant read, write on directory DATA_PUMP_DIR to gsmadmin_internal;

Grant succeeded.

Set DB_FILE_NAME_CONVERT

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-distributed 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> 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/

Set Up Oracle Net TNS Listener

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. 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, 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).

If standby shard databases will be used:

Enable Flashback Database

Enable Flashback Database if your distributed database will use Data Guard standby shard databases. 

$ sqlplus / as sysdba

SQL> alter session set container=shard_pdb_name;
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Enable FORCE LOGGING

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

$ sqlplus / as sysdba

SQL> alter session set container=shard_pdb_name;
SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

Set STANDBY_FILE_MANAGEMENT

If a standby shard databases will be part of the distributed database configuration, the STANDBY_FILE_MANAGEMENT database parameter should be set to AUTO to automatically create new database files on any standby shard databases.

If this parameter is set to MANUAL (which is the default), then new database files created during CREATE TABLESPACE commands, for example, will not be created on the standby. This will cause data unavailability and application errors if the standby ever becomes a primary database. 

$ sqlplus / as sysdba

SQL> alter session set container=shard_pdb_name;
SQL> show parameter standby_file_management

NAME TYPE VALUE
------------------------------------ ----------- ------------
standby_file_management string AUTO

If Raft replication will be used:

Set the following database initialization parameters:

  • FILESYSTEMIO_OPTIONS=setall - enables asynchronous I/O

  • UNDO_RETENTION=900 - this is the default, and it is automatically tuned, but it is recommend that you do not explicitly set this parameter to a very low value.