OceanBase系列之–Step_by_step搭建单副本集群
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: OceanBase系列之–Step_by_step搭建单副本集群
今年年初oceanbase 发布了2.2版本，并且通过了权威机构tpcc测试，荣登全球No 1，可谓炙手可热；之前我是直接在官网https://oceanbase.alipay.com/download/resource 下载；不过刚刚看了一下，貌似暂时不提供下载了。由于oceanbase本身对于硬件要求极高；之前尝试了多次都无法初始化成功，因此这里我弄了一套配置相对较较高的虚拟机来做单副本集群测试(8c/40g).
- 创建admin用户;
- 解压软件，安装observer软件,略.
- 创建相关目录
|
1 2 3 4 5 6 |
su - admin mkdir -p /data/1/obdemo/{etc3,sort_dir,sstable} mkdir -p /data/log1/obdemo/{clog,etc2,ilog,slog,oob_clog} mkdir -p /home/admin/oceanbase/store/obdemo for t in {etc3,sort_dir,sstable};do ln -s /data/1/obdemo/$t /home/admin/oceanbase/store/obdemo/$t; done for t in {clog,etc2,ilog,slog,oob_clog};do ln -s /data/log1/obdemo/$t /home/admin/oceanbase/store/obdemo/$t; done |
4. 测试存储磁盘IO能力(这一步很重要)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[admin@td1 log]$ time /home/admin/oceanbase/bin/ob_admin io_bench -c /home/admin/oceanbase/etc -d /data/1/obdemo user:root succ to open, filename=ob_admin.log, fd=3, wf_fd=2 real 4m42.333s user 1m30.231s sys 0m32.084s [admin@td1 log]$ [admin@td1 log]$ cat /home/admin/oceanbase/etc/io_resource.conf version 1 io_type io_size_byte io_ps io_rt_us 0 4096 2892.50 6482.37 0 8192 2876.50 7022.35 0 16384 2705.25 7075.50 0 32768 2350.50 6990.87 0 65536 1932.25 7273.85 0 131072 598.50 9973.00 0 262144 451.25 17096.54 0 524288 319.75 20875.44 1 2097152 107.75 6117.22 submit_thread_cnt 4 getevent_thread_cnt 4 |
这是必备步骤，否则启动observer会报如下错误(你可以看到observer启动时会进行相关io配置的读取操作)：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[2020-05-26 23:23:56.392632] ERROR [SERVER] init_io (ob_server.cpp:882) [7113][0][Y0-0000000000000000] [lt=0] [dc=0] init io benchmark fail, (ret=-4027) BACKTRACE:0x726996a 0x72156c5 0x4114ae 0x1765f6a 0x4f2bac4 0x4f34f64 0x3fc342f 0x7f7af4c1fc05 0x40240c5 [2020-05-26 23:23:58.191834] WARN resolve_basic_table (ob_dml_resolver.cpp:1135) [7248][268][YB42C0A86529-0005A68EAF08FFB7] [lt=0] [dc=0] Table 'oceanbase.__tenant_parameter' doesn't exist [2020-05-26 23:23:58.197895] WARN resolve_basic_table (ob_dml_resolver.cpp:1135) [7884][1534][YB42C0A86529-0005A68EAF08FFB8] [lt=0] [dc=0] Table 'oceanbase.__all_root_table' doesn't exist ....... [2020-05-26 23:23:58.859245] WARN resolve_basic_table (ob_dml_resolver.cpp:1135) [7426][624][YB42C0A86529-0005A68EAF08FFD0] [lt=15] [dc=0] Table 'oceanbase.__all_root_table' doesn't exist [2020-05-26 23:23:58.860010] WARN resolve_basic_table (ob_dml_resolver.cpp:1135) [7930][1626][YB42C0A86529-0005A68EAF08FFD1] [lt=0] [dc=0] Table 'oceanbase.__all_unit' doesn't exist [2020-05-26 23:23:58.000] easy_baseth_pool.c:310(tid:7f7af6c9c8c0)[7113] monitor thread created, tp=0x7f7a0a4e4ce0 tid=7f7734a8c700 [2020-05-26 23:23:58.000] easy_baseth_pool.c:232(tid:7f7734a8c700)[7943] monitor us :100000 sec :0.100000 [2020-05-26 23:23:58.000] easy_baseth_pool.c:310(tid:7f7af6c9c8c0)[7113] monitor thread created, tp=0x7f79bb139e30 tid=7f7721eca700 [2020-05-26 23:23:58.000] easy_baseth_pool.c:232(tid:7f7721eca700)[7956] monitor us :100000 sec :0.100000 [2020-05-26 23:23:58.000] easy_baseth_pool.c:310(tid:7f7af6c9c8c0)[7113] monitor thread created, tp=0x7f7a0a5e2390 tid=7f770f6c9700 [2020-05-26 23:23:58.000] easy_baseth_pool.c:232(tid:7f770f6c9700)[7969] monitor us :100000 sec :0.100000 |
5. 启动observer
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[admin@td1 oceanbase]$ cd /home/admin/oceanbase && /home/admin/oceanbase/bin/observer -i ens192 -P 2882 -p 2881 -z zone1 -d /home/admin/oceanbase/store/obdemo -r '192.168.101.41:2882:2881' -c 20200525 -n obdemo -l ERROR -o "memory_limit=30G,system_memory=15G,datafile_size=60G,config_additional_dir=/data/1/obdemo/etc3;/data/log1/obdemo/etc2" /home/admin/oceanbase/bin/observer -i ens192 -P 2882 -p 2881 -z zone1 -d /home/admin/oceanbase/store/obdemo -r 192.168.101.41:2882:2881 -c 20200525 -n obdemo -l ERROR -o memory_limit=30G,system_memory=15G,datafile_size=60G,config_additional_dir=/data/1/obdemo/etc3;/data/log1/obdemo/etc2 devname: ens192 rpc port: 2882 mysql port: 2881 zone: zone1 data_dir: /home/admin/oceanbase/store/obdemo rs list: 192.168.101.41:2882:2881 cluster id: 20200525 appname: obdemo log level: ERROR optstr: memory_limit=36G,system_memory=10G,datafile_size=60G,config_additional_dir=/data/1/obdemo/etc3;/data/log1/obdemo/etc2 [admin@td1 oceanbase]$ |
由于我这里虚拟机配置不高；因此需要限制memory内存和相关存储空间大小；针对上述几个参数进行简单说明：
1) memory_limit: 控制整个ob的内存使用,最大值为36GB(我这里物理内存为40g)
2) system_memory为ob保留内存，可以理解为给软件本身使用的，不能给租户使用;
而且这部分内存不能太小，最低为10g大小，否则会启动失败；
3)datafile_size: 指定整个数据库实例所能使用的最大存储空间为60Gb;
4)由于默认ob的log_level是info，导致日志刷新很快，因此我修改为了error；仅记录关键信息
如果参数设置过小，就会遇到如下类似错误：
“ERROR 1235 (0A000) at line 1: unit min memory less than __min_full_resource_pool_memory not supported”
6. 初始集群单副本
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[admin@td1 oceanbase]$ mysql -h127.1 -uroot -P2881 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3221225472 Server version: 5.7.25 OceanBase 2.2.30 (r1872059-defe4fdc46b03542cd4195a67d7e55ce564ba716) (Built Mar 16 2020 22:43:07) Copyright (c) 2000, 2020, 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. mysql> set session ob_query_timeout=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> alter system bootstrap ZONE 'zone1' SERVER '192.168.101.41:2882'; Query OK, 0 rows affected (20.13 sec) mysql> |
由于默认ob_query_timeout为100s;一般建议改大一点，防止bootstrap失败。另外我这里只有一个节点，因此指定的server就写一个就好了。
初始化成功之后，可以通过mysql客户端登录并进行验证：
|
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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
[admin@td1 oceanbase]$ mysql -h127.1 -uroot@sys -P2881 -p -c -A Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3221487670 Server version: 5.7.25 OceanBase 2.2.30 (r1872059-defe4fdc46b03542cd4195a67d7e55ce564ba716) (Built Mar 16 2020 22:43:07) Copyright (c) 2000, 2020, 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. mysql> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | SYS | | LBACSYS | | ORAAUDITOR | | test | +--------------------+ 7 rows in set (0.00 sec) mysql> mysql> alter user root identified by "enmotech"; Query OK, 0 rows affected (0.02 sec) mysql> alter system set enable_syslog_recycle=True; Query OK, 0 rows affected (0.02 sec) mysql> alter system set max_syslog_file_count=10; Query OK, 0 rows affected (0.02 sec) mysql> show parameters where name in ('enable_syslog_recycle', 'max_syslog_file_count'); +-------+----------+----------------+----------+-----------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+ | zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | +-------+----------+----------------+----------+-----------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+ | zone1 | observer | 192.168.101.41 | 2882 | enable_syslog_recycle | NULL | True | specifies whether log file recycling is turned on. Value: True：turned on; False: turned off | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | | zone1 | observer | 192.168.101.41 | 2882 | max_syslog_file_count | NULL | 10 | specifies the maximum number of the log files that can co-exist before the log file recycling kicks in. Each log file can occupy at most 256MB disk space. When this value is set to 0, no log file will be removed. Range: [0, +∞) in integer | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | +-------+----------+----------------+----------+-----------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+ 2 rows in set (0.01 sec) |
第一次登录时sys密码为空，因此首次登录后建议修改密码。
7. 创建obproxy账户
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> create user 'proxyro' IDENTIFIED BY '3u^0kCdpE'; Query OK, 0 rows affected (0.02 sec) mysql> grant select on oceanbase.* to proxyro IDENTIFIED BY '3u^0kCdpE'; Query OK, 0 rows affected (0.03 sec) mysql> show grants for proxyro; +--------------------------------------------+ | Grants for proxyro@% | +--------------------------------------------+ | GRANT USAGE ON *.* TO 'proxyro' | | GRANT SELECT ON `oceanbase`.* TO 'proxyro' | +--------------------------------------------+ 2 rows in set (0.01 sec) |
用户创建成功之后，然后rpm安装obproxy即可。 注意这里obproxy的密码创建似乎是固定的，如果指定其他password，后面可能导致无法连接。不知道是不是因为是测试版本的缘故？
8. 创建资源池
首先我们检查一下资源池的情况：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
obclient> use oceanbase; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed obclient> obclient> select a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, (cpu_total-cpu_assigned) cpu_free, round(mem_total/1024/1024/1024) mem_total_gb, round((mem_total-mem_assigned)/1024/1024/1024) mem_free_gb, usec_to_time(b.last_offline_time) last_offline_time, usec_to_time(b.start_service_time) start_service_time -> from __all_virtual_server_stat a join __all_server b on (a.svr_ip=b.svr_ip and a.svr_port=b.svr_port) -> order by a.zone, a.svr_ip -> ; +-------+---------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+ | zone | observer | cpu_total | cpu_free | mem_total_gb | mem_free_gb | last_offline_time | start_service_time | +-------+---------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+ | zone1 | 192.168.101.41:2882 | 6 | 3.5 | 26 | 20 | 1970-01-01 08:00:00.000000 | 2020-05-26 23:57:16.630368 | +-------+---------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+ 1 row in set (0.01 sec) |
很明显我这里的zone1 server可用cpu只有3.5了；总的可用内存仅为20G了。因此在后面创建租户时需要注意，否则会报错。那么另外的资源去哪儿了呢？
|
1 2 3 4 5 6 7 8 9 10 11 12 |
obclient> 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 | 8 | 7 | 1 | zone1 | 192.168.101.41:2882 | 1 | sys | +--------------------+------------------+---------+---------+------------+------------+---------+-------+---------------------+-----------+-------------+ 1 row in set (0.01 sec) |
剩下这部分被系统本身给消耗了。这部分内存消耗还不低呢。
通过obclient登录去创建资源池和租户：
obclient -h127.1 -uroot -P2881 -p
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
obclient> CREATE resource unit my_unit_config max_cpu=4, min_cpu=2, max_memory='4G', min_memory='2G', max_iops=500, min_iops=200, max_session_num=300, max_disk_size='20G'; Query OK, 0 rows affected (0.01 sec) obclient> obclient> CREATE resource pool bmsql_pool unit = 'my_unit_config', unit_num = 1; ERROR 1235 (0A000): unit min memory less than __min_full_resource_pool_memory not supported obclient> obclient> ALTER resource unit my_unit_config max_cpu=2, min_cpu=1, max_memory='12G', min_memory='10G'; Query OK, 0 rows affected (2.77 sec) obclient> create resource pool oboracle_pool unit = 'my_unit_config', unit_num = 1; Query OK, 0 rows affected (0.01 sec) obclient> obclient> create tenant oboracle resource_pool_list=('oboracle_pool'), primary_zone='RANDOM',comment 'oracle tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='oracle'; Query OK, 0 rows affected (0.93 sec) obclient> |
ok. Oracle租户我们已经创建成功了。
9. 启动obproxy
这里需要注意，obproxy就类似Oracle listener，只不过obproxy本身可以支持集群模式，也可以理解为一个代理；通过代理来转发应用连接到底层数据库。
|
1 2 3 4 5 6 7 |
[root@td1 install]# cd /opt/taobao/install/obproxy && bin/obproxy -r "192.168.101.41:2881" -p 2883 -o "enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false" -c obdemo bin/obproxy -r 192.168.101.41:2881 -p 2883 -o enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false -c obdemo rs list: 192.168.101.41:2881 listen port: 2883 optstr: enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false cluster_name: obdemo [root@td1 obproxy]# |
10. 登录Oracle租户
|
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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
[admin@td1 obproxy]$ obclient -h192.168.101.41 -usys@oboracle#obdemo -P2883 -p Enter password: Welcome to the OceanBase monitor. Commands end with ; or \g. Your OceanBase connection id is 1 Server version: 5.6.25 OceanBase 2.2.30 (r1872059-defe4fdc46b03542cd4195a67d7e55ce564ba716) (Built Mar 16 2020 22:43:07) 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> alter user sys identified by 'enmotech'; ERROR-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ''enmotech'' at line 1 obclient> obclient> alter user sys identified by enmotech; Query OK, 0 rows affected (0.02 sec) obclient> exit Bye [admin@td1 obproxy]$ obclient -h192.168.101.41 -usys@oboracle#obdemo -P2883 -p Enter password: Welcome to the OceanBase monitor. Commands end with ; or \g. Your OceanBase connection id is 5 Server version: 5.6.25 OceanBase 2.2.30 (r1872059-defe4fdc46b03542cd4195a67d7e55ce564ba716) (Built Mar 16 2020 22:43:07) 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> create user roger identified by roger; Query OK, 0 rows affected (0.01 sec) obclient> grant all privileges on *.* to roger with grant option; Query OK, 0 rows affected (0.02 sec) obclient> grant dba to roger; ERROR-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'dba to roger' at line 1 obclient> grant sysdba to roger; ERROR-01919: role 'SYSDBA' does not exist obclient> obclient> grant drop,select,create,update,delete on *.* to roger; Query OK, 0 rows affected (0.01 sec) obclient> GRANT CREATE SYNONYM ON *.* TO roger; Query OK, 0 rows affected (0.01 sec) obclient> |
大家可以看到创建用户的语法跟Oracle操作类似，不过授权操作有一些差异；看上去没有role的概念。总的来说兼容性还是很不错的。
ok。 到这里基于oceanbase 的单副本集群就算部署完毕了，也创建了一个oracle租户。oceanbase的租户之间资源是严格隔离的，类似Oracle 12c pdb模式；这方面做得比MySQL 要好。之前看文章说oceanbase支持flashback操作；这里我先来测一把。
首先创建个测试表：
|
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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
obclient> drop table test; Query OK, 0 rows affected (0.01 sec) obclient> create table test (a number,b varchar2(20)); Query OK, 0 rows affected (0.04 sec) obclient> insert into test values(1,'aaa'); Query OK, 1 row affected (0.00 sec) obclient> insert into test values(2,'bbb'); Query OK, 1 row affected (0.01 sec) obclient> insert into test values(3,''); Query OK, 1 row affected (0.01 sec) obclient> insert into test values(3,null); Query OK, 1 row affected (0.00 sec) obclient> commit; Query OK, 0 rows affected (0.00 sec) obclient> select * from test; +------+------+ | A | B | +------+------+ | 1 | aaa | | 2 | bbb | | 3 | NULL | | 3 | NULL | +------+------+ 4 rows in set (0.00 sec) obclient> select * from test where b is null; +------+------+ | A | B | +------+------+ | 3 | NULL | | 3 | NULL | +------+------+ 2 rows in set (0.00 sec) obclient> select * from test where b is not null; ERROR-00600: internal error code, arguments: -6210, Transaction is timeout obclient> obclient> create index idx_test_a on test(a); ERROR-00600: internal error code, arguments: -6210, Transaction is timeout obclient> create index idx_test_b on test(b); Query OK, 0 rows affected (0.42 sec) obclient> create index idx_test_a on test(a); Query OK, 0 rows affected (0.43 sec) |
从上面的测试看来，oceanbase的oracle模式，对于null的处理跟Oracle完全一致。
先来试试drop table：
|
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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
obclient> drop table test; Query OK, 0 rows affected (0.03 sec) obclient> flashback table test to before drop; ERROR-00942: table or view does not exist obclient> obclient> flashback table test to before drop; ERROR-00942: table or view does not exist obclient> show variables like '%recycle%'; +---------------+-------+ | VARIABLE_NAME | VALUE | +---------------+-------+ | recyclebin | ON | +---------------+-------+ 1 row in set (0.00 sec) obclient> show RECYCLEBIN; +-------------------------------------+-----------------------------------+-------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-------------------------------------+-----------------------------------+-------+----------------------------+ | RECYCLE_$_20200525_1590571477017768 | TEST | TABLE | 2020-05-27 17:24:37.018219 | | RECYCLE_$_20200525_1590574534815168 | __idx_1100611139453782_IDX_TEST_B | INDEX | 2020-05-27 18:15:34.815276 | | RECYCLE_$_20200525_1590574534818480 | __idx_1100611139453782_IDX_TEST_A | INDEX | 2020-05-27 18:15:34.818556 | | RECYCLE_$_20200525_1590574534820792 | TEST | TABLE | 2020-05-27 18:15:34.820854 | +-------------------------------------+-----------------------------------+-------+----------------------------+ 4 rows in set (0.01 sec) obclient> FLASHBACK TABLE RECYCLE_$_20200525_1590574534820792 to before drop; Query OK, 0 rows affected (0.01 sec) obclient> select table_name,index_name,index_type from dba_indexes where table_name='TEST'; +------------+--------------------------------+------------+ | TABLE_NAME | INDEX_NAME | INDEX_TYPE | +------------+--------------------------------+------------+ | TEST | RECYCLE_OBIDX_1590578713779249 | NORMAL | | TEST | RECYCLE_OBIDX_1590578713782638 | NORMAL | +------------+--------------------------------+------------+ 2 rows in set (0.01 sec) obclient> show RECYCLEBIN; +-------------------------------------+---------------+-------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-------------------------------------+---------------+-------+----------------------------+ | RECYCLE_$_20200525_1590571477017768 | TEST | TABLE | 2020-05-27 17:24:37.018219 | +-------------------------------------+---------------+-------+----------------------------+ 1 row in set (0.00 sec) |
据说需要注意的是，oceanbase这里的flashback操作命令跟Oracle略微有些不同，需要制定回收站中对象名称，当然oceanbase 默认就启用了回收站功能。 另外就是如果表flashback之后，我们可以发现表上的index也跟踪闪回了；但是索引名称并没有变回去。这或许是一个需要改善的地方。
如何单独把index删除了，oceanbase还能单独去flashback index吗？ 显然是不能的。
另外oceanbase还支持 flashback truncate和flashback query；闪回查询这里不演示了；重点来看看flashback truncate的操作。
|
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 30 31 32 33 34 35 36 37 38 |
obclient> truncate table test; Query OK, 0 rows affected (0.06 sec) obclient> show recyclebin; +-------------------------------------+-------------------------------------------------------+-------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-------------------------------------+-------------------------------------------------------+-------+----------------------------+ | RECYCLE_$_20200525_1590571477017768 | TEST | TABLE | 2020-05-27 17:24:37.018219 | | RECYCLE_$_20200525_1590579018231440 | __idx_1100611139453782_RECYCLE_OBIDX_1590578713779249 | INDEX | 2020-05-27 19:30:18.231530 | | RECYCLE_$_20200525_1590579018234080 | __idx_1100611139453782_RECYCLE_OBIDX_1590578713782638 | INDEX | 2020-05-27 19:30:18.234148 | | RECYCLE_$_20200525_1590579018236208 | TEST | TABLE | 2020-05-27 19:30:18.236277 | +-------------------------------------+-------------------------------------------------------+-------+----------------------------+ 4 rows in set (0.00 sec) obclient> FLASHBACK TABLE RECYCLE_$_20200525_1590579018236208 to before drop; ERROR-00600: internal error code, arguments: -5020, Table 'TEST' already exists obclient> obclient> FLASHBACK TABLE RECYCLE_$_20200525_1590579018236208 to before drop rename to test_recover; Query OK, 0 rows affected (0.02 sec) obclient> insert into test select * from test_recover; Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 obclient> commit; Query OK, 0 rows affected (0.00 sec) obclient> select * from test; +------+--------------+ | A | B | +------+--------------+ | 1 | aaa | | 2 | bbb | | 3 | NULL | | 3 | NULL | | 4 | enmotech.com | +------+--------------+ 5 rows in set (0.00 sec) |
大家注意这里操作方法有点独特；由于truncate对象本身是存在的，因此ob这里采取了flashback table …to new_name table+insert的方式。以此来实现数据恢复。这一点实现比较巧妙，个人觉得非常赞！
Leave a Reply
You must be logged in to post a comment.