12c New Features - Data file move without taking tablespace read only or offline.

12c New Features - Data file move without taking tablespace read only or offline.

one of the features of 12.1.0 is datafile rename online. so from 12.1.0 no longer we need to take tablespace offline or readonly. It can be done online.

I have tested this in normal database to understand the behaviour easily.

database version: 12.1.0

file management: file system

SQL> 1
  1* select file_name,tablespace_name,status from dba_data_files where tablespace_name='USERS'
SQL> /

FILE_NAME                             TABLESPACE_NAME            STATUS
------------------------------------------------------------ ------------------------------ ---------
/media/12c_db/oradata/ora12cdb/loc2/users02.dbf          USERS                AVAILABLE
/media/12c_db/oradata/ora12cdb/users01.dbf             USERS                AVAILABLE
/media/12c_db/oradata/ora12cdb/loc2/users03.dbf          USERS                AVAILABLE

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME            STATUS
------------------------------ ---------
USERS                   ONLINE

SQL>
SQL> select * from v$version;

BANNER                               CON_ID
-------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.        0
1.0.1.0 - 64bit Production

PL/SQL Release 12.1.0.1.0 - Production                0
CORE    12.1.0.1.0    Production                    0
TNS for Linux: Version 12.1.0.1.0 - Production            0
NLSRTL Version 12.1.0.1.0 - Production                0

SQL> alter database move datafile '/media/12c_db/oradata/ora12cdb/loc2/users02.dbf' to '/media/12c_db/oradata/ora12cdb/users02.dbf';  

Database altered.

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME            STATUS
------------------------------ ---------
USERS                   ONLINE

SQL> select file_name,tablespace_name,status from dba_data_files where tablespace_name='USERS';

FILE_NAME                             TABLESPACE_NAME            STATUS
------------------------------------------------------------ ------------------------------ ---------
/media/12c_db/oradata/ora12cdb/users01.dbf             USERS                AVAILABLE
/media/12c_db/oradata/ora12cdb/loc2/users03.dbf          USERS                AVAILABLE
/media/12c_db/oradata/ora12cdb/users02.dbf             USERS                AVAILABLE
 

alert log

alter database move datafile '/media/12c_db/oradata/ora12cdb/loc2/users02.dbf' to '/media/12c_db/oradata/ora12cdb/users02.dbf' Sat Jun 29 20:58:00 2013 Moving datafile /media/12c_db/oradata/ora12cdb/loc2/users02.dbf (5) to /media/12c_db/oradata/ora12cdb/users02.dbf Move operation committed for file /media/12c_db/oradata/ora12cdb/users02.dbf Completed: alter database move datafile '/media/12c_db/oradata/ora12cdb/loc2/users02.dbf' to '/media/12c_db/oradata/ora12cdb/users02.dbf' $

12c feature: Pluggable Databases

The below demo is created on the basis of the following reference link

Oracle Database 12c: New Features - Pluggable Databases- by mikerajendran

details provided by mikerajendran in his blog

CDB vs. PDB

CDB is an acronym for “Container Database” and PDB is an acronym for “Pluggable Database”. I think it will be easier to explain with a metaphor for DBAs. Think of a freight train with many cars up to 250. Each container could be having different contents, with delivery target for different customers and completely packed/sealed independently with customer options but the entire freight is carried by a single engine or carrier at the front. It will be stupid enough to run 250 freight trucks but rather it is efficient to consolidate them into a single freight train. When running independently we will spend on gas, drivers’ expenses and much more complicated to manage them. The freight train is basically the CDB and each car is the PDB.

PDB is fully backward compatible to pre-12.1 database releases. There is nothing different from a developer or application connectivity perspective. Everything stays the same but the PDB will belong to a single CDB. When application connects to the PDB, it will specify the destination PDB via a database service. All home-grown or third party applications typically will have connectivity defined out of the application so it is easier to just change the service name outside of the application code. So all database connectivity should use “database service” rather than using the legacy approach of ORACLE_SID based connectivity. ORACLE_SID ties the application connectivity to a specific database instance and does not give the scalability or high availability. You can have many pluggable databases in 12.1.

So now we will see the creation of pluggabble database
I am using dbca to create the pluggable database as it is first time.
I got to see the process and it creating normal database then it converting the database as plugabble database

