Published on April 12, 2014
My First 100 days with an Exadata Presented by : Gustavo René Antúnez Oracle DBA The Pythian Group April, 2014
About Pythian • Recognized Leader: – Global industry-leader in remote database administration services and consulting for Oracle, Oracle Applications, MySQL and Microsoft SQL Server – Work with over 250 multinational companies such as Forbes.com, Fox Sports, Nordion and Western Union to help manage their complex IT deployments • Expertise: – One of the world’s largest concentrations of dedicated, full-time DBA expertise. Employ 9 Oracle ACEs/ACE Directors. – Hold 7 Specializations under Oracle Platinum Partner program, including Oracle Exadata, Oracle GoldenGate & Oracle RAC • Global Reach & Scalability: – Around the clock global remote support for DBA and consulting, systems administration, special projects or emergency response
About Me – Oracle DBA – Part of the First Oracle Associate Group • Started with Version 9.2 in 2004 – With The Pythian Group for the last year – Movie Fanatic – Music Lover – Bringing the best from México (Mexihtli) to the rest of the world and in the process photographing it :) – reneantunez.com – @grantunez • #CLV14
Where do I come from?
How did I get to be a DBA
Happiest Job of 2014!* *http://www.forbes.com/sites/susanadams/2014/03/20/the-happiest-and-unhappiest-jobs-in-2014/ Work-life balance Relationship with boss and co- workers Daily tasks Job resources Field will grow by 15% between 2012 and 2022 DBA can be the key driver of success
What is Oracle Exadata Database Machine? • Optimized database machine designed by Oracle. It is a combination of Oracle Exadata Storage Server software, Oracle Database software, completely redundant hardware and a high-bandwidth low-latency InfiniBand network that connects all the components inside.
My Version of an Oracle Exadata Database Machine Storage Nodes Database Nodes Infiniband Leaf Switches ILOM,KVM, Cisco Network Switch
My Version of Rack Mount Options of an Exadata 1/8 1/4 1/2 1 X3-2 and X4-2
Compute or Database Nodes Grid Infrastructure RDBMS RAC Linux or Solaris OS Linux OS commands, SQL*Plus, ASMCMD, CRSCTL
Storage or Cell Node Exadata Storage Server Software 4 PCIe flash cards 12 rotating magnetic disks 12 rotating magnetic disks Linux OS only cellcli,dcli, Linux OS Commands
Abstraction of Disk 1 and 2 of the Cell Node Physical Disk Grid Disk Grid Disk Grid Disk LUN OS Storage Area (Disk1 and Disk2) Cell Disk Cell System Area (Disk 1 and Disk 2) Faster (Outside) Slower (Inside)
CellCLI> list cell detail name: exa1c114_net0 … cellVersion: OSS_220.127.116.11.0_LINUX.X64_110929 cpuCount: 16 … ipaddress1: 192.168.12.114/24 kernelVersion: 2.6.18-18.104.22.168.2.el5 locatorLEDStatus: off makeModel: SUN MICROSYSTEMS SUN FIRE X4275 SERVER SAS metricHistoryDays: 7 … powerCount: 2/2 powerStatus: normal releaseVersion: 22.214.171.124.0 … status: online … upTime: 97 days, 21:53 General information about your cell
CellCLI> list physicaldisk detail name: 20:0 deviceId: 8 diskType: HardDisk enclosureDeviceId: 20 errMediaCount: 0 errOtherCount: 0 foreignState: false luns: 0_0 makeModel: "SEAGATE ST360057SSUN600G" physicalFirmware: 0805 physicalInsertTime: 2010-02-05T10:31:54+00:00 physicalInterface: sas physicalSerial: E03WV1 physicalSize: 558.9109999993816G slotNumber: 0 status: normal General Information about your Physical Disk
CellCLI> list lun detail name: 0_0 cellDisk: CD_00_exa1c114_net0 deviceName: /dev/sda diskType: HardDisk id: 0_0 isSystemLun: TRUE lunAutoCreate: FALSE lunSize: 557.861328125G lunUID: 0_0 physicalDrives: 20:0 raidLevel: 0 lunWriteCacheMode: "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU" status: normal General Information about your LUN Disk
CellCLI> list celldisk detail name: CD_00_exa1c114_net0 comment: creationTime: 2011-06-14T02:25:45+00:00 deviceName: /dev/sda devicePartition: /dev/sda3 diskType: HardDisk errorCount: 0 freeSpace: 0 id: 00000130-8bf8-0381-0000-000000000000 interleaving: none lun: 0_0 raidLevel: 0 size: 528.734375G status: normal General Information about your Cell Disk
CellCLI> list griddisk detail name: DATA_CD_00_exa1c114_net0 asmDiskGroupName: DATA asmDiskName: DATA_CD_00_exa1c114_NET0 availableTo: cellDisk: CD_00_exa1c114_net0 comment: creationTime: 2011-06-14T02:26:18+00:00 diskType: HardDisk errorCount: 0 id: 00000130-8bf8-867a-0000-000000000000 offset: 32M size: 430G status: active General Information about your Grid Disk
View Name Description V$CELL IP addresses assigned to the cells. This also includes the hash value for the cell which is included in the Exadata storage cell session waits in v$session_wait in the P1 column. V$CELL_CONFIG Configuration information about all the levels in a storage cell. V$CELL_REQUEST_TOTALS Snapshots of the requests made to each cell over the last 15 minutes V$CELL_STATE Current statistics for each storage cell. V$CELL_THREAD_HISTORY Detail of events over the previous 15 minutes for the storage cell. Database Performance views to get information concerning the storage cells
Integrated Lights Out Manager (ILOM) • Cell and Compute Nodes come with a dedicated management channel for device maintenance called Integrated Lights Out Manager that works independently from these nodes • It supports various interfaces for accessing it: – Web Interface – CLI (Command Line Interface) – Remote console – Intelligent Platform Management Interface (IPMI)
Integrated Lights Out Manager (ILOM) • When using the ILOM to view your Hardware, we need to understand that there are 2 types – Customer-Replace Unit (CRU) – Field-Replaceable Units (FRU) -> show faulty Target | Property | Value --------------------+------------------------ +--------------------------------- /SP/faultmgmt/0 | fru | /SYS/SP … faults/0 | | /SP/faultmgmt/0/ | uuid | a6409101-e792-efb0-e4a2- b4674191 … /SP/faultmgmt/0/ | product_serial_number | < SERIAL NUMBER > faults/0 | | /SP/faultmgmt/0/ | chassis_serial_number | < SERIAL NUMBER > faults/0 | |
Integrated Lights Out Manager (ILOM) • You can also login to the node via the ILOM home/antunez> ssh email@example.com Password: … Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved. -> start /SP/console Are you sure you want to start /SP/console (y/n)? y … test01db01.pythian.com login: root Password: Last login: Thu Jan 30 20:01:47 from 10.10.10.11 [root@test01db01 ~]# ps -eaf | grep pmon grid 12167 1 0 21:42 ? 00:00:00 asm_pmon_+ASM1 root 13408 11206 0 21:42 ttyS0 00:00:00 grep pmon
Integrated Lights Out Manager (ILOM) • You can also power on a server with the IPMI interface [root@test01db01 ~]# ipmitool -H test01db01-ilom -U root -P welcome1 chassis power on • Interact with the ILOM through the IPMI interface [root@test01db01 ~]# ipmitool sunoem cli "show /SP/network" Connected. Use ^D to exit. -> show /SP/network /SP/network Targets: interconnect ipv6 test Properties: commitpending = (Cannot show property) dhcp_server_ip = none …
Integrated Lights Out Manager (ILOM) • http://tiny.cc/hw03dx
Disk Replacement • Common tasks done and that normally is not done on non-Exadata environment is a disk replacement. • The following two MOS documents should be revised before doing a disk replacement : – 1390836.1 (Predictive Failure) – 1386147.1 (Hard Failure)
Network Consolidated interconnect backbone that serves all of the hardware components. iDB is used to ship SQL operations down to the cell nodes for execution and to return query result sets to the database kernel It uses “zero copy”
Exachk: healtcheck for Exadata • MOS note 1070954.1 • The tool is non-intrusive, when it completes it's collection and analysis, it produces two reports, summary and detailed. • Advised to be run as a part of periodic maintenance operations on the exadata. • Configure oem12c to perform checkups on exadata (exachk) – http://tiny.cc/scz3dx
Exachk: healtcheck for Exadata
Useful MOS Note and ASR • Information Center: Troubleshooting Oracle Exadata Database Machine (Doc ID 1346612.2) • ASR is designed to automatically create Service Requests when specific hardware fault events occur. • ASR requires the following software packages to be downloaded and installed: – Oracle Automated Service Manager (OASM) (SUNWsasm package) – ASR Plug-in (SUNWswasr package)
Hybrid Columnar Compression (HCC) • HCC is optimized to use both database and storage capabilities on Exadata to deliver tremendous space savings • Data will only be compressed in HCC format when it is loaded using direct path loads types BLOCK HEADER CU HEADER BLOCK HEADER BLOCK HEADER BLOCK HEADER C3 C4 C1 C2 C7 C5 C6 C8 C8 Logical Compression Unit
Hybrid Columnar Compression (HCC) • HCC has four types of compression: – Query Low. - It uses the LZO compression algorithm. CREATE TABLE ... COMPRESS FOR QUERY LOW; – Query High. - It uses the ZLIB (gzip) compression algorithm. CREATE TABLE ... COMPRESS FOR QUERY HIGH; – Archive Low. - It uses the ZLIB (gzip) compression algorithm as well, but at a higher-level compression than Query High. CREATE TABLE ... COMPRESS FOR ARCHIVE LOW; – Archive High. – It uses Bzip2 compression CREATE TABLE ... COMPRESS FOR ARCHIVE HIGH;
Hybrid Columnar Compression (HCC) • For new tables and partitions: CREATE TABLE emp (emp_id NUMBER, first_name VARCHAR2(128), last_name VARCHAR2(128)) COMPRESS FOR QUERY HIGH; • For existing tables and partitions Online Redefinition (DBMS_REDEFINITION) ALTER TABLE ... MOVE COMPRESS FOR QUERY HIGH
Hybrid Columnar Compression (HCC) Compression Method Segment Size High NDV Low NDV MB Reduction MB Reduction No Compression 3,109.00 3,008.00 Basic 2,769.00 10.90% 246.88 91.80% OLTP 3,080.60 0.90% 280.94 90.70% Query Low 2,466.90 20.70% 113.25 96.20% Query High 1,637.40 47.30% 5.31 99.80% Archive Low 1,632.80 47.50% 5.31 99.80% Archive High 1,546.10 50.30% 5.31 99.80% • We created 2 tables consisting of only 12 columns and both with close to 20 billion rows. Many of the columns in table 1 have a very HIGH number of distinct values (NDV) and the second one with very LOW number of distinct values (NDV)
Hybrid Columnar Compression (HCC) • Oracle also provides an EHCC advisor, called GET_EHCC_CR, which can be found in MOS 1269846.1 SQL> EXEC GET_EHCC_CR('DATA','TST','EX_TAB_ORIGINAL',NULL,3); Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows COMPRESSED_TYPE = "Compress For Archive Low" COMPRESSED_BLOCKS = 10417 UNCOMPRESSED_BLOCKS = 19611 COMPRESSED_ROWS = 96 UNCOMPRESSED_ROWS = 51 COMPRESSION_RATIO = 1.8
Hybrid Columnar Compression (HCC) • Row level locking goes away when using HCC. • Every update of a record stored in HCC format results in a migrated row • Non-direct path inserts will be loaded into OLTP compressed blocks as opposed to HCC format. • For the above reasons, mixing HCC with DML is not recommended.
Smart Scan and Offloading • Processes queries at the storage layer, returning only relevant rows and columns to the database server. • Offloading refers to the concept of moving processing from the database servers to the storage layer • Will not use Smart Scan – if the columns being requested include a database large object (LOB) – if a table is a clustered table – If is an index-organized table
Smart Scan and Offloading • There are three basic requirements that must be met for Smart Scans to occur: – There must be a full scan of an object. • These correspond to TABLE ACCESS FULL and INDEX FAST FULL SCAN operations of an execution plan. – The object must be stored on Exadata storage. – The scan must use Oracle’s Direct Path Read mechanism
• The scan must use Oracle’s Direct Path Read mechanism. Smart Scan and Offloading Server Process SQL Work Areas Private SQL AreaSession Memory Database Buffer Cache
Smart Scan and Offloading • With Smart Scan, come several optimizations: – Column Projection or Column Filtering • The cell server will only return the columns requested. – Table that has 50 columns, and only 5 are selected and 2 involved in the join operation. The cell server will only return 7 columns.
Smart Scan and Offloading – Predicate Filtering • The cell server will only return the rows requested instead of all the rows in a table. – Since iDB includes the predicate information in its requests, this is accomplished by performing the standard filtering operations at the storage cells before returning the data.
Smart Scan and Offloading – Storage Indexes • Cell Server In-Memory structure that its main goal is to reduce the number I/O’s from disk • Automatically created and maintained transparently by the Exadata Storage Server Software • Three “commonly” known hidden database parameters that deal with Storage Indexes – _kcfis_storageidx_disabled .- When set to TRUE it will turn Storage Indexes off – _kcfis_storageidx_diag_mode.- This will enable you to trace the Storage Index – _cell_storidx_mode.- Controls where Storage Indexes will be applied (Comparison operators)
Smart Scan and Offloading • Should You Drop Indexes in Exadata? – http://tiny.cc/wr03dx
Exadata Patching • You now have several components – The firmware of the nodes – Operating System – Infiniband Switches – RDBMS and Grid Infrastructure binaries • MOS document 888828.1 will have the latest patches and release dates • Information Center: Upgrading Oracle Exadata Database Machine (Doc ID 1364356.2) • Exadata Database Server Patching using the DB Node Update Utility (Doc ID 1553103.1)
Different types of patches for Exadata Database Machine • Storage Server software – Contains updates to firmware, operating system, and/ or Exadata Storage Server • Rolling • Non-rolling • Database server – Oracle Grid Infrastructure and Database software – Operating system software and firmware • InfiniBand switch software • Additional components – Ethernet switch, KVM, and PDU patches
Example of dbnodeupdate.sh [root@test01db01 ~]# ./dbnodeupdate.sh -u -l /u01/stage/ p16432033/p16432033_112321_Linux-x86-64.zip … (*) 2013-12-07 03:24:52: Backing up /etc/yum.repos.d/Exadata- computenode.repo in /etc/yum.repos.d.bak/071213032414 … Active Image version : 126.96.36.199.0.120304 Active Kernel version : 2.6.18-2188.8.131.52.1.el5 … Automatic checks incl. : Issue 1.8 - Hotspare not reclaimed : Issue 1.10 - Cell and Database image versions 184.108.40.206.2 or lower require workaround before patching …
Example of dbnodeupdate.sh … Manual checks todo : Issue 1.11 - Database Server upgrades to 220.127.116.11.0 or higher may hit network routing issues after the upgrade Note : After upgrading and rebooting run './ dbnodeupdate.sh -c' to finish post steps Continue ? [Y/n] Y (*) 2013-12-07 03:25:30: Verifying GI and DB's are shutdown … (*) 2013-12-07 03:36:34: System will reboot automatically for changes to take effect (*) 2013-12-07 03:36:34: After reboot run "./dbnodeupdate.sh -c" to complete the upgrade
Exadata and EM12c • EM 12c offers an integrated approach to managing Oracle Exadata Database Machine • Use the Oracle Exadata Discovery Cookbook – http://www.oracle.com/technetwork/oem/exa-mgmt/em12c- exadata-discovery-cookbook-1662643.pdf • Pre discovery process and pre work will save you a lot of headaches – exadataDiscoveryPreCheck.pl from MOS note 1473912.1 – Need to have the ILOM user oemuser created – Preferably to use the ASMSNMP user for ASM instances – Make sure all the /etc/hosts are correctly set
Exadata and EM12c ************************************************************* * Enterprise Manager Exadata Pre-Discovery checks * ************************************************************** … Script used is /home/oracle/working/antunez/exadataDiscoveryPreCheck.pl … test1cel01 test1cel02 test1cel03 test2cel01 test2cel02 test2cel03 test2cel04 … Verifying component versions... ------------------------------- Verifying Exadata Storage Server Software version... ---------------------------------------------------- Verifying version for test1cel01 cell node... Exadata Storage Server Software version is 18.104.22.168.1 ===> Ok … Press [Enter] to continue... Verifying ILOM ipmitool version... ---------------------------------- ILOM ipmitool version is 22.214.171.124 ===> Ok
Exadata and EM12c • From the setup menu – Select Add targets à Add targets manuallyàAdd Non-HostàTargets using Guided process àOracle Exadata • Follow this order when discovering – DataBase Machine – Cluster and HA Services on each node – Databases, Listeners and ASM
Exadata and EM12c • Recommended that you setup the target properties correctly when discovering • If Having Problems discovering a Database target, use the rcuJDBCEngine to test. firstname.lastname@example.org [oms] /opt/software/oms/Middleware/ oms/bin oracle $ ./rcuJDBCEngine dbsnmp@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=test01-scan) (Port=1521))(CONNECT_DATA=(SERVICE_NAME=testdb)))" JDBC_SCRIPT test.sql … Completed SQL script execution normally. 1 scripts were processed
Exadata and EM12c
Slideshare • Session ID 544 • http://www.slideshare.net/ GustavoReneAntunez
Pythian at Collaborate 2014 Session ID Session Date Start Time Session Room Session Title Presenter Name 157 4/9/2014 8:30 AM Level 3, Lido 3101B Database Private Clouds with Oracle Database 12c Marc Fielding 574 4/9/2014 8:30 AM Level 3, Lido 3005 DBA 101 : Calling all New Database Administrators Rene Antunez 14408 4/9/2014 4:30 PM Sands, Level 1 - 309What's New in Oracle E-Business Suite R12.2 for Database Administrators? Vasu Balla 158 4/10/2014 3:00 PM Level 3, San Polo 3403 Accelerate Your Exadata Deployment with the Skills You Already Have Marc Fielding 411 4/10/2014 4:15 PM Level 3, Murano 3306 Thou Shalt Not Steal: Securing Your Infrastructure in the Age of Snowden Paul Vallee 544 4/10/2014 11:00 AM Level 3, San Polo 3403 My First 100 days with an Exadata Rene Antunez 135 4/11/2014 9:45 AM Level 3, Lido 3003 Ensuring Your Physical Standby is Usable Michael Abbey 194 4/11/2014 12:15 PM Level 3, Lido 3101B 12c Multi-Tenancy and Exadata IORM: An Ideal Cloud Based Resource Management Fahd Chughtai 546 4/11/2014 8:30 AM Level 3, Lido 3103 RMAN in 12c: The Next Generation ReneAntunez 878 4/11/2014 9:45 AM Level 3, San Polo 3405 Practical Machine Learning for DBAs Alex Gorbachev
Thank you – Q&A To contact us email@example.com 1-877-PYTHIAN To follow us http://www.pythian.com/blog http://www.facebook.com/pages/The-Pythian-Group/163902527671 @pythian http://www.linkedin.com/company/pythian We are hiring! Booth 1535
Presentación que realice en el Evento Nacional de Gobierno Abierto, realizado los ...
In this presentation we will describe our experience developing with a highly dyna...
Presentation to the LITA Forum 7th November 2014 Albuquerque, NM
Un recorrido por los cambios que nos generará el wearabletech en el futuro
Um paralelo entre as novidades & mercado em Wearable Computing e Tecnologias Assis...
As you step up to the job in your first 100 days, ... “What are the ten things that I should do in my first hundred days?”
My First 100 days with an Exadata (PPT) 4,392 Views. YunusEmreBaransel. A Deep Dive into ASM Redundancy in Exadata. 1,800 Views. OracleHardware. Exadata X5 ...
intelligent executive.com intelligent executive intelligent executive Your first 100 days in position
The First 100 Days. ... The PowerPoint PPT presentation: "The First 100 Days" is the property of its rightful owner. Do you have PowerPoint slides to share?
My First 100 Days One hundred days is only a short time, and far shorter ... My First 100 My own plan for the first 100 days was to figure out who the right
The Issue For a leader, success or failure is made in the first 100 days. Project, role, assignment, initiative
First 90 Days In A New Job Presentation. ... I have a lengthy presentation to do for a new job on what I would do in the first 100 days. ... ← Name My ...
Leadership in your first 100 days Recent studies by First100, a leadership consultancy, ...