Create a DBLink

OceanBase Database provides DBLinks to support cross-data source access. You can use a DBLink to access a remote database from your local database.

In the Oracle mode of OceanBase Database, you can create a DBLink between two Oracle tenants of OceanBase Database or between an Oracle tenant of OceanBase Database and an Oracle database.

Prerequisites

To create a DBLink, you must have the CREATE DATABASE LINK or CREATE PUBLIC DATABASE LINK privilege. For information about how to grant user privileges, see Grant direct privileges.

Considerations

• We recommend that you set the local_ip:local_port parameter following the MY_NAME parameter to the IP address and port number of an OceanBase Database Proxy (ODP) node of the local cluster. This way, the ODP node automatically routes the reverse link to the correct node to obtain local data.

  Assume that you set the local_ip:local_port parameter following the MY_NAME parameter to the IP address and port number of OBServer node A of the local cluster when you create a DBLink. In Oracle mode of OceanBase Database, when you execute the INSERT, UPDATE, DELETE, or MERGE INTO statement to write data of a local table to a remote table, the following cases will occur:

  • If the SQL statement is executed on OBServer node A, the node to which data is written over the DBLink is as expected.
  • If the SQL statement is executed on another OBServer node, for example, OBServer node B, in the local cluster, the node to which data is written over the DBLink is not as expected, and an error will be returned. This is because the reverse link is not executed in the expected transaction branch and the obtained data does not contain changes within the transaction. To be specific, the local_ip:local_port parameter following the MY_NAME parameter is set to the IP address and port number of OBServer node A. Therefore, the reverse link will definitely read data of the distributed XA transaction branch from OBServer node A. However, the SQL statement for writing data over the DBLink is not executed on OBServer node A. As a result, the distributed XA transaction branch is not running on OBServer node A but on OBServer node B.

• We recommend that you set the ip:port parameter following the CONNECT TO parameter to the IP address and port number of an ODP node in the remote cluster. If you set this parameter to the IP address and port number of an OBServer node in the remote cluster, the DBLink cannot connect to the remote cluster when the OBServer node fails, compromising high availability.

Create a DBLink from an OceanBase database to another OceanBase database

To access a remote OceanBase database, you can create a DBLink from an Oracle tenant in your local OceanBase database to an Oracle tenant of the remote OceanBase database.

To create a DBLink, you must specify the DBLink name and provide information, such as the username, tenant name, password, IP address, port number, and access type, of the remote database. The data write feature of DBLink supports reverse links. The remote database can access objects, such as tables, views, and synonyms, in the local database through a reverse link. To use the reverse link feature, you must provide information, such as the username, tenant name, password, IP address, and port number, of the local database when you create a DBLink.

The SQL syntax for creating a DBLink from one Oracle tenant of OceanBase Database to another is as follows:

CREATE DATABASE LINK dblink_name CONNECT TO user@tenant IDENTIFIED BY remote_password [OB] HOST 'ip:port'
    [CLUSTER "cluster_name"]
    [MY_NAME local_user@local_tenant IDENTIFIED BY local_password HOST 'local_ip:local_port' [CLUSTER "local_cluster_name"]];

where:

• dblink_name specifies the name of the DBLink. The value can contain a maximum of 128 characters.

• user specifies the username for logging on to the remote OceanBase database.

• tenant specifies the tenant name of the remote OceanBase database.

• remote_password specifies the password for logging on to the remote OceanBase database. If the password contains a special character, such as @, #, or !, you must enclose the password with double quotation marks (") to avoid syntax errors.

• OB specifies that the type of the remote database is OceanBase. This parameter is optional.

  If this parameter is not specified, the remote database is an OceanBase database.

• ip specifies the IP address of the remote OceanBase database. It can be the IP address of the OceanBase Database Proxy (ODP) for the cluster or the IP address of an OBServer node in the cluster.

  If the IP address of an OBServer node is specified, network connectivity must be ensured between the local database and the specified OBServer node.

• port specifies the port of the remote OceanBase database. It can be the port of the ODP for the cluster, which is 2883 by default, or the SQL port of an OBServer node in the cluster, which is 2881 by default.

  If the SQL port of an OBServer node is specified, network connectivity must be ensured between the local database and the specified OBServer node.

