Skip to main content

Rename/move datafile online with oracle database 12c

Rename/Move data file online - This is the first new feature I am demonstration on Oracle database 12c new features. This is really a wonderful feature.

You cannot only move datafiles  form one location to another on normal file system, you can even move your datafiles  from NON-ASM to ASM Diskgrups:

In this article I will demonstrate rename/move :

1) Normal rename of datafille
2) Rename from normal location to ASM
3) Move from one ASM location to another
4) Move from ASM to Non-ASM


1) Normal rename of datafille


SQL> SQL> select name, STATUS  from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ ----------
/u01/12cDb_Home/oradata/prod/system01.dbf                    SYSTEM
/u01/12cDb_Home/oradata/prod/example01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/sysaux01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/undotbs01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/prod_data/my_data01.dbf         ONLINE
/u01/12cDb_Home/oradata/prod/users01.dbf                     ONLINE



6 rows selected.

SQL> alter database move datafile
  2  '/u01/12cDb_Home/oradata/prod/prod_data/my_data01.dbf'    
  3  to 
  4  '/u01/12cDb_Home/oradata/prod/mydata01.dbf';

Database altered.

SQL> select name, STATUS  from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ ----------
/u01/12cDb_Home/oradata/prod/system01.dbf                    SYSTEM
/u01/12cDb_Home/oradata/prod/example01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/sysaux01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/undotbs01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/mydata01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/users01.dbf                     ONLINE

6 rows selected.

SQL> 




2) Rename from normal location to ASM




SQL> select name, STATUS  from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ ----------
/u01/12cDb_Home/oradata/prod/system01.dbf                    SYSTEM
/u01/12cDb_Home/oradata/prod/example01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/sysaux01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/undotbs01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/mydata01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/users01.dbf                     ONLINE

6 rows selected.

SQL>



SQL> alter database move datafile
  2  '/u01/12cDb_Home/oradata/prod/mydata01.dbf'
  3  to
  4  '+DBDATA/prod/mydata01.dbf';

Database altered.

SQL> alter database move datafile
  2  '/u01/12cDb_Home/oradata/prod/users01.dbf'
  3  to 
  4  '+DBDATA/prod/users01.dbf';

Database altered.

SQL> select name, STATUS  from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ ----------
/u01/12cDb_Home/oradata/prod/system01.dbf                    SYSTEM
/u01/12cDb_Home/oradata/prod/example01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/sysaux01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/undotbs01.dbf                   ONLINE
+DBDATA/prod/mydata01.dbf                                    ONLINE
+DBDATA/prod/users01.dbf                                     ONLINE

6 rows selected.

SQL>



 3) Move from one ASM location to another



SQL> select name, STATUS  from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ ----------
/u01/12cDb_Home/oradata/prod/system01.dbf                    SYSTEM
/u01/12cDb_Home/oradata/prod/example01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/sysaux01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/undotbs01.dbf                   ONLINE
+DBDATA/prod/mydata01.dbf                                    ONLINE
+DBDATA/prod/users01.dbf                                     ONLINE
6 rows selected.



SQL> alter database move datafile 
  2  '+DBDATA/prod/mydata01.dbf'
  3  to '+DBDATA/prod/prod_data/mydata01.dbf';

Database altered.

SQL> alter database move datafile
  2  '+DBDATA/prod/users01.dbf'
  3  to
  4  '+DBDATA/prod/prod_data/users01.dbf';
Database altered.

SQL>  select name, STATUS  from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ ----------
/u01/12cDb_Home/oradata/prod/system01.dbf                    SYSTEM
/u01/12cDb_Home/oradata/prod/example01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/sysaux01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/undotbs01.dbf                   ONLINE
+DBDATA/prod/prod_data/mydata01.dbf                          ONLINE
+DBDATA/prod/prod_data/users01.dbf                           ONLINE

6 rows selected.

SQL>


