Monthly Archives: May 2019

Online Redo Log Switch Frequency Map

Posted on by Standard 4

A query I find myself often running is the online redo log switch frequency map query, which queries the v$log_history/gv$log_history (for cluster databases) view and show the historical log switch frequency.

Why you might ask? Well it’s important to see how frequent log switches are occurring as Oracle’s rule of thumb is to not switch more then 3 logs per hour (20 minutes of redo) at peak DML activity to prevent excessive checkpoints.  The query can also highlight abnormal churn (DML activity).

Online Redo Log Switch Frequency Map Query

set pages 999 lines 400
col h0 format 999
col h1 format 999
col h2 format 999
col h3 format 999
col h4 format 999
col h5 format 999
col h6 format 999
col h7 format 999
col h8 format 999
col h9 format 999
col h10 format 999
col h11 format 999
col h12 format 999
col h13 format 999
col h14 format 999
col h15 format 999
col h16 format 999
col h17 format 999
col h18 format 999
col h19 format 999
col h20 format 999
col h21 format 999
col h22 format 999
col h23 format 999
SELECT TRUNC (first_time) "Date", inst_id, TO_CHAR (first_time, 'Dy') "Day",
 COUNT (1) "Total",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23",
 ROUND (COUNT (1) / 24, 2) "Avg"
FROM gv$log_history
WHERE thread# = inst_id
AND first_time > sysdate -7
GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy')
ORDER BY 1,2;

Online Redo Log Switch Frequency Map Output

SQL> set pages 999 lines 400
SQL> col h0 format 999
SQL> col h1 format 999
SQL> col h2 format 999
SQL> col h3 format 999
SQL> col h4 format 999
SQL> col h5 format 999
SQL> col h6 format 999
SQL> col h7 format 999
SQL> col h8 format 999
SQL> col h9 format 999
SQL> col h10 format 999
SQL> col h11 format 999
SQL> col h12 format 999
SQL> col h13 format 999
SQL> col h14 format 999
SQL> col h15 format 999
SQL> col h16 format 999
SQL> col h17 format 999
SQL> col h18 format 999
SQL> col h19 format 999
SQL> col h20 format 999
SQL> col h21 format 999
SQL> col h22 format 999
SQL> col h23 format 999
SQL> SELECT TRUNC (first_time) "Date", inst_id, TO_CHAR (first_time, 'Dy') "Day",
SQL>  COUNT (1) "Total",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",
SQL>  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23",
SQL>  ROUND (COUNT (1) / 24, 2) "Avg"
SQL> FROM gv$log_history
SQL> WHERE thread# = inst_id
SQL> AND first_time > sysdate -7
SQL> GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy')
SQL> ORDER BY 1,2;

Date         INST_ID Day      Total   h0   h1   h2   h3   h4   h5   h6   h7   h8   h9  h10  h11  h12  h13  h14  h15  h16  h17  h18  h19  h20  h21  h22  h23        Avg
--------- ---------- --- ---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----------
24-MAY-19          1 Fri          1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1        .04
24-MAY-19          2 Fri          1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1        .04
25-MAY-19          1 Sat         56    1    1    3    0    2    2    0    7    0    0   10    4    4    6    3    1    2    4    1    1    1    2    0    1       2.33
25-MAY-19          2 Sat         62    0    3    3    0    1    2    0    7    0    0    8    5    2    2    3    5    4    2    3    3    5    4    0    0       2.58
26-MAY-19          1 Sun         56    1    1    3    0    2    5    0   11    0    4    1    1    7    7    3    5    1    0    0    0    1    1    1    1       2.33
26-MAY-19          2 Sun         28    1    3    3    0    1    4    0    5    0    1    1    0    2    3    1    1    1    0    0    0    0    0    1    0       1.17
27-MAY-19          1 Mon         33    1    1    3    0    1    4    0    6    5    1    0    1    1    2    1    1    1    0    0    0    1    1    0    2       1.38
27-MAY-19          2 Mon         21    0    3    3    0    0    4    0    3    2    0    0    1    0    1    0    1    0    1    0    0    0    0    1    1        .88
28-MAY-19          1 Tue         43    2    3    3    0    3    5    1    4    3    1    3    0    1    1    1    1    2    3    2    1    1    0    1    1       1.79
28-MAY-19          2 Tue         38    2    4    3    1    1    4    1    7    1    1    1    1    0    1    0    0    2    4    0    1    0    1    1    1       1.58
29-MAY-19          1 Wed         58    2    4    4    1    3    5    2   12    3    1    3    1    2    5    4    1    1    0    1    0    1    0    2    0       2.42
29-MAY-19          2 Wed         28    0    2    3    0    1    4    1    5    1    0    1    1    0    2    1    1    1    1    0    0    1    1    0    1       1.17
30-MAY-19          1 Thu         39    2    3    3    0    1    5    1    7    1    1    2    3    1    2    2    1    0    1    1    0    0    1    0    1       1.63
30-MAY-19          2 Thu         29    0    1    3    0    2    4    1    4    2    1    1    1    1    1    2    0    1    2    0    0    1    1    0    0       1.21
31-MAY-19          1 Fri        153    2    1    3    0    2    4    1    8    3    0   23   29   10    2    1   18   20    6   12    2    3    3    0    0       6.38
31-MAY-19          2 Fri        223    1    2    3    0    1    4    0    3    1    0   71   86   29    1    0    6    7    2    4    0    1    1    0    0       9.29

