DM_DCP学习

一、表分区：分为垂直分区和水平分区；水平分区按分类分为：范围、哈希、列表、组合分区；

范围分区：

1. 创建表空间：create tablespace TEST datafile

‘/dm8/data/DAMNEG/TEST_01.DBF’ SIZE 256 autoextend on next 1 maxsize 10240

1. Create table TEST.T1(iD INT, NAME VARCHAR(20) )

PARTITION BY RANGE (ID)

(

PARATITION P1 VALUES LESS THAN (100),

    PARATITION P2 VALUES LESS THAN (200),

    PARATITION P3 VALUES LESS THAN (300)

);

ALERT TABLE TEST.T1 ADD PARTITION PN VALUES LESS THAN (MAXVALUE)

1. Create table TEST.T1(ID INT, NAME VARCHAR(20) )

PARTITION BY LIST(name)

(

PARATITION P1 VALUES (“1”,”2”),

    PARATITION P2 VALUES (“4”,”3”),

    PARATITION P3 VALUES (“6”,”5”)

);

ALERT TABLE TEST.T1 ADD PARTITION PN VALUES (DEFAULT)

1. Create table TEST.T1(ID INT, NAME VARCHAR(20) )

PARTITION BY HASH(ID) PARTITION 20;

1. Create table TEST.T1(ID INT, NAME VARCHAR(20) )

PARTITION BY HASH(ID)

( PARTITION P1,

PARTITION P2,

PARTITION P3

);

二、创建外部表：

Create external table <表名><表结构定义> from <控制文件路径> ｜<

数据文件路径><参数>；

例子：

create external table test.ext(id int, name varchar(20)) from '/dm8/ext/a.ctl';

• DM8 序列、同义词、物化视图

序列：在内存预先申请的一段地址空间，类似于取号排队。

管理工具新建序列

同义词:公共同义词、普通同义词；

物化视图：

数据要单独存储，占用磁用空间，规划表空间。

物化视图的数据来自于基表，基表发生变化，物化视图可以根据更新方式来进行数据更新。

更新方式：手动（默认的方式）：demand、自动更新：commit

创建一个手动更新的物化视图

create materialized view TEST.MV1 AS SELECT * FROM TEST.T1;

refresh materialized view test.mv1;

自动更新：

创建物化视图日志

create materialized view log on test.t1;

创建完全更新物化视图

create materialized view test.mv3 refresh complete on commit as

select * from test.t1;

创建快速更新物化视图

create materialized view test.mv4 refresh fast on commit as select *

from test.t1;

• 索引

唯一索引、函数索引、复合索引、位图索引、索引的维护、分区索引、全文索引；

管理工具新建索引

五、DEM 配置主要步骤：

1、配置 JAVA 环境

2、修改 dm.ini 参数

3、运行脚本 dem_init.sql

4、配置中间件 tomcat

六 角色权限

 

• 审计

审计用户：SP_SET_ENABLE_AUDIT(2)

关闭审计：sp_set_enable_audit(0);

Sp_audit_stmt(‘UPDATE TABLE’,’TEST’,’ALL’)；

• 快速数据加载

Bin:   ./dmfldr sysdba/dameng123 control='/dm8/fldr/fldrtest.ctl'

options(

skip=1

)

LOAD DATA

INFILE '/dm8/fldr/test2.txt' INTO TABLE TEST.T50

FIELDS ','

• 优化

数据缓冲区：normal(buffer),keep,fast ,recycle

MULTI_PAGE_GET_NUM=1 一条“自由链”， “LRU”链 ”脏“链

(2)日志缓冲区：RLOG_BUF_SIZE page 2N 次方

(3)字典缓冲区：DICT_BUF_SIZE

(4)Sql 缓冲区: cache_pool_size

• awr报告

sp_init_awr_sys(1);

call dbms_workload_repository.awr_set_interval(10);

call dbms_workload_repository.create_snapshot();

> select * from sys.wrm$_snapshot;

call sys.awr_report_html(1,4,'/dm8','awr1.html');

• 数据守护

主备机初始化数据库:

./dminit path=/dm8/data instance_name=DM01

./dminit path=/dm8/data instance_name=DM02

./DmServiceDM01 stop

./dmrman

Mkdir /dm8/backup

BACKUP DATABASE '/dm8/data/DMDW/dm.ini' FULL TO BACKUP_FILE1 BACKUPSET '/dm8/backup/BACKUP_FILE_01'

备机：

./DmServiceDM02 stop

主机：

Cd /dm8/backup/BACKUP_FILE_01

Scp *.* 192.168.1.20:/dm8/backup

备机：恢复

Cd /dm8/bin

./dmrman

RESTORE DATABASE '/dm8/data/DMDW/dm.ini' FROM BACKUPSET '/dm8/backup'

RECOVER DATABASE '/dm8/data/DMDW/dm.ini' FROM BACKUPSET '/dm8/backup'

