DBA Survival Guide

1
DBA Survival Guide

• Colin Abbott
• Supervisor Database Operations
• McGill University
• colin.abbott_at_mcgill.ca

To be courteous to others, please turn off all
cell phones and pagers.
2
Introduction
3
Our Job 5 years ago

• Monitor 1 or 2 production databases
• Clone test database on demand
• Compile new programs/packages/procedures
• Verify daily cold backups
• Perform upgrades and apply patches
• Verify that gurjobs and sleep-wake are running
• Code some Unix scripts

4
Our Job Today

• Monitor 25 Production databases (and growing)
• Monitor 87 test databases (and growing)
• Hot Backups / RMAN
• OEM (Oracle Enterprise Manager)
• Apache and Oracle Application Server
• Upgrades, Patches
• Clones

5
Our Job Today (cont.)

• Dataguard
• RAC (Real Application Clusters)
• On-line operations
• XML
• Unix Scripting, Windows Scripting
• Web programming, Web tailoring
• Javascript, HTML
• Internet Native Banner (forms services)
• Capacity planning / Hardware planning

6
Our Job Today (cont)

• Storage area Networks (SAN)
• Demand for high availability
• Web services
• Migration to cost based optimizer
• Migration to Oracle 10g
• Migration from MMB to LMB
• Metadirectories
• SSO, LDAP, OID, Active Directory integration
• Change management

7
Our Job Today (cont.)

• Tuning SQL (and training Developers)
• Data warehouse, Operational Data Store, OLAP
• Replication
• Materialized views
• Partitioning
• LDIS
• Portals

8
Our Job Today (cont.)

• Workflow
• Java
• Security threats / Virus (ex. Alert 68)
• Message Queues
• Onames (Oracle Names)
• VPD / FGAC
• Protection of privacy legislation
• Load balancing solutions
• Etc etc etc.

9
Quotes from E-Week

• A reason for additional responsibilities
  placed on DBAs' shoulders can be traced to needed
  mastery of operating-system-related functionality
  steadily migrating to the database. Todd
  Langille, associate director of Administrative
  Computing for Dartmouth College, in Hanover,
  N.H., said more DBAs are being assigned "tweaking
  and tuning" tasks typically associated with
  programmers

10
Quotes from E-Week

• "DBAs have more exposure and responsibility for
  middle-tier applications like Web servers and
  application servers there's a whole middle layer
  of software that has come along for the ride with
  our move toward Web-based applications," said
  Langille. "It's definitely adding up to another
  big chunk of work to an already-burdened staff."

11
Summary

• The lines are becoming blurred between the
  developers and the DBAs and between the DBAs and
  the sysadmins
• The ongoing trend as more functionality is moved
  into the database is that the DBA needs to become
  the expert of everything

12
Goal of Session

• Tips and tricks to help the DBA survive 2004/2005
• Explain some new Oracle features in the context
  of how we are using them
• Note - due to time constraints of the session,
  topics will be high level. More information is
  available in the Oracle administrators guides.

13
Topics

• Oracle 9i new features to implement in
  preparation for Oracle 10g
• Tuning with the new advisors in Oracle 9i
• OEM Monitor the enterprise with grid control
• Our SAN Migration (database reorg and 9i new
  segment management features)

14
Topics (cont.)

• Neat things worth investigating
• Workspace Manager
• History of dataguard
• New methods to gather statistics
• Flashback queries
• Resumable operations
• External Tables
• LogMiner
• RMAN much improved in 9i
• Block Compression
• dynamic_sampling

15
Oracle 9i new features to implement in
preparation for Oracle 10g
16
Oracle 9i new features to implement in
preparation for Oracle 10g

• Intro
• Dynamic SGA
• SPFILE
• UNDO Tablespace
• PGA_AGGREGATE_TARGET
• FAST_START_MTTR_TARGET

17
Intro

• Oracle is advertising 10g as a big change in
  Oracles architecture, this part of the session
  will cover some things that can be done now in
  preparation for 10g
• Oracle 9is goal reduce the number of parameters
  used to configure a database, move towards a
  self-tuning architecture
• Many new features in 9i are infrastructure for
  10g (Only partially implemented or with little or
  no value in 9i)

18
Dynamic SGA

• Prior to 9i
• SGA components were a fixed size
• Needed to reinstance the database to make
  changes.
• Since 9i
• Can dynamically change the values of the buffer
  cache, shared pool and large pool