4) Move from ASM to Non-ASM 



SQL>  select name, STATUS  from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ ----------
/u01/12cDb_Home/oradata/prod/system01.dbf                    SYSTEM
/u01/12cDb_Home/oradata/prod/example01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/sysaux01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/undotbs01.dbf                   ONLINE
+DBDATA/prod/prod_data/mydata01.dbf                          ONLINE
+DBDATA/prod/prod_data/users01.dbf                           ONLINE

6 rows selected.

SQL> alter database move datafile
  2  '+DBDATA/prod/prod_data/mydata01.dbf'
  3  to 
  4  '/u01/12cDb_Home/oradata/prod/mydata01.dbf';

Database altered.

SQL> alter database move datafile
  2  '+DBDATA/prod/prod_data/users01.dbf'
  3  to 
  4  '/u01/12cDb_Home/oradata/prod/users01.dbf';

Database altered.
SQL> select name, STATUS  from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ ----------
/u01/12cDb_Home/oradata/prod/system01.dbf                    SYSTEM
/u01/12cDb_Home/oradata/prod/example01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/sysaux01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/undotbs01.dbf                   ONLINE
/u01/12cDb_Home/oradata/prod/mydata01.dbf                    ONLINE
/u01/12cDb_Home/oradata/prod/users01.dbf                     ONLINE

6 rows selected.

SQL> 


SQL>



Labels:

Comments

rakesh said…
Great...
24 August 2013 at 03:42
Post a Comment

Popular posts from this blog

Disable Firewall on Oracle Linux 8

In this blogpost we will see how we can stop/disable the firewall on Oracle Linux 8, the firewall command is same in both linux 7 an linux 8. The below listed is the procedure for stopping and disabling the  firewall on Oracle Linux 8. - Here we can see the firewall deamon in active state - Here when we stop the firewall in previous command, now the firewall daemon is dead - For permanent disabling the firewall on server, we can use "disable" option The following commands will be helpful: #systemctl status firewalld #systemctl stop firewalld #systemctl disable firewalld #systemctl enable firewalld #systemctl start firewalld Hope it helps !! Thanks for reading :) regards, X A H E E R
3 comments
Read more

Enable Desktop on Oracle Solaris 11.4

Oracle Solaris 11 installation has multiple options to choose for installation of an Operating Environment, but mostly Oracle Solaris text install media is used and this installation media doesn't offer the GUI Desktop Environment by default after the installation. This blog post will explain how we can enable the desktop for Oracle Solaris 11.4 operating system, after the completion of installation. We have to install "solaris-desktop" package and reboot the machine and GUI desktop will be enabled for the Operating System. In this blog post my virtual machine is connected to the internet and hence I am able to use available pupblic repository for package installation, if in case internet is not available for the server/machine then we have configure the local/Server  repository for the installation. Follow the below steps for desktop package installation: After installation of dekstop package we are now able to login with GUI desktop environment...
1 comment
Read more

Installation of Oracle Applications R12.1.1 on Linux and vmware

The below article is step by step procedure to Install oracle E-Business suite R12.1.1 on Oracle Enterprise Linux 5.6 (32-bit) using vmware server 2 on windows 7 64 bit. This is a single node Installation. (All services will be hosting on same machine 0 db, conc, web, forms etc) I have Installed it on a virtual machine with 4gb ram, 120 GB of local disk space and 1 core cpu. - Requires 80 GB of free space for fresh installation (excluding stage area) - Requires 100+ GB of free space for vision installation (excl. stage) I will split this configuration in 5 steps: 1) create and configure virtual machine 2) Install linux operating system 3) create stage Area 4) Perform all operating system per-requisites 5) Install oracle E-business suite R12.1.1 create and configure virtual machine: >> Login to vmware Server console >> click on add virtual machine >> Here i have selected iso image for OS Installation, you can select local cd drive if u want. >> Now the virtual m...
81 comments
Read more