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.

DM7联机执行SQL语句进行加密备份与设置跟踪日志

联机执行SQL语句进行加密备份与设置跟踪日志
下面将介绍联机执行SQL语句进行数据备份的高级特性,主要内容包括:
1. 加密备份
2. 设置跟踪日志文件
DM7的数据库、表空间、表备份和归档备份均支持创建加密备份和备份时设置跟踪日志文件,本节仅以数据库为例说明这两种类型备份的创建。

1.加密备份
DM7提供加密备份的方式保护用户的备份集,没有权限的用户无法访问加密的备份集。备份语句中通过指定IDENTIFIED BY…WITH ENCRYPTION…ENCRYPT WITH…执行加密备份。其中,参数IDENTIFIED BY指定加密密码,长度不超过128字节,若长度超长服务器会报语法分析出错;参数WITH ENCRYPTION指定加密类型,加密类型分为简单加密和复杂加密,简单加密是对备份文件设置口令,但文件内容仍以明文存储,复杂加密则对备份文件进行完全的加密,备份文件以密文方式存储,用户可根据备份数据的重要程度选择加密类型;参数ENCRYPT WITH指定加密算法,不同加密算法具体见参数说明,也可通过“SELECT * FROM V$CIPHERS”语句查询DM7支持的加密算法,其中算法MD5和SHA1不能在此处使用。默认使用的加密算法为AES256_CFB。

加密备份过程中参数IDENTIFIED BY必须指定,参数WITH ENCRYPTION和参数ENCRYPT WITH可不指定,此时WITH ENCRYPTION默认值为1,ENCRYPT WITH默认值为AES256_CFB。例如,以下两种加密备份语句都是合法的:

SQL> backup database to db_rac_backup_encrypt_01 backupset '/dm7/backup/db_rac_backup_encrypt_2020052901' identified by "abcd123456";
executed successfully
used time: 00:00:01.379. Execute id is 85.
SQL> backup database to db_rac_backup_encrypt_02 backupset '/dm7/backup/db_rac_backup_encrypt_2020052902' identified by "abcd123456" encrypt with rc4;
executed successfully
used time: 00:00:01.313. Execute id is 86.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/db_rac_backup_encrypt_2020052901');

LINEID     SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/db_rac_backup_encrypt_2020052901')
---------- -------------------------------------------------------------------------------
1          1

used time: 2.066(ms). Execute id is 87.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/db_rac_backup_encrypt_2020052902');

LINEID     SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/db_rac_backup_encrypt_2020052902')
---------- -------------------------------------------------------------------------------
1          1

used time: 1.532(ms). Execute id is 88.
SQL> select * from v$backupset where backup_path like '/dm7/backup/db_rac_backup_encrypt_202005290%';

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        1180634048  -1          DB_RAC_BACKUP_ENCRYPT_01 /dm7/backup/db_rac_backup_encrypt_2020052901 0           0           1           rac         -1                    2020-05-29 16:41:40.000665        1            0              0           0           33554432    50908                51058                2           4           1            0           0           49398                117507596    0
2          DISK        244985784   -1          DB_RAC_BACKUP_ENCRYPT_02 /dm7/backup/db_rac_backup_encrypt_2020052902 0           0           1           rac         -1                    2020-05-29 16:43:00.000380        1            0              0           0           33554432    50908                51064                2           4           1            0           0           49398                117507596    0

used time: 00:00:01.053. Execute id is 89.

若指定了加密密码,但加密类型WITH ENCRYPTION参数指定为0,则为非加密备份,如下所示:

SQL> backup database to db_rac_backup_encrypt_03 backupset '/dm7/backup/db_rac_backup_encrypt_2020052903' identified by "abcd123456" with encryption 0;
executed successfully
used time: 00:00:01.313. Execute id is 90.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/db_rac_backup_encrypt_2020052903');

LINEID     SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/db_rac_backup_encrypt_2020052903')
---------- -------------------------------------------------------------------------------
1          1

used time: 1.899(ms). Execute id is 91.
SQL> select * from v$backupset where backup_path like '/dm7/backup/db_rac_backup_encrypt_202005290%';

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        1180634048  -1          DB_RAC_BACKUP_ENCRYPT_01 /dm7/backup/db_rac_backup_encrypt_2020052901 0           0           1           rac         -1                    2020-05-29 16:41:40.000665        1            0              0           0           33554432    50908                51058                2           4           1            0           0           49398                117507596    0
2          DISK        244985784   -1          DB_RAC_BACKUP_ENCRYPT_02 /dm7/backup/db_rac_backup_encrypt_2020052902 0           0           1           rac         -1                    2020-05-29 16:43:00.000380        1            0              0           0           33554432    50908                51064                2           4           1            0           0           49398                117507596    0
3          DISK        1995527143  -1          DB_RAC_BACKUP_ENCRYPT_03 /dm7/backup/db_rac_backup_encrypt_2020052903 0           0           1           rac         -1                    2020-05-29 16:46:17.000633        0            0              0           0           33554432    50908                51070                2           4           1            0           0           49398                117507596    0

used time: 00:00:01.069. Execute id is 92.

从encrypt_type列可以看到备份集/dm7/backup/db_rac_backup_encrypt_2020052903为0,也就证明了备份是没有加密的。

下面以数据库完全备份为例,创建加密密码为“abcd123456”,加密算法为“rc4”的复杂类型数据库加密备份,完整步骤如下:
1) 配置归档 。
2) 保证数据库处于OPEN状态。
3) 备份数据库,输入以下语句:

SQL> backup database to db_rac_backup_encrypt_04 backupset '/dm7/backup/db_rac_backup_encrypt_2020052904' identified by "abcd123456" with encryption 2 encrypt with rc4;
executed successfully
used time: 00:00:01.356. Execute id is 93.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/db_rac_backup_encrypt_2020052904');

LINEID     SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/db_rac_backup_encrypt_2020052904')
---------- -------------------------------------------------------------------------------
1          1

used time: 1.868(ms). Execute id is 94.
SQL> select * from v$backupset where backup_path='/dm7/backup/db_rac_backup_encrypt_2020052904';

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        141217020   -1          DB_RAC_BACKUP_ENCRYPT_04 /dm7/backup/db_rac_backup_encrypt_2020052904 0           0           1           rac         -1                    2020-05-29 16:49:56.000930        2            0              0           0           33554432    50908                51076                2           4           1            0           0           49398                117507596    0

