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/[email protected] <<-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/[email protected] <<-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/[email protected] <<-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/[email protected] <<-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:
[[email protected] 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 [[email protected] 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 [[email protected] 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 [[email protected] 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 [[email protected] 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.
[[email protected] 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:
[[email protected] 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:
[[email protected] 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/[email protected] <<-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/[email protected] <<-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:
[[email protected] 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.
24 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
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
Very nicely clarified
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 .
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
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.
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.
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.
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.
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>
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.
The following views may be used to determine the incremental level
The following RMAN catalog views also can be used to determine incremental level
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.
Hi Gabriel,
What version of Oracle is this?
What is the value of the BACKUP_TYPE column?
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.
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? :)
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,
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.
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!
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.
how to
Q1:
Your Oracle Database storage system is “file system” and you have to move all database-related files and recovery area to new partition “E:\” for example with the least database down time Write the suitable code in correct syntax.
Q2:
Schedule “backup Oracle database” as following:
1- Thursday backup incremental 0 at 22:00
2- Rest of the week incremental 1 at 21:00
Create the appropriate automation schedule scripts for the request and explain in detail what to do with the right code
Create table as following :
Create table your_group_name_tab (name varchar2 (5), age number,salary number);
• Insert into table three records
• Commit
• Update your_group_name_tab by setting salary = salary * 1.2
• commit
• Suppose you forgot the old value and you want to recover table to before update Recover the table to before update with all steps needed