16 rows selected.

SQL>

Where the log switch frequency exceeds 3 per hour, I have highlighted as red.

From the above output, we can see that most the time the 3 log switch per hour is met, with the 5th and 7th hour exceeding the threshold.  Also we can see today there as been abnormal and excessive churn at 10th and 11th hour.

Investigating this further, we look at v$log to see the size of the online redo logs:

SQL> set pages 999 lines 400
SQL> col FIRST_CHANGE# format 999999999999999
SQL> select GROUP#, THREAD#, SEQUENCE#, BYTES/1024/1024 SIZE_MB, BLOCKSIZE, MEMBERS, ARCHIVED, STATUS, FIRST_CHANGE#, FIRST_TIME, NEXT_CHANGE#, NEXT_TIME, CON_ID from v$log;

    GROUP#    THREAD#  SEQUENCE#    SIZE_MB  BLOCKSIZE    MEMBERS ARC STATUS              FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------- --------- ------------ --------- ----------
         1          1      40649       4096        512          2 YES INACTIVE             137921001754 31-MAY-19   1.3793E+11 31-MAY-19          0
         2          1      40650       4096        512          2 NO  CURRENT              137930464620 31-MAY-19   2.8147E+14                    0
         3          1      40648       4096        512          2 YES INACTIVE             137920121808 31-MAY-19   1.3792E+11 31-MAY-19          0
         4          2      39319       4096        512          2 NO  CURRENT              137921005288 31-MAY-19   2.8147E+14                    0
         5          2      39317       4096        512          2 YES INACTIVE             137917291807 31-MAY-19   1.3792E+11 31-MAY-19          0
         6          2      39318       4096        512          2 YES INACTIVE             137919186475 31-MAY-19   1.3792E+11 31-MAY-19          0

6 rows selected.

SQL>

As we can see the online redo logs are 4GB, which is the default size on Oracle Exadata, which this is.  We could increase the size of the redo logs but it trade-off between performance verse space usage.  The peak we can see was 29 log switches for instance 1 and 86 log switches for instance 2, assuming these were full redo logs before switching, this is 29 + 86 logs switches for the database.  Which is 115 for the the hour, equating to a significant 460 GB of churn!  But this is an Exadata Machine and is sized and designed for this sort of load 🙂  To resize the redo logs to switch only 3 per hour, we would need to resize to 154GB, this wouldn’t be feasible, so we could either increase to something more reasonable like 10GB or leave as is, depending on what more important, the performance or space usage.

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Prevent crontab jobs overlapping using flock

Posted on by Standard 2

As an Oracle DBA, you may find yourself in the situation where you have crontab jobs overlapping.

For example an RMAN backup takes longer then normal, then overlaps with another RMAN database backup leading to more resources being consumed:

00 00 * * * /home/oracle/scripts/backups/db_backup.sh PROD1 >> /home/oracle/backups/logs/db_backup_PROD1.log 2>&1
00 01 * * * /home/oracle/scripts/backups/db_backup.sh PROD2 >> /home/oracle/backups/logs/db_backup_PROD2.log 2>&1

If the PROD1 backup takes longer then 1 hour, then it will contend with the PROD2 backup when it starts.

Another more recent example for myself, is when I was copying archive logs to AWS for an Oracle Database Standard Edition migration using a manual standby, thus needed to manually transfer archive logs using rsync every 15 minutes:

