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

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 (266)

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)

December 5, 2019

GaussDB 100: 用户权限以及修改 SYS 用户的缺省口令

在完成 GaussDB 的安装之后,应该及时修改缺省的用户口令,避免发生安全问题。

缺省的系统存在两个帐号,SYS 是系统的 SYSDBA 用户,PUBLIC 是一个特殊都公用用户对象。

SQL> select username from dba_users;
USERNAME
------------------------------------------------------
SYS
PUBLIC

SYS 用户的缺省口令是: Changeme_123

[root@enmodb1 ~]# su - eygle
Last login: Wed Dec 4 22:41:05 CST 2019 on pts/1
[eygle@enmodb1 ~]$ zsql SYS/Changeme_123@127.0.0.1:1888

Warning: SSL connection to server without CA certificate is insecure. Continue anyway? (y/n):y
connected.

SQL> select * from v$version;

VERSION
GaussDB_100_1.0.0.B019 Release 0b7bb43
ZENGINE
0b7bb43

3 rows fetched.

SQL> ALTER USER SYS IDENTIFIED BY ENmotech_100 REPLACE Changeme_123;

Succeed.

SQL> connect SYS/ENmotech_100@127.0.0.1:1888

Warning: SSL connection to server without CA certificate is insecure. Continue anyway? (y/n):y
connected.

SQL>

这样就完成了数据库创建之后的第一个安全工作。

GaussDB 缺省有 4 个角色:

SQL> select role from dba_roles;

ROLE

----------------------------------------------------------------

DBA

RESOURCE

CONNECT

STATISTICS

4 rows fetched.

权限视图同 Oracle 一样是 DBA_SYS_PRIVS :

SQL> select grantee,privilege from dba_sys_privs;

GRANTEE PRIVILEGE

---------------------------------------------------------------- ----------------------------

SYS ALTER ANY INDEX

SYS ALTER ANY MATERIALIZED VIEW

SYS ALTER ANY PROCEDURE

SYS ALTER ANY ROLE

SYS ALTER ANY SEQUENCE

SYS ALTER ANY TABLE

SYS ALTER ANY TRIGGER

SYS ALTER DATABASE

SYS ALTER PROFILE

SYS ALTER SESSION

SYS ALTER SYSTEM

SYS ALTER TABLESPACE

SYS ALTER USER

SYS CREATE ANY INDEX

SYS CREATE ANY MATERIALIZED VIEW

SYS CREATE ANY PROCEDURE

SYS CREATE ANY SEQUENCE

SYS CREATE ANY SYNONYM

SYS CREATE ANY TABLE

SYS CREATE ANY TRIGGER

SYS CREATE ANY VIEW

SYS CREATE DATABASE

SYS CREATE MATERIALIZED VIEW

SYS CREATE NODE

SYS CREATE PROCEDURE

SYS CREATE PROFILE

SYS CREATE PUBLIC SYNONYM

SYS CREATE ROLE

SYS CREATE SEQUENCE

SYS CREATE SESSION

SYS CREATE SYNONYM

SYS CREATE TABLE

SYS CREATE TABLESPACE

SYS CREATE TRIGGER

SYS CREATE USER

SYS CREATE VIEW

SYS CREATE DISTRIBUTE RULE

SYS DROP ANY INDEX

SYS DROP ANY MATERIALIZED VIEW

SYS DROP ANY PROCEDURE

SYS DROP ANY ROLE

SYS DROP ANY SEQUENCE

SYS DROP ANY SYNONYM

SYS DROP ANY TABLE

SYS DROP ANY TRIGGER

SYS DROP ANY VIEW

SYS DROP PROFILE

SYS DROP PUBLIC SYNONYM

SYS DROP TABLESPACE

SYS DROP USER

SYS FLASHBACK ANY TABLE

SYS FLASHBACK ARCHIVE ADMINISTER

SYS GLOBAL QUERY REWRITE

SYS GRANT ANY OBJECT PRIVILEGE

SYS GRANT ANY PRIVILEGE

SYS GRANT ANY ROLE

SYS LOCK ANY TABLE

SYS MANAGE TABLESPACE

SYS ON COMMIT REFRESH

SYS PURGE DBA_RECYCLEBIN

SYS READ ANY TABLE

SYS SELECT ANY SEQUENCE

SYS SELECT ANY TABLE

SYS UNLIMITED TABLESPACE

SYS UNDER ANY VIEW

