Oracle Solutions We believe in delivering tangible results for our customers in a cost-effective manner
Rename Datafile In Oracle Standby on New Location
“Suppose you come across a situation while serving a customer, where the DR server faces space limitations. In order to address this concern, I devised a solution to rename the data file and transfer it to a new mount with ample space. The process of renaming a data file on a standby database is quite simple and 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 immediateSQL> startup mountStep 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/PROD/USER10.dbf' TO '/u02/oracle/PROD/USER10.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;
