OceanBase2.2集群——oracle租户的使用体验

一步一步学习OceanBase系列 本篇是oceanbase2.2版本实操第三篇 OB2.2 oracle租户的使用
oceanbase2.2版本实操第一篇：OceanBase 2.2集群初体验——手动安装ob2.2集群
oceanbase2.2版本实操第二篇：OceanBase2.2集群——MySQL租户的使用体验

本系列总的专栏：一步一步学习OceanBase系列

oceanbase2.2集群——oracle租户的使用体验

1、创建oracle兼容类型的租户

注意：
（1）、创建资源池，资源池最小规格要求是1c5g，如果不满足最小资源要求，就会报错。详见上一篇mysql租户的体验文章。
MySQL [oceanbase]> create resource pool mysql_pool_test unit = 'my_unit_1c2g', unit_num = 1;
ERROR 1235 (0A000): unit min memory less than __min_full_resource_pool_memory not supported
MySQL [oceanbase]> 

（2）、另外，还要注意，每个资源池，指定的unit_num数量，不能超过每个zone下的observer服务器个数，否则也会报错。
可以创建不同的资源池，每个不同的资源池对应的unit单元规格可以相同。详见上一篇mysql租户的体验文章。
MySQL [oceanbase]> create resource pool mysql_pool_test unit = 'my_unit_1c2g', unit_num =2;
ERROR 4656 (HY000): resource pool unit num is bigger than zone server count
MySQL [oceanbase]> 

（3）、在上一篇mysql租户的体验文章里也说了，同一个资源池resource pool 只能属于一个租户。也就是说，一个资源池已经分配给某个租户了，不能再分配给其它租户。不然会报以下错误：
MySQL [oceanbase]> create tenant ora_test_tent resource_pool_list=('mysql_pool_test'), primary_zone='RANDOM',comment 'oracle tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='oracle';
ERROR 4626 (HY000): resource pool 'mysql_pool_test' has already been granted to a tenant
MySQL [oceanbase]> 


开始创建资源单元和资源池：
$ mysql -h172.16.18.81 -uroot@sys#obdemo -P2883 -padmin123 -c -A oceanbase
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.6.25 OceanBase 2.2.30 (r20200515183156-366007805d18f72e1fbd8568ada91849305c53ac) (Built May 15 2020 19:45:08)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [oceanbase]>
MySQL [oceanbase]> select unit_config_id,name,max_cpu,min_cpu,round(max_memory/1024/1024/1024) max_mem_gb, round(min_memory/1024/1024/1024) min_mem_gb, round(max_disk_size/1024/1024/1024) max_disk_size_gb
    -> from __all_unit_config
    -> order by unit_config_id;
+----------------+-----------------+---------+---------+------------+------------+------------------+
| unit_config_id | name            | max_cpu | min_cpu | max_mem_gb | min_mem_gb | max_disk_size_gb |
+----------------+-----------------+---------+---------+------------+------------+------------------+
|              1 | sys_unit_config |       5 |     2.5 |         13 |         11 |               40 |
|           1002 | my_unit_1c5g    |       1 |       1 |          5 |          5 |               10 |
+----------------+-----------------+---------+---------+------------+------------+------------------+
2 rows in set (0.00 sec)

MySQL [oceanbase]>


这里基于已有的资源单元unit，创建一个新的资源池，供oracle租户使用：
MySQL [oceanbase]> create resource pool ora_pool_test unit = 'my_unit_1c5g', unit_num = 1;
Query OK, 0 rows affected (0.08 sec)

MySQL [oceanbase]> select t1.name resource_pool_name, t2.`name` unit_config_name, t2.max_cpu, t2.min_cpu, 
    -> round(t2.max_memory/1024/1024/1024) max_mem_gb, round(t2.min_memory/1024/1024/1024) min_mem_gb, 
    -> t3.unit_id, t3.zone, concat(t3.svr_ip,':',t3.`svr_port`) observer,t4.tenant_id, t4.tenant_name
    -> from __all_resource_pool t1 join __all_unit_config t2 on (t1.unit_config_id=t2.unit_config_id)
    ->     join __all_unit t3 on (t1.`resource_pool_id` = t3.`resource_pool_id`)
    ->     left join __all_tenant t4 on (t1.tenant_id=t4.tenant_id)
    -> order by t1.`resource_pool_id`, t2.`unit_config_id`, t3.unit_id
    -> ;
