Oracle ASM Reduces Cost of VLDB Deployment

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

3
Application Description
4
Logical Database Structure
5
General Loading Schema
6
High Level Physical Layer
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

9
What ASM is NOT

• A cluster file system
• Available for non Oracle files
• Available for OCR and voting disks
• General file system

10
Options prior to ASM

• Raw
• With LVM
• Without LVM

• Cooked
• With LVM
• Without LVM

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 (DBAs)
• Expected thousands of data files
• Didnt 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

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

14
What is iSCSI Protocol?

• A network protocol standard that allows the use
  of the SCSI protocol over TCP/IP networks
• 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

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).

16
Simple iSCSI system
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

19
Separate Disk Groups
20
Shared Disk Groups
21
Shared Disk Group-
Multiple RAC Instances
22
Network Design
23
Typical Performance in MB/s
System Component Throughput /Performance Throughput /Performance
Bits Bytes
16 Port Switch8 2GBit/s 1200 MB/s
Fibre Channel 2 GBit/s 200 MB/s
Disk Controller 2 GBit/s 180 MB/s
GigE NIC 2 GBit/s 80 MB/s
1 Gbit HBA 1 GBit/s 100 MB/s
2 Gbit HBA 2 GBit/s 200 MB/s
24
iSCSI End Point Options
25
Network Configuration
26
Physical Architecture
27
FAS3050 1
28
FAS3050 2
29
FAS3020
30
Database size
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

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

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 ltindex namegt MODIFY
  PARTITION ltpartition namegt UNUSABLE
• On all partitions. If using a Global Index then
  mark the entire index as UNUSABLE
• Step 4 - ALTER INDEX ltindex namegt REBUILD
  PARTITION ltnew partition namegt NOLOGGING
• On the most fresh partition this will enable
  the application to run with the new index ASAP.
• Step 5 - ALTER INDEX ltindex namegt REBUILD
  PARTITION ltpartition namegt 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

36
Gathering Oracle Stats

• Gather partitions statistics (First time)
• Exec dbms_stats.gather_table_stats(ownnamegtltsche
  ma namegt', tabname gt lttab namegt', PARTNAMEgt
  ltpart namegt' , estimate_percent gt
  DBMS_STATS.AUTO_SAMPLE_SIZE, cascadegtTRUE,
  method_optgt'FOR ALL COLUMNS SIZE AUTO')
• Exec dbms_stats.gather_table_stats
  (ownnamegtltschema namegt' , tabnamegtlttab namegt'
  , PARTNAMEgt 'ltpart namegt' , estimate_percentgt1,C
  ASCADEgt true)
• Unlock Statistics (If previously locked)
• Exec dbms_stats.unlock_table_stats(OWNNAMEgtltsche
  ma namegt' , TABNAMEgtlttab namegt')

37
Gathering Oracle Stats More

• Copy statistics
• Execdbms_stats.COPY_TABLE_STATS(OWNNAMEgtltschema
  namegt' , TABNAMEgtlttab namegt ' ,
  SRCPARTNAMEgtltNth partition namegt' ,
  DSTPARTNAMEgt'ltNth1 partition namegt')
• Lock table statistics
• Exec dbms_stats.lock_table_stats(OWNNAMEgtltschema
  namegt' , TABNAMEgtlttab namegt')

38
Index Creation Example
39
Index Creation Example More
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

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

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

45
Oracle Clusterware

• Carefully plan your file system layout
• If you use ASMLIB with iSCSI disks, dont 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

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.

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

48
Filer view
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

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

51
Backup and Recovery
52
SnapManager for Oracle Backups

• A management tool with a GUI and command-line
  interface for Oracle Database administrators that
  simplifies backup, recovery, and cloning for
  Oracle Databases
• Designed to leverage Oracle Database 10g data
  management and grid features such as ASM, RAC,
  and RMAN
• Utilizes NetApp Snapshot technology to create
  extremely fast and space-efficient backups
• Snapshot copies are point-in-time copies of a
  database that are created nearly instantaneously.
  
• These backups can also be registered with Oracle
  RMAN, which facilitates the use of RMAN to
  restore and recover the database at finer
  granularities such as blocks.

53
How SnapManager Works

• To create a successful backup, SnapManager
  performs the following operations
• Determines the list of data files, control files,
  and log files that make up the database. These
  files can be located on one or more ASM disk
  groups.
• Puts the database in hot backup mode
• Creates an atomic Snapshot copy of all the filer
  volumes that make up the ASM disk group
• Ends the hot backup mode
• Clones the Snapshot copy and then renames the
  cloned ASM disk group
• Verifies the backup
• Registers the cloned ASM disk group and storage
  information in the RMAN repository

54
Disk Backup vs. SnapManager Backup

• To demonstrate the benefit of snapshot backup
  technology we measured the time taken to backup
  70GB database to the disk. Heres what we found
• Backup to the disk
• Backup set complete, elapsed time 002520
• It would take about 18 hours to backup 3 TB
  database.

55
Time Log for SnapManager Backup

• 2006-11-10 174345,396 INFO SMO-07100
  Placing database into online backup mode.
• 2006-11-10 174600,724 DEBUG SMO-12000
  Executing SnapDriveCommand
• 2006-11-10 174604,762 DEBUGSMO-12001 Result
  SnapDriveResult (00004.037) (took 4 seconds to
  take a snapshot of the volume)
• 2006-11-10 175555,841 INFO SMO-13037
  Successfully completed operation Backup
• 2006-11-10 175555,947 INFO SMO-13048
  Operation Status SUCCESS
• 2006-11-10 175556,030 INFO SMO-13049
  Elapsed Time 01233.238 (including all backup
  management procedures)
• From database alter log
• Fri Nov 10 174345 2006 ALTER DATABASE BEGIN
  BACKUP
• Fri Nov 10 174642 2006 ALTER DATABASE END
  BACKUP

56
Restore Operation

• Similar to backups, SnapManager for Oracle
  relieves the DBA from having to worry about the
  underlying layout of data in terms of storage
  subsystems, disks, host volumes, or host file
  systems.
• DBAs just choose the backup they want to restore
  from, and SnapManager does the rest.
• DBAs can also specify the date and time or the
  Database SCN to which they would like the
  database restored.

57
Thank you

• Hanan Hit, Principal Database Architect
• Lina Shabelsky, Senior Application and Database
  Engineer