Oracle RAC on Windows Server 2003 Best Practices

1. Oracle RAC on Windows Server 2003 Best Practices Summary of Additional Best Practices

2. Objectives The objectives for this unit are to learn Best Practices in the following areas: • Hardware • Storage • Database Layout • Maintenance/Operations • Performance Monitoring and Analysis • Backup/Restore Policies • Windows Server Optimizations • General Oracle Best Practices

3. Hardware • Use “Name Brand” servers • An Enterprise Oracle database needs Enterprise-class reliability and performance • An Enterprise Oracle database needs Enterprise-class Support • Don’t skimp on RAM • If Oracle Buffer Cache is too small for an instance, performance will suffer • Maximize the size of level 2 and level 3 cache on the processors • Oracle performance is strongly influenced by processor cache

4. Hardware - Interconnect • Use a high speed interconnect • Use a low overhead interconnect • Use separate switches for private interconnect and public networks • Do not use a crossover cable • Crossover cables have been known to cause problems with NICS during failover events

5. Storage • Size the storage subsystem • Must be able to handle the required I/O operations needed • One disk can handle approximately 125 random I/Os per second without queuing (300 sequential I/Os per second) • If your application under full load generates 1000 random I/Os per second, you will require at least 8 disks • Must be reliable and fault tolerant • Must be scalable

6. Storage • Use hardware or software disk striping • Use Oracle SAME – Stripe and Mirror Everywhere • Implement with hardware striping • Oracle 10g ASM can be implemented with software striping and (optional) hardware striping • RAID 1+0 or 0+1 preferred • Good read and write performance • Higher cost than RAID 5 • RAID 5 can be a detriment for transactional databases • Good read performance • Significant write overhead • The larger the stripe width, the better the performance • One disk can perform only 125 random I/Os per second without queuing • A ten disk striped set can perform 1250 I/Os per second without queuing

7. Storage - ASM • Use large ASM disk groups for the main data file storage area • 16+ disks in a group offers optimal performance with the disk-striping algorithm for ASM • Use RAID 1 disk pairs or multiple RAID 10 sets of disks • For smaller ASM disk groups, consider using a single hardware striped RAID group • The software striping algorithm is less efficient than hardware for small disk groups • If you have a High Availability configured disk array, use external redundancy instead of software mirroring.

8. Database Layout • Place all database files on one or more large striped RAID groups • DO NOT need to separate data and index tablespaces, or system and temp, etc. • It is more important to achieve parallel throughput through disk striping than data type separation • Exception: Partitioned tablespaces can be placed on multiple “disks”, as long as each “disk” is actually a 4 disk RAID 1+0 stripe set (or bigger) • A large ASM disk group accomplishes the same thing as a large hardware striped RAID group

9. Database Layout • Place Redo Log files on separate disks • Each database instance should get its own dedicated RAID 1 disk pair • This optimizes for sequential I/O, allowing up to 300 transactional I/Os per second • Yes, this wastes disk space. So what? Would you rather save a few gigabytes of disk space or get double the transactional performance? • Place Archive Log files on separate disks • Do this for security rather than performance • Archive log writes are performed asynchronously – the database doesn’t wait for archive log writes • However, you don’t want to lose your archive logs if you happen to lose your database files in a disaster

10. Maintenance/Operations • Analyze database objects on a regular basis • Optimizes performance • Once per week minimum • After every major database load • Correct invalid database objects on a regular basis • Use object recompile scripts • Run after every major database load or import • Rebuild indexes on a regular basis • B-tree indexes become unbalanced after multiple inserts and updates • Reorganize tablespaces periodically • Limits tablespace fragmentation

11. Maintenance/Operations • Use command line scripts to automate repetitive tasks • Use either the default Windows shell or any of the full-featured scripting languages (i.e. WSH and VBScript) • Use the AT command to schedule tasks

