Moving datafile online in Oracle 12c (12.1.0.2)

Starting from Oracle database 12c release 1 (12.1.0.2), you can move a data file from one location/filesystem to another/ASM diskgroup while database is open and being used by the application users.


Taking an example of ASM storage where mistakenly a datafile was added to a tablespace without plus(+) shine before the diskgroup name and new file got added inside the $ORACLE_HOME on the filesystem.


In this situation, DBA should quickly try to reduce the newly added file size to smallest size possible and turn off the datafile autoextend so no much data could be written and datafile size remain small which gives us a luxury to move it to proper location in minimum time.

Below highlighted data file was added mistakenly which was placed into ORACLE_HOME.

FILE_NAME                                                                                GB AUT     MAX_GB
-------------------------------------------------------------------------------- ---------- --- ----------
+DATA/TESTDB/DATAFILE/users.4513.1027281799                                10 YES 31.9999847
+DATA/TESTDB/DATAFILE/users.4813.1027281809                                 4 YES 31.9999847
/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/DATA                                      2 YES         31
+DATA/TESTDB/DATAFILE/users.8520.1027282145                                 2 YES         31




Let's move the datafile created inside the /u01 mount to our +DATA ASM Diskgroup with following command.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/DATA' TO '+DATA';

Database altered.

Now, let's validate the datafile list if that is moved.

SQL> select file_name, bytes/1024/1024/1024 as GB ,autoextensible,MAXBYTES/1024/1024/1024 MAX_GB from dba_data_files where tablespace_name='users' order by GB desc;

FILE_NAME                                                                                GB AUT     MAX_GB
-------------------------------------------------------------------------------- ---------- --- ----------
+DATA/TESTDB/DATAFILE/users.4513.1027281799                                10 YES 31.9999847
+DATA/TESTDB/DATAFILE/users.4813.1027281809                                 4 YES 31.9999847
+DATA/TESTDB/DATAFILE/users.8520.1027282145                                 2 YES         31
+DATA/TESTDB/DATAFILE/users.4194.1027284059                        .001953125 NO           0




Data file moved successfully as you can see in the above output.

Now once datafile is moved to the appropriate location, you can resize and turn autoextend on for its auto growth.

SQL> alter database datafile '+DATA/TESTDB/DATAFILE/users.4194.1027284059' resize 1g;

Database altered.

SQL> alter database datafile '+DATA/TESTDB/DATAFILE/users.4194.1027284059' autoextend on maxsize 32767m;

Database altered.

SQL> 

Let's validate the data file which we moved if that has no any corrupt blocks.

RMAN> validate datafile 68;

Starting validate at 17-DEC-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=112 instance=TESTDB1 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00068 name=+DATA/TESTDB/DATAFILE/users.4194.1027284059
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
68   OK     0              130945       131072          15464094246834
  File Name: +DATA/TESTDB/DATAFILE/users.4194.1027284059
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              127

Finished validate at 17-DEC-19

RMAN>

Datafile validation is successful.




Hope it helps, thanks for reading, please subscribe to this blog to stay updated with latest news on Oracle Cloud Infrastructure and Oracle Autonomous Database Cloud Services and new articles.

Twitter : https://twitter.com/rajsoft8899
Linkedin : https://www.linkedin.com/in/raj-kumar-kushwaha-5a289219/