本文主要分享一下个人参加完DCP培训后，觉得需要注意的几点，重点在分区表、作业创建、集群搭建等，其中涉及到实例创建、表空间用户创建、授权、分表表创建、索引创建、快速加载、作业创建、集群搭建配置。
1、创建数据库实例
严格按题目要求创建数据库实例，推荐使用图形化界面创建，且不勾选优化
注意授权和DISPLAY变量设置

xhost +

echo $DISPLAY

su - dmdba

$ export DISPLAY=:2.0
$ cd /dm8/tool
$ ./dbca.sh
2、创建表空间及管理用户
按题目要求，目录、大小、扩展属性等：
disql SYSDBA/Dameng123
SQL> create tablespace DMTS01 datafile '/dm8/data/DMTS01.DBF' size 500 autoextend on next 2;
SQL> create tablespace DMTS02 datafile '/dm8/data/DMTS02.DBF' size 500 autoextend on next 2;
SQL> create tablespace DMTS03 datafile '/dm8/data/DMTS03.DBF' size 500 autoextend on next 2;
SQL> create tablespace DMTS04 datafile '/dm8/data/DMTS04.DBF' size 500 autoextend on next 2;
SQL> create user DMTEST IDENTIFIED by Dameng123 DEFAULT TABLESPACE DMTS01;
SQL> grant PUBLIC, RESOURCE, SOI, VTI to DMTEST;
3、创建分区表，到数据，创建分区索引并验证
$ cat /opt/prod.dat查看数据结构，然后创建
可使用manager管理工具创建，也可命令行创建
$ disql DMTEST/Dameng123
SQL> create table "DMTEST"."PROD"
(
"ProdID" INTEGER not null ,
"ProdName" VARCHAR(50),
"Code" VARCHAR(10),
"ReOrder" INTEGER,
"Cost" NUMBER(30, 2),
"Price" NUMBER(30, 2),
"PriSrc" VARCHAR(20),
"SecSrc" VARCHAR(10),
"ObsoletedDate" DATE
)
PARTITION BY RANGE ("ProdID")
(
PARTITION "P1" VALUES LESS THAN (1010) STORAGE( initial 1, next 1, minextents 1, fillfactor 0, on "DMTS01"),
PARTITION "P2" VALUES LESS THAN (1020) STORAGE( initial 1, next 1, minextents 1, fillfactor 0, on "DMTS02"),
PARTITION "P3" VALUES LESS THAN (1030) STORAGE( initial 1, next 1, minextents 1, fillfactor 0, on "DMTS03"),
PARTITION "P4" VALUES LESS THAN (MAXVALUE) STORAGE( initial 1, next 1, minextents 1, fillfactor 0, on "DMTS04")
)
storage(initial 1, next 1, minextents 1, fillfactor 0);
分区表里加载数据：

cd /opt

vi prod.ctl

LOAD DATA
INFILE '/opt/prod.dat'
INTO TABLE DMTEST.PROD
FIELDS ','

chown dmdba:dinstall prod.ctl prod.dat

su - dmdba

$ cd /dm8/bin
$ ./dmfldr DMTEST/Dameng123 control='/opt/prod.ctl' skip=1
检查数据是否成功：
SQL> select * from DMTEST.PROD;
SQL> select count(*) from DMTEST.PROD;