used time: 00:00:01.067. Execute id is 95.

对于增量备份加密,如果基备份存在加密,则使用的加密算法和加密密码必须与基备份中一致;如果基备份未进行加密处理,则对增量备份使用的加密密码和算法没有特殊要求。

SQL> backup database increment base on backupset '/dm7/backup/db_rac_backup_encrypt_2020052904' backupset '/dm7/backup/db_rac_increment_backup_encrypt_2020052901' identified by "abcd123456" with encryption 2 encrypt with rc4;
executed successfully
used time: 00:00:01.262. Execute id is 96.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/db_rac_increment_backup_encrypt_2020052901');

LINEID     SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/db_rac_increment_backup_encrypt_2020052901')
---------- -----------------------------------------------------------------------------------------
1          1

used time: 2.210(ms). Execute id is 97.
SQL> select * from v$backupset where backup_path='/dm7/backup/db_rac_increment_backup_encrypt_2020052901';

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        195701720   -1          DB_INCR_rac_20200529_165327_000906 /dm7/backup/db_rac_increment_backup_encrypt_2020052901 1           0           1           rac         -1          DB_RAC_BACKUP_ENCRYPT_04 2020-05-29 16:53:29.000129        2            0              0           0           33554432    50908                51082                2           4           1            0           0           49398                117507596    0

used time: 00:00:01.075. Execute id is 98.

2.设置跟踪日志文件
DM7备份时支持设置跟踪日志文件,日志记录了SBT接口的调用过程,用户通过查看日志可跟踪备份还原过程。

与生成跟踪日志文件相关的参数有两个:TRACE FILE和TRACE LEVEL。TRACE FILE用于指定生成的跟踪日志文件路径,TRACE LEVEL表示是否启用TRACE。TRACE LEVEL有效值包括1和2。1表示不启用TRACE功能,2表示启用,系统默认值为1。

指定参数TRACE FILE但TRACE LEVEL值设置为1即不启用TRACE功能,会生成TRACE文件,但不会写入TRACE信息。如下所示:

SQL> backup database backupset '/dm7/backup/db_rac_backup_2020052905' trace file '/dm7/backup/db_rac_trace.log' trace level 1;
executed successfully
used time: 00:00:01.719. Execute id is 99.

[dmdba@dmrac1 backup]$ cat db_rac_trace.log

TRACE LEVEL值设置为2即启用TRACE功能,但若TRACE FILE没有指定,系统默认在执行码路径的log目录下生成DM_SBTTRACE_年月.log文件。如下所示:

SQL> backup database to db_rac_backup_trace_2020052901 backupset '/dm7/backup/db_rac_backup_trace_2020052901' trace level 2;
executed successfully
used time: 00:00:01.354. Execute id is 101.

[dmdba@dmrac1 log]$ pwd
/dm7/log

[dmdba@dmrac1 log]$ cat dm_SBTTRACE_202005.log
2020-05-29 17:01:27 sbtbackup(type=1, name=db_rac_backup_trace_2020052901.bak)
2020-05-29 17:01:27 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:01:27 sbtwrite(buf != NULL, buf_len = 5095936)
2020-05-29 17:01:27 sbtwrite(buf != NULL, buf_len = 21496320)
2020-05-29 17:01:27 sbtwrite(buf != NULL, buf_len = 49664)
2020-05-29 17:01:27 sbtclose()
2020-05-29 17:01:27 sbtinfo(!NULL)
file [db_rac_backup_trace_2020052901.bak] finished, name:[db_rac_backup_trace_2020052901.bak],created time:[2020-05-29 17:01:27],expirated time:[2105-12-31 23:59:59],label:[],comment:[]

2020-05-29 17:01:27 sbtbackup(type=1, name=db_rac_backup_trace_2020052901_1.bak)
2020-05-29 17:01:27 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:01:27 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:01:27 sbtclose()
2020-05-29 17:01:27 sbtinfo(!NULL)
file [db_rac_backup_trace_2020052901_1.bak] finished, name:[db_rac_backup_trace_2020052901_1.bak],created time:[2020-05-29 17:01:27],expirated time:[2105-12-31 23:59:59],label:[],comment:[]

2020-05-29 17:01:27 sbtbackup(type=3, name=db_rac_backup_trace_2020052901.meta)
2020-05-29 17:01:27 sbtwrite(buf != NULL, buf_len = 49152)
2020-05-29 17:01:27 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:01:27 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:01:27 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:01:27 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:01:27 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:01:27 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:01:27 sbtwrite(buf != NULL, buf_len = 512)
2020-05-29 17:01:27 sbtclose()
2020-05-29 17:01:27 sbtinfo(!NULL)
file [db_rac_backup_trace_2020052901.meta] finished, name:[db_rac_backup_trace_2020052901.meta],created time:[2020-05-29 17:01:27],expirated time:[2105-12-31 23:59:59],label:[],comment:[]

若TRACE FILE使用相对路径,日志文件生成在执行码同级目录下。
以数据库完全备份为例,为备份设置跟踪日志文件的操作步骤如下:
1) 配置归档,参考3.1节。
2) 保证数据库处于OPEN状态。
3) 备份数据库,输入以下语句:

SQL> backup database to db_rac_backup_trace_2020052903 backupset '/dm7/backup/db_rac_backup_trace_2020052903' trace file '/dm7/backup/db_rac_trace.log' trace level 2;
executed successfully
used time: 00:00:01.321. Execute id is 104.

