2

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 May 12 '16 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 May 13 '16 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 May 18 '16 at 9:23
  • any update on this? – Stelios Jul 28 '16 at 16:13
  • Yes, I'll write an "answer" for better formating. – o0x258 Aug 1 '16 at 9:31
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;
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}")
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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