Thursday, January 9, 2014

Resizing Virtual Disks of VirtualBox

I have a virtual box version 4.3.4 64bit installed in Windows7 64bit. As a virtual host there is 64bit Redhat6.2 Enterprise Server and an Oracle Database 11.2.0.3 installed on it. The case is, I need more disk space in my virtual host for the Oracle Database datafiles. I have already assigned three virtual disks and I dont want to add more disks but to extend the existing ones. After extending the virtual disks of the Virtualbox then I need to extend the physical volumes and the logical volumes and then the filesystems.

Hence there is not any user interface functionality, starting from Virtualbox 4.0 there is a way to extend the .vdi virtual disk sizes. "VboxManage" supports to extend and change the .vdi virtual disk attributes. "VboxManage" executable can be found in the following paths of the related operating systems.


--Using Windows as a Host
# "C:\Program Files\Oracle\VirtualBox\VboxManage.exe" modifyhd virtual_hard_disk_name.vdi --resize size_in_mb

--Using Mac as a Host
# /Applications/VirtualBox.app/Contents/MacOS/VBoxManage modifyhd virtual_hard_disk_name.vdi --resize size_in_mb

--Using Linux as a Host
# VBoxManage modifyhd virtual_hard_disk_name.vdi --resize size_in_mb

# "C:\Program Files\Oracle\VirtualBox\VboxManage.exe" list hdds
UUID:           f7a2fda3-c730-465a-b022-8ee89e48a093
Parent UUID:    base
State:          locked write
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_root.vdi
Storage format: VDI
Capacity:       10240 MBytes

UUID:           2be1f81e-b86b-41fe-9268-8622b5f4e4ec
Parent UUID:    base
State:          locked write
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_oracle01.vdi
Storage format: VDI
Capacity:       20480 MBytes

UUID:           e62699cc-19dc-4e34-bf09-8e1a9ac6b415
Parent UUID:    base
State:          locked write
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_oracle02.vdi
Storage format: VDI
Capacity:       20480 MBytes

UUID:           2d2d2490-d8a3-4184-bf54-02d874264f1b
Parent UUID:    base
State:          locked write
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_oracle03.vdi
Storage format: VDI
Capacity:       20480 MBytes

UUID:           3d4385bf-ac3b-4ae7-a3f3-13c14fac4ff5
Parent UUID:    base
State:          locked write
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_oracle04.vdi
Storage format: VDI
Capacity:       30720 MBytes

Virtual drives of the Virtualbox can be extended by using the following command (Please refer to your installation directory of the virtualbox to locate and execute the VboxManage.exe). VboxManage accepts either the full path of the virtual disk or the UUID of the disk. I prefered the UUID to extend.


# "C:\Program Files\Oracle\VirtualBox\VboxManage.exe" modifyhd 2d2d2490-d8a3-4184-bf54-02d874264f1b --resize 30170
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

As you can examine from the following output that the disk UUID 2d2d2490-d8a3-4184-bf54-02d874264f1b is extended from 20480MB to 30720MB successfully.


# "C:\Program Files\Oracle\VirtualBox\VboxManage.exe" list hdds
UUID:           f7a2fda3-c730-465a-b022-8ee89e48a093
Parent UUID:    base
State:          created
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_root.vdi
Storage format: VDI
Capacity:       10240 MBytes

UUID:           2be1f81e-b86b-41fe-9268-8622b5f4e4ec
Parent UUID:    base
State:          created
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_oracle01.vdi
Storage format: VDI
Capacity:       20480 MBytes

UUID:           e62699cc-19dc-4e34-bf09-8e1a9ac6b415
Parent UUID:    base
State:          created
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_oracle02.vdi
Storage format: VDI
Capacity:       20480 MBytes

UUID:           2d2d2490-d8a3-4184-bf54-02d874264f1b
Parent UUID:    base
State:          created
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_oracle03.vdi
Storage format: VDI
Capacity:       30720 MBytes

UUID:           3d4385bf-ac3b-4ae7-a3f3-13c14fac4ff5
Parent UUID:    base
State:          created
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_oracle04.vdi
Storage format: VDI
Capacity:       30720 MBytes

The rest of the operations will be held in the virtual host, which is RHEL6.2 64bit with logical volume manager used.

First /dev/sd* devices should be rescanned. I used /sbin/partprobe and then a reboot because all my devices where mounted. In order not to reboot, all devices should be unmounted first.

After the reboot, checking with fdisk gives the updated disk size of 30Gb. But the problem is the partition itself. The partition is still 20G and there is a way to extend it which is simply removing and recreating it. But, the thing is, i dont want to lose the data inside this partition which is a part of my volume group. After some investigation, I learned that if I delete the partition and create it with same SystemID (8e - Linux LVM) and stating from the very same starting block my data should not be lost. This worths trying.


