Dataguard · RAC · RMAN

Copy Datafile from Physical Standby to Primary – RMAN

If you have a corrupt datafile on production system you can perform the recovery using the dataguard sysem. You can backup the datafile from dataguard system to the primary system using rman. It is like copying the datafile over the network and registering that copy on the primary database and switching the corrupt datafile with that copy. To bring that datafile online you run the standart datafile recovery senario.

P.S.  This method can be also used to replace a datafile on the dataguard with a production copy.

List the current schema for the primary database.

[oracle@primaryhost ~]$ rman 

RMAN> connect target sys@primarytest
target database Password: 
connected to target database: PRIMTEST (DBID=2360494746)

RMAN> report schema;
Report of database schema for database with db_unique_name PRIMTEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    16384    SYSTEM               ***     +DATA/primtest/datafile/system.305.839760429
2    16384    SYSAUX               ***     +DATA/primtest/datafile/sysaux.304.839760443
3    16384    UNDOTBS1             ***     +DATA/primtest/datafile/undotbs1.303.839760457
4    1024     USERS                ***     +DATA/primtest/datafile/users.301.839760477
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    32768    TEMP                 524288      +DATA/primtest/tempfile/temp.302.839760921

Copy the datafile from the dataguard to primary instance

[oracle@primaryhost ~]$ rman 

RMAN> connect target sys@standbytest
target database Password: 
connected to target database: PRIMTEST (DBID=2360494746, not open)

RMAN> connect auxiliary sys@primarytest
auxiliary database Password: 
connected to auxiliary database: PRIMTEST (DBID=2360494746)

RMAN> backup as copy datafile 4 auxiliary format '+DATA';

Starting backup at 24-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=993 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/stnbtest/datafile/users.293.839761205
output file name=+DATA/primtest/datafile/users.952.840360665 tag=TAG20140224T085510
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 24-FEB-14

On the primary database, switch the datafile that is copied from th standby with the original datafile.

[oracle@primaryhost ~]$ rman 

RMAN> connect target sys@primarytest

target database Password: 
connected to target database: PRIMTEST (DBID=2360494746)

RMAN> list copy of datafile 4;
using target database control file instead of recovery catalog
specification does not match any datafile copy in the repository

RMAN> catalog datafilecopy '+DATA/primtest/datafile/users.952.840360665';

cataloged datafile copy
datafile copy file name=+DATA/primtest/datafile/users.952.840360665 RECID=3 STAMP=840361417

RMAN>  sql 'alter database datafile 4 offline';
sql statement: alter database datafile 4 offline

RMAN> run {
2>  set newname for datafile 4 to '+DATA/primtest/datafile/users.952.840360665';
3>  switch datafile 4;
4> }
executing command: SET NEWNAME
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=840361417 file name=+DATA/primtest/datafile/users.952.840360665

RMAN> recover datafile 4;
Starting recover at 24-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=303 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-FEB-14

RMAN> SQL 'alter database datafile 4 online ';
sql statement: alter database datafile 4 online 

RMAN> report schema ;
Report of database schema for database with db_unique_name PRIMTEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    16384    SYSTEM               ***     +DATA/primtest/datafile/system.305.839760429
2    16384    SYSAUX               ***     +DATA/primtest/datafile/sysaux.304.839760443
3    16384    UNDOTBS1             ***     +DATA/primtest/datafile/undotbs1.303.839760457
4    1024     USERS                ***     +DATA/primtest/datafile/users.952.840360665

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    32768    TEMP                 524288      +DATA/primtest/tempfile/temp.302.839760921
Advertisements

One thought on “Copy Datafile from Physical Standby to Primary – RMAN

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s