MOVE DATAFILE IN 11G TRADITIONAL METHODS
In older days, it was quite difficult in maintaining the files in database compared to today.
Let’s again remember the difficulty in moving a datafile from one disk group to another with a maintenance downtime window until the 12c new feature online move came into picture
Datafiles were mistakenly placed in OCR disk group (+MGMTX) . This datafile needs to be moved to DATAX2 new disk group
kIsH@x3z6zx9<^>select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+MGMTX/x3z6zx9/datafile/users.256.1103221777
+DATAX/x3z6zx9/datafile/users.259.1098264613
+DATAX/x3z6zx9/datafile/sysaux.257.1098264613
+DATAX/x3z6zx9/datafile/system.256.1098264613
+DATAX/x3z6zx9/datafile/example.265.1098264707
+INDX/x3z6zx9/datafile/tbspc_indx.256.1098273921
+DATAX/x3z6zx9/datafile/undotbs3.267.1101332575
+REDOX/x3z6zx9/datafile/example.264.1103222495
Make the datafile offline
kIsH@x3z6zx9<^>alter database datafile '+MGMTX/x3z6zx9/datafile/users.256.1103221777' offline;
Database altered.
Copy the datafile using RMAN from source to destination
[oracle@x3dbzx36 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr 30 14:59:12 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: X3Z6ZX9 (DBID=3942786352)
RMAN> copy datafile '+MGMTX/x3z6zx9/datafile/users.256.1103221777' to '+DATAX2';
Starting backup at 30-APR-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+MGMTX/x3z6zx9/datafile/users.256.1103221777
output file name=+DATAX2/x3z6zx9/datafile/users.256.1103381987 tag=TAG20220430T145946 RECID=2 STAMP=1103382023
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
Finished backup at 30-APR-22
Rename the datafile to new location
kIsH@x3z6zx9<^>alter database rename file '+MGMTX/x3z6zx9/datafile/users.256.1103221777' to '+DATAX2/x3z6zx9/datafile/users.256.1103381987';
Database altered.
So far, the logical datafile records stored in control file header are updated. Now the physical file should be moved. Switch datafile to copy
RMAN> switch datafile '+DATAX2/x3z6zx9/datafile/users.256.1103381987' to copy;
using target database control file instead of recovery catalog
datafile 3 switched to datafile copy "+DATAX2/x3z6zx9/datafile/users.256.1103381987"
Recover the datafile using RMAN
[oracle@x3dbzx36 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr 30 15:08:59 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: X3Z6ZX9 (DBID=3942786352)
RMAN> recover datafile '+DATAX2/x3z6zx9/datafile/users.256.1103381987';
Starting recover at 30-APR-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 30-APR-22
Check the active datafiles and the new datafile is not in active list which means it is not accessed by any process.
ASMCMD> lsof
DB_Name Instance_Name Path
x3z6zx9 x3z6zx9 +datax/x3z6zx9/controlfile/current.260.1098264689
x3z6zx9 x3z6zx9 +datax/x3z6zx9/datafile/example.265.1098264707
x3z6zx9 x3z6zx9 +datax/x3z6zx9/datafile/sysaux.257.1098264613
x3z6zx9 x3z6zx9 +datax/x3z6zx9/datafile/system.256.1098264613
x3z6zx9 x3z6zx9 +datax/x3z6zx9/datafile/undotbs3.267.1101332575
x3z6zx9 x3z6zx9 +datax/x3z6zx9/datafile/users.259.1098264613
x3z6zx9 x3z6zx9 +datax/x3z6zx9/tempfile/temp.264.1098264699
x3z6zx9 x3z6zx9 +indx/x3z6zx9/datafile/tbspc_indx.256.1098273921
x3z6zx9 x3z6zx9 +recox/x3z6zx9/controlfile/current.256.1098264689
x3z6zx9 x3z6zx9 +recox/x3z6zx9/tempfile/temp.426.1103318779
x3z6zx9 x3z6zx9 +recox/x3z6zx9/tempfile/temp2.499.1103318535
x3z6zx9 x3z6zx9 +recox/x3z6zx9/tempfile/tempx.262.1098278637
x3z6zx9 x3z6zx9 +redox/x3z6zx9/onlinelog/group_7.260.1102800971
x3z6zx9 x3z6zx9 +redox/x3z6zx9/onlinelog/group_7.261.1102801081
Bring back datafile in new location to online
kIsH@x3z6zx9<^>alter database datafile '+DATAX2/x3z6zx9/datafile/users.256.1103381987' online;
Database altered.
Validate the results
kIsH@x3z6zx9<^>select name from v$datafile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATAX/x3z6zx9/datafile/system.256.1098264613
+DATAX/x3z6zx9/datafile/sysaux.257.1098264613
+DATAX2/x3z6zx9/datafile/users.256.1103381987
+DATAX/x3z6zx9/datafile/users.259.1098264613
+DATAX/x3z6zx9/datafile/example.265.1098264707
+INDX/x3z6zx9/datafile/tbspc_indx.256.1098273921
+DATAX/x3z6zx9/datafile/undotbs3.267.1101332575
+REDOX/x3z6zx9/datafile/example.264.1103222495
8 rows selected.
New datafile became active now
ASMCMD> lsof
DB_Name Instance_Name Path
x3z6zx9 x3z6zx9 +datax/x3z6zx9/controlfile/current.260.1098264689
x3z6zx9 x3z6zx9 +datax/x3z6zx9/datafile/example.265.1098264707
x3z6zx9 x3z6zx9 +datax/x3z6zx9/datafile/sysaux.257.1098264613
x3z6zx9 x3z6zx9 +datax/x3z6zx9/datafile/system.256.1098264613
x3z6zx9 x3z6zx9 +datax/x3z6zx9/datafile/undotbs3.267.1101332575
x3z6zx9 x3z6zx9 +datax/x3z6zx9/datafile/users.259.1098264613
x3z6zx9 x3z6zx9 +datax/x3z6zx9/tempfile/temp.264.1098264699
x3z6zx9 x3z6zx9 +datax2/x3z6zx9/datafile/users.256.1103381987 <<=== Active
x3z6zx9 x3z6zx9 +indx/x3z6zx9/datafile/tbspc_indx.256.1098273921
x3z6zx9 x3z6zx9 +recox/x3z6zx9/controlfile/current.256.1098264689
x3z6zx9 x3z6zx9 +recox/x3z6zx9/tempfile/temp.426.1103318779
x3z6zx9 x3z6zx9 +recox/x3z6zx9/tempfile/temp2.499.1103318535
x3z6zx9 x3z6zx9 +recox/x3z6zx9/tempfile/tempx.262.1098278637
x3z6zx9 x3z6zx9 +redox/x3z6zx9/onlinelog/group_7.260.1102800971
x3z6zx9 x3z6zx9 +redox/x3z6zx9/onlinelog/group_7.261.1102801081