RE: Lightweight method for testing database backup processes

From: Noveljic Nenad <>
Date: Mon, 21 Aug 2017 14:31:13 +0000
Message-ID: <13936_1503325883_599AEEBB_13936_2586_1_ECDEF0CC6716EC4596FCBC871F48292AB18CC7BE_at_ZRH-S231>

I’ve implemented a restore end-to-end test solution similar to your specification, however with the following differences:

  1. I don’t pick databases to restore randomly. First, I take new databases which have never been restored. Second, I prioritize the databases by the last restore time.
  2. Same as you’ve suggested.
  3. No need to convert the time to SCN. I just specify the time obtained by the step 2.
  4. I restore the whole database, instead of individual tables. Luckily, it is feasible on my site.
  5. The alerting is integrated in the enterprise monitoring solution (Nagios) instead of sending e-mails around.

Besides that, it has proven to be useful to load the rman restore log files into a database, so they can easily be made accessible per self-service to anyone (auditors, internal revision, management etc.) who wants to have the evidence of a successful restore.

The solution is implemented in object-oriented Perl enriched with a bunch of useful CPAN modules.

I sleep much better knowing that my databases have been successfully restored every once in a while.



Twitter: _at_NenadNoveljic
Home Page:

From: [] On Behalf Of Chris Stephens Sent: Montag, 21. August 2017 16:05
To:; Subject: Re: Lightweight method for testing database backup processes

i implicitly test my car every morning to get to work. i guess it would no longer be a daily test if i lost my job because my database wasn't recoverable. i get your point though.

thanks for the link Kellyn!
On Mon, Aug 21, 2017 at 8:55 AM Mladen Gogala <<>> wrote:

Why would you want to rest rman backups? Do you have any doubts of their quality? You can do "restore validate" is you suspect a problem. I understand that you should be cautious, but rman has proven itself many times over, there is no need to test it whether it will work. Do you test your car every morning?

On 08/21/2017 09:39 AM, Chris Stephens wrote: We are looking for an efficient way to regularly test RMAN backups across a large (and growing) Exadata database environment.

After watching this video i thought about doing the following:

create a dedicated, small tablespace in all databases to hold a single table with a single date/timestamp column. create a scheduler job to insert current sysdate/systimestamp value once per day and delete all rows older than recovery window setting for RMAN.

write a script to 1) randomly pick a database on each Exadata system 2) randomly pick a day that falls within the recovery window requirement for that database 3) converts that day to a valid SCN 4) uses the new table PITR functionality to restore the table 4) confirm expected table content 5) sends success/failure summary email.

execute the script with a frequency that makes us feel comfortable with our backups.

we also intend to have a process that utilizes the "restore preview" RMAN command to get a list of backup pieces to run the RMAN "validate" command against for a randomly chosen SCN that falls within recovery window.

Does anyone see any big issues with this process? Any other ideas for efficiently testing database backups? our databases will soon be large enough to make testing through full restores infeasible.

any feedback is greatly appreciated!



Mladen Gogala

Oracle DBA

Tel: (347) 321-1217<tel:(347)%20321-1217>

Please consider the environment before printing this e-mail. Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.

<html xmlns="">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">p { font-family: Arial;font-size:9pt }</style>
<br>Important Notice</br>
<br>This message is intended only for the individual named. It may contain confidential or privileged information. If you are not the named addressee you should in particular not disseminate, distribute, modify or copy this e-mail. Please notify the sender immediately by e-mail, if you have received this message by mistake and delete it from your system.</br>
<br>E-mail transmission may not be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also processing of incoming e-mails cannot be guaranteed. All liability of the Vontobel Group and its affiliates for any damages resulting from e-mail use is excluded. You are advised that urgent and time sensitive messages should not be sent by e-mail and if verification is required please request a printed version.<br/>

Received on Mon Aug 21 2017 - 16:31:13 CEST

Original text of this message