Create a service

OceanBase Database allows you to create a service for a user tenant in OceanBase Cloud Platform (OCP) or by executing the CREATE SERVICE statement on a command-line interface (CLI). This topic describes how to create a service on a CLI. However, we recommend that you use OCP. For more information, see View the details of a tenant.

Background information

In OceanBase Database V2.x and V3.x, the Physical Standby Database solution is provided in the form of primary/standby clusters. Clusters in primary/standby mode have the same cluster name and contain tenants with the same name as the cluster name. Clusters in different primary/standby relationships have different cluster names. Therefore, whether clusters are in a primary/standby relationship can be uniquely identified by cluster names. When you use OceanBase Database Proxy (ODP) to connect to the database, you can specify the cluster name to route the connection to the primary cluster.

In OceanBase Database V4.1.0 and later, the Physical Standby Database solution is provided in the form of primary/standby tenants. Tenants in primary/standby mode can belong to different clusters. The primary tenant and its standby tenants do not record the information of each other. To enable automatic routing to the primary tenant or a standby tenant, OceanBase Database introduces the concept of service. A service can serve multiple tenants in the same cluster or across clusters. When you use ODP to connect to the database, you can specify the service name to route the connection to the primary tenant.

Limitations

• You cannot create a service for the sys tenant or a meta tenant.

• You can create only one service for a user tenant.

• You cannot execute the CREATE SERVICE statement in a session created based on a service name.

• This feature is used with ODP and OCP. Supported versions of ODP and OCP are as follows:

  • ODP: V4.3.1 and later

  • OCP: V4.3.1 and later

Considerations

When you create services for tenants, make sure that one service serves at most one primary tenant. We recommend that you use OCP to maintain primary/standby tenants and manage services. For more information about how to manage primary/standby tenants in OCP, see OCP documentation.

Prerequisites

• You have the ALTER SYSTEM privilege.

• The units for the tenant do not contain temporarily offline OBServer nodes. Otherwise, the temporarily offline OBServer nodes cannot provide the service specified by the service name after you execute the CREATE SERVICE statement. Therefore, we recommend that you wait until the temporarily offline OBServer nodes are recovered or permanently offline before you execute the CREATE SERVICE statement. Alternatively, you can execute the CREATE SERVICE statement, wait until the temporarily offline OBServer nodes are recovered or permanently offline, and then execute the START SERVICE statement to start the service.

  To query temporarily offline OBServer nodes in the units for the tenant, perform the following steps:

  1. Log in to the sys tenant of the cluster where the current tenant resides as the root user.

  2. Query temporarily offline OBServer nodes in the units for the tenant and obtain the LAST_OFFLINE_TIME values of the OBServer nodes.

     obclient [oceanbase]> SELECT SVR_IP, SVR_PORT, LAST_OFFLINE_TIME, NOW() FROM oceanbase.DBA_OB_SERVERS WHERE LAST_OFFLINE_TIME IS NOT NULL AND (SVR_IP, SVR_PORT) IN (SELECT DISTINCT SVR_IP, SVR_PORT FROM oceanbase.DBA_OB_UNITS WHERE TENANT_ID = user_tenant_id);
     

     Replace user_tenant_id with the tenant ID of the current tenant.

     If the query result is empty, the units for the tenant do not contain temporarily offline OBServer nodes. Otherwise, check whether the OBServer nodes are temporarily offline.

  3. Query the server_permanent_offline_time parameter of each offline OBServer node, and determine the time when the OBServer node goes permanently offline based on the obtained parameter value and the LAST_OFFLINE_TIME value. The server_permanent_offline_time parameter specifies the time threshold for heartbeat missing. When the heartbeat of an OBServer node is missing for the specified period of time, the OBServer node is considered permanently offline. Data replicas on a permanently offline OBServer node must be automatically supplemented. For more information about this parameter, see server_permanent_offline_time.

     obclient [oceanbase]> SHOW PARAMETERS LIKE 'server_permanent_offline_time';
     

     In the query result, if the sum of LAST_OFFLINE_TIME + server_permanent_offline_time values is greater than the value returned by the NOW() function, the OBServer node is permanently offline. Otherwise, the OBServer node is temporarily offline.

