DBA Survival Guide Colin Abbott Supervisor Database Operations

DBA Survival Guide Colin Abbott Supervisor Database Operations Mc. Gill University colin. abbott@mcgill. ca To be courteous to others, please turn off all cell phones and pagers. DBA Survival Guide

Introduction DBA Survival Guide

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 9/25/2020 DBA Survival Guide 3

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 9/25/2020 DBA Survival Guide 4

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 9/25/2020 DBA Survival Guide 5

Our Job Today (cont) • • • Storage area Networks (SAN) Demand for high availability Web services Migration to cost based optimizer Migration to Oracle 10 g Migration from MMB to LMB Metadirectories SSO, LDAP, OID, Active Directory integration Change management 9/25/2020 DBA Survival Guide 6

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

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. 9/25/2020 DBA Survival Guide 8

Quotes from E-Week “A reason for additional responsibilities placed on DBAs' shoulders can be traced to needed mastery of operating-systemrelated 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” 9/25/2020 DBA Survival Guide 9

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 alreadyburdened staff. " 9/25/2020 DBA Survival Guide 10

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 9/25/2020 DBA Survival Guide 11

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. 9/25/2020 DBA Survival Guide 12

Topics • Oracle 9 i: new features to implement in preparation for Oracle 10 g • Tuning with the new advisors in Oracle 9 i • OEM: Monitor the enterprise with “grid control” • Our SAN Migration (database reorg and 9 i new segment management features) 9/25/2020 DBA Survival Guide 13

Topics (cont. ) • Neat things worth investigating – – – – – Workspace Manager History of dataguard New methods to gather statistics Flashback queries Resumable operations External Tables Log. Miner RMAN much improved in 9 i Block Compression dynamic_sampling 9/25/2020 DBA Survival Guide 14

Oracle 9 i: new features to implement in preparation for Oracle 10 g DBA Survival Guide

Oracle 9 i: new features to implement in preparation for Oracle 10 g • • • Intro Dynamic SGA SPFILE UNDO Tablespace PGA_AGGREGATE_TARGET FAST_START_MTTR_TARGET 9/25/2020 DBA Survival Guide 16

Intro • Oracle is advertising 10 g 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 10 g • Oracle 9 i’s goal: reduce the number of parameters used to configure a database, move towards a “self-tuning” architecture • Many new features in 9 i are infrastructure for 10 g (Only partially implemented or with little or no value in 9 i) 9/25/2020 DBA Survival Guide 17

Dynamic SGA • Prior to 9 i: – SGA components were a fixed size – Needed to reinstance the database to make changes. • Since 9 i: – 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) 9/25/2020 DBA Survival Guide 18

Dynamic SGA (cont. ) • In ORACLE 10 g: – ORACLE will “self-tune” these parameters as the load on the database changes – feature is enabled by setting up Dynamic SGA 9/25/2020 DBA Survival Guide 19

SPFILE • Prior to 9 i: – The DBA needed to edit the PFILE (init. ora) anytime they wanted to persist an alter system command. • Since 9 i: – 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 10 g: – Oracle will want to write to the SPFILE as it self-tunes in order to maintain a baseline. 9/25/2020 DBA Survival Guide 20

UNDO Tablespace • Prior to 9 i: – DBAs configured rollback segments • Since 9 i: – 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. 9/25/2020 DBA Survival Guide 21

UNDO Tablespace • In 10 g: – Many features were added to flashback in 10 g (including a new flashback area). To prepare for 10 g should implement auto undo management and get familiar with flashback queries. 9/25/2020 DBA Survival Guide 22

PGA_AGGREGATE_TARGET • Prior to 9 i: – Difficult to tune memory usage for SQL • • SORT_AREA_SIZE HASH_AREA_SIZE BITMAP_MERGE_AREA_SIZE CREATE_BITMAP_AREA_SIZE • Since 9 i: – 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. 9/25/2020 DBA Survival Guide 23

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

FAST_START_MTTR_TARGET • Prior to 9 i: – 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 9 i: – 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. 9/25/2020 DBA Survival Guide 25

Tuning with the new advisors in Oracle 9 i DBA Survival Guide

Tuning with the new advisors in Oracle 9 i • Oracle 9 i 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 9/25/2020 DBA Survival Guide 27

Tuning with the new advisors in Oracle 9 i • Advisors are based on v$ Views • Can see a graphical representation using OEM • 10 g will use the advisors to “self-tune” the database 9/25/2020 DBA Survival Guide 28

STATISTICS_LEVEL • Prior to 9 i: – you had to set parameters for managing every type of advice view. • Since 9 i: – Oracle has consolidated this into 1 parameter with 3 values 9/25/2020 DBA Survival Guide 29

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 9/25/2020 DBA Survival Guide 30

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 9/25/2020 DBA Survival Guide 31

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 9/25/2020 DBA Survival Guide 32

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 9/25/2020 DBA Survival Guide 33

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. 9/25/2020 DBA Survival Guide 34

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

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

