How to move a Standby Datafile from One Disk Group to another Disk Group

Standby Datafile movement from One Disk Group to

another Disk Group

On Primary(CAALP) -

Stop redo Transport on Primary

DGMGRL> edit database 'CAALP' SET STATE=TRANSPORT-OFF;

Observation on SQL Layer

log_archive_dest_state_2 has become RESET

Note: RESET functions the same as DEFER, but clears any error messages for the destination if it
had previously failed.

On standby (CAALPS) -

Switch file management to manual

Observation before changing

Observation after changing

On standby (CAALPS) -

Open database in read only mode

On standby (CAALPS) -

Identify files that need to move as per CR

set lines 200 col DataFile for a60 select t.name "TBS" , d.name "DataFile", d.FILE#, d.STATUS from v$datafile d, v$tablespace t where d.ts#=t.ts# and t.name like 'DATA%' and d.name like '+DATA%' and t.name in ('DATA_2017_TS3','DATA_2018_TS1','DATA_2018_TS2','DATA_2019_TS1','DATA_2019_TS2');

I see some 5 data files which I need to move.

On standby (CAALPS) -

Make backup copy of datafile(s)

rman target /

RMAN> backup as copy datafile '+DATA/caalps/datafile/data_2018_ts1.1045.974819759' format '+CFARCHDATA2';

RMAN> backup as copy datafile '+DATA/caalps/datafile/data_2018_ts2.1046.974819761' format '+CFARCHDATA2';

RMAN> backup as copy datafile '+DATA/caalps/datafile/data_2017_ts3.1042.974609657' format '+CFARCHDATA2';

RMAN> backup as copy datafile '+DATA/caalps/datafile/data_2019_ts1.1043.974611459' format '+CFARCHDATA2';

RMAN> backup as copy datafile '+DATA/caalps/datafile/data_2019_ts2.1044.974611459' format '+CFARCHDATA2';

Screen shot of one Datafile copy.  We can do it for remaining data files in the same way.

On standby(CAALPS) -

Switch to copies of datafiles by putting database in mount state.

DGMGRL> SHUTDOWN IMMEDIATE - We need to shut down all instances using DGMGRL

DGMGRL> STARTUP NOMOUNT - on first node of standby                                                  

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Confirm show configuration 'SUCCESS'

On standby (CAALPS) -

Delete old datafiles

RMAN> DELETE DATAFILECOPY "+DATA/caalps/datafile/data_2018_ts1.1045.974819759";

RMAN> DELETE DATAFILECOPY "+DATA/caalps/datafile/data_2018_ts2.1046.974819761";

RMAN> DELETE DATAFILECOPY "+DATA/caalps/datafile/data_2017_ts3.1042.974609657";

RMAN> DELETE DATAFILECOPY "+DATA/caalps/datafile/data_2019_ts1.1043.974611459";

RMAN> DELETE DATAFILECOPY "+DATA/caalps/datafile/data_2019_ts2.1044.974611459";

Screenshot of deleting one datafile

Revert the Changes that you made on Primary and Standby

Standby - Convert the DB to read only mode

Standby - Start MRP

Standby - Revert File Management to AUTO again.

Primary - Enable Redo shipping