在分区表上创建的索引就是分区索引：
SQL> create index DMTEST.PROD_IND on DMTEST.PROD("ProdID");
验证：
SQL> explain select * from PROD where "ProdID"=1;
4、创建hash分区表，验证，创建全文索引并验证
SQL> create table "DMTEST"."T_HASH"
(
"ID" INTEGER,
"DEAL_DATE" DATE,
"CODE" VARCHAR(20),
"CONTENTS" VARCHAR(40)
)
PARTITION BY HASH ("ID")
(
PARTITION "P01",
PARTITION "P02",
PARTITION "P03",
PARTITION "P04",
PARTITION "P05",
PARTITION "P06",
PARTITION "P07",
PARTITION "P08",
PARTITION "P09",
PARTITION "P10",
PARTITION "P11",
PARTITION "P12",
PARTITION "P13",
PARTITION "P14",
PARTITION "P15",
PARTITION "P16"
)
storage(initial 1, next 1, minextents 1, fillfactor 0);
切换到DMTEST用户下：
SQL> insert into t_hash
(id, deal_date, code, contents)
select rownum,
to_date(to_char(sysdate - 365, 'J') +TRUNC(DBMS_RANDOM.VALUE(0, 365)),'J'),
ceil(dbms_random.value(1, 1000)),
lpad('dameng', 20, '*')
from dual connect by rownum <= 100000;
SQL> commit;
创建全文索引：
SQL> CREATE CONTEXT INDEX T_HASH_IND on DMTEST.T_HASH(CONTENTS) LEXER default_lexer;
SQL> alter CONTEXT INDEX DMTEST.T_HASH_IND on DMTEST.T_HASH REBUILD;
验证：
SQL> select CONTENTS from T_HASH;
SQL> explain select * from T_HASH where contains(CONTENTS, 'dameng');

5、创建作业
disql SYSDBA/Dameng123
SQL> call SP_INIT_JOB_SYS(1); 创建代理环境
创建作业可以使用管理工具进行，也可以命令行操作

图形化创建步骤：作业右键新建作业、输入作业名字和描述、添加作业步骤、完善作业步骤内容，选择作业类型以及别的相关配置后确定、新建作业调度，完善调度选项和要求后确定、查看对应的DDL语句，如无问题可以确定执行

命令行操作如下：
SQL>call SP_CREATE_JOB('JB1',1,0,'',0,0,'',0,'full database backup every Wednesday night at 23:00');
SQL> call SP_JOB_CONFIG_START('JB1');
SQL> call SP_JOB_SET_EP_SEQNO('JB1', 0);
SQL> call SP_ADD_JOB_STEP('JB1', 'S1', 6, '00000000/dm8/backup', 0, 0, 0, 0, NULL, 0);
SQL> call SP_ADD_JOB_SCHEDULE('JB1', 'S1', 1, 2, 1, 8, 0, '23:00:00', NULL, '2025-11-27 10:35:05', NULL, '');
SQL> call SP_JOB_CONFIG_COMMIT('JB1');

SQL> call SP_CREATE_JOB('JB2',1,0,'',0,0,'',0,'delete backup files before 8 days');
SQL> call SP_JOB_CONFIG_START('JB2');
SQL> call SP_ADD_JOB_STEP('JB2', 'S1', 0, 'sf_bakset_backup_dir_add(''DISK'',''/dm8/backup''); sp_db_bakset_remove_batch(''DISK'',now()-8);', 0, 0, 0, 0, NULL, 0);
SQL> call SP_ADD_JOB_SCHEDULE('JOB_REMOVE1', 'S1', 1, 1, 1, 0, 0, '22:00:00', NULL, '2025-11-27 9:13:35', NULL, '');
SQL> call SP_JOB_CONFIG_COMMIT('JOB_REMOVE1');

6、集群搭建
搭建之前需要在备机上创建同样的实例，然后关闭主机和备机的数据库
$ /dm8/bin/DmServiceDMSVR01 stop
主机上进行备份
./dmrman
BACKUP DATABASE '/dm8/data/DM01/dm.ini' FULL TO BACKUP_FILE1 BACKUPSET '/dm8/backup/BACKUP_FILE_01'
将备份拷贝到备机上：
cd /dm8/backup/BACKUP_FILE_01
scp * 192.168.109.126:/dm8/backup/

在备机上查看拷贝的备份，并进行还原
./dmrman
RESTORE DATABASE '/dm8/data/DM01/dm.ini' FROM BACKUPSET '/dm8/backup'
RECOVER DATABASE '/dm8/data/DM01/dm.ini' FROM BACKUPSET '/dm8/backup'
RECOVER DATABASE '/dm8/data/DM01/dm.ini' UPDATE DB_MAGIC
在主机上操作：
/dm8/data/DM01/目录下修改相关文件（dm.ini、dmmal.ini、dmarch.ini、dmwatcher.ini）
dm.ini（ALTER_MODE_STATUS、ENABLE_OFFLINE_TS、MAL_INI、ARCH_INI需要修改，其他基本一致，无需修改）

