Move data files in oracle database

In Oracle database, you might need to move datafiles to a different disk or folder for better I/O performance or storage management. In this article, I will show you how to move the data files of an Oracle standby database on a Windows system. This will be a helpful guide for you. The same steps can also be executed on Linux/Unix systems.

When moving datafiles in oracle standby database, you need to be careful about log synchronization.

There are three methods to move data files to another disk in an Oracle database.

Method 1: Rename the Datafile: In this method, the ALTER DATABASE RENAME FILE command is used.

Method 2: Online Datafile Move (Oracle 12c and above)

This second method allows for datafile movement while the database remains online. While the database is open, the datafile can be moved while users continue their operations on the system.

-- For example:

SQL> alter database move datafile 'D:\ORADATA\ORCLDG1\DATA1.DBF' to 'F:\oradata\ORCLDG1\ORCLDG1\datafile\DATA1.DBF';

--If you want to keep the old file KEEP parameter can be used.

SQL> alter database move datafile 'D:\ORADATA\ORCLDG1\DATA1.DBF' to 'F:\oradata\ORCLDG1\ORCLDG1\datafile\DATA1.DBF' KEEP;

Method 3: Edit the Control File: if you choose this method, you can move the datafile by editing the control file’s content.

Step-by-Step Datafile Move in Standby:

Actions to be Taken Before Closing the Database.

1. Backup: Make sure to backup your database before moving data files.
2. List the current locations of database files, control file, log file, temp file files.
3. Make sure that the standby database is synchronized with the production database.

--1. Checking existing datafiles, logfiles and tempfiles

SQL> select name from v$datafile;
SQL> select name from v$logfile;
SQL> select file_name from dba_temp_files;

--2. Checking the location of control files:

SQL> show parameter control_files;

--3. Checking the standby_file_management status:

SQL> show parameter standby_file_management

--4. Synchronization check

SQL> SELECT 
    ARCH.THREAD# AS "Thread", 
    ARCH.SEQUENCE# AS "Last Sequence Received", 
    APPL.SEQUENCE# AS "Last Sequence Applied", 
    (ARCH.SEQUENCE# - APPL.SEQUENCE#) AS "Difference" 
FROM 
    (SELECT THREAD#, SEQUENCE# 
     FROM V$ARCHIVED_LOG 
     WHERE (THREAD#, FIRST_TIME) IN 
           (SELECT THREAD#, MAX(FIRST_TIME) 
            FROM V$ARCHIVED_LOG 
            GROUP BY THREAD#)
    ) ARCH,
    (SELECT THREAD#, SEQUENCE# 
     FROM V$LOG_HISTORY 
     WHERE (THREAD#, FIRST_TIME) IN 
           (SELECT THREAD#, MAX(FIRST_TIME) 
            FROM V$LOG_HISTORY 
            GROUP BY THREAD#)
    ) APPL 
WHERE ARCH.THREAD# = APPL.THREAD# 
ORDER BY 1;

After we do all our checks and note the locations of the database files, we can start moving the database data files to the new disk.

-- 1. First, we will stop the standby database.

SQL> alter system set standby_file_management=manual;

SQL> alter database recover managed standby database cancel;

SQL> shutdown immediate;

-- 2. Open db in NOMOUNT mode and set parameters
-- We can change the necessary parameters here,
-- or we can edit the pfile content and create an spfile from pfile.

SQL> startup nomount;

-- Set the new location for control files

SQL> alter system set control_files='F:\oradata\ORCLDG1\ORCLDG1\controlfile\control01.ctl','F:\oradata\ORCLDG1\ORCLDG1\controlfile\control02.ctl' scope=spfile;

-- Set the new location for data files.

SQL> alter system set db_create_file_dest='F:\oradata\ORCLDG1' scope=spfile;

-- Set db_file_name_convert

SQL> alter system set db_file_name_convert='D:\oradata\ORCL','F:\oradata\ORCLDG1\ORCLDG1\datafile' scope=spfile;

-- Set log_file_name_convert

SQL> alter system set log_file_name_convert='D:\ORADATA\ORCL','F:\oradata\fast_recovery_area' scope=spfile;

-- Set db_recovery_file_dest

SQL> alter system set db_recovery_file_dest='F:\oradata\fast_recovery_area' scope=spfile;

-- The database is closed and opened in mount mode.

SQL> shutdown immediate;

SQL> startup mount;

-- Copy data files to new location you can manually copy files on Windows.
-- use the following command to copy files in the background on Linux:

cp /old_location/datafile.dbf /new_location/datafile.dbf

-- Rename the moved data files, log files, and temp files

SQL> alter database rename file 'D:\ORADATA\ORCLDG1\DATA1.DBF' to 'F:\oradata\ORCLDG1\ORCLDG1\datafile\DATA1.DBF';

SQL> alter database rename file 'F:\ORADATA\REDO1.LOG' to 'F:\oradata\fast_recovery_area\ONLINELOG\REDO1.LOG';

SQL> alter database rename file 'D:\ORADATA\ORCLDG1\TEMP1.DBF' to 'F:\oradata\ORCLDG1\ORCLDG1\datafile\TEMP1.DBF';

-- Restart the standby database

SQL> alter system set standby_file_management=AUTO;

-- Start MRP for standby database

SQL> alter database recover managed standby database using current logfile disconnect from session;

-- Perform necessary checks

SQL> show parameter standby_file_management;

SQL> select name from v$datafile;

SQL> select name from v$logfile;

SQL> select file_name from dba_temp_files;

SQL> show parameter control_files;

-- Check if standby is working properly

SQL> SELECT 
    ARCH.THREAD# AS "Thread", 
    ARCH.SEQUENCE# AS "Last Sequence Received", 
    APPL.SEQUENCE# AS "Last Sequence Applied", 
    (ARCH.SEQUENCE# - APPL.SEQUENCE#) AS "Difference" 
FROM 
    (SELECT THREAD#, SEQUENCE# 
     FROM V$ARCHIVED_LOG 
     WHERE (THREAD#, FIRST_TIME) IN 
           (SELECT THREAD#, MAX(FIRST_TIME) 
            FROM V$ARCHIVED_LOG 
            GROUP BY THREAD#)
    ) ARCH,
    (SELECT THREAD#, SEQUENCE# 
     FROM V$LOG_HISTORY 
     WHERE (THREAD#, FIRST_TIME) IN 
           (SELECT THREAD#, MAX(FIRST_TIME) 
            FROM V$LOG_HISTORY 
            GROUP BY THREAD#)
    ) APPL 
WHERE ARCH.THREAD# = APPL.THREAD# 
ORDER BY 1;

I’m sure this article will be useful to you, I wish you good work :) If you need more help, feel free to contact me on linkedin

Best regards!