OEM: Monitor the enterprise with “grid control” • Now called Oracle Enterprise Manager 10 g grid control (OEM 10 gc) • No Longer Java Client, OC 4 J Container running in the Oracle Application Server • FAQ Metalink note # 273579. 1 9/25/2020 DBA Survival Guide 37

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 (11 i) HP Tru 64 (5. 1 a) IBM AIX 64 bit (5. 2) RH Linux ES and AS (2. 1) RH Linux (3) United Linux 1. 0 (SP 3) Windows 2000, XP 9/25/2020 DBA Survival Guide 38

OEM: Monitor the enterprise with “grid control” • Supported Database targets: – Oracle Database (8. 1. 7. 4, 9. x, 10 g) – Oracle Listener (8. 1. 7. 4, 9. x, 10 g) – Application Server (9. 0. 2, 9. 0. 3, 9. 0. 4) – Real Application Clusters (9. x, 10 g) – Web Applications (custom targets) – Management Repository and OMS (10 g) – Management Agent (10 g) 9/25/2020 DBA Survival Guide 39

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 9/25/2020 DBA Survival Guide 40

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

OEM: Monitor the enterprise with “grid control” 9/25/2020 DBA Survival Guide 42

Our SAN Migration (database reorganization and new segment features in 9 i) DBA Survival Guide

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) 9/25/2020 DBA Survival Guide 44

Migration to a SAN • Took this opportunity to reorganize the database and implement new segment features in 9 i • 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” 9/25/2020 DBA Survival Guide 45

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) 9/25/2020 DBA Survival Guide 46

Locally Managed Tablespaces • • CREATE TABLESPACE WORK_DATAFILE '/bdata 17/oradata/UP 6 F/work_data_UP 6 F_01. dbf' SIZE 2000 M AUTOEXTEND OFF LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8 K SEGMENT SPACE MANAGEMENT AUTO; • • CREATE TABLESPACE DATA 1_64 M DATAFILE '/bdata 9/oradata/UP 6 F/data 1_64 m_UP 6 F_01. dbf' SIZE 2000 M AUTOEXTEND OFF, LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64 M BLOCKSIZE 8 K SEGMENT SPACE MANAGEMENT AUTO; 9/25/2020 DBA Survival Guide 47

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. 9/25/2020 DBA Survival Guide 48

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_MONITO RING(TRUE); 9/25/2020 DBA Survival Guide 49

Logically Organized Structure • Created tablespaces for Banner data. Decided to use the same extent sizes Oracle uses for AUTOALLOCATE – – DATA 1_64 K, DATA 1_1 M, DATA 1_8 M, DATA 1_64 M DATA 2_64 K, DATA 2_1 M, DATA 2_8 M, DATA 2_64 M INDX 1_64 K, INDX 1_1 M, INDX 1_8 M, INDX 1_64 M INDX 2_64 K, INDX 2_1 M, INDX 2_8 M, INDX 2_64 M 9/25/2020 DBA Survival Guide 50

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 9/25/2020 DBA Survival Guide 51

Logically Organized Structure • Special Tablespaces: – WORK_DATA – LOB_64 k – DEVELOPMENT – PERF_DATA – MV_LOGS – MERCURY 9/25/2020 DBA Survival Guide 52

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 9/25/2020 DBA Survival Guide 53

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 DATA 1_1 M has 65 1 M extents it is a candidate to be moved to DATA 1_64 M 9/25/2020 DBA Survival Guide 54

Other neat things! DBA Survival Guide

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 9/25/2020 DBA Survival Guide 56

History of Dataguard • • • What is a standby database Manual Log Shipping in 7. X Physical Standby Read-Only Physical Standby Logical Standby (Why it doesn’t work with Banner) How 10 g will help 9/25/2020 DBA Survival Guide 57

New methods to gather statistics • • The demise of RBO Gather Auto with Segment monitoring Gathering system statistics Much smarter in 10 g 9/25/2020 DBA Survival Guide 58

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 9 i Release 1, improved in 9 i Release 2 with the select …as of clause • Flashback database in 10 g 9/25/2020 DBA Survival Guide 59

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 9/25/2020 DBA Survival Guide 60

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. 9/25/2020 DBA Survival Guide 61

Log. Miner • Tool to mine the data in archive logs • Many enhancements in 9 i – 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) 9/25/2020 DBA Survival Guide 62

RMAN • Oracle is pushing people towards RMAN • Much Improved in 9 i – Persistent configuration • • Block Recovery Restartable backups Integration with OEM Clone a database now supported 9/25/2020 DBA Survival Guide 63

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) 9/25/2020 DBA Survival Guide 64

Block Compression • Example Create table TBRACCD_2004 COMPRESS as select * from taismgr. tbraccd order by tbraccd_term_code 9/25/2020 DBA Survival Guide 65

Block Compression • Determine low cardinality from DBA_TAB_COL_STATISTICS 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 9/25/2020 DBA Survival Guide 66

Dynamic sampling • Database parameter or Hint • Will sample the data in the tables prior to parsing a statement • Will use CBO 9/25/2020 DBA Survival Guide 67

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 9/25/2020 DBA Survival Guide 68

Questions Please fill out the evaluation form for this session and hand it to the moderator on your way out. 9/25/2020 DBA Survival Guide 69