INSTANCE_NAME = DMSVR01
PORT_NUM = 5236
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 = 60
MAL_CONN_FAIL_INTERVAL = 60
[MAL_INST1]
MAL_INST_NAME =DMSVR01
MAL_HOST = 192.168.109.128
MAL_PORT = 55101
MAL_INST_HOST = 192.168.109.128
MAL_INST_PORT = 5236
MAL_DW_PORT = 65101
MAL_INST_DW_PORT = 45101
[MAL_INST2]
MAL_INST_NAME =DMSVR02
MAL_HOST = 192.168.109.126
MAL_PORT = 55121
MAL_INST_HOST = 192.168.109.126
MAL_INST_PORT = 5236
MAL_DW_PORT = 65121
MAL_INST_DW_PORT = 45121

dmarch.ini
[ARCHIVE_REALTIME]
ARCH_TYPE = REALTIME
ARCH_DEST =DMSVR02
[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 = MANUAL
DW_ERROR_TIME = 60
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 60
INST_OGUID = 13301388
INST_INI = /dm8/data/DM01/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dm8/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0

以Mount方式启动主库，修改参数并设置为主机
./dmserver /dm8/data/DM01/dm.ini mount
./disql sysdba/Dameng123
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL>sp_set_oguid(13301388);
SQL>alter database primary;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);

在备机上操作：
/dm8/data/DM01/目录下修改相关文件（ALTER_MODE_STATUS、ENABLE_OFFLINE_TS、MAL_INI、ARCH_INI需要修改）
dm.ini
INSTANCE_NAME = DMSVR02
PORT_NUM = 5236
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（直接主机上SCP拷贝无需修改）
MAL_CHECK_INTERVAL = 60
MAL_CONN_FAIL_INTERVAL = 60
[MAL_INST1]
MAL_INST_NAME =DMSVR01
MAL_HOST = 192.168.109.128
MAL_PORT = 55101
MAL_INST_HOST = 192.168.109.128
MAL_INST_PORT = 5236
MAL_DW_PORT = 65101
MAL_INST_DW_PORT = 45101
[MAL_INST2]
MAL_INST_NAME =DMSVR02
MAL_HOST = 192.168.109.126
MAL_PORT = 55121
MAL_INST_HOST = 192.168.109.126
MAL_INST_PORT = 5236
MAL_DW_PORT = 65121
MAL_INST_DW_PORT = 45121

dmarch.ini
[ARCHIVE_REALTIME]
ARCH_TYPE = REALTIME
ARCH_DEST = DMSVR01
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm8/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT =

dmwatcher.ini（直接主机上SCP拷贝、无需修改）
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = MANUAL
DW_ERROR_TIME = 60
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 60
INST_OGUID = 13301388
INST_INI = /dm8/data/DM01/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dm8/bin/dmserver
RLOG_APPLY_THRESHOLD = 0

以 Mount 方式启动备库，修改参数并设置为备机
./dmserver /dm8/data/DM01/dm.ini mount
./disql sysdba/Dameng123
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL>sp_set_oguid(13301388);
SQL>alter database standby;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);

在主备机上分别执行下面命令，后查看数据库状态为open：
./dmwatcher /dm8/data/DM01/dmwatcher.ini

在监视器上操作：
/dm8目录下添加dmmonitor.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 = 13301388
MON_DW_IP = 192.168.109.128:65101
MON_DW_IP = 192.168.109.126:65121

./dmmonitor /dm8/dmmonitor.ini

注册服务到操作系统里
/dm8/script/root目录使用root用户执行：
主机、备机
./dm_service_installer.sh -t dmserver -p DMSERVER -dm_ini /dm8/data/DM01/dm.ini
./dm_service_installer.sh -t dmwatcher -p DMWATCHER -watcher_ini /dm8/data/DM01/dmwatcher.ini
监视器
./dm_service_installer.sh -t dmmonitor -p DMMONITOR -monitor_ini /dm8/dmmonitor.ini
启动服务
systemctl start DmServiceDMSERVER
systemctl start DmWatcherServiceDMWATCHER
systemctl start DmMonitorServiceDMMONITOR