RMAN full backup vs. level 0 incremental

Posted in: Oracle, Technical Track

Perhaps you’ve wondered about this. What is the difference between taking an RMAN full backup and a level 0 incremental backup?

If you read the documentation the following explanation will be found here: Incremental Backups

The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy. Thus, an incremental level 0 backup is a full backup that happens to be the parent of incremental backups whose level is greater than 0.

Logically that is true, but for many folks that is not enough. Are they different physically, and if so exactly how are they different physically?

If you google for an explanation you will find a number of ideas, most based on what the documentation says.

Here are some examples of what I found:

  • The only difference between level 0 and full is that a level 0 is marked in the RMAN catalog as a level 0.
  • Level 0 incremental and full backups are physically identical.

If you are an experienced IT professional then by now you have learned not to assume that articles found on the WWW are always correct.It seemed like it might be an interesting exercise to find out what the differences might be.

This called for some experimentation; here is the general procedure followed:

  • Put the database in mount state so there is no activity in the database.
  • Create two RMAN ‘full’ backups.
  • Create two RMAN incremental level 0 backups.
  • Open the database and perform some DML and/or DDL.
  • Create an incremental level 1 backup.
  • Do more DML/DDL activity.
  • Create another incremental level 1 backup.

This will provide some backup files to examine. You may be wondering why two of each backup type was made. The reason for that is to filter out changes in the backup file that are not relevant to the difference in backup types. That should become more clear with examples.

The test environment is as follows:

  • Test database: Oracle 12.1.0.2 CDB on Linux 6.5.
  • RMAN Catalog database: Oracle 11.2.0.2 on Linux 5.5.

The following backup commands were used to create two full and two level 0 backups:

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
rman target / catalog rman12c/rman12c@oravm <<-EOF
run {
allocate channel ch1 device type disk format '/mnt/oracle-backups/ora12c/full-01/%U';
backup database tag 'full-01' plus archivelog tag 'full-01';
}
EOF
rman target / catalog rman12c/rman12c@oravm <<-EOF
run {
allocate channel ch1 device type disk format '/mnt/oracle-backups/ora12c/full-02/%U';
backup database tag 'full-02' plus archivelog tag 'full-02';
}
EOF
rman target / catalog rman12c/rman12c@oravm <<-EOF
run {
allocate channel ch1 device type disk format '/mnt/oracle-backups/ora12c/lvl0-01/%U';
backup incremental level 0 database tag 'lvl0-01' plus archivelog tag 'lvl0-01';
}
EOF
rman target / catalog rman12c/rman12c@oravm <<-EOF
run {
allocate channel ch1 device type disk format '/mnt/oracle-backups/ora12c/lvl0-02/%U';
backup incremental level 0 database tag 'lvl0-02' plus archivelog tag 'lvl0-02';
}
EOF

Let’s take a look at the size of the files:

[root@lestrade ora12c]# ls -l full-01/*
-rw-r----- 1 root root    6878208 Oct 27 15:15 full-01/0sqkp2dl_1_1
-rw-r----- 1 root root  761749504 Oct 27 15:16 full-01/0tqkp2dn_1_1
-rw-r----- 1 root root 1696112640 Oct 27 15:17 full-01/0uqkp2eq_1_1
-rw-r----- 1 root root  735043584 Oct 27 15:17 full-01/0vqkp2g7_1_1
-rw-r----- 1 root root  623837184 Oct 27 15:17 full-01/10qkp2h0_1_1
[root@lestrade ora12c]# ls -l full-02/*
-rw-r----- 1 root root    6878208 Oct 27 15:18 full-02/12qkp2hm_1_1
-rw-r----- 1 root root  761749504 Oct 27 15:18 full-02/13qkp2ho_1_1
-rw-r----- 1 root root 1696112640 Oct 27 15:19 full-02/14qkp2ir_1_1
-rw-r----- 1 root root  735043584 Oct 27 15:19 full-02/15qkp2k8_1_1
-rw-r----- 1 root root  623837184 Oct 27 15:20 full-02/16qkp2l2_1_1
[root@lestrade ora12c]# ls -l lvl0-01/*
-rw-r----- 1 root root    6878208 Oct 27 15:20 lvl0-01/18qkp2lm_1_1
-rw-r----- 1 root root  761749504 Oct 27 15:21 lvl0-01/19qkp2lo_1_1
-rw-r----- 1 root root 1696112640 Oct 27 15:21 lvl0-01/1aqkp2mr_1_1
-rw-r----- 1 root root  735043584 Oct 27 15:22 lvl0-01/1bqkp2o8_1_1
-rw-r----- 1 root root  623837184 Oct 27 15:22 lvl0-01/1cqkp2p2_1_1
[root@lestrade ora12c]# ls -l lvl0-02/*
-rw-r----- 1 root root    6878208 Oct 27 15:23 lvl0-02/1eqkp2q4_1_1
-rw-r----- 1 root root  761749504 Oct 27 15:23 lvl0-02/1fqkp2q6_1_1
-rw-r----- 1 root root 1696112640 Oct 27 15:24 lvl0-02/1gqkp2r9_1_1
-rw-r----- 1 root root  735043584 Oct 27 15:25 lvl0-02/1hqkp2sm_1_1
-rw-r----- 1 root root  623837184 Oct 27 15:25 lvl0-02/1iqkp2tf_1_1

