Move or Rename Oracle Files

DBA needs to rename or move oracle file from one filesystem or ASM DISKGroup to another.

  • Move Datafiles Using ALTER DATABASE MOVE DATAFILE command – 12c and onward
  • Moving Datafile Using RMAN with offline option – 10g or 11g

Prerequisites: Database should be configured archive log mode.

Move Datafiles Using ALTER DATABASE MOVE DATAFILE command – 12c and onward

From 12c, can move any Datafiles in online by using ALTER DATABASE MOVE DATAFILE command. I will show how to move all datafiles from +DATA01 to +DATA DISKGroup. Follow below steps.

Step 1: Stop all applications and you may stop listener to make sure there is no new connection from any users.

Step 2: Make sure you have database backup (Full Backup + Archive Log Backup or Incremental Backup).

Step 3: Find DataFile list.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
--------------------------------------------------------------------------------
+DATA01/ORCL/DATAFILE/system.266.1044638517
+DATA01/ORCL/DATAFILE/sysaux.262.1044636479
+DATA01/ORCL/DATAFILE/undotbs1.263.1044637461
+DATA01/ORCL/DATAFILE/users.261.1044630801
+DATA01/ORCL/DATAFILE/undotbs2.265.1044638477
+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753
+DATA01/ORCL/DATAFILE/tbs_02.256.1044232727
+DATA01/ORCL/DATAFILE/ts_data.260.1044630755
+DATA01/ORCL/DATAFILE/ts_data01.258.1044294097

9 rows selected.

Step 4: Identify the target DiskGroup to move:

SQL> SELECT NAME FROM V$ASM_DISKGROUP;
NAME
------------------------------
DATA01
OCR
FRA
DATA

Find DiskGroup details using ASMCMD:

[oracle@racdr1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@racdr1 ~]$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     15342    14530                0           14530              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     15342     4096                0            4096              0             N  DATA01/
MOUNTED  EXTERN  N         512   4096  1048576     10228    10129                0           10129              0             N  FRA/
MOUNTED  EXTERN  N         512   4096  1048576      9201     4713                0            4713              0             Y  OCR/

Step 5: Prepare script like below for all datafile or particular data file (which files want to move)

ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/system.266.1044638517' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/sysaux.262.1044636479' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/undotbs1.263.1044637461' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/users.261.1044630801' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/undotbs2.265.1044638477' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/tbs_02.256.1044232727' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/ts_data.260.1044630755' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/ts_data01.258.1044294097' TO '+DATA';

Execute the command:

