oracle asm reduces cost of vldb deployment l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Oracle ASM Reduces Cost of VLDB Deployment PowerPoint Presentation
Download Presentation
Oracle ASM Reduces Cost of VLDB Deployment

Loading in 2 Seconds...

play fullscreen
1 / 57

Oracle ASM Reduces Cost of VLDB Deployment - PowerPoint PPT Presentation


  • 882 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Oracle ASM Reduces Cost of VLDB Deployment' - Melvin


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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
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

logical database structure
Logical Database Structure

Management Db

Operational

Data Store

Historical

Data Store

Archive

Data Store

(SATA)

OLAP

Confidential and Proprietary

general loading schema
General Loading Schema

Staging

ODS –

Last Hour

Aggregators

OLAP

Data Warehouse

Loader

Bus

Confidential and Proprietary

high level physical layer
High Level – Physical Layer

Confidential and Proprietary

asm flexible efficient time saver
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

what asm is not
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

options prior to asm
Raw

With LVM

Without LVM

Cooked

With LVM

Without LVM

Options prior to ASM

Confidential and Proprietary

why we chose asm in the first place
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

which protocol to use
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

what is iscsi protocol
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

wikipedia iscsi definitions
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

simple iscsi system
Simple iSCSI system

Confidential and Proprietary

deployment models18
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

separate disk groups
Separate Disk Groups

Confidential and Proprietary

shared disk groups
Shared Disk Groups

Confidential and Proprietary

shared disk group multiple rac instances
Shared Disk Group- Multiple RAC Instances

Confidential and Proprietary

typical performance in mb s
Typical Performance in MB/s

Confidential and Proprietary

iscsi end point options
iSCSI End Point Options

Confidential and Proprietary

network configuration
Network Configuration

Confidential and Proprietary

fas3050 1
FAS3050 #1

Confidential and Proprietary

fas3050 2
FAS3050 #2

Confidential and Proprietary

fas3020
FAS3020

Confidential and Proprietary

database size
Database size

Confidential and Proprietary

oracle operations reduce disk i o
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

index creation new app version
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

index creation new app version35
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

gathering oracle stats
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

gathering oracle stats more
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

index creation example
Index Creation Example

Confidential and Proprietary

index creation example more
Index Creation Example – More

Confidential and Proprietary

netapp asm our best practices
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

rac 10gr2 implementation learnings
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

network implementation
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

oracle clusterware
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

storage
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

storage provisioning with asm
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

filer view
Filer view

Confidential and Proprietary

adding storage to the database
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

creating asm disks
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

snapmanager for oracle backups
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.

Confidential and Proprietary

how snapmanager works
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

Confidential and Proprietary

disk backup vs snapmanager backup
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. Here’s what we found:
  • Backup to the disk:
  • Backup set complete, elapsed time: 00:25:20
  • It would take about 18 hours to backup 3 TB database.

Confidential and Proprietary

time log for snapmanager backup
Time Log for SnapManager Backup
  • 2006-11-10 17:43:45,396 [INFO ]: SMO-07100: Placing database into online backup mode.
  • 2006-11-10 17:46:00,724 [DEBUG]: SMO-12000: Executing SnapDriveCommand
  • 2006-11-10 17:46:04,762 [DEBUG]:SMO-12001: Result SnapDriveResult (0:00:04.037) (took 4 seconds to take a snapshot of the volume)
  • 2006-11-10 17:55:55,841 [INFO ]: SMO-13037: Successfully completed operation: Backup
  • 2006-11-10 17:55:55,947 [INFO ]: SMO-13048: Operation Status: SUCCESS
  • 2006-11-10 17:55:56,030 [INFO ]: SMO-13049: Elapsed Time: 0:12:33.238 (including all backup management procedures)
  • From database alter log:

Fri Nov 10 17:43:45 2006 ALTER DATABASE BEGIN BACKUP

Fri Nov 10 17:46:42 2006 ALTER DATABASE END BACKUP

Confidential and Proprietary

restore operation
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.

Confidential and Proprietary

thank you

Thank you

Hanan Hit, Principal Database Architect

Lina Shabelsky, Senior Application and Database Engineer