Moving Online Datafile To a New Location in 12c

Oracle has officially announced 12c database on 10th of july . This 12 c database is having around 500 new features and according to Oracle Corp this is true cloud database. Whatever, we will see the demonstration of some new features of 12c database in coming posts . Here i am going to show you how you can move a datafile which is online .

Before 12c database you needed to make the tablespace offline while moving any of its datafile .

But from 12c release you can move datafile while its online .

SQL> col file_name for a70
SQL> set lines 200
SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                           TABLESPACE_NAME
-------------------------------------------------- ------------------
/data1/oradata/orcl/system01.dbf                       SYSTEM
/data1/oradata/orcl/sysaux01.dbf                       SYSAUX
/data1/oradata/orcl/users01.dbf                        USERS
/data1/oradata/orcl/undotbs01.dbf                      UNDOTBS1

SQL> alter database move datafile '/data1/oradata/orcl/users01.dbf' to '/data2/oradata/orcl/users01.dbf';

Database altered.

SQL>  select file_name,tablespace_name from dba_data_files;

FILE_NAME                                             TABLESPACE_NAME
---------------------------------------------------- ------------------
/data1/oradata/orcl/undotbs01.dbf                       UNDOTBS1
/data1/oradata/orcl/sysaux01.dbf                        SYSAUX
/data1/oradata/orcl/system01.dbf                        SYSTEM
/data2/oradata/orcl/users01.dbf                         USERS

This is what got recorded in alert log about this operation

alter database move datafile '/data1/oradata/orcl/users01.dbf' to '/data2/oradata/orcl/users01.dbf'
Tue Jul 16 00:42:51 2013
Moving datafile /data1/oradata/orcl/users01.dbf (6) to /data2/oradata/orcl/users01.dbf
Move operation committed for file /data2/oradata/orcl/users01.dbf
Completed: alter database move datafile '/data1/oradata/orcl/users01.dbf' to '/data2/oradata/orcl/users01.dbf'



EVEN YOU CAN MOVE SYSTEM DATAFILE TOO.......

SQL> col file_name for a70
SQL> set lines 200
SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                                TABLESPACE_NAME
----------------------------------------------------- ---------------------
/data1/oradata/orcl/undotbs01.dbf                          UNDOTBS1
/data1/oradata/orcl/sysaux01.dbf                           SYSAUX
/data1/oradata/orcl/system01.dbf                           SYSTEM
/data2/oradata/orcl/users01.dbf                            USERS

SQL> alter database move datafile '/data1/oradata/orcl/system01.dbf' to '/data2/oradata/orcl/system01.dbf';

Database altered.

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                              TABLESPACE_NAME
------------------------------------------------- -------------------
/data2/oradata/orcl/system01.dbf                           SYSTEM
/data1/oradata/orcl/undotbs01.dbf                          UNDOTBS1
/data1/oradata/orcl/sysaux01.dbf                           SYSAUX
/data2/oradata/orcl/users01.dbf                            USERS

This is what got recorded in alert log about this operation


Tue Jul 16 00:51:17 2013
alter database move datafile '/data1/oradata/orcl/system01.dbf' to '/data2/oradata/orcl/system01.dbf'
Tue Jul 16 00:51:17 2013
Moving datafile /data1/oradata/orcl/system01.dbf (1) to /data2/oradata/orcl/system01.dbf
Tue Jul 16 00:52:10 2013
Move operation committed for file /data2/oradata/orcl/system01.dbf
Completed: alter database move datafile '/data1/oradata/orcl/system01.dbf' to '/data2/oradata/orcl/system01.dbf'

Configuration of Oracle Enterprise Manager Database Express in 12c

We can access Oracle Database in GUI mode by configuring OEM database control or Grid/Cloud control through browser. 

OEM database control allows to manage one database in GUI mode and Grid/Cloud control allows you to manage hundreds or thousands of targets through browser . 

From 12c database you can enjoy the GUI features of Oracle by configuring OEM Database Express. Till now (Not confirmed) Oracle has removed database control from 12c database and has introduced OEM Database Express .  As per my own experience OEM Database Express has limited features than the OEM Database control .

I have 12c database installed on RHEL 6.1 guest machine installed in VMware .
Learn here Installation of Oracle Database 12 c r1 on RHEL 6.1

I have access to RHEL guest machine from host Windows 7 through IP 192.168.71.131 and will be using this IP to access OEM DB express . You can also access OEM DB express 12c in Guest RHEL browser but you should have Latest Flash player installed in browser . I did not  have so i chose to access OEM DB express from host OS windows 7 . 

Following are the steps to configure OEM Database Express . Make Sure you have a running listener so that OEM can connect to DB through this listener .

[oracle@linux6 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 06-JUL-2013 19:28:57

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux6)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                06-JUL-2013 18:43:22
Uptime                    0 days 0 hr. 45 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /orabin/app/oracle/product/12.1.0/network/admin/listener.ora
Listener Log File         /orabin/app/oracle/product/diag/tnslsnr/linux6/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux6)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=linux6)(PORT=5500))(Security=(my_wallet_directory=/orabin/app/oracle/product/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully


[root@linux6 Desktop]# xhost +
access control disabled, clients can connect from any host

[oracle@linux6 ~]$ dbca