Rename a datafile in dataguard environment

If you want to rename a data file on a standby database, you need to do it manually:

1. Cancel the Data Guard recovery (set dg_broker_start to false and restart the database in mount status if you use dataguard broker service).
2. Set the standby file management to manual
alter system set standby_file_management=manual.
3. Move the file with OS commands, just as you did on the primary database.
4. Rename the file with
alter database rename file ‘old’ to ‘new’.
Re-enable the automatic standby file management and dg broker and restart the recovery (start db in mount status if you use data guard)

Following are the steps:
On Primary:
1. Check the database role for verified it is primary server

select name,db_unique_name,database_role,switchover_status from v$database;

select database_role,switchover_status from v$database;

2. Change the parameter standby file management from auto to manual

show parameter STANDBY_FILE_MANAGEMENT

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;


On Standby:
3. Check the database role and verified it is standby

select name,db_unique_name,database_role,switchover_status from v$database;

select database_role,switchover_status from v$database;

4. Check the Status of archive applied and data guard in sync

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

5. Change the parameter in Standby

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

On PRIMARY:
6. Check the file name and location which need to rename

select file_name from dba_data_files where tablespace_name='EXAMPLE';

7. Shutdown the primary database and move the file for rename or rename at same location

shutdown immediate

mv /u02/oradata/orcl/example01.dbf /u03/oradata/orcl/example01_temp.dbf

8. Startup the database at mount state and update the control file with new location or name.

Startup mount

alter tablespace example rename datafile '/u02/oradata/orcl/example01.dbf' to '/u03/oradata/orcl/example01_temp.dbf';

On Standby:
9. Check the location of file

select ts#,name from v$tablespace where name='EXAMPLE';

select name from v$datafile where ts#=6;

10. Cancel the recover of standby Server and clean shutdown the database.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

shutdown immediate

11. Move the datafile to new location or new name with OS commands.

mv /u02/oradata/orcl/example01.dbf /u03/oradata/orcl/example01_temp.dbf

12. Start the standby database at mount state and update control file.

startup mount

alter tablespace example rename datafile '/u02/oradata/orcl/example01.dbf' to '/u03/oradata/orcl/example01_temp.dbf';

13. Verify the location at standby

select name from v$datafile where ts#=6;

14. Start the standby recover process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

15. Make both the primary and standby parameter to auto

Primary:
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Standby :
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;