[20221020]奇怪的增量备份.txt
[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文件,问题很快就可以定位了.
--//生产系统做增量备份遇到的怪异问题,给奇葩的运维人员狠狠地涮了一把,做一个记录:
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文件,问题很快就可以定位了.
