Ferhat's Blog

There will be only one database

Archive for the ‘Exadata’ Category

ipmitool to measure electricity usage of Exadata

Posted by fsengonul on September 16, 2013

If you’re curious about the electricity usage of an exadata rack  ( or have shortage of power in your data center) you may try to use a smart PDU.
But there is a better and cheaper way to measure it by using the ipmitool. After the collection, it’s so easy to create a graph and compare different exadata versions.

exadata_electricity_compare
In this graph , 2 X3-2 HC(High Capacity) / 2 X2-2 HP(High Performance) and 1 v2 SATA racks are compared. The electricity usage of the HP disks seems much more than the HC ones. It would be interesting to compare the relationship between the throughput, cpu usage and electricity.The details are below:

[root@xxx01 ~]# ipmitool sensor | grep -i vps
VPS_CPUS         | 50.000     | Watts      | ok    | na
VPS_MEMORY       | 12.000     | Watts      | ok    | na
VPS_FANS         | 42.000     | Watts      | ok    | na
/SYS/VPS         | 370.000    | Watts      | ok    | na

Our sysadmin Mustafa Altuğ Kamacı has coded a nice script to collect this info from all compute and storage cells. The script is triggered from the crontab.

[root@xxx01 ~]# cat /usr/bin/pwrstat
#!/bin/ksh
PATH=$PATH:/usr/bin:/usr/sbin:/bin
export PATH
d=`date '+%d%m%y'`
t=`date '+%H:%M'`
integer P1=0
integer p1=0
for i in `cat /root/group_all`
do
p1=`ssh -q $i "ipmitool sensor get /SYS/VPS|grep 'Sensor Reading'"|awk '{a=a+$4}END{print a }'`
P1=$P1+$p1
done
echo $t " " $P1 "Watt"  >> /home/pwrstat/pwrstat_$d.log
[root@xxx01 ~]#
root@maxdb01 pwrstat]# ls -al
total 376
drwxr-xr-x  2 root root 4096 Sep 16 00:00 .
drwxr-xr-x 23 root root 4096 Sep 10 15:26 ..
-rw-r--r--  1 root root 3173 Aug  1 23:55 pwrstat_010813.log
-rw-r--r--  1 root root 5472 Sep  1 23:55 pwrstat_010913.log
-rw-r--r--  1 root root 5472 Aug  2 23:55 pwrstat_020813.log
-rw-r--r--  1 root root 5472 Sep  2 23:55 pwrstat_020913.log
-rw-r--r--  1 root root 5472 Aug  3 23:55 pwrstat_030813.log
-rw-r--r--  1 root root 5472 Sep  3 23:55 pwrstat_030913.log
-rw-r--r--  1 root root 5472 Aug  4 23:55 pwrstat_040813.log
.
.
.
[root@xxx01 pwrstat]# cat pwrstat_010913.log
00:00   17580 Watt
00:05   17890 Watt
00:10   17350 Watt
00:15   17510 Watt
00:20   17990 Watt
00:25   17800 Watt
00:30   17640 Watt
00:35   17720 Watt
00:40   17780 Watt
00:45   17830 Watt
00:50   17950 Watt
00:55   17410 Watt
01:00   17970 Watt
01:05   17510 Watt
01:10   17600 Watt

Posted in Exadata | Tagged: , , | 2 Comments »

OOW 2011 sessions

Posted by fsengonul on September 19, 2011

My sessions/forums in Oracle Open World 2011:

04561 – Turkcell’s Oracle Exadata Journey Continues: Three Full Racks Running Six Databases
13803 – Oracle Exadata Hybrid Columnar Compression: Next-Generation Compression
14048 – Maximize Your ROI with Oracle Database Cloud
Data Warehouse Global Leaders Annual Meeting

 

You may use the following link and search for Sengonul,  to add  the sessions

https://oracleus.wingateweb.com/scheduler/speakers.do

 

And the details:

Title: Turkcell’s Oracle Exadata Journey Continues: Three Full Racks Running Six Databases
Time Monday, 11:00 AM, Moscone South – 302
Length 1 Hour
Abstract: Turkcell, the leading telco operator in Turkey, with more than 33 million subscribers, started its Oracle Exadata journey a little more than a year ago with one full machine and achieved tremendous success. After it experienced a tenfold improvement in performance, storage, and datacenter footprint for its 100 TB data warehouse database, it was a no-brainer to continue on this route, so it added two new Exadata Database Machine X2-2s and consolidated all of its six databases in its data warehouse domain on three full racks. In this session, it shares its experience in this episode of the journey.

