DMDPC恢复到指定时间点

架构说明
服务器/实例 10.15.1.12 10.15.1.13 10.15.1.16
MP实例 MP_A MP_B MP_C
SP实例 SP1 SP2 SP3
BP实例1 BP1_A BP2_A BP3_A
BP实例2 BP3_B BP1_B BP2_B
BP实例3 BP2_C BP3_C BP1_C

参数说明
联机情况下连接SP执行备份，在每个MP及BP的主节点生成备份，脱机后进行归档备份，同样在主节点下生成备份
恢复到指定时间点，需搭配DPC_LOG_INTERVAL参数使用，关于此参数说明

BAK_MAGIC：备份集魔数，用于唯一标识DMDPC集群内同一批次的MP和BP的备份集。联机备份时，自动生成。脱机备份时，可手动指定，不指定时默认为0。还原时可使用show backupset ‘备份集路径/备份集名称’ info meta;获取。
DPC_MAGIC：DMDPC集群魔数，用于唯一标识一个DMDPC集群，同一个DMDPC集群内的MP和BP节点的DPC_MAGIC值相同。还原时可使用show backupset ‘备份集路径/备份集名称’ info db;获取。

步骤简述
操作步骤如下：

创建测试表，插入数据，备份后新增数据
停止数据库服务，使用dmrman进行归档脱机备份
拷贝主节点归档备份及数据备份到备节点
还原主节点
备节点还原归档，通过指定归档恢复数据库到指定时间点

恢复步骤
创建表，插入数据，备份后新增数据

归档备份
停库后进行归档备份，注：需备份MP及BP主节点
–备份12节点归档
–备份MP归档
BACKUP ARCHIVE LOG DATABASE ‘/data/lhr/dmdpc/MP_A/DAMENG/dm.ini’;
–备份BP归档
BACKUP ARCHIVE LOG DATABASE ‘/data/lhr/dmdpc/BP1_A/DAMENG/dm.ini’;
–备份13节点归档
–备份BP归档
BACKUP ARCHIVE LOG DATABASE ‘/data/lhr/dmdpc/BP2_A/DAMENG/dm.ini’;
–备份16节点归档
–备份BP归档
BACKUP ARCHIVE LOG DATABASE ‘/data/lhr/dmdpc/BP3_A/DAMENG/dm.ini’;
拷贝归档备份及数据库备份到备库
12节点
–拷贝 MP 实例备份到备库
scp -r /data/lhr/dmdpc/MP_A/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_779624 dmdba@10.15.1.13:/data/lhr/dmdpc/MP_B/DAMENG/bak
scp -r /data/lhr/dmdpc/MP_A/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_779624 dmdba@10.15.1.16:/data/lhr/dmdpc/MP_C/DAMENG/bak
–拷贝 MP 归档备份到备库
scp -r /data/lhr/dmdpc/MP_A/DAMENG/bak/ARCH_FULL_20240830_150323_149153 dmdba@10.15.1.13:/data/lhr/dmdpc/MP_B/DAMENG/bak
scp -r /data/lhr/dmdpc/MP_A/DAMENG/bak/ARCH_FULL_20240830_150323_149153 dmdba@10.15.1.16:/data/lhr/dmdpc/MP_C/DAMENG/bak
–拷贝 BP1_A 实例备份到备库
scp -r /data/lhr/dmdpc/BP1_A/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_784667 dmdba@10.15.1.13:/data/lhr/dmdpc/BP1_B/DAMENG/bak/
scp -r /data/lhr/dmdpc/BP1_A/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_784667 dmdba@10.15.1.16:/data/lhr/dmdpc/BP1_C/DAMENG/bak/
–拷贝 BP1_A 归档备份到备库
scp -r /data/lhr/dmdpc/BP1_A/DAMENG/bak/ARCH_FULL_20240830_150233_039253 dmdba@10.15.1.13:/data/lhr/dmdpc/BP1_B/DAMENG/bak
scp -r /data/lhr/dmdpc/BP1_A/DAMENG/bak/ARCH_FULL_20240830_150233_039253 dmdba@10.15.1.16:/data/lhr/dmdpc/BP1_C/DAMENG/bak
### 13节点
–拷贝 BP2_A 实例备份到备库
scp -r /data/lhr/dmdpc/BP2_A/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_781337 dmdba@10.15.1.16:/data/lhr/dmdpc/BP2_B/DAMENG/bak/
scp -r /data/lhr/dmdpc/BP2_A/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_781337 dmdba@10.15.1.12:/data/lhr/dmdpc/BP2_C/DAMENG/bak/
–拷贝 BP2_A 归档备份到备库
scp -r /data/lhr/dmdpc/BP2_A/DAMENG/bak/ARCH_FULL_20240830_150402_090428 dmdba@10.15.1.16:/data/lhr/dmdpc/BP2_B/DAMENG/bak
scp -r /data/lhr/dmdpc/BP2_A/DAMENG/bak/ARCH_FULL_20240830_150402_090428 dmdba@10.15.1.12:/data/lhr/dmdpc/BP2_C/DAMENG/bak