+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-----------------+
| resource_pool_name | unit_config_name | max_cpu | min_cpu | max_mem_gb | min_mem_gb | unit_id | zone  | observer          | tenant_id | tenant_name     |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-----------------+
| sys_pool           | sys_unit_config  |       5 |     2.5 |         13 |         11 |       1 | zone1 | 172.16.18.81:2882 |         1 | sys             |
| sys_pool           | sys_unit_config  |       5 |     2.5 |         13 |         11 |       2 | zone2 | 172.16.18.82:2882 |         1 | sys             |
| sys_pool           | sys_unit_config  |       5 |     2.5 |         13 |         11 |       3 | zone3 | 172.16.18.91:2882 |         1 | sys             |
| mysql_pool_test    | my_unit_1c5g     |       1 |       1 |          5 |          5 |    1001 | zone1 | 172.16.18.81:2882 |      1001 | mysql_test_tent |
| mysql_pool_test    | my_unit_1c5g     |       1 |       1 |          5 |          5 |    1002 | zone2 | 172.16.18.82:2882 |      1001 | mysql_test_tent |
| mysql_pool_test    | my_unit_1c5g     |       1 |       1 |          5 |          5 |    1003 | zone3 | 172.16.18.91:2882 |      1001 | mysql_test_tent |
| ora_pool_test      | my_unit_1c5g     |       1 |       1 |          5 |          5 |    1004 | zone1 | 172.16.18.81:2882 |      NULL | NULL            |
| ora_pool_test      | my_unit_1c5g     |       1 |       1 |          5 |          5 |    1005 | zone2 | 172.16.18.82:2882 |      NULL | NULL            |
| ora_pool_test      | my_unit_1c5g     |       1 |       1 |          5 |          5 |    1006 | zone3 | 172.16.18.91:2882 |      NULL | NULL            |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-----------------+
9 rows in set (0.00 sec)

MySQL [oceanbase]> 


创建oracle类型的租户
创建租户的需指定租户使用的资源池、数据分布策略(primary_zone为RANDOM)、租户字符集(默认utf8，也可以改为gbk)、租户访问白名单(ob_tcp_invited_nodes)、租户兼容级别(ob_compatibility_mode)。

MySQL [oceanbase]> create tenant ora_test_tent resource_pool_list=('ora_pool_test'), primary_zone='RANDOM',comment 'oracle tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='oracle';
Query OK, 0 rows affected (4.06 sec)

MySQL [oceanbase]> 

查看OB集群所有租户信息: 租户类别compatibility_mode，0为mysql兼容模式的租户， 1为oracle兼容模式的租户
MySQL [oceanbase]> select tenant_id, tenant_name, compatibility_mode,zone_list, locality ,gmt_modified from __all_tenant;
+-----------+-----------------+--------------------+-------------------+---------------------------------------------+----------------------------+
| tenant_id | tenant_name     | compatibility_mode | zone_list         | locality                                    | gmt_modified               |
+-----------+-----------------+--------------------+-------------------+---------------------------------------------+----------------------------+
|         1 | sys             |                  0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 2020-08-12 12:05:00.986146 |
|      1001 | mysql_test_tent |                  0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 2020-08-12 15:30:23.097537 |
|      1003 | ora_test_tent   |                  1 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 2020-08-12 16:46:58.868624 |
+-----------+-----------------+--------------------+-------------------+---------------------------------------------+----------------------------+
3 rows in set (0.00 sec)

MySQL [oceanbase]> 


2、登录oracle租户，修改sys用户密码

对于ORACLE租户而言，默认用户名是sys，这点跟ORACLE一致。注意不要跟OB集群的SYS租户混淆。
登录sys用户，默认密码为空，登录后修改sys用户密码。
由于是oracle类型的租户，只能用obclient客户端连接(obclient工具可以访问OceanBase的MySQL和ORACLE租户)。