SYS COMMENT ANY TABLE

SYS UPDATE ANY TABLE

SYS INSERT ANY TABLE

SYS DELETE ANY TABLE

SYS EXECUTE ANY PROCEDURE

SYS SYSBACKUP

SYS SYSDBA

SYS SYSOPER

SYS ANALYZE ANY

SYS DROP NODE

SYS ALTER NODE

DBA ALTER ANY INDEX

DBA ALTER ANY MATERIALIZED VIEW

DBA ALTER ANY PROCEDURE

DBA ALTER ANY ROLE

DBA ALTER ANY SEQUENCE

DBA ALTER ANY TABLE

DBA ALTER ANY TRIGGER

DBA ALTER DATABASE

DBA ALTER PROFILE

DBA ALTER SESSION

DBA ALTER SYSTEM

DBA ALTER TABLESPACE

DBA ALTER USER

DBA CREATE ANY INDEX

DBA CREATE ANY MATERIALIZED VIEW

DBA CREATE ANY PROCEDURE

DBA CREATE ANY SEQUENCE

DBA CREATE ANY SYNONYM

DBA CREATE ANY TABLE

DBA CREATE ANY TRIGGER

DBA CREATE ANY VIEW

DBA CREATE DATABASE

DBA CREATE MATERIALIZED VIEW

DBA CREATE NODE

DBA CREATE PROCEDURE

DBA CREATE PROFILE

DBA CREATE PUBLIC SYNONYM

DBA CREATE ROLE

DBA CREATE SEQUENCE

DBA CREATE SESSION

DBA CREATE SYNONYM

DBA CREATE TABLE

DBA CREATE TABLESPACE

DBA CREATE TRIGGER

DBA CREATE USER

DBA CREATE VIEW

DBA CREATE DISTRIBUTE RULE

DBA DROP ANY INDEX

DBA DROP ANY MATERIALIZED VIEW

DBA DROP ANY PROCEDURE

DBA DROP ANY ROLE

DBA DROP ANY SEQUENCE

DBA DROP ANY SYNONYM

DBA DROP ANY TABLE

DBA DROP ANY TRIGGER

DBA DROP ANY VIEW

DBA DROP PROFILE

DBA DROP PUBLIC SYNONYM

DBA DROP TABLESPACE

DBA DROP USER

DBA FLASHBACK ANY TABLE

DBA FLASHBACK ARCHIVE ADMINISTER

DBA GLOBAL QUERY REWRITE

DBA GRANT ANY OBJECT PRIVILEGE

DBA GRANT ANY PRIVILEGE

DBA GRANT ANY ROLE

DBA LOCK ANY TABLE

DBA MANAGE TABLESPACE

DBA ON COMMIT REFRESH

DBA PURGE DBA_RECYCLEBIN

DBA READ ANY TABLE

DBA SELECT ANY SEQUENCE

DBA SELECT ANY TABLE

DBA UNLIMITED TABLESPACE

DBA UNDER ANY VIEW

DBA COMMENT ANY TABLE

DBA UPDATE ANY TABLE

DBA INSERT ANY TABLE

DBA DELETE ANY TABLE

DBA EXECUTE ANY PROCEDURE

DBA SYSBACKUP

DBA SYSDBA

DBA SYSOPER

DBA ANALYZE ANY

DBA DROP NODE

DBA ALTER NODE

RESOURCE CREATE PROCEDURE

RESOURCE CREATE SEQUENCE

RESOURCE CREATE TABLE

RESOURCE CREATE TRIGGER

CONNECT CREATE SESSION

157 rows fetched.

供参考。

Posted by eygle at 6:15 PM | Permalink | Beginner (54)

December 4, 2019

GaussDB 100 OLTP: 缩减创建数据库的20G空间需求及GS-00714

在 GaussDB 100 创建数据库时,首先会进行磁盘空间检查,如果低于 20G 就会抛出异常,所以很多朋友说 GaussDB 100 的最小空间使用是 20GB。

通常空间不足的信息如下:

[root@enmodb1 GaussDB_100_1.0.0-DATABASE-REDHAT-64bit]# python install.py -U eygle:enmotech -R /gaussdb/1.0.0 -D /gaussdb/gaussdata -C LSNR_ADDR=127.0.0.1,192.168.1.132 -C LSNR_PORT=1888
Checking runner.
Checking parameters.
End check parameters.
Checking user.
End check user.
Checking old install.
End check old install.
Checking kernel parameters.
Checking directory.
Error: The sum of database program and data directories available size smaller than 20580M, current size is: 14457M