16节点

–拷贝 BP3_A 实例备份到备库
scp -r /data/lhr/dmdpc/BP3_A/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_777909 dmdba@10.15.1.12:/data/lhr/dmdpc/BP3_B/DAMENG/bak/
scp -r /data/lhr/dmdpc/BP3_A/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_777909 dmdba@10.15.1.13:/data/lhr/dmdpc/BP3_C/DAMENG/bak/
–拷贝 BP3_A 归档备份到备库
scp -r /data/lhr/dmdpc/BP3_A/DAMENG/bak/ARCH_FULL_20240830_150432_333963 dmdba@10.15.1.12:/data/lhr/dmdpc/BP3_B/DAMENG/bak
scp -r /data/lhr/dmdpc/BP3_A/DAMENG/bak/ARCH_FULL_20240830_150432_333963 dmdba@10.15.1.13:/data/lhr/dmdpc/BP3_C/DAMENG/bak

还原MP

### 获取bak_magic

show backupset ‘/data/lhr/dmdpc/MP_A/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_779624’ info meta;

### 获取DPC_MAGIC

show backupset ‘/data/lhr/dmdpc/MP_A/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_779624’ info db;

–还原 MP_A数据

RESTORE DATABASE ‘/data/lhr/dmdpc/MP_A/DAMENG/dm.ini’ FROM BACKUPSET ‘/data/lhr/dmdpc/MP_A/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_779624’ USE BAK_MAGIC 849104265;
RECOVER DATABASE ‘/data/lhr/dmdpc/MP_A/DAMENG/dm.ini’ WITH ARCHIVEDIR ‘/data/lhr/dmdpc/MP_A/DAMENG/arch/’ UNTIL TIME ‘2024-08-30 12:04:00’;
RECOVER DATABASE ‘/data/lhr/dmdpc/MP_A/DAMENG/dm.ini’ UPDATE DPC_MAGIC 303765128;
RECOVER DATABASE ‘/data/lhr/dmdpc/MP_A/DAMENG/dm.ini’ update DB_MAGIC;

#### --还原 MP_B 归档

RESTORE ARCHIVE LOG FROM BACKUPSET ‘/data/lhr/dmdpc/MP_B/DAMENG/bak/ARCH_FULL_20240830_150323_149153’ TO DATABASE ‘/data/lhr/dmdpc/MP_B/DAMENG/dm.ini’ OVERWRITE 2;

还原 MP_B数据

RESTORE DATABASE ‘/data/lhr/dmdpc/MP_B/DAMENG/dm.ini’ FROM BACKUPSET ‘/data/lhr/dmdpc/MP_B/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_779624’ USE BAK_MAGIC 849104265;
RECOVER DATABASE ‘/data/lhr/dmdpc/MP_B/DAMENG/dm.ini’ WITH ARCHIVEDIR ‘/data/lhr/dmdpc/MP_B/DAMENG/arch/’ UNTIL TIME ‘2024-08-30 12:04:00’;
RECOVER DATABASE ‘/data/lhr/dmdpc/MP_B/DAMENG/dm.ini’ UPDATE DPC_MAGIC 303765128;
RECOVER DATABASE ‘/data/lhr/dmdpc/MP_B/DAMENG/dm.ini’ update DB_MAGIC;

–还原 MP_C 归档

RESTORE ARCHIVE LOG FROM BACKUPSET ‘/data/lhr/dmdpc/MP_C/DAMENG/bak/ARCH_FULL_20240830_150323_149153’ TO DATABASE ‘/data/lhr/dmdpc/MP_C/DAMENG/dm.ini’ OVERWRITE 2;

–还原 MP_C数据

