Syed Saad Ali

Oracle ACE Pro

Oracle Solution Architect

Oracle E-Business Suite

Oracle Cloud Infrastructure

Oracle Fusion Middleware

Oracle Database Administration

Oracle Weblogic Administration

Syed Saad Ali

Oracle ACE Pro

Oracle Solution Architect

Oracle E-Business Suite

Oracle Cloud Infrastructure

Oracle Fusion Middleware

Oracle Database Administration

Oracle Weblogic Administration

Blog Post

Rename Datafile In Oracle Standby on New Location

Rename Datafile In Oracle Standby on New Location

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;
 
 
 
 
Related Posts
Write a comment