DM7搭建读写分离集群

读写分离集群
环境说明
下列机器事先都安装了DM软件,安装路径为/dm7,执行程序保存在/dm7/bin目录中,数据存放路径为/dm7/data

机器名     IP地址                    初始状态                          操作系统    
18c1     10.13.13.171(对外)         主库 JY1                         redhat 6.7
         10.13.13.171(mal对内)



18c2     10.13.13.172(对外)
         10.13.13.171(mal对内)      备库 JY2                         redhat 6.7

dmks     10.13.13.187               确认监视器                       redhat 6.7



实例名       port_num             dw_port    mal_host                mal_port        mal_dw_port
jy1          5236                 5239       10.13.13.171          5237            5238
jy2          5236                 5239       10.13.13.172          5237            5238

数据准备
在主库机器上初始化数据库到目录/dm7/data:

[dmdba@18c1 bin]$ ./dminit path=/dm7/data db_name=jy instance_name=jy1 port_num=5236 page_size=8 charset=0
initdb V7.1.6.46-Build(2018.02.08-89107)ENT 
db version: 0x7000a
file dm.key not found, use default license!
License will expire in 14 day(s) on 2020-06-17

 log file path: /dm7/data/jy/jy01.log


 log file path: /dm7/data/jy/jy02.log

write to dir [/dm7/data/jy].
create dm database success. 2020-06-03 16:01:44

注册服务用于启动数据库

[root@18c1 root]# ./dm_service_installer.sh -i /dm7/data/jy/dm.ini -p jy1 -t dmserver
ln -s '/usr/lib/systemd/system/DmServicejy1.service' '/etc/systemd/system/multi-user.target.wants/DmServicejy1.service'
Finished to create the service (DmServicejy1)

正常启动数据库并正常关闭

[root@18c1 root]# service DmServicejy1 start
Redirecting to /bin/systemctl start  DmServicejy1.service
[root@18c1 root]# ps -ef | grep dmserver
dmdba    29989     1 13 16:04 ?        00:00:04 /dm7/bin/dmserver /dm7/data/jy/dm.ini -noconsole
root     30292  3890  0 16:05 pts/1    00:00:00 grep --color=auto dmserver
[dmdba@18c1 bin]$ ./disql SYSDBA/SYSDBA

