eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

February 16, 2020

Oracle 20c 新特性:原生的区块链表支持(Blockchain Table)

区块链表是仅插入表(Only-Insert),将行组织成许多链。通过使用加密哈希将链中除第一行之外的每一行链接到链中的前一行。

区块链表中的行是防篡改的。每行包含一个密码哈希值,该值基于该行中的数据和链中上一行的哈希值。如果某行被篡改,则该行的哈希值会更改,这会导致链中下一行的哈希值发生更改。为了增强欺诈保护,可以将可选的用户签名添加到行中。如果您在区块链表行上签名,则必须使用数字证书。在验证区块链表中的链时,数据库需要证书来验证行签名。

下图是区块链表的示意图:
image.png

可以对区块链表进行索引和分区。您可以通过建表时的选项控制是否以及何时从区块链表中删除行。您还可以控制是否可以删除区块链表。区块链表可与事务和查询中的(常规)表一起使用。

区块链表用于实施集中式区块链应用程序,其中中央权限是Oracle数据库。集中式区块链为组织提供了更大的可定制性和控制力,因为他们可以决定谁可以加入网络。参与者是不同的数据库用户,他们信任Oracle数据库来维护交易的防篡改区块链。所有参与者都必须具有将数据插入到区块链表中的特权。区块链的内容由应用程序定义和管理。与分散式区块链相比,集中式区块链在与基于共识的分布式区块链相比首选更高吞吐量和更低交易延迟的场景中很有用。
BlockChain.gif

以下测试,展示了区块链表的基本用法。

首先创建一个区块链表,指定不可DROP,不可 DELETE 的基本约束:

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production

SQL> create blockchain table yhem
  2  (
  3  id 		     number primary key,
  4  name		     varchar2(20),
  5  join_date		     date)
  6  NO DROP UNTIL 30 DAYS IDLE
  7  NO DELETE LOCKED
  8  HASHING USING "SHA2_512" VERSION "v1";

Table created.

插入区块记录:

SQL> insert into yhem values(1,'EYGLE',to_date('2010-08-08','yyyy-mm-dd'));

1 row created.

SQL> insert into yhem values(2,'KAMUS',to_date('2010-08-08','yyyy-mm-dd'));

1 row created.

SQL> insert into yhem values(3,'ORA-600',to_date('2010-08-08','yyyy-mm-dd'));

1 row created.

SQL> insert into yhem values(4,'YANGTINGKUN',to_date('2010-08-08','yyyy-mm-dd'));

1 row created.

SQL> commit;

Commit complete.

这个区块链表,除了插入操作,删除、更新,以及DROP 操作,都是禁止的:

SQL> delete yhem where id=1;
delete yhem where id=1
       *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table


SQL> update yhem set name='Guoqiang,Gai' where id=1;
update yhem set name='Guoqiang,Gai' where id=1
       *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table


SQL> drop table yhem;
drop table yhem
           *
ERROR at line 1:
ORA-05723: drop blockchain table YHEM not allowed

Oracle 还提供了 PACKAGE 去校验数据的一致性:

SQL> set serveroutput on
SQL> DECLARE
  2  	     verified_rows NUMBER :=0;
  3  BEGIN
  4  	     DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS('EYGLE','YHEM',number_of_rows_verified=> verified_rows);
  5  	     DBMS_OUTPUT.PUT_LINE('Number of rows verified =' || verified_rows);
  6  END;
  7  /
Number of rows verified =4

PL/SQL procedure successfully completed.

保留周期对于区块链表是最重要的选项,NO DROP UNTIL 30 DAYS IDLE 指定在数据表IDLE多长时间后可以删除,这个设置可以增大,但是不可以减少:

SQL> ALTER TABLE yhem NO DROP UNTIL 1 DAYS IDLE;
ALTER TABLE yhem NO DROP UNTIL 1 DAYS IDLE
*
ERROR at line 1:
ORA-05732: retention value cannot be lowered


