Sandy Gruver Senior Technical Consultant HP/Oracle Advanced Technology Center sandra.gruver@hp

1. Best Practices for Oracle on HPUX Sandy Gruver Senior Technical ConsultantHP/Oracle Advanced Technology Center sandra.gruver@hp.com

2. Installation setup for Oracle on HPUX Recommended Kernel parameter settings for Oracle Using WLM with Oracle and HPUX partitions File System recommendations Storage Layout Recommendations Backup and Recovery best practices Day to day Oracle DBA hints and best practices High Availability for Oracle on HPUX Monitoring your Oracle environment Support for Oracle on HPUX and Oracle information Topics

3. Before Installing Oracle on HPUX

4. Brief review HPUX SysAdm tool - SAM

5. H/W and S/W requirementsOverall system requirements • Operating System • HPUX 11.0 and HPUX 11i. • Oracle 9i onward is only available for 64 bit HPUX • S/W requirements • Any X server supported by the UNIX system • Need to have ar, cc and ld under /usr/ccs/bin • Need to have Java make installed • Physical Memory • Minimum 256 MB for 9i Server (as per release notes). Suggested to have around 4 GB or more • Swap Space • Twice the amount of physical Memory or Minimum 400 MB • Disk Space • Approx 3 GB for Database S/W and additional space for seed database etc.

6. H/W and S/W requirementsPatches are extremely important • Install the necessary HP-UX patches • Listed in the Oracle Installation Guide • For patch bundles: • http://www.software.hp.com/SUPPORT_PLUS • For individual patches: • http://itresourcecenter.hp.com • To check on patches: • $ /usr/sbin/swlist -l patch • $ /usr/sbin/swlist -l patch patch_number • $ /usr/sbin/swlist -l bundle • Increase /tmp space to at least 2GB • Modify Kernel parameters for Oracle

7. Enhancing performance of Oracle enable Sched_Noage As root, create the file /etc/privgroup dba MLOCK(for asynch IO) dba RTSCHED RTPRIO(for priority scheduling) Issue the commands: # /usr/sbin/setprivgrp -f /etc/privgroup # /usr/sbin/getprivgrp dba Set the HPUX_SCHED_NOAGE Oracle initialization parameter • On HP-UX 11.0, the range is 153 to 255 • On HP-UX 11i, the range is 178 to 255

8. Oracle Users and Groups • Create UNIX groups (oinstall, dba) # /usr/sbin/groupadd oinstall # /usr/sbin/groupadd dba • Create a UNIX account (oracle) to own Oracle software # /usr/sbin/useradd -g oinstall -G dba oracle • For Oracle10g: Create a Unix account (extjob) to own the executable extjob # /usr/sbin/useradd extjob • After installing Oracle, make these changes to the extjob file • # cd oracle_home/bin • # mv extjob.nobody extjob • # chown extjob extjob • # chmod 4711 extjob

9. A Few Important Environment variables • ORACLE_HOME e.g. export ORACLE_HOME=/oracle/9i_64b • ORACLE_SID e.g. export ORACLE_SID=oratest • PATH e.g. export PATH=$ORACLE_HOME/bin:$PATH • SHLIB_PATH/LD_LIBRARY_PATH e.g. export LD_LIBRARY_PATH=$ LD_LIBRARY_PATH:$ORACLE_HOME/lib64

10. File and Directory Setup tasks • Set the home directory of user oracle to • $ORACLE_OWNER $HOMEdirectory* • Set the default shell to /bin/sh for the Bourne shell • In the .profile, set • umask 022 • xhost + • Check the Oracle Inventory information after installation # more /var/opt/oracle/oraInst.loc inventory_loc=/u01/app/oracle/oraInventory inst_group=oinstall *See simplified OFA later in presentation

11. Implementing Asynchronous I/ONOTE: Done ONLY if using RAW (not fs) storage 1. Enter the following command as the root user: # sam 2. Choose the Kernel Configuration area. 3. Choose the Drivers area. 4. Choose the asynchronous disk driver (asyncdsk). 5. Select Actions>Add Driver to Kernel. 6. Select List>Configurable Parameters. 7. Choose the MAX_ASYNC_PORTS parameter. 8. Select Action>Modify Configurable Parameter. 9. Specify a new value for the parameter, then choose OK Set Oracle initialization parameter DISK_ASYNCH_IO to TRUE.

12. Implementing Asynchronous I/O

13. Recommended Kernel parameter settings for Oracle