0,15,30,45 * * * * /home/oracle/copy_Arch_to_AWS.sh > /home/oracle/copy_Arch_to_AWS.log

Ran fine most the time but when there was significant churn in the database, the crontab job would overlap causing several rsync 😦

The easiest solution is to wrap the crontab job in flock 🙂

Using flock

flock is a linux utility that can uses a lock file to determine if the process is already running.  The syntax I use is:

flock -x <lockfile> -c '<command>'

The “-x” is to obtain exclude lock and the “-c” is the command to run.

Flock examples

Backup example

00 00 * * * flock -x /home/oracle/scripts/backups/backup.lock -c '/home/oracle/scripts/backups/db_backup.sh PROD1 >> /home/oracle/backups/logs/db_backup_PROD1.log 2>&1'
00 01 * * * flock -x /home/oracle/scripts/backups/backup.lock -c '/home/oracle/scripts/backups/db_backup.sh PROD2 >> /home/oracle/backups/logs/db_backup_PROD2.log 2>&1'

Now when the backup for PROD2 starts flock will check for the lock and will see if exist and will not run the command until the backup for PROD1 is completed 🙂

Archive log copy example

0,15,30,45 * * * * flock -x /home/oracle/copy_Arch_to_AWS.lock -c '/home/oracle/copy_Arch_to_AWS.sh > /home/oracle/copy_Arch_to_AWS.log'

Now when the job runs, an exclusive lock is taken an hence when it runs again in 15 minutes if there an existing run, then it will not run the command until the previous one is completed 🙂  This will essentially queue the copies instead of them overlapping causing several rsync, which just exacerbate the issue.

Advance use of flock

Timeout

You can add “-w <seconds>“, which is the amount of time to wait for exclusive lock before exiting without running command. for example:

0,15,30,45 * * * * flock -w 300 -x /home/oracle/copy_Arch_to_AWS.lock -c '/home/oracle/copy_Arch_to_AWS.sh > /home/oracle/copy_Arch_to_AWS.log'

Now flock will wait 5 minutes for the previous archive log copy job to complete before exiting without running the command for that run 🙂

Viewing lock

If you want to see the lock taken by flock, you can run :

[oracle@dc1sbxdb001 ~]$ fuser -v /home/oracle/copy_Arch_to_AWS.lock
                                    USER   PID    ACCESS COMMAND
/home/oracle/copy_Arch_to_AWS.lock: oracle 341039 f....  flock
                                    oracle 341040 f....  rsync

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

RMAN Incremental Updating Backup & Demo Part 1

Posted on by Standard 7

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Incremental Updating Backup

An incremental updating backup is a level 0 incremental backup which is recovered using level 1 incremental backups.  This avoids the overhead of taking level 0 incremental backups, for example in a backup strategy where a level 0 is taken every Sunday and all other days level 1.

An simple example would be, an incremental level 0 backup is taken once and would be recovered daily.  For example everyday an incremental level 1 backup is taken and then the incremental level 1 backup taken yesterday would be used to recover the incremental level 0 backup.  Thus the incremental level 0 backup would fluctuate between 1 to 2 days behind current, i.e. at the point when the backup script is about to run, the incremental level 1 backup would be as of approximately 48 hours ago and when ran would be as of approximately 24 hours ago.

Incremental Updating Backup Demo

We take an incremental updating backup using my script  9_incremental_updated_backup.sh:

[oracle@dc1sbxdb001 demo]$ ./9_incremental_updated_backup.sh 
-----------------------
Step 1: Set environment
-----------------------

Setting the Database Environment using oraenv...
The Oracle base remains unchanged with value /u01/app/oracle

ORACLE_SID: ZEDDBA
ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1

Press Enter to continue

The environment is set to my ZEDDBA database, next we recover an incremental level 0 backup followed by taking an incremental level 1 backup, which we do three times as shown in the content of the 9_incremental_updated_backup.cmd file:

---------------------------------------
Step 2: Take Incremental Updated Backup
---------------------------------------

Clearing all backups...

Content of 9_incremental_updated_backup.cmd file:

RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE';
HOST 'read Press Enter';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE;
HOST 'read Press Enter';
RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE';
HOST 'read Press Enter';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE;
HOST 'read Press Enter';
RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE';
HOST 'read Press Enter';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE;

Press Enter to continue

