概述
回收站默认是关闭的。如果开启的话，回收站里包含 OB 被删除的对象(租户，表，索引，database等)。当删除OB某个对象后，该对象会进入回收站，删除对象的存储空间并不会被释放，仍然占用物理空间。
开启回收站
使用 sys租户 或者 用户租户的管理员 登录修改
[root@rac1 ~]# obclient -h10.0.0.197 -P2883 -uroot@sys#myoceanbase -p'aaAA11__' -Doceanbase -A -c; Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 524910 Server version: OceanBase_CE 4.2.1.0 (r100000102023092807-7b0f43693565654bb1d7343f728bc2013dfff959) (Built Sep 28 2023 07:25:28)Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> SET GLOBAL recyclebin = on; Query OK, 0 rows affected (0.052 sec)obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> exit Bye [root@rac1 ~]# obclient -h10.0.0.197 -P2883 -uroot@mysqlbak#myoceanbase -p'aaAA11__' -Doceanbase -A -c; Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 524911 Server version: OceanBase_CE 4.2.1.0 (r100000102023092807-7b0f43693565654bb1d7343f728bc2013dfff959) (Built Sep 28 2023 07:25:28)Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> SET GLOBAL recyclebin = on; Query OK, 0 rows affected (0.030 sec)obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> exit Bye [root@rac1 ~]#
恢复租户
删除租户
只有 sys 租户 可以进行删除租户的操作，使用回收站查看 被删除的租户 需要 通过 sys 租户 登录 查看， 如果删除对象属于 数据库(database), 表 等对象。使用 sys租户 和 相关租户的管理员查看均可
使用 sys租户登录 删除 其它租户
[root@rac1 ~]# obclient -h10.0.0.197 -P2883 -uroot@sys#myoceanbase -p'aaAA11__' -Doceanbase -A -c; Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 524912 Server version: OceanBase_CE 4.2.1.0 (r100000102023092807-7b0f43693565654bb1d7343f728bc2013dfff959) (Built Sep 28 2023 07:25:28)Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> SET GLOBAL recyclebin = 1; Query OK, 0 rows affected (0.003 sec)obclient [oceanbase]> obclient [oceanbase]> SET GLOBAL recyclebin = true; Query OK, 0 rows affected (0.003 sec)obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> SHOW RECYCLEBIN; Empty set (0.005 sec)obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> drop TENANT mysqlbak; Query OK, 0 rows affected (0.050 sec)obclient [oceanbase]> obclient [oceanbase]>
查看回收站
可以发现 被删租户mysqlbak 已被放入回收站
obclient [oceanbase]> SHOW RECYCLEBIN; +-----------------------------------------+---------------+--------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-----------------------------------------+---------------+--------+----------------------------+ | __recycle_$_1715845727_1715862359970160 | mysqlbak | TENANT | 2024-05-17 10:46:56.072550 | +-----------------------------------------+---------------+--------+----------------------------+ 1 row in set (0.005 sec)
通过租户名恢复
[root@rac1 ~]# obclient -h10.0.0.197 -P2883 -uroot@sys#myoceanbase -p'aaAA11__' -Doceanbase -A -c; Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 524949 Server version: OceanBase_CE 4.2.1.0 (r100000102023092807-7b0f43693565654bb1d7343f728bc2013dfff959) (Built Sep 28 2023 07:25:28)Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> SHOW RECYCLEBIN; +-----------------------------------------+---------------+--------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-----------------------------------------+---------------+--------+----------------------------+ | __recycle_$_1715845727_1715862359970160 | mysqlbak | TENANT | 2024-05-17 10:46:56.072550 | +-----------------------------------------+---------------+--------+----------------------------+ 1 row in set (0.006 sec)obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> FLASHBACK TENANT mysqlbak TO BEFORE DROP; Query OK, 0 rows affected (0.032 sec)obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> exit Bye [root@rac1 ~]# obclient -h10.0.0.197 -P2883 -uroot@mysqlbak#myoceanbase -p'aaAA11__' -Doceanbase -A -c; Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 524950 Server version: OceanBase_CE 4.2.1.0 (r100000102023092807-7b0f43693565654bb1d7343f728bc2013dfff959) (Built Sep 28 2023 07:25:28)Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.obclient [oceanbase]>
通过回收站中的名称恢复
由于 被删租户 在 回收站 的名称是 全局唯一的 ，因此强烈建议 使用 回收站中的名称恢复
[root@rac1 ~]# obclient -h10.0.0.197 -P2883 -uroot@sys#myoceanbase -p'aaAA11__' -Doceanbase -A -c; Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 524951 Server version: OceanBase_CE 4.2.1.0 (r100000102023092807-7b0f43693565654bb1d7343f728bc2013dfff959) (Built Sep 28 2023 07:25:28)Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.obclient [oceanbase]> drop TENANT mysqlbak; Query OK, 0 rows affected (0.025 sec)obclient [oceanbase]> SHOW RECYCLEBIN; +-----------------------------------------+---------------+--------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-----------------------------------------+---------------+--------+----------------------------+ | __recycle_$_1715845727_1715916550830336 | mysqlbak | TENANT | 2024-05-17 11:33:18.441053 | +-----------------------------------------+---------------+--------+----------------------------+ 1 row in set (0.005 sec)obclient [oceanbase]> obclient [oceanbase]> FLASHBACK TENANT __recycle_$_1715845727_1715916550830336 TO BEFORE DROP; Query OK, 0 rows affected (0.026 sec)
恢复被删租户之后重命名
使用租户名还原之后重命名
[root@rac1 ~]# obclient -h10.0.0.197 -P2883 -uroot@sys#myoceanbase -p'aaAA11__' -Doceanbase -A -c; Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 524955 Server version: OceanBase_CE 4.2.1.0 (r100000102023092807-7b0f43693565654bb1d7343f728bc2013dfff959) (Built Sep 28 2023 07:25:28)Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.obclient [oceanbase]> obclient [oceanbase]> drop TENANT mysqlbak; Query OK, 0 rows affected (0.028 sec)obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> SHOW RECYCLEBIN; +-----------------------------------------+---------------+--------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-----------------------------------------+---------------+--------+----------------------------+ | __recycle_$_1715845727_1715916858016656 | mysqlbak | TENANT | 2024-05-17 11:38:45.554886 | +-----------------------------------------+---------------+--------+----------------------------+ 1 row in set (0.007 sec)obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> FLASHBACK TENANT mysqlbak TO BEFORE DROP RENAME TO obmysql; Query OK, 0 rows affected (0.021 sec)obclient [oceanbase]> obclient [oceanbase]>
使用回收站名称还原租户重命名
[root@rac1 ~]# obclient -h10.0.0.197 -P2883 -uroot@sys#myoceanbase -p'aaAA11__' -Doceanbase -A -c; Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 524961 Server version: OceanBase_CE 4.2.1.0 (r100000102023092807-7b0f43693565654bb1d7343f728bc2013dfff959) (Built Sep 28 2023 07:25:28)Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> drop TENANT obmysql; Query OK, 0 rows affected (0.027 sec)obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> SHOW RECYCLEBIN; +-----------------------------------------+---------------+--------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-----------------------------------------+---------------+--------+----------------------------+ | __recycle_$_1715845727_1715917204567576 | obmysql | TENANT | 2024-05-17 11:42:52.154001 | +-----------------------------------------+---------------+--------+----------------------------+ 1 row in set (0.004 sec)obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> FLASHBACK TENANT __recycle_$_1715845727_1715917204567576 TO BEFORE DROP RENAME TO mysqlbak; Query OK, 0 rows affected (0.024 sec)obclient [oceanbase]>
恢复数据库(database)
恢复数据时 不允许使用 数据库原始名称，只能使用 回收站名称 来恢复, 并且恢复后的数据库名称不能与已有数据库名称重复
恢复数据库
使用 sys租户 或者 MySQL租户的管理员 登录进行恢复
[root@rac1 ~]# obclient -h10.0.0.197 -P2883 -uroot@mysqlbak#myoceanbase -p'aaAA11__' -Doceanbase -A -c; Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 524962 Server version: OceanBase_CE 4.2.1.0 (r100000102023092807-7b0f43693565654bb1d7343f728bc2013dfff959) (Built Sep 28 2023 07:25:28)Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.obclient [oceanbase]> obclient [oceanbase]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | oceanbase | | test | +--------------------+ 4 rows in set (0.007 sec)obclient [oceanbase]> obclient [oceanbase]> drop database test; Query OK, 0 rows affected (0.031 sec)obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> SHOW RECYCLEBIN; +-----------------------------------------+---------------+----------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-----------------------------------------+---------------+----------+----------------------------+ | __recycle_$_1715845727_1715917792242600 | test | DATABASE | 2024-05-17 11:49:52.243462 | +-----------------------------------------+---------------+----------+----------------------------+ 1 row in set (0.007 sec)obclient [oceanbase]> obclient [oceanbase]> FLASHBACK DATABASE __recycle_$_1715845727_1715917792242600 TO BEFORE DROP; Query OK, 0 rows affected (0.034 sec)obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | oceanbase | | test | +--------------------+ 4 rows in set (0.006 sec)
恢复数据库并重命名
obclient [oceanbase]> drop database test; Query OK, 0 rows affected (0.030 sec)obclient [oceanbase]> obclient [oceanbase]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | oceanbase | +--------------------+ 3 rows in set (0.006 sec)obclient [oceanbase]> obclient [oceanbase]> SHOW RECYCLEBIN; +-----------------------------------------+---------------+----------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-----------------------------------------+---------------+----------+----------------------------+ | __recycle_$_1715845727_1715918146268704 | test | DATABASE | 2024-05-17 11:55:46.268177 | +-----------------------------------------+---------------+----------+----------------------------+ 1 row in set (0.006 sec)obclient [oceanbase]> obclient [oceanbase]> FLASHBACK DATABASE __recycle_$_1715845727_1715918146268704 TO BEFORE DROP RENAME TO ceshi; Query OK, 0 rows affected (0.029 sec)obclient [oceanbase]> obclient [oceanbase]> show databases; +--------------------+ | Database | +--------------------+ | ceshi | | information_schema | | mysql | | oceanbase | +--------------------+ 4 rows in set (0.006 sec)obclient [oceanbase]>
恢复表
使用 sys租户，MySQL 用户租户 和 Oracle 用户租户 均可以从回收站中 恢复表。恢复时可修改表的名称，但是不能与已有表重名
删除表
当删除某个表时，表中的 索引 会随着表 一起进入回收站。
obclient [tpcc]> drop table bmsql_item; Query OK, 0 rows affected (0.040 sec)obclient [tpcc]> obclient [tpcc]> obclient [tpcc]> obclient [tpcc]> SHOW RECYCLEBIN; +-----------------------------------------+-----------------------+-------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-----------------------------------------+-----------------------+-------+----------------------------+ | __recycle_$_1715845727_1715925641147936 | __idx_500011_index_id | INDEX | 2024-05-17 14:00:41.147178 | | __recycle_$_1715845727_1715925641155432 | bmsql_item | TABLE | 2024-05-17 14:00:41.154715 | +-----------------------------------------+-----------------------+-------+----------------------------+ 2 rows in set (0.006 sec)obclient [tpcc]>
恢复表
还原表时可以使用 表名 和 回收站中的名称 还原，与 还原租户 一样，建议使用 回收站中的名称还原。可以发现，还原表时，原表自带的索引也会随着表一起被还原。
obclient [tpcc]> SHOW RECYCLEBIN; +-----------------------------------------+-----------------------+-------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-----------------------------------------+-----------------------+-------+----------------------------+ | __recycle_$_1715845727_1715925641147936 | __idx_500011_index_id | INDEX | 2024-05-17 14:00:41.147178 | | __recycle_$_1715845727_1715925641155432 | bmsql_item | TABLE | 2024-05-17 14:00:41.154715 | +-----------------------------------------+-----------------------+-------+----------------------------+ 2 rows in set (0.006 sec)obclient [tpcc]> obclient [tpcc]> FLASHBACK TABLE __recycle_$_1715845727_1715925641155432 TO BEFORE DROP; Query OK, 0 rows affected (0.032 sec)obclient [tpcc]> obclient [tpcc]> obclient [tpcc]> show create table bmsql_item; +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | bmsql_item | CREATE TABLE `bmsql_item` (`i_id` int(11) NOT NULL,`i_name` varchar(24) DEFAULT NULL,`i_price` decimal(5,2) DEFAULT NULL,`i_data` varchar(50) DEFAULT NULL,`i_im_id` int(11) DEFAULT NULL,KEY `index_id` (`i_id`) BLOCK_SIZE 16384 LOCAL ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 | +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.020 sec)
注意
单独删除表中索引时 ，索引不会进入回收站
obclient [ceshi]> drop index index_id on bmsql_item; Query OK, 0 rows affected (0.243 sec)obclient [ceshi]> obclient [ceshi]> obclient [ceshi]> obclient [ceshi]> SHOW RECYCLEBIN; Empty set (0.006 sec)obclient [ceshi]>
恢复表并重命名
obclient [tpcc]> drop table bmsql_item; Query OK, 0 rows affected (0.046 sec)obclient [tpcc]> obclient [tpcc]> obclient [tpcc]> obclient [tpcc]> SHOW RECYCLEBIN; +-----------------------------------------+-----------------------+-------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-----------------------------------------+-----------------------+-------+----------------------------+ | __recycle_$_1715845727_1715926394294472 | __idx_500011_index_id | INDEX | 2024-05-17 14:13:14.294342 | | __recycle_$_1715845727_1715926394302672 | bmsql_item | TABLE | 2024-05-17 14:13:14.301867 | +-----------------------------------------+-----------------------+-------+----------------------------+ 2 rows in set (0.006 sec)obclient [tpcc]> obclient [tpcc]> obclient [tpcc]> FLASHBACK TABLE __recycle_$_1715845727_1715926394302672 TO BEFORE DROP RENAME To bmsql_test; Query OK, 0 rows affected (0.040 sec)obclient [tpcc]> obclient [tpcc]> show create table bmsql_test; +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | bmsql_test | CREATE TABLE `bmsql_test` (`i_id` int(11) NOT NULL,`i_name` varchar(24) DEFAULT NULL,`i_price` decimal(5,2) DEFAULT NULL,`i_data` varchar(50) DEFAULT NULL,`i_im_id` int(11) DEFAULT NULL,KEY `index_id` (`i_id`) BLOCK_SIZE 16384 LOCAL ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 | +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.028 sec)
恢复表到指定的数据库并重命名
还原表时，默认将表 还原至 删除之前所在的 database 或者 schema 中
obclient [tpcc]> drop table bmsql_test; Query OK, 0 rows affected (0.039 sec)obclient [tpcc]> obclient [tpcc]> obclient [tpcc]> obclient [tpcc]> SHOW RECYCLEBIN; +-----------------------------------------+-----------------------+-------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-----------------------------------------+-----------------------+-------+----------------------------+ | __recycle_$_1715845727_1715926738118152 | __idx_500011_index_id | INDEX | 2024-05-17 14:18:58.117600 | | __recycle_$_1715845727_1715926738124128 | bmsql_test | TABLE | 2024-05-17 14:18:58.123400 | +-----------------------------------------+-----------------------+-------+----------------------------+ 2 rows in set (0.006 sec)obclient [tpcc]> obclient [tpcc]> obclient [tpcc]> show databases; +--------------------+ | Database | +--------------------+ | ceshi | | information_schema | | mysql | | oceanbase | | tpcc | +--------------------+ 5 rows in set (0.006 sec)obclient [tpcc]> obclient [tpcc]> FLASHBACK TABLE __recycle_$_1715845727_1715926738124128 TO BEFORE DROP RENAME To ceshi.bmsql_item; Query OK, 0 rows affected (0.036 sec)obclient [tpcc]> use ceshi; Database changed obclient [ceshi]> obclient [ceshi]> show tables; +-----------------+ | Tables_in_ceshi | +-----------------+ | bmsql_item | +-----------------+ 1 row in set (0.004 sec)
总结
1.还原任何对象时，都建议使用 回收站中的名称 还原
2.单独删除索引，索引不会进入回收站。删除表时，表中的索引会随着表 一起进入回收站，而且还原时，索引也会随着表一起还原
3.truncate 表是 OB 4.X.X.X 版本不支持通过回收站还原
obclient [tpcc]> truncate table bmsql_config; Query OK, 0 rows affected (0.096 sec)obclient [tpcc]> obclient [tpcc]> obclient [tpcc]> obclient [tpcc]> show tables; +------------------+ | Tables_in_tpcc | +------------------+ | bmsql_config | | bmsql_customer | | bmsql_district | | bmsql_history | | bmsql_new_order | | bmsql_oorder | | bmsql_order_line | | bmsql_stock | | bmsql_warehouse | +------------------+ 9 rows in set (0.004 sec)obclient [tpcc]> SHOW RECYCLEBIN; +-----------------------------------------+---------------+-------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-----------------------------------------+---------------+-------+----------------------------+ | __recycle_$_1715845727_1716174434474744 | bmsql_item | TABLE | 2024-05-20 11:07:14.476023 | +-----------------------------------------+---------------+-------+----------------------------+ 1 row in set (0.005 sec)obclient [tpcc]>