LATEST TOPICS

Oracle Database 12c: Move Datafile Online

Prior to Oracle Database 12c, moving a datafile was always a OFFLINE task. There were different approach to move a datafile. However, there was always a downtime associated with the datafile move operation.

Oracle has introduced ONLINE datafile move (relocation) starting with Oracle Database 12c. We can now MOVE or RENAME a datafile without impacting the availability of the datafile.

Lets see a simple demonstration and compare the datafile MOVE operation between 12c and Pre 12c databases.

Move Datafile: Pre 12c

Lets say, I have the following datafiles

sys@LABDB> select file#,name,status from v$datafile;

     FILE# NAME                                                    STATUS
---------- ------------------------------------------------------- -------
         1 +DATA/LABDB/DATAFILE/system.318.866672579               SYSTEM
         2 +DATA/LABDB/DATAFILE/undotbs1.315.866674507             ONLINE
         3 +DATA/LABDB/DATAFILE/sysaux.316.866672533               ONLINE
         6 +DATA/LABDB/DATAFILE/users.317.866672533                ONLINE

Lets say, I would like to move the datafile 6 (+DATA/LABDB/DATAFILE/users.317.866672533) to a Non-ASM file system.

To minimize the downtime, at best we can bring the datafile OFFLINE, physically move the datafile to new location and then bring the datafile ONLINE after recovering it as shown below.

---- 
---- Bring the datafile 6 OFFLINE
----
sys@LABDB> alter database datafile 6 offline;

Database altered.


-----
----- Copy or Move the file from ASM to Local File system
-----
ASMCMD> cp +DATA/LABDB/DATAFILE/users.317.867240733 /workspace/data/labdb/users1.dbf
copying +DATA/LABDB/DATAFILE/users.317.867240733 -> /workspace/data/labdb/users1.dbf
ASMCMD>

[oracle@labserver trace]$ ls -lrt /workspace/data/labdb/users1.dbf
-rw-r----- 1 oracle dba 5251072 Dec 25 12:16 /workspace/data/labdb/users1.dbf


-----
----- Update the database pointer to point to the new datafile
-----
sys@LABDB>  alter database rename file '+DATA/LABDB/DATAFILE/users.317.867240733' to '/workspace/data/labdb/users1.dbf';

Database altered.

-----
----- Recover the new datafile to make it consistent
-----
sys@LABDB>  alter database recover datafile '/workspace/data/labdb/users1.dbf';

Database altered.


-----
----- Bring back the datafile 6 ONLINE
-----
sys@LABDB>  alter database datafile '/workspace/data/labdb/users1.dbf' online;

Database altered.

The other methods, involve bringing the respective tablespace OFFLINE or bring the database in MOUNT state and then MOVE the datafile. However, I haven’t covered those methods here as I am focusing on the method with least downtime to compare it with the Oracle 12c ONLINE method.

Now, lets see how can we MOVE a datafile with Oracle Database 12c.

Move Datafile: 12c

Oracle Database 12c has introduced a new command ‘ALTER DATABASE MOVE DATAFILE’ that can be used to move a datafile ONLINE without compromising the datafile availability.

Here is the syntax:

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

Lets say, I have the following datafiles in ASM file system.

sys@LABDB> select file#,name,status from v$datafile;

     FILE# NAME                                                    STATUS
---------- ------------------------------------------------------- -------
         1 +DATA/LABDB/DATAFILE/system.318.866672579               SYSTEM
         2 +DATA/LABDB/DATAFILE/undotbs1.315.866674507             ONLINE
         3 +DATA/LABDB/DATAFILE/sysaux.316.866672533               ONLINE
         6 +DATA/LABDB/DATAFILE/users.317.866672533                ONLINE

I would like to move the datafiles to OS file system without impacting the availability.

Here is what, I can do.

sys@LABDB> alter database move datafile 2 to '/workspace/data/labdb/undotbs1.dbf';

Database altered.

sys@LABDB> alter database move datafile '+DATA/LABDB/DATAFILE/system.318.866672579' to '/workspace/data/labdb/system1.dbf';

Database altered.

sys@LABDB> alter database move datafile 3 to '/workspace/data/labdb/sysaux1.dbf';

Database altered.

sys@LABDB> alter database move datafile 6 to '/workspace/data/labdb/users1.dbf';

Database altered.

Lets check the status of the datafiles.

sys@LABDB> select file#,name,status from v$datafile;

     FILE# NAME                                                    STATUS
