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? 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.987122725Disable 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 > exitI still don't understand the error and it's not within the scope; hence, did not bother to investigate further.
