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
• Dont 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 10 or 01 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 10 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 doesnt wait for archive log writes
• However, you dont 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
  -gt System -gt Advanced -gt Settings -gt 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
• -gt Administrative Tools -gt 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 dont 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.