Oracle 12c Moving Datafile Online
Contributions, Oracle

Oracle 12c – Moving Datafile Online

This is one of the tedious jobs which every DBA needs to perform at some point in time due to:

  • Insufficient Disk Space
  • Move from Non-ASM to ASM disk

 

Prior to Oracle 12c, it was a challenge for DBAs to perform this activity because clients always want minimal downtime, plus it requires additional database activities to be performed. (For example, Issue of some OS command followed by database commands.)

In order to understand the ease of this process, let’s see what DBAs needs to do to move a datafile prior 12c.

High level steps include:

1. Change Tablespace Offline – We need to do this so that DBWR does not write new blocks there. Database downtime starts from this command.

2. Move/Copy datafile to target location – This is an OS command and not all DBA’s have server level access.

3. Update Controlfile – This is a must because after the file moves to target location Controlfile needs to be updated.

4. Bring tablespace Online – We need to do this so that DBWR starts writing blocks again. Database downtime endss

Example of moving datafile prior Oracle 12c:

login as: oracle
oracle@lab1’s password:
Last login: Sun Sep 18 22:02:35 2016 from 10.62.109.49
[oracle@lab1]$ ps -ef|grep pmon
oracle 4239 4208 0 07:52 pts/0 00:00:00 grep pmon
oracle 5721 1 0 Aug22 ? 00:04:50 ora_pmon_LAB11G
grid 8794 1 0 Jul30 ? 00:07:54 asm_pmon_+ASM
oracle 10213 1 0 Jul30 ? 00:08:13 ora_pmon_LAB12C_NC
oracle 10585 1 0 Jul30 ? 00:08:16 ora_pmon_LAB12C_C

[oracle@lab1]$ . oraenv
ORACLE_SID = [oracle] ? LAB11G
The Oracle base for ORACLE_HOME=/opt/app/oracle/database/product/11.2.0.4/db_home11g is /opt/app/oracle/database
[oracle@lab1]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 23 07:57:46 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

STEP 1: TAKE TABLESPACE OFFLINE – DOWNTIME STARTS HERE

SQL> ALTER TABLESPACE USERS OFFLINE;

tablespace altered.

STEP 2: MAKE CHANGES AT OS LAYER

SQL>!

[oracle@lab1]$ mv /u01/data/users01.dbf /u02/data/users01.dbf

STEP 3: UPDATE CONTROL FILE WITH NEW DATAFILE LOCATION

SQL> Alter database datafile ‘/u01/data/users01.dbf’ rename to ‘/u02/data/users01.dbf’;

STEP 4: BRING TABLESPACE ONLINE – DOWNTIME ENDS HERE

SQL> ALTER TABLESPACE USERS ONLINE;

tablespace altered.

Starting Oracle 12c, all the above steps can be completed with a single command. Let’s see with an example.

With Oracle 12c – STEP 1 & DONE:

[oracle@lab1]$ . oraenv
ORACLE_SID = [oracle] ? LAB12C_NC
The Oracle base for ORACLE_HOME=/opt/app/oracle/database/product/12.2.0.2/db_home12c is /opt/app/oracle/database1
[oracle@lab1]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 23 09:14:38 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> Alter database move datafile ‘/u01/data/users01.dbf’ to ‘/u02/data/users01.dbf’;

Database altered.

Benefits:

1. No Downtime required, so everyone (including client) is happy.
2. Online operation on the datafile can be performed while the datafile move is in progress.
3. No need for the DBA to move the file at OS layer, the command will take care of that. If the file already exists in target location, use the REUSE clause to overwrite it.

SQL> Alter database move datafile ‘/u01/data/users01.dbf’ to ‘/u02/data/users01.dbf’ REUSE;

4. If you want to copy the file, just use the KEEP clause so that it retain the original file at source.

SQL> Alter database move datafile ‘/u01/data/users01.dbf’ to ‘/u02/data/users01.dbf’ KEEP;

5. No need to update CONTROLFILE separately.

The real advantage of this is the ability to move datafile from Non-ASM to ASM and vice versa without downtime and without using RMAN.

SQL> Alter database move datafile ‘/u01/data/users01.dbf’ to ‘+DATA’;

Database altered.

Final Thoughts

Note: This command will throw ORA-01516 (Non Existent Log file, Data File etc.) when working with Pluggable Database, because we cannot move PDB’s datafile having current container set to CDB.

[oracle@lab1]$ . oraenv
ORACLE_SID = [oracle] ? LAB12C_C
The Oracle base for ORACLE_HOME=/opt/app/oracle/database/product/12.2.0.2/db_home_1_12c is /opt/app/oracle/database2
[oracle@lab1]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 23 09:14:38 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> alter database move datafile ‘/u01/data/users01.dbf’ to ‘/u02/data/users01.dbf’ REUSE;
alter database move datafile ‘/u01/data/users01.dbf’ to ‘/u02/data/users01.dbf’ REUSE
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file “17”

If we are working with CONTAINER DATABASES, in order to move files we must set our container to the PDB and run the command

SQL> alter session set container=pdb1;
Session altered.

SQL> alter database move datafile ‘/u01/data/users01.dbf’ to ‘/u02/data/users01.dbf’ REUSE;
Database altered.

SQL> alter session set container=CDB$ROOT;
Session altered.

About The Author

Sudipta Ganguly

Sudipta Ganguly has been an Oracle DBA for 18 years, predominantly focusing on Oracle Database Management and Administration with experience in Telecom, Insurance, Banking and Financial Services, Healthcare and Manufacturing based Applications. He has major interest in developing, planning & implementing High Availability Disaster Recovery Planning & Solutions, Data Center Migration, Database tuning and RMAN.