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>



Comments

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

Popular posts from this blog

Installation of Oracle Applications R12.1.1 on Linux and vmware

-
Image
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...
Read more

Oracle AVDF Installation and Setup Document

-
Image
This blogpost will provide you detailed information about Oracle Audit Vault and Database Firewall (Oracle AVDF) setup. Oracle AVDF is a comprehensive Database Activity Monitoring (DAM) solution that integrates with native audit data. Environment Setup: [oracrp@ebs-dev2-db01 ~]$ mkdir -pv /oradb/oracle/avcli mkdir: created directory ‘/oradb/oracle/avcli’ [oracrp@ebs-dev2-db01 ~]$ mkdir -pv /oradb/oracle/avagent mkdir: created directory ‘/oradb/oracle/avagent’ [oracrp@ebs-dev2-db01 ~]$ - Add these variables to the environment file vi DEV2CDB.env # AVS export AVCLI_HOME="/oradb/oracle/avcli" export AV_HOME="/oradb/oracle/avagent" export PATH="$PATH:$AV_HOME/bin" Download the JAR files:   AVDF installation requires one network interface card on respective hosts. IP assigned to AV server NIC will communicate with target databases and IP assigned to DF server will connect to AV Download - Agent jar file:   Login to AV console as avadmin user   - Go t...
Read more

ntp service in Maintenance mode Solaris 10

-
Hi, On one of our newly configured solaris 10 server, successfully configured a ntp client. But when trying to start ntp service its getting failed and going into maintance mode. Due to this there a 5 minutes of time difference between this server and other servers. bash-3.00$ svcs ntp maintenance 16:12:09 svc:/network/ntp:default When we check in detail: bash-3.00$ svcs -x ntp svc:/network/ntp:default (Network Time Protocol (NTP)) State: maintenance since Sun Jan 08 08:12:07 2012 Reason: Start method failed repeatedly, last exited with status 1. See: http://sun.com/msg/SMF-8000-KS See: xntpd(1M) See: ntpdate(1M) See: ntpq(1M) See: /var/svc/log/network-ntp:default.log Impact: This service is not running. When we check logfile: [ Aug 11 13:11:15 Executing start method ("/lib/svc/method/xntp") ] /sbin/sh: /lib/svc/method/xntp: not found [ Aug 11 13:11:15 Method "start" exited with status 1 ] Stuck !!!!! Don't worry if you encounter same e...
Read more