create pluggable database PDB$SEED as clone  using '/media/12c_db/product/12.1.0/dbhome_1/assistants/dbca/templates//pdbseed.xml'  source_file_nam
e_convert = ('/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/temp01.dbf','/media/12c_db/oradata/ORA12CC1/datafile/pdbseed_temp01.dbf',
'/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/system01.dbf','/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_system_8wz2hq6d_.dbf',
'/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/sysaux01.dbf','/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_sysaux_8wz2hq68_.dbf') file_name_c
onvert=NONE  NOCOPY
Sat Jun 29 18:30:39 2013
****************************************************************
Pluggable Database PDB$SEED with pdb id - 2 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Database Characterset is US7ASCII
Deleting old file#2 from file$ 
Deleting old file#4 from file$ 
Adding new file#5 to file$(old file#2) 
Adding new file#7 to file$(old file#4) 
ALTER SYSTEM: Flushing buffer cache inst=0 container=2 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB$SEED with pdb id - 2 is now marked as NEW.
****************************************************************
Completed: create pluggable database PDB$SEED as clone  using '/media/12c_db/product/12.1.0/dbhome_1/assistants/dbca/templates//pdbseed.xml'  sour
ce_file_name_convert = ('/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/temp01.dbf','/media/12c_db/oradata/ORA12CC1/datafile/pdbseed_temp01.dbf
',
'/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/system01.dbf','/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_system_8wz2hq6d_.dbf',
'/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/sysaux01.dbf','/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_sysaux_8wz2hq68_.dbf') file_name_c
onvert=NONE  NOCOPY
alter pluggable database PDB$SEED open restricted

once created you can see this parameter is enabled
  enable_pluggable_database= TRUE

SQL> set pages 1000

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

--------------- ----------------

HOST_NAME

----------------------------------------------------------------

VERSION  STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT

----------------- --------- ------------ --- ---------- ------- ---------------

LOGINS   SHU DATABASE_STATUS INSTANCE_ROLE    ACTIVE_ST BLO     CON_ID

---------- --- ----------------- ------------------ --------- --- ----------

INSTANCE_MO EDITION

----------- -------

FAMILY

--------------------------------------------------------------------------------

     1 ora12cc1

ovs1.co.nz

12.1.0.1.0  29-JUN-13 OPEN NO      1 STOPPED

ALLOWED    NO  ACTIVE PRIMARY_INSTANCE   NORMAL    NO   0

REGULAR     EE

$ cat initora12cc1.ora

ora12cc1.__data_transfer_cache_size=0

ora12cc1.__db_cache_size=402653184

ora12cc1.__java_pool_size=16777216

ora12cc1.__large_pool_size=50331648

ora12cc1.__oracle_base='/media/12c_db'#ORACLE_BASE set from environment

ora12cc1.__pga_aggregate_target=503316480

ora12cc1.__sga_target=738197504

ora12cc1.__shared_io_pool_size=0

ora12cc1.__shared_pool_size=251658240

ora12cc1.__streams_pool_size=0

*.audit_file_dest='/media/12c_db/admin/ora12cc1/adump'

*.audit_trail='db'

*.compatible='12.1.0.0.0'

*.control_files='/media/12c_db/oradata/ORA12CC1/controlfile/o1_mf_8wz2gv8x_.ctl','/media/12c_db/fast_recovery_area/ORA12CC1/controlfile/o1_mf_8wz2gvog_.ctl'

*.db_block_size=8192

*.db_create_file_dest='/media/12c_db/oradata'

*.db_domain=''

*.db_name='ora12cc1'

*.db_recovery_file_dest='/media/12c_db/fast_recovery_area'

*.db_recovery_file_dest_size=4800m

*.diagnostic_dest='/media/12c_db'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora12cc1XDB)'

*.enable_pluggable_database=true

*.memory_target=1177m

*.open_cursors=300

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

SQL> COLUMN "RESTRICTED" FORMAT A10
SQL> select v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
  2  from v$PDBs v inner join dba_pdbs d
  3   using (GUID)
  4  order by v.create_scn
  5  /

NAME                   OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------------
PDB$SEED               READ ONLY  NO         NORMAL
ORA12CP1               MOUNTED      n/a         NORMAL


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

NAME
--------------------------------------------------------------------------------
       TS# STATUS
---------- -------
/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_system_8wz29n4m_.dbf
     0 SYSTEM

/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_sysaux_8wz26nvn_.dbf
     1 ONLINE

/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_undotbs1_8wz2dnof_.dbf
     2 ONLINE


NAME
--------------------------------------------------------------------------------
       TS# STATUS
---------- -------
/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_system_8wz2hq6d_.dbf
     0 SYSTEM

/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_users_8wz2dmgk_.dbf
     4 ONLINE

/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_sysaux_8wz2hq68_.dbf
     1 ONLINE


NAME
--------------------------------------------------------------------------------
       TS# STATUS
---------- -------
/media/12c_db/oradata/ORA12CC1/E05626A829151520E0430F02000A6A84/datafile/o1_mf_s
ystem_8wz2y8z4_.dbf
     0 SYSTEM

/media/12c_db/oradata/ORA12CC1/E05626A829151520E0430F02000A6A84/datafile/o1_mf_s
ysaux_8wz2y92h_.dbf
     1 ONLINE

