You can deploy a sharded database, by creating the shards at the same time for a new database, or by adding the shards from a preexisting database.

Oracle Sharding supports two deployment methods. The first method is with the CREATE SHARD command, where the creation of shards and the replication configuration are automatically done by the Oracle Sharding management tier. This method cannot be used in a multitenant architecture where PDBs are used as shards.

The second deployment method is with the ADD SHARD command. If your database creation standards require that you deploy the SDB using your own pre-created databases, the ADD SHARD deployment method supports this requirement by simply adding your prebuilt database shards.

You can use a multitenant container database (CDB) containing pluggable databases (PDBs) as shards and shard catalogs in your Oracle Sharding configuration.

To support consolidation of databases on under-utilized hardware, for ease of management, or geographical business requirements, you can use PDBs in CDBs as database shards. For example, for database consolidation, you can add other, non-shard or shard PDBs to the CDB containing a shard PDB. A CDB can contain shard PDBs from different sharded databases, each with their own separate catalog databases. CDBs can support multiple PDB shards from different sharded databases; however, this support is limited to only one PDB shard from a given sharded database for each CDB.

To add a shard PDB to the sharded database configuration, you should first add the CDB in which that shard PDB is contained to the shard catalog. The GDSCTL command ADD CDB is used to add a pre-created CDB to the shard catalog. Then, use the GDSCTL ADD SHARD command with the -cdb option to add shards which are a PDB contained within a CDB to the sharded database during deployment.

The following example adds a CDB with unique name db11 to the shard catalog and then adds it to shardgroup shgrp1 in a sharded database configuration.

GDSCTL> add cdb -connect CDB$ROOT_connect_string -pwd GSMROOTUSER_password
GDSCTL> add shard -cdb db11 -connect PDB_connect_string –shardgroup shgrp1
 -deploy_as active_standby -pwd GSMUSER_password

Use CONFIG CDB to display information about the CDB in the shard catalog.

GDSCTL> config cdb

Name: tstsdbyb 
Connection string: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cdb1host)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=cdb1.example.com))) 
SCAN address: 
ONS remote port: 0 
Disk Threshold, ms: 20 
CPU Threshold, %: 75 
Version: 18.0.0.0 
Rack:

GDSCTL> modify cdb -shard cdb1 -pwd new_GSMROOTUSER_password

GDSCTL> remove cdb -cdb cdb1

To deploy a system-managed sharded database you create shardgroups and shards, create and configure the databases to be used as shards, execute the DEPLOY command, and create role-based global services.

1. If you are using the ADD SHARD method described in Introduction to Sharded Database Deployment you must first create the databases to be used as shards on their respective hosts.

   The shard databases must have the following characteristics:

   • They must have an associated TNS Listener on each host

   • The GSMUSER account must be unlocked with a known password

   • SYSDG and SYSBACKUP privileges must be granted to GSMUSER

   • The primary and standby databases must be configured as such

   • Redo apply should be set up between the corresponding primary and standby databases

   • Flashback and force logging should be enabled

   • The compatible parameter must be set to at least 12.2.0

   • A server parameter file (SPFILE) must be in use

   • A DATA_PUMP_DIR directory object must be created in each database and must point to a valid directory

   You must then validate that a database is correctly set up for sharding. Run the following statements against each database, while logged in as SYS, before adding it to the configuration.

   SQL> set serveroutput on
   SQL> execute DBMS_GSM_FIX.validateShard

   Screen output will include INFO, WARNING, and ERROR information that needs to be analyzed for any issues. All WARNING and ERROR messages must be resolved. Re-run validateShard() after making changes to confirm the configuration.

2. Connect to the shard director host.

   $ ssh os_user@shard_director1_host
   

3. Set the global service manager for the current session, and specify the credentials to administer it.

   $ gdsctl
   GDSCTL> set gsm -gsm sharddirector1
   GDSCTL> connect mysdbadmin/mysdbadmin_password

4. Add a shardgroup for the primary shards.

   In this example the shardgroup is named primary_shardgroup, and is part of a Data Guard replication solution.

   GDSCTL> add shardgroup -shardgroup primary_shardgroup -deploy_as primary
    -region region1

   The following example shows the shardgroup creation for an Oracle GoldenGate replication solution.

   GDSCTL> add shardgroup -shardgroup shardgroup1 -region region1 -repfactor 2

5. Add a shardgroup for the standby shards.

   In this example the shardgroup is named standby_shardgroup, and is created for an Active Data Guard active standby using the -deploy_as parameter.

   GDSCTL> add shardgroup -shardgroup standby_shardgroup -deploy_as active_standby
    -region region2 

   Adding a second shardgroup in an Oracle GoldenGate configuration might look like the following example.

   GDSCTL> add shardgroup -shardgroup shardgroup2 -region region2 -repfactor 2 

6. Add each shard’s host address to the valid node checking for registration (VNCR) list in the catalog, then create or add the shard in either the primary or standby shardgroup, as shown in the following examples.

   Note:

   The valid node checking for registration (VNCR) feature provides the ability to configure and dynamically update a set of IP addresses, host names, or subnets from which registration requests are allowed by the shard directors. Database instance registration with a shard director succeeds only when the request originates from a valid node. By default, the shard management tier (based on Oracle Global Data Services framework) automatically adds a VNCR entry for the host on which a remote database is running each time create shard or add shard is executed. The automation (called auto-VNCR) finds the public IP address of the target host, and automatically adds a VNCR entry for that IP address. If the host has multiple public IP addresses, then the address on which the database registers may not be the same as the address which was added using auto-VNCR and , as a result, registration many be rejected. If the target database host has multiple public IP addresses, it is advisable that you configure VNCR manually for this host using the add invitednode or add invitedsubnet commands in GDSCTL.

   If there are multiple net-cards on the target host (/sbin/ifconfig returns more than one public interface), use add invitednode to be safe (after finding out which interface will be used to route packets).

   If there is any doubt about registration, then use config vncr and use add invitednode as necessary. There is no harm in doing this, because if the node is added already, auto-VNCR ignores it, and if you try to add it after auto-VNCR already added it, you will get a warning stating that it already exists.

   The following example shows how to create four shards using the CREATE SHARD command, using a Data Guard high availability solution, two of which are in the primary shardgroup and two in the standby shardgroup. The credential_name is the operating system credential you created in the shard catalog for the destination host.

   While creating the shards, you can also set the SYS password in the create shard using -sys_password as shown in the following example. This sets the SYS password after the shards are created when running DEPLOY.

   There are other optional parameters for CREATE SHARD that allow you to customize the database parameters, storage and file locations, listener port numbers, and so on, which are documented in the Oracle Database Global Data Services Concepts and Administration Guide appendices.

   GDSCTL> add invitednode shard_host_1 
   GDSCTL> create shard -shardgroup primary_shardgroup -destination shard_host_1
    -credential credential_name -sys_password sys_password
   
   GDSCTL> add invitednode shard_host_2
   GDSCTL> create shard -shardgroup standby_shardgroup -destination shard_host_2
    -credential credential_name -sys_password sys_password
   GDSCTL> add invitednode shard_host_3 
   GDSCTL> create shard -shardgroup primary_shardgroup -destination shard_host_3
    -credential credential_name -sys_password sys_password
   GDSCTL> add invitednode shard_host_4
   GDSCTL> create shard -shardgroup standby_shardgroup -destination shard_host_4
    -credential credential_name -sys_password sys_password

   In an Oracle GoldenGate replication solution, the shardgroups would not be designated as primary and standby because replication is handled at the chunk level and distributed among the shards within a shardgroup. However, a disaster recovery best practice is to replicate a shardgroup to one or more data centers. The following is an example of creating a shard with Oracle GoldenGate replication.

   GDSCTL> create shard -shardgroup shardgroup -destination shard_host
    -credential oracle_cred -netparam /home/oracle/netca_dbhome.rsp
    -gg_service shard_host_1:$ADMINSRVR_PORT/$GGHOME 
    -gg_password ggadmin_password
    -dbparamfile /home/oracle/dbparams01.tmp
    -dbtemplatefile /home/oracle/sharddb01.dbt

   If you are using the ADD SHARD method described in Introduction to Sharded Database Deployment, use the following command instead of the CREATE SHARD commands in the example above. If the shard database to be added is a pluggable database (PDB), you must use the -cdb option to ADD SHARD to specify which container database (CDB) the PDB shard is in. In addition, ADD CDB must be used before the ADD SHARD command to add the CDB to the catalog. See Oracle Database Global Data Services Concepts and Administration Guide for the syntax for ADD CDB and ADD SHARD. Note that in Oracle Database 18c, only one PDB in each CDB is allowed to be a shard.

   GDSCTL> add shard –shardgroup shgrp1
    –connect shard_host:TNS_listener_port/shard_database_name
    –pwd GSMUSER_password

