How do you test your Oracle database backup?

by Michael Dinh on Nov 12, 2018 12: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? 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. The db_unique_name for the restored database will always be restoredr for consistency. Here is the parameter file and basically the only change required is db_name with all others being 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'

Here is script for rman restore database after restore controlfile, etc... 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

Block change tracking file is identical for the restored and DR database.

restore1> select * from v$block_change_tracking;
 
 STATUS FILENAME
 ---------- -------------------------------
 ENABLED +DATA/DBDR/CHANGETRACKING/ctf.3853.987122725
 
 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. 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 VM, I made a leap of faith.

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 distinct regexp_substr(filename,'[[:alpha:]]+',1,2) db_unique_name from V$BLOCK_CHANGE_TRACKING;
 
 DB_UNIQUE_NAME
 ------------------
 
 restore1> select filename, status from V$BLOCK_CHANGE_TRACKING;
 
 FILENAME STATUS
 ------------------------------ ----------
 DISABLED
 
 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> 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> 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.

No Comments Yet

Let us know what you think

See all

Share this

How do you test your Oracle database backup?

Share this

No Comments Yet

Subscribe by email