[dmdba@dmrac1 backup]$ cat db_rac_trace.log
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_rac_backup_trace_2020052902/db_rac_backup_trace_2020052902.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 25088)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/ts_main_increment_bak_20200529_01/ts_main_increment_bak_20200529_01.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 8704)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_rac_backup_encrypt_2020052904/db_rac_backup_encrypt_2020052904.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 25088)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_backup_20200528_01/db_backup_20200528_01.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 25088)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_rac_backup_encrypt_2020052903/db_rac_backup_encrypt_2020052903.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 25088)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_backup_20200529_01/db_backup_20200529_01.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 25088)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_rac_backup_encrypt_2020052902/db_rac_backup_encrypt_2020052902.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 25088)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_backup_20200528_04/db_backup_20200528_04.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 25088)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_backup_20200528_02/db_backup_20200528_02.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 25088)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_rac_backup_2020052905/db_rac_backup_2020052905.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 25088)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_backup_20200528_06/db_backup_20200528_06.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 182272)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/ts_main_full_bak_20200529_02/ts_main_full_bak_20200529_02.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 8704)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_rac_increment_backup_encrypt_2020052901/db_rac_increment_backup_encrypt_2020052901.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 25088)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/ts_main_increment_bak_20200529_02/ts_main_increment_bak_20200529_02.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 8704)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/ts_main_full_bak_20200529_01/ts_main_full_bak_20200529_01.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 8704)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_backup_20200528_05/db_backup_20200528_05.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 25088)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_increment_backup_20200529_01/db_increment_backup_20200529_01.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 25088)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/ts_main_increment_bak_2020052903/ts_main_increment_bak_2020052903.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 8704)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_rac_backup_encrypt_2020052901/db_rac_backup_encrypt_2020052901.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 25088)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_backup_20200528_03/db_backup_20200528_03.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 25088)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_increment_backup_20200529_02/db_increment_backup_20200529_02.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 25088)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=/dm7/backup/db_rac_backup_trace_2020052901/db_rac_backup_trace_2020052901.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 25088)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=+DMDATA/data/rac/bak/ts_backup_main_01/ts_backup_main_01.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 8704)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=+DMDATA/data/rac/bak/tab_t1_bak_01/tab_t1_bak_01.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 5632)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtrestore(filepath=+DMDATA/data/rac/bak/table_tab_01_backup_20200529/table_tab_01_backup_20200529.meta)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 45056)
2020-05-29 17:15:13 sbtread(buf != NULL, buf_len = 5632)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtend(del_flag=FALSE)
2020-05-29 17:15:13 sbtbackup(type=1, name=db_rac_backup_trace_2020052903.bak)
2020-05-29 17:15:13 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtwrite(buf != NULL, buf_len = 5095936)
2020-05-29 17:15:13 sbtwrite(buf != NULL, buf_len = 21496320)
2020-05-29 17:15:13 sbtwrite(buf != NULL, buf_len = 49664)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtinfo(!NULL)
file [db_rac_backup_trace_2020052903.bak] finished, name:[db_rac_backup_trace_2020052903.bak],created time:[2020-05-29 17:15:13],expirated time:[2105-12-31 23:59:59],label:[],comment:[]

2020-05-29 17:15:13 sbtbackup(type=1, name=db_rac_backup_trace_2020052903_1.bak)
2020-05-29 17:15:13 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtwrite(buf != NULL, buf_len = 2560)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtinfo(!NULL)
file [db_rac_backup_trace_2020052903_1.bak] finished, name:[db_rac_backup_trace_2020052903_1.bak],created time:[2020-05-29 17:15:13],expirated time:[2105-12-31 23:59:59],label:[],comment:[]

2020-05-29 17:15:13 sbtbackup(type=3, name=db_rac_backup_trace_2020052903.meta)
2020-05-29 17:15:13 sbtwrite(buf != NULL, buf_len = 49152)
2020-05-29 17:15:13 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtwrite(buf != NULL, buf_len = 4096)
2020-05-29 17:15:13 sbtwrite(buf != NULL, buf_len = 512)
2020-05-29 17:15:13 sbtclose()
2020-05-29 17:15:13 sbtinfo(!NULL)
file [db_rac_backup_trace_2020052903.meta] finished, name:[db_rac_backup_trace_2020052903.meta],created time:[2020-05-29 17:15:13],expirated time:[2105-12-31 23:59:59],label:[],comment:[]

2020-05-29 17:15:13 sbtend(del_flag=FALSE)

如果指定的TRACE文件已存在,服务器不会覆盖已存在的文件而是在已有文件基础上继续记录日志。

DM7联机执行SQL语句进行归档备份

联机执行SQL语句进行归档备份
本节主要介绍使用DIsql完成归档备份及实施一些备份策略,如限制备份集大小、加密备份等。主要内容包括:
1. 概述
2. 设置备份选项
3. 备份归档

1. 概述
在DIsql工具中使用BACKUP语句可以备份归档日志。使用归档备份的前提:一是,归档文件的db_magic值和库的db_magic值必须一样;二是,服务器必须配置归档;三是,归档日志必须连续,如果出现不连续的情况,前面的会忽略,仅备份最新的连续部分。如果未收集到指定范围内的归档,则不会备份。联机备份的时候经常会切换归档文件,最后一个归档总是空的,所以最后一个归档不会被备份。

DIsql中输入以下即可备份归档:

SQL> select * from v$dm_ini where para_name='BAK_PATH';

LINEID     PARA_NAME PARA_VALUE                          MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE                          FILE_VALUE                          DESCRIPTION      PARA_TYPE
---------- --------- ----------------------------------- --------- --------- ------- ----------------------------------- ----------------------------------- ---------------- ---------
1          BAK_PATH  /dm_home/dmdba/dmdbms/data/jydm/bak NULL      NULL      N       /dm_home/dmdba/dmdbms/data/jydm/bak /dm_home/dmdba/dmdbms/data/jydm/bak backup file path READ ONLY

used time: 7.311(ms). Execute id is 1219.


SQL> backup archive log all to archivelog_backup_2020052901 backupset 'archivelog_backup_2020052901';
backup archive log all to archivelog_backup_2020052901 backupset 'archivelog_backup_2020052901';
[-7109]:Pipe connect failure.
used time: 2.618(ms). Execute id is 0.

在Oracle Linux7.1中要使用dmap需要手动启动DmAPService服务和dmap执行码,在Redhat中启动DmAPService服务时会同时启动dmap

[root@shard1 bak]# systemctl start DmAPService

[dmdba@shard1 ~]$ cd /dm_home/dmdba/dmdbms/bin
[dmdba@shard1 bin]$ ./dmap
dmap V7.1.6.46-Build(2018.02.08-89107)ENT
dmap is ready

SQL> backup archive log all to archivelog_backup_2020052901 backupset 'archivelog_backup_2020052901';
executed successfully
used time: 00:00:34.482. Execute id is 1234.

SQL> select * from v$backupset where backup_path='/dm_home/dmdba/dmdbms/data/jydm/bak/archivelog_backup_2020052901';

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        -30479448   -1          ARCHIVELOG_BACKUP_2020052901 /dm_home/dmdba/dmdbms/data/jydm/bak/archivelog_backup_2020052901 3           0           4           ARCHIVE     -1                    2020-05-29 18:44:25.000480        0            0              1           0           33554432    8236220              15088441             1           9           1            0           0           15088441             117507596    0