RESTORE DATABASE ‘/data/lhr/dmdpc/MP_C/DAMENG/dm.ini’ FROM BACKUPSET ‘/data/lhr/dmdpc/MP_C/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_779624’ USE BAK_MAGIC 849104265;
RECOVER DATABASE ‘/data/lhr/dmdpc/MP_C/DAMENG/dm.ini’ WITH ARCHIVEDIR ‘/data/lhr/dmdpc/MP_C/DAMENG/arch/’ UNTIL TIME ‘2024-08-30 12:04:00’;
RECOVER DATABASE ‘/data/lhr/dmdpc/MP_C/DAMENG/dm.ini’ UPDATE DPC_MAGIC 303765128;
RECOVER DATABASE ‘/data/lhr/dmdpc/MP_C/DAMENG/dm.ini’ update DB_MAGIC;

还原BP

–还原 BP1_A

RESTORE DATABASE ‘/data/lhr/dmdpc/BP1_A/DAMENG/dm.ini’ FROM BACKUPSET ‘/data/lhr/dmdpc/BP1_A/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_784667’ USE BAK_MAGIC 849104265;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP1_A/DAMENG/dm.ini’ WITH ARCHIVEDIR ‘/data/lhr/dmdpc/BP1_A/DAMENG/arch/’ UNTIL TIME ‘2024-08-30 12:04:00’;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP1_A/DAMENG/dm.ini’ UPDATE DPC_MAGIC 303765128;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP1_A/DAMENG/dm.ini’ update DB_MAGIC;

–还原 BP1_B 归档

RESTORE ARCHIVE LOG FROM BACKUPSET ‘/data/lhr/dmdpc/BP1_B/DAMENG/bak/ARCH_FULL_20240830_150233_039253’ TO DATABASE ‘/data/lhr/dmdpc/BP1_B/DAMENG/dm.ini’ OVERWRITE 2;

–还原 BP1_B

RESTORE DATABASE ‘/data/lhr/dmdpc/BP1_B/DAMENG/dm.ini’ FROM BACKUPSET ‘/data/lhr/dmdpc/BP1_B/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_784667’ USE BAK_MAGIC 849104265;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP1_B/DAMENG/dm.ini’ WITH ARCHIVEDIR ‘/data/lhr/dmdpc/BP1_B/DAMENG/arch/’ UNTIL TIME ‘2024-08-30 12:04:00’;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP1_B/DAMENG/dm.ini’ UPDATE DPC_MAGIC 303765128;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP1_B/DAMENG/dm.ini’ update DB_MAGIC;

–还原 BP1_C 归档

RESTORE ARCHIVE LOG FROM BACKUPSET ‘/data/lhr/dmdpc/BP1_C/DAMENG/bak/ARCH_FULL_20240830_150233_039253’ TO DATABASE ‘/data/lhr/dmdpc/BP1_C/DAMENG/dm.ini’ OVERWRITE 2;

–还原 BP1_C

RESTORE DATABASE ‘/data/lhr/dmdpc/BP1_C/DAMENG/dm.ini’ FROM BACKUPSET ‘/data/lhr/dmdpc/BP1_C/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_784667’ USE BAK_MAGIC 849104265;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP1_C/DAMENG/dm.ini’ WITH ARCHIVEDIR ‘/data/lhr/dmdpc/BP1_C/DAMENG/arch/’ UNTIL TIME ‘2024-08-30 12:04:00’;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP1_C/DAMENG/dm.ini’ UPDATE DPC_MAGIC 303765128;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP1_C/DAMENG/dm.ini’ update DB_MAGIC;

–还原 BP2_A

RESTORE DATABASE ‘/data/lhr/dmdpc/BP2_A/DAMENG/dm.ini’ FROM BACKUPSET ‘/data/lhr/dmdpc/BP2_A/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_781337’ USE BAK_MAGIC 849104265;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP2_A/DAMENG/dm.ini’ WITH ARCHIVEDIR ‘/data/lhr/dmdpc/BP2_A/DAMENG/arch/’ UNTIL TIME ‘2024-08-30 12:04:00’;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP2_A/DAMENG/dm.ini’ UPDATE DPC_MAGIC 303765128;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP2_A/DAMENG/dm.ini’ update DB_MAGIC;

–还原 BP2_B 归档

RESTORE ARCHIVE LOG FROM BACKUPSET ‘/data/lhr/dmdpc/BP2_B/DAMENG/bak/ARCH_FULL_20240830_150402_090428’ TO DATABASE ‘/data/lhr/dmdpc/BP2_B/DAMENG/dm.ini’ OVERWRITE 2;

–还原 BP2_B