14. HP-UX Kernel Settings for Oracle DBs • Specified in the documentation for Oracle • See “Oracle Database Installation Guide” • To list all kernel parameters $/usr/sbin/kmtune –l |more Or use SAM • To check 32 or 64 bit HP-UX 11.x $ /bin/getconf KERNEL_BITS $ 64 • To check 32 or 64 bit Oracle software version $ cd $ORACLE_HOME/bin $ file oracle $ oracle: ELF-64 executable object file - PA-RISC 2.0 (LP64)

15. Recommended Minimum HP-UX Kernel Settings for Oracle DBs File System buffer-Cache Parameters: Open or Locked Files Parameters:

16. Recommended Minimum HP-UX Kernel Settings for Oracle DBs Parameters for Logical Volume Manager: Parameters for Memory Paging and Variable Page Size:

17. Recommended Minimum HP-UX Kernel Settings for Oracle DBs Parameters for Process Management:

18. Recommended Minimum HP-UX Kernel Settings for Oracle DBs InterProcessCommunication Message Parameters: InterProcessCommunication Semaphore Parameters:

19. Recommended Minimum HP-UX Kernel Settings for Oracle DBs InterProcessCommunication Shared Memory Parameters: Miscellaneous Parameters:

20. Updating Kernel Parameters Edit /stand/vmunix • Backup /stand/system • Edit/modify /stand/system • config /stand/system ? Check that it created vmunix_test in local directory • Backup /stand/vmunix • mv /stand/vmunix_test /stand/vmunix • shutdown -r OR Use SAM

21. Using WLM with Oracle and HPUX partitions

22. HP Virtual Server Environment:server resource flexing for applications Policy engine provisions resources based on SLOs and business priorities for each application. server resource pool security Reporting Oracle Apps

23. OS image with SW isolation OS image with SW isolation OS image with SW isolation HP Partitioning Continuum Hard partitions nPar Virtual partitions vPar Clustered nodes Resources partitions PRM/pSets 1 OS image hard partition Application 1 with guaranteed compute resources OS image with HW isolation Application 2 with guaranteed compute resources Based on CPUs or percentages OS image with HW isolation OS image with HW isolation Application n with guaranteed compute resources HP-UX Workload Manager Isolation Highest degree of separation Flexibility Highest degree of dynamic capabilities

24. vPar1 vPar2 vPar3 • multiple applications, instances or versions • name space and resource isolation HP-UXRevision APatch Lvl 1 HP-UXRevision A Patch Lvl 2 HP-UXRevision BPatch Lvl 1 Dept. AApp 1 Dept. AApp 1’ Dept. B App 2 Hardware Platform / Hard Partition vPar Monitor • creates illusion of separate hardware platforms • manages shared physical resources • monitors health of operating system instances vPars logical overview • individual “servers” • different OS revs • each OS custom-tunable • dynamic resource allocations

25. Dept. BApp 2 Dept. BApp 3 Dept. AApp 1 Dept. AApp 1’ HP-UX Revision B.3 HP-UX Revision A.1 HP-UX Revision B.3 HP-UX Revision A.2 HP-UX Virtual Partitions • Why choose vPars over nPars? • vPars provides: • Dynamic processor movement without rebooting the partition • Single cpu granularity • Can run within an nPar • Why choose vPars over resource partitions? • vPars provides: • Software fault isolation • Different versions of the OS • Application isolation Multiple HP-UX instances running on the same system or in the same nPar rp5470, rp7400, Superdome, rp8400, rp7410, Itanium2 server

