备份恢复-物理恢复
备份还原原理
DM数据库中的数据存储在数据库的物理数据文件中，数据文件按照页、簇和段的方式进行管理，数据页是最小的数据存储单元。任何一个对 DM 数据库的操作，归根结底都是对某个数据文件页的读写操作。因此，DM 备份的本质就是 从数据库文件中拷贝有效的数据页保存到备份集中，这里的有效数据页包括数据文件的描述页和被分配使用的数据页。而在备份的过程中，如果数据库系统还在继续运行，这期间的数据库操作并不是都会立即体现到数据文件中，而是首先以日志的形式写到归档日志中，因此，为了保证用户可以通过备份集将数据恢复到备份结束时间点的状态，就需要将备份过程中产生的归档日志也保存到备份集中。
还原与恢复是备份的逆过程。还原是将备份集中的有效数据页重新写入目标数据文件的过程。恢复则是指通过重做归档日志，将数据库状态恢复到备份结束时的状态；也可以恢复到指定时间点和指定 LSN 。恢复结束以后，数据库中可能存在处于未提交状态的活动事务，这些活动事务在恢复结束后的第一次数据库系统启动时，会由 DM 数据库自动进行回滚。 备份、还原与恢复的关系如图：
归档配置
联机配置
修改数据库状态 SQL> alter database mount; 操作已执行 已用时间: 3.853(毫秒). 执行号:0. 修改归档状态 SQL> alter database archivelog; 操作已执行 已用时间: 14.150(毫秒). 执行号:0. 配置归档 SQL> alter database add archivelog 'dest=/dmdata/arch,type=local,file_size=64,space_limit=1024'; alter database add archivelog 'dest=/dmdata/arch,type=local,file_size=64,space_limit=1024'; executed successfully 已用时间: 4.324(毫秒). 执行号:0. SQL> 查看归档状态 SQL> select * from v$arch_status ; 行号 ARCH_TYPE ARCH_DEST ARCH_STATUS ARCH_SRC ---------- --------- ------------ ----------- -------- 1 LOCAL /dmdata/arch VALID DMSERVER 已用时间: 2.596(毫秒). 执行号:2702. 修改数据库状态 SQL> alter database open; 操作已执行 已用时间: 23.013(毫秒). 执行号:0. 切换日志，查看归档信息 SQL> alter system switch logfile; 操作已执行 已用时间: 8.930(毫秒). 执行号:0. SQL> select * from v$dm_arch_ini; 行号 ARCH_NAME ARCH_TYPE ARCH_DEST ARCH_FILE_SIZE ARCH_SPACE_LIMIT ARCH_HANG_FLAG ARCH_TIMER_NAME ---------- -------------- --------- ------------ -------------- ---------------- -------------- --------------- ARCH_IS_VALID ARCH_WAIT_APPLY ARCH_INCOMING_PATH ARCH_CURR_DEST ARCH_FLUSH_BUF_SIZE ARCH_RESERVE_TIME ------------- --------------- ------------------ -------------- ------------------- ----------------- ARCH_LOCAL_SHARE ARCH_LOCAL_SHARE_CHECK ARCH_SEND_DELAY ARCH_DEST_IP ARCH_DEST_PORT ARCH_DEST_ID ---------------- ---------------------- --------------- ------------ -------------- ------------ ARCH_ASM_MIRROR ARCH_ASM_STRIPING ARCH_RECOVER_TIME ARCH_CENTER_ID ARCH_FAILOVER ARCH_SUBSCRIBE_MODE --------------- ----------------- ----------------- -------------- ------------- ------------------- ARCH_THRESHOLD_PER_SECOND ARCH_STANDBY_APPLY ARCH_DISTRICT_ID ARCH_VOTE_PRIORITY ARCH_CENTER_COMMIT ------------------------- ------------------ ---------------- ------------------ ------------------ 1 ARCHIVE_LOCAL1 LOCAL /dmdata/arch 100 1024 1 NULL Y NULL NULL /dmdata/arch 2 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 已用时间: 3.368(毫秒). 执行号:2703. 查看归档状态 SQL> select * from v$arch_status; 行号 ARCH_TYPE ARCH_DEST ARCH_STATUS ARCH_SRC ---------- --------- ------------ ----------- -------- 1 LOCAL /dmdata/arch VALID DMSERVER 已用时间: 2.800(毫秒). 执行号:2704. SQL>复制
手动配置归档
修改配置文件
[dmdba@localhost DAMENG]$ grep 'ARCH_INI' dm.ini ARCH_INI = 1 #dmarch.ini [dmdba@localhost DAMENG]$ vi dmarch.ini [dmdba@localhost DAMENG]$ cat dmarch.ini #DaMeng Database Archive Configuration file #this is comments ARCH_WAIT_APPLY = 0 [ARCHIVE_LOCAL1] ARCH_TYPE = LOCAL ARCH_DEST = /dmdata/arch ARCH_FILE_SIZE = 100 ARCH_SPACE_LIMIT = 1024 ARCH_FLUSH_BUF_SIZE = 2 ARCH_HANG_FLAG = 1复制
重启数据库
[dmdba@localhost DAMENG]$ systemctl restart DmServiceDMSERVER ==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units === Authentication is required to manage system services or units. Authenticating as: root Password: ==== AUTHENTICATION COMPLETE === [dmdba@localhost DAMENG]$ [dmdba@localhost DAMENG]$ systemctl status DmServiceDMSERVER ● DmServiceDMSERVER.service - DM Instance Service Loaded: loaded (/usr/lib/systemd/system/DmServiceDMSERVER.service; enabled; vendor preset: disabled) Active: active (running) since 五 2025-05-30 17:07:34 CST; 1s ago Process: 19971 ExecStop=/opt/dmdbms/bin/DmServiceDMSERVER stop (code=exited, status=0/SUCCESS) Process: 20024 ExecStart=/opt/dmdbms/bin/DmServiceDMSERVER start (code=exited, status=0/SUCCESS) Main PID: 20045 (dmserver) CGroup: /system.slice/DmServiceDMSERVER.service └─20045 /opt/dmdbms/bin/dmserver path=/opt/dmdbms/data/DAMENG/dm.ini -noconsole复制
联机数据库备份与恢复
设置备份选项
backupset [backupset_name] 指定备份集路径和名称 to [backup_name] 指定备份名 backupinfo 添加备份描述信息 DEVICE TYPE [DISK|TAPE] 指定介质类型 maxpiecesize 限制备份片大小 limit 限速 identified by 加密 ENCRYPT WITH 指定加密算法 COMPRESSED 备份压缩 PARALLELL 并行备份复制
联机备份
数据库备份
执行全库备份
SQL> backup database full with backupdir '/opt/dmdbms/data/DAMENG/bak' backupname fullbak1 backupset 'backset_1' BACKUPINFO '第一次全备份' maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2; 操作已执行 已用时间: 00:00:08.664. 执行号:501.复制
查看生成的备份文件
SQL> select * from v$backupset; 行号 DEVICE_TYPE BACKUP_ID PARENT_ID BACKUP_NAME BACKUP_PATH TYPE LEVEL RANGE# OBJECT_NAME OBJECT_ID BASE_NAME ---------- ----------- ----------- ----------- ----------- ------------------------------------------------- ----------- ----------- ----------- ----------- ----------- --------- BACKUP_TIME DESC# ENCRYPT_TYPE COMPRESS_LEVEL WITHOUT_LOG USE_PWR PKG_SIZE BEGIN_LSN END_LSN BKP_NUM -------------------------- ------------------ ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- ----------- DBF_NUM PARALLEL_NUM BAKSET_TYPE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE MIN_DCT_VER DDL_CLONE BAK_MAGIC VERSION SUB_VERSION BAKSET_MAGIC ----------- ------------ ----------- ----------- -------------------- ------------ ----------- ----------- ----------- ----------- ----------- ----------- ------------ 1 DISK 1058056292 -1 FULLBAK1 /opt/dmdbms/data/DAMENG/bak/backset_1 0 0 1 DAMENG -1 2025-05-30 17:22:10.087386 第一次全备份 0 1 0 0 67108864 52810 52898 3 5 2 0 0 0 134283815 0 4 0 0 16394 14 1058056292 2 DISK 1498157108 1058056292 FULLBAK1 /opt/dmdbms/data/DAMENG/bak/backset_1/backset_1_1 0 0 1 DAMENG -1 2025-05-30 17:22:07.802874 第一次全备份 0 1 0 0 67108864 52810 0 1 3 1 0 0 0 134283815 0 4 0 NULL 16394 14 1498157108 3 DISK 1932845582 1058056292 FULLBAK1 /opt/dmdbms/data/DAMENG/bak/backset_1/backset_1_0 0 0 1 DAMENG -1 2025-05-30 17:22:08.030674 第一次全备份 0 1 0 0 67108864 52810 0 1 1 1 0 0 0 134283815 0 4 0 NULL 16394 14 1932845582 已用时间: 24.948(毫秒). 执行号:503. SQL> host ls -RL /opt/dmdbms/data/DAMENG/bak /opt/dmdbms/data/DAMENG/bak: backset_1 /opt/dmdbms/data/DAMENG/bak/backset_1: backset_1_0 backset_1_1 backset_1.bak backset_1.meta /opt/dmdbms/data/DAMENG/bak/backset_1/backset_1_0: backset_1_0.bak backset_1_0.meta /opt/dmdbms/data/DAMENG/bak/backset_1/backset_1_1: backset_1_1.bak backset_1_1.meta SQL>复制
基于全备做增量备份
SQL> backup database INCREMENT with backupdir '/opt/dmdbms/data/DAMENG/bak' backupname incbak1 backupset 'incbak_1' BACKUPINFO '第一次增量备份' maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2; 操作已执行 已用时间: 00:00:13.034. 执行号:504. SQL> select * from v$backupset where BACKUP_NAME='INCBAK1'; 行号 DEVICE_TYPE BACKUP_ID PARENT_ID BACKUP_NAME BACKUP_PATH TYPE LEVEL RANGE# OBJECT_NAME OBJECT_ID BASE_NAME ---------- ----------- ----------- ----------- ----------- ----------------------------------------------- ----------- ----------- ----------- ----------- ----------- --------- BACKUP_TIME DESC# ENCRYPT_TYPE COMPRESS_LEVEL WITHOUT_LOG USE_PWR PKG_SIZE BEGIN_LSN END_LSN BKP_NUM -------------------------- --------------------- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- ----------- DBF_NUM PARALLEL_NUM BAKSET_TYPE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE MIN_DCT_VER DDL_CLONE BAK_MAGIC VERSION SUB_VERSION BAKSET_MAGIC ----------- ------------ ----------- ----------- -------------------- ------------ ----------- ----------- ----------- ----------- ----------- ----------- ------------ 1 DISK 1454046922 -1 INCBAK1 /opt/dmdbms/data/DAMENG/bak/incbak_1 1 0 1 DAMENG -1 FULLBAK1 2025-05-30 17:24:48.208988 第一次增量备份 0 1 0 0 67108864 52912 52912 2 4 2 0 0 0 134283815 0 4 0 0 16394 14 1454046922 2 DISK 1536854952 1454046922 INCBAK1 /opt/dmdbms/data/DAMENG/bak/incbak_1/incbak_1_1 1 0 1 DAMENG -1 FULLBAK1 2025-05-30 17:24:46.134393 第一次增量备份 0 1 0 0 67108864 52912 0 1 3 1 0 0 0 134283815 0 4 0 NULL 16394 14 1536854952 3 DISK 1536854952 1454046922 INCBAK1 /opt/dmdbms/data/DAMENG/bak/incbak_1/incbak_1_0 1 0 1 DAMENG -1 FULLBAK1 2025-05-30 17:24:46.166737 第一次增量备份 0 1 0 0 67108864 52912 0 1 1 1 0 0 0 134283815 0 4 0 NULL 16394 14 1536854952 已用时间: 43.265(毫秒). 执行号:506. SQL> host ls -RL /opt/dmdbms/data/DAMENG/bak/incbak_1 /opt/dmdbms/data/DAMENG/bak/incbak_1: incbak_1_0 incbak_1_1 incbak_1.meta /opt/dmdbms/data/DAMENG/bak/incbak_1/incbak_1_0: incbak_1_0.bak incbak_1_0.meta /opt/dmdbms/data/DAMENG/bak/incbak_1/incbak_1_1: incbak_1_1.bak incbak_1_1.meta复制
表空间备份
准备表空间
SQL> create tablespace test_backup datafile '/opt/dmdbms/data/DAMENG/TEST_BACKUP.DBF' SIZE 256 ; 操作已执行 已用时间: 59.954(毫秒). 执行号:507.复制
完全备份
SQL> backup tablespace test_backup full with backupdir '/opt/dmdbms/data/DAMENG/bak' backupname fullbak_tbs1 backupset 'fullbak_tbs1' BACKUPINFO '第一次表空间备份' maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2; 操作已执行 已用时间: 00:00:05.031. 执行号:508.复制
查看
SQL> select * from v$backupset WHERE BACKUP_NAME='FULLBAK_TBS1'; 1 DISK 377961154 -1 FULLBAK_TBS1 /opt/dmdbms/data/DAMENG/bak/fullbak_tbs1 0 0 2 TEST_BACKUP 6 2025-05-30 17:35:05.424484 第一次表空间备份 0 1 0 0 67108864 52936 52943 1 1 1 0 0 0 134283815 0 4 0 0 16394 14 377961154 已用时间: 48.607(毫秒). 执行号:509. SQL> host ls -RL /opt/dmdbms/data/DAMENG/bak/fullbak_tbs1 /opt/dmdbms/data/DAMENG/bak/fullbak_tbs1: fullbak_tbs1.bak fullbak_tbs1.meta复制
基于全备做表空间增量备份
SQL> backup tablespace test_backup INCREMENT backupset 'incbak_tbs_1' BACKUPINFO '第一次增量表空间备份' maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2; 操作已执行 已用时间: 00:00:04.923. 执行号:511.复制
查看备份
SQL> select * from v$backupset WHERE BASE_NAME='FULLBAK_TBS1' AND DESC#='第一次增量表空间备份'; 1 DISK 1462098156 -1 TS_INCREMENT_20250530_174237_893445 /opt/dmdbms/data/DAMENG/bak/incbak_tbs_1 1 0 2 TEST_BACKUP 6 FULLBAK_TBS1 2025-05-30 17:42:42.764756 第一次增量表空间备份 0 1 0 0 67108864 52947 52966 0 1 1 0 0 0 134283815 0 4 0 0 16394 14 1462098156 已用时间: 42.190(毫秒). 执行号:518. SQL> host ls -RL /opt/dmdbms/data/DAMENG/bak/incbak_tbs_1 /opt/dmdbms/data/DAMENG/bak/incbak_tbs_1: incbak_tbs_1.meta复制
表备份
准备表
SQL> create table t2 (id int ,name varchar(20)); insert into t2 values (1,'a') ;操作已执行 已用时间: 22.210(毫秒). 执行号:2201. SQL> 影响行数 1 已用时间: 1.178(毫秒). 执行号:2202.复制
执行备份
SQL> backup table t2 backupname t1bak2 backupset 't2bak_1' BACKUPINFO '第一次备份表t2' maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1; 操作已执行 已用时间: 00:00:01.126. 执行号:2203.复制
查看生成的备份文件
SQL> select * from v$backupset where BACKUP_NAME='T1BAK2' ; 1 DISK 999656132 -1 T1BAK2 /opt/dmdbms/data/DAMENG/bak/t2bak_1 2 0 3 SYSDBA.T2 -1 2025-05-30 18:24:22.607580 第一次备份表t2 0 1 0 0 67108864 54518 55018 1 0 1 0 0 0 134283815 0 4 0 0 16394 14 999656132 已用时间: 104.576(毫秒). 执行号:2206. SQL> host ls -RL /opt/dmdbms/data/DAMENG/bak/t2bak_1 /opt/dmdbms/data/DAMENG/bak/t2bak_1: t2bak_1.bak t2bak_1.meta复制
归档备份
备份归档
SQL> backup archivelog all with backupdir '/opt/dmdbms/data/DAMENG/bak' backupname archbak1 backupset 'arachbakset_1' BACKUPINFO '第一次归档备份' maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2; backup archivelog all with backupdir '/opt/dmdbms/data/DAMENG/bak' backupname archbak1 backupset 'arachbakset_1' BACKUPINFO '第一次归档备份' maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2; [-718]:收集到的归档日志不连续. 已用时间: 00:00:02.835. 执行号:0. SQL> SQL> select checkpoint(100) ; 行号 CHECKPOINT(100) ---------- --------------- 1 0 已用时间: 19.306(毫秒). 执行号:2208.复制
查看归档信息
SQL> select ARCH_LSN, CLSN, PATH from V$ARCH_FILE; 行号 ARCH_LSN CLSN ---------- -------------------- -------------------- PATH ------------------------------------------------------------------ 1 52438 52571 /dmdata/arch/ARCHIVE_LOCAL1_0x543E1A30_EP0_2025-05-29_10-32-03.log 2 52686 52765 /dmdata/arch/ARCHIVE_LOCAL1_0x543E1A30_EP0_2025-05-30_16-57-52.log 3 52765 52802 /dmdata/arch/ARCHIVE_LOCAL1_0x543E1A30_EP0_2025-05-30_17-03-14.log 行号 ARCH_LSN CLSN ---------- -------------------- -------------------- PATH ------------------------------------------------------------------ 4 52803 55055 /dmdata/arch/ARCHIVE_LOCAL1_0x543E1A30_EP0_2025-05-30_17-07-20.log 已用时间: 11.525(毫秒). 执行号:2210. SQL> alter system switch logfile ; 操作已执行 已用时间: 3.800(毫秒). 执行号:0. SQL> alter system switch logfile ; 操作已执行 已用时间: 1.610(毫秒). 执行号:0. SQL> SQL> select ARCH_LSN, CLSN, PATH from V$ARCH_FILE; 行号 ARCH_LSN CLSN ---------- -------------------- -------------------- PATH ------------------------------------------------------------------ 1 52438 52571 /dmdata/arch/ARCHIVE_LOCAL1_0x543E1A30_EP0_2025-05-29_10-32-03.log 2 52686 52765 /dmdata/arch/ARCHIVE_LOCAL1_0x543E1A30_EP0_2025-05-30_16-57-52.log 3 52765 52802 /dmdata/arch/ARCHIVE_LOCAL1_0x543E1A30_EP0_2025-05-30_17-03-14.log 行号 ARCH_LSN CLSN ---------- -------------------- -------------------- PATH ------------------------------------------------------------------ 4 52803 55055 /dmdata/arch/ARCHIVE_LOCAL1_0x543E1A30_EP0_2025-05-30_17-07-20.log 5 55055 55063 /dmdata/arch/ARCHIVE_LOCAL1_0x543E1A30_EP0_2025-05-30_18-30-44.log 6 55064 55067 /dmdata/arch/ARCHIVE_LOCAL1_0x543E1A30_EP0_2025-05-30_18-32-04.log 6 rows got复制
基于LSN做归档备份
SQL> BACKUP ARCHIVELOG LSN BETWEEN 52803 AND 55067 BACKUPSET '/opt/dmdbms/data/DAMENG/bak/arch_bak_time_31'; 操作已执行 已用时间: 00:00:05.039. 执行号:2212. SQL> SQL> SQL> select * from v$backupset where BACKUP_PATH='/opt/dmdbms/data/DAMENG/bak/arch_bak_time_31'; 行号 DEVICE_TYPE BACKUP_ID PARENT_ID BACKUP_NAME BACKUP_PATH TYPE LEVEL RANGE# OBJECT_NAME ---------- ----------- ----------- ----------- -------------------------------- -------------------------------------------- ----------- ----------- ----------- ----------- OBJECT_ID BASE_NAME BACKUP_TIME DESC# ENCRYPT_TYPE COMPRESS_LEVEL WITHOUT_LOG USE_PWR PKG_SIZE BEGIN_LSN END_LSN ----------- --------- -------------------------- ----- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- BKP_NUM DBF_NUM PARALLEL_NUM BAKSET_TYPE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE MIN_DCT_VER DDL_CLONE BAK_MAGIC VERSION SUB_VERSION ----------- ----------- ------------ ----------- ----------- -------------------- ------------ ----------- ----------- ----------- ----------- ----------- ----------- BAKSET_MAGIC ------------ 1 DISK 1978987098 -1 ARCH_FULL_20250530_183233_084451 /opt/dmdbms/data/DAMENG/bak/arch_bak_time_31 3 0 4 DAMENG -1 2025-05-30 18:32:37.998642 0 0 1 0 67108864 52803 55091 1 3 1 0 0 0 134283815 0 4 0 0 16394 14 1978987098 已用时间: 43.136(毫秒). 执行号:2503.复制
加密备份
参数
IDENTIFIED BY... 指定加密密码； WITH ENCRYPTION... 指定加密类型 ENCRYPT WITH... 指定加密算法复制
执行备份
SQL> BACKUP DATABASE BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_bak_for_encrypt_01' IDENTIFIED BY "Wangyang23" WITH ENCRYPTION 2 ENCRYPT WITH RC4 ; 操作已执行 已用时间: 00:00:06.033. 执行号:2504. SQL> SQL> select * from v$backupset where BACKUP_PATH='/opt/dmdbms/data/DAMENG/bak/db_bak_for_encrypt_01' ; 行号 DEVICE_TYPE BACKUP_ID PARENT_ID BACKUP_NAME BACKUP_PATH TYPE LEVEL RANGE# OBJECT_NAME ---------- ----------- ----------- ----------- ------------------------------ ------------------------------------------------- ----------- ----------- ----------- ----------- OBJECT_ID BASE_NAME BACKUP_TIME DESC# ENCRYPT_TYPE COMPRESS_LEVEL WITHOUT_LOG USE_PWR PKG_SIZE BEGIN_LSN END_LSN ----------- --------- -------------------------- ----- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- BKP_NUM DBF_NUM PARALLEL_NUM BAKSET_TYPE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE MIN_DCT_VER DDL_CLONE BAK_MAGIC VERSION SUB_VERSION ----------- ----------- ------------ ----------- ----------- -------------------- ------------ ----------- ----------- ----------- ----------- ----------- ----------- BAKSET_MAGIC ------------ 1 DISK 133525638 -1 DB_FULL_20250530_190136_330606 /opt/dmdbms/data/DAMENG/bak/db_bak_for_encrypt_01 0 0 1 DAMENG -1 2025-05-30 19:01:42.302236 2 0 0 0 67108864 55096 55142 2 6 1 0 0 0 134283815 0 4 0 0 16394 14 133525638 已用时间: 81.710(毫秒). 执行号:2505. SQL>复制
联机恢复
联机状态下，只能进行表的备份恢复，库和表空间仅能在脱机环境下通过RMAN还原。
检查备份
SQL> SELECT SF_BAKSET_CHECK('DISK',' /opt/dmdbms/data/DAMENG/bak/t2bak_1'); 行号 SF_BAKSET_CHECK('DISK','/opt/dmdbms/data/DAMENG/bak/t2bak_1') ---------- ------------------------------------------------------------- 1 1 已用时间: 143.996(毫秒). 执行号:701.复制
清空表数据
SQL> truncate table t2; 操作已执行 已用时间: 47.490(毫秒). 执行号:708.复制
执行表结构还原
SQL> RESTORE TABLE sysdba.T2 STRUCT FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/t2bak_1'; 操作已执行 已用时间: 128.482(毫秒). 执行号:705.复制
执行数据还原
SQL> RESTORE TABLE sysdba.T2 FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/t2bak_1'; 操作已执行 已用时间: 269.658(毫秒). 执行号:706.复制
查看数据
SQL> select * from t2; 行号 ID NAME ---------- ----------- ---- 1 1 a 已用时间: 3.305(毫秒). 执行号:707. SQL>复制
备份集管理命令介绍：
SF_BAKSET_BACKUP_DIR_ADD ：添加备份目录。 SF_BAKSET_BACKUP_DIR_REMOVE ：删除内存中指定的备份目录。 SF_BAKSET_BACKUP_DIR_REMOVE_ALL ：删除内存中全部的备份目录。 SF_BAKSET_CHECK ：对备份集进行校验。 SF_BAKSET_REMOVE ：删除指定设备类型和指定备份集目录的备份集。 SF_BAKSET_REMOVE_BATCH ：批量删除满足指定条件的所有备份集。 SF_BAKSET_REMOVE_BATCH_S ：批量安全删除满足指定条件的所有库级备份集。 SF_BAKSET_REMOVE_BATCH_N ：批量删除满足指定条件的所有备份集，并保留用户指定个数的库级完全备份集。 SP_DB_BAKSET_REMOVE_BATCH ：批量删 除指定时间之前的数据库备份集。 SP_TS_BAKSET_REMOVE_BATCH ：批量删除指定表空间对象及指定时间之前的表空间备份集。 SP_TAB_BAKSET_REMOVE_BATCH ：批量删除指定表对象及指定时间之前的表备份集。 SP_ARCH_BAKSET_REMOVE_BATCH ：批量删除指定条件的归档备份集。 V$BACKUPSET ：显示备份集基本信息。 V$BACKUPSET_DBINFO ：显示备份集的数据库相关信息。 V$BACKUPSET_DBF ：显示备份 集中数据文件的相关信息。 V$BACKUPSET_ARCH ：显示备份集的归档信息。 V$BACKUPSET_BKP ：显示备份集的备份片信息。 V$BACKUPSET_SEARCH_DIRS ：显示备份集搜索目录。 V$BACKUPSET_TABLE ：显示表备份集中备份表信息。 V$BACKUPSET_SUBS ：显示并行备份中生成的子备份集信息。 V$BACKUP_MONITOR ：显示当前备份任务实时监控信息。 V$BACKUP_HISTORY ：显示最近 100 条备份监控信息。 V$BACKUP_FILES ：显示当前备份任务待备份数据文件列表。复制
脱机备份还原
DMRMAN介绍
DMRMAN工具是DM数据库的脱机备份恢复管理工具，是数据库安装包中的一个工具，无需专门部署，可以用于数据库在脱机状态下的备份恢复。联机状态下仅能使用disql来进行操作。
使用 DMRMAN 工具可以将数据库中的一个或多个数据文件、数据库信息等备份到一个称为“备份集”的逻辑结构中，备份集的格式是特定的，只能由 DMRMAN 创建和访问。
操作DMRMAN
启停
[dmdba@localhost ~]$ dmrman dmrman V8 RMAN> exit time used: 5.083(ms) [dmdba@localhost ~]$复制
配置信息操作管理
[dmdba@localhost ~]$ dmrman dmrman V8 RMAN> help 备份集版本: 0x400A 子版本: 0xE 书写格式: ./dmrman KEYWORD=<value> {KEYWORD=<value>} 其中{}表示大括号内的关键词可为0~N个，N为正整数，{}本身无需出现在语句中 使用示例: ./dmrman CTLFILE=/opt/dm7data/dameng/res_ctl.txt ---------------------------------------------------------------------------------------------------------------------------------------------- 关键词（KEYWORD） 说明 缺省值 是否必选项 ---------------------------------------------------------------------------------------------------------------------------------------------- CTLFILE 指定执行语句所在的文件路径 无 否 CTLSTMT 指定待执行语句 无 否 DCR_INI 指定dmdcr.ini路径；若未指定且当前目录中dmdcr.ini存在，则使用当前目录中的dmdcr.ini 无 否 可单独使用，也可与其他参数配合使用 USE_AP 指定备份、还原执行载体，1/2：DMAP/进程自身 默认是DMAP 否 AP_PORT 连接AP时用的端口号, 可单独使用, 也可与其他参数配合使用 默认是4236 否 CFG_PATH 指定CFG配置文件路径 无 否 HELP 打印帮助信息 无 否 ---------------------------------------------------------------------------------------------------------------------------------------------- time used: 1.853(ms) RMAN>复制
配置
RMAN> configure DEVICE TYPE: DISK DEVICE PARMS: FORMAT: DESTINATION: TRACE FILE: TRACE LEVEL: 1 OPEN FILES: 0 MAXPIECESIZE: 131072 READ SPEED: 0 WRITE SPEED: 0 TASK THREAD: 4 PARALLEL: 1 READ SIZE: 1024 PARALLEL POLICY: 1 PACKAGE SIZE: 64 PACKAGE CRC: ENABLE PACKAGE CHECK: ENABLE POOL SIZE: 1024 time used: 1.926(ms) RMAN>复制
修改配置
RMAN> CONFIGURE DEFAULT DEVICE TYPE TAPE PARMS 'command'; time used: 1.463(ms) RMAN> configure DEVICE TYPE: TAPE DEVICE PARMS: command FORMAT: DESTINATION: TRACE FILE: TRACE LEVEL: 1 OPEN FILES: 0 MAXPIECESIZE: 131072 READ SPEED: 0 WRITE SPEED: 0 TASK THREAD: 4 PARALLEL: 1 READ SIZE: 1024 PARALLEL POLICY: 1 PACKAGE SIZE: 64 PACKAGE CRC: ENABLE PACKAGE CHECK: ENABLE POOL SIZE: 1024 time used: 2.587(ms) RMAN> CONFIGURE DEFAULT DEVICE TYPE DISK ; time used: 2.014(ms) RMAN> CONFIGURE DEFAULT TRACE FILE '/opt/dmdbms/data/DAMENG/bak/trace.log' TRACE LEVEL 2; time used: 5.477(ms) RMAN> configure DEVICE TYPE: DISK DEVICE PARMS: FORMAT: DESTINATION: TRACE FILE: /opt/dmdbms/data/DAMENG/bak/trace.log TRACE LEVEL: 2 OPEN FILES: 0 MAXPIECESIZE: 131072 READ SPEED: 0 WRITE SPEED: 0 TASK THREAD: 4 PARALLEL: 1 READ SIZE: 1024 PARALLEL POLICY: 1 PACKAGE SIZE: 64 PACKAGE CRC: ENABLE PACKAGE CHECK: ENABLE POOL SIZE: 1024 time used: 3.058(ms) RMAN>复制
脱机备份
关闭数据库
[root@localhost tmp]# systemctl stop DmServiceDMSERVER.service复制
完全备份
RMAN> BACKUP DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FULL BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_01'; BACKUP DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FULL BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_01'; Processing backupset /opt/dmdbms/data/DAMENG/bak/db_full_bak_01 [Percent:100.00%][Speed:0.00M/s][Cost:00:00:04][Remaining:00:00:00] backup successfully! time used: 00:00:05.950 RMAN>复制
增量备份
RMAN> BACKUP DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' INCREMENT WITH BACKUPDIR '/opt/dmdbms/data/DAMENG/bak' BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_increment_bak_02'; BACKUP DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' INCREMENT WITH BACKUPDIR '/opt/dmdbms/data/DAMENG/bak' BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_increment_bak_02'; Processing backupset /opt/dmdbms/data/DAMENG/bak/db_increment_bak_02 [Percent:100.00%][Speed:0.00M/s][Cost:00:00:04][Remaining:00:00:00] backup successfully! time used: 00:00:05.453 RMAN> RMAN> RMAN>复制
归档备份
RMAN> BACKUP ARCHIVE LOG ALL DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' BACKUPSET '/opt/dmdbms/data/DAMENG/bak/arch_all_bak_01'; BACKUP ARCHIVE LOG ALL DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' BACKUPSET '/opt/dmdbms/data/DAMENG/bak/arch_all_bak_01'; ==============================Error Stack============================== DM[-718]:收集到的归档日志不连续 ======================================================================= RMAN> BACKUP ARCHIVE LOG LSN BETWEEN 52803 AND 55055 DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' BACKUPSET '/opt/dmdbms/data/DAMENG/bak/arch_lsn_bak_02'; BACKUP ARCHIVE LOG LSN BETWEEN 52803 AND 55055 DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' BACKUPSET '/opt/dmdbms/data/DAMENG/bak/arch_lsn_bak_02'; Processing backupset /opt/dmdbms/data/DAMENG/bak/arch_lsn_bak_02 [Percent:100.00%][Speed:0.00M/s][Cost:00:00:03][Remaining:00:00:00] [Percent:100.00%][Speed:0.00M/s][Cost:00:00:04][Remaining:00:00:00] backup successfully! time used: 00:00:05.193 RMAN> RMAN> RMAN>复制
脱机备份管理
备份集查看
RMAN> show backupset '/opt/dmdbms/data/DAMENG/bak/db_full_bak_01';复制
查看指定目录下所有备份集信息。
RMAN> SHOW BACKUPSETS WITH BACKUPDIR '/opt/dmdbms/data/DAMENG/bak';复制
查看部分备份集信息,DB、META、FILE、TABLESPACE、TABLE
RMAN>SHOW BACKUPSETS WITH BACKUPDIR '/opt/dmdbms/data/DAMENG/bak' USE DB_MAGIC 1413356080;复制
查看备份的元数据信息
RMAN> show backupset '/opt/dmdbms/data/DAMENG/bak/db_full_bak_01' info meta复制
备份集校验
RMAN>CHECK BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_01';复制
备份集删除
RMAN>REMOVE BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_01'; RMAN> REMOVE BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_01'; REMOVE BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_01'; ==============================Error Stack============================== DM[-8202]:[/opt/dmdbms/data/DAMENG/bak/db_increment_bak_02]的基备份，不能删除 ======================================================================= RMAN> REMOVE BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_increment_bak_02'; REMOVE BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_increment_bak_02'; remove backupset successfully. time used: 54.745(ms) RMAN>复制
删除备份目录下的某一个数据库备份集
RMAN>REMOVE BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_01' DATABASE '/opt/dmdbms/data/DAMENG/dm.ini'; REMOVE BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_01' DATABASE '/opt/dmdbms/data/DAMENG/dm.ini'; remove backupset successfully. time used: 45.766(ms)复制
删除整个目录下的备份
RMAN>REMOVE BACKUPSETS WITH BACKUPDIR '/opt/dmdbms/data/DAMENG/bak'; REMOVE BACKUPSETS WITH BACKUPDIR '/opt/dmdbms/data/DAMENG/bak'; remove backupsets successfully. time used: 51.866(ms)复制
基于时间删除
RMAN>REMOVE BACKUPSETS WITH BACKUPDIR '/opt/dmdbms/data/DAMENG/bak' UNTIL TIME '2025-06-03 13:38:00'; REMOVE BACKUPSETS WITH BACKUPDIR '/opt/dmdbms/data/DAMENG/bak' UNTIL TIME '2025-06-03 13:38:00'; remove backupsets successfully. time used: 39.720(ms)复制
脱机还原恢复全库
还原数据库
RMAN> RESTORE DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_for_restore'; RESTORE DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_for_restore'; [Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00] restore successfully. time used: 00:00:03.230 RMAN>复制
基于源参数进行还原
RMAN> RESTORE DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' REUSE DMINI FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_for_restore'; RESTORE DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' REUSE DMINI FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_for_restore'; [Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00] restore successfully. time used: 00:00:02.641复制
恢复日志
RMAN> RECOVER DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_for_restore'; RECOVER DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_for_restore'; ==============================Error Stack============================== DM[603]:备份集[/opt/dmdbms/data/DAMENG/bak/db_full_bak_for_restore]备份过程中未产生日志 ======================================================================= recover successfully! time used: 00:00:01.225复制
更新魔数
RMAN> RECOVER DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' UPDATE DB_MAGIC; RECOVER DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' UPDATE DB_MAGIC; recover successfully! time used: 00:00:01.224 RMAN>复制
增量备份还原
联机全备
SQL> BACKUP DATABASE FULL BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_01'; 操作已执行 已用时间: 00:00:09.404. 执行号:501.复制
造测试数据
SQL> SQL> CREATE TABLE TAB_FOR_RECOVER_03(C1 INT); BEGIN FOR I IN 1..1000 LOOP INSERT INTO TAB_FOR_RECOVER_03 VALUES(I); COMMIT; END LOOP; END; / 操作已执行 已用时间: 13.072(毫秒). 执行号:502. SQL> 2 3 4 5 6 7 8 9 10 11 12 13 DMSQL 过程已成功完成 已用时间: 842.187(毫秒). 执行号:503.复制
联机增量备份
SQL> BACKUP DATABASE INCREMENT WITH BACKUPDIR '/opt/dmdbms/data/DAMENG/bak' BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_increment_bak_02'; 操作已执行 已用时间: 00:00:09.294. 执行号:504. SQL> SQL>复制
删除表
SQL> drop table TAB_FOR_RECOVER_03 ; 操作已执行 已用时间: 74.596(毫秒). 执行号:505. SQL> commit ; 操作已执行 已用时间: 0.486(毫秒). 执行号:506. SQL> exit复制
进行数据不完全恢复（不加归档）
RMAN> RESTORE DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_01'; RESTORE DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_01'; [Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00] restore successfully. time used: 00:00:02.871 RMAN> RESTORE DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_increment_bak_02'; RESTORE DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_increment_bak_02'; [Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00] restore successfully. time used: 00:00:02.759 RMAN> RECOVER DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_increment_bak_02'; RECOVER DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_increment_bak_02'; [Percent:100.00%][Speed:6390.00PKG/s][Cost:00:00:00][Remaining:00:00:00] recover successfully! time used: 00:00:03.239 RMAN> RECOVER DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' UPDATE DB_MAGIC; RECOVER DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' UPDATE DB_MAGIC; recover successfully! time used: 00:00:01.341 RMAN>复制
启动数据库查看数据
[root@localhost tmp]# systemctl start DmServiceDMSERVER.service su - dmdba disql sysdba/Dameng123 [root@localhost tmp]# su - dmdba 上一次登录：二 6月 3 15:50:03 CST 2025pts/0 上 s[dmdba@localhost ~]$ disql sysdba/Dameng123 服务器[LOCALHOST:5236]:处于普通打开状态 登录使用时间 : 7.515(ms) disql V8 SQL SQL> select count(*) from TAB_FOR_RECOVER_03 ; 行号 COUNT(*) ---------- -------------------- 1 1000 已用时间: 2.814(毫秒). 执行号:601. SQL> SQL>复制
基于归档做完全恢复
RMAN> RESTORE DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_01'; RESTORE DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_full_bak_01'; [Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00] restore successfully. time used: 00:00:02.649 RMAN> RESTORE DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_increment_bak_02'; RESTORE DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_increment_bak_02'; [Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00] restore successfully. time used: 00:00:02.676 RMAN> RECOVER DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_increment_bak_02'; RECOVER DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/opt/dmdbms/data/DAMENG/bak/db_increment_bak_02'; file dm.key not found, use default license! [Percent:100.00%][Speed:2257.43PKG/s][Cost:00:00:00][Remaining:00:00:00] recover successfully! time used: 00:00:04.242 RMAN> RMAN> RECOVER DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' WITH ARCHIVEDIR '/dmdata/arch'; RECOVER DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' WITH ARCHIVEDIR '/dmdata/arch'; [Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00] recover successfully! time used: 00:00:01.226 RMAN> RECOVER DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' UPDATE DB_MAGIC; RECOVER DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' UPDATE DB_MAGIC; recover successfully! time used: 00:00:01.233复制
启动数据库查看数据
[root@localhost tmp]# systemctl start DmServiceDMSERVER.service [root@localhost tmp]# su - dmdba 上一次登录：二 6月 3 15:39:08 CST 2025pts/0 上 [dmdba@localhost ~]$ disql sysdba/Dameng123 服务器[LOCALHOST:5236]:处于普通打开状态 登录使用时间 : 5.638(ms) disql V8 SQL> select count(*) from TAB_FOR_RECOVER_03 ; select count(*) from TAB_FOR_RECOVER_03 ; 第1 行附近出现错误[-2106]:无效的表或视图名[TAB_FOR_RECOVER_03]. 已用时间: 2.297(毫秒). 执行号:0. SQL> quit复制
总结
DM数据库备份，主要是将数据库的已经使用的有效数据页拷贝到备份中，然后备份期间的日志会正常记录到REDO以及归档中，恢复的时候，使用备份+归档，就可以将数据库恢复到最新状态，也可以使用备份+部分归档或者不加归档进行不完全恢复。
DM数据库的备份有两种模式，一种是通过disql进行联机备份，一种是通过DMRMAN进行脱机备份，备份主要通过disql进行联机备份。
DM数据库在恢复方面，表的备份恢复可以在联机状态下进行。数据库以及表空间级别的备份可以在联机状态下进行，恢复必须在脱机状态下进行。
通过DMRMAN，可以对数据库进行备份校验、备份集导出、基于时间点/LSN等方式还原，并且还可以对增量备份进行合并、归档修复等操作。
本次测试对联机备份和DMRMAN备份恢复进行了简单测试，验证了整体流程。
文章转载自达梦产品与服务，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