Next we recover an incremental level 0 backup using the “RECOVER COPY OF DATABASE” syntax with the tag of the incremental level 1 backup we want to use for recovery “WITH TAG ‘INC UPDATE’“:

Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/9_incremental_updated_backup.cmd'

Recovery Manager: Release 12.2.0.1.0 - Production on Thu May 30 19:31:09 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ZEDDBA (DBID=3520942925)

RMAN> RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE';
Starting recover at 30-MAY-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
Finished recover at 30-MAY-19
RMAN> HOST 'read Press Enter';

host command complete

As you can see RMAN states there is no copy of the datafiles found to recover.  This is because we don’t yet have an incremental level 0 backup.

Next we take an incremental level 1 backup using the “BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘INC UPDATE’ DATABASE” syntax giving the tag of the incremental level 0 backup:

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE;
Starting backup at 30-MAY-19
using channel ORA_DISK_1
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_system_gh08cy6s_.dbf tag=INC UPDATE RECID=5 STAMP=1009654288
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_sysaux_gh08d1g5_.dbf tag=INC UPDATE RECID=6 STAMP=1009654291
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_undotbs1_gh08d4lc_.dbf tag=INC UPDATE RECID=7 STAMP=1009654293
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_users_gh08d7rb_.dbf tag=INC UPDATE RECID=8 STAMP=1009654296
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-MAY-19

Starting Control File and SPFILE Autobackup at 30-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_30/o1_mf_s_1009654296_gh08d93p_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 30-MAY-19
RMAN> HOST 'read Press Enter';

host command complete

As you can see RMAN still states there no parent backup or copy of the datafiles found, so instead takes an incremental level 0 backup (copy) for you.

Next we again recover an incremental level 0 backup using the “RECOVER COPY OF DATABASE” syntax with the tag of the incremental level 1 backup we want to use for recovery “WITH TAG ‘INC UPDATE’” to see if it behaves any different this time:

RMAN> RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE';
Starting recover at 30-MAY-19
using channel ORA_DISK_1
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
Finished recover at 30-MAY-19
RMAN> HOST 'read Press Enter';

host command complete

As you can see RMAN states there still no copy of the datafiles found to recover.  This is not exactly true as an incremental level 0 backup was taken above, however there is no incremental level 1 backup to use to recover hence the message.

Next we again take an incremental level 1 backup using the “BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘INC UPDATE’ DATABASE” syntax giving the tag of the incremental level 0 backup to see if it behaves any different this time:

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE;
Starting backup at 30-MAY-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
channel ORA_DISK_1: starting piece 1 at 30-MAY-19
channel ORA_DISK_1: finished piece 1 at 30-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_30/o1_mf_nnnd1_INC_UPDATE_gh08dsxd_.bkp tag=INC UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 30-MAY-19

Starting Control File and SPFILE Autobackup at 30-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_30/o1_mf_s_1009654317_gh08dx4z_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 30-MAY-19
RMAN> HOST 'read Press Enter';

host command complete

As you can see RMAN now is aware of the incremental level 0 backup and thus takes an incremental level 1 backup as requested.

Next we again recover an incremental level 0 backup using the “RECOVER COPY OF DATABASE” syntax with the tag of the incremental level 1 backup we want to use for recovery “WITH TAG ‘INC UPDATE’” to see if it behaves any different this time:

RMAN> RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE';
Starting recover at 30-MAY-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy file number=00001 name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_system_gh08cy6s_.dbf
recovering datafile copy file number=00002 name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_sysaux_gh08d1g5_.dbf
recovering datafile copy file number=00003 name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_undotbs1_gh08d4lc_.dbf
recovering datafile copy file number=00004 name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_users_gh08d7rb_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_30/o1_mf_nnnd1_INC_UPDATE_gh08dsxd_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_30/o1_mf_nnnd1_INC_UPDATE_gh08dsxd_.bkp tag=INC UPDATE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 30-MAY-19

Starting Control File and SPFILE Autobackup at 30-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_30/o1_mf_s_1009654322_gh08f35f_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 30-MAY-19
RMAN> HOST 'read Press Enter';

host command complete

As there is now an incremental level 1 backup that has the tag ‘INC UPDATE‘ it is used to recover the incremental level 0 backup to the point when the incremental level 1 backup was taken.

