Move Datafile in Oracle Dataguard Database

In this post, I will tell you about the method of moving temp datafiles, database that has a Dataguard environment installed.

Check List

• Check current location of datafiles :

SQL> select tablespace_name, file_name from dba_temp_files;TABLESPACE_NAME FILE_NAMETEMP /u01/app/oracle/oradata/orcl/temp01.dbf
TEMP1 /u01/app/oracle/oradata/orcl/temp1_02.dbf

You can use the following query to list other datafiles.

select name from v$datafile;

Let’s look at the value of the STANDBY_FILE_MANAGEMENT parameter.

SQL> show parameter STANDBY_FILE_MANAGEMENTNAME TYPE VALUE
 — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — 
standby_file_management string AUTO

STANDBY_FILE_MANAGEMENT: The value of this parameter is also automatically reflected in the standby database when a new datafile is added to the primary database when AUTO is made and deleted.

NOTE: Let’s check the size of the datafile we want to move and make sure there is enough space on the disk we want to move.

PROCESS STEPS:

1. First, let’s stop the apply process on the dataguard

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Let’s set our STANDBY_FILE_MANAGEMENT parameter to “MANUAL”.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

3. Let’s close our dataguard database.

SQL> shutdown immediate

4. Just in case, let’s copy the datafile we want to move to the new directory. We can delete, after successful copying.

$ cp /u01/app/oracle/oradata/orcl/temp01.dbf   /u03/oradata/orcl/temp01.dbf$ cp /u01/app/oracle/oradata/orcl/temp1_03.dbf   /u03/oradata/orcl/temp1_02.dbf

5. Let’s change the name in the old directory of the datafiles we are moving

$ mv /u01/app/oracle/oradata/orcl/temp01.dbf   /u01/app/oracle/oradata/orcl/temp01.dbf_org$ mv /u01/app/oracle/oradata/orcl/temp1_02.dbf   /u01/app/oracle/oradata/orcl/temp1_02.dbf_org

6. Let’s open our database in mount mode

SQL> startup mount;

7. After the migration process is completed, we need to connect to sqlplus and rename the datafile.

SQL> alter database rename file '/u01/app/oracle/oradata/orcl/temp1_03.dbf' to '/u03/oradata/orcl/temp1_03.dbf';SQL> alter database rename file '/u01/app/oracle/oradata/orcl/temp01.dbf' to '/u03/oradata/orcl/temp01.dbf';

8. Let’s set our STANDBY_FILE_MANAGEMENT parameter to “AUTO”.

SQL> alter system set standby_file_management=AUTO;

9. Let’s start applying in our dataguard database

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

10. Let’s check prod and dataguard database are sync with the following query

SQL> select name,value,time_computed from v$dataguard_stats;

11. After sync, we can delete our datafiles in the old directory.

[root@dbyedek orcl]# rm -rf *.dbf_org

We have successfully migrated our temp datafiles from our dataguard database.