• Maximum size is controlled by SGA_MAX_SIZE (not
  dynamically alterable)

19
Dynamic SGA (cont.)

• In ORACLE 10g
• ORACLE will self-tune these parameters as the
  load on the database changes
• feature is enabled by setting up Dynamic SGA

20
SPFILE

• Prior to 9i
• The DBA needed to edit the PFILE (init.ora)
  anytime they wanted to persist an alter system
  command.
• Since 9i
• Oracle introduced the SPFILE which is a binary
  representation of the PFILE.
• The DBA can tell Oracle to update the SPFILE as
  part of the alter system command.
• Alter system set undo_retention10800 scope
  MEMORY
• Alter system set undo_retention10800 scope
  SPFILE
• Alter system set undo_retention10800 scope
  BOTH
• In Oracle 10g
• Oracle will want to write to the SPFILE as it
  self-tunes in order to maintain a baseline.

21
UNDO Tablespace

• Prior to 9i
• DBAs configured rollback segments
• Since 9i
• Oracle introduced UNDO Tablespace which
  optionally replaces Rollback segemnts
• You set the undo_retention parameter to tell
  Oracle how long it should try and keep a read
  consistent image
• you can use UNDO tablespace to flashback and run
  a query at a specific point in time.

22
UNDO Tablespace

• In 10g
• Many features were added to flashback in 10g
  (including a new flashback area). To prepare for
  10g should implement auto undo management and get
  familiar with flashback queries.

23
PGA_AGGREGATE_TARGET

• Prior to 9i
• Difficult to tune memory usage for SQL
• SORT_AREA_SIZE
• HASH_AREA_SIZE
• BITMAP_MERGE_AREA_SIZE
• CREATE_BITMAP_AREA_SIZE
• Since 9i
• Oracle has introduced automatic SQL execution
  memory management
• The DBA sets PGA_AGGREGATE_TARGET to the total
  amount of memory you want Oracle to allocate to
  the PGA, Oracle will then allocate this to
  sessions as they need it.

24
PGA_AGGREGATE_TARGET

• Setting PGA_AGGREGATE_TARGET will reduce disk
  operations and make tuning the PGA easier.

25
FAST_START_MTTR_TARGET

• Prior to 9i
• Many parameters were used to control checkpoints.
  
• Difficult to predict time to perform crash
  recovery
• Difficult to commit to a Service Level Agreement
  (SLA)
• Since 9i
• Now only need to set FAST_START_MTTR_TARGET
• This parameter is the number of seconds it should
  take Oracle to perform a crash recovery and be
  ready to open.
• Oracle will set defaults for other parameters
  based on the value of FAST_START_MTTR_TARGET
• Setting it low will impact performance due to
  excessive load placed on db writer.

26
Tuning with the new advisors in Oracle 9i
27
Tuning with the new advisors in Oracle 9i

• Oracle 9i has introduced new advisors to help the
  DBA tune their database.
• MTTR Advisor (mean time to recover)
• Undo Tablespace Advisor
• PGA Target Advisor
• DB Cache Advisor
• Shared Pool Advisor

28
Tuning with the new advisors in Oracle 9i

• Advisors are based on v Views
• Can see a graphical representation using OEM
• 10g will use the advisors to self-tune the
  database

29
STATISTICS_LEVEL

• Prior to 9i
• you had to set parameters for managing every
  type of advice view.
• Since 9i
• Oracle has consolidated this into 1 parameter
  with 3 values

30
STATISTICS_LEVEL

• STATISTICS_LEVELBASICTYPICALALL
• BASIC No advisories or statistics are
  collected.
• TYPICAL The following advisories or statistics
  are collected
• Buffer cache advisory
• MTTR advisory
• Shared Pool sizing advisory
• Segment level statistics
• PGA target advisory
• Timed statistics
• ALL All of TYPICAL, plus the following
• Timed operating system statistics
• Row source execution statistics

31
MTTR Advisor

• Will show /- in disk writes based on the number
  of seconds specified for Oracle to perform crash
  recovery.
• vmttr_target_advice
• Goal Maintain your service level agreements
  without making checkpointing a bottleneck

32
UNDO Advisor

• Will show undo retention vs. size of undo
  tablespace
• Based on formula (undo space undo_retention
  undo blocks per second overhead)
• Undo blocks per second derived from vundostat
• Goal Minimize snapshot too old error, provide
  enough undo to flashback

33
PGA TARGET Advisor

