How do you test your Oracle database backup?

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?