Alert: 不建议生产库使用YashanDB “nologging” table

by

在前一篇文章中，我分享了在 PostgreSQL 系数据库（如 Kingbase、GaussDB、Highgo 等）中，不建议使用 unlogged table 的风险。其实，从 Oracle 迁移到 YashanDB 时同样存在类似隐患。YashanDB 虽然提供了 nologging 语法，并宣称与 Oracle 完全兼容，但其底层实现机制并不相同。此外，在分布式数据库中，由于大多基于 Redo 日志实现强一致性，多数并不支持 nologging 功能。本文我将继续围绕 YashanDB 做一个简单的测试。

测试YAshanDB nologging

— 环境 yashanDB 23.5 YAC

YashanDB SQL Enterprise Edition Release 23.5.1.100 x86_64

Connected to:
YashanDB Server Enterprise Edition Release 23.5.1.100 x86_64 - Linux

SQL> create table t_nologging(id int,name varchar2(100));

Succeed.

SQL> alter table t_nologging nologging;
Succeed.

SQL> insert into t_nologging values(1,'anbob');

1 row affected.

SQL>  commit;

Succeed.

SQL> select * from t_nologging;

          ID NAME
------------ ----------------------------------------------------------------
           1 anbob

1 row fetched.

Note： 数据已持久化， 下面重启数据库。

$ yasboot cluster status -c yashandb -d
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| hostid   | node_type | nodeid | pid   | instance_status | database_status | database_role | listen_address    | source_node | data_path                      |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| host0001 | ce        | 1-1:1  | 8909  | open            | normal          | primary       | 172.x.y.74:1688   | -           | /data/yashan/yasdb_data/ce-1-1 |
+----------+-----------+--------+-------+-----------------+-----------------+---------------+----------------  ---+-------------+--------------------------------+
| host0002 | ce        | 1-2:2  | 15542 | open            | normal          | primary       | 172.x.y.75:1688   | -           | /data/yashan/yasdb_data/ce-1-2 |
+----------+-----------+--------+-------+-----------------+-----------------+---------------+-------------------+-------------+--------------------------------+

[yashan@yashan-74 ~]$ yasboot cluster restart  -c yashandb
+-------------------------------------------------------------------------------------------------------------+
| type | uuid             | name                | hostid | index    | status  | return_code | progress | cost |
+-------------------------------------------------------------------------------------------------------------+
| type | uuid             | name                | hostid | index    | status  | return_code | progress | cost |
+-------------------------------------------------------------------------------------------------------------+
| task | ee10d067aeaa7350 | ReStartYasdbCluster | -      | yashandb | SUCCESS | 0           | 100      | 53   |
+------+------------------+---------------------+--------+----------+---------+-------------+----------+------+
task completed, status: SUCCESS

重启后再次检查nologging的数据

$ sh conn.sh
YashanDB SQL Enterprise Edition Release 23.5.1.100 x86_64

Connected to:
YashanDB Server Enterprise Edition Release 23.5.1.100 x86_64 - Linux

SQL> select * from t_nologging;

YAS-02329 the table is corrupted

SQL> insert into t_nologging values(1,'anbob');

YAS-02329 the table is corrupted

SQL> truncate table t_nologging;

Succeed.

Note: 重启后nologging的表被标记”corrupted”损坏。 只能做truncate或drop DDL操作。

目前yashan也没有force logging数据库级的强制，所以如果在有主备从库的环境中，是直接不允放有nologging的表,会报错。

SQL> ALTER TABLE t_exist  NOLOGGING;

YAS-02328 table nologging is not allowed when standby exists

在数据库使用yasldr导入时，也可以使用nologging的方式，如果指定为TRUE，这在导入的过程中会转换为nologging, 导入结束会恢复为logging, 使用的是LOGGING ASYNC(yashanDB的在修改表为[no]logging时，增加第三个选项，异步改为logging，减少锁等待时间)

检查Nologging的表

SQL> ALTER TABLE T_NOLOGGING NOLOGGING;

SQL> SELECT * FROM V$DICT_CACHE WHERE NAME='T_NOLOGGING';

     USER_ID             OBJECT_ID NAME                                                                  VERSION         TYPE IN_RECYCLE_BIN        VALID          PVT    REF_COUNT   MEMORY_CONTEXT_USED              LOAD_SCN NOLOGGING_INSTANCE_ID
------------ --------------------- ---------------------------------------------------------------- ------------ ------------ -------------- ------------ ------------ ------------ --------------------- --------------------- ---------------------
           0                 13748 T_NOLOGGING                                                                 0            1              0                                                                                                        1

1 row fetched.

SQL> SELECT OWNER,TABLE_NAME,LOGGING,CORRUPTED,TABLE_TYPE FROM DBA_TABLES WHERE TABLE_NAME='T_NOLOGGING';

OWNER                                                            TABLE_NAME                                                       LOGGING CORRUPTED TABLE_TYPE
---------------------------------------------------------------- ---------------------------------------------------------------- ------- --------- ----------
SYS                                                              T_NOLOGGING                                                      N       N         HEAP

1 row fetched.

SQL> TRUNCATE TABLE T_NOLOGGING;

Succeed.

SQL> SELECT OWNER,TABLE_NAME,LOGGING,CORRUPTED,TABLE_TYPE FROM DBA_TABLES WHERE TABLE_NAME='T_NOLOGGING';

OWNER                                                            TABLE_NAME                                                       LOGGING CORRUPTED TABLE_TYPE
---------------------------------------------------------------- ---------------------------------------------------------------- ------- --------- ----------
SYS                                                              T_NOLOGGING                                                      Y       N         HEAP

1 row fetched.

Note: 通过V$DICT_CACHE .NOLOGGING_INSTANCE_ID可以查询nologging的实例， 并且注意在做了truncate 后nologging表会自动修改为logging.

其他一些注意事项

  • 不能将临时表设置为nologging属性。
  • nologging表不能执行并发操作。
  • nologging表不能执行回滚操作
  • 数据库重启时会将nologging表标记为corrupted
  • 不能在线创建索引
  • 主备环境中，不能将表设置为nologging属性。

— over —

Leave a Comment

Free Web Hosting