SQL> ALTER TABLE yhem NO DROP UNTIL 31 DAYS IDLE;

Table altered.

而对于DELETE来说,NO DELETE LOCKED 是指数据不允许删除:

SQL> ALTER TABLE yhem NO DELETE UNTIL 20 DAYS AFTER INSERT LOCKED;
ALTER TABLE ENMOTECH NO DELETE UNTIL 20 DAYS AFTER INSERT LOCKED
*
ERROR at line 1:
ORA-05731: blockchain table yhem cannot be altered

Oracle 的区块链表,是对于集中式互信应用,提供了一个选择。

Posted by eygle at 3:07 PM | Permalink | Oracle12c/11g (161)

February 15, 2020

Oracle Database 20c 云上首发 官方文档全线提供下载

Oracle 20c 的全部文档已经上线,这标志着 Oracle 20c 即将公开发布。目前 20c 已经首先在 Oracle Cloud 作为预览版(Preview)上线:
image.png

本站转载了四个核心文档:

  1. Oracle 20c 概念手册
  2. Oracle 20c 新特性手册
  3. Oracle 20c In-Memory 手册
  4. Oracle 20c 多租户手册

image.png

官方文档索引:
PIC.jpg

官方文档地址:
https://docs.oracle.com/en/database/oracle/oracle-database/20/index.html

云上Demo:
EQuOOJGVUAAk_TM.jpeg

这是来自 Oracle 的情人节首发,祝大家情人节快乐

Posted by eygle at 8:54 AM | Permalink | OraNews (267)

February 12, 2020

2020从新开始:Oracle DBA 必备技能和学习索引

对于 Oracle DBA,我们整理了以下学习线索,供大家参考:

Oracle DBA 必备核心技能:

  1. Oracle DBA核心技能:数据库的跟踪和分析方法之SQL_TRACE和DBMS_SYSTEM
  2. Oracle DBA核心技能:数据库的跟踪和分析方法之 10046与等待事件
  3. Oracle DBA核心技能:通过 10046 跟踪解决 12.2 多租户 PDB 未知问题
  4. Oracle DBA核心技能:通过10046解决数据库RAC集群不能启动案例
  5. Oracle DBA核心技能:System State转储分析之ROW CACHE ENQUEUE问题定位
  6. Oracle DBA核心技能:使用 errorstack 进行错误跟踪和诊断
  7. Oracle DBA核心技能:AWR数据的导出和导入转移
  8. Oracle DBA核心技能:AWR 深度解读 Redo Nowait指标的算法和诊断
  9. Oracle DBA核心技能:举一反三触类旁通的 TBL$OR$IDX$PART$NUM 范例

Oracle 新特性系列:

  1. Oracle 19c 新特性:ADG DML 重定向
  2. Oracle 19c 新特性:ADG的自动DML重定向增强读写分离
  3. Oracle 19c ADG 新特性及最佳实践
  4. Oracle 12.2 :在线(Online)表移动新特性
  5. Oracle 20c 十大新特性一览
  6. Oracle 20c 新特性详解:SQL Macro 宏 SCALAR / TABLE 带来的敏捷和便利

文档推荐:

  1. Oracle数据库的跟踪和分析方法
    这是WORD格式的完整版本,介绍了Oracle数据库至关重要的跟踪诊断方法和案例解析。
  2. Oracle 12.2 新特性系列:Approx_Query_Processing
  3. Oracle SGA 的内部解析内核分析 作者:Julian Dyke

DBA FAQ:

  1. 删除的分区能够通过Flashback进行闪回吗?
  2. Oracle数据库中 MISSING 名称的数据文件是如何出现的?
  3. Oracle 12.2 使用 8 字节存储SCN带来的影响?

Posted by eygle at 8:30 AM | Permalink | FAQ (267)

December 8, 2019

GaussDB T / A : 从 100 到 200 和 300 命名的变迁

