DBA Survival Guide

1. DBA Survival Guide Colin Abbott Supervisor Database Operations McGill University colin.abbott@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 DBA Survival Guide

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 DBA Survival Guide

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 DBA Survival Guide

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 DBA Survival Guide

7. Our Job Today (cont.) • Tuning SQL (and training Developers) • Data warehouse, Operational Data Store, OLAP • Replication • Materialized views • Partitioning • LDIS • Portals DBA Survival Guide

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. DBA Survival Guide

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” DBA Survival Guide

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." DBA Survival Guide

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 DBA Survival Guide

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 administrator’s guides. DBA Survival Guide

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) DBA Survival Guide

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 DBA Survival Guide

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 DBA Survival Guide

17. Intro • Oracle is advertising 10g as a big change in Oracle’s architecture, this part of the session will cover some things that can be done now in preparation for 10g • Oracle 9i’s 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) DBA Survival Guide

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) DBA Survival Guide

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 DBA Survival Guide

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_retention=10800 scope MEMORY Alter system set undo_retention=10800 scope SPFILE Alter system set undo_retention=10800 scope BOTH • In Oracle 10g: • Oracle will want to write to the SPFILE as it self-tunes in order to maintain a baseline. DBA Survival Guide

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. DBA Survival Guide

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. DBA Survival Guide

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. DBA Survival Guide

24. PGA_AGGREGATE_TARGET • Setting PGA_AGGREGATE_TARGET will reduce disk operations and make tuning the PGA easier. DBA Survival Guide

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. DBA Survival Guide

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 DBA Survival Guide

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 DBA Survival Guide

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 DBA Survival Guide

30. STATISTICS_LEVEL • STATISTICS_LEVEL={BASIC|TYPICAL|ALL} • 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 DBA Survival Guide

31. MTTR Advisor • Will show +/- in disk writes based on the number of seconds specified for Oracle to perform crash recovery. • v$mttr_target_advice • Goal: Maintain your service level agreements without making checkpointing a bottleneck DBA Survival Guide

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 v$undostat • Goal: Minimize snapshot too old error, provide enough undo to flashback DBA Survival Guide

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 DBA Survival Guide

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. DBA Survival Guide

35. Shared Pool Advisor • Advice shows change in parse time. • Goal: Avoid reloading and parsing frequently used statements DBA Survival Guide

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 DBA Survival Guide

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 DBA Survival Guide

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) DBA Survival Guide

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 DBA Survival Guide

41. OEM: Monitor the enterprise with “grid control” • Use OEM to be proactive • Warning Alerts • Critical Alerts • Ability to set your own thresholds DBA Survival Guide

42. OEM: Monitor the enterprise with “grid control” DBA Survival Guide

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) DBA Survival Guide

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” DBA Survival Guide

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) DBA Survival Guide

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; DBA Survival Guide

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. DBA Survival Guide

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); DBA Survival Guide

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 DBA Survival Guide