Dataguard · Oracle

Copy Datafile from Physical Standby to Primary – ASM

You can also copy a datafile using asm command like “cp” command. But there are some side-effects as far as I see.

1 – When you copy a datafile to asm. It is somehow violates the OMF structure.
2 – For the copied datafiles,you have to live with asm symbolic links.
3 – During the copy process, you have to stop the apply process on the dataguard side.
4 – ASM should be registered on the listener
5 – ASM should have a password file set for remote copy

on primary preparation for ASM

[grid@primaryhost ]$ sqlplus / as sysasm
SQL> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
SQL> grant sysasm to sys;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  TRUE

[grid@primaryhost dbs]$  orapwd file=orapw+ASM password=PASSW11RD

on standby stop the apply process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

on standby copy the datafile to primary 

[oracle@standbyhost ~]$ asmcmd
ASMCMD> cp --port 1536 +DATA/stnbtest/datafile/users.293.839761205  sys@192.168.1.10.+ASM1:+DATA/primtest/datafile/users1
Enter password: ********
sh: -c: line 0: unexpected EOF while looking for matching `''
sh: -c: line 1: syntax error: unexpected end of file
sh: -c: line 0: unexpected EOF while looking for matching `''
sh: -c: line 1: syntax error: unexpected end of file
copying +DATA/stnbtest/datafile/users.293.839761205 -> 192.168.1.10:+DATA/primtest/datafile/users1
ASMCMD>exit

on standby resume the apply process

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using current logfile DISCONNECT PARALLEL 16;

on primary switch the datafile on the datafile copy from the dataguard

ASMCMD> ls -l 
Type      Redund  Striped  Time             Sys  Name
DATAFILE  MIRROR  COARSE   FEB 17 12:00:00  Y    SYSAUX.304.839760443
DATAFILE  MIRROR  COARSE   FEB 17 12:00:00  Y    SYSTEM.305.839760429
DATAFILE  MIRROR  COARSE   FEB 17 12:00:00  Y    UNDOTBS1.303.839760457
DATAFILE  MIRROR  COARSE   FEB 24 09:00:00  Y    USERS.952.840360665
                                            N    users1 => +DATA/asm/datafile/users1.301.840365997

[oracle@primaryhost ~]$ rman 
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 24 11:08:24 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target sys@vtytest
target database Password: 
connected to target database: VTYTEST (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/asm/datafile/users1.301.840365997';
cataloged datafile copy
datafile copy file name=+DATA/asm/datafile/users1.301.840365997 RECID=5 STAMP=840366582

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

RMAN>  run {
2> set newname for datafile 4 to '+DATA/ASM/DATAFILE/users1.301.840365997';
3> switch datafile 4;
4> }
executing command: SET NEWNAME
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=840366582 file name=+DATA/asm/datafile/users1.301.840365997

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 VTYTEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    16384    SYSTEM               ***     +DATA/vtytest/datafile/system.305.839760429
2    16384    SYSAUX               ***     +DATA/vtytest/datafile/sysaux.304.839760443
3    16384    UNDOTBS1             ***     +DATA/vtytest/datafile/undotbs1.303.839760457
4    1024     USERS                ***     +DATA/asm/datafile/users1.301.840365997
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    32768    TEMP                 524288      +DATA/vtytest/tempfile/temp.302.839760921
Advertisements

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