RE: Lightweight method for testing database backup processes

From: Matthew Parker <>
Date: Mon, 21 Aug 2017 11:25:23 -0700
Message-ID: <00ae01d31aaa$dbcacd60$93606820$>

I have to disagree with you, in most organizations it is not a DR test. A DR test servers a different purpose than ensuring that your backups and processes are good by testing them on a regular basis..  

It is not faith based testing.

There are a variety of testing that can be performed.  

First there are backups that are offsite besides just database backups.

I have been in a variety of organizations that have quarterly SOX audits where we pull back a set of tapes based on a random selections of files by the auditor to verify that backups are statistically good.

There is also some organizations I have worked for where the requirement was to yearly test all backups and it was not a single yearly test it was were testing backups throughout the year to verify the system was working throughout the year, not just at one selected timepoint, but by the end of the year we had recovered at least once all multi-thousand databases. You normally setup automation to perform the onsite based backups, but the selection of offsite backups to prove those processes too normally has some manual intervention.  

Testing of a single tablespace is a viability test of the database if you fully recover it to open the database. This is how lots of organizations that have databases that are 100TB – 1PB size oracle database test the viability of the backup. They don’t necessarily have enough space to restore every portion of the database but can restore pieces at time.

I also restored system, sysaux, undo and 1 tablespace through multiple cycles so that in the end the complete database was restore tested.  

Having all your DBAs testing restores also keeps them practiced on the process and increases the interaction between the DBA and Backup team which is always a good thing. Yes, I have been at organizations where they do not test backups at all, and then when the oncall is pinged to do the restore something is wrong they fumble through SOPs to try and figure what needs to be done and the recovery takes longer than it should or others have to become involved because the DBAs are not practicing their craft.

It also helps your team capture changes in the process as sometime the different teams don’t communicate well with each other and it is better to discover some change that could be detrimental to you during a test instead of when you really need it.  

When I first started out as a DBA the Senior DBA in our org basically setup a test system and put me through 30 days of disaster recovery training. He would destroy the database and it was my job to restore it and explain how he had destroyed/broken it. It was invaluable training        

Matthew Parker

Chief Technologist

Dimensional DBA

425-891-7934 (cell)

D&B 047931344


<> View Matthew Parker's profile on LinkedIn


From: Mladen Gogala [] Sent: Monday, August 21, 2017 9:32 AM
To: Matthew Parker <>;;; Subject: Re: Lightweight method for testing database backup processes  

On 08/21/2017 11:04 AM, Matthew Parker wrote:

Most organizations who have to participate in any type of compliance requirements such as SOX Compliance are required to test their backups.

And most organizations do perform such testing. Such practice is called "DR test" and usually occurs once or twice per year. Testing on daily or weekly schedule is something unusual, even if it's only done using "restore validate". Further more, the OP proposed testing backup/restore of a specific tablespace. I don't see how correctness of the tablespace backup guarantees the correctness of the full or incremental database backup? That looks like a faith based testing strategy. Regards

Mladen Gogala
Oracle DBA
Tel: (347) 321-1217

Received on Mon Aug 21 2017 - 20:25:23 CEST

Original text of this message