南大通用数据库-Gbase-8a-学习-10-Gbase8a通过Dblink访问Oracle

一、测试环境

二、测试步骤

1、Oracle创建用户

SQL> create user czg identified by qwer1234; 

User created.

SQL> grant dba to czg;

Grant succeeded.
1
2
3
4
5
6
7

2、Oracle生成测试数据

SQL> conn czg;
Enter password: 
Connected.

SQL> create table test (id int,name varchar(10));

Table created.

SQL> insert into test values(1,'czg');

1 row created.

SQL> insert into test values(2,'zxj');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

	ID NAME
---------- ------------------------------
	 1 czg
	 2 zxj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

3、配置

[gbase@xdw0 pkg]$ tar -xvf GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15.tar

[gbase@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]$ ll
总用量 12
drwxrwxrwx 5 gbase gbase  101 8月  15 15:53 conf
drwxrwxrwx 2 gbase gbase   30 11月 30 2020 gateway
-rwxrwxrwx 1 gbase gbase 1896 11月  7 2019 gbaseGatewayServer.sh
-rwxrwxrwx 1 gbase gbase  654 7月  16 2020 gt.sh
drwxrwxrwx 5 gbase gbase  185 7月  16 2020 jre
drwxrwxrwx 4 gbase gbase 4096 7月  16 2020 lib
drwxrwxrwx 2 gbase gbase   30 8月  15 15:31 logs
1
2
3
4
5
6
7
8
9
10
11

（1）conf.properties

这个文件不用改动，但大家需要记录一下端口9898。

[gbase@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]$ cat conf/conf.properties 
gbase.gt.port=9898
gbase.gt.encode=gbk
gbase.gt.pagesize=1000
#load data type : batch=1, insert values=0
gbase.gt.load.data.type=1
gbase.gt.table.use.decimal=1
#paging query : not=0, yes=1;default=0
gbase.gt.gc.paging.query=0
gbase.gt.st.paging.query=0
gbase.gt.orcl.paging.query=0
#commit type : transaction commit=0, paging commit=1
gbase.gt.commit.type=0
#timeout
gbase.gt.wait.timeout=7200
#timeout to fetch gcluster datasource(second) : default=108000
gbase.gt.gc.fetch.timeout=108000
#thread pool type: 0-fixed thread pool 1-thread pool executor
gbase.gt.thread.pool.type=0
#thread pool size(gbase.gt.thread.pool.type=0 effect)
gbase.gt.thread.pool.size=5000
#core pool size(gbase.gt.thread.pool.type=1 effect)
gbase.gt.core.pool.size=200
#maximum pool size(gbase.gt.thread.pool.type=1 effect)
gbase.gt.maximum.pool.size=5000
#keep alive time(gbase.gt.thread.pool.type=1 effect)
gbase.gt.keep.alive.time=0
#queue size(gbase.gt.thread.pool.type=1 effect)
gbase.gt.queue.size=10
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

（2）gbase8a_gcluster.properties

我这边的集群是一个管理节点，两个数据节点，我们都要写到这个配置文件里。

[gbase@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]$ gcadmin
CLUSTER STATE:  ACTIVE
CLUSTER MODE:   NORMAL

=====================================================================
|               GBASE COORDINATOR CLUSTER INFORMATION               |
=====================================================================
|   NodeName   |       IpAddress       |gcware |gcluster |DataState |
---------------------------------------------------------------------
| coordinator1 |    192.168.142.10     | OPEN  |  OPEN   |    0     |
---------------------------------------------------------------------
=================================================================
|                GBASE DATA CLUSTER INFORMATION                 |
=================================================================
|NodeName |       IpAddress       |gnode |syncserver |DataState |
-----------------------------------------------------------------
|  node1  |    192.168.142.10     | OPEN |   OPEN    |    0     |
-----------------------------------------------------------------
|  node2  |    192.168.142.11     | OPEN |   OPEN    |    0     |
-----------------------------------------------------------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

[gbase@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]$ cat conf/gcluster/gbase8a_gcluster.properties 
[gc1]
gcluster_IP=192.168.142.10
gcluster_port=5258
gcluster_user=root
gcluster_pwd=qwer1234
gcluster_encode=utf-8
[gn1]
gcluster_IP=192.168.142.10
gcluster_port=5050
gcluster_user=root
gcluster_pwd=qwer1234
gcluster_encode=utf-8
[gn2]
gcluster_IP=192.168.142.11
gcluster_port=5050
gcluster_user=root
gcluster_pwd=qwer1234
gcluster_encode=utf-8
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

