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

Database Creation using DBCA in 12 c

In one of my previous post I have shown you how to create Oracle 11g database using DBCA . In this post i am going to show you database creation using DBCA in 12c .

Oracle has recently made available 12c database for download and you can download it using following link 

Oracle Database 12c Download

For installation steps of 12c database you can click Installation of Oracle Database 12 c r1 on RHEL 6.1

Well , lets talk about DBCA of 12c version . Oracle has made significant changes in DBCA interactive interface and now you have two ways to create the database in DBCA .First is basic mode that is nothing but with default configuration . We have other mode called "advanced " mode where you will get more options to customize your database creation .

One more significant addition is PLUGGABLE database option . (Please read Oracle Docs for pluggable Database option) . The main database you will create is called as container database . You can not keep same name for pluggable database and container database . Let oracle announce all the new features of 12c for discussion on pluggable database to be more correct .

Installation of Oracle Database 12 c r1 on RHEL 6.1

This post shows Installation of Oracle database 12c r1 (12.1.0.1) on Red Hat Linux 6.1 64 bit OS .
For this installation I have installed RHEL 6.1 selecting all the packages available .

I will install Oracle RDBMS in  /orabin mount point under the path /orabin/app/oracle/product/12.0.1 . This path is nothing but the ORACLE HOME. For ORACLE BASE the path will be /orabin/app/oracle/product

We will create OINSTALL group which will install the software and DBA which can create the database .

Let us make the path in OS

[root@linux6 orabin]# mkdir -p /orabin/app/oracle/product/12.0.1
[root@linux6 orabin]# groupadd oinstall
[root@linux6 orabin]# groupadd dba
[root@linux6 orabin]# useradd -g oinstall -G dba -d /home/oracle -m oracle
[root@linux6 orabin]# passwd oracle
Changing password for user oracle.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

[root@linux6 orabin]# chown -R oracle:oinstall /orabin
[root@linux6 orabin]# chown -R oracle:dba /data1 /data2

Oracle RDBMS software has been kept in /opt hence set permission to /opt so that we can unzip the RDBMS binaries .

[root@linux6 orabin]# chmod -R 777 /opt

after you finish unzip of files you will get single directory as "database" .

Now we will set the bash profile for our installation.

[root@linux6 opt]# su - oracle
[oracle@linux6 ~]$ vi .bash_profile

export ORACLE_HOME=/orabin/app/oracle/product/12.0.1
export ORACLE_SID=orcl
export PATH=$PATH:$HOME/bin
export PATH=$ORACLE_HOME/bin:$PATH:.

After setting the bash profile we can start the installation of Oracle Database.

OUI is GUI based installar hence for Allowing normal user to invoke GUI, run following command as root user.

[root@linux1 ~]# xhost +

In following Snapshots installer did not give pre-requisite check window but my installation got succeeded .  

Now its time to invoke OUI i.e runInstallar

[oracle@linux1 database]$ ./runInstallar

Oracle Database Creation Using DBCA

We have many ways to create Oracle Database and we generally use following two ways.
1.Manually (Read It Here) 
2.Using DBCA

My previous post shows how to create database manually .This post will show you how to create database through DBCA.

DBCA - This is a utility provided by Oracle to create database in GUI mode. Once you invoke dbca it will guide you step by step in a interactive mode to complete  database creation. You will also get option to configure dbcontrol along with database creation.

For invoking dbca you wil l have to set oracle home properly or you can directly run the utility from $ORACLE_HOME/bin . Make sure that before invoking dbca you have issued "xhost +" command from root prompt.

$ dbca

 This is the welcome page - click next

 Here you can choose general purpose database or can go for custom database which will provide you lot more option to choose . If you choose custom database then you can greatly customize your database . I will choose general purpose database.

 Here you have option to decide on SID and database name. You can keep same or keep different values.

 In this window you have choice to configure db console along with db creation . Db console will give you chance to manage database in GUI mode through the browser. If you don't want to configure db console then uncheck the radio button . I chose to go with db console.

 Choose to give different password for each user or give same password for all users.

 If you give a weak password then this warning will be shown. Choose "yes" .

 I will keep all the database files in single location on file system so i checked "use common location for all database files". Here you can also choose ASM .

 You can configure FRA and can enable archival . If you choose to any of the option or both then you have the option to set the path also.

 Choose the path for archive log destination.

 You can choose to install the sample schemas in your database .

 Here you  can choose the desired value of memory size with which your instance will allocate size of SGA and PGA. This also gives you the option to go for automatic memory management which will relax you from manually sizing the memory components .

 Set the maximum number of processes that will be allowed to run for Oracle instance.

 Choose the character set and click next.

 You can have a look on default parameters setting and can also edit there values.

 Choose the connection mode on your database. Details are given with each option .

 You can edit the locations , number and sizes of contolfiles, datafiles and redolog groups here.

 Click on finish to start the database creation.

Here the window shows you the summary of your settings that will be used for database creation .
Now the database creation will start.

After completion of DB creation you can see the result and can note the URL to access the dbconsole.
Click exit to close the DBCA.

..........................  :)