Next we again take an incremental level 1 backup using the “BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘INC UPDATE’ DATABASE” syntax giving the tag of the incremental level 0 backup to see if it behaves any different this time:

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE;
Starting backup at 30-MAY-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
channel ORA_DISK_1: starting piece 1 at 30-MAY-19
channel ORA_DISK_1: finished piece 1 at 30-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_30/o1_mf_nnnd1_INC_UPDATE_gh08ffh1_.bkp tag=INC UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30-MAY-19

Starting Control File and SPFILE Autobackup at 30-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_30/o1_mf_s_1009654334_gh08fgqj_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 30-MAY-19

Recovery Manager complete.

Press Enter to continue

As you can see RMAN takes incremental level 1 backup as requested, just as before and will continue to do so till there is no incremental level 0 backup.

Finally, we update the demo log table:

-------------------------------------
Step 3: Updating and viewing demo log
-------------------------------------
Calling 'sqlplus / as sysdba'
To updated and view demo log

1 row created.

Commit complete.

WHEN COMMENTS
------------------------------ --------------------------------------------------
16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode
16-MAY-19 05.40.23.000000 PM Full Backup
16-MAY-19 05.46.17.000000 PM Full Backup plus Archive Logs
17-MAY-19 04.55.12.000000 PM Image Copy
20-MAY-19 05.13.44.000000 PM Incremental Level 0
22-MAY-19 12.09.17.000000 PM Incremental Level 1
22-MAY-19 12.54.43.000000 PM Incremental Level 1 with BCT
24-MAY-19 06.28.33.000000 PM Incremental Level 1 Cumulative
30-MAY-19 07.32.24.000000 PM Incremental Updated Backup

9 rows selected.

Press Enter to exit shell script

[oracle@dc1sbxdb001 demo]$

Reference

Scripts

  1. 9_incremental_updated_backup.sh
  2. 9_incremental_updated_backup.cmd

To download all 2 in one zip: 9_incremental_updated_backup.zip

The rest of the series

  1. Oracle Database File Placement Best Practice & Create Database Demo
  2. RMAN Full Backup & Demo
  3. RMAN Image Copy & Demo
  4. RMAN Incremental & Demo Part 1 (Level 0)
  5. RMAN Incremental & Demo Part 2 (Level 1)
  6. RMAN Incremental with Block Change Tracking & Demo
  7. RMAN Incremental Differential vs Cumulative & Demo
  8. RMAN Incremental Updating Backup & Demo Part 1
  9. RMAN Incremental Updating Backup & Demo Part 2
  10. Flashback
  11. RMAN Block Media Recovery
  12. RMAN Recover database with only FRA
  13. RMAN Obsolete

Please Note: Links to the blog posts will be released daily and updated here.

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

VT-x is not available (VERR_VMX_NO_VMX) on VirtualBox after Windows 10 Update

Posted on by Standard Reply

So in the mist of writing my “RMAN Back to Basics” series, I got the following error when trying to start my Virtual Machine in Oracle’s VirtualBox:
VirtualBox_VERR_VMX_NO_VMX

“Failed to open a session for the virtual machine dc1sbxdb001

VT-x is not available (VERR_VMX_NO_VMX).
Result Code: E_FAIL (0x80004005)
Component: ConsoleWrap
Interface: IConsole {872da645-4a9b-1727-bee2-5585105b9eed}”

I was like, “How’s this possible?  I only just used my Virtual Machines yesterday!“.

Then I remembered Windows 10 had advised me it wanted to restart my laptop for some Windows Updates.  Probably something my work had pushed out.  So at the end of the day, I did “Update and restart” and it did take some time and several restarts.  Then I shutdown my laptop for the day.

After some googling, it seemed it was to do with Microsoft Hyper-V, so I followed my own instructions from my blog post:
Disabling Microsoft’s Hyper-V to use Oracle’s VirtualBox

And “Voilà” after a reboot my Virtual Machine started back up, so I could continue my blog series 🙂

Seems like in one of the Windows Update, Microsoft had decided to re-enable Hyper-V which I had previous had off on boot.

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

RMAN Incremental Differential vs Cumulative & Demo

Posted on by Standard 14

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Differential Incremental Backups (default)

A differential incremental backup, backs up all the blocks that have changed after the most recent incremental backup, which can be either level 1 or 0.

RMAN determines which is the most recent level 1 backup and then backs up all the blocks changed after that backup, if no level 1 then all the blocks changed after the last level 0 backup are backed up.  If no level 0 backup exist, then a level 0 backup is taken.