The number and sizes of the files appear the same regardless of the backup type, lending some credence to the idea that they may be physically identical. So now let’s dump some of the files to determine the difference.

For purposes of this experiment we are going to examine the backup files that contain datafile 1 from the database, the SYSTEM tablespace datafile.

We can find these backup files with the RMAN list command:

RMAN> list backup of datafile 1;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1259    Full    1.58G      DISK        00:00:39     2015-10-27 15:15:13
        BP Key: 1267   Status: AVAILABLE  Compressed: NO  Tag: FULL-01
        Piece Name: /mnt/oracle-backups/ora12c/full-01/0uqkp2eq_1_1
  List of Datafiles in backup set 1259
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 50957811   2015-10-27 18:08:29 +DATA/JS03/DATAFILE/system.258.856612879
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1341    Full    1.58G      DISK        00:00:39     2015-10-27 15:17:22
        BP Key: 1349   Status: AVAILABLE  Compressed: NO  Tag: FULL-02
        Piece Name: /mnt/oracle-backups/ora12c/full-02/14qkp2ir_1_1
  List of Datafiles in backup set 1341
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 50957811   2015-10-27 18:08:29 +DATA/JS03/DATAFILE/system.258.856612879
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1433    Incr 0  1.58G      DISK        00:00:39     2015-10-27 15:19:30
        BP Key: 1441   Status: AVAILABLE  Compressed: NO  Tag: LVL0-01
        Piece Name: /mnt/oracle-backups/ora12c/lvl0-01/1aqkp2mr_1_1
  List of Datafiles in backup set 1433
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1    0  Incr 50957811   2015-10-27 18:08:29 +DATA/JS03/DATAFILE/system.258.856612879
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1531    Incr 0  1.58G      DISK        00:00:42     2015-10-27 15:21:55
        BP Key: 1539   Status: AVAILABLE  Compressed: NO  Tag: LVL0-02
        Piece Name: /mnt/oracle-backups/ora12c/lvl0-02/1gqkp2r9_1_1
  List of Datafiles in backup set 1531
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1    0  Incr 50957811   2015-10-27 18:08:29 +DATA/JS03/DATAFILE/system.258.856612879

Now dump the backup pieces to hex files. Just the first 100M is being dumped from each, resulting in ~300M text files.

DUMPDIR=hexdump
mkdir -p $DUMPDIR
for f in full-01/0uqkp2eq_1_1 full-02/14qkp2ir_1_1 lvl0-01/1aqkp2mr_1_1 lvl0-02/1gqkp2r9_1_1
do
        tag=$(dirname $f)
        filename=$(basename $f)
        filename="${DUMPDIR}/${tag}_${filename}.txt"
        echo $filename
        # dump first 100M
        dd if=$f bs=512 count=204800 2>/dev/null | hexdump -C > $filename
done
[root@lestrade hexdump]# ls -l full* lvl0*
-rw-r--r-- 1 root root 305807309 Oct 27 16:11 full-01_0uqkp2eq_1_1.txt
-rw-r--r-- 1 root root 305807309 Oct 27 16:11 full-02_14qkp2ir_1_1.txt
-rw-r--r-- 1 root root 305807309 Oct 27 16:11 lvl0-01_1aqkp2mr_1_1.txt
-rw-r--r-- 1 root root 305807309 Oct 27 16:11 lvl0-02_1gqkp2r9_1_1.txt

We can compare the dump of the two full backup files. As there were no changes to the database during this time (you may recall the database is in MOUNT state) the only difference in the files should be changes to metadata.