• Will show optimal executions, single pass
  executions and multipass executions for different
  values of PGA_AGGREGATE_TARGET
• Goal to reduce disk sorts and have optimal
  executions in memory

34
DB CACHE Advisor

• Buffer cache advice shows change in physical
  reads (cache miss)
• Goal Obtain optimal cache hit ratio by ensuring
  that blocks are found in cache and do not have to
  be read from disk.

35
Shared Pool Advisor

• Advice shows change in parse time.
• Goal Avoid reloading and parsing frequently used
  statements

36
OEM Monitor the enterprise with grid control
37
OEM Monitor the enterprise with grid control

• Now called Oracle Enterprise Manager 10g grid
  control (OEM10gc)
• No Longer Java Client, OC4J Container running in
  the Oracle Application Server
• FAQ Metalink note 273579.1

38
OEM Monitor the enterprise with grid control

• Supports Host monitoring for
• Sun Solaris 32 bit (8, 9)
• Sun Solaris 64 bit (8,9)
• HP-UX 64 bit (11i)
• HP Tru64 (5.1a)
• IBM AIX 64 bit (5.2)
• RH Linux ES and AS (2.1)
• RH Linux (3)
• United Linux 1.0 (SP3)
• Windows 2000, XP

39
OEM Monitor the enterprise with grid control

• Supported Database targets
• Oracle Database (8.1.7.4, 9.x, 10g)
• Oracle Listener (8.1.7.4, 9.x, 10g)
• Application Server (9.0.2, 9.0.3, 9.0.4)
• Real Application Clusters (9.x, 10g)
• Web Applications (custom targets)
• Management Repository and OMS (10g)
• Management Agent  (10g)

40
OEM Monitor the enterprise with grid control

• What can you do with OEM
• Home page overview of the enterprise
• Monitor a host
• Monitor a database
• Maintenance and Administration
• View performance snapshots and look at historical
  performance, analyse patterns and trends

41
OEM Monitor the enterprise with grid control

• Use OEM to be proactive
• Warning Alerts
• Critical Alerts
• Ability to set your own thresholds

42
OEM Monitor the enterprise with grid control
43
Our SAN Migration (database reorganization and
new segment features in 9i)
44
Our SAN Migration

• Challenges
• ERP systems are growing beyond the capacity of
  local storage.
• Make the database more logically organized
• Reduce errors due to space issues (reduce DBA
  work to maintain segments)

45
Migration to a SAN

• Took this opportunity to reorganize the database
  and implement new segment features in 9i
• After 5 years on Banner was time for a clean up
• Moved to locally managed tablespaces
• Implemented segment monitoring
• Moved to auto segment management
• Set maxextents unlimited on all non-system
  segments
• Organized segments based on size and usage
• Created separate tablespace for LOB data
• Created WORK_DATA tablespace for temporary
  segments

46
Locally Managed Tablespaces

• Extent sizes are managed in a bitmap on the
  tablespace they are no longer managed in the
  dictionary.
• Uniform extent sizes eliminate fragmentation
• With Autoallocate Oracle will determine the
  extent size (always multiples)

47
Locally Managed Tablespaces

• CREATE TABLESPACE WORK_DATA DATAFILE
• '/bdata17/oradata/UP6F/work_data_UP6F_01.dbf'
  SIZE 2000M AUTOEXTEND OFF
• LOGGING
• ONLINE
• PERMANENT
• EXTENT MANAGEMENT LOCAL AUTOALLOCATE
• BLOCKSIZE 8K
• SEGMENT SPACE MANAGEMENT AUTO
• CREATE TABLESPACE DATA1_64M DATAFILE
• '/bdata9/oradata/UP6F/data1_64m_UP6F_01.dbf'
  SIZE 2000M AUTOEXTEND OFF,
• LOGGING
• ONLINE
• PERMANENT
• EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64M
• BLOCKSIZE 8K
• SEGMENT SPACE MANAGEMENT AUTO

48
Auto Segment Space Management

• Free Space in a block is managed in a bitmap
• Replaces freelists, freelist groups and pctused
• Eliminates freelist contention on heavily
  inserted tables.

49
Segment Monitoring

• 2 months in advance turned on segment monitoring
  to track selects, inserts, updates, deletes for
  all segments
• alter table fimsmgr.ftvfund MONITORING
• select from dba_tab_modifications where
  table_name 'FTVFUND
• Shows inserts 1440713, updates 1096550,
  Deletes 657158
