12c subtle backup view differences



After our recent spate of 12c upgrades i was reviewing a couple of monitoring jobs we had setup and discovered a problem with our cloud control metric extension to check how long it has been since the last level 0 backup.

(stick with me all non cloud control folks as this is still relevant for you too...)

** quick clarification point - level 0 and full backups are essentially the same - however they are recorded as different things completely in the controlfile/rman catalog and a full backup cannot have a incremental level 1 backup taken after it - therefore all our backups are defined as incremental level 0 rather than full **

The simple check i run to see if there has been a level 0 backup involves running this SQL

  select nvl(min  (sysdate-bjd.start_time),31)
  from v$rman_backup_job_details bjd ,v$backup_set_details bsd
  where bsd.session_recid=bjd.session_recid
  and bsd.session_key=bjd.session_key
  and bsd.SESSION_STAMP=bjd.SESSION_STAMP
  and bsd.BACKUP_TYPE||bsd.INCREMENTAL_LEVEL='D0'
  and bjd.status='COMPLETED'

Essentially it tries to identify the most recent incremental level 0 backup that finished successfully, if it doesn't find any at all return 31, an alert then kicks in if this value is >8.

That's been working fine for 10g and 11g (and i think it works with 9i too but we can't connect to our 9i db's from cloud control as the hosting os is too old for the 12c agent).

However in 12c this always returns 31 (i.e. null) - but why.....?

Well after a bit of investigation it seems Oracle made a subtle change to the data presented in V$BACKUP_SET_DETAILS - now for Incremental backups the backup type is explicitly recorded as 'I' rather than 'D' - which makes a lot of sense (and would have helped me when i was writing the original query) however it means that my query doesn't work.

To fix it i just need to change 1 letter (a D to an I - in order to get this SQL)

  select nvl(min  (sysdate-bjd.start_time),31)
  from v$rman_backup_job_details bjd ,v$backup_set_details bsd
  where bsd.session_recid=bjd.session_recid
  and bsd.session_key=bjd.session_key
  and bsd.SESSION_STAMP=bjd.SESSION_STAMP
  and bsd.BACKUP_TYPE||bsd.INCREMENTAL_LEVEL='I0'
  and bjd.status='COMPLETED'

So that fixes it and gives me the result i want - however from cloud control it means i have 2 different queries - this i don't want - so how do i deal with that?

Well thanks to some clever plsql i can do this

begin
OPEN :1
$IF DBMS_DB_VERSION.VER_LE_11 $THEN 
  for   select nvl(min  (sysdate-bjd.start_time),31)
  from v$rman_backup_job_details bjd ,v$backup_set_details bsd
  where bsd.session_recid=bjd.session_recid
  and bsd.session_key=bjd.session_key
  and bsd.SESSION_STAMP=bjd.SESSION_STAMP
  and bsd.BACKUP_TYPE||bsd.INCREMENTAL_LEVEL='D0'
  and bjd.status='COMPLETED';
$ELSE
  for   select nvl(min  (sysdate-bjd.start_time),31)
  from v$rman_backup_job_details bjd ,v$backup_set_details bsd
  where bsd.session_recid=bjd.session_recid
  and bsd.session_key=bjd.session_key
  and bsd.SESSION_STAMP=bjd.SESSION_STAMP
  and bsd.BACKUP_TYPE||bsd.INCREMENTAL_LEVEL='I0'
  and bjd.status='COMPLETED';
$END
end;

So if the version is less than or equal to 11 run the top query, if it's anything else (i.e. 12c and above run the bottom query) - neat huh? (well i thought so anyway... :-))

For reference the original cloud control walkthrough is here http://dbaharrison.blogspot.de/2013/07/12c-metric-extension-for-cloud-control.html - just change the sql to the plsql shown above and away you go.



2 comments:

  1. I have a problem with the new statement for Oracle below 11G + 12 C, I get the following error:

    Failed to get test Metric Extension metric result.: Missing IN or OUT parameter at index:: 1
    Can you help me?

    ReplyDelete
  2. Why can't we have a simple "OR" condition
    and (bsd.BACKUP_TYPE||bsd.INCREMENTAL_LEVEL='I0' OR bsd.BACKUP_TYPE||bsd.INCREMENTAL_LEVEL='D0')

    ReplyDelete