1 回收站概述
回收站在原理上说就是一个数据字典表,放置用户删除的数据库对象信息。用户删除的对象被放入回收站后,其实仍然占据着物理空间,除非您手动进行清除(PURGE)或者对象定期被数据库系统删除。
在当前版本中,支持进入回收站的对象有索引、表、库和租户。由于当前仅 sys 租户可以删除租户,故 MySQL 用户租户和 Oracle 用户租户被删除后,只能进入 sys 租户的回收站中。
- sys 租户:支持管理的回收站对象包括数据库、表、索引及租户。
- MySQL 用户租户:支持管理的回收站对象包括数据库、表及索引。
- Oracle 用户租户:支持管理的回收站对象包括表和索引。
使用限制
- 直接删除索引,该索引不会进入回收站。删除表时,表上的索引会随主表一起进入回收站。
- FLASHBACK 数据库对象的顺序需要符合从属关系,即:Database > Table。
- 不支持直接恢复索引,恢复表会连同索引一并恢复。
- 不能对回收站对象做任何查询和 DML 操作,DDL 操作中也仅支持 Purge 和 Flashback 操作。
- 进入回收站的对象并不是直接删除,而是会继续占用存储空间。
2 开启/关闭回收站
在 OceanBase 数据库中,通过系统变量 recyclebin 来控制回收站的开启和关闭,默认回收站为关闭状态。
在 OB 4.1 版本中,开启回收站对性能基本没有影响了。
2.1 查看回收站状态
obclient [backup]> SHOW VARIABLES LIKE 'recyclebin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin | OFF |
+---------------+-------+
1 row in set (0.001 sec)
recyclebin 是租户级参数,要在租户重修改,可以配置如下值:
- on 、1 或 true:表示开启回收站。
- off 、0 或 false:表示关闭回收站。
可以在两个级别设置:
- Global :整个租户内生效。
- Session :对当前Session 生效。
2.2 开启回收站
连接到租户:
[dave@www.cndba.cn ~]$obclient -h192.168.1.21 -P2881 -uroot@backup -p'xxxxx' -Doceanbase -A
Welcome to the OceanBase. Commands end with ; or /g.
Your OceanBase connection id is 3221747599
Server version: OceanBase_CE 4.1.0.0 (r101010022023051821-f7379b26f8cd11f026e06846043550f9e0d42ead) (Built May 18 2023 21:14:14)
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 [backup]> SET GLOBAL recyclebin = on;
Query OK, 0 rows affected (0.193 sec)
obclient [backup]> SET GLOBAL recyclebin = 1;
Query OK, 0 rows affected (0.002 sec)
obclient [backup]> SET GLOBAL recyclebin = true;
Query OK, 0 rows affected (0.002 sec)
obclient [oceanbase]> SHOW VARIABLES LIKE 'recyclebin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin | ON |
+---------------+-------+
1 row in set (0.002 sec)
2.3 关闭回收站
obclient [oceanbase]> SET GLOBAL recyclebin = off;
Query OK, 0 rows affected (0.126 sec)
obclient [oceanbase]> SET GLOBAL recyclebin = 0;
Query OK, 0 rows affected (0.002 sec)
obclient [oceanbase]> SET GLOBAL recyclebin = false;
Query OK, 0 rows affected (0.001 sec)
obclient [oceanbase]> SHOW VARIABLES LIKE 'recyclebin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin | ON |
+---------------+-------+
1 row in set (0.000 sec)
obclient [oceanbase]> exit
Bye
重新登录生效:
[dave@www.cndba.cn ~]$obclient -h192.168.1.21 -P2881 -uroot@backup -p'xxxxx' -Doceanbase -A
Welcome to the OceanBase. Commands end with ; or /g.
Your OceanBase connection id is 3221560590
Server version: OceanBase_CE 4.1.0.0 (r101010022023051821-f7379b26f8cd11f026e06846043550f9e0d42ead) (Built May 18 2023 21:14:14)
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]> SHOW VARIABLES LIKE 'recyclebin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin | OFF |
+---------------+-------+
1 row in set (0.000 sec)
obclient [oceanbase]>
3 查看回收站中对象
[dave@www.cndba.cn ~]$obclient -h192.168.1.21 -P2881 -uroot@backup -p'xxxxx' -Doceanbase -A
Welcome to the OceanBase. Commands end with ; or /g.
Your OceanBase connection id is 3221525741
Server version: OceanBase_CE 4.1.0.0 (r101010022023051821-f7379b26f8cd11f026e06846043550f9e0d42ead) (Built May 18 2023 21:14:14)
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]> use backup
Database changed
obclient [backup]> show tables;
+------------------+
| Tables_in_backup |
+------------------+
| bmsql_config |
| bmsql_customer |
| bmsql_district |
| bmsql_history |
| bmsql_item |
| bmsql_new_order |
| bmsql_oorder |
| bmsql_order_line |
| bmsql_stock |
| bmsql_warehouse |
+------------------+
10 rows in set (0.001 sec)
obclient [backup]> drop table bmsql_config;
Query OK, 0 rows affected (0.924 sec)
obclient [backup]> SHOW RECYCLEBIN;
+--------------------------------+---------------+-------+----------------------------+
| OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME |
+--------------------------------+---------------+-------+----------------------------+
| __recycle_$_1_1688449379272432 | bmsql_config | TABLE | 2023-07-04 13:42:59.283804 |
+--------------------------------+---------------+-------+----------------------------+
1 row in set (0.005 sec)
obclient [backup]>
返回结果中的相关字段说明如下:
- OBJECT_NAME:表示该对象在回收站中的名称。
- ORIGINAL_NAME:表示原对象名。
- CREATETIME:表示该对象进入回收站的时间。
- TYPE:表示回收站对象所属的类型。其中:
- 系统租户(sys 租户)支持 INDEX、TABLE、DATABASE 和 TENANT 四种类型。
- MySQL 用户租户支持 INDEX、TABLE 和 DATABASE 三种类型。
- Oracle 用户租户支持 INDEX 和 TABLE 两种类型。
4 恢复回收站对象
4.1 恢复表
sys 租户、 MySQL 用户租户和 Oracle 用户租户均可以从回收站中恢复表。恢复时可修改表的名称,但是不能与已有表重名,否则系统会报错。
[dave@www.cndba.cn ~]$obclient -h192.168.1.21 -P2881 -uroot@backup -p'xxxxx' -Doceanbase -A
Welcome to the OceanBase. Commands end with ; or /g.
Your OceanBase connection id is 3221523401
Server version: OceanBase_CE 4.1.0.0 (r101010022023051821-f7379b26f8cd11f026e06846043550f9e0d42ead) (Built May 18 2023 21:14:14)
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]> use backup;
Database changed
obclient [backup]>
obclient [backup]> drop table bmsql_customer;
Query OK, 0 rows affected (0.611 sec)
查看回收站:
obclient [backup]> SHOW RECYCLEBIN;
+--------------------------------+----------------------------------+-------+----------------------------+
| OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME |
+--------------------------------+----------------------------------+-------+----------------------------+
| __recycle_$_1_1688449379272432 | bmsql_config | TABLE | 2023-07-04 13:42:59.283804 |
| __recycle_$_1_1688458388028600 | __idx_501364_bmsql_customer_idx1 | INDEX | 2023-07-04 16:13:08.028826 |
| __recycle_$_1_1688458388060704 | bmsql_customer | TABLE | 2023-07-04 16:13:08.061344 |
+--------------------------------+----------------------------------+-------+----------------------------+
3 rows in set (0.002 sec)
从回收站中恢复表,恢复后的表名为进入回收站前的表名:
obclient [backup]> FLASHBACK TABLE __recycle_$_1_1688458388060704 to before drop;
Query OK, 0 rows affected (0.155 sec)
obclient [backup]> select count(1) from bmsql_customer;
+----------+
| count(1) |
+----------+
| 6000000 |
+----------+
1 row in set (0.026 sec)
obclient [backup]>
从回收站中恢复表并重命名:
obclient [backup]> SHOW RECYCLEBIN;
+--------------------------------+---------------+-------+----------------------------+
| OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME |
+--------------------------------+---------------+-------+----------------------------+
| __recycle_$_1_1688449379272432 | bmsql_config | TABLE | 2023-07-04 13:42:59.283804 |
+--------------------------------+---------------+-------+----------------------------+
1 row in set (0.002 sec)
obclient [backup]> FLASHBACK TABLE __recycle_$_1_1688449379272432 to before drop rename to dave;
Query OK, 0 rows affected (0.133 sec)
obclient [backup]> select count(1) from dave;
+----------+
| count(1) |
+----------+
| 4 |
+----------+
1 row in set (0.089 sec)
从回收站中将表恢复到指定的数据库或 Schema 对象并重命名:
obclient [backup]> drop table dave;
Query OK, 0 rows affected (0.415 sec)
obclient [backup]> show recyclebin;
+--------------------------------+---------------+-------+----------------------------+
| OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME |
+--------------------------------+---------------+-------+----------------------------+
| __recycle_$_1_1688458928714784 | dave | TABLE | 2023-07-04 16:22:08.715401 |
+--------------------------------+---------------+-------+----------------------------+
1 row in set (0.002 sec)
obclient [backup]> FLASHBACK TABLE __recycle_$_1_1688458928714784 to before drop rename to ustc.dave;
Query OK, 0 rows affected (0.141 sec)
obclient [backup]> select count(1) from ustc.dave;
+----------+
| count(1) |
+----------+
| 4 |
+----------+
1 row in set (0.013 sec)
4.2 恢复数据库
sys 租户或 MySQL 用户租户可以从回收站中恢复数据库。恢复时可修改数据库名称,但不能与已有数据库重名,否则系统会报错。
从回收站中恢复数据库,恢复后的数据库名为进入回收站前的名称:
obclient [backup]> drop database ustc;
Query OK, 1 row affected (0.408 sec)
obclient [backup]> show recyclebin;
+--------------------------------+---------------+----------+----------------------------+
| OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME |
+--------------------------------+---------------+----------+----------------------------+
| __recycle_$_1_1688459423547168 | ustc | DATABASE | 2023-07-04 16:30:23.546660 |
+--------------------------------+---------------+----------+----------------------------+
1 row in set (0.002 sec)
obclient [backup]> FLASHBACK DATABASE __recycle_$_1_1688459423547168 to before drop;
Query OK, 0 rows affected (0.143 sec)
obclient [backup]> show recyclebin;
Empty set (0.002 sec)
从回收站中恢复数据库并重命名:
obclient [backup]> drop database ustc;
Query OK, 1 row affected (0.409 sec)
obclient [backup]> show recyclebin;
+--------------------------------+---------------+----------+----------------------------+
| OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME |
+--------------------------------+---------------+----------+----------------------------+
| __recycle_$_1_1688459584139248 | ustc | DATABASE | 2023-07-04 16:33:04.138794 |
+--------------------------------+---------------+----------+----------------------------+
1 row in set (0.003 sec)
obclient [backup]> FLASHBACK DATABASE __recycle_$_1_1688459584139248 to before drop rename to dave;
Query OK, 0 rows affected (0.143 sec)
obclient [backup]> show recyclebin;
Empty set (0.002 sec)
4.3 恢复租户
由于当前仅 sys 租户可以删除租户,故仅 sys 租户可以将回收站中的租户恢复为正常租户。恢复时可修改租户名称,但不能与已有租户重名,否则系统会报错。
租户的恢复是依赖 SYS 租户的,我们之前测试都是针对单个租户进行回收站的启用和关闭。
所以在测试租户恢复之前,需要先启用 SYS 租户的回收站。 从实际使用来看,整租户的恢复应用场景较少。 这里仅做测试。
4.3.1 开通 SYS 租户的回收站
[dave@www.cndba.cn ~]$obclient -h192.168.1.21 -P2881 -uroot -p'xxxxx' -Doceanbase -A
Welcome to the OceanBase. Commands end with ; or /g.
Your OceanBase connection id is 3221625909
Server version: OceanBase_CE 4.1.0.0 (r101010022023051821-f7379b26f8cd11f026e06846043550f9e0d42ead) (Built May 18 2023 21:14:14)
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]> show recyclebin;
Empty set (0.005 sec)
obclient [oceanbase]> SHOW VARIABLES LIKE 'recyclebin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin | OFF |
+---------------+-------+
1 row in set (0.000 sec)
obclient [oceanbase]> SET GLOBAL recyclebin = on;
Query OK, 0 rows affected (0.124 sec)
obclient [oceanbase]> SHOW VARIABLES LIKE 'recyclebin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin | OFF |
+---------------+-------+
1 row in set (0.000 sec)
obclient [oceanbase]> exit
Bye
[dave@www.cndba.cn ~]$obclient -h192.168.1.21 -P2881 -uroot -p'xxxxx' -Doceanbase -A
Welcome to the OceanBase. Commands end with ; or /g.
Your OceanBase connection id is 3221570940
Server version: OceanBase_CE 4.1.0.0 (r101010022023051821-f7379b26f8cd11f026e06846043550f9e0d42ead) (Built May 18 2023 21:14:14)
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]> SHOW VARIABLES LIKE 'recyclebin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin | ON |
+---------------+-------+
1 row in set (0.000 sec)
obclient [oceanbase]>
4.3.2 恢复
将回收站中的租户恢复为正常租户,且恢复后的租户名为进入回收站前的租户名:
obclient [oceanbase]> show recyclebin;
Empty set (0.004 sec)
obclient [oceanbase]> drop tenant backup2;
Query OK, 0 rows affected (0.124 sec)
obclient [oceanbase]> show recyclebin;
+--------------------------------+---------------+--------+----------------------------+
| OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME |
+--------------------------------+---------------+--------+----------------------------+
| __recycle_$_1_1688461891658032 | backup2 | TENANT | 2023-07-04 17:12:43.347386 |
+--------------------------------+---------------+--------+----------------------------+
1 row in set (0.004 sec)
obclient [oceanbase]> FLASHBACK TENANT __recycle_$_1_1688461891658032 to before drop;
Query OK, 0 rows affected (0.116 sec)
obclient [oceanbase]> show recyclebin;
Empty set (0.004 sec)
将回收站中的租户恢复为正常租户并重命名:
obclient [oceanbase]> drop tenant backup2;
Query OK, 0 rows affected (0.116 sec)
obclient [oceanbase]> show recyclebin;
+--------------------------------+---------------+--------+----------------------------+
| OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME |
+--------------------------------+---------------+--------+----------------------------+
| __recycle_$_1_1688462003782944 | backup2 | TENANT | 2023-07-04 17:13:52.231967 |
+--------------------------------+---------------+--------+----------------------------+
1 row in set (0.005 sec)
obclient [oceanbase]> FLASHBACK TENANT __recycle_$_1_1688462003782944 to before drop rename to ustc;
Query OK, 0 rows affected (0.113 sec)
obclient [oceanbase]> select tenant_id,tenant_name,tenant_type,COMPATIBILITY_MODE,PRIMARY_ZONE from dba_ob_tenants;
+-----------+-------------+-------------+--------------------+-------------------+
| tenant_id | tenant_name | tenant_type | COMPATIBILITY_MODE | PRIMARY_ZONE |
+-----------+-------------+-------------+--------------------+-------------------+
| 1 | sys | SYS | MYSQL | RANDOM |
| 1001 | META$1002 | META | MYSQL | RANDOM |
| 1002 | ocp | USER | MYSQL | RANDOM |
| 1011 | META$1012 | META | MYSQL | RANDOM |
| 1012 | tpcc_mysql | USER | MYSQL | RANDOM |
| 1013 | META$1014 | META | MYSQL | zone1,zone2,zone3 |
| 1014 | oms | USER | MYSQL | zone1,zone2,zone3 |
| 1015 | META$1016 | META | MYSQL | RANDOM |
| 1016 | backup | USER | MYSQL | RANDOM |
| 1019 | META$1020 | META | MYSQL | RANDOM |
| 1020 | open_gpt | USER | MYSQL | RANDOM |
| 1021 | META$1022 | META | MYSQL | zone1,zone2,zone3 |
| 1022 | ustc | USER | MYSQL | zone1,zone2,zone3 |
+-----------+-------------+-------------+--------------------+-------------------+
13 rows in set (0.001 sec)
obclient [oceanbase]>
5 清理回收站
OB 的回收站启用后,如果没有配置自动清理的话,里面的会一直保存。 建议在启用回收站配置自动清理。
5.1 手工清理
登录 OB:
- MySQL 租户的管理员用户为 root 用户,Oracle 租户的管理员用户为 SYS 用户。
- 如果需要清理租户回收站对象,则需要使用 sys 租户登录数据库。
查看当前回收站里的内容:
obclient [(none)]> SHOW RECYCLEBIN;
+-------------------------------------+-------------------+----------+----------------------------+
| OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME |
+-------------------------------------+-------------------+----------+----------------------------+
| RECYCLE_$_100017_1672050541224936 | oracle001 | TENANT | 2023-01-16 11:01:40.258000 |
| __recycle_$_100017_1673426335319344 | __idx_500788_idx1 | INDEX | 2023-01-11 16:38:55.318878 |
| __recycle_$_100017_1673426335331800 | t1 | TABLE | 2023-01-11 16:38:55.331481 |
| __recycle_$_100017_1673854707660504 | infotest | DATABASE | 2023-01-16 15:38:27.660436 |
+-------------------------------------+-------------------+----------+----------------------------+
4 rows in set
彻底删除 租户:仅 sys 租户支持清理租户回收站对象。
obclient [(none)]> PURGE TENANT oracle001;
obclient [(none)]> PURGE TENANT RECYCLE_$_100017_1672050541224936;
彻底删除 数据库:仅 sys 租户和 MySQL 用户租户支持清理回收站中的数据库对象。
obclient [(none)]> PURGE DATABASE __recycle_$_100017_1673854707660504;
注意:这里不支持使用原始名称。
彻底删除 表:
obclient [(none)]> PURGE TABLE __recycle_$_100017_1673426335331800;
obclient [(none)]> PURGE TABLE t1;
彻底删除 索引:
obclient [(none)]> PURGE INDEX __recycle_$_100017_1673426335319344;
注意:这里不支持使用原始名称。
将所有对象从回收站中彻底清除
obclient [(none)]> PURGE RECYCLEBIN;
5.2 自动清理
OceanBase 数据库当前支持通过集群级配置项 recyclebin_object_expire_time 自动清理收站中过期的 Schema 对象,其默认值为 0s。其中:
- 值为 0s 时,表示关闭自动 Purge 回收站功能。
- 值为非 0s 时,表示 Purge 一段时间前进入回收站的对象。
因为该参数是集群级的,所以需要连接 SYS 租户进行配置。
该参数默认为 0,表示没有开启自动清理。
[dave@www.cndba.cn ~]$obclient -h192.168.1.21 -P2881 -uroot -p'xxxxx' -Doceanbase -A
Welcome to the OceanBase. Commands end with ; or /g.
Your OceanBase connection id is 3221679644
Server version: OceanBase_CE 4.1.0.0 (r101010022023051821-f7379b26f8cd11f026e06846043550f9e0d42ead) (Built May 18 2023 21:14:14)
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 parameters like '%recyclebin_object%'/G
*************************** 1. row ***************************
zone: zone3
svr_type: observer
svr_ip: 192.168.1.23
svr_port: 2882
name: recyclebin_object_expire_time
data_type: NULL
value: 0s
info: recyclebin object expire time, default 0 that means auto purge recyclebin off. Range: [0s, +∞)
section: ROOT_SERVICE
scope: CLUSTER
source: DEFAULT
edit_level: DYNAMIC_EFFECTIVE
打开自动 Purge 回收站功能,并回收 7 天前进入回收站的 Schema 对象:
obclient [oceanbase]> ALTER SYSTEM SET recyclebin_object_expire_time = "7d";
Query OK, 0 rows affected (0.041 sec)
obclient [oceanbase]> show parameters like '%recyclebin_object%'/G
*************************** 1. row ***************************
zone: zone2
svr_type: observer
svr_ip: 192.168.1.22
svr_port: 2882
name: recyclebin_object_expire_time
data_type: NULL
value: 7d
info: recyclebin object expire time, default 0 that means auto purge recyclebin off. Range: [0s, +∞)
section: ROOT_SERVICE
scope: CLUSTER
source: DEFAULT
edit_level: DYNAMIC_EFFECTIVE
关闭自动 Purge 回收站功能:
obclient [oceanbase]> ALTER SYSTEM SET recyclebin_object_expire_time = "0s";
Query OK, 0 rows affected (0.035 sec)
版权声明:本文为博主原创文章,未经博主允许不得转载。