used time: 00:00:01.010. Execute id is 1266.


SQL> select * from v$parameter where name='BAK_USE_AP';

LINEID     ID          NAME       TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- ----------- ---------- ---- ----- --------- ---------- -----------------------------------------------------------------------------------
1          541         BAK_USE_AP SYS  1     1         1          backup use assistant plus-in, 0:use sub process; 1:use AP; 2:not use AP. default 1.

used time: 6.765(ms). Execute id is 1252.
SQL> select * from v$parameter where name='BAK_POLICY';

LINEID     ID          NAME       TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- ----------- ---------- ---- ----- --------- ---------- --------------
1          7           BAK_POLICY SYS  0     0         0          backup pattern

used time: 7.405(ms). Execute id is 1253.

备份集“archivelog_backup_2020052901”会生成到默认的备份路径下。如要设置其他备份选项需参考下文的备份归档语法。

语法如下:

BACKUP
[ALL | [FROM LSN ]| [UNTIL LSN ]|[LSN BETWEEN  AND ] | [FROM TIME '

ALL:备份所有的归档;

FROM LSN:指定备份的起始lsn。

UNTIL LSN:指定备份的截止lsn。归档日志的有效LSN范围(起始lsn,截止lsn)可以通过V$ARCH_FILE查看,或者通过dmclvt工具分析日志的结果查看。如果用户无法确定准确的lsn,也可以指定一个模糊的lsn值,取值范围(1~9223372036854775807)。只要指定的FROM LSN、UNTIL LSN与有效LSN范围有重叠部分,就会备份包含重叠部分的完整日志文件。

FROM TIME:指定备份的开始的时间点。

UNTILTIME:指定备份的截止的时间点。
BETWEEN … AND …:指定备份的区间。指定区间后,只会备份指定区间内的归档文件。:搜索过滤。搜索过滤仅限于根据备份指定条件能找到的所有归档备份集。1) num TIMES,取值范围为0~2147483647,指若归档文件已经备份了num次,则不再备份;否则备份。如num=3,则认为已经备份了3次的归档文件就不再备份。若num=0,则认为所有都不需要备份。2)SINCE TIME ‘datetime_String’,指定时间开始没有备份的归档文件进行备份。3)若以上两种均未指定,则备份所有未备份过的归档日志文件。

DELETE INPUT:用于指定备份完成之后,是否删除归档操作。

TO:指定生成备份名称。若未指定,系统随机生成,默认备份名格式为:ARCH_备份时间。其中,备份时间为开始备份的系统时间。

BACKUPSET:指定当前备份集生成路径,若指定为相对路径,则在默认备份路径中生成备份集。若不指定具体备份集路径,则在默认备份路径下,以约定归档备份集命名规则生成默认的归档备份集目录。归档备份默认备份集目录名生成规则:ARCH_LOG_时间,如
ARCH_LOG_20160518_143057_123456。表明该备份集为2016年5月18日14时30分57秒123456毫秒时生成的归档备份集。

DEVICE TYPE:指存储备份集的介质类型,支持DISK和TAPE,默认DISK。DISK表示存储备份集到磁盘,TAPE表示存储到磁带。

PARMS:只对介质类型为TAPE时有效。

BACKUPINFO:备份的描述信息。最大不超过256个字节。

MAXPIECESIZE:最大备份片文件大小上限,以M为单位,最小128M,32位系统最大2G,64位系统最大128G。

IDENTIFIED BY:指定备份时的加密密码。密码应该使用双引号括起来,这样避免一些特殊字符通不过语法检测。密码的设置规则遵行ini参数pwd_policy指定的口令策略。

WITH ENCRYPTION:指定加密类型,0表示不加密,不对备份文件进行加密处理;1表示简单加密,对备份文件设置口令,但文件内容仍以明文存储;2表示完全数据加密,对备份文件进行完全的加密,备份文件以密文方式存储。

ENCRYPT WITH:加密算法。缺省情况下,算法为AES256_CFB。

加密算法包括:

DES_ECB、DES_CBC、DES_CFB、DES_OFB、DESEDE_ECB、
DESEDE_CBC、DESEDE_CFB、DESEDE_OFB、AES128_ECB、
AES128_CBC、AES128_CFB 、AES128_OFB、AES192_ECB、
AES192_CBC、AES192_CFB 、AES192_OFB、AES256_ECB、
AES256_CBC、AES256_CFB 、AES256_OFB 、RC4

COMPRESSED:取值范围0~9。0表示不压缩,1表示1级压缩,9表示9级压缩。压缩级别越高,压缩越慢,但压缩比越高。若未指定,但指定COMPRESSED,则默认1;否则,默认0。

WITHOUT LOG:只是语法支持,不起任何作用。

TRACE FILE:指定生成的TRACE文件。启用TRACE,但不指定TRACE FILE时,默认在DM数据库系统的log目录下生成DM_SBTTRACE_年月.LOG文件;若使用相对路径,则生成在执行码同级目录下。若用户指定,则指定的文件不能为已经存在的文件,否则报错;也不可以为ASM文件。

TRACE LEVEL:有效值1、2,默认为1表示不启用TRACE,此时若指定了TRACE FILE,会生成TRACE文件,但不写入TRACE信息;为2启用TRACE并写入TRACE相关内容。

TASK THREAD:备份过程中数据处理过程线程的个数,取值范围0~64,默认为4。若指定为0,则调整为1;若指定超过当前系统主机核数,则调整为当前主机核数。线程数(TASK THREAD)*并行数(PARALLEL)不得超过512。

PARALLEL:指定并行备份的并行数,取值范围0~128。若不指定,则默认为4,指定0或者1均认为为非并行备份。并行备份不支持介质为TAPE的备份。线程数(TASK THREAD)*并行数(PARALLEL)不得超过512。

2. 设置备份选项
归档备份常用的备份选项有设置备份名、设置备份集路径、指定介质参数、添加备份描述等,设置方式同数据库备份相同。