华为的 GaussDB 数据库,从传承和沿革上,让行业里的朋友非常困惑,我整理一下相关的信息记录于此。

最初,华为的数据库公布出来的型号系列有三款,分别是 100、200 和 300 ,统一的命名都是 GaussDB 。

GaussDB 100 ,以 OLTP 为方向,最初和招商银行联合研发,然后推广,在 2020年6月,将会开源单机版本;

GaussDB 200,以 PostgreSQL 为出发点,面向 MPP 研发,工商银行率先尝试使用,然后推广;

GaussDB 300,以 HTAP 为方向,民生银行尝试使用。

在2019年10月左右,华为 GaussDB 的命名再次调整:

GaussDB 100 ,更名为 GaussDB T ,以 OLTP 和集群为方向;

GaussDB 200 合并 300 的部分设计,更名为 GaussDB A,以分析型为主方向;

GaussDB 300,型号取消,涉及功能并入 100 或 200 。

Gaussdb T 有 单机、HA、分布式集群 三种架构,据说内部在研发 RAC 集群架构,在2020年1月1日将会发布(小道消息)。

GaussDBT.jpg

Gaussdb T 的三种架构中,单机是基本架构;HA是多套单机组成,可以是1主1备、1主2备等;分布式集群由多套HA组成,一般是多套1主2备组成。

Posted by eygle at 5:15 PM | Permalink | FAQ (267)

December 6, 2019

GaussDB T : 100 的多线程模式及后台线程介绍

在 GaussDB 启动的过程中,可以从日志中清晰的看到,后台依次启动了一系列的线程。

GaussDB 100采用单进程、多线程结构。服务器在运行时由各种内存数据结构和一系列线程组成,不同类型的线程完成不同的任务,这其中包括 LGWR / DBWR / CKPT / SMON / STATS ,基本上与 Oracle 命名保持一致。

UTC+8 2019-12-06 17:05:28.691|ZENGINE|00000|77309420628|INFO>[LOG] file '/ssd_data/gaussdb/gaussdata/log/enmotech_alarm.log' is added [srv_param.c:488]

UTC+8 2019-12-06 17:05:28.691|ZENGINE|00000|9300|INFO>[LOG] file '/ssd_data/gaussdb/gaussdata/log/run/zengine.rlog' is added [cm_log.c:643]

UTC+8 2019-12-06 17:05:28.691|ZENGINE|00000|9300|INFO>[PARAM] LSNR_ADDR = 127.0.0.1

UTC+8 2019-12-06 17:05:28.691|ZENGINE|00000|9300|INFO>[PARAM] LSNR_PORT = 1611

UTC+8 2019-12-06 17:05:28.691|ZENGINE|00000|9300|INFO>[PARAM] DATA_BUFFER_SIZE = 2G

UTC+8 2019-12-06 17:05:28.691|ZENGINE|00000|9300|INFO>[PARAM] SHARED_POOL_SIZE = 1G

UTC+8 2019-12-06 17:05:28.691|ZENGINE|00000|9300|INFO>[PARAM] LOG_BUFFER_SIZE = 64M

UTC+8 2019-12-06 17:05:28.691|ZENGINE|00000|9300|INFO>[PARAM] LOG_BUFFER_COUNT = 8

UTC+8 2019-12-06 17:05:28.691|ZENGINE|00000|9300|INFO>[PARAM] TEMP_BUFFER_SIZE = 1G

UTC+8 2019-12-06 17:05:28.691|ZENGINE|00000|9300|INFO>[PARAM] SESSIONS = 1500

UTC+8 2019-12-06 17:05:28.691|ZENGINE|00000|9300|INFO>[PARAM] CONTROL_FILES = (/gaussdb/gaussdata/data/cntl1, /gaussdb/gaussdata/data/cntl2, /gaussdb/gaussdata/data/cntl3)

