DM7使用disql管理备份

下面介绍如何使用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)
)

参数说明:
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.

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.