7. Check the configuration from a shard director.

   Note that the shard names, sh1, sh2, sh3, and sh4, are system generated shard names when the CREATE SHARD method is used.

   GDSCTL> config
   Regions
   -----------------------
   region1
   region2
   
   GSMs
   -----------------------
   sharddirector1
   sharddirector2
   
   Sharded Database
   -----------------------
   cust_sdb
   
   Databases
   -----------------------
   sh1
   sh2
   sh3
   sh4
   
   Shard Groups
   -----------------------
   primary_shardgroup
   standby_shardgroup
   
   Shard spaces
   -----------------------
   shardspaceora
   
   Services
   -----------------------
   
   GDSCTL pending requests
   -----------------------
   Command          Object          Status
   -------          ------          ------
   
   Global properties
   -----------------------
   Name: oradbcloud
   Master GSM: sharddirector1
   DDL sequence #: 0
   
   GDSCTL> config shardspace
   SHARDSPACE       Chunks
   ----------       ------
   shardspaceora    12
   
   GDSCTL> config shardgroup
   Shard Group               Chunks    Region    SHARDSPACE
   -----------               ------    ------    ----------
   primary_shardgroup        12        region1   shardspaceora
   standby_shardgroup        12        region2   shardspaceora
   
   GDSCTL> config vncr
   Name    Group ID 
   ----    --------
   shard_host_1 
   shard_host_2
   shard_host_3
   shard_host_4
   shard_catalog_host_IP
   
   GDSCTL> config shard
   Name     Shard Group          Status    State    Region    Availability
   ----     -----------          ------    -----    ------    ------------
   sh1      primary_shardgroup   U         none     region1   -
   sh2      standby_shardgroup   U         none     region2   -
   sh3      primary_shardgroup   U         none     region1   -
   sh4      standby_shardgroup   U         none     region2   -

8. Run the DEPLOY command to create the shards and the replicas.

   The DEPLOY command takes some time to run, approximately 15 to 30 minutes.

   GDSCTL> deploy

   When the CREATE SHARD method is used to create the shards, the DEPLOY command creates the primary and standby shards using DBCA. Archivelog and flashback, which is required for the Fast-Start Failover observers to perform standby reinstantiation, are enabled for all of the shards.

   Once the primary and standby shards are built, the DEPLOY command configures the Data Guard Broker with Fast-Start Failover enabled. The Fast-Start Failover observers are automatically started on the standby group’s shard director (sharddirector2 in this example).

9. Verify that all of the shards are deployed.

   GDSCTL> config shard
   Name    Shard Group           Status    State        Region    Availability
   ----    -----------           ------    -----        ------    ------------
   sh1      primary_shardgroup   Ok        Deployed     region1   ONLINE
   sh2      standby_shardgroup   Ok        Deployed     region2   READ_ONLY
   sh3      primary_shardgroup   Ok        Deployed     region1   ONLINE
   sh4      standby_shardgroup   Ok        Deployed     region2   READ_ONLY

10. Verify that all of the shards are registered.

    GDSCTL> databases
    Database: "sh1" Registered: Y State: Ok ONS: N. Role: PRIMARY
    Instances: 1 Region: region1
       Registered instances: 
         cust_sdb%1
    Database: "sh2" Registered: Y State: Ok ONS: N. Role: PH_STNDBY
    Instances: 1 Region: region2
       Registered instances: 
         cust_sdb%11
    Database: "sh3" Registered: Y State: Ok ONS: N. Role: PRIMARY
    Instances: 1 Region: region1
       Registered instances: 
         cust_sdb%21
    Database: "sh4" Registered: Y State: Ok ONS: N. Role: PH_STNDBY
    Instances: 1 Region: region2
       Registered instances: 
         cust_sdb%31

11. Check the configuration of a shard.

    GDSCTL> config shard -shard sh1
    Name: sh1
    Shard Group: primary_shardgroup
    Status: Ok
    State: Deployed
    Region: region1
    Connection string: shard_host_1:TNS_listener_port/sh1:dedicated
    SCAN address:
    ONS remote port: 0
    Disk Threshold, ms: 20
    CPU Threshold, %: 75
    Version: 18.0.0.0
    Last Failed DDL: 
    DDL Error: ---
    Failed DDL id:
    Availability: ONLINE
    
    Supported services
    -----------------------
    Name                Preferred Status
    ----                --------- ------

12. Add a global service that runs on all of the primary shards.

    The oltp_rw_srvc global service is a global data service that a client can use to connect to the sharded database. The oltp_rw_srvc service runs the OLTP transactions on the primary shards. Likewise, the oltp_ro_srvc global service is created to run read-only workload on the standby shards.

    GDSCTL> add service -service oltp_rw_srvc -role primary 
    
    GDSCTL> config service 
    
    Name          Network name                      Pool         Started Preferred all
    ----          ------------                      ----         ------- -------------
    oltp_rw_srvc  oltp_rw_srvc.cust_sdb.oracdbcloud cust_sdb     No      Yes

13. Start the oltp_rw_srvc global service.

    GDSCTL> start service -service oltp_rw_srvc
    
    GDSCTL> status service
    Service "oltp_rw_srvc.cust_sdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
       Instance "cust_sdb%1", name: "sh1", db: "sh1", region: "region1", status: ready.
       Instance "cust_sdb%21", name: "sh3", db: "sh3", region: "region1", status: ready.

14. Add a global service for the read-only workload to run on the standby shards.

    GDSCTL> add service -service oltp_ro_srvc -role physical_standby
    
    GDSCTL> config service 
    
    Name          Network name                      Pool         Started Preferred all
    ----          ------------                      ----         ------- -------------
    oltp_rw_srvc  oltp_rw_srvc.cust_sdb.oracdbcloud cust_sdb     Yes     Yes
    oltp_ro_srvc  oltp_ro_srvc.cust_sdb.oracdbcloud cust_sdb     No      Yes
    

15. Start the read-only service, and verify the status of the global services.

    GDSCTL> start service -service oltp_ro_srvc
    
    GDSCTL> status service
    Service "oltp_ro_srvc.cust_sdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
       Instance "cust_sdb%11", name: "sh2", db: "sh2", region: "region2", status: ready.
       Instance "cust_sdb%31", name: "sh4", db: "sh4", region: "region2", status: ready.
    Service "oltp_rw_srvc.cust_sdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
       Instance "cust_sdb%1", name: "sh1", db: "sh1", region: "region1", status: ready.
       Instance "cust_sdb%21", name: "sh3", db: "sh3", region: "region1", status: ready.

Create the schema user, tablespace set, sharded tables, and duplicated tables for the sharded database. Verify that the DDLs are propagated to all of the shards, and, while connected to the shards, verify the automatic Data Guard Broker configuration with Fast-Start Failover.

1. Connect to the shard catalog database, create the application schema user, and grant privileges and roles to the user.

   In this example, the application schema user is called app_schema.

   $ sqlplus / as sysdba
   
   SQL> alter session enable shard ddl;
   SQL> create user app_schema identified by app_schema_password;
   SQL> grant all privileges to app_schema;
   SQL> grant gsmadmin_role to app_schema;
   SQL> grant select_catalog_role to app_schema;
   SQL> grant connect, resource to app_schema;
   SQL> grant dba to app_schema;
   SQL> grant execute on dbms_crypto to app_schema;

2. Create a tablespace set for the sharded tables.

   SQL> CREATE TABLESPACE SET TSP_SET_1 using template
    (datafile size 100m autoextend on next 10M maxsize unlimited
     extent management local segment space management auto);
   

   Specifying the shardspace is optional when creating the tablespace set. If the shardspace is not specified in the command, the default shardspace, shardspaceora, is used.

3. If you use LOBs in a column, you can specify a tablespace set for the LOBs.

   SQL> CREATE TABLESPACE SET LOBTS1;

   Note:

   Tablespace sets for LOBS cannot be specified at the subpartitition level in system-managed sharding.

4. Create a tablespace for the duplicated tables.

   In this example the duplicated table is the Products table in the sample Customers-Orders-Products schema.

   SQL> CREATE TABLESPACE products_tsp datafile size 100m
    autoextend on next 10M maxsize unlimited
    extent management local uniform size 1m; 
   

5. Create a sharded table for the root table.

   In this example, the root table is the Customers table in the sample Customers-Orders-Products schema.

   SQL> CONNECT app_schema/app_schema_password
   
   SQL> ALTER SESSION ENABLE SHARD DDL;
   
   SQL> CREATE SHARDED TABLE Customers
     (
       CustId      VARCHAR2(60) NOT NULL,
       FirstName   VARCHAR2(60),
       LastName    VARCHAR2(60),
       Class       VARCHAR2(10),
       Geo         VARCHAR2(8),
       CustProfile VARCHAR2(4000),
       Passwd      RAW(60),
       CONSTRAINT pk_customers PRIMARY KEY (CustId),
       CONSTRAINT json_customers CHECK (CustProfile IS JSON)
     ) TABLESPACE SET TSP_SET_1
     PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;

   Note:

   If any columns contain LOBs, you can include the tablespace set in the parent table creation statement, as shown here.

   SQL> CREATE SHARDED TABLE Customers
     (
       CustId      VARCHAR2(60) NOT NULL,
       FirstName   VARCHAR2(60),
       LastName    VARCHAR2(60),
       Class       VARCHAR2(10),
       Geo         VARCHAR2(8),
       CustProfile VARCHAR2(4000),
       Passwd      RAW(60),
       image       BLOB,
       CONSTRAINT pk_customers PRIMARY KEY (CustId),
       CONSTRAINT json_customers CHECK (CustProfile IS JSON)
     ) TABLESPACE SET TSP_SET_1
       LOB(image) store as (TABLESPACE SET LOBTS1) 
     PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;

