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