/media/12c_db/oradata/ORA12CC1/E05626A829151520E0430F02000A6A84/datafile/o1_mf_u

NAME
--------------------------------------------------------------------------------
       TS# STATUS
---------- -------
sers_8wz30lod_.dbf
     3 ONLINE

/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_users_8wz81r9g_.dbf
     4 ONLINE


10 rows selected.


I will post little more examples on pluggable database. This above is just creation of pluggable database.

ORACLE-BASE - Oracle Database 12c Release 1 (12.1) Installation On Oracle Linux 6

ORACLE-BASE - Oracle Database 12c Release 1 (12.1) Installation On Oracle Linux 6

Please click the above link. I used the above link as reference and I have did the following installation in oracle linux 6

I have unziped the linuxamd64_12c_database* to database.

for pre-requiste I simply did 11G pre-requiste and the prequiste completed sucessfully for 12c database install.

so you can go for 

yum install

or

yum install oracle-rdbms-server-11gR2-preinstall -y

running root scripts 

$ su - root
Password: 
[root@ovs1 ~]# /media/12c_db/product/12.1.0/dbhome_1/root.sh
Performing root user operation for Oracle 12c 

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /media/12c_db/product/12.1.0/dbhome_1

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.

12cR1 installation done.

Please say your comments and let me know if you need any different scenario.

adding swap space in linux

Adding swap space in linux

easy  way is to use dd and add it on


1. decide the mountpoint where you are going to create swapspace. In my example, I plan to add swapspace in "/media/db_disk" mount point.

cd /media/db_disk
as root user


[root@ovs1 Desktop]#  dd if=/dev/zero of=/media/db_disk/myswapfile3 bs=1M count=2048
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 74.1111 s, 29.0 MB/s

checking the swap file

[root@ovs1 Desktop]# ls -l /media/db_disk/myswapfile3
-rw-r--r--. 1 root root 2147483648 Jun 28 15:45 /media/db_disk/myswapfile3

change the permission of the swap file

[root@ovs1 Desktop]# ls -l /media/db_disk/myswapfile3
-rw-------. 1 root root 2147483648 Jun 28 15:45 /media/db_disk/myswapfile

run mkswap command to format and ready for swap file use

[root@ovs1 Desktop]# mkswap /media/db_disk/myswapfile3
Setting up swapspace version 1, size = 2097148 KiB
no label, UUID=c0928bcc-ffe3-47c1-9051-a9bf43c14228


add the swapfile to swap group

[root@ovs1 Desktop]# swapon /media/db_disk/myswapfile3
[root@ovs1 Desktop]# swapon -s
Filename                Type        Size    Used    Priority
/dev/dm-1                               partition    2064380    0    -1
/media/grid/swapfile1                   file        1048572    0    -2
/media/db_disk/myswapfile3              file        2097148    0    -3


now important thing is that we need to add this swapfile in automount list. otherwise after reboot it wont get appear in the swap list


/etc/fstab is the file which is readed to do automount during startup.

# /etc/fstab
# Created by anaconda on Thu Jan 12 13:21:03 2012
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/vg_oraclelinux6-lv_root /                       btrfs    defaults        1 1
UUID=ed6b5002-07d3-4381-9057-47ee31704c78 /boot                   ext4    defaults        1 2
/dev/sde                /media/grid             ext4    defaults        0 0
/dev/sdf                /media/db_disk          ext4    defaults        0 0
/dev/sdg                /media/12c_db           ext4    defaults        0 0
/dev/mapper/vg_oraclelinux6-lv_swap swap                    swap    defaults        0 0
/media/grid/swapfile1   swapfile1               swap  defaults        0 0
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0

now add the following line in /etc/fstab


/media/db_disk/myswapfile3 swapfile3            swap  defaults        0 0



[root@ovs1 Desktop]#
[root@ovs1 Desktop]# cat /etc/fstab

#
# /etc/fstab
# Created by anaconda on Thu Jan 12 13:21:03 2012
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/vg_oraclelinux6-lv_root /                       btrfs    defaults        1 1
UUID=ed6b5002-07d3-4381-9057-47ee31704c78 /boot                   ext4    defaults        1 2
/dev/sde                /media/grid             ext4    defaults        0 0
/dev/sdf                /media/db_disk          ext4    defaults        0 0
/dev/sdg                /media/12c_db           ext4    defaults        0 0
/dev/mapper/vg_oraclelinux6-lv_swap swap                    swap    defaults        0 0
/media/grid/swapfile1   swapfile1               swap  defaults        0 0
/media/db_disk/myswapfile3 swapfile3            swap  defaults        0 0
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
[root@ovs1 Desktop]#


now after restart the swap file will get automatically mounted