UTC+8 2019-12-06 17:05:28.691|ZENGINE|00000|9300|INFO>[PARAM] DBWR_PROCESSES = 8

UTC+8 2019-12-06 17:05:28.691|ZENGINE|00000|9300|INFO>[PARAM] INSTANCE_NAME = enmotech

UTC+8 2019-12-06 17:05:28.691|ZENGINE|00000|9300|INFO>[PARAM] ENABLE_SYSDBA_LOGIN = TRUE

UTC+8 2019-12-06 17:05:28.691|ZENGINE|00000|206158439508|INFO>starting instance(normal)

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9302|INFO>lgwr thread started

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9303|INFO>dbwr thread started

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9305|INFO>dbwr thread started

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9306|INFO>dbwr thread started

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9304|INFO>dbwr thread started

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9307|INFO>dbwr thread started

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9308|INFO>dbwr thread started

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9309|INFO>dbwr thread started

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9310|INFO>dbwr thread started

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9311|INFO>ckpt thread started

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9312|INFO>smon thread started

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9313|INFO>stats thread started

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9314|INFO>index page recycle thread started

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9315|INFO>rollback thread started

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9316|INFO>rollback thread started

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9317|INFO>rmon thread started

UTC+8 2019-12-06 17:05:28.743|ZENGINE|00000|9318|INFO>job master thread started

UTC+8 2019-12-06 17:05:28.782|ZENGINE|00000|9319|INFO>reactor thread started

UTC+8 2019-12-06 17:05:28.782|ZENGINE|00000|9300|INFO>local ip: 127.0.0.1

UTC+8 2019-12-06 17:05:28.782|ZENGINE|00000|9320|INFO>tcp-lsnr thread started

UTC+8 2019-12-06 17:05:28.782|ZENGINE|00000|9321|INFO>uds-lsnr thread started

UTC+8 2019-12-06 17:05:28.788|ZENGINE|00000|9300|INFO>SSL disabled: server certificate or private key file is not available.

UTC+8 2019-12-06 17:05:28.788|ZENGINE|00000|9300|INFO>start to alter database MOUNT

UTC+8 2019-12-06 17:05:28.823|ZENGINE|00000|9300|INFO>[ARCH] Init arch is_archive 0

UTC+8 2019-12-06 17:05:28.823|ZENGINE|00000|9300|INFO>[ARCH] Initialization complete

UTC+8 2019-12-06 17:05:28.823|ZENGINE|00000|9300|INFO>sucessfully alter database MOUNT

UTC+8 2019-12-06 17:05:28.823|ZENGINE|00000|206158439508|INFO>start to alter database OPEN

UTC+8 2019-12-06 17:05:28.841|ZENGINE|00000|140733193397332|INFO>[ARCH] Start ARCH thread for ARCHIVE_DEST_1[/ssd_data/gaussdb/gaussdata/archive_log]

UTC+8 2019-12-06 17:05:28.841|ZENGINE|00000|6477904490279216212|INFO>The last shutdown is a consistent shutdown

UTC+8 2019-12-06 17:05:28.841|ZENGINE|00000|9300|INFO>database start recovery

UTC+8 2019-12-06 17:05:28.841|ZENGINE|00000|9300|INFO>recovery from file:1,point:83006,lfn:15709

UTC+8 2019-12-06 17:05:28.841|ZENGINE|00000|9300|INFO>recovery expected least end with file:1,point:83006,lfn:15709

UTC+8 2019-12-06 17:05:28.860|ZENGINE|00000|140733193397332|INFO>[RCY] recovery real end with file:1,point:83006,lfn:15709

UTC+8 2019-12-06 17:05:28.860|ZENGINE|00000|140733193397332|INFO>[RCY] current lfn 15709, rcy point lfn 15709, consistent point 15709, lrp point lfn 15709

UTC+8 2019-12-06 17:05:28.889|ZENGINE|00000|140642999084116|INFO>no valid standby configuration

