Rename Datafile In Oracle Standby on New Location

Recently, I encountered a situation while working for a customer where there was a space constraint on the DR server. Unfortunately, we were unable to allocate additional space. However, some directories had approximately 90GB of available free space. To resolve this issue, I came up with a solution to rename the data file and move it to a new mount that had sufficient space. The process of renaming a data file on a standby database is relatively straightforward.

Step 1: Terminate the managed recovery process and transition the physical standby database to the mount state.

SQL> Alter database recover managed standby database cancel;

SQL> shutdown immediate

SQL> startup mount

Step 2: Now, Modify the value of the standby_file_management parameter to MANUAL. By default, this parameter is set to AUTO, which results in the automatic addition of standby datafiles whenever a datafile is added in the primary database.

SQL> alter system set standby_file_management=MANUAL;

Step 3: Relocate the datafile to a new location using the appropriate OS command. Since I am operating in the 19c environment, I have the capability to perform the move online, ensuring uninterrupted accessibility.

ALTER DATABASE MOVE DATAFILE '/u01/oracle/db/UATDB/APEX002.dbf' TO '/u20/oracle/db/UATDB/APEX002.dbf';

Step 4: Restore the standby_file_management parameter back to its default setting of AUTO.

SQL> alter system set standby_file_management=AUTO;

Step 5: Resume the managed recovery process that was halted in Step 1.

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