# fdisk /dev/sdc

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): p

Disk /dev/sdc: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00079340

   Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1        2611    20970496   8e  Linux LVM

Command (m for help): d
Selected partition 1

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-3916, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-3916, default 3916):
Using default value 3916

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): p

Disk /dev/sdc: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00079340

   Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1        3916    31454246   8e  Linux LVM

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.

WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table. The new table will be used at
the next reboot or after you run partprobe(8) or kpartx(8)
Syncing disks.

After the reboot it seems the partition is extended successfully and my LVM works fine. Next, I should extend my physical volume pv's.


# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             9.9G  7.1G  2.3G  76% /
tmpfs                 2.0G  100K  2.0G   1% /dev/shm
/dev/mapper/vg_oracle-lv_oracle
                       30G   17G   12G  61% /oracle
/dev/mapper/vg_oracle-lv_oradata
                       60G   53G  3.2G  95% /oradata
shmfs                 2.0G  100K  2.0G   1% /dev/shm
shared                120G   94G   26G  79% /media/sf_shared
/dev/sr0               62M   62M     0 100% /media/VBOXADDITIONS_4.3.4_91027

# pvresize /dev/sdc1 30720
  Physical volume "/dev/sdc1" changed
  Failed to read physical volume "30720"
  1 physical volume(s) resized / 0 physical volume(s) not resized

# pvdisplay /dev/sdc1
  --- Physical volume ---
  PV Name               /dev/sdc1
  VG Name               vg_oracle
  PV Size               30.00 GiB / not usable 986.00 KiB
  Allocatable           yes
  PE Size               4.00 MiB
  Total PE              7679
  Free PE               2560
  Allocated PE          5119
  PV UUID               bkjcdV-ZOMc-0pSe-BfcW-pbny-ka4f-MiDBFO

# vgdisplay
  --- Volume group ---
  VG Name               vg_oracle
  System ID           
  Format                lvm2
  Metadata Areas        4
  Metadata Sequence No  21
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                2
  Open LV               2
  Max PV                0
  Cur PV                4
  Act PV                4
  VG Size               109.98 GiB
  PE Size               4.00 MiB
  Total PE              28156
  Alloc PE / Size       23015 / 89.90 GiB
  Free  PE / Size       5141 / 20.08 GiB
  VG UUID               9ERVOu-ATwS-j7H5-5vce-PLj9-3aG9-c7biBO



with a couple of reboots it seems that it is possible to extend the virtual disks of Virtualbox without dataloss... Cheers..

Friday, September 27, 2013

OOW Notes - ASM New Features on 12c

Thursday 12:30 - ASM New features


  • simple idea
  • 1to1 ASM to server
  • shared disk groups
  • wide file striping
  • 10years old in oracle database


grid computing .. cloud computing
  • more consolidation → more database instances per node
  • more clustering → more nodes in clusters
  • larger storage configurations


ASM evolves
  • maximize ASM robustness because increased number of database instances running on large servers
  • minimize ASM overhead because of large clusters
  • minimize cluster reconfiguration overhead because of large clusters


engineered systems (EXADATA and database appliance)
  • complete solutions
  • optimized for a particular objective
  • storage management provided by ASM
  • storage cells provides management offloading


flex ASM (NEW)
  • eliminate requirement for an ASM instance on every cluster server
  • database instances connect any ASM instance in the clustering database instances can failover to a secondary ASM instance
  • admins specify the cardinality of the ASM instances (default 3)


ASM network
in the previous version an oracle cluster required:
  • public network for client applications
  • one or more private networks for interconnect communication within the cluster


remote access (NEW)
in the previous versions database instances use os authentication to connect ASM
ASM clients and the server are always on the same server so this should change


12c database instance and ASM servers can be on different servers
  • flex ASM users password file authentication
  • password file in the ASM diskgroup
  • default config. is configured with the ASM installation


other flex features
  • increased mac number of disks to 511 (previous 63)
  • command for renaming ASM disk
  • ASM instance patch-level verification
  • replicated physical metadata
  • improves reliability
  • virtual metadata has always been replicated with ASM mirroring


admins can now specify a failure group repair time (NEW)
  • similar to existing disk repair time
  • new diskgroup attributes; failgroup_repair_time
  • default time 24 hours
  • power limit can be set for disk resync. operations
  • Conceptually similar to power limit setting for disk group rebalance
  • rebalance: 1 (least resource) to 1024 most resources


disk rescync now check points (NEW)
interrupted resync operations are automatic restart