这个限制其实来自 install.py 脚本中的检查:

    vfs = os.statvfs(self.data)
       availableSize = vfs.f_bavail * vfs.f_bsize / (1024*1024)
       log("Database data directory available size: %sM" % str(availableSize))
       # check install path and data dir are in the same path or not
        if stdout_list[0] == stdout_list[1]:
            if(availableSize < 20580):
                logExit("The sum of database program and data directories available size smaller than 20580M, "
                        "current size is: %sM" % str(availableSize))
        else:
            if(availableSize < 20480):
                logExit("Database data directory available size smaller than 20480M, current size is: "
                        "%sM" % str(availableSize))

可以修改这个限制来减少空间需求。当然前提是降低数据库的创建空间需求。

在 GaussDB 100中,创建数据库调用的脚本是 create_database.sample.sql 。

通过这个脚本可以看到 GaussDB OLTP 数据库的创建过程和关键步骤。在 GaussDB 100中,缺省创建 3 个控制文件,6个 2G 大小的日志文件,用户表空间 5 个 1G 的数据文件。1 个 1G 的 SYSTEM 表空间文件,数据库缺省创建既运行在归档模式下:

create database gauss character set binary
CONTROLFILE
('/opt/gaussdb/data/data/cntl1',
'/opt/gaussdb/data/data/cntl2',
'/opt/gaussdb/data/data/cntl3')
LOGFILE
('/opt/gaussdb/data/data/log1' size 2G,
'/opt/gaussdb/data/data/log2' size 2G,
'/opt/gaussdb/data/data/log3' size 2G,
'/opt/gaussdb/data/data/log4' size 2G,
'/opt/gaussdb/data/data/log5' size 2G,
'/opt/gaussdb/data/data/log6' size 2G)
system tablespace DATAFILE
'/opt/gaussdb/data/data/system' size 1G
undo tablespace DATAFILE
'/opt/gaussdb/data/data/undo' size 1G
default tablespace DATAFILE
'/opt/gaussdb/data/data/user1' size 1G autoextend ON next 32M,
'/opt/gaussdb/data/data/user2' size 1G autoextend ON next 32M,
'/opt/gaussdb/data/data/user3' size 1G autoextend ON next 32M,
'/opt/gaussdb/data/data/user4' size 1G autoextend ON next 32M,
'/opt/gaussdb/data/data/user5' size 1G autoextend ON next 32M
temporary tablespace TEMPFILE
'/opt/gaussdb/data/data/temp1_01' size 160M autoextend ON next 32M,
'/opt/gaussdb/data/data/temp1_02' size 160M autoextend ON next 32M nologging
tablespace TEMPFILE
'/opt/gaussdb/data/data/temp2_01' size 160M autoextend ON next 32M,
'/opt/gaussdb/data/data/temp2_02' size 160M autoextend ON next 32M nologging
undo tablespace TEMPFILE
'/opt/gaussdb/data/data/temp2_undo' size 1G
ARCHIVELOG;

我们如果不是大规模测试,可以将 LOG 日志的大小改为 256M ,这样能够节约大约 10G 的存储空间。

修改了创建数据库的脚本,再修改 install.py 将 20580 和 20480 修改为 10580 和 10480 ,就能够剩下创建空间。

在创建数据库时,可以通过 -f 参数调用自定义的创建数据库脚本,即可解决。类似如下:

python install.py -U eygle:enmotech -R /gaussdb/1.0.0 -D /gaussdb/gaussdata -C LSNR_ADDR=127.0.0.1,192.168.1.132 -C LSNR_PORT=1888 -f /gaussdb/create_database.sample.sql

注意 LOG 日志的大小和 Log Buffer 设置相关,过小的设置会出现类似如下错误,我这里将 LOG 设置为 256M 大小:

GS-00714, Log file size should be larger than log keep size 125845504

探索 GaussDB ,从一点一滴开始。

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

December 3, 2019

ORA-00600 KGLDELETEALLPINSONOBJ1 一个横贯10g/11g/12c的BUG

在墨天轮上有朋友遇到了一个少见的 ORA-600 错误,第一个错误参数是:KGLDELETEALLPINSONOBJ1 。

从 KGLDELETEALLPINSONOBJ1 的命名可以很快的猜测到基本情况:

KGL - Kernel Generic Library cache ,这意味着问题位于 Library Cache 层面;