[oracle@racdr1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 1 17:55:43 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/system.266.1044638517' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/sysaux.262.1044636479' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/undotbs1.263.1044637461' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/users.261.1044630801' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/undotbs2.265.1044638477' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/tbs_02.256.1044232727' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/ts_data.260.1044630755' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/ts_data01.258.1044294097' TO '+DATA';

Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

Step 6: Verify the datafile: All Files moved to +DATA DiskGroup

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/ORCL/DATAFILE/system.258.1044640545
+DATA/ORCL/DATAFILE/sysaux.268.1044640561
+DATA/ORCL/DATAFILE/undotbs1.260.1044640581
+DATA/ORCL/DATAFILE/users.257.1044640585
+DATA/ORCL/DATAFILE/undotbs2.277.1044640587
+DATA/ORCL/DATAFILE/tbs_01.278.1044640589
+DATA/ORCL/DATAFILE/tbs_02.279.1044640593
+DATA/ORCL/DATAFILE/ts_data.280.1044640595
+DATA/ORCL/DATAFILE/ts_data01.281.1044640657

9 rows selected.

Step 7: Take database full backup.

Moving Datafile Using RMAN with offline option – 10g or 11g

Above option – (ALTER DATABASE MOVE) will not support for 10g or 11g. I will discuss how can perform datafile movement or renaming on 10g or 11g.

Step 1: Stop all applications and you may stop listener to make sure there is no new connection from any users.

Step 2: Make sure you have database backup (Full Backup + Archive Log Backup or Incremental Backup).

Step 3: Find DataFile list.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
--------------------------------------------------------------------------------
+DATA01/ORCL/DATAFILE/system.266.1044638517
+DATA01/ORCL/DATAFILE/sysaux.262.1044636479
+DATA01/ORCL/DATAFILE/undotbs1.263.1044637461
+DATA01/ORCL/DATAFILE/users.261.1044630801
+DATA01/ORCL/DATAFILE/undotbs2.265.1044638477
+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753
+DATA01/ORCL/DATAFILE/tbs_02.256.1044232727
+DATA01/ORCL/DATAFILE/ts_data.260.1044630755
+DATA01/ORCL/DATAFILE/ts_data01.258.1044294097

9 rows selected.

Step 4: Identify the target DiskGroup to move:

SQL> SELECT NAME FROM V$ASM_DISKGROUP;
NAME
------------------------------
DATA01
OCR
FRA
DATA

Find DiskGroup details using ASMCMD:

[oracle@racdr1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@racdr1 ~]$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     15342    14530                0           14530              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     15342     4096                0            4096              0             N  DATA01/
MOUNTED  EXTERN  N         512   4096  1048576     10228    10129                0           10129              0             N  FRA/
MOUNTED  EXTERN  N         512   4096  1048576      9201     4713                0            4713              0             Y  OCR/

Step 5: Take offline the data file which want to move

alter database datafile '+DATA/ORCL/DATAFILE/tbs_01.274.1044232703' offline;
alter database datafile '+DATA/ORCL/DATAFILE/ts_data.275.1044294089' offline;
alter database datafile '+DATA/ORCL/DATAFILE/users.259.1043890029' offline;

Step 6: Copy the datafile to new location (New DISKGroup)

Note::: Save these log and it will help to identify the new file name specially for ASM.

[oracle@racdr1 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jul 1 15:12:11 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1571670809)

RMAN> copy datafile '+DATA/ORCL/DATAFILE/tbs_01.274.1044232703' to '+DATA01';

Starting backup at 01-JUL-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=79 instance=ORCL1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DATA/ORCL/DATAFILE/tbs_01.274.1044232703
output file name=+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753 tag=TAG20200701T151233 RECID=1 STAMP=1044630753
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-JUL-20

RMAN> copy datafile '+DATA/ORCL/DATAFILE/ts_data.275.1044294089' to '+DATA01';
Starting backup at 01-JUL-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/ORCL/DATAFILE/ts_data.275.1044294089
output file name=+DATA01/ORCL/DATAFILE/ts_data.260.1044630755 tag=TAG20200701T151235 RECID=2 STAMP=1044630792
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
Finished backup at 01-JUL-20

RMAN> copy datafile '+DATA/ORCL/DATAFILE/users.259.1043890029' to '+DATA01';
Starting backup at 01-JUL-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/ORCL/DATAFILE/users.259.1043890029
output file name=+DATA01/ORCL/DATAFILE/users.261.1044630801 tag=TAG20200701T151321 RECID=3 STAMP=1044630801
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-JUL-20

Step 7: Rename the datafile

Note::: New datafile name (generated auto file name for ASM) will get from Step 6 (RMAN output).

SQL> alter database rename file '+DATA/ORCL/DATAFILE/tbs_01.274.1044232703' to '+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753';

alter database rename file '+DATA/ORCL/DATAFILE/ts_data.275.1044294089' to '+DATA01/ORCL/DATAFILE/ts_data.260.1044630755';

alter database rename file '+DATA/ORCL/DATAFILE/users.259.1043890029' to '+DATA01/ORCL/DATAFILE/users.261.1044630801';

Database altered.

SQL> SQL>
Database altered.

SQL> SQL>
Database altered.

Step 8: Switch Datafile using RMAN

switch datafile '+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753' to copy;
switch datafile '+DATA01/ORCL/DATAFILE/ts_data.260.1044630755' to copy;
switch datafile '+DATA01/ORCL/DATAFILE/users.261.1044630801' to copy;
[oracle@racdr1 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jul 1 15:22:07 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1571670809)

RMAN> switch datafile '+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753' to copy;

using target database control file instead of recovery catalog
datafile 7 switched to datafile copy "+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753"

RMAN> switch datafile '+DATA01/ORCL/DATAFILE/ts_data.260.1044630755' to copy;

datafile 2 switched to datafile copy "+DATA01/ORCL/DATAFILE/ts_data.260.1044630755"

RMAN> switch datafile '+DATA01/ORCL/DATAFILE/users.261.1044630801' to copy;

datafile 6 switched to datafile copy "+DATA01/ORCL/DATAFILE/users.261.1044630801"

Step 9: Recovery the new datafile using RMAN

recover datafile '+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753';
recover datafile '+DATA01/ORCL/DATAFILE/ts_data.260.1044630755';
recover datafile '+DATA01/ORCL/DATAFILE/users.261.1044630801';
RMAN> recover datafile '+DATA01/ORCL/DATAFILE/ts_data.260.1044630755';

Starting recover at 01-JUL-20
using channel ORA_DISK_1

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

Finished recover at 01-JUL-20

RMAN> recover datafile '+DATA01/ORCL/DATAFILE/users.261.1044630801';

Starting recover at 01-JUL-20
using channel ORA_DISK_1

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

Finished recover at 01-JUL-20

Step 10: Bring back datafile online

RMAN> alter database datafile '+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753' online;
Statement processed

RMAN> alter database datafile '+DATA01/ORCL/DATAFILE/ts_data.260.1044630755' online;
Statement processed

RMAN> alter database datafile '+DATA01/ORCL/DATAFILE/users.261.1044630801' online;
Statement processed

Step 11: Take database full backup.

Leave a Reply