26. Where vPars provide the most value • System/data center consolidation • development/test environments • increased system utilization • varying workload requirements: • time of day: order entry during day, batch at night • time of month (payroll, end-of-month/end-of-year financials • as particular needs require • service provider (providing system resources to different users/applications) • unique application tuning of O/S • time zoning

27. File System recommendations

28. File system Options Using Online JFS improves performance • Set these mount options • delaylog • nodatainlog • mincache=direct • convosync=direct • Enabling largefiles with Online JFS# fsadm -F vxfs -o largefiles /filesystem

29. Oracle db_block_size • The default Oracle db_block_size on HP-UX is 2048. • We recommend using: • db_block_size = 8192 for OLTP Applications • db_block_size = 8192 to 16384 for DSS/DW Applications • LVM designed for 8192 blocksize • Oracle RAC may benefit from a smaller db_block_size to reduce the amount of data to transfer between the nodes for cache fusion • Must be set when creating database

30. Data Storage Format Raw or File System? • Oracle databases can be implemented either: • in file systems, or • in “raw” format • File Systems: • overhead (space, CPU, and locking) • double-buffering & synchronous I/O

31. Oracle mapping to LVM

32. Space Layout File System Raw FS overhead Oracle Data Oracle Data FS file Logical Volume Oracle Data Oracle Data FS file Logical Volume empty space Logical Volume

33. “Raw” vs. File System some common misconceptions FACT PERCEPTION • Raw requires ugly low-level UNIX commands. • Raw uses LVM just like file systems. • If you manage a FS you already manage raw. • Need rocket scientists to understand & manage.

34. Database Administration Example preparing to create a database • FILE SYSTEM • create volume group • create logical volumes • leave extra room • ‘newfs’ on logical vols • mount file systems • create Oracle files • tie to fs files • /orafs/ora/file1.dbf • RAW • create volume group • create logical volumes • create extra LVs • create Oracle files • tie to LVs • /dev/vgora/rorafile1.dbf

35. Database Administration Example define oracle data commands FILE SYSTEM CREATEDB: STARTUP NOMOUNT CREATE CONTROLFILE … DATAFILE '/ora1/data01.dbf' RAW CREATEDB: STARTUP NOMOUNT CREATE CONTROLFILE … DATAFILE '/dev/vg9idata/rdata01_2000M.dbf'

36. Database Administration Example typical database tasks MONITOR SPACE FS: OEM + bdf RAW: OEM ADD MORE SPACE FS: use extra space in FS or create new FS RAW: use extra LVs or create new VGs ACCIDENTALLY DESTROY DATABASE FS: ‘rm -R *’ or ‘newfs’ or ‘lvremove’ RAW: ‘lvremove’

37. Backup & Recover • FILE SYSTEM • RMAN & (Storage Data Protector) • fbackup • tar or cpio or dd • optionally pipe to compress • RAW • RMAN & (Storage Data Protector) • dd • optionally pipe to compress RMAN is the preferred method of backing up Oracle RMAN does not distinguish between raw and FS

38. Summary Raw-based Databases on HP-UX • Better performance than HP-UX filesystems • 33-100% better throughput • No harder to manage than file systems • same tools & techniques as file-system admin

39. Storage Layout Recommendations

40. Storage technologythen vs. now

41. The usual database layout • sort database objects by size and expected I/O volume • implement high-volume objects in striped volumes with various stripe-widths • squeeze other objects in where they fit • monitor performance of individual disks and objects • play ‘chess’ with file placement to find best performance

42. ‘SAME’ technique(proposed by Oracle’s Juan Loaiza) Stripe And Mirror Everything • stripe all files across maximum # disks • use 1MB stripe size • use mirroring for high availability • place “hot” files on outer edge of disks • keep it simple

43. SAME advantages • large I/Os minimize impact of disk head movement • very wide stripe-set allows full I/O throughput capacity to help all transactions • no need to consider characteristics of individual files/tables/transactions

44. SAME modified for large storage arrays • increase stripe depth to 4-8MB • implement small critical objects with small stripe • ignore disk-geometry considerations • create separate stripesets for different subsets of storage (different RAID levels or disk size/speeds) • possibly move redo logs to separate device OR use cache LUN

45. Oracle data organization tablespace • Objects & Tables • data, index, etc • Tablespaces • Files • map to space at OS level tables/objects ‘logical’ file file file file ... ‘physical’

46. Oracle mapping to LVM

47. tablespace tablespace tablespace oracle LV LV LV LVM vol. grp. PV PV PV PV PV ... array LUN LUN LUN LUN LUN LUN LUN LUN LUN EXAMPLE: PV mapping to a disk array PV mapping to the hp xp array Array (RAID) group Array (RAID) group Array (RAID) group

48. Implementing SAME • decide “stripeset width” (# disks or array groups) • four to eight groups recommended • create one volume group per stripeset • create multiple logical volumes per VG • (create filesystems on top of LVs) • allocate Oracle objects among stripesets • map Oracle files to LVs or to filesystem files

49. Implementing SAME • create LVs using LVM ‘extent striping’ • use 4MB or 8MB extents • divide objects evenly among VGs • keep each object wholly contained in a VG • for raw I/O • create standard-sized LVs • use symbolic links in Oracle to point to file locations

50. Special Considerations for Oracle Redo Logs • Online logs deserve very special attention • multiplex logs in addition to RAID • locate members so they share no common points of failure at the disk, channel, or board level • where possible, locate on dedicated (or otherwise quiet) disk spindles • Possible downside effects • excess of disk capacity to meet all these constraints • isolation to single disks or LUNs may not be possible • peak throughput is reduced