Tuesday, March 26, 2013

Moving Datafile from Physical Standby to Primary via Rman

Rman is an interesting tool and it seems everyday you learn something new. As part of a production issue we lost a diskgroup . This Diskgroup was only a small subset of the actual data but due to an issue in the underlying disk (External Redundancy). The database crashed. As we were debugging the issue with our vendor . We decided to use Rman and our Physical Standby to bring back the datafiles to help mitigate the outage. Now we could have failed over to our standby but since  our Standby hardware was not sized to handle production workload that was a risk the business was not willing to take.
As Oracle 11.1 you can use file from a standby(physical) database and move them to the primary.
We went thru to the Standby and determined which datafiles were in the particular group and then connected to primary from the standby as an auxiliary and copied the files over the network to the different diskgroup.

connect sys@standby AUXILIARY sys@primary;
BACKUP AS COPY DATAFILE 2 AUXILIARY FORMAT '+DATA/COPY_FILE/COPY_FILE.dbf;
..
..
Once the Files are copied you can switch the datafiles .
CATALOG DATAFILE COPY '+DATA/COPY_FILE/COPY_FILE.dbf';

RUN {
  SET NEWNAME FOR DATAFILE 2 TO ''+DATA/COPY_FILE/COPY_FILE.dbf';
  SWITCH DATAFILE 2;
}
This will allow for a move of the datafile as well as rename on the primary.
you will then  have do a crash recovery on the datafile(s) in question.

recover database 2;
alter database datafile 2 online;
you have just copied a file over the network and plugged it into your primary from standby. This is a pretty convenient if accidents happens.

Google Search