热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
登录
注册
DM8数据库备份恢复
技术分享/
文章详情
/
DM8数据库备份恢复
赵国伟
2025/05/16
59
0
0
一、备份还原原理
DM 备份的本质就是从数据库文件中拷贝有效的数据页保存到备份集中，这里的有效数据页包括数据文件的描述页和被分配使用的数据页。而在备份的过程中，如果数据库系统还在继续运行，这期间的数据库操作并不是都会立即体现到数据文件中，而是首先以日志的形式写到归档日志中，因此，为了保证用户可以通过备份集将数据恢复到备份结束时间点的状态，就需要将备份过程中产生的归档日志也保存到备份集中。
还原与恢复是备份的逆过程。还原是将备份集中的有效数据页重新写入目标数据文件的过程。恢复则是指通过重做归档日志，将数据库状态恢复到备份结束时的状态；也可以恢复到指定时间点和指定 LSN。恢复结束以后，数据库中可能存在处于未提交状态的活动事务，这些活动事务在恢复结束后的第一次数据库系统启动时，会由 DM 数据库自动进行回滚。
二、备份还原实战
2.1支持与限制
联机备份
对联机备份的支持与限制：
1)MPP 环境仅允许库和归档备份，且各节点都会执行，生成相应的备份集，支持DDL CLONE；
2)DSC 环境支持库备份、表空间备份和表备份，要求 DSC 环境的所有节点都处于OPEN状态；
3) MOUNT 状态所有备份均不支持；
4) SUSPEND 状态所有备份均不支持；
5) OPEN 状态支持所有备份，支持 DDL CLONE；
6) PRIMARY 模式支持所有备份，支持 DDL CLONE；
7) STANDBY 模式仅支持库级、表空间级和归档备份，支持 DDL CLONE；
8) DDL CLONE 必须备份归档，不允许指定 WITHOUT LOG。
联机还原
仅支持表级还原，对联机还原的支持与限制
1)MPP 和分布式数据库不支持；
2)PRIMARY 支持；
3)SUSPEND 状态所有还原均不支持；
4)OPEN/NORMAL 支持。
脱机备份
脱机备份支持库级和归档备份。
1)MPP 视同单机环境，仅当前节点执行备份操作；
2)允许异常退出后备份，支持 DDL_CLONE；
3)DSC 支持库级备份，支持 DDL_CLONE；当 DSC 环境下正常节点的 CKPT_LSN 小于故障节点的 CKPT_LSN 时，不支持脱机备份。
脱机还原
脱机还原跟目标库所处的模式、状态以及集群环境（MPP 和 DSC）无关，允许库级、表空间级和归档还原。在一般的应用场景中，常规性的数据库维护工作，即在不影响数据库正常运行的情况下，建议定期执行联机数据库备份，且完全备份和增量备份结合使用。执行两次完全备份的时间间隔可以尽量长一点，在两次完全备份之间执行一定数量的增量备份，比如，可以选择每周执行一次完全备份，一周内每天执行一次增量备份。为了尽量减少对数据库正常工作的影响，建议备份时间，选择在工作量较少的时间，比如深夜。
2.2开启归档
– 联机配置归档
SQL> alter database mount;
executed successfully
used time: 5.567(ms). Execute id is 0.
SQL> alter database archivelog;
executed successfully
used time: 45.013(ms). Execute id is 0.
SQL> alter database add archivelog 'dest=/data/DM/arch,type=local,file_size=64,space_limit=10240';
executed successfully
used time: 3.529(ms). Execute id is 0.
SQL> alter database open;
executed successfully
used time: 13.938(ms). Execute id is 0.
SQL> alter system switch logfile;
executed successfully
used time: 6.869(ms). Execute id is 0.
SQL> select * from v$dm_arch_ini;
LINEID 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
--------------- ----------------- ----------------- -------------- ------------- ------------------- ------------------------- ------------------
1 ARCHIVE_LOCAL1 LOCAL /data/DM/arch 64 10240 1 NULL Y NULL NULL
/data/DM/arch 2 0 0 0 0 NULL NULL NULL
NULL NULL NULL NULL NULL NULL NULL NULL
used time: 17.572(ms). Execute id is 6.
SQL> select * from v$arch_status;
LINEID ARCH_TYPE ARCH_DEST ARCH_STATUS ARCH_SRC
---------- --------- ------------- ----------- --------
1 LOCAL /data/DM/arch VALID DMSERVER
used time: 10.888(ms). Execute id is 7.
SQL>
– 手动配置归档
[dmdba@dm-standalone DM]$ vim dmarch.ini
#DaMeng Database Archive Configuration file
#this is comments
ARCH_WAIT_APPLY = 0
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /data/DM/arch
ARCH_FILE_SIZE = 64
ARCH_SPACE_LIMIT = 10240
ARCH_FLUSH_BUF_SIZE = 2
ARCH_HANG_FLAG = 1
[dmdba@dm-standalone DM]$
2.3联机备份还原
2.3.1 数据库备份
– 全库备份
SQL> backup database full with backupdir '/data/DM/bak' backupname fullbak1 backupset 'backset_1' BACKUPINFO '第一次全备份'
2 maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2;
executed successfully
used time: 00:00:08.862. Execute id is 1205.
SQL>
– 查看生成的备份文件
[dmdba@dm-standalone bak]$ ls -RL backset_1/
backset_1/:
backset_1_0 backset_1_1 backset_1.meta
backset_1/backset_1_0:
backset_1_0.bak backset_1_0.meta
backset_1/backset_1_1:
backset_1_1.bak backset_1_1.meta
[dmdba@dm-standalone bak]$
– 全库增量备份
SQL> backup database INCREMENT with backupdir '/data/DM/bak' backupname incbak1 backupset 'incbak_1' BACKUPINFO '第一次增量备份'
2 maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2;
executed successfully
used time: 00:00:06.803. Execute id is 1207.
SQL>
– 查看生成的备份文件
[dmdba@dm-standalone bak]$ ls -RL incbak_1/
incbak_1/:
incbak_1_0 incbak_1_1 incbak_1.bak incbak_1.meta
incbak_1/incbak_1_0:
incbak_1_0.meta
incbak_1/incbak_1_1:
incbak_1_1.bak incbak_1_1.meta
[dmdba@dm-standalone bak]$
– 表空间备份
SQL> backup tablespace tbs_idx_test full with backupdir '/data/DM/bak' backupname fullbak1 backupset 'tbs_backset_1' BACKUPINFO '第一次表空间备份'
2 maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2;
executed successfully
used time: 00:00:05.012. Execute id is 1206.
SQL>
– 查看生成的备份文件
[dmdba@dm-standalone bak]$ ls -RL tbs_backset_1/
tbs_backset_1/:
tbs_backset_1.bak tbs_backset_1.meta
[dmdba@dm-standalone bak]$
– 表备份
SQL> backup table t2 backupname t1bak2 backupset 't2bak_1' BACKUPINFO '第一次备份表t2'
2 maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1;
executed successfully
used time: 00:00:01.091. Execute id is 1209.
SQL>
– 查看生成的备份文件
[dmdba@dm-standalone bak]$ ls -RL t2bak_1/
t2bak_1/:
t2bak_1.bak t2bak_1.meta
[dmdba@dm-standalone bak]$
– 归档备份
SQL> backup archivelog all with backupdir '/data/DM/bak' backupname archbak1 backupset 'arachbakset_1' BACKUPINFO '第一次归档备份'
2 maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2;
executed successfully
used time: 00:00:06.938. Execute id is 1210.
SQL>
– 查看生成的备份文件
[dmdba@dm-standalone bak]$ ls -RL arachbakset_1/
arachbakset_1/:
arachbakset_1_0 arachbakset_1_1 arachbakset_1.meta
arachbakset_1/arachbakset_1_0:
arachbakset_1_0.bak arachbakset_1_0.meta
arachbakset_1/arachbakset_1_1:
arachbakset_1_1.bak arachbakset_1_1.meta
[dmdba@dm-standalone bak]$
2.3.2 备份集管理
– 查看备份搜索路径
SQL> select * from V$BACKUPSET_SEARCH_DIRS
2 ;
LINEID DIR
---------- ------------
1 /data/DM/bak
used time: 9.670(ms). Execute id is 1211.
SQL>
– 目录添加删除
SQL> SF_BAKSET_BACKUP_DIR_ADD('DISK','/data/DM/bak2');
DMSQL executed successfully
used time: 4.291(ms). Execute id is 1212.
SQL> select * from V$BACKUPSET_SEARCH_DIRS;
LINEID DIR
---------- -------------
1 /data/DM/bak2
2 /data/DM/bak
used time: 5.402(ms). Execute id is 1213.
SQL> SF_BAKSET_BACKUP_DIR_REMOVE('DISK','/data/DM/bak2');
DMSQL executed successfully
used time: 5.182(ms). Execute id is 1214.
SQL> select * from V$BACKUPSET_SEARCH_DIRS;
LINEID DIR
---------- ------------
1 /data/DM/bak
used time: 0.375(ms). Execute id is 1215.
SQL> SQL> SF_BAKSET_BACKUP_DIR_REMOVE_ALL();
DMSQL executed successfully
used time: 5.078(ms). Execute id is 1216.
SQL> select * from V$BACKUPSET_SEARCH_DIRS;
LINEID DIR
---------- ------------
1 /data/DM/bak
used time: 1.978(ms). Execute id is 1217.
SQL>
– 备份集校验与删除
SQL> SF_BAKSET_CHECK('DISK','/data/DM/bak/backset_1');
DMSQL executed successfully
used time: 106.749(ms). Execute id is 1218.
SQL>
SQL> SF_BAKSET_REMOVE('DISK','/data/DM/bak/backset_1',1);
DMSQL executed successfully
used time: 57.499(ms). Execute id is 1219.
SQL>
– 安全删除满足条件的备份
SQL> SF_BAKSET_REMOVE_BATCH ('DISK', now(), NULL, NULL);
DMSQL executed successfully
used time: 40.377(ms). Execute id is 1221.
SQL>
–安全删除满足条件的备份并保留最近一份完整备份
SQL> SF_BAKSET_REMOVE_BATCH_S('DISK', now(), NULL, NULL);
DMSQL executed successfully
used time: 32.417(ms). Execute id is 1222.
SQL>
–安全删除满足条件的备份并保留最近2份完整备份
SQL> SF_BAKSET_REMOVE_BATCH_N('DISK', now(), NULL, NULL,2);
DMSQL executed successfully
used time: 32.198(ms). Execute id is 1223.
– 删除满足条件的数据库备份
SQL> SP_DB_BAKSET_REMOVE_BATCH('DISK', now());
DMSQL executed successfully
used time: 39.514(ms). Execute id is 1224.
– 删除满足条件的表空间备份
SQL> SP_TS_BAKSET_REMOVE_BATCH('DISK', now(),'ts1');
DMSQL executed successfully
used time: 32.869(ms). Execute id is 1225.
– 删除满足条件的表备份
SQL> SP_TAB_BAKSET_REMOVE_BATCH('DISK', now(),'sysdba','t1');
DMSQL executed successfully
used time: 39.276(ms). Execute id is 1226.
– 删除满足条件的归档备份
SQL> SP_ARCH_BAKSET_REMOVE_BATCH('DISK', now());
DMSQL executed successfully
used time: 31.021(ms). Execute id is 1227.
SQL>
2.3.3 备份集查看
– 查看备份集基本信息
SQL> select * from V$BACKUPSET;
LINEID 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 745643445 -1 FULLBAK1 /data/DM/bak/backset_1 0 0 1 DM -1
2025-04-18 01:29:02.127492 第一次全备份 0 1 0 0 67108864 54175 54183
3 6 2 0 0 0 134283815 0 4 0 0 16394
14 745643445
LINEID 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
----------- ------------
2 DISK 1450475777 745643445 FULLBAK1 /data/DM/bak/backset_1/backset_1_1 0 0 1 DM -1
2025-04-18 01:28:59.760494 第一次全备份 0 1 0 0 67108864 54175 0
1 3 1 0 0 0 134283815 0 4 0 0 16394
14 1450475777
LINEID 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
----------- ------------
3 DISK 1274267694 745643445 FULLBAK1 /data/DM/bak/backset_1/backset_1_0 0 0 1 DM -1
2025-04-18 01:29:00.071381 第一次全备份 0 1 0 0 67108864 54175 0
1 2 1 0 0 0 134283815 0 4 0 0 16394
14 1274267694
used time: 33.644(ms). Execute id is 1230.
SQL>
– 查看备份集数据库信息
SQL> select * from V$BACKUPSET_DBINFO;
LINEID DEVICE_TYPE BACKUP_ID BACKUP_NAME BACKUP_PATH EXTENT_SIZE PAGE_SIZE LOG_PAGE_SIZE CASE_SENSITVE DB_MAGIC PMNT_MAGIC UNICODE_FLAG
---------- ----------- ----------- ----------- ---------------------- ----------- ----------- ------------- ------------- ----------- ----------- ------------
DB_VERSION GLOBAL_VERSION ENABLE_POLICY ARCH_FLAG DSC_NODE PAGE_CHECK RLOG_ENCRYPT EX_CIPHER_NAME EX_CIPHER_ID EX_HASH_NAME EX_HASH_ID
----------- -------------- ------------- ----------- ----------- ----------- ------------ -------------- ------------ ------------ -----------
LENGTH_IN_CHAR USE_NEW_HASH BLANK_PAD_MODE SRC_DB_MAGIC DPC_MAGIC
-------------- ------------ -------------- ------------ -----------
1 DISK 745643445 FULLBAK1 /data/DM/bak/backset_1 16 16384 512 1 460641410 1606286038 0
458765 V8 0 1 1 3 0 0 0
0 1 0 460641410 0
used time: 36.570(ms). Execute id is 1231.
SQL>
– 显示备份集数据库文件信息
SQL> select * from V$BACKUPSET_DBF;
LINEID DEVICE_TYPE BACKUP_ID BACKUPNAME BACKUPPATH FILE_SEQ TS_ID FILE_ID TS_STATE TS_NAME
---------- ----------- ----------- ---------- ---------------------------------- ----------- ----------- ----------- ----------- ------------
FILE_NAME MIRROR_PATH FILE_LEN MAX_LIMIT_SIZE AUTO_EXTEND NEXT_SIZE START_BKP_SEQ START_BKP_OFF END_BKP_SEQ
------------------------- ----------- -------------------- -------------- ----------- ----------- ------------- -------------------- -----------
END_BKP_OFF
--------------------
1 DISK 1450475777 FULLBAK1 /data/DM/bak/backset_1/backset_1_1 2 1 0 0 ROLL
/data/DM/ROLL.DBF 134217728 0 1 0 NULL NULL NULL
NULL
LINEID DEVICE_TYPE BACKUP_ID BACKUPNAME BACKUPPATH FILE_SEQ TS_ID FILE_ID TS_STATE TS_NAME
---------- ----------- ----------- ---------- ---------------------------------- ----------- ----------- ----------- ----------- ------------
FILE_NAME MIRROR_PATH FILE_LEN MAX_LIMIT_SIZE AUTO_EXTEND NEXT_SIZE START_BKP_SEQ START_BKP_OFF END_BKP_SEQ
------------------------- ----------- -------------------- -------------- ----------- ----------- ------------- -------------------- -----------
END_BKP_OFF
--------------------
2 DISK 1450475777 FULLBAK1 /data/DM/bak/backset_1/backset_1_1 4 5 0 0 TBS_TEST
/data/DM/tbs_test.dbf 104857600 0 1 0 NULL NULL NULL
NULL
LINEID DEVICE_TYPE BACKUP_ID BACKUPNAME BACKUPPATH FILE_SEQ TS_ID FILE_ID TS_STATE TS_NAME
---------- ----------- ----------- ---------- ---------------------------------- ----------- ----------- ----------- ----------- ------------
FILE_NAME MIRROR_PATH FILE_LEN MAX_LIMIT_SIZE AUTO_EXTEND NEXT_SIZE START_BKP_SEQ START_BKP_OFF END_BKP_SEQ
------------------------- ----------- -------------------- -------------- ----------- ----------- ------------- -------------------- -----------
END_BKP_OFF
--------------------
3 DISK 1450475777 FULLBAK1 /data/DM/bak/backset_1/backset_1_1 5 6 0 0 TBS_IDX_TEST
/data/DM/tbs_idx_test.dbf 67108864 0 1 0 NULL NULL NULL
NULL
LINEID DEVICE_TYPE BACKUP_ID BACKUPNAME BACKUPPATH FILE_SEQ TS_ID FILE_ID TS_STATE TS_NAME
---------- ----------- ----------- ---------- ---------------------------------- ----------- ----------- ----------- ----------- ------------
FILE_NAME MIRROR_PATH FILE_LEN MAX_LIMIT_SIZE AUTO_EXTEND NEXT_SIZE START_BKP_SEQ START_BKP_OFF END_BKP_SEQ
------------------------- ----------- -------------------- -------------- ----------- ----------- ------------- -------------------- -----------
END_BKP_OFF
--------------------
4 DISK 1274267694 FULLBAK1 /data/DM/bak/backset_1/backset_1_0 1 0 0 0 SYSTEM
/data/DM/SYSTEM.DBF 77594624 0 1 0 NULL NULL NULL
NULL
LINEID DEVICE_TYPE BACKUP_ID BACKUPNAME BACKUPPATH FILE_SEQ TS_ID FILE_ID TS_STATE TS_NAME
---------- ----------- ----------- ---------- ---------------------------------- ----------- ----------- ----------- ----------- ------------
FILE_NAME MIRROR_PATH FILE_LEN MAX_LIMIT_SIZE AUTO_EXTEND NEXT_SIZE START_BKP_SEQ START_BKP_OFF END_BKP_SEQ
------------------------- ----------- -------------------- -------------- ----------- ----------- ------------- -------------------- -----------
END_BKP_OFF
--------------------
5 DISK 1274267694 FULLBAK1 /data/DM/bak/backset_1/backset_1_0 3 4 0 0 MAIN
/data/DM/MAIN.DBF 134217728 0 1 0 NULL NULL NULL
NULL
used time: 33.091(ms). Execute id is 1232.
SQL>
– 查看备份集归档信息
SQL> select * from V$BACKUPSET_ARCH;
LINEID DEVICE_TYPE BACKUP_ID BACKUPNAME BACKUPPATH FILE_SEQ FILE_NAME FILE_LEN DSC_SEQNO BEGIN_SEQNO BEGIN_LSN
---------- ----------- ----------- ---------- ---------------------- ----------- --------- -------------------- ----------- -------------------- --------------------
END_SEQNO END_LSN CREATE_TIME CLOSE_TIME START_BKP_SEQ START_BKP_OFF END_BKP_SEQ
-------------------- -------------------- ----------------------- ----------------------- ------------- -------------------- -----------
END_BKP_OFF
--------------------
1 DISK 745643445 FULLBAK1 /data/DM/bak/backset_1 1 8192 0 3931 54175
3932 54183 0-00-00 00:00:00.000000 0-00-00 00:00:00.000000 NULL NULL NULL
NULL
used time: 32.528(ms). Execute id is 1233.
SQL>
– 查看备份片信息
SQL> select * from V$BACKUPSET_BKP;
LINEID DEVICE_TYPE BACKUP_ID BACKUPNAME BACKUPPATH BKP_NTH FILE_NAME BKP_LEN
---------- ----------- ----------- ---------- ---------------------------------- ----------- --------------- --------------------
1 DISK 745643445 FULLBAK1 /data/DM/bak/backset_1 0 backset_1.bak 6144
2 DISK 1450475777 FULLBAK1 /data/DM/bak/backset_1/backset_1_1 0 backset_1_1.bak 39936
3 DISK 1274267694 FULLBAK1 /data/DM/bak/backset_1/backset_1_0 0 backset_1_0.bak 1198080
used time: 37.561(ms). Execute id is 1234.
SQL>
– 查看备份表信息
SQL> select * from V$BACKUPSET_TABLE;
LINEID DEVICE_TYPE BACKUP_ID BACKUPNAME BACKUPPATH SCHEMANAME USERNAME TSNAME TABLENAME TABLETYPE
---------- ----------- ----------- ---------- -------------------- ---------- -------- ------ --------- -----------
INIT_SQL
-----------------------------------------------------------------------------------------------------------------------------------------------------------
DCONS_SQL DIDX_SQL BIDX_NUM META_VERSION
--------- -------- ----------- ------------
1 DISK 1609359304 T1BAK2 /data/DM/bak/t2bak_1 SYSDBA SYSDBA MAIN T2 0
DECLARE V_STR TEXT; BEGIN V_STR := 'CREATE SCHEMA "SYSDBA" AUTHORIZATION "SYSDBA" ; CREATE TABLE "T2" ( "CONTENT" VARCHAR(2000)) STORAGE(ON "MAIN", CLUSTERBTR) ; CREATE CONTEXT INDEX "SYSDBA"."CTI_CONTENT" ON "SYSDBA"."T2"(CONTENT) TABLESPACE "MAIN" LEXER CHINESE_LEXER; '; EXECUTE IMMEDIATE V_STR; END;
NULL NULL 0 28676
used time: 37.360(ms). Execute id is 1237.
SQL>
– 显示子备份集信息
SQL> select * from V$BACKUPSET_SUBS;
LINEID DEVICE_TYPE BACKUPNAME BACKUP_ID PARENT_ID BACKUPPATH BKP_NUM DBF_NUM BAKSET_SEQNO
---------- ----------- ---------- ----------- ----------- ---------------------------------- ----------- ----------- ------------
1 DISK FULLBAK1 1450475777 745643445 /data/DM/bak/backset_1/backset_1_1 1 3 1
2 DISK FULLBAK1 1274267694 745643445 /data/DM/bak/backset_1/backset_1_0 1 2 0
used time: 43.175(ms). Execute id is 1238.
SQL>
2.3.4 备份集监控
– 监控当前备份信息
SQL> select * from V$BACKUP_MONITOR;
LINEID PATH BAKSET_MAGIC START_TIME REMAINING TOTAL_SIZE PCNT READ_SIZE WRITE_SIZE AVG_READ
---------- --------------------- ------------ -------------------------- --------- -------------------- ---- -------------------- -------------------- --------
MIN_READ MAX_READ CUR_READ AVG_WRITE MIN_WRITE MAX_WRITE CUR_WRITE
-------- -------- -------- --------- --------- --------- ---------
1 /data/DM/bak/incbak_1 110135685 2025-04-18 01:41:27.000000 00:00:00 0 0 0 0 0
0 0 0 0 0 0 0
used time: 0.476(ms). Execute id is 1240.
SQL>
– 当前备份任务待备份数据文件列表
SQL> select * from V$BACKUP_FILES;
– 显示最近100 条备份监控信息
SQL> select * from V$BACKUP_HISTORY limit 1;
LINEID PATH START_TIME END_TIME READ_SIZE WRITE_SIZE AVG_READ MIN_READ MAX_READ
---------- ---------------------- -------------------------- -------------------------- -------------------- -------------------- -------- -------- --------
AVG_WRITE MIN_WRITE MAX_WRITE BAKSET_MAGIC BACKUP_NAME RANGE TYPE
--------- --------- --------- -------------------- ----------- ----------- -----------
SQL
-----------------------------------------------------------------------------------------------------------------------------------------------------------
STATUS ERROR
------ --------------------------------------------
1 /data/DM/bak/backset_1 2025-04-18 00:17:14.000000 2025-04-18 00:17:16.000000 0 0 0 0 0
0 0 0 1211077442 FULLBAK1 1 0
backup database full with backupdir '/data/DM/bak' backupname fullbak1 backupset 'backset_1' BACKUPINFO '第一次全备份' maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2;
FAIL Tablespace is offline or datafile is deleted
used time: 3.833(ms). Execute id is 1243.
SQL>
2.3.5 联机还原
SQL> create table t2(content varchar(2000));
executed successfully
used time: 9.388(ms). Execute id is 618.
SQL> insert into t2 values('删除原有的全文索引，对基表进行全表扫描，逐一重构索引信息。在创建全文索引成功
2 后，需完完全更新全文索引才可以执行有效的全文检索。完全更新全文索引没有次数限制，
3 用户可以根据需要在增量更新或者是完全更新失败以及发生系统故障后都可以执行完全更
4 新全文索引。另外，完全更新由于完全丢弃辅助表已有数据，重新开始对基表数据进行分词
5 并填充到辅助表，因此服务器允许这种情况下更改分词算法。');
affect rows 1
used time: 4.748(ms). Execute id is 619.
SQL> commit;
executed successfully
used time: 4.555(ms). Execute id is 620.
SQL> CREATE CONTEXT INDEX cti_content ON t2(content) LEXER DEFAULT_LEXER;
executed successfully
used time: 19.543(ms). Execute id is 621.
SQL> backup table t2 backupname t2bak2 backupset 't2bak_2' BACKUPINFO '第二次备份表t2' ;
executed successfully
used time: 00:00:01.060. Execute id is 622.
SQL> delete t2;
affect rows 1
used time: 5.405(ms). Execute id is 624.
SQL> commit;
executed successfully
used time: 1.596(ms). Execute id is 625.
SQL> select * from t2;
no rows
used time: 0.232(ms). Execute id is 626.
SQL> RESTORE TABLE t2 FROM BACKUPSET 't2bak_2' ;
RESTORE TABLE t2 FROM BACKUPSET 't2bak_2' ;
[-8327]:the table to be restored has secondary indexes or extra constraints.
used time: 126.693(ms). Execute id is 0.
SQL> select * from t2;
no rows
used time: 4.065(ms). Execute id is 628.
SQL> RESTORE TABLE t2 struct FROM BACKUPSET 't2bak_2' ;
executed successfully
used time: 155.747(ms). Execute id is 630.
SQL> select * from t2;
no rows
used time: 4.399(ms). Execute id is 631.
SQL> RESTORE TABLE t2 FROM BACKUPSET 't2bak_2' ;
executed successfully
used time: 307.651(ms). Execute id is 632.
SQL> select * from t2;
LINEID CONTENT
---------- -----------------------------------------------------------------------------------------------------------------------------------------------------------
1 删除原有的全文索引，对基表进行全表扫描，逐一重构索引信息。在创建全文索引成功 后，需完完全更新全文索引才可以执行有效的全文检索。完全更新全文索引没有次数限制， 用户可以根据需要在增量更新或者是完全更新失败以及发生系统故障后都可以执行完全更 新全文索引。另外，完全更新由于完全丢弃辅助表已有数据，重新开始对基表数据进行分词 并填充到辅助表，因此服务器允许这种情况下更改分词算法。
used time: 5.309(ms). Execute id is 636.
SQL>
2.4脱机备份还原（DMRMAN)
2.4.1 DMRMAN 概述
DMRMAN 是 DM 提供的命令行工具，无需额外安装。DMRMAN 的结构比较复杂，为了更好地使用它我们需要了解它的结构。接下来将对 DMRMAN 的重要组成部分进行介绍。
源库
源库是待备份的数据库。使用 DMRMAN 工具只可对数据库进行脱机备份，联机备份需要通过 DIsql 工具实现。DMRMAN 可以针对整个数据库执行脱机完全备份和增量备份，数据库可以配置归档也可以不配置。
目标库
目标库是待还原的数据库，即用来做还原的库，也称为目标还原库。目标库可以是通过dm.ini 文件指定的数据库，也可以是目标文件目录。通过 dm.ini 指定库时，dm.ini 及其配置中的 CTL_PATH 必须有效，且库必须处于关闭状态；指定文件目录时，指定目录作为SYSTEM_PATH 处理，可以存在，也可以不存在，但必须有效。目前目标库的还原仅支持脱机还原，可通过 DMRMAN 和 CONSOLE 工具实现。
DMRMAN 客户端
DMRMAN 客户端是 DM 软件的一部分，用来执行数据库的备份还原操作。DMRMAN客户端是一个命令行工具，命令行的好处是管理员可以编写复杂的脚本，将 DMRMAN 和操作系统的任务调度结合起来可以实现备份的自动化。
备份集
利用 DMRMAN 工具将数据库中的一个或多个数据文件、数据库信息等备份到一个称为“备份集”的逻辑结构中，备份集的格式是特定的，只能由 DMRMAN 创建和访问。
2.4.2 启动和配置 DMRMAN
– 启动和退出
[dmdba@dm-standalone ~]$ dmrman
dmrman V8
RMAN> exit
time used: 25.254(ms)
[dmdba@dm-standalone ~]$
使用 CONFIGURE 命令进行 DMRMAN 的默认参数配置，配置默认的存储介
质类型、跟踪日志文件、备份集搜索目录、归档日志搜索目录。
RMAN> configure
DEVICE TYPE: DISK
DEVICE PARMS:
FORMAT:
DESTINATION:
TRACE FILE:
TRACE LEVEL: 1
OPEN FILES: 4294967295
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
POOL SIZE: 1024
time used: 9.790(ms)
– 清除所有默认配置
RMAN> configure clear
time used: 0.683(ms)
RMAN> configure device type disk
time used: 16.089(ms)
– 配置备份集搜索路径
RMAN> configure default BACKUPDIR add '/data/DM/bak'
time used: 1.114(ms)
– 配置跟踪文件
RMAN> CONFIGURE DEFAULT TRACE FILE '/data/DM/trace.log ' TRACE LEVEL 2;
time used: 1.105(ms)
– 删除备份集搜索路径
RMAN> CONFIGURE DEFAULT BACKUPDIR DELETE '/data/DM/bak';
time used: 0.872(ms)
– 配置归档搜索路径
RMAN> CONFIGURE DEFAULT ARCHIVEDIR ADD '/data/DM/arch','/data/DM/arch2'
time used: 2.472(ms)
RMAN>
– 导出备份集（未生效）
RMAN> LOAD BACKUPSETS FROM device type DISK WITH BACKUPDIR '/data/DM/bak' TO BACKUPDIR '/data/DM/bak1';
LOAD BACKUPSETS FROM device type DISK WITH BACKUPDIR '/data/DM/bak' TO BACKUPDIR '/data/DM/bak1';
RMAN>
– 导出备份集映射文件
RMAN> dump backupset '/data/DM/bak/backset_1' database '/data/DM/dm.ini' mapped file '/data/DM/bak1/mapp.txt';
2.4.3 数据备份
– 全量备份数据库
RMAN> backup database '/data/DM/dm.ini' with backupdir '/data/DM/bak' backupname fullbak1 backupset 'backset_1' BACKUPINFO '第一次全备份' maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2;
backup database '/data/DM/dm.ini' with backupdir '/data/DM/bak' backupname fullbak1 backupset 'backset_1' BACKUPINFO '第一次全备份' maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2;
Processing backupset /data/DM/bak/backset_1
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:06][Remaining:00:00:00]
backup successfully!
time used: 00:00:07.204
RMAN>
– 增量备份数据库
RMAN> backup database '/data/DM/dm.ini' INCREMENT base on backupset 'backset_1' with backupdir '/data/DM/bak' backupname incbak1 backupset 'incbak_1' BACKUPINFO '第一次增量备份' maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2 ;
backup database '/data/DM/dm.ini' INCREMENT base on backupset 'backset_1' with backupdir '/data/DM/bak' backupname incbak1 backupset 'incbak_1' BACKUPINFO '第一次增量备份' maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2;
Processing backupset /data/DM/bak/incbak_1
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:05][Remaining:00:00:00]
backup successfully!
time used: 00:00:07.037
RMAN>
– 备份归档日志
RMAN> backup archivelog database '/data/DM/dm.ini' with backupdir '/data/DM/bak' backupname archbak1 backupset 'arachbakset_1' BACKUPINFO '第一次归档备份' maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2;
backup archivelog database '/data/DM/dm.ini' with backupdir '/data/DM/bak' backupname archbak1 backupset 'arachbakset_1' BACKUPINFO '第一次归档备份' maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2;
Processing backupset /data/DM/bak/arachbakset_1
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:05][Remaining:00:00:00]
backup successfully!
time used: 00:00:06.883
RMAN> backup archivelog time between '2025-04-17 12:00:00' and '2025-04-18 10:00:00' database '/data/DM/dm.ini' with backupdir '/data/DM/bak' backupname archbak2 backupset 'arachbakset_2' BACKUPINFO '第一次归档备份' maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2;
backup archivelog time between '2025-04-17 12:00:00' and '2025-04-18 10:00:00' database '/data/DM/dm.ini' with backupdir '/data/DM/bak' backupname archbak2 backupset 'arachbakset_2' BACKUPINFO '第一次归档备份' maxpiecesize 10000 limit read speed 100 write speed 100 compressed level 1 parallel 2;
Processing backupset /data/DM/bak/arachbakset_2
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:05][Remaining:00:00:00]
backup successfully!
time used: 00:00:06.817
RMAN>
2.4.4 管理备份
show backupset '/data/DM/bak/backset_1' RECURSIVE info db;
show backupset '/data/DM/bak/backset_1' RECURSIVE info file;
show backupset '/data/DM/bak/backset_1' RECURSIVE info meta;
show backupset '/data/DM/bak/backset_1' RECURSIVE info ep;
show backupset '/data/DM/bak/backset_1' RECURSIVE database '/data/DM/dm.ini'
with backupdir '/data/DM/bak' info db;
– 备份集校验
RMAN> check backupset '/data/DM/bak/backset_1' database '/data/DM/dm.ini'
check backupset '/data/DM/bak/backset_1' database '/data/DM/dm.ini'
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]
check backupset successfully.
time used: 191.794(ms)
RMAN>
– 备份集删除
RMAN> remove backupset '/data/DM/bak/backset_1' database '/data/DM/dm.ini' with backupdir '/data/DM/bak'
remove backupset '/data/DM/bak/backset_1' database '/data/DM/dm.ini' with backupdir '/data/DM/bak'
remove backupset successfully.
time used: 44.150(ms)
RMAN> remove DATABASE backupsets database '/data/DM/dm.ini' with backupdir '/data/DM/bak';
remove DATABASE backupsets database '/data/DM/dm.ini' with backupdir '/data/DM/bak';
remove backupsets successfully.
time used: 39.063(ms)
2.4.5 数据库还原和恢复
– 还原数据库
RMAN> restore database '/data/DM/dm.ini' with check reuse dmini auto extend overwrite from backupset '/data/DM/bak/backset_1' with backupdir '/data/DM/bak' rename to 'DM';
restore database '/data/DM/dm.ini' with check reuse dmini auto extend overwrite from backupset '/data/DM/bak/backset_1' with backupdir '/data/DM/bak' rename to 'DM';
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]
check backupset successfully.
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
restore successfully.
time used: 00:00:03.042
RMAN>
– 恢复数据库
RMAN> recover database '/data/DM/dm.ini' with archivedir '/data/DM/arch','/data/DM/arch2';
recover database '/data/DM/dm.ini' with archivedir '/data/DM/arch' , '/data/DM/arch2';
[Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]
recover successfully!
time used: 399.585(ms)
RMAN> recover database '/data/DM/dm.ini' update db_magic;
recover database '/data/DM/dm.ini' update db_magic;
recover successfully!
time used: 00:00:01.054
RMAN>
2.4.6 表空间还原和恢复
– 联机备份表空间
SQL> backup tablespace tbs_idx_test full with backupdir '/data/DM/bak' backupname fullbak1 backupset 'tbs_backset_1' BACKUPINFO '第一次表空间备份' ;
executed successfully
used time: 00:00:05.802. Execute id is 601.
SQL>
– rman还原表空间
RMAN> RESTORE DATABASE '/data/DM/dm.ini' TABLESPACE tbs_idx_test FROM BACKUPSET 'tbs_backset_1';
RESTORE DATABASE '/data/DM/dm.ini' TABLESPACE tbs_idx_test FROM BACKUPSET 'tbs_backset_1';
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
restore successfully.
time used: 00:00:02.418
– rman恢复表空间
RMAN> RECOVER DATABASE '/data/DM/dm.ini' TABLESPACE tbs_idx_test;
RECOVER DATABASE '/data/DM/dm.ini' TABLESPACE tbs_idx_test;
dres_backupset_recover_ts begin
[Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]
dres_backupset_recover_ts end
recover successfully.
time used: 439.102(ms)
RMAN>
2.4.7 归档还原
RMAN> backup archivelog database '/data/DM/dm.ini' with backupdir '/data/DM/bak' backupname archbak3 backupset 'arachbakset_3' BACKUPINFO '第一次归档备份';
backup archivelog database '/data/DM/dm.ini' with backupdir '/data/DM/bak' backupname archbak3 backupset 'arachbakset_3' BACKUPINFO '第一次归档备份';
Processing backupset /data/DM/bak/arachbakset_3
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:04][Remaining:00:00:00]
backup successfully!
time used: 00:00:05.318
RMAN> restore archivelog with check from backupset '/data/DM/bak/arachbakset_3' to archivedir '/data/DM/arch2';
restore archivelog with check from backupset '/data/DM/bak/arachbakset_3' to archivedir '/data/DM/arch2';
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]
check backupset successfully.
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]
restore successfully.
time used: 347.217(ms)
RMAN>
2.4.8 归档修复
一般建议在数据库故障后，应立即执行归档修复，否则后续还原恢复将会导致联机日志中未刷入本地归档的 REDO 日志丢失，届时再利用本地归档恢复将无法恢复到故障前的最新状态。
RMAN> repair archivelog database '/data/DM/dm.ini';
repair archivelog database '/data/DM/dm.ini';
repair archive log successfully.
repair time used: 221.112(ms)
time used: 221.390(ms)
RMAN>
2.4.9 查看操作日志
– 事件日志文件 dm_实例名_xxx.log
– 备份还原日志文件dm_BAKRES_xxx.log
– DMRMAN日志文件 dm_dmrman_xxx.log
三、联机拷贝还原
3.1拷贝
– 开启备份
[dmdba@dm-standalone data]$ disql sysdba/Dameng_123
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 6.064(ms)
disql V8
SQL> SP_SET_PARA_VALUE (2,'DDL_AUTO_COMMIT',0);
DMSQL executed successfully
used time: 14.339(ms). Execute id is 601.
SQL> SP_BACKUP_COPY_BEGIN();
DMSQL executed successfully
used time: 792.954(ms). Execute id is 602.
SQL>
– 执行系统拷贝文件命令
[dmdba@dm-standalone ~]$ cp -r /data/DM/* /data/DM1/
– 结束备份
SQL> SP_BACKUP_COPY_END('/data/DM/bak/bakupset1111');
DMSQL executed successfully
used time: 00:00:01.196. Execute id is 603.
SQL> SP_SET_PARA_VALUE (2,'DDL_AUTO_COMMIT',1);
DMSQL executed successfully
used time: 11.675(ms). Execute id is 604.
SQL>
3.2还原
– 删除原log文件
rm -rf /data/DM/*.log
– dmrman 还原
RMAN> RESTORE DATABASE '/data/DM/dm.ini' FROM BACKUPSET '/data/DM/bak/bakupset1111' ;
RESTORE DATABASE '/data/DM/dm.ini' FROM BACKUPSET '/data/DM/bak/bakupset1111';
file dm.key not found, use default license!
restore successfully.
time used: 00:00:01.408
RMAN>
– 恢复
RMAN> RECOVER DATABASE '/data/DM/dm.ini' WITH ARCHIVEDIR '/data/DM/arch';
RECOVER DATABASE '/data/DM/dm.ini' WITH ARCHIVEDIR '/data/DM/arch';
[Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]
recover successfully!
time used: 480.559(ms)
RMAN> RECOVER DATABASE '/data/DM/dm.ini' UPDATE DB_MAGIC;
RECOVER DATABASE '/data/DM/dm.ini' UPDATE DB_MAGIC;
recover successfully!
time used: 00:00:01.048
RMAN>
评论
后发表回复
作者
文章
阅读量
获赞
扫一扫
联系客服
联系客服
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1