Querying Data Files

col name format a60
set pagesize 9999
select ts#,file#,name,status from v$datafile d where d.status!='ONLINE' ;
select * from v$tablespace;

Important Considerations

When relocating or renaming data files for SYSTEM, SYSAUX, and UNDO tablespaces, it is highly recommended to perform these operations with the database shut down. Online operations on these critical tablespaces may lead to unexpected errors or even database crashes.

Method 1: Operating System Copy

Offline Procedure

This approach works for any file type and requires database shutdown:

1. SHUTDOWN IMMEDIATE
2. Copy the data file to the new location using OS commands
3. STARTUP MOUNT
4. ALTER DATABASE RENAME FILE 'old_file_path' TO 'new_file_path';
5. ALTER DATABASE OPEN;

Online Procedure

It's possible to use OS copy while the database is running, but only for data files that can be taken OFFLINE:

1. alter database datafile '/path/to/file.dbf' offline;
2. recover datafile '/path/to/file.dbf' ;
3. ! cp /path/to/file.dbf /new/path/file_new.dbf
4. ALTER DATABASE RENAME FILE '/path/to/file.dbf' TO '/new/path/file_new.dbf';
5. alter database datafile '/new/path/file_new.dbf' online;

Method 2: RMAN Online Operations

RMAN> sql "alter database datafile 'file_name' offline";
RMAN> recover datafile file_number;
RMAN> run {
2> copy datafile 'old_file_location' to 'new_file_location';
3> switch datafile 'old_file_location' to datafilecopy 'new_file_location';
4> }
RMAN> sql "alter database datafile 'file_name' online";

-- Alternatively, using file numbers
RMAN> sql "alter database datafile 4 online";

Example:

SYS@DB> col name format a60
SYS@DB> set pagesize 9999
SYS@DB> select ts#,file#,name,status from v$datafile d where d.status!='ONLINE' ;
 
       TS#      FILE# NAME                                                         STATUS
 ---------- ---------- ------------------------------------------------------------ --------------
        0          1 /u01/app/oradata/DB/system01.dbf                  SYSTEM
        4          4 /u01/app/oradata/DB/users01.dbf                   RECOVER

RMAN> sql "alter database datafile 4 offline";
 
sql statement: alter database datafile 4 offline
 
RMAN> recover datafile 4;
 
Starting recover at 2023-01-15 10:06:56
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 2023-01-15 10:06:56

RMAN> run {
2> copy datafile '/u01/app/oradata/DB/users01.dbf' to '/u01/app/oradata/DB/users01_new.dbf';
3> switch datafile '/u01/app/oradata/DB/users01.dbf' to datafilecopy '/u01/app/oradata/DB/users01_new.dbf';
4> }
 
Starting backup at 2023-01-15 10:09:24
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oradata/DB/users01.dbf
output file name=/u01/app/oradata/DB/users01_new.dbf tag=TAG20230115T100924 RECID=2 STAMP=1120752565
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2023-01-15 10:09:25

datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=1120752565 file name=/u01/app/oradata/DB/users01_new.dbf

RMAN> sql "alter database datafile 4 online";
 
sql statement: alter database datafile 4 online

Method 3: ALTER TABLESPACE Operation

1. Take the tablespace OFFLINE: ALTER TABLESPACE TS_DATA OFFLINE;
2. Use OS commands to rename or move the data file
3. Use ALTER TABLESPACE to update the database: ALTER TABLESPACE TS_DATA RENAME DATAFILE '/u01/data/data01.dbf' TO '/u02/data/data01_new.dbf';
4. Bring the tablespace ONLINE: ALTER TABLESPACE TS_DATA ONLINE;

Method 4: Oracle 12c and Later Feature

Unlike previous versions, Oracle Database 12c R1 and later simplify the process of moving or renaming data files with the introduction of the ALTER DATABASE MOVE DATAFILE command.

This operation can be performed online while users continue to execute queries, DML, and DDL operations. Additionally, data files can be migrated between different storage systems, such as from non-ASM to ASM and vice versa.

Syntax

ALTER DATABASE MOVE DATAFILE ( 'filename' | 'ASM_filename' | file_number )
 [ TO ( 'filename' | 'ASM_filename' ) ]
 [ REUSE ] [ KEEP ]

The KEEP option retains the original data file, while the REUSE option allows overwriting an existing file. By default, files are reused if neither option is specified.

When the source file is an OMF (Oracle Managed File), the KEEP option cannot be used. If the target file is an OMF, the TO clause can be omitted, and the file will be created using the OMF name in the DB_CREATE_FILE_DEST location.

Note that the ALTER DATABASE MOVE DATAFILE syntax does not apply to temporary files, and appropriate privileges are required.

Examples

Renaming a Data File

SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users01.dbf' TO '/u00/data/users_01.dbf';

Migrating from Non-ASM to ASM

SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '+DG_DATA';

Migrating Between ASM Disk Groups

SQL> ALTER DATABASE MOVE DATAFILE '+DG_DATA/DBNAME/DATAFILE/users_01.dbf' TO '+DG_DATA_02';

Overwriting Existing File

SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' REUSE;

Copying File While Preserving Original

SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP;

Important: In Oracle 12c and later, container-specific operations must be performed. Attempting to move a data file outside of the appropriate container may result in an error like "ORA-01516: nonexistent log file, data file, or temporary file".