（3）oracle_link1.properties

oracle_link1.properties需要和sample文件同一层。

[gbase@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]$ cat conf/dataSource/oracle_link1.properties 
[ds1]
dataSource_dbtype=oracle
dataSource_IP=192.168.142.11
dataSource_port=1521
dataSource_dbname=orcl
dataSource_user=czg
dataSource_pwd=qwer1234
1
2
3
4
5
6
7
8

（4）启动程序

[root@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]# chown -R gbase:gbase /opt/pkg/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.1

[root@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]# su - gbase
上一次登录：一 8月 15 16:00:06 CST 2022pts/0 上

[gbase@xdw0 ~]$ cd /opt/pkg/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15/

[gbase@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]$ sh gt.sh 

[gbase@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]$ ps -ef|grep java
gbase     24603      1  5 16:55 pts/0    00:00:00 java -Dfile.encoding=UTF-8 -Dcom.sun.management.jmxremote -Xmx2048m -cp gateway/gbaseGateway.jar:lib/commons-beanutils-1.7.0.jar:lib/commons-collections-3.2.1.jar:lib/commons-lang-2.4.jar:lib/commons-logging-1.1.jar:lib/commons-net-1.4.1.jar:lib/dom4j-1.6.1.jar:lib/ezmorph-1.0.6.jar:lib/gbase-connector-java-8.3.81.53-build55.5.5-bin.jar:lib/jaxen-1.1-beta-6.jar:lib/json-lib-2.4-jdk15.jar:lib/jtds-1.2.5.jar:lib/log4j-1.2.15.jar:lib/ojdbc8.jar:lib/commons-configuration-1.8.jar:lib/oscarJDBC.jar:lib/oscarClusterJDBC.jar cn.com.gbase.gbaseGateway.server.GBaseGateway
gbase     24624  24537  0 16:56 pts/0    00:00:00 grep --color=auto java
1
2
3
4
5
6
7
8
9
10
11
12

（5）修改gcluster层参数

每个管理节点都需要添加此参数。
我的dblink程序是在192.168.142.10节点启动，所以填写此IP。

添加这两个参数到/opt/gcluster/config/gbase_8a_gcluster.cnf中

gbase_dblink_gateway_ip = '192.168.142.10'
gbase_dblink_gateway_port = 9898
1
2

我是放在[gbased]下

（6）重启服务

我这边是重启了所有节点的服务，但应该是只重启所有管理节点的服务。

[root@xdw0 dataSource]# service gcware restart
Stopping GCMonit success!
Signaling GCRECOVER (gcrecover) to terminate:              [  确定  ]
Waiting for gcrecover services to unload:...               [  确定  ]
Signaling GCSYNC (gc_sync_server) to terminate:            [  确定  ]
Waiting for gc_sync_server services to unload:             [  确定  ]
Signaling GCLUSTERD  to terminate:                         [  确定  ]
Waiting for gclusterd services to unload:.....             [  确定  ]
Signaling GBASED  to terminate:                            [  确定  ]
Waiting for gbased services to unload:...                  [  确定  ]
Signaling GCWARE (gcware) to terminate:                    [  确定  ]
Waiting for gcware services to unload:..                   [  确定  ]
Starting GCWARE (gcwexec):                                 [  确定  ]
Starting GBASED    :                                       [  确定  ]
Starting GCSYNC :                                          [  确定  ]
Starting GCLUSTERD :                                       [  确定  ]
Starting GCRECOVER :                                       [  确定  ]
Starting GCMonit success!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

（7）gbase创建用户

gbase> create user czg identified by 'qwer1234';
Query OK, 0 rows affected (Elapsed: 00:00:00.11)

gbase> grant all privileges on *.* to 'czg'@'%';
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> flush privileges;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
1
2
3
4
5
6
7
8

（8）创建Dblink

oracle_link1这个名称和oracle_link1.properties的前缀要一样。

gbase> CREATE DATABASE LINK dblink_oracle connect to '' identified by '' using 'oracle_link1';
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
1
2

（9）测试Dblink

gbase> select * from (select * from test@dblink_oracle) as a;
+------+------+
| ID   | NAME |
+------+------+
|    1 | czg  |
|    2 | zxj  |
+------+------+
2 rows in set (Elapsed: 00:00:01.05)
1
2
3
4
5
6
7
8