---
title: How do you test your Oracle database backup?
description: This blog post examines some suggestions for testing your Oracle Database Backup.
---

[Blog | Pythian ](https://www.pythian.com/blog)

# [How do you test your Oracle database backup?](https://www.pythian.com/blog/test-oracle-database-backup)

 Written by [Michael Dinh](https://www.pythian.com/blog/author/michael-dinh) | Nov 12, 2018 5:00:00 AM

From my experience, the one area where a DBA cannot afford to make errors is in database backup. When was the last time database backup was tested? Was it successful? How often is backup tested? Is testing automated?

## The Critical Importance of Testing Database Backups

I was asked to validate backups for a few standby databases by restoring the database backup to the same host.

- **Given:** There are multiple RAC standby database instances running on the same host.
- **Objective:** Test RMAN backup of RAC standby databases by restoring the latest backup to the same host at node1.
- **Consistency:** The `db_unique_name` for the restored database will always be **restoredr** for consistency.

### Configuration and Parameter Setup

Here is the parameter file; basically the only change required is `db_name`, with all others remaining consistent.

```
$ cat initrestore1.ora  *.cluster_database=false  *.compatible='12.1.0.2.0'  *.db_block_size=8192  *.db_create_file_dest='+DATA'  *.db_files=5000  *.db_name='DB'  *.db_recovery_file_dest_size=4398046511104  *.db_recovery_file_dest='+RECO'  *.db_unique_name='restoredr'  *.diagnostic_dest='/u01/app/oracle'  *.pga_aggregate_target=3439329280  *.processes=1000  *.sga_max_size=5150605312  *.sga_target=5150605312  restore1.instance_number=1  restore2.instance_number=2  restore1.thread=1  restore2.thread=2  restore1.undo_tablespace='UNDOTBS1'  restore2.undo_tablespace='UNDOTBS2' 
```

## The RMAN Restoration Script

Here is the script for the RMAN restore database after restoring the controlfile. It was implemented as such since the process from here is consistent.

```
$ cat restore1.rman  # CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/zfs/backup02/db_backups/%d/RMAN/%F';  # CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/zfs/backup02/db_backups/%d/RMAN/%d_%I_%s_%p.%T.bak';  #  # Note: %d is db_name from parameter file and not db_unique_name  #  # Usage: nohup rman @ restore1.rman %d > /tmp/restore1.out 2>&1 &  # spool log to /tmp/restore1.log   connect target;  sql 'alter database mount STANDBY database';  sql 'alter database disable block change tracking';   run {  allocate channel c1 type disk;  allocate channel c2 type disk;  allocate channel c3 type disk;  allocate channel c4 type disk;  allocate channel c5 type disk;  allocate channel c6 type disk;  allocate channel c7 type disk;  allocate channel c8 type disk;  allocate channel c9 type disk;  catalog start with '/zfs/backup02/db_backups/&1/RMAN/' noprompt;  set newname for database to new;  restore database;  switch datafile all;  recover database NOREDO;  release channel c2;  release channel c3;  release channel c4;  release channel c5;  release channel c6;  release channel c7;  release channel c8;  release channel c9;  sql 'alter database open READ ONLY';  }  exit 
```

## Addressing the "Scary Part": File Conflicts and Block Change Tracking

The Block change tracking (BCT) file was found to be identical for both the restored and DR database.

**restore1 check:**

```
restore1> select * from v$block_change_tracking; STATUS FILENAME ---------- ------------------------------- ENABLED +DATA/DBDR/CHANGETRACKING/ctf.3853.987122725 
```

**dbdr check:**

```
dbdr> select * from v$block_change_tracking; STATUS FILENAME ---------- ------------------------------- ENABLED +DATA/DBDR/CHANGETRACKING/ctf.3853.987122725 
```

Disable block change tracking to prevent DR instance crash due to bad file error.

### Resolving Inconsistent File Locations

Here is the scary part: **DATA** and **TEMP** files reside in a new location at `+DATA/RESTOREDR`; however, **LOG** files reside at the original location `+DATA/DBDR`. I was very hesitant to drop the restored database. Upon further testing using a VM, I made a leap of faith.

**Verification of file locations:**

```
restore1> @logfile.sql restore1> show parameter db%name  NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string DB db_unique_name string restoredr pdb_file_name_convert string  restore1> col db_unique_name for a18 restore1> col filename for a30 trunc restore1> select open_mode from v$database;  OPEN_MODE -------------------- READ ONLY  restore1> select distinct regexp_substr(name,'[[:alpha:]]+',1,2) db_unique_name from V$DATAFILE; DB_UNIQUE_NAME ------------------ RESTOREDR  restore1> select distinct regexp_substr(name,'[[:alpha:]]+',1,2) db_unique_name from V$TEMPFILE; DB_UNIQUE_NAME ------------------ RESTOREDR  restore1> select distinct regexp_substr(member,'[[:alpha:]]+',1,2) db_unique_name from V$LOGFILE; DB_UNIQUE_NAME ------------------ DBDR  restore1> select filename, status from V$BLOCK_CHANGE_TRACKING; FILENAME STATUS ------------------------------ ---------- DISABLED 
```

## Final Cleanup: Dropping the Restored Database

Once validation was complete, the final step was to drop the test environment.

```
restore1> @drop_restore1.sql restore1> set lines 300 timing off pages 10000 trimsp on tab off echo on restore1> startup force mount restrict exclusive; ORACLE instance started.  Total System Global Area 4294967296 bytes Fixed Size 2932632 bytes Variable Size 3046682728 bytes Database Buffers 1191182336 bytes Redo Buffers 54169600 bytes Database mounted.  restore1> drop database; ERROR: ORA-01034: ORACLE not available Process ID: 278789 Session ID: 2792 Serial number: 319  Database dropped.  Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options > exit 
```

I still don't understand the error and it's not within the scope; hence, did not bother to investigate further.

## Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?

 

[View full post](https://www.pythian.com/blog/test-oracle-database-backup)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Michael Dinh"
  },
  "dateModified" : "2026-03-24T06:34:16.113Z",
  "datePublished" : "2018-11-12T05:00:00Z",
  "headline" : "How do you test your Oracle database backup?",
  "image" : {
    "@type" : "ImageObject",
    "height" : 60,
    "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
    "width" : 60
  },
  "mainEntityOfPage" : "https://www.pythian.com/blog/test-oracle-database-backup",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60,
      "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
      "width" : 60
    },
    "name" : "Pythian Blog"
  }
}
```