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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s