UTC+8 2019-12-06 17:05:28.889|ZENGINE|00000|9300|INFO>[DB] sse42 available 1

UTC+8 2019-12-06 17:05:28.889|ZENGINE|00000|9300|INFO>sucessfully alter database OPEN

UTC+8 2019-12-06 17:05:28.889|ZENGINE|00000|9300|INFO>instance started

UTC+8 2019-12-06 17:05:28.942|ZENGINE|00000|9315|INFO>rollback thread closed [knl_tran.c:1306]

UTC+8 2019-12-06 17:05:28.942|ZENGINE|00000|9316|INFO>rollback thread closed [knl_tran.c:1306]

UTC+8 2019-12-06 17:05:29.751|ZENGINE|00053|9327|INFO>load table parts: load table parts, uid: 0, tid: 289, partcnt: 3962 [knl_partition.c:1493]

UTC+8 2019-12-06 17:05:29.751|ZENGINE|00053|13850784198304879|INFO>load index parts: load index parts, uid: 0, tid: 289, iid: 0, index partcnt: 3962 [knl_partition.c:2358]

UTC+8 2019-12-06 17:05:29.768|ZENGINE|00053|9327|INFO>load table parts: load table parts, uid: 0, tid: 275, partcnt: 35 [knl_partition.c:1493]

UTC+8 2019-12-06 17:05:29.782|ZENGINE|00053|9327|INFO>load table parts: load table parts, uid: 0, tid: 285, partcnt: 35 [knl_partition.c:1493]

UTC+8 2019-12-06 17:05:29.787|ZENGINE|00053|14135557709898863|INFO>load index parts: load index parts, uid: 0, tid: 285, iid: 0, index partcnt: 35 [knl_partition.c:2358]

UTC+8 2019-12-06 17:05:29.791|ZENGINE|00054|9328|INFO>load table parts: load table parts, uid: 0, tid: 266, partcnt: 3962 [knl_partition.c:1493]

UTC+8 2019-12-06 17:05:29.791|ZENGINE|00054|13855182244815984|INFO>load index parts: load index parts, uid: 0, tid: 266, iid: 0, index partcnt: 3962 [knl_partition.c:2358]

UTC+8 2019-12-06 17:05:29.791|ZENGINE|00054|9328|INFO>load table parts: load table parts, uid: 0, tid: 263, partcnt: 3962 [knl_partition.c:1493]

UTC+8 2019-12-06 17:05:29.791|ZENGINE|00054|9328|INFO>load table parts: load table parts, uid: 0, tid: 264, partcnt: 3962 [knl_partition.c:1493]

UTC+8 2019-12-06 17:05:29.791|ZENGINE|00054|13855182244815984|INFO>load index parts: load index parts, uid: 0, tid: 264, iid: 0, index partcnt: 3962 [knl_partition.c:2358]

UTC+8 2019-12-06 17:05:29.793|ZENGINE|00054|9328|INFO>load table parts: load table parts, uid: 0, tid: 265, partcnt: 3962 [knl_partition.c:1493]

UTC+8 2019-12-06 17:05:29.793|ZENGINE|00054|14418132198237296|INFO>load index parts: load index parts, uid: 0, tid: 265, iid: 0, index partcnt: 3962 [knl_partition.c:2358]

UTC+8 2019-12-06 17:05:29.795|ZENGINE|00053|140642999084143|INFO>load table parts: load table parts, uid: 0, tid: 287, partcnt: 35 [knl_partition.c:1493]

UTC+8 2019-12-06 17:05:29.799|ZENGINE|00053|9327|INFO>load table parts: load table parts, uid: 0, tid: 291, partcnt: 1 [knl_partition.c:1493]

UTC+8 2019-12-06 17:05:29.799|ZENGINE|00053|16106982058501231|INFO>load index parts: load index parts, uid: 0, tid: 291, iid: 0, index partcnt: 1 [knl_partition.c:2358]

