Oracle Move Datafile

We can move/relocate oracle datafile to different location, In this article, we will move datafile with the help of Recovery Manager. Consider step by step approach to move oracle datafile as below: Step I: Get database datafile ID those you wanted to move with the help of REPORT SCHEMA rman command as below. Also see my notes on 12c move oracle data files online. To move a datafile, either take the tablespace it belongs to offline, or shut down the database and bring it back up into a mounted state. The system tablespace, temporary tablespaces and tablespaces used for rollback will require the database be in. Prior to Oracle 12c moving non-ASM data files required taking data files ‘offline’ and negotiating an outage with the business, with Oracle 12c this is no longer the case. Storage Maintenance You can use the ALTER DATABASE MOVE DATAFILE to move a data file to different location, disk or storage system. 2) If a datafile is not empty, it cannot be dropped. If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile. 3)You cannot drop the first or only datafile in a tablespace. This means that DROP DATAFILE cannot be used with a bigfile tablespace. How to rename or move a datafile in oracle. 14389 views 1 min, 32 sec read 3 If you are in 12c version,then renaming a datafile can be done online, without making.

The new Oracle Database 12c allows to move datafiles ONLINE while they’re been used. This allows great availability when dealing with database moving, compared to the previous approach.

Pre 12c:

copy datafile with RMAN
offline datafile
switch datafile to copy
recover datafile
alter datafile online

From 12c:

move the datafile! 🙂

Oracle move datafile to another diskgroup

The actual command for moving the datafile is:

</div><table><tbody><tr><td><div><span>SQL</span><span>></span><span>alter</span><span>database</span><span>move</span>datafile1<span>to</span><span>'/data/CLASSIC/system01.dbf'</span>;</div></td></tr></tbody></table><p>where the source can be specified using the file#, or the actual path.</p><p>The destination must be specified only if moving to a non-OMF file, otherwise it takes the <strong>db_create_file_dest</strong> parameter:</p><p>in the latter it will move the system datafile back to my +DATA diskgroup.</p><p>So, it’s a great enhancement to move database:</p><h2>Data File Host Music Download</h2><ul><li>from a storage to another without using ASM</li><li>migrating online from an ASM DG to another</li><li>moving from FS to ASM and viceversa</li><li>(not tested) move datafiles on Windows from a logical disk to another!</li><li>etc.</li></ul><p>Full example (including some information on the proper MV enqueue):</p><div><textarea wrap='soft' readonly='>SQL> select file#, name from v$datafile; FILE# NAME ---------- ------------------------------------------------------------------------------------------ 1 +DATA/CLASSIC/DATAFILE/system.267.814717093 2 +DATA/CLASSIC/DATAFILE/sysaux.266.814717123 3 +DATA/CLASSIC/DATAFILE/undotbs1.260.814717147 4 +DATA/CLASSIC/DATAFILE/users.258.814717205 5 +DATA/CLASSIC/DATAFILE/fda1.284.815853305 SQL> select * from x$ksqst where indx=63; ADDR INDX INST_ID CON_ID KS KSQSTREQ KSQSTWAT KSQSTSGT KSQSTFGT KSQSTWTM KSQSTRSN ---------------- ---------- ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------------------------------------------------------------- KSQSTEXPL KSQSTEVIDX ------------------------------------------------------------------------------------------------------------------------ ---------- 000000008EE528A8 63 1 0 MV 0 0 0 0 0 datafile move Held during online datafile move operation or cleanup 157 SQL> alter database move datafile 1 to '/data/CLASSIC/system01.dbf'; Database altered. SQL> SQL> select file#, name from v$datafile; FILE# NAME ---------- ------------------------------------------------------------------------------------------ 1 /data/CLASSIC/system01.dbf 2 +DATA/CLASSIC/DATAFILE/sysaux.266.814717123 3 +DATA/CLASSIC/DATAFILE/undotbs1.260.814717147 4 +DATA/CLASSIC/DATAFILE/users.258.814717205 5 +DATA/CLASSIC/DATAFILE/fda1.284.815853305 SQL> select * from x$ksqst where indx=63; ADDR INDX INST_ID CON_ID KS KSQSTREQ KSQSTWAT KSQSTSGT KSQSTFGT KSQSTWTM KSQSTRSN ---------------- ---------- ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------------------------------------------------------------- KSQSTEXPL KSQSTEVIDX ------------------------------------------------------------------------------------------------------------------------ ---------- 000000008EE528A8 63 1 0 MV 56 0 3 53 0 datafile move Held during online datafile move operation or cleanup 157

---------- ------------------------------------------------------------------------------------------

2+DATA/CLASSIC/DATAFILE/sysaux.266.814717123

4+DATA/CLASSIC/DATAFILE/users.258.814717205

ADDRINDXINST_IDCON_IDKSKSQSTREQKSQSTWATKSQSTSGTKSQSTFGTKSQSTWTMKSQSTRSN

---------------- ---------- ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------ ----------

Heldduringonlinedatafilemoveoperationorcleanup157

SQL>alterdatabasemovedatafile1to'/data/CLASSIC/system01.dbf';

Databasealtered.

SQL>

SQL>selectfile#,namefromv$datafile;

FILE#NAME

---------- ------------------------------------------------------------------------------------------

2+DATA/CLASSIC/DATAFILE/sysaux.266.814717123

4+DATA/CLASSIC/DATAFILE/users.258.814717205

ADDRINDXINST_IDCON_IDKSKSQSTREQKSQSTWATKSQSTSGTKSQSTFGTKSQSTWTMKSQSTRSN

---------------- ---------- ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------ ----------

Heldduringonlinedatafilemoveoperationorcleanup157

Controlfiles cannot be moved online yet. The other kind of files (temp and redo logs) off course can be moved easily by creating the new ones and deleting the old ones, as it was on pre-12c releases.

Oracle Move Datafile To Another Diskgroup

Cheers

Ludo

The following two tabs change content below.

Oracle Move Datafile Online

Ludovico ia a member of the Oracle Database High Availability (HA), Scalability & Maximum Availability Architecture (MAA) Product Management team in Oracle. He focuses on Fleet Patching and Provisioning (FPP), MAA-optimized software lifecycle management, Cloud MAA and Online Operations.

Oracle Move Datafile From Filesystem To Asm

The fear of (availability) loss is a path to the dark side. - October 5, 2020
Data Guard, Easy Connect and the Observer for multiple configurations - August 14, 2020
Script to check Data Guard status from SQL - August 12, 2020