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;