DM7使用Disql执行表还原

DM7仅支持表空间和表的联机还原,数据库和归档日志的还原必须通过脱机工具DMRMAN执行。下面主要介绍如何使用DIsql工具还原表空间和表。
下面将介绍使用DIsql工具完成基本的表还原操作及实施一些还原策略。主要内容包括:
1. 概述
2. 表还原

1. 概述
表还原与表空间还原不同,不需要配置归档,DIsql中输入以下简单的RESTORE语句就可还原表:

SQL>RESTORE TABLE TAB_01 FROM BACKUPSET 'tab_bak_01';
语法如下:
RESTORE TABLE [<表名>][STRUCT]
[WITH INDEX | WITHOUT INDEX] [WITH CONSTRAINT|WITHOUT CONSTRAINT]
FROM BACKUPSET'<备份集路径>' [DEVICE TYPE <介质类型> [PARMS '<介质参数>']]
[IDENTIFIED BY <密码>] [ENCRYPT WITH <加密算法>]
[TRACE FILE '<TRACE文件名>'] [TRACE LEVEL <TRACE日志级别>];

TABLE:需要还原的表名称。

STRUCT:执行表结构还原,若未指定,则认为是表中数据还原;表数据还原要求还原目标表结构与备份集中完全一致,否则报错,所以表结构还原可以在表数据还原之前执行,减少报错。

WITH INDEX/WITHOUT INDEX:指定还原数据后是否重建二级索引,默认重建。

WITH CONSTRAINT/WITHOUT CONSTRAINT:指定还原数据后是否重建约束,默认重建。

BACKUPSET:表空间备份时指定的备份集路径。若指定为相对路径,会在默认备份目录下搜索备份集。

DEVICE TYPE:指存储备份集的介质类型,目前表还原暂时仅支持DISK,表示备份集存储介质为磁盘。

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

IDENTIFIED BY:加密备份表时,用户设置的密码。

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

TRACE FILE:指定生成的TRACE文件名。

TRACE LEVEL:有效值1、2,默认为1表示不启用TRACE,为2表示启用TRACE。

使用说明:
1. 仅支持对普通用户表进行还原,包括堆表。其中,系统表、临时表、物化视图表、物化视图附属表和日志表、特定模式(DBG_PKG/INFORMATION_SCHEMA/INFO_SCHEM/SYSREP/SYSGEO/SYSJOB/SYSCPT/SYS)下的表不支持还原。表列类型为对象类型的表也不支持表还原。若还原表中存在位图连接索引和位图连接虚索引也不支持还原。

2. 备份集路径指备份集所在目录,其中应包含完整备份数据,包括元数据文件(.meta)和备份片文件(.bak)。仅支持从表备份集中还原表。

3. 表名设置为可选参数。若指定,则表必须存在且表定义必须与备份表严格一致;若不指定,则使用备份集中记录的备份表作为还原目标表。

4. 若指定WITHOUT INDEX,则还原后目标表中无索引信息,即使目标表本来存在,也会被删除;若未指定,则在数据还原后重新创建备份集中索引,目标表原来的索引信息会被删除。

5. 若指定WITHOUT CONSTRAINT,则还原后目标表中无约束信息,聚集主键除外,即使目标表本来存在,也会被删除;若未指定,则在数据还原后重新创建备份集中约束,目标表原来的约束信息会被删除。

6. 目标表所在的表空间必须处于联机状态。

7. 数据守护环境下,主库允许表备份还原,备库不允许。

8. MOUNT和SUSPEND状态下不允许进行表还原。

9. MPP环境不允许进行表还原。

10. 若在语句中指定STRUCT关键字,则执行表结构还原。表结构还原会根据备份集中备份表还原要求,对目标表定义进行校验,并删除目标表中已存在的二级索引和约束;若未指定WITHOUT CONSTRAINT,则会将备份集中的CHECK约束在目标表上重建。

11. 不指定STRUCT关键字,则执行表数据还原,表数据还原默认仅会将备份表中聚集索引上数据进行还原。表数据还原默认仅会在目标表定义与备份表一致且不存在二级索引和约束的情况下执行;若还原时未指定WITHOUT CONSTRAINT,也允许目标表中存在与备份表中相同的CHECK约束。

12. 若在未指定STRUCT的情况下,执行还原出现存在二级索引或冗余约束的错误;或者不指定目标表时,报目标不存在的情况,可先执行STRUCT还原后,再继续执行实际数据的还原。