UTC+8 2019-12-06 17:05:29.801|ZENGINE|00053|46913927783535|INFO>load table parts: load table parts, uid: 0, tid: 290, partcnt: 1 [knl_partition.c:1493]

UTC+8 2019-12-06 17:05:29.801|ZENGINE|00053|13845290935133295|INFO>load index parts: load index parts, uid: 0, tid: 290, iid: 0, index partcnt: 1 [knl_partition.c:2358]

UTC+8 2019-12-06 17:05:29.805|ZENGINE|00053|9327|INFO>load table parts: load table parts, uid: 0, tid: 293, partcnt: 35 [knl_partition.c:1493]

UTC+8 2019-12-06 17:05:29.811|ZENGINE|00053|14971190841975919|INFO>load index parts: load index parts, uid: 0, tid: 293, iid: 0, index partcnt: 35 [knl_partition.c:2358]

操作系统的进程则仅有核心一个:

[root@enmodb3 run]# ps -ef|grep eygle

root 5453 5400 0 10:30 pts/0 00:00:00 su - eygle

eygle 5454 5453 0 10:30 pts/0 00:00:00 -bash

eygle 9300 1 0 17:05 pts/0 00:00:08 /ssd_data/gaussdb/1.0.0/bin/zengine open -D /ssd_data/gaussdb/gaussdata

官方文档说明如下:

GaussDB 100为保证SYS用户在紧急情况下可以操作数据库,为SYS用户预留5个独立会话、1个工作线程。

日常运行中有16个默认的自治事务会话(可以通过AUTONOMOUS_SESSIONS、KNL_AUTONOMOUS_SESSIONS参数设置),32个预留的内部使用会话,用于资源回收、checkpoint等,16个sql并行框架会话。内部使用会话功能如下表所示。

SID

session功能

0

切换实例状态,例如kernel启停,主机降备,备机升主等

1

lgwr线程

2

ckpt线程

3

smon线程

4

预留,暂未使用

5

undo preload线程

6

arch线程

7

rst线程

8

lsnr线程

9

mrp线程

10

预留,暂未使用

11

fal线程

12

timer线程

13

rollback线程

14

预留,暂未使用

15

log async线程

16

gts synchronizer线程

17

rcy线程

18

预留,暂未使用

19

预留,暂未使用

20

预留,暂未使用

21

预留,暂未使用

22

预留,暂未使用

23

预留,暂未使用

24

预留,暂未使用

25

shd trans clean线程

26

stats线程

27

插入统计信息到临时表,加载临时表的统计信息到dc上

28

加载分布式的节点

29

job线程

30

index page recycle线程

31

预留,暂未使用

这是非常早期的信息,会随着版本变化而变化。

Posted by eygle at 5:09 PM | Permalink | Beginner (54)

近期发表

  • GaussDB 100: 用户权限以及修改 SYS 用户的缺省口令 - December 5, 2019
  • GaussDB 100 OLTP: 缩减创建数据库的20G空间需求及GS-00714 - December 4, 2019
  • ORA-00600 KGLDELETEALLPINSONOBJ1 一个横贯10g/11g/12c的BUG - December 3, 2019
  • 笔记:从点滴讨论中看 OceanBase 的设计理念和架构 - November 27, 2019
  • GS-00001 - GaussDB 100 OLTP第一号错误的诊断和解决 - November 25, 2019
  • GaussDB OLTP 100 华为数据库的创建脚本和模板 - November 25, 2019
  • 2019-11-15 数据技术嘉年华北京盛大开启 - November 1, 2019
  • 2019,细数国产的那些和 MySQL 有关都数据库 - November 1, 2019
  • 替换Oracle 数据库,阿里巴巴和亚马逊在逃离什么? - October 18, 2019
  • 2019,国产数据库元年开启新纪元 - October 16, 2019


  • CopyRight © 2004 ~ 2012 eygle.com, All rights reserved.