[root@lestrade hexdump]# diff full-01_0uqkp2eq_1_1.txt full-02_14qkp2ir_1_1.txt
7,8c7,8
< 00002010  46 7d 00 00 00 02 10 0c  00 02 10 0c c8 29 05 07  |F}...........)..|
< 00002020  4a 53 30 33 00 00 00 00  de 19 01 00 00 00 00 00  |JS03............|
---
> 00002010  dd 7e 00 00 00 02 10 0c  00 02 10 0c c8 29 05 07  |.~...........)..|
> 00002020  4a 53 30 33 00 00 00 00  fd 19 01 00 00 00 00 00  |JS03............|
12,13c12,13
< 00002060  01 00 00 00 da 89 4c 35  1e 00 00 00 04 00 00 00  |......L5........|
< 00002070  01 00 00 00 46 55 4c 4c  2d 30 31 00 00 00 00 00  |....FULL-01.....|
---
> 00002060  01 00 00 00 5b 8a 4c 35  24 00 00 00 04 00 00 00  |....[.L5$.......|
> 00002070  01 00 00 00 46 55 4c 4c  2d 30 32 00 00 00 00 00  |....FULL-02.....|

These two full backup files differ only the by these four lines. These differences are in the 17th OS block of the files and can be identified as metadata by the tags FULL-0[12] and the database name JS03. There are other differences that do not appear as printable characters. I did spend a little time trying to decode their meanings; they may be backup piece#, backup piece keys, backup set keys, and similar. Determining the meaning of these didn’t really seem necessary to understand the difference between full and incremental level 0 backups, so I did not continue. If you do know what these values represent, please, leave a comment.

Doing a diff on the level 0 hex dump files shows changes in the same lines:

[root@lestrade hexdump]# diff lvl0-01_1aqkp2mr_1_1.txt lvl0-02_1gqkp2r9_1_1.txt
7,8c7,8
< 00002010  bc 02 00 00 00 02 10 0c  00 02 10 0c c8 29 05 07  |.............)..|
< 00002020  4a 53 30 33 00 00 00 00  1a 1a 01 00 00 00 00 00  |JS03............|
---
> 00002010  34 03 00 00 00 02 10 0c  00 02 10 0c c8 29 05 07  |4............)..|
> 00002020  4a 53 30 33 00 00 00 00  39 1a 01 00 00 00 00 00  |JS03....9.......|
12,13c12,13
< 00002060  01 00 00 00 db 8a 4c 35  2a 00 00 00 04 00 00 00  |......L5*.......|
< 00002070  01 00 00 00 4c 56 4c 30  2d 30 31 00 00 00 00 00  |....LVL0-01.....|
---
> 00002060  01 00 00 00 69 8b 4c 35  30 00 00 00 04 00 00 00  |....i.L50.......|
> 00002070  01 00 00 00 4c 56 4c 30  2d 30 32 00 00 00 00 00  |....LVL0-02.....|

Now it is time to compare a full backup file to a level 0 backup file:

[root@lestrade hexdump]# diff full-01_0uqkp2eq_1_1.txt lvl0-01_1aqkp2mr_1_1.txt
7,8c7,8
< 00002010  46 7d 00 00 00 02 10 0c  00 02 10 0c c8 29 05 07  |F}...........)..|
< 00002020  4a 53 30 33 00 00 00 00  de 19 01 00 00 00 00 00  |JS03............|
---
> 00002010  bc 02 00 00 00 02 10 0c  00 02 10 0c c8 29 05 07  |.............)..|
> 00002020  4a 53 30 33 00 00 00 00  1a 1a 01 00 00 00 00 00  |JS03............|
12,13c12,13
< 00002060  01 00 00 00 da 89 4c 35  1e 00 00 00 04 00 00 00  |......L5........|
< 00002070  01 00 00 00 46 55 4c 4c  2d 30 31 00 00 00 00 00  |....FULL-01.....|
---
> 00002060  01 00 00 00 db 8a 4c 35  2a 00 00 00 04 00 00 00  |......L5*.......|
> 00002070  01 00 00 00 4c 56 4c 30  2d 30 31 00 00 00 00 00  |....LVL0-01.....|
15c15
< 00002090  00 00 00 00 04 04 00 00  5a 08 00 00 c3 2c 04 00  |........Z....,..|
---
> 00002090  00 00 00 00 05 04 00 00  5a 08 00 00 c3 2c 04 00  |........Z....,..|

Notice the new line 2090? This is the line that contains the difference between a full and a level 0 backup file.

Before continuing it is time to create a couple of level 1 backups:

SQL> create user dilbert identified by phb;
User created.
SQL> alter user dilbert quota 10m on users;
User altered.
SQL> create table dilbert.t1 as select systimestamp t1 from dual;
Table created.
====
rman target / catalog rman12c/xxx@oravm <<-EOF
run {
allocate channel ch1 device type disk format '/mnt/oracle-backups/ora12c/lvl1-01/%U';
backup incremental level 1 database tag 'lvl1-01' plus archivelog tag 'lvl1-01';
}
EOF
===
Create another table in dilbert schema
SQL> create table dilbert.t2 as select * from dba_users;
Table created.
rman target / catalog rman12c/xxx@oravm <<-EOF
run {
allocate channel ch1 device type disk format '/mnt/oracle-backups/ora12c/lvl1-03/%U';
backup incremental level 1 database tag 'lvl1-03' plus archivelog tag 'lvl1-03';
}
EOF
Note:  lvl1-02 backup was discarded as I forgot to make db changes before making it.