RESTORE DATABASE ‘/data/lhr/dmdpc/BP2_B/DAMENG/dm.ini’ FROM BACKUPSET ‘/data/lhr/dmdpc/BP2_B/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_781337’ USE BAK_MAGIC 849104265;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP2_B/DAMENG/dm.ini’ WITH ARCHIVEDIR ‘/data/lhr/dmdpc/BP2_B/DAMENG/arch/’ UNTIL TIME ‘2024-08-30 12:04:00’;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP2_B/DAMENG/dm.ini’ UPDATE DPC_MAGIC 303765128;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP2_B/DAMENG/dm.ini’ update DB_MAGIC;

–还原 BP2_C 归档

RESTORE ARCHIVE LOG FROM BACKUPSET ‘/data/lhr/dmdpc/BP2_C/DAMENG/bak/ARCH_FULL_20240830_150402_090428’ TO DATABASE ‘/data/lhr/dmdpc/BP2_C/DAMENG/dm.ini’ OVERWRITE 2;

–还原 BP2_C

RESTORE DATABASE ‘/data/lhr/dmdpc/BP2_C/DAMENG/dm.ini’ FROM BACKUPSET ‘/data/lhr/dmdpc/BP2_C/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_781337’ USE BAK_MAGIC 849104265;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP2_C/DAMENG/dm.ini’ WITH ARCHIVEDIR ‘/data/lhr/dmdpc/BP2_C/DAMENG/arch/’ UNTIL TIME ‘2024-08-30 12:04:00’;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP2_C/DAMENG/dm.ini’ UPDATE DPC_MAGIC 303765128;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP2_C/DAMENG/dm.ini’ update DB_MAGIC;

–还原 BP3_A

RESTORE DATABASE ‘/data/lhr/dmdpc/BP3_A/DAMENG/dm.ini’ FROM BACKUPSET ‘/data/lhr/dmdpc/BP3_A/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_777909’ USE BAK_MAGIC 849104265;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP3_A/DAMENG/dm.ini’ WITH ARCHIVEDIR ‘/data/lhr/dmdpc/BP3_A/DAMENG/arch/’ UNTIL TIME ‘2024-08-30 12:04:00’;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP3_A/DAMENG/dm.ini’ UPDATE DPC_MAGIC 303765128;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP3_A/DAMENG/dm.ini’ update DB_MAGIC;

–还原 BP3_B 归档

RESTORE ARCHIVE LOG FROM BACKUPSET ‘/data/lhr/dmdpc/BP3_B/DAMENG/bak/ARCH_FULL_20240830_150432_333963’ TO DATABASE ‘/data/lhr/dmdpc/BP3_B/DAMENG/dm.ini’ OVERWRITE 2;

–还原 BP3_B

RESTORE DATABASE ‘/data/lhr/dmdpc/BP3_B/DAMENG/dm.ini’ FROM BACKUPSET ‘/data/lhr/dmdpc/BP3_B/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_777909’ USE BAK_MAGIC 849104265;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP3_B/DAMENG/dm.ini’ WITH ARCHIVEDIR ‘/data/lhr/dmdpc/BP3_B/DAMENG/arch/’ UNTIL TIME ‘2024-08-30 12:04:00’;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP3_B/DAMENG/dm.ini’ UPDATE DPC_MAGIC 303765128;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP3_B/DAMENG/dm.ini’ update DB_MAGIC;

–还原 BP3_C 归档

RESTORE ARCHIVE LOG FROM BACKUPSET ‘/data/lhr/dmdpc/BP3_C/DAMENG/bak/ARCH_FULL_20240830_150432_333963’ TO DATABASE ‘/data/lhr/dmdpc/BP3_C/DAMENG/dm.ini’ OVERWRITE 2;

–还原 BP3_C

RESTORE DATABASE ‘/data/lhr/dmdpc/BP3_C/DAMENG/dm.ini’ FROM BACKUPSET ‘/data/lhr/dmdpc/BP3_C/DAMENG/bak/DB_DAMENG_FULL_20240830_113120_777909’ USE BAK_MAGIC 849104265;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP3_C/DAMENG/dm.ini’ WITH ARCHIVEDIR ‘/data/lhr/dmdpc/BP3_C/DAMENG/arch/’ UNTIL TIME ‘2024-08-30 12:04:00’;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP3_C/DAMENG/dm.ini’ UPDATE DPC_MAGIC 303765128;
RECOVER DATABASE ‘/data/lhr/dmdpc/BP3_C/DAMENG/dm.ini’ update DB_MAGIC;