Server[LOCALHOST:5236]:mode is normal, state is open
login used time: 8.010(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT 
Connected to: DM 7.1.6.46
SQL> exit

[root@18c1 root]# service DmServicejy1 stop
Redirecting to /bin/systemctl stop  DmServicejy1.service

一.配置操作
主库
配置dm.ini文件,配置以下参数

[dmdba@18c1 jy]$ vi dmmal.ini
INSTANCE_NAME = JY1
PORT_NUM = 5236                             
DW_PORT = 5239                              
DW_ERROR_TIME = 60                         
ALTER_MODE_STATUS = 0                       
ENABLE_OFFLINE_TS = 2                       
MAL_INI = 1                                
ARCH_INI = 1                               
HA_INST_CHECK_FLAG = 1                     
RLOG_SEND_APPLY_MON = 64                   

配置dmmal.ini文件
配置MAL系统,各主备库的dmmal.ini配置必须完全一致,MAL_HOST使用内部网络IP,MAL_PORT与dm.ini中PORT_NUM使用不同的端口值,MAL_DW_PORT是各实例对应的守护进程之间,以及守护进程和监视器之间的通信端口,配置如下:

[dmdba@18c1 jy]$ vi dmmal.ini
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5
[MAL_INST1]
MAL_INST_NAME = JY1
MAL_HOST = 10.13.13.171
MAL_PORT = 5237
MAL_INST_HOST = 10.13.13.171
MAL_INST_PORT = 5236
MAL_DW_PORT = 5238

[MAL_INST2]
MAL_INST_NAME = JY2
MAL_HOST = 10.13.13.172
MAL_PORT = 5237
MAL_INST_HOST = 10.13.13.172
MAL_INST_PORT = 5236
MAL_DW_PORT = 5238

配置dmarch.ini
修改dmarch.ini,配置本地归档和实时归档。除了本地归档外,其他归档配置项中的ARCH_DEST表示实例是Primary模式时,需要同步归档数据的目标实例名。当前实例DM1是主库,需要向DM2(实时备库)同步数据,因此实时归档的ARCH_DEST配置为DM2。

[dmdba@18c1 jy]$ vi dmarch.ini
[ARCHIVE_TIMELY]
ARCH_TYPE = TIMELY
ARCH_DEST = JY2
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm7/data/jy/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0

配置dmwatcher.ini
修改dmwatcher.ini配置守护进程,配置为全局守护类型,使用自动切换模式。

[dmdba@18c1 jy]$ vi dmwatcher.ini
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = AUTO
DW_ERROR_TIME = 10
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 10
INST_OGUID = 453332
INST_INI = /dm7/data/jy/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dm7/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0

配置dmwatcher.ctl
同一个守护进程组,必须使用同一份dmwatcher.ctl。因此,只需要使用dmctlcvt工具生成一份dmwatcher.ctl文件,然后分别拷贝到各个数据库目录下即可。在配置完成dmwatcher.ini后,使用dmctlcvt工具生成dmwatcher.ctl:(特别注意,DEST目录为jy的上一级目录,否则不生成控制文件)

[dmdba@18c1 bin]$ ./dmctlcvt TYPE=3 SRC=/dm7/data/jy/dmwatcher.ini DEST=/dm7/data
DMCTLCVT V7.1.6.46-Build(2018.02.08-89107)ENT 
convert txt to ctl success!

会在/dm7/data目录中生成一个GRP1目录,在GRP1目录中生成了dmwatcher.ctl控制文件

[dmdba@18c1 data]$ ls -lrt
total 4
drwxr-xr-x 6 dmdba dinstall 4096 Jun  3 16:23 jy
drwxr-xr-x 2 dmdba dinstall   26 Jun  3 16:23 GRP1

[dmdba@18c1 data]$ cd GRP1/
[dmdba@18c1 GRP1]$ ls -lrt
total 4
-rw-r--r-- 1 dmdba dinstall 512 Jun  3 16:23 dmwatcher.ctl
[dmdba@18c1 GRP1]$ cp  dmwatcher.ctl /dm7/data/jy/

拷贝生成的dmwatcher.ctl文件到数据文件目录/dm7/data/jy。

将主库相关文件传输到备机:

[dmdba@18c1 dm7]$ scp -r data/ dmdba@10.13.13.172:/dm7/
The authenticity of host '10.13.13.172 (10.13.13.172)' can't be established.
ECDSA key fingerprint is 7f:1f:9a:0f:8b:d1:e0:17:32:08:12:73:d8:1d:9c:da.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.13.13.172' (ECDSA) to the list of known hosts.
dmdba@10.13.13.172's password: 
dminit20200603160057.log                                                                                                                                                                                  100%  727     0.7KB/s   00:00    
sqllog.ini                                                                                                                                                                                                100%  479     0.5KB/s   00:00    
dm.ctl                                                                                                                                                                                                    100% 5120     5.0KB/s   00:00    
jy01.log                                                                                                                                                                                                  100%  256MB 128.0MB/s   00:02    
jy02.log                                                                                                                                                                                                  100%  256MB  85.3MB/s   00:03    
dm_20200603160143_364345.ctl                                                                                                                                                                              100% 5120     5.0KB/s   00:00    
dm_20200603160450_367099.ctl                                                                                                                                                                              100% 5120     5.0KB/s   00:00    
SYSTEM.DBF                                                                                                                                                                                                100%   21MB  21.0MB/s   00:00    
dm_service.prikey                                                                                                                                                                                         100%  633     0.6KB/s   00:00    
MAIN.DBF                                                                                                                                                                                                  100%  128MB 128.0MB/s   00:01    
ROLL.DBF                                                                                                                                                                                                  100%  128MB 128.0MB/s   00:01    
dminst.sys                                                                                                                                                                                                100%  220     0.2KB/s   00:00    
TEMP.DBF                                                                                                                                                                                                  100%   10MB  10.0MB/s   00:00    
rep_conflict.log                                                                                                                                                                                          100%   12     0.0KB/s   00:00    
dm.ini                                                                                                                                                                                                    100%   40KB  39.8KB/s   00:00    
dmmal.ini                                                                                                                                                                                                 100%  558     0.5KB/s   00:00    
dmarch.ini                                                                                                                                                                                                100%  340     0.3KB/s   00:00    
dmwatcher.ini                                                                                                                                                                                             100%  665     0.7KB/s   00:00    
dmwatcher.ctl                                                                                                                                                                                             100%  512     0.5KB/s   00:00    
dmwatcher.ctl                                                                                                                                                                                             100%  512     0.5KB/s   00:00    
[dmdba@18c1 dm7]$ 

备机修改相关配置
修改dm.ini

INSTANCE_NAME = JY2

修改dmarch.ini

ARCH_DEST = JY1

dmwatcher.ini,dmwatcher.ctl,dmmal.ini与主库一致不用修改 二:启动到mount状态设置oguid 主库

[dmdba@18c1 bin]$ ./dmserver /dm7/data/jy/dm.ini mount
file dm.key not found, use default license!
version info: develop
Use normal os_malloc instead of HugeTLB
Use normal os_malloc instead of HugeTLB
DM Database Server x64 V7.1.6.46-Build(2018.02.08-89107)ENT  startup...
License will expire in 14 day(s) on 2020-06-17
ckpt lsn: 32981
SYSTEM IS READY.

[dmdba@18c1 bin]$ ./disql SYSDBA/SYSDBA

Server[LOCALHOST:5236]:mode is normal, state is mount
login used time: 5.995(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT 
Connected to: DM 7.1.6.46
SQL> sp_set_oguid(453332);
DMSQL executed successfully
used time: 68.576(ms). Execute id is 1.

备库

[dmdba@18c2 bin]$ ./dmserver /dm7/data/jy/dm.ini mount
file dm.key not found, use default license!
version info: develop
Use normal os_malloc instead of HugeTLB
Use normal os_malloc instead of HugeTLB
DM Database Server x64 V7.1.6.46-Build(2018.02.08-89107)ENT  startup...
License will expire in 14 day(s) on 2020-06-17
ckpt lsn: 32981
SYSTEM IS READY.

[dmdba@18c2 bin]$ ./disql SYSDBA/SYSDBA

Server[LOCALHOST:5236]:mode is normal, state is mount
login used time: 6.344(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT 
Connected to: DM 7.1.6.46
SQL> sp_set_oguid(453332);
DMSQL executed successfully
used time: 32.329(ms). Execute id is 1.

注册服务用于启动数据库

[root@18c2 root]# ./dm_service_installer.sh -i /dm7/data/jy/dm.ini -p jy2 -t dmserver
ln -s '/usr/lib/systemd/system/DmServicejy2.service' '/etc/systemd/system/multi-user.target.wants/DmServicejy2.service'
Finished to create the service (DmServicejy2)

三:打开数据库
主库以primary打开

SQL> alter database primary;
executed successfully
used time: 43.384(ms). Execute id is 0.

备库以standby 打开

SQL> alter database standby;
executed successfully
used time: 100.645(ms). Execute id is 0.

四:启动守护进程
启动各个主备库上的守护进程:
主库

[dmdba@18c1 bin]$ ./dmwatcher /dm7/data/jy/dmwatcher.ini
DMWATCHER[2.1] V7.1.6.46-Build(2018.02.08-89107)ENT 
DMWATCHER[2.1] IS READY
show
2020-06-03 16:43:10
---------------------------------------------------------------------------
GROUP_NAME       TYPE      MODE      OGUID       MPP_FLAG  AUTO_RESTART LOCAL_DW_STATUS 
GRP1             GLOBAL    AUTO      453332      FALSE     TRUE         OPEN            

INST_OK   NAME             SVR_MODE  SYS_STATUS   OPEN_CNT        RTYPE     N_TASK    TASK_MEM_USED   FLSN            CLSN            SLSN            SSLSN           
OK        JY1              PRIMARY   OPEN         2               TIMELY    0         0               34412           34412           34412           34412           
---------------------------------------------------------------------------

备库

[dmdba@18c2 bin]$ ./dmwatcher /dm7/data/jy/dmwatcher.ini
DMWATCHER[2.1] V7.1.6.46-Build(2018.02.08-89107)ENT 
DMWATCHER[2.1] IS READY
show
2020-06-03 16:43:05
---------------------------------------------------------------------------
GROUP_NAME       TYPE      MODE      OGUID       MPP_FLAG  AUTO_RESTART LOCAL_DW_STATUS 
GRP1             GLOBAL    AUTO      453332      FALSE     TRUE         OPEN            

INST_OK   NAME             SVR_MODE  SYS_STATUS   OPEN_CNT        RTYPE     N_TASK    TASK_MEM_USED   FLSN            CLSN            SLSN            SSLSN           
OK        JY2              STANDBY   OPEN         1               TIMELY    0         0               32981           32981           32981           32981           
---------------------------------------------------------------------------

五:查看file_lsn与cur_lsn主备库是否一致
主库

SQL> select file_LSN, cur_LSN from v$rlog;

LINEID     FILE_LSN             CUR_LSN             
---------- -------------------- --------------------
1          34412                34412

used time: 1.203(ms). Execute id is 6.

备库

SQL> select file_LSN, cur_LSN from v$rlog;

LINEID     FILE_LSN             CUR_LSN             
---------- -------------------- --------------------
1          34412                34412

used time: 1.228(ms). Execute id is 3.

测试数据同步
主库:

SQL> create table t1(id int);
executed successfully
used time: 23.402(ms). Execute id is 4.
SQL> insert into t1 values(1);
affect rows 1

used time: 1.303(ms). Execute id is 5.
SQL> commit;
executed successfully
used time: 4.034(ms). Execute id is 6.
SQL> select file_LSN, cur_LSN from v$rlog;

LINEID     FILE_LSN             CUR_LSN             
---------- -------------------- --------------------
1          34443                34443

used time: 0.555(ms). Execute id is 7.

备库:

SQL> select file_LSN, cur_LSN from v$rlog;

LINEID     FILE_LSN             CUR_LSN             
---------- -------------------- --------------------
1          34443                34443

used time: 0.325(ms). Execute id is 2.
SQL> select * from t1;

LINEID     ID         
---------- -----------
1          1

used time: 1.373(ms). Execute id is 3.

六:配置监视器(基本要求,安装dm7的软件)
由于主库和实时备库的守护进程配置为自动切换模式,因此这里选择配置确认监视器。和普通监视器相比,确认监视器除了相同的命令支持外,在主库发生故障时,能够自动通知实时备库接管为新的主库,具有自动故障处理的功能。修改dmmonitor.ini配置确认监视器,其中MON_DW_IP中的IP和PORT和dmmal.ini中的MAL_HOST和MAL_DW_PORT配置项保持一致。

[dmdba@ora19c data]$ vi dmmonitor.ini
[dmdba@dmks dmdbms]$ vi dmmonitor.ini
MON_DW_CONFIRM = 1
MON_LOG_PATH = /dm_home/dmdbms/log
MON_LOG_INTERVAL = 60
MON_LOG_FILE_SIZE = 32
MON_LOG_SPACE_LIMIT = 0
[GRP1]
MON_INST_OGUID = 453332
MON_DW_IP = 10.13.13.171:5238
MON_DW_IP = 10.13.13.172:5238

启动监视器:

[dmdba@dmks bin]$ ./dmmonitor /dm_home/dmdbms/dmmonitor.ini
[monitor]         2020-06-03 10:54:59: DMMONITOR[2.1] V7.1.6.46-Build(2018.02.08-89107)ENT 
[monitor]         2020-06-03 10:54:59: DMMONITOR[2.1] IS READY.

[monitor]         2020-06-03 10:54:59: Received message from(JY1)
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN           CLSN           SSLSN          SLSN           
                  2020-06-03 16:47:46  OPEN           OK        JY1              OPEN        PRIMARY   VALID    2        34443          34443          34443          34443          

[monitor]         2020-06-03 10:54:59: Received message from(JY2)
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN           CLSN           SSLSN          SLSN           
                  2020-06-03 16:47:47  OPEN           OK        JY2              OPEN        STANDBY   VALID    2        34443          34443          34443          34443          

在JDBC连接串中增加了两个连接属性:
rwSeparate 是否使用读写分离系统,默认0;取值(0不使用,1使用)。
rwPercent 分发到主库的事务占主备库总事务的百分比,有效值0~100,默认值25。
下面使用jdbc来测试读写分离

package cs;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;


public class testrw 
{
	// 驱动
    public static String driver = "dm.jdbc.driver.DmDriver";
    // 连接URL
    public static String url = "jdbc:dm://10.13.13.171:5236?rwSeparate=1&rwPercent=10"; 
    // 数据库用户
    public static String username = "SYSDBA";
    // 数据库密码
    public static String password = "SYSDBA";
 
    /**
     * @param driver
     * @param url
     * @param username
     * @param password
     * @return
     */
    public static Connection createConnection(String driver, String url,
            String username, String password) {
        Connection connection = null;
        try {
            // 加载JDBC驱动程序
            Class.forName(driver);
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception ex) {
            ex.printStackTrace();
            System.err.println("Load JDBC Driver Error : " + ex.getMessage());
        }
        return connection;
    }
 
    /**
     * @param args
     */
    public static void main(String[] args){
        Connection connection = createConnection(driver,url,username,password);
        System.out.println(connection);
        try
        {  PreparedStatement ps1=connection.prepareStatement("select * from t2;");
           ResultSet rs = ps1.executeQuery();
           Statement ps=connection.createStatement();
           ps.addBatch("insert into t2 values(2)");
           ps.executeBatch();
           String name = "";
           while (rs.next())
           {
             name = rs.getString("ID");
             System.out.println("ID is:"+name);
           }
           rs.close();
           ps.close();
        }
        catch (Exception ex)
        {
        	ex.printStackTrace();
            System.err.println("Run SQL Error : " + ex.getMessage());
        }
    }

}

备库上执行的是查询语句执行时间是2020-06-10 22:18:14.000000

SQL> select * from v$sessions;

LINEID     SESS_ID              SESS_SEQ    SQL_TEXT                  STATE  N_STMT      N_USED_STMT SEQ_NO      CURR_SCH USER_NAME TRX_ID               CREATE_TIME                 CLNT_TYPE TIME_ZONE CHK_CONS CHK_IDENT RDONLY INS_NULL COMPILE_FLAG AUTO_CMT DDL_AUTOCMT RS_FOR_QRY CHK_NET ISO_LEVEL   CLNT_HOST       APPNAME CLNT_IP               OSNAME                 CONN_TYPE    VPOOLADDR            RUN_STATUS MSG_STATUS LAST_RECV_TIME              LAST_SEND_TIME              DCP_FLAG THRD_ID     CONNECTED   PORT_TYPE   SRC_SITE    MAL_ID               CONCURRENT_FLAG
---------- -------------------- ----------- ------------------------- ------ ----------- ----------- ----------- -------- --------- -------------------- --------------------------- --------- --------- -------- --------- ------ -------- ------------ -------- ----------- ---------- ------- ----------- --------------- ------- --------------------- ---------------------- ------------ -------------------- ---------- ---------- --------------------------- --------------------------- -------- ----------- ----------- ----------- ----------- -------------------- ---------------
1          139663411057416      3           select * from v$sessions; ACTIVE 64          1           16          SYSDBA   SYSDBA    140737488355329      2020-06-03 16:43:58.000000  SQL3      +08:00    N        N         N      Y        N            N        Y           N          N       1           18c2            disql   ::1                   Linux                   HOMOGENEOUS 139663410989816      RUNNING    RECIEVE    2020-06-10 22:18:39.000000  2020-06-10 22:16:07.000000  N        4982        1           0           65535       NULL                 0
2          139663412173640      11          select * from t2;         IDLE   64          2           5           SYSDBA   SYSDBA    0                    2020-06-10 22:18:14.000000  JDBC      +08:00    N        N         N      Y        N            Y        Y           N          N       1           WIN-ROUOJ6ERFO3         ::ffff:10.13.13.242 Windows Server 2008 R2  HOMOGENEOUS 139663412106040      IDLE       SEND       2020-06-10 22:18:21.000000  2020-06-10 22:18:21.000000  N        12355       1           0           65535       NULL                 0

used time: 0.833(ms). Execute id is 25.

主库上执行的是插入语句执行时间是2020-06-10 22:18:13.000000

SQL> select * from v$sessions;

LINEID     SESS_ID              SESS_SEQ    SQL_TEXT                  STATE  N_STMT      N_USED_STMT SEQ_NO      CURR_SCH USER_NAME TRX_ID               CREATE_TIME                 CLNT_TYPE TIME_ZONE CHK_CONS CHK_IDENT RDONLY INS_NULL COMPILE_FLAG AUTO_CMT DDL_AUTOCMT RS_FOR_QRY CHK_NET ISO_LEVEL   CLNT_HOST       APPNAME CLNT_IP               OSNAME                 CONN_TYPE    VPOOLADDR            RUN_STATUS MSG_STATUS LAST_RECV_TIME              LAST_SEND_TIME              DCP_FLAG THRD_ID     CONNECTED   PORT_TYPE   SRC_SITE    MAL_ID               CONCURRENT_FLAG
---------- -------------------- ----------- ------------------------- ------ ----------- ----------- ----------- -------- --------- -------------------- --------------------------- --------- --------- -------- --------- ------ -------- ------------ -------- ----------- ---------- ------- ----------- --------------- ------- --------------------- ---------------------- ------------ -------------------- ---------- ---------- --------------------------- --------------------------- -------- ----------- ----------- ----------- ----------- -------------------- ---------------
1          139880043352296      1           select * from v$sessions; ACTIVE 64          1           42          SYSDBA   SYSDBA    1124                 2020-06-03 16:41:00.000000  SQL3      +08:00    N        N         N      Y        N            N        Y           N          N       1           18c1            disql   ::1                   Linux                   HOMOGENEOUS 139878427790072      RUNNING    RECIEVE    2020-06-10 22:18:50.000000  2020-06-10 22:15:59.000000  N        17205       1           0           65535       NULL                 0
2          139878562075400      12          insert into t2 values(2)  IDLE   64          2           4           SYSDBA   SYSDBA    0                    2020-06-10 22:18:13.000000  JDBC      +08:00    N        N         N      Y        N            Y        Y           N          N       1           WIN-ROUOJ6ERFO3         ::ffff:10.13.13.242 Windows Server 2008 R2  HOMOGENEOUS 139878562007800      IDLE       SEND       2020-06-10 22:18:26.000000  2020-06-10 22:18:26.000000  N        21802       1           0           65535       NULL                 0

used time: 1.602(ms). Execute id is 41.

DM7搭建数据守护

数据守护(DG)
环境说明
下列机器事先都安装了DM软件,安装路径为/dm7,执行程序保存在/dm7/bin目录中,数据存放路径为/dm7/data

机器名     IP地址                  初始状态                         操作系统    
19c1     10.13.13.141(对外)        主库 dm1                         redhat 6.7
         10.10.10.141(mal对内)



19c2     10.13.13.142(对外)
         10.10.10.142(mal对内)     备库 dm2                         redhat 6.7

ora19c   10.13.13.140              确认监视器                       redhat 6.7



实例名       port_num             dw_port    mal_host              mal_port        mal_dw_port
dm1          5236                 5239       10.10.10.141          5237            5238
dm2          5236                 5239       10.10.10.142          5237            5238

数据准备
在主库机器上初始化数据库到目录/dm7/data:

[dmdba@19c1 bin]$ ./dminit path=/dm7/data db_name=DAMENG instance_name=DM1 port_num=5236 page_size=8 charset=0
initdb V7.1.6.46-Build(2018.02.08-89107)ENT
db version: 0x7000a
file dm.key not found, use default license!
License will expire in 14 day(s) on 2020-06-17

log file path: /dm7/data/DAMENG/DAMENG01.log

log file path: /dm7/data/DAMENG/DAMENG02.log

write to dir [/dm7/data/DAMENG].
create dm database success. 2020-06-03 02:55:43

注册服务用于启动数据库

[root@19c1 root]# ./dm_service_installer.sh -i /dm7/data/DAMENG/dm.ini -p DM1 -t dmserver
ln -s '/usr/lib/systemd/system/DmServiceDM1.service' '/etc/systemd/system/multi-user.target.wants/DmServiceDM1.service'
Finished to create the service (DmServiceDM1)

正常启动数据库并正常关闭

[root@19c1 root]# service DmServiceDM1 start
Redirecting to /bin/systemctl start DmServiceDM1.service
[root@19c1 root]# ps -ef | grep dmserver
dmdba 21312 1 14 02:59 ? 00:00:03 /dm7/bin/dmserver /dm7/data/DAMENG/dm.ini -noconsole
root 21536 18668 0 03:00 pts/2 00:00:00 grep --color=auto dmserver
[dmdba@19c1 bin]$ ./disql SYSDBA/SYSDBA

Server[LOCALHOST:5236]:mode is normal, state is open
login used time: 5.903(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT
Connected to: DM 7.1.6.46
SQL> exit

[root@19c1 root]# service DmServiceDM1 stop
Redirecting to /bin/systemctl stop DmServiceDM1.service

ARCH_INI=1(打开归档)
MAL_INI=1(打开MAL配置)
DW_PORT=5239 (DW端口)

配置dmmal.ini文件
配置MAL系统,各主备库的dmmal.ini配置必须完全一致,MAL_HOST使用内部网络IP,MAL_PORT与dm.ini中PORT_NUM使用不同的端口值,MAL_DW_PORT是各实例对应的守护进程之间,以及守护进程和监视器之间的通信端口,配置如下:

[dmdba@19c1 DAMENG]$ vi dmmal.ini
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5

[MAL_INST1]
MAL_INST_NAME = DM1
MAL_HOST = 10.10.10.141
MAL_PORT = 5237
MAL_INST_HOST = 10.13.13.141
MAL_INST_PORT = 5236
MAL_DW_PORT = 5238

[MAL_INST2]
MAL_INST_NAME = DM2
MAL_HOST = 10.10.10.142
MAL_PORT = 5237
MAL_INST_HOST = 10.13.13.142
MAL_INST_PORT = 5236
MAL_DW_PORT = 5238

配置dmarch.ini
修改dmarch.ini,配置本地归档和实时归档。除了本地归档外,其他归档配置项中的ARCH_DEST表示实例是Primary模式时,需要同步归档数据的目标实例名。当前实例DM1是主库,需要向DM2(实时备库)同步数据,因此实时归档的ARCH_DEST配置为DM2。

[dmdba@19c1 DAMENG]$ vi dmarch.ini
[ARCHIVE_REALTIME]
ARCH_TYPE = REALTIME
ARCH_DEST = DM2
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm7/data/DAMENG/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0

配置dmwatcher.ini
修改dmwatcher.ini配置守护进程,配置为全局守护类型,使用自动切换模式。

[dmdba@19c1 DAMENG]$ vi dmwatcher.ini
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = AUTO
DW_ERROR_TIME = 10
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 10
INST_OGUID = 453331
INST_INI = /dm7/data/DAMENG/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dm7/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0

配置dmwatcher.ctl
同一个守护进程组,必须使用同一份dmwatcher.ctl。因此,只需要使用dmctlcvt工具生成一份dmwatcher.ctl文件,然后分别拷贝到各个数据库目录下即可。在配置完成dmwatcher.ini后,使用dmctlcvt工具生成dmwatcher.ctl:(特别注意,DEST目录为DAMENG的上一级目录,否则不生成控制文件)

[dmdba@19c1 bin]$ ./dmctlcvt TYPE=3 SRC=/dm7/data/DAMENG/dmwatcher.ini DEST=/dm7/data
DMCTLCVT V7.1.6.46-Build(2018.02.08-89107)ENT
convert txt to ctl success!

会在/dm7/data目录中生成一个GRP1目录,在GRP1目录中生成了dmwatcher.ctl控制文件

[root@19c1 data]# ls -lrt
total 4
drwxr-xr-x 8 dmdba dinstall 4096 Jun 3 03:44 DAMENG
drwxr-xr-x 2 dmdba dinstall 26 Jun 3 03:53 GRP1

[root@19c1 data]# cd GRP1
[root@19c1 GRP1]# ls -lrt
total 4
-rw-r--r-- 1 dmdba dinstall 512 Jun 3 03:53 dmwatcher.ctl

拷贝生成的dmwatcher.ctl文件到数据文件目录/dm7/data/DAMENG。

将主库相关文件传输到备机:

[dmdba@19c1 dm7]$ scp -r data/ dmdba@10.13.13.142:/dm7/
The authenticity of host '10.13.13.142 (10.13.13.142)' can't be established.
ECDSA key fingerprint is 7f:1f:9a:0f:8b:d1:e0:17:32:08:12:73:d8:1d:9c:da.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.13.13.142' (ECDSA) to the list of known hosts.
dmdba@10.13.13.142's password:
dminit20200603025444.log 100% 783 0.8KB/s 00:00
sqllog.ini 100% 479 0.5KB/s 00:00
dm.ctl 100% 5120 5.0KB/s 00:00
DAMENG01.log 100% 256MB 256.0MB/s 00:01
DAMENG02.log 100% 256MB 128.0MB/s 00:02
dm_20200603025543_320471.ctl 100% 5120 5.0KB/s 00:00
dm_20200603030017_373808.ctl 100% 5120 5.0KB/s 00:00
SYSTEM.DBF 100% 21MB 21.0MB/s 00:01
dm_service.prikey 100% 633 0.6KB/s 00:00
MAIN.DBF 100% 128MB 128.0MB/s 00:01
ROLL.DBF 100% 128MB 128.0MB/s 00:01
dminst.sys 100% 220 0.2KB/s 00:00
TEMP.DBF 100% 10MB 10.0MB/s 00:00
rep_conflict.log 100% 12 0.0KB/s 00:00
dm.ini 100% 40KB 39.8KB/s 00:00
dmmal.ini 100% 558 0.5KB/s 00:00
dmarch.ini 100% 367 0.4KB/s 00:00
dmwatcher.ini 100% 615 0.6KB/s 00:00
dmwatcher.ctl 100% 512 0.5KB/s 00:00

备机修改相关配置

修改dm.ini
INSTANCE_NAME = DM2

修改dmarch.ini

ARCH_DEST = DM1

其中dmwatcher.ini,dmmal.ini,dmwatcher.ctl和主库一致

二:启动到mount状态设置oguid
主库

[dmdba@19c1 bin]$ ./dmserver /dm7/data/DAMENG/dm.ini mount
file dm.key not found, use default license!
version info: develop
Use normal os_malloc instead of HugeTLB
Use normal os_malloc instead of HugeTLB
DM Database Server x64 V7.1.6.46-Build(2018.02.08-89107)ENT startup...
License will expire in 14 day(s) on 2020-06-17
ckpt lsn: 32981
SYSTEM IS READY.

[dmdba@19c1 bin]$ ./disql SYSDBA/SYSDBA

Server[LOCALHOST:5236]:mode is normal, state is mount
login used time: 6.020(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT
Connected to: DM 7.1.6.46
SQL> sp_set_oguid(453331);
DMSQL executed successfully
used time: 38.995(ms). Execute id is 1.
SQL> exit

备库

[dmdba@19c2 bin]$ ./dmserver /dm7/data/DAMENG/dm.ini mount
file dm.key not found, use default license!
version info: develop
Use normal os_malloc instead of HugeTLB
Use normal os_malloc instead of HugeTLB
DM Database Server x64 V7.1.6.46-Build(2018.02.08-89107)ENT startup...
License will expire in 14 day(s) on 2020-06-17
ckpt lsn: 32981
SYSTEM IS READY.

[dmdba@19c2 bin]$ ./disql SYSDBA/SYSDBA

Server[LOCALHOST:5236]:mode is normal, state is mount
login used time: 6.005(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT
Connected to: DM 7.1.6.46
SQL> sp_set_oguid(453331);
DMSQL executed successfully
used time: 46.333(ms). Execute id is 1.

注册服务用于启动数据库

[root@19c2 root]# ./dm_service_installer.sh -i /dm7/data/DAMENG/dm.ini -p DM2 -t dmserver
ln -s '/usr/lib/systemd/system/DmServiceDM2.service' '/etc/systemd/system/multi-user.target.wants/DmServiceDM2.service'
Finished to create the service (DmServiceDM2)

三:打开数据库
主库以primary打开

SQL> alter database primary;
executed successfully
used time: 37.838(ms). Execute id is 0.

备库以standby 打开

SQL> alter database standby;
executed successfully
used time: 39.682(ms). Execute id is 0.

四:启动守护进程
启动各个主备库上的守护进程:
主库

[dmdba@19c1 bin]$ ./dmwatcher /dm7/data/DAMENG/dmwatcher.ini
DMWATCHER[2.1] V7.1.6.46-Build(2018.02.08-89107)ENT
DMWATCHER[2.1] IS READY

备库

[dmdba@19c2 bin]$ ./dmwatcher /dm7/data/DAMENG/dmwatcher.ini
DMWATCHER[2.1] V7.1.6.46-Build(2018.02.08-89107)ENT
DMWATCHER[2.1] IS READY

五:查看file_lsn与cur_lsn主备库是否一致
主库
SQL> select file_LSN, cur_LSN from v$rlog;

LINEID FILE_LSN CUR_LSN
---------- -------------------- --------------------
1 34412 34412

used time: 1.203(ms). Execute id is 6.

备库

SQL> select file_LSN, cur_LSN from v$rlog;

LINEID FILE_LSN CUR_LSN
---------- -------------------- --------------------
1 34412 34412

used time: 1.228(ms). Execute id is 3.

测试数据同步
主库:

SQL> create table t1(id int);
executed successfully
used time: 18.410(ms). Execute id is 7.
SQL> insert into t1 values(1);
affect rows 1

used time: 0.890(ms). Execute id is 8.
SQL> commit;
executed successfully
used time: 6.026(ms). Execute id is 9.
SQL> select file_LSN, cur_LSN from v$rlog;

LINEID FILE_LSN CUR_LSN
---------- -------------------- --------------------
1 34443 34443

used time: 0.340(ms). Execute id is 10.

备库:

SQL> select file_LSN, cur_LSN from v$rlog;

LINEID FILE_LSN CUR_LSN
---------- -------------------- --------------------
1 34443 34443

used time: 0.363(ms). Execute id is 4.
SQL> select * from t1;

LINEID ID
---------- -----------
1 1

used time: 1.213(ms). Execute id is 5.

六:配置监视器(基本要求,安装dm7的软件)
由于主库和实时备库的守护进程配置为自动切换模式,因此这里选择配置确认监视器。和普通监视器相比,确认监视器除了相同的命令支持外,在主库发生故障时,能够自动通知实时备库接管为新的主库,具有自动故障处理的功能。
修改dmmonitor.ini配置确认监视器,其中MON_DW_IP中的IP和PORT和dmmal.ini中的MAL_HOST和MAL_DW_PORT配置项保持一致。

[dmdba@ora19c data]$ vi dmmonitor.ini
MON_DW_CONFIRM = 1
MON_LOG_PATH = /dm7/data/log
MON_LOG_INTERVAL = 60
MON_LOG_FILE_SIZE = 32
MON_LOG_SPACE_LIMIT = 0
[GRP1]
MON_INST_OGUID = 453331
MON_DW_IP = 10.10.10.141:5238
MON_DW_IP = 10.10.10.142:5238

启动监视器:

[dmdba@ora19c bin]$ ./dmmonitor /dm7/data/dmmonitor.ini
[monitor] 2020-06-03 09:18:20: DMMONITOR[2.1] V7.1.6.46-Build(2018.02.08-89107)ENT
[monitor] 2020-06-03 09:18:20: DMMONITOR[2.1] IS READY.

[monitor] 2020-06-03 09:18:20: Received message from(DM1)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN SSLSN SLSN
2020-06-03 04:35:58 OPEN OK DM1 OPEN PRIMARY VALID 2 34443 34443 34443 34443

[monitor] 2020-06-03 09:18:20: Received message from(DM2)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN SSLSN SLSN
2020-06-03 04:35:58 OPEN OK DM2 OPEN STANDBY VALID 2 34443 34443 34443 34443

达梦dmfldr加载大字段

dmfldr加载大字段
1.外部数据

[dmdba@shard1 ~]$ vi t1.txt
1,aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

2.创建控制文件

[dmdba@shard1 ~]$ vi t1.ctl
load data
infile '/home/dmdba/t1.txt'
into table test_lob
fields ','

3.创建表

SQL> create table test_lob(id int,name clob);
executed successfully
used time: 51.594(ms). Execute id is 7552.

[dmdba@shard1 ~]$ dmfldr sysdba/xxzx7817600 control=\'\/home\/dmdba\/t1.ctl\' 
dmfldr V7.1.6.46-Build(2018.02.08-89107)ENT 
dmfldr: 

Copyright (c) 2011, 2015, Dameng.  All rights reserved.

Control file:

Loaded rows:All

Rows per commit to server: 50000

 Rows to skip: 0

 Errors count allowed: 100

 Whether to load direct: Yes

 Whether insert self-increase col: No

 Whether data has sort by gather index: No

 Character sets:GBK



Data file counts: 1
/home/dmdba/t1.txt

Error file :fldr.bad

Dest table :TEST_LOB

Column Name                                                                                                                      Packed data type     End
ID                                                                                                                               CHARACTER            ,
NAME                                                                                                                             CHARACTER            ,


row buffer number is 4
task thread number is 4
not set lob dir
0 rows committed.

Dest table :TEST_LOB
0 Rows loaded success
Due to data format error, 0 rows abandon
Due to data error, 1 rows not loaded

Skip logic record counts: 0
Read logic record counts: 1
Refuse logic record counts: 1

The total time used: 38.056(ms)

上面显示拒绝加载了一行记录也就是说数据没有加载成功。
在加载大字段时需要指定direct=false选项

[dmdba@shard1 ~]$ dmfldr sysdba/xxzx7817600 control=\'\/home\/dmdba\/t1.ctl\' direct=false
dmfldr V7.1.6.46-Build(2018.02.08-89107)ENT 
dmfldr: 

Copyright (c) 2011, 2015, Dameng.  All rights reserved.

Control file:

Loaded rows:All

Rows per commit to server: 50000

 Rows to skip: 0

 Errors count allowed: 100

 Whether to load direct: Yes

 Whether insert self-increase col: No

 Whether data has sort by gather index: No

 Character sets:GBK



Data file counts: 1
/home/dmdba/t1.txt

Error file :fldr.bad

Dest table :TEST_LOB

Column Name                                                                                                                      Packed data type     End
ID                                                                                                                               CHARACTER            ,
NAME                                                                                                                             CHARACTER            ,


1 rows processed.

Dest table :TEST_LOB
1 Rows loaded success
Due to data format error, 0 rows abandon
Due to data error, 0 rows not loaded

Skip logic record counts: 0
Read logic record counts: 1
Refuse logic record counts: 0

The total time used: 229.173(ms)

达梦dmfldr数据快速加载

dmfldr数据快速加载
1.外部数据(文本)

[dmdba@shard1 ~]$ cat prod.dat
ProdID,ProdName,Code,ReOrder,Cost,Price,PriSrc,SecSrc,ObsoletedDate
1001,DaMeng Database,Server,25,45.45,14.32,Redwood Shores,Reston,
1002,DaMeng Database,Server,15,55.78,50.10,Redwood Shores,Reston,2014-02-14

2.控制文件

[dmdba@shard1 ~]$ cat prod.ctl
load data
infile '/home/dmdba/prod.dat'
into table test_load
fields ','

3.数据库规划表

create table test_load
(prodid int,
prodname varchar(40),
code varchar(30),
reorder int,
cost int,
price int,
prisrc varchar(30),
secsrc varchar(30),
obsoleteddate date
);

4.加载数据

[dmdba@shard1 ~]$ dmfldr sysdba/xxzx7817600 control='/home/dmdba/prod.ctl'
dmfldr V7.1.6.46-Build(2018.02.08-89107)ENT 
error occured during getting control input.Make sure the input is embraced by charactor '

控制文件输入参数出错,请确认以符号’作为首尾字符

[dmdba@shard1 ~]$ dmfldr sysdba/xxzx7817600 control=\'/home/dmdba/prod.ctl\' skip=1
dmfldr V7.1.6.46-Build(2018.02.08-89107)ENT 
dmfldr: 

Copyright (c) 2011, 2015, Dameng.  All rights reserved.

Control file:

Loaded rows:All

Rows per commit to server: 50000

 Rows to skip: 1

 Errors count allowed: 100

 Whether to load direct: Yes

 Whether insert self-increase col: No

 Whether data has sort by gather index: No

 Character sets:GBK



Data file counts: 1
/home/dmdba/prod.dat

Error file :fldr.bad

Dest table :TEST_LOAD

Column Name                                                                                                                      Packed data type     End
PRODID                                                                                                                           CHARACTER            ,
PRODNAME                                                                                                                         CHARACTER            ,
CODE                                                                                                                             CHARACTER            ,
REORDER                                                                                                                          CHARACTER            ,
COST                                                                                                                             CHARACTER            ,
PRICE                                                                                                                            CHARACTER            ,
PRISRC                                                                                                                           CHARACTER            ,
SECSRC                                                                                                                           CHARACTER            ,
OBSOLETEDDATE                                                                                                                    CHARACTER            ,


row buffer number is 4
task thread number is 4
2 rows committed.

Dest table :TEST_LOAD
2 Rows loaded success
Due to data format error, 0 rows abandon
Due to data error, 0 rows not loaded

Skip logic record counts: 1
Read logic record counts: 2
Refuse logic record counts: 0

The total time used: 26.677(ms)

或者

[dmdba@shard1 ~]$ dmfldr sysdba/xxzx7817600 control=\'\/home\/dmdba\/prod.ctl\' skip=1
dmfldr V7.1.6.46-Build(2018.02.08-89107)ENT 
dmfldr: 

Copyright (c) 2011, 2015, Dameng.  All rights reserved.

Control file:

Loaded rows:All

Rows per commit to server: 50000

 Rows to skip: 1

 Errors count allowed: 100

 Whether to load direct: Yes

 Whether insert self-increase col: No

 Whether data has sort by gather index: No

 Character sets:GBK



Data file counts: 1
/home/dmdba/prod.dat

Error file :fldr.bad

Dest table :TEST_LOAD

Column Name                                                                                                                      Packed data type     End
PRODID                                                                                                                           CHARACTER            ,
PRODNAME                                                                                                                         CHARACTER            ,
CODE                                                                                                                             CHARACTER            ,
REORDER                                                                                                                          CHARACTER            ,
COST                                                                                                                             CHARACTER            ,
PRICE                                                                                                                            CHARACTER            ,
PRISRC                                                                                                                           CHARACTER            ,
SECSRC                                                                                                                           CHARACTER            ,
OBSOLETEDDATE                                                                                                                    CHARACTER            ,


row buffer number is 4
task thread number is 4
2 rows committed.

Dest table :TEST_LOAD
2 Rows loaded success
Due to data format error, 0 rows abandon
Due to data error, 0 rows not loaded

Skip logic record counts: 1
Read logic record counts: 2
Refuse logic record counts: 0

The total time used: 30.680(ms)

DM7审计之语句序列审计

审计机制是DM数据库管理系统安全管理的重要组成部分之一。DM数据库除了提供数据安全保护措施外,还提供对日常事件的事后审计监督。DM具有一个灵活的审计子系统,可以通过它来记录系统级事件、个别用户的行为以及对数据库对象的访问。通过考察、跟踪审计信息,数据库审计员可以查看用户访问的形式以及曾试图对该系统进行的操作,从而采取积极、有效的应对措施。

审计开关
在DM系统中,专门为审计设置了开关,要使用审计功能首先要打开审计开关。审计开关由DM的INI参数ENABLE_AUDIT控制,有三种取值:
0:关闭审计
1:打开普通审计
2:打开普通审计和实时审计
在普通版本中,ENABLE_AUDIT的缺省值为0;在安全版本中,ENABLE_AUDIT的缺省值为2。

审计开关必须由具有DBA权限的管理员进行设置。

系统管理员可通过查询V$PARAMETER动态视图查询ENABLE_AUDIT的当前值。

SQL> select * from v$parameter where name='ENABLE_AUDIT';

LINEID     ID          NAME         TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- ----------- ------------ ---- ----- --------- ---------- ---------------------------------------------------------------------------------------
1          385         ENABLE_AUDIT SYS  0     0         0          Flag For Allowing Audit, 0: no audit 1: normal audit  2:normal audit and realtime audit

used time: 8.170(ms). Execute id is 24948.

SQL> sp_set_para_value(1,'ENABLE_AUDIT',2);
DMSQL executed successfully
used time: 80.901(ms). Execute id is 24968.
SQL> select * from v$parameter where name='ENABLE_AUDIT';

LINEID     ID          NAME         TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- ----------- ------------ ---- ----- --------- ---------- ---------------------------------------------------------------------------------------
1          385         ENABLE_AUDIT SYS  2     2         2          Flag For Allowing Audit, 0: no audit 1: normal audit  2:normal audit and realtime audit

used time: 6.829(ms). Execute id is 24969.
SQL> select * from v$dm_ini where para_name='ENABLE_AUDIT';

LINEID     PARA_NAME    PARA_VALUE MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION                                                                             PARA_TYPE
---------- ------------ ---------- --------- --------- ------- ---------- ---------- --------------------------------------------------------------------------------------- ---------
1          ENABLE_AUDIT 2          0         2         N       2          2          Flag For Allowing Audit, 0: no audit 1: normal audit  2:normal audit and realtime audit SYS

used time: 7.090(ms). Execute id is 24970.
SQL>

也可以通过使用客户端工具Console或调用系统过程SP_SET_PARA_VALUE重新设置ENABLE_AUDIT的值,ENABLE_AUDIT为动态INI参数。

审计的设置与取消
数据库审计员指定被审计对象的活动称为审计设置,只有具有AUDIT DATABASE权限的审计员才能进行审计设置。DM提供审计设置系统过程来实现这种设置,被审计的对象可以是某类操作,也可以是某些用户在数据库中的全部行踪。只有预先设置的操作和用户才能被DM系统自动进行审计。

DM允许在三个级别上进行审计设置
系统级:系统的启动与关闭,此级别的审计无法也无需由用户进行设置,只要审计开关打开就会自动生成对应审计记录
语句级:导致影响特定类型数据库对象的特殊SQL或语句组的审计。如AUDIT TABLE 将审计CREATE TABLE、ALTER TABLE和DROP TABLE等语句
对象级:审计作用在特殊对象上的语句。如test表上的INSERT语句

审计设置存放于DM字典表SYSAUDIT中,进行一次审计设置就在SYSAUDIT中增加一条对应的记录,取消审计则删除SYSAUDIT中相应的记录。

语句序列审计
DM还提供了语句序列审计功能,作为语句级审计和对象级审计的补充。语句序列审计需要审计员预先建立一个审计规则,包含N条SQL语句(SQL1,SQL2……),如果某个会话依次执行了这些SQL语句,就会触发审计。

建立语句序列审计规则的过程包括下面三个系统过程。

VOID
SP_AUDIT_SQLSEQ_START(
NAME VARCHAR (128)
)

VOID
SP_AUDIT_SQLSEQ_ADD(
NAME VARCHAR (128),
SQL VARCHAR (8188)
)

VOID
SP_AUDIT_SQLSEQ_END(
NAME VARCHAR (128)
)

参数说明:
NAME 语句序列审计规则名
SQL 需要审计的语句序列中的SQL语句
使用说明:
建立语句序列审计规则需要先调用SP_AUDIT_SQLSEQ_START,之后调用若干次SP_AUDIT_SQLSEQ_ADD,每次加入一条SQL语句,审计规则中的SQL语句顺序根据加入SQL语句的顺序确定,最后调用SP_AUDIT_SQLSEQ_END完成规则的建立。

例如,建立一个语句序列审计规则audit_sql1。

SQL> sp_audit_sqlseq_start('audit_sql1');
DMSQL executed successfully
used time: 0.901(ms). Execute id is 25115.
SQL> sp_audit_sqlseq_add('audit_sql1','select c2 from t1;');
DMSQL executed successfully
used time: 0.698(ms). Execute id is 25117.
SQL> sp_audit_sqlseq_add('audit_sql1','select c1 from t2;');
DMSQL executed successfully
used time: 0.709(ms). Execute id is 25118.
SQL> sp_audit_sqlseq_add('audit_sql1','select * from t3;');
DMSQL executed successfully
used time: 0.814(ms). Execute id is 25119.
SQL> sp_audit_sqlseq_end('audit_sql1');
DMSQL executed successfully
used time: 32.279(ms). Execute id is 25120.

在别一个会话执行语句

SQL> select * from t1;
DMSQL executed successfully
used time: 0.814(ms). Execute id is 5119.

SQL> select * from t2;
DMSQL executed successfully
used time: 0.814(ms). Execute id is 5120.

SQL> select * from t3;
DMSQL executed successfully
used time: 0.814(ms). Execute id is 5121.

查询审计记录

SQL> select * from v$auditrecords;

LINEID     USERID      USERNAME ROLEID      ROLENAME IP               SCHID       SCHNAME OBJID       OBJNAME OPERATION    SUCC_FLAG SQL_TEXT                                                                                             DESCRIBTION                 OPTIME                      MAC                 
---------- ----------- -------- ----------- -------- ---------------- ----------- ------- ----------- ------- ------------ --------- ---------------------------------------------------------------------------------------------------- --------------------------- --------------------------  -------------------
1          50331649    SYSDBA   67108864    DBA      ::ffff:127.0.0.1 -1                  -1                  DROP TABLE   Y         drop table cs purge;                                                                                                             2020-05-27 19:28:06.000000  00:00:00:00:00:00

2          50331649    SYSDBA   67108864    DBA      ::ffff:127.0.0.1 -1                  -1                  CREATE TABLE Y         create table cs(id int,name varchar(20));                                                                                        2020-05-27 19:28:19.000000  00:00:00:00:00:00

3          50331649    SYSDBA   67108864    DBA      ::ffff:127.0.0.1 -1                  -1                  CREATE USER  Y         create user cs2 identified by ******;                                                                                            2020-05-27 19:32:18.000000  00:00:00:00:00:00

4          50331750    JY       67108864    DBA      ::ffff:127.0.0.1 150995951   JY      1454        T1      UPDATE       Y         update t1 set c2='WY' where c1=2;                                                                                                2020-05-27 19:36:39.000000  00:00:00:00:00:00

5          50331750    JY       67108864    DBA      ::ffff:127.0.0.1 150995951   JY      1454        T1      DELETE       Y         delete from t1;                                                                                                                  2020-05-27 19:37:08.000000  00:00:00:00:00:00

6          50331649    SYSDBA   67108864    DBA      ::ffff:127.0.0.1 150995945   PERSON  1254        ADDRESS INSERT       Y         insert into person.address values('常德武陵区武陵大道938号',null,'德武陵区','415700',10);                                        2020-05-27 19:58:29.000000  00:00:00:00:00:00

7          50331649    SYSDBA   -1                   ::ffff:127.0.0.1 -1                  -1                  SQL SEQ      Y         select name from t1; select id from t2; select * from t3;                                             audit_sql1                 2020-05-27 20:14:44.000000  00:00:00:00:00:00


7 rows got

used time: 1.013(ms). Execute id is 25133.

可使用下面的系统过程删除指定的语句序列审计规则。

VOID
SP_AUDIT_SQLSEQ_DEL(
NAME VARCHAR (128)
)

参数说明:
NAME 语句序列审计规则名
例如,删除语句序列审计规则AUDIT_SQL1。

SQL> sp_audit_sqlseq_del('audit_sql1');
DMSQL executed successfully
used time: 26.854(ms). Execute id is 25137.
Proudly powered by WordPress | Indrajeet by Sus Hill.