3. 备份归档
归档备份拷贝指定归档目录下的所有的归档文件到备份集中,并记录各个归档文件的属性,文件大小,LSN区间等。归档备份不存在增量备份
之说。当需要保存库的归档时,可以使用归档备份。
完整的备份归档步骤如下:
1) 配置归档,参考3.1节。
2) 数据库处于OPEN或者MOUNT状态。
3) DIsql中输入备份数据库语句。
例如,备份归档日志,通过LSN BETWEEN … AND …来指定起始和截至LSN。
首先,确定LSN范围。

SQL> select ARCH_LSN, CLSN, PATH from V$ARCH_FILE;

LINEID     ARCH_LSN             CLSN                 PATH
---------- -------------------- -------------------- ----------------------------------------------------------------------
1          0                    0                    /dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200529193925775_0.log
2          8236220              8493250              /dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200407140301179_0.log
3          8493250              9294927              /dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200407225606099_0.log
4          9294928              11047946             /dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200410180147714_0.log
5          11047947             11054313             /dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200425175952636_0.log
6          11054313             12906866             /dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200509201513282_0.log
7          12906867             13775969             /dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200513203613043_0.log
8          13775970             14461220             /dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200526003546144_0.log
9          14461221             14942037             /dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200526003632028_0.log
10         14942037             15088441             /dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200528230749046_0.log
11         15088442             15092081             /dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200529184351533_0.log
12         15092082             15092086             /dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200529193755551_0.log

12 rows got

通过查询结果选出备份的起始LSN和截至LSN。比如15092082 15092086

其次,备份归档。

SQL> backup archivelog lsn between 15092082 and 15092086 backupset 'arch_backup_lsn_15092082_15092086';
executed successfully
used time: 00:00:01.174. Execute id is 1316.

SQL> select * from v$backupset where backup_path='/dm_home/dmdba/dmdbms/data/jydm/bak/arch_backup_lsn_15092082_15092086';

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        -1385444837 -1          ARCH_20200529_194216_000768 /dm_home/dmdba/dmdbms/data/jydm/bak/arch_backup_lsn_15092082_15092086 3           0           4           ARCHIVE     -1                    2020-05-29 19:42:17.000861        0            0              1           0           33554432    15092082             15092086             1           1           1            0           0           15092086             117507596    0

used time: 00:00:01.013. Execute id is 1317.

DM7联机执行SQL语句进行表备份

联机执行SQL语句进行表备份
下面将介绍使用DIsql完成表备份及实施一些备份策略,如限制备份集大小、加密备份等。主要内容包括:
1. 概述
2. 设置备份选项
3. 备份表

与备份数据库与表空间不同,备份表不需要服务器配置归档,DIsql中输入以下即可备份用户表:

SQL> backup table t1 backupset 'tab_t1_bak_01';
executed successfully
used time: 00:00:14.215. Execute id is 77.

SQL> select * from v$backupset where backup_path like '+DMDATA/data/rac/bak/tab%';

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        -1366932633 -1          TAB_T1_20200529_144738_000092 +DMDATA/data/rac/bak/tab_t1_bak_01 2           0           3           SYSDBA.T1   -1                    2020-05-29 14:47:45.000864        0            0              0           0           33554432    50908                51025                1           0           1            0           0           49398                117507596    0

used time: 00:00:01.064. Execute id is 79.

备份集“tab_t1_bak_01”会生成到默认的备份路径下。如要设置其他备份选项需参考下文的备份表语法。
语法如下:

BACKUP TABLE < 表名>
[TO < 备份名>]
BACKUPSET ['< 备份集路径>'] [DEVICE TYPE < 介质类型> [PARMS '< 介质参数>']]
[BACKUPINFO '< 备份集描述>']
[MAXPIECESIZE < 备份片限制大小>]
[IDENTIFIED BY < 加密密码>[WITH ENCRYPTION][ENCRYPT WITH < 加密算法>]]
[COMPRESSED [LEVEL < 压缩级别>]]
[TRACE FILE ''] [TRACE LEVEL ]

TABLE:指定备份的表,只能备份用户表。

TO:指定生成备份名称。若未指定,系统随机生成,默认备份名格式为:DB_备份类型_表名_备份时间。其中,备份时间为开始备份的系统时间。

BACKUPSET:指定当前备份集生成路径,若指定为相对路径,则在默认备份路径中生成备份集。若不指定具体备份集路径,则在默认备份路径下以约定规则生成默认的表备份集目录。表备份默认备份集目录名生成规则:TAB_表名_BTREE_时间,如
TAB_T1_BTREE_20160518_143057_123456。表明该备份集为2016年5月18日14时30分57秒123456毫秒时生成的表名为T1的表备份集。若表名超长,使上述完整名称长度大于128个字节,则去掉表名字段,调整为TAB_BTREE_时间。

DEVICE TYPE:指存储备份集的介质类型,表备份暂时只支持DISK,表示存储备份集到磁盘。

PARMS:只对介质类型为TAPE时有效。

BACKUPINFO:备份的描述信息。最大不超过256个字节。

MAXPIECESIZE:最大备份片文件大小上限,以M为单位,最小128M,32位系统最大2G,64位系统最大128G。

IDENTIFIED BY:指定备份时的加密密码。密码应用双引号括起来,这样避免一些特殊字符通不过语法检测。密码的设置规则遵行ini参数pwd_policy指定的口令策略。

WITH ENCRYPTION:指定加密类型,0表示不加密,不对备份文件进行加密处理;1表示简单加密,对备份文件设置口令,但文件内容仍以明文存储;2表示完全数据加密,对备份文件进行完全的加密,备份文件以密文方式存储。

ENCRYPT WITH:加密算法。缺省情况下,算法为AES256_CFB。

加密算法包括:

DES_ECB、DES_CBC、DES_CFB、DES_OFB、DESEDE_ECB、
DESEDE_CBC、DESEDE_CFB、DESEDE_OFB、AES128_ECB、
AES128_CBC、AES128_CFB 、AES128_OFB、AES192_ECB、
AES192_CBC、AES192_CFB 、AES192_OFB、AES256_ECB、
AES256_CBC、AES256_CFB 、AES256_OFB 、RC4

COMPRESSED:取值范围0~9。0表示不压缩,1表示1级压缩,9表示9级压缩。压缩级别越高,压缩越慢,但压缩比越高。若未指定,但指定COMPRESSED,则默认1;否则,默认0。

