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.
.svg){kind=small}
.svg){kind=small}