备份集过期时间_DM7如何使用disql管理备份?

880bb2a446520a88b06ea7277319a63f.png

下面介绍如何使用DIsql工具管理数据库备份、表空间备份、表备份,以及归档备份。本节内容主要包括:

  1. 概述
  2. 备份目录管理
  3. 备份集管理
  4. 备份信息查看

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)
)

...

✨ 接下来内容请点击【原文】进行查看~

更多数据库相关内容,可访问【墨天轮】进行浏览。

a3193fe69c8a9a8daaf6db6b707c832d.png
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页