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:
- 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.