Visit Counter

Thursday, November 21, 2013

Oracle 12c Database Online Move Datafile


Prior to Oracle 12c, if you wanted to move a database’s file, you either had to shutdown the database, or take the datafile/tablespace offline. Here is an example of the steps you might take

SQL> ALTER TABLESPACE test OFFLINE;
SQL> !mv  /ora/test1/test01.dbf    /ora/test2/test01.dbf
SQL> ALTER DATABASE RENAME FILE ‘/ora/test1/test01' TO ‘/ora/test2/test01.dbf’;
SQL>ALTER TABLESPACE test ONLINE;


The good news is that Oracle 12cR1 now offers the ability to move entire datafiles between different storage locations without ever having to take the datafiles offline. The datafiles being moved remain completely accessible to applications in almost all situations, including querying against or performing DML and DDL operations against existing objects, creating new objects, and even rebuilding indexes online. Online Move Datafile (OMD) also makes it possible to migrate a datafile between non-ASM and ASM storage (or vice-versa) while maintaining transparent application access to that datafile’s underlying database objects. OMD is completely compatible with online block media recovery, the automatic extension of a datafile, the modification of a tablespace between READ WRITE and READ ONLY mode, and it even permits backup operations to continue against any datafiles that are being moved via this feature.




[oracle@oracle12c bin]$ export ORACLE_SID=cdb1

[oracle@oracle12c bin]$ pwd

/ora/oracle/app/oracle/product/12.1.0/dbhome_1/bin

[oracle@oracle12c bin]$ ./sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Fri 6 Sept 22 01:38:02 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.


SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT


SQL> alter session set container=pdb1;

Session altered.


 SQL> show pdbs;

    CON_ID CON_NAME  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1  READ WRITE NO




SQL> select file_name from dba_data_Files;

FILE_NAME
--------------------------------------------------------------------------------
/ora/oracle/app/oracle/oradata/cdb1/pdb1/system01.dbf
/ora/oracle/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
/ora/oracle/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf




SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> select tablespace_name from dba_data_Files;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
USERS

Creating Tablespace user_data


SQL> create tablespace user_dat 
  2  datafile '/ora/oracle/app/oracle/oradata/cdb1/pdb1/userdat.dbf'
  3  size 1g;

Tablespace created.

SQL> select tablespace_name from dba_data_files;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
USERS
USER_DAT


Inserting Rows in test table

SQL> create table system.test(id number) tablespace user_dat;

Table created.

SQL> insert into system.test values(05);

1 row created.

SQL> commit;

Commit complete.

Move Tablespace datafile to another location


SQL> alter database move datafile
  2  '/ora/oracle/app/oracle/oradata/cdb1/pdb1/userdat.dbf' to
  3  '/ora/oracle/app/oracle/oradata/cdb1/movefile/userdat.dbf';

Database altered.


SQL> insert into system.test values(20);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from system.test;

ID
----------
5
20

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@oracle12c bin]$ pwd
/ora/oracle/app/oracle/product/12.1.0/dbhome_1/bin
oracle@oracle12c bin]$ cd /

oracle@oracle12c cdb1]$ ls

control01.ctl  pdb1.xml  pdbseed     redo03.log    temp01.dbf
movefile       pdb2      redo01.log  sysaux01.dbf  undotbs01.dbf
pdb1           pdb3      redo02.log  system01.dbf  users01.dbf



oracle@oracle12c cdb1]$ cd movefile

oracle@oracle12c movefile]$ pwd

/ora/oracle/app/oracle/oradata/cdb1/movefile

oracle@oracle12c movefile]$ ls

userdat.dbf


[oracle@oracle12c movefile]$ cd ..

[oracle@oracle12c cdb1]$ cd pdb1

[oracle@oracle12c pdb1]$ ls

pdb1_users01.dbf  
sysaux01.dbf  
system01.dbf  
temp01.dbf