• exec dbms_stats.ALTER_DATABASE_TAB_MONITORING(TRUE
  )

50
Logically Organized Structure

• Created tablespaces for Banner data. Decided to
  use the same extent sizes Oracle uses for
  AUTOALLOCATE
• DATA1_64K, DATA1_1M, DATA1_8M, DATA1_64M
• DATA2_64K, DATA2_1M, DATA2_8M, DATA2_64M
• INDX1_64K, INDX1_1M, INDX1_8M, INDX1_64M
• INDX2_64K, INDX2_1M, INDX2_8M, INDX2_64M

51
Logically Organized Structure

• Very large Banner tables get their own tablespace
  (once we go to CBO they will be partitioned for
  better manageability)
• FGBTRND_DATA,FGBTRND_INDX
• FGBTRNH_DATA,FGBTRNH_INDX
• NHRDIST_DATA,NHRDIST_INDX
• SARPERR_DATA,SARPERR_INDX
• SFRFAUD_DATA,SFRFAUD_INDX

52
Logically Organized Structure

• Special Tablespaces
• WORK_DATA
• LOB_64k
• DEVELOPMENT
• PERF_DATA
• MV_LOGS
• MERCURY

53
Migration

• Create new tablespaces on the SAN
• Use Alter table move tablespace command
• Rebuild indexes on new tablespaces
• Use export/import and scripts for LOBs
• Verify if any objects left in old tablespaces
• Drop old tablespaces.
• Backup
• Monitor performance

54
Maintenance

• Having a logically organized structure allows us
  to automate monthly segment maintenance.
• If a segment would take at least one full extent
  in a larger tablespace it will be promoted
• Example if a segment in DATA1_1M has 65 1M
  extents it is a candidate to be moved to DATA1_64M

55
Other neat things!
56
Workspace Manager

• Not for a production database
• Allows you to perform simulations without
  requiring another test instance
• Versions a table so that it can be used for many
  purposes

57
History of Dataguard

• What is a standby database
• Manual Log Shipping in 7.X
• Physical Standby
• Read-Only Physical Standby
• Logical Standby (Why it doesnt work with Banner)
  How 10g will help

58
New methods to gather statistics

• The demise of RBO
• Gather Auto with Segment monitoring
• Gathering system statistics
• Much smarter in 10g

59
Flashback Queries

• Allows you to flashback and see data as of a
  point-in-time
• Requires Automatic Undo Management
• Retention based on UNDO_RETENTION parameter
• Convoluted to use in 9i Release1, improved in 9i
  Release 2 with the select as of clause
• Flashback database in 10g

60
Resumable operations

• Operations that fail because of maxextents, lack
  of space in a tablespace, or quota can now be
  resumed without rolling back and restarting

61
External Tables

• Is really a flat file outside of Oracle
• Can select from it just like any Oracle table
• Uses SQLLOADER to read the file
• Caution Will cause gostage to fail during
  Banner upgrades.

62
LogMiner

• Tool to mine the data in archive logs
• Many enhancements in 9i
• Can use on-line dictionary
• Can dump dictionary to redo logs
• GUI interface in OEM
• Tracks DDL
• Skips past corrupt logs
• Can now dedicate a tablespace to logminr (rather
  then being in system)

63
RMAN

• Oracle is pushing people towards RMAN
• Much Improved in 9i
• Persistent configuration
• Block Recovery
• Restartable backups
• Integration with OEM
• Clone a database now supported

64
Block Compression

• Compress attribute of a segment
• Only compresses rows during direct load
  operations
• Best compression ratio when loaded in order of a
  column with low cardinality (column with most
  like data)

65
Block Compression

• Example
• Create table TBRACCD_2004 COMPRESS as
• select from taismgr.tbraccd order by
  tbraccd_term_code

66
Block Compression

• Determine low cardinality from DBA_TAB_COL_STATIST
  ICS or by executing a query like select count(
  distinct(column) from table
• Not currently supported with Logical standby
  database
• Better suited for DSS then OLTP

67
Dynamic sampling

• Database parameter or Hint
• Will sample the data in the tables prior to
  parsing a statement
• Will use CBO

68
Surviving

• Make management aware of our job role and its
  importance to the organization
• Align DBA goals with business objectives
• Make management aware of the importance of
  training (50 of everything DBAs know will be
  obsolete in one year)
• Keep up to date on new features
• Try and learn something new every day
• Have fun

69
Questions
Please fill out the evaluation form for this
session and hand it to the moderator on your way
out.