Oracle Move Data Files: Safe Procedures and Examples [Linux/19c]

When reorganizing storage or cleaning up directories in your Oracle environment, knowing how to move data files safely is essential. This article targets oracle 19c on Linux and explains two approaches—online move and the traditional offline + OS copy—using commands that work on a real system, with practical cautions.

[reference]

Conclusion & Quick Steps (Snippet)
Background and Basics (Terminology)
Procedures & Implementation (Prereqs and Branches)
Troubleshooting (Common Errors)
Operations, Monitoring, and Security Notes
FAQ
Summary (Key Points)

Conclusion & Quick Steps (Snippet)

Method A: Online move (recommended)
Move while the database stays OPEN using ALTER DATABASE MOVE DATAFILE. Oracle copies internally, so you temporarily need free space for both source and destination. After completion, the control file points to the new path.
Method B: Offline move (traditional)
Set the target tablespace OFFLINE → move the file at the OS level → update the path in the control file using ALTER DATABASE RENAME FILE → bring the tablespace ONLINE. You can rename multiple files at once.
When using OMF/ASM
With OMF (Oracle Managed Files) enabled, you can omit the destination and let Oracle name/place the file automatically; moving across ASM disk groups is straightforward.

Background and Basics (Terminology)

What is a data file?
It is an OS file that makes up a tablespace. The control file records which tablespace owns which file and where it lives. During a move, ensure the physical file location and the path stored in the control file match.
Essentials of online move (12c and later)
ALTER DATABASE MOVE DATAFILE allows moving while the database remains OPEN. Oracle creates an internal copy and switches over. To keep the old file, specify KEEP (not supported when the source is OMF). For PDB files, connect to the target PDB before running the command.
Essentials of offline move
Simply relocating the file at the OS level doesn’t update the path in the control file; use ALTER DATABASE RENAME FILE to make them consistent.

Procedures & Implementation (Prereqs and Branches)

0) Prerequisites (test environment used here)

OS: Oracle Linux 7/8 equivalent (commands are generic Linux)
DB: Oracle Database 19c non-CDB (SID example: ORCL)
Storage: File system (ASM noted separately)
Execution: SYSDBA
To reduce risk, use a user tablespace (special notes for SYSTEM/UNDO below)

1) Setup for the demo (user / tablespace / table)

You can run these SQL statements as-is (adjust paths to your environment).

-- Create a tablespace and a data file to move
CREATE TABLESPACE tbs_demo
  DATAFILE '/u01/app/oracle/oradata/ORCL/tbs_demo01.dbf' SIZE 50M
  AUTOEXTEND ON NEXT 10M MAXSIZE 1G;

-- Demo user and privileges
CREATE USER demo IDENTIFIED BY Demo1234
  DEFAULT TABLESPACE tbs_demo
  QUOTA UNLIMITED ON tbs_demo;

GRANT CREATE SESSION, CREATE TABLE TO demo;

-- Sample table and data
CONN demo/Demo1234
CREATE TABLE t1 (id NUMBER PRIMARY KEY, payload VARCHAR2(100));
INSERT INTO t1 VALUES (1, 'before move');
COMMIT;

-- Subsequent operations as SYSDBA
CONN / AS SYSDBA;

Note: CREATE TABLESPACE creates the physical file and records the path in the control file. AUTOEXTEND configures automatic growth.

2) Method A: Online move (recommended)

Requirement: Ensure the destination has free space roughly equal to the current file size (+ margin) because Oracle creates an internal copy.

┌────────────── Before move (DB OPEN) ──────────────┐
│  /u01/.../tbs_demo01.dbf (in use)                 │
└───────────────┬───────────────────────────────────┘
                │  internal copy (higher I/O)
┌───────────────▼───────────────────────────────────┐
│  /u02/.../tbs_demo01.dbf (new) ← switchover → old │
└──────────────────────────────────────────────────┘

-- 2-1) Precheck: current path
COLUMN file_name FORMAT A70
SELECT file_id, file_name, tablespace_name
  FROM dba_data_files
 WHERE tablespace_name='TBS_DEMO';

-- 2-2) Linux: prepare destination directory if needed
-- Running OS commands from SQL*Plus
HOST mkdir -p /u02/oradata/ORCL

-- 2-3) Online move (database remains OPEN)
ALTER DATABASE MOVE DATAFILE
  '/u01/app/oracle/oradata/ORCL/tbs_demo01.dbf'
TO
  '/u02/oradata/ORCL/tbs_demo01.dbf';