---------- ------------------------------------------------------- -------
         1 /workspace/data/labdb/system1.dbf                       SYSTEM
         2 /workspace/data/labdb/undotbs1.dbf                      ONLINE
         3 /workspace/data/labdb/sysaux1.dbf                       ONLINE
         6 /workspace/data/labdb/users1.dbf                        ONLINE

sys@LABDB>

Here we are, all the files are moved to local file system. It was too simple, just a single command did all the work for us and most importantly without a downtime.

When, we check the session trace, we could see the operation the Oracle has performed at the background.

Basically, Oracle has created the new datafile and then copied all the data blocks from existing datafile to the new file. Once the blocks are copied, the dictionary entry is updated to point to the new datafile.

*** 2014-12-25 11:14:18.487
*** SESSION ID:(56.13) 2014-12-25 11:14:18.487
*** CLIENT ID:() 2014-12-25 11:14:18.487
*** SERVICE NAME:(SYS$USERS) 2014-12-25 11:14:18.487
*** MODULE NAME:(sqlplus@labserver.home.com (TNS V1-V3)) 2014-12-25 11:14:18.487
*** ACTION NAME:() 2014-12-25 11:14:18.487

Moving datafile +DATA/LABDB/DATAFILE/undotbs1.315.866674507 (2) to /workspace/data/labdb/undotbs1.dbf
The secondary file /workspace/data/labdb/undotbs1.dbf is created

*** 2014-12-25 11:14:24.257
Blocks copied for file /workspace/data/labdb/undotbs1.dbf
Move operation committed for file /workspace/data/labdb/undotbs1.dbf

*** 2014-12-25 11:14:25.310
Move operation completed for file /workspace/data/labdb/undotbs1.dbf

*** 2014-12-25 11:14:58.260
Moving datafile +DATA/LABDB/DATAFILE/system.318.866672579 (1) to /workspace/data/labdb/system1.dbf

*** 2014-12-25 11:14:58.308
The secondary file /workspace/data/labdb/system1.dbf is created

*** 2014-12-25 11:15:55.634
Blocks copied for file /workspace/data/labdb/system1.dbf
Move operation committed for file /workspace/data/labdb/system1.dbf

*** 2014-12-25 11:15:56.726
Move operation completed for file /workspace/data/labdb/system1.dbf

The move command also removes the old datafile once the datafile is copied to new location.

ASMCMD> cd  +DATA/LABDB
ASMCMD> ls
CONTROLFILE/
ONLINELOG/
TEMPFILE/

However, we can retain the original datafile by using the KEEP option of the MOVE command provided the source datafile is not using OMF standards.

If DB_CREATE_FILE_DEST is defined for the database, we can skip the TO clause of the MOVE command. In this case the new datafile would be created as per OMF standards in the location mentioned by the DB_CREATE_FILE_DEST parameter.

sys@LABDB> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA

sys@LABDB> alter database move datafile 6;

Database altered.

sys@LABDB> select file#,name,status from v$datafile;

     FILE# NAME                                                    STATUS
---------- ------------------------------------------------------- -------
         1 /workspace/data/labdb/system1.dbf                       SYSTEM
         2 /workspace/data/labdb/undotbs1.dbf                      ONLINE
         3 /workspace/data/labdb/sysaux1.dbf                       ONLINE
         6 +DATA/LABDB/DATAFILE/users.317.867238963                ONLINE

If, we check the session trace; we could see DB_CREATE_FILE_DEST (+DATA) is being appended as the TO clause for the MOVE command.

*** 2014-12-25 11:42:43.414
Moving datafile /workspace/data/labdb/users1.dbf (6) to +DATA
The secondary file +DATA/LABDB/DATAFILE/users.317.867238963 is created
Blocks copied for file +DATA/LABDB/DATAFILE/users.317.867238963
Move operation committed for file +DATA/LABDB/DATAFILE/users.317.867238963

*** 2014-12-25 11:42:44.666
Move operation completed for file +DATA/LABDB/DATAFILE/users.317.867238963

However, there are certain restriction with respect to the MOVE command. For example, we can not use the MOVE command to relocate a TEMPFILE or REDO LOGFILE.

Conclusion:

Oracle has made the relocation and renaming of datafile a simple task for the DBA. Just a single command and we are done. The greatest advantage is obviously the ability to perform relocation or renaming ONLINE. This new feature would be very useful while migrating from one file system to another without compromising database availability.

Reference:

Renaming and Relocating Data Files

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed