Transcript Exadata Management for DBAs

Exadata Management for DBAs
Arup Nanda
Longtime DBA and Now DMA
What it this About?
• How Exadata is different?
• Who manages it?
– Oracle DBAs
– System Admins
– Composite Team
• Who does what?
• What you need to know about managing it
• What must you learn about potential issues
2
What is Exadata
• Looks like an appliance
• But is not an appliance. Why?
– It contains additional software to make it a better
database machine
– The components are individually adminstered
• That’s why Oracle calls it a Database Machine
(DBM)
• DMA – Database Machine Administrator
3
The Magic #1
CPU
iDB
Memory
Network
I/O Controller
Disk
4
Magic #2 Storage Cell Server
•
•
iDB
•
Disk1
Disk2
Cells are Sun Blades
Run Oracle Enterprise
Linux
Software called Exadata
Storage Server (ESS)
which understands iDB
Disk3
5
Magic #3 Storage Indexes
Storage Indexes store in memory
of the Cell Server the areas on
the disk and the MIN/MAX value
of the column and whether NULL
exists. They eliminate disk I/O.
SELECT …
FROM TABLE
WHERE COL1 = 1
Storage Index
Disk1
Disk2
Disk3
MIN = 3
MIN = 4
MIN = 1
MIN = 3
MAX = 5
MAX = 5
MAX = 2
MAX = 5
Disk4
6
Put Together: One Full Rack
RAC Cluster
Database
Node 1
Database
Node 7
InfiniBand
Switch
Cell 1
Cell 1
Cell 1
Cell 1
Database
Node 8
Network Switch
Clients
connect to the
database
nodes.
Cell 14
7
Disk Layout
Compute
Nodes
Storage
Cell
•
•
•
•
•
Disks (hard and flash) are
connected to the cells.
The disks are partitioned at the
cell
Some partitions are presented
as filesystems
The rest are used for ASM
diskgroups
All these disks/partitions are
presented to the compute nodes
8
Node
Disk Presentation
filesystem
Cell
filesystem
9
Command Components
Compute
Nodes
Storage Cell
Linux Commands – vmstat, mpstat, fdisk, etc.
ASM Commands – SQL*Plus, ASMCMD, ASMCA
Database Commands – startup, alter database, etc.
Clusterware Commands – CRSCTL, SRVCTL, etc.
Linux Commands – vmstat, mpstat, fdisk, etc.
CellCLI – command line tool to manage the Cell
5-part Linux Commands article series
http://bit.ly/k4mKQS
4-part Exadata Command Reference article series
http://bit.ly/lljFl0
10
Disk Failures
Datafile
block1
Cell 1
block1
Cell 2
block1
11
Server Management
• Sun Blades and Oracle Enterprise Linux
• Normal Sysadmin Work
– Shutdown, fdisk, etc.
• ILOM – Integrated Lights Out Management
• KVM allows physical access
– But you can use ILOM for virtual console
• Needs Pure Linux Skills
12
Network Management
• Two types of network
– Ethernet
– Infiniband
• Tools
– ibstatus
– iblinkinfo
– verify-topology
13
Special Oracle Provided Tools
• All tools are found at
/opt/oracle.SupportTools
• CheckHWnFWProfile
– to check the HW profile
• Directory ibdiagtools
/opt/MegaRAID/MegaCli/MegaCli64
# ipmitool -H prolcel01-ilom -U root chassis
power on
# imageinfo
# imagehistory
14
Database and Cluster Management
• Cluster
– crsctl, srvctl
• ASM
– asmcmd, SQL*Plus
• Database
– srvctl, SQL*Plus
15
Storage Management
• Two ways to manage the storage
– Enterprise Manager
– CellCLI
16
Checking Storage Index Use
select decode(name,
'cell physical IO bytes saved by storage index',
'SI Savings',
'cell physical IO interconnect bytes returned by smart scan',
'Smart Scan'
) as stat_name, value/1024/1024 as stat_value
from v$mystat s, v$statname n
where s.statistic# = n.statistic#
and n.name in (
'cell physical IO bytes saved by storage index',
'cell physical IO interconnect bytes returned by smart scan’)
17
Smart Scan Savings
• Output
STAT_NAME STAT_VALUE
---------- ---------SI Savings 0.000
Smart Scan 0.000
• Smart Scan did not yield any savings
• Why not?
• Disable Smart Scans, if needed
– cell_offload_processing = true;
– _kcfis_storageidx_disabled = true;
18
Why Not?
• Pre-requisite for Smart Scan
– Direct Path
– Full Table or Full Index Scan
– > 0 Predicates
– Simple Comparison Operators
• Other Reasons
– Cell is not offload capable
• The diskgroup attribute cell.smart_scan_capable set to
FALSE;
– Not on clustered tables, IOTs, etc.
19
One Cluster?
One Cluster
QA1
QA2
QA3
Prod1
Prod2
Prod3
Dev1
Int1
QA1
QA2
Prod4
Prod1
Prod2
Prod3
Dev1
Int1
QA1
QA2
QA3
Prod1
Prod2
Prod3
Dev1
Int1
Dev2
20
Many Clusters?
QA Cluster
QA1
QA2
QA Cluster
Prod Cluster
QA3
Prod1
Prod2
Prod Cluster
Prod3
Dev
Int
Dev1
Int1
Dev
Int
21
Administration Skills
Skill
System Administrator
Storage Administrator
Network Administrator
Database Administrator
Cell Administration
Needed
10%
0%
5%
60%
25%
DBA
Sys Admin
Network Admin
Cell Admin
22
Resources
• My Papers
– 5-part Linux Commands article series http://bit.ly/k4mKQS
– 4-part Exadata Command Reference article series
http://bit.ly/lljFl0
• OTN Page on Exadata
– http://www.oracle.com/technetwork/database/exadata/index.
html
• Tutorials
– http://www.oracle.com/technetwork/tutorials/index.html
• OTN Exadata Forum
– https://forums.oracle.com/forums/forum.jspa?forumID=829
23
Thank You!
24