If we were to go on and compare all of the backup files that contain datafile 1:

[root@lestrade hexdump]# grep ^00002090 *.txt
full-01_0uqkp2eq_1_1.txt:00002090  00 00 00 00 04 04 00 00  5a 08 00 00 c3 2c 04 00  |........Z....,..|
full-02_14qkp2ir_1_1.txt:00002090  00 00 00 00 04 04 00 00  5a 08 00 00 c3 2c 04 00  |........Z....,..|
lvl0-01_1aqkp2mr_1_1.txt:00002090  00 00 00 00 05 04 00 00  5a 08 00 00 c3 2c 04 00  |........Z....,..|
lvl0-02_1gqkp2r9_1_1.txt:00002090  00 00 00 00 05 04 00 00  5a 08 00 00 c3 2c 04 00  |........Z....,..|
lvl1-01_1mqkp7jk_1_1.txt:00002090  00 00 00 00 05 00 00 00  5a 08 00 00 c3 2c 04 00  |........Z....,..|
lvl1-03_22qks49l_1_1.txt:00002090  00 00 00 00 05 00 00 00  5a 08 00 00 c3 2c 04 00  |........Z....,..|

A bitmask emerges that is peculiar to each backup type:

Starting at offset 0x2090
     00 01 02 03 04 05 06 07
Full 00 00 00 00 04 04 00 00  
LVL0 00 00 00 00 05 04 00 00
LVL1 00 00 00 00 05 00 00 00

What might these mean?
Here’s a possible explanation:

0x2094 = 0x04 = FULL
0x2094 = 0x05 = Incremental

0x2095 = 0x04 = level 0 if the previous byte is 0x05
0x2095 = 0x00 = level 1 if the previous byte is 0x05

Of course this doesn’t explain the purpose of 0x04 at 0x205 if the backup is ‘FULL’.

To fully understand these flags would require dumping and examining many more backup files, not just of datafiles but of archive logs, controlfiles, etc.

One thing is clear from this experiment; there are differences in the metadata contained in the file headers when comparing a full backup to a level 0 incremental backup. It is not simply a matter of the one backup being cataloged differently by RMAN; the backup pieces can be identified as either full or incremental level backups based on the metadata in the file header.

So what good is all this effort for a little bit of knowledge?

How about the next time you are asked about the difference between a level 0 and full backup? Now you can say something other that quoting the documentation or some article on the internet (one that is lacking anything to backup the assertions made).

If the question is posed to you during an interview, this information becomes even more valuable.

Now another question comes to mind: Can backup files made with a full backup be modified to work as incremental level 0 backups?

That would be an interesting experiment. This is not something one would normally do, but it is not too hard to imagine a scenario where only a full backup, an incremental level 1 backup and the following archive logs were available to recover a database. This would be a desperate measure and a last resort, but sometimes last resorts are necessary.

Do you think this can be done? If so please give it a try, but only test it on an expendable test system please.

Please be sure to leave a comment if you try this experiment, it would be quite interesting to discuss.

 

Discover more about our expertise in Oracle.

email

Author

Interested in working with Jared? Schedule a tech call.

About the Author

Oracle experience: started with Oracle 7.0.13 Programming Experience: Perl, PL/SQL, Shell, SQL Also some other odds and ends that are no longer useful Systems: Networking, Storage, OS to varying degrees. Have fond memories of DG/UX

23 Comments. Leave new

Hello Jared,

Thanks for the post (nice overview).

I would like to add something related to the “WWW” information which states that the difference between “full, level 0, level 1” backups is only stored in the RMAN catalog.

e.g. What happens if we try to catalog the backup files, how would then RMAN know what type of backup we are trying to catalog unless there is information in the backup files header.

I would expect “physical” differences in the backup files, about the “identical” part of full and level 0 backups I think it is related only to the data that is backed up.

Regards
Ivica Arsov

Reply

Hi Ivica,

I agree with you. However I have found contradictory information in a number of places after searching. This and the fact that I wanted a definitive answer to the question is what led to this article.

Jared

Reply

Very nicely clarified

Reply

0x2010 checksum

rman > change backuppiece …. uncatalog ;

$ bvi -b 0 -s 16384 full_…
modify 0x2094 0x04=>0x05

— using bbed
BBED> set dba 20,1
DBA 0x05000001 (83886081 20,1)
BBED> sum apply

rman> catalog backuppiece “….” ;

RMAN> list backupset ;
— you will Type= Incr .

Reply

I installed bvi and gave that a try. Apparently there is more metadata than that that is required to change than just that flag.

### modify header bit to change Incremental to Full – does not work

RMAN> change backuppiece ‘/mnt/oracle-backups/ora12c/lvl1-03/21qks49k_1_1’ uncatalog;

