Database Software

Dears,

Background:

We are trying to change our backup & restore strategy to use only FORCE LOGGED level 0  & level 1 (purge archive logs at all other times - the window outside of the level 0 & level 1  - which will take all non-logged archives) for DWH system which is hammered with non logged operation at all times. Our operations is aware of this and are OK with data loss (which can be reloaded). Need for such backup is, we want a DB with no non-logged corruption in case of a restore/recovery done.

DB version : 12.1.0.2

PSU level : APR 2017

Platform: Linux x86_64

Issue:

We are trying to certify our procedure to see if oracle is able to use FORCE LOGGED level 0 and level 1 successfully for the restore/recovery operation using a test restore/recovery of the whole database. But we ran into situation where 1 or few datafiles were recovered using archive files (we didnt purge archives yet, since we haven't certified the procedure) compared to incremental level 1.

Below is the command used for recovery (we didnt force 'recover database noredo' to apply incremental level 1 yet).

recover database DELETE ARCHIVELOG MAXSIZE 200G;

Out of 682 dfs, 669 are recovered using incremental level 1 backup. Rest of the 13 had archives to recover them (out of the 13, 6 are new dfs which are created after level 0 backup was kicked off, so we have no concerns for them using archives, we will find a way around for them in our procedure) - but the other 7 had some unique finding, please read the below table - notice the highlighted boxes... (coming from v$backup_datafile) - this is an example data.

Query used: [column heading may be misleading]

select *

from

(

select c.file#

,c.checkpoint_change# current_ckpt#

,c.checkpoint_time current_ckpt_time

,a.ckptscn level_0_ckpt#

,b.incscn level1_ckpt#

from v$datafile_header c

,(select file#,checkpoint_change# ckptscn from v$backup_datafile where incremental_level=0 and completion_time > to_date('27/02/2020 04:05:00')) a

,(select file#,incremental_change# incscn from v$backup_datafile where incremental_level=1 and completion_time > to_date('27/02/2020 04:05:00')) b

where b.file#=a.file#(+)

and c.file#=b.file#

)

where (level_0_ckpt# is NULL OR level_0_ckpt#!=level1_ckpt#)

order by 1;

Example Data (not actual one):

Question : Is it possible to have such a change# jump for a df (same) which stayed across level 0 and level 1?

We observed this jump on all those 7 dfs, the incremental change# of level 1 > checkpoint change# of level 0!!!!

Read: https://docs.oracle.com/database/121/BRADV/rcmcncpt.htm#BRADV89498  ( About the Incremental Backup Algorithm)

Oracle in the doc says the below under Incremental start SCN

The incremental start SCN is most often the checkpoint SCN of the parent of the level 1 backup.

***most often - not 100% of the time!!!!!***

then in the next passage it says

If the backup is cumulative, then the incremental start SCN is the checkpoint SCN of the most recent level 0 backup.

Our case is cumulative for now, since we didnt have any level 1 between our level 0 or level 1. One level 0 and one level 1.

We assume at this stage, oracle is properly picking the archives as its way to move forward since there are chance we may miss few blocks in case it uses the incremental level 1 to recover the db.

So the reason for this discussion thread is

1) Is our understanding valid at this stage on

          a) the way oracle's incremental backup is performed?

          b) why oracle picked the archives over incremental level 1?

2) If our understanding is valid, why oracle had a jump between the lvl0.checkpoint_change# and lvl1.incremental_change#?

We need the above questions clarified to move forward with our desired backup/restore strategy.

Thank you!