rman启用备份跟踪bct，增备大小预期变小，但花费时间却还和全备一样多？？？

aix6.1+oracle11.2.0.4
本打算通过rman启用备份跟踪bct，提升增量备份速度。但并没有达到预期。（变更的块太分散？）

查看每小时归档日志量：日志量并不高。
select * from (select to_char(first_time,'yyyy-mm-dd hh24') hour,count(*) from v$log_history group by to_char(first_time,'yyyy-mm-dd hh24') order by hour desc) a where rownum<60;

已启用了备份跟踪，但是增量备份并没有节省备份时间？可能什么原因？这正常吗？欢迎评论。

备份时间从2：30到6：09，6：14，差不多是3小时40分钟。
0级备份大小400多给G，1级增备大小25G.


查看视图列USED_CHANGE_TRACKING显示YES表示应用到了track.

对比0级和1级备份读取的块，1级读取的数量确实有变少，但奇怪的是时间并没少多少。





定时任务：

备份脚本参考：

15 3 3,9,15,21,27 * * /740ora/oracle/bak.sh
15 3 1,2,4-8,10-14,16-20,22-26,28-31 * * /740ora/oracle/bak-1.sh

oracle@jyca:/rman(jyc)>cat /home/oracle/bak.sh
ORACLE_HOME=/home/oracle/11.2.0.4
ORACLE_SID=jyc
export ORACLE_HOME ORACLE_SID 
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LIBPATH=$LIBPATH:$ORACLE_HOME/lib32
export NLS_LANG ORA_NLS33 LIBPATH
PATH=$ORACLE_HOME/bin:$PATH
export PATH
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"

cd /rman/
find /rman/ -mtime +15 -name "jyc*" -exec rm -f {} \;

rman log /rman/rman-`date +%Y%m%d-%H%M`.log <<EOF
connect target /;
RUN {
ALLOCATE CHANNEL ch00 TYPE DISK RATE 15m; 
ALLOCATE CHANNEL ch01 TYPE DISK RATE 15m; 
ALLOCATE CHANNEL ch02 TYPE DISK RATE 15m;
ALLOCATE CHANNEL ch03 TYPE DISK RATE 15m;
 crosscheck archivelog all;
 delete noprompt expired archivelog all;
 crosscheck backup;
 delete noprompt expired backup;
 delete noprompt obsolete;
 backup incremental level 0 database
 format '/rman/jyc_incr0_%d_%t_%s'
 plus archivelog
 format '/rman/jyc_arch0_%d_%t_%s'
 delete all input;
sql 'alter system archive log current';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
ALLOCATE CHANNEL ch00 TYPE DISK;
BACKUP
    FORMAT '/rman/jyc_cntrl_%s_%p_%t'
    CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
}
exit;
EOF
oracle@jyca:/rman(jyc)>cat //home/oracle/bak-1.sh
ORACLE_HOME=/home/oracle/11.2.0.4
ORACLE_SID=jyc
export ORACLE_HOME ORACLE_SID 
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LIBPATH=$LIBPATH:$ORACLE_HOME/lib32
export NLS_LANG ORA_NLS33 LIBPATH
PATH=$ORACLE_HOME/bin:$PATH
export PATH
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"

rman log /rman/rman-`date +%Y%m%d-%H%M`.log <<EOF
connect target /;
RUN {
ALLOCATE CHANNEL ch00 TYPE DISK RATE 15m; 
ALLOCATE CHANNEL ch01 TYPE DISK RATE 15m; 
ALLOCATE CHANNEL ch02 TYPE DISK RATE 15m;
ALLOCATE CHANNEL ch03 TYPE DISK RATE 15m;
 crosscheck archivelog all;
 delete noprompt expired archivelog all;
 crosscheck backup;
 delete noprompt expired backup;
 delete noprompt obsolete;
 backup incremental level 1 database
 format '/rman/jyc_incr1_%d_%t_%s'
 plus archivelog
 format '/rman/jyc_arch1_%d_%t_%s'
 delete all input;
sql 'alter system archive log current';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
ALLOCATE CHANNEL ch00 TYPE DISK;
BACKUP
    FORMAT '/rman/jyc_cntrl_%s_%p_%t'
    CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
}
exit;
EOF