-- 2-4) Verify after switchover
SELECT file_name FROM dba_data_files WHERE tablespace_name='TBS_DEMO';
HOST ls -l /u02/oradata/ORCL/tbs_demo01.dbf;

Note: MOVE DATAFILE performs copy → switchover internally. If you do not specify KEEP, the old file becomes unused; verify and review backup policy before deleting any old files.

OMF/ASM example (reference)
With OMF configured, you can omit the destination and let Oracle auto-name the file. Moving across ASM disk groups is also supported.

-- Define an OMF destination (example)
ALTER SYSTEM SET db_create_file_dest='/u02/oradata/ORCL' SCOPE=BOTH;

-- Destination omitted: Oracle creates an OMF-named file
ALTER DATABASE MOVE DATAFILE
  '/u02/oradata/ORCL/tbs_demo01.dbf';

3) Method B: Offline move (traditional)

┌───────────────┐   mv (OS)   ┌────────────────┐
│ /u01/.../dbf  │ ─────────▶ │ /u03/.../dbf   │
└─────┬─────────┘             └──────┬─────────┘
      │   ALTER DATABASE RENAME FILE  │
      └───────────────▶  Update path in control file

-- 3-1) Take the tablespace offline
ALTER TABLESPACE tbs_demo OFFLINE NORMAL;

-- 3-2) Move the file at the OS level
HOST mkdir -p /u03/oradata/ORCL
HOST mv /u01/app/oracle/oradata/ORCL/tbs_demo01.dbf /u03/oradata/ORCL/tbs_demo01.dbf

-- 3-3) Update the path stored in the control file
ALTER DATABASE RENAME FILE
  '/u01/app/oracle/oradata/ORCL/tbs_demo01.dbf'
TO
  '/u03/oradata/ORCL/tbs_demo01.dbf';

-- 3-4) Bring the tablespace online
ALTER TABLESPACE tbs_demo ONLINE;

-- 3-5) Verify
SELECT file_name FROM dba_data_files WHERE tablespace_name='TBS_DEMO';

Note: You can rename multiple files in one statement (e.g., RENAME FILE 'a','b' TO 'A','B').

Troubleshooting (Common Errors)

Symptom/Error	Typical Cause	Checks & Remediation
ORA-01511 / ORA-01516 (during rename)	Wrong path / file missing / wrong state	Verify old/new paths on OS → check spellings in SQL → remember `RENAME FILE` is run after moving the file at OS level. Review full error stack.
ORA-01157: cannot identify/lock data file	OS rename/delete/permission issue; mismatch between control file and physical file	Confirm physical file exists; either move it back or use `RENAME FILE` to match the control file. Restore from backup if required.
ORA-01537: cannot add file – file already part of database	Attempted to add a file name already registered	Use a different name or check existing files before adding.

Operations, Monitoring, and Security Notes

I/O load and duration: Online move triggers an internal copy, increasing I/O temporarily. Avoid peak hours.
Backup consistency: After a move, the control file path changes, and RMAN picks up the new path for the next backup. Consider taking an ad-hoc consistency backup right after the move.
Leverage OMF/ASM: Enabling OMF simplifies naming/placement. Moves across ASM disk groups are supported.
Data Guard considerations: If file paths differ on the standby, plan and test ahead. Follow Oracle’s documented steps and constraints.
SYSTEM/UNDO/TEMP specifics: MOVE DATAFILE is available for SYSTEM, but for UNDO it’s safer to create a new UNDO tablespace and switch. For TEMP, add a new TEMPFILE then drop the old one.

FAQ

Q1. Can I really move data files while the database is running?
A. Use ALTER DATABASE MOVE DATAFILE. Ensure sufficient free space for the temporary copy and consider I/O impact.

Q2. How do I move a PDB’s data file?
A. Connect to the target PDB and execute the same command there.

Q3. What about ASM and OMF?
A. For ASM, specify a destination like +DATA2/.... With OMF enabled, you can omit the destination and let Oracle auto-name the file.

Q4. How should I handle TEMP and UNDO?
A. TEMP: add a new TEMPFILE then drop the old one. UNDO: create a new UNDO tablespace and switch to it for a safer approach.

Summary (Key Points)

Prefer online move (MOVE DATAFILE) for minimal downtime; verify free space and I/O window.
The traditional path is: OFFLINE → OS move → RENAME FILE → ONLINE.
OMF/ASM simplifies placement and supports cross-disk-group moves.
Watch out for SYSTEM/UNDO/TEMP special handling; use recommended alternatives where safer.

This article targets Oracle Database 19c (screens and defaults may differ in other versions).

[reference]
Managing Tablespaces