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.