uncataloged backup piece
backup piece handle=/mnt/oracle-backups/ora12c/lvl1-03/21qks49k_1_1 RECID=121 STAMP=894308660
Uncataloged 1 objects

# modify the file 0x2094 from 0x05 to 0x04

RMAN> catalog backuppiece ‘/mnt/oracle-backups/ora12c/lvl1-03/21qks49k_1_1’;

ORA-19870: error while restoring backup piece /mnt/oracle-backups/ora12c/lvl1-03/21qks49k_1_1
ORA-19608: /mnt/oracle-backups/ora12c/lvl1-03/21qks49k_1_1 is not a backup piece

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 11/17/2015 07:46:38
RMAN-06209: List of failed objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: ————— —————————————————
RMAN-06214: Backup Piece /mnt/oracle-backups/ora12c/lvl1-03/21qks49k_1_1

# Restore the original file back

RMAN> catalog backuppiece ‘/mnt/oracle-backups/ora12c/lvl1-03/21qks49k_1_1’;

cataloged backup piece
backup piece handle=/mnt/oracle-backups/ora12c/lvl1-03/21qks49k_1_1 RECID=1 STAMP=896007889

### modify header bit to change Full to Incremental – also fails.

RMAN> change backuppiece ‘/mnt/oracle-backups/ora12c/full-02/15qkp2k8_1_1’ uncatalog;

uncataloged backup piece
backup piece handle=/mnt/oracle-backups/ora12c/full-02/15qkp2k8_1_1 RECID=98 STAMP=894208648
Uncataloged 1 objects

# modify the file 0x2094 from 0x04 to 0x05

catalog backuppiece ‘/mnt/oracle-backups/ora12c/full-02/15qkp2k8_1_1’;

RMAN> catalog backuppiece ‘/mnt/oracle-backups/ora12c/full-02/15qkp2k8_1_1’;

ORA-19870: error while restoring backup piece /mnt/oracle-backups/ora12c/full-02/15qkp2k8_1_1
ORA-19608: /mnt/oracle-backups/ora12c/full-02/15qkp2k8_1_1 is not a backup piece

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 11/17/2015 07:59:57
RMAN-06209: List of failed objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: ————— —————————————————
RMAN-06214: Backup Piece /mnt/oracle-backups/ora12c/full-02/15qkp2k8_1_1

Reply

RMAN> backup database format ‘/home/oracle/backup/full_%u.dbf’ tag=’FULL’;

Starting backup at 2015-11-18 22:48:24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=202 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/mnt/ramdisk/book/system01.dbf
input datafile file number=00002 name=/mnt/ramdisk/book/sysaux01.dbf
input datafile file number=00003 name=/mnt/ramdisk/book/undotbs01.dbf
input datafile file number=00005 name=/mnt/ramdisk/book/example01.dbf
input datafile file number=00004 name=/mnt/ramdisk/book/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-11-18 22:48:25
Finished backup at 2015-11-18 22:48:42

RMAN> list backup ;

List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————-
1 Full 1.88G DISK 00:00:10 2015-11-18 22:48:35
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /home/oracle/backup/full_01qmjt9p.dbf
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——————- —-
1 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/system01.dbf
2 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/sysaux01.dbf
3 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/undotbs01.dbf
4 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/users01.dbf
5 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/example01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————-
2 Full 9.67M DISK 00:00:01 2015-11-18 22:48:41
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /home/oracle/backup/full_02qmjta8.dbf
SPFILE Included: Modification time: 2015-11-18 22:00:20
SPFILE db_unique_name: BOOK
Control File Included: Ckp SCN: 2067619 Ckp time: 2015-11-18 22:48:40

RMAN> change backuppiece ‘/home/oracle/backup/full_01qmjt9p.dbf’ uncatalog ;
uncataloged backup piece
backup piece handle=/home/oracle/backup/full_01qmjt9p.dbf RECID=1 STAMP=896136505
Uncataloged 1 objects

$ ll
total 1983948
-rw-r—– 1 oracle oinstall 2019409920 2015-11-18 22:50:07 full_01qmjt9p.dbf
-rw-r—– 1 oracle oinstall 10158080 2015-11-18 22:48:41 full_02qmjta8.dbf

$ bvi -s 16384 full_01qmjt9p.dbf

— modify the file 0x2094 from 0x04 to 0x05

— using bbed compute checksum
BBED> set filename ‘/home/oracle/backup/full_01qmjt9p.dbf’
FILENAME /home/oracle/backup/full_01qmjt9p.dbf

BBED> sum
Check value for File 0, Block 1:
current = 0x2722, required = 0x2723

BBED> sum apply
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Check value for File 0, Block 1:
current = 0x2723, required = 0x2723