• The SWITCHOVER_STATUS value of the tenant is NORMAL, which indicates that the tenant is not in an intermediate state during primary/standby switchover.

  You can query the TENANT_ROLE and SWITCHOVER_STATUS values of a tenant in either of the following ways:

  • Query the TENANT_ROLE and SWITCHOVER_STATUS values of a specified tenant in the sys tenant.

    obclient [oceanbase]> SELECT TENANT_ID, TENANT_NAME, TENANT_ROLE, SWITCHOVER_STATUS FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = mysql_tenant;
    

  • Query the TENANT_ROLE and SWITCHOVER_STATUS values of a user tenant in the current tenant.

    obclient [oceanbase]> SELECT TENANT_ID, TENANT_NAME, TENANT_ROLE, SWITCHOVER_STATUS FROM oceanbase.DBA_OB_TENANTS;
    

    obclient [SYS]> SELECT TENANT_ID, TENANT_NAME, TENANT_ROLE, SWITCHOVER_STATUS FROM SYS.DBA_OB_TENANTS;
    

Procedure

When you create services for tenants in primary/standby mode, you must separately create services with the same service name for them.

1. Log in to a user tenant or the sys tenant of the cluster as the tenant administrator.

2. Query the TENANT_ROLE value of the specified tenant.

   One service serves at most one primary tenant. Therefore, you must determine the role of the current tenant before you create a service for it.

   Here is an example:

   obclient [oceanbase]> SELECT TENANT_ID, TENANT_NAME, TENANT_ROLE FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = mysql_tenant;
   

3. Query tenants in the service to be created.

   Notice

   The following statement queries the names and tenant IDs of all primary and standby tenants corresponding to the specified service name in the current cluster. In the cross-cluster Physical Standby Database solution, you must also query the corresponding clusters.

   Here is an example:

   obclient [oceanbase]> SELECT t.tenant_id, t.tenant_name, t.tenant_role
   FROM oceanbase.CDB_OB_SERVICES AS s JOIN oceanbase.DBA_OB_TENANTS AS t
   ON s.tenant_id = t.tenant_id 
   WHERE s.service_name = 's_hz';
   

   Based on the query result and the TENANT_ROLE value, make sure that the service to be created serves at most one primary tenant.

4. Create the service for the tenant.

   Here is an example:

   ALTER SYSTEM CREATE SERVICE service_name [TENANT [=] tenant_name];
   

   The parameters are described as follows:

   • service_name: the name of the service to be created.
   • TENANT [=] tenant_name: the name of the tenant for which you want to create the service. This parameter is valid only in the sys tenant.

   Here are some examples:

   • Create a service named s_hz for a tenant named mysql_tenant in the sys tenant.

     obclient [oceanbase]> ALTER SYSTEM CREATE SERVICE s_hz TENANT = mysql_tenant;
     

   • Create a service named s_hz for a user tenant in the current tenant.

     obclient > ALTER SYSTEM CREATE SERVICE s_hz;
     

   If Query OK is returned, you can connect to all OBServer nodes except the permanently offline ones in the units for the tenant by specifying the service name. If OB_SERVICE_NOT_FULLY_STARTED is returned, you can connect to only some of OBServer nodes except the permanently offline ones in the units for the tenant by specifying the service name. You can query the connectable OBServer nodes from the CDB_OB_TENANT_EVENT_HISTORY view in the sys tenant or the DBA_OB_TENANT_EVENT_HISTORY (MySQL mode) or DBA_OB_TENANT_EVENT_HISTORY (Oracle mode) view in a user tenant.

What to do next

Verify that services with the same service name are created for the primary and standby tenants and that the clusters to which the primary and standby tenants belong are managed by OCP and associated with the same ODP cluster. Then, use ODP to connect to the database, and specify the service name to route the connection to the primary tenant or a standby tenant. A sample connection string is as follows:

obclient -hip -Pport -uuser_name@SERVICE:service_name

The parameters are described as follows:

• ip: the IP address of ODP.
• port: the port number of ODP.
• user_name: the username of the tenant to log in to.
• service_name: the service name of the tenant to log in to.

Here is an example:

obclient -h10.xx.xx.xx -P2883 -uuser1@SERVICE:s_hz

For more information about the connection methods, see Overview (MySQL mode) and Overview (Oracle mode).

References

• Query the service status
• Start a service
• Stop a service
• Delete a service