$ obclient -h172.16.18.81 -usys@ora_test_tent#obdemo -P2883 -p -c -A
Enter password: 
Welcome to the OceanBase monitor.  Commands end with ; or \g.
Your OceanBase connection id is 25
Server version: 5.6.25 OceanBase 2.2.30 (r20200515183156-366007805d18f72e1fbd8568ada91849305c53ac) (Built May 15 2020 19:45:08)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient> 
obclient> alter user sys identified by admin123;
Query OK, 0 rows affected (0.18 sec)

obclient> exit
Bye

验证sys密码修改成功
$ obclient -h172.16.18.81 -usys@ora_test_tent#obdemo -P2883 -padmin123 -c -A
obclient: [Warning] Using a password on the command line interface can be insecure.
Welcome to the OceanBase monitor.  Commands end with ; or \g.
Your OceanBase connection id is 26
Server version: 5.6.25 OceanBase 2.2.30 (r20200515183156-366007805d18f72e1fbd8568ada91849305c53ac) (Built May 15 2020 19:45:08)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient> select sysdate from dual;
+---------------------+
| SYSDATE             |
+---------------------+
| 2020-08-12 17:01:03 |
+---------------------+
1 row in set (0.01 sec)

obclient> 
obclient> select username from dba_users;
+------------+
| USERNAME   |
+------------+
| SYS        |
| LBACSYS    |
| ORAAUDITOR |
+------------+
3 rows in set (0.05 sec)

obclient> show grants for sys;
+--------------------------------------------------------+
| Grants for SYS@%                                       |
+--------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'SYS' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON "LBACSYS".* TO 'SYS'           |
| GRANT ALL PRIVILEGES ON "ORAAUDITOR".* TO 'SYS'        |
| GRANT ALL PRIVILEGES ON "SYS".* TO 'SYS'               |
| GRANT ALL PRIVILEGES ON "oceanbase".* TO 'SYS'         |
| GRANT ALL PRIVILEGES ON "__public".* TO 'SYS'          |
| GRANT ALL PRIVILEGES ON "__recyclebin".* TO 'SYS'      |
+--------------------------------------------------------+
7 rows in set (0.04 sec)

obclient> 


3、oracle租户初始化变量设置(重要)
默认OceanBase对oracle租户的超时时间设置较短，所以这里修改一下ORACLE租户的默认设置，尽可能的跟ORACLE保持一致。
如果不设置，连接时间超时会报错：ERROR-00600: internal error code, arguments: -6210, Transaction is timeout
本人也遇到了，因此建议设置。

$ obclient -h172.16.18.81 -usys@ora_test_tent#obdemo -P2883 -padmin123 -c -A
obclient: [Warning] Using a password on the command line interface can be insecure.
Welcome to the OceanBase monitor.  Commands end with ; or \g.
Your OceanBase connection id is 44
Server version: 5.6.25 OceanBase 2.2.30 (r20200515183156-366007805d18f72e1fbd8568ada91849305c53ac) (Built May 15 2020 19:45:08)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient> SHOW GLOBAL variables LIKE '%timeout%';
+---------------------+-----------+
| VARIABLE_NAME       | VALUE     |
+---------------------+-----------+
| connect_timeout     | 10        |
| interactive_timeout | 28800     |
| net_read_timeout    | 30        |
| net_write_timeout   | 60        |
| ob_query_timeout    | 10000000  |
| ob_trx_idle_timeout | 120000000 |
| ob_trx_timeout      | 100000000 |
| wait_timeout        | 28800     |
+---------------------+-----------+
8 rows in set (0.03 sec)

obclient>
obclient> SET GLOBAL ob_query_timeout = 10000000000;
Query OK, 0 rows affected (0.17 sec)

obclient> SET GLOBAL ob_trx_idle_timeout = 12000000000;
Query OK, 0 rows affected (0.12 sec)

obclient> SET GLOBAL ob_trx_timeout = 10000000000;
Query OK, 0 rows affected (0.09 sec)

