1级备份变0级的原因 及 Enabling Change Tracking生效没有

1级变0级的原因：

1.explicit deletion of the level 0 backups , ignoring retention policy,不要手动delete backup,使用delete obsolete

2.deletion from disk followed by crossheck/delete expired 不要crosscheck expired  会删除字典

3.if a catalog is NOT used, level 0 backup metadata may age out of the controlfile (controlfile keep值太小)

Enabling Change Tracking需要确保increment 不超过8个！同时enable change tracking file不是开启就会生效：

---就是说enable change tracking file 后差量备份后第一次的增量备份是不会用的，第二次会用，即使之前是1级备份。

---增量差量需要用到0级才能用上，一级的不算。

---用不到enable change tracking file，只是变慢点， 不会导致level=1会变成level 0，但是如果metadata中找不到level 0备份，level1 会触发level=0 backup. 这个和enable change tracking file没有关系的。

How do I find out how many (or %) of blocks changed between RMAN backups? (Doc ID 866166.1)

GOAL

 Customers often request queries to tell how many blocks changed between backups, to better understand database usage. You query the v$backup_datafile view as shown, to determine how effective the change tracking data is in minimizing the incremental backup I/O (the pct_read_for_backup column). A high percentage for the pct_read_for_backup column indicates that RMAN reads most blocks in the data file during an incremental backup. To reduce this ratio you decrease the time between incremental backups.

SOLUTION

Instructions

Run queries in SQL*Plus

Sample Code

The following query will show % of blocks read for BCT incremental:
 

SELECT file#,

TRUNC(AVG(datafile_blocks)) blocks_in_file,

TRUNC(AVG(blocks_read)) blocks_read,

TRUNC(AVG(blocks)) blocks_backed_up,

TRUNC(AVG(blocks_read/datafile_blocks)*100)

pct_read_for_backup

FROM v$backup_datafile

WHERE used_change_tracking='YES'

AND incremental_level > 0
GROUP BY file#;

Also, you can find out the difference in # of blocks read with a level 0, level 1 without BCT, and level 1 with BCT:

select file#,blocks_read, blocks,creation_time,checkpoint_change#, used_change_tracking from v$backup_datafile where incremental_level=0 order by 5,1;

select file#,blocks_read, blocks,creation_time,checkpoint_change#, used_change_tracking from v$backup_datafile where incremental_level=1 and used_change_tracking='NO' order by 5,1;

select file#,blocks_read, blocks,creation_time,checkpoint_change#, used_change_tracking from v$backup_datafile where incremental_level=1 and used_change_tracking='YES' order by 5,1;

-------------------------说明1级备份会备份0级的--------------------------

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.

GOAL

Sometimes, an RMANincremental backup takes much longer than expected and creates a backuppiece that is a big as a full or level 0 incremental backup.

To understand what is going on, v$backup_datafile should be queried for all backups in the current backup cycle ie from the previous level 0 backup to current:
 

SQL>set lines 800
SQL>set pages 100
SQL>alter session set nls_date_format='dd-mon-rr hh24:mi:ss';
SQL>select recid, file#, to_char(creation_change#), incremental_level, to_char(incremental_change#) inc#,to_char(checkpoint_change#) ckp#, 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, used_change_tracking
from v$backup_datafile where completion_time > 'date';