TRACE FILE:指定生成的TRACE文件。启用TRACE,但不指定TRACE FILE时,默认在DM数据库系统的log目录下生成DM_SBTTRACE_年月.LOG文件;若使用相对路径,则生成在执行码同级目录下。若用户指定,则指定的文件不能为已经存在的文件,否则报错;也不可以为ASM文件。

TRACE LEVEL:有效值1、2,默认为1表示不启用TRACE,此时若指定了TRACE FILE,会生成TRACE文件,但不写入TRACE信息;为2启用TRACE并写入TRACE相关内容。

使用说明:
1. 支持对用户的非分区行存储表和堆表进行备份。其中,系统表、临时表、物化视图表、物化视图附属表和日志表、特定模式(DBG_PKG/INFORMATION_SCHEMA/INFO_SCHEM/SYSREP/SYSGEO/SYSJOB/SYSCPT/SYS)下的表不支持备份。表列类型为对象类型的表不支持表备份。表备份不备份表上的注释,default表达式中函数定义,所以还原时需用户自行确认。
2. 当备份数据超过限制大小时,会生成新的备份文件,新的备份文件名是初始文件名后加文件编号。
3. 表备份时,其所属表空间必须处于联机状态。
4. 目前表备份不支持备份到TAPE介质上。

2. 设置备份选项
表备份常用的备份选项有设置备份名、设置备份集路径、指定介质参数、添加备份描述等,设置方式同数据库备份相同。

3. 备份表
表备份拷贝指定表所使用的所有数据页到备份集中,并记录各个数据页之间的逻辑关系用来恢复表数据结构。表备份均为联机完全备份,不需要备份归档日志,不存在增量备份之说。当数据库中某张表比较重要而又没必要备份整个数据库或表空间时就可以选择表备份。完整的备份表步骤如下:
1) 保证数据库处于OPEN状态。
2) 创建待备份的表TAB_01:

SQL> create table tab_01(c1 int);
executed successfully
used time: 32.117(ms). Execute id is 80.

3) DIsql中输入备份表语句,简单的备份语句如下:

SQL> backup table tab_01 to tab_tab_01_backup_20200529 backupset 'table_tab_01_backup_20200529';
executed successfully
used time: 00:00:13.981. Execute id is 81.

上面的语句将在默认备份目录+DMDATA/data/rac/bak(由BAK_PATH参数所指定)中生成备份集

SQL> select * from v$backupset where backup_path='+DMDATA/data/rac/bak/table_tab_01_backup_20200529';

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        2025284018  -1          TAB_TAB_01_BACKUP_20200529 +DMDATA/data/rac/bak/table_tab_01_backup_20200529 2           0           3           SYSDBA.TAB_01 -1                    2020-05-29 15:08:24.000485        0            0              0           0           33554432    50908                51055                1           0           1            0           0           49398                117507596    0

used time: 00:00:01.063. Execute id is 82.

DM7联机执行SQL语句进行表空间备份

联机执行SQL语句进行表空间备份
下面将描述了如何使用DIsql完成最基本的表空间备份及实施一些备份策略,如限制备份集大小、加密备份等。主要内容包括:
1. 概述
2. 设置备份选项
3. 备份表空间

1. 概述
在DIsql工具中使用BACKUP语句也可以备份单个表空间。同备份数据库一样,执行表空间备份服务器也必须运行在归档模式下,启动DIsql输入以下语句即可备份表空间:

SQL> backup tablespace main backupset 'ts_backup_main_01';
executed successfully
used time: 00:00:13.841. Execute id is 45.

默认的备份目录为+DMDATA/data/rac/bak

SQL> select * from v$dm_ini where para_name='BAK_PATH';

LINEID     PARA_NAME PARA_VALUE           MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE           FILE_VALUE           DESCRIPTION      PARA_TYPE
---------- --------- -------------------- --------- --------- ------- -------------------- -------------------- ---------------- ---------
1          BAK_PATH  +DMDATA/data/rac/bak NULL      NULL      N       +DMDATA/data/rac/bak +DMDATA/data/rac/bak backup file path READ ONLY

used time: 9.531(ms). Execute id is 48.
SQL>


SQL> select * from v$backupset where backup_path like '+DMDATA/data/rac/bak%';

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        2044941324  -1          TS_FULL_MAIN_20200529_091214_000914 +DMDATA/data/rac/bak/ts_backup_main_01 0           0           2           MAIN        4                     2020-05-29 09:12:22.000279        0            0              0           0           33554432    50908                50990                1           1           1            0           0           49398                117507596    0

used time: 00:00:01.041. Execute id is 50.


ASM>cd ts_backup_main_01
+DMDATA/data/rac/bak/ts_backup_main_01
Used time: 2.098(ms).
ASM>ls
        file : ts_backup_main_01.bak
        file : ts_backup_main_01.meta
total count 2.
Used time: 3.583(ms).

备份集“ts_backup_main_01”会生成到默认的备份路径下。如要设置其他备份选项需参考下文的联机备份表空间语法。

语法如下:
BACKUP TABLESPACE <表空间名> [FULL | INCREMENT [CUMULATIVE][WITH BACKUPDIR ‘<基备份搜索目录>'{,'<基备份搜索目录>’}]|
[BASE ON BACKUPSET ‘<基备份集目录>’]][TO <备份名>] BACKUPSET [‘<备份集路径>’]
[DEVICE TYPE <介质类型> [PARMS ‘<介质参数>’]]
[BACKUPINFO ‘<备份集描述>’] [MAXPIECESIZE <备份片限制大小>]
[IDENTIFIED BY <加密密码>[WITH ENCRYPTION][ENCRYPT WITH <加密算法>]] [COMPRESSED [LEVEL <压缩级别>]]
[TRACE FILE ‘<TRACE文件名>’] [TRACE LEVEL <TRACE日志级别>]
[TASK THREAD <线程数>][PARALLEL [<并行数>]];

表空间名:指定备份的表空间名称(除了temp表空间)。

FULL|INCREMENT:备份类型,FULL表示完全备份,INCREMENT表示增量备份。若不指定,默认为完全备份。

CUMULATIVE:用于增量备份中,指明为累积增量备份类型,若不指定则缺省为差异增量备份类型。

WITH BACKUPDIR:用于增量备份中,指定备份目录,最大长度为256个字节。若不指定,服务器自动在默认备份目录下搜索基备份。如果基备份不在默认的备份目录下,增量备份必须指定该参数。

BASE ON:用于增量备份中,指定基备份集目录。