The diagram below shows how the level 1 differential incremental backups are just backing up from the last level 1:

Incremental_Differential

Cumulative Incremental Backups

A cumulative incremental backup, backs up all the blocks that have changed after the most recent incremental backup at level 0 only.

This reduces the work needed when recovering after a restore as only the desired level 1 is required to be applied instead of several level 1 in sequence.  The trade off is as every level 1 backup needs to backup all the blocks changed from the last level 0, it is repeated the work done by the previous level 1, thus require more space and more time.

The diagram below shows how the level 1 cumulative incremental backup are backing up from the last level 0:

Incremental_Cumulative

Cumulative Incremental Backup Demo

We take an cumulative incremental level 1 backup using my script 8_incremental_level_1_cumulative.sh:

[oracle@dc1sbxdb001 demo]$ ./8_incremental_level_1_cumulative.sh 
-----------------------
Step 1: Set environment
-----------------------

Setting the Database Environment using oraenv...
The Oracle base remains unchanged with value /u01/app/oracle

ORACLE_SID: ZEDDBA
ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1

Press Enter to continue

The environment is set to my ZEDDBA database, next we will take an differential backup twice, followed by cumulative incremental backup twice:

-------------------------------------------------
Step 2: Take Incremental Level 1 Cumlative Backup
-------------------------------------------------

Content of 8_incremental_level_1_cumulative.cmd file:

BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'INCR LEVEL 1 CUM';
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'INCR LEVEL 1 CUM';
HOST 'read Press Enter to LIST BACKUP';
LIST BACKUP;

Press Enter to continue

Next a differential incremental backup is taken:

Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/8_incremental_level_1_cumulative.cmd'

Recovery Manager: Release 12.2.0.1.0 - Production on Fri May 24 18:27:49 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ZEDDBA (DBID=3520942925)

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
Starting backup at 24-MAY-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=157 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
channel ORA_DISK_1: starting piece 1 at 24-MAY-19
channel ORA_DISK_1: finished piece 1 at 24-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_ggjbdsg0_.bkp tag=INCR LEVEL 1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-MAY-19

Starting Control File and SPFILE Autobackup at 24-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132076_ggjbdwoh_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 24-MAY-19

Next another differential incremental backup is taken:

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
Starting backup at 24-MAY-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
channel ORA_DISK_1: starting piece 1 at 24-MAY-19
channel ORA_DISK_1: finished piece 1 at 24-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_ggjbdys0_.bkp tag=INCR LEVEL 1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-MAY-19

Starting Control File and SPFILE Autobackup at 24-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132081_ggjbf23y_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 24-MAY-19

Next a cumulative incremental backup is taken:

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'INCR LEVEL 1 CUM';
Starting backup at 24-MAY-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
channel ORA_DISK_1: starting piece 1 at 24-MAY-19
channel ORA_DISK_1: finished piece 1 at 24-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf3rn_.bkp tag=INCR LEVEL 1 CUM comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-MAY-19

Starting Control File and SPFILE Autobackup at 24-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132084_ggjbf516_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 24-MAY-19

Next another cumulative incremental backup is taken:

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'INCR LEVEL 1 CUM';
Starting backup at 24-MAY-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
channel ORA_DISK_1: starting piece 1 at 24-MAY-19
channel ORA_DISK_1: finished piece 1 at 24-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf6rv_.bkp tag=INCR LEVEL 1 CUM comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-MAY-19

Starting Control File and SPFILE Autobackup at 24-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132087_ggjbf81t_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 24-MAY-19

Next we list the backup within RMAN using ‘LIST BACKUP‘:

RMAN> HOST 'read Press Enter to LIST BACKUP';
host command complete

RMAN> LIST BACKUP;

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