Sample output  (edited, and for file#1 only):
 

FILE#   CREATION_CHANGE# LVL                INC#        CKP#                     TIME          %Wrtn
1           8                                                                         0      6541999900034     28-Nov-10   88.86
1           8                                          2                             8      6542006129555     30-Nov-10   88.94
1           8                                          2     6542006129555      6542013100819     30-Nov-10     4.47
1           8                                          2    6542013100819       6542025661065      01-Dec-10    4.12
1           8                                          1                            8       6542032339845      03-Dec-10   89.05

 

SOLUTION

The RMAN backup metadata shows that the backup taken at the beginning of the backup cycle on 28-Nov-10 which should have been a level 0 was in fact a FULL database backup.
This is confirmed by Level (LVL) being NULL.
When an incremental level n is run, it is based on the checkpoint scn of the previous incremental at the same level or lower.
The incremental level 2 on on Nov 30 did not find a level 0 so it based the backup on all changes since file creation (scn 8) hence this first level 2 incremental is as big as a regular level 0 or full backup.
Subsequent level 2s (30-Nov, 01-Dec) are correctly based in the ckp# of the previous level 2.
On 03-Dec a level 1 is done - again we look for a previous level 1 or 0 and do not find one so this backup is based on create scn of the datafile.

An incremental backup strategy needs a baseline incremental level 0 backup , NOT a full database backup.   Although they are similar in content only an incremental level 0 backup can be used as the start point for an incremental backup strategy.

For this to work correctly you need to use:

backup incremental level 0 database;

-----0级备份被误删---------------------------

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.2 [Release 9.2 to 11.2]
Information in this document applies to any platform.

SYMPTOMS

Incremental backup cycle:

Sundays:        Level 0
Monday-Sat:  cumulative level 1

Every Friday and Saturday, the time for the incremental level 1 backup suddenly increases and results in a much larger backuppiece being generated.  The usual backuppiece size is around 200 MB, but on Friday and Saturday it is around 800 MB despite the fact that there is no extra activity on thedatabase during these days. The archive log generation is the same as on previous days.

 

CAUSE

In general, an RMAN incremental backup is based on the checkpoint scn of the previous incremental backup or the previous level 0 backup if this is a cummulative backup.    If no level 0 backup is found, then any existing incrementals become useless as an incremental without a baseline level 0 cannot be applied.  In such a situation, the next incremental will be based on the file creation scn ie
ALL blocks changed since file creation will be backed up.   So where an incremental backup strategy is deployed,  the backup metadata for the complete cycle (starting from level 0 backup) must be maintained in the rman repository.

Level 0 backups will be  removed prematurely from the rman repository under the following circumstances:

a. explicit deletion of the level 0 backups , ignoring retention policy
b. copy of rman backups to tape then  deletion from disk followed by crossheck/delete expired
c. if a catalog is NOT used, level 0 backup metadata may age out of the controlfile (only likely if the level 0s are taken very infrequently)

(a) and (b) are common practices where backups are written to disk first  and space is at a premium - this is fine as long as the backup metada remains untouched in the RMAN repository.
 

To confirm if the backup metadata is missing:

a. RMAN>list backup of database;
   
    Look for the absence of level 0 backups for the present backup cycle

b. Query v$backup_datafile:

    SQL> set lines 400
    SQL> alter session set nls_date_format='dd-mon-rr hh24:mi:ss';
    SQL> select recid, file#, to_char(creation_change#)crscn,
                incremental_level lvl, to_char(incremental_change#) incrscn,
                to_char(checkpoint_change#) ckpscn, checkpoint_time ckptime,
                completion_time endtime, USED_CHANGE_TRACKING bct,
                blocks_read  read, block_size bsz, blocks wrtn
         from v$backup_datafile
         where file# > 0
           and completion_time > '<date>';

     Look for datafile backups where incremental_change#=creation_change
         
          Sample output (edited):

recid   File# crscn  Lvl   incr_change#    ckp change#
------  ----- ------ ----- -------------   -----------------
61888    1       5    0                0   6066791848649
61975    1       5    1    6066791848649   6067000893725
62073    1       5    1    6066791848649   6067362932086
62179    1       5    1    6066791848649   6067740524868
62280    1       5    1    6066791848649   6068546799488
62393    1       5    1                5   6069467166501

   For the backup with recid 62393, the backup was based on scn 5, which is the file creation scn.

 

SOLUTION

Do not use an explicit DELETE command to remove any backups belonging to current backup cycle.
When copying backups from disk to tape and then deleting them from disk, do not run crossheck/delete expired commands as this will remove the corresponding backup metadata.

To maintain the backup metadata correctly:

set a retention policy to match your incremental backup cycle and use delete force obsolete 
 

RMAN> configure retention policy to recovery window of 7 days;
RMAN> delete force obsolete;

 .  using 'DELETE OBSOLETE' ensures only backups outside your retention policy will be deleted , the backup metadata for the current incremental cycle will be preserved
 . using 'FORCE' tells rman to ignore any io errors (when the OS reports that the backuppiece does not
    exist)

If you are not using a catalog ensure that CONTROL_FILE_RECORD_KEEP_TIME is set to your recovery window +1.

--------------------------------------------

Is a Level=0 Database Backup Required after Enabling Change Tracking? (Doc ID 469692.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.3 and later
Information in this document applies to any platform.

GOAL

Answer the question if a level=0 database backup after enabling the change tracking file?

SOLUTION

Do I have to take a level=0 database backup after enabling the change tracking file?

RESEARCH

=========

TESTCASE
----------------

/* Turn on change tracking */

SQL> startup mount;
ORACLE instance started.

Total System Global Area 595591168 bytes
Fixed Size 1263128 bytes
Variable Size 205523432 bytes
Database Buffers 381681664 bytes
Redo Buffers 7122944 bytes
Database mounted.
SQL> alter database disable block change tracking;

Database altered.

SQL> alter database open;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 595591168 bytes
Fixed Size 1263128 bytes
Variable Size 205523432 bytes
Database Buffers 381681664 bytes
Redo Buffers 7122944 bytes
Database mounted.

SQL> alter database enable block change tracking using file '/u01/temp/V102/change_tracking.f';

Database altered.

SQL> alter database open;

Database altered.

/* What file does the object which will be changed exist? */

SQL> l
1 select tablespace_name from dba_tables
2* where owner='SCOTT' and table_name='DEPT'
SQL> /

TABLESPACE_NAME
------------------------------
USERS

SQL> select file_id from dba_data_files where tablespace_name='USERS';

FILE_ID
----------
4

SQL> insert into scott.dept select * from scott.dept;

16 rows created.

SQL> commit;

Commit complete.

SQL> alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
04-DEC-2007 16:24:08

/* when was the last level=0 backup */

SQL> l
1 select file#, completion_time, incremental_level
2 from v$backup_datafile where file#=4
3* and incremental_level=0
SQL> /

FILE# COMPLETION_TIME INCREMENTAL_LEVEL
---------- -------------------- -----------------
4 15-JUN-2007 15:45:59 0
4 15-JUN-2007 17:19:56 0
4 04-DEC-2007 15:44:38 0

/* Take incremental level 1 backup */

Recovery Manager: Release 10.2.0.3.0 - Production on Tue Dec 4 16:24:50 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target

connected to target database: V102 (DBID=2225406446)

RMAN> backup incremental level=1 database;

Starting backup at 04-DEC-2007 16:25:06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=149 devtype=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/V102_oradata/system01.dbf
input datafile fno=00004 name=/u01/V102_oradata/users01.dbf
input datafile fno=00002 name=/u01/V102_oradata/undotbs01.dbf
input datafile fno=00003 name=/u01/V102_oradata/sysaux01.dbf
input datafile fno=00005 name=/u01/V102_oradata/system02.dbf
channel ORA_DISK_1: starting piece 1 at 04-DEC-2007 16:25:08
channel ORA_DISK_1: finished piece 1 at 04-DEC-2007 16:28:56
piece
handle=/u01/temp/flash_areas/V102/V102/backupset/2007_12_04/o1_mf_nnnd1_TAG20071204T162508_3ockfntq_
.bkp tag=TAG20071204T162508 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:49
Finished backup at 04-DEC-2007 16:28:57

/* Was change tracking file used? It shouldn't as there is no incremental backup AFTER the change
tracking was enabled */

SQL> l
1 select file#, completion_time, USED_CHANGE_TRACKING
2 from v$backup_datafile where file#=4
3* and trunc(completion_time)>sysdate-1
SQL> /

FILE# COMPLETION_TIME USE
---------- -------------------- ---
4 04-DEC-2007 16:28:42 NO

--catalog情况下查看

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"
from rc_backup_datafile
where completion_time between
to_date('08-10-2010 09:00:00', 'MM-DD-YYYY HH24:MI:SS') and
to_date('08-10-2010 13:00:00', 'MM-DD-YYYY HH24:MI:SS')
and db_key=458227
order by file#;

--

SQL> insert into scott.dept select * from scott.dept;

32 rows created.

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
--------------------
04-DEC-2007 16:30:55

SQL>

/* Another incremental backup, this one DOES have a previous incremental taken after change
tracking was enabled */

RMAN> backup incremental level=1 database;

Starting backup at 04-DEC-2007 16:31:00
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/V102_oradata/system01.dbf
input datafile fno=00004 name=/u01/V102_oradata/users01.dbf
input datafile fno=00002 name=/u01/V102_oradata/undotbs01.dbf
input datafile fno=00003 name=/u01/V102_oradata/sysaux01.dbf
input datafile fno=00005 name=/u01/V102_oradata/system02.dbf
channel ORA_DISK_1: starting piece 1 at 04-DEC-2007 16:31:01
channel ORA_DISK_1: finished piece 1 at 04-DEC-2007 16:31:08
piece
handle=/u01/temp/flash_areas/V102/V102/backupset/2007_12_04/o1_mf_nnnd1_TAG20071204T163100_3ockrqht_
.bkp tag=TAG20071204T163100 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 04-DEC-2007 16:31:08

/* Was the change tracking file used for datafile #4? */

SQL> l
1 select file#, completion_time, USED_CHANGE_TRACKING
2 from v$backup_datafile where file#=4
3* and trunc(completion_time)>sysdate-1
SQL> /

FILE# COMPLETION_TIME USE
---------- -------------------- ---
4 04-DEC-2007 16:28:42 NO
4 04-DEC-2007 16:31:04 YES

/* Yes it was */

ANSWER
=======

A full backup is NOT required for using the change tracking file for incremental backups. However, an incremental backup IS required of the database after change tracking is enabled. Once an incremental backup is taken (even a level=1), although this backup will not use change tracking file, but FUTURE incremental backups WILL start using the change tracking file.

---就是说enable change tracking file 后差量备份后第一次的增量备份是不会用的，第二次会用，即使之前是1级备份。

For incremental cumulative backups a full backup IS required for using the change tracking file. The only way to re-instantiate the Block change tracking file for use with an incremental cumulative backup (which compares to the last full backup) is by performing a full backup.

----增量差量需要用到0级才能用上，一级的不算。

Note, this assumes there is already a level=0 backup before the change tracking file is enabled.  If not, the level=1 will automatically take a level=0 backup. 

---用不到enable change tracking file，只是变慢点， 不会导致level=1会变成level 0，但是如果metadata中找不到level 0备份，level1 会触发level=0 backup. 这个和enable change tracking file没有关系的。