今天就跟大家聊聊有关DM7中怎么利用disql管理备份，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。
1. 概述
管理备份一个重要的目的是删除不再需要的备份，DM7没有提供自动删除过期备份的功能，删除备份需要手动执行。备份管理相关系统过程与函数总结如下：
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：批量删除满足指定条件的所有备份集。
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：显示并行备份中生成的子备份集信息。
SF_BAKSET_BACKUP_DIR_ADD添加备份目录仅对当前会话有效。调用删除备份等函数或查看动态视图时要先调用SF_BAKSET_BACKUP_DIR_ADD添加备份目录，否则仅搜索默认备份路径下的备份集。
2. 备份目录管理
这里的备份目录是指备份集搜索目录，这些目录被记录在内存中，当执行动态视图（参见3.2.4.4 备份信息查看）或批量删除备份集时，均会从这些指定目录中先搜索所有备份集信息。
本节主要内容包括：
SF_BAKSET_BACKUP_DIR_ADD SF_BAKSET_BACKUP_DIR_REMOVE SF_BAKSET_BACKUP_DIR_REMOVE_ALL
SF_BAKSET_BACKUP_DIR_ADD函数
添加备份目录。若添加目录已经存在或者为库默认备份路径，则认为已经存在，不添加，但也不报错。
定义：
INT SF_BAKSET_BACKUP_DIR_ADD( device_type varchar, backup_dir varchar(256) )
参数说明：
device_type：待添加的备份目录对应存储介质类型，DISK或者TAPE。
backup_dir：待添加的备份目录。
返回值：
1：目录添加成功；其它情况下报错。
举例说明：
SQL> select sf_bakset_backup_dir_add('disk','arch_backup_lsn_15092082_15092086');
LINEID SF_BAKSET_BACKUP_DIR_ADD('disk','arch_backup_lsn_15092082_15092086')
---------- --------------------------------------------------------------------
1 1
used time: 26.411(ms). Execute id is 1569.SF_BAKSET_BACKUP_DIR_REMOVE函数
删除备份目录。若删除目录为库默认备份路径，不进行删除，认为删除失败。若指定目录存在于记录的合法目录中，则删除；不存在或者为空则跳过，正常返回。
定义：
INT SF_BAKSET_BACKUP_DIR_REMOVE ( device_type varchar, backup_dir varchar(256) )
参数说明：
device_type：待删除的备份目录对应存储介质类型。待删除的备份目录对应存储介质类型，DISK或者TAPE。
backup_dir：待删除的备份目录。
返回值：
1：目录删除成功；其他情况报错。
举例说明：
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- -----------------------------------
1 arch_backup_lsn_15092082_15092086
2 /dm_home/dmdba/dmdbms/data/jydm/bak
used time: 36.620(ms). Execute id is 1573.
SQL> select sf_bakset_backup_dir_remove('disk','arch_backup_lsn_15092082_15092086');
LINEID SF_BAKSET_BACKUP_DIR_REMOVE('disk','arch_backup_lsn_15092082_15092086')
---------- -----------------------------------------------------------------------
1 1
used time: 1.057(ms). Execute id is 1575.
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- -----------------------------------
1 /dm_home/dmdba/dmdbms/data/jydm/bak
used time: 0.987(ms). Execute id is 1577.SF_BAKSET_BACKUP_DIR_REMOVE_ALL函数清理全部备份目录，默认备份目录除外。
定义：
INT SF_BAKSET_BACKUP_DIR_REMOVE_ALL ()
返回值：
1：目录全部清理成功；其它情况下报错。
举例说明：
SQL> select sf_bakset_backup_dir_remove_all(); LINEID SF_BAKSET_BACKUP_DIR_REMOVE_ALL() ---------- --------------------------------- 1 1 used time: 1.019(ms). Execute id is 1580.
3. 备份集管理(备份集校验与删除)
本节介绍备份管理中最重要的功能，备份集校验和备份集删除。单个备份集删除时并行备份中地子备份集不允许单独删除；在给定备份集搜
集目录中发现存在引用删除备份集作为基备份的需要执行级联删除，默认报错。批量删除备份集时，跳过收集到的单独的子备份集。主要内
容如下：
SF_BAKSET_CHECK SF_BAKSET_REMOVE SF_BAKSET_REMOVE_BATCH SP_DB_BAKSET_REMOVE_BATCH SP_TS_BAKSET_REMOVE_BATCH SP_TAB_BAKSET_REMOVE_BATCH SP_ARCH_BAKSET_REMOVE_BATCH
SF_BAKSET_CHECK函数对备份集进行校验。
定义：
INT SF_BAKSET_CHECK( device_type varchar, bakset_pathvarchar(256) )
参数说明：
device_type：设备类型，disk或tape。
bakset_path：待校验的备份集目录。
返回值：
1：备份集目录存在且合法；否则报错。
举例说明：
SQL> backup database full to db_rac_bak_for_check backupset '/dm7/backup/db_rac_bak_for_check';
executed successfully
used time: 00:00:01.410. Execute id is 158.
SQL> select sf_bakset_check('disk','/dm7/backup/db_rac_bak_for_check');
LINEID SF_BAKSET_CHECK('disk','/dm7/backup/db_rac_bak_for_check')
---------- ----------------------------------------------------------
1 1
used time: 12.669(ms). Execute id is 159.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/db_rac_bak_for_check');
LINEID SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/db_rac_bak_for_check')
---------- -------------------------------------------------------------------
1 1
used time: 1.610(ms). Execute id is 162.
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- --------------------------------
1 /dm7/backup/db_rac_bak_for_check
2 +DMDATA/data/rac/bak
used time: 0.770(ms). Execute id is 163.
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 DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE
---------- ----------- ----------- ----------- -------------------- -------------------------------- ----------- ----------- ----------- ----------- ----------- --------- --------------------------- ----- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ ----------- ----------- -------------------- ------------ -----------
1 DISK 53418202 -1 DB_RAC_BAK_FOR_CHECK /dm7/backup/db_rac_bak_for_check 0 0 1 rac -1 2020-05-29 21:14:50.000773 0 0 0 0 33554432 50908 51113 2 4 1 0 0 49398 117507596 0
used time: 00:00:01.023. Execute id is 164.SF_BAKSET_REMOVE函数
删除指定设备类型和指定备份集目录的备份集。一次只检查一个合法.meta文件，然后删除对应备份集；若存在非法或非正常备份的.meta文件，则报错或直接返回，不会接着检查下一个.meta文件；若同一个备份集下还存在其它备份文件或备份集，则只删除备份文件，不会删除整个备份集。
定义：
INT SF_BAKSET_REMOVE ( device_type varchar, backsetpath varchar(256), option integer )
参数说明：
device_type：设备类型，disk或tape。
backsetpath：待删除的备份集目录。
Option:删除备份集选项，0默认删除，1级联删除。可选参数。并行备份集中子备份集不允许单独删除。目标备份集被其他备份集引用为基备份的，默认删除，报错；级联删除情况下，会递归将相关的增量备份也删除。
返回值：
1：备份集目录删除成功，其它情况下报错。
举例说明：
SQL> backup database full to db_rac_bak_for_remove backupset '/dm7/backup/db_rac_bak_for_remove';
executed successfully
used time: 00:00:01.320. Execute id is 165.
SQL> backup database increment base on backupset '/dm7/backup/db_rac_bak_for_remove' backupset '/dm7/backup/db_rac_bak_for_remove_incr';
executed successfully
used time: 00:00:01.255. Execute id is 170.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/db_rac_bak_for_remove');
LINEID SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/db_rac_bak_for_remove')
---------- --------------------------------------------------------------------
1 1
used time: 1.836(ms). Execute id is 171.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/db_rac_bak_for_remove_incr');
LINEID SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/db_rac_bak_for_remove_incr')
---------- -------------------------------------------------------------------------
1 1
used time: 1.444(ms). Execute id is 172.
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 DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE
---------- ----------- ----------- ----------- ---------------------------------- -------------------------------------- ----------- ----------- ----------- ----------- ----------- --------------------- --------------------------- ----- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ ----------- ----------- -------------------- ------------ -----------
1 DISK 53418202 -1 DB_RAC_BAK_FOR_CHECK /dm7/backup/db_rac_bak_for_check 0 0 1 rac -1 2020-05-29 21:14:50.000773 0 0 0 0 33554432 50908 51113 2 4 1 0 0 49398 117507596 0
2 DISK 1763138770 -1 DB_RAC_BAK_FOR_REMOVE /dm7/backup/db_rac_bak_for_remove 0 0 1 rac -1 2020-05-29 22:00:34.000524 0 0 0 0 33554432 50908 51119 2 4 1 0 0 49398 117507596 0
3 DISK -1036285990 -1 DB_INCR_rac_20200529_220232_000624 /dm7/backup/db_rac_bak_for_remove_incr 1 0 1 rac -1 DB_RAC_BAK_FOR_REMOVE 2020-05-29 22:02:33.000834 0 0 0 0 33554432 50908 51135 2 4 1 0 0 49398 117507596 0
used time: 00:00:01.038. Execute id is 173.
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- --------------------------------------
1 /dm7/backup/db_rac_bak_for_check
2 /dm7/backup/db_rac_bak_for_remove
3 /dm7/backup/db_rac_bak_for_remove_incr
4 +DMDATA/data/rac/bak
used time: 0.781(ms). Execute id is 174.
SQL> select sf_bakset_remove('disk','/dm7/backup/db_rac_bak_for_remove');
select sf_bakset_remove('disk','/dm7/backup/db_rac_bak_for_remove');
[-8202]:Be the base backupset of /dm7/backup/db_rac_bak_for_remove_incr,cannot be removed.
used time: 00:00:01.023. Execute id is 0.报错了，提示说它是另一个备份集的基备份不能被删除
SQL> select sf_bakset_remove('disk','/dm7/backup/db_rac_bak_for_remove',1);
LINEID SF_BAKSET_REMOVE('disk','/dm7/backup/db_rac_bak_for_remove',1)
---------- --------------------------------------------------------------
1 1
used time: 50.201(ms). Execute id is 176.检查备份集可以确认在删除基备份时确实级联删除了增量备份
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 DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE ---------- ----------- ----------- ----------- -------------------- -------------------------------- ----------- ----------- ----------- ----------- ----------- --------- --------------------------- ----- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ ----------- ----------- -------------------- ------------ ----------- 1 DISK 53418202 -1 DB_RAC_BAK_FOR_CHECK /dm7/backup/db_rac_bak_for_check 0 0 1 rac -1 2020-05-29 21:14:50.000773 0 0 0 0 33554432 50908 51113 2 4 1 0 0 49398 117507596 0 used time: 00:00:01.050. Execute id is 177.
SF_BAKSET_REMOVE_BATCH函数
批量删除满足指定条件的所有备份集。
定义：
INT SF_BAKSET_REMOVE_BATCH ( device_type varchar, end_time datetime, range int, obj_name varchar(257) )
参数说明：
device_type：设备类型，disk或tape。指定NULL，则忽略存储设备的区分。
end_time：删除备份集生成的结束时间，仅删除end_time之前的备份集，必须指定。
range：指定删除备份的级别。1代表库级，2代表表空间级，3代表表级，4代表归档备份。若指定NULL，则忽略备份集备份级别的区分。
obj_name：待删除备份集中备份对象的名称，仅表空间级和表级有效。若为表级备份删除，则需指定完整的表名（模式.表名），否则，将认为删除会话当前模式下的表备份。若指定为NULL，则忽略备份集中备份对象名称区分
返回值：
1：备份集目录删除成功，其它情况下报错。
举例说明：
SQL> backup database full to db_rac_full_bak_for_remove backupset '/dm7/backup/db_rac_full_bak_for_remove';
executed successfully
used time: 00:00:01.498. Execute id is 184.
SQL> backup tablespace main full to tab_main_full_bak_for_remove backupset '/dm7/backup/tab_main_full_bak_for_remove';
executed successfully
used time: 00:00:01.121. Execute id is 185.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/db_rac_full_bak_for_remove');
LINEID SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/db_rac_full_bak_for_remove')
---------- -------------------------------------------------------------------------
1 1
used time: 1.574(ms). Execute id is 186.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/tab_main_full_bak_for_remove');
LINEID SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/tab_main_full_bak_for_remove')
---------- ---------------------------------------------------------------------------
1 1
used time: 1.881(ms). Execute id is 187.
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- ----------------------------------------
1 /dm7/backup/db_rac_full_bak_for_remove
2 /dm7/backup/tab_main_full_bak_for_remove
3 +DMDATA/data/rac/bak
used time: 0.784(ms). Execute id is 188.
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 DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE
---------- ----------- ----------- ----------- ---------------------------- ---------------------------------------- ----------- ----------- ----------- ----------- ----------- --------- --------------------------- ----- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ ----------- ----------- -------------------- ------------ -----------
1 DISK 760083173 -1 DB_RAC_FULL_BAK_FOR_REMOVE /dm7/backup/db_rac_full_bak_for_remove 0 0 1 rac -1 2020-05-30 10:21:23.000661 0 0 0 0 33554432 50908 51141 2 4 1 0 0 49398 117507596 0
2 DISK -1117064059 -1 TAB_MAIN_FULL_BAK_FOR_REMOVE /dm7/backup/tab_main_full_bak_for_remove 0 0 2 MAIN 4 2020-05-30 10:22:41.000744 0 0 0 0 33554432 50908 51147 1 1 1 0 0 49398 117507596 0
used time: 00:00:01.025. Execute id is 189.
SQL> select sf_bakset_remove_batch('disk',now(),null,null);
LINEID SF_BAKSET_REMOVE_BATCH('disk',NOW(),NULL,NULL)
---------- ----------------------------------------------
1 1
used time: 21.228(ms). Execute id is 190.
SQL> select * from v$backupset;
no rows
used time: 00:00:01.023. Execute id is 191.SP_DB_BAKSET_REMOVE_BATCH过程
批量删除指定时间之前的数据库备份集。使用该方法前，需要先使用SF_BAKSET_BACKUP_DIR_ADD添加将要删除的备份集目录，否则只删除默
认备份路径下的备份集。
定义：
SP_DB_BAKSET_REMOVE_BATCH ( device_type varchar, end_time datetime )
参数说明：
device_type：设备类型，disk或tape。指定NULL，则忽略存储设备的区分。
end_time：删除备份集生成的结束时间，仅删除end_time之前的备份集，必须指定。
举例说明：
SQL> backup database full to db_rac_full_bak_for_del backupset '/dm7/backup/db_rac_full_bak_for_del';
executed successfully
used time: 00:00:01.580. Execute id is 194.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/db_rac_full_bak_for_del');
LINEID SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/db_rac_full_bak_for_del')
---------- ----------------------------------------------------------------------
1 1
used time: 1.725(ms). Execute id is 195.
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- -----------------------------------
1 /dm7/backup/db_rac_full_bak_for_del
2 +DMDATA/data/rac/bak
used time: 0.483(ms). Execute id is 196.
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 DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE
---------- ----------- ----------- ----------- ----------------------- ----------------------------------- ----------- ----------- ----------- ----------- ----------- --------- --------------------------- ----- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ ----------- ----------- -------------------- ------------ -----------
1 DISK 361744824 -1 DB_RAC_FULL_BAK_FOR_DEL /dm7/backup/db_rac_full_bak_for_del 0 0 1 rac -1 2020-05-30 10:27:00.000621 0 0 0 0 33554432 50908 51153 2 4 1 0 0 49398 117507596 0
used time: 00:00:01.024. Execute id is 197.
SQL> call sp_db_bakset_remove_batch('disk',now());
DMSQL executed successfully
used time: 36.535(ms). Execute id is 198.
SQL> select * from v$backupset;
no rows
used time: 00:00:01.021. Execute id is 199.SP_TS_BAKSET_REMOVE_BATCH过程
批量删除指定表空间对象及指定时间之前的表空间备份集。使用该方法前，需要先使用SF_BAKSET_BACKUP_DIR_ADD添加将要删除的备份集目录，否则只删除默认备份路径下的备份集。
定义：
SP_TS_BAKSET_REMOVE_BATCH ( device_type varchar, end_time datetime, ts_name varchar(128) )
参数说明：
device_type：设备类型，disk或tape。指定NULL，则忽略存储设备的区分。
end_time：删除备份集生成的结束时间，仅删除end_time之前的备份集，必须指定。
ts_name：表空间名，若未指定，则认为删除所有满足条件的表空间备份集。
举例说明：
SQL> backup tablespace main full to tab_main_bak_full_for_del backupset '/dm7/backup/tab_main_bak_full_for_del';
executed successfully
used time: 00:00:01.123. Execute id is 202.
SQL> sf_bakset_backup_dir_add('disk','/dm7/backup/tab_main_bak_full_for_del');
DMSQL executed successfully
used time: 1.256(ms). Execute id is 203.
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- -------------------------------------
1 /dm7/backup/tab_main_bak_full_for_del
2 +DMDATA/data/rac/bak
used time: 0.773(ms). Execute id is 204.
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 DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE
---------- ----------- ----------- ----------- ------------------------- ------------------------------------- ----------- ----------- ----------- ----------- ----------- --------- --------------------------- ----- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ ----------- ----------- -------------------- ------------ -----------
1 DISK 798489856 -1 TAB_MAIN_BAK_FULL_FOR_DEL /dm7/backup/tab_main_bak_full_for_del 0 0 2 MAIN 4 2020-05-30 10:38:29.000350 0 0 0 0 33554432 50908 51159 1 1 1 0 0 49398 117507596 0
used time: 00:00:01.027. Execute id is 205.
SQL> call sp_ts_bakset_remove_batch('disk',now(),'main');
DMSQL executed successfully
used time: 16.765(ms). Execute id is 206.
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 DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE
---------- ----------- ----------- ----------- ------------------------- ------------------------------------- ----------- ----------- ----------- ----------- ----------- --------- --------------------------- ----- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ ----------- ----------- -------------------- ------------ -----------
1 DISK 798489856 -1 TAB_MAIN_BAK_FULL_FOR_DEL /dm7/backup/tab_main_bak_full_for_del 0 0 2 MAIN 4 2020-05-30 10:38:29.000350 0 0 0 0 33554432 50908 51159 1 1 1 0 0 49398 117507596 0
used time: 00:00:01.022. Execute id is 207.说明指定表空间名时表空间名要大写
SQL> call sp_ts_bakset_remove_batch('disk',now(),'MAIN');
DMSQL executed successfully
used time: 13.667(ms). Execute id is 208.
SQL> select * from v$backupset;
no rows
used time: 00:00:01.023. Execute id is 209.删除备份目录
SQL> sf_bakset_backup_dir_remove_all(); DMSQL executed successfully used time: 0.787(ms). Execute id is 210. SQL> select * from v$backupset_search_dirs; LINEID DIR ---------- -------------------- 1 +DMDATA/data/rac/bak used time: 0.836(ms). Execute id is 211.
SP_TAB_BAKSET_REMOVE_BATCH过程
批量删除指定表对象及指定时间之前的表备份集。使用该方法前，需要先使用SF_BAKSET_BACKUP_DIR_ADD添加将要删除的备份集目录，否则只删除默认备份路径下的备份集。
定义：
SP_TAB_BAKSET_REMOVE_BATCH ( device_type varchar, end_time datetime, sch_name varchar(128), tab_name varchar(128) ) 参数说明： device_type：设备类型，disk或tape。指定NULL，则忽略存储设备的区分。 end_time：删除备份集生成的结束时间，仅删除end_time之前的备份集，必须指定。 sch_name：表所属的模式名。 tab_name：表名，只要模式名和表名有一个指定，就认为需要匹配目标；若均指定为NULL，则认为删除满足条件的所有表备份。 举例说明：
SQL> create table tab_for_del_batch(c1 int);
executed successfully
used time: 18.840(ms). Execute id is 212.
SQL> insert into tab_for_del_batch values(1);
affect rows 1
used time: 1.840(ms). Execute id is 213.
SQL> commit;
executed successfully
used time: 1.557(ms). Execute id is 214.
SQL> backup table tab_for_del_batch to tab_bak_for_del_batch backupset '/dm7/backup/tab_bak_for_del_batch';
executed successfully
used time: 00:00:01.142. Execute id is 215.
SQL> call sf_bakset_backup_dir_add('disk','/dm7/backup/tab_bak_for_del_batch');
DMSQL executed successfully
used time: 1.436(ms). Execute id is 216.
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- ---------------------------------
1 /dm7/backup/tab_bak_for_del_batch
2 +DMDATA/data/rac/bak
used time: 0.742(ms). Execute id is 217.
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 DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE
---------- ----------- ----------- ----------- --------------------- --------------------------------- ----------- ----------- ----------- ------------------------ ----------- --------- --------------------------- ----- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ ----------- ----------- -------------------- ------------ -----------
1 DISK -424456112 -1 TAB_BAK_FOR_DEL_BATCH /dm7/backup/tab_bak_for_del_batch 2 0 3 SYSDBA.TAB_FOR_DEL_BATCH -1 2020-05-30 11:07:05.000089 0 0 0 0 33554432 50908 51200 1 0 1 0 0 49398 117507596 0
used time: 00:00:01.023. Execute id is 218.
SQL> sp_tab_bakset_remove_batch('disk',now(),'SYSDBA','TAB_FOR_DEL_BATCH');
DMSQL executed successfully
used time: 14.860(ms). Execute id is 219.
SQL> select * from v$backupset;
no rows
used time: 00:00:01.022. Execute id is 220.
SQL> sf_bakset_backup_dir_remove_all();
DMSQL executed successfully
used time: 0.955(ms). Execute id is 221.
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- --------------------
1 +DMDATA/data/rac/bak
used time: 1.138(ms). Execute id is 222.SP_ARCH_BAKSET_REMOVE_BATCH过程
批量删除指定时间之前的归档备份集。使用该方法前，需要先使用SF_BAKSET_BACKUP_DIR_ADD添加将要删除的备份集目录，否则只删除默认备份路径下的备份集。
定义：
SP_ARCH_BAKSET_REMOVE_BATCH ( device_type varchar, end_time datetime )
参数说明：
device_type：设备类型，disk或tape。指定NULL，则忽略存储设备的区分。
end_time：删除备份集生成的结束时间，仅删除end_time之前的备份集，必须指定。
举例说明：
SQL> backup archivelog to arch_bak_for_del_batch backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/arch_bak_for_del_batch';
executed successfully
used time: 00:00:36.170. Execute id is 2476.
SQL> sf_bakset_backup_dir_add('disk','/dm_home/dmdba/dmdbms/data/jydm/bak/arch_bak_for_del_batch');
DMSQL executed successfully
used time: 0.768(ms). Execute id is 2478.
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- ----------------------------------------------------------
1 /dm_home/dmdba/dmdbms/data/jydm/bak/arch_bak_for_del_batch
2 /dm_home/dmdba/dmdbms/data/jydm/bak
used time: 0.568(ms). Execute id is 2479.
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 DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE
---------- ----------- ----------- ----------- ---------------------- ---------------------------------------------------------- ----------- ----------- ----------- ----------- ----------- --------- --------------------------- ----- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ ----------- ----------- -------------------- ------------ -----------
1 DISK 1259288472 -1 ARCH_BAK_FOR_DEL_BATCH /dm_home/dmdba/dmdbms/data/jydm/bak/arch_bak_for_del_batch 3 0 4 ARCHIVE -1 2020-05-30 14:29:32.000827 0 0 1 0 33554432 8236220 15220690 1 12 1 0 0 15220690 117507596 0
2 DISK 1259288472 -1 ARCH_BAK_FOR_DEL_BATCH /dm_home/dmdba/dmdbms/data/jydm/bak/arch_bak_for_del_batch 3 0 4 ARCHIVE -1 2020-05-30 14:29:32.000827 0 0 1 0 33554432 8236220 15220690 1 12 1 0 0 15220690 117507596 0
used time: 00:00:01.023. Execute id is 2480.
SQL> sp_arch_bakset_remove_batch('disk',now());
DMSQL executed successfully
used time: 178.477(ms). Execute id is 2485.
SQL> select * from v$backupset;
no rows
used time: 00:00:01.009. Execute id is 2486.
SQL> sf_bakset_backup_dir_remove_all();
DMSQL executed successfully
used time: 0.826(ms). Execute id is 2492.
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- -----------------------------------
1 /dm_home/dmdba/dmdbms/data/jydm/bak
used time: 0.763(ms). Execute id is 2493.4. 备份信息查看
DM7提供了一系列动态视图供用户查看备份集相关信息，在查看之前应先使用SF_BAKSET_BACKUP_DIR_ADD添加备份集目录，否则只显示默认备份路径下的备份集信息，使用方法如下例所示：
SQL>SELECT SF_BAKSET_BACKUP_DIR_ADD('DISK', '/home/dm_bak');下面逐个介绍DM7提供的备份相关动态视图。
V$BACKUPSET显示备份集基本信息。
SQL> desc v$backupset LINEID NAME TYPE$ NULLABLE ---------- ----------------- ------------- -------- 1 DEVICE_TYPE VARCHAR(10) Y 备份集存储介质类型 2 BACKUP_ID INTEGER Y 备份ID 3 PARENT_ID INTEGER Y 并行备份的子备份集所属备份的ID 4 BACKUP_NAME VARCHAR(512) Y 备份名 5 BACKUP_PATH VARCHAR(1024) Y 备份路径 6 TYPE INTEGER Y 0：基备份，1：增量备份，2：表备份，3：归档备份 7 LEVEL INTEGER Y 是否脱机备份。0：联机备份，1：脱机备份 8 RANGE# INTEGER Y 1：库备份，2：表空间备份，3：表级备份，4：归档备份 9 OBJECT_NAME VARCHAR(1025) Y 对象名：数据库名、表空间名或者表名 10 OBJECT_ID INTEGER Y 对象ID，表备份时无效 11 BASE_NAME VARCHAR(512) Y 基备份名，表备份时无效 12 BACKUP_TIME DATETIME(6) Y 备份时间 13 DESC# VARCHAR(1024) Y 备份描述信息 14 ENCRYPT_TYPE INTEGER Y 加密类型 15 COMPRESS_LEVEL INTEGER Y 压缩级别 16 WITHOUT_LOG INTEGER Y 联机数据库备份是否备份日志，表备份时无效 17 USE_PWR INTEGER Y 增量备份过程中是否使用PWR优化,均不使用，保留仅为了兼容 18 PKG_SIZE INTEGER Y 数据包大写标志，内部实现 19 BEGIN_LSN BIGINT Y 备份的起始LSN值，表备份时无效 20 END_LSN BIGINT Y 结束备份的LSN值，表备份时无效 21 BKP_NUM INTEGER Y 备份片个数，即备份集中.bak文件个数 22 DBF_NUM INTEGER Y 备份集中包含的数据库\表空间数据文件个数，表备份时无效 23 PARALLEL_NUM INTEGER Y 并行备份的并行数，0或者1为非并行备份集 24 DDL_CLONE INTEGER Y DDL_CLONE库备份标识，0不是，1是 25 MPP_FLAG INTEGER Y MPP库备份标识，0不是，1是 26 MIN_TRX_START_LSN BIGINT Y 备份时活动事务最小的LSN 27 MIN_EXEC_VER INTEGER Y 备份集适用的最小执行码的版本号，转换为16进制匹配版本号 28 CUMULATIVE INTEGER Y 增量备份时，是否为累积增量备份。1是，0否
下面以创建数据库备份为例，查看备份集的介质类型、备份路径、备份类型等基本信息：
SQL> backup database full to db_rac_bak_for_info backupset '/dm7/backup/db_rac_bak_for_info';
executed successfully
used time: 00:00:01.298. Execute id is 224.
SQL> sf_bakset_backup_dir_add('disk','/dm7/backup/db_rac_bak_for_info');
DMSQL executed successfully
used time: 0.939(ms). Execute id is 225.
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- -------------------------------
1 /dm7/backup/db_rac_bak_for_info
2 +DMDATA/data/rac/bak
used time: 1.726(ms). Execute id is 226.
SQL> select device_type,backup_path,type,range# from v$backupset;
LINEID DEVICE_TYPE BACKUP_PATH TYPE RANGE#
---------- ----------- ------------------------------- ----------- -----------
1 DISK /dm7/backup/db_rac_bak_for_info 0 1
used time: 00:00:01.023. Execute id is 227.V$BACKUPSET_DBINFO显示备份集的数据库相关信息。
SQL> desc v$backupset_dbinfo; LINEID NAME TYPE$ NULLABLE ---------- -------------- ------------- -------- 1 DEVICE_TYPE VARCHAR(10) Y 备份集存储介质类型 2 BACKUP_ID INTEGER Y 备份ID 3 BACKUP_NAME VARCHAR(512) Y 备份名 4 BACKUP_PATH VARCHAR(1024) Y 备份路径 5 EXTENT_SIZE INTEGER Y 数据文件使用的簇大小 6 PAGE_SIZE INTEGER Y 页大小 7 LOG_PAGE_SIZE INTEGER Y 日志文件页大小 8 CASE_SENSITVE INTEGER Y 大小写敏感标志 9 DB_MAGIC INTEGER Y 数据库的magic 10 PM_DB_MAGIC INTEGER Y 永久魔数（permenant_magic） 11 UNICODE_FLAG INTEGER Y unicode标志 12 DB_VERSION INTEGER Y 数据库版本 13 GLOBAL_VERSION VARCHAR(512) Y 数据库全局版本信息 14 ENABLE_POLICY INTEGER Y 安全策略 15 ARCH_FLAG INTEGER Y 归档是否打开的标志 16 RAC_NODE INTEGER Y 高性能集群的节点数目 17 PAGE_CHECK INTEGER Y 数据页校验配置 18 RLOG_ENCRYPT INTEGER Y 归档日志是否加密 19 EX_CIPHER_NAME VARCHAR(512) Y 外部加密算法名称 20 EX_CIPHER_ID INTEGER Y 外部加密算法名称对应的ID 21 EX_HASH_NAME VARCHAR(512) Y 外部HASH算法名称 22 EX_HASH_ID INTEGER Y 外部HASH算法名称对应的ID 23 LENGTH_IN_CHAR INTEGER Y VARCHAR类型长度是否以字符为单位 24 USE_NEW_HASH INTEGER Y 是否使用改进的字符类型HASH算法 25 BLANK_PAD_MODE INTEGER Y 数据库空格填充模式
表还原时要求目标库的特定建库参数要与源库一致，如页大小、簇大小，通过查看备份文件的数据库信息可确定目标库需要设置哪些建库参数。下面以创建表备份为例，查看备份集的页大小、簇大小、大小写是否敏感、UNICODE_FLAG等数据库信息：
SQL> backup table tab_01 to tab_01_bak backupset '/dm7/backup/tab_01_bak';
executed successfully
used time: 00:00:01.119. Execute id is 236.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/tab_01_bak');
LINEID SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/tab_01_bak')
---------- ---------------------------------------------------------
1 1
used time: 2.096(ms). Execute id is 237.
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- ----------------------
1 /dm7/backup/tab_01_bak
2 +DMDATA/data/rac/bak
used time: 0.726(ms). Execute id is 238.
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 DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE
---------- ----------- ----------- ----------- ----------- ---------------------- ----------- ----------- ----------- ------------- ----------- --------- --------------------------- ----- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ ----------- ----------- -------------------- ------------ -----------
1 DISK 1132430805 -1 TAB_01_BAK /dm7/backup/tab_01_bak 2 0 3 SYSDBA.TAB_01 -1 2020-05-30 14:56:59.000255 0 0 0 0 33554432 50908 51208 1 0 1 0 0 49398 117507596 0
used time: 00:00:01.023. Execute id is 239.
SQL> select backup_path,page_size,extent_size,case_sensitive from v$backupset_dbinfo where backup_path='/dm7/backup/tab_01_bak';
LINEID BACKUP_PATH PAGE_SIZE EXTENT_SIZE CASE_SENSITIVE
---------- ---------------------- ----------- ----------- --------------
1 /dm7/backup/tab_01_bak 8192 16 1
used time: 00:00:01.028. Execute id is 240.V$BACKUPSET_DBF显示备份集中数据文件的相关信息，表备份时无效。
SQL> desc v$backupset_dbf LINEID NAME TYPE$ NULLABLE ---------- -------------- ------------- -------- 1 DEVICE_TYPE VARCHAR(10) Y 备份集存储介质类型 2 BACKUP_ID INTEGER Y 备份ID 3 BACKUPNAME VARCHAR(512) Y 备份名 4 BACKUPPATH VARCHAR(1024) Y 备份路径 5 FILE_SEQ INTEGER Y 备份的数据文件序号 6 TS_ID INTEGER Y 表空间ID 7 FILE_ID INTEGER Y 数据文件ID 8 TS_STATE INTEGER Y 表空间状态 9 TS_NAME VARCHAR(512) Y 表空间名 10 FILE_NAME VARCHAR(1024) Y 包含完整路径的数据文件名 11 MIRROR_PATH VARCHAR(1024) Y 镜像文件路径 12 FILE_LEN BIGINT Y 数据文件占用的字节大小 13 MAX_LIMIT_SIZE INTEGER Y 文件最大大小，以M为单位 14 AUTO_EXTEND INTEGER Y 是否支持自动扩展：1支持，0不支持 15 NEXT_SIZE INTEGER Y 文件每次扩展大小，以M为单位 16 START_BKP_SEQ INTEGER Y 起始备份片编号 17 START_BKP_OFF BIGINT Y 起始备份片偏移 18 END_BKP_SEQ INTEGER Y 结束备份片编号 19 END_BKP_OFF BIGINT Y 结束备份片偏移
数据库和表空间备份集中记录了备份的数据文件具体信息，如果想了解备份集中包含了哪些数据文件且这些数据文件有什么属性，可通过查询V$BACKUPSET_DBF实现。下面以表空间备份为例，查看备份集中的数据文件信息。
SQL> create tablespace ts_for_dbf datafile 'ts_for_dbf_01.dbf' size 128;
executed successfully
used time: 00:00:01.594. Execute id is 247.
SQL> alter tablespace ts_for_dbf add datafile 'ts_for_dbf_02.dbf' size 128;
executed successfully
used time: 236.359(ms). Execute id is 248.
SQL> select * from v$datafile;
LINEID GROUP_ID ID PATH CLIENT_PATH CREATE_TIME STATUS$ RW_STATUS LAST_CKPT_TIME MODIFY_TIME MODIFY_TRX TOTAL_SIZE FREE_SIZE FREE_PAGE_NO PAGES_READ PAGES_WRITE PAGE_SIZE READ_REQUEST WRITE_REQUEST AUTO_EXTEND MAX_SIZE NEXT_SIZE MIRROR_PATH
---------- ----------- ----------- ---------------------------------- ----------------- --------------------------- ----------- ----------- --------------------------- --------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------- ------------ ------------- ----------- ----------- ----------- -----------
1 0 0 +DMDATA/data/rac/system.dbf system.dbf 2020-04-26 15:44:40 1 2 2020-05-30 15:06:01 2020-04-26 15:44:40 8326 16384 15760 1520 248 50 8192 248 50 1 16777215 0 NULL
2 1 0 +DMDATA/data/rac/roll.dbf roll.dbf 2020-04-26 15:44:40 1 2 2020-05-30 15:06:01 2020-04-26 15:44:40 8326 16384 13780 2624 1303 5209 8192 1303 5209 1 16777215 0 NULL
3 3 0 +DMDATA/data/rac/TEMP0.DBF TEMP0.DBF 2020-05-28 16:52:15 1 2 2020-05-30 15:06:01 2020-05-28 16:52:15 8326 1280 1270 32 0 0 8192 0 0 1 16777215 0 NULL
4 4 0 +DMDATA/data/rac/main.dbf main.dbf 2020-04-26 15:44:43 1 2 2020-05-30 15:06:01 2020-04-26 15:44:43 8326 16384 16369 64 4 10 8192 4 10 1 16777215 0 NULL
5 5 0 +DMDATA/data/rac/ts_for_dbf_01.dbf ts_for_dbf_01.dbf 2020-05-30 15:06:00 1 2 2020-05-30 15:06:01 2020-05-30 15:06:00 8326 16384 16376 32 0 1 8192 0 1 1 16777215 0 NULL
6 5 1 +DMDATA/data/rac/ts_for_dbf_02.dbf ts_for_dbf_02.dbf 2020-05-30 15:06:29 1 2 2020-05-30 15:06:01 2020-05-30 15:06:29 8326 16384 16383 32 0 0 8192 0 0 1 16777215 0 NULL
6 rows got
used time: 5.737(ms). Execute id is 249.
SQL> backup tablespace ts_for_dbf to ts_for_dbf_bak backupset '/dm7/backup/ts_for_dbf_bak';
executed successfully
used time: 00:00:01.114. Execute id is 250.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/ts_for_dbf_bak');
LINEID SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/ts_for_dbf_bak')
---------- -------------------------------------------------------------
1 1
used time: 1.928(ms). Execute id is 251.
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- --------------------------
1 /dm7/backup/ts_for_dbf_bak
2 +DMDATA/data/rac/bak
used time: 0.848(ms). Execute id is 252.
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 DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE
---------- ----------- ----------- ----------- -------------- -------------------------- ----------- ----------- ----------- ----------- ----------- --------- --------------------------- ----- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ ----------- ----------- -------------------- ------------ -----------
1 DISK 1346953844 -1 TS_FOR_DBF_BAK /dm7/backup/ts_for_dbf_bak 0 0 2 TS_FOR_DBF 5 2020-05-30 15:08:17.000425 0 0 0 0 33554432 51236 51238 1 2 1 0 0 49398 117507596 0
used time: 00:00:01.023. Execute id is 253.
SQL> select file_seq,ts_id,file_id,ts_name,file_name from v$backupset_dbf;
LINEID FILE_SEQ TS_ID FILE_ID TS_NAME FILE_NAME
---------- ----------- ----------- ----------- ---------- ----------------------------------
1 1 5 0 TS_FOR_DBF +DMDATA/data/rac/ts_for_dbf_01.dbf
2 2 5 1 TS_FOR_DBF +DMDATA/data/rac/ts_for_dbf_02.dbf
used time: 00:00:01.023. Execute id is 254.V$BACKUPSET_ARCH显示备份集中归档文件的信息，且仅归档备份才会有数据。
SQL> desc v$backupset_arch LINEID NAME TYPE$ NULLABLE ---------- -------------- ------------- -------- 1 DEVICE_TYPE VARCHAR(10) Y 备份集存储介质类型 2 BACKUP_ID INTEGER Y 备份ID 3 BACKUPNAME VARCHAR(512) Y 备份名 4 BACKUPPATH VARCHAR(1024) Y 备份路径 5 FILE_SEQ INTEGER Y 备份的数据文件序号 6 FILE_NAME VARCHAR(1024) Y 归档文件路径 7 FILE_LEN BIGINT Y 归档文件大小 8 BEGIN_LSN BIGINT Y 备份归档的起始LSN值 9 BEGIN_SEQNO BIGINT Y 起始备份归档序号 10 BEGIN_RPAG_OFF INTEGER Y 备份归档起始LSN对应归档页内偏移 11 END_LSN BIGINT Y 结束备份归档的LSN值 12 CREATE_TIME DATETIME(6) Y 归档文件的创建时间 13 CLOSE_TIME DATETIME(6) Y 归档文件的关闭时间 14 START_BKP_SEQ INTEGER Y 起始备份片编号 15 START_BKP_OFF BIGINT Y 起始备份片偏移 16 END_BKP_SEQ INTEGER Y 结束备份片编号 17 END_BKP_OFF BIGINT Y 结束备份片偏移
备份集日志信息可以是联机库备份备份开始到备份结束这段时间服务器产生的日志，也可以是归档备份中备份的归档信息。接下来以数据库备份为例查询备份集日志信息。
SQL> backup database to db_rac_bak backupset '/dm7/backup/db_rac_bak';
executed successfully
used time: 00:00:01.451. Execute id is 259.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/db_rac_bak');
LINEID SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/db_rac_bak')
---------- ---------------------------------------------------------
1 1
used time: 1.664(ms). Execute id is 260.
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- ----------------------
1 /dm7/backup/db_rac_bak
2 +DMDATA/data/rac/bak
used time: 0.775(ms). Execute id is 261.
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 DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE
---------- ----------- ----------- ----------- ----------- ---------------------- ----------- ----------- ----------- ----------- ----------- --------- --------------------------- ----- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ ----------- ----------- -------------------- ------------ -----------
1 DISK -274484992 -1 DB_RAC_BAK /dm7/backup/db_rac_bak 0 0 1 rac -1 2020-05-30 15:16:49.000122 0 0 0 0 33554432 51236 51244 2 6 1 0 0 49398 117507596 0
used time: 00:00:01.020. Execute id is 262.
SQL> select backuppath,file_seq,file_name,begin_lsn,end_lsn from v$backupset_arch where backuppath='/dm7/backup/db_rac_bak';
LINEID BACKUPPATH FILE_SEQ FILE_NAME BEGIN_LSN END_LSN
---------- ---------------------- ----------- --------- -------------------- --------------------
1 /dm7/backup/db_rac_bak 6 51241 51244
used time: 00:00:01.025. Execute id is 263.V$BACKUPSET_BKP显示备份集的备份片信息。
SQL> desc v$backupset_bkp LINEID NAME TYPE$ NULLABLE ---------- ----------- ------------- -------- 1 DEVICE_TYPE VARCHAR(10) Y 备份集存储介质类型 2 BACKUP_ID INTEGER Y 备份ID 3 BACKUPNAME VARCHAR(512) Y 备份名 4 BACKUPPATH VARCHAR(1024) Y 备份路径 5 BKP_NTH INTEGER Y 备份片文件编号 6 FILE_NAME VARCHAR(1024) Y 备份文件名 7 BKP_LEN BIGINT Y 备份片长度
以数据库备份为例，查看备份集中的备份片信息。
SQL> backup database to db_rac_bak backupset '/dm7/backup/db_rac_bak';
executed successfully
used time: 00:00:01.451. Execute id is 259.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/db_rac_bak');
LINEID SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/db_rac_bak')
---------- ---------------------------------------------------------
1 1
used time: 1.664(ms). Execute id is 260.
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- ----------------------
1 /dm7/backup/db_rac_bak
2 +DMDATA/data/rac/bak
used time: 0.775(ms). Execute id is 261.
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 DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE
---------- ----------- ----------- ----------- ----------- ---------------------- ----------- ----------- ----------- ----------- ----------- --------- --------------------------- ----- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ ----------- ----------- -------------------- ------------ -----------
1 DISK -274484992 -1 DB_RAC_BAK /dm7/backup/db_rac_bak 0 0 1 rac -1 2020-05-30 15:16:49.000122 0 0 0 0 33554432 51236 51244 2 6 1 0 0 49398 117507596 0
used time: 00:00:01.020. Execute id is 262.
SQL> select backuppath,bkp_nth,file_name,bkp_len from v$backupset_bkp where backuppath='/dm7/backup/db_rac_bak';
LINEID BACKUPPATH BKP_NTH FILE_NAME BKP_LEN
---------- ---------------------- ----------- ---------------- --------------------
1 /dm7/backup/db_rac_bak 0 db_rac_bak.bak 26688000
2 /dm7/backup/db_rac_bak 1 db_rac_bak_1.bak 6144
used time: 00:00:01.025. Execute id is 265.V$BACKUPSET_SEARCH_DIRS显示备份集搜索目录。
SQL> desc v$backupset_search_dirs LINEID NAME TYPE$ NULLABLE ---------- ---- ------------- -------- 1 DIR VARCHAR(1024) Y 备份集搜索目录
查询V$BACKUPSET_SEARCH_DIRS显示当前会话已添加的备份目录，即备份集搜索目录。若用户没有添加备份目录，那么仅显示默认的备份目录。
SQL> select sf_bakset_backup_dir_remove_all(); LINEID SF_BAKSET_BACKUP_DIR_REMOVE_ALL() ---------- --------------------------------- 1 1 used time: 0.882(ms). Execute id is 267. SQL> select * from v$backupset_search_dirs; LINEID DIR ---------- -------------------- 1 +DMDATA/data/rac/bak used time: 0.965(ms). Execute id is 268.
如果添加备份目录，查询结果包括默认备份目录和用户添加的备份目录。
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/db_rac_bak');
LINEID SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/db_rac_bak')
---------- ---------------------------------------------------------
1 1
used time: 0.894(ms). Execute id is 269.
SQL> select * from v$backupset_search_dirs;
LINEID DIR
---------- ----------------------
1 /dm7/backup/db_rac_bak
2 +DMDATA/data/rac/bak
used time: 0.714(ms). Execute id is 270.V$BACKUPSET_TABLE显示表备份集中备份表信息，仅表备份有效。
SQL> desc v$backupset_table LINEID NAME TYPE$ NULLABLE ---------- ------------ ------------- -------- 1 DEVICE_TYPE VARCHAR(10) Y 备份集存储介质类型 2 BACKUP_ID INTEGER Y 备份ID 3 BACKUPNAME VARCHAR(512) Y 备份名 4 BACKUPPATH VARCHAR(1024) Y 备份路径 5 SCHEMANAME VARCHAR(512) Y 备份表所属的模式名 6 USERNAME VARCHAR(512) Y 执行表备份的用户名 7 TSNAME VARCHAR(512) Y 备份表存储的表空间名 8 TABLENAME VARCHAR(512) Y 备份表名 9 TABLETYPE INTEGER Y 表类型 10 INIT_SQL VARCHAR(4096) Y 完整建表语句，忽略引用约束（语句可能会被截断） 11 DCONS_SQL VARCHAR(4096) Y 备份表中被禁用约束的创建语句（语句可能会被截断，DMRMAN可查看完整语句） 12 DIDX_SQL VARCHAR(4096) Y 备份表中无效二级索引的创建语句（语句可能会被截断，DMRMAN可查看完整语句） 13 BIDX_NUM INTEGER Y 备份集中备份的二级索引个数 14 META_VERSION INTEGER Y 当前表备份的元信息的版本号
下面创建表备份并查看备份中备份名、备份路径、表名等信息。
SQL> create table tab_for_info(c1 int);
executed successfully
used time: 12.459(ms). Execute id is 277.
SQL> insert into tab_for_info values(1);
affect rows 1
used time: 1.779(ms). Execute id is 278.
SQL> commit;
executed successfully
used time: 1.775(ms). Execute id is 279.
SQL> backup table tab_for_info to tab_for_info_bak backupset '/dm7/backup/tab_for_info_bak';
executed successfully
used time: 00:00:01.133. Execute id is 280.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/tab_for_info_bak');
LINEID SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/tab_for_info_bak')
---------- ---------------------------------------------------------------
1 1
used time: 1.250(ms). Execute id is 281.
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 DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE
---------- ----------- ----------- ----------- ---------------- ---------------------------- ----------- ----------- ----------- ------------------- ----------- --------- --------------------------- ----- ------------ -------------- ----------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ ----------- ----------- -------------------- ------------ -----------
1 DISK 378752781 -1 TAB_FOR_INFO_BAK /dm7/backup/tab_for_info_bak 2 0 3 SYSDBA.TAB_FOR_INFO -1 2020-05-30 15:31:29.000827 0 0 0 0 33554432 51236 51284 1 0 1 0 0 49398 117507596 0
used time: 00:00:01.019. Execute id is 282.
SQL> select backupname,tablename from v$backupset_table;
LINEID BACKUPNAME TABLENAME
---------- ---------------- ------------
1 TAB_FOR_INFO_BAK TAB_FOR_INFO
used time: 00:00:01.018. Execute id is 283.V$BACKUPSET_SUBS显示并行备份中生成的子备份集信息
SQL> desc v$backupset_subs LINEID NAME TYPE$ NULLABLE ---------- ----------- ------------- -------- 1 DEVICE_TYPE VARCHAR(10) Y 备份集存储介质类型 2 BACKUPNAME VARCHAR(512) Y 备份名 3 BACKUP_ID INTEGER Y 备份ID 4 PARENT_ID INTEGER Y 子备份集所属主备份集ID 5 BACKUPPATH VARCHAR(1024) Y 各备份集绝对路径 6 BKP_NUM INTEGER Y 各备份集中备份片文件个数，可能为0 7 DBF_NUM INTEGER Y 各备份集中备份数据文件个数，可能为0
数据库和表空间支持并行备份，备份后会在主备份集中生成多个子备份集，查询视图V$BACKUPSET_SUBS可获取子备份集中包含的备份片文件个数及备份数据文件个数等信息。以创建数据库并行备份为例，查看子备份集的相关信息。
SQL> backup database full to db_rac_bak backupset '/dm7/backup/db_rac_bak' parallel 4;
executed successfully
used time: 00:00:05.766. Execute id is 304.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/db_rac_bak');
LINEID SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/db_rac_bak')
---------- ---------------------------------------------------------
1 1
used time: 0.918(ms). Execute id is 305.
SQL> select backuppath,bkp_num,dbf_num from v$backupset_subs;
LINEID BACKUPPATH BKP_NUM DBF_NUM
---------- ----------------------------------- ----------- -----------
1 /dm7/backup/db_rac_bak/db_rac_bak_2 1 1
2 /dm7/backup/db_rac_bak/db_rac_bak_3 1 1
3 /dm7/backup/db_rac_bak/db_rac_bak_0 1 2
4 /dm7/backup/db_rac_bak/db_rac_bak_1 1 1
used time: 13.850(ms). Execute id is 306.看完上述内容，你们对DM7中怎么利用disql管理备份有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