13. WITH /WITHOUT INDEX用于指定还原数据后是否重建二级索引,默认重建;WITH/WITHOUT CONSTRAINT指定还原数据后是否在目标表上重建约束,默认重建。对于备份表中存在的无效索引和约束,若索引为无效或者不可见,则重建后,也保持无效或者不可见;若约束无效,则重建后,约束也保持无效状态。

14. TRACE FILE若用户指定,则指定的文件不能为已经存在的文件,否则报错;也不可以为ASM文件。

15. 若表中存在大字段列,表备份时INI参数BLOB_OUTROW_REC_STOR大于0,而建立还原目标表时INI参数BLOB_OUTROW_REC_STOR等于0,那么若大字段列存在行外数据,在执行表还原时会报错,且表数据会丢失。

16. 表还原不检查目标表的缺省表达式(default值)。

2. 表还原
执行表还原,数据库必须处于OPEN状态,MOUNT和SUSPEND状态下不允许执行表还原。与表空间还原不同,表还原不需要配置归档。表还原是联机完全备份还原,不需要借助本地归档日志,所以还原后不需要恢复。完整的表备份还原步骤如下:
1) 保证数据库为OPEN状态。
2) 创建待备份的表。

SQL> create table tab_for_res_1(c1 int);
操作已执行
已用时间: 3.498(毫秒). 执行号:57.
SQL> begin
2   for i in 1 .. 100 loop
3   insert into tab_for_res_1 values(i);
4   end loop;
5   commit;
6   end;
7   /
DMSQL 过程已成功完成
已用时间: 2.710(毫秒). 执行号:58.

3) 备份表数据。

SQL> backup table tab_for_res_1 to tab_for_res_1_bak backupset 'E:\dmdbms\backup\tab_for_res_1_bak';
操作已执行
已用时间: 00:00:01.138. 执行号:59.

4) 校验备份。此步骤为可选。

SQL> select sf_bakset_check('disk','E:\dmdbms\backup\tab_for_res_1_bak');

行号       SF_BAKSET_CHECK('disk','E:\dmdbms\backup\tab_for_res_1_bak')
---------- ------------------------------------------------------------
1          1

已用时间: 21.435(毫秒). 执行号:60.

5) 还原表数据。

SQL> truncate table tab_for_res_1;
操作已执行
已用时间: 5.238(毫秒). 执行号:62.
SQL> select count(*) from tab_for_res_1;

行号       COUNT(*)
---------- --------------------
1          0

已用时间: 1.165(毫秒). 执行号:63.
SQL> restore table tab_for_res_1 from backupset 'E:\dmdbms\backup\tab_for_res_1_bak';
操作已执行
已用时间: 68.217(毫秒). 执行号:64.
SQL> select count(*) from tab_for_res_1;

行号       COUNT(*)
---------- --------------------
1          100

已用时间: 1.329(毫秒). 执行号:65.

表还原实质是表内数据的还原,以及索引和约束等的重建。如果备份文件与目标表中都包含索引或约束该如何还原呢?下面以表中包含索引为例说明如何还原表,具体步骤如下:
1) 保证数据库为OPEN状态。
2) 创建待备份的表。

SQL> create table tab_for_res_2(c1 int);
操作已执行
已用时间: 3.088(毫秒). 执行号:67.
SQL> begin
2   for i in 1 .. 10 loop
3   insert into tab_for_res_2 values(i);
4   end loop;
5   commit;
6   end;
7   /
DMSQL 过程已成功完成
已用时间: 5.434(毫秒). 执行号:68.

3) 创建索引。

SQL> create index i_tab_for_res_2 on tab_for_res_2(c1);
操作已执行
已用时间: 6.720(毫秒). 执行号:69.

4) 备份表。

SQL> backup table tab_for_res_2 backupset 'E:\dmdbms\backup\tab_for_res_2_bak';
操作已执行
已用时间: 00:00:01.102. 执行号:70.

5) 校验备份。此步骤为可选。

SQL> select sf_bakset_check('disk','E:\dmdbms\backup\tab_for_res_2_bak');

行号       SF_BAKSET_CHECK('disk','E:\dmdbms\backup\tab_for_res_2_bak')
---------- ------------------------------------------------------------
1          1

已用时间: 38.046(毫秒). 执行号:71.

6) 执行表结构还原。表备份和表中都包含索引,如果直接执行表数据还原会报错:还原表中存在二级索引或冗余约束。

