Home / Errors/Workarounds19c / Rename Datafile In Oracle Standby on New Location

Rename Datafile In Oracle Standby on New Location

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 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/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;
  • 5

About Syed Saad Ali

With 13 years of experience as a certified and skilled Oracle Database Administrator, I possess the expertise to handle various levels of database maintenance tasks and proficiently perform Oracle updates. Throughout my career, I have honed my analytical abilities, enabling me to swiftly diagnose and resolve issues as they arise. I excel in planning and executing special projects within time-sensitive environments, showcasing exceptional organizational and time management skills. My extensive knowledge encompasses directing, coordinating, and exercising authoritative control over all aspects of planning, organization, and successful project completions. Additionally, I have a strong aptitude for resolving customer relations matters by prioritizing understanding and effective communication. I am adept at interacting with customers, vendors, and management, ensuring seamless communication and fostering positive relationships.

Check Also


Redo Transport Services fails with ORA-16198

Redo Transport Services fails with ORA-16198 At the alert log, we received this error message on …

Leave a Reply