Hello Oracle Database Admins!

In the last few months I came across that it would be a good idea to monitor rman backups and check if any file needs backup.

RMAN> report need backup;
--> Report ERROR if any records show up.

Aditionally I would like to monitor the following command:

RMAN> report unrecoverable;
--> Report ERROR if any records show up.

I do not want to parse the output of RMAN. So here is my question: Does anyone know a method to monitor these two commands using SQL and/or PL/SQL?

I know that RMAN just uses SQL and PL/SQL to get the result but an sqltrace just lead me into non-documented pl/sql funtions with encrypted code.

  • Does anyone monitor this at all?
    – o0x258
    Commented May 12, 2016 at 13:15
  • 1
    So, did you try calling that undocumented plsql code? The code might be encrypted but the output most likely wouldn't be.
    – Raj
    Commented May 13, 2016 at 14:14
  • I was hoping that anyone out threre already did that and could give me a hand. When I will do it, I'll post the outcome here. But right now I have little time. The sql trace was already helpful. Maybe I have to do the trace again with 11g. 12c seems to be much more complicated because of the cdb feature.
    – o0x258
    Commented May 18, 2016 at 9:23
  • any update on this?
    – Stelios
    Commented Jul 28, 2016 at 16:13
  • Yes, I'll write an "answer" for better formating.
    – o0x258
    Commented Aug 1, 2016 at 9:31

1 Answer 1


A) Oracle replied to my SR that it is not supported run the SQLs manually. This is not entirely correct but I expected something like that.

B) The second command report unrecoverable can be queried with SQL. The result is even more accurate than from RMAN.

select file#, unrecoverable_change#, unrecoverable_time from v$datafile;

RMAN's report unrecoveryble will only report files which contain content which are unrecoveryble after the latest backup. This SQL reports unrecoverable things in general. Normally we do not try to make the database only recoverable after the latest backup.

C) For both commands I wrote a "screen reader". Here are some code snippets:

## Execute RMAN command report need backup
RMANOUT=$(rman target / << EOF 2>&1
report need backup;

## Execute RMAN command report unrecoverable
RMANOUT=$(rman target / << EOF 2>&1
report unrecoverable;

# Filter file list
DATAFILES=$(echo -e "${RMANOUT}" | sed '1,/---/d' | sed '/Recovery Manager complete./d' | sed 's/RMAN>//g' | sed '/^[[:space:]]/d')

## Parse all files
while IFS=$'\n' read -a ROW
  # Convert lines into array (separator " ")
  IFS=' ' read -a VAL <<< ${ROW[0]}

  # No files reported
  if [ "${VAL}" == "" ]; then break; fi

  # Do whatever you like! In my case generate XMLdoc
done < <(echo -e "${DATAFILES}")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.