RMAN> catalog backuppiece ‘/home/oracle/backup/full_01qmjt9p.dbf’ ;
cataloged backup piece
backup piece handle=/home/oracle/backup/full_01qmjt9p.dbf RECID=3 STAMP=896136973

RMAN> list backup ;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————-
2 Full 9.67M DISK 00:00:01 2015-11-18 22:48:41
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /home/oracle/backup/full_02qmjta8.dbf
SPFILE Included: Modification time: 2015-11-18 22:00:20
SPFILE db_unique_name: BOOK
Control File Included: Ckp SCN: 2067619 Ckp time: 2015-11-18 22:48:40

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————-
3 Incr 0 1.88G DISK 00:00:00 2015-11-18 22:48:25
~~~~~
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /home/oracle/backup/full_01qmjt9p.dbf
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——————- —-
1 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/system01.dbf
2 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/sysaux01.dbf
3 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/undotbs01.dbf
4 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/users01.dbf
5 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/example01.dbf

— type full => incr0.

Reply

RMAN> backup database format ‘/home/oracle/backup/full_%u.dbf’ tag=’FULL’;

Starting backup at 2015-11-18 22:48:24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=202 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/mnt/ramdisk/book/system01.dbf
input datafile file number=00002 name=/mnt/ramdisk/book/sysaux01.dbf
input datafile file number=00003 name=/mnt/ramdisk/book/undotbs01.dbf
input datafile file number=00005 name=/mnt/ramdisk/book/example01.dbf
input datafile file number=00004 name=/mnt/ramdisk/book/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-11-18 22:48:25
Finished backup at 2015-11-18 22:48:42

RMAN> list backup ;

List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————-
1 Full 1.88G DISK 00:00:10 2015-11-18 22:48:35
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /home/oracle/backup/full_01qmjt9p.dbf
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——————- —-
1 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/system01.dbf
2 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/sysaux01.dbf
3 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/undotbs01.dbf
4 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/users01.dbf
5 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/example01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————-
2 Full 9.67M DISK 00:00:01 2015-11-18 22:48:41
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /home/oracle/backup/full_02qmjta8.dbf
SPFILE Included: Modification time: 2015-11-18 22:00:20
SPFILE db_unique_name: BOOK
Control File Included: Ckp SCN: 2067619 Ckp time: 2015-11-18 22:48:40

RMAN> change backuppiece ‘/home/oracle/backup/full_01qmjt9p.dbf’ uncatalog ;
uncataloged backup piece
backup piece handle=/home/oracle/backup/full_01qmjt9p.dbf RECID=1 STAMP=896136505
Uncataloged 1 objects

$ ll
total 1983948
-rw-r—– 1 oracle oinstall 2019409920 2015-11-18 22:50:07 full_01qmjt9p.dbf
-rw-r—– 1 oracle oinstall 10158080 2015-11-18 22:48:41 full_02qmjta8.dbf

$ bvi -s 16384 full_01qmjt9p.dbf

— modify the file 0x2094 from 0x04 to 0x05

— using bbed compute checksum
BBED> set filename ‘/home/oracle/backup/full_01qmjt9p.dbf’
FILENAME /home/oracle/backup/full_01qmjt9p.dbf

BBED> sum
Check value for File 0, Block 1:
current = 0x2722, required = 0x2723

BBED> sum apply
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Check value for File 0, Block 1:
current = 0x2723, required = 0x2723

RMAN> catalog backuppiece ‘/home/oracle/backup/full_01qmjt9p.dbf’ ;
cataloged backup piece
backup piece handle=/home/oracle/backup/full_01qmjt9p.dbf RECID=3 STAMP=896136973

RMAN> list backup ;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————-
2 Full 9.67M DISK 00:00:01 2015-11-18 22:48:41
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /home/oracle/backup/full_02qmjta8.dbf
SPFILE Included: Modification time: 2015-11-18 22:00:20
SPFILE db_unique_name: BOOK
Control File Included: Ckp SCN: 2067619 Ckp time: 2015-11-18 22:48:40

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————-
3 Incr 0 1.88G DISK 00:00:00 2015-11-18 22:48:25
~~~~~
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /home/oracle/backup/full_01qmjt9p.dbf
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——————- —-
1 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/system01.dbf
2 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/sysaux01.dbf
3 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/undotbs01.dbf
4 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/users01.dbf
5 Full 2067614 2015-11-18 22:48:25 /mnt/ramdisk/book/example01.dbf

— type full => incr.

Reply

RMAN> backup database format ‘/home/oracle/backup/full_%u.dbf’ tag=’FULL’;

RMAN> list backup ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————-
1 Full 1.88G DISK 00:00:10 2015-11-18 22:48:35
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /home/oracle/backup/full_01qmjt9p.dbf
List of Datafiles in backup set 1
….
RMAN> change backuppiece ‘/home/oracle/backup/full_01qmjt9p.dbf’ uncatalog ;
uncataloged backup piece
backup piece handle=/home/oracle/backup/full_01qmjt9p.dbf RECID=1 STAMP=896136505
Uncataloged 1 objects