obclient> SHOW GLOBAL variables WHERE variable_name IN ('ob_query_timeout', 'ob_trx_idle_timeout', 'ob_trx_timeout');
+---------------------+-------------+
| VARIABLE_NAME       | VALUE       |
+---------------------+-------------+
| ob_query_timeout    | 10000000000 |
| ob_trx_idle_timeout | 12000000000 |
| ob_trx_timeout      | 10000000000 |
+---------------------+-------------+
3 rows in set (0.01 sec)

obclient> exit
Bye
$

4、oracle租户下，创建用户，并授权。

对于oracle类型的租户，使用和oracle一样，可以创建多个schemas，然后在schema里创建业务表等对象。
oracle租户下的不同用户(schemas)，在oceanbase里其实对应就是mysql租户的不同database。

$ obclient -h172.16.18.81 -usys@ora_test_tent#obdemo -P2883 -padmin123 -c -A
obclient: [Warning] Using a password on the command line interface can be insecure.
Welcome to the OceanBase monitor.  Commands end with ; or \g.
Your OceanBase connection id is 27
Server version: 5.6.25 OceanBase 2.2.30 (r20200515183156-366007805d18f72e1fbd8568ada91849305c53ac) (Built May 15 2020 19:45:08)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient> 
obclient> create user aps2 identified by aps2#12345;
Query OK, 0 rows affected (0.21 sec)

obclient>
obclient> grant all privileges on aps2.* to aps2 with grant option;
Query OK, 0 rows affected (0.18 sec)

obclient> grant select,create ,drop  on *.* to aps2;
Query OK, 0 rows affected (0.09 sec)

obclient> show grants for aps2;
+--------------------------------------------------------------+
| Grants for APS2@%                                            |
+--------------------------------------------------------------+
| GRANT CREATE, DROP, SELECT ON *.* TO 'APS2'                  |
| GRANT ALL PRIVILEGES ON "APS2".* TO 'APS2' WITH GRANT OPTION |
+--------------------------------------------------------------+
2 rows in set (0.02 sec)

obclient>


5、登录新用户，创建表对象等。

$ obclient -h172.16.18.81 -uaps2@ora_test_tent#obdemo -P2883 -paps2#12345 -c -A 
obclient: [Warning] Using a password on the command line interface can be insecure.
Welcome to the OceanBase monitor.  Commands end with ; or \g.
Your OceanBase connection id is 31
Server version: 5.6.25 OceanBase 2.2.30 (r20200515183156-366007805d18f72e1fbd8568ada91849305c53ac) (Built May 15 2020 19:45:08)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient>  

创建一个非分区表test
obclient> create table test(id int,name varchar2(20),age int,dt date);
Query OK, 0 rows affected (0.75 sec)

obclient> desc test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| ID    | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
| NAME  | VARCHAR2(20) | YES  | NULL | NULL    | NULL  |
| AGE   | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
| DT    | DATE         | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.04 sec)

obclient> show create table test;
+-------+----------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------+
| TEST  | CREATE TABLE "TEST" (
  "ID" NUMBER(38),
  "NAME" VARCHAR2(20),
  "AGE" NUMBER(38),
  "DT" DATE
) COMPRESS FOR ARCHIVE REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10  |
+-------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

obclient> 
obclient> insert into test values(1,'ob2.2-oracle',20,sysdate);
Query OK, 1 row affected (0.04 sec)

obclient> insert into test values(2,'张三',26,to_date('1998-06-17','yyyy-mm-dd'));
Query OK, 1 row affected (0.01 sec)

obclient> insert into test values(3,'李四',32,to_date('1989-10-13','yyyy-mm-dd'));
Query OK, 1 row affected (0.01 sec)

obclient> 

obclient> commit;
Query OK, 0 rows affected (0.03 sec)

记住：oracle租户里，事务一定要显示commit提交，否则会话退出后，事务不会自动提交而是回滚。
本人也遇到了，很尬尬，明明插入了数据，怎么退出后重新登录查询，数据就没了。哈哈~~~
可以登录oracle租户sys用户，查看show global variables like '%commit%';