Title: Oracle Exadata Hybrid Columnar Compression: Next-Generation Compression
Time Tuesday, 11:45 AM, Moscone South – 304
Length 1 Hour
Abstract: Is your data warehouse growing faster than your storage budget? Is the size of your data warehouse slowing down your users’ queries? Are you convinced that there isn’t a way to archive your OLTP data and keep it accessible to users? If you answered yes to any of these questions, your attendance at this session is mandatory! You will learn how Oracle Exadata hybrid columnar compression can shrink your data warehouse to as little as 1/15 of its original size and improve query performance by drastically reducing I/O. You will also learn how Oracle Exadata hybrid columnar compression, with up to 20x compression for archive data, lets you keep your historical data available for users, and your storage administrator won’t even care that it’s there.

Title: Maximize Your ROI with Oracle Database Cloud
Time Monday, 03:30 PM, Moscone South – 308
Length 1 Hour
Abstract: Database cloud deployments provide the best ROI for deploying databases in a cloud environment. They are based on and leverage advanced database capabilities, and many customers are already benefiting from the capex and opex savings enabled by database cloud deployments. This session presents best practices for maximizing ROI when implementing database consolidation and deploying database as a service (DaaS) to improve overall business agility and significantly reduce database deployment times. It includes specific customer use cases and shows how the customers are maximizing the ROI of database cloud environments.

 

 

Posted in Exadata, oracle | Leave a Comment »

Presentation in ilOUG

Posted by fsengonul on May 16, 2011

I’ll be in Tel Aviv on Wednesday (18/05) to present our last exadata project.
Thanks to Israel Oracle User Group for their kind invitation.
It would also be a great chance for me to learn their experiences both on exadata and oracle user group activities.
For more information : http://www.iloug.org.il/Event_Page.php?EventID=104

Knowledge will increase when it is shared. That’s the best side of user groups.

Posted in Exadata, oracle | 4 Comments »

The eagle has landed

Posted by fsengonul on April 19, 2011

After 3 months of planning and logistics, the migration of uncompressed 600 TB’s of data from Europe to Asia has finished last weekend. Now our 2 X2-2 Racks is hosting 4 databases.

Thanks for everybody who has  involved in this project with both their supports and critics.

Posted in Exadata | 4 Comments »

Playing with policy managed databases

Posted by fsengonul on March 17, 2011

It was a great idea to try quality of service in 11gR2. But unfortunately at the moment it only supports OLTP workload.(average response time of less than one second, and preferably, an average response time of less than 0.5 seconds) and it does not support parallel queries.

Even though I can’t use them at the moment, I can still gain something from the server pool architecture.
I can move nodes from one database to another.
Below you may find my first tries.


oracle@node01:/home/oracle>srvctl status srvpool -g POOL1B

Server pool name: POOL1B
Active servers count: 1
oracle@node01:/home/oracle>srvctl status srvpool -g POOL1B -a
Server pool name: POOL1B
Active servers count: 1
Active server names: node14
NAME=node14 STATE=ONLINE
oracle@node01:/home/oracle>srvctl config database -d XDB
Database unique name: XDB
Database name: XDB
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/XDB/spfileXDB.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: POOL1B
Database instances:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Database is policy managed
oracle@node01:/home/oracle>srvctl status srvpool -g POOL1B -a
Server pool name: POOL1B
Active servers count: 1
Active server names: node14
NAME=node14 STATE=ONLINE
oracle@node01:/home/oracle>srvctl start database -d XDB
oracle@node01:/home/oracle>srvctl status database -d XDB
Instance XDB_1 is running on node node14
oracle@node01:/home/oracle>srvctl modify srvpool -g POOL1B -n "node14,node15"
oracle@node01:/home/oracle>srvctl status srvpool -g POOL1B -a
Server pool name: POOL1B
Active servers count: 2
Active server names: node14,node15
NAME=node14 STATE=ONLINE
NAME=node15 STATE=ONLINE
oracle@node01:/home/oracle>srvctl status database -d XDB
Instance XDB_1 is running on node node14
Instance XDB_4 is running on node node15
oracle@node01:/home/oracle>srvctl modify srvpool -g POOL1B -n "node14"
PRCS-1011 : Failed to modify server pool POOL1B
CRS-2736: The operation requires stopping resource 'ora.XDB.db' on server 'node15'
CRS-2738: Unable to modify server pool 'ora.POOL1B' as this will affect running resources, but the force option was not specified
oracle@node01:/home/oracle>srvctl stop instance -d XDB -n node15
oracle@node01:/home/oracle>srvctl status database -d XDB
Instance XDB_1 is running on node node14
Instance XDB_4 is not running on node node15
oracle@node01:/home/oracle>srvctl modify srvpool -g POOL1B -n "node14"
oracle@node01:/home/oracle>srvctl status database -d XDB
Instance XDB_1 is running on node node14
oracle@node01:/home/oracle>