sometimes rebalance operations are required to restore redundancy.
Example:
a disk fails and no replacement available
HBA containing a failure group goes offline


  • with oracle database flex ASM optimized reorganization
  • critical files such as control files log files are restored before datafiles
  • secondary failure is less likely result in critical file loss

  • admins can now specify the content type for each disk group:
  • new disk group attribute; content.type (possible values data, recovery or system)


  • disk group primary/secondary partnering changes with content.type
  • decreases likelihood that multiple failures cause data loss


silent data corruption is a fact in todays storage world
  • database checks for logical consistency when reading data
  • if a logical corruption is detected then automatic recovery can be performed using ASM mirror copies
  • for seldom accessed data, over time all mirror copies of the data could be corrupted

  • with oracle db 12c data can be proactively scrubbed (NEW)
  • scrubbing occurs automatically during rebalance operations
  • with flex ASM, most rebalance tasks offloaded to EXADATA storage
  • each offload request can replace numerous IO


IO distribution in 12c (NEW)
  • each read request is sent to at least loaded available disk
  • even read is transparent to apps and enabled default
  • users on IO bound systems should notice a performance improvement


managing flex ASM (nothing changes)


  • srvctl status asm
  • srvctl modifiy asm -count 4
  • srvctl start asm -n node_name
  • srvctl stop asm -n nodename


ASM Trivia's


why power limit setting value to 11. Why 11?


what was it is called before ASM?


all asm file extent pointers are protected from corruption by XORing to a constant.. what is that constant? 42



The acronym describing ASM data allocation and protection policy is LIKE what?

Thursday, September 26, 2013

OOW Notes - JSON data in Oracle Database

Wednesday 17:00 – Storing and Querying JSON Data in Oracle Database 12c


Understanding schema-less data management


growing trend: semi-structured data without string schema requirements
data first → schema later


growing trend adopting semistructured data tinto the database


  • xml
  • json (nosql - mongodb)


needs


structural and nonstructural coexist
  • using single RDBMS to store both
  • using one declarative set oriented SQL
  • parallel replication security transaction in one infrastructure


data can be stored without schema
  • store semi structural data in its native aggregated form without shredding


data can be queried without schema
  • use sql extended with semi structured query languages


example
  • SQL/XML embedding XQUERY/XPATH
  • SQL/SJON embedding JSON path language


data can be indexed without schema
  • indexing XML using structural XML index based on XMLTABLE expression
  • indexing XML using XML full text index


road map of managing JSON data using oracle XML/JSON db
  • JSON can be persisted without schema-less storing JSON data in varchar/raw/clob/blob columns
  • JSON data can be queried without schema (SQL/JSON embedding)
  • Function based indexes


overview of JSON


growing JSON adoption
  • strong usage in web client programming
  • rest interfaces (Facebook API)
  • starts to influence server side coding
  • data model for nosql stores like couchDB, mongodb
  • very well suited for rapid application development


goal
  • make rdbms JSON aware storage queries indexing
  • leverage relational features sql indexed joins transactions
  • enable schema-flexiable app development (data first schema later)


storing JSON data
  • no new datatype use existing: varchar2, raw, blob, clob, bfile
  • existing OCI, .NET and JDBC api works on JSON documents


check constraint to make ensure only valid JSON gets inserted


create table jtab (col1 varchar2(4000)) constraint jcons check (col1 is JSON);
insert into jtab values '{“name”:”oracle”}';


querying with JSON data with JSON/SQL


SQL/JSON emerging standard by Oracle+IBM
native JSON processing no rewrite to Xquery etc..


Oracle: streaming evaluation (no need to load all data into the memory )


path expression similar to javascript
  • starts with symbol $
  • . to native inside an object
  • [] to navigate inside an array
  • $.phoneNumber[1].type
  • wildcards $.phonenumbers[*]?(type=”home”).number


JSON functionalities


use a path expression to select a value from the JSON data and return it as a SQL value


JSON_VALUE operator


select JSON_VALUE ('$.creditHistory[1].creditScore'
PASSING JCOL
RETURNING NUMBER
NULL ON ERROR) from t1;


select JSON_VALUE('$.geo.lat' passing col DEFAULT 'this is not a value' ON ERROR;


JSON_QUERY operator


select JSON_QUERY ('$.creditHistory[1]' PASSING col) from table_name;


  • return JSON object '{“year”:2011, “creditScore”:650}'
  • can return multiple values


JSON_EXISTS operator


select count(*) from table_name where JSON_EXISTS('$.creditScore' PASSING col);


JSON_TABLE rowsource


  • project JSON data as relational table
  • items inside array becomes rows
  • JSON tables can be chained


select jtab.*
from table_name, JSON_table ('$.phoneNumbers[*]' passing col)
columns
ph_type varcahar2(10) path '$.type',
ph_num … path '$.number') jtab;