TO:指定生成备份名称。若未指定,系统随机生成,默认备份名格式为:DB_备份类型_表空间名_备份时间。其中,备份时间为开始备份的系统时间。

BACKUPSET:指定当前备份集生成路径。若指定为相对路径,则在默认备份路径中生成备份集。若不指定,则在默认备份路径下以约定规则生成默认的表空间备份集目录。表空间级备份默认备份集目录名生成规则:TS_表空间名_备份类型_时间,如
TS_MAIN_INCREMENT_20160518_143057_123456。表明该备份集为2016年5月18日14时30分57秒123456毫秒时生成的表空间名为MAIN的表空间增量备份集。若表空间名称超长,使上述完整名称长度大于128个字节,则去掉表空间名字段,调整为TS_备份类型_时间。

DEVICE TYPE:指存储备份集的介质类型,支持DISK和TAPE,默认DISK。DISK表示存储备份集到磁盘,TAPE表示存储到磁带。

PARMS:只对介质类型为TAPE时有效。

BACKUPINFO:备份的描述信息。最大不超过256个字节。

MAXPIECESIZE:最大备份片文件大小上限,以M为单位,最小128M,32位系统最大2G,64位系统最大128G。

IDENTIFIED BY:指定备份时的加密密码。密码应用双引号括起来,这样避免一些特殊字符通不过语法检测。密码的设置规则遵行ini参数pwd_policy指定的口令策略。

WITH ENCRYPTION:指定加密类型,0表示不加密,不对备份文件进行加密处理;1表示简单加密,对备份文件设置口令,但文件内容仍以明文存;2表示完全数据加密,对备份文件进行完全的加密,备份文件以密文方式存储。

ENCRYPT WITH:加密算法。缺省情况下,算法为AES256_CFB。

加密算法包括:

DES_ECB、DES_CBC、DES_CFB、DES_OFB、DESEDE_ECB、
DESEDE_CBC、DESEDE_CFB、DESEDE_OFB、AES128_ECB、
AES128_CBC、AES128_CFB 、AES128_OFB、AES192_ECB、
AES192_CBC、AES192_CFB 、AES192_OFB、AES256_ECB、
AES256_CBC、AES256_CFB 、AES256_OFB 、RC4

COMPRESSED:取值范围0~9。0表示不压缩,1表示1级压缩,9表示9级压缩。压缩级别越高,压缩越慢,但压缩比越高。若未指定,但指定COMPRESSED,则默认1;否则,默认0。

TRACE FILE:指定生成的TRACE文件。启用TRACE,但不指定TRACE FILE时,默认在DM数据库系统的log目录下生成DM_SBTTRACE_年月.LOG文件;若使用相对路径,则生成在执行码同级目录下。若用户指定,则指定的文件不能为已经存在的文件,否则报错;也不可以为ASM文件。

TRACE LEVEL:有效值1、2,默认为1表示不启用TRACE,此时若指定了TRACE FILE,会生成TRACE文件,但不写入TRACE信息;为2启用TRACE并写入TRACE相关内容。

TASK THREAD:备份过程中数据处理过程线程的个数,取值范围0~64,默认为4。若指定为0,则调整为1;若指定超过当前系统主机核数,则调整为主机核数。线程数(TASK THREAD)*并行数(PARALLEL)不得超过512。

PARALLEL:指定并行备份的并行数,取值范围0~128。若不指定,则默认为4,指定0或者1均认为为非并行备份。若未指定关键PARALLEL,则认为非并行备份。并行备份不支持存在介质为TAPE的备份。线程数(TASK THREAD)*并行数(PARALLEL)不得超过512。

使用说明:
1. 当备份数据超过限制大小时,会生成新的备份文件,新的备份文件名是初始文件名后加文件编号。
2. 系统处于归档模式下时,才允许进行表空间备份。
3. MOUNT状态下,不允许进行表空间备份。
4. MPP环境不允许进行表空间备份。

2. 设置备份选项
表空间备份也可指定备份集路径、介质类型、备份名等备份选项。

增量备份指定基备份目录
BASE ON 参数用于增量备份中,用来指定基备份集的目录。如果不指定该参数,会在备份搜索目录中搜索最近一次的完全备份或增量备份作为这次增量备份的基备份。若需要在特定的备份集基础上执行增量备份就需要使用该参数。

下面以增量备份用户MAIN表空间为例,指定BASE ON参数执行增量备份:

SQL> backup tablespace main backupset '/dm7/backup/ts_main_full_bak_20200529_01';
executed successfully
used time: 00:00:01.133. Execute id is 51.

SQL> backup tablespace main increment backupset '/dm7/backup/ts_main_increment_bak_20200529_01';
executed successfully
used time: 00:00:01.126. Execute id is 52.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/ts_main_full_bak_20200529_01');

LINEID     SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/ts_main_full_bak_20200529_01')
---------- ---------------------------------------------------------------------------
1          1

used time: 2.024(ms). Execute id is 53.
SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/ts_main_increment_bak_20200529_01');

LINEID     SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/ts_main_increment_bak_20200529_01')
---------- --------------------------------------------------------------------------------
1          1

used time: 1.520(ms). Execute id is 54.


SQL> select * from v$backupset where backup_path like '/dm7/backup/ts%';

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        1493450986  -1          TS_FULL_MAIN_20200529_111133_000175 /dm7/backup/ts_main_full_bak_20200529_01      0           0           2           MAIN        4                                               2020-05-29 11:11:34.000272        0            0              0           0           33554432    50908                50996                1           1           1            0           0           49398                117507596    0
2          DISK        1885674227  -1          TS_INCR_MAIN_20200529_111346_000167 /dm7/backup/ts_main_increment_bak_20200529_01 1           0           2           MAIN        4           TS_FULL_MAIN_20200529_111133_000175 2020-05-29 11:13:47.000258        0            0              0           0           33554432    50908                51002                1           1           1            0           0           49398                117507596    0

used time: 00:00:01.048. Execute id is 55.

SQL> backup tablespace main increment base on backupset '/dm7/backup/ts_main_full_bak_20200529_01' backupset '/dm7/backup/ts_main_increment_bak_20200529_02';
executed successfully
used time: 00:00:01.133. Execute id is 57.

SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/ts_main_increment_bak_20200529_02');

LINEID     SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/ts_main_increment_bak_20200529_02')
---------- --------------------------------------------------------------------------------
1          1