obclient>
obclient> select * from test;
+------+--------------+------+---------------------+
| ID   | NAME         | AGE  | DT                  |
+------+--------------+------+---------------------+
|    1 | ob2.2-oracle |   20 | 2020-08-12 18:05:03 |
|    2 | 张三         |   26 | 1998-06-17 00:00:00 |
|    3 | 李四         |   32 | 1989-10-13 00:00:00 |
+------+--------------+------+---------------------+
3 rows in set (0.01 sec)

obclient> 

创建一个hash分区表
obclient> create table test_hash(id int not null,name varchar2(20) not null,age int,dt date default sysdate)
    -> partition by hash(id)
    -> partitions 3;
Query OK, 0 rows affected (0.58 sec)

obclient> insert into test_hash values(1,'ob2.2-oracle',20,sysdate);
Query OK, 1 row affected (0.22 sec)

obclient> insert into test_hash values(2,'张三',26,to_date('1998-06-17','yyyy-mm-dd'));
Query OK, 1 row affected (0.14 sec)

obclient> insert into test_hash values(3,'李四',32,to_date('1989-10-13','yyyy-mm-dd'));
Query OK, 1 row affected (0.20 sec)

obclient> 
obclient> commit;
Query OK, 0 rows affected (0.03 sec)

obclient>
obclient> select * from test_hash;
+----+--------------+------+---------------------+
| ID | NAME         | AGE  | DT                  |
+----+--------------+------+---------------------+
|  2 | 张三         |   26 | 1998-06-17 00:00:00 |
|  1 | ob2.2-oracle |   20 | 2020-08-12 18:21:53 |
|  3 | 李四         |   32 | 1989-10-13 00:00:00 |
+----+--------------+------+---------------------+
3 rows in set (0.01 sec)

obclient> select * from test;
+------+--------------+------+---------------------+
| ID   | NAME         | AGE  | DT                  |
+------+--------------+------+---------------------+
|    1 | ob2.2-oracle |   20 | 2020-08-12 18:31:34 |
|    2 | 张三         |   26 | 1998-06-17 00:00:00 |
|    3 | 李四         |   32 | 1989-10-13 00:00:00 |
+------+--------------+------+---------------------+
3 rows in set (0.00 sec)

obclient> exit
Bye
$


6、查看oracle租户下的表分区主副本和备副本所在的节点。

$ obclient -h172.16.18.81 -uroot@sys#obdemo -P2883 -padmin123 -c -A oceanbase
obclient: [Warning] Using a password on the command line interface can be insecure.
Welcome to the OceanBase monitor.  Commands end with ; or \g.
Your OceanBase connection id is 42
Server version: 5.6.25 OceanBase 2.2.30 (r20200515183156-366007805d18f72e1fbd8568ada91849305c53ac) (Built May 15 2020 19:45:08)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

查看OB集群所有租户信息：租户类别compatibility_mode，0为mysql兼容模式的租户， 1为oracle兼容模式的租户
obclient> select tenant_id, tenant_name, compatibility_mode,zone_list, locality ,gmt_modified from __all_tenant;
+-----------+-----------------+--------------------+-------------------+---------------------------------------------+----------------------------+
| tenant_id | tenant_name     | compatibility_mode | zone_list         | locality                                    | gmt_modified               |
+-----------+-----------------+--------------------+-------------------+---------------------------------------------+----------------------------+
|         1 | sys             |                  0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 2020-08-12 12:05:00.986146 |
|      1001 | mysql_test_tent |                  0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 2020-08-12 15:30:23.097537 |
|      1003 | ora_test_tent   |                  1 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 2020-08-12 16:46:58.868624 |
+-----------+-----------------+--------------------+-------------------+---------------------------------------------+----------------------------+
3 rows in set (0.01 sec)

