[20221020]奇怪的增量备份.txt

–//生产系统做增量备份遇到的怪异问题,给奇葩的运维人员狠狠地涮了一把,做一个记录:

1.环境:

SYS@192.168.100.235:1521/orcl> @ pr

==============================

PORT_STRING                   : x86_64/Linux 2.4.xx

VERSION                       : 19.0.0.0.0

BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Version 19.3.0.0.0

BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

CON_ID                        : 0

PL/SQL procedure successfully completed.

2.问题:

–//在rman下查看:

RMAN> list backupset 7868;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

——- —- — ———- ———– ———— ——————-

7868    Incr 1  4.47G      SBT_TAPE    00:35:56     2022-10-20 00:26:15

        BP Key: 8615   Status: AVAILABLE  Compressed: NO  Tag: 2022_10_19_23_50_13

        Handle: ORDB_ORCL_7894_1_1118533819   Media:

  List of Datafiles in backup set 7868

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  —- — —- ———- ——————- ———– —— —-

  1    1  Incr 42592240372 2022-10-19 23:50:19              NO    /u02/app/oracle/oradata/orcl/datafile/ORCL/system01.dbf

  2    1  Incr 42592240372 2022-10-19 23:50:19 42592296511  NO    /u02/app/oracle/oradata/orcl/datafile/ORCL/lis_max_data.dbf

  3    1  Incr 42592240372 2022-10-19 23:50:19              NO    /u02/app/oracle/oradata/orcl/datafile/ORCL/sysaux01.dbf

  4    1  Incr 42592240372 2022-10-19 23:50:19              NO    /u02/app/oracle/oradata/orcl/datafile/ORCL/undotbs01.dbf

  5    1  Incr 42592240372 2022-10-19 23:50:19 42592295965  NO    /u02/app/oracle/oradata/orcl/datafile/ORCL/lis_data.dbf

  7    1  Incr 42592240372 2022-10-19 23:50:19              NO    /u02/app/oracle/oradata/orcl/datafile/ORCL/users01.dbf

  8    1  Incr 42592240372 2022-10-19 23:50:19              NO    /u02/app/oracle/oradata/orcl/datafile/ORCL/lis_max_data01.dbf

  9    1  Incr 42592240372 2022-10-19 23:50:19 42592244778  NO    /u02/app/oracle/oradata/orcl/datafile/ORCL/lis_data02.dbf

  10   1  Incr 42592240372 2022-10-19 23:50:19 42592295898  NO    /u02/app/oracle/oradata/orcl/datafile/ORCL/lis_max_data02.dbf

  11   1  Incr 42592240372 2022-10-19 23:50:19 42592268142  NO    /u02/app/oracle/oradata/orcl/datafile/ORCL/lis_max_data03.dbf

–//不知道虚拟磁带库是否有文件大小的限制.目前4.47G.

SYS@192.168.100.235:1521/orcl> @ dashtop sql_id,module1 1=1 &day

    Total

  Seconds     AAS %This   SQL_ID        MODULE1              FIRST_SEEN          LAST_SEEN

——— ——- ——- ————- ——————– ——————- ——————-

     7030      .1   22%                                      2022-10-11 10:56:18 2022-10-12 10:00:29

     5090      .1   16%                 w3wp.exe             2022-10-11 10:56:18 2022-10-12 10:00:19

     2180      .0    7%                 backup incr datafile 2022-10-11 23:50:09 2022-10-12 00:26:24

     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

     1530      .0    5%                 backup archivelog    2022-10-12 00:27:04 2022-10-12 02:32:11

–//很明显发现问题在于运维人员没有打开块跟踪特性,导致要扫描整个数据文件,不然做增量level=1不会使用这么长时间.

–//ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

SYS@192.168.100.235:1521/orcl> SELECT * FROM V$BLOCK_CHANGE_TRACKING

  2  @ pr

==============================

STATUS                        : DISABLED

FILENAME                      :

BYTES                         :

CON_ID                        :

PL/SQL procedure successfully completed.

SYS@192.168.100.235:1521/orcl> alter database enable block change tracking using file “/u01/app/oracle/oradata/orcl/changetracking/block_change_tracking.f” reuse;

Database altered.

SYS@192.168.100.235:1521/orcl> SELECT * FROM V$BLOCK_CHANGE_TRACKING

  2  @pr

==============================

STATUS                        : ENABLED

FILENAME                      : /u01/app/oracle/oradata/orcl/changetracking/block_change_tracking.f

BYTES                         : 11599872

CON_ID                        : 0

PL/SQL procedure successfully completed.

–//几天后检查发现增量备份时间并没有减少,我开始以为我自己禁用了块跟踪,但是我清晰的记得当时下班前我是打开的了.

–//我接着再次执行(我的工作笔记记录的是上个星期4做的操作2022/10/20):

alter database enable block change tracking using file “/u01/app/oracle/oradata/orcl/changetracking/block_change_tracking.f” reuse;

–//这次应该可以了把.因为星期6,7应该有1次level=0的全备份.

–//可是今天上班检查(星期1 2022/10/24)发现增量备份还是需要很长时间.难道我对backup incr datafile理解有误.

–//难道是接着做增量的第1次level=1的增量备份还是无法使用,我给在测试环境测试看看.

SYS@192.168.100.235:1521/orcl> SELECT * FROM V$BLOCK_CHANGE_TRACKING

  2  @ pr

==============================

STATUS                        : DISABLED

FILENAME                      :

BYTES                         :

CON_ID                        :

PL/SQL procedure successfully completed.

–//发现居然变成了DISABLED,难道有人不让我enable block change tracking吗?而且这次我不可能犯错.

3.检查跟踪文件发现:

–//检查发现实际上对方写的脚本有1个alter database disable block change tracking操作,奇葩!!alert*.log有记录:

2022-10-18T23:50:12.040797+08:00

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

alter database disable block change tracking

2022-10-18T23:50:12.057827+08:00

stopping change tracking

2022-10-18T23:50:12.058898+08:00

Block change tracking service stopping.

Stopping background process CTWR

2022-10-18T23:50:13.112232+08:00

Deleted file /u01/app/oracle/oradata/orcl/changetracking/block_change_tracking.f

Completed: alter database disable block change tracking

2022-10-19T00:25:54.803845+08:00

Control autobackup written to SBT_TAPE device

–//很明显对方的增量备份脚本在备份前禁用了块跟踪文件特性,执行时间也能对上2022-10-18T23:50.12.

–//奇葩的运维人员….我根本不知道这位同行是如何想的,难道这位同行遇到什么bug或者遇到这类增量方式无法恢复的情况.

–//连续浪费好几天的时间检查该问题,如果一开始查询alert文件,问题很快就可以定位了.