Fun with backup

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Mon, 24 Sep 2012 14:32:44 +0000 (UTC)
Message-ID: <pan.2012.09.24.14.32.04_at_gmail.com>

I had some fun with backup over the weekend. The setup is like this:

1. There is the primary database and an active guard standby database.
2. There is RMAN catalog
3. Backups are done on the standby database, not on the primary.

This was a DR test. The question was how to restore the primary database. Well, the DBID and the database name are the same for both the primary database and the standby. One thing that is not the same is DB_UNIQUE_NAME. Essentially, when restoring the primary database off standby backup copies, one needs to change DB_UNIQUE_NAME. Unfortunately, that requires a restart.

RMAN> list backup of database;

specification does not match any backup in the repository

RMAN> list backup of database for db_unique_name='STBY';

List of Backup Set for database with db_unique_name STBY

---

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------
431     Full    1.27G      DISK        00:01:06     2012-09-23 16:01:14
        BP Key: 435   Status: AVAILABLE  Compressed: NO  Tag: 
TAG20120923T160006
        Piece Name: /data/oracle/backup/fulldb1enlu9g8_1_1.bak
  List of Datafiles in backup set 431
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 3076374    2012-09-23 15:49:58 /data/oracle/data/O11/
system01.dbf
  2       Full 3076374    2012-09-23 15:49:58 /data/oracle/data/O11/
sysaux01.dbf
  3       Full 3076374    2012-09-23 15:49:58 /data/oracle/data/O11/
undotbs01.dbf
  4       Full 3076374    2012-09-23 15:49:58 /data/oracle/data/O11/
users01.dbf
  5       Full 3076374    2012-09-23 15:49:58 /data/oracle/data/O11/

example01.dbf

RMAN> Unfortunately, it is not possible to do a restore using this trick:

RMAN> restore database validate for db_unique_name='STBY';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found "for": expecting one of: "channel, check, device, force, from, header, high, preview, skip readonly, to restore point, until restore point, until, validate, ;" RMAN-01007: at line 1 column 27 file: standard input

Changing DB_UNIQUE_NAME dynamically is not possible:

SQL> alter system set db_unique_name='STBY' scope=memory; alter system set db_unique_name='STBY' scope=memory

                 *

ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

That, in particular, means that there is no recovery without a restart if backup is taken off the standby database. I must say I hoped that this would be a bit more flexible and allow me to set the DB_UNIQUE_NAME and do the restore. What I can do is to backup a datafile as a copy on standby, copy the file to the primary and catalog it there using RMAN. There is a solution, but it's far from automated and requires some work.

-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

Received on Mon Sep 24 2012 - 09:32:44 CDT