How to move datafile from one mountpoint to another in Standby Database???
Steps to move a datafile in standby database;
MRP Process needs to be shutdown.
alter database managed standby database cancel;
Need to defer log archive destination from primary,Here we had archive destination at location 2.
alter system set log_archive_dest_state_2='DEFER';
Shut Down the Database(Standby)
shut immediate
Change sql_file_management parameter to manual from Automatic;
alter system set sql_file_management='MANUAL';
Fetch the Datafile details.
select name from v$datafile;
Check for the file which is to be moved from above query output.
Move the datafile manually on OS and rename in the database to new location
For Linux--Use mv command
For Windows-- Copy paste can be done and later file can be deleted.
alter system rename datafile '/old path/name' to '/newpath/name';
Check if the files are renamed or not.
select name from v$datafiles;
Change the sql file management parameter
alter system set sql_file_management='AUTO';
Start MRP
alter database managed standby database disconnect from session;
