Recovery Manager: Release 10.2.0.3.0 - Production on Mon Oct 5 20:46:04 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: P477 (DBID=3052489846, not open)
using target database control file instead of recovery catalog
RMAN> backup as copy datafile '+SHARED_DATA_DG01/c477/datafile/tigerdata01.281.699480277' format '+SHARED_FRA_DG01';
Starting backup at 05-OCT-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=620 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=619 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00021 name=+SHARED_DATA_DG01/c477/datafile/tigerdata01.281.699480277
output filename=+SHARED_FRA_DG01/c477/datafile/tigerdata01.256.699482855 tag=TAG20091005T204734 recid=41 stamp=699482959
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55
Finished backup at 05-OCT-09
Starting Control File and SPFILE Autobackup at 05-OCT-09
piece handle=/ora01/orabkup/P477/rman/cf_c-3052489846-20091005-00.bak comment=NONE
Finished Control File and SPFILE Autobackup at 05-OCT-09
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database datafile '+SHARED_DATA_DG01/c477/datafile/tigerdata01.281.699480277' offline drop;
Database altered.
SQL> exit
[cltpbgsqdb05:oracle]/apps/orautil/app/oracle/mak/scripts/admin> rman target / nocatalog
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Oct 5 20:57:39 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: P477 (DBID=3052489846, not open)
using target database control file instead of recovery catalog
RMAN> SWITCH DATAFILE "+SHARED_DATA_DG01/c477/datafile/tigerdata01.281.699480277" to copy;
datafile 21 switched to datafile copy "+SHARED_FRA_DG01/c477/datafile/tigerdata01.256.699482855"
RMAN> exit
Recovery Manager complete.
[cltpbgsqdb05:oracle]/apps/orautil/app/oracle/mak/scripts/admin> s
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Oct 5 20:58:38 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options
SQL> alter database datafile '+SHARED_FRA_DG01/c477/datafile/tigerdata01.256.699482855' online;
Database altered.
SQL> select name from v$datafile where name like '%tigerdata01%' order by creation_time;
NAME
--------------------------------------------------------------------------------
+SHARED_DATA_DG01/c477/datafile/tigerdata01.263.697412327
+SHARED_FRA_DG01/c477/datafile/tigerdata01.256.699482855
SQL>
SQL>
SQL> recover standby database;
ORA-00279: change 48204120380 generated at 10/05/2009 16:43:26 needed for
thread 1
ORA-00289: suggestion : +SHARED_AUXFILES_DG01
ORA-00280: change 48204120380 for thread 1 is in sequence #8107
Specify log: {
auto
ORA-00279: change 48204122646 generated at 10/05/2009 17:31:03 needed for
thread 1
ORA-00289: suggestion : +SHARED_AUXFILES_DG01
ORA-00280: change 48204122646 for thread 1 is in sequence #8108
ORA-00278: log file
'+SHARED_AUXFILES_DG01/c477/archivelog/2009_10_05/thread_1_seq_8107.552.69947108
9' no longer needed for this recovery
SQL> alter system set standby_file_management=auto;
System altered.
SQL>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Connect to ASM Instance
SQL> alter diskgroup SHARED_DATA_DG01 drop file '+SHARED_DATA_DG01/c477/datafile/tigerdata01.281.699480277';
Diskgroup altered.
Posts
I have followed below steps to perform datafile move on physical standby database - 11gR2 version
ReplyDelete1. Set standby file management to manual
show parameter standby
alter system set standby_file_management=manual;
2. Stop media recovery
SQL> alter database recover managed standby database cancel;
3. Take RMAN copy backup for datafile needs to move
RMAN> connect target /
RMAN> run
{
backup as copy datafile '+DATA01/cfap_dc3a/datafile/per_d.327.900976403' format '+SSD02';
}
4. Get the new location from output of above command and do switch datafile copy
RMAN> connect target /
RMAN> run
{
switch datafile '+DATA01/cfap_dc3a/datafile/per_d.280.900965265' to datafilecopy '';
}
5. Verify the datafile location..the destination should be SSD02 with status ONLINE
set lines 400
col name for a80
set pages 600
select name, sum(bytes)/1024/1024/1024 GB from v$datafile where name like '%per_d%' group by name order by name;
6. Start media recovery
SQL> alter database recover managed standby database disconnect;
7. Set standby file management to auto
sps
show parameter standby
alter system set standby_file_management=auto;
8. Drop the old files from DATA01 to reclaim the space
alter diskgroup DATA01 drop file '+DATA01/cfap_dc3a/datafile/per_d.280.900965265';
9. Check DATA01 space if it is reclaimed