Posted in Exadata, oracle | Leave a Comment »

What happens when exadata has lost two disks?

Posted by fsengonul on March 8, 2011

Actually the question is “What happens when ASM has lost two disks in different failgroups when redundancy is normal?”
We’re sure that there will be no problem if we lose all of the disks in a single failgroup but in order to answer the question we have to understand how mirroring works in ASM.
In normal redundancy, mirror of each extent is written to another disk in another failgroup. This causes a partnership between these disks. As far as I’ve found out this information is kept in the x$kfdpartner table.

From the following query it seems that an extend in DATA_CD_10_CEL27 has a mirror on DATA_CD_07_CEL01. If we lose these two disks then the system has to crash.
On the other hand if we lose DATA_CD_10_CEL27 and the disks in the partner list ( 8 disks at the moment) stays alive, there will be no problem if we lose another disk.


select dg.name dg_name,
d.name disk_name,
pd.name partner_name
from x$kfdpartner p,v$asm_diskgroup dg,v$asm_disk d,v$asm_disk pd
where
p.grp=dg.group_number
and d.disk_number=p.disk and d.group_number=p.grp
and pd.disk_number=p.number_kfdpartner and pd.group_number=p.grp
and dg.name='DATA' and d.name='DATA_CD_10_CEL27';

DG_NAME DISK_NAME PARTNER_NAME
DATA DATA_CD_10_CEL27 DATA_CD_07_CEL01
DATA DATA_CD_10_CEL27 DATA_CD_06_CEL01
DATA DATA_CD_10_CEL27 DATA_CD_07_CEL25
DATA DATA_CD_10_CEL27 DATA_CD_06_CEL25
DATA DATA_CD_10_CEL27 DATA_CD_07_CEL26
DATA DATA_CD_10_CEL27 DATA_CD_06_CEL26
DATA DATA_CD_10_CEL27 DATA_CD_07_CEL28
DATA DATA_CD_10_CEL27 DATA_CD_06_CEL28

Posted in Exadata, oracle | Leave a Comment »

What happens when exadata has lost a disk?

Posted by fsengonul on February 5, 2011

We have experienced a disk failure today and changed it without any problem or manual commands.
This morning we have lost a disk in exadata. We got an alert and an email mentioning that “Hard disk status changed to predicative failure: critical” . There was also the drawing of the location of the corrupted disk in the email.
From the logs of the cell and asm, it can be easily seen that it has dropped the grid disks and started a rebalance operation in order to be sure that all the data has 2 copies.
We did not wait for the oracle/sun engineer to come and replace the disk. Our system admins has replaced the disk and exadata automatically recognized the new disk and started a new rebalance operation without any manual commands.


/* cell  triggers the drop operation */
Sat Feb 05 11:50:31 2011
Received subopcode 6 in publish ASM Query on 3 guids.
NOTE: Initiating ASM Instance operation: ASM DROP critical disk on 3 disks
DATA_CD_08_cel11 [00000xxxx-yyyy-zzzz-0000-000000000000]
RECO_CD_08_cel11 [00000xxxx-yyyy-zzzz-0000-000000000000]
SYSTEMDG_CD_08_cel11 [00000xxxx-yyyy-zzzz-0000-000000000000]


