Renaming A Datafile In A Physical Standby Environment

Stop EBS application services

1. Set standby_file_management to manual on primary as well as both standby database.

On Primary
================
SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO


SQL> alter system set standby_file_management=MANUAL scope=both;
SQL> show parameter standby_file_management

On standby
==========
show parameter standby_file_management
alter system set standby_file_management=MANUAL scope=both;
show parameter standby_file_management

2. Take the tablespace offline on the primary database

alter tablespace USERS offline;

3. Copy files to /oradataB location

cp -i /oradataA/users01.dbf /oradataB/

4. Rename the datafile in the primary database

alter tablespace USERS rename datafile '/oradataA/users01.dbf' to '/oradataB/users01.dbf';

5. Bring the Tablespace back online in the primary database

alter tablespace USERS online;

6.  On standby
================
a) stop redo apply on standby database.

recover managed standby database cancel;

b) Modify db_file_name_convert and  log_file_name_convert parameter of Standby init file to include /oradataB mount point .

Modify Value:
alter system set db_file_name_convert='/oradataA','/oradataB' scope=spfile;
alter system set log_file_name_convert='/oradataA','/oradataB' scope=spfile;

c) Shutdown the standby database.

 shutdown immediate

d) Move the datafile to the new location at the first standby database.

cp -i /oradataA/users01.dbf /oradataB/

e) start and mount the first standby database.

startup mount

f) Rename the Datafile in the standby database.

alter database rename file '/oradataA/users01.dbf' to '/oradataB/users01.dbf';

g) Start redo apply standby database.

recover managed standby database disconnect from session;

7. Set standby_file_management as auto on both primary and both standby database.

alter system set standby_file_management=auto scope=both;

On Primary
================
show parameter standby_file_management
alter system set standby_file_management=AUTO scope=both;
show parameter standby_file_management

On Standby
================
show parameter standby_file_management
alter system set standby_file_management=AUTO scope=both;
show parameter standby_file_management

8. If everything working fine, remove the old datafile from Primary and Standby

Verify mount point and Start EBS application services