How to rename Datafile(s) in Standby Database In Oracle


Scenario:

I was working for customer where they have space issue on DR server. Now the issue is that we are not in position to add new space. So certain directories was already having free spaces around 150GB. 
So as a solution I thought of renaming the  datafile and moving it in new mount which have already have some space.

 Renaming a datafile on standby database is a quite simple process.

Steps:

1) Cancel the managed recovery and bring physical standby database to mount state.

SQL> Alter database recover managed standby database cancel;
SQL> shutdown immediate
SQL> startup mount


2) Change value of standby_file_management parameter to MANUAL. This parameter is set to AUTO by default, which means that standby datafiles will be added automatically as soon as a datafile is added in the primary database.

SQL> alter system set standby_file_management=MANUAL;

3) Move the datafile to new location using OS command.(As I am in 12c I can do the move online)

ALTER DATABASE MOVE DATAFILE '/u01/oradata/DBTEST/demo01.dbf' TO '/u02/oradata/DBTEST/demo01.dbf';


4) Reset the standby_file_management parameter back to AUTO.

SQL> alter system set standby_file_management=AUTO;

5) Start managed recovery once again.

SQL> Alter database recover managed standby database using current logfile disconnect;









If you like please follow and comment