6. Create a sharded table for the other tables in the table family.

   In this example, sharded tables are created for the Orders and LineItems tables in the sample Customers-Orders-Products schema.

   The Orders sharded table is created first:

   SQL> CREATE SHARDED TABLE Orders
     (
       OrderId     INTEGER NOT NULL,
       CustId      VARCHAR2(60) NOT NULL,
       OrderDate   TIMESTAMP NOT NULL,
       SumTotal    NUMBER(19,4),
       Status      CHAR(4),
       CONSTRAINT  pk_orders PRIMARY KEY (CustId, OrderId),
       CONSTRAINT  fk_orders_parent FOREIGN KEY (CustId) 
       REFERENCES Customers ON DELETE CASCADE
     ) PARTITION BY REFERENCE (fk_orders_parent);
   

   Create the sequence used for the OrderId column.

   SQL> CREATE SEQUENCE Orders_Seq;

   Create a sharded table for LineItems

   SQL> CREATE SHARDED TABLE LineItems
     (
       OrderId     INTEGER NOT NULL,
       CustId      VARCHAR2(60) NOT NULL,
       ProductId   INTEGER NOT NULL,
       Price       NUMBER(19,4),
       Qty         NUMBER,
       CONSTRAINT  pk_items PRIMARY KEY (CustId, OrderId, ProductId),
       CONSTRAINT  fk_items_parent FOREIGN KEY (CustId, OrderId)
       REFERENCES Orders ON DELETE CASCADE
     ) PARTITION BY REFERENCE (fk_items_parent);

7. Create any required duplicated tables.

   In this example, the Products table is a duplicated object.

   SQL> CREATE DUPLICATED TABLE Products
     (
       ProductId  INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       Name       VARCHAR2(128),
       DescrUri   VARCHAR2(128),
       LastPrice  NUMBER(19,4)
     ) TABLESPACE products_tsp;

