注册
【与达梦同行】达梦分区置换
技术分享/ 文章详情 /

【与达梦同行】达梦分区置换

S-surprise-H 2022/12/29 1289 4 1

达梦分区置换的方法,前提是:创建的普通表和分区表的表结构以及索引且索引顺序全部保持一致后,再进行置换,以下有两种迁移方式可供参考
1.通过分区置换的方法,置换为普通表后,DTS开启多个窗口并行迁移到目的端,迁移完成后,目的端通过分区置换方法,再置换为分区表;
2.通过分区置换的方法,置换为普通表后,通过表备份还原的方法,将备份还原到目的端后,通过分区置换方法,再置换为分区表。

下面对第二种分区置换方法进行举例说明:

1.统计分区表的分区数量:

select count(*) from "TEST"."TT_上海";
select count(*) from "TEST"."TT_云南";
select count(*) from "TEST"."TT_内蒙";

2.创建和分区表相同表结构的普通表以及索引:

CREATE TABLE "TEST"."TT_上海_1" ("area_code" VARCHAR(255),"TTsn" VARCHAR(80),"org_code" VARCHAR(64),"file_url" VARCHAR(50),"entity" TEXT,"created" DATETIME(6)) STORAGE(ON "TEST_DATA", CLUSTERBTR) ;
CREATE TABLE "TEST"."TT_云南_2" ("area_code" VARCHAR(255),"TTsn" VARCHAR(80),"org_code" VARCHAR(64),"file_url" VARCHAR(50),"entity" TEXT,"created" DATETIME(6)) STORAGE(ON "TEST_DATA", CLUSTERBTR) ;
CREATE TABLE "TEST"."TT_内蒙_3" ("area_code" VARCHAR(255),"TTsn" VARCHAR(80),"org_code" VARCHAR(64),"file_url" VARCHAR(50),"entity" TEXT,"created"

CREATE  INDEX "index-TT_1" ON "TEST"."TT_上海_1"("org_code" ASC) STORAGE(ON "TEST_DATA", CLUSTERBTR) ;
CREATE  INDEX "index-TT_2" ON "TEST"."TT_云南_2"("org_code" ASC) STORAGE(ON "TEST_DATA", CLUSTERBTR) ;
CREATE  INDEX "index-TT_3" ON "TEST"."TT_内蒙_3"("org_code" ASC) STORAGE(ON "TEST_DATA", CLUSTERBTR) ;

3.将分区表的各个分区转换为普通表:

alter table "TEST".TT exchange PARTITION  "上海"  with table "TEST"."TT_上海_1";
alter table "TEST".TT exchange PARTITION  "云南"  with table "TEST"."TT_云南_2";
alter table "TEST".TT exchange PARTITION  "内蒙"  with table "TEST"."TT_内蒙_3";

4.备份表普通表:

BACKUP TABLE "TEST"."TT_上海_1" BACKUPSET '/data2/dmdata/dbbak/TT_1';
BACKUP TABLE "TEST"."TT_云南_2" BACKUPSET '/data2/dmdata/dbbak/TT_2';
BACKUP TABLE "TEST"."TT_内蒙_3" BACKUPSET '/data2/dmdata/dbbak/TT_3';

5.将所有的表备份发送到目标机器 10.xx.xx.12 上:

scp -r /data2/dmdata/dbbak dmdba@10.xx.xx.12:/dev/shm/dbbak

6.还原表结构:

RESTORE TABLE struct FROM BACKUPSET '/dev/shm/dbbak/dbbak/TT_1';
RESTORE TABLE struct FROM BACKUPSET '/dev/shm/dbbak/dbbak/TT_2';
RESTORE TABLE struct FROM BACKUPSET '/dev/shm/dbbak/dbbak/TT_3';

7.还原表数据:

RESTORE TABLE FROM BACKUPSET '/dev/shm/dbbak/dbbak/TT_1';
RESTORE TABLE FROM BACKUPSET '/dev/shm/dbbak/dbbak/TT_2';
RESTORE TABLE FROM BACKUPSET '/dev/shm/dbbak/dbbak/TT_3';

8.在 10.xx.xx.12 上新建一张分区表:

CREATE TABLE "TEST"."TT10"
(
"area_code" VARCHAR(255),
"TTsn" VARCHAR(80),
"org_code" VARCHAR(64),
"file_url" VARCHAR(50),
"entity" TEXT,
"created" DATETIME(6))
PARTITION BY LIST("area_code")

(
PARTITION  "内蒙"  VALUES('150000')  ,
PARTITION  "云南"  VALUES('530000')  ,
PARTITION  "上海"  VALUES('310000')  ,
)

CREATE  INDEX "index-orgCode10" ON "TEST"."TT10"("org_code" ASC) STORAGE(ON "TEST_DATA", CLUSTERBTR) ;

9.将 10.xx.xx.12 上的普通表转换为分区表:

alter table "TEST".TT exchange PARTITION  "上海"  with table "TEST"."TT_上海_1";
alter table "TEST".TT exchange PARTITION  "云南"  with table "TEST"."TT_云南_2";
alter table "TEST".TT exchange PARTITION  "内蒙"  with table "TEST"."TT_内蒙_3";

至此分区置换完成。

评论
后发表回复

作者

文章

阅读量

获赞

扫一扫
联系客服