$ bvi -s 16384 full_01qmjt9p.dbf

— modify the file 0x2094 from 0x04 to 0x05

— using bbed compute checksum
BBED> set filename ‘/home/oracle/backup/full_01qmjt9p.dbf’
FILENAME /home/oracle/backup/full_01qmjt9p.dbf

BBED> sum
Check value for File 0, Block 1:
current = 0x2722, required = 0x2723

BBED> sum apply
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Check value for File 0, Block 1:
current = 0x2723, required = 0x2723

RMAN> catalog backuppiece ‘/home/oracle/backup/full_01qmjt9p.dbf’ ;
cataloged backup piece
backup piece handle=/home/oracle/backup/full_01qmjt9p.dbf RECID=3 STAMP=896136973

RMAN> list backup ;
….

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————-
3 Incr 0 1.88G DISK 00:00:00 2015-11-18 22:48:25
~~~~~
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /home/oracle/backup/full_01qmjt9p.dbf
List of Datafiles in backup set 3

— type full => incr0.

Reply

Cool, thanks for the further tests. I had wrongly assumed that the use of bbed was an alternative to bvi, but a quick look at your posts show that is not the case.
Thanks again, I will try this out again.

Reply
Kamran Bakhshandeh
August 26, 2016 12:35 am

Hi Jared

Another difference is that level o backups can not be changed with “KEEP” option:

RMAN> change backup tag FB_FULL_ONEOFF keep UNTIL TIME ’31-SEP-2016′;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of KEEP command at 08/26/2016 15:28:16
RMAN-06531: CHANGE … KEEP not supported for incremental BACKUPSET

RMAN>

Reply

Is there any place in the DB or in the RMAN catalog where we can find a column to store if we are running a Level 0 or a Level 1? I have spent many time looking in the view without luck.

Any help would be appreciated.

Tks.

Reply

The following views may be used to determine the incremental level


11:25:52 ora12c102rac02.jks.com - sys@js122a2 SQL> @findcol sys incremental_level
This utility searches for columns in DBA_TAB_COLUMNS

Account to search for column on (Partial Name OK) :
Column name to search for (Partial Name OK) :

OWNER      TABLE NAME                     COLUMN                         DATA_TYPE
---------- ------------------------------ ------------------------------ --------------------
SYS        GV_$BACKUP_DATAFILE            INCREMENTAL_LEVEL              NUMBER

           GV_$BACKUP_SET                 INCREMENTAL_LEVEL              NUMBER

           GV_$DATAFILE_COPY              INCREMENTAL_LEVEL              NUMBER

           GV_$PROXY_DATAFILE             INCREMENTAL_LEVEL              NUMBER

           V_$BACKUP_DATAFILE             INCREMENTAL_LEVEL              NUMBER

           V_$BACKUP_DATAFILE_DETAILS     INCREMENTAL_LEVEL              NUMBER

           V_$BACKUP_SET                  INCREMENTAL_LEVEL              NUMBER

           V_$BACKUP_SET_DETAILS          INCREMENTAL_LEVEL              NUMBER

           V_$DATAFILE_COPY               INCREMENTAL_LEVEL              NUMBER

           V_$PROXY_DATAFILE              INCREMENTAL_LEVEL              NUMBER



10 rows selected.

Reply

The following RMAN catalog views also can be used to determine incremental level

11:29:51 oravm01.jks.com - sys@oravm1 SQL> @findcol rman12c incremental
This utility searches for columns in DBA_TAB_COLUMNS

Account to search for column on (Partial Name OK) :
Column name to search for (Partial Name OK) :

11:30:18 oravm01.jks.com - sys@oravm1 SQL> /

OWNER      TABLE NAME                     COLUMN                         DATA_TYPE
---------- ------------------------------ ------------------------------ --------------------
RMAN12C    RCI_BACKUP_DATAFILE            INCREMENTAL_CHANGE#            NUMBER
                                          INCREMENTAL_LEVEL              NUMBER

           RCI_BACKUP_PIECE               INCREMENTAL_LEVEL              NUMBER

           RCI_BACKUP_SET                 INCREMENTAL_LEVEL              NUMBER

           RC_BACKUP_DATAFILE             INCREMENTAL_CHANGE#            NUMBER
                                          INCREMENTAL_LEVEL              NUMBER

           RC_BACKUP_DATAFILE_DETAILS     INCREMENTAL_CHANGE#            NUMBER
                                          INCREMENTAL_LEVEL              NUMBER

           RC_BACKUP_PIECE                INCREMENTAL_LEVEL              NUMBER

           RC_BACKUP_PIECE_DETAILS        INCREMENTAL_LEVEL              NUMBER

           RC_BACKUP_SET                  INCREMENTAL_LEVEL              NUMBER

           RC_BACKUP_SET_DETAILS          INCREMENTAL_LEVEL              NUMBER

           RC_DATAFILE_COPY               INCREMENTAL_LEVEL              NUMBER

           RC_PROXY_DATAFILE              INCREMENTAL_LEVEL              NUMBER

           _RS_RCI_BACKUP_DATAFILE_       INCREMENTAL_CHANGE#            NUMBER
                                          INCREMENTAL_LEVEL              NUMBER

           _RS_RCI_BACKUP_PIECE_          INCREMENTAL_LEVEL              NUMBER

           _RS_RCI_BACKUP_SET_            INCREMENTAL_LEVEL              NUMBER



