一、表分区:分为垂直分区和水平分区;水平分区按分类分为:范围、哈希、列表、组合分区;
范围分区:
- 创建表空间:create tablespace TEST datafile
‘/dm8/data/DAMNEG/TEST_01.DBF’ SIZE 256 autoextend on next 1 maxsize 10240
- 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)
- 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)
- Create table TEST.T1(ID INT, NAME VARCHAR(20) )
PARTITION BY HASH(ID) PARTITION 20;
- 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