SQL> truncate table tab_for_res_2;
操作已执行
已用时间: 4.115(毫秒). 执行号:73.
SQL> select * from tab_for_res_2;
未选定行

已用时间: 1.113(毫秒). 执行号:74.
SQL> restore table tab_for_res_2 from backupset 'E:\dmdbms\backup\tab_for_res_2_bak';
restore table tab_for_res_2 from backupset 'E:\dmdbms\backup\tab_for_res_2_bak';
[-8327]:还原表中存在二级索引或冗余约束.
已用时间: 22.306(毫秒). 执行号:0.
SQL> restore table tab_for_res_2 struct from backupset 'E:\dmdbms\backup\tab_for_res_2_bak';
操作已执行
已用时间: 27.047(毫秒). 执行号:76.

7) 执行表数据还原。

SQL> restore table tab_for_res_2 from backupset 'E:\dmdbms\backup\tab_for_res_2_bak';
操作已执行
已用时间: 66.335(毫秒). 执行号:77.
SQL> select * from tab_for_res_2;

行号       C1
---------- -----------
1          1
2          2
3          3
4          4
5          5
6          6
7          7
8          8
9          9
10         10

10 rows got

已用时间: 1.491(毫秒). 执行号:78.

DM7使用Disql执行表空间还原

DM7仅支持表空间和表的联机还原,数据库和归档日志的还原必须通过脱机工具DMRMAN执行。本章节主要介绍如何使用DIsql工具还原表空间和表。
下面将介绍使用DIsql工具完成基本的表空间还原操作及实施一些还原策略。主要内容包括:
1. 概述
2. 表空间还原

1. 概述
在DIsql中使用RESTORE语句可以还原表空间,还原表空间需要服务器运行在归档模式下且还原之前需要将表空间脱机。启动DIsql输入以下语句即可还原表空间:

SQL> backup tablespace main to ts_main_bak backupset 'E:\dmdbms\backup\ts_main_bak';
操作已执行
已用时间: 00:00:01.066. 执行号:4.
SQL> alter tablespace main offline;
操作已执行
已用时间: 101.032(毫秒). 执行号:5.
SQL> restore tablespace main from backupset 'E:\dmdbms\backup\ts_main_bak';
操作已执行
已用时间: 316.624(毫秒). 执行号:6.
SQL> alter tablespace main online;
操作已执行
已用时间: 9.330(毫秒). 执行号:7.

指定备份集“E:\dmdbms\backup\ts_main_bak”为绝对路径,如果指定相对路径,服务器自动会在默认备份路径下搜索备份集。

语法如下:

RESTORE TABLESPACE < 表空间名>
[DATAFILE< <文件编号> {,< 文件编号>} | '< 文件路径>' {,'< 文件路径>'}>]
FROM BACKUPSET '< 备份集路径>' [DEVICE TYPE < 介质类型> [PARMS '< 介质参数>']]
[IDENTIFIED BY < 加密密码>] [ENCRYPT WITH < 加密算法>]
[WITH BACKUPDIR '< 备份目录>' {,'< 备份目录>'}]
[WITH ARCHIVEDIR '归档目录'{,'归档目录'}]
[MAPPED FILE '< 映射文件>']
[TRACE FILE ''] [TRACE LEVEL ][TASK THREAD < 线程数>][NOT PARALLEL]
[UNTIL TIME '< 时间串>'] [UNTIL LSN ];

TABLESPACE:指定还原的表空间,除了temp表空间。

DATAFILE:还原指定的数据文件。可以指定数据文件编号或数据文件路径。文件编号,对应动态视图V$DATAFILE中ID列的值;文件路径,对应动态视图V$DATAFILE中PATH或者MIRROR_PATH列的值,也可以仅指定数据文件名称(相对路径),与表空间中数据文件匹配时,会使用SYSTEM目录补齐。

BACKUPSET:指定还原备份集的路径。若指定为相对路径,会在默认备份目录下搜索备份集。

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

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

IDENTIFIED BY:加密备份表空间时,用户设置的密码。

ENCRYPT WITH:加密算法。缺省情况下,算法为AES256_CFB。具体包含哪些加密算法请参考表空间备份的参数说明。

WITH BACKUPDIR:指定备份搜索目录,最大长度为256个字节。使用完全备份还原中,若指定的备份集路径为相对路径可通过设置此参数搜索备份集;增量备份还原中设置该参数除上述功能外还用于搜索基备份集。

WITH ARCHIVEDIR:归档日志搜索目录。如果归档日志不在配置文件中指定的目录下,或者归档日志分散在多个目录,此时就需要指定该参数用于搜索归档日志。