18 rows selected.

Reply

Tks Jared, but that is actually my problem, Im able to find those columns but they don’t have any useful information, that is, Im running an incremental backup, and checking those views in the database and the column incremental_level does not have information at all! Just like it is not being updated there, I understand that in the catalog there is no information for backups currently running but only historical, for currenctly running info I need to check locally in the database, so, Im still lost about where to look at the incremental level of a currently running backup.

Reply

Hi Gabriel,

What version of Oracle is this?

What is the value of the BACKUP_TYPE column?

Reply

11204.

Right now Im running a INCR L0 for a db:

SQL> select operation, status, OBJECT_TYPE, OUTPUT_DEVICE_TYPe from v$rman_status where status like ‘RUNNING%’;

OPERATION STATUS OBJECT_TYPE OUTPUT_DEVICE_TYP
————— ———————– ————- —————–
BACKUP RUNNING DB INCR SBT_TAPE

SQL> select session_key, start_time, OUTPUT_DEVICE_TYPe, STATUS, INPUT_TYPE from V$RMAN_BACKUP_JOB_DETAILS where status like ‘RUNNING%’;

SESSION_KEY START_TIM OUTPUT_DEVICE_TYP STATUS INPUT_TYPE
———– ——— —————– ———————– ————-
9092 01-NOV-16 SBT_TAPE RUNNING DB INCR

So far, so good, now, I need to display if the incremental is L0, L1; however, I’m not able to find in any view this value, I have verified several views but this value is null for that column, i.e. INCREMENTAL_LEVEL:

FROM RMAN script:

BACKUP TAG ‘xxxx_full’ INCREMENTAL LEVEL 0 DATABASE INCLUDE CURRENT CONTROLFILE;

SQL> select backup_type, INCREMENTAL_LEVEL from v$backup_set where start_time > trunc(sysdate) order by start_time;

B INCREMENTAL_LEVEL
– ————————-
L
L
L
L
L

Hi Gabriel,

Take a look here at the docs for v$backup_set:
https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_1061.htm#REFRN30025

The current backups as posted are for archive logs.

Try this:
select distinct backup_type
, count(*) over (partition by backup_type order by backup_type) bkup_counts
from v$backup_set
/

I have only a test database to try this on right no – no backups of it.

Reply

hi Jared,

then I’m still more confused than before. This is the only backup that is running today in that db (that is why I filtered by sysdate), and it is currently processing datafiles, so I’m back to starting point, where is oracle supossed to store this value in the DD? And why in heaven’s name is so hard to find it? :)

Reply

Hi Jared, I think my last reply got lost, I was mentioning that I’m more confused now, this is the only backup running today for this db and currently is processing datafiles, so not sure why the value is ‘L’, also I’m back to the starting point, no idea where is oracle storing incremental level value and why is so hard to find,

Reply

Hi Gabriel, I’m not sure what else I can help with here. Oracle has indicated the backups currently running are for archive logs only. Perhaps you could correlate the times from the dictionary views to the RMAN logs.

Reply

Hi Jared, I’v got a solution, is not very elegant but at least is working:

select distinct(substr(output,instr(output,’incremental level’,1,1)+18, 1)) IL
from gv$rman_output
where
output like ‘%incremental%’
and session_RECID=&session_recid;

And getting session_recid from V$RMAN_BACKUP_JOB_DETAILS. This way I can get the incremental level for the current backup from the backup output. :)

Rigth now, I’m in testing phase but all my cases have been successfull so far.

I really appreciate your help here.

Tks!

Reply

My pleasure Gabriel. It’s been a few months since I have made heavy use of RMAN DD queries, and so don’t recall the details behind this thought, but I do recall preferring the RC_ views in the RMAN catalog over the V$ views. Something to consider.

The problem is that RC_views are not in real time, but historical. By the way, I think one of my friends works with you, can you pls give him my regards? It’s Rene Antunez.

Leave a Reply

Your email address will not be published. Required fields are marked *