12. Performance Monitoring and Analysis • Consult the customer • Ask the user community how the system seems • This can be a good indication of a problem • This can often be unreliable information • Log user complaints • The log can be used to determine trends • The log can be used to document problem queries • The Log can be used to validate fixes

13. Performance Monitoring and Analysis • Regular monitoring • Regular monitoring can be used to determine long term resource usage • Perform long-term monitoring • Analyze long-term logs on a regular basis • Instantaneous monitoring has limited use • Determine your metrics and monitor them regularly • Take advantage of third party tools. • How well these tools server you depend on how well you have configured them for your installation. • Alerts should be set. • Rules should be set.

14. Performance Monitoring and Analysis • Use Oracle Performance Counters to your advantage • Collect in log files • Set thresholds for Event Alerts • Create STATSPACK or AWR reports on a regular basis • Create snapshots one or two times a day • Create reports spanning different length periods (short and long term) • Do not span database shutdown/startup • Record data for long term trends

15. Performance Monitoring and Analysis • Pick a performance tuning methodology • Develop a methodology for Tuning • Stick with it • Set Goals • Keep Records!!

16. Backup/Restore Policies • A production database should always run in Archive Log mode • Allows capability to restore to last logged transaction • Archive logs dating from the last full backup should be immediately accessible in case of the need to recover • A full backup should be scheduled at least once a week • Incremental backups can be run in between • Daily backups are recommended • Cold or hot OS backups may be performed • Alternatively, RMAN backups may be performed • Export and Import can supplement, but not replace other backup methods • Only backups methods that can take advantage of archive logs can be used to restore recent transactions

17. Backup/Restore Policies • Document your recovery method • Keep a manual readily available with a step-by-step guide to recovery • Update the manual frequently • “Dry run” your recovery method • If at all possible, test your recovery method by simulating a disaster • This may require test servers/test storage • Perform a complete database recovery, and record your response time • Perform a post-test assessment of your recovery readiness

18. Windows • Disable Unnecessary Services • License Logging Service • Plug and Play • Remote Access Autodial Manager • Remote Access Connection Manager • Remote Access Server • Telephony Service • These are not necessary on an Oracle server

19. Windows • Close unnecessary foreground applications • Remove all applications from Startup folders of Windows Server console operators. • Minimize the window when executing long-running scripts from a command prompt, to prevent excessive window repaints. • Disable screen savers, which can saturate the CPU. If necessary, choose Blank Screen.

20. Windows • Apply the Latest reliable Service Pack • In general, Microsoft Service Packs improve performance and are desirable. • However, some new Service Packs can interfere with Oracle Performance. • It is usually best to wait a few weeks until a Service pack is known to be effective and problem-free. • Always install with an Uninstall folder, so that the Service Pack can be reversed later, if necessary.

21. Windows • Adjust Performance Options under Control Panel -> System -> Advanced -> Settings -> Advanced • Set Processor scheduling to favor background processes • Set Memory usage to favor Programs over the System cache • Set Virtual memory to 1-4 times the size of RAM. If possible, split the memory across multiple physical disks

22. Windows • Adjust Server Roles under Control Panel • -> Administrative Tools -> Configure Your Server Wizard • Set the Server Role as an Application Server • Do not configure as a File Server, Print Server, or Primary or Backup Domain Controller

23. General Oracle Best Practices • Use locally managed tablespaces • Use uniform extent size • Use automatic segment management • Use automatic UNDO management • These are all defaults for Oracle 10g • Make sure old scripts don’t “hardwire” legacy methods of resource allocation

24. Review • Which is more important for Oracle performance, processor speed or processor cache size? • True or False: Oracle data should be split across separate individual disks by data type for optimal performance. • True or False: Oracle servers should be configured as Application Servers. • Name some Oracle features that you should take advantage of.

25. Summary Best practices were discussed in the following areas: • Hardware • Storage • Database Layout • Maintenance/Operations • Performance Monitoring and Analysis • Backup/Restore Policies • Windows Server Optimizations • General Oracle Best Practices Following these practices will result in improved performance and reliability.