MAPPED FILE :指定存放还原目标路径的文件,参考3.3.5.2.1小节。

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;若指定超过当前系统主机核数,则调整为当前系统主机核数。

NOT PARALLEL:指定并行备份集使用非并行方式还原。对于非并行备份集,不论是否指定该关键字,均采用非并行还原。

UNTIL TIME:恢复表空间到指定的时间点。主备环境不支持。

UNTIL LSN:恢复表空间到指定的LSN。主备环境不支持。

表空间还原不建议使用UNTIL LSN或者UNTIL TIME,若要使用,则确保备份集生成之后未执行过DDL操作,否则将可能出现不可预知的后果。

使用说明:
1. 支持从非DDL_CLONE的库级备份集和表空间级备份集中还原出指定表空间,若还原目标表空间状态不是RES_OFFLINE或CORRUPT,还原指定表空间中指定数据文件。
2. 备份集路径是指备份集所在目录,其中应包含完整备份数据,包括元数据文件(.meta)和备份片文件(.bak)。
3. 还原前应该先将还原的表空间脱机,只能还原用户表空间。
4. 该功能在配置本地归档后才起作用,若为RAC库中表空间还原,则需要配置REMOTE归档。
5. MPP环境不允许进行表空间还原。
6. 若指定NOT PARALLEL,则认为并行备份不使用并行还原,默认并行备份执行并行还原。对于非并行备份集还原,则忽略。
7. 若未指定UNTIL TIME或者UNTIL LSN,则均还原到最新状态。使用UNTIL TIME(或者UNTIL LSN)的同时不能指定DATAFILE还原,否则报语法错误。
8. OPEN和MOUNT状态下支持表空间还原,SUSPEND状态下则不支持。

2. 表空间还原
本节介绍使用DIsql工具如何还原表空间。本节主要内容包括:
1. 使用表空间完全备份还原表空间
2. 使用表空间增量备份还原表空间
3. 使用数据库备份还原表空间

1.使用表空间完全备份还原表空间
表空间的还原、恢复操作是一次性完成,因此还原后不需要执行恢复操作。使用表空间完全备份还原表空间的完整步骤如下:
1) 配置归档。
2) 保证数据库处于OPEN或MOUNT状态。
3) 备份表空间。

SQL> backup tablespace main to ts_main_bak backupset 'E:\dmdbms\backup\ts_main_bak';
操作已执行
已用时间: 00:00:01.069. 执行号:9.

4) 校验表空间备份。此步骤为可选,如果确定备份文件合法可不进行备份校验。

SQL> select sf_bakset_check('disk','E:\dmdbms\backup\ts_main_bak');

行号       SF_BAKSET_CHECK('disk','E:\dmdbms\backup\ts_main_bak')
---------- ------------------------------------------------------
1          1

已用时间: 17.741(毫秒). 执行号:10.

5) 修改表空间为脱机。

SQL> alter tablespace main offline;
操作已执行
已用时间: 137.581(毫秒). 执行号:11.

6) 还原表空间。

SQL> restore tablespace main from backupset 'E:\dmdbms\backup\ts_main_bak';
操作已执行
已用时间: 276.897(毫秒). 执行号:12.

7) 修改表空间为联机。

SQL> alter tablespace main online;
操作已执行
已用时间: 17.971(毫秒). 执行号:13.

2.使用表空间增量备份还原表空间
在增量备份与基备份同目录情况下,使用增量备份还原表空间步骤与完全备份一致。当增量备份与基备份不在同一个目录下时需要使用RESTORE TABLESPACE…WITH BACKUPDIR语句还原。这里以后面一种情况为例说明使用增量备份还原表空间。步骤如下:
1) 配置归档。
2) 保证数据库处于OPEN或MOUNT状态。
3) 完全备份表空间。

SQL> backup tablespace main to ts_main_full_bak backupset 'E:\dmdbms\backup\ts_main_full_bak';
操作已执行
已用时间: 00:00:01.095. 执行号:35.

4) 多次增量备份表空间。

SQL> begin
2   for i in 1 .. 100 loop
3    insert into t1 values(i);
4   end loop;
5   commit;
6   end;
7   /
DMSQL 过程已成功完成
已用时间: 3.383(毫秒). 执行号:36.
SQL> backup tablespace main increment with backupdir 'E:\dmdbms\backup\ts_main_full_bak' backupset 'E:\dmdbms\backup1\ts_main_incr_bak_01';
操作已执行
已用时间: 00:00:01.079. 执行号:37.