8. From the shard director host, verify that there were no failures during the creation of the tablespaces.

   GDSCTL> show ddl
   id    DDL Text                                  Failed shards
   --    --------                                  -------------
   5     grant connect, resource to app_schema
   6     grant dba to app_schema
   7     grant execute on dbms_crypto to app_s... 
   8     CREATE TABLESPACE SET  TSP_SET_1 usin...
   9     CREATE TABLESPACE products_tsp datafi...
   10    CREATE SHARDED TABLE Customers (   Cu...
   11    CREATE SHARDED TABLE Orders (   Order...
   12    CREATE SEQUENCE Orders_Seq;
   13    CREATE SHARDED TABLE LineItems (   Or...
   14    CREATE MATERIALIZED VIEW "APP_SCHEMA"...
   

   Note:

   The show ddl command output might be truncated. You can run SELECT ddl_text FROM gsmadmin_internal.ddl_requests on the catalog to see the full text of the statements.
9. Verify that there were no DDL errors on each of the shards.

   Run the config shard and config chunks commands on each shard in your configuration.

   GDSCTL> config shard -shard sh1
   Name: sh1
   Shard Group: primary_shardgroup
   Status: Ok
   State: Deployed
   Region: region1
   Connection string: shard_host_1:1521/sh1_host:dedicated
   SCAN address: 
   ONS remote port: 0
   Disk Threshold, ms: 20
   CPU Threshold, %: 75
   Version: 18.0.0.0
   Last Failed DDL: 
   DDL Error: ---
   Failed DDL id: 
   Availability: ONLINE
   
   Supported services
   ------------------------
   Name                                          Preferred Status    
   ----                                          --------- ------    
   oltp_ro_srvc                                  Yes       Enabled   
   oltp_rw_srvc                                  Yes       Enabled  
   
   GDSCTL> config chunks
   Chunks
   ------------------------
   Database                      From      To        
   --------                      ----      --        
   sh1                           1         6         
   sh2                           1         6         
   sh3                           7         12        
   sh4                           7         12 

10. Verify that the tablespaces of the tablespace set you created for the sharded table family and the tablespaces you created for the duplicated tables are created on all of the shards.

    The number of tablespaces in the tablespace set is based on the number of chunks you specified in the create shardcatalog command.

    The tablespace set with the first 6 chunks of the 12 that were specified in the shard catalog creation example, and the duplicated Products tablespace is shown in the following example.

    $ sqlplus / as sysdba
    
    SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files
     order by tablespace_name;
    
    TABLESPACE_NAME 		    MB
    ----------------------- ----------
    C001TSP_SET_1           100
    C002TSP_SET_1           100
    C003TSP_SET_1			      100
    C004TSP_SET_1			      100
    C005TSP_SET_1			      100
    C006TSP_SET_1			      100
    PRODUCTS_TSP            100
    SYSAUX                  650
    SYSTEM                  890
    SYS_SHARD_TS			      100
    TSP_SET_1			          100
    
    TABLESPACE_NAME 		     MB
    ------------------------ ----------
    UNDOTBS1			           105
    USERS					             5
    
    13 rows selected.
    

    Repeat this step on all of the shards in your configuration.

11. Verify that the chunks and chunk tablespaces were created on all of the shards.

    SQL> set linesize 140
    SQL> column table_name format a20
    SQL> column tablespace_name format a20
    SQL> column partition_name format a20
    SQL> show parameter db_unique_name
    
    NAME             TYPE        VALUE
    ---------------- ----------- ------------------------------
    db_unique_name   string      sh1
    
    SQL> select table_name, partition_name, tablespace_name
     from dba_tab_partitions
     where tablespace_name like 'C%TSP_SET_1'
     order by tablespace_name;
    
    
    TABLE_NAME       PARTITION_NAME   TABLESPACE_NAME
    ---------------- ---------------- --------------------
    ORDERS           CUSTOMERS_P1     C001TSP_SET_1
    CUSTOMERS        CUSTOMERS_P1     C001TSP_SET_1
    LINEITEM         CUSTOMERS_P1     C001TSP_SET_1
    CUSTOMERS        CUSTOMERS_P2     C002TSP_SET_1
    LINEITEMS        CUSTOMERS_P2     C002TSP_SET_1
    ORDERS           CUSTOMERS_P2     C002TSP_SET_1
    CUSTOMERS        CUSTOMERS_P3     C003TSP_SET_1
    ORDERS           CUSTOMERS_P3     C003TSP_SET_1
    LINEITEMS        CUSTOMERS_P3     C003TSP_SET_1
    ORDERS           CUSTOMERS_P4     C004TSP_SET_1
    CUSTOMERS        CUSTOMERS_P4     C004TSP_SET_1
    
    TABLE_NAME       PARTITION_NAME   TABLESPACE_NAME
    ---------------- ---------------- --------------------
    LINEITEMS        CUSTOMERS_P4     C004TSP_SET_1
    CUSTOMERS        CUSTOMERS_P5     C005TSP_SET_1
    LINEITEMS        CUSTOMERS_P5     C005TSP_SET_1
    ORDERS           CUSTOMERS_P5     C005TSP_SET_1
    CUSTOMERS        CUSTOMERS_P6     C006TSP_SET_1
    LINEITEMS        CUSTOMERS_P6     C006TSP_SET_1
    ORDERS           CUSTOMERS_P6     C006TSP_SET_1
    18 rows selected.
    

    Repeat this step on all of the shards in your configuration.

12. Connect to the shard catalog database and verify that the chunks are uniformly distributed.

    $ sqlplus / as sysdba
    
    SQL> set echo off
    SQL> SELECT a.name Shard, COUNT(b.chunk_number) Number_of_Chunks
      FROM gsmadmin_internal.database a, gsmadmin_internal.chunk_loc b
      WHERE a.database_num=b.database_num
      GROUP BY a.name
      ORDER BY a.name;
    
    SHARD			       NUMBER_OF_CHUNKS
    ------------------------------ ----------------
    sh1					      6
    sh2					      6
    sh3					      6
    sh4					      6

13. Verify that the sharded and duplicated tables were created.

    Log in as the application schema user on the shard catalog database and each of the shards.

    The following example shows querying the tables on a database shard as the app_schema user.

    $ sqlplus app_schema/app_schema_password
    Connected.
    
    SQL> select table_name from user_tables;
    
    TABLE_NAME
    -----------------------------------------------------------------------
    CUSTOMERS
    ORDERS
    LINEITEMS
    PRODUCTS
    
    4 rows selected.

14. Verify that the Data Guard Broker automatic Fast-Start Failover configuration was done.

    $ ssh os_username@shard_host_1
    $ dgmgrl
    
    DGMGRL> connect sys/password
    Connected to "sh1"
    Connected as SYSDG.
    DGMGRL> show configuration
    
    Configuration - sh1
    
      Protection Mode: MaxPerformance
      Members:
      sh1 - Primary database
        sh2 - (*) Physical standby database 
    
    Fast-Start Failover: ENABLED
    
    Configuration Status:
    SUCCESS   (status updated 15 seconds ago)
    
    DGMGRL> show database sh1
    
    Database - sh1
    
      Role:               PRIMARY
      Intended State:     TRANSPORT-ON
      Instance(s):
        sh1
    
    Database Status:
    SUCCESS
    
    DGMGRL> show database sh2
    
    Database - sh2
    
      Role:               PHYSICAL STANDBY
      Intended State:     APPLY-ON
      Transport Lag:      0 seconds (computed 0 seconds ago)
      Apply Lag:          0 seconds (computed 0 seconds ago)
      Average Apply Rate: 2.00 KByte/s
      Real Time Query:    ON
      Instance(s):
        sh2
    
    Database Status:
    SUCCESS
    
    DGMGRL> show fast_start failover
    
    Fast-Start Failover: ENABLED
    
      Threshold:          30 seconds
      Target:             sh2
      Observer:           shard_director_host
      Lag Limit:          30 seconds
      Shutdown Primary:   TRUE
      Auto-reinstate:     TRUE
      Observer Reconnect: (none)
      Observer Override:  FALSE
    
    Configurable Failover Conditions
      Health Conditions:
        Corrupted Controlfile          YES
        Corrupted Dictionary           YES
        Inaccessible Logfile            NO
        Stuck Archiver                  NO
        Datafile Write Errors          YES
    
      Oracle Error Conditions:
        (none)

15. Locate the Fast-Start Failover observers.

    Connect to the shard catalog database and run the following commands:

    $ sqlplus / as sysdba
    
    SQL> SELECT observer_state FROM gsmadmin_internal.broker_configs;
    
    OBSERVER_STATE
    --------------------------------------------------------------------------------
    GSM server SHARDDIRECTOR2. Observer started. 
    Log files at '/u01/app/oracle/product/18.0.0/gsmhome_1/network/admin/
    gsm_observer_1.log'.
    
    GSM server SHARDDIRECTOR2. Observer started. 
    Log files at '/u01/app/oracle/product/18.0.0/gsmhome_1/network.admin/
    gsm_observer_2.log'.

The system-managed sharded database (SDB) demo application simulates the workload of an online retail store. Use it to validate the setup of any system-managed (automatic sharding) SDB configuration. The demo application also provides a practical example of sharding concepts for administrators and developers new to database sharding.

The demo application assumes that a system-managed SDB environment was already created along with the CUSTOMER table-family. The environment may have any number of chunks and shards (database nodes). When run, the application will first populate the Products table and then start a one-hour workload that can be paused at any time by the administrator. The workload includes four types of transactions: create a customer order, lookup the list of orders, create a new product, and multi-shard query with report generation. All aspects of a sharded database configuration are exercised.

You can download the demo application, along with a README file that describes how to run and monitor it, from My Oracle Support Document 2184500.1.

The following procedure describes how to deploy a user-defined sharded database using the ADD SHARD command and an Oracle Active Data Guard high availability solution.

1. Because this procedure describes using the ADD SHARD method as detailed in Introduction to Sharded Database Deployment, you must first create the databases to be used as shards on their respective hosts.

   The shard databases must have the following characteristics:

   • They must have an associated TNS Listener on each host

   • The GSMUSER account must be unlocked with a known password

   • SYSDG and SYSBACKUP privileges must be granted to GSMUSER

   • The primary and standby databases must be configured as such

   • Redo apply should be set up between the corresponding primary and standby databases

   • Flashback and force logging should be enabled

   • The compatible parameter must be set to at least 12.2.0

   • A server parameter file (SPFILE) must be in use

   • A DATA_PUMP_DIR directory object must be created in each database and must point to a valid directory

   You must then validate that a database is correctly set up for sharding. Execute the following against each database before adding it to the configuration.

   SQL> set serveroutput on
   SQL> execute DBMS_GSM_FIX.validateShard

   Screen output will include INFO, WARNING, and ERROR information that needs to be analyzed for any issues. All WARNING and ERROR messages must be resolved. Re-run validateShard() after making changes to confirm the configuration.

2. Connect to the shard director host.

   $ ssh os_user@shard_director1_host
   

3. Set the global service manager for the current session, and specify the credentials to administer it.

   $ gdsctl
   GDSCTL> set gsm -gsm sharddirector1
   GDSCTL> connect mysdbadmin/mysdbadmin_password

4. Add shardspaces to the sharded database configuration for each customized grouping of shards your business case requires.

   The shardspaces contain the primary shard database and one or more active standbys.

   In this example the shardspaces are named shspace1 and shspace2. You can choose your own names.

   GDSCTL> add shardspace -shardspace shspace1 -protectmode maxavailability
   GDSCTL> add shardspace -shardspace shspace2 -protectmode maxavailability

5. Add each shard’s host address to the valid node checking for registration (VNCR) list in the catalog, then add the shard in either the primary or standby shardgroup, as shown in the following example.

   Note:

   The valid node checking for registration (VNCR) feature provides the ability to configure and dynamically update a set of IP addresses, host names, or subnets from which registration requests are allowed by the shard directors. Database instance registration with a shard director succeeds only when the request originates from a valid node. By default, the shard management tier (based on Oracle Global Data Services framework) automatically adds a VNCR entry for the host on which a remote database is running each time create shard or add shard is executed. The automation (called auto-VNCR) finds the public IP address of the target host, and automatically adds a VNCR entry for that IP address. If the host has multiple public IP addresses, then the address on which the database registers may not be the same as the address which was added using auto-VNCR and , as a result, registration many be rejected. If the target database host has multiple public IP addresses, it is advisable that you configure VNCR manually for this host using the add invitednode or add invitedsubnet commands in GDSCTL.

   If there are multiple net-cards on the target host (/sbin/ifconfig returns more than one public interface), use add invitednode to be safe (after finding out which interface will be used to route packets).

   If there is any doubt about registration, then use config vncr and use add invitednode as necessary. There is no harm in doing this, because if the node is added already, auto-VNCR ignores it, and if you try to add it after auto-VNCR already added it, you will get a warning stating that it already exists.

   The following example shows how to add four shards using the ADD SHARD command, the first two of which are primary and active standby shards in shardspace shspace1, and the second two are primary and active standbys in shardspace shspace2. Note that the primaries are given a region of region1 and the standbys are given region2.

   GDSCTL> add invitednode shard_host_1 
   GDSCTL> add shard -connect shard_host_1:1521/shard_database_name
    -shardspace shspace1 -deploy_as primary -pwd GSMUSER_password -region region1
   
   GDSCTL> add invitednode shard_host_2
   GDSCTL> add shard -connect shard_host_2:1521/shard_database_name
    -shardspace shspace1 -deploy_as active_standby -pwd GSMUSER_password
    -region region2
   
   GDSCTL> add invitednode shard_host_3 
   GDSCTL> add shard -connect shard_host_3:1521/shard_database_name
    -shardspace shspace2 -deploy_as primary -pwd GSMUSER_password -region region1
   
   GDSCTL> add invitednode shard_host_4
   GDSCTL> add shard -connect shard_host_4:1521/shard_database_name
    -shardspace shspace2 -deploy_as active_standby -pwd GSMUSER_password
    -region region2

   If the shard database to be added is a pluggable database (PDB), you must use the -cdb option to ADD SHARD to specify which container database (CDB) the PDB shard is in. In addition, ADD CDB must be used before the ADD SHARD command to add the CDB to the catalog. See Oracle Database Global Data Services Concepts and Administration Guide for the syntax for ADD CDB and ADD SHARD. Note that in Oracle Database 18c, only one PDB in each CDB is allowed to be a shard.

6. Check the configuration from a shard director.

   GDSCTL> config
   Regions
   -----------------------
   region1
   region2
   
   GSMs
   -----------------------
   sharddirector1
   sharddirector2
   
   Sharded Database
   -----------------------
   udef_shpool
   
   Databases
   -----------------------
   sh1
   sh2
   sh3
   sh4
   
   Shard spaces
   -----------------------
   shspace1
   shspace2
   
   Services
   -----------------------
   
   GDSCTL pending requests
   -----------------------
   Command          Object          Status
   -------          ------          ------
   
   Global properties
   -----------------------
   Name: oradbcloud
   Master GSM: sharddirector1
   DDL sequence #: 0
   
   
   GDSCTL> config vncr
   Name                    Group ID 
   ----                    --------
   shard_host_1 
   shard_host_2
   shard_host_3
   shard_host_4
   shard_catalog_host_IP
   
   
   GDSCTL> config shard
   Name     Shard space          Status    State    Region    Availability
   ----     -----------          ------    -----    ------    ------------
   sh1      shspace1             U         none     region1   -
   sh2      shspace1             U         none     region2   -
   sh3      shspace2             U         none     region1   -
   sh4      shspace2             U         none     region2   -

7. Run the DEPLOY command to create the shards and the replicas.

   The DEPLOY command takes some time to run, approximately 15 to 30 minutes.

   GDSCTL> deploy

   Once the primary and standby shards are built, the DEPLOY command configures the Data Guard Broker with Fast-Start Failover enabled. The Fast-Start Failover observers are automatically started on the standby group’s shard director (sharddirector2 in this example).

8. Verify that all of the shards are deployed.

   GDSCTL> config shard
   Name    Shard space           Status    State        Region    Availability
   ----    -----------           ------    -----        ------    ------------
   sh1      shspace1             Ok        Deployed     region1   ONLINE
   sh2      shspace1             Ok        Deployed     region2   READ_ONLY
   sh3      shspace2             Ok        Deployed     region1   ONLINE
   sh4      shspace2             Ok        Deployed     region2   READ_ONLY

9. Verify that all of the shards are registered.

   GDSCTL> databases
   Database: "sh1" Registered: Y State: Ok ONS: N. Role: PRIMARY
   Instances: 1 Region: region1
      Registered instances: 
        udef_shpool%1
   Database: "sh2" Registered: Y State: Ok ONS: N. Role: PH_STNDBY
   Instances: 1 Region: region2
      Registered instances: 
        udef_shpool%11
   Database: "sh3" Registered: Y State: Ok ONS: N. Role: PRIMARY
   Instances: 1 Region: region1
      Registered instances: 
        udef_shpool%21
   Database: "sh4" Registered: Y State: Ok ONS: N. Role: PH_STNDBY
   Instances: 1 Region: region2
      Registered instances: 
        udef_shpool%31

10. Check the configuration of a shard.

    GDSCTL> config shard -shard sh1
    Name: sh1
    Shard space: shspace1
    Status: Ok
    State: Deployed
    Region: region1
    Connection string: shard_host_1:1521/sh1:dedicated
    SCAN address:
    ONS remote port: 0
    Disk Threshold, ms: 20
    CPU Threshold, %: 75
    Version: 18.0.0.0
    Last Failed DDL: 
    DDL Error: ---
    Failed DDL id:
    Availability: ONLINE
    
    Supported services
    -----------------------
    Name                Preferred Status
    ----                --------- ------

11. Add a global service that runs on all of the primary shards.

    The oltp_rw_srvc global service is a global data service that a client can use to connect to the sharded database. The oltp_rw_srvc service runs the OLTP transactions on the primary shards. Likewise, the oltp_ro_srvc global service is created to run read-only workload on the standby shards.

    GDSCTL> add service -service oltp_rw_srvc -role primary 
    
    GDSCTL> config service 
    
    Name          Network name                      Pool         Started Preferred all
    ----          ------------                      ----         ------- -------------
    oltp_rw_srvc  oltp_rw_srvc.cust_sdb.oracdbcloud udef_shpool  No      Yes

12. Start the oltp_rw_srvc global service.

    GDSCTL> start service -service oltp_rw_srvc
    
    GDSCTL> status service
    Service "oltp_rw_srvc.cust_sdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
       Instance "udef_shpool%1", name: "sh1", db: "sh1", region: "region1",
       status: ready.
       Instance "udef_shpool%21", name: "sh3", db: "sh3", region: "region1",
       status: ready.

13. Add a global service for the read-only workload to run on the standby shards.

    GDSCTL> add service -service oltp_ro_srvc -role physical_standby
    
    GDSCTL> config service 
    
    Name          Network name                      Pool         Started Preferred all
    ----          ------------                      ----         ------- -------------
    oltp_rw_srvc  oltp_rw_srvc.cust_sdb.oracdbcloud cust_sdb     Yes     Yes
    oltp_ro_srvc  oltp_ro_srvc.cust_sdb.oracdbcloud cust_sdb     No      Yes
    

14. Start the read-only service, and verify the status of the global services.

    GDSCTL> start service -service oltp_ro_srvc
    
    GDSCTL> status service
    Service "oltp_ro_srvc.cust_sdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
       Instance "udef_shpool%11", name: "sh2", db: "sh2", region: "region2",
     status: ready.
       Instance "udef_shpool%31", name: "sh4", db: "sh4", region: "region2",
     status: ready.
    Service "oltp_rw_srvc.cust_sdb.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
       Instance "udef_shpool%1", name: "sh1", db: "sh1", region: "region1",
     status: ready.
       Instance "udef_shpool%21", name: "sh3", db: "sh3", region: "region1",
     status: ready.

Create the schema user, tablespace set, sharded tables, and duplicated tables for the SDB. Verify that the DDLs are propagated to all of the shards, and, while connected to the shards, verify the automatic Data Guard Broker configuration with Fast-Start Failover.

1. Connect to the shard catalog database, create the application schema user, and grant privileges and roles to the user.

   In this example, the application schema user is called app_schema.

   $ sqlplus / as sysdba
   
   SQL> alter session enable shard ddl;
   SQL> create user app_schema identified by app_schema_password;
   SQL> grant all privileges to app_schema;
   SQL> grant gsmadmin_role to app_schema;
   SQL> grant select_catalog_role to app_schema;
   SQL> grant connect, resource to app_schema;
   SQL> grant dba to app_schema;
   SQL> grant execute on dbms_crypto to app_schema;

2. Create tablespaces for the sharded tables.

   SQL> CREATE TABLESPACE c1_tsp DATAFILE SIZE 100M autoextend on next 10M maxsize 
   unlimited extent management local segment space management auto in
    shardspace shspace1;
   
   SQL> CREATE TABLESPACE c2_tsp DATAFILE SIZE 100M autoextend on next 10M maxsize 
   unlimited extent management local segment space management auto in
    shardspace shspace2;

3. If you use LOBs in any columns, you can specify tablespaces for the LOBs.

   SQL> CREATE TABLESPACE lobts1 ... in shardspace shspace1;
   
   SQL> CREATE TABLESPACE lobts2 ... in shardspace shspace2;

4. Create a tablespace for the duplicated tables.

   In this example the duplicated table is the Products table in the sample Customers-Orders-Products schema.

   SQL> CREATE TABLESPACE products_tsp datafile size 100m autoextend
    on next 10M maxsize unlimited extent management local uniform size 1m; 
   

5. Create a sharded table for the root table.

   In this example, the root table is the Customers table in the sample Customers-Orders-Products schema.

   SQL> CONNECT app_schema/app_schema_password
   
   SQL> ALTER SESSION ENABLE SHARD DDL;
   
   SQL> CREATE SHARDED TABLE Customers
     (
       CustId      VARCHAR2(60) NOT NULL,
       CustProfile VARCHAR2(4000),
       Passwd      RAW(60),
       CONSTRAINT pk_customers PRIMARY KEY (CustId),
       CONSTRAINT json_customers CHECK (CustProfile IS JSON)
     ) PARTITION BY RANGE (CustId)
     ( PARTITION ck1 values less than ('m') tablespace ck1_tsp,
       PARTITION ck2 values less than (MAXVALUE) tablespace ck2_tsp
     );

   Note:

   If any columns in the sharded tables contain LOBs, the CREATE SHARDED TABLE statement can include the LOB tablespaces, as shown here.

   SQL> CREATE SHARDED TABLE Customers
     (
       CustId      VARCHAR2(60) NOT NULL,
       CustProfile VARCHAR2(4000),
       Passwd      RAW(60),
       image       BLOB,
       CONSTRAINT pk_customers PRIMARY KEY (CustId),
       CONSTRAINT json_customers CHECK (CustProfile IS JSON)
     ) PARTITION BY RANGE (CustId)
     ( PARTITION ck1 values less than ('m') tablespace ck1_tsp
        lob(image) store as (tablespace lobts1),
       PARTITION ck2 values less than (MAXVALUE) tablespace ck2_tsp
        lob(image) store as (tablespace lobts2)
     );

6. Create a sharded table for the other tables in the table family.

   In this example, sharded tables are created for the Orders and LineItems tables in the sample Customers-Orders-Products schema.

   The Orders sharded table is created first:

   SQL> CREATE SHARDED TABLE Orders
     (
       OrderId     INTEGER NOT NULL,
       CustId      VARCHAR2(60) NOT NULL,
       OrderDate   TIMESTAMP NOT NULL,
       SumTotal    NUMBER(19,4),
       Status      CHAR(4),
       CONSTRAINT  pk_orders PRIMARY KEY (CustId, OrderId),
       CONSTRAINT  fk_orders_parent FOREIGN KEY (CustId) 
       REFERENCES Customers ON DELETE CASCADE
     ) PARTITION BY REFERENCE (fk_orders_parent);
   

   Create the sequence used for the OrderId column.

   SQL> CREATE SEQUENCE Orders_Seq;

   Create a sharded table for LineItems

   SQL> CREATE SHARDED TABLE LineItems
     (
       OrderId     INTEGER NOT NULL,
       CustId      VARCHAR2(60) NOT NULL,
       ProductId   INTEGER NOT NULL,
       Price       NUMBER(19,4),
       Qty         NUMBER,
       CONSTRAINT  pk_items PRIMARY KEY (CustId, OrderId, ProductId),
       CONSTRAINT  fk_items_parent FOREIGN KEY (CustId, OrderId)
       REFERENCES Orders ON DELETE CASCADE
     ) PARTITION BY REFERENCE (fk_items_parent);

7. Create any required duplicated tables.

   In this example, the Products table is a duplicated object.

   SQL> CREATE DUPLICATED TABLE Products
     (
       ProductId  INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       Name       VARCHAR2(128),
       DescrUri   VARCHAR2(128),
       LastPrice  NUMBER(19,4)
     ) TABLESPACE products_tsp;

8. From the shard director host, verify that there were no failures during the creation of the tablespaces.

   GDSCTL> show ddl
   id    DDL Text                                  Failed shards
   --    --------                                  -------------
   
   3       grant create table, create procedure,...               
   4       grant unlimited tablespace to app_schema               
   5       grant select_catalog_role to app_schema                
   6       create tablespace c1_tsp DATAFILE SIZ...               
   7       Create tablespace c2_tsp DATAFILE SIZ...               
   8       CREATE SHARDED TABLE Customers (   Cu...               
   9       CREATE SHARDED TABLE Orders (   Order...               
   10      CREATE SHARDED TABLE LineItems (   Or...               
   11      create tablespace products_tsp datafi... 
   12      CREATE MATERIALIZED VIEW "APP_SCHEMA"...      

   Note:

   The show ddl command output might be truncated. You can run SELECT ddl_text FROM gsmadmin_internal.ddl_requests on the catalog to see the full text of the statements.
9. Verify that there were no DDL errors on each of the shards.

   Run the config shard and config chunks commands on each shard in your configuration.

   GDSCTL> config shard -shard sh1
   
   Name: sh1
   Shard space: shspace1
   Status: Ok
   State: Deployed
   Region: region1
   Connection string: shard_host_1:1521/sh1:dedicated
   SCAN address: 
   ONS remote port: 0
   Disk Threshold, ms: 20
   CPU Threshold, %: 75
   Version: 18.0.0.0
   Last Failed DDL: 
   DDL Error: ---
   Failed DDL id: 
   Availability: ONLINE
   Rack:
   
   Supported services
   ------------------------
   Name                                          Preferred Status    
   ----                                          --------- ------    
   oltp_ro_srvc                                  Yes       Enabled   
   oltp_rw_srvc                                  Yes       Enabled  
   
   GDSCTL> config chunks
   Chunks
   ------------------------
   Database                      From      To        
   --------                      ----      --        
   sh1                           1         1         
   sh2                           1         1         
   sh3                           2         2        
   sh4                           2         2 

10. Verify that the tablespaces you created for the sharded table family and the tablespaces you created for the duplicated tables are created on all of the shards.

    The number of tablespaces in the tablespace set is based on the number of chunks you specified in the create shardcatalog command.

    The tablespace set with the first 6 chunks of the 12 that were specified in the shard catalog creation example, and the duplicated Products tablespace is shown in the following example.

    $ sqlplus / as sysdba
    
    SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB
     from sys.dba_data_files
     order by tablespace_name;
    
    TABLESPACE_NAME 		    MB
    ----------------------- ----------
    C1_TSP                     100
    PRODUCTS_TSP                    10
    SYSAUX                    722.1875
    SYSEXT                          39
    SYSTEM                  782.203125
    SYS_SHARD_TS                   100
    UD1                            470
    
    7 rows selected.
    

    Repeat this step on all of the shards in your configuration.

11. Verify that the chunks and chunk tablespaces were created on all of the shards.

    SQL> set linesize 140
    SQL> column table_name format a20
    SQL> column tablespace_name format a20
    SQL> column partition_name format a20
    SQL> show parameter db_unique_name
    
    NAME             TYPE        VALUE
    ---------------- ----------- ------------------------------
    db_unique_name   string      sh1
    
    SQL> select table_name, partition_name, tablespace_name
     from dba_tab_partitions
     where tablespace_name like 'C%TSP_SET_1'
     order by tablespace_name;
    
    
    TABLE_NAME       PARTITION_NAME   TABLESPACE_NAME
    ---------------- ---------------- --------------------
    CUSTOMERS            CK1                  C1_TSP
    ORDERS               CK1                  C1_TSP
    LINEITEMS            CK1                  C1_TSP
    

    Repeat this step on all of the shards in your configuration.

12. Verify that the sharded and duplicated tables were created.

    Log in as the application schema user on the shard catalog database and each of the shards.

    The following example shows querying the tables on a database shard as the app_schema user.

    $ sqlplus app_schema/app_schema_password
    Connected.
    
    SQL> select table_name from user_tables;
    
    TABLE_NAME
    -----------------------------------------------------------------------
    CUSTOMERS
    ORDERS
    LINEITEMS
    PRODUCTS
    USLOG$_PRODUCTS
    

13. Verify that the Data Guard Broker automatic Fast-Start Failover configuration was done.

    $ ssh os_username@shard_host_1
    $ dgmgrl
    
    DGMGRL> connect sys/password
    Connected to "sh1"
    Connected as SYSDG.
    DGMGRL> show configuration
    
    Configuration - sh1
    
      Protection Mode: MaxPerformance
      Members:
      sh1 - Primary database
        sh2 - (*) Physical standby database 
    
    Fast-Start Failover: ENABLED
    
    Configuration Status:
    SUCCESS   (status updated 15 seconds ago)
    
    DGMGRL> show database sh1
    
    Database - sh1
    
      Role:               PRIMARY
      Intended State:     TRANSPORT-ON
      Instance(s):
        sh1
    
    Database Status:
    SUCCESS
    
    DGMGRL> show database sh2
    
    Database - sh2
    
      Role:               PHYSICAL STANDBY
      Intended State:     APPLY-ON
      Transport Lag:      0 seconds (computed 0 seconds ago)
      Apply Lag:          0 seconds (computed 0 seconds ago)
      Average Apply Rate: 2.00 KByte/s
      Real Time Query:    ON
      Instance(s):
        sh2
    
    Database Status:
    SUCCESS
    
    DGMGRL> show fast_start failover
    
    Fast-Start Failover: ENABLED
    
      Threshold:          30 seconds
      Target:             sh2
      Observer:           shard_director_host
      Lag Limit:          30 seconds
      Shutdown Primary:   TRUE
      Auto-reinstate:     TRUE
      Observer Reconnect: (none)
      Observer Override:  FALSE
    
    Configurable Failover Conditions
      Health Conditions:
        Corrupted Controlfile          YES
        Corrupted Dictionary           YES
        Inaccessible Logfile            NO
        Stuck Archiver                  NO
        Datafile Write Errors          YES
    
      Oracle Error Conditions:
        (none)

14. Locate the Fast-Start Failover observers.

    Connect to the shard catalog database and run the following commands:

    $ ssh oracle@shard6
    
    $ ps -ef |grep dgmgrl
    oracle    8210  8089  0 22:18 pts/4    00:00:00 grep dgmgrl
    oracle   20189     1  0 02:57 ?        00:02:40 dgmgrl -delete_script
     @/u01/app/oracle/product/18.0.0/gsmhome_1/network/admin/gsm_observer_1.cfg
    oracle   20193     1  0 02:57 ?        00:02:43 dgmgrl -delete_script
     @/u01/app/oracle/product/18.0.0/gsmhome_1/network/admin/gsm_observer_2.cfg

To deploy a composite SDB you create shardgroups and shards, execute the DEPLOY command, and create role-based global services.

1. Connect to a shard director host, and verify the environment variables.

   $ ssh os_user@shard_director_home
   $ env |grep ORA
   ORACLE_BASE=/u01/app/oracle
   ORACLE_HOME=/u01/app/oracle/product/18.0.0/gsmhome_1

2. Set the global service manager for the current session, and specify the credentials to administer it.

   $ gdsctl
   GDSCTL> set gsm -gsm sharddirector1
   GDSCTL> connect mysdbadmin/mysdbadmin_password

3. Add shardspaces and shardgroups for each customized grouping of shards your business case requires.

   In this example the shardspaces and shardgroups are created for the America and Europe customers. You can choose your own names.

   GDSCTL> add shardspace -shardspace cust_america
   GDSCTL> add shardgroup -shardspace cust_america -shardgroup america_shgrp1
    -deploy_as primary -region region1
   
   GDSCTL> add shardspace -shardspace cust_europe
   GDSCTL> add shardgroup -shardspace cust_europe -shardgroup europe_shgrp1
    -deploy_as primary -region region2

   Note:

   For production deployments, additional shardgroups must be created for high availability using the add shardgroup command

4. Verify the shardspace and shardgroup configurations.

   GDSCTL> config shardspace
   SHARDSPACE                    Chunks                        
   ----------                    ------                        
   cust_america                  12                            
   cust_europe                   12                            
   shardspaceora                 12   
    
   GDSCTL>config shardgroup
   Shard Group         Chunks Region              SHARDSPACE          
   -----------         ------ ------              ----------          
   america_shgrp1      12     region1             cust_america        
   europe_shgrp1       12     region2             cust_europe         
   

5. Verify the sharded database configuration.

   GDSCTL> config
   
   Regions
   ------------------------
   region1 
   region2   
   
   GSMs
   ------------------------
   sharddirector1    
   sharddirector2 
   
   Sharded Database
   ------------------------
   cust_sdb_comp 
   
   Databases
   ------------------------
   
   Shard Groups
   ------------------------
   america_shgrp1  
   europe_shgrp1 
   
   Shard spaces
   ------------------------
   cust_america 
   cust_europe 
   shardspaceora
   
   Services
   ------------------------
   
   GDSCTL pending requests
   ------------------------
   Command              Object               Status
   -------              ------               ------
   
   Global properties
   ------------------------
   Name: oradbcloud
   Master GSM: sharddirector1
   DDL sequence #: 0
   

6. Add each shard’s host address to the valid node checking for registration (VNCR) list in the catalog, then create the shard in either the primary or standby shardgroup, as shown in the following example.

   Note:

   The valid node checking for registration (VNCR) feature provides the ability to configure and dynamically update a set of IP addresses, host names, or subnets from which registration requests are allowed by the shard directors. Database instance registration with a shard director succeeds only when the request originates from a valid node. By default, the shard management tier (based on Oracle Global Data Services framework) automatically adds a VNCR entry for the host on which a remote database is running each time create shard or add shard is executed. The automation (called auto-VNCR) finds the public IP address of the target host, and automatically adds a VNCR entry for that IP address. If the host has multiple public IP addresses, then the address on which the database registers may not be the same as the address which was added using auto-VNCR and , as a result, registration many be rejected. If the target database host has multiple public IP addresses, it is advisable that you configure VNCR manually for this host using the add invitednode or add invitedsubnet commands in GDSCTL.

   If there are multiple net-cards on the target host (/sbin/ifconfig returns more than one public interface), use add invitednode to be safe (after finding out which interface will be used to route packets).

   If there is any doubt about registration, then use config vncr and use add invitednode as necessary. There is no harm in doing this, because if the node is added already, auto-VNCR ignores it, and if you try to add it after auto-VNCR already added it, you will get a warning stating that it already exists.

   The example shows how to create four shards, two of which are in the America shardgroup and two in the Europe shardgroup. The os_credential is the operating system credential you created on each host.

   While creating the shards, you can also set the SYS password in the CREATE SHARD using -sys_password as shown in the following example. This sets the SYS password after the shards are created when running DEPLOY. There are other optional parameters for CREATE SHARD that allow you to customize the database parameters, storage and file locations, listener port numbers, and so on, which are documented in the Oracle Database Global Data Services Concepts and Administration Guide appendices.

   GDSCTL> add invitednode shard_host_1 
   GDSCTL> create shard -shardgroup america_shgrp1 -destination shard_host_1
    -credential os_credential-sys_password
   
   GDSCTL> add invitednode shard_host_2
   GDSCTL> create shard -shardgroup america_shgrp1 -destination shard_host_2
    -credential os_credential-sys_password
   
   GDSCTL> add invitednode shard_host_3 
   GDSCTL> create shard -shardgroup europe_shgrp1 -destination shard_host_3
    -credential os_credential-sys_password
   
   GDSCTL> add invitednode shard_host_4
   GDSCTL> create shard -shardgroup europe_shgrp1 -destination shard_host_4
    -credential os_credential-sys_password

   If you are using the ADD SHARD method described in Introduction to Sharded Database Deployment, use the following command instead of the CREATE SHARD commands in the example above. If the shard database to be added is a pluggable database (PDB), you must use the -cdb option to ADD SHARD to specify which container database (CDB) the PDB shard is in. In addition, ADD CDB must be used before the ADD SHARD command to add the CDB to the catalog. See Oracle Database Global Data Services Concepts and Administration Guide for the syntax for ADD CDB and ADD SHARD. Note that in Oracle Database 18c, only one PDB in each CDB is allowed to be a shard.

   GDSCTL> add shard –shardgroup america_shgrp1
    –connect shard_host:TNS_listener_port/shard_database_name
    –pwd GSMUSER_password

7. Check the configuration from a shard director.

   Note that the shard names, sh1, sh2, sh3, and sh4, are system generated shard names.

   GDSCTL> config shard
   Name           Shard Group         Status    State       Region    Availability
   ----           -----------         ------    -----       ------    ------------
   sh1            america_shgrp1      U         none        region1   -         
   sh2            america_shgrp1      U         none        region1   -         
   sh3            europe_shgrp1       U         none        region2   -         
   sh4            europe_shgrp1       U         none        region2   -         
   
   GDSCTL> config vncr
   Name    Group ID 
   ----    --------
   shard_host_1
   shard_host_2
   shard_host_3
   shard_host_4
   shard_catalog_host_IP
   

8. Run the DEPLOY command to create the shards.

   GDSCTL> deploy

   The DEPLOY command takes some time to run, approximately 15 to 30 minutes. The DEPLOY command creates the shards using DBCA.

9. Verify that all of the shards are deployed.

   GDSCTL> config shard
   Name        Shard Group         Status    State       Region    Availability 
   ----        -----------         ------    -----       ------    ------------ 
   sh1         america_shgrp1      Ok        Deployed    region1   ONLINE  
   sh2         america_shgrp1      Ok        Deployed    region1   ONLINE 
   sh3         europe_shgrp1       Ok        Deployed    region2   ONLINE 
   sh4         europe_shgrp1       Ok        Deployed    region2   ONLINE

10. Verify that all of the shards are registered.

    GDSCTL> databases
    Database: "sh1" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1
     Region: region1
       Registered instances:
         cust_sdb_comp%1
    Database: "sh2" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1
     Region: region1
       Registered instances:
         cust_sdb_comp%11
    Database: "sh3" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1
     Region: region2
       Registered instances:
         cust_sdb_comp%21
    Database: "sh4" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1
     Region: region2
       Registered instances:
         cust_sdb_comp%31
    

11. Check the configuration of a shard.

    GDSCTL> config shard -shard sh1
    
    Name: sh1
    Shard Group: america_shgrp1
    Status: Ok
    State: Deployed
    Region: region1
    Connection string: shard1:1521/sh1:dedicated
    SCAN address: 
    ONS remote port: 0
    Disk Threshold, ms: 20
    CPU Threshold, %: 75
    Version: 18.0.0.0
    Last Failed DDL: 
    DDL Error: ---
    Failed DDL id: 
    Availability: ONLINE
    
    
    Supported services
    ------------------------
    Name                                                            Preferred Status    
    ----                                                            --------- ------
    

12. Add a global service that runs on all of the primary shards.

    The oltp_rw_srvc global service is the global data services listener that helps route a connection from the client to the actual database. The oltp_rw_srvc service runs the OLTP transactions on the primary shards.

    GDSCTL> add service -service oltp_rw_srvc
    GDSCTL> config service 
    
    Name           Network name                  Pool           Started Preferred all 
    ----           ------------                  ----           ------- ------------- 
    oltp_rw_srvc   oltp_rw_srvc.cust_sdb_comp.or cust_sdb_comp  No      Yes           
                   adbcloud   
    

13. Start the oltp_rw_srvc global service.

    GDSCTL> start service -service oltp_rw_srvc
    
    GDSCTL> status service
    Service "oltp_rw_srvc.cust_sdb_comp.oradbcloud" has 4 instance(s). 
     Affinity: ANYWHERE
       Instance "cust_sdb_comp%1", name: "sh1", db: "sh1", region: "region1",
     status: ready.
       Instance "cust_sdb_comp%11", name: "sh2", db: "sh2", region: "region1",
     status: ready.
       Instance "cust_sdb_comp%21", name: "sh3", db: "sh3", region: "region2",
     status: ready.
       Instance "cust_sdb_comp%31", name: "sh4", db: "sh4", region: "region2",
     status: ready. 
    

Create the schema user, tablespace set, sharded tables, and duplicated tables for the SDB. Verify that the DDLs are propagated to all of the shards, and, while connected to the shards, verify the automatic Data Guard Broker configuration with Fast-Start Failover.

1. Connect to the shard catalog host, and set the ORACLE_SID to the shard catalog name.
2. Connect to the shard catalog database, create the application schema user, and grant privileges and roles to the user.

   In this example, the application schema user is called app_schema.

   $ sqlplus / as sysdba
   
   SQL> connect / as sysdba
   SQL> alter session enable shard ddl;
   SQL> create user app_schema identified by app_schema_password;
   SQL> grant connect, resource, alter session to app_schema;
   SQL> grant execute on dbms_crypto to app_schema;
   SQL> grant create table, create procedure, create tablespace,
    create materialized view to app_schema;
   SQL> grant unlimited tablespace to app_schema;
   SQL> grant select_catalog_role to app_schema;
   SQL> grant all privileges to app_schema;
   SQL> grant gsmadmin_role to app_schema;
   SQL> grant dba to app_schema;
   

3. Create tablespace sets for the sharded tables.

   SQL> CREATE TABLESPACE SET  
     TSP_SET_1 in shardspace cust_america using template
     (datafile size 100m autoextend on next 10M maxsize
      unlimited extent management
      local segment space management auto );
   
   SQL> CREATE TABLESPACE SET
     TSP_SET_2 in shardspace cust_europe using template
     (datafile size 100m autoextend on next 10M maxsize
      unlimited extent management
      local segment space management auto );

   Specifying the shardspace is optional when creating the tablespace set. If the shardspace is not specified in the command, the default shardspace is used.

4. If you use LOBs in any columns, you can specify tablespace sets for the LOBs.

   SQL> CREATE TABLESPACE SET LOBTS1 in shardspace cust_america ... ;
   
   SQL> CREATE TABLESPACE SET LOBTS2 in shardspace cust_europe ... ;

   Note:

   Tablespace sets for LOBs cannot be specified at the subpartitition level in composite sharding.

5. Create a tablespace for the duplicated tables.

   In this example the duplicated table is the Products table in the sample Customers-Orders-Products schema.

   CREATE TABLESPACE products_tsp datafile size 100m autoextend on next 10M
    maxsize unlimited extent management local uniform size 1m;

6. Create a sharded table for the root table.

   In this example, the root table is the Customers table in the sample Customers-Orders-Products schema.

   connect app_schema/app_schema_password
   alter session enable shard ddl;
   
   CREATE SHARDED TABLE Customers
   (
     CustId      VARCHAR2(60) NOT NULL,
     FirstName   VARCHAR2(60),
     LastName    VARCHAR2(60),
     Class       VARCHAR2(10),
     Geo         VARCHAR2(8),
     CustProfile VARCHAR2(4000),
     Passwd      RAW(60),
     CONSTRAINT pk_customers PRIMARY KEY (CustId),
     CONSTRAINT json_customers CHECK (CustProfile IS JSON)
   ) partitionset by list(GEO)
   partition by consistent hash(CustId)
   partitions auto
   (partitionset america values ('AMERICA') tablespace set tsp_set_1,
   partitionset europe values ('EUROPE') tablespace set tsp_set_2
   );
   

   Note:

   If any columns in the sharded tables contain LOBs, the CREATE SHARDED TABLE statement can include the LOB tablespace set, as shown here.

   CREATE SHARDED TABLE Customers
   (
     CustId      VARCHAR2(60)  NOT NULL,
     FirstName   VARCHAR2(60),
     LastName    VARCHAR2(60),
     Class       VARCHAR2(10),
     Geo         VARCHAR2(8)   NOT NULL,
     CustProfile VARCHAR2(4000),
     Passwd      RAW(60),
     image       BLOB,
     CONSTRAINT pk_customers PRIMARY KEY (CustId),
     CONSTRAINT json_customers CHECK (CustProfile IS JSON)
   ) partitionset by list(GEO)
   partition by consistent hash(CustId)
   partitions auto
   (partitionset america values ('AMERICA') tablespace set tsp_set_1
    lob(image) store as (tablespace set lobts1),
   partitionset europe values ('EUROPE') tablespace set tsp_set_2
    lob(image) store as (tablespace set lobts2));
   

7. Create a sharded table for the other tables in the table family.

   In this example, sharded tables are created for the Orders and LineItems tables in the sample Customers-Orders-Products schema.

   Create the sequence used for the OrderId column.

   CREATE SEQUENCE Orders_Seq;
   

   The Orders sharded table is created first:

   CREATE SHARDED TABLE Orders
   (
     OrderId     INTEGER NOT NULL,
     CustId      VARCHAR2(60) NOT NULL,
     OrderDate   TIMESTAMP NOT NULL,
     SumTotal    NUMBER(19,4),
     Status      CHAR(4),
     constraint  pk_orders primary key (CustId, OrderId),
     constraint  fk_orders_parent foreign key (CustId) 
       references Customers on delete cascade
   ) partition by reference (fk_orders_parent);
   

   Create a sharded table for LineItems

   CREATE SHARDED TABLE LineItems
   (
     OrderId     INTEGER NOT NULL,
     CustId      VARCHAR2(60) NOT NULL,
     ProductId   INTEGER NOT NULL,
     Price       NUMBER(19,4),
     Qty         NUMBER,
     constraint  pk_items primary key (CustId, OrderId, ProductId),
     constraint  fk_items_parent foreign key (CustId, OrderId)
       references Orders on delete cascade
   ) partition by reference (fk_items_parent);

8. Create any required duplicated tables.

   In this example, the Products table is a duplicated object.

   CREATE DUPLICATED TABLE Products
   (
     ProductId  INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     Name       VARCHAR2(128),
     DescrUri   VARCHAR2(128),
     LastPrice  NUMBER(19,4)
   ) tablespace products_tsp;

9. From the shard director host, verify that there were no failures during the creation of the tablespaces.

   GDSCTL> show ddl
   id      DDL Text                                 Failed shards 
   --      --------                                 ------------- 
   11      CREATE TABLESPACE SET  TSP_SET_2 in s...               
   12      CREATE TABLESPACE products_tsp datafi...               
   13      CREATE SHARDED TABLE Customers (   Cu...               
   14      CREATE SEQUENCE Orders_Seq;                            
   15      CREATE SHARDED TABLE Orders (   Order...               
   16      CREATE SHARDED TABLE LineItems (   Or...               
   17      create database link "PRODUCTSDBLINK@...               
   18      CREATE MATERIALIZED VIEW "PRODUCTS"  ...               
   19      CREATE OR REPLACE FUNCTION PasswCreat...               
   20      CREATE OR REPLACE FUNCTION PasswCheck...     
   

10. Verify that there were no DDL errors on each of the shards.

    Run the config shard and config chunks commands on each shard in your configuration.

    GDSCTL> config shard -shard sh1
    
    Name: sh1
    Shard Group: america_shgrp1
    Status: Ok
    State: Deployed
    Region: region1
    Connection string: shard1:1521/sh1:dedicated
    SCAN address: 
    ONS remote port: 0
    Disk Threshold, ms: 20
    CPU Threshold, %: 75
    Version: 18.0.0.0
    Last Failed DDL: 
    DDL Error: ---
    Failed DDL id: 
    Availability: ONLINE
    
    
    Supported services
    ------------------------
    Name                                                            Preferred Status    
    ----                                                            --------- ------    
    oltp_rw_srvc                                                    Yes       Enabled  
    
    GDSCTL> config chunks
    Chunks
    ------------------------
    Database                      From      To        
    --------                      ----      --        
    sh1                           1         6         
    sh2                           7         12        
    sh3                           1         6         
    sh4                           7         12
    

11. Verify that the tablespaces of the tablespace set you created for the sharded table family and the tablespaces you created for the duplicated tables are created on all of the shards.

    The number of tablespaces in the tablespace set is based on the number of chunks you specified in the create shardcatalog command.

    The tablespace set with the first 6 chunks of the 12 that were specified in the shard catalog creation example, and the duplicated Products tablespace is shown in the following example on the shard_host_1.

    $ sqlplus / as sysdba
    
    SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB
     from sys.dba_data_files
     order by tablespace_name;
    
    TABLESPACE_NAME 		       MB
    ------------------------------ ----------
    C001TSP_SET_1			      100
    C002TSP_SET_1			      100
    C003TSP_SET_1			      100
    C004TSP_SET_1			      100
    C005TSP_SET_1			      100
    C006TSP_SET_1			      100
    PRODUCTS_TSP			      100
    SYSAUX				      650
    SYSTEM				      890
    SYS_SHARD_TS			      100
    TSP_SET_1			      100
    
    TABLESPACE_NAME 		       MB
    ------------------------------ ----------
    TSP_SET_2			      100
    UNDOTBS1			      110
    USERS					5
    
    14 rows selected.
    

    Repeat this step on all of the shards in your configuration.

12. Verify that the chunks and chunk tablespaces were created on all of the shards.

    SQL> set linesize 140
    SQL> column table_name format a20
    SQL> column tablespace_name format a20
    SQL> column partition_name format a20
    SQL> show parameter db_unique_name
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    db_unique_name			     string	 sh2
    
    
    SQL> select table_name, partition_name, tablespace_name
     from dba_tab_partitions
     where tablespace_name like 'C%TSP_SET_1'
     order by tablespace_name;
    
    TABLE_NAME	     PARTITION_NAME	  TABLESPACE_NAME
    -------------------- -------------------- --------------------
    LINEITEMS	     CUSTOMERS_P7	  C007TSP_SET_1
    CUSTOMERS	     CUSTOMERS_P7	  C007TSP_SET_1
    ORDERS		     CUSTOMERS_P7	  C007TSP_SET_1
    CUSTOMERS	     CUSTOMERS_P8	  C008TSP_SET_1
    LINEITEMS	     CUSTOMERS_P8	  C008TSP_SET_1
    ORDERS		     CUSTOMERS_P8	  C008TSP_SET_1
    LINEITEMS	     CUSTOMERS_P9	  C009TSP_SET_1
    CUSTOMERS	     CUSTOMERS_P9	  C009TSP_SET_1
    ORDERS		     CUSTOMERS_P9	  C009TSP_SET_1
    CUSTOMERS	     CUSTOMERS_P10	  C00ATSP_SET_1
    LINEITEMS	     CUSTOMERS_P10	  C00ATSP_SET_1
    
    TABLE_NAME	     PARTITION_NAME	  TABLESPACE_NAME
    -------------------- -------------------- --------------------
    ORDERS		     CUSTOMERS_P10	  C00ATSP_SET_1
    CUSTOMERS	     CUSTOMERS_P11	  C00BTSP_SET_1
    LINEITEMS	     CUSTOMERS_P11	  C00BTSP_SET_1
    ORDERS		     CUSTOMERS_P11	  C00BTSP_SET_1
    CUSTOMERS	     CUSTOMERS_P12	  C00CTSP_SET_1
    LINEITEMS	     CUSTOMERS_P12	  C00CTSP_SET_1
    ORDERS		     CUSTOMERS_P12	  C00CTSP_SET_1
    
    18 rows selected.
    

    Repeat this step on all of the shards in your configuration.

13. Connect to the shard catalog database and verify that the chunks are uniformly distributed.

    $ sqlplus / as sysdba
    
    SQL> set echo off
    SQL> select a.name Shard,  count( b.chunk_number) Number_of_Chunks
     from gsmadmin_internal.database a, gsmadmin_internal.chunk_loc b
     where a.database_num=b.database_num  group by a.name;
    
    SHARD			       NUMBER_OF_CHUNKS
    ------------------------------ ----------------
    sh1					      6
    sh2					      6
    sh3					      6
    sh4					      6
    

14. Verify that the sharded and duplicated tables were created.

    Log in as the application schema user on the shard catalog database and each of the shards.

    The following example shows querying the tables on a database shard as the app_schema user.

    $ sqlplus app_schema/app_schema_password
    Connected.
    SQL> select table_name from user_tables;
    
    TABLE_NAME
    -----------------------------------------------------------------------
    CUSTOMERS
    ORDERS
    LINEITEMS
    PRODUCTS
    
    4 rows selected.

To propagate a single encryption key to all of the databases in the sharded database configuration, you must create a master encryption key on the shard catalog, then use wallet export, followed by wallet import onto the shards, and activate the keys.

1. Create an encryption key on the shard catalog.

   With shard DDL enabled, issue the following statements.

   ADMINISTER KEY MANAGEMENT CREATE KEYSTORE wallet_directory_path IDENTIFIED BY
    keystore_password;
   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keystore_password;

   The keystore_password should be the same if you prefer to issue wallet open and close commands centrally from the catalog.

   Note:

   The wallet directory path should match the ENCRYPTION_WALLET_LOCATION in the corresponding sqlnet.ora.

   ENCRYPTION_WALLET_LOCATION parameter is being deprecated. You are advised to use the WALLET_ROOT static initialization and TDE_CONFIGURATION dynamic initialization parameter instead.

   With shard DDL disabled, issue the following statement.

   ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keystore_password WITH BACKUP;

   An encryption key is created and activated in the shard catalog database’s wallet.

   If you issue this statement with DDL enabled, it will also create encryption keys in each of the shards’ wallets, which are different keys from that of the catalog. In order for data movement to work, you cannot use different encryption keys on each shard.

2. Get the master key ID from the shard catalog keystore.

   SELECT KEY_ID  FROM V$ENCRYPTION_KEYS 
   WHERE ACTIVATION_TIME =
    (SELECT MAX(ACTIVATION_TIME) FROM V$ENCRYPTION_KEYS
     WHERE ACTIVATING_DBID = (SELECT DBID FROM V$DATABASE));

3. With shard DDL disabled, export the catalog wallet containing the encryption key.

   ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET secret_phrase TO
    wallet_export_file IDENTIFIED BY keystore_password;

   (Optional) Enter the result of the step here.
4. Physically copy the wallet file to each of the shard hosts, into their corresponding wallet export file location, or put the wallet file on a shared disk to which all of the shards have access.
5. With shard DDL disabled, log on to each shard and import the wallet containing the key.

   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keystore_password;
   ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET secret_phrase FROM
    wallet_export_file IDENTIFIED BY keystore_password WITH BACKUP;

6. Restart the shard databases.
7. Activate the key on all of the shards.

   On the catalog with shard DDL enabled

   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keystore_password;
   ADMINISTER KEY MANAGEMENT USE KEY master_key_id IDENTIFIED BY keystore_password
    WITH BACKUP;

SELECT KEY_ID  FROM V$ENCRYPTION_KEYS 
WHERE ACTIVATION_TIME =
 (SELECT MAX(ACTIVATION_TIME) FROM V$ENCRYPTION_KEYS
  WHERE ACTIVATING_DBID = (SELECT DBID FROM V$DATABASE));