bct启用和检查相关命令参考如下：

alter database disable block change tracking;
select status,filename from v$block_change_tracking;
alter database enable block change tracking using file '/home/oracle/rman/rman.track';

set linesize 200
column name format a50
column value format a25
col description format a40
select x.ksppinm name,y.ksppstvl value,y.ksppstdf isdefault,x.ksppdesc description,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and
x.ksppinm like '%_bct_bitmaps_per_file%'
order by
translate(x.ksppinm, ' _', ' ');

alter system set "_bct_bitmaps_per_file"=16 sid='*';

select file#,
       blocks_changed,
       block_size,
       blocks_changed * block_size bytes_changed,
       round(blocks_changed / blocks * 100, 2) percent_changed
from v$datafile join
     (select fno
             file#,
             sum(bct) blocks_changed
      from (select distinct fno, bno, bct from x$krcbit
            where vertime >= (select curr_vertime from x$krcfde
                              where csno=x$krcbit.csno and fno=x$krcbit.fno))
      group by fno order by 1)
using(file#);

The V$BACKUP_DATAFILE view contains a column called USED_CHANGE_TRACKING.
A value of YES for this column for an incremental backup level > 0 means that RMAN used
the tracking file to speed up the incremental backup. This can help you determine how effective
the the tracking file in minimizing the I/O activity during an incremental backup. The following
query can be used:

SQL> select file#,
            avg(datafile_blocks),
            avg(blocks_read),
            avg(blocks_read/datafile_blocks) * 100 as "% read for backup"
       from v$backup_datafile
      where incremental_level > 0
        and used_change_tracking = 'YES'
      group by file#
      order by file#;

检查是否用到bct:
select INCREMENTAL_LEVEL,file#,USED_CHANGE_TRACKING fro,CHECKPOINT_TIME,COMPLETION_TIME from v$backup_datafile order by CHECKPOINT_TIME asc;
检查隐含参数：
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND upper(x.ksppinm) LIKE upper('%_bct_bitmaps_per_file%')
/

查看增量备份的情况。

set line 160
set wrap off
select
file# fno,
used_change_tracking BCT,
incremental_level INCR,
datafile_blocks BLKS,
block_size blksz,
blocks_read READ,
round((blocks_read/datafile_blocks) * 100,2) "%READ",
blocks WRTN, round((blocks/datafile_blocks)*100,2) "%WRTN",completion_time
from V$BACKUP_DATAFILE
where completion_time between
to_date('2023-04-06 03:00:00', 'YYYY-MM-DD HH24:MI:SS') and
to_date('2023-04-06 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
order by file# asc;

select file#,
            avg(datafile_blocks),
            avg(blocks_read),
            avg(blocks_read/datafile_blocks) * 100 as "% read for backup"
       from v$backup_datafile
      where incremental_level > 0
        and used_change_tracking = 'YES'
      group by file#
      order by file#;

查看表空间占用的脚本参考：

set line 132
set wrap off
set pagesize 0
select t.*
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;

set lines 180 pages 200  
COL INPUT_TYPE FORMAT a20
COL STATUS FORMAT a20
COL minutes FORMAT 999.999
COL Input_mb FORMAT 99,999.99
COL Output_mb FORMAT 99,999.99

SELECT SESSION_KEY, INPUT_TYPE, STATUS,
TO_CHAR(START_TIME,'yyyy-mm-dd hh24:mi') start_time,
TO_CHAR(END_TIME,'yyyy-mm-dd hh24:mi') end_time,
INPUT_BYTES/1024/1024 Input_mb,
OUTPUT_BYTES/1024/1024 Output_mb,
ELAPSED_SECONDS/60 minutes
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
-----------
INPUT_BYTES
 NUMBER
 Sum of all input file sizes backed up by this job
 OUTPUT_BYTES
 NUMBER
 Output size of all pieces generated by this job
 

INPUT_BYTES 实际上就是指备份时读取的文件大小，而 OUTPUT_BYTES 指的是备份实际备份出来的文件大小。

相关参考：
RMAN incremental level 1 backups are not using block change tracking file (Doc ID 1192652.1)
https://www.modb.pro/db/44096
https://mp.weixin.qq.com/s/dxKMQT9jywXwkgg71KtsHA