RECOVER DATABASE '/dm8/data/DMDW/dm.ini' UPDATE DB_MAGIC

主机：数据库文件夹下：

注册数据守护服务：

Vi dm.Ini

INSTANCE_NAME = GRP1_RT_01

PORT_NUM = 5246

DW_INACTIVE_INTERVAL = 60

ALTER_MODE_STATUS = 0

ENABLE_OFFLINE_TS = 2

MAL_INI = 1

ARCH_INI = 1

RLOG_SEND_APPLY_MON = 64

dmmal.ini

MAL_CHECK_INTERVAL = 5

MAL_CONN_FAIL_INTERVAL = 5

[MAL_INST1]

MAL_INST_NAME = GRP1_RT_01

MAL_HOST = 192.168.1.30

MAL_PORT = 55101

MAL_INST_HOST = 192.168.1.30

MAL_INST_PORT = 5246

MAL_DW_PORT = 65101

MAL_INST_DW_PORT = 45101

[MAL_INST2]

MAL_INST_NAME = GRP1_RT_03

MAL_HOST = 192.168.1.20

MAL_PORT = 55121

MAL_INST_HOST = 192.168.1.20

MAL_INST_PORT = 5246

MAL_DW_PORT = 65121

MAL_INST_DW_PORT = 45121

dmarch.ini

[ARCHIVE_REALTIME]

ARCH_TYPE = REALTIME

ARCH_DEST = GRP1_RT_03

[ARCHIVE_LOCAL1]

ARCH_TYPE = LOCAL

ARCH_DEST = /dm8/arch

ARCH_FILE_SIZE = 128

ARCH_SPACE_LIMIT = 0

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 = /dm8/data/DMDW/dm.ini  

INST_AUTO_RESTART = 1

INST_STARTUP_CMD = /dm8/bin/dmserver

RLOG_SEND_THRESHOLD = 0  

RLOG_APPLY_THRESHOLD = 0

启动主库

./dmserver /dm8/data/DMDW/dm.ini  mount

SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);

SQL>sp_set_oguid(453331);

SQL>alter database primary;

SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);

备机：

dm.ini

INSTANCE_NAME = GRP1_RT_03

PORT_NUM = 5246

DW_INACTIVE_INTERVAL = 60

ALTER_MODE_STATUS = 0  

ENABLE_OFFLINE_TS = 2

MAL_INI = 1

ARCH_INI = 1  

RLOG_SEND_APPLY_MON = 64

dmmal.ini

MAL_CHECK_INTERVAL = 5

MAL_CONN_FAIL_INTERVAL = 5  

[MAL_INST1]

MAL_INST_NAME = GRP1_RT_01  

MAL_HOST = 192.168.1.30

MAL_PORT = 55101

MAL_INST_HOST = 192.168.1.30

MAL_INST_PORT = 5246

MAL_DW_PORT = 65101  

MAL_INST_DW_PORT = 45101

[MAL_INST2]

MAL_INST_NAME = GRP1_RT_03

MAL_HOST = 192.168.1.20

MAL_PORT = 55121

MAL_INST_HOST = 192.168.1.20

MAL_INST_PORT = 5246

MAL_DW_PORT = 65121

MAL_INST_DW_PORT = 45121

dmarch.ini

[ARCHIVE_REALTIME]

ARCH_TYPE = REALTIME

ARCH_DEST = GRP1_RT_01  

[ARCHIVE_LOCAL1]

ARCH_TYPE = LOCAL

ARCH_DEST = /dm8/arch  

ARCH_FILE_SIZE = 128  

ARCH_SPACE_LIMIT = 0

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 = /dm8/data/DMDW/dm.ini

INST_AUTO_RESTART = 1

INST_STARTUP_CMD = /dm8/bin/dmserver

RLOG_SEND_THRESHOLD = 0

RLOG_APPLY_THRESHOLD = 0

以 Mount 方式启动备库

./dmserver /dm8/data/DMDW/dm.ini mount

SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);

SQL>sp_set_oguid(453331);

SQL>alter database standby;

SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);

./dmwatcher /dm8/data/dmdw/dmwatcher.ini

主机:./dmwatcher /dm8/data/dmdw/dmwatcher.ini

监视器：

dmmonitor.ini

MON_DW_CONFIRM = 1

MON_LOG_PATH = /dm8/data/log  

MON_LOG_INTERVAL = 60

MON_LOG_FILE_SIZE = 32

MON_LOG_SPACE_LIMIT = 0

[GRP1]

MON_INST_OGUID = 453331  

MON_DW_IP = 192.168.1.30:65101

MON_DW_IP = 192.168.1.20:65121

./dmmonitor /dm8/dmmonitor.ini

show

Switchover

./dm_service_installer.sh -t dmwatcher -p DMWATCHER

-watcher_ini /dm8/data/DAMENG/dmwatcher.ini

./dm_service_installer.sh -t dmmonitor -p DMMONITOR

-monitor_ini /dm8/data/DAMENG/dmmonitor.ini