obclient> SELECT  t1.tenant_id,t1.tenant_name,t2.database_name,t3.table_id,t3.table_Name,t3.tablegroup_id,t3.part_num,t4.partition_Id,
    ->     t4.zone,t4.svr_ip,t4.role, round(t4.data_size/1024/1024) data_size_mb
    -> from `gv$tenant` t1
    ->     join `gv$database` t2 on (t1.tenant_id = t2.tenant_id)
    ->     join gv$table t3 on (t2.tenant_id = t3.tenant_id    and t2.database_id = t3.database_id and t3.index_type = 0)
    ->     left join `gv$partition` t4 on (t2.tenant_id = t4.tenant_id and ( t3.table_id = t4.table_id or t3.tablegroup_id = t4.table_id ) and t4.role in (1,2))
    -> where t1.tenant_id = 1003
    -> order by t3.tablegroup_id, t3.table_name,t4.partition_Id ;
+-----------+---------------+---------------+------------------+------------+---------------+----------+--------------+-------+--------------+------+--------------+
| tenant_id | tenant_name   | database_name | table_id         | table_Name | tablegroup_id | part_num | partition_Id | zone  | svr_ip       | role | data_size_mb |
+-----------+---------------+---------------+------------------+------------+---------------+----------+--------------+-------+--------------+------+--------------+
|      1003 | ora_test_tent | APS2          | 1102810162709329 | TEST       |            -1 |        1 |            0 | zone1 | 172.16.18.81 |    1 |            0 |
|      1003 | ora_test_tent | APS2          | 1102810162709329 | TEST       |            -1 |        1 |            0 | zone2 | 172.16.18.82 |    2 |            0 |
|      1003 | ora_test_tent | APS2          | 1102810162709329 | TEST       |            -1 |        1 |            0 | zone3 | 172.16.18.91 |    2 |            0 |
|      1003 | ora_test_tent | APS2          | 1102810162709330 | TEST_HASH  |            -1 |        3 |            0 | zone1 | 172.16.18.81 |    2 |            0 |
|      1003 | ora_test_tent | APS2          | 1102810162709330 | TEST_HASH  |            -1 |        3 |            0 | zone2 | 172.16.18.82 |    1 |            0 |
|      1003 | ora_test_tent | APS2          | 1102810162709330 | TEST_HASH  |            -1 |        3 |            0 | zone3 | 172.16.18.91 |    2 |            0 |
|      1003 | ora_test_tent | APS2          | 1102810162709330 | TEST_HASH  |            -1 |        3 |            1 | zone1 | 172.16.18.81 |    2 |            0 |
|      1003 | ora_test_tent | APS2          | 1102810162709330 | TEST_HASH  |            -1 |        3 |            1 | zone2 | 172.16.18.82 |    2 |            0 |
|      1003 | ora_test_tent | APS2          | 1102810162709330 | TEST_HASH  |            -1 |        3 |            1 | zone3 | 172.16.18.91 |    1 |            0 |
|      1003 | ora_test_tent | APS2          | 1102810162709330 | TEST_HASH  |            -1 |        3 |            2 | zone1 | 172.16.18.81 |    1 |            0 |
|      1003 | ora_test_tent | APS2          | 1102810162709330 | TEST_HASH  |            -1 |        3 |            2 | zone2 | 172.16.18.82 |    2 |            0 |
|      1003 | ora_test_tent | APS2          | 1102810162709330 | TEST_HASH  |            -1 |        3 |            2 | zone3 | 172.16.18.91 |    2 |            0 |
+-----------+---------------+---------------+------------------+------------+---------------+----------+--------------+-------+--------------+------+--------------+
12 rows in set (0.36 sec)

obclient> 

以上结果可以看到: ora_test_tent 租户下aps2用户(库)中

非分区表test的p0号分区，主副本在zone1下的81节点，两个备副本在zone2的82节点和zone3的91节点。

分区表test_hash三个分区的主副本都在不同的zone下:
test_hash分区表的p0分区主副本在zone2下的82节点，两个备副本在zone1的81节点和zone3的91节点。
test_hash分区表的p1分区主副本在zone3下的91节点，两个备副本在zone1的81节点和zone2的82节点。
test_hash分区表的p2分区主副本在zone1下的81节点。两个备副本在zone2的82节点和zone3的91节点。


到此，OceanBase2.2集群——oracle租户的体验到此结束。

一步一步学习OceanBase系列

下次实操继续更新~~~


复制