SQL> begin
2   for i in 101 .. 200 loop
3   insert into t1 values(i);
4   end loop;
5   end;
6   /
DMSQL 过程已成功完成
已用时间: 1.274(毫秒). 执行号:38.
SQL> alter system switch logfile;
操作已执行
已用时间: 14.166(毫秒). 执行号:0.
SQL> backup tablespace main increment with backupdir 'E:\dmdbms\backup\ts_main_full_bak','E:\dmdbms\backup1\ts_main_incr_bak_01' backupset 'E:\dmdbms\backup2\ts_main_incr_bak_02';
操作已执行
已用时间: 00:00:01.081. 执行号:39.

5) 校验表空间备份。此步骤为可选,如果确定备份文件合法可不进行备份校验。

SQL> select sf_bakset_check('disk','E:\dmdbms\backup\ts_main_full_bak');

行号       SF_BAKSET_CHECK('disk','E:\dmdbms\backup\ts_main_full_bak')
---------- -----------------------------------------------------------
1          1

已用时间: 20.931(毫秒). 执行号:40.
SQL> select sf_bakset_check('disk','E:\dmdbms\backup1\ts_main_incr_bak_01');

行号       SF_BAKSET_CHECK('disk','E:\dmdbms\backup1\ts_main_incr_bak_01')
---------- ---------------------------------------------------------------
1          1

已用时间: 31.184(毫秒). 执行号:41.
SQL> select sf_bakset_check('disk','E:\dmdbms\backup2\ts_main_incr_bak_02');

行号       SF_BAKSET_CHECK('disk','E:\dmdbms\backup2\ts_main_incr_bak_02')
---------- ---------------------------------------------------------------
1          1

已用时间: 25.171(毫秒). 执行号:42.

6) 修改表空间为脱机

SQL> alter tablespace main offline;
操作已执行
已用时间: 98.420(毫秒). 执行号:43.

7) 使用增量备份E:\dmdbms\backup2\ts_main_incr_bak_02还原表空间。

SQL> restore tablespace main from backupset 'E:\dmdbms\backup2\ts_main_incr_bak_02' with backupdir 'E:\dmdbms\backup\ts_main_full_bak','E:\dmdbms\backup1\ts_main_incr_bak_01';
操作已执行
已用时间: 370.046(毫秒). 执行号:44.

8) 修改表空间为联机。

SQL> alter tablespace main online;
操作已执行
已用时间: 15.717(毫秒). 执行号:45.
SQL> select count(*) from t1;

行号       COUNT(*)
---------- --------------------
1          200

已用时间: 1.345(毫秒). 执行号:46.

3.使用数据库备份还原表空间
使用数据库备份还原表空间可以快速还原一个或多个表空间,而不影响数据库中其他的表空间和对象。在数据库中仅有特定的表空间损坏且没有表空间备份的情况下,可以选择使用此种方式还原。
具体步骤如下:
1) 配置归档。
2) 保证数据库处于OPEN或MOUNT状态。
3) 创建数据库完全备份。

SQL> backup database full to db_jydm_full_bak backupset 'E:\dmdbms\backup\ts_jydm_full_bak';
操作已执行
已用时间: 00:00:01.198. 执行号:50.

4) 校验表空间备份。此步骤为可选,如果确定备份文件合法可不进行备份校验。

SQL> select sf_bakset_check('disk','E:\dmdbms\backup\ts_jydm_full_bak');

行号       SF_BAKSET_CHECK('disk','E:\dmdbms\backup\ts_jydm_full_bak')
---------- -----------------------------------------------------------
1          1

已用时间: 46.115(毫秒). 执行号:51.

5) 修改表空间为脱机。

SQL> alter tablespace main offline;
操作已执行
已用时间: 96.596(毫秒). 执行号:52.

6) 还原表空间。

SQL> restore tablespace main from backupset 'E:\dmdbms\backup\ts_jydm_full_bak';
操作已执行
已用时间: 326.842(毫秒). 执行号:53.

7) 修改表空间为联机。

SQL> alter tablespace main online;
操作已执行
已用时间: 17.338(毫秒). 执行号:54.
SQL> select count(*) from t1;

行号       COUNT(*)
---------- --------------------
1          200

已用时间: 1.969(毫秒). 执行号:55.

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.

Proudly powered by WordPress | Indrajeet by Sus Hill.