Create the Shard Catalog Database
Use the following information and guidelines to create the shard catalog database.
The shard catalog database contains a small amount of distributed database topology metadata and also contains all the duplicated tables that will be created for use by your sharded application. The shard 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 distributed database 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 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 distributed database 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
GDSCTL 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=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
andOPEN_LINKS_PER_INSTANCE
database initialization parameter values must be greater than or equal to the number of shards that will be part of the distributed database configuration.$ sqlplus / as sysdba 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 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 on theCREATE SHARDCATALOG
orADD SHARDSPACE
commands) and/or tablespaces in the system.$ sqlplus / as sysdba SQL> alter session set container=catalog_pdb_name; SQL> show parameter db_files NAME TYPE VALUE ------------------------------------ ----------- ------------ db_files integer 1024
-
To support Oracle Managed Files, which is 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=catalog_pdb_name; SQL> show parameter db_create_file_dest NAME TYPE VALUE --------------------- --------- ----------------------------- db_create_file_dest string /u01/app/oracle/oradata
-
If a standby catalog database will be part of the distributed database configuration, the
STANDBY_FILE_MANAGEMENT
database parameter should be set to in order to automatically create new database files on any standby catalog databases.If this parameter is set to
MANUAL
(which is the default), then new database files created duringCREATE 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=catalog_pdb_name; SQL> show parameter standby_file_management NAME TYPE VALUE ------------------------------------ ----------- ------------ standby_file_management stirng AUTO
-
An Oracle-provided user account named
GSMCATUSER
must be unlocked and assigned a password inside the 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 distributed database commands.Note that
GSMCATUSER
is a common user in the container database. As a result, its password is the same forCDB$ROOT
and all PDBs in the CDB. If multiple PDBs in a single CDB are to be used as catalog databases for different distributed database configurations, they will all share the sameGSMCATUSER
password which can be a security concern. To avoid this potential security concern, configure a separate CDB to host each shard catalog. Each CDB should contain only a single shard catalog PDB so that no other PDBs in the CDB can share the commonGSMCATUSER
password. In this way, multiple shard catalogs can be configured across several CDBs, each having differentGSMCATUSER
passwords.The password you specify is used later during distributed database 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. 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 PDB designated as the shard catalog.
This account is the administrator account for the distributed database 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 distributed database topology or perform other administrative tasks.GDSCTL
connects as this user to the shard catalog database whenGDSCTL
commands are run. The user name and password specified are used later in theCREATE SHARDCATALOG
command. As with theGSMCATUSER
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 explicitCONNECT
command fromGDSCTL
to reset the values in the wallet.$ sqlplus / as sysdba 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 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, 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 distributed database 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 distributed database-specific configuration steps required.