• cluster_name specifies the name of the cluster on the remote OceanBase database. You need to specify the cluster name only when the specified IP address and port number are those of the ODP of the cluster and the ODP is deployed by using ConfigUrl. The cluster name is case-sensitive and must be enclosed with double quotation marks (").

  Note

  You can deploy a proxy such as ODP for a cluster by using the ConfigURL or RootService list.

  • ConfigUrl: When ODP is started, the obproxy_config_server_url parameter specified in the command is used to query the RootServer information of the OceanBase cluster.
  • RoortService list: When ODP is started, the -r parameter specified in the command is used to query the RootServer information of the OceanBase cluster.

• [MY_NAME local_user@local_tenant IDENTIFIED BY local_password HOST 'local_ip:local_port' [CLUSTER "local_cluster_name"]] specifies the username, tenant name, password, IP address, port number, and cluster of the local database, which are required only if you want to use the reverse link feature of DBLink.

  • local_user: the username of the local database.

  • local_tenant: the name of the tenant to which the local database belongs.

  • local_password: the password used to log on to the local database. If the password contains a special character, such as @, #, or !, you must enclose the password with double quotation marks (") to avoid syntax errors.

  • local_ip: the IP address of an OBServer node in the cluster of the local database.

  • local_port: the SQL port number of an OBServer node in the cluster of the local database. By default, the SQL port number of the OBServer node is 2881.

  • local_cluster_name: the name of the cluster of the local OceanBase database. You need to specify the cluster name only when the specified IP address and port number are those of the ODP of the cluster and the ODP is deployed by using ConfigUrl. The cluster name must be enclosed with double quotation marks (").

Here is an example:

• Create a DBLink to connect to a remote OceanBase database by using the IP address and port number of the specified OBServer node.

  obclient> CREATE DATABASE LINK ob_dblink CONNECT TO ob_user@oracle IDENTIFIED BY ****** OB HOST 'xx.xx.xx.xx:2881';
  Query OK, 1 row affected
  

• Create a DBLink to connect to a remote OceanBase database by using the IP address and port number of the ODP of a cluster. The ODP is deployed by using ConfigUrl.

  obclient> CREATE DATABASE LINK ob_dblink_proxy CONNECT TO ob_user@oracle IDENTIFIED BY ****** OB HOST 'xx.xx.xx.xx:2883' CLUSTER "ob410";
  Query OK, 1 row affected
  

  Notice

  Enclose the cluster name with double quotation marks (") to prevent the cluster name from being capitalized.

• Create a DBLink named ob_dblink_reverse_link that connects to a remote OceanBase database and provides the reverse link feature.

  
  obclient> CREATE DATABASE LINK ob_dblink_reverse_link CONNECT TO ob_user2@oracle IDENTIFIED BY ****** OB HOST  'xx.xx.xx.xx:2881' MY_NAME local_ob_user@oracle IDENTIFIED BY ****** HOST 'xx.xx.xx.xx:2881';
  Query OK, 1 row affected
  

Create a DBLink from an OceanBase database to an Oracle database

To access a remote Oracle database, you can create a DBLink from an Oracle tenant in your local OceanBase database to the remote Oracle database.

To create a DBLink, you must specify the DBLink name and provide information, such as the username, tenant name, password, IP address, port number, and access type, of the remote database. The data write feature of DBLink supports reverse links. The remote database can access objects, such as tables, views, and synonyms, in the local database through a reverse link. The DBLink from an OceanBase database to an Oracle database does not support reverse links.

The SQL syntax for creating a DBLink from an OceanBase database to an Oracle database is as follows:

obclient> CREATE DATABASE LINK dblink_name CONNECT TO user@oracle IDENTIFIED BY remote_password OCI HOST 'ip:port/oracle_service_name';

where:

• dblink_name specifies the name of the DBLink. The value can contain a maximum of 128 characters.

• user specifies the username for logging on to the remote Oracle database.

• oracle is always used if the remote database is an Oracle database.

• remote_password specifies the password for logging on to the remote Oracle database. If the password contains a special character, such as @, #, or !, you must enclose the password with double quotation marks ("") to avoid syntax errors.

• OCI indicates that the type of the remote database is Oracle. If this parameter is not specified, the remote database type is OceanBase by default.

• ip specifies the IP address of the remote Oracle Database instance.

• port specifies the port number of the remote Oracle Database instance.

• oracle_service_name specifies the name of the service provided by the remote Oracle Database instance.

Example: Create a DBLink to connect to a remote Oracle database.

obclient> CREATE DATABASE LINK orcl_dblink CONNECT TO orcl_user@oracle IDENTIFIED BY ****** OCI HOST 'xx.xx.xx.xx:1521/ORCL';
Query OK, 1 row affected

References

For more information about operations on DBLinks, see the following topics:

• Query existing DBLinks

• Use a DBLink to access data in a remote database

• Use a DBLink to modify data in a remote database

• Drop a DBLink