used time: 1.748(ms). Execute id is 58.
SQL> select * from v$backupset where backup_path like '/dm7/backup/ts%';

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        1493450986  -1          TS_FULL_MAIN_20200529_111133_000175 /dm7/backup/ts_main_full_bak_20200529_01      0           0           2           MAIN        4                                               2020-05-29 11:11:34.000272        0            0              0           0           33554432    50908                50996                1           1           1            0           0           49398                117507596    0
2          DISK        1885674227  -1          TS_INCR_MAIN_20200529_111346_000167 /dm7/backup/ts_main_increment_bak_20200529_01 1           0           2           MAIN        4           TS_FULL_MAIN_20200529_111133_000175 2020-05-29 11:13:47.000258        0            0              0           0           33554432    50908                51002                1           1           1            0           0           49398                117507596    0
3          DISK        707233150   -1          TS_INCR_MAIN_20200529_111805_000417 /dm7/backup/ts_main_increment_bak_20200529_02 1           0           2           MAIN        4           TS_FULL_MAIN_20200529_111133_000175 2020-05-29 11:18:06.000513        0            0              0           0           33554432    50908                51008                1           1           1            0           0           49398                117507596    0

used time: 00:00:01.045. Execute id is 59.

上述示例中,增量备份ts_main_increment_bak_20200529_02若不指定备份集’/dm7/backup/ts_main_full_bak_20200529_01作为基备份,那么默认会使用最近一次的备份集/dm7/backup/ts_main_increment_bak_20200529_01作为基备份。

3. 备份表空间
同数据库备份一样,表空间备份也可分为完全备份和增量备份。本节主要内容包括:
1. 完全备份
2. 增量备份

1.完全备份
执行表空间备份,数据库必须处于OPEN状态,MOUNT和SUSPEND状态下不允许执行表空间备份。表空间备份就是拷贝表空间内所有数据文件有效数据的过程。DM7仅支持表空间联机备份,完全备份一个表空间步骤如下:
1) 配置归档 。
2) 保证数据库处于OPEN状态。
3) DIsql中输入备份表空间语句,最简单的不设置其他参数的完全备份表空间语句,如下所示:

SQL> backup tablespace main full backupset '/dm7/backup/ts_main_full_bak_20200529_02';
executed successfully
used time: 00:00:01.129. Execute id is 68.

备份语句中的FULL参数可以省略,不指定备份类型服务器会默认指定备份类型为完全备份。

SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/ts_main_full_bak_20200529_02');

LINEID     SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/ts_main_full_bak_20200529_02')
---------- ---------------------------------------------------------------------------
1          1

used time: 2.508(ms). Execute id is 69.
SQL> select * from v$backupset where backup_path='/dm7/backup/ts_main_full_bak_20200529_02';

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        -1442575362 -1          TS_FULL_MAIN_20200529_112705_000332 /dm7/backup/ts_main_full_bak_20200529_02 0           0           2           MAIN        4                     2020-05-29 11:27:06.000424        0            0              0           0           33554432    50908                51014                1           1           1            0           0           49398                117507596    0

used time: 00:00:01.046. Execute id is 70.

2.增量备份
执行表空间增量备份的主要目的同数据库增量备份一样是为了快速备份数据库中的修改,减少备份时间和避免重复的备份。
增量备份表空间步骤如下:
1) 配置归档。
2) 保证数据库处于OPEN状态。
3) DIsql中输入备份表空间语句,最简单的不设置其他参数的增量备份语句如下:

SQL> backup tablespace main increment base on backupset '/dm7/backup/ts_main_full_bak_20200529_02' backupset '/dm7/backup/ts_main_increment_bak_2020052903';
executed successfully
used time: 00:00:01.134. Execute id is 71.

SQL> select sf_bakset_backup_dir_add('disk','/dm7/backup/ts_main_increment_bak_2020052903');

LINEID     SF_BAKSET_BACKUP_DIR_ADD('disk','/dm7/backup/ts_main_increment_bak_2020052903')
---------- -------------------------------------------------------------------------------
1          1

used time: 1.720(ms). Execute id is 72.
SQL>  select * from v$backupset where backup_path like '%/dm7/backup/ts_main_%20200529%';

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        1493450986  -1          TS_FULL_MAIN_20200529_111133_000175 /dm7/backup/ts_main_full_bak_20200529_01      0           0           2           MAIN        4                                               2020-05-29 11:11:34.000272        0            0              0           0           33554432    50908                50996                1           1           1            0           0           49398                117507596    0
2          DISK        1885674227  -1          TS_INCR_MAIN_20200529_111346_000167 /dm7/backup/ts_main_increment_bak_20200529_01 1           0           2           MAIN        4           TS_FULL_MAIN_20200529_111133_000175 2020-05-29 11:13:47.000258        0            0              0           0           33554432    50908                51002                1           1           1            0           0           49398                117507596    0
3          DISK        707233150   -1          TS_INCR_MAIN_20200529_111805_000417 /dm7/backup/ts_main_increment_bak_20200529_02 1           0           2           MAIN        4           TS_FULL_MAIN_20200529_111133_000175 2020-05-29 11:18:06.000513        0            0              0           0           33554432    50908                51008                1           1           1            0           0           49398                117507596    0
4          DISK        -1442575362 -1          TS_FULL_MAIN_20200529_112705_000332 /dm7/backup/ts_main_full_bak_20200529_02      0           0           2           MAIN        4                                               2020-05-29 11:27:06.000424        0            0              0           0           33554432    50908                51014                1           1           1            0           0           49398                117507596    0
5          DISK        -738478264  -1          TS_INCR_MAIN_20200529_113155_000766 /dm7/backup/ts_main_increment_bak_2020052903  1           0           2           MAIN        4           TS_FULL_MAIN_20200529_112705_000332 2020-05-29 11:31:56.000863        0            0              0           0           33554432    50908                51020                1           1           1            0           0           49398                117507596    0

used time: 00:00:01.062. Execute id is 73.

备份语句中指定的INCREMENT参数表示执行的备份类型为增量备份,不可省略。若要创建累积增量备份,还需要指定CUMULATIVE参数,否则缺省为差异增量备份。若基备份不在默认备份目录,WITH BACKUPDIR参数必须指定,用于搜索基备份集。

Proudly powered by WordPress | Indrajeet by Sus Hill.