/* the corrupt disk has been replaced with the spare one */
Sat Feb 05 16:40:44 2011
Drop celldisk CD_08_cel11 (options: force, from memory only) - begin
Drop celldisk CD_08_cel11 - end
Sat Feb 05 16:40:44 2011
Open received invalid device name SYSTEMDG_CD_08_cel11
Sat Feb 05 16:40:44 2011
Open received invalid device name SYSTEMDG_CD_08_cel11
Sat Feb 05 16:42:44 2011
create CELLDISK CD_08_cel11 on device /dev/sdi
Sat Feb 05 16:42:44 2011
create GRIDDISK DATA_CD_08_cel11 on CELLDISK CD_08_cel11
Griddisk DATA_CD_08_cel11  - number is (248)
NOTE: Initiating ASM instance operation:
Operation: DROP and ADD of ASM disk for Grid disk guid=00000xxxx-yyyy-zzzz-0000-000000000000
Received subopcode 4 in publish ASM Query on 1 guids.
NOTE: Initiating ASM Instance operation: ASM DROP ADD disk on 1 disks
DATA_CD_08_cel11 [00000xxxx-yyyy-zzzz-0000-000000000000]

Storage Index Allocation for GridDisk DATA_CD_08_cel11 successful

Sat Feb 05 16:42:44 2011
create GRIDDISK RECO_CD_08_cel11 on CELLDISK CD_08_cel11
Griddisk RECO_CD_08_cel11  - number is (252)
NOTE: Initiating ASM instance operation:
Operation: DROP and ADD of ASM disk for Grid disk guid=00000xxxx-yyyy-zzzz-0000-000000000000
Received subopcode 4 in publish ASM Query on 1 guids.
NOTE: Initiating ASM Instance operation: ASM DROP ADD disk on 1 disks
RECO_CD_08_cel11 [00000xxxx-yyyy-zzzz-0000-000000000000]

Storage Index Allocation for GridDisk RECO_CD_08_cel11 successful

 



Sat Feb 05 16:42:44 2011
create GRIDDISK SYSTEMDG_CD_08_cel11 on CELLDISK CD_08_cel11
Griddisk SYSTEMDG_CD_08_cel11  - number is (256)
NOTE: Initiating ASM instance operation:
Operation: DROP and ADD of ASM disk for Grid disk guid=00000xxxx-yyyy-zzzz-0000-000000000000
Received subopcode 4 in publish ASM Query on 1 guids.
NOTE: Initiating ASM Instance operation: ASM DROP ADD disk on 1 disks
SYSTEMDG_CD_08_cel11 [00000xxxx-yyyy-zzzz-0000-000000000000]

Posted in Exadata, oracle | 7 Comments »

2 x2-2 have been installed.

Posted by fsengonul on January 16, 2011

Our new x2-2 machines have been installed. We now have 16 compute nodes and 28 storage cells. The main issue in the installation is cabling for two racks. They have been cabled as 2 separate 8 node machines out of the factory. The Sun engineer first removed the 7 inter-switch links between leaf switches. And he also removed the 2 links between the spine and leaf switches. It seems that in a single RACK installation leaf to leaf links provide the shortest path while in a 2 RACK installation we do not have enough empty ports to connect all spines directly. They have used 32 cables to connect the spine and leaf switches between RACKs.
Below you may find the diagrams for cabling.
Other than that there is no difference between a single node and multi node installation.
PS: I should also mention about a bug in the cluvfy that it does not work for more than 10 nodes.

Posted in Exadata | 5 Comments »

Just because I have been a good DBA in 2010, Santa Claus is bringing me 2 new x2-2 for christmas :)

Posted by fsengonul on December 14, 2010

It seems that our new 2 x2-2 boxes will arrive before the end of this year. We have already started to plan our new migration process from the existing v2 full rack sas machine into the newly comming x2-2 high performance systems. This time we will deal will 16 nodes. Migration will be quite simple as taking a backup and restoring it. And taking an incremental backup and restoring it , until we had a read only window to direct our etls. But we do not like easy projects 🙂 So to get things complicated we are planning to change the place of our datacenter. It’s good news for me because I will have more time to play with this new toy before it becomes production.
If you have anything in mind to test or try on x2-2 machine, just write your comments and we will plan the stress tests together.

Posted in Exadata, oracle | 1 Comment »

Turkcell Exadata Podcast

Posted by fsengonul on November 24, 2010

You may listen our exadata podcast from

http://streaming.oracle.com/ebn/podcasts/media/9547030_Turkcell_111810.mp3

 

 

 

Posted in Exadata, oracle | 1 Comment »