Oracle RMAN Monitoring – Report Need Backup

oraclerman

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.

Best Answer

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;
exit
EOF
)

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

# 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
do
  # 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
  XMLDOC="${XMLDOC}<ROW><DBID>${DBID}</DBID><INST_ID>${INST_ID}</INST_ID><FILE_ID>${VAL[0]}</FILE_ID><BACKUP_CNT>${VAL[1]}</BACKUP_CNT><FILE_NAME>${VAL[2]}</FILE_NAME></ROW>"
done < <(echo -e "${DATAFILES}")
Related Question