How Do You Test Your Oracle Database Backup?

Posted in: Technical Track

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.

email

Interested in working with Michael? Schedule a tech call.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *