1 / 57

Oracle ASM Reduces Cost of VLDB Deployment

Oracle ASM Reduces Cost of VLDB Deployment Hanan Hit, Principal Database Architect Lina Shabelsky, Senior Application and Database Engineer NOCOUG Winter Conference February 08, 2007 Agenda Application description What ASM is and is not Protocol etc. Deployment models Network design

Melvin
Download Presentation

Oracle ASM Reduces Cost of VLDB Deployment

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle ASM Reduces Cost of VLDB Deployment Hanan Hit, Principal Database Architect Lina Shabelsky, Senior Application and Database Engineer NOCOUG Winter Conference February 08, 2007

  2. Agenda • Application description • What ASM is and is not • Protocol etc. • Deployment models • Network design • Physical architecture • Oracle operations to reduce required I/O • Index creation stats • Best practices • Lessons learned during VLDB implementation • Backup and recovery Confidential and Proprietary

  3. Application Description

  4. Logical Database Structure Management Db Operational Data Store Historical Data Store Archive Data Store (SATA) OLAP Confidential and Proprietary

  5. General Loading Schema Staging ODS – Last Hour Aggregators OLAP Data Warehouse Loader Bus Confidential and Proprietary

  6. High Level – Physical Layer Confidential and Proprietary

  7. What ASM is and is not

  8. ASM - Flexible, Efficient, Time Saver • Vertically integrated file system • Easy file management system • Management of: • Raw device volume • Oracle data files • Online and archive logs • RMAN backup • Multi platform availability • Stripes data across all raw volumes • Hot spot detection and correction • Optional mirroring (best with SAN/NAS) but striping is NOT • Oracle 10g Release 1 and above Confidential and Proprietary

  9. What ASM is NOT • A cluster file system • Available for non Oracle files • Available for OCR and voting disks • General file system Confidential and Proprietary

  10. Raw With LVM Without LVM Cooked With LVM Without LVM Options prior to ASM Confidential and Proprietary

  11. Why We Chose ASM in the First Place? • New implementation using 10g • Linux RHEL shop • Mid range storage array - full enterprise business needs • “Just trust the hardware to handle it” • Not a workable solution (DBA’s) • Expected thousands of data files • Didn’t want to use the BIG File option (YET). • RAC & non RAC implementations • Obvious need for storage growth with unpredictable limit size • Do more with less • Very few DBAs & sys/storage admin • Block level access to storage • Easy storage provisioning Confidential and Proprietary

  12. Protocol

  13. Which Protocol to Use? • FC – SAN (2/4 GBit/s) • iSCSI – IP-SAN (2/4 GBit/s) • NFS – NAS (2/4 GBit/s) • FCIP Confidential and Proprietary

  14. What is iSCSI Protocol? • A network protocol standard that allows the use of the SCSI protocol over TCP/IPnetworks • A transport layer protocol in the SCSI-3 specifications framework • Expected to capture more than 10% of storage systems revenue and an even greater percentage of capacity by 2008 - Hot technologies for 2007 (Storage Magazine) • “iSCSI SAN is definitely happening” • - Stephen Foskett, GlassHouse Technologies Inc., Framingham, MA Confidential and Proprietary

  15. Wikipedia iSCSI Definitions • iSCSI initiator in client/server terminology, is akin to a client device that connects to some service offered by the server (in this case an iSCSI target). • An iSCSI target is akin to a server, in that it provides block level access to its storage media (usually a hard drive, but can be other types of SCSI devices). • Only one iSCSI initiator can talk to a given iSCSI target at a time (one-to-one). Confidential and Proprietary

  16. Simple iSCSI system Confidential and Proprietary

  17. Deployment Models

  18. Deployment Models • Separate Disk Groups • Use storage base features for ASM deployment data management • Backup/recovery, cloning etc. • Shared Disk Groups • Use Oracle tools exclusively for all data management methods Confidential and Proprietary

  19. Separate Disk Groups Confidential and Proprietary

  20. Shared Disk Groups Confidential and Proprietary

  21. Shared Disk Group- Multiple RAC Instances Confidential and Proprietary

  22. Network Design

  23. Typical Performance in MB/s Confidential and Proprietary

  24. iSCSI End Point Options Confidential and Proprietary

  25. Network Configuration Confidential and Proprietary

  26. Physical Architecture

  27. FAS3050 #1 Confidential and Proprietary

  28. FAS3050 #2 Confidential and Proprietary

  29. FAS3020 Confidential and Proprietary

  30. Database size Confidential and Proprietary

  31. Oracle operations to reduce required I/O

  32. Oracle Operations - Reduce Disk I/O • Index creation on many billions of rows • New software version while supporting legacy data • Gathering Oracle stats Confidential and Proprietary

  33. Index creation statistics

  34. Index Creation – New App Version • Set/unset the SKIP_UNUSABLE_INDEXES • When set to TRUE Oracle will not attempt to use or report errors when an index is marked as unusable • System Level - ALTER SYSTEM SET SKIP_UNUSABLE_INDEXES = TRUE; • Session Level ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE; Confidential and Proprietary

  35. Index Creation – New App Version • Step 1 - Create the new index with the UNUSABLE attribute • Step 2 - System Level - ALTER SYSTEM SET SKIP_UNUSABLE_INDEXES = TRUE; • Step 3 -ALTER INDEX <index name> MODIFY PARTITION <partition name> UNUSABLE; • On all partitions. If using a Global Index then mark the entire index as UNUSABLE • Step 4 -ALTER INDEX <index name> REBUILD PARTITION <new partition name> NOLOGGING; • On the most fresh partition this will enable the application to run with the new index ASAP. • Step 5 - ALTER INDEX <index name> REBUILD PARTITION <partition name> NOLOGGING; • On all the legacy (non hotspot) partitions. This will be rebuild according the system workload. • Final Step - System Level - ALTER SYSTEM SET SKIP_UNUSABLE_INDEXES = FALSE; Confidential and Proprietary

  36. Gathering Oracle Stats • Gather partitions statistics (First time) • Exec dbms_stats.gather_table_stats(ownname=>‘<schema name>', tabname => ‘<tab name>', PARTNAME=> ‘<part name>' , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE AUTO'); • Exec dbms_stats.gather_table_stats (ownname=>‘<schema name>' , tabname=>‘<tab name>' , PARTNAME=> '<part name>' , estimate_percent=>1,CASCADE=> true); • Unlock Statistics(If previously locked) • Exec dbms_stats.unlock_table_stats(OWNNAME=>‘<schema name>' , TABNAME=>‘<tab name>'); Confidential and Proprietary

  37. Gathering Oracle Stats – More • Copy statistics • Execdbms_stats.COPY_TABLE_STATS(OWNNAME=>‘<schema name>' , TABNAME=>‘<tab name> ' , SRCPARTNAME=>‘<N’th partition name>' , DSTPARTNAME=>'<N’th+1 partition name>'); • Lock table statistics • Exec dbms_stats.lock_table_stats(OWNNAME=>‘<schema name>' , TABNAME=>‘<tab name>'); Confidential and Proprietary

  38. Index Creation Example Confidential and Proprietary

  39. Index Creation Example – More Confidential and Proprietary

  40. Best Practices

  41. NetApp/ASM – Our Best Practices • Single aggregate • FlexVol • ASM external redundancy • Separate disk groups • Maximum volume size allowed 16TB while recommended is not more then 3TB • Use RAID-DP with maximum 16 drives in a single RAID group • Set minra to off – even on DSS • Jumbo frames – 9K MTU • Single mode VIF Confidential and Proprietary

  42. Lessons learned during VLDB implementation

  43. RAC 10gR2 Implementation Learnings • Implementation of VLDB in RHEL 4.3, software iSCSI, NetApp and ASM environment • Procedure of provisioning additional storage • Backup procedures with NetApp SnapManager for Oracle Confidential and Proprietary

  44. Network Implementation • Isolate your database from the rest of the IP traffic • Use separate VLAN for access to the storage • Use oifcfg to verify the setup of your interfaces • Use IP bonding on both – server and filer to achieve network stability and as an alternative solution to multipathing • IP bonding allows you to aggregate multiple network interfaces into higher performance network link and provides failover solution Confidential and Proprietary

  45. Oracle Clusterware • Carefully plan your file system layout • If you use ASMLIB with iSCSI disks, don’t map OCR and voting disk to raw devices in RAC environment – use OCFS or NFS • Raw devices are not aware of ASMLIB • In RedHat 4 update 3, the use of /etc/sysconfig/rawdevices is deprecated Confidential and Proprietary

  46. Storage • When planning for the large database consult your storage vendor about RAID configuration, storage provisioning and backup procedures. • As an example – using NetApp as storage appliance allows DBAs to take advantage of RAID-DP (Double Parity) – fault tolerance of RAID 1 at the price of RAID 4. • NetApp flexible architecture allows DBAs to provision additional storage with minimal downtime. • SnapManager for Oracle allows DBAs to combine NetApp snapshot technology with Oracle RMAN to dramatically decrease time taken to backup and restore database. Confidential and Proprietary

  47. Storage Provisioning with ASM • Adding 1TB of storage takes no more than 30 minutes with Netapp • Step1 – Ask your system administrator to plug in Netapp shelves • Step 2 – login to the filer and add new disks to the aggregate leaving 2 spares per array • Step 3 – create new volume and LUNs, add them to the initiator group Confidential and Proprietary

  48. Filer view Confidential and Proprietary

  49. Adding Storage to the Database • Step 4 - After new LUNs created on the filer, reboot the database server to automatically discover new LUNS. You can verify that LUNs are accessible from the database server using NetApp host utilities: Confidential and Proprietary

  50. Creating ASM Disks • Step 5 – run fdisk on new devices. • Step 6 – run /etc/init.d/oracleasm createdisk. • Last step – connect to ASM instance and execute “create diskgroup…” command. You can also use Enterprise Manager db control: Confidential and Proprietary

More Related