...
...
...
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Incr 1 38.23M DISK 00:00:02 24-MAY-19 
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_ggjbdsg0_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 603534 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 1 Incr 603534 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 1 Incr 603534 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 1 Incr 603534 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
26 Full 8.31M DISK 00:00:00 24-MAY-19 
BP Key: 26 Status: AVAILABLE Compressed: NO Tag: TAG20190524T182756
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132076_ggjbdwoh_.bkp
SPFILE Included: Modification time: 24-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 603541 Ckp time: 24-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Incr 1 64.00K DISK 00:00:03 24-MAY-19 
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_ggjbdys0_.bkp
List of Datafiles in backup set 27
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 603560 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 1 Incr 603560 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 1 Incr 603560 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 1 Incr 603560 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
28 Full 8.31M DISK 00:00:01 24-MAY-19 
BP Key: 28 Status: AVAILABLE Compressed: NO Tag: TAG20190524T182801
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132081_ggjbf23y_.bkp
SPFILE Included: Modification time: 24-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 603567 Ckp time: 24-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29 Incr 1 38.23M DISK 00:00:01 24-MAY-19 
BP Key: 29 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1 CUM
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf3rn_.bkp
List of Datafiles in backup set 29
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 603585 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 1 Incr 603585 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 1 Incr 603585 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 1 Incr 603585 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
30 Full 8.31M DISK 00:00:01 24-MAY-19 
BP Key: 30 Status: AVAILABLE Compressed: NO Tag: TAG20190524T182804
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132084_ggjbf516_.bkp
SPFILE Included: Modification time: 24-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 603592 Ckp time: 24-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31 Incr 1 38.23M DISK 00:00:01 24-MAY-19 
BP Key: 31 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1 CUM
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf6rv_.bkp
List of Datafiles in backup set 31
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 603610 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 1 Incr 603610 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 1 Incr 603610 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 1 Incr 603610 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32 Full 8.31M DISK 00:00:01 24-MAY-19 
BP Key: 32 Status: AVAILABLE Compressed: NO Tag: TAG20190524T182807
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132087_ggjbf81t_.bkp
SPFILE Included: Modification time: 24-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 603617 Ckp time: 24-MAY-19

Recovery Manager complete.

Press Enter to continue

Next we can see the first differential incremental backup backed up 39M of changed blocks, however the second differential incremental backup only backed up 72K as only backed up the blocks changed from the last level 1 backup.  Where as the cumulative incremental on both the first and second backup, backed up 39M of changed blocks:

Files size on disk:
...
...
...

/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24:
total 115M
-rw-r-----. 1 oracle oinstall 39M May 24 18:27 o1_mf_nnnd1_INCR_LEVEL_1_ggjbdsg0_.bkp
-rw-r-----. 1 oracle oinstall 72K May 24 18:28 o1_mf_nnnd1_INCR_LEVEL_1_ggjbdys0_.bkp
-rw-r-----. 1 oracle oinstall 39M May 24 18:28 o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf3rn_.bkp
-rw-r-----. 1 oracle oinstall 39M May 24 18:28 o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf6rv_.bkp

Press Enter to continue

Finally, we update the demo log table:

-------------------------------------
Step 3: Updating and viewing demo log
-------------------------------------
Calling 'sqlplus / as sysdba'
To updated and view demo log

1 row created.

Commit complete.

WHEN COMMENTS
------------------------------ --------------------------------------------------
16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode
16-MAY-19 05.40.23.000000 PM Full Backup
16-MAY-19 05.46.17.000000 PM Full Backup plus Archive Logs
17-MAY-19 04.55.12.000000 PM Image Copy
20-MAY-19 05.13.44.000000 PM Incremental Level 0
22-MAY-19 12.09.17.000000 PM Incremental Level 1
22-MAY-19 12.54.43.000000 PM Incremental Level 1 with BCT
24-MAY-19 06.28.33.000000 PM Incremental Level 1 Cumulative

8 rows selected.

Press Enter to exit shell script

[oracle@dc1sbxdb001 demo]$

Reference

Scripts

  1. 8_incremental_level_1_cumulative.sh
  2. 8_incremental_level_1_cumulative.cmd

To download all 2 in one zip: 8_incremental_level_1_cumulative.zip

The rest of the series

  1. Oracle Database File Placement Best Practice & Create Database Demo
  2. RMAN Full Backup & Demo
  3. RMAN Image Copy & Demo
  4. RMAN Incremental & Demo Part 1 (Level 0)
  5. RMAN Incremental & Demo Part 2 (Level 1)
  6. RMAN Incremental with Block Change Tracking & Demo
  7. RMAN Incremental Differential vs Cumulative & Demo
  8. RMAN Incremental Updating Backup & Demo Part 1
  9. RMAN Incremental Updating Backup & Demo Part 2
  10. Flashback
  11. RMAN Block Media Recovery
  12. RMAN Recover database with only FRA
  13. RMAN Obsolete

Please Note: Links to the blog posts will be released daily and updated here.

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)