How to Move Data File Location

How to Move Data File Location

Move Data File: We are going to move or rename data files.

There're several ways that can move data files from one place to another place.

ALTER DATABASE MOVE DATAFILE - Online 

The new feature from release 12c enables us to move data files online.

ALTER DATABASE RENAME FILE - Offline

The old-style of renaming procedure can also move data files offline.

SWITCH DATAFILE TO COPY - Using RMAN

We backup a copy of the data file by RMAN and make a switch to the copy and then make it work.

SWITCH TABLESPACE TO COPY - Using RMAN

We backup a copy of the tablespace by RMAN and make a switch to the copy and then make it work.

SWITCH DATABASE TO COPY - Using RMAN

We backup a copy of the database by RMAN and make a switch to the copy and then make it work.

Alter Database Move Datafile:

Move Data File Online

1. Connect to the database.

. oraenv

sqlplus / as sysdba

2. Check the Database Status.

select name, open_mode from v$database;

select instance_name, status from v$instance;

3. Check current datafiles.

set pages 1000 lines 1000;

column FILE_NAME format a70;

select FILE_ID,FILE_NAME from dba_data_files;

4. Move datafile

Standalone Database:

alter database move datafile 7 to '/tmp/users.dbf' reuse;

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_kh3nsdqj_.dbf' TO '/tmp/users.dbf';

RAC Database:

alter database move datafile 7 to '+DATA/DEVCDB/users.dbf' reuse;

alter database move datafile '+DATA/DEVDB/datafile/users.dbf' to '+DATA/DEVDB/datafile/users.dbf' reuse;

5. Verify

set pages 1000 lines 1000;

column name format a70;

select FILE_ID,FILE_NAME from dba_data_files;

Alter Database Rename File

datafile renaming procedure which is using ALTER DATABASE RENAME FILE can also move data files for us, but it can only do it offline.

Some datafiles will be moved to the new location but their tablespaces will remain unchanged.

1. Connect to the database.

. oraenv

sqlplus / as sysdba

2. Check the Database Status.

select name, open_mode from v$database;

select instance_name, status from v$instance;

3. List tablespaces and datafiles

set pages 1000 lines 1000;

column name format a70;

select FILE_ID,FILE_NAME from dba_data_files;

4. Alter Tablespace Offline

We have to take their tablespaces offline first for further operations, which will affect online users who are using them. 

alter tablespace tbs_1 offline;

alter tablespace tbs_4 offline;

Alternatively, you can take data files offline instead of tablespaces, if the database is in ARCHIVELOG mode. 

You don't have to know the tablespaces that the data files belong to.

alter database datafile '/u01/app/oracle/oradata/DEVDB/datafile/TBS_1.dbf' offline;

alter database datafile '/u01/app/oracle/oradata/DEVDB/datafile/TBS_2.dbf' offline;

Performing recover might be needed after bringing data files online. 

Taking the entire tablespace offline is safer than bringing individual data file offline.

5. Copy Data Files to New Location

[oracle@oraclelab1 ~]$ cp -ip /u01/app/oracle/oradata/DEVDB/datafile/TBS_1.dbf /u01/app/oracle/oradata/DEVDB/datafile/TBS_1_New.dbf;

[oracle@oraclelab1 ~]$ cp -ip /u01/app/oracle/oradata/DEVDB/datafile/TBS_2.dbf /u01/app/oracle/oradata/DEVDB/datafile/TBS_1_New.dbf;

6. Alter Database Rename File

alter database rename file '/u01/app/oracle/oradata/DEVDB/datafile/TBS_1.dbf' to '/u01/app/oracle/oradata/DEVDB/datafile/TBS_1_New.dbf';

alter database rename file '/u01/app/oracle/oradata/DEVDB/datafile/TBS_2.dbf' to '/u01/app/oracle/oradata/DEVDB/datafile/TBS_1_New.dbf';

Alternatively, you can do renaming on tablespace-level.

alter tablespace tbs_1 rename datafile '/u01/app/oracle/oradata/DEVDB/datafile/TBS_1.dbf' to '/u01/app/oracle/oradata/DEVDB/datafile/TBS_1_New.dbf';

alter tablespace tbs_4 rename datafile '/u01/app/oracle/oradata/DEVDB/datafile/TBS_2.dbf' to '/u01/app/oracle/oradata/DEVDB/datafile/TBS_1_New.dbf';

We should always operate data files on database level as a best practice.

7. Alter Tablespace Online

alter tablespace tbs_1 online;

alter tablespace tbs_4 online;

Or Follow below:

recover datafile '/u01/app/oracle/oradata/DEVDB/datafile/TBS_1_New.dbf';

recover datafile '/u01/app/oracle/oradata/DEVDB/datafile/TBS_1_New.dbf';

alter database datafile '/u01/app/oracle/oradata/DEVDB/datafile/TBS_1_New.dbf' online;

alter database datafile '/u01/app/oracle/oradata/DEVDB/datafile/TBS_1_New.dbf' online;

I recover the two datafiles before bringing them online. This is only possible for the database which are in ARCHIVELOG mode.

8. Remove Unused Data Files

For reclaiming the disk space, you have to remove the source data files.

[oracle@oraclelab1 ~]$ rm -i /u01/app/oracle/oradata/DEVDB/datafile/TBS_1.dbf

[oracle@oraclelab1 ~]$ rm -i /u01/app/oracle/oradata/DEVDB/datafile/TBS_2.dbf

Regards,

Mallik