DELETE - 代表是删除信息;

ALLPINSONOBJ - 代表 ALL Pins On OBJ ,代表某个对象上的所有 Library Cache Pin .

所以这个错误意味着,Oracle 在清除某个对象上的全部 Library Cache Pin 时,遇到了内部错误,抛出了异常。这种情况多数是代码层面的BUG引起的。

我们摘录一段错误跟踪文件:

Trace file /u01/app/oracle/diag/taldb1_m001_1107.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME: /u01/app/oracle/product/12.2.0.1/dbhome_1
System name: Linux
Release: 2.6.39-400.281.1.el6uek.x86_64
Version: #1 SMP Fri Jun 17 20:10:16 PDT 2016
Machine: x86_64
Instance name: portaldb1
Redo thread mounted by this instance: 1
Oracle process number: 646
Unix process pid: 1107, image: oracle@bserver (M001)


*** 2019-12-02T03:37:04.327775+08:00
*** SESSION ID:(2589.45217) 2019-12-02T03:37:04.327800+08:00
*** CLIENT ID:() 2019-12-02T03:37:04.327809+08:00
*** SERVICE NAME:(SYS$BACKGROUND) 2019-12-02T03:37:04.327817+08:00
*** MODULE NAME:(MMON_SLAVE) 2019-12-02T03:37:04.327826+08:00
*** ACTION NAME:(Advisor Task Maintenance) 2019-12-02T03:37:04.327834+08:00
*** CLIENT DRIVER:() 2019-12-02T03:37:04.327842+08:00

2019-12-02T03:37:04.327545+08:00
Incident 1525642 created, dump file: /u01/app/oracle/taldb1_m001_1107_i15.trc
ORA-00600: internal error code, arguments: [kglDeleteAllPinsOnObj1], [0x1D4E29D4A8], [0x18D8D5C8C8], [], [], [], [], [], [], [], [], []

从以上跟踪文件可以看到,这个问题的基本要素:

1. MMON 执行内部操作;

2.内部的操作是 Advisor Task Maintenance;

进一步从跟踪文件可以获得详细的信息,系统任务 SQL Access Advisor 的 CLEANUP / DELETE 清理操作触发了 ORA-600 错误,触发语句是一个 DROP 物化视图的操作:

----- Current SQL Statement for this session (sql_id=5qcmq7xb5zrhj) -----
DROP MATERIALIZED VIEW "SYSTEM"."MV$$_341480004"
[TOC00005]
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x1d1d528fd0 1619 package body SYS.PRVT_ACCESS_ADVISOR.CLEANUP_TASK
0x1d1d528fd0 1265 package body SYS.PRVT_ACCESS_ADVISOR.RESET_TASK
0x1d1d528fd0 1356 package body SYS.PRVT_ACCESS_ADVISOR.DELETE_TASK
0x180eab3b78 38 type body SYS.WRI$_ADV_SQLACCESS_ADV.SUB_DELETE
0x1d5e8873e8 2132 package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x1d5e8873e8 7326 package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x180e53c568 1 anonymous block

结合这些因素,接下来需要借助官方支持站点 MOS 查找已知的 BUG 。

第一个相关的BUG来自 Oracle 10g ,影响版本是 10.2.0.4 :

Bug 8262446 Problems with DBMS_ADVISOR.DELETE_TASK

这是最直接相关的问题,同样的问题在 11g 中还有出现,现在看起来在 12c 中同样没有彻底解决。

当然这个问题的触发原因是未提交事务引起的,如果能在 DELETE 前确保没有活动事务自然可以避免,另外在频繁触发的环境中,可以考虑停用 DBMS_ADVISOR 定时任务(事实上,很多用户并未使用和参考这个特性的结论)。

顺手还发现了 12.2 中的另外一个 BUG:

Bug 21632909 ORA-600[kgldeleteallpinsonobj1] When Create a Table with Attribute Clustering with ZONEMAP

以上供参考。

Posted by eygle at 11:43 AM | Permalink | Case (164) | Internal (130)

近期发表

  • 笔记:从点滴讨论中看 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
  • Oracle 20c 新特性详解:SQL Macro 宏 SCALAR / TABLE 模式 - October 9, 2019
  • Oracle Database 20c 十大新特性一览 - New Features - October 8, 2019
  • Oracle 20c 新特性:细粒度的Multitenant 和3个PDB多租户支持 - October 7, 2019


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