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 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 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 the CREATE SHARDCATALOG or ADD 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 AI 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 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=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 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 distributed database configurations, they will all share the same GSMCATUSER 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 common GSMCATUSER password. In this